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

Page generated by adrdox