1 /**
2 DDBC - D DataBase Connector - abstraction layer for RDBMS access, with interface similar to JDBC.
3
4 Source file ddbc/drivers/pgsqldstruct.ddbc.d.
5 DDBC library attempts to provide implementation independent interface to different databases.
6
7 Set of supported RDBMSs can be extended by writing Drivers for particular DBs.
8
9 JDBC documentation can be found here:
10 $(LINK http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/package-summary.html)$(BR)
11
12 This module contains implementation POD utilities.
13 ----
14 import dstruct.ddbc;
15 import std.stdio;
16
17 // prepare database connectivity
18 auto conn = createConnection("sqlite:ddbctest.sqlite");
19 scope(exit) conn.close();
20 Statement stmt = conn.createStatement();
21 scope(exit) stmt.close();
22 // fill database with test data
23 stmt.executeUpdate("DROP TABLE IF EXISTS user");
24 stmt.executeUpdate("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR(255) NOT NULL, flags int null)");
25 stmt.executeUpdate(`INSERT INTO user (id, name, flags) VALUES (1, "John", 5), (2, "Andrei", 2), (3, "Walter", 2), (4, "Rikki", 3), (5, "Iain", 0), (6, "Robert", 1)`);
26
27 // our POD object
28 struct User {
29 long id;
30 string name;
31 int flags;
32 }
33
34 writeln("reading all user table rows");
35 foreach(e; stmt.select!User) {
36 writeln("id:", e.id, " name:", e.name, " flags:", e.flags);
37 }
38
39 writeln("reading user table rows with where and order by");
40 foreach(e; stmt.select!User.where("id < 6").orderBy("name desc")) {
41 writeln("id:", e.id, " name:", e.name, " flags:", e.flags);
42 }
43 ----
44
45 Copyright: Copyright 2013
46 License: $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0).
47 Author: Vadim Lopatin
48 */
49 module dstruct.ddbc.pods;
50
51 import std.algorithm;
52 import std.traits;
53 import std.typecons;
54 import std.conv;
55 import std.datetime;
56 import std..string;
57 import std.variant;
58
59 static import std.ascii;
60
61 import dstruct.ddbc.core;
62
63 alias Nullable!byte Byte;
64 alias Nullable!ubyte Ubyte;
65 alias Nullable!short Short;
66 alias Nullable!ushort Ushort;
67 alias Nullable!int Int;
68 alias Nullable!uint Uint;
69 alias Nullable!long Long;
70 alias Nullable!ulong Ulong;
71 alias Nullable!float Float;
72 alias Nullable!double Double;
73 alias Nullable!SysTime NullableSysTime;
74 alias Nullable!DateTime NullableDateTime;
75 alias Nullable!Date NullableDate;
76 alias Nullable!TimeOfDay NullableTimeOfDay;
77
78 enum PropertyMemberType : int {
79 BOOL_TYPE, // bool
80 BYTE_TYPE, // byte
81 SHORT_TYPE, // short
82 INT_TYPE, // int
83 LONG_TYPE, // long
84 UBYTE_TYPE, // ubyte
85 USHORT_TYPE, // ushort
86 UINT_TYPE, // uint
87 ULONG_TYPE, // ulong
88 NULLABLE_BYTE_TYPE, // Nullable!byte
89 NULLABLE_SHORT_TYPE, // Nullable!short
90 NULLABLE_INT_TYPE, // Nullable!int
91 NULLABLE_LONG_TYPE, // Nullable!long
92 NULLABLE_UBYTE_TYPE, // Nullable!ubyte
93 NULLABLE_USHORT_TYPE,// Nullable!ushort
94 NULLABLE_UINT_TYPE, // Nullable!uint
95 NULLABLE_ULONG_TYPE, // Nullable!ulong
96 FLOAT_TYPE, // float
97 DOUBLE_TYPE, // double
98 NULLABLE_FLOAT_TYPE, // Nullable!float
99 NULLABLE_DOUBLE_TYPE,// Nullable!double
100 STRING_TYPE, // string
101 SYSTIME_TYPE,
102 DATETIME_TYPE, // std.datetime.DateTime
103 DATE_TYPE, // std.datetime.Date
104 TIME_TYPE, // std.datetime.TimeOfDay
105 NULLABLE_SYSTIME_TYPE,
106 NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
107 NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
108 NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
109 BYTE_ARRAY_TYPE, // byte[]
110 UBYTE_ARRAY_TYPE, // ubyte[]
111 }
112
113 /// converts camel case MyEntityName to my_entity_name
114 string camelCaseToUnderscoreDelimited(immutable string s) {
115 string res;
116 bool lastLower = false;
117 static import std.ascii;
118
119 foreach(ch; s) {
120 if (ch >= 'A' && ch <= 'Z') {
121 if (lastLower) {
122 lastLower = false;
123 res ~= "_";
124 }
125 res ~= std.ascii.toLower(ch);
126 } else if (ch >= 'a' && ch <= 'z') {
127 lastLower = true;
128 res ~= ch;
129 } else {
130 res ~= ch;
131 }
132 }
133 return res;
134 }
135
136 unittest {
137 static assert(camelCaseToUnderscoreDelimited("User") == "user");
138 static assert(camelCaseToUnderscoreDelimited("MegaTableName") == "mega_table_name");
139 }
140
141
142 template isSupportedSimpleType(T, string m) {
143 alias typeof(__traits(getMember, T, m)) ti;
144 static if (is(ti == function)) {
145 static if (is(ReturnType!(ti) == bool)) {
146 enum bool isSupportedSimpleType = true;
147 } else static if (is(ReturnType!(ti) == byte)) {
148 enum bool isSupportedSimpleType = true;
149 } else static if (is(ReturnType!(ti) == short)) {
150 enum bool isSupportedSimpleType = true;
151 } else static if (is(ReturnType!(ti) == int)) {
152 enum bool isSupportedSimpleType = true;
153 } else static if (is(ReturnType!(ti) == long)) {
154 enum bool isSupportedSimpleType = true;
155 } else static if (is(ReturnType!(ti) == ubyte)) {
156 enum bool isSupportedSimpleType = true;
157 } else static if (is(ReturnType!(ti) == ushort)) {
158 enum bool isSupportedSimpleType = true;
159 } else static if (is(ReturnType!(ti) == uint)) {
160 enum bool isSupportedSimpleType = true;
161 } else static if (is(ReturnType!(ti) == ulong)) {
162 enum bool isSupportedSimpleType = true;
163 } else static if (is(ReturnType!(ti) == float)) {
164 enum bool isSupportedSimpleType = true;
165 } else static if (is(ReturnType!(ti) == double)) {
166 enum bool isSupportedSimpleType = true;
167 } else static if (is(ReturnType!(ti) == Nullable!byte)) {
168 enum bool isSupportedSimpleType = true;
169 } else static if (is(ReturnType!(ti) == Nullable!short)) {
170 enum bool isSupportedSimpleType = true;
171 } else static if (is(ReturnType!(ti) == Nullable!int)) {
172 enum bool isSupportedSimpleType = true;
173 } else static if (is(ReturnType!(ti) == Nullable!long)) {
174 enum bool isSupportedSimpleType = true;
175 } else static if (is(ReturnType!(ti) == Nullable!ubyte)) {
176 enum bool isSupportedSimpleType = true;
177 } else static if (is(ReturnType!(ti) == Nullable!ushort)) {
178 enum bool isSupportedSimpleType = true;
179 } else static if (is(ReturnType!(ti) == Nullable!uint)) {
180 enum bool isSupportedSimpleType = true;
181 } else static if (is(ReturnType!(ti) == Nullable!ulong)) {
182 enum bool isSupportedSimpleType = true;
183 } else static if (is(ReturnType!(ti) == Nullable!float)) {
184 enum bool isSupportedSimpleType = true;
185 } else static if (is(ReturnType!(ti) == Nullable!double)) {
186 enum bool isSupportedSimpleType = true;
187 } else static if (is(ReturnType!(ti) == string)) {
188 enum bool isSupportedSimpleType = true;
189 } else static if (is(ReturnType!(ti) == SysTime)) {
190 enum bool isSupportedSimpleType = true;
191 } else static if (is(ReturnType!(ti) == DateTime)) {
192 enum bool isSupportedSimpleType = true;
193 } else static if (is(ReturnType!(ti) == Date)) {
194 enum bool isSupportedSimpleType = true;
195 } else static if (is(ReturnType!(ti) == TimeOfDay)) {
196 enum bool isSupportedSimpleType = true;
197 } else static if (is(ReturnType!(ti) == Nullable!SysTime)) {
198 enum bool isSupportedSimpleType = true;
199 } else static if (is(ReturnType!(ti) == Nullable!DateTime)) {
200 enum bool isSupportedSimpleType = true;
201 } else static if (is(ReturnType!(ti) == Nullable!Date)) {
202 enum bool isSupportedSimpleType = true;
203 } else static if (is(ReturnType!(ti) == Nullable!TimeOfDay)) {
204 enum bool isSupportedSimpleType = true;
205 } else static if (is(ReturnType!(ti) == byte[])) {
206 enum bool isSupportedSimpleType = true;
207 } else static if (is(ReturnType!(ti) == ubyte[])) {
208 enum bool isSupportedSimpleType = true;
209 } else static if (true) {
210 enum bool isSupportedSimpleType = false;
211 }
212 } else static if (is(ti == bool)) {
213 enum bool isSupportedSimpleType = true;
214 } else static if (is(ti == byte)) {
215 enum bool isSupportedSimpleType = true;
216 } else static if (is(ti == short)) {
217 enum bool isSupportedSimpleType = true;
218 } else static if (is(ti == int)) {
219 enum bool isSupportedSimpleType = true;
220 } else static if (is(ti == long)) {
221 enum bool isSupportedSimpleType = true;
222 } else static if (is(ti == ubyte)) {
223 enum bool isSupportedSimpleType = true;
224 } else static if (is(ti == ushort)) {
225 enum bool isSupportedSimpleType = true;
226 } else static if (is(ti == uint)) {
227 enum bool isSupportedSimpleType = true;
228 } else static if (is(ti == ulong)) {
229 enum bool isSupportedSimpleType = true;
230 } else static if (is(ti == float)) {
231 enum bool isSupportedSimpleType = true;
232 } else static if (is(ti == double)) {
233 enum bool isSupportedSimpleType = true;
234 } else static if (is(ti == Nullable!byte)) {
235 enum bool isSupportedSimpleType = true;
236 } else static if (is(ti == Nullable!short)) {
237 enum bool isSupportedSimpleType = true;
238 } else static if (is(ti == Nullable!int)) {
239 enum bool isSupportedSimpleType = true;
240 } else static if (is(ti == Nullable!long)) {
241 enum bool isSupportedSimpleType = true;
242 } else static if (is(ti == Nullable!ubyte)) {
243 enum bool isSupportedSimpleType = true;
244 } else static if (is(ti == Nullable!ushort)) {
245 enum bool isSupportedSimpleType = true;
246 } else static if (is(ti == Nullable!uint)) {
247 enum bool isSupportedSimpleType = true;
248 } else static if (is(ti == Nullable!ulong)) {
249 enum bool isSupportedSimpleType = true;
250 } else static if (is(ti == Nullable!float)) {
251 enum bool isSupportedSimpleType = true;
252 } else static if (is(ti == Nullable!double)) {
253 enum bool isSupportedSimpleType = true;
254 } else static if (is(ti == string)) {
255 enum bool isSupportedSimpleType = true;
256 } else static if (is(ti == SysTime)) {
257 enum bool isSupportedSimpleType = true;
258 } else static if (is(ti == DateTime)) {
259 enum bool isSupportedSimpleType = true;
260 } else static if (is(ti == Date)) {
261 enum bool isSupportedSimpleType = true;
262 } else static if (is(ti == TimeOfDay)) {
263 enum bool isSupportedSimpleType = true;
264 } else static if (is(ti == Nullable!SysTime)) {
265 enum bool isSupportedSimpleType = true;
266 } else static if (is(ti == Nullable!DateTime)) {
267 enum bool isSupportedSimpleType = true;
268 } else static if (is(ti == Nullable!Date)) {
269 enum bool isSupportedSimpleType = true;
270 } else static if (is(ti == Nullable!TimeOfDay)) {
271 enum bool isSupportedSimpleType = true;
272 } else static if (is(ti == byte[])) {
273 enum bool isSupportedSimpleType = true;
274 } else static if (is(ti == ubyte[])) {
275 enum bool isSupportedSimpleType = true;
276 } else static if (true) {
277 enum bool isSupportedSimpleType = false;
278 }
279 }
280
281 PropertyMemberType getPropertyType(ti)() {
282 //pragma(msg, T.stringof);
283 //alias typeof(T) ti;
284 static if (is(ti == bool)) {
285 return PropertyMemberType.BOOL_TYPE;
286 } else static if (is(ti == byte)) {
287 return PropertyMemberType.BYTE_TYPE;
288 } else static if (is(ti == short)) {
289 return PropertyMemberType.SHORT_TYPE;
290 } else static if (is(ti == int)) {
291 return PropertyMemberType.INT_TYPE;
292 } else static if (is(ti == long)) {
293 return PropertyMemberType.LONG_TYPE;
294 } else static if (is(ti == ubyte)) {
295 return PropertyMemberType.UBYTE_TYPE;
296 } else static if (is(ti == ushort)) {
297 return PropertyMemberType.USHORT_TYPE;
298 } else static if (is(ti == uint)) {
299 return PropertyMemberType.UINT_TYPE;
300 } else static if (is(ti == ulong)) {
301 return PropertyMemberType.ULONG_TYPE;
302 } else static if (is(ti == float)) {
303 return PropertyMemberType.FLOAT_TYPE;
304 } else static if (is(ti == double)) {
305 return PropertyMemberType.DOUBLE_TYPE;
306 } else static if (is(ti == Nullable!byte)) {
307 return PropertyMemberType.NULLABLE_BYTE_TYPE;
308 } else static if (is(ti == Nullable!short)) {
309 return PropertyMemberType.NULLABLE_SHORT_TYPE;
310 } else static if (is(ti == Nullable!int)) {
311 return PropertyMemberType.NULLABLE_INT_TYPE;
312 } else static if (is(ti == Nullable!long)) {
313 return PropertyMemberType.NULLABLE_LONG_TYPE;
314 } else static if (is(ti == Nullable!ubyte)) {
315 return PropertyMemberType.NULLABLE_UBYTE_TYPE;
316 } else static if (is(ti == Nullable!ushort)) {
317 return PropertyMemberType.NULLABLE_USHORT_TYPE;
318 } else static if (is(ti == Nullable!uint)) {
319 return PropertyMemberType.NULLABLE_UINT_TYPE;
320 } else static if (is(ti == Nullable!ulong)) {
321 return PropertyMemberType.NULLABLE_ULONG_TYPE;
322 } else static if (is(ti == Nullable!float)) {
323 return PropertyMemberType.NULLABLE_FLOAT_TYPE;
324 } else static if (is(ti == Nullable!double)) {
325 return PropertyMemberType.NULLABLE_DOUBLE_TYPE;
326 } else static if (is(ti == string)) {
327 return PropertyMemberType.STRING_TYPE;
328 } else static if (is(ti == SysTime)) {
329 return PropertyMemberType.SYSTIME_TYPE;
330 } else static if (is(ti == DateTime)) {
331 return PropertyMemberType.DATETIME_TYPE;
332 } else static if (is(ti == Date)) {
333 return PropertyMemberType.DATE_TYPE;
334 } else static if (is(ti == TimeOfDay)) {
335 return PropertyMemberType.TIME_TYPE;
336 } else static if (is(ti == Nullable!SysTime)) {
337 return PropertyMemberType.NULLABLE_SYSTIME_TYPE;
338 } else static if (is(ti == Nullable!DateTime)) {
339 return PropertyMemberType.NULLABLE_DATETIME_TYPE;
340 } else static if (is(ti == Nullable!Date)) {
341 return PropertyMemberType.NULLABLE_DATE_TYPE;
342 } else static if (is(ti == Nullable!TimeOfDay)) {
343 return PropertyMemberType.NULLABLE_TIME_TYPE;
344 } else static if (is(ti == byte[])) {
345 return PropertyMemberType.BYTE_ARRAY_TYPE;
346 } else static if (is(ti == ubyte[])) {
347 return PropertyMemberType.UBYTE_ARRAY_TYPE;
348 } else static if (true) {
349 static assert (false, "has unsupported type " ~ ti.stringof);
350 }
351 }
352
353 PropertyMemberType getPropertyMemberType(T, string m)() {
354 alias typeof(__traits(getMember, T, m)) ti;
355 static if (is(ti == bool)) {
356 return PropertyMemberType.BOOL_TYPE;
357 } else static if (is(ti == byte)) {
358 return PropertyMemberType.BYTE_TYPE;
359 } else static if (is(ti == short)) {
360 return PropertyMemberType.SHORT_TYPE;
361 } else static if (is(ti == int)) {
362 return PropertyMemberType.INT_TYPE;
363 } else static if (is(ti == long)) {
364 return PropertyMemberType.LONG_TYPE;
365 } else static if (is(ti == ubyte)) {
366 return PropertyMemberType.UBYTE_TYPE;
367 } else static if (is(ti == ushort)) {
368 return PropertyMemberType.USHORT_TYPE;
369 } else static if (is(ti == uint)) {
370 return PropertyMemberType.UINT_TYPE;
371 } else static if (is(ti == ulong)) {
372 return PropertyMemberType.ULONG_TYPE;
373 } else static if (is(ti == float)) {
374 return PropertyMemberType.FLOAT_TYPE;
375 } else static if (is(ti == double)) {
376 return PropertyMemberType.DOUBLE_TYPE;
377 } else static if (is(ti == Nullable!byte)) {
378 return PropertyMemberType.NULLABLE_BYTE_TYPE;
379 } else static if (is(ti == Nullable!short)) {
380 return PropertyMemberType.NULLABLE_SHORT_TYPE;
381 } else static if (is(ti == Nullable!int)) {
382 return PropertyMemberType.NULLABLE_INT_TYPE;
383 } else static if (is(ti == Nullable!long)) {
384 return PropertyMemberType.NULLABLE_LONG_TYPE;
385 } else static if (is(ti == Nullable!ubyte)) {
386 return PropertyMemberType.NULLABLE_UBYTE_TYPE;
387 } else static if (is(ti == Nullable!ushort)) {
388 return PropertyMemberType.NULLABLE_USHORT_TYPE;
389 } else static if (is(ti == Nullable!uint)) {
390 return PropertyMemberType.NULLABLE_UINT_TYPE;
391 } else static if (is(ti == Nullable!ulong)) {
392 return PropertyMemberType.NULLABLE_ULONG_TYPE;
393 } else static if (is(ti == Nullable!float)) {
394 return PropertyMemberType.NULLABLE_FLOAT_TYPE;
395 } else static if (is(ti == Nullable!double)) {
396 return PropertyMemberType.NULLABLE_DOUBLE_TYPE;
397 } else static if (is(ti == string)) {
398 return PropertyMemberType.STRING_TYPE;
399 } else static if (is(ti == SysTime)) {
400 return PropertyMemberType.SYSTIME_TYPE;
401 } else static if (is(ti == DateTime)) {
402 return PropertyMemberType.DATETIME_TYPE;
403 } else static if (is(ti == Date)) {
404 return PropertyMemberType.DATE_TYPE;
405 } else static if (is(ti == TimeOfDay)) {
406 return PropertyMemberType.TIME_TYPE;
407 } else static if (is(ti == Nullable!SysTime)) {
408 return PropertyMemberType.NULLABLE_SYSTIME_TYPE;
409 } else static if (is(ti == Nullable!DateTime)) {
410 return PropertyMemberType.NULLABLE_DATETIME_TYPE;
411 } else static if (is(ti == Nullable!Date)) {
412 return PropertyMemberType.NULLABLE_DATE_TYPE;
413 } else static if (is(ti == Nullable!TimeOfDay)) {
414 return PropertyMemberType.NULLABLE_TIME_TYPE;
415 } else static if (is(ti == byte[])) {
416 return PropertyMemberType.BYTE_ARRAY_TYPE;
417 } else static if (is(ti == ubyte[])) {
418 return PropertyMemberType.UBYTE_ARRAY_TYPE;
419 } else static if (true) {
420 static assert (false, "Member " ~ m ~ " of class " ~ T.stringof ~ " has unsupported type " ~ ti.stringof);
421 }
422 }
423
424 string getPropertyReadCode(T, string m)() {
425 return "entity." ~ m;
426 }
427
428 string getPropertyReadCode(alias T)() {
429 return "entity." ~ T.stringof;
430 }
431
432 static immutable bool[] ColumnTypeCanHoldNulls =
433 [
434 false, //BOOL_TYPE // bool
435 false, //BYTE_TYPE, // byte
436 false, //SHORT_TYPE, // short
437 false, //INT_TYPE, // int
438 false, //LONG_TYPE, // long
439 false, //UBYTE_TYPE, // ubyte
440 false, //USHORT_TYPE, // ushort
441 false, //UINT_TYPE, // uint
442 false, //ULONG_TYPE, // ulong
443 true, //NULLABLE_BYTE_TYPE, // Nullable!byte
444 true, //NULLABLE_SHORT_TYPE, // Nullable!short
445 true, //NULLABLE_INT_TYPE, // Nullable!int
446 true, //NULLABLE_LONG_TYPE, // Nullable!long
447 true, //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
448 true, //NULLABLE_USHORT_TYPE,// Nullable!ushort
449 true, //NULLABLE_UINT_TYPE, // Nullable!uint
450 true, //NULLABLE_ULONG_TYPE, // Nullable!ulong
451 false,//FLOAT_TYPE, // float
452 false,//DOUBLE_TYPE, // double
453 true, //NULLABLE_FLOAT_TYPE, // Nullable!float
454 true, //NULLABLE_DOUBLE_TYPE,// Nullable!double
455 false, //STRING_TYPE // string -- treat as @NotNull by default
456 false, //SYSTIME_TYPE
457 false, //DATETIME_TYPE, // std.datetime.DateTime
458 false, //DATE_TYPE, // std.datetime.Date
459 false, //TIME_TYPE, // std.datetime.TimeOfDay
460 true, //NULLABLE_SYSTIME_TYPE
461 true, //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
462 true, //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
463 true, //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
464 true, //BYTE_ARRAY_TYPE, // byte[]
465 true, //UBYTE_ARRAY_TYPE, // ubyte[]
466 ];
467
468 bool isColumnTypeNullableByDefault(T, string m)() {
469 return ColumnTypeCanHoldNulls[getPropertyMemberType!(T,m)];
470 }
471
472 static immutable string[] ColumnTypeKeyIsSetCode =
473 [
474 "(%s != 0)", //BOOL_TYPE // bool
475 "(%s != 0)", //BYTE_TYPE, // byte
476 "(%s != 0)", //SHORT_TYPE, // short
477 "(%s != 0)", //INT_TYPE, // int
478 "(%s != 0)", //LONG_TYPE, // long
479 "(%s != 0)", //UBYTE_TYPE, // ubyte
480 "(%s != 0)", //USHORT_TYPE, // ushort
481 "(%s != 0)", //UINT_TYPE, // uint
482 "(%s != 0)", //ULONG_TYPE, // ulong
483 "(!%s.isNull)", //NULLABLE_BYTE_TYPE, // Nullable!byte
484 "(!%s.isNull)", //NULLABLE_SHORT_TYPE, // Nullable!short
485 "(!%s.isNull)", //NULLABLE_INT_TYPE, // Nullable!int
486 "(!%s.isNull)", //NULLABLE_LONG_TYPE, // Nullable!long
487 "(!%s.isNull)", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
488 "(!%s.isNull)", //NULLABLE_USHORT_TYPE,// Nullable!ushort
489 "(!%s.isNull)", //NULLABLE_UINT_TYPE, // Nullable!uint
490 "(!%s.isNull)", //NULLABLE_ULONG_TYPE, // Nullable!ulong
491 "(%s != 0)",//FLOAT_TYPE, // float
492 "(%s != 0)",//DOUBLE_TYPE, // double
493 "(!%s.isNull)", //NULLABLE_FLOAT_TYPE, // Nullable!float
494 "(!%s.isNull)", //NULLABLE_DOUBLE_TYPE,// Nullable!double
495 "(%s !is null)", //STRING_TYPE // string
496 "(%s != SysTime())", //SYSTIME_TYPE, // std.datetime.systime : SysTime
497 "(%s != DateTime())", //DATETIME_TYPE, // std.datetime.DateTime
498 "(%s != Date())", //DATE_TYPE, // std.datetime.Date
499 "(%s != TimeOfDay())", //TIME_TYPE, // std.datetime.TimeOfDay
500 "(!%s.isNull)", //NULLABLE_SYSTIME_TYPE, // Nullable!std.datetime.systime.SysTime
501 "(!%s.isNull)", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
502 "(!%s.isNull)", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
503 "(!%s.isNull)", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
504 "(%s !is null)", //BYTE_ARRAY_TYPE, // byte[]
505 "(%s !is null)", //UBYTE_ARRAY_TYPE, // ubyte[]
506 ];
507
508 string getColumnTypeKeyIsSetCode(T, string m)() {
509 return substituteParam(ColumnTypeKeyIsSetCode[getPropertyMemberType!(T,m)()], getPropertyReadCode!(T,m)());
510 }
511
512 static immutable string[] ColumnTypeIsNullCode =
513 [
514 "(false)", //BOOL_TYPE // bool
515 "(false)", //BYTE_TYPE, // byte
516 "(false)", //SHORT_TYPE, // short
517 "(false)", //INT_TYPE, // int
518 "(false)", //LONG_TYPE, // long
519 "(false)", //UBYTE_TYPE, // ubyte
520 "(false)", //USHORT_TYPE, // ushort
521 "(false)", //UINT_TYPE, // uint
522 "(false)", //ULONG_TYPE, // ulong
523 "(%s.isNull)", //NULLABLE_BYTE_TYPE, // Nullable!byte
524 "(%s.isNull)", //NULLABLE_SHORT_TYPE, // Nullable!short
525 "(%s.isNull)", //NULLABLE_INT_TYPE, // Nullable!int
526 "(%s.isNull)", //NULLABLE_LONG_TYPE, // Nullable!long
527 "(%s.isNull)", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
528 "(%s.isNull)", //NULLABLE_USHORT_TYPE,// Nullable!ushort
529 "(%s.isNull)", //NULLABLE_UINT_TYPE, // Nullable!uint
530 "(%s.isNull)", //NULLABLE_ULONG_TYPE, // Nullable!ulong
531 "(false)",//FLOAT_TYPE, // float
532 "(false)",//DOUBLE_TYPE, // double
533 "(%s.isNull)", //NULLABLE_FLOAT_TYPE, // Nullable!float
534 "(%s.isNull)", //NULLABLE_DOUBLE_TYPE,// Nullable!double
535 "(%s is null)", //STRING_TYPE // string
536 "(false)", //SYSTIME_TYPE
537 "(false)", //DATETIME_TYPE, // std.datetime.DateTime
538 "(false)", //DATE_TYPE, // std.datetime.Date
539 "(false)", //TIME_TYPE, // std.datetime.TimeOfDay
540 "(%s.isNull)", //NULLABLE_SYSTIME_TYPE
541 "(%s.isNull)", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
542 "(%s.isNull)", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
543 "(%s.isNull)", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
544 "(%s is null)", //BYTE_ARRAY_TYPE, // byte[]
545 "(%s is null)", //UBYTE_ARRAY_TYPE, // ubyte[]
546 ];
547
548 string getColumnTypeIsNullCode(T, string m)() {
549 return substituteParam(ColumnTypeIsNullCode[getPropertyMemberType!(T,m)()], getPropertyReadCode!(T,m)());
550 }
551
552 static immutable string[] ColumnTypeSetNullCode =
553 [
554 "bool nv;", // BOOL_TYPE // bool
555 "byte nv = 0;", //BYTE_TYPE, // byte
556 "short nv = 0;", //SHORT_TYPE, // short
557 "int nv = 0;", //INT_TYPE, // int
558 "long nv = 0;", //LONG_TYPE, // long
559 "ubyte nv = 0;", //UBYTE_TYPE, // ubyte
560 "ushort nv = 0;", //USHORT_TYPE, // ushort
561 "uint nv = 0;", //UINT_TYPE, // uint
562 "ulong nv = 0;", //ULONG_TYPE, // ulong
563 "Nullable!byte nv;", //NULLABLE_BYTE_TYPE, // Nullable!byte
564 "Nullable!short nv;", //NULLABLE_SHORT_TYPE, // Nullable!short
565 "Nullable!int nv;", //NULLABLE_INT_TYPE, // Nullable!int
566 "Nullable!long nv;", //NULLABLE_LONG_TYPE, // Nullable!long
567 "Nullable!ubyte nv;", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
568 "Nullable!ushort nv;", //NULLABLE_USHORT_TYPE,// Nullable!ushort
569 "Nullable!uint nv;", //NULLABLE_UINT_TYPE, // Nullable!uint
570 "Nullable!ulong nv;", //NULLABLE_ULONG_TYPE, // Nullable!ulong
571 "float nv = 0;",//FLOAT_TYPE, // float
572 "double nv = 0;",//DOUBLE_TYPE, // double
573 "Nullable!float nv;", //NULLABLE_FLOAT_TYPE, // Nullable!float
574 "Nullable!double nv;", //NULLABLE_DOUBLE_TYPE,// Nullable!double
575 "string nv;", //STRING_TYPE // string
576 "SysTime nv;", //SYSTIME_TYPE
577 "DateTime nv;", //DATETIME_TYPE, // std.datetime.DateTime
578 "Date nv;", //DATE_TYPE, // std.datetime.Date
579 "TimeOfDay nv;", //TIME_TYPE, // std.datetime.TimeOfDay
580 "Nullable!SysTime nv;", //NULLABLE_SYSTIME_TYPE
581 "Nullable!DateTime nv;", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
582 "Nullable!Date nv;", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
583 "Nullable!TimeOfDay nv;", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
584 "byte[] nv = null;", //BYTE_ARRAY_TYPE, // byte[]
585 "ubyte[] nv = null;", //UBYTE_ARRAY_TYPE, // ubyte[]
586 ];
587
588 static immutable string[] ColumnTypePropertyToVariant =
589 [
590 "Variant(%s)", //BOOL_TYPE // bool
591 "Variant(%s)", //BYTE_TYPE, // byte
592 "Variant(%s)", //SHORT_TYPE, // short
593 "Variant(%s)", //INT_TYPE, // int
594 "Variant(%s)", //LONG_TYPE, // long
595 "Variant(%s)", //UBYTE_TYPE, // ubyte
596 "Variant(%s)", //USHORT_TYPE, // ushort
597 "Variant(%s)", //UINT_TYPE, // uint
598 "Variant(%s)", //ULONG_TYPE, // ulong
599 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_BYTE_TYPE, // Nullable!byte
600 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_SHORT_TYPE, // Nullable!short
601 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_INT_TYPE, // Nullable!int
602 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_LONG_TYPE, // Nullable!long
603 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
604 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_USHORT_TYPE,// Nullable!ushort
605 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_UINT_TYPE, // Nullable!uint
606 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_ULONG_TYPE, // Nullable!ulong
607 "Variant(%s)",//FLOAT_TYPE, // float
608 "Variant(%s)",//DOUBLE_TYPE, // double
609 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_FLOAT_TYPE, // Nullable!float
610 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DOUBLE_TYPE,// Nullable!double
611 "Variant(%s)", //STRING_TYPE // string
612 "Variant(%s)", //SYSTIME_TYPE
613 "Variant(%s)", //DATETIME_TYPE, // std.datetime.DateTime
614 "Variant(%s)", //DATE_TYPE, // std.datetime.Date
615 "Variant(%s)", //TIME_TYPE, // std.datetime.TimeOfDay
616 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_SYSTIME_TYPE
617 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
618 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
619 "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
620 "Variant(%s)", //BYTE_ARRAY_TYPE, // byte[]
621 "Variant(%s)", //UBYTE_ARRAY_TYPE, // ubyte[]
622 ];
623
624 static immutable string[] ColumnTypeDatasetReaderCode =
625 [
626 "r.getBoolean(index)", //BOOL_TYPE, // bool
627 "r.getByte(index)", //BYTE_TYPE, // byte
628 "r.getShort(index)", //SHORT_TYPE, // short
629 "r.getInt(index)", //INT_TYPE, // int
630 "r.getLong(index)", //LONG_TYPE, // long
631 "r.getUbyte(index)", //UBYTE_TYPE, // ubyte
632 "r.getUshort(index)", //USHORT_TYPE, // ushort
633 "r.getUint(index)", //UINT_TYPE, // uint
634 "r.getUlong(index)", //ULONG_TYPE, // ulong
635 "Nullable!byte(r.getByte(index))", //NULLABLE_BYTE_TYPE, // Nullable!byte
636 "Nullable!short(r.getShort(index))", //NULLABLE_SHORT_TYPE, // Nullable!short
637 "Nullable!int(r.getInt(index))", //NULLABLE_INT_TYPE, // Nullable!int
638 "Nullable!long(r.getLong(index))", //NULLABLE_LONG_TYPE, // Nullable!long
639 "Nullable!ubyte(r.getUbyte(index))", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
640 "Nullable!ushort(r.getUshort(index))", //NULLABLE_USHORT_TYPE,// Nullable!ushort
641 "Nullable!uint(r.getUint(index))", //NULLABLE_UINT_TYPE, // Nullable!uint
642 "Nullable!ulong(r.getUlong(index))", //NULLABLE_ULONG_TYPE, // Nullable!ulong
643 "r.getFloat(index)",//FLOAT_TYPE, // float
644 "r.getDouble(index)",//DOUBLE_TYPE, // double
645 "Nullable!float(r.getFloat(index))", //NULLABLE_FLOAT_TYPE, // Nullable!float
646 "Nullable!double(r.getDouble(index))", //NULLABLE_DOUBLE_TYPE,// Nullable!double
647 "r.getString(index)", //STRING_TYPE // string
648 "r.getSysTime(index)", //SYSTIME_TYPE
649 "r.getDateTime(index)", //DATETIME_TYPE, // std.datetime.DateTime
650 "r.getDate(index)", //DATE_TYPE, // std.datetime.Date
651 "r.getTime(index)", //TIME_TYPE, // std.datetime.TimeOfDay
652 "Nullable!SysTime(r.getSysTime(index))", //NULLABLE_SYSTIME_TYPE
653 "Nullable!DateTime(r.getDateTime(index))", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
654 "Nullable!Date(r.getDate(index))", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
655 "Nullable!TimeOfDay(r.getTime(index))", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
656 "r.getBytes(index)", //BYTE_ARRAY_TYPE, // byte[]
657 "r.getUbytes(index)", //UBYTE_ARRAY_TYPE, // ubyte[]
658 ];
659
660 string getColumnTypeDatasetReadCode(T, string m)() {
661 return ColumnTypeDatasetReaderCode[getPropertyMemberType!(T,m)()];
662 }
663
664 string getVarTypeDatasetReadCode(T)() {
665 return ColumnTypeDatasetReaderCode[getPropertyType!T];
666 }
667
668 string getPropertyWriteCode(T, string m)() {
669 //immutable PropertyMemberKind kind = getPropertyMemberKind!(T, m)();
670 immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyMemberType!(T,m)()];
671 immutable string datasetReader = "(!r.isNull(index) ? " ~ getColumnTypeDatasetReadCode!(T, m)() ~ " : nv)";
672 return nullValueCode ~ "entity." ~ m ~ " = " ~ datasetReader ~ ";";
673 }
674
675 string getPropertyWriteCode(T)() {
676 //immutable PropertyMemberKind kind = getPropertyMemberKind!(T, m)();
677 immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyType!T];
678 immutable string datasetReader = "(!r.isNull(index) ? " ~ getVarTypeDatasetReadCode!T ~ " : nv)";
679 return nullValueCode ~ "a = " ~ datasetReader ~ ";";
680 }
681
682 /// returns array of field names
683 string[] getColumnNamesForType(T)() if (__traits(isPOD, T)) {
684 string[] res;
685 foreach(m; FieldNameTuple!T) {
686 static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
687 // skip non-public members
688 static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
689 static if (isSupportedSimpleType!(T, m)) {
690 res ~= m;
691 }
692 }
693 }
694 }
695 return res;
696 }
697
698 string getColumnReadCode(T, string m)() {
699 return "{" ~ getPropertyWriteCode!(T,m)() ~ "index++;}\n";
700 }
701
702 string getAllColumnsReadCode(T)() {
703 string res = "int index = 1;\n";
704 foreach(m; FieldNameTuple!T) {
705 static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
706 // skip non-public members
707 static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
708 static if (isSupportedSimpleType!(T, m)) {
709 res ~= getColumnReadCode!(T, m);
710 }
711 }
712 }
713 }
714 return res;
715 }
716
717 string getAllColumnsReadCode(T, fieldList...)() {
718 string res = "int index = 1;\n";
719 foreach(m; fieldList) {
720 res ~= getColumnReadCode!(T, m);
721 }
722 return res;
723 }
724
725 unittest {
726 struct User1 {
727 long id;
728 string name;
729 int flags;
730 }
731 //pragma(msg, "nullValueCode = " ~ ColumnTypeSetNullCode[getPropertyMemberType!(User, "id")()]);
732 //pragma(msg, "datasetReader = " ~ getColumnTypeDatasetReadCode!(User, "id")());
733 //pragma(msg, "getPropertyWriteCode: " ~ getPropertyWriteCode!(User, "id"));
734 //pragma(msg, "getAllColumnsReadCode:\n" ~ getAllColumnsReadCode!(User));
735 //static assert(getPropertyWriteCode!(User, "id") == "long nv = 0;entity.id = (!r.isNull(index) ? r.getLong(index) : nv);");
736 }
737
738 unittest {
739 struct User1 {
740 long id;
741 string name;
742 int flags;
743 }
744 static assert(getPropertyMemberType!(User1, "id")() == PropertyMemberType.LONG_TYPE);
745 static assert(getPropertyMemberType!(User1, "name")() == PropertyMemberType.STRING_TYPE);
746 //pragma(msg, "getPropertyMemberType unit test passed");
747 }
748
749
750
751 /// returns table name for struct type
752 string getTableNameForType(T)() if (__traits(isPOD, T)) {
753 return camelCaseToUnderscoreDelimited(T.stringof);
754 }
755
756 unittest {
757 struct User1 {
758 long id;
759 string name;
760 int flags;
761 }
762 static assert(getTableNameForType!User1() == "user1");
763 }
764
765 /// returns "SELECT <field list> FROM <table name>"
766 string generateSelectSQL(T)() {
767 return "SELECT " ~ join(getColumnNamesForType!(T)(), ",") ~ " FROM " ~ getTableNameForType!(T)();
768 }
769
770 unittest {
771 struct User1 {
772 long id;
773 string name;
774 int flags;
775 }
776 static assert(generateSelectSQL!User1() == "SELECT id,name,flags FROM user1");
777 }
778
779 string joinFieldList(fieldList...)() {
780 string res;
781 foreach(f; fieldList) {
782 if (res.length)
783 res ~= ",";
784 res ~= f;
785 }
786 return res;
787 }
788
789 /// returns "SELECT <field list> FROM <table name>"
790 string generateSelectSQL(T, fieldList...)() {
791 string res = "SELECT ";
792 res ~= joinFieldList!fieldList;
793 res ~= " FROM " ~ getTableNameForType!(T)();
794 return res;
795 }
796
797 unittest {
798 //pragma(msg, "column names: " ~ join(getColumnNamesForType!(User)(), ","));
799 //pragma(msg, "select SQL: " ~ generateSelectSQL!(User)());
800 }
801
802 /// returns "SELECT <field list> FROM <table name>"
803 string generateSelectForGetSQL(T)() {
804 string res = generateSelectSQL!T();
805 res ~= " WHERE id=";
806 return res;
807 }
808
809 string generateSelectForGetSQLWithFilter(T)() {
810 string res = generateSelectSQL!T();
811 res ~= " WHERE ";
812 return res;
813 }
814
815 T get(T)(Statement stmt, long id) {
816 T entity;
817 static immutable getSQL = generateSelectForGetSQL!T();
818 ResultSet r;
819 r = stmt.executeQuery(getSQL ~ to!string(id));
820 r.next();
821 mixin(getAllColumnsReadCode!T());
822 return entity;
823 }
824
825 T get(T)(Statement stmt, string filter) {
826 T entity;
827 static immutable getSQL = generateSelectForGetSQLWithFilter!T();
828 ResultSet r;
829 r = stmt.executeQuery(getSQL ~ filter);
830 r.next();
831 mixin(getAllColumnsReadCode!T());
832 return entity;
833 }
834
835 string getColumnTypeDatasetReadCodeByName(T, string m)() {
836 PropertyMemberType pmt = getPropertyMemberType!(T,m)();
837 final switch(pmt) with (PropertyMemberType) {
838 case BOOL_TYPE:
839 return `r.getBoolean("` ~ m ~ `")`;
840 case BYTE_TYPE:
841 return `r.getByte("` ~ m ~ `")`;
842 case SHORT_TYPE:
843 return `r.getShort("` ~ m ~ `")`;
844 case INT_TYPE:
845 return `r.getInt("` ~ m ~ `")`;
846 case LONG_TYPE:
847 return `r.getLong("` ~ m ~ `")`;
848 case UBYTE_TYPE:
849 return `r.getUbyte("` ~ m ~ `")`;
850 case USHORT_TYPE:
851 return `r.getUshort("` ~ m ~ `")`;
852 case UINT_TYPE:
853 return `r.getUint("` ~ m ~ `")`;
854 case ULONG_TYPE:
855 return `r.getUlong("` ~ m ~ `")`;
856 case FLOAT_TYPE:
857 return `r.getFloat("` ~ m ~ `")`;
858 case DOUBLE_TYPE:
859 return `r.getDouble("` ~ m ~ `")`;
860 case STRING_TYPE:
861 return `r.getString("` ~ m ~ `")`;
862 case DATE_TYPE:
863 return `r.getDate("` ~ m ~ `")`;
864 case TIME_TYPE:
865 return `r.getTime("` ~ m ~ `")`;
866 case SYSTIME_TYPE:
867 return `r.getSysTime("` ~ m ~ `")`;
868 case DATETIME_TYPE:
869 return `r.getDateTime("` ~ m ~ `")`;
870 case BYTE_ARRAY_TYPE:
871 return `r.getBytes("` ~ m ~ `")`;
872 case UBYTE_ARRAY_TYPE:
873 return `r.getUbytes("` ~ m ~ `")`;
874 case NULLABLE_BYTE_TYPE:
875 return `Nullable!byte(r.getByte("` ~ m ~ `"))`;
876 case NULLABLE_SHORT_TYPE:
877 return `Nullable!short(r.getShort("` ~ m ~ `"))`;
878 case NULLABLE_INT_TYPE:
879 return `Nullable!int(r.getInt("` ~ m ~ `"))`;
880 case NULLABLE_LONG_TYPE:
881 return `Nullable!long(r.getLong("` ~ m ~ `"))`;
882 case NULLABLE_UBYTE_TYPE:
883 return `Nullable!ubyte(r.getUbyte("` ~ m ~ `"))`;
884 case NULLABLE_USHORT_TYPE:
885 return `Nullable!ushort(r.getUshort("` ~ m ~ `"))`;
886 case NULLABLE_UINT_TYPE:
887 return `Nullable!uint(r.getUint("` ~ m ~ `"))`;
888 case NULLABLE_ULONG_TYPE:
889 return `Nullable!ulong(r.getUlong("` ~ m ~ `"))`;
890 case NULLABLE_FLOAT_TYPE:
891 return `Nullable!float(r.getFloat("` ~ m ~ `"))`;
892 case NULLABLE_DOUBLE_TYPE:
893 return `Nullable!double(r.getDouble("` ~ m ~ `"))`;
894 case NULLABLE_STRING_TYPE:
895 return `r.getString("` ~ m ~ `")`;
896 case NULLABLE_DATE_TYPE:
897 return `Nullable!Date(r.getDate("` ~ m ~ `"))`;
898 case NULLABLE_TIME_TYPE:
899 return `Nullable!Time(r.getTime("` ~ m ~ `"))`;
900 case NULLABLE_SYSTIME_TYPE:
901 return `Nullable!SysTime(r.getSysTime("` ~ m ~ `"))`;
902 case NULLABLE_DATETIME_TYPE:
903 return `Nullable!DateTime(r.getDateTime("` ~ m ~ `"))`;
904 }
905 }
906
907 string getPropertyWriteCodeByName(T, string m)() {
908 immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyMemberType!(T,m)()];
909 immutable string propertyWriter = nullValueCode ~ "entity." ~ m ~ " = " ~ getColumnTypeDatasetReadCodeByName!(T, m)() ~ ";\n";
910 return propertyWriter ~ "if (r.wasNull) entity." ~ m ~ " = nv;";
911 }
912
913 string getColumnReadCodeByName(T, string m)() {
914 return "{" ~ getPropertyWriteCodeByName!(T,m)() ~ "}\n";
915 }
916
917 string getAllColumnsReadCodeByName(T)() {
918 string res;
919 foreach(m; FieldNameTuple!T) {
920 static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
921 // skip non-public members
922 static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
923 static if (isSupportedSimpleType!(T, m)) {
924 res ~= getColumnReadCodeByName!(T, m);
925 }
926 }
927 }
928 }
929 return res;
930 }
931
932 /**
933 * Extract a row from the result set as the specified type.
934 * Requires that next has already been checked.
935 * Can be used for example to extract rows from executing a PreparedStatement.
936 */
937 T get(T)(ResultSet r) {
938 T entity;
939 mixin(getAllColumnsReadCodeByName!T());
940 return entity;
941 }
942
943 /// range for select query
944 struct select(T, fieldList...) if (__traits(isPOD, T)) {
945 T entity;
946 private Statement stmt;
947 private ResultSet r;
948 static immutable selectSQL = generateSelectSQL!(T, fieldList)();
949 string whereCondSQL;
950 string orderBySQL;
951 this(Statement stmt) {
952 this.stmt = stmt;
953 }
954 ref select where(string whereCond) {
955 whereCondSQL = " WHERE " ~ whereCond;
956 return this;
957 }
958 ref select orderBy(string order) {
959 orderBySQL = " ORDER BY " ~ order;
960 return this;
961 }
962 ref T front() {
963 return entity;
964 }
965 void popFront() {
966 }
967 @property bool empty() {
968 if (!r)
969 r = stmt.executeQuery(selectSQL ~ whereCondSQL ~ orderBySQL);
970 if (!r.next())
971 return true;
972 mixin(getAllColumnsReadCode!(T, fieldList));
973 return false;
974 }
975 ~this() {
976 if (r)
977 r.close();
978 }
979 }
980
981 /// returns "INSERT INTO <table name> (<field list>) VALUES (value list)
982 string generateInsertSQL(T)() {
983 string res = "INSERT INTO " ~ getTableNameForType!(T)();
984 string []values;
985 foreach(m; FieldNameTuple!T) {
986 if (m != "id") {
987 static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
988 // skip non-public members
989 static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
990 values ~= m;
991 }
992 }
993 }
994 }
995 res ~= "(" ~ join(values, ",") ~ ")";
996 res ~= " VALUES ";
997 return res;
998 }
999
1000 string addFieldValue(T)(string m) {
1001 string tmp = `{Variant v = o.`~m~`;`;
1002 tmp ~= `static if (isColumnTypeNullableByDefault!(T, "`~m~`")()) {`;
1003 tmp ~= ` if(o.`~m~`.isNull) {`;
1004 tmp ~= ` values ~= "NULL";`;
1005 tmp ~= ` } else {`;
1006 tmp ~= ` values ~= "'" ~ to!string(o.` ~ m ~ `) ~ "'";`;
1007 tmp ~= `}} else {`;
1008 tmp ~= ` values ~= "'" ~ to!string(o.` ~ m ~ `) ~ "'";`;
1009 tmp ~= `}}`;
1010 return tmp;
1011 // return `values ~= "'" ~ to!string(o.` ~ m ~ `) ~ "'";`;
1012 }
1013
1014 bool insert(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
1015 auto insertSQL = generateInsertSQL!(T)();
1016 string []values;
1017 foreach(m; FieldNameTuple!T) {
1018 if (m != "id") {
1019 static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
1020 // skip non-public members
1021 static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
1022 // pragma(msg,addFieldValue!(T)(m));
1023 mixin(addFieldValue!(T)(m));
1024 }
1025 }
1026 }
1027 }
1028 insertSQL ~= "(" ~ join(values, ",") ~ ")";
1029 Variant insertId;
1030 stmt.executeUpdate(insertSQL, insertId);
1031 o.id = insertId.get!long;
1032 return true;
1033 }
1034
1035 /// returns "UPDATE <table name> SET field1=value1 WHERE id=id
1036 string generateUpdateSQL(T)() {
1037 string res = "UPDATE " ~ getTableNameForType!(T)();
1038 string []values;
1039 foreach(m; FieldNameTuple!T) {
1040 if (m != "id") {
1041 static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
1042 // skip non-public members
1043 static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
1044 values ~= m;
1045 }
1046 }
1047 }
1048 }
1049 res ~= " SET ";
1050 return res;
1051 }
1052
1053 string addUpdateValue(T)(string m) {
1054 return `values ~= "` ~ m ~ `=\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
1055 }
1056
1057 bool update(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
1058 auto updateSQL = generateUpdateSQL!(T)();
1059 string []values;
1060 foreach(m; FieldNameTuple!T) {
1061 if (m != "id") {
1062 static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
1063 // skip non-public members
1064 static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
1065
1066 // static if(typeof(__traits(getMember, T, m)) == function) {
1067 // pragma(msg, "Ignoring function: "~m~"()");
1068 // }
1069
1070 // static if(is(__traits(getMember, T, m) == function)) {
1071 // pragma(msg, "Ignoring function: "~m~"()");
1072 // } else {
1073 // pragma(msg, addUpdateValue!(T)(m));
1074 // //mixin(addUpdateValue!(T)(m));
1075 // }
1076
1077 static if (__traits(getOverloads, T, m).length > 0) {
1078 // even if the struct/class doesn't have and override (such as opAssign) the compiler
1079 // can potentially add one. See: https://dlang.org/library/std/traits/has_elaborate_assign.html
1080 pragma(msg, "Ignoring 'override "~m~"()'");
1081 } else {
1082 pragma(msg, addUpdateValue!(T)(m));
1083 mixin(addUpdateValue!(T)(m));
1084 }
1085 }
1086 }
1087 }
1088 }
1089 updateSQL ~= join(values, ",");
1090 updateSQL ~= mixin(`" WHERE id="~ to!string(o.id) ~ ";"`);
1091 Variant updateId;
1092 stmt.executeUpdate(updateSQL, updateId);
1093 return true;
1094 }
1095
1096 /// returns "DELETE FROM <table name> WHERE id=id
1097 string generateDeleteSQL(T)() {
1098 string res = "DELETE FROM " ~ getTableNameForType!(T)();
1099 return res;
1100 }
1101
1102 bool remove(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
1103 auto deleteSQL = generateDeleteSQL!(T)();
1104 deleteSQL ~= mixin(`" WHERE id="~ to!string(o.id) ~ ";"`);
1105 Variant deleteId;
1106 stmt.executeUpdate(deleteSQL, deleteId);
1107 return true;
1108 }
1109
1110 template isSupportedSimpleTypeRef(M) {
1111 alias typeof(M) ti;
1112 static if (!__traits(isRef, M)) {
1113 enum bool isSupportedSimpleTypeRef = false;
1114 } else static if (is(ti == bool)) {
1115 enum bool isSupportedSimpleType = true;
1116 } else static if (is(ti == byte)) {
1117 enum bool isSupportedSimpleType = true;
1118 } else static if (is(ti == short)) {
1119 enum bool isSupportedSimpleType = true;
1120 } else static if (is(ti == int)) {
1121 enum bool isSupportedSimpleType = true;
1122 } else static if (is(ti == long)) {
1123 enum bool isSupportedSimpleType = true;
1124 } else static if (is(ti == ubyte)) {
1125 enum bool isSupportedSimpleType = true;
1126 } else static if (is(ti == ushort)) {
1127 enum bool isSupportedSimpleType = true;
1128 } else static if (is(ti == uint)) {
1129 enum bool isSupportedSimpleType = true;
1130 } else static if (is(ti == ulong)) {
1131 enum bool isSupportedSimpleType = true;
1132 } else static if (is(ti == float)) {
1133 enum bool isSupportedSimpleType = true;
1134 } else static if (is(ti == double)) {
1135 enum bool isSupportedSimpleType = true;
1136 } else static if (is(ti == Nullable!byte)) {
1137 enum bool isSupportedSimpleType = true;
1138 } else static if (is(ti == Nullable!short)) {
1139 enum bool isSupportedSimpleType = true;
1140 } else static if (is(ti == Nullable!int)) {
1141 enum bool isSupportedSimpleType = true;
1142 } else static if (is(ti == Nullable!long)) {
1143 enum bool isSupportedSimpleType = true;
1144 } else static if (is(ti == Nullable!ubyte)) {
1145 enum bool isSupportedSimpleType = true;
1146 } else static if (is(ti == Nullable!ushort)) {
1147 enum bool isSupportedSimpleType = true;
1148 } else static if (is(ti == Nullable!uint)) {
1149 enum bool isSupportedSimpleType = true;
1150 } else static if (is(ti == Nullable!ulong)) {
1151 enum bool isSupportedSimpleType = true;
1152 } else static if (is(ti == Nullable!float)) {
1153 enum bool isSupportedSimpleType = true;
1154 } else static if (is(ti == Nullable!double)) {
1155 enum bool isSupportedSimpleType = true;
1156 } else static if (is(ti == string)) {
1157 enum bool isSupportedSimpleType = true;
1158 } else static if (is(ti == SysTime)) {
1159 enum bool isSupportedSimpleType = true;
1160 } else static if (is(ti == DateTime)) {
1161 enum bool isSupportedSimpleType = true;
1162 } else static if (is(ti == Date)) {
1163 enum bool isSupportedSimpleType = true;
1164 } else static if (is(ti == TimeOfDay)) {
1165 enum bool isSupportedSimpleType = true;
1166 } else static if (is(ti == Nullable!SysTime)) {
1167 enum bool isSupportedSimpleType = true;
1168 } else static if (is(ti == Nullable!DateTime)) {
1169 enum bool isSupportedSimpleType = true;
1170 } else static if (is(ti == Nullable!Date)) {
1171 enum bool isSupportedSimpleType = true;
1172 } else static if (is(ti == Nullable!TimeOfDay)) {
1173 enum bool isSupportedSimpleType = true;
1174 } else static if (is(ti == byte[])) {
1175 enum bool isSupportedSimpleType = true;
1176 } else static if (is(ti == ubyte[])) {
1177 enum bool isSupportedSimpleType = true;
1178 } else static if (true) {
1179 enum bool isSupportedSimpleType = false;
1180 }
1181 }
1182
1183 // TODO: use better way to count parameters
1184 int paramCount(destList...)() {
1185 int res = 0;
1186 foreach(p; destList) {
1187 res++;
1188 }
1189 return res;
1190 }
1191
1192 bool isSupportedSimpleTypeRefList(destList...)() {
1193 foreach(p; destList) {
1194 static if (!isSupportedSimpleTypeRef!p) {
1195 return false;
1196 }
1197 }
1198 return true;
1199 }
1200
1201 struct select(Args...) {//if (isSupportedSimpleTypeRefList!Args())
1202 private Statement stmt;
1203 private ResultSet r;
1204 private void delegate() _copyFunction;
1205 private int rowIndex;
1206
1207 this(Args...)(Statement stmt, string sql, ref Args args) {
1208 this.stmt = stmt;
1209 selectSQL = sql;
1210 _copyFunction = delegate() {
1211 foreach(i, ref a; args) {
1212 int index = i + 1;
1213 mixin(getPropertyWriteCode!(typeof(a)));
1214 }
1215 };
1216 }
1217
1218 string selectSQL;
1219 string whereCondSQL;
1220 string orderBySQL;
1221 ref select where(string whereCond) {
1222 whereCondSQL = " WHERE " ~ whereCond;
1223 return this;
1224 }
1225 ref select orderBy(string order) {
1226 orderBySQL = " ORDER BY " ~ order;
1227 return this;
1228 }
1229 int front() {
1230 return rowIndex;
1231 }
1232 void popFront() {
1233 rowIndex++;
1234 }
1235 @property bool empty() {
1236 if (!r)
1237 r = stmt.executeQuery(selectSQL ~ whereCondSQL ~ orderBySQL);
1238 if (!r.next())
1239 return true;
1240 _copyFunction();
1241 return false;
1242 }
1243 ~this() {
1244 if (r)
1245 r.close();
1246 }
1247
1248 }