sqlbuilder.dataset source code

1 module sqlbuilder.dataset;
2 
3 import sqlbuilder.uda;
4 import sqlbuilder.types;
5 import sqlbuilder.traits;
6 
7 TableDef buildTableDef(T, alias relation, mappings...)(Spec joinType, TableDef dependency)
8 {
9     assert(isJoin(joinType), "Error, invalid spec for join with dependencies: " ~ joinType);
10     auto tableid = makeSpec(dependency.as ~ "_" ~ joinType ~ "_" ~ relation.name, Spec.tableid);
11     auto deptable = dependency.as.makeSpec(Spec.tableid);
12     auto expr = ExprString(makeSpec(joinType), getTableName!(relation.foreign_table).makeSpec(Spec.id),
13         " AS ", tableid[2 .. $].makeSpec(Spec.id), " ON (");
14 
15     static string genKeyId(T, string fieldname)()
16     {
17         return getColumnName!(__traits(getMember, T, fieldname)).makeSpec(Spec.id);
18     }
19 
20     if(mappings.length > 1)
21         expr ~= andSpec;
22     // static
23     foreach(i, m; mappings)
24     {
25         static if(i != 0)
26             expr ~= sepSpec;
27         static if(isKeyLiteral(m.foreign_key))
28         {
29             // the foreign key is a value, which means we need to put it into
30             // the expression as-is. However, if the key is not a value, we
31             // need to swap the order.
32             static if(isKeyLiteral(m.key))
33             {
34                 expr ~= ExprString(m.foreign_key, " ", m.key);
35             }
36             else
37             {
38                 expr ~= ExprString(deptable, genKeyId!(T, m.key), " ", m.foreign_key);
39             }
40         }
41         else static if(isKeyLiteral(m.key))
42             expr ~= ExprString(tableid, genKeyId!(relation.foreign_table, m.foreign_key), " ", m.key);
43         else
44             expr ~= ExprString(tableid, genKeyId!(relation.foreign_table, m.foreign_key), " = ", deptable, genKeyId!(T, m.key));
45     }
46 
47     if(mappings.length > 1)
48         expr ~= endGroupSpec;
49     expr ~= ")";
50 
51     return TableDef(tableid[2 .. $], expr, [dependency]);
52 }
53 
54 TableDef buildTableDef(T)(string rootName = null)
55 {
56     auto expr = ExprString([getTableName!T.makeSpec(Spec.id)]);
57     if(rootName == null)
58         rootName = getTableName!T;
59     if(rootName != getTableName!T)
60     {
61         expr ~= " AS ";
62         expr ~= rootName.makeSpec(Spec.id);
63     }
64     return TableDef(rootName, expr);
65 }
66 
67 template staticTableDef(T, alias relation, Spec joinType, TableDef dependency, m...)
68 {
69     static const TableDef staticTableDef = buildTableDef!(T, relation, m)(joinType, dependency);
70 }
71 
72 template staticTableDef(T)
73 {
74     static const TableDef staticTableDef = buildTableDef!(T)();
75 }
76 
77 /*auto quoteIdentifier2(string name)
78 {
79     import std.range : chain;
80     return chain(`"`, name, `"`);
81 }*/
82 
83 private ColumnDef!T makeColumnDef(T)(const TableDef table, string tablename, string colname)
84 {
85     return ColumnDef!T(table, ExprString([tablename.makeSpec(Spec.tableid), colname.makeSpec(Spec.id)]));
86 }
87 
88 template DataSet(T)
89 {
90     alias DataSet = DataSet!(T, staticTableDef!(T), false);
91 }
92 
93 struct DataSet(T, alias core, bool AN)
94 {
95     alias RowType = T;
96     enum tableDef = core;
97     enum anyNull = AN;
98 
99     @property auto opDispatch(string item)() if (isField!(T, item))
100     {
101         // This is a column of the row, so just build the correct column definition.
102         import std.typecons : Nullable;
103         static if(anyNull && !is(typeof(__traits(getMember, T, item)) : Nullable!U, U))
104             alias X = Nullable!(typeof(__traits(getMember, T, item)));
105         else static if(is(getAllowNullType!(__traits(getMember, T, item))))
106         {
107             // this tells the fetcher how to deal with nulls properly
108             alias X = getAllowNullType!(__traits(getMember, T, item));
109         }
110         else
111             alias X = typeof(__traits(getMember, T, item));
112 
113         static auto result() {
114             return makeColumnDef!(X) (core, core.as,
115                         getColumnName!(__traits(getMember, T, item)));
116         }
117         if(__ctfe) return result();
118         static col = result();
119         return col;
120     }
121 
122     template opDispatch(string item) if (isRelation!(T, item))
123     {
124         @property auto opDispatch(Spec joinType = Spec.none)() if (joinType == Spec.none || isJoin(joinType))
125         {
126             // this is a relationship, use the UDAs assigned to the appropriate
127             // item to generate the new dataset.
128             enum field = getRelationField!(T, item);
129             static assert(field != null);
130             enum relation = getRelationFor!(__traits(getMember, T, field));
131             alias m = getMappingsFor!(__traits(getMember, T, field));
132             enum realJoinType = joinType == Spec.none ? (relation.joinType == Spec.none ? Spec.leftJoin : relation.joinType) : joinType;
133             return .DataSet!(relation.foreign_table, staticTableDef!(T, relation, realJoinType, core, m), anyNull || !relation.strong).init;
134         }
135 
136         @property auto opDispatch(Strong strongOverride, Spec joinType = Spec.none)() if (joinType == Spec.none || isJoin(joinType))
137         {
138             // this is a relationship, use the UDAs assigned to the appropriate
139             // item to generate the new dataset.
140             enum field = getRelationField!(T, item);
141             static assert(field != null);
142             enum relation = getRelationFor!(__traits(getMember, T, field));
143             alias m = getMappingsFor!(__traits(getMember, T, field));
144             enum realJoinType = joinType == Spec.none ? (relation.joinType == Spec.none ? Spec.leftJoin : relation.joinType) : joinType;
145             return .DataSet!(relation.foreign_table, staticTableDef!(T, relation, realJoinType, core, m), anyNull || !strongOverride).init;
146         }
147     }
148 
149     // shortcut for all columns
150     @property auto allColumns()
151     {
152         import std.typecons : Nullable;
153         static if(anyNull)
154             alias X = Nullable!T;
155         else
156             alias X = T;
157         static auto result() {
158             return ColumnDef!(RowObj!X)(core, ExprString(core.as.makeSpec(Spec.tableid), "*",
159                                                   objEndSpec));
160         }
161         if(__ctfe) return result();
162         static col = result();
163         return col;
164     }
165 }
166 
167 // gets a data set that is defined by the related table. Used when it's too
168 // cumbersome to define all the various relations in the primary table.
169 //
170 // if relationName is null, and there is only one relation between the tables,
171 // then that relation is used. If relationName is not null, then the relation
172 // must be that name. A relation name is required when 2 relations between the
173 // types exist.
174 //
175 // The join name is kind of convoluted, but hard to make a unique one that
176 // reads well in English. perhaps this can be improved (maybe reverse the table
177 // definitions up to that point).
178 auto related(T, string relationName = null, Spec joinType = Spec.none, Strong strongRelation = Strong.no, DS1)(DS1 dataset) if (isDataSet!DS1 && (joinType == Spec.none || isJoin(joinType)))
179 {
180     static if(relationName.length)
181     {
182         static assert(isRelation!(T, relationName),
183                       "No relation named " ~ relationName ~ " for " ~ T.stringof
184                       ~ " to " ~ DS1.RowType.stringof);
185         enum relatedField = getRelationField!(T, relationName);
186     }
187     else
188     {
189         enum relatedField = getRelationField!(T, dataset.RowType);
190         static assert(relatedField.length,
191                       "No relation for " ~ T.stringof ~ " to "
192                       ~ DS1.RowType.stringof);
193     }
194 
195     // now have the related field, fetch the relation and the mapping, and do
196     // it in reverse.
197     static assert(relatedField != null);
198     import std.meta : staticMap;
199     template recipMapping(mapping m)
200     {
201         enum recipMapping = mapping(m.key, m.foreign_key);
202     }
203     alias mappings = staticMap!(recipMapping, getMappingsFor!(__traits(getMember, T, relatedField)));
204     enum revRelation = getRelationFor!(__traits(getMember, T, relatedField));
205     enum relation = refersTo!T(getTableName!T ~ "_having_" ~ revRelation.name, revRelation.joinType, strongRelation);
206     enum realJoin = joinType == Spec.none ? (relation.joinType == Spec.none ? Spec.leftJoin : relation.joinType) : joinType;
207     return DataSet!(T, staticTableDef!(revRelation.foreign_table, relation, realJoin, dataset.tableDef, mappings), dataset.anyNull || !strongRelation).init;
208 }
209 
210 auto alwaysRelated(T, string relationName = null, Spec joinType = Spec.none, DS1)(DS1 dataset)
211 {
212     return related!(T, relationName, joinType, Strong.yes)(dataset);
213 }
214 
215 
216 version(unittest)
217 {
218     struct MyBool
219     {
220         bool _val;
221         string dbValue() { return _val ? "Y" : "N"; }
222         alias _val this;
223         static MyBool fromDbValue(string item) {
224             return MyBool(item == "Y" || item == "y");
225         }
226     }
227 
228     @tableName("author")
229     static struct Author
230     {
231         string firstName;
232         string lastName;
233         @primaryKey @autoIncrement int id = -1;
234         @colType("VARCHAR(1)") MyBool ynAnswer;
235 
236         // relations
237         static @mapping("author_id") @refersTo!book Relation books;
238         static @mapping("author_id") @mapping("book_type", "= 0") @refersTo!book Relation referenceBooks;
239     }
240 
241     enum BookType {
242         Reference,
243         Fiction
244     }
245     static struct book
246     {
247         @unique @colName("name") @colType("VARCHAR(100)") string title;
248         @refersTo!Author("author", Spec.innerJoin) @colName("auth_id") int author_id;
249         BookType book_type;
250         @primaryKey @autoIncrement int id = -1;
251     }
252     static struct review
253     {
254         @refersTo!book("book") int book_id;
255         @allowNull string comment;
256         @allowNull(-1) int rating;
257     }
258 }
259 
260 version(unittest)
261     import sqlbuilder.testing;
262 version(Have_mysql_native) unittest
263 {
264     import sqlbuilder.dialect.mysql;
265     import sqlbuilder.types;
266     import std.typecons : Nullable;
267     import std.stdio;
268     // create a dataset based on author
269     DataSet!(Author) ds;
270     with(ds)
271     {
272         auto q = select().where(lastName, " = ", "Alexandrescu".param).select(ds, books.title).orderBy(books.title);
273         //writeln(q);
274         writeln(q.sql);
275         writeln(q.params);
276         auto q2 = select(ds, referenceBooks.title).where(lastName, " = ", "Alexandrescu".param).orderBy(referenceBooks.title);
277         writeln(q2.sql);
278         writeln(q2.params);
279     }
280 
281     DataSet!(book) ds2;
282 
283     with(ds2)
284     {
285         import std.range;
286         static struct Optional(T)
287         {
288             Parameter!T value;
289             bool valid = false;
290             alias value this;
291         }
292         Optional!string s;
293         auto q = select(ds2, author.related!book.title.as("other_book_title")).where(author.lastName, " = ", s);
294         //pragma(msg, q.RowTypes);
295         writeln(q.sql);
296         writeln(q.RowTypes.stringof);
297         writeln(q.params);
298 
299         s.params = only("Alexandrescu");
300         s.valid = true;
301         auto q2 = select(allColumns, ds2.author.books!(Spec.innerJoin).title.as("other_book_title")).where(ds2.author.lastName, " = ", s);
302         writeln(q2.sql);
303         writeln(q2.RowTypes.stringof);
304         writeln(q2.params);
305         writeln(createTableSql!Author);
306         writeln(createTableSql!book);
307         writeln(createTableSql!review);
308     }
309 
310     // try some inserts
311     {
312         auto i = insert(Author("Andrei", "Alexandrescu"));
313         writeln(i.sql);
314         writeln(i.params);
315 
316         i = insert(ds.tableDef).set(ds.firstName, "Andrei".param);
317         writeln(i.sql);
318         writeln(i.params);
319     }
320 
321     // updates
322     {
323         auto u = update(Author("Steven", "Schveighoffer", 1));
324         writeln(u.sql);
325         writeln(u.params);
326 
327         u = set(ds.firstName, "George".param).where(ds.id, " = ", 5.param);
328         writeln(u.sql);
329         writeln(u.params);
330     }
331 
332     // deletes
333     {
334         auto d = remove(Author("Steven", "Schveighoffer", 1));
335         writeln(d.sql);
336         writeln(d.params);
337         d = removeFrom(ds.tableDef).havingKey(Author("Steven", "Schveighoffer", 1));
338         writeln(d.sql);
339         writeln(d.params);
340         d = removeFrom(ds.tableDef).havingKey(ds.books!(Spec.innerJoin), 1);
341         writeln(d.sql);
342         writeln(d.params);
343         d = removeFrom(ds.tableDef).havingKey!Author(1);
344         writeln(d.sql);
345         writeln(d.params);
346     }
347 
348     // TODO: CTFE support
349     // Idea: using a custom dialect that uses strings instead of data values,
350     // store the FQN of the types needed to create the actual query. Then use a
351     // template which processes the FQN to provide a custom function that
352     // accepts the right parameter types/names.
353     //auto blah = genSQL();
354     //enum ctsql = genSQL();
355     /*pragma(msg, ctsql);
356     writeln(ctsql);*/
357 }