session_schema.go 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444
  1. // Copyright 2016 The Xorm Authors. All rights reserved.
  2. // Use of this source code is governed by a BSD-style
  3. // license that can be found in the LICENSE file.
  4. package xorm
  5. import (
  6. "database/sql"
  7. "errors"
  8. "fmt"
  9. "reflect"
  10. "strings"
  11. "github.com/go-xorm/core"
  12. )
  13. // Ping test if database is ok
  14. func (session *Session) Ping() error {
  15. defer session.resetStatement()
  16. if session.IsAutoClose {
  17. defer session.Close()
  18. }
  19. return session.DB().Ping()
  20. }
  21. // CreateTable create a table according a bean
  22. func (session *Session) CreateTable(bean interface{}) error {
  23. v := rValue(bean)
  24. if err := session.Statement.setRefValue(v); err != nil {
  25. return err
  26. }
  27. defer session.resetStatement()
  28. if session.IsAutoClose {
  29. defer session.Close()
  30. }
  31. return session.createOneTable()
  32. }
  33. // CreateIndexes create indexes
  34. func (session *Session) CreateIndexes(bean interface{}) error {
  35. v := rValue(bean)
  36. if err := session.Statement.setRefValue(v); err != nil {
  37. return err
  38. }
  39. defer session.resetStatement()
  40. if session.IsAutoClose {
  41. defer session.Close()
  42. }
  43. sqls := session.Statement.genIndexSQL()
  44. for _, sqlStr := range sqls {
  45. _, err := session.exec(sqlStr)
  46. if err != nil {
  47. return err
  48. }
  49. }
  50. return nil
  51. }
  52. // CreateUniques create uniques
  53. func (session *Session) CreateUniques(bean interface{}) error {
  54. v := rValue(bean)
  55. if err := session.Statement.setRefValue(v); err != nil {
  56. return err
  57. }
  58. defer session.resetStatement()
  59. if session.IsAutoClose {
  60. defer session.Close()
  61. }
  62. sqls := session.Statement.genUniqueSQL()
  63. for _, sqlStr := range sqls {
  64. _, err := session.exec(sqlStr)
  65. if err != nil {
  66. return err
  67. }
  68. }
  69. return nil
  70. }
  71. func (session *Session) createOneTable() error {
  72. sqlStr := session.Statement.genCreateTableSQL()
  73. _, err := session.exec(sqlStr)
  74. return err
  75. }
  76. // DropIndexes drop indexes
  77. func (session *Session) DropIndexes(bean interface{}) error {
  78. v := rValue(bean)
  79. if err := session.Statement.setRefValue(v); err != nil {
  80. return err
  81. }
  82. defer session.resetStatement()
  83. if session.IsAutoClose {
  84. defer session.Close()
  85. }
  86. sqls := session.Statement.genDelIndexSQL()
  87. for _, sqlStr := range sqls {
  88. _, err := session.exec(sqlStr)
  89. if err != nil {
  90. return err
  91. }
  92. }
  93. return nil
  94. }
  95. // DropTable drop table will drop table if exist, if drop failed, it will return error
  96. func (session *Session) DropTable(beanOrTableName interface{}) error {
  97. tableName, err := session.Engine.tableName(beanOrTableName)
  98. if err != nil {
  99. return err
  100. }
  101. var needDrop = true
  102. if !session.Engine.dialect.SupportDropIfExists() {
  103. sqlStr, args := session.Engine.dialect.TableCheckSql(tableName)
  104. results, err := session.query(sqlStr, args...)
  105. if err != nil {
  106. return err
  107. }
  108. needDrop = len(results) > 0
  109. }
  110. if needDrop {
  111. sqlStr := session.Engine.Dialect().DropTableSql(tableName)
  112. _, err = session.exec(sqlStr)
  113. return err
  114. }
  115. return nil
  116. }
  117. // IsTableExist if a table is exist
  118. func (session *Session) IsTableExist(beanOrTableName interface{}) (bool, error) {
  119. tableName, err := session.Engine.tableName(beanOrTableName)
  120. if err != nil {
  121. return false, err
  122. }
  123. return session.isTableExist(tableName)
  124. }
  125. func (session *Session) isTableExist(tableName string) (bool, error) {
  126. defer session.resetStatement()
  127. if session.IsAutoClose {
  128. defer session.Close()
  129. }
  130. sqlStr, args := session.Engine.dialect.TableCheckSql(tableName)
  131. results, err := session.query(sqlStr, args...)
  132. return len(results) > 0, err
  133. }
  134. // IsTableEmpty if table have any records
  135. func (session *Session) IsTableEmpty(bean interface{}) (bool, error) {
  136. v := rValue(bean)
  137. t := v.Type()
  138. if t.Kind() == reflect.String {
  139. return session.isTableEmpty(bean.(string))
  140. } else if t.Kind() == reflect.Struct {
  141. rows, err := session.Count(bean)
  142. return rows == 0, err
  143. }
  144. return false, errors.New("bean should be a struct or struct's point")
  145. }
  146. func (session *Session) isTableEmpty(tableName string) (bool, error) {
  147. defer session.resetStatement()
  148. if session.IsAutoClose {
  149. defer session.Close()
  150. }
  151. var total int64
  152. sqlStr := fmt.Sprintf("select count(*) from %s", session.Engine.Quote(tableName))
  153. err := session.DB().QueryRow(sqlStr).Scan(&total)
  154. session.saveLastSQL(sqlStr)
  155. if err != nil {
  156. if err == sql.ErrNoRows {
  157. err = nil
  158. }
  159. return true, err
  160. }
  161. return total == 0, nil
  162. }
  163. // find if index is exist according cols
  164. func (session *Session) isIndexExist2(tableName string, cols []string, unique bool) (bool, error) {
  165. defer session.resetStatement()
  166. if session.IsAutoClose {
  167. defer session.Close()
  168. }
  169. indexes, err := session.Engine.dialect.GetIndexes(tableName)
  170. if err != nil {
  171. return false, err
  172. }
  173. for _, index := range indexes {
  174. if sliceEq(index.Cols, cols) {
  175. if unique {
  176. return index.Type == core.UniqueType, nil
  177. }
  178. return index.Type == core.IndexType, nil
  179. }
  180. }
  181. return false, nil
  182. }
  183. func (session *Session) addColumn(colName string) error {
  184. defer session.resetStatement()
  185. if session.IsAutoClose {
  186. defer session.Close()
  187. }
  188. col := session.Statement.RefTable.GetColumn(colName)
  189. sql, args := session.Statement.genAddColumnStr(col)
  190. _, err := session.exec(sql, args...)
  191. return err
  192. }
  193. func (session *Session) addIndex(tableName, idxName string) error {
  194. defer session.resetStatement()
  195. if session.IsAutoClose {
  196. defer session.Close()
  197. }
  198. index := session.Statement.RefTable.Indexes[idxName]
  199. sqlStr := session.Engine.dialect.CreateIndexSql(tableName, index)
  200. _, err := session.exec(sqlStr)
  201. return err
  202. }
  203. func (session *Session) addUnique(tableName, uqeName string) error {
  204. defer session.resetStatement()
  205. if session.IsAutoClose {
  206. defer session.Close()
  207. }
  208. index := session.Statement.RefTable.Indexes[uqeName]
  209. sqlStr := session.Engine.dialect.CreateIndexSql(tableName, index)
  210. _, err := session.exec(sqlStr)
  211. return err
  212. }
  213. // Sync2 synchronize structs to database tables
  214. func (session *Session) Sync2(beans ...interface{}) error {
  215. engine := session.Engine
  216. tables, err := engine.DBMetas()
  217. if err != nil {
  218. return err
  219. }
  220. var structTables []*core.Table
  221. for _, bean := range beans {
  222. v := rValue(bean)
  223. table, err := engine.mapType(v)
  224. if err != nil {
  225. return err
  226. }
  227. structTables = append(structTables, table)
  228. var tbName = session.tbNameNoSchema(table)
  229. var oriTable *core.Table
  230. for _, tb := range tables {
  231. if strings.EqualFold(tb.Name, tbName) {
  232. oriTable = tb
  233. break
  234. }
  235. }
  236. if oriTable == nil {
  237. err = session.StoreEngine(session.Statement.StoreEngine).CreateTable(bean)
  238. if err != nil {
  239. return err
  240. }
  241. err = session.CreateUniques(bean)
  242. if err != nil {
  243. return err
  244. }
  245. err = session.CreateIndexes(bean)
  246. if err != nil {
  247. return err
  248. }
  249. } else {
  250. for _, col := range table.Columns() {
  251. var oriCol *core.Column
  252. for _, col2 := range oriTable.Columns() {
  253. if strings.EqualFold(col.Name, col2.Name) {
  254. oriCol = col2
  255. break
  256. }
  257. }
  258. if oriCol != nil {
  259. expectedType := engine.dialect.SqlType(col)
  260. curType := engine.dialect.SqlType(oriCol)
  261. if expectedType != curType {
  262. if expectedType == core.Text &&
  263. strings.HasPrefix(curType, core.Varchar) {
  264. // currently only support mysql & postgres
  265. if engine.dialect.DBType() == core.MYSQL ||
  266. engine.dialect.DBType() == core.POSTGRES {
  267. engine.logger.Infof("Table %s column %s change type from %s to %s\n",
  268. tbName, col.Name, curType, expectedType)
  269. _, err = engine.Exec(engine.dialect.ModifyColumnSql(table.Name, col))
  270. } else {
  271. engine.logger.Warnf("Table %s column %s db type is %s, struct type is %s\n",
  272. tbName, col.Name, curType, expectedType)
  273. }
  274. } else if strings.HasPrefix(curType, core.Varchar) && strings.HasPrefix(expectedType, core.Varchar) {
  275. if engine.dialect.DBType() == core.MYSQL {
  276. if oriCol.Length < col.Length {
  277. engine.logger.Infof("Table %s column %s change type from varchar(%d) to varchar(%d)\n",
  278. tbName, col.Name, oriCol.Length, col.Length)
  279. _, err = engine.Exec(engine.dialect.ModifyColumnSql(table.Name, col))
  280. }
  281. }
  282. } else {
  283. if !(strings.HasPrefix(curType, expectedType) && curType[len(expectedType)] == '(') {
  284. engine.logger.Warnf("Table %s column %s db type is %s, struct type is %s",
  285. tbName, col.Name, curType, expectedType)
  286. }
  287. }
  288. } else if expectedType == core.Varchar {
  289. if engine.dialect.DBType() == core.MYSQL {
  290. if oriCol.Length < col.Length {
  291. engine.logger.Infof("Table %s column %s change type from varchar(%d) to varchar(%d)\n",
  292. tbName, col.Name, oriCol.Length, col.Length)
  293. _, err = engine.Exec(engine.dialect.ModifyColumnSql(table.Name, col))
  294. }
  295. }
  296. }
  297. if col.Default != oriCol.Default {
  298. engine.logger.Warnf("Table %s Column %s db default is %s, struct default is %s",
  299. tbName, col.Name, oriCol.Default, col.Default)
  300. }
  301. if col.Nullable != oriCol.Nullable {
  302. engine.logger.Warnf("Table %s Column %s db nullable is %v, struct nullable is %v",
  303. tbName, col.Name, oriCol.Nullable, col.Nullable)
  304. }
  305. } else {
  306. session := engine.NewSession()
  307. session.Statement.RefTable = table
  308. session.Statement.tableName = tbName
  309. defer session.Close()
  310. err = session.addColumn(col.Name)
  311. }
  312. if err != nil {
  313. return err
  314. }
  315. }
  316. var foundIndexNames = make(map[string]bool)
  317. var addedNames = make(map[string]*core.Index)
  318. for name, index := range table.Indexes {
  319. var oriIndex *core.Index
  320. for name2, index2 := range oriTable.Indexes {
  321. if index.Equal(index2) {
  322. oriIndex = index2
  323. foundIndexNames[name2] = true
  324. break
  325. }
  326. }
  327. if oriIndex != nil {
  328. if oriIndex.Type != index.Type {
  329. sql := engine.dialect.DropIndexSql(tbName, oriIndex)
  330. _, err = engine.Exec(sql)
  331. if err != nil {
  332. return err
  333. }
  334. oriIndex = nil
  335. }
  336. }
  337. if oriIndex == nil {
  338. addedNames[name] = index
  339. }
  340. }
  341. for name2, index2 := range oriTable.Indexes {
  342. if _, ok := foundIndexNames[name2]; !ok {
  343. sql := engine.dialect.DropIndexSql(tbName, index2)
  344. _, err = engine.Exec(sql)
  345. if err != nil {
  346. return err
  347. }
  348. }
  349. }
  350. for name, index := range addedNames {
  351. if index.Type == core.UniqueType {
  352. session := engine.NewSession()
  353. session.Statement.RefTable = table
  354. session.Statement.tableName = tbName
  355. defer session.Close()
  356. err = session.addUnique(tbName, name)
  357. } else if index.Type == core.IndexType {
  358. session := engine.NewSession()
  359. session.Statement.RefTable = table
  360. session.Statement.tableName = tbName
  361. defer session.Close()
  362. err = session.addIndex(tbName, name)
  363. }
  364. if err != nil {
  365. return err
  366. }
  367. }
  368. }
  369. }
  370. for _, table := range tables {
  371. var oriTable *core.Table
  372. for _, structTable := range structTables {
  373. if strings.EqualFold(table.Name, session.tbNameNoSchema(structTable)) {
  374. oriTable = structTable
  375. break
  376. }
  377. }
  378. if oriTable == nil {
  379. //engine.LogWarnf("Table %s has no struct to mapping it", table.Name)
  380. continue
  381. }
  382. for _, colName := range table.ColumnsSeq() {
  383. if oriTable.GetColumn(colName) == nil {
  384. engine.logger.Warnf("Table %s has column %s but struct has not related field", table.Name, colName)
  385. }
  386. }
  387. }
  388. return nil
  389. }