THIS IS A TEST INSTANCE ONLY! REPOSITORIES CAN BE DELETED AT ANY TIME!

You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

493 lines
12KB

  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. "xorm.io/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) AutoIncrStr() string {
  194. return "AUTOINCREMENT"
  195. }
  196. func (db *sqlite3) SupportEngine() bool {
  197. return false
  198. }
  199. func (db *sqlite3) SupportCharset() bool {
  200. return false
  201. }
  202. func (db *sqlite3) IndexOnTable() bool {
  203. return false
  204. }
  205. func (db *sqlite3) IndexCheckSql(tableName, idxName string) (string, []interface{}) {
  206. args := []interface{}{idxName}
  207. return "SELECT name FROM sqlite_master WHERE type='index' and name = ?", args
  208. }
  209. func (db *sqlite3) TableCheckSql(tableName string) (string, []interface{}) {
  210. args := []interface{}{tableName}
  211. return "SELECT name FROM sqlite_master WHERE type='table' and name = ?", args
  212. }
  213. func (db *sqlite3) DropIndexSql(tableName string, index *core.Index) string {
  214. // var unique string
  215. quote := db.Quote
  216. idxName := index.Name
  217. if !strings.HasPrefix(idxName, "UQE_") &&
  218. !strings.HasPrefix(idxName, "IDX_") {
  219. if index.Type == core.UniqueType {
  220. idxName = fmt.Sprintf("UQE_%v_%v", tableName, index.Name)
  221. } else {
  222. idxName = fmt.Sprintf("IDX_%v_%v", tableName, index.Name)
  223. }
  224. }
  225. return fmt.Sprintf("DROP INDEX %v", quote(idxName))
  226. }
  227. func (db *sqlite3) ForUpdateSql(query string) string {
  228. return query
  229. }
  230. /*func (db *sqlite3) ColumnCheckSql(tableName, colName string) (string, []interface{}) {
  231. args := []interface{}{tableName}
  232. sql := "SELECT name FROM sqlite_master WHERE type='table' and name = ? and ((sql like '%`" + colName + "`%') or (sql like '%[" + colName + "]%'))"
  233. return sql, args
  234. }*/
  235. func (db *sqlite3) IsColumnExist(tableName, colName string) (bool, error) {
  236. args := []interface{}{tableName}
  237. query := "SELECT name FROM sqlite_master WHERE type='table' and name = ? and ((sql like '%`" + colName + "`%') or (sql like '%[" + colName + "]%'))"
  238. db.LogSQL(query, args)
  239. rows, err := db.DB().Query(query, args...)
  240. if err != nil {
  241. return false, err
  242. }
  243. defer rows.Close()
  244. if rows.Next() {
  245. return true, nil
  246. }
  247. return false, nil
  248. }
  249. // splitColStr splits a sqlite col strings as fields
  250. func splitColStr(colStr string) []string {
  251. colStr = strings.TrimSpace(colStr)
  252. var results = make([]string, 0, 10)
  253. var lastIdx int
  254. var hasC, hasQuote bool
  255. for i, c := range colStr {
  256. if c == ' ' && !hasQuote {
  257. if hasC {
  258. results = append(results, colStr[lastIdx:i])
  259. hasC = false
  260. }
  261. } else {
  262. if c == '\'' {
  263. hasQuote = !hasQuote
  264. }
  265. if !hasC {
  266. lastIdx = i
  267. }
  268. hasC = true
  269. if i == len(colStr)-1 {
  270. results = append(results, colStr[lastIdx:i+1])
  271. }
  272. }
  273. }
  274. return results
  275. }
  276. func parseString(colStr string) (*core.Column, error) {
  277. fields := splitColStr(colStr)
  278. col := new(core.Column)
  279. col.Indexes = make(map[string]int)
  280. col.Nullable = true
  281. col.DefaultIsEmpty = true
  282. for idx, field := range fields {
  283. if idx == 0 {
  284. col.Name = strings.Trim(strings.Trim(field, "`[] "), `"`)
  285. continue
  286. } else if idx == 1 {
  287. col.SQLType = core.SQLType{Name: field, DefaultLength: 0, DefaultLength2: 0}
  288. continue
  289. }
  290. switch field {
  291. case "PRIMARY":
  292. col.IsPrimaryKey = true
  293. case "AUTOINCREMENT":
  294. col.IsAutoIncrement = true
  295. case "NULL":
  296. if fields[idx-1] == "NOT" {
  297. col.Nullable = false
  298. } else {
  299. col.Nullable = true
  300. }
  301. case "DEFAULT":
  302. col.Default = fields[idx+1]
  303. col.DefaultIsEmpty = false
  304. }
  305. }
  306. return col, nil
  307. }
  308. func (db *sqlite3) GetColumns(tableName string) ([]string, map[string]*core.Column, error) {
  309. args := []interface{}{tableName}
  310. s := "SELECT sql FROM sqlite_master WHERE type='table' and name = ?"
  311. db.LogSQL(s, args)
  312. rows, err := db.DB().Query(s, args...)
  313. if err != nil {
  314. return nil, nil, err
  315. }
  316. defer rows.Close()
  317. var name string
  318. for rows.Next() {
  319. err = rows.Scan(&name)
  320. if err != nil {
  321. return nil, nil, err
  322. }
  323. break
  324. }
  325. if name == "" {
  326. return nil, nil, errors.New("no table named " + tableName)
  327. }
  328. nStart := strings.Index(name, "(")
  329. nEnd := strings.LastIndex(name, ")")
  330. reg := regexp.MustCompile(`[^\(,\)]*(\([^\(]*\))?`)
  331. colCreates := reg.FindAllString(name[nStart+1:nEnd], -1)
  332. cols := make(map[string]*core.Column)
  333. colSeq := make([]string, 0)
  334. for _, colStr := range colCreates {
  335. reg = regexp.MustCompile(`,\s`)
  336. colStr = reg.ReplaceAllString(colStr, ",")
  337. if strings.HasPrefix(strings.TrimSpace(colStr), "PRIMARY KEY") {
  338. parts := strings.Split(strings.TrimSpace(colStr), "(")
  339. if len(parts) == 2 {
  340. pkCols := strings.Split(strings.TrimRight(strings.TrimSpace(parts[1]), ")"), ",")
  341. for _, pk := range pkCols {
  342. if col, ok := cols[strings.Trim(strings.TrimSpace(pk), "`")]; ok {
  343. col.IsPrimaryKey = true
  344. }
  345. }
  346. }
  347. continue
  348. }
  349. col, err := parseString(colStr)
  350. if err != nil {
  351. return colSeq, cols, err
  352. }
  353. cols[col.Name] = col
  354. colSeq = append(colSeq, col.Name)
  355. }
  356. return colSeq, cols, nil
  357. }
  358. func (db *sqlite3) GetTables() ([]*core.Table, error) {
  359. args := []interface{}{}
  360. s := "SELECT name FROM sqlite_master WHERE type='table'"
  361. db.LogSQL(s, args)
  362. rows, err := db.DB().Query(s, args...)
  363. if err != nil {
  364. return nil, err
  365. }
  366. defer rows.Close()
  367. tables := make([]*core.Table, 0)
  368. for rows.Next() {
  369. table := core.NewEmptyTable()
  370. err = rows.Scan(&table.Name)
  371. if err != nil {
  372. return nil, err
  373. }
  374. if table.Name == "sqlite_sequence" {
  375. continue
  376. }
  377. tables = append(tables, table)
  378. }
  379. return tables, nil
  380. }
  381. func (db *sqlite3) GetIndexes(tableName string) (map[string]*core.Index, error) {
  382. args := []interface{}{tableName}
  383. s := "SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ?"
  384. db.LogSQL(s, args)
  385. rows, err := db.DB().Query(s, args...)
  386. if err != nil {
  387. return nil, err
  388. }
  389. defer rows.Close()
  390. indexes := make(map[string]*core.Index, 0)
  391. for rows.Next() {
  392. var tmpSQL sql.NullString
  393. err = rows.Scan(&tmpSQL)
  394. if err != nil {
  395. return nil, err
  396. }
  397. if !tmpSQL.Valid {
  398. continue
  399. }
  400. sql := tmpSQL.String
  401. index := new(core.Index)
  402. nNStart := strings.Index(sql, "INDEX")
  403. nNEnd := strings.Index(sql, "ON")
  404. if nNStart == -1 || nNEnd == -1 {
  405. continue
  406. }
  407. indexName := strings.Trim(sql[nNStart+6:nNEnd], "` []")
  408. var isRegular bool
  409. if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
  410. index.Name = indexName[5+len(tableName):]
  411. isRegular = true
  412. } else {
  413. index.Name = indexName
  414. }
  415. if strings.HasPrefix(sql, "CREATE UNIQUE INDEX") {
  416. index.Type = core.UniqueType
  417. } else {
  418. index.Type = core.IndexType
  419. }
  420. nStart := strings.Index(sql, "(")
  421. nEnd := strings.Index(sql, ")")
  422. colIndexes := strings.Split(sql[nStart+1:nEnd], ",")
  423. index.Cols = make([]string, 0)
  424. for _, col := range colIndexes {
  425. index.Cols = append(index.Cols, strings.Trim(col, "` []"))
  426. }
  427. index.IsRegular = isRegular
  428. indexes[index.Name] = index
  429. }
  430. return indexes, nil
  431. }
  432. func (db *sqlite3) Filters() []core.Filter {
  433. return []core.Filter{&core.IdFilter{}}
  434. }
  435. type sqlite3Driver struct {
  436. }
  437. func (p *sqlite3Driver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  438. if strings.Contains(dataSourceName, "?") {
  439. dataSourceName = dataSourceName[:strings.Index(dataSourceName, "?")]
  440. }
  441. return &core.Uri{DbType: core.SQLITE, DbName: dataSourceName}, nil
  442. }