Unofficial Dub Package Documentation 
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 }

Page generated by adrdox