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.

1254 lines
45KB

  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. // from http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
  14. var (
  15. postgresReservedWords = map[string]bool{
  16. "A": true,
  17. "ABORT": true,
  18. "ABS": true,
  19. "ABSENT": true,
  20. "ABSOLUTE": true,
  21. "ACCESS": true,
  22. "ACCORDING": true,
  23. "ACTION": true,
  24. "ADA": true,
  25. "ADD": true,
  26. "ADMIN": true,
  27. "AFTER": true,
  28. "AGGREGATE": true,
  29. "ALL": true,
  30. "ALLOCATE": true,
  31. "ALSO": true,
  32. "ALTER": true,
  33. "ALWAYS": true,
  34. "ANALYSE": true,
  35. "ANALYZE": true,
  36. "AND": true,
  37. "ANY": true,
  38. "ARE": true,
  39. "ARRAY": true,
  40. "ARRAY_AGG": true,
  41. "ARRAY_MAX_CARDINALITY": true,
  42. "AS": true,
  43. "ASC": true,
  44. "ASENSITIVE": true,
  45. "ASSERTION": true,
  46. "ASSIGNMENT": true,
  47. "ASYMMETRIC": true,
  48. "AT": true,
  49. "ATOMIC": true,
  50. "ATTRIBUTE": true,
  51. "ATTRIBUTES": true,
  52. "AUTHORIZATION": true,
  53. "AVG": true,
  54. "BACKWARD": true,
  55. "BASE64": true,
  56. "BEFORE": true,
  57. "BEGIN": true,
  58. "BEGIN_FRAME": true,
  59. "BEGIN_PARTITION": true,
  60. "BERNOULLI": true,
  61. "BETWEEN": true,
  62. "BIGINT": true,
  63. "BINARY": true,
  64. "BIT": true,
  65. "BIT_LENGTH": true,
  66. "BLOB": true,
  67. "BLOCKED": true,
  68. "BOM": true,
  69. "BOOLEAN": true,
  70. "BOTH": true,
  71. "BREADTH": true,
  72. "BY": true,
  73. "C": true,
  74. "CACHE": true,
  75. "CALL": true,
  76. "CALLED": true,
  77. "CARDINALITY": true,
  78. "CASCADE": true,
  79. "CASCADED": true,
  80. "CASE": true,
  81. "CAST": true,
  82. "CATALOG": true,
  83. "CATALOG_NAME": true,
  84. "CEIL": true,
  85. "CEILING": true,
  86. "CHAIN": true,
  87. "CHAR": true,
  88. "CHARACTER": true,
  89. "CHARACTERISTICS": true,
  90. "CHARACTERS": true,
  91. "CHARACTER_LENGTH": true,
  92. "CHARACTER_SET_CATALOG": true,
  93. "CHARACTER_SET_NAME": true,
  94. "CHARACTER_SET_SCHEMA": true,
  95. "CHAR_LENGTH": true,
  96. "CHECK": true,
  97. "CHECKPOINT": true,
  98. "CLASS": true,
  99. "CLASS_ORIGIN": true,
  100. "CLOB": true,
  101. "CLOSE": true,
  102. "CLUSTER": true,
  103. "COALESCE": true,
  104. "COBOL": true,
  105. "COLLATE": true,
  106. "COLLATION": true,
  107. "COLLATION_CATALOG": true,
  108. "COLLATION_NAME": true,
  109. "COLLATION_SCHEMA": true,
  110. "COLLECT": true,
  111. "COLUMN": true,
  112. "COLUMNS": true,
  113. "COLUMN_NAME": true,
  114. "COMMAND_FUNCTION": true,
  115. "COMMAND_FUNCTION_CODE": true,
  116. "COMMENT": true,
  117. "COMMENTS": true,
  118. "COMMIT": true,
  119. "COMMITTED": true,
  120. "CONCURRENTLY": true,
  121. "CONDITION": true,
  122. "CONDITION_NUMBER": true,
  123. "CONFIGURATION": true,
  124. "CONNECT": true,
  125. "CONNECTION": true,
  126. "CONNECTION_NAME": true,
  127. "CONSTRAINT": true,
  128. "CONSTRAINTS": true,
  129. "CONSTRAINT_CATALOG": true,
  130. "CONSTRAINT_NAME": true,
  131. "CONSTRAINT_SCHEMA": true,
  132. "CONSTRUCTOR": true,
  133. "CONTAINS": true,
  134. "CONTENT": true,
  135. "CONTINUE": true,
  136. "CONTROL": true,
  137. "CONVERSION": true,
  138. "CONVERT": true,
  139. "COPY": true,
  140. "CORR": true,
  141. "CORRESPONDING": true,
  142. "COST": true,
  143. "COUNT": true,
  144. "COVAR_POP": true,
  145. "COVAR_SAMP": true,
  146. "CREATE": true,
  147. "CROSS": true,
  148. "CSV": true,
  149. "CUBE": true,
  150. "CUME_DIST": true,
  151. "CURRENT": true,
  152. "CURRENT_CATALOG": true,
  153. "CURRENT_DATE": true,
  154. "CURRENT_DEFAULT_TRANSFORM_GROUP": true,
  155. "CURRENT_PATH": true,
  156. "CURRENT_ROLE": true,
  157. "CURRENT_ROW": true,
  158. "CURRENT_SCHEMA": true,
  159. "CURRENT_TIME": true,
  160. "CURRENT_TIMESTAMP": true,
  161. "CURRENT_TRANSFORM_GROUP_FOR_TYPE": true,
  162. "CURRENT_USER": true,
  163. "CURSOR": true,
  164. "CURSOR_NAME": true,
  165. "CYCLE": true,
  166. "DATA": true,
  167. "DATABASE": true,
  168. "DATALINK": true,
  169. "DATE": true,
  170. "DATETIME_INTERVAL_CODE": true,
  171. "DATETIME_INTERVAL_PRECISION": true,
  172. "DAY": true,
  173. "DB": true,
  174. "DEALLOCATE": true,
  175. "DEC": true,
  176. "DECIMAL": true,
  177. "DECLARE": true,
  178. "DEFAULT": true,
  179. "DEFAULTS": true,
  180. "DEFERRABLE": true,
  181. "DEFERRED": true,
  182. "DEFINED": true,
  183. "DEFINER": true,
  184. "DEGREE": true,
  185. "DELETE": true,
  186. "DELIMITER": true,
  187. "DELIMITERS": true,
  188. "DENSE_RANK": true,
  189. "DEPTH": true,
  190. "DEREF": true,
  191. "DERIVED": true,
  192. "DESC": true,
  193. "DESCRIBE": true,
  194. "DESCRIPTOR": true,
  195. "DETERMINISTIC": true,
  196. "DIAGNOSTICS": true,
  197. "DICTIONARY": true,
  198. "DISABLE": true,
  199. "DISCARD": true,
  200. "DISCONNECT": true,
  201. "DISPATCH": true,
  202. "DISTINCT": true,
  203. "DLNEWCOPY": true,
  204. "DLPREVIOUSCOPY": true,
  205. "DLURLCOMPLETE": true,
  206. "DLURLCOMPLETEONLY": true,
  207. "DLURLCOMPLETEWRITE": true,
  208. "DLURLPATH": true,
  209. "DLURLPATHONLY": true,
  210. "DLURLPATHWRITE": true,
  211. "DLURLSCHEME": true,
  212. "DLURLSERVER": true,
  213. "DLVALUE": true,
  214. "DO": true,
  215. "DOCUMENT": true,
  216. "DOMAIN": true,
  217. "DOUBLE": true,
  218. "DROP": true,
  219. "DYNAMIC": true,
  220. "DYNAMIC_FUNCTION": true,
  221. "DYNAMIC_FUNCTION_CODE": true,
  222. "EACH": true,
  223. "ELEMENT": true,
  224. "ELSE": true,
  225. "EMPTY": true,
  226. "ENABLE": true,
  227. "ENCODING": true,
  228. "ENCRYPTED": true,
  229. "END": true,
  230. "END-EXEC": true,
  231. "END_FRAME": true,
  232. "END_PARTITION": true,
  233. "ENFORCED": true,
  234. "ENUM": true,
  235. "EQUALS": true,
  236. "ESCAPE": true,
  237. "EVENT": true,
  238. "EVERY": true,
  239. "EXCEPT": true,
  240. "EXCEPTION": true,
  241. "EXCLUDE": true,
  242. "EXCLUDING": true,
  243. "EXCLUSIVE": true,
  244. "EXEC": true,
  245. "EXECUTE": true,
  246. "EXISTS": true,
  247. "EXP": true,
  248. "EXPLAIN": true,
  249. "EXPRESSION": true,
  250. "EXTENSION": true,
  251. "EXTERNAL": true,
  252. "EXTRACT": true,
  253. "FALSE": true,
  254. "FAMILY": true,
  255. "FETCH": true,
  256. "FILE": true,
  257. "FILTER": true,
  258. "FINAL": true,
  259. "FIRST": true,
  260. "FIRST_VALUE": true,
  261. "FLAG": true,
  262. "FLOAT": true,
  263. "FLOOR": true,
  264. "FOLLOWING": true,
  265. "FOR": true,
  266. "FORCE": true,
  267. "FOREIGN": true,
  268. "FORTRAN": true,
  269. "FORWARD": true,
  270. "FOUND": true,
  271. "FRAME_ROW": true,
  272. "FREE": true,
  273. "FREEZE": true,
  274. "FROM": true,
  275. "FS": true,
  276. "FULL": true,
  277. "FUNCTION": true,
  278. "FUNCTIONS": true,
  279. "FUSION": true,
  280. "G": true,
  281. "GENERAL": true,
  282. "GENERATED": true,
  283. "GET": true,
  284. "GLOBAL": true,
  285. "GO": true,
  286. "GOTO": true,
  287. "GRANT": true,
  288. "GRANTED": true,
  289. "GREATEST": true,
  290. "GROUP": true,
  291. "GROUPING": true,
  292. "GROUPS": true,
  293. "HANDLER": true,
  294. "HAVING": true,
  295. "HEADER": true,
  296. "HEX": true,
  297. "HIERARCHY": true,
  298. "HOLD": true,
  299. "HOUR": true,
  300. "ID": true,
  301. "IDENTITY": true,
  302. "IF": true,
  303. "IGNORE": true,
  304. "ILIKE": true,
  305. "IMMEDIATE": true,
  306. "IMMEDIATELY": true,
  307. "IMMUTABLE": true,
  308. "IMPLEMENTATION": true,
  309. "IMPLICIT": true,
  310. "IMPORT": true,
  311. "IN": true,
  312. "INCLUDING": true,
  313. "INCREMENT": true,
  314. "INDENT": true,
  315. "INDEX": true,
  316. "INDEXES": true,
  317. "INDICATOR": true,
  318. "INHERIT": true,
  319. "INHERITS": true,
  320. "INITIALLY": true,
  321. "INLINE": true,
  322. "INNER": true,
  323. "INOUT": true,
  324. "INPUT": true,
  325. "INSENSITIVE": true,
  326. "INSERT": true,
  327. "INSTANCE": true,
  328. "INSTANTIABLE": true,
  329. "INSTEAD": true,
  330. "INT": true,
  331. "INTEGER": true,
  332. "INTEGRITY": true,
  333. "INTERSECT": true,
  334. "INTERSECTION": true,
  335. "INTERVAL": true,
  336. "INTO": true,
  337. "INVOKER": true,
  338. "IS": true,
  339. "ISNULL": true,
  340. "ISOLATION": true,
  341. "JOIN": true,
  342. "K": true,
  343. "KEY": true,
  344. "KEY_MEMBER": true,
  345. "KEY_TYPE": true,
  346. "LABEL": true,
  347. "LAG": true,
  348. "LANGUAGE": true,
  349. "LARGE": true,
  350. "LAST": true,
  351. "LAST_VALUE": true,
  352. "LATERAL": true,
  353. "LC_COLLATE": true,
  354. "LC_CTYPE": true,
  355. "LEAD": true,
  356. "LEADING": true,
  357. "LEAKPROOF": true,
  358. "LEAST": true,
  359. "LEFT": true,
  360. "LENGTH": true,
  361. "LEVEL": true,
  362. "LIBRARY": true,
  363. "LIKE": true,
  364. "LIKE_REGEX": true,
  365. "LIMIT": true,
  366. "LINK": true,
  367. "LISTEN": true,
  368. "LN": true,
  369. "LOAD": true,
  370. "LOCAL": true,
  371. "LOCALTIME": true,
  372. "LOCALTIMESTAMP": true,
  373. "LOCATION": true,
  374. "LOCATOR": true,
  375. "LOCK": true,
  376. "LOWER": true,
  377. "M": true,
  378. "MAP": true,
  379. "MAPPING": true,
  380. "MATCH": true,
  381. "MATCHED": true,
  382. "MATERIALIZED": true,
  383. "MAX": true,
  384. "MAXVALUE": true,
  385. "MAX_CARDINALITY": true,
  386. "MEMBER": true,
  387. "MERGE": true,
  388. "MESSAGE_LENGTH": true,
  389. "MESSAGE_OCTET_LENGTH": true,
  390. "MESSAGE_TEXT": true,
  391. "METHOD": true,
  392. "MIN": true,
  393. "MINUTE": true,
  394. "MINVALUE": true,
  395. "MOD": true,
  396. "MODE": true,
  397. "MODIFIES": true,
  398. "MODULE": true,
  399. "MONTH": true,
  400. "MORE": true,
  401. "MOVE": true,
  402. "MULTISET": true,
  403. "MUMPS": true,
  404. "NAME": true,
  405. "NAMES": true,
  406. "NAMESPACE": true,
  407. "NATIONAL": true,
  408. "NATURAL": true,
  409. "NCHAR": true,
  410. "NCLOB": true,
  411. "NESTING": true,
  412. "NEW": true,
  413. "NEXT": true,
  414. "NFC": true,
  415. "NFD": true,
  416. "NFKC": true,
  417. "NFKD": true,
  418. "NIL": true,
  419. "NO": true,
  420. "NONE": true,
  421. "NORMALIZE": true,
  422. "NORMALIZED": true,
  423. "NOT": true,
  424. "NOTHING": true,
  425. "NOTIFY": true,
  426. "NOTNULL": true,
  427. "NOWAIT": true,
  428. "NTH_VALUE": true,
  429. "NTILE": true,
  430. "NULL": true,
  431. "NULLABLE": true,
  432. "NULLIF": true,
  433. "NULLS": true,
  434. "NUMBER": true,
  435. "NUMERIC": true,
  436. "OBJECT": true,
  437. "OCCURRENCES_REGEX": true,
  438. "OCTETS": true,
  439. "OCTET_LENGTH": true,
  440. "OF": true,
  441. "OFF": true,
  442. "OFFSET": true,
  443. "OIDS": true,
  444. "OLD": true,
  445. "ON": true,
  446. "ONLY": true,
  447. "OPEN": true,
  448. "OPERATOR": true,
  449. "OPTION": true,
  450. "OPTIONS": true,
  451. "OR": true,
  452. "ORDER": true,
  453. "ORDERING": true,
  454. "ORDINALITY": true,
  455. "OTHERS": true,
  456. "OUT": true,
  457. "OUTER": true,
  458. "OUTPUT": true,
  459. "OVER": true,
  460. "OVERLAPS": true,
  461. "OVERLAY": true,
  462. "OVERRIDING": true,
  463. "OWNED": true,
  464. "OWNER": true,
  465. "P": true,
  466. "PAD": true,
  467. "PARAMETER": true,
  468. "PARAMETER_MODE": true,
  469. "PARAMETER_NAME": true,
  470. "PARAMETER_ORDINAL_POSITION": true,
  471. "PARAMETER_SPECIFIC_CATALOG": true,
  472. "PARAMETER_SPECIFIC_NAME": true,
  473. "PARAMETER_SPECIFIC_SCHEMA": true,
  474. "PARSER": true,
  475. "PARTIAL": true,
  476. "PARTITION": true,
  477. "PASCAL": true,
  478. "PASSING": true,
  479. "PASSTHROUGH": true,
  480. "PASSWORD": true,
  481. "PATH": true,
  482. "PERCENT": true,
  483. "PERCENTILE_CONT": true,
  484. "PERCENTILE_DISC": true,
  485. "PERCENT_RANK": true,
  486. "PERIOD": true,
  487. "PERMISSION": true,
  488. "PLACING": true,
  489. "PLANS": true,
  490. "PLI": true,
  491. "PORTION": true,
  492. "POSITION": true,
  493. "POSITION_REGEX": true,
  494. "POWER": true,
  495. "PRECEDES": true,
  496. "PRECEDING": true,
  497. "PRECISION": true,
  498. "PREPARE": true,
  499. "PREPARED": true,
  500. "PRESERVE": true,
  501. "PRIMARY": true,
  502. "PRIOR": true,
  503. "PRIVILEGES": true,
  504. "PROCEDURAL": true,
  505. "PROCEDURE": true,
  506. "PROGRAM": true,
  507. "PUBLIC": true,
  508. "QUOTE": true,
  509. "RANGE": true,
  510. "RANK": true,
  511. "READ": true,
  512. "READS": true,
  513. "REAL": true,
  514. "REASSIGN": true,
  515. "RECHECK": true,
  516. "RECOVERY": true,
  517. "RECURSIVE": true,
  518. "REF": true,
  519. "REFERENCES": true,
  520. "REFERENCING": true,
  521. "REFRESH": true,
  522. "REGR_AVGX": true,
  523. "REGR_AVGY": true,
  524. "REGR_COUNT": true,
  525. "REGR_INTERCEPT": true,
  526. "REGR_R2": true,
  527. "REGR_SLOPE": true,
  528. "REGR_SXX": true,
  529. "REGR_SXY": true,
  530. "REGR_SYY": true,
  531. "REINDEX": true,
  532. "RELATIVE": true,
  533. "RELEASE": true,
  534. "RENAME": true,
  535. "REPEATABLE": true,
  536. "REPLACE": true,
  537. "REPLICA": true,
  538. "REQUIRING": true,
  539. "RESET": true,
  540. "RESPECT": true,
  541. "RESTART": true,
  542. "RESTORE": true,
  543. "RESTRICT": true,
  544. "RESULT": true,
  545. "RETURN": true,
  546. "RETURNED_CARDINALITY": true,
  547. "RETURNED_LENGTH": true,
  548. "RETURNED_OCTET_LENGTH": true,
  549. "RETURNED_SQLSTATE": true,
  550. "RETURNING": true,
  551. "RETURNS": true,
  552. "REVOKE": true,
  553. "RIGHT": true,
  554. "ROLE": true,
  555. "ROLLBACK": true,
  556. "ROLLUP": true,
  557. "ROUTINE": true,
  558. "ROUTINE_CATALOG": true,
  559. "ROUTINE_NAME": true,
  560. "ROUTINE_SCHEMA": true,
  561. "ROW": true,
  562. "ROWS": true,
  563. "ROW_COUNT": true,
  564. "ROW_NUMBER": true,
  565. "RULE": true,
  566. "SAVEPOINT": true,
  567. "SCALE": true,
  568. "SCHEMA": true,
  569. "SCHEMA_NAME": true,
  570. "SCOPE": true,
  571. "SCOPE_CATALOG": true,
  572. "SCOPE_NAME": true,
  573. "SCOPE_SCHEMA": true,
  574. "SCROLL": true,
  575. "SEARCH": true,
  576. "SECOND": true,
  577. "SECTION": true,
  578. "SECURITY": true,
  579. "SELECT": true,
  580. "SELECTIVE": true,
  581. "SELF": true,
  582. "SENSITIVE": true,
  583. "SEQUENCE": true,
  584. "SEQUENCES": true,
  585. "SERIALIZABLE": true,
  586. "SERVER": true,
  587. "SERVER_NAME": true,
  588. "SESSION": true,
  589. "SESSION_USER": true,
  590. "SET": true,
  591. "SETOF": true,
  592. "SETS": true,
  593. "SHARE": true,
  594. "SHOW": true,
  595. "SIMILAR": true,
  596. "SIMPLE": true,
  597. "SIZE": true,
  598. "SMALLINT": true,
  599. "SNAPSHOT": true,
  600. "SOME": true,
  601. "SOURCE": true,
  602. "SPACE": true,
  603. "SPECIFIC": true,
  604. "SPECIFICTYPE": true,
  605. "SPECIFIC_NAME": true,
  606. "SQL": true,
  607. "SQLCODE": true,
  608. "SQLERROR": true,
  609. "SQLEXCEPTION": true,
  610. "SQLSTATE": true,
  611. "SQLWARNING": true,
  612. "SQRT": true,
  613. "STABLE": true,
  614. "STANDALONE": true,
  615. "START": true,
  616. "STATE": true,
  617. "STATEMENT": true,
  618. "STATIC": true,
  619. "STATISTICS": true,
  620. "STDDEV_POP": true,
  621. "STDDEV_SAMP": true,
  622. "STDIN": true,
  623. "STDOUT": true,
  624. "STORAGE": true,
  625. "STRICT": true,
  626. "STRIP": true,
  627. "STRUCTURE": true,
  628. "STYLE": true,
  629. "SUBCLASS_ORIGIN": true,
  630. "SUBMULTISET": true,
  631. "SUBSTRING": true,
  632. "SUBSTRING_REGEX": true,
  633. "SUCCEEDS": true,
  634. "SUM": true,
  635. "SYMMETRIC": true,
  636. "SYSID": true,
  637. "SYSTEM": true,
  638. "SYSTEM_TIME": true,
  639. "SYSTEM_USER": true,
  640. "T": true,
  641. "TABLE": true,
  642. "TABLES": true,
  643. "TABLESAMPLE": true,
  644. "TABLESPACE": true,
  645. "TABLE_NAME": true,
  646. "TEMP": true,
  647. "TEMPLATE": true,
  648. "TEMPORARY": true,
  649. "TEXT": true,
  650. "THEN": true,
  651. "TIES": true,
  652. "TIME": true,
  653. "TIMESTAMP": true,
  654. "TIMEZONE_HOUR": true,
  655. "TIMEZONE_MINUTE": true,
  656. "TO": true,
  657. "TOKEN": true,
  658. "TOP_LEVEL_COUNT": true,
  659. "TRAILING": true,
  660. "TRANSACTION": true,
  661. "TRANSACTIONS_COMMITTED": true,
  662. "TRANSACTIONS_ROLLED_BACK": true,
  663. "TRANSACTION_ACTIVE": true,
  664. "TRANSFORM": true,
  665. "TRANSFORMS": true,
  666. "TRANSLATE": true,
  667. "TRANSLATE_REGEX": true,
  668. "TRANSLATION": true,
  669. "TREAT": true,
  670. "TRIGGER": true,
  671. "TRIGGER_CATALOG": true,
  672. "TRIGGER_NAME": true,
  673. "TRIGGER_SCHEMA": true,
  674. "TRIM": true,
  675. "TRIM_ARRAY": true,
  676. "TRUE": true,
  677. "TRUNCATE": true,
  678. "TRUSTED": true,
  679. "TYPE": true,
  680. "TYPES": true,
  681. "UESCAPE": true,
  682. "UNBOUNDED": true,
  683. "UNCOMMITTED": true,
  684. "UNDER": true,
  685. "UNENCRYPTED": true,
  686. "UNION": true,
  687. "UNIQUE": true,
  688. "UNKNOWN": true,
  689. "UNLINK": true,
  690. "UNLISTEN": true,
  691. "UNLOGGED": true,
  692. "UNNAMED": true,
  693. "UNNEST": true,
  694. "UNTIL": true,
  695. "UNTYPED": true,
  696. "UPDATE": true,
  697. "UPPER": true,
  698. "URI": true,
  699. "USAGE": true,
  700. "USER": true,
  701. "USER_DEFINED_TYPE_CATALOG": true,
  702. "USER_DEFINED_TYPE_CODE": true,
  703. "USER_DEFINED_TYPE_NAME": true,
  704. "USER_DEFINED_TYPE_SCHEMA": true,
  705. "USING": true,
  706. "VACUUM": true,
  707. "VALID": true,
  708. "VALIDATE": true,
  709. "VALIDATOR": true,
  710. "VALUE": true,
  711. "VALUES": true,
  712. "VALUE_OF": true,
  713. "VARBINARY": true,
  714. "VARCHAR": true,
  715. "VARIADIC": true,
  716. "VARYING": true,
  717. "VAR_POP": true,
  718. "VAR_SAMP": true,
  719. "VERBOSE": true,
  720. "VERSION": true,
  721. "VERSIONING": true,
  722. "VIEW": true,
  723. "VOLATILE": true,
  724. "WHEN": true,
  725. "WHENEVER": true,
  726. "WHERE": true,
  727. "WHITESPACE": true,
  728. "WIDTH_BUCKET": true,
  729. "WINDOW": true,
  730. "WITH": true,
  731. "WITHIN": true,
  732. "WITHOUT": true,
  733. "WORK": true,
  734. "WRAPPER": true,
  735. "WRITE": true,
  736. "XML": true,
  737. "XMLAGG": true,
  738. "XMLATTRIBUTES": true,
  739. "XMLBINARY": true,
  740. "XMLCAST": true,
  741. "XMLCOMMENT": true,
  742. "XMLCONCAT": true,
  743. "XMLDECLARATION": true,
  744. "XMLDOCUMENT": true,
  745. "XMLELEMENT": true,
  746. "XMLEXISTS": true,
  747. "XMLFOREST": true,
  748. "XMLITERATE": true,
  749. "XMLNAMESPACES": true,
  750. "XMLPARSE": true,
  751. "XMLPI": true,
  752. "XMLQUERY": true,
  753. "XMLROOT": true,
  754. "XMLSCHEMA": true,
  755. "XMLSERIALIZE": true,
  756. "XMLTABLE": true,
  757. "XMLTEXT": true,
  758. "XMLVALIDATE": true,
  759. "YEAR": true,
  760. "YES": true,
  761. "ZONE": true,
  762. }
  763. // DefaultPostgresSchema default postgres schema
  764. DefaultPostgresSchema = "public"
  765. )
  766. const postgresPublicSchema = "public"
  767. type postgres struct {
  768. core.Base
  769. }
  770. func (db *postgres) Init(d *core.DB, uri *core.Uri, drivername, dataSourceName string) error {
  771. err := db.Base.Init(d, db, uri, drivername, dataSourceName)
  772. if err != nil {
  773. return err
  774. }
  775. if db.Schema == "" {
  776. db.Schema = DefaultPostgresSchema
  777. }
  778. return nil
  779. }
  780. func (db *postgres) SqlType(c *core.Column) string {
  781. var res string
  782. switch t := c.SQLType.Name; t {
  783. case core.TinyInt:
  784. res = core.SmallInt
  785. return res
  786. case core.Bit:
  787. res = core.Boolean
  788. return res
  789. case core.MediumInt, core.Int, core.Integer:
  790. if c.IsAutoIncrement {
  791. return core.Serial
  792. }
  793. return core.Integer
  794. case core.BigInt:
  795. if c.IsAutoIncrement {
  796. return core.BigSerial
  797. }
  798. return core.BigInt
  799. case core.Serial, core.BigSerial:
  800. c.IsAutoIncrement = true
  801. c.Nullable = false
  802. res = t
  803. case core.Binary, core.VarBinary:
  804. return core.Bytea
  805. case core.DateTime:
  806. res = core.TimeStamp
  807. case core.TimeStampz:
  808. return "timestamp with time zone"
  809. case core.Float:
  810. res = core.Real
  811. case core.TinyText, core.MediumText, core.LongText:
  812. res = core.Text
  813. case core.NVarchar:
  814. res = core.Varchar
  815. case core.Uuid:
  816. return core.Uuid
  817. case core.Blob, core.TinyBlob, core.MediumBlob, core.LongBlob:
  818. return core.Bytea
  819. case core.Double:
  820. return "DOUBLE PRECISION"
  821. default:
  822. if c.IsAutoIncrement {
  823. return core.Serial
  824. }
  825. res = t
  826. }
  827. if strings.EqualFold(res, "bool") {
  828. // for bool, we don't need length information
  829. return res
  830. }
  831. hasLen1 := (c.Length > 0)
  832. hasLen2 := (c.Length2 > 0)
  833. if hasLen2 {
  834. res += "(" + strconv.Itoa(c.Length) + "," + strconv.Itoa(c.Length2) + ")"
  835. } else if hasLen1 {
  836. res += "(" + strconv.Itoa(c.Length) + ")"
  837. }
  838. return res
  839. }
  840. func (db *postgres) SupportInsertMany() bool {
  841. return true
  842. }
  843. func (db *postgres) IsReserved(name string) bool {
  844. _, ok := postgresReservedWords[name]
  845. return ok
  846. }
  847. func (db *postgres) Quote(name string) string {
  848. name = strings.Replace(name, ".", `"."`, -1)
  849. return "\"" + name + "\""
  850. }
  851. func (db *postgres) AutoIncrStr() string {
  852. return ""
  853. }
  854. func (db *postgres) SupportEngine() bool {
  855. return false
  856. }
  857. func (db *postgres) SupportCharset() bool {
  858. return false
  859. }
  860. func (db *postgres) IndexOnTable() bool {
  861. return false
  862. }
  863. func (db *postgres) IndexCheckSql(tableName, idxName string) (string, []interface{}) {
  864. if len(db.Schema) == 0 {
  865. args := []interface{}{tableName, idxName}
  866. return `SELECT indexname FROM pg_indexes WHERE tablename = ? AND indexname = ?`, args
  867. }
  868. args := []interface{}{db.Schema, tableName, idxName}
  869. return `SELECT indexname FROM pg_indexes ` +
  870. `WHERE schemaname = ? AND tablename = ? AND indexname = ?`, args
  871. }
  872. func (db *postgres) TableCheckSql(tableName string) (string, []interface{}) {
  873. if len(db.Schema) == 0 {
  874. args := []interface{}{tableName}
  875. return `SELECT tablename FROM pg_tables WHERE tablename = ?`, args
  876. }
  877. args := []interface{}{db.Schema, tableName}
  878. return `SELECT tablename FROM pg_tables WHERE schemaname = ? AND tablename = ?`, args
  879. }
  880. func (db *postgres) ModifyColumnSql(tableName string, col *core.Column) string {
  881. if len(db.Schema) == 0 {
  882. return fmt.Sprintf("alter table %s ALTER COLUMN %s TYPE %s",
  883. tableName, col.Name, db.SqlType(col))
  884. }
  885. return fmt.Sprintf("alter table %s.%s ALTER COLUMN %s TYPE %s",
  886. db.Schema, tableName, col.Name, db.SqlType(col))
  887. }
  888. func (db *postgres) DropIndexSql(tableName string, index *core.Index) string {
  889. quote := db.Quote
  890. idxName := index.Name
  891. tableName = strings.Replace(tableName, `"`, "", -1)
  892. tableName = strings.Replace(tableName, `.`, "_", -1)
  893. if !strings.HasPrefix(idxName, "UQE_") &&
  894. !strings.HasPrefix(idxName, "IDX_") {
  895. if index.Type == core.UniqueType {
  896. idxName = fmt.Sprintf("UQE_%v_%v", tableName, index.Name)
  897. } else {
  898. idxName = fmt.Sprintf("IDX_%v_%v", tableName, index.Name)
  899. }
  900. }
  901. if db.Uri.Schema != "" {
  902. idxName = db.Uri.Schema + "." + idxName
  903. }
  904. return fmt.Sprintf("DROP INDEX %v", quote(idxName))
  905. }
  906. func (db *postgres) IsColumnExist(tableName, colName string) (bool, error) {
  907. args := []interface{}{db.Schema, tableName, colName}
  908. query := "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = $1 AND table_name = $2" +
  909. " AND column_name = $3"
  910. if len(db.Schema) == 0 {
  911. args = []interface{}{tableName, colName}
  912. query = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = $1" +
  913. " AND column_name = $2"
  914. }
  915. db.LogSQL(query, args)
  916. rows, err := db.DB().Query(query, args...)
  917. if err != nil {
  918. return false, err
  919. }
  920. defer rows.Close()
  921. return rows.Next(), nil
  922. }
  923. func (db *postgres) GetColumns(tableName string) ([]string, map[string]*core.Column, error) {
  924. args := []interface{}{tableName}
  925. s := `SELECT column_name, column_default, is_nullable, data_type, character_maximum_length,
  926. CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
  927. CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
  928. FROM pg_attribute f
  929. JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
  930. LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
  931. LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  932. LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
  933. LEFT JOIN pg_class AS g ON p.confrelid = g.oid
  934. LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
  935. WHERE c.relkind = 'r'::char AND c.relname = $1%s AND f.attnum > 0 ORDER BY f.attnum;`
  936. var f string
  937. if len(db.Schema) != 0 {
  938. args = append(args, db.Schema)
  939. f = " AND s.table_schema = $2"
  940. }
  941. s = fmt.Sprintf(s, f)
  942. db.LogSQL(s, args)
  943. rows, err := db.DB().Query(s, args...)
  944. if err != nil {
  945. return nil, nil, err
  946. }
  947. defer rows.Close()
  948. cols := make(map[string]*core.Column)
  949. colSeq := make([]string, 0)
  950. for rows.Next() {
  951. col := new(core.Column)
  952. col.Indexes = make(map[string]int)
  953. var colName, isNullable, dataType string
  954. var maxLenStr, colDefault *string
  955. var isPK, isUnique bool
  956. err = rows.Scan(&colName, &colDefault, &isNullable, &dataType, &maxLenStr, &isPK, &isUnique)
  957. if err != nil {
  958. return nil, nil, err
  959. }
  960. // fmt.Println(args, colName, isNullable, dataType, maxLenStr, colDefault, isPK, isUnique)
  961. var maxLen int
  962. if maxLenStr != nil {
  963. maxLen, err = strconv.Atoi(*maxLenStr)
  964. if err != nil {
  965. return nil, nil, err
  966. }
  967. }
  968. col.Name = strings.Trim(colName, `" `)
  969. if colDefault != nil {
  970. col.Default = *colDefault
  971. col.DefaultIsEmpty = false
  972. if strings.HasPrefix(col.Default, "nextval(") {
  973. col.IsAutoIncrement = true
  974. }
  975. } else {
  976. col.DefaultIsEmpty = true
  977. }
  978. if isPK {
  979. col.IsPrimaryKey = true
  980. }
  981. col.Nullable = (isNullable == "YES")
  982. switch dataType {
  983. case "character varying", "character":
  984. col.SQLType = core.SQLType{Name: core.Varchar, DefaultLength: 0, DefaultLength2: 0}
  985. case "timestamp without time zone":
  986. col.SQLType = core.SQLType{Name: core.DateTime, DefaultLength: 0, DefaultLength2: 0}
  987. case "timestamp with time zone":
  988. col.SQLType = core.SQLType{Name: core.TimeStampz, DefaultLength: 0, DefaultLength2: 0}
  989. case "double precision":
  990. col.SQLType = core.SQLType{Name: core.Double, DefaultLength: 0, DefaultLength2: 0}
  991. case "boolean":
  992. col.SQLType = core.SQLType{Name: core.Bool, DefaultLength: 0, DefaultLength2: 0}
  993. case "time without time zone":
  994. col.SQLType = core.SQLType{Name: core.Time, DefaultLength: 0, DefaultLength2: 0}
  995. case "oid":
  996. col.SQLType = core.SQLType{Name: core.BigInt, DefaultLength: 0, DefaultLength2: 0}
  997. default:
  998. col.SQLType = core.SQLType{Name: strings.ToUpper(dataType), DefaultLength: 0, DefaultLength2: 0}
  999. }
  1000. if _, ok := core.SqlTypes[col.SQLType.Name]; !ok {
  1001. return nil, nil, fmt.Errorf("Unknown colType: %v", dataType)
  1002. }
  1003. col.Length = maxLen
  1004. if !col.DefaultIsEmpty {
  1005. if col.SQLType.IsText() {
  1006. if strings.HasSuffix(col.Default, "::character varying") {
  1007. col.Default = strings.TrimRight(col.Default, "::character varying")
  1008. } else if !strings.HasPrefix(col.Default, "'") {
  1009. col.Default = "'" + col.Default + "'"
  1010. }
  1011. } else if col.SQLType.IsTime() {
  1012. if strings.HasSuffix(col.Default, "::timestamp without time zone") {
  1013. col.Default = strings.TrimRight(col.Default, "::timestamp without time zone")
  1014. }
  1015. }
  1016. }
  1017. cols[col.Name] = col
  1018. colSeq = append(colSeq, col.Name)
  1019. }
  1020. return colSeq, cols, nil
  1021. }
  1022. func (db *postgres) GetTables() ([]*core.Table, error) {
  1023. args := []interface{}{}
  1024. s := "SELECT tablename FROM pg_tables"
  1025. if len(db.Schema) != 0 {
  1026. args = append(args, db.Schema)
  1027. s = s + " WHERE schemaname = $1"
  1028. }
  1029. db.LogSQL(s, args)
  1030. rows, err := db.DB().Query(s, args...)
  1031. if err != nil {
  1032. return nil, err
  1033. }
  1034. defer rows.Close()
  1035. tables := make([]*core.Table, 0)
  1036. for rows.Next() {
  1037. table := core.NewEmptyTable()
  1038. var name string
  1039. err = rows.Scan(&name)
  1040. if err != nil {
  1041. return nil, err
  1042. }
  1043. table.Name = name
  1044. tables = append(tables, table)
  1045. }
  1046. return tables, nil
  1047. }
  1048. func getIndexColName(indexdef string) []string {
  1049. var colNames []string
  1050. cs := strings.Split(indexdef, "(")
  1051. for _, v := range strings.Split(strings.Split(cs[1], ")")[0], ",") {
  1052. colNames = append(colNames, strings.Split(strings.TrimLeft(v, " "), " ")[0])
  1053. }
  1054. return colNames
  1055. }
  1056. func (db *postgres) GetIndexes(tableName string) (map[string]*core.Index, error) {
  1057. args := []interface{}{tableName}
  1058. s := fmt.Sprintf("SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1")
  1059. if len(db.Schema) != 0 {
  1060. args = append(args, db.Schema)
  1061. s = s + " AND schemaname=$2"
  1062. }
  1063. db.LogSQL(s, args)
  1064. rows, err := db.DB().Query(s, args...)
  1065. if err != nil {
  1066. return nil, err
  1067. }
  1068. defer rows.Close()
  1069. indexes := make(map[string]*core.Index, 0)
  1070. for rows.Next() {
  1071. var indexType int
  1072. var indexName, indexdef string
  1073. var colNames []string
  1074. err = rows.Scan(&indexName, &indexdef)
  1075. if err != nil {
  1076. return nil, err
  1077. }
  1078. indexName = strings.Trim(indexName, `" `)
  1079. if strings.HasSuffix(indexName, "_pkey") {
  1080. continue
  1081. }
  1082. if strings.HasPrefix(indexdef, "CREATE UNIQUE INDEX") {
  1083. indexType = core.UniqueType
  1084. } else {
  1085. indexType = core.IndexType
  1086. }
  1087. colNames = getIndexColName(indexdef)
  1088. var isRegular bool
  1089. if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
  1090. newIdxName := indexName[5+len(tableName):]
  1091. isRegular = true
  1092. if newIdxName != "" {
  1093. indexName = newIdxName
  1094. }
  1095. }
  1096. index := &core.Index{Name: indexName, Type: indexType, Cols: make([]string, 0)}
  1097. for _, colName := range colNames {
  1098. index.Cols = append(index.Cols, strings.Trim(colName, `" `))
  1099. }
  1100. index.IsRegular = isRegular
  1101. indexes[index.Name] = index
  1102. }
  1103. return indexes, nil
  1104. }
  1105. func (db *postgres) Filters() []core.Filter {
  1106. return []core.Filter{&core.IdFilter{}, &core.QuoteFilter{}, &core.SeqFilter{Prefix: "$", Start: 1}}
  1107. }
  1108. type pqDriver struct {
  1109. }
  1110. type values map[string]string
  1111. func (vs values) Set(k, v string) {
  1112. vs[k] = v
  1113. }
  1114. func (vs values) Get(k string) (v string) {
  1115. return vs[k]
  1116. }
  1117. func parseURL(connstr string) (string, error) {
  1118. u, err := url.Parse(connstr)
  1119. if err != nil {
  1120. return "", err
  1121. }
  1122. if u.Scheme != "postgresql" && u.Scheme != "postgres" {
  1123. return "", fmt.Errorf("invalid connection protocol: %s", u.Scheme)
  1124. }
  1125. escaper := strings.NewReplacer(` `, `\ `, `'`, `\'`, `\`, `\\`)
  1126. if u.Path != "" {
  1127. return escaper.Replace(u.Path[1:]), nil
  1128. }
  1129. return "", nil
  1130. }
  1131. func parseOpts(name string, o values) error {
  1132. if len(name) == 0 {
  1133. return fmt.Errorf("invalid options: %s", name)
  1134. }
  1135. name = strings.TrimSpace(name)
  1136. ps := strings.Split(name, " ")
  1137. for _, p := range ps {
  1138. kv := strings.Split(p, "=")
  1139. if len(kv) < 2 {
  1140. return fmt.Errorf("invalid option: %q", p)
  1141. }
  1142. o.Set(kv[0], kv[1])
  1143. }
  1144. return nil
  1145. }
  1146. func (p *pqDriver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  1147. db := &core.Uri{DbType: core.POSTGRES}
  1148. var err error
  1149. if strings.HasPrefix(dataSourceName, "postgresql://") || strings.HasPrefix(dataSourceName, "postgres://") {
  1150. db.DbName, err = parseURL(dataSourceName)
  1151. if err != nil {
  1152. return nil, err
  1153. }
  1154. } else {
  1155. o := make(values)
  1156. err = parseOpts(dataSourceName, o)
  1157. if err != nil {
  1158. return nil, err
  1159. }
  1160. db.DbName = o.Get("dbname")
  1161. }
  1162. if db.DbName == "" {
  1163. return nil, errors.New("dbname is empty")
  1164. }
  1165. return db, nil
  1166. }
  1167. type pqDriverPgx struct {
  1168. pqDriver
  1169. }
  1170. func (pgx *pqDriverPgx) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  1171. // Remove the leading characters for driver to work
  1172. if len(dataSourceName) >= 9 && dataSourceName[0] == 0 {
  1173. dataSourceName = dataSourceName[9:]
  1174. }
  1175. return pgx.pqDriver.Parse(driverName, dataSourceName)
  1176. }