microrm.queries source code

1 module microrm.queries;
2 
3 import std.exception : enforce;
4 import std.algorithm : joiner;
5 import std.string : join;
6 
7 import d2sqlite3;
8 
9 import microrm.util;
10 import microrm.exception;
11 
12 debug (microrm) import std.stdio : stderr;
13 
14 enum BASEQUERYLENGTH = 512;
15 
16 struct Select(T, BUF)
17 {
18     import std.range : InputRange;
19 
20     mixin baseQueryData!("SELECT * FROM %s");
21     mixin whereCondition;
22 
23     private ref orderBy(string[] fields, string orderType)
24     {
25         assert(orderType == "ASC" || orderType == "DESC");
26         query.put(" ORDER BY ");
27         query.put(fields.joiner(", "));
28         query.put(" ");
29         query.put(orderType);
30         return this;
31     }
32 
33     ref ascOrderBy(string[] fields...) { return orderBy(fields, "ASC"); }
34     ref descOrderBy(string[] fields...) { return orderBy(fields, "DESC"); }
35 
36     auto run() @property
37     {
38         import std.range : inputRangeObject;
39         import std.algorithm : map;
40 
41         enforce(db, "database is null");
42 
43         query.put(';');
44         auto q = query.data.idup;
45         debug (microrm) stderr.writeln(q);
46         auto result = (*db).executeCheck(q);
47 
48         static T qconv(typeof(result.front) e)
49         {
50             enum names = fieldNames!("", T)();
51             T ret;
52             static string rr()
53             {
54                 string[] res;
55                 res ~= "import std.traits;";
56                 foreach (i, a; fieldNames!("", T)())
57                 {
58                     res ~= `{`;
59                     res ~= q{alias ET = typeof(ret.%s);}.format(a[1..$-1]);
60                     res ~= q{static if (!isStaticArray!ET)};
61                     res ~= format(q{ret.%1$s = e[%2$d].as!ET;}, a[1..$-1], i);
62                     res ~= q{else};
63                     res ~= `
64                         {
65                             import std.algorithm : min;
66                             auto ubval = e[%2$d].as!(ubyte[]);
67                             auto etval = cast(typeof(ET.init[]))ubval;
68                             auto ln = min(ret.%1$s.length, etval.length);
69                             ret.%1$s[0..ln] = etval[0..ln];
70                         }
71                         `.format(a[1..$-1], i);
72                     res ~= `}`;
73                 }
74                 return res.join("\n");
75             }
76             mixin(rr());
77             return ret;
78         }
79 
80         return result.map!qconv;
81     }
82 }
83 
84 unittest
85 {
86     static struct Foo
87     {
88         ulong id;
89         string text;
90         ulong ts;
91     }
92 
93     import std.array : Appender;
94     Appender!(char[]) buf;
95 
96     auto test = Select!(Foo, typeof(buf))(null, &buf);
97     test.where("text =", "privet").and("ts >", 123);
98     assert (test.query.data == "SELECT * FROM Foo WHERE text = 'privet' AND ts > '123'");
99 }
100 
101 void buildInsertOrReplace(T, W)(ref W buf, bool replace, size_t valCount=1)
102 {
103     if (!replace) buf.put("INSERT INTO ");
104     else buf.put("INSERT OR REPLACE INTO ");
105     buf.put(tableName!T);
106     buf.put(" (");
107 
108     bool isInsertId = true;
109 
110     auto tt = T.init;
111     foreach (i, f; tt.tupleof)
112     {
113         alias F = typeof(f);
114         enum name = __traits(identifier, tt.tupleof[i]);
115 
116         static if (is(F==struct))
117             enum tmp = fieldNames!(name, F)().join(",");
118         else
119         {
120             if (name == IDNAME && !replace) continue;
121             enum tmp = "'" ~ name ~ "'";
122         }
123 
124         buf.put(tmp);
125         static if (i+1 != tt.tupleof.length)
126             buf.put(",");
127     }
128     buf.put(") VALUES (");
129 
130     foreach (n; 0 .. valCount)
131     {
132         foreach (i, f; tt.tupleof)
133         {
134             enum name = __traits(identifier, tt.tupleof[i]);
135             alias F = typeof(f);
136             static if (is(F==struct)) buf.fmtValues!F;
137             else
138             {
139                 if (name == IDNAME && !replace) continue;
140                 buf.put("?");
141             }
142             static if (i+1 != tt.tupleof.length) buf.put(",");
143         }
144         if (n+1 != valCount) buf.put("),(");
145     }
146     buf.put(");");
147 }
148 
149 void fmtValues(T, W)(ref W buf)
150 {
151     auto tt = T.init;
152     foreach (i, f; tt.tupleof)
153     {
154         alias F = typeof(f);
155         static if (is(F==struct)) buf.fmtValues!F;
156         else buf.put("?");
157         static if (i+1 != tt.tupleof.length)
158             buf.put(",");
159     }
160 }
161 
162 unittest
163 {
164     static struct Foo
165     {
166         ulong id;
167         string text;
168         float val;
169         ulong ts;
170     }
171 
172     import std.array : appender;
173     auto buf = appender!(char[]);
174 
175     buf.buildInsertOrReplace!Foo(true);
176     auto q = buf.data;
177     assert(q == "INSERT OR REPLACE INTO Foo "~
178                 "('id','text','val','ts') VALUES "~
179                 "(?,?,?,?);");
180     buf.clear();
181     buf.buildInsertOrReplace!Foo(false);
182     q = buf.data;
183     assert(q == "INSERT INTO Foo "~
184                 "('text','val','ts') VALUES "~
185                 "(?,?,?);");
186 }
187 
188 unittest
189 {
190     static struct Foo
191     {
192         ulong id;
193         string text;
194         float val;
195         ulong ts;
196     }
197 
198     static struct Bar
199     {
200         ulong id;
201         float value;
202         Foo foo;
203     }
204 
205     import std.array : appender;
206     auto buf = appender!(char[]);
207 
208     buf.buildInsertOrReplace!Bar(true);
209     auto q = buf.data;
210     assert(q == "INSERT OR REPLACE INTO Bar "~
211                 "('id','value','foo.id','foo.text','foo.val','foo.ts') VALUES "~
212                 "(?,?,?,?,?,?);");
213     buf.clear();
214     buf.buildInsertOrReplace!Bar(false);
215     q = buf.data;
216     assert(q == "INSERT INTO Bar "~
217                 "('value','foo.id','foo.text','foo.val','foo.ts') VALUES "~
218                 "(?,?,?,?,?);");
219     buf.clear();
220     buf.buildInsertOrReplace!Bar(false, 3);
221     q = buf.data;
222     assert(q == "INSERT INTO Bar "~
223                 "('value','foo.id','foo.text','foo.val','foo.ts') VALUES "~
224                 "(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?);");
225 }
226 
227 unittest
228 {
229     struct Foo { string text; float val; ulong ts; }
230     struct Bar { float v; Foo foo; }
231     struct Baz { ulong id; float v; Bar xyz; float w; }
232     
233 
234     import std.array : appender;
235     auto buf = appender!(char[]);
236 
237     buf.buildInsertOrReplace!Baz(true);
238     auto q = buf.data;
239     assert(q == "INSERT OR REPLACE INTO Baz "~
240                 "('id','v','xyz.v',"~
241                 "'xyz.foo.text','xyz.foo.val','xyz.foo.ts','w') VALUES "~
242                 "(?,?,?,?,"~"?,?,?);");
243 }
244 
245 struct Delete(T, BUF)
246 {
247     mixin baseQueryData!("DELETE FROM %s");
248     mixin whereCondition;
249 
250     auto run() @property
251     {
252         enforce(db, "database is null");
253 
254         query.put(';');
255         auto q = query.data.idup;
256         debug (microrm) stderr.writeln(q);
257         return (*db).executeCheck(q);
258     }
259 }
260 
261 unittest
262 {
263     static struct Foo
264     {
265         ulong id;
266         string text;
267         ulong ts;
268     }
269 
270     import std.array : Appender;
271     Appender!(char[]) buf;
272 
273     auto test = Delete!(Foo, typeof(buf))(null, &buf);
274     test.where("text =", "privet").and("ts >", 123);
275     assert (test.query.data == "DELETE FROM Foo WHERE text = 'privet' AND ts > '123'");
276 }
277 
278 struct Count(T, BUF)
279 {
280     mixin baseQueryData!("SELECT Count(*) FROM %s");
281     mixin whereCondition;
282 
283     size_t run() @property
284     {
285         enforce(db, "database is null");
286         auto q = query.data.idup;
287         debug (microrm) stderr.writeln(q);
288         return (*db).executeCheck(q).front.front.as!size_t;
289     }
290 }
291 
292 unittest
293 {
294     static struct Foo
295     {
296         ulong id;
297         string text;
298         ulong ts;
299     }
300     
301     import std.array : Appender;
302     Appender!(char[]) buf;
303 
304     auto test = Count!(Foo, typeof(buf))(null, &buf);
305     test.where("text =", "privet").and("ts >", 123);
306     assert (test.query.data == "SELECT Count(*) FROM Foo WHERE text = 'privet' AND ts > '123'");
307 }