dialect_mssql.go 15 KB

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