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.

568 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. "errors"
  7. "fmt"
  8. "net/url"
  9. "strconv"
  10. "strings"
  11. "xorm.io/core"
  12. )
  13. var (
  14. mssqlReservedWords = map[string]bool{
  15. "ADD": true,
  16. "EXTERNAL": true,
  17. "PROCEDURE": true,
  18. "ALL": true,
  19. "FETCH": true,
  20. "PUBLIC": true,
  21. "ALTER": true,
  22. "FILE": true,
  23. "RAISERROR": true,
  24. "AND": true,
  25. "FILLFACTOR": true,
  26. "READ": true,
  27. "ANY": true,
  28. "FOR": true,
  29. "READTEXT": true,
  30. "AS": true,
  31. "FOREIGN": true,
  32. "RECONFIGURE": true,
  33. "ASC": true,
  34. "FREETEXT": true,
  35. "REFERENCES": true,
  36. "AUTHORIZATION": true,
  37. "FREETEXTTABLE": true,
  38. "REPLICATION": true,
  39. "BACKUP": true,
  40. "FROM": true,
  41. "RESTORE": true,
  42. "BEGIN": true,
  43. "FULL": true,
  44. "RESTRICT": true,
  45. "BETWEEN": true,
  46. "FUNCTION": true,
  47. "RETURN": true,
  48. "BREAK": true,
  49. "GOTO": true,
  50. "REVERT": true,
  51. "BROWSE": true,
  52. "GRANT": true,
  53. "REVOKE": true,
  54. "BULK": true,
  55. "GROUP": true,
  56. "RIGHT": true,
  57. "BY": true,
  58. "HAVING": true,
  59. "ROLLBACK": true,
  60. "CASCADE": true,
  61. "HOLDLOCK": true,
  62. "ROWCOUNT": true,
  63. "CASE": true,
  64. "IDENTITY": true,
  65. "ROWGUIDCOL": true,
  66. "CHECK": true,
  67. "IDENTITY_INSERT": true,
  68. "RULE": true,
  69. "CHECKPOINT": true,
  70. "IDENTITYCOL": true,
  71. "SAVE": true,
  72. "CLOSE": true,
  73. "IF": true,
  74. "SCHEMA": true,
  75. "CLUSTERED": true,
  76. "IN": true,
  77. "SECURITYAUDIT": true,
  78. "COALESCE": true,
  79. "INDEX": true,
  80. "SELECT": true,
  81. "COLLATE": true,
  82. "INNER": true,
  83. "SEMANTICKEYPHRASETABLE": true,
  84. "COLUMN": true,
  85. "INSERT": true,
  86. "SEMANTICSIMILARITYDETAILSTABLE": true,
  87. "COMMIT": true,
  88. "INTERSECT": true,
  89. "SEMANTICSIMILARITYTABLE": true,
  90. "COMPUTE": true,
  91. "INTO": true,
  92. "SESSION_USER": true,
  93. "CONSTRAINT": true,
  94. "IS": true,
  95. "SET": true,
  96. "CONTAINS": true,
  97. "JOIN": true,
  98. "SETUSER": true,
  99. "CONTAINSTABLE": true,
  100. "KEY": true,
  101. "SHUTDOWN": true,
  102. "CONTINUE": true,
  103. "KILL": true,
  104. "SOME": true,
  105. "CONVERT": true,
  106. "LEFT": true,
  107. "STATISTICS": true,
  108. "CREATE": true,
  109. "LIKE": true,
  110. "SYSTEM_USER": true,
  111. "CROSS": true,
  112. "LINENO": true,
  113. "TABLE": true,
  114. "CURRENT": true,
  115. "LOAD": true,
  116. "TABLESAMPLE": true,
  117. "CURRENT_DATE": true,
  118. "MERGE": true,
  119. "TEXTSIZE": true,
  120. "CURRENT_TIME": true,
  121. "NATIONAL": true,
  122. "THEN": true,
  123. "CURRENT_TIMESTAMP": true,
  124. "NOCHECK": true,
  125. "TO": true,
  126. "CURRENT_USER": true,
  127. "NONCLUSTERED": true,
  128. "TOP": true,
  129. "CURSOR": true,
  130. "NOT": true,
  131. "TRAN": true,
  132. "DATABASE": true,
  133. "NULL": true,
  134. "TRANSACTION": true,
  135. "DBCC": true,
  136. "NULLIF": true,
  137. "TRIGGER": true,
  138. "DEALLOCATE": true,
  139. "OF": true,
  140. "TRUNCATE": true,
  141. "DECLARE": true,
  142. "OFF": true,
  143. "TRY_CONVERT": true,
  144. "DEFAULT": true,
  145. "OFFSETS": true,
  146. "TSEQUAL": true,
  147. "DELETE": true,
  148. "ON": true,
  149. "UNION": true,
  150. "DENY": true,
  151. "OPEN": true,
  152. "UNIQUE": true,
  153. "DESC": true,
  154. "OPENDATASOURCE": true,
  155. "UNPIVOT": true,
  156. "DISK": true,
  157. "OPENQUERY": true,
  158. "UPDATE": true,
  159. "DISTINCT": true,
  160. "OPENROWSET": true,
  161. "UPDATETEXT": true,
  162. "DISTRIBUTED": true,
  163. "OPENXML": true,
  164. "USE": true,
  165. "DOUBLE": true,
  166. "OPTION": true,
  167. "USER": true,
  168. "DROP": true,
  169. "OR": true,
  170. "VALUES": true,
  171. "DUMP": true,
  172. "ORDER": true,
  173. "VARYING": true,
  174. "ELSE": true,
  175. "OUTER": true,
  176. "VIEW": true,
  177. "END": true,
  178. "OVER": true,
  179. "WAITFOR": true,
  180. "ERRLVL": true,
  181. "PERCENT": true,
  182. "WHEN": true,
  183. "ESCAPE": true,
  184. "PIVOT": true,
  185. "WHERE": true,
  186. "EXCEPT": true,
  187. "PLAN": true,
  188. "WHILE": true,
  189. "EXEC": true,
  190. "PRECISION": true,
  191. "WITH": true,
  192. "EXECUTE": true,
  193. "PRIMARY": true,
  194. "WITHIN": true,
  195. "EXISTS": true,
  196. "PRINT": true,
  197. "WRITETEXT": true,
  198. "EXIT": true,
  199. "PROC": true,
  200. }
  201. )
  202. type mssql struct {
  203. core.Base
  204. }
  205. func (db *mssql) Init(d *core.DB, uri *core.Uri, drivername, dataSourceName string) error {
  206. return db.Base.Init(d, db, uri, drivername, dataSourceName)
  207. }
  208. func (db *mssql) SqlType(c *core.Column) string {
  209. var res string
  210. switch t := c.SQLType.Name; t {
  211. case core.Bool:
  212. res = core.Bit
  213. if strings.EqualFold(c.Default, "true") {
  214. c.Default = "1"
  215. } else if strings.EqualFold(c.Default, "false") {
  216. c.Default = "0"
  217. }
  218. case core.Serial:
  219. c.IsAutoIncrement = true
  220. c.IsPrimaryKey = true
  221. c.Nullable = false
  222. res = core.Int
  223. case core.BigSerial:
  224. c.IsAutoIncrement = true
  225. c.IsPrimaryKey = true
  226. c.Nullable = false
  227. res = core.BigInt
  228. case core.Bytea, core.Blob, core.Binary, core.TinyBlob, core.MediumBlob, core.LongBlob:
  229. res = core.VarBinary
  230. if c.Length == 0 {
  231. c.Length = 50
  232. }
  233. case core.TimeStamp:
  234. res = core.DateTime
  235. case core.TimeStampz:
  236. res = "DATETIMEOFFSET"
  237. c.Length = 7
  238. case core.MediumInt:
  239. res = core.Int
  240. case core.Text, core.MediumText, core.TinyText, core.LongText, core.Json:
  241. res = core.Varchar + "(MAX)"
  242. case core.Double:
  243. res = core.Real
  244. case core.Uuid:
  245. res = core.Varchar
  246. c.Length = 40
  247. case core.TinyInt:
  248. res = core.TinyInt
  249. c.Length = 0
  250. case core.BigInt:
  251. res = core.BigInt
  252. c.Length = 0
  253. default:
  254. res = t
  255. }
  256. if res == core.Int {
  257. return core.Int
  258. }
  259. hasLen1 := (c.Length > 0)
  260. hasLen2 := (c.Length2 > 0)
  261. if hasLen2 {
  262. res += "(" + strconv.Itoa(c.Length) + "," + strconv.Itoa(c.Length2) + ")"
  263. } else if hasLen1 {
  264. res += "(" + strconv.Itoa(c.Length) + ")"
  265. }
  266. return res
  267. }
  268. func (db *mssql) SupportInsertMany() bool {
  269. return true
  270. }
  271. func (db *mssql) IsReserved(name string) bool {
  272. _, ok := mssqlReservedWords[name]
  273. return ok
  274. }
  275. func (db *mssql) Quote(name string) string {
  276. return "\"" + name + "\""
  277. }
  278. func (db *mssql) SupportEngine() bool {
  279. return false
  280. }
  281. func (db *mssql) AutoIncrStr() string {
  282. return "IDENTITY"
  283. }
  284. func (db *mssql) DropTableSql(tableName string) string {
  285. return fmt.Sprintf("IF EXISTS (SELECT * FROM sysobjects WHERE id = "+
  286. "object_id(N'%s') and OBJECTPROPERTY(id, N'IsUserTable') = 1) "+
  287. "DROP TABLE \"%s\"", tableName, tableName)
  288. }
  289. func (db *mssql) SupportCharset() bool {
  290. return false
  291. }
  292. func (db *mssql) IndexOnTable() bool {
  293. return true
  294. }
  295. func (db *mssql) IndexCheckSql(tableName, idxName string) (string, []interface{}) {
  296. args := []interface{}{idxName}
  297. sql := "select name from sysindexes where id=object_id('" + tableName + "') and name=?"
  298. return sql, args
  299. }
  300. /*func (db *mssql) ColumnCheckSql(tableName, colName string) (string, []interface{}) {
  301. args := []interface{}{tableName, colName}
  302. sql := `SELECT "COLUMN_NAME" FROM "INFORMATION_SCHEMA"."COLUMNS" WHERE "TABLE_NAME" = ? AND "COLUMN_NAME" = ?`
  303. return sql, args
  304. }*/
  305. func (db *mssql) IsColumnExist(tableName, colName string) (bool, error) {
  306. query := `SELECT "COLUMN_NAME" FROM "INFORMATION_SCHEMA"."COLUMNS" WHERE "TABLE_NAME" = ? AND "COLUMN_NAME" = ?`
  307. return db.HasRecords(query, tableName, colName)
  308. }
  309. func (db *mssql) TableCheckSql(tableName string) (string, []interface{}) {
  310. args := []interface{}{}
  311. sql := "select * from sysobjects where id = object_id(N'" + tableName + "') and OBJECTPROPERTY(id, N'IsUserTable') = 1"
  312. return sql, args
  313. }
  314. func (db *mssql) GetColumns(tableName string) ([]string, map[string]*core.Column, error) {
  315. args := []interface{}{}
  316. s := `select a.name as name, b.name as ctype,a.max_length,a.precision,a.scale,a.is_nullable as nullable,
  317. "default_is_null" = (CASE WHEN c.text is null THEN 1 ELSE 0 END),
  318. replace(replace(isnull(c.text,''),'(',''),')','') as vdefault,
  319. ISNULL(i.is_primary_key, 0), a.is_identity as is_identity
  320. from sys.columns a
  321. left join sys.types b on a.user_type_id=b.user_type_id
  322. left join sys.syscomments c on a.default_object_id=c.id
  323. LEFT OUTER JOIN
  324. sys.index_columns ic ON ic.object_id = a.object_id AND ic.column_id = a.column_id
  325. LEFT OUTER JOIN
  326. sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
  327. where a.object_id=object_id('` + tableName + `')`
  328. db.LogSQL(s, args)
  329. rows, err := db.DB().Query(s, args...)
  330. if err != nil {
  331. return nil, nil, err
  332. }
  333. defer rows.Close()
  334. cols := make(map[string]*core.Column)
  335. colSeq := make([]string, 0)
  336. for rows.Next() {
  337. var name, ctype, vdefault string
  338. var maxLen, precision, scale int
  339. var nullable, isPK, defaultIsNull, isIncrement bool
  340. err = rows.Scan(&name, &ctype, &maxLen, &precision, &scale, &nullable, &defaultIsNull, &vdefault, &isPK, &isIncrement)
  341. if err != nil {
  342. return nil, nil, err
  343. }
  344. col := new(core.Column)
  345. col.Indexes = make(map[string]int)
  346. col.Name = strings.Trim(name, "` ")
  347. col.Nullable = nullable
  348. col.DefaultIsEmpty = defaultIsNull
  349. if !defaultIsNull {
  350. col.Default = vdefault
  351. }
  352. col.IsPrimaryKey = isPK
  353. col.IsAutoIncrement = isIncrement
  354. ct := strings.ToUpper(ctype)
  355. if ct == "DECIMAL" {
  356. col.Length = precision
  357. col.Length2 = scale
  358. } else {
  359. col.Length = maxLen
  360. }
  361. switch ct {
  362. case "DATETIMEOFFSET":
  363. col.SQLType = core.SQLType{Name: core.TimeStampz, DefaultLength: 0, DefaultLength2: 0}
  364. case "NVARCHAR":
  365. col.SQLType = core.SQLType{Name: core.NVarchar, DefaultLength: 0, DefaultLength2: 0}
  366. case "IMAGE":
  367. col.SQLType = core.SQLType{Name: core.VarBinary, DefaultLength: 0, DefaultLength2: 0}
  368. default:
  369. if _, ok := core.SqlTypes[ct]; ok {
  370. col.SQLType = core.SQLType{Name: ct, DefaultLength: 0, DefaultLength2: 0}
  371. } else {
  372. return nil, nil, fmt.Errorf("Unknown colType %v for %v - %v", ct, tableName, col.Name)
  373. }
  374. }
  375. cols[col.Name] = col
  376. colSeq = append(colSeq, col.Name)
  377. }
  378. return colSeq, cols, nil
  379. }
  380. func (db *mssql) GetTables() ([]*core.Table, error) {
  381. args := []interface{}{}
  382. s := `select name from sysobjects where xtype ='U'`
  383. db.LogSQL(s, args)
  384. rows, err := db.DB().Query(s, args...)
  385. if err != nil {
  386. return nil, err
  387. }
  388. defer rows.Close()
  389. tables := make([]*core.Table, 0)
  390. for rows.Next() {
  391. table := core.NewEmptyTable()
  392. var name string
  393. err = rows.Scan(&name)
  394. if err != nil {
  395. return nil, err
  396. }
  397. table.Name = strings.Trim(name, "` ")
  398. tables = append(tables, table)
  399. }
  400. return tables, nil
  401. }
  402. func (db *mssql) GetIndexes(tableName string) (map[string]*core.Index, error) {
  403. args := []interface{}{tableName}
  404. s := `SELECT
  405. IXS.NAME AS [INDEX_NAME],
  406. C.NAME AS [COLUMN_NAME],
  407. IXS.is_unique AS [IS_UNIQUE]
  408. FROM SYS.INDEXES IXS
  409. INNER JOIN SYS.INDEX_COLUMNS IXCS
  410. ON IXS.OBJECT_ID=IXCS.OBJECT_ID AND IXS.INDEX_ID = IXCS.INDEX_ID
  411. INNER JOIN SYS.COLUMNS C ON IXS.OBJECT_ID=C.OBJECT_ID
  412. AND IXCS.COLUMN_ID=C.COLUMN_ID
  413. WHERE IXS.TYPE_DESC='NONCLUSTERED' and OBJECT_NAME(IXS.OBJECT_ID) =?
  414. `
  415. db.LogSQL(s, args)
  416. rows, err := db.DB().Query(s, args...)
  417. if err != nil {
  418. return nil, err
  419. }
  420. defer rows.Close()
  421. indexes := make(map[string]*core.Index, 0)
  422. for rows.Next() {
  423. var indexType int
  424. var indexName, colName, isUnique string
  425. err = rows.Scan(&indexName, &colName, &isUnique)
  426. if err != nil {
  427. return nil, err
  428. }
  429. i, err := strconv.ParseBool(isUnique)
  430. if err != nil {
  431. return nil, err
  432. }
  433. if i {
  434. indexType = core.UniqueType
  435. } else {
  436. indexType = core.IndexType
  437. }
  438. colName = strings.Trim(colName, "` ")
  439. var isRegular bool
  440. if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
  441. indexName = indexName[5+len(tableName):]
  442. isRegular = true
  443. }
  444. var index *core.Index
  445. var ok bool
  446. if index, ok = indexes[indexName]; !ok {
  447. index = new(core.Index)
  448. index.Type = indexType
  449. index.Name = indexName
  450. index.IsRegular = isRegular
  451. indexes[indexName] = index
  452. }
  453. index.AddColumn(colName)
  454. }
  455. return indexes, nil
  456. }
  457. func (db *mssql) CreateTableSql(table *core.Table, tableName, storeEngine, charset string) string {
  458. var sql string
  459. if tableName == "" {
  460. tableName = table.Name
  461. }
  462. sql = "IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = '" + tableName + "' ) CREATE TABLE "
  463. sql += db.Quote(tableName) + " ("
  464. pkList := table.PrimaryKeys
  465. for _, colName := range table.ColumnsSeq() {
  466. col := table.GetColumn(colName)
  467. if col.IsPrimaryKey && len(pkList) == 1 {
  468. sql += col.String(db)
  469. } else {
  470. sql += col.StringNoPk(db)
  471. }
  472. sql = strings.TrimSpace(sql)
  473. sql += ", "
  474. }
  475. if len(pkList) > 1 {
  476. sql += "PRIMARY KEY ( "
  477. sql += strings.Join(pkList, ",")
  478. sql += " ), "
  479. }
  480. sql = sql[:len(sql)-2] + ")"
  481. sql += ";"
  482. return sql
  483. }
  484. func (db *mssql) ForUpdateSql(query string) string {
  485. return query
  486. }
  487. func (db *mssql) Filters() []core.Filter {
  488. return []core.Filter{&core.IdFilter{}, &core.QuoteFilter{}}
  489. }
  490. type odbcDriver struct {
  491. }
  492. func (p *odbcDriver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  493. var dbName string
  494. if strings.HasPrefix(dataSourceName, "sqlserver://") {
  495. u, err := url.Parse(dataSourceName)
  496. if err != nil {
  497. return nil, err
  498. }
  499. dbName = u.Query().Get("database")
  500. } else {
  501. kv := strings.Split(dataSourceName, ";")
  502. for _, c := range kv {
  503. vv := strings.Split(strings.TrimSpace(c), "=")
  504. if len(vv) == 2 {
  505. switch strings.ToLower(vv[0]) {
  506. case "database":
  507. dbName = vv[1]
  508. }
  509. }
  510. }
  511. }
  512. if dbName == "" {
  513. return nil, errors.New("no db name provided")
  514. }
  515. return &core.Uri{DbName: dbName, DbType: core.MSSQL}, nil
  516. }