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.stdio;
52 import std.algorithm;
53 import std.traits;
54 import std.typecons;
55 import std.conv;
56 import std.datetime;
57 import std.string;
58 import std.variant;
59 
60 static import std.ascii;
61 
62 import ddbc.core;
63 
64 alias Nullable!byte Byte;
65 alias Nullable!ubyte Ubyte;
66 alias Nullable!short Short;
67 alias Nullable!ushort Ushort;
68 alias Nullable!int Int;
69 alias Nullable!uint Uint;
70 alias Nullable!long Long;
71 alias Nullable!ulong Ulong;
72 alias Nullable!float Float;
73 alias Nullable!double Double;
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     DATETIME_TYPE, // std.datetime.DateTime
128     DATE_TYPE, // std.datetime.Date
129     TIME_TYPE, // std.datetime.TimeOfDay
130     NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
131     NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
132     NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
133     BYTE_ARRAY_TYPE, // byte[]
134     UBYTE_ARRAY_TYPE, // ubyte[]
135 }
136 
137 /// converts camel case MyEntityName to my_entity_name
138 string camelCaseToUnderscoreDelimited(immutable string s) {
139     string res;
140     bool lastLower = false;
141     static import std.ascii;
142 
143     foreach(ch; s) {
144         if (ch >= 'A' && ch <= 'Z') {
145             if (lastLower) {
146                 lastLower = false;
147                 res ~= "_";
148             }
149             res ~= std.ascii.toLower(ch);
150         } else if (ch >= 'a' && ch <= 'z') {
151             lastLower = true;
152             res ~= ch;
153         } else {
154             res ~= ch;
155         }
156     }
157     return res;
158 }
159 
160 unittest {
161     static assert(camelCaseToUnderscoreDelimited("User") == "user");
162     static assert(camelCaseToUnderscoreDelimited("MegaTableName") == "mega_table_name");
163 }
164 
165 
166 template isSupportedSimpleType(T, string m) {
167     alias typeof(__traits(getMember, T, m)) ti;
168     static if (is(ti == function)) {
169         static if (is(ReturnType!(ti) == bool)) {
170             enum bool isSupportedSimpleType = true;
171         } else static if (is(ReturnType!(ti) == byte)) {
172             enum bool isSupportedSimpleType = true;
173         } else static if (is(ReturnType!(ti) == short)) {
174             enum bool isSupportedSimpleType = true;
175         } else static if (is(ReturnType!(ti) == int)) {
176             enum bool isSupportedSimpleType = true;
177         } else static if (is(ReturnType!(ti) == long)) {
178             enum bool isSupportedSimpleType = true;
179         } else static if (is(ReturnType!(ti) == ubyte)) {
180             enum bool isSupportedSimpleType = true;
181         } else static if (is(ReturnType!(ti) == ushort)) {
182             enum bool isSupportedSimpleType = true;
183         } else static if (is(ReturnType!(ti) == uint)) {
184             enum bool isSupportedSimpleType = true;
185         } else static if (is(ReturnType!(ti) == ulong)) {
186             enum bool isSupportedSimpleType = true;
187         } else static if (is(ReturnType!(ti) == float)) {
188             enum bool isSupportedSimpleType = true;
189         } else static if (is(ReturnType!(ti) == double)) {
190             enum bool isSupportedSimpleType = true;
191         } else static if (is(ReturnType!(ti) == Nullable!byte)) {
192             enum bool isSupportedSimpleType = true;
193         } else static if (is(ReturnType!(ti) == Nullable!short)) {
194             enum bool isSupportedSimpleType = true;
195         } else static if (is(ReturnType!(ti) == Nullable!int)) {
196             enum bool isSupportedSimpleType = true;
197         } else static if (is(ReturnType!(ti) == Nullable!long)) {
198             enum bool isSupportedSimpleType = true;
199         } else static if (is(ReturnType!(ti) == Nullable!ubyte)) {
200             enum bool isSupportedSimpleType = true;
201         } else static if (is(ReturnType!(ti) == Nullable!ushort)) {
202             enum bool isSupportedSimpleType = true;
203         } else static if (is(ReturnType!(ti) == Nullable!uint)) {
204             enum bool isSupportedSimpleType = true;
205         } else static if (is(ReturnType!(ti) == Nullable!ulong)) {
206             enum bool isSupportedSimpleType = true;
207         } else static if (is(ReturnType!(ti) == Nullable!float)) {
208             enum bool isSupportedSimpleType = true;
209         } else static if (is(ReturnType!(ti) == Nullable!double)) {
210             enum bool isSupportedSimpleType = true;
211         } else static if (is(ReturnType!(ti) == string)) {
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) == DateTime)) {
216             enum bool isSupportedSimpleType = true;
217         } else static if (is(ReturnType!(ti) == Date)) {
218             enum bool isSupportedSimpleType = true;
219         } else static if (is(ReturnType!(ti) == TimeOfDay)) {
220             enum bool isSupportedSimpleType = true;
221         } else static if (is(ReturnType!(ti) == Nullable!DateTime)) {
222             enum bool isSupportedSimpleType = true;
223         } else static if (is(ReturnType!(ti) == Nullable!Date)) {
224             enum bool isSupportedSimpleType = true;
225         } else static if (is(ReturnType!(ti) == Nullable!TimeOfDay)) {
226             enum bool isSupportedSimpleType = true;
227         } else static if (is(ReturnType!(ti) == byte[])) {
228             enum bool isSupportedSimpleType = true;
229         } else static if (is(ReturnType!(ti) == ubyte[])) {
230             enum bool isSupportedSimpleType = true;
231         } else static if (true) {
232             enum bool isSupportedSimpleType = false;
233         }
234     } else static if (is(ti == bool)) {
235         enum bool isSupportedSimpleType = true;
236     } else static if (is(ti == byte)) {
237         enum bool isSupportedSimpleType = true;
238     } else static if (is(ti == short)) {
239         enum bool isSupportedSimpleType = true;
240     } else static if (is(ti == int)) {
241         enum bool isSupportedSimpleType = true;
242     } else static if (is(ti == long)) {
243         enum bool isSupportedSimpleType = true;
244     } else static if (is(ti == ubyte)) {
245         enum bool isSupportedSimpleType = true;
246     } else static if (is(ti == ushort)) {
247         enum bool isSupportedSimpleType = true;
248     } else static if (is(ti == uint)) {
249         enum bool isSupportedSimpleType = true;
250     } else static if (is(ti == ulong)) {
251         enum bool isSupportedSimpleType = true;
252     } else static if (is(ti == float)) {
253         enum bool isSupportedSimpleType = true;
254     } else static if (is(ti == double)) {
255         enum bool isSupportedSimpleType = true;
256     } else static if (is(ti == Nullable!byte)) {
257         enum bool isSupportedSimpleType = true;
258     } else static if (is(ti == Nullable!short)) {
259         enum bool isSupportedSimpleType = true;
260     } else static if (is(ti == Nullable!int)) {
261         enum bool isSupportedSimpleType = true;
262     } else static if (is(ti == Nullable!long)) {
263         enum bool isSupportedSimpleType = true;
264     } else static if (is(ti == Nullable!ubyte)) {
265         enum bool isSupportedSimpleType = true;
266     } else static if (is(ti == Nullable!ushort)) {
267         enum bool isSupportedSimpleType = true;
268     } else static if (is(ti == Nullable!uint)) {
269         enum bool isSupportedSimpleType = true;
270     } else static if (is(ti == Nullable!ulong)) {
271         enum bool isSupportedSimpleType = true;
272     } else static if (is(ti == Nullable!float)) {
273         enum bool isSupportedSimpleType = true;
274     } else static if (is(ti == Nullable!double)) {
275         enum bool isSupportedSimpleType = true;
276     } else static if (is(ti == string)) {
277         enum bool isSupportedSimpleType = true;
278     } else static if (is(ti == String)) {
279         enum bool isSupportedSimpleType = true;
280     } else static if (is(ti == DateTime)) {
281         enum bool isSupportedSimpleType = true;
282     } else static if (is(ti == Date)) {
283         enum bool isSupportedSimpleType = true;
284     } else static if (is(ti == TimeOfDay)) {
285         enum bool isSupportedSimpleType = true;
286     } else static if (is(ti == Nullable!DateTime)) {
287         enum bool isSupportedSimpleType = true;
288     } else static if (is(ti == Nullable!Date)) {
289         enum bool isSupportedSimpleType = true;
290     } else static if (is(ti == Nullable!TimeOfDay)) {
291         enum bool isSupportedSimpleType = true;
292     } else static if (is(ti == byte[])) {
293         enum bool isSupportedSimpleType = true;
294     } else static if (is(ti == ubyte[])) {
295         enum bool isSupportedSimpleType = true;
296     } else static if (true) {
297         enum bool isSupportedSimpleType = false;
298     }
299 }
300 
301 PropertyMemberType getPropertyType(ti)() {
302     //pragma(msg, T.stringof);
303     //alias typeof(T) ti;
304 	static if (is(ti == bool)) {
305 		return PropertyMemberType.BOOL_TYPE;
306     } else static if (is(ti == byte)) {
307         return PropertyMemberType.BYTE_TYPE;
308     } else static if (is(ti == short)) {
309         return PropertyMemberType.SHORT_TYPE;
310     } else static if (is(ti == int)) {
311         return PropertyMemberType.INT_TYPE;
312     } else static if (is(ti == long)) {
313         return PropertyMemberType.LONG_TYPE;
314     } else static if (is(ti == ubyte)) {
315         return PropertyMemberType.UBYTE_TYPE;
316     } else static if (is(ti == ushort)) {
317         return PropertyMemberType.USHORT_TYPE;
318     } else static if (is(ti == uint)) {
319         return PropertyMemberType.UINT_TYPE;
320     } else static if (is(ti == ulong)) {
321         return PropertyMemberType.ULONG_TYPE;
322     } else static if (is(ti == float)) {
323         return PropertyMemberType.FLOAT_TYPE;
324     } else static if (is(ti == double)) {
325         return PropertyMemberType.DOUBLE_TYPE;
326     } else static if (is(ti == Nullable!byte)) {
327         return PropertyMemberType.NULLABLE_BYTE_TYPE;
328     } else static if (is(ti == Nullable!short)) {
329         return PropertyMemberType.NULLABLE_SHORT_TYPE;
330     } else static if (is(ti == Nullable!int)) {
331         return PropertyMemberType.NULLABLE_INT_TYPE;
332     } else static if (is(ti == Nullable!long)) {
333         return PropertyMemberType.NULLABLE_LONG_TYPE;
334     } else static if (is(ti == Nullable!ubyte)) {
335         return PropertyMemberType.NULLABLE_UBYTE_TYPE;
336     } else static if (is(ti == Nullable!ushort)) {
337         return PropertyMemberType.NULLABLE_USHORT_TYPE;
338     } else static if (is(ti == Nullable!uint)) {
339         return PropertyMemberType.NULLABLE_UINT_TYPE;
340     } else static if (is(ti == Nullable!ulong)) {
341         return PropertyMemberType.NULLABLE_ULONG_TYPE;
342     } else static if (is(ti == Nullable!float)) {
343         return PropertyMemberType.NULLABLE_FLOAT_TYPE;
344     } else static if (is(ti == Nullable!double)) {
345         return PropertyMemberType.NULLABLE_DOUBLE_TYPE;
346     } else static if (is(ti == string)) {
347         return PropertyMemberType.STRING_TYPE;
348     } else static if (is(ti == String)) {
349         return PropertyMemberType.NULLABLE_STRING_TYPE;
350     } else static if (is(ti == DateTime)) {
351         return PropertyMemberType.DATETIME_TYPE;
352     } else static if (is(ti == Date)) {
353         return PropertyMemberType.DATE_TYPE;
354     } else static if (is(ti == TimeOfDay)) {
355         return PropertyMemberType.TIME_TYPE;
356     } else static if (is(ti == Nullable!DateTime)) {
357         return PropertyMemberType.NULLABLE_DATETIME_TYPE;
358     } else static if (is(ti == Nullable!Date)) {
359         return PropertyMemberType.NULLABLE_DATE_TYPE;
360     } else static if (is(ti == Nullable!TimeOfDay)) {
361         return PropertyMemberType.NULLABLE_TIME_TYPE;
362     } else static if (is(ti == byte[])) {
363         return PropertyMemberType.BYTE_ARRAY_TYPE;
364     } else static if (is(ti == ubyte[])) {
365         return PropertyMemberType.UBYTE_ARRAY_TYPE;
366     } else static if (true) {
367         assert (false, "has unsupported type " ~ ti.stringof);
368     }
369 }
370 
371 PropertyMemberType getPropertyMemberType(T, string m)() {
372     alias typeof(__traits(getMember, T, m)) ti;
373     static if (is(ti == bool)) {
374         return PropertyMemberType.BOOL_TYPE;
375     } else static if (is(ti == byte)) {
376         return PropertyMemberType.BYTE_TYPE;
377     } else static if (is(ti == short)) {
378         return PropertyMemberType.SHORT_TYPE;
379     } else static if (is(ti == int)) {
380         return PropertyMemberType.INT_TYPE;
381     } else static if (is(ti == long)) {
382         return PropertyMemberType.LONG_TYPE;
383     } else static if (is(ti == ubyte)) {
384         return PropertyMemberType.UBYTE_TYPE;
385     } else static if (is(ti == ushort)) {
386         return PropertyMemberType.USHORT_TYPE;
387     } else static if (is(ti == uint)) {
388         return PropertyMemberType.UINT_TYPE;
389     } else static if (is(ti == ulong)) {
390         return PropertyMemberType.ULONG_TYPE;
391     } else static if (is(ti == float)) {
392         return PropertyMemberType.FLOAT_TYPE;
393     } else static if (is(ti == double)) {
394         return PropertyMemberType.DOUBLE_TYPE;
395     } else static if (is(ti == Nullable!byte)) {
396         return PropertyMemberType.NULLABLE_BYTE_TYPE;
397     } else static if (is(ti == Nullable!short)) {
398         return PropertyMemberType.NULLABLE_SHORT_TYPE;
399     } else static if (is(ti == Nullable!int)) {
400         return PropertyMemberType.NULLABLE_INT_TYPE;
401     } else static if (is(ti == Nullable!long)) {
402         return PropertyMemberType.NULLABLE_LONG_TYPE;
403     } else static if (is(ti == Nullable!ubyte)) {
404         return PropertyMemberType.NULLABLE_UBYTE_TYPE;
405     } else static if (is(ti == Nullable!ushort)) {
406         return PropertyMemberType.NULLABLE_USHORT_TYPE;
407     } else static if (is(ti == Nullable!uint)) {
408         return PropertyMemberType.NULLABLE_UINT_TYPE;
409     } else static if (is(ti == Nullable!ulong)) {
410         return PropertyMemberType.NULLABLE_ULONG_TYPE;
411     } else static if (is(ti == Nullable!float)) {
412         return PropertyMemberType.NULLABLE_FLOAT_TYPE;
413     } else static if (is(ti == Nullable!double)) {
414         return PropertyMemberType.NULLABLE_DOUBLE_TYPE;
415     } else static if (is(ti == string)) {
416         return PropertyMemberType.STRING_TYPE;
417     } else static if (is(ti == String)) {
418         return PropertyMemberType.NULLABLE_STRING_TYPE;
419     } else static if (is(ti == DateTime)) {
420         return PropertyMemberType.DATETIME_TYPE;
421     } else static if (is(ti == Date)) {
422         return PropertyMemberType.DATE_TYPE;
423     } else static if (is(ti == TimeOfDay)) {
424         return PropertyMemberType.TIME_TYPE;
425     } else static if (is(ti == Nullable!DateTime)) {
426         return PropertyMemberType.NULLABLE_DATETIME_TYPE;
427     } else static if (is(ti == Nullable!Date)) {
428         return PropertyMemberType.NULLABLE_DATE_TYPE;
429     } else static if (is(ti == Nullable!TimeOfDay)) {
430         return PropertyMemberType.NULLABLE_TIME_TYPE;
431     } else static if (is(ti == byte[])) {
432         return PropertyMemberType.BYTE_ARRAY_TYPE;
433     } else static if (is(ti == ubyte[])) {
434         return PropertyMemberType.UBYTE_ARRAY_TYPE;
435     } else static if (true) {
436         assert (false, "Member " ~ m ~ " of class " ~ T.stringof ~ " has unsupported type " ~ ti.stringof);
437     }
438 }
439 
440 string getPropertyReadCode(T, string m)() {
441     return "entity." ~ m;
442 }
443 
444 string getPropertyReadCode(alias T)() {
445     return "entity." ~ T.stringof;
446 }
447 
448 static immutable bool[] ColumnTypeCanHoldNulls = 
449 [
450     false, //BOOL_TYPE     // bool
451     false, //BYTE_TYPE,    // byte
452     false, //SHORT_TYPE,   // short
453     false, //INT_TYPE,     // int
454     false, //LONG_TYPE,    // long
455     false, //UBYTE_TYPE,   // ubyte
456     false, //USHORT_TYPE,  // ushort
457     false, //UINT_TYPE,    // uint
458     false, //ULONG_TYPE,   // ulong
459     true, //NULLABLE_BYTE_TYPE,  // Nullable!byte
460     true, //NULLABLE_SHORT_TYPE, // Nullable!short
461     true, //NULLABLE_INT_TYPE,   // Nullable!int
462     true, //NULLABLE_LONG_TYPE,  // Nullable!long
463     true, //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
464     true, //NULLABLE_USHORT_TYPE,// Nullable!ushort
465     true, //NULLABLE_UINT_TYPE,  // Nullable!uint
466     true, //NULLABLE_ULONG_TYPE, // Nullable!ulong
467     false,//FLOAT_TYPE,   // float
468     false,//DOUBLE_TYPE,   // double
469     true, //NULLABLE_FLOAT_TYPE, // Nullable!float
470     true, //NULLABLE_DOUBLE_TYPE,// Nullable!double
471     false, //STRING_TYPE   // string  -- treat as @NotNull by default
472     true, //NULLABLE_STRING_TYPE   // String
473     false, //DATETIME_TYPE, // std.datetime.DateTime
474     false, //DATE_TYPE, // std.datetime.Date
475     false, //TIME_TYPE, // std.datetime.TimeOfDay
476     true, //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
477     true, //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
478     true, //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
479     true, //BYTE_ARRAY_TYPE, // byte[]
480     true, //UBYTE_ARRAY_TYPE, // ubyte[]
481 ];
482 
483 bool isColumnTypeNullableByDefault(T, string m)() {
484     return ColumnTypeCanHoldNulls[getPropertyMemberType!(T,m)];
485 }
486 
487 static immutable string[] ColumnTypeKeyIsSetCode = 
488 [
489     "(%s != 0)", //BOOL_TYPE     // bool
490     "(%s != 0)", //BYTE_TYPE,    // byte
491     "(%s != 0)", //SHORT_TYPE,   // short
492     "(%s != 0)", //INT_TYPE,     // int
493     "(%s != 0)", //LONG_TYPE,    // long
494     "(%s != 0)", //UBYTE_TYPE,   // ubyte
495     "(%s != 0)", //USHORT_TYPE,  // ushort
496     "(%s != 0)", //UINT_TYPE,    // uint
497     "(%s != 0)", //ULONG_TYPE,   // ulong
498     "(!%s.isNull)", //NULLABLE_BYTE_TYPE,  // Nullable!byte
499     "(!%s.isNull)", //NULLABLE_SHORT_TYPE, // Nullable!short
500     "(!%s.isNull)", //NULLABLE_INT_TYPE,   // Nullable!int
501     "(!%s.isNull)", //NULLABLE_LONG_TYPE,  // Nullable!long
502     "(!%s.isNull)", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
503     "(!%s.isNull)", //NULLABLE_USHORT_TYPE,// Nullable!ushort
504     "(!%s.isNull)", //NULLABLE_UINT_TYPE,  // Nullable!uint
505     "(!%s.isNull)", //NULLABLE_ULONG_TYPE, // Nullable!ulong
506     "(%s != 0)",//FLOAT_TYPE,   // float
507     "(%s != 0)",//DOUBLE_TYPE,   // double
508     "(!%s.isNull)", //NULLABLE_FLOAT_TYPE, // Nullable!float
509     "(!%s.isNull)", //NULLABLE_DOUBLE_TYPE,// Nullable!double
510     "(%s !is null)", //STRING_TYPE   // string
511     "(%s !is null)", //NULLABLE_STRING_TYPE   // String
512     "(%s != DateTime())", //DATETIME_TYPE, // std.datetime.DateTime
513     "(%s != Date())", //DATE_TYPE, // std.datetime.Date
514     "(%s != TimeOfDay())", //TIME_TYPE, // std.datetime.TimeOfDay
515     "(!%s.isNull)", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
516     "(!%s.isNull)", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
517     "(!%s.isNull)", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
518     "(%s !is null)", //BYTE_ARRAY_TYPE, // byte[]
519     "(%s !is null)", //UBYTE_ARRAY_TYPE, // ubyte[]
520 ];
521 
522 string getColumnTypeKeyIsSetCode(T, string m)() {
523     return substituteParam(ColumnTypeKeyIsSetCode[getPropertyMemberType!(T,m)()], getPropertyReadCode!(T,m)());
524 }
525 
526 static immutable string[] ColumnTypeIsNullCode = 
527 [
528     "(false)", //BOOL_TYPE     // bool
529     "(false)", //BYTE_TYPE,    // byte
530     "(false)", //SHORT_TYPE,   // short
531     "(false)", //INT_TYPE,     // int
532     "(false)", //LONG_TYPE,    // long
533     "(false)", //UBYTE_TYPE,   // ubyte
534     "(false)", //USHORT_TYPE,  // ushort
535     "(false)", //UINT_TYPE,    // uint
536     "(false)", //ULONG_TYPE,   // ulong
537     "(%s.isNull)", //NULLABLE_BYTE_TYPE,  // Nullable!byte
538     "(%s.isNull)", //NULLABLE_SHORT_TYPE, // Nullable!short
539     "(%s.isNull)", //NULLABLE_INT_TYPE,   // Nullable!int
540     "(%s.isNull)", //NULLABLE_LONG_TYPE,  // Nullable!long
541     "(%s.isNull)", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
542     "(%s.isNull)", //NULLABLE_USHORT_TYPE,// Nullable!ushort
543     "(%s.isNull)", //NULLABLE_UINT_TYPE,  // Nullable!uint
544     "(%s.isNull)", //NULLABLE_ULONG_TYPE, // Nullable!ulong
545     "(false)",//FLOAT_TYPE,   // float
546     "(false)",//DOUBLE_TYPE,   // double
547     "(%s.isNull)", //NULLABLE_FLOAT_TYPE, // Nullable!float
548     "(%s.isNull)", //NULLABLE_DOUBLE_TYPE,// Nullable!double
549     "(%s is null)", //STRING_TYPE   // string
550     "(%s is null)", //NULLABLE_STRING_TYPE   // String
551     "(false)", //DATETIME_TYPE, // std.datetime.DateTime
552     "(false)", //DATE_TYPE, // std.datetime.Date
553     "(false)", //TIME_TYPE, // std.datetime.TimeOfDay
554     "(%s.isNull)", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
555     "(%s.isNull)", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
556     "(%s.isNull)", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
557     "(%s is null)", //BYTE_ARRAY_TYPE, // byte[]
558     "(%s is null)", //UBYTE_ARRAY_TYPE, // ubyte[]
559 ];
560 
561 string getColumnTypeIsNullCode(T, string m)() {
562     return substituteParam(ColumnTypeIsNullCode[getPropertyMemberType!(T,m)()], getPropertyReadCode!(T,m)());
563 }
564 
565 static immutable string[] ColumnTypeSetNullCode = 
566 [
567     "bool nv;", // BOOL_TYPE   // bool
568     "byte nv = 0;", //BYTE_TYPE,    // byte
569     "short nv = 0;", //SHORT_TYPE,   // short
570     "int nv = 0;", //INT_TYPE,     // int
571     "long nv = 0;", //LONG_TYPE,    // long
572     "ubyte nv = 0;", //UBYTE_TYPE,   // ubyte
573     "ushort nv = 0;", //USHORT_TYPE,  // ushort
574     "uint nv = 0;", //UINT_TYPE,    // uint
575     "ulong nv = 0;", //ULONG_TYPE,   // ulong
576     "Nullable!byte nv;", //NULLABLE_BYTE_TYPE,  // Nullable!byte
577     "Nullable!short nv;", //NULLABLE_SHORT_TYPE, // Nullable!short
578     "Nullable!int nv;", //NULLABLE_INT_TYPE,   // Nullable!int
579     "Nullable!long nv;", //NULLABLE_LONG_TYPE,  // Nullable!long
580     "Nullable!ubyte nv;", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
581     "Nullable!ushort nv;", //NULLABLE_USHORT_TYPE,// Nullable!ushort
582     "Nullable!uint nv;", //NULLABLE_UINT_TYPE,  // Nullable!uint
583     "Nullable!ulong nv;", //NULLABLE_ULONG_TYPE, // Nullable!ulong
584     "float nv = 0;",//FLOAT_TYPE,   // float
585     "double nv = 0;",//DOUBLE_TYPE,   // double
586     "Nullable!float nv;", //NULLABLE_FLOAT_TYPE, // Nullable!float
587     "Nullable!double nv;", //NULLABLE_DOUBLE_TYPE,// Nullable!double
588     "string nv;", //STRING_TYPE   // string
589     "string nv;", //NULLABLE_STRING_TYPE   // String
590     "DateTime nv;", //DATETIME_TYPE, // std.datetime.DateTime
591     "Date nv;", //DATE_TYPE, // std.datetime.Date
592     "TimeOfDay nv;", //TIME_TYPE, // std.datetime.TimeOfDay
593     "Nullable!DateTime nv;", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
594     "Nullable!Date nv;", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
595     "Nullable!TimeOfDay nv;", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
596     "byte[] nv = null;", //BYTE_ARRAY_TYPE, // byte[]
597     "ubyte[] nv = null;", //UBYTE_ARRAY_TYPE, // ubyte[]
598 ];
599 
600 static immutable string[] ColumnTypePropertyToVariant = 
601 [
602     "Variant(%s)", //BOOL_TYPE     // bool
603     "Variant(%s)", //BYTE_TYPE,    // byte
604     "Variant(%s)", //SHORT_TYPE,   // short
605     "Variant(%s)", //INT_TYPE,     // int
606     "Variant(%s)", //LONG_TYPE,    // long
607     "Variant(%s)", //UBYTE_TYPE,   // ubyte
608     "Variant(%s)", //USHORT_TYPE,  // ushort
609     "Variant(%s)", //UINT_TYPE,    // uint
610     "Variant(%s)", //ULONG_TYPE,   // ulong
611     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_BYTE_TYPE,  // Nullable!byte
612     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_SHORT_TYPE, // Nullable!short
613     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_INT_TYPE,   // Nullable!int
614     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_LONG_TYPE,  // Nullable!long
615     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
616     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_USHORT_TYPE,// Nullable!ushort
617     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_UINT_TYPE,  // Nullable!uint
618     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_ULONG_TYPE, // Nullable!ulong
619     "Variant(%s)",//FLOAT_TYPE,   // float
620     "Variant(%s)",//DOUBLE_TYPE,   // double
621     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_FLOAT_TYPE, // Nullable!float
622     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DOUBLE_TYPE,// Nullable!double
623     "Variant(%s)", //STRING_TYPE   // string
624     "Variant(%s)", //NULLABLE_STRING_TYPE   // String
625     "Variant(%s)", //DATETIME_TYPE, // std.datetime.DateTime
626     "Variant(%s)", //DATE_TYPE, // std.datetime.Date
627     "Variant(%s)", //TIME_TYPE, // std.datetime.TimeOfDay
628     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
629     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
630     "(%s.isNull ? Variant(null) : Variant(%s.get()))", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
631     "Variant(%s)", //BYTE_ARRAY_TYPE, // byte[]
632     "Variant(%s)", //UBYTE_ARRAY_TYPE, // ubyte[]
633 ];
634 
635 static immutable string[] ColumnTypeDatasetReaderCode = 
636 [
637     "r.getBoolean(index)", //BOOL_TYPE,    // bool
638     "r.getByte(index)", //BYTE_TYPE,    // byte
639     "r.getShort(index)", //SHORT_TYPE,   // short
640     "r.getInt(index)", //INT_TYPE,     // int
641     "r.getLong(index)", //LONG_TYPE,    // long
642     "r.getUbyte(index)", //UBYTE_TYPE,   // ubyte
643     "r.getUshort(index)", //USHORT_TYPE,  // ushort
644     "r.getUint(index)", //UINT_TYPE,    // uint
645     "r.getUlong(index)", //ULONG_TYPE,   // ulong
646     "Nullable!byte(r.getByte(index))", //NULLABLE_BYTE_TYPE,  // Nullable!byte
647     "Nullable!short(r.getShort(index))", //NULLABLE_SHORT_TYPE, // Nullable!short
648     "Nullable!int(r.getInt(index))", //NULLABLE_INT_TYPE,   // Nullable!int
649     "Nullable!long(r.getLong(index))", //NULLABLE_LONG_TYPE,  // Nullable!long
650     "Nullable!ubyte(r.getUbyte(index))", //NULLABLE_UBYTE_TYPE, // Nullable!ubyte
651     "Nullable!ushort(r.getUshort(index))", //NULLABLE_USHORT_TYPE,// Nullable!ushort
652     "Nullable!uint(r.getUint(index))", //NULLABLE_UINT_TYPE,  // Nullable!uint
653     "Nullable!ulong(r.getUlong(index))", //NULLABLE_ULONG_TYPE, // Nullable!ulong
654     "r.getFloat(index)",//FLOAT_TYPE,   // float
655     "r.getDouble(index)",//DOUBLE_TYPE,   // double
656     "Nullable!float(r.getFloat(index))", //NULLABLE_FLOAT_TYPE, // Nullable!float
657     "Nullable!double(r.getDouble(index))", //NULLABLE_DOUBLE_TYPE,// Nullable!double
658     "r.getString(index)", //STRING_TYPE   // string
659     "r.getString(index)", //NULLABLE_STRING_TYPE   // String
660     "r.getDateTime(index)", //DATETIME_TYPE, // std.datetime.DateTime
661     "r.getDate(index)", //DATE_TYPE, // std.datetime.Date
662     "r.getTime(index)", //TIME_TYPE, // std.datetime.TimeOfDay
663     "Nullable!DateTime(r.getDateTime(index))", //NULLABLE_DATETIME_TYPE, // Nullable!std.datetime.DateTime
664     "Nullable!Date(r.getDate(index))", //NULLABLE_DATE_TYPE, // Nullable!std.datetime.Date
665     "Nullable!TimeOfDay(r.getTime(index))", //NULLABLE_TIME_TYPE, // Nullable!std.datetime.TimeOfDay
666     "r.getBytes(index)", //BYTE_ARRAY_TYPE, // byte[]
667     "r.getUbytes(index)", //UBYTE_ARRAY_TYPE, // ubyte[]
668 ];
669 
670 string getColumnTypeDatasetReadCode(T, string m)() {
671     return ColumnTypeDatasetReaderCode[getPropertyMemberType!(T,m)()];
672 }
673 
674 string getVarTypeDatasetReadCode(T)() {
675     return ColumnTypeDatasetReaderCode[getPropertyType!T];
676 }
677 
678 string getPropertyWriteCode(T, string m)() {
679     //immutable PropertyMemberKind kind = getPropertyMemberKind!(T, m)();
680     immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyMemberType!(T,m)()];
681     immutable string datasetReader = "(!r.isNull(index) ? " ~ getColumnTypeDatasetReadCode!(T, m)() ~ " : nv)";
682     return nullValueCode ~ "entity." ~ m ~ " = " ~ datasetReader ~ ";";
683 }
684 
685 string getPropertyWriteCode(T)() {
686     //immutable PropertyMemberKind kind = getPropertyMemberKind!(T, m)();
687     immutable string nullValueCode = ColumnTypeSetNullCode[getPropertyType!T];
688     immutable string datasetReader = "(!r.isNull(index) ? " ~ getVarTypeDatasetReadCode!T ~ " : nv)";
689     return nullValueCode ~ "a = " ~ datasetReader ~ ";";
690 }
691 
692 /// returns array of field names
693 string[] getColumnNamesForType(T)()  if (__traits(isPOD, T)) {
694     string[] res;
695     foreach(m; __traits(allMembers, T)) {
696         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
697             // skip non-public members
698             static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
699                 alias typeof(__traits(getMember, T, m)) ti;
700                 res ~= m;
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                 res ~= getColumnReadCode!(T, m);
718             }
719         }
720     }
721     return res;
722 }
723 
724 string getAllColumnsReadCode(T, fieldList...)() {
725     string res = "int index = 1;\n";
726     foreach(m; fieldList) {
727         res ~= getColumnReadCode!(T, m);
728     }
729     return res;
730 }
731 
732 unittest {
733     struct User1 {
734         long id;
735         string name;
736         int flags;
737     }
738     //pragma(msg, "nullValueCode = " ~ ColumnTypeSetNullCode[getPropertyMemberType!(User, "id")()]);
739     //pragma(msg, "datasetReader = " ~ getColumnTypeDatasetReadCode!(User, "id")());
740     //pragma(msg, "getPropertyWriteCode: " ~ getPropertyWriteCode!(User, "id"));
741     //pragma(msg, "getAllColumnsReadCode:\n" ~ getAllColumnsReadCode!(User));
742     //static assert(getPropertyWriteCode!(User, "id") == "long nv = 0;entity.id = (!r.isNull(index) ? r.getLong(index) : nv);");
743 }
744 
745 unittest {
746     struct User1 {
747         long id;
748         string name;
749         int flags;
750     }
751     static assert(getPropertyMemberType!(User1, "id")() == PropertyMemberType.LONG_TYPE);
752     static assert(getPropertyMemberType!(User1, "name")() == PropertyMemberType.STRING_TYPE);
753     //pragma(msg, "getPropertyMemberType unit test passed");
754 }
755 
756 
757 
758 /// returns table name for struct type
759 string getTableNameForType(T)() if (__traits(isPOD, T)) {
760     return camelCaseToUnderscoreDelimited(T.stringof);
761 }
762 
763 unittest {
764     struct User1 {
765         long id;
766         string name;
767         int flags;
768     }
769     static assert(getTableNameForType!User1() == "user1");
770 }
771 
772 /// returns "SELECT <field list> FROM <table name>"
773 string generateSelectSQL(T)() {
774     return "SELECT " ~ join(getColumnNamesForType!(T)(), ",") ~ " FROM " ~ getTableNameForType!(T)();
775 }
776 
777 string joinFieldList(fieldList...)() {
778     string res;
779     foreach(f; fieldList) {
780         if (res.length)
781             res ~= ",";
782         res ~= f;
783     }
784     return res;
785 }
786 
787 /// returns "SELECT <field list> FROM <table name>"
788 string generateSelectSQL(T, fieldList...)() {
789     string res = "SELECT ";
790     res ~= joinFieldList!fieldList;
791     res ~= " FROM " ~ getTableNameForType!(T)();
792     return res;
793 }
794 
795 unittest {
796     //pragma(msg, "column names: " ~ join(getColumnNamesForType!(User)(), ","));
797     //pragma(msg, "select SQL: " ~ generateSelectSQL!(User)());
798 }
799 
800 /// returns "SELECT <field list> FROM <table name>"
801 string generateSelectForGetSQL(T)() {
802     string res = generateSelectSQL!T();
803     res ~= " WHERE id=";
804     return res;
805 }
806 
807 string generateSelectForGetSQLWithFilter(T)() {
808   string res = generateSelectSQL!T();
809   res ~= " WHERE ";
810   return res;
811 }
812 
813 T get(T)(Statement stmt, long id) {
814   T entity;
815   static immutable getSQL = generateSelectForGetSQL!T();
816   ResultSet r;
817   r = stmt.executeQuery(getSQL ~ to!string(id));
818   r.next();
819   mixin(getAllColumnsReadCode!T());
820   return entity;
821 }
822 
823 T get(T)(Statement stmt, string filter) {
824   T entity;
825   static immutable getSQL = generateSelectForGetSQLWithFilter!T();
826   ResultSet r;
827   writeln(getSQL ~ filter);
828   r = stmt.executeQuery(getSQL ~ filter);
829   r.next();
830   mixin(getAllColumnsReadCode!T());
831   return entity;
832 }
833 
834 /// range for select query
835 struct select(T, fieldList...) if (__traits(isPOD, T)) {
836   T entity;
837   private Statement stmt;
838   private ResultSet r;
839   static immutable selectSQL = generateSelectSQL!(T, fieldList)();
840   string whereCondSQL;
841   string orderBySQL;
842   this(Statement stmt) {
843     this.stmt = stmt;
844   }
845   ref select where(string whereCond) {
846     whereCondSQL = " WHERE " ~ whereCond;
847     return this;
848   }
849   ref select orderBy(string order) {
850     orderBySQL = " ORDER BY " ~ order;
851     return this;
852   }
853   ref T front() {
854     return entity;
855   }
856   void popFront() {
857   }
858   @property bool empty() {
859     if (!r)
860       r = stmt.executeQuery(selectSQL ~ whereCondSQL ~ orderBySQL);
861     if (!r.next())
862       return true;
863     mixin(getAllColumnsReadCode!(T, fieldList));
864     return false;
865   }
866   ~this() {
867     if (r)
868       r.close();
869   }
870 }
871 
872 /// returns "INSERT INTO <table name> (<field list>) VALUES (value list)
873 string generateInsertSQL(T)() {
874     string res = "INSERT INTO " ~ getTableNameForType!(T)();
875     string []values;
876     foreach(m; __traits(allMembers, T)) {
877       if (m != "id") {
878         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
879           // skip non-public members
880           static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
881             values ~= m;
882           }
883         }
884       }
885     }
886     res ~= "(" ~ join(values, ",") ~ ")";
887     res ~= " VALUES ";
888     return res;
889 }
890 
891 string addFieldValue(T)(string m) {
892   string tmp = `{Variant v = o.`~m~`;`;
893   tmp ~=  `static if (isColumnTypeNullableByDefault!(T, "`~m~`")()) {`;
894   tmp ~= `	if(o.`~m~`.isNull) {`;
895   tmp ~= `		values ~= "NULL";`;
896   tmp ~= `	} else {`;
897   tmp ~= `		values ~= "\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
898   tmp ~= `}} else {`;
899   tmp ~= `		values ~= "\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
900   tmp ~= `}}`;
901   return tmp;
902   // return `values ~= "\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
903 }
904 
905 bool insert(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
906     auto insertSQL = generateInsertSQL!(T)();
907     string []values;
908     foreach(m; __traits(allMembers, T)) {
909       if (m != "id") {
910         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
911           // skip non-public members
912           static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
913             // pragma(msg,addFieldValue!(T)(m));
914             mixin(addFieldValue!(T)(m));
915           }
916         }
917       }
918     }
919     insertSQL ~= "(" ~ join(values, ",") ~ ")";
920     Variant insertId;
921     stmt.executeUpdate(insertSQL, insertId);
922     o.id = insertId.get!long;
923     return true;
924 }
925 
926 /// returns "UPDATE <table name> SET field1=value1 WHERE id=id
927 string generateUpdateSQL(T)() {
928   string res = "UPDATE " ~ getTableNameForType!(T)();
929   string []values;
930   foreach(m; __traits(allMembers, T)) {
931     if (m != "id") {
932       static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
933         // skip non-public members
934         static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
935           values ~= m;
936         }
937       }
938     }
939   }
940   res ~= " SET ";
941   return res;
942 }
943 
944 string addUpdateValue(T)(string m) {
945   return `values ~= "` ~ m ~ `=\"" ~ to!string(o.` ~ m ~ `) ~ "\"";`;
946 }
947 
948 bool update(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
949     auto updateSQL = generateUpdateSQL!(T)();
950     string []values;
951     foreach(m; __traits(allMembers, T)) {
952       if (m != "id") {
953         static if (__traits(compiles, (typeof(__traits(getMember, T, m))))){
954           // skip non-public members
955           static if (__traits(getProtection, __traits(getMember, T, m)) == "public") {
956             // pragma(msg, addUpdateValue!(T)(m));
957             mixin(addUpdateValue!(T)(m));
958           }
959         }
960       }
961     }
962     updateSQL ~= join(values, ",");
963     updateSQL ~= mixin(`" WHERE id="~ to!string(o.id) ~ ";"`);
964     Variant updateId;
965     stmt.executeUpdate(updateSQL, updateId);
966     return true;
967 }
968 
969 /// returns "DELETE FROM <table name> WHERE id=id
970 string generateDeleteSQL(T)() {
971   string res = "DELETE FROM " ~ getTableNameForType!(T)();
972   return res;
973 }
974 
975 bool remove(T)(Statement stmt, ref T o) if (__traits(isPOD, T)) {
976   auto deleteSQL = generateDeleteSQL!(T)();
977   deleteSQL ~= mixin(`" WHERE id="~ to!string(o.id) ~ ";"`);
978   Variant deleteId;
979   stmt.executeUpdate(deleteSQL, deleteId);
980   return true;
981 }
982 
983 template isSupportedSimpleTypeRef(M) {
984     alias typeof(M) ti;
985     static if (!__traits(isRef, M)) {
986         enum bool isSupportedSimpleTypeRef = false;
987     } else static if (is(ti == bool)) {
988         enum bool isSupportedSimpleType = true;
989     } else static if (is(ti == byte)) {
990         enum bool isSupportedSimpleType = true;
991     } else static if (is(ti == short)) {
992         enum bool isSupportedSimpleType = true;
993     } else static if (is(ti == int)) {
994         enum bool isSupportedSimpleType = true;
995     } else static if (is(ti == long)) {
996         enum bool isSupportedSimpleType = true;
997     } else static if (is(ti == ubyte)) {
998         enum bool isSupportedSimpleType = true;
999     } else static if (is(ti == ushort)) {
1000         enum bool isSupportedSimpleType = true;
1001     } else static if (is(ti == uint)) {
1002         enum bool isSupportedSimpleType = true;
1003     } else static if (is(ti == ulong)) {
1004         enum bool isSupportedSimpleType = true;
1005     } else static if (is(ti == float)) {
1006         enum bool isSupportedSimpleType = true;
1007     } else static if (is(ti == double)) {
1008         enum bool isSupportedSimpleType = true;
1009     } else static if (is(ti == Nullable!byte)) {
1010         enum bool isSupportedSimpleType = true;
1011     } else static if (is(ti == Nullable!short)) {
1012         enum bool isSupportedSimpleType = true;
1013     } else static if (is(ti == Nullable!int)) {
1014         enum bool isSupportedSimpleType = true;
1015     } else static if (is(ti == Nullable!long)) {
1016         enum bool isSupportedSimpleType = true;
1017     } else static if (is(ti == Nullable!ubyte)) {
1018         enum bool isSupportedSimpleType = true;
1019     } else static if (is(ti == Nullable!ushort)) {
1020         enum bool isSupportedSimpleType = true;
1021     } else static if (is(ti == Nullable!uint)) {
1022         enum bool isSupportedSimpleType = true;
1023     } else static if (is(ti == Nullable!ulong)) {
1024         enum bool isSupportedSimpleType = true;
1025     } else static if (is(ti == Nullable!float)) {
1026         enum bool isSupportedSimpleType = true;
1027     } else static if (is(ti == Nullable!double)) {
1028         enum bool isSupportedSimpleType = true;
1029     } else static if (is(ti == string)) {
1030         enum bool isSupportedSimpleType = true;
1031     } else static if (is(ti == String)) {
1032         enum bool isSupportedSimpleType = true;
1033     } else static if (is(ti == DateTime)) {
1034         enum bool isSupportedSimpleType = true;
1035     } else static if (is(ti == Date)) {
1036         enum bool isSupportedSimpleType = true;
1037     } else static if (is(ti == TimeOfDay)) {
1038         enum bool isSupportedSimpleType = true;
1039     } else static if (is(ti == Nullable!DateTime)) {
1040         enum bool isSupportedSimpleType = true;
1041     } else static if (is(ti == Nullable!Date)) {
1042         enum bool isSupportedSimpleType = true;
1043     } else static if (is(ti == Nullable!TimeOfDay)) {
1044         enum bool isSupportedSimpleType = true;
1045     } else static if (is(ti == byte[])) {
1046         enum bool isSupportedSimpleType = true;
1047     } else static if (is(ti == ubyte[])) {
1048         enum bool isSupportedSimpleType = true;
1049     } else static if (true) {
1050         enum bool isSupportedSimpleType = false;
1051     }
1052 }
1053 
1054 // TODO: use better way to count parameters
1055 int paramCount(destList...)() {
1056     int res = 0;
1057     foreach(p; destList) {
1058         res++;
1059     }
1060     return res;
1061 }
1062 
1063 bool isSupportedSimpleTypeRefList(destList...)() {
1064     foreach(p; destList) {
1065         static if (!isSupportedSimpleTypeRef!p) {
1066             return false;
1067         }
1068     }
1069     return true;
1070 }
1071 
1072 struct select(Args...)  {//if (isSupportedSimpleTypeRefList!Args())
1073     private Statement stmt;
1074     private ResultSet r;
1075     private void delegate() _copyFunction;
1076     private int rowIndex;
1077     
1078     this(Args...)(Statement stmt, string sql, ref Args args) {
1079         this.stmt = stmt;
1080         selectSQL = sql;
1081         _copyFunction = delegate() {
1082             foreach(i, ref a; args) {
1083                 int index = i + 1;
1084                 mixin(getPropertyWriteCode!(typeof(a)));
1085             }
1086         };
1087     }
1088 
1089     string selectSQL;
1090     string whereCondSQL;
1091     string orderBySQL;
1092     ref select where(string whereCond) {
1093         whereCondSQL = " WHERE " ~ whereCond;
1094         return this;
1095     }
1096     ref select orderBy(string order) {
1097         orderBySQL = " ORDER BY " ~ order;
1098         return this;
1099     }
1100     int front() {
1101         return rowIndex;
1102     }
1103     void popFront() {
1104         rowIndex++;
1105     }
1106     @property bool empty() {
1107         if (!r)
1108             r = stmt.executeQuery(selectSQL ~ whereCondSQL ~ orderBySQL);
1109         if (!r.next())
1110             return true;
1111         _copyFunction();
1112         return false;
1113     }
1114     ~this() {
1115         if (r)
1116             r.close();
1117     }
1118 
1119 }