1 /// Generates SQL query with appropriate variable types
2 module dpq2.query_gen;
3 
4 import dpq2.args: QueryParams;
5 import dpq2.connection: Connection;
6 import std.conv: to;
7 import std.traits: isInstanceOf;
8 import std.array: appender;
9 import dpq2.conv.from_d_types: toValue;
10 
11 private enum ArgLikeIn
12 {
13     INSERT, // looks like "FieldName" and passes value as appropriate variable
14     UPDATE, // looks like "FieldName" = $3 and passes value into appropriate dollar variable
15 }
16 
17 private struct Arg(ArgLikeIn _argLikeIn, T)
18 {
19     enum argLikeIn = _argLikeIn;
20 
21     string name;
22     T value;
23 }
24 
25 private struct DollarArg(T)
26 {
27     T value;
28 }
29 
30 /// INSERT-like argument
31 auto i(T)(string statementArgName, T value)
32 {
33     return Arg!(ArgLikeIn.INSERT, T)(statementArgName, value);
34 }
35 
36 /// UPDATE-like argument
37 auto u(T)(string statementArgName, T value)
38 {
39     return Arg!(ArgLikeIn.UPDATE, T)(statementArgName, value);
40 }
41 
42 /// Argument representing dollar, usable in SELECT statements
43 auto d(T)(T value)
44 {
45     return DollarArg!T(value);
46 }
47 
48 private struct CTStatement(SQL_CMD...)
49 {
50     QueryParams qp;
51     alias qp this;
52 
53     this(Connection conn, SQL_CMD sqlCmd)
54     {
55         qp = parseSqlCmd!SQL_CMD(conn, sqlCmd);
56     }
57 }
58 
59 private string dollarsString(size_t num)
60 {
61     string ret;
62 
63     foreach(i; 1 .. num+1)
64     {
65           ret ~= `$`;
66           ret ~= i.to!string;
67 
68           if(i < num)
69                 ret ~= `,`;
70     }
71 
72     return ret;
73 }
74 
75 private template isStatementArg(T)
76 {
77     enum isStatementArg = 
78         isInstanceOf!(Arg, T) ||
79         isInstanceOf!(DollarArg, T);
80 }
81 
82 private bool symbolNeedsDelimit(dchar c)
83 {
84     import std.ascii: isAlphaNum;
85 
86     return c == '$' || c.isAlphaNum;
87 }
88 
89 private void concatWithDelimiter(A, T)(ref A appender, T val)
90 {
91 
92     if(
93         val.length &&
94         appender.data.length &&
95         val[0].symbolNeedsDelimit &&
96         appender.data[$-1].symbolNeedsDelimit
97     )
98         appender ~= ' ';
99 
100     appender ~= val;
101 }
102 
103 private string escapeName(string s, Connection conn)
104 {
105     if(conn !is null)
106         return conn.escapeIdentifier(s);
107     else
108         return '"'~s~'"';
109 }
110 
111 private QueryParams parseSqlCmd(SQL_CMD...)(Connection conn, SQL_CMD sqlCmd)
112 {
113     QueryParams qp;
114     auto resultSql = appender!string;
115 
116     foreach(i, V; sqlCmd)
117     {
118         // argument variable is found?
119         static if(isStatementArg!(typeof(V)))
120         {
121             // previous argument already was processed?
122             static if(i > 0 && isStatementArg!(typeof(sqlCmd[i-1])))
123             {
124                 resultSql ~= `,`;
125             }
126 
127             static if(isInstanceOf!(DollarArg, typeof(V)))
128             {
129                 resultSql.concatWithDelimiter(`$`);
130                 resultSql ~= (qp.args.length + 1).to!string;
131             }
132             else static if(V.argLikeIn == ArgLikeIn.UPDATE)
133             {
134                 resultSql ~= V.name.escapeName(conn);
135                 resultSql ~= `=$`;
136                 resultSql ~= (qp.args.length + 1).to!string;
137             }
138             else static if(V.argLikeIn == ArgLikeIn.INSERT)
139             {
140                 resultSql ~= V.name.escapeName(conn);
141             }
142             else
143                 static assert(false);
144 
145             qp.args ~= V.value.toValue;
146         }
147         else
148         {
149             // Usable as INSERT VALUES ($1, $2, ...) argument
150             static if(is(typeof(V) == Dollars))
151             {
152                 resultSql ~= dollarsString(qp.args.length);
153             }
154             else
155             {
156                 // ordinary part of SQL statement
157                 resultSql.concatWithDelimiter(V);
158             }
159         }
160     }
161 
162     qp.sqlCommand = resultSql[];
163 
164     return qp;
165 }
166 
167 struct Dollars {}
168 
169 ///
170 auto wrapStatement(C : Connection, T...)(C conn, T statement)
171 {
172     return CTStatement!T(conn, statement);
173 }
174 
175 ///
176 auto wrapStatement(T...)(T statement)
177 if(!is(T[0] == Connection))
178 {
179     return CTStatement!T(null, statement);
180 }
181 
182 unittest
183 {
184     auto stmnt = wrapStatement(`abc=`, d(123));
185 
186     assert(stmnt.qp.sqlCommand == `abc=$1`);
187     assert(stmnt.qp.args.length == 1);
188     assert(stmnt.qp.args[0] == 123.toValue);
189 }
190 
191 unittest
192 {
193     auto stmnt = wrapStatement(
194         `SELECT`, d!string("abc"), d!int(123)
195     );
196 
197     assert(stmnt.qp.args.length == 2);
198     assert(stmnt.qp.args[0] == "abc".toValue);
199     assert(stmnt.qp.args[1] == 123.toValue);
200 }
201 
202 unittest
203 {
204     auto stmnt = wrapStatement(
205         `UPDATE table1`,
206         `SET`,
207             u(`boolean_field`, true),
208             u(`integer_field`, 123),
209             u(`text_field`, `abc`),
210     );
211 
212     assert(stmnt.qp.sqlCommand.length > 10);
213     assert(stmnt.qp.args.length == 3);
214     assert(stmnt.qp.args[0] == true.toValue);
215     assert(stmnt.qp.args[1] == 123.toValue);
216     assert(stmnt.qp.args[2] == `abc`.toValue);
217 }
218 
219 unittest
220 {
221     int integer = 123;
222     int another_integer = 456;
223     string text = "abc";
224 
225     auto stmnt = wrapStatement(
226         `INSERT INTO table1 (`,
227             i(`integer_field`, integer),
228             i(`text_field`, text),
229         `) WHERE`,
230             u(`integer_field`, another_integer),
231         `VALUES(`, Dollars(),`)`
232     );
233 
234     assert(stmnt.qp.sqlCommand.length > 10);
235     assert(stmnt.qp.args[0] == 123.toValue);
236     assert(stmnt.qp.args[1] == `abc`.toValue);
237     assert(stmnt.qp.args[2] == 456.toValue);
238 }
239 
240 version(integration_tests)
241 void _integration_test(string connParam)
242 {
243     import dpq2.connection: createTestConn;
244 
245     auto conn = createTestConn(connParam);
246     auto stmnt = wrapStatement(conn, i("Some Integer", 123));
247 
248     assert(stmnt.qp.sqlCommand == `"Some Integer"`);
249     assert(stmnt.qp.args.length == 1);
250     assert(stmnt.qp.args[0] == 123.toValue);
251 }