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/pgsqlddbc.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 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 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 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!DateTime NullableDateTime;
74 alias Nullable!Date NullableDate;
75 alias Nullable!TimeOfDay NullableTimeOfDay;
76 
77 /// Wrapper around string, to distinguish between Null and NotNull fields: string is NotNull, String is Null -- same interface as in Nullable
78 // Looks ugly, but I tried `typedef string String`, but it is deprecated; `alias string String` cannot be distinguished from just string. How to define String better?
79 struct String
80 {
81     string _value;
82 
83     /**
84     Returns $(D true) if and only if $(D this) is in the null state.
85     */
86     @property bool isNull() const pure nothrow @safe
87     {
88         return _value is null;
89     }
90 
91     /**
92     Forces $(D this) to the null state.
93     */
94     void nullify()
95     {
96         _value = null;
97     }
98 
99     alias _value this;
100 }
101 
102 enum PropertyMemberType : int {
103     BOOL_TYPE,    // bool
104     BYTE_TYPE,    // byte
105     SHORT_TYPE,   // short
106     INT_TYPE,     // int
107     LONG_TYPE,    // long
108     UBYTE_TYPE,   // ubyte
109     USHORT_TYPE,  // ushort
110     UINT_TYPE,    // uint
111     ULONG_TYPE,   // ulong
112     NULLABLE_BYTE_TYPE,  // Nullable!byte
113     NULLABLE_SHORT_TYPE, // Nullable!short
114     NULLABLE_INT_TYPE,   // Nullable!int
115     NULLABLE_LONG_TYPE,  // Nullable!long
116     NULLABLE_UBYTE_TYPE, // Nullable!ubyte
117     NULLABLE_USHORT_TYPE,// Nullable!ushort
118     NULLABLE_UINT_TYPE,  // Nullable!uint
119     NULLABLE_ULONG_TYPE, // Nullable!ulong
120     FLOAT_TYPE,   // float
121     DOUBLE_TYPE,   // double
122     NULLABLE_FLOAT_TYPE, // Nullable!float
123     NULLABLE_DOUBLE_TYPE,// Nullable!double
124     STRING_TYPE,   // string
125     NULLABLE_STRING_TYPE,   // nullable string - String struct
126     DATETIME_TYPE, // std.datetime.DateTime
127     DATE_TYPE, // std.datetime.Date
128     TIME_TYPE, // std.datetime.TimeOfDay
129     NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
130     NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
131     NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
132     BYTE_ARRAY_TYPE, // byte[]
133     UBYTE_ARRAY_TYPE, // ubyte[]
134 }
135 
136 /// converts camel case MyEntityName to my_entity_name
137 string camelCaseToUnderscoreDelimited(immutable string s) {
138     string res;
139     bool lastLower = false;
140     static import std.ascii;
141 
142     foreach(ch; s) {
143         if (ch >= 'A' && ch <= 'Z') {
144             if (lastLower) {
145                 lastLower = false;
146                 res ~= "_";
147             }
148             res ~= std.ascii.toLower(ch);
149         } else if (ch >= 'a' && ch <= 'z') {
150             lastLower = true;
151             res ~= ch;
152         } else {
153             res ~= ch;
154         }
155     }
156     return res;
157 }
158 
159 unittest {
160     static assert(camelCaseToUnderscoreDelimited("User") == "user");
161     static assert(camelCaseToUnderscoreDelimited("MegaTableName") == "mega_table_name");
162 }
163 
164 
165 template isSupportedSimpleType(T, string m) {
166     alias typeof(__traits(getMember, T, m)) ti;
167     static if (is(ti == function)) {
168         static if (is(ReturnType!(ti) == bool)) {
169             enum bool isSupportedSimpleType = true;
170         } else static if (is(ReturnType!(ti) == byte)) {
171             enum bool isSupportedSimpleType = true;
172         } else static if (is(ReturnType!(ti) == short)) {
173             enum bool isSupportedSimpleType = true;
174         } else static if (is(ReturnType!(ti) == int)) {
175             enum bool isSupportedSimpleType = true;
176         } else static if (is(ReturnType!(ti) == long)) {
177             enum bool isSupportedSimpleType = true;
178         } else static if (is(ReturnType!(ti) == ubyte)) {
179             enum bool isSupportedSimpleType = true;
180         } else static if (is(ReturnType!(ti) == ushort)) {
181             enum bool isSupportedSimpleType = true;
182         } else static if (is(ReturnType!(ti) == uint)) {
183             enum bool isSupportedSimpleType = true;
184         } else static if (is(ReturnType!(ti) == ulong)) {
185             enum bool isSupportedSimpleType = true;
186         } else static if (is(ReturnType!(ti) == float)) {
187             enum bool isSupportedSimpleType = true;
188         } else static if (is(ReturnType!(ti) == double)) {
189             enum bool isSupportedSimpleType = true;
190         } else static if (is(ReturnType!(ti) == Nullable!byte)) {
191             enum bool isSupportedSimpleType = true;
192         } else static if (is(ReturnType!(ti) == Nullable!short)) {
193             enum bool isSupportedSimpleType = true;
194         } else static if (is(ReturnType!(ti) == Nullable!int)) {
195             enum bool isSupportedSimpleType = true;
196         } else static if (is(ReturnType!(ti) == Nullable!long)) {
197             enum bool isSupportedSimpleType = true;
198         } else static if (is(ReturnType!(ti) == Nullable!ubyte)) {
199             enum bool isSupportedSimpleType = true;
200         } else static if (is(ReturnType!(ti) == Nullable!ushort)) {
201             enum bool isSupportedSimpleType = true;
202         } else static if (is(ReturnType!(ti) == Nullable!uint)) {
203             enum bool isSupportedSimpleType = true;
204         } else static if (is(ReturnType!(ti) == Nullable!ulong)) {
205             enum bool isSupportedSimpleType = true;
206         } else static if (is(ReturnType!(ti) == Nullable!float)) {
207             enum bool isSupportedSimpleType = true;
208         } else static if (is(ReturnType!(ti) == Nullable!double)) {
209             enum bool isSupportedSimpleType = true;
210         } else static if (is(ReturnType!(ti) == string)) {
211             enum bool isSupportedSimpleType = true;
212         } else static if (is(ReturnType!(ti) == String)) {
213             enum bool isSupportedSimpleType = true;
214         } else static if (is(ReturnType!(ti) == DateTime)) {
215             enum bool isSupportedSimpleType = true;
216         } else static if (is(ReturnType!(ti) == Date)) {
217             enum bool isSupportedSimpleType = true;
218         } else static if (is(ReturnType!(ti) == TimeOfDay)) {
219             enum bool isSupportedSimpleType = true;
220         } else static if (is(ReturnType!(ti) == Nullable!DateTime)) {
221             enum bool isSupportedSimpleType = true;
222         } else static if (is(ReturnType!(ti) == Nullable!Date)) {
223             enum bool isSupportedSimpleType = true;
224         } else static if (is(ReturnType!(ti) == Nullable!TimeOfDay)) {
225             enum bool isSupportedSimpleType = true;
226         } else static if (is(ReturnType!(ti) == byte[])) {
227             enum bool isSupportedSimpleType = true;
228         } else static if (is(ReturnType!(ti) == ubyte[])) {
229             enum bool isSupportedSimpleType = true;
230         } else static if (true) {
231             enum bool isSupportedSimpleType = false;
232         }
233     } else static if (is(ti == bool)) {
234         enum bool isSupportedSimpleType = true;
235     } else static if (is(ti == byte)) {
236         enum bool isSupportedSimpleType = true;
237     } else static if (is(ti == short)) {
238         enum bool isSupportedSimpleType = true;
239     } else static if (is(ti == int)) {
240         enum bool isSupportedSimpleType = true;
241     } else static if (is(ti == long)) {
242         enum bool isSupportedSimpleType = true;
243     } else static if (is(ti == ubyte)) {
244         enum bool isSupportedSimpleType = true;
245     } else static if (is(ti == ushort)) {
246         enum bool isSupportedSimpleType = true;
247     } else static if (is(ti == uint)) {
248         enum bool isSupportedSimpleType = true;
249     } else static if (is(ti == ulong)) {
250         enum bool isSupportedSimpleType = true;
251     } else static if (is(ti == float)) {
252         enum bool isSupportedSimpleType = true;
253     } else static if (is(ti == double)) {
254         enum bool isSupportedSimpleType = true;
255     } else static if (is(ti == Nullable!byte)) {
256         enum bool isSupportedSimpleType = true;
257     } else static if (is(ti == Nullable!short)) {
258         enum bool isSupportedSimpleType = true;
259     } else static if (is(ti == Nullable!int)) {
260         enum bool isSupportedSimpleType = true;
261     } else static if (is(ti == Nullable!long)) {
262         enum bool isSupportedSimpleType = true;
263     } else static if (is(ti == Nullable!ubyte)) {
264         enum bool isSupportedSimpleType = true;
265     } else static if (is(ti == Nullable!ushort)) {
266         enum bool isSupportedSimpleType = true;
267     } else static if (is(ti == Nullable!uint)) {
268         enum bool isSupportedSimpleType = true;
269     } else static if (is(ti == Nullable!ulong)) {
270         enum bool isSupportedSimpleType = true;
271     } else static if (is(ti == Nullable!float)) {
272         enum bool isSupportedSimpleType = true;
273     } else static if (is(ti == Nullable!double)) {
274         enum bool isSupportedSimpleType = true;
275     } else static if (is(ti == string)) {
276         enum bool isSupportedSimpleType = true;
277     } else static if (is(ti == String)) {
278         enum bool isSupportedSimpleType = true;
279     } else static if (is(ti == DateTime)) {
280         enum bool isSupportedSimpleType = true;
281     } else static if (is(ti == Date)) {
282         enum bool isSupportedSimpleType = true;
283     } else static if (is(ti == TimeOfDay)) {
284         enum bool isSupportedSimpleType = true;
285     } else static if (is(ti == Nullable!DateTime)) {
286         enum bool isSupportedSimpleType = true;
287     } else static if (is(ti == Nullable!Date)) {
288         enum bool isSupportedSimpleType = true;
289     } else static if (is(ti == Nullable!TimeOfDay)) {
290         enum bool isSupportedSimpleType = true;
291     } else static if (is(ti == byte[])) {
292         enum bool isSupportedSimpleType = true;
293     } else static if (is(ti == ubyte[])) {
294         enum bool isSupportedSimpleType = true;
295     } else static if (true) {
296         enum bool isSupportedSimpleType = false;
297     }
298 }
299 
300 PropertyMemberType getPropertyType(ti)() {
301     //pragma(msg, T.stringof);
302     //alias typeof(T) ti;
303 	static if (is(ti == bool)) {
304 		return PropertyMemberType.BOOL_TYPE;
305     } else static if (is(ti == byte)) {
306         return PropertyMemberType.BYTE_TYPE;
307     } else static if (is(ti == short)) {
308         return PropertyMemberType.SHORT_TYPE;
309     } else static if (is(ti == int)) {
310         return PropertyMemberType.INT_TYPE;
311     } else static if (is(ti == long)) {
312         return PropertyMemberType.LONG_TYPE;
313     } else static if (is(ti == ubyte)) {
314         return PropertyMemberType.UBYTE_TYPE;
315     } else static if (is(ti == ushort)) {
316         return PropertyMemberType.USHORT_TYPE;
317     } else static if (is(ti == uint)) {
318         return PropertyMemberType.UINT_TYPE;
319     } else static if (is(ti == ulong)) {
320         return PropertyMemberType.ULONG_TYPE;
321     } else static if (is(ti == float)) {
322         return PropertyMemberType.FLOAT_TYPE;
323     } else static if (is(ti == double)) {
324         return PropertyMemberType.DOUBLE_TYPE;
325     } else static if (is(ti == Nullable!byte)) {
326         return PropertyMemberType.NULLABLE_BYTE_TYPE;
327     } else static if (is(ti == Nullable!short)) {
328         return PropertyMemberType.NULLABLE_SHORT_TYPE;
329     } else static if (is(ti == Nullable!int)) {
330         return PropertyMemberType.NULLABLE_INT_TYPE;
331     } else static if (is(ti == Nullable!long)) {
332         return PropertyMemberType.NULLABLE_LONG_TYPE;
333     } else static if (is(ti == Nullable!ubyte)) {
334         return PropertyMemberType.NULLABLE_UBYTE_TYPE;
335     } else static if (is(ti == Nullable!ushort)) {
336         return PropertyMemberType.NULLABLE_USHORT_TYPE;
337     } else static if (is(ti == Nullable!uint)) {
338         return PropertyMemberType.NULLABLE_UINT_TYPE;
339     } else static if (is(ti == Nullable!ulong)) {
340         return PropertyMemberType.NULLABLE_ULONG_TYPE;
341     } else static if (is(ti == Nullable!float)) {
342         return PropertyMemberType.NULLABLE_FLOAT_TYPE;
343     } else static if (is(ti == Nullable!double)) {
344         return PropertyMemberType.NULLABLE_DOUBLE_TYPE;
345     } else static if (is(ti == string)) {
346         return PropertyMemberType.STRING_TYPE;
347     } else static if (is(ti == String)) {
348         return PropertyMemberType.NULLABLE_STRING_TYPE;
349     } else static if (is(ti == DateTime)) {
350         return PropertyMemberType.DATETIME_TYPE;
351     } else static if (is(ti == Date)) {
352         return PropertyMemberType.DATE_TYPE;
353     } else static if (is(ti == TimeOfDay)) {
354         return PropertyMemberType.TIME_TYPE;
355     } else static if (is(ti == Nullable!DateTime)) {
356         return PropertyMemberType.NULLABLE_DATETIME_TYPE;
357     } else static if (is(ti == Nullable!Date)) {
358         return PropertyMemberType.NULLABLE_DATE_TYPE;
359     } else static if (is(ti == Nullable!TimeOfDay)) {
360         return PropertyMemberType.NULLABLE_TIME_TYPE;
361     } else static if (is(ti == byte[])) {
362         return PropertyMemberType.BYTE_ARRAY_TYPE;
363     } else static if (is(ti == ubyte[])) {
364         return PropertyMemberType.UBYTE_ARRAY_TYPE;
365     } else static if (true) {
366         assert (false, "has unsupported type " ~ ti.stringof);
367     }
368 }
369 
370 PropertyMemberType getPropertyMemberType(T, string m)() {
371     alias typeof(__traits(getMember, T, m)) ti;
372     static if (is(ti == bool)) {
373         return PropertyMemberType.BOOL_TYPE;
374     } else static if (is(ti == byte)) {
375         return PropertyMemberType.BYTE_TYPE;
376     } else static if (is(ti == short)) {
377         return PropertyMemberType.SHORT_TYPE;
378     } else static if (is(ti == int)) {
379         return PropertyMemberType.INT_TYPE;
380     } else static if (is(ti == long)) {
381         return PropertyMemberType.LONG_TYPE;
382     } else static if (is(ti == ubyte)) {
383         return PropertyMemberType.UBYTE_TYPE;
384     } else static if (is(ti == ushort)) {
385         return PropertyMemberType.USHORT_TYPE;
386     } else static if (is(ti == uint)) {
387         return PropertyMemberType.UINT_TYPE;
388     } else static if (is(ti == ulong)) {
389         return PropertyMemberType.ULONG_TYPE;
390     } else static if (is(ti == float)) {
391         return PropertyMemberType.FLOAT_TYPE;
392     } else static if (is(ti == double)) {
393         return PropertyMemberType.DOUBLE_TYPE;
394     } else static if (is(ti == Nullable!byte)) {
395         return PropertyMemberType.NULLABLE_BYTE_TYPE;
396     } else static if (is(ti == Nullable!short)) {
397         return PropertyMemberType.NULLABLE_SHORT_TYPE;
398     } else static if (is(ti == Nullable!int)) {
399         return PropertyMemberType.NULLABLE_INT_TYPE;
400     } else static if (is(ti == Nullable!long)) {
401         return PropertyMemberType.NULLABLE_LONG_TYPE;
402     } else static if (is(ti == Nullable!ubyte)) {
403         return PropertyMemberType.NULLABLE_UBYTE_TYPE;
404     } else static if (is(ti == Nullable!ushort)) {
405         return PropertyMemberType.NULLABLE_USHORT_TYPE;
406     } else static if (is(ti == Nullable!uint)) {
407         return PropertyMemberType.NULLABLE_UINT_TYPE;
408     } else static if (is(ti == Nullable!ulong)) {
409         return PropertyMemberType.NULLABLE_ULONG_TYPE;
410     } else static if (is(ti == Nullable!float)) {
411         return PropertyMemberType.NULLABLE_FLOAT_TYPE;
412     } else static if (is(ti == Nullable!double)) {
413         return PropertyMemberType.NULLABLE_DOUBLE_TYPE;
414     } else static if (is(ti == string)) {
415         return PropertyMemberType.STRING_TYPE;
416     } else static if (is(ti == String)) {
417         return PropertyMemberType.NULLABLE_STRING_TYPE;
418     } else static if (is(ti == DateTime)) {
419         return PropertyMemberType.DATETIME_TYPE;
420     } else static if (is(ti == Date)) {
421         return PropertyMemberType.DATE_TYPE;
422     } else static if (is(ti == TimeOfDay)) {
423         return PropertyMemberType.TIME_TYPE;
424     } else static if (is(ti == Nullable!DateTime)) {
425         return PropertyMemberType.NULLABLE_DATETIME_TYPE;
426     } else static if (is(ti == Nullable!Date)) {
427         return PropertyMemberType.NULLABLE_DATE_TYPE;
428     } else static if (is(ti == Nullable!TimeOfDay)) {
429         return PropertyMemberType.NULLABLE_TIME_TYPE;
430     } else static if (is(ti == byte[])) {
431         return PropertyMemberType.BYTE_ARRAY_TYPE;
432     } else static if (is(ti == ubyte[])) {
433         return PropertyMemberType.UBYTE_ARRAY_TYPE;
434     } else static if (true) {
435         assert (false, "Member " ~ m ~ " of class " ~ T.stringof ~ " has unsupported type " ~ ti.stringof);
436     }
437 }
438 
439 string getPropertyReadCode(T, string m)() {
440     return "entity." ~ m;
441 }
442 
443 string getPropertyReadCode(alias T)() {
444     return "entity." ~ T.stringof;
445 }
446 
447 static immutable bool[] ColumnTypeCanHoldNulls = 
448 [
449     false, //BOOL_TYPE     // bool
450     false, //BYTE_TYPE,    // byte
451     false, //SHORT_TYPE,   // short
452     false, //INT_TYPE,     // int
453     false, //LONG_TYPE,    // long
454     false, //UBYTE_TYPE,   // ubyte
455     false, //USHORT_TYPE,  // ushort
456     false, //UINT_TYPE,    // uint
457     false, //ULONG_TYPE,   // ulong
458     true, //NULLABLE_BYTE_TYPE,  // Nullable!byte
459     true, //NULLABLE_SHORT_TYPE, // Nullable!short
460     true, //NULLABLE_INT_TYPE,   // Nullable!int
461     true, //NULLABLE_LONG_TYPE,  // Nullable!long
462     true, //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
463     true, //NULLABLE_USHORT_TYPE,// Nullable!ushort
464     true, //NULLABLE_UINT_TYPE,  // Nullable!uint
465     true, //NULLABLE_ULONG_TYPE, // Nullable!ulong
466     false,//FLOAT_TYPE,   // float
467     false,//DOUBLE_TYPE,   // double
468     true, //NULLABLE_FLOAT_TYPE, // Nullable!float
469     true, //NULLABLE_DOUBLE_TYPE,// Nullable!double
470     false, //STRING_TYPE   // string  -- treat as @NotNull by default
471     true, //NULLABLE_STRING_TYPE   // String
472     false, //DATETIME_TYPE, // std.datetime.DateTime
473     false, //DATE_TYPE, // std.datetime.Date
474     false, //TIME_TYPE, // std.datetime.TimeOfDay
475     true, //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
476     true, //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
477     true, //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
478     true, //BYTE_ARRAY_TYPE, // byte[]
479     true, //UBYTE_ARRAY_TYPE, // ubyte[]
480 ];
481 
482 bool isColumnTypeNullableByDefault(T, string m)() {
483     return ColumnTypeCanHoldNulls[getPropertyMemberType!(T,m)];
484 }
485 
486 static immutable string[] ColumnTypeKeyIsSetCode = 
487 [
488     "(%s != 0)", //BOOL_TYPE     // bool
489     "(%s != 0)", //BYTE_TYPE,    // byte
490     "(%s != 0)", //SHORT_TYPE,   // short
491     "(%s != 0)", //INT_TYPE,     // int
492     "(%s != 0)", //LONG_TYPE,    // long
493     "(%s != 0)", //UBYTE_TYPE,   // ubyte
494     "(%s != 0)", //USHORT_TYPE,  // ushort
495     "(%s != 0)", //UINT_TYPE,    // uint
496     "(%s != 0)", //ULONG_TYPE,   // ulong
497     "(!%s.isNull)", //NULLABLE_BYTE_TYPE,  // Nullable!byte
498     "(!%s.isNull)", //NULLABLE_SHORT_TYPE, // Nullable!short
499     "(!%s.isNull)", //NULLABLE_INT_TYPE,   // Nullable!int
500     "(!%s.isNull)", //NULLABLE_LONG_TYPE,  // Nullable!long
501     "(!%s.isNull)", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
502     "(!%s.isNull)", //NULLABLE_USHORT_TYPE,// Nullable!ushort
503     "(!%s.isNull)", //NULLABLE_UINT_TYPE,  // Nullable!uint
504     "(!%s.isNull)", //NULLABLE_ULONG_TYPE, // Nullable!ulong
505     "(%s != 0)",//FLOAT_TYPE,   // float
506     "(%s != 0)",//DOUBLE_TYPE,   // double
507     "(!%s.isNull)", //NULLABLE_FLOAT_TYPE, // Nullable!float
508     "(!%s.isNull)", //NULLABLE_DOUBLE_TYPE,// Nullable!double
509     "(%s !is null)", //STRING_TYPE   // string
510     "(%s !is null)", //NULLABLE_STRING_TYPE   // String
511     "(%s != DateTime())", //DATETIME_TYPE, // std.datetime.DateTime
512     "(%s != Date())", //DATE_TYPE, // std.datetime.Date
513     "(%s != TimeOfDay())", //TIME_TYPE, // std.datetime.TimeOfDay
514     "(!%s.isNull)", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
515     "(!%s.isNull)", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
516     "(!%s.isNull)", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
517     "(%s !is null)", //BYTE_ARRAY_TYPE, // byte[]
518     "(%s !is null)", //UBYTE_ARRAY_TYPE, // ubyte[]
519 ];
520 
521 string getColumnTypeKeyIsSetCode(T, string m)() {
522     return substituteParam(ColumnTypeKeyIsSetCode[getPropertyMemberType!(T,m)()], getPropertyReadCode!(T,m)());
523 }
524 
525 static immutable string[] ColumnTypeIsNullCode = 
526 [
527     "(false)", //BOOL_TYPE     // bool
528     "(false)", //BYTE_TYPE,    // byte
529     "(false)", //SHORT_TYPE,   // short
530     "(false)", //INT_TYPE,     // int
531     "(false)", //LONG_TYPE,    // long
532     "(false)", //UBYTE_TYPE,   // ubyte
533     "(false)", //USHORT_TYPE,  // ushort
534     "(false)", //UINT_TYPE,    // uint
535     "(false)", //ULONG_TYPE,   // ulong
536     "(%s.isNull)", //NULLABLE_BYTE_TYPE,  // Nullable!byte
537     "(%s.isNull)", //NULLABLE_SHORT_TYPE, // Nullable!short
538     "(%s.isNull)", //NULLABLE_INT_TYPE,   // Nullable!int
539     "(%s.isNull)", //NULLABLE_LONG_TYPE,  // Nullable!long
540     "(%s.isNull)", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
541     "(%s.isNull)", //NULLABLE_USHORT_TYPE,// Nullable!ushort
542     "(%s.isNull)", //NULLABLE_UINT_TYPE,  // Nullable!uint
543     "(%s.isNull)", //NULLABLE_ULONG_TYPE, // Nullable!ulong
544     "(false)",//FLOAT_TYPE,   // float
545     "(false)",//DOUBLE_TYPE,   // double
546     "(%s.isNull)", //NULLABLE_FLOAT_TYPE, // Nullable!float
547     "(%s.isNull)", //NULLABLE_DOUBLE_TYPE,// Nullable!double
548     "(%s is null)", //STRING_TYPE   // string
549     "(%s is null)", //NULLABLE_STRING_TYPE   // String
550     "(false)", //DATETIME_TYPE, // std.datetime.DateTime
551     "(false)", //DATE_TYPE, // std.datetime.Date
552     "(false)", //TIME_TYPE, // std.datetime.TimeOfDay
553     "(%s.isNull)", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
554     "(%s.isNull)", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
555     "(%s.isNull)", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
556     "(%s is null)", //BYTE_ARRAY_TYPE, // byte[]
557     "(%s is null)", //UBYTE_ARRAY_TYPE, // ubyte[]
558 ];
559 
560 string getColumnTypeIsNullCode(T, string m)() {
561     return substituteParam(ColumnTypeIsNullCode[getPropertyMemberType!(T,m)()], getPropertyReadCode!(T,m)());
562 }
563 
564 static immutable string[] ColumnTypeSetNullCode = 
565 [
566     "bool nv;", // BOOL_TYPE   // bool
567     "byte nv = 0;", //BYTE_TYPE,    // byte
568     "short nv = 0;", //SHORT_TYPE,   // short
569     "int nv = 0;", //INT_TYPE,     // int
570     "long nv = 0;", //LONG_TYPE,    // long
571     "ubyte nv = 0;", //UBYTE_TYPE,   // ubyte
572     "ushort nv = 0;", //USHORT_TYPE,  // ushort
573     "uint nv = 0;", //UINT_TYPE,    // uint
574     "ulong nv = 0;", //ULONG_TYPE,   // ulong
575     "Nullable!byte nv;", //NULLABLE_BYTE_TYPE,  // Nullable!byte
576     "Nullable!short nv;", //NULLABLE_SHORT_TYPE, // Nullable!short
577     "Nullable!int nv;", //NULLABLE_INT_TYPE,   // Nullable!int
578     "Nullable!long nv;", //NULLABLE_LONG_TYPE,  // Nullable!long
579     "Nullable!ubyte nv;", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
580     "Nullable!ushort nv;", //NULLABLE_USHORT_TYPE,// Nullable!ushort
581     "Nullable!uint nv;", //NULLABLE_UINT_TYPE,  // Nullable!uint
582     "Nullable!ulong nv;", //NULLABLE_ULONG_TYPE, // Nullable!ulong
583     "float nv = 0;",//FLOAT_TYPE,   // float
584     "double nv = 0;",//DOUBLE_TYPE,   // double
585     "Nullable!float nv;", //NULLABLE_FLOAT_TYPE, // Nullable!float
586     "Nullable!double nv;", //NULLABLE_DOUBLE_TYPE,// Nullable!double
587     "string nv;", //STRING_TYPE   // string
588     "string nv;", //NULLABLE_STRING_TYPE   // String
589     "DateTime nv;", //DATETIME_TYPE, // std.datetime.DateTime
590     "Date nv;", //DATE_TYPE, // std.datetime.Date
591     "TimeOfDay nv;", //TIME_TYPE, // std.datetime.TimeOfDay
592     "Nullable!DateTime nv;", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
593     "Nullable!Date nv;", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
594     "Nullable!TimeOfDay nv;", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
595     "byte[] nv = null;", //BYTE_ARRAY_TYPE, // byte[]
596     "ubyte[] nv = null;", //UBYTE_ARRAY_TYPE, // ubyte[]
597 ];
598 
599 static immutable string[] ColumnTypePropertyToVariant = 
600 [
601     "Variant(%s)", //BOOL_TYPE     // bool
602     "Variant(%s)", //BYTE_TYPE,    // byte
603     "Variant(%s)", //SHORT_TYPE,   // short
604     "Variant(%s)", //INT_TYPE,     // int
605     "Variant(%s)", //LONG_TYPE,    // long
606     "Variant(%s)", //UBYTE_TYPE,   // ubyte
607     "Variant(%s)", //USHORT_TYPE,  // ushort
608     "Variant(%s)", //UINT_TYPE,    // uint
609     "Variant(%s)", //ULONG_TYPE,   // ulong
610     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_BYTE_TYPE,  // Nullable!byte
611     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_SHORT_TYPE, // Nullable!short
612     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_INT_TYPE,   // Nullable!int
613     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_LONG_TYPE,  // Nullable!long
614     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
615     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_USHORT_TYPE,// Nullable!ushort
616     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_UINT_TYPE,  // Nullable!uint
617     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_ULONG_TYPE, // Nullable!ulong
618     "Variant(%s)",//FLOAT_TYPE,   // float
619     "Variant(%s)",//DOUBLE_TYPE,   // double
620     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_FLOAT_TYPE, // Nullable!float
621     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DOUBLE_TYPE,// Nullable!double
622     "Variant(%s)", //STRING_TYPE   // string
623     "Variant(%s)", //NULLABLE_STRING_TYPE   // String
624     "Variant(%s)", //DATETIME_TYPE, // std.datetime.DateTime
625     "Variant(%s)", //DATE_TYPE, // std.datetime.Date
626     "Variant(%s)", //TIME_TYPE, // std.datetime.TimeOfDay
627     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
628     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
629     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
630     "Variant(%s)", //BYTE_ARRAY_TYPE, // byte[]
631     "Variant(%s)", //UBYTE_ARRAY_TYPE, // ubyte[]
632 ];
633 
634 static immutable string[] ColumnTypeDatasetReaderCode = 
635 [
636     "r.getBoolean(index)", //BOOL_TYPE,    // bool
637     "r.getByte(index)", //BYTE_TYPE,    // byte
638     "r.getShort(index)", //SHORT_TYPE,   // short
639     "r.getInt(index)", //INT_TYPE,     // int
640     "r.getLong(index)", //LONG_TYPE,    // long
641     "r.getUbyte(index)", //UBYTE_TYPE,   // ubyte
642     "r.getUshort(index)", //USHORT_TYPE,  // ushort
643     "r.getUint(index)", //UINT_TYPE,    // uint
644     "r.getUlong(index)", //ULONG_TYPE,   // ulong
645     "Nullable!byte(r.getByte(index))", //NULLABLE_BYTE_TYPE,  // Nullable!byte
646     "Nullable!short(r.getShort(index))", //NULLABLE_SHORT_TYPE, // Nullable!short
647     "Nullable!int(r.getInt(index))", //NULLABLE_INT_TYPE,   // Nullable!int
648     "Nullable!long(r.getLong(index))", //NULLABLE_LONG_TYPE,  // Nullable!long
649     "Nullable!ubyte(r.getUbyte(index))", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
650     "Nullable!ushort(r.getUshort(index))", //NULLABLE_USHORT_TYPE,// Nullable!ushort
651     "Nullable!uint(r.getUint(index))", //NULLABLE_UINT_TYPE,  // Nullable!uint
652     "Nullable!ulong(r.getUlong(index))", //NULLABLE_ULONG_TYPE, // Nullable!ulong
653     "r.getFloat(index)",//FLOAT_TYPE,   // float
654     "r.getDouble(index)",//DOUBLE_TYPE,   // double
655     "Nullable!float(r.getFloat(index))", //NULLABLE_FLOAT_TYPE, // Nullable!float
656     "Nullable!double(r.getDouble(index))", //NULLABLE_DOUBLE_TYPE,// Nullable!double
657     "r.getString(index)", //STRING_TYPE   // string
658     "r.getString(index)", //NULLABLE_STRING_TYPE   // String
659     "r.getDateTime(index)", //DATETIME_TYPE, // std.datetime.DateTime
660     "r.getDate(index)", //DATE_TYPE, // std.datetime.Date
661     "r.getTime(index)", //TIME_TYPE, // std.datetime.TimeOfDay
662     "Nullable!DateTime(r.getDateTime(index))", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
663     "Nullable!Date(r.getDate(index))", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
664     "Nullable!TimeOfDay(r.getTime(index))", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
665     "r.getBytes(index)", //BYTE_ARRAY_TYPE, // byte[]
666     "r.getUbytes(index)", //UBYTE_ARRAY_TYPE, // ubyte[]
667 ];
668 
669 string getColumnTypeDatasetReadCode(T, string m)() {
670     return ColumnTypeDatasetReaderCode[getPropertyMemberType!(T,m)()];
671 }
672 
673 string getVarTypeDatasetReadCode(T)() {
674     return ColumnTypeDatasetReaderCode[getPropertyType!T];
675 }
676 
677 string getPropertyWriteCode(T, string m)() {
678     //immutable PropertyMemberKind kind = getPropertyMemberKind!(T, m)();
679     immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyMemberType!(T,m)()];
680     immutable string datasetReader = "(!r.isNull(index) ? " ~ getColumnTypeDatasetReadCode!(T, m)() ~ " : nv)";
681     return nullValueCode ~ "entity." ~ m ~ " = " ~ datasetReader ~ ";";
682 }
683 
684 string getPropertyWriteCode(T)() {
685     //immutable PropertyMemberKind kind = getPropertyMemberKind!(T, m)();
686     immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyType!T];
687     immutable string datasetReader = "(!r.isNull(index) ? " ~ getVarTypeDatasetReadCode!T ~ " : nv)";
688     return nullValueCode ~ "a = " ~ datasetReader ~ ";";
689 }
690 
691 /// returns array of field names
692 string[] getColumnNamesForType(T)()  if (__traits(isPOD, T)) {
693     string[] res;
694     foreach(m; __traits(allMembers, T)) {
695         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
696             // skip non-public members
697             static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
698                 static if (isSupportedSimpleType!(T, m)) {
699                     res ~= m;
700                 }
701             }
702         }
703     }
704     return res;
705 }
706 
707 string getColumnReadCode(T, string m)() {
708     return "{" ~ getPropertyWriteCode!(T,m)() ~ "index++;}\n";
709 }
710 
711 string getAllColumnsReadCode(T)() {
712     string res = "int index = 1;\n";
713     foreach(m; __traits(allMembers, T)) {
714         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
715             // skip non-public members
716             static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
717                 static if (isSupportedSimpleType!(T, m)) {
718                     res ~= getColumnReadCode!(T, m);
719                 }
720             }
721         }
722     }
723     return res;
724 }
725 
726 string getAllColumnsReadCode(T, fieldList...)() {
727     string res = "int index = 1;\n";
728     foreach(m; fieldList) {
729         res ~= getColumnReadCode!(T, m);
730     }
731     return res;
732 }
733 
734 unittest {
735     struct User1 {
736         long id;
737         string name;
738         int flags;
739     }
740     //pragma(msg, "nullValueCode = " ~ ColumnTypeSetNullCode[getPropertyMemberType!(User, "id")()]);
741     //pragma(msg, "datasetReader = " ~ getColumnTypeDatasetReadCode!(User, "id")());
742     //pragma(msg, "getPropertyWriteCode: " ~ getPropertyWriteCode!(User, "id"));
743     //pragma(msg, "getAllColumnsReadCode:\n" ~ getAllColumnsReadCode!(User));
744     //static assert(getPropertyWriteCode!(User, "id") == "long nv = 0;entity.id = (!r.isNull(index) ? r.getLong(index) : nv);");
745 }
746 
747 unittest {
748     struct User1 {
749         long id;
750         string name;
751         int flags;
752     }
753     static assert(getPropertyMemberType!(User1, "id")() == PropertyMemberType.LONG_TYPE);
754     static assert(getPropertyMemberType!(User1, "name")() == PropertyMemberType.STRING_TYPE);
755     //pragma(msg, "getPropertyMemberType unit test passed");
756 }
757 
758 
759 
760 /// returns table name for struct type
761 string getTableNameForType(T)() if (__traits(isPOD, T)) {
762     return camelCaseToUnderscoreDelimited(T.stringof);
763 }
764 
765 unittest {
766     struct User1 {
767         long id;
768         string name;
769         int flags;
770     }
771     static assert(getTableNameForType!User1() == "user1");
772 }
773 
774 /// returns "SELECT <field list> FROM <table name>"
775 string generateSelectSQL(T)() {
776     return "SELECT " ~ join(getColumnNamesForType!(T)(), ",") ~ " FROM " ~ getTableNameForType!(T)();
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 DATETIME_TYPE:
867             return `r.getDateTime("` ~ m ~ `")`;
868         case BYTE_ARRAY_TYPE:
869             return `r.getBytes("` ~ m ~ `")`;
870         case UBYTE_ARRAY_TYPE:
871             return `r.getUbytes("` ~ m ~ `")`;
872         case NULLABLE_BYTE_TYPE:
873             return `Nullable!byte(r.getByte("` ~ m ~ `"))`;
874         case NULLABLE_SHORT_TYPE:
875             return `Nullable!short(r.getShort("` ~ m ~ `"))`;
876         case NULLABLE_INT_TYPE:
877             return `Nullable!int(r.getInt("` ~ m ~ `"))`;
878         case NULLABLE_LONG_TYPE:
879             return `Nullable!long(r.getLong("` ~ m ~ `"))`;
880         case NULLABLE_UBYTE_TYPE:
881             return `Nullable!ubyte(r.getUbyte("` ~ m ~ `"))`;
882         case NULLABLE_USHORT_TYPE:
883             return `Nullable!ushort(r.getUshort("` ~ m ~ `"))`;
884         case NULLABLE_UINT_TYPE:
885             return `Nullable!uint(r.getUint("` ~ m ~ `"))`;
886         case NULLABLE_ULONG_TYPE:
887             return `Nullable!ulong(r.getUlong("` ~ m ~ `"))`;
888         case NULLABLE_FLOAT_TYPE:
889             return `Nullable!float(r.getFloat("` ~ m ~ `"))`;
890         case NULLABLE_DOUBLE_TYPE:
891             return `Nullable!double(r.getDouble("` ~ m ~ `"))`;
892         case NULLABLE_STRING_TYPE:
893             return `r.getString("` ~ m ~ `")`;
894         case NULLABLE_DATE_TYPE:
895             return `Nullable!Date(r.getDate("` ~ m ~ `"))`;
896         case NULLABLE_TIME_TYPE:
897             return `Nullable!Time(r.getTime("` ~ m ~ `"))`;
898         case NULLABLE_DATETIME_TYPE:
899             return `Nullable!DateTime(r.getDateTime("` ~ m ~ `"))`;
900     }
901 }
902 
903 string getPropertyWriteCodeByName(T, string m)() {
904     immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyMemberType!(T,m)()];
905     immutable string propertyWriter = nullValueCode ~ "entity." ~ m ~ " = " ~ getColumnTypeDatasetReadCodeByName!(T, m)() ~ ";\n";
906     return propertyWriter ~ "if (r.wasNull) entity." ~ m ~ " = nv;";
907 }
908 
909 string getColumnReadCodeByName(T, string m)() {
910     return "{" ~ getPropertyWriteCodeByName!(T,m)() ~ "}\n";
911 }
912 
913 string getAllColumnsReadCodeByName(T)() {
914     string res;
915     foreach(m; __traits(allMembers, T)) {
916         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
917             // skip non-public members
918             static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
919                 static if (isSupportedSimpleType!(T, m)) {
920                     res ~= getColumnReadCodeByName!(T, m);
921                 }
922             }
923         }
924     }
925     return res;
926 }
927 
928 /**
929  * Extract a row from the result set as the specified type.
930  * Requires that next has already been checked.
931  * Can be used for example to extract rows from executing a PreparedStatement.
932  */
933 T get(T)(ResultSet r) {
934     T entity;
935     mixin(getAllColumnsReadCodeByName!T());
936     return entity;
937 }
938 
939 /// range for select query
940 struct select(T, fieldList...) if (__traits(isPOD, T)) {
941   T entity;
942   private Statement stmt;
943   private ResultSet r;
944   static immutable selectSQL = generateSelectSQL!(T, fieldList)();
945   string whereCondSQL;
946   string orderBySQL;
947   this(Statement stmt) {
948     this.stmt = stmt;
949   }
950   ref select where(string whereCond) {
951     whereCondSQL = " WHERE " ~ whereCond;
952     return this;
953   }
954   ref select orderBy(string order) {
955     orderBySQL = " ORDER BY " ~ order;
956     return this;
957   }
958   ref T front() {
959     return entity;
960   }
961   void popFront() {
962   }
963   @property bool empty() {
964     if (!r)
965       r = stmt.executeQuery(selectSQL ~ whereCondSQL ~ orderBySQL);
966     if (!r.next())
967       return true;
968     mixin(getAllColumnsReadCode!(T, fieldList));
969     return false;
970   }
971   ~this() {
972     if (r)
973       r.close();
974   }
975 }
976 
977 /// returns "INSERT INTO <table name> (<field list>) VALUES (value list)
978 string generateInsertSQL(T)() {
979     string res = "INSERT INTO " ~ getTableNameForType!(T)();
980     string []values;
981     foreach(m; __traits(allMembers, T)) {
982       if (m != "id") {
983         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
984           // skip non-public members
985           static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
986             values ~= m;
987           }
988         }
989       }
990     }
991     res ~= "(" ~ join(values, ",") ~ ")";
992     res ~= " VALUES ";
993     return res;
994 }
995 
996 string addFieldValue(T)(string m) {
997   string tmp = `{Variant v = o.`~m~`;`;
998   tmp ~=  `static if (isColumnTypeNullableByDefault!(T, "`~m~`")()) {`;
999   tmp ~= `	if(o.`~m~`.isNull) {`;
1000   tmp ~= `		values ~= "NULL";`;
1001   tmp ~= `	} else {`;
1002   tmp ~= `		values ~= "\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
1003   tmp ~= `}} else {`;
1004   tmp ~= `		values ~= "\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
1005   tmp ~= `}}`;
1006   return tmp;
1007   // return `values ~= "\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
1008 }
1009 
1010 bool insert(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
1011     auto insertSQL = generateInsertSQL!(T)();
1012     string []values;
1013     foreach(m; __traits(allMembers, T)) {
1014       if (m != "id") {
1015         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
1016           // skip non-public members
1017           static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
1018             // pragma(msg,addFieldValue!(T)(m));
1019             mixin(addFieldValue!(T)(m));
1020           }
1021         }
1022       }
1023     }
1024     insertSQL ~= "(" ~ join(values, ",") ~ ")";
1025     Variant insertId;
1026     stmt.executeUpdate(insertSQL, insertId);
1027     o.id = insertId.get!long;
1028     return true;
1029 }
1030 
1031 /// returns "UPDATE <table name> SET field1=value1 WHERE id=id
1032 string generateUpdateSQL(T)() {
1033   string res = "UPDATE " ~ getTableNameForType!(T)();
1034   string []values;
1035   foreach(m; __traits(allMembers, T)) {
1036     if (m != "id") {
1037       static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
1038         // skip non-public members
1039         static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
1040           values ~= m;
1041         }
1042       }
1043     }
1044   }
1045   res ~= " SET ";
1046   return res;
1047 }
1048 
1049 string addUpdateValue(T)(string m) {
1050   return `values ~= "` ~ m ~ `=\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
1051 }
1052 
1053 bool update(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
1054     auto updateSQL = generateUpdateSQL!(T)();
1055     string []values;
1056     foreach(m; __traits(allMembers, T)) {
1057       if (m != "id") {
1058         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
1059           // skip non-public members
1060           static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
1061             // pragma(msg, addUpdateValue!(T)(m));
1062             mixin(addUpdateValue!(T)(m));
1063           }
1064         }
1065       }
1066     }
1067     updateSQL ~= join(values, ",");
1068     updateSQL ~= mixin(`" WHERE id="~ to!string(o.id) ~ ";"`);
1069     Variant updateId;
1070     stmt.executeUpdate(updateSQL, updateId);
1071     return true;
1072 }
1073 
1074 /// returns "DELETE FROM <table name> WHERE id=id
1075 string generateDeleteSQL(T)() {
1076   string res = "DELETE FROM " ~ getTableNameForType!(T)();
1077   return res;
1078 }
1079 
1080 bool remove(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
1081   auto deleteSQL = generateDeleteSQL!(T)();
1082   deleteSQL ~= mixin(`" WHERE id="~ to!string(o.id) ~ ";"`);
1083   Variant deleteId;
1084   stmt.executeUpdate(deleteSQL, deleteId);
1085   return true;
1086 }
1087 
1088 template isSupportedSimpleTypeRef(M) {
1089     alias typeof(M) ti;
1090     static if (!__traits(isRef, M)) {
1091         enum bool isSupportedSimpleTypeRef = false;
1092     } else static if (is(ti == bool)) {
1093         enum bool isSupportedSimpleType = true;
1094     } else static if (is(ti == byte)) {
1095         enum bool isSupportedSimpleType = true;
1096     } else static if (is(ti == short)) {
1097         enum bool isSupportedSimpleType = true;
1098     } else static if (is(ti == int)) {
1099         enum bool isSupportedSimpleType = true;
1100     } else static if (is(ti == long)) {
1101         enum bool isSupportedSimpleType = true;
1102     } else static if (is(ti == ubyte)) {
1103         enum bool isSupportedSimpleType = true;
1104     } else static if (is(ti == ushort)) {
1105         enum bool isSupportedSimpleType = true;
1106     } else static if (is(ti == uint)) {
1107         enum bool isSupportedSimpleType = true;
1108     } else static if (is(ti == ulong)) {
1109         enum bool isSupportedSimpleType = true;
1110     } else static if (is(ti == float)) {
1111         enum bool isSupportedSimpleType = true;
1112     } else static if (is(ti == double)) {
1113         enum bool isSupportedSimpleType = true;
1114     } else static if (is(ti == Nullable!byte)) {
1115         enum bool isSupportedSimpleType = true;
1116     } else static if (is(ti == Nullable!short)) {
1117         enum bool isSupportedSimpleType = true;
1118     } else static if (is(ti == Nullable!int)) {
1119         enum bool isSupportedSimpleType = true;
1120     } else static if (is(ti == Nullable!long)) {
1121         enum bool isSupportedSimpleType = true;
1122     } else static if (is(ti == Nullable!ubyte)) {
1123         enum bool isSupportedSimpleType = true;
1124     } else static if (is(ti == Nullable!ushort)) {
1125         enum bool isSupportedSimpleType = true;
1126     } else static if (is(ti == Nullable!uint)) {
1127         enum bool isSupportedSimpleType = true;
1128     } else static if (is(ti == Nullable!ulong)) {
1129         enum bool isSupportedSimpleType = true;
1130     } else static if (is(ti == Nullable!float)) {
1131         enum bool isSupportedSimpleType = true;
1132     } else static if (is(ti == Nullable!double)) {
1133         enum bool isSupportedSimpleType = true;
1134     } else static if (is(ti == string)) {
1135         enum bool isSupportedSimpleType = true;
1136     } else static if (is(ti == String)) {
1137         enum bool isSupportedSimpleType = true;
1138     } else static if (is(ti == DateTime)) {
1139         enum bool isSupportedSimpleType = true;
1140     } else static if (is(ti == Date)) {
1141         enum bool isSupportedSimpleType = true;
1142     } else static if (is(ti == TimeOfDay)) {
1143         enum bool isSupportedSimpleType = true;
1144     } else static if (is(ti == Nullable!DateTime)) {
1145         enum bool isSupportedSimpleType = true;
1146     } else static if (is(ti == Nullable!Date)) {
1147         enum bool isSupportedSimpleType = true;
1148     } else static if (is(ti == Nullable!TimeOfDay)) {
1149         enum bool isSupportedSimpleType = true;
1150     } else static if (is(ti == byte[])) {
1151         enum bool isSupportedSimpleType = true;
1152     } else static if (is(ti == ubyte[])) {
1153         enum bool isSupportedSimpleType = true;
1154     } else static if (true) {
1155         enum bool isSupportedSimpleType = false;
1156     }
1157 }
1158 
1159 // TODO: use better way to count parameters
1160 int paramCount(destList...)() {
1161     int res = 0;
1162     foreach(p; destList) {
1163         res++;
1164     }
1165     return res;
1166 }
1167 
1168 bool isSupportedSimpleTypeRefList(destList...)() {
1169     foreach(p; destList) {
1170         static if (!isSupportedSimpleTypeRef!p) {
1171             return false;
1172         }
1173     }
1174     return true;
1175 }
1176 
1177 struct select(Args...)  {//if (isSupportedSimpleTypeRefList!Args())
1178     private Statement stmt;
1179     private ResultSet r;
1180     private void delegate() _copyFunction;
1181     private int rowIndex;
1182     
1183     this(Args...)(Statement stmt, string sql, ref Args args) {
1184         this.stmt = stmt;
1185         selectSQL = sql;
1186         _copyFunction = delegate() {
1187             foreach(i, ref a; args) {
1188                 int index = i + 1;
1189                 mixin(getPropertyWriteCode!(typeof(a)));
1190             }
1191         };
1192     }
1193 
1194     string selectSQL;
1195     string whereCondSQL;
1196     string orderBySQL;
1197     ref select where(string whereCond) {
1198         whereCondSQL = " WHERE " ~ whereCond;
1199         return this;
1200     }
1201     ref select orderBy(string order) {
1202         orderBySQL = " ORDER BY " ~ order;
1203         return this;
1204     }
1205     int front() {
1206         return rowIndex;
1207     }
1208     void popFront() {
1209         rowIndex++;
1210     }
1211     @property bool empty() {
1212         if (!r)
1213             r = stmt.executeQuery(selectSQL ~ whereCondSQL ~ orderBySQL);
1214         if (!r.next())
1215             return true;
1216         _copyFunction();
1217         return false;
1218     }
1219     ~this() {
1220         if (r)
1221             r.close();
1222     }
1223 
1224 }

Page generated by adrdox