dialect_sqlite3.go 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456
  1. // Copyright 2015 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. "regexp"
  10. "strings"
  11. "github.com/go-xorm/core"
  12. )
  13. var (
  14. sqlite3ReservedWords = map[string]bool{
  15. "ABORT": true,
  16. "ACTION": true,
  17. "ADD": true,
  18. "AFTER": true,
  19. "ALL": true,
  20. "ALTER": true,
  21. "ANALYZE": true,
  22. "AND": true,
  23. "AS": true,
  24. "ASC": true,
  25. "ATTACH": true,
  26. "AUTOINCREMENT": true,
  27. "BEFORE": true,
  28. "BEGIN": true,
  29. "BETWEEN": true,
  30. "BY": true,
  31. "CASCADE": true,
  32. "CASE": true,
  33. "CAST": true,
  34. "CHECK": true,
  35. "COLLATE": true,
  36. "COLUMN": true,
  37. "COMMIT": true,
  38. "CONFLICT": true,
  39. "CONSTRAINT": true,
  40. "CREATE": true,
  41. "CROSS": true,
  42. "CURRENT_DATE": true,
  43. "CURRENT_TIME": true,
  44. "CURRENT_TIMESTAMP": true,
  45. "DATABASE": true,
  46. "DEFAULT": true,
  47. "DEFERRABLE": true,
  48. "DEFERRED": true,
  49. "DELETE": true,
  50. "DESC": true,
  51. "DETACH": true,
  52. "DISTINCT": true,
  53. "DROP": true,
  54. "EACH": true,
  55. "ELSE": true,
  56. "END": true,
  57. "ESCAPE": true,
  58. "EXCEPT": true,
  59. "EXCLUSIVE": true,
  60. "EXISTS": true,
  61. "EXPLAIN": true,
  62. "FAIL": true,
  63. "FOR": true,
  64. "FOREIGN": true,
  65. "FROM": true,
  66. "FULL": true,
  67. "GLOB": true,
  68. "GROUP": true,
  69. "HAVING": true,
  70. "IF": true,
  71. "IGNORE": true,
  72. "IMMEDIATE": true,
  73. "IN": true,
  74. "INDEX": true,
  75. "INDEXED": true,
  76. "INITIALLY": true,
  77. "INNER": true,
  78. "INSERT": true,
  79. "INSTEAD": true,
  80. "INTERSECT": true,
  81. "INTO": true,
  82. "IS": true,
  83. "ISNULL": true,
  84. "JOIN": true,
  85. "KEY": true,
  86. "LEFT": true,
  87. "LIKE": true,
  88. "LIMIT": true,
  89. "MATCH": true,
  90. "NATURAL": true,
  91. "NO": true,
  92. "NOT": true,
  93. "NOTNULL": true,
  94. "NULL": true,
  95. "OF": true,
  96. "OFFSET": true,
  97. "ON": true,
  98. "OR": true,
  99. "ORDER": true,
  100. "OUTER": true,
  101. "PLAN": true,
  102. "PRAGMA": true,
  103. "PRIMARY": true,
  104. "QUERY": true,
  105. "RAISE": true,
  106. "RECURSIVE": true,
  107. "REFERENCES": true,
  108. "REGEXP": true,
  109. "REINDEX": true,
  110. "RELEASE": true,
  111. "RENAME": true,
  112. "REPLACE": true,
  113. "RESTRICT": true,
  114. "RIGHT": true,
  115. "ROLLBACK": true,
  116. "ROW": true,
  117. "SAVEPOINT": true,
  118. "SELECT": true,
  119. "SET": true,
  120. "TABLE": true,
  121. "TEMP": true,
  122. "TEMPORARY": true,
  123. "THEN": true,
  124. "TO": true,
  125. "TRANSACTI": true,
  126. "TRIGGER": true,
  127. "UNION": true,
  128. "UNIQUE": true,
  129. "UPDATE": true,
  130. "USING": true,
  131. "VACUUM": true,
  132. "VALUES": true,
  133. "VIEW": true,
  134. "VIRTUAL": true,
  135. "WHEN": true,
  136. "WHERE": true,
  137. "WITH": true,
  138. "WITHOUT": true,
  139. }
  140. )
  141. type sqlite3 struct {
  142. core.Base
  143. }
  144. func (db *sqlite3) Init(d *core.DB, uri *core.Uri, drivername, dataSourceName string) error {
  145. return db.Base.Init(d, db, uri, drivername, dataSourceName)
  146. }
  147. func (db *sqlite3) SqlType(c *core.Column) string {
  148. switch t := c.SQLType.Name; t {
  149. case core.Bool:
  150. if c.Default == "true" {
  151. c.Default = "1"
  152. } else if c.Default == "false" {
  153. c.Default = "0"
  154. }
  155. return core.Integer
  156. case core.Date, core.DateTime, core.TimeStamp, core.Time:
  157. return core.DateTime
  158. case core.TimeStampz:
  159. return core.Text
  160. case core.Char, core.Varchar, core.NVarchar, core.TinyText,
  161. core.Text, core.MediumText, core.LongText, core.Json:
  162. return core.Text
  163. case core.Bit, core.TinyInt, core.SmallInt, core.MediumInt, core.Int, core.Integer, core.BigInt:
  164. return core.Integer
  165. case core.Float, core.Double, core.Real:
  166. return core.Real
  167. case core.Decimal, core.Numeric:
  168. return core.Numeric
  169. case core.TinyBlob, core.Blob, core.MediumBlob, core.LongBlob, core.Bytea, core.Binary, core.VarBinary:
  170. return core.Blob
  171. case core.Serial, core.BigSerial:
  172. c.IsPrimaryKey = true
  173. c.IsAutoIncrement = true
  174. c.Nullable = false
  175. return core.Integer
  176. default:
  177. return t
  178. }
  179. }
  180. func (db *sqlite3) FormatBytes(bs []byte) string {
  181. return fmt.Sprintf("X'%x'", bs)
  182. }
  183. func (db *sqlite3) SupportInsertMany() bool {
  184. return true
  185. }
  186. func (db *sqlite3) IsReserved(name string) bool {
  187. _, ok := sqlite3ReservedWords[name]
  188. return ok
  189. }
  190. func (db *sqlite3) Quote(name string) string {
  191. return "`" + name + "`"
  192. }
  193. func (db *sqlite3) QuoteStr() string {
  194. return "`"
  195. }
  196. func (db *sqlite3) AutoIncrStr() string {
  197. return "AUTOINCREMENT"
  198. }
  199. func (db *sqlite3) SupportEngine() bool {
  200. return false
  201. }
  202. func (db *sqlite3) SupportCharset() bool {
  203. return false
  204. }
  205. func (db *sqlite3) IndexOnTable() bool {
  206. return false
  207. }
  208. func (db *sqlite3) IndexCheckSql(tableName, idxName string) (string, []interface{}) {
  209. args := []interface{}{idxName}
  210. return "SELECT name FROM sqlite_master WHERE type='index' and name = ?", args
  211. }
  212. func (db *sqlite3) TableCheckSql(tableName string) (string, []interface{}) {
  213. args := []interface{}{tableName}
  214. return "SELECT name FROM sqlite_master WHERE type='table' and name = ?", args
  215. }
  216. func (db *sqlite3) DropIndexSql(tableName string, index *core.Index) string {
  217. //var unique string
  218. quote := db.Quote
  219. idxName := index.Name
  220. if !strings.HasPrefix(idxName, "UQE_") &&
  221. !strings.HasPrefix(idxName, "IDX_") {
  222. if index.Type == core.UniqueType {
  223. idxName = fmt.Sprintf("UQE_%v_%v", tableName, index.Name)
  224. } else {
  225. idxName = fmt.Sprintf("IDX_%v_%v", tableName, index.Name)
  226. }
  227. }
  228. return fmt.Sprintf("DROP INDEX %v", quote(idxName))
  229. }
  230. func (db *sqlite3) ForUpdateSql(query string) string {
  231. return query
  232. }
  233. /*func (db *sqlite3) ColumnCheckSql(tableName, colName string) (string, []interface{}) {
  234. args := []interface{}{tableName}
  235. sql := "SELECT name FROM sqlite_master WHERE type='table' and name = ? and ((sql like '%`" + colName + "`%') or (sql like '%[" + colName + "]%'))"
  236. return sql, args
  237. }*/
  238. func (db *sqlite3) IsColumnExist(tableName, colName string) (bool, error) {
  239. args := []interface{}{tableName}
  240. query := "SELECT name FROM sqlite_master WHERE type='table' and name = ? and ((sql like '%`" + colName + "`%') or (sql like '%[" + colName + "]%'))"
  241. db.LogSQL(query, args)
  242. rows, err := db.DB().Query(query, args...)
  243. if err != nil {
  244. return false, err
  245. }
  246. defer rows.Close()
  247. if rows.Next() {
  248. return true, nil
  249. }
  250. return false, nil
  251. }
  252. func (db *sqlite3) GetColumns(tableName string) ([]string, map[string]*core.Column, error) {
  253. args := []interface{}{tableName}
  254. s := "SELECT sql FROM sqlite_master WHERE type='table' and name = ?"
  255. db.LogSQL(s, args)
  256. rows, err := db.DB().Query(s, args...)
  257. if err != nil {
  258. return nil, nil, err
  259. }
  260. defer rows.Close()
  261. var name string
  262. for rows.Next() {
  263. err = rows.Scan(&name)
  264. if err != nil {
  265. return nil, nil, err
  266. }
  267. break
  268. }
  269. if name == "" {
  270. return nil, nil, errors.New("no table named " + tableName)
  271. }
  272. nStart := strings.Index(name, "(")
  273. nEnd := strings.LastIndex(name, ")")
  274. reg := regexp.MustCompile(`[^\(,\)]*(\([^\(]*\))?`)
  275. colCreates := reg.FindAllString(name[nStart+1:nEnd], -1)
  276. cols := make(map[string]*core.Column)
  277. colSeq := make([]string, 0)
  278. for _, colStr := range colCreates {
  279. reg = regexp.MustCompile(`,\s`)
  280. colStr = reg.ReplaceAllString(colStr, ",")
  281. if strings.HasPrefix(strings.TrimSpace(colStr), "PRIMARY KEY") {
  282. parts := strings.Split(strings.TrimSpace(colStr), "(")
  283. if len(parts) == 2 {
  284. pkCols := strings.Split(strings.TrimRight(strings.TrimSpace(parts[1]), ")"), ",")
  285. for _, pk := range pkCols {
  286. if col, ok := cols[strings.Trim(strings.TrimSpace(pk), "`")]; ok {
  287. col.IsPrimaryKey = true
  288. }
  289. }
  290. }
  291. continue
  292. }
  293. fields := strings.Fields(strings.TrimSpace(colStr))
  294. col := new(core.Column)
  295. col.Indexes = make(map[string]int)
  296. col.Nullable = true
  297. col.DefaultIsEmpty = true
  298. for idx, field := range fields {
  299. if idx == 0 {
  300. col.Name = strings.Trim(strings.Trim(field, "`[] "), `"`)
  301. continue
  302. } else if idx == 1 {
  303. col.SQLType = core.SQLType{Name: field, DefaultLength: 0, DefaultLength2: 0}
  304. }
  305. switch field {
  306. case "PRIMARY":
  307. col.IsPrimaryKey = true
  308. case "AUTOINCREMENT":
  309. col.IsAutoIncrement = true
  310. case "NULL":
  311. if fields[idx-1] == "NOT" {
  312. col.Nullable = false
  313. } else {
  314. col.Nullable = true
  315. }
  316. case "DEFAULT":
  317. col.Default = fields[idx+1]
  318. col.DefaultIsEmpty = false
  319. }
  320. }
  321. if !col.SQLType.IsNumeric() && !col.DefaultIsEmpty {
  322. col.Default = "'" + col.Default + "'"
  323. }
  324. cols[col.Name] = col
  325. colSeq = append(colSeq, col.Name)
  326. }
  327. return colSeq, cols, nil
  328. }
  329. func (db *sqlite3) GetTables() ([]*core.Table, error) {
  330. args := []interface{}{}
  331. s := "SELECT name FROM sqlite_master WHERE type='table'"
  332. db.LogSQL(s, args)
  333. rows, err := db.DB().Query(s, args...)
  334. if err != nil {
  335. return nil, err
  336. }
  337. defer rows.Close()
  338. tables := make([]*core.Table, 0)
  339. for rows.Next() {
  340. table := core.NewEmptyTable()
  341. err = rows.Scan(&table.Name)
  342. if err != nil {
  343. return nil, err
  344. }
  345. if table.Name == "sqlite_sequence" {
  346. continue
  347. }
  348. tables = append(tables, table)
  349. }
  350. return tables, nil
  351. }
  352. func (db *sqlite3) GetIndexes(tableName string) (map[string]*core.Index, error) {
  353. args := []interface{}{tableName}
  354. s := "SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ?"
  355. db.LogSQL(s, args)
  356. rows, err := db.DB().Query(s, args...)
  357. if err != nil {
  358. return nil, err
  359. }
  360. defer rows.Close()
  361. indexes := make(map[string]*core.Index, 0)
  362. for rows.Next() {
  363. var tmpSQL sql.NullString
  364. err = rows.Scan(&tmpSQL)
  365. if err != nil {
  366. return nil, err
  367. }
  368. if !tmpSQL.Valid {
  369. continue
  370. }
  371. sql := tmpSQL.String
  372. index := new(core.Index)
  373. nNStart := strings.Index(sql, "INDEX")
  374. nNEnd := strings.Index(sql, "ON")
  375. if nNStart == -1 || nNEnd == -1 {
  376. continue
  377. }
  378. indexName := strings.Trim(sql[nNStart+6:nNEnd], "` []")
  379. var isRegular bool
  380. if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
  381. index.Name = indexName[5+len(tableName):]
  382. isRegular = true
  383. } else {
  384. index.Name = indexName
  385. }
  386. if strings.HasPrefix(sql, "CREATE UNIQUE INDEX") {
  387. index.Type = core.UniqueType
  388. } else {
  389. index.Type = core.IndexType
  390. }
  391. nStart := strings.Index(sql, "(")
  392. nEnd := strings.Index(sql, ")")
  393. colIndexes := strings.Split(sql[nStart+1:nEnd], ",")
  394. index.Cols = make([]string, 0)
  395. for _, col := range colIndexes {
  396. index.Cols = append(index.Cols, strings.Trim(col, "` []"))
  397. }
  398. index.IsRegular = isRegular
  399. indexes[index.Name] = index
  400. }
  401. return indexes, nil
  402. }
  403. func (db *sqlite3) Filters() []core.Filter {
  404. return []core.Filter{&core.IdFilter{}}
  405. }
  406. type sqlite3Driver struct {
  407. }
  408. func (p *sqlite3Driver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  409. return &core.Uri{DbType: core.SQLITE, DbName: dataSourceName}, nil
  410. }