dialect_oracle.go 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906
  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. "regexp"
  9. "strconv"
  10. "strings"
  11. "github.com/go-xorm/core"
  12. )
  13. var (
  14. oracleReservedWords = map[string]bool{
  15. "ACCESS": true,
  16. "ACCOUNT": true,
  17. "ACTIVATE": true,
  18. "ADD": true,
  19. "ADMIN": true,
  20. "ADVISE": true,
  21. "AFTER": true,
  22. "ALL": true,
  23. "ALL_ROWS": true,
  24. "ALLOCATE": true,
  25. "ALTER": true,
  26. "ANALYZE": true,
  27. "AND": true,
  28. "ANY": true,
  29. "ARCHIVE": true,
  30. "ARCHIVELOG": true,
  31. "ARRAY": true,
  32. "AS": true,
  33. "ASC": true,
  34. "AT": true,
  35. "AUDIT": true,
  36. "AUTHENTICATED": true,
  37. "AUTHORIZATION": true,
  38. "AUTOEXTEND": true,
  39. "AUTOMATIC": true,
  40. "BACKUP": true,
  41. "BECOME": true,
  42. "BEFORE": true,
  43. "BEGIN": true,
  44. "BETWEEN": true,
  45. "BFILE": true,
  46. "BITMAP": true,
  47. "BLOB": true,
  48. "BLOCK": true,
  49. "BODY": true,
  50. "BY": true,
  51. "CACHE": true,
  52. "CACHE_INSTANCES": true,
  53. "CANCEL": true,
  54. "CASCADE": true,
  55. "CAST": true,
  56. "CFILE": true,
  57. "CHAINED": true,
  58. "CHANGE": true,
  59. "CHAR": true,
  60. "CHAR_CS": true,
  61. "CHARACTER": true,
  62. "CHECK": true,
  63. "CHECKPOINT": true,
  64. "CHOOSE": true,
  65. "CHUNK": true,
  66. "CLEAR": true,
  67. "CLOB": true,
  68. "CLONE": true,
  69. "CLOSE": true,
  70. "CLOSE_CACHED_OPEN_CURSORS": true,
  71. "CLUSTER": true,
  72. "COALESCE": true,
  73. "COLUMN": true,
  74. "COLUMNS": true,
  75. "COMMENT": true,
  76. "COMMIT": true,
  77. "COMMITTED": true,
  78. "COMPATIBILITY": true,
  79. "COMPILE": true,
  80. "COMPLETE": true,
  81. "COMPOSITE_LIMIT": true,
  82. "COMPRESS": true,
  83. "COMPUTE": true,
  84. "CONNECT": true,
  85. "CONNECT_TIME": true,
  86. "CONSTRAINT": true,
  87. "CONSTRAINTS": true,
  88. "CONTENTS": true,
  89. "CONTINUE": true,
  90. "CONTROLFILE": true,
  91. "CONVERT": true,
  92. "COST": true,
  93. "CPU_PER_CALL": true,
  94. "CPU_PER_SESSION": true,
  95. "CREATE": true,
  96. "CURRENT": true,
  97. "CURRENT_SCHEMA": true,
  98. "CURREN_USER": true,
  99. "CURSOR": true,
  100. "CYCLE": true,
  101. "DANGLING": true,
  102. "DATABASE": true,
  103. "DATAFILE": true,
  104. "DATAFILES": true,
  105. "DATAOBJNO": true,
  106. "DATE": true,
  107. "DBA": true,
  108. "DBHIGH": true,
  109. "DBLOW": true,
  110. "DBMAC": true,
  111. "DEALLOCATE": true,
  112. "DEBUG": true,
  113. "DEC": true,
  114. "DECIMAL": true,
  115. "DECLARE": true,
  116. "DEFAULT": true,
  117. "DEFERRABLE": true,
  118. "DEFERRED": true,
  119. "DEGREE": true,
  120. "DELETE": true,
  121. "DEREF": true,
  122. "DESC": true,
  123. "DIRECTORY": true,
  124. "DISABLE": true,
  125. "DISCONNECT": true,
  126. "DISMOUNT": true,
  127. "DISTINCT": true,
  128. "DISTRIBUTED": true,
  129. "DML": true,
  130. "DOUBLE": true,
  131. "DROP": true,
  132. "DUMP": true,
  133. "EACH": true,
  134. "ELSE": true,
  135. "ENABLE": true,
  136. "END": true,
  137. "ENFORCE": true,
  138. "ENTRY": true,
  139. "ESCAPE": true,
  140. "EXCEPT": true,
  141. "EXCEPTIONS": true,
  142. "EXCHANGE": true,
  143. "EXCLUDING": true,
  144. "EXCLUSIVE": true,
  145. "EXECUTE": true,
  146. "EXISTS": true,
  147. "EXPIRE": true,
  148. "EXPLAIN": true,
  149. "EXTENT": true,
  150. "EXTENTS": true,
  151. "EXTERNALLY": true,
  152. "FAILED_LOGIN_ATTEMPTS": true,
  153. "FALSE": true,
  154. "FAST": true,
  155. "FILE": true,
  156. "FIRST_ROWS": true,
  157. "FLAGGER": true,
  158. "FLOAT": true,
  159. "FLOB": true,
  160. "FLUSH": true,
  161. "FOR": true,
  162. "FORCE": true,
  163. "FOREIGN": true,
  164. "FREELIST": true,
  165. "FREELISTS": true,
  166. "FROM": true,
  167. "FULL": true,
  168. "FUNCTION": true,
  169. "GLOBAL": true,
  170. "GLOBALLY": true,
  171. "GLOBAL_NAME": true,
  172. "GRANT": true,
  173. "GROUP": true,
  174. "GROUPS": true,
  175. "HASH": true,
  176. "HASHKEYS": true,
  177. "HAVING": true,
  178. "HEADER": true,
  179. "HEAP": true,
  180. "IDENTIFIED": true,
  181. "IDGENERATORS": true,
  182. "IDLE_TIME": true,
  183. "IF": true,
  184. "IMMEDIATE": true,
  185. "IN": true,
  186. "INCLUDING": true,
  187. "INCREMENT": true,
  188. "INDEX": true,
  189. "INDEXED": true,
  190. "INDEXES": true,
  191. "INDICATOR": true,
  192. "IND_PARTITION": true,
  193. "INITIAL": true,
  194. "INITIALLY": true,
  195. "INITRANS": true,
  196. "INSERT": true,
  197. "INSTANCE": true,
  198. "INSTANCES": true,
  199. "INSTEAD": true,
  200. "INT": true,
  201. "INTEGER": true,
  202. "INTERMEDIATE": true,
  203. "INTERSECT": true,
  204. "INTO": true,
  205. "IS": true,
  206. "ISOLATION": true,
  207. "ISOLATION_LEVEL": true,
  208. "KEEP": true,
  209. "KEY": true,
  210. "KILL": true,
  211. "LABEL": true,
  212. "LAYER": true,
  213. "LESS": true,
  214. "LEVEL": true,
  215. "LIBRARY": true,
  216. "LIKE": true,
  217. "LIMIT": true,
  218. "LINK": true,
  219. "LIST": true,
  220. "LOB": true,
  221. "LOCAL": true,
  222. "LOCK": true,
  223. "LOCKED": true,
  224. "LOG": true,
  225. "LOGFILE": true,
  226. "LOGGING": true,
  227. "LOGICAL_READS_PER_CALL": true,
  228. "LOGICAL_READS_PER_SESSION": true,
  229. "LONG": true,
  230. "MANAGE": true,
  231. "MASTER": true,
  232. "MAX": true,
  233. "MAXARCHLOGS": true,
  234. "MAXDATAFILES": true,
  235. "MAXEXTENTS": true,
  236. "MAXINSTANCES": true,
  237. "MAXLOGFILES": true,
  238. "MAXLOGHISTORY": true,
  239. "MAXLOGMEMBERS": true,
  240. "MAXSIZE": true,
  241. "MAXTRANS": true,
  242. "MAXVALUE": true,
  243. "MIN": true,
  244. "MEMBER": true,
  245. "MINIMUM": true,
  246. "MINEXTENTS": true,
  247. "MINUS": true,
  248. "MINVALUE": true,
  249. "MLSLABEL": true,
  250. "MLS_LABEL_FORMAT": true,
  251. "MODE": true,
  252. "MODIFY": true,
  253. "MOUNT": true,
  254. "MOVE": true,
  255. "MTS_DISPATCHERS": true,
  256. "MULTISET": true,
  257. "NATIONAL": true,
  258. "NCHAR": true,
  259. "NCHAR_CS": true,
  260. "NCLOB": true,
  261. "NEEDED": true,
  262. "NESTED": true,
  263. "NETWORK": true,
  264. "NEW": true,
  265. "NEXT": true,
  266. "NOARCHIVELOG": true,
  267. "NOAUDIT": true,
  268. "NOCACHE": true,
  269. "NOCOMPRESS": true,
  270. "NOCYCLE": true,
  271. "NOFORCE": true,
  272. "NOLOGGING": true,
  273. "NOMAXVALUE": true,
  274. "NOMINVALUE": true,
  275. "NONE": true,
  276. "NOORDER": true,
  277. "NOOVERRIDE": true,
  278. "NOPARALLEL": true,
  279. "NOREVERSE": true,
  280. "NORMAL": true,
  281. "NOSORT": true,
  282. "NOT": true,
  283. "NOTHING": true,
  284. "NOWAIT": true,
  285. "NULL": true,
  286. "NUMBER": true,
  287. "NUMERIC": true,
  288. "NVARCHAR2": true,
  289. "OBJECT": true,
  290. "OBJNO": true,
  291. "OBJNO_REUSE": true,
  292. "OF": true,
  293. "OFF": true,
  294. "OFFLINE": true,
  295. "OID": true,
  296. "OIDINDEX": true,
  297. "OLD": true,
  298. "ON": true,
  299. "ONLINE": true,
  300. "ONLY": true,
  301. "OPCODE": true,
  302. "OPEN": true,
  303. "OPTIMAL": true,
  304. "OPTIMIZER_GOAL": true,
  305. "OPTION": true,
  306. "OR": true,
  307. "ORDER": true,
  308. "ORGANIZATION": true,
  309. "OSLABEL": true,
  310. "OVERFLOW": true,
  311. "OWN": true,
  312. "PACKAGE": true,
  313. "PARALLEL": true,
  314. "PARTITION": true,
  315. "PASSWORD": true,
  316. "PASSWORD_GRACE_TIME": true,
  317. "PASSWORD_LIFE_TIME": true,
  318. "PASSWORD_LOCK_TIME": true,
  319. "PASSWORD_REUSE_MAX": true,
  320. "PASSWORD_REUSE_TIME": true,
  321. "PASSWORD_VERIFY_FUNCTION": true,
  322. "PCTFREE": true,
  323. "PCTINCREASE": true,
  324. "PCTTHRESHOLD": true,
  325. "PCTUSED": true,
  326. "PCTVERSION": true,
  327. "PERCENT": true,
  328. "PERMANENT": true,
  329. "PLAN": true,
  330. "PLSQL_DEBUG": true,
  331. "POST_TRANSACTION": true,
  332. "PRECISION": true,
  333. "PRESERVE": true,
  334. "PRIMARY": true,
  335. "PRIOR": true,
  336. "PRIVATE": true,
  337. "PRIVATE_SGA": true,
  338. "PRIVILEGE": true,
  339. "PRIVILEGES": true,
  340. "PROCEDURE": true,
  341. "PROFILE": true,
  342. "PUBLIC": true,
  343. "PURGE": true,
  344. "QUEUE": true,
  345. "QUOTA": true,
  346. "RANGE": true,
  347. "RAW": true,
  348. "RBA": true,
  349. "READ": true,
  350. "READUP": true,
  351. "REAL": true,
  352. "REBUILD": true,
  353. "RECOVER": true,
  354. "RECOVERABLE": true,
  355. "RECOVERY": true,
  356. "REF": true,
  357. "REFERENCES": true,
  358. "REFERENCING": true,
  359. "REFRESH": true,
  360. "RENAME": true,
  361. "REPLACE": true,
  362. "RESET": true,
  363. "RESETLOGS": true,
  364. "RESIZE": true,
  365. "RESOURCE": true,
  366. "RESTRICTED": true,
  367. "RETURN": true,
  368. "RETURNING": true,
  369. "REUSE": true,
  370. "REVERSE": true,
  371. "REVOKE": true,
  372. "ROLE": true,
  373. "ROLES": true,
  374. "ROLLBACK": true,
  375. "ROW": true,
  376. "ROWID": true,
  377. "ROWNUM": true,
  378. "ROWS": true,
  379. "RULE": true,
  380. "SAMPLE": true,
  381. "SAVEPOINT": true,
  382. "SB4": true,
  383. "SCAN_INSTANCES": true,
  384. "SCHEMA": true,
  385. "SCN": true,
  386. "SCOPE": true,
  387. "SD_ALL": true,
  388. "SD_INHIBIT": true,
  389. "SD_SHOW": true,
  390. "SEGMENT": true,
  391. "SEG_BLOCK": true,
  392. "SEG_FILE": true,
  393. "SELECT": true,
  394. "SEQUENCE": true,
  395. "SERIALIZABLE": true,
  396. "SESSION": true,
  397. "SESSION_CACHED_CURSORS": true,
  398. "SESSIONS_PER_USER": true,
  399. "SET": true,
  400. "SHARE": true,
  401. "SHARED": true,
  402. "SHARED_POOL": true,
  403. "SHRINK": true,
  404. "SIZE": true,
  405. "SKIP": true,
  406. "SKIP_UNUSABLE_INDEXES": true,
  407. "SMALLINT": true,
  408. "SNAPSHOT": true,
  409. "SOME": true,
  410. "SORT": true,
  411. "SPECIFICATION": true,
  412. "SPLIT": true,
  413. "SQL_TRACE": true,
  414. "STANDBY": true,
  415. "START": true,
  416. "STATEMENT_ID": true,
  417. "STATISTICS": true,
  418. "STOP": true,
  419. "STORAGE": true,
  420. "STORE": true,
  421. "STRUCTURE": true,
  422. "SUCCESSFUL": true,
  423. "SWITCH": true,
  424. "SYS_OP_ENFORCE_NOT_NULL$": true,
  425. "SYS_OP_NTCIMG$": true,
  426. "SYNONYM": true,
  427. "SYSDATE": true,
  428. "SYSDBA": true,
  429. "SYSOPER": true,
  430. "SYSTEM": true,
  431. "TABLE": true,
  432. "TABLES": true,
  433. "TABLESPACE": true,
  434. "TABLESPACE_NO": true,
  435. "TABNO": true,
  436. "TEMPORARY": true,
  437. "THAN": true,
  438. "THE": true,
  439. "THEN": true,
  440. "THREAD": true,
  441. "TIMESTAMP": true,
  442. "TIME": true,
  443. "TO": true,
  444. "TOPLEVEL": true,
  445. "TRACE": true,
  446. "TRACING": true,
  447. "TRANSACTION": true,
  448. "TRANSITIONAL": true,
  449. "TRIGGER": true,
  450. "TRIGGERS": true,
  451. "TRUE": true,
  452. "TRUNCATE": true,
  453. "TX": true,
  454. "TYPE": true,
  455. "UB2": true,
  456. "UBA": true,
  457. "UID": true,
  458. "UNARCHIVED": true,
  459. "UNDO": true,
  460. "UNION": true,
  461. "UNIQUE": true,
  462. "UNLIMITED": true,
  463. "UNLOCK": true,
  464. "UNRECOVERABLE": true,
  465. "UNTIL": true,
  466. "UNUSABLE": true,
  467. "UNUSED": true,
  468. "UPDATABLE": true,
  469. "UPDATE": true,
  470. "USAGE": true,
  471. "USE": true,
  472. "USER": true,
  473. "USING": true,
  474. "VALIDATE": true,
  475. "VALIDATION": true,
  476. "VALUE": true,
  477. "VALUES": true,
  478. "VARCHAR": true,
  479. "VARCHAR2": true,
  480. "VARYING": true,
  481. "VIEW": true,
  482. "WHEN": true,
  483. "WHENEVER": true,
  484. "WHERE": true,
  485. "WITH": true,
  486. "WITHOUT": true,
  487. "WORK": true,
  488. "WRITE": true,
  489. "WRITEDOWN": true,
  490. "WRITEUP": true,
  491. "XID": true,
  492. "YEAR": true,
  493. "ZONE": true,
  494. }
  495. )
  496. type oracle struct {
  497. core.Base
  498. }
  499. func (db *oracle) Init(d *core.DB, uri *core.Uri, drivername, dataSourceName string) error {
  500. return db.Base.Init(d, db, uri, drivername, dataSourceName)
  501. }
  502. func (db *oracle) SqlType(c *core.Column) string {
  503. var res string
  504. switch t := c.SQLType.Name; t {
  505. case core.Bit, core.TinyInt, core.SmallInt, core.MediumInt, core.Int, core.Integer, core.BigInt, core.Bool, core.Serial, core.BigSerial:
  506. res = "NUMBER"
  507. case core.Binary, core.VarBinary, core.Blob, core.TinyBlob, core.MediumBlob, core.LongBlob, core.Bytea:
  508. return core.Blob
  509. case core.Time, core.DateTime, core.TimeStamp:
  510. res = core.TimeStamp
  511. case core.TimeStampz:
  512. res = "TIMESTAMP WITH TIME ZONE"
  513. case core.Float, core.Double, core.Numeric, core.Decimal:
  514. res = "NUMBER"
  515. case core.Text, core.MediumText, core.LongText, core.Json:
  516. res = "CLOB"
  517. case core.Char, core.Varchar, core.TinyText:
  518. res = "VARCHAR2"
  519. default:
  520. res = t
  521. }
  522. hasLen1 := (c.Length > 0)
  523. hasLen2 := (c.Length2 > 0)
  524. if hasLen2 {
  525. res += "(" + strconv.Itoa(c.Length) + "," + strconv.Itoa(c.Length2) + ")"
  526. } else if hasLen1 {
  527. res += "(" + strconv.Itoa(c.Length) + ")"
  528. }
  529. return res
  530. }
  531. func (db *oracle) AutoIncrStr() string {
  532. return "AUTO_INCREMENT"
  533. }
  534. func (db *oracle) SupportInsertMany() bool {
  535. return true
  536. }
  537. func (db *oracle) IsReserved(name string) bool {
  538. _, ok := oracleReservedWords[name]
  539. return ok
  540. }
  541. func (db *oracle) Quote(name string) string {
  542. return "\"" + name + "\""
  543. }
  544. func (db *oracle) QuoteStr() string {
  545. return "\""
  546. }
  547. func (db *oracle) SupportEngine() bool {
  548. return false
  549. }
  550. func (db *oracle) SupportCharset() bool {
  551. return false
  552. }
  553. func (db *oracle) SupportDropIfExists() bool {
  554. return false
  555. }
  556. func (db *oracle) IndexOnTable() bool {
  557. return false
  558. }
  559. func (db *oracle) DropTableSql(tableName string) string {
  560. return fmt.Sprintf("DROP TABLE `%s`", tableName)
  561. }
  562. func (db *oracle) CreateTableSql(table *core.Table, tableName, storeEngine, charset string) string {
  563. var sql string
  564. sql = "CREATE TABLE "
  565. if tableName == "" {
  566. tableName = table.Name
  567. }
  568. sql += db.Quote(tableName) + " ("
  569. pkList := table.PrimaryKeys
  570. for _, colName := range table.ColumnsSeq() {
  571. col := table.GetColumn(colName)
  572. /*if col.IsPrimaryKey && len(pkList) == 1 {
  573. sql += col.String(b.dialect)
  574. } else {*/
  575. sql += col.StringNoPk(db)
  576. //}
  577. sql = strings.TrimSpace(sql)
  578. sql += ", "
  579. }
  580. if len(pkList) > 0 {
  581. sql += "PRIMARY KEY ( "
  582. sql += db.Quote(strings.Join(pkList, db.Quote(",")))
  583. sql += " ), "
  584. }
  585. sql = sql[:len(sql)-2] + ")"
  586. if db.SupportEngine() && storeEngine != "" {
  587. sql += " ENGINE=" + storeEngine
  588. }
  589. if db.SupportCharset() {
  590. if len(charset) == 0 {
  591. charset = db.URI().Charset
  592. }
  593. if len(charset) > 0 {
  594. sql += " DEFAULT CHARSET " + charset
  595. }
  596. }
  597. return sql
  598. }
  599. func (db *oracle) IndexCheckSql(tableName, idxName string) (string, []interface{}) {
  600. args := []interface{}{tableName, idxName}
  601. return `SELECT INDEX_NAME FROM USER_INDEXES ` +
  602. `WHERE TABLE_NAME = :1 AND INDEX_NAME = :2`, args
  603. }
  604. func (db *oracle) TableCheckSql(tableName string) (string, []interface{}) {
  605. args := []interface{}{tableName}
  606. return `SELECT table_name FROM user_tables WHERE table_name = :1`, args
  607. }
  608. func (db *oracle) MustDropTable(tableName string) error {
  609. sql, args := db.TableCheckSql(tableName)
  610. db.LogSQL(sql, args)
  611. rows, err := db.DB().Query(sql, args...)
  612. if err != nil {
  613. return err
  614. }
  615. defer rows.Close()
  616. if !rows.Next() {
  617. return nil
  618. }
  619. sql = "Drop Table \"" + tableName + "\""
  620. db.LogSQL(sql, args)
  621. _, err = db.DB().Exec(sql)
  622. return err
  623. }
  624. /*func (db *oracle) ColumnCheckSql(tableName, colName string) (string, []interface{}) {
  625. args := []interface{}{strings.ToUpper(tableName), strings.ToUpper(colName)}
  626. return "SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = ?" +
  627. " AND column_name = ?", args
  628. }*/
  629. func (db *oracle) IsColumnExist(tableName, colName string) (bool, error) {
  630. args := []interface{}{tableName, colName}
  631. query := "SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = :1" +
  632. " AND column_name = :2"
  633. db.LogSQL(query, args)
  634. rows, err := db.DB().Query(query, args...)
  635. if err != nil {
  636. return false, err
  637. }
  638. defer rows.Close()
  639. if rows.Next() {
  640. return true, nil
  641. }
  642. return false, nil
  643. }
  644. func (db *oracle) GetColumns(tableName string) ([]string, map[string]*core.Column, error) {
  645. args := []interface{}{tableName}
  646. s := "SELECT column_name,data_default,data_type,data_length,data_precision,data_scale," +
  647. "nullable FROM USER_TAB_COLUMNS WHERE table_name = :1"
  648. db.LogSQL(s, args)
  649. rows, err := db.DB().Query(s, args...)
  650. if err != nil {
  651. return nil, nil, err
  652. }
  653. defer rows.Close()
  654. cols := make(map[string]*core.Column)
  655. colSeq := make([]string, 0)
  656. for rows.Next() {
  657. col := new(core.Column)
  658. col.Indexes = make(map[string]int)
  659. var colName, colDefault, nullable, dataType, dataPrecision, dataScale *string
  660. var dataLen int
  661. err = rows.Scan(&colName, &colDefault, &dataType, &dataLen, &dataPrecision,
  662. &dataScale, &nullable)
  663. if err != nil {
  664. return nil, nil, err
  665. }
  666. col.Name = strings.Trim(*colName, `" `)
  667. if colDefault != nil {
  668. col.Default = *colDefault
  669. col.DefaultIsEmpty = false
  670. }
  671. if *nullable == "Y" {
  672. col.Nullable = true
  673. } else {
  674. col.Nullable = false
  675. }
  676. var ignore bool
  677. var dt string
  678. var len1, len2 int
  679. dts := strings.Split(*dataType, "(")
  680. dt = dts[0]
  681. if len(dts) > 1 {
  682. lens := strings.Split(dts[1][:len(dts[1])-1], ",")
  683. if len(lens) > 1 {
  684. len1, _ = strconv.Atoi(lens[0])
  685. len2, _ = strconv.Atoi(lens[1])
  686. } else {
  687. len1, _ = strconv.Atoi(lens[0])
  688. }
  689. }
  690. switch dt {
  691. case "VARCHAR2":
  692. col.SQLType = core.SQLType{Name: core.Varchar, DefaultLength: len1, DefaultLength2: len2}
  693. case "NVARCHAR2":
  694. col.SQLType = core.SQLType{Name: core.NVarchar, DefaultLength: len1, DefaultLength2: len2}
  695. case "TIMESTAMP WITH TIME ZONE":
  696. col.SQLType = core.SQLType{Name: core.TimeStampz, DefaultLength: 0, DefaultLength2: 0}
  697. case "NUMBER":
  698. col.SQLType = core.SQLType{Name: core.Double, DefaultLength: len1, DefaultLength2: len2}
  699. case "LONG", "LONG RAW":
  700. col.SQLType = core.SQLType{Name: core.Text, DefaultLength: 0, DefaultLength2: 0}
  701. case "RAW":
  702. col.SQLType = core.SQLType{Name: core.Binary, DefaultLength: 0, DefaultLength2: 0}
  703. case "ROWID":
  704. col.SQLType = core.SQLType{Name: core.Varchar, DefaultLength: 18, DefaultLength2: 0}
  705. case "AQ$_SUBSCRIBERS":
  706. ignore = true
  707. default:
  708. col.SQLType = core.SQLType{Name: strings.ToUpper(dt), DefaultLength: len1, DefaultLength2: len2}
  709. }
  710. if ignore {
  711. continue
  712. }
  713. if _, ok := core.SqlTypes[col.SQLType.Name]; !ok {
  714. return nil, nil, fmt.Errorf("Unknown colType %v %v", *dataType, col.SQLType)
  715. }
  716. col.Length = dataLen
  717. if col.SQLType.IsText() || col.SQLType.IsTime() {
  718. if !col.DefaultIsEmpty {
  719. col.Default = "'" + col.Default + "'"
  720. }
  721. }
  722. cols[col.Name] = col
  723. colSeq = append(colSeq, col.Name)
  724. }
  725. return colSeq, cols, nil
  726. }
  727. func (db *oracle) GetTables() ([]*core.Table, error) {
  728. args := []interface{}{}
  729. s := "SELECT table_name FROM user_tables"
  730. db.LogSQL(s, args)
  731. rows, err := db.DB().Query(s, args...)
  732. if err != nil {
  733. return nil, err
  734. }
  735. defer rows.Close()
  736. tables := make([]*core.Table, 0)
  737. for rows.Next() {
  738. table := core.NewEmptyTable()
  739. err = rows.Scan(&table.Name)
  740. if err != nil {
  741. return nil, err
  742. }
  743. tables = append(tables, table)
  744. }
  745. return tables, nil
  746. }
  747. func (db *oracle) GetIndexes(tableName string) (map[string]*core.Index, error) {
  748. args := []interface{}{tableName}
  749. s := "SELECT t.column_name,i.uniqueness,i.index_name FROM user_ind_columns t,user_indexes i " +
  750. "WHERE t.index_name = i.index_name and t.table_name = i.table_name and t.table_name =:1"
  751. db.LogSQL(s, args)
  752. rows, err := db.DB().Query(s, args...)
  753. if err != nil {
  754. return nil, err
  755. }
  756. defer rows.Close()
  757. indexes := make(map[string]*core.Index, 0)
  758. for rows.Next() {
  759. var indexType int
  760. var indexName, colName, uniqueness string
  761. err = rows.Scan(&colName, &uniqueness, &indexName)
  762. if err != nil {
  763. return nil, err
  764. }
  765. indexName = strings.Trim(indexName, `" `)
  766. var isRegular bool
  767. if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
  768. indexName = indexName[5+len(tableName):]
  769. isRegular = true
  770. }
  771. if uniqueness == "UNIQUE" {
  772. indexType = core.UniqueType
  773. } else {
  774. indexType = core.IndexType
  775. }
  776. var index *core.Index
  777. var ok bool
  778. if index, ok = indexes[indexName]; !ok {
  779. index = new(core.Index)
  780. index.Type = indexType
  781. index.Name = indexName
  782. index.IsRegular = isRegular
  783. indexes[indexName] = index
  784. }
  785. index.AddColumn(colName)
  786. }
  787. return indexes, nil
  788. }
  789. func (db *oracle) Filters() []core.Filter {
  790. return []core.Filter{&core.QuoteFilter{}, &core.SeqFilter{Prefix: ":", Start: 1}, &core.IdFilter{}}
  791. }
  792. type goracleDriver struct {
  793. }
  794. func (cfg *goracleDriver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  795. db := &core.Uri{DbType: core.ORACLE}
  796. dsnPattern := regexp.MustCompile(
  797. `^(?:(?P<user>.*?)(?::(?P<passwd>.*))?@)?` + // [user[:password]@]
  798. `(?:(?P<net>[^\(]*)(?:\((?P<addr>[^\)]*)\))?)?` + // [net[(addr)]]
  799. `\/(?P<dbname>.*?)` + // /dbname
  800. `(?:\?(?P<params>[^\?]*))?$`) // [?param1=value1&paramN=valueN]
  801. matches := dsnPattern.FindStringSubmatch(dataSourceName)
  802. //tlsConfigRegister := make(map[string]*tls.Config)
  803. names := dsnPattern.SubexpNames()
  804. for i, match := range matches {
  805. switch names[i] {
  806. case "dbname":
  807. db.DbName = match
  808. }
  809. }
  810. if db.DbName == "" {
  811. return nil, errors.New("dbname is empty")
  812. }
  813. return db, nil
  814. }
  815. type oci8Driver struct {
  816. }
  817. //dataSourceName=user/password@ipv4:port/dbname
  818. //dataSourceName=user/password@[ipv6]:port/dbname
  819. func (p *oci8Driver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  820. db := &core.Uri{DbType: core.ORACLE}
  821. dsnPattern := regexp.MustCompile(
  822. `^(?P<user>.*)\/(?P<password>.*)@` + // user:password@
  823. `(?P<net>.*)` + // ip:port
  824. `\/(?P<dbname>.*)`) // dbname
  825. matches := dsnPattern.FindStringSubmatch(dataSourceName)
  826. names := dsnPattern.SubexpNames()
  827. for i, match := range matches {
  828. switch names[i] {
  829. case "dbname":
  830. db.DbName = match
  831. }
  832. }
  833. if db.DbName == "" {
  834. return nil, errors.New("dbname is empty")
  835. }
  836. return db, nil
  837. }