dstruct.dialects.pgsqldialect source code

1 /**
2  * DStruct - Object-Relation Mapping for D programming language, with interface similar to Hibernate. 
3  * 
4  * Source file dstruct/dialects/sqlitedialect.d.
5  *
6  * This module contains implementation of PGSQLDialect class which provides implementation specific SQL syntax information.
7  * 
8  * Copyright: Copyright 2013
9  * License:   $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0).
10  * Author:   Vadim Lopatin
11  */
12 module dstruct.dialects.pgsqldialect;
13 
14 import std.conv;
15 
16 import dstruct.dialect;
17 import dstruct.metadata;
18 import dstruct.type;
19 import dstruct.ddbc.core;
20 
21 
22 string[] PGSQL_RESERVED_WORDS = 
23     [
24      "ABORT",
25      "ACTION",
26      "ADD",
27      "AFTER",
28      "ALL",
29      "ALTER",
30      "ANALYZE",
31      "AND",
32      "AS",
33      "ASC",
34      "ATTACH",
35      "AUTOINCREMENT",
36      "BEFORE",
37      "BEGIN",
38      "BETWEEN",
39      "BY",
40      "CASCADE",
41      "CASE",
42      "CAST",
43      "CHECK",
44      "COLLATE",
45      "COLUMN",
46      "COMMIT",
47      "CONFLICT",
48      "CONSTRAINT",
49      "CREATE",
50      "CROSS",
51      "CURRENT_DATE",
52      "CURRENT_TIME",
53      "CURRENT_TIMESTAMP",
54      "DATABASE",
55      "DEFAULT",
56      "DEFERRABLE",
57      "DEFERRED",
58      "DELETE",
59      "DESC",
60      "DETACH",
61      "DISTINCT",
62      "DROP",
63      "EACH",
64      "ELSE",
65      "END",
66      "ESCAPE",
67      "EXCEPT",
68      "EXCLUSIVE",
69      "EXISTS",
70      "EXPLAIN",
71      "FAIL",
72      "FOR",
73      "FOREIGN",
74      "FROM",
75      "FULL",
76      "GLOB",
77      "GROUP",
78      "HAVING",
79      "IF",
80      "IGNORE",
81      "IMMEDIATE",
82      "IN",
83      "INDEX",
84      "INDEXED",
85      "INITIALLY",
86      "INNER",
87      "INSERT",
88      "INSTEAD",
89      "INTERSECT",
90      "INTO",
91      "IS",
92      "ISNULL",
93      "JOIN",
94      "KEY",
95      "LEFT",
96      "LIKE",
97      "LIMIT",
98      "MATCH",
99      "NATURAL",
100      "NO",
101      "NOT",
102      "NOTNULL",
103      "NULL",
104      "OF",
105      "OFFSET",
106      "ON",
107      "OR",
108      "ORDER",
109      "OUTER",
110      "PLAN",
111      "PRAGMA",
112      "PRIMARY",
113      "QUERY",
114      "RAISE",
115      "REFERENCES",
116      "REGEXP",
117      "REINDEX",
118      "RELEASE",
119      "RENAME",
120      "REPLACE",
121      "RESTRICT",
122      "RIGHT",
123      "ROLLBACK",
124      "ROW",
125      "SAVEPOINT",
126      "SELECT",
127      "SET",
128      "TABLE",
129      "TEMP",
130      "TEMPORARY",
131      "THEN",
132      "TO",
133      "TRANSACTION",
134      "TRIGGER",
135      "UNION",
136      "UNIQUE",
137      "UPDATE",
138      "USER",
139      "USING",
140      "VACUUM",
141      "VALUES",
142      "VIEW",
143      "VIRTUAL",
144      "WHEN",
145      "WHERE",
146      ];
147 
148 
149 class PGSQLDialect : Dialect {
150     ///The character specific to this dialect used to close a quoted identifier.
151     override char closeQuote() const { return '"'; }
152     ///The character specific to this dialect used to begin a quoted identifier.
153     override char  openQuote() const { return '"'; }
154     
155     // returns string like "BIGINT(20) NOT NULL" or "VARCHAR(255) NULL"
156     override string getColumnTypeDefinition(const PropertyInfo pi, const PropertyInfo overrideTypeFrom = null) {
157         immutable Type type = overrideTypeFrom !is null ? overrideTypeFrom.columnType : pi.columnType;
158         immutable SqlType sqlType = type.getSqlType();
159         bool fk = pi is null;
160         string nullablility = !fk && pi.nullable ? " NULL" : " NOT NULL";
161         string pk = !fk && pi.key ? " PRIMARY KEY" : "";
162         if (!fk && pi.generated) {
163             if (sqlType == SqlType.SMALLINT || sqlType == SqlType.TINYINT)
164                 return "SERIAL PRIMARY KEY";
165             if (sqlType == SqlType.INTEGER)
166                 return "SERIAL PRIMARY KEY";
167             return "BIGSERIAL PRIMARY KEY";
168         }
169         string def = "";
170         int len = 0;
171         if (cast(NumberType)type !is null) {
172             len = (cast(NumberType)type).length;
173         }
174         if (cast(StringType)type !is null) {
175             len = (cast(StringType)type).length;
176         }
177         string modifiers = nullablility ~ def ~ pk;
178         string lenmodifiers = "(" ~ to!string(len > 0 ? len : 255) ~ ")" ~ modifiers;
179         switch (sqlType) {
180             case SqlType.BIGINT:
181                 return "BIGINT" ~ modifiers;
182             case SqlType.BIT:
183             case SqlType.BOOLEAN:
184                 return "BOOLEAN" ~ modifiers;
185             case SqlType.INTEGER:
186                 return "INT" ~ modifiers;
187             case SqlType.NUMERIC:
188                 return "INT" ~ modifiers;
189             case SqlType.SMALLINT:
190                 return "SMALLINT" ~ modifiers;
191             case SqlType.TINYINT:
192                 return "SMALLINT" ~ modifiers;
193             case SqlType.FLOAT:
194                 return "FLOAT(24)" ~ modifiers;
195             case SqlType.DOUBLE:
196                 return "FLOAT(53)" ~ modifiers;
197             case SqlType.DECIMAL:
198                 return "REAL" ~ modifiers;
199             case SqlType.DATE:
200                 return "DATE" ~ modifiers;
201             case SqlType.DATETIME:
202                 return "TIMESTAMP" ~ modifiers;
203             case SqlType.TIME:
204                 return "TIME" ~ modifiers;
205             case SqlType.CHAR:
206             case SqlType.CLOB:
207             case SqlType.LONGNVARCHAR:
208             case SqlType.LONGVARBINARY:
209             case SqlType.LONGVARCHAR:
210             case SqlType.NCHAR:
211             case SqlType.NCLOB:
212             case SqlType.VARBINARY:
213             case SqlType.VARCHAR:
214             case SqlType.NVARCHAR:
215                 return "TEXT" ~ modifiers;
216             case SqlType.BLOB:
217                 return "BYTEA";
218             default:
219                 return "TEXT";
220         }
221     }
222     
223     override string getCheckTableExistsSQL(string tableName) {
224         return "select relname from pg_class where relname = " ~ quoteSqlString(tableName) ~ " and relkind='r'";
225     }
226     
227     override string getUniqueIndexItemSQL(string indexName, string[] columnNames) {
228         return "UNIQUE " ~ createFieldListSQL(columnNames);
229     }
230     
231     /// for some of RDBMS it's necessary to pass additional clauses in query to get generated value (e.g. in Postgres - " returing id"
232     override string appendInsertToFetchGeneratedKey(string query, const EntityInfo entity) {
233         return query ~ " RETURNING " ~ quoteIfNeeded(entity.getKeyProperty().columnName);
234     }
235 
236     this() {
237         addKeywords(PGSQL_RESERVED_WORDS);
238     }
239 }
240