Simple and Powerful ORM for Go, support mysql,postgres,tidb,sqlite3,mssql,oracle https://xorm.io
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.

655 lines
17KB

  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. "crypto/tls"
  7. "errors"
  8. "fmt"
  9. "regexp"
  10. "strconv"
  11. "strings"
  12. "time"
  13. "xorm.io/core"
  14. )
  15. var (
  16. mysqlReservedWords = map[string]bool{
  17. "ADD": true,
  18. "ALL": true,
  19. "ALTER": true,
  20. "ANALYZE": true,
  21. "AND": true,
  22. "AS": true,
  23. "ASC": true,
  24. "ASENSITIVE": true,
  25. "BEFORE": true,
  26. "BETWEEN": true,
  27. "BIGINT": true,
  28. "BINARY": true,
  29. "BLOB": true,
  30. "BOTH": true,
  31. "BY": true,
  32. "CALL": true,
  33. "CASCADE": true,
  34. "CASE": true,
  35. "CHANGE": true,
  36. "CHAR": true,
  37. "CHARACTER": true,
  38. "CHECK": true,
  39. "COLLATE": true,
  40. "COLUMN": true,
  41. "CONDITION": true,
  42. "CONNECTION": true,
  43. "CONSTRAINT": true,
  44. "CONTINUE": true,
  45. "CONVERT": true,
  46. "CREATE": true,
  47. "CROSS": true,
  48. "CURRENT_DATE": true,
  49. "CURRENT_TIME": true,
  50. "CURRENT_TIMESTAMP": true,
  51. "CURRENT_USER": true,
  52. "CURSOR": true,
  53. "DATABASE": true,
  54. "DATABASES": true,
  55. "DAY_HOUR": true,
  56. "DAY_MICROSECOND": true,
  57. "DAY_MINUTE": true,
  58. "DAY_SECOND": true,
  59. "DEC": true,
  60. "DECIMAL": true,
  61. "DECLARE": true,
  62. "DEFAULT": true,
  63. "DELAYED": true,
  64. "DELETE": true,
  65. "DESC": true,
  66. "DESCRIBE": true,
  67. "DETERMINISTIC": true,
  68. "DISTINCT": true,
  69. "DISTINCTROW": true,
  70. "DIV": true,
  71. "DOUBLE": true,
  72. "DROP": true,
  73. "DUAL": true,
  74. "EACH": true,
  75. "ELSE": true,
  76. "ELSEIF": true,
  77. "ENCLOSED": true,
  78. "ESCAPED": true,
  79. "EXISTS": true,
  80. "EXIT": true,
  81. "EXPLAIN": true,
  82. "FALSE": true,
  83. "FETCH": true,
  84. "FLOAT": true,
  85. "FLOAT4": true,
  86. "FLOAT8": true,
  87. "FOR": true,
  88. "FORCE": true,
  89. "FOREIGN": true,
  90. "FROM": true,
  91. "FULLTEXT": true,
  92. "GOTO": true,
  93. "GRANT": true,
  94. "GROUP": true,
  95. "HAVING": true,
  96. "HIGH_PRIORITY": true,
  97. "HOUR_MICROSECOND": true,
  98. "HOUR_MINUTE": true,
  99. "HOUR_SECOND": true,
  100. "IF": true,
  101. "IGNORE": true,
  102. "IN": true, "INDEX": true,
  103. "INFILE": true, "INNER": true, "INOUT": true,
  104. "INSENSITIVE": true, "INSERT": true, "INT": true,
  105. "INT1": true, "INT2": true, "INT3": true,
  106. "INT4": true, "INT8": true, "INTEGER": true,
  107. "INTERVAL": true, "INTO": true, "IS": true,
  108. "ITERATE": true, "JOIN": true, "KEY": true,
  109. "KEYS": true, "KILL": true, "LABEL": true,
  110. "LEADING": true, "LEAVE": true, "LEFT": true,
  111. "LIKE": true, "LIMIT": true, "LINEAR": true,
  112. "LINES": true, "LOAD": true, "LOCALTIME": true,
  113. "LOCALTIMESTAMP": true, "LOCK": true, "LONG": true,
  114. "LONGBLOB": true, "LONGTEXT": true, "LOOP": true,
  115. "LOW_PRIORITY": true, "MATCH": true, "MEDIUMBLOB": true,
  116. "MEDIUMINT": true, "MEDIUMTEXT": true, "MIDDLEINT": true,
  117. "MINUTE_MICROSECOND": true, "MINUTE_SECOND": true, "MOD": true,
  118. "MODIFIES": true, "NATURAL": true, "NOT": true,
  119. "NO_WRITE_TO_BINLOG": true, "NULL": true, "NUMERIC": true,
  120. "ON OPTIMIZE": true, "OPTION": true,
  121. "OPTIONALLY": true, "OR": true, "ORDER": true,
  122. "OUT": true, "OUTER": true, "OUTFILE": true,
  123. "PRECISION": true, "PRIMARY": true, "PROCEDURE": true,
  124. "PURGE": true, "RAID0": true, "RANGE": true,
  125. "READ": true, "READS": true, "REAL": true,
  126. "REFERENCES": true, "REGEXP": true, "RELEASE": true,
  127. "RENAME": true, "REPEAT": true, "REPLACE": true,
  128. "REQUIRE": true, "RESTRICT": true, "RETURN": true,
  129. "REVOKE": true, "RIGHT": true, "RLIKE": true,
  130. "SCHEMA": true, "SCHEMAS": true, "SECOND_MICROSECOND": true,
  131. "SELECT": true, "SENSITIVE": true, "SEPARATOR": true,
  132. "SET": true, "SHOW": true, "SMALLINT": true,
  133. "SPATIAL": true, "SPECIFIC": true, "SQL": true,
  134. "SQLEXCEPTION": true, "SQLSTATE": true, "SQLWARNING": true,
  135. "SQL_BIG_RESULT": true, "SQL_CALC_FOUND_ROWS": true, "SQL_SMALL_RESULT": true,
  136. "SSL": true, "STARTING": true, "STRAIGHT_JOIN": true,
  137. "TABLE": true, "TERMINATED": true, "THEN": true,
  138. "TINYBLOB": true, "TINYINT": true, "TINYTEXT": true,
  139. "TO": true, "TRAILING": true, "TRIGGER": true,
  140. "TRUE": true, "UNDO": true, "UNION": true,
  141. "UNIQUE": true, "UNLOCK": true, "UNSIGNED": true,
  142. "UPDATE": true, "USAGE": true, "USE": true,
  143. "USING": true, "UTC_DATE": true, "UTC_TIME": true,
  144. "UTC_TIMESTAMP": true, "VALUES": true, "VARBINARY": true,
  145. "VARCHAR": true,
  146. "VARCHARACTER": true,
  147. "VARYING": true,
  148. "WHEN": true,
  149. "WHERE": true,
  150. "WHILE": true,
  151. "WITH": true,
  152. "WRITE": true,
  153. "X509": true,
  154. "XOR": true,
  155. "YEAR_MONTH": true,
  156. "ZEROFILL": true,
  157. }
  158. )
  159. type mysql struct {
  160. core.Base
  161. net string
  162. addr string
  163. params map[string]string
  164. loc *time.Location
  165. timeout time.Duration
  166. tls *tls.Config
  167. allowAllFiles bool
  168. allowOldPasswords bool
  169. clientFoundRows bool
  170. rowFormat string
  171. }
  172. func (db *mysql) Init(d *core.DB, uri *core.Uri, drivername, dataSourceName string) error {
  173. return db.Base.Init(d, db, uri, drivername, dataSourceName)
  174. }
  175. func (db *mysql) SetParams(params map[string]string) {
  176. rowFormat, ok := params["rowFormat"]
  177. if ok {
  178. var t = strings.ToUpper(rowFormat)
  179. switch t {
  180. case "COMPACT":
  181. fallthrough
  182. case "REDUNDANT":
  183. fallthrough
  184. case "DYNAMIC":
  185. fallthrough
  186. case "COMPRESSED":
  187. db.rowFormat = t
  188. break
  189. default:
  190. break
  191. }
  192. }
  193. }
  194. func (db *mysql) SqlType(c *core.Column) string {
  195. var res string
  196. switch t := c.SQLType.Name; t {
  197. case core.Bool:
  198. res = core.TinyInt
  199. c.Length = 1
  200. case core.Serial:
  201. c.IsAutoIncrement = true
  202. c.IsPrimaryKey = true
  203. c.Nullable = false
  204. res = core.Int
  205. case core.BigSerial:
  206. c.IsAutoIncrement = true
  207. c.IsPrimaryKey = true
  208. c.Nullable = false
  209. res = core.BigInt
  210. case core.Bytea:
  211. res = core.Blob
  212. case core.TimeStampz:
  213. res = core.Char
  214. c.Length = 64
  215. case core.Enum: // mysql enum
  216. res = core.Enum
  217. res += "("
  218. opts := ""
  219. for v := range c.EnumOptions {
  220. opts += fmt.Sprintf(",'%v'", v)
  221. }
  222. res += strings.TrimLeft(opts, ",")
  223. res += ")"
  224. case core.Set: // mysql set
  225. res = core.Set
  226. res += "("
  227. opts := ""
  228. for v := range c.SetOptions {
  229. opts += fmt.Sprintf(",'%v'", v)
  230. }
  231. res += strings.TrimLeft(opts, ",")
  232. res += ")"
  233. case core.NVarchar:
  234. res = core.Varchar
  235. case core.Uuid:
  236. res = core.Varchar
  237. c.Length = 40
  238. case core.Json:
  239. res = core.Text
  240. default:
  241. res = t
  242. }
  243. hasLen1 := (c.Length > 0)
  244. hasLen2 := (c.Length2 > 0)
  245. if res == core.BigInt && !hasLen1 && !hasLen2 {
  246. c.Length = 20
  247. hasLen1 = true
  248. }
  249. if hasLen2 {
  250. res += "(" + strconv.Itoa(c.Length) + "," + strconv.Itoa(c.Length2) + ")"
  251. } else if hasLen1 {
  252. res += "(" + strconv.Itoa(c.Length) + ")"
  253. }
  254. return res
  255. }
  256. func (db *mysql) SupportInsertMany() bool {
  257. return true
  258. }
  259. func (db *mysql) IsReserved(name string) bool {
  260. _, ok := mysqlReservedWords[name]
  261. return ok
  262. }
  263. func (db *mysql) Quote(name string) string {
  264. return "`" + name + "`"
  265. }
  266. func (db *mysql) SupportEngine() bool {
  267. return true
  268. }
  269. func (db *mysql) AutoIncrStr() string {
  270. return "AUTO_INCREMENT"
  271. }
  272. func (db *mysql) SupportCharset() bool {
  273. return true
  274. }
  275. func (db *mysql) IndexOnTable() bool {
  276. return true
  277. }
  278. func (db *mysql) IndexCheckSql(tableName, idxName string) (string, []interface{}) {
  279. args := []interface{}{db.DbName, tableName, idxName}
  280. sql := "SELECT `INDEX_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS`"
  281. sql += " WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? AND `INDEX_NAME`=?"
  282. return sql, args
  283. }
  284. /*func (db *mysql) ColumnCheckSql(tableName, colName string) (string, []interface{}) {
  285. args := []interface{}{db.DbName, tableName, colName}
  286. sql := "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? AND `COLUMN_NAME` = ?"
  287. return sql, args
  288. }*/
  289. func (db *mysql) TableCheckSql(tableName string) (string, []interface{}) {
  290. args := []interface{}{db.DbName, tableName}
  291. sql := "SELECT `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? and `TABLE_NAME`=?"
  292. return sql, args
  293. }
  294. func (db *mysql) GetColumns(tableName string) ([]string, map[string]*core.Column, error) {
  295. args := []interface{}{db.DbName, tableName}
  296. s := "SELECT `COLUMN_NAME`, `IS_NULLABLE`, `COLUMN_DEFAULT`, `COLUMN_TYPE`," +
  297. " `COLUMN_KEY`, `EXTRA`,`COLUMN_COMMENT` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?"
  298. db.LogSQL(s, args)
  299. rows, err := db.DB().Query(s, args...)
  300. if err != nil {
  301. return nil, nil, err
  302. }
  303. defer rows.Close()
  304. cols := make(map[string]*core.Column)
  305. colSeq := make([]string, 0)
  306. for rows.Next() {
  307. col := new(core.Column)
  308. col.Indexes = make(map[string]int)
  309. var columnName, isNullable, colType, colKey, extra, comment string
  310. var colDefault *string
  311. err = rows.Scan(&columnName, &isNullable, &colDefault, &colType, &colKey, &extra, &comment)
  312. if err != nil {
  313. return nil, nil, err
  314. }
  315. col.Name = strings.Trim(columnName, "` ")
  316. col.Comment = comment
  317. if "YES" == isNullable {
  318. col.Nullable = true
  319. }
  320. if colDefault != nil {
  321. col.Default = *colDefault
  322. col.DefaultIsEmpty = false
  323. } else {
  324. col.DefaultIsEmpty = true
  325. }
  326. cts := strings.Split(colType, "(")
  327. colName := cts[0]
  328. colType = strings.ToUpper(colName)
  329. var len1, len2 int
  330. if len(cts) == 2 {
  331. idx := strings.Index(cts[1], ")")
  332. if colType == core.Enum && cts[1][0] == '\'' { // enum
  333. options := strings.Split(cts[1][0:idx], ",")
  334. col.EnumOptions = make(map[string]int)
  335. for k, v := range options {
  336. v = strings.TrimSpace(v)
  337. v = strings.Trim(v, "'")
  338. col.EnumOptions[v] = k
  339. }
  340. } else if colType == core.Set && cts[1][0] == '\'' {
  341. options := strings.Split(cts[1][0:idx], ",")
  342. col.SetOptions = make(map[string]int)
  343. for k, v := range options {
  344. v = strings.TrimSpace(v)
  345. v = strings.Trim(v, "'")
  346. col.SetOptions[v] = k
  347. }
  348. } else {
  349. lens := strings.Split(cts[1][0:idx], ",")
  350. len1, err = strconv.Atoi(strings.TrimSpace(lens[0]))
  351. if err != nil {
  352. return nil, nil, err
  353. }
  354. if len(lens) == 2 {
  355. len2, err = strconv.Atoi(lens[1])
  356. if err != nil {
  357. return nil, nil, err
  358. }
  359. }
  360. }
  361. }
  362. if colType == "FLOAT UNSIGNED" {
  363. colType = "FLOAT"
  364. }
  365. if colType == "DOUBLE UNSIGNED" {
  366. colType = "DOUBLE"
  367. }
  368. col.Length = len1
  369. col.Length2 = len2
  370. if _, ok := core.SqlTypes[colType]; ok {
  371. col.SQLType = core.SQLType{Name: colType, DefaultLength: len1, DefaultLength2: len2}
  372. } else {
  373. return nil, nil, fmt.Errorf("Unknown colType %v", colType)
  374. }
  375. if colKey == "PRI" {
  376. col.IsPrimaryKey = true
  377. }
  378. if colKey == "UNI" {
  379. // col.is
  380. }
  381. if extra == "auto_increment" {
  382. col.IsAutoIncrement = true
  383. }
  384. if !col.DefaultIsEmpty {
  385. if col.SQLType.IsText() {
  386. col.Default = "'" + col.Default + "'"
  387. } else if col.SQLType.IsTime() && col.Default != "CURRENT_TIMESTAMP" {
  388. col.Default = "'" + col.Default + "'"
  389. }
  390. }
  391. cols[col.Name] = col
  392. colSeq = append(colSeq, col.Name)
  393. }
  394. return colSeq, cols, nil
  395. }
  396. func (db *mysql) GetTables() ([]*core.Table, error) {
  397. args := []interface{}{db.DbName}
  398. s := "SELECT `TABLE_NAME`, `ENGINE`, `TABLE_ROWS`, `AUTO_INCREMENT`, `TABLE_COMMENT` from " +
  399. "`INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? AND (`ENGINE`='MyISAM' OR `ENGINE` = 'InnoDB' OR `ENGINE` = 'TokuDB')"
  400. db.LogSQL(s, args)
  401. rows, err := db.DB().Query(s, args...)
  402. if err != nil {
  403. return nil, err
  404. }
  405. defer rows.Close()
  406. tables := make([]*core.Table, 0)
  407. for rows.Next() {
  408. table := core.NewEmptyTable()
  409. var name, engine, tableRows, comment string
  410. var autoIncr *string
  411. err = rows.Scan(&name, &engine, &tableRows, &autoIncr, &comment)
  412. if err != nil {
  413. return nil, err
  414. }
  415. table.Name = name
  416. table.Comment = comment
  417. table.StoreEngine = engine
  418. tables = append(tables, table)
  419. }
  420. return tables, nil
  421. }
  422. func (db *mysql) GetIndexes(tableName string) (map[string]*core.Index, error) {
  423. args := []interface{}{db.DbName, tableName}
  424. s := "SELECT `INDEX_NAME`, `NON_UNIQUE`, `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?"
  425. db.LogSQL(s, args)
  426. rows, err := db.DB().Query(s, args...)
  427. if err != nil {
  428. return nil, err
  429. }
  430. defer rows.Close()
  431. indexes := make(map[string]*core.Index, 0)
  432. for rows.Next() {
  433. var indexType int
  434. var indexName, colName, nonUnique string
  435. err = rows.Scan(&indexName, &nonUnique, &colName)
  436. if err != nil {
  437. return nil, err
  438. }
  439. if indexName == "PRIMARY" {
  440. continue
  441. }
  442. if "YES" == nonUnique || nonUnique == "1" {
  443. indexType = core.IndexType
  444. } else {
  445. indexType = core.UniqueType
  446. }
  447. colName = strings.Trim(colName, "` ")
  448. var isRegular bool
  449. if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
  450. indexName = indexName[5+len(tableName):]
  451. isRegular = true
  452. }
  453. var index *core.Index
  454. var ok bool
  455. if index, ok = indexes[indexName]; !ok {
  456. index = new(core.Index)
  457. index.IsRegular = isRegular
  458. index.Type = indexType
  459. index.Name = indexName
  460. indexes[indexName] = index
  461. }
  462. index.AddColumn(colName)
  463. }
  464. return indexes, nil
  465. }
  466. func (db *mysql) CreateTableSql(table *core.Table, tableName, storeEngine, charset string) string {
  467. var sql string
  468. sql = "CREATE TABLE IF NOT EXISTS "
  469. if tableName == "" {
  470. tableName = table.Name
  471. }
  472. sql += db.Quote(tableName)
  473. sql += " ("
  474. if len(table.ColumnsSeq()) > 0 {
  475. pkList := table.PrimaryKeys
  476. for _, colName := range table.ColumnsSeq() {
  477. col := table.GetColumn(colName)
  478. if col.IsPrimaryKey && len(pkList) == 1 {
  479. sql += col.String(db)
  480. } else {
  481. sql += col.StringNoPk(db)
  482. }
  483. sql = strings.TrimSpace(sql)
  484. if len(col.Comment) > 0 {
  485. sql += " COMMENT '" + col.Comment + "'"
  486. }
  487. sql += ", "
  488. }
  489. if len(pkList) > 1 {
  490. sql += "PRIMARY KEY ( "
  491. sql += db.Quote(strings.Join(pkList, db.Quote(",")))
  492. sql += " ), "
  493. }
  494. sql = sql[:len(sql)-2]
  495. }
  496. sql += ")"
  497. if storeEngine != "" {
  498. sql += " ENGINE=" + storeEngine
  499. }
  500. if len(charset) == 0 {
  501. charset = db.URI().Charset
  502. }
  503. if len(charset) != 0 {
  504. sql += " DEFAULT CHARSET " + charset
  505. }
  506. if db.rowFormat != "" {
  507. sql += " ROW_FORMAT=" + db.rowFormat
  508. }
  509. return sql
  510. }
  511. func (db *mysql) Filters() []core.Filter {
  512. return []core.Filter{&core.IdFilter{}}
  513. }
  514. type mymysqlDriver struct {
  515. }
  516. func (p *mymysqlDriver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  517. db := &core.Uri{DbType: core.MYSQL}
  518. pd := strings.SplitN(dataSourceName, "*", 2)
  519. if len(pd) == 2 {
  520. // Parse protocol part of URI
  521. p := strings.SplitN(pd[0], ":", 2)
  522. if len(p) != 2 {
  523. return nil, errors.New("Wrong protocol part of URI")
  524. }
  525. db.Proto = p[0]
  526. options := strings.Split(p[1], ",")
  527. db.Raddr = options[0]
  528. for _, o := range options[1:] {
  529. kv := strings.SplitN(o, "=", 2)
  530. var k, v string
  531. if len(kv) == 2 {
  532. k, v = kv[0], kv[1]
  533. } else {
  534. k, v = o, "true"
  535. }
  536. switch k {
  537. case "laddr":
  538. db.Laddr = v
  539. case "timeout":
  540. to, err := time.ParseDuration(v)
  541. if err != nil {
  542. return nil, err
  543. }
  544. db.Timeout = to
  545. default:
  546. return nil, errors.New("Unknown option: " + k)
  547. }
  548. }
  549. // Remove protocol part
  550. pd = pd[1:]
  551. }
  552. // Parse database part of URI
  553. dup := strings.SplitN(pd[0], "/", 3)
  554. if len(dup) != 3 {
  555. return nil, errors.New("Wrong database part of URI")
  556. }
  557. db.DbName = dup[0]
  558. db.User = dup[1]
  559. db.Passwd = dup[2]
  560. return db, nil
  561. }
  562. type mysqlDriver struct {
  563. }
  564. func (p *mysqlDriver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  565. dsnPattern := regexp.MustCompile(
  566. `^(?:(?P<user>.*?)(?::(?P<passwd>.*))?@)?` + // [user[:password]@]
  567. `(?:(?P<net>[^\(]*)(?:\((?P<addr>[^\)]*)\))?)?` + // [net[(addr)]]
  568. `\/(?P<dbname>.*?)` + // /dbname
  569. `(?:\?(?P<params>[^\?]*))?$`) // [?param1=value1&paramN=valueN]
  570. matches := dsnPattern.FindStringSubmatch(dataSourceName)
  571. // tlsConfigRegister := make(map[string]*tls.Config)
  572. names := dsnPattern.SubexpNames()
  573. uri := &core.Uri{DbType: core.MYSQL}
  574. for i, match := range matches {
  575. switch names[i] {
  576. case "dbname":
  577. uri.DbName = match
  578. case "params":
  579. if len(match) > 0 {
  580. kvs := strings.Split(match, "&")
  581. for _, kv := range kvs {
  582. splits := strings.Split(kv, "=")
  583. if len(splits) == 2 {
  584. switch splits[0] {
  585. case "charset":
  586. uri.Charset = splits[1]
  587. }
  588. }
  589. }
  590. }
  591. }
  592. }
  593. return uri, nil
  594. }