The OpenD Programming Language

1 /++
2 	Helper functions for generating database stuff.
3 
4 	Note: this is heavily biased toward Postgres
5 +/
6 module arsd.database_generation;
7 
8 /*
9 
10 	FIXME: support partial indexes and maybe "using"
11 	FIXME: support views
12 
13 	Let's put indexes in there too and make index functions be the preferred way of doing a query
14 	by making them convenient af.
15 */
16 
17 private enum UDA;
18 
19 @UDA struct PrimaryKey {
20 	string sql;
21 }
22 
23 @UDA struct Default {
24 	string sql;
25 }
26 
27 @UDA struct Unique { }
28 
29 @UDA struct ForeignKey(alias toWhat, string behavior) {
30 	alias ReferencedTable = __traits(parent, toWhat);
31 }
32 
33 enum CASCADE = "ON UPDATE CASCADE ON DELETE CASCADE";
34 enum NULLIFY = "ON UPDATE CASCADE ON DELETE SET NULL";
35 enum RESTRICT = "ON UPDATE CASCADE ON DELETE RESTRICT";
36 
37 @UDA struct DBName { string name; }
38 
39 struct Nullable(T) {
40 	bool isNull = true;
41 	T value;
42 
43 	void opAssign(typeof(null)) {
44 		isNull = true;
45 	}
46 
47 	void opAssign(T v) {
48 		isNull = false;
49 		value = v;
50 	}
51 
52 	T toArsdJsvar() { return value; }
53 
54 	string toString() {
55 		import std.conv;
56 
57 		if (isNull) {
58 			return "Nullable.null";
59 		}
60 		else {
61 			return to!string(this.value);
62 		}
63 	}
64 }
65 
66 struct Timestamp {
67 	string value;
68 	string toArsdJsvar() { return value; }
69 
70 	// FIXME: timezone
71 	static Timestamp fromStrings(string date, string time) {
72 	        if(time.length < 6)
73 			time ~= ":00";
74 		import std.datetime;
75 		return Timestamp(SysTime.fromISOExtString(date ~ "T" ~ time).toISOExtString());
76 	}
77 }
78 
79 SysTime parseDbTimestamp(Timestamp s) {
80 	return parseDbTimestamp(s.value);
81 }
82 
83 SysTime parseDbTimestamp(string s) {
84 	if(s.length == 0) return SysTime.init;
85 	auto date = s[0 .. 10];
86 	auto time = s[11 .. 20];
87 	auto tz = s[20 .. $];
88 	return SysTime.fromISOExtString(date ~ "T" ~ time ~ tz);
89 }
90 
91 struct Constraint(string sql) {}
92 
93 struct Index(Fields...) {}
94 struct UniqueIndex(Fields...) {}
95 
96 struct Serial {
97 	int value;
98 	int toArsdJsvar() { return value; }
99 	int getValue() { return value; }
100 	alias getValue this;
101 }
102 
103 
104 string generateCreateTableFor(alias O)() {
105 	enum tableName = tableNameFor!O();
106 	string sql = "CREATE TABLE " ~ tableName ~ " (";
107 	string postSql;
108 	bool outputtedPostSql = false;
109 
110 	string afterTableSql;
111 
112 	void addAfterTableSql(string s) {
113 		afterTableSql ~= s;
114 		afterTableSql ~= "\n";
115 	}
116 
117 	void addPostSql(string s) {
118 		if(outputtedPostSql) {
119 			postSql ~= ",";
120 		}
121 		postSql ~= "\n";
122 		postSql ~= "\t" ~ s;
123 		outputtedPostSql = true;
124 	}
125 
126 	bool outputted = false;
127 	static foreach(memberName; __traits(allMembers, O)) {{
128 		alias member = __traits(getMember, O, memberName);
129 		static if(is(typeof(member) == Constraint!constraintSql, string constraintSql)) {
130 		version(dbgenerate_sqlite) {} else { // FIXME: make it work here too, it is the specifics of the constraint strings
131 			if(outputted) {
132 				sql ~= ",";
133 			}
134 			sql ~= "\n";
135 			sql ~= "\tCONSTRAINT " ~ memberName;
136 			sql ~= " ";
137 			sql ~= constraintSql;
138 			outputted = true;
139 		}
140 		} else static if(is(typeof(member) == Index!Fields, Fields...)) {
141 			string fields = "";
142 			static foreach(field; Fields) {
143 				if(fields.length)
144 					fields ~= ", ";
145 				fields ~= __traits(identifier, field);
146 			}
147 			addAfterTableSql("CREATE INDEX " ~ tableName ~ "_" ~ memberName ~ " ON " ~ tableName ~ "("~fields~")");
148 		} else static if(is(typeof(member) == UniqueIndex!Fields, Fields...)) {
149 			string fields = "";
150 			static foreach(field; Fields) {
151 				if(fields.length)
152 					fields ~= ", ";
153 				fields ~= __traits(identifier, field);
154 			}
155 			addAfterTableSql("CREATE UNIQUE INDEX " ~ tableName ~ "_" ~ memberName ~ " ON " ~ tableName ~ "("~fields~")");
156 		} else static if(is(typeof(member) T)) {
157 			if(outputted) {
158 				sql ~= ",";
159 			}
160 			sql ~= "\n";
161 			sql ~= "\t" ~ memberName;
162 
163 			static if(is(T == Nullable!P, P)) {
164 				static if(is(P == int))
165 					sql ~= " INTEGER NULL";
166 				else static if(is(P == string))
167 					sql ~= " TEXT NULL";
168 				else static if(is(P == double))
169 					sql ~= " FLOAT NULL";
170 				else static if(is(P == Timestamp))
171 					sql ~= " TIMESTAMPTZ NULL";
172 				else static assert(0, P.stringof);
173 			} else static if(is(T == int))
174 				sql ~= " INTEGER NOT NULL";
175 			else static if(is(T == Serial)) {
176 				version(dbgenerate_sqlite)
177 					sql ~= " INTEGER PRIMARY KEY AUTOINCREMENT";
178 				else
179 					sql ~= " SERIAL"; // FIXME postgresism
180 			} else static if(is(T == string))
181 				sql ~= " TEXT NOT NULL";
182 			else static if(is(T == double))
183 				sql ~= " FLOAT NOT NULL";
184 			else static if(is(T == bool))
185 				sql ~= " BOOLEAN NOT NULL";
186 			else static if(is(T == Timestamp)) {
187 				version(dbgenerate_sqlite)
188 					sql ~= " TEXT NOT NULL";
189 				else
190 					sql ~= " TIMESTAMPTZ NOT NULL"; // FIXME: postgresism
191 			} else static if(is(T == enum))
192 				sql ~= " INTEGER NOT NULL"; // potentially crap but meh
193 
194 			static foreach(attr; __traits(getAttributes, member)) {
195 				static if(is(typeof(attr) == Default)) {
196 					// FIXME: postgresism there, try current_timestamp in sqlite
197 					version(dbgenerate_sqlite) {
198 						import std.string;
199 						sql ~= " DEFAULT " ~ std..string.replace(attr.sql, "now()", "current_timestamp");
200 					} else
201 						sql ~= " DEFAULT " ~ attr.sql;
202 				} else static if(is(attr == Unique)) {
203 					sql ~= " UNIQUE";
204 				} else static if(is(attr == PrimaryKey)) {
205 					version(dbgenerate_sqlite) {
206 						static if(is(T == Serial)) {} // skip, it is done above
207 						else
208 						addPostSql("PRIMARY KEY(" ~ memberName ~ ")");
209 					} else
210 						addPostSql("PRIMARY KEY(" ~ memberName ~ ")");
211 				} else static if(is(attr == ForeignKey!(to, sqlPolicy), alias to, string sqlPolicy)) {
212 					string refTable = tableNameFor!(__traits(parent, to))();
213 					string refField = to.stringof;
214 					addPostSql("FOREIGN KEY(" ~ memberName ~ ") REFERENCES "~refTable~"("~refField~(sqlPolicy.length ? ") " : ")") ~ sqlPolicy);
215 				}
216 			}
217 
218 			outputted = true;
219 		}
220 	}}
221 
222 	if(postSql.length && outputted)
223 		sql ~= ",\n";
224 
225 	sql ~= postSql;
226 	sql ~= "\n);\n";
227 	sql ~= afterTableSql;
228 
229 	return sql;
230 }
231 
232 string tableNameFor(T)(string def = toTableName(T.stringof)) {
233 	foreach(attr; __traits(getAttributes, T))
234 		static if(is(typeof(attr) == DBName))
235 			def = attr.name;
236 	return def;
237 }
238 
239 string toTableName(string t) {
240 	return plural(50, beautify(t, '_', true));
241 }
242 
243 // copy/pasted from english.d
244 private string plural(int count, string word, string pluralWord = null) {
245 	if(count == 1 || word.length == 0)
246 		return word; // it isn't actually plural
247 
248 	if(pluralWord !is null)
249 		return pluralWord;
250 
251 	switch(word[$ - 1]) {
252 		case 's':
253 			return word ~ "es";
254 		case 'f':
255 			return word[0 .. $-1] ~ "ves";
256 		case 'y':
257 			return word[0 .. $-1] ~ "ies";
258 		case 'a', 'e', 'i', 'o', 'u':
259 		default:
260 			return word ~ "s";
261 	}
262 }
263 
264 // copy/pasted from cgi
265 private string beautify(string name, char space = ' ', bool allLowerCase = false) {
266 	if(name == "id")
267 		return allLowerCase ? name : "ID";
268 
269 	char[160] buffer;
270 	int bufferIndex = 0;
271 	bool shouldCap = true;
272 	bool shouldSpace;
273 	bool lastWasCap;
274 	foreach(idx, char ch; name) {
275 		if(bufferIndex == buffer.length) return name; // out of space, just give up, not that important
276 
277 		if((ch >= 'A' && ch <= 'Z') || ch == '_') {
278 			if(lastWasCap) {
279 				// two caps in a row, don't change. Prolly acronym.
280 			} else {
281 				if(idx)
282 					shouldSpace = true; // new word, add space
283 			}
284 
285 			lastWasCap = true;
286 		} else {
287 			lastWasCap = false;
288 		}
289 
290 		if(shouldSpace) {
291 			buffer[bufferIndex++] = space;
292 			if(bufferIndex == buffer.length) return name; // out of space, just give up, not that important
293 			shouldSpace = false;
294 		}
295 		if(shouldCap) {
296 			if(ch >= 'a' && ch <= 'z')
297 				ch -= 32;
298 			shouldCap = false;
299 		}
300 		if(allLowerCase && ch >= 'A' && ch <= 'Z')
301 			ch += 32;
302 		buffer[bufferIndex++] = ch;
303 	}
304 	return buffer[0 .. bufferIndex].idup;
305 }
306 
307 import arsd.database;
308 /++
309 
310 +/
311 void save(O)(ref O t, Database db) {
312 	t.insert(db);
313 }
314 
315 /++
316 
317 +/
318 void insert(O)(ref O t, Database db) {
319 	auto builder = new InsertBuilder;
320 	builder.setTable(tableNameFor!O());
321 
322 	static foreach(memberName; __traits(allMembers, O)) {{
323 		alias member = __traits(getMember, O, memberName);
324 		static if(is(typeof(member) T)) {
325 
326 			static if(is(T == Nullable!P, P)) {
327 				auto v = __traits(getMember, t, memberName);
328 				if(v.isNull)
329 					builder.addFieldWithSql(memberName, "NULL");
330 				else
331 					builder.addVariable(memberName, v.value);
332 			} else static if(is(T == int))
333 				builder.addVariable(memberName, __traits(getMember, t, memberName));
334 			else static if(is(T == Serial)) {
335 				auto v = __traits(getMember, t, memberName).value;
336 				if(v) {
337 					builder.addVariable(memberName, v);
338 				} else {
339 					// skip and let it auto-fill
340 				}
341 			} else static if(is(T == string)) {
342 				builder.addVariable(memberName, __traits(getMember, t, memberName));
343 			} else static if(is(T == double))
344 				builder.addVariable(memberName, __traits(getMember, t, memberName));
345 			else static if(is(T == bool))
346 				builder.addVariable(memberName, __traits(getMember, t, memberName));
347 			else static if(is(T == Timestamp)) {
348 				auto v = __traits(getMember, t, memberName).value;
349 				if(v.length)
350 					builder.addVariable(memberName, v);
351 			} else static if(is(T == enum))
352 				builder.addVariable(memberName, cast(int) __traits(getMember, t, memberName));
353 		}
354 	}}
355 
356 	import std.conv;
357 	version(dbgenerate_sqlite) {
358 		builder.execute(db);
359 		foreach(row; db.query("SELECT max(id) FROM " ~ tableNameFor!O()))
360 			t.id.value = to!int(row[0]);
361 	} else {
362 		static if (__traits(hasMember, O, "id"))
363 		{
364 			foreach(row; builder.execute(db, "RETURNING id")) // FIXME: postgres-ism
365 				t.id.value = to!int(row[0]);
366 		}
367 		else
368 		{
369 			builder.execute(db);
370 		}
371 	}
372 }
373 
374 // Check that insert doesn't require an `id`
375 unittest
376 {
377 	static struct NoPK
378 	{
379 		int a;
380 	}
381 
382 	alias test = insert!NoPK;
383 }
384 ///
385 class RecordNotFoundException : Exception {
386 	this() { super("RecordNotFoundException"); }
387 }
388 
389 /++
390 	Returns a given struct populated from the database. Assumes types known to this module.
391 
392 	MyItem item = db.find!(MyItem.id)(3);
393 
394 	If you just give a type, it assumes the relevant index is "id".
395 
396 +/
397 static auto find(alias T)(Database db, int id) {
398 	// FIXME:
399 	// if it is unique, return an individual item.
400 	// if not, return the array
401 	static if (!is(T)) {
402 		static const string fieldName = T.stringof;
403 		alias FType = typeof(T); // field type
404 		alias TType = __traits(parent, T); // Table type
405 	}
406 	else {
407 		static const string fieldName = "id";
408 		alias FType = int;
409 		alias TType = T;
410 	}
411 
412 	static assert(is(FType : int),
413 			TType.stringof ~ "." ~ fieldName ~ " should be an Integral field");
414 
415 	string q = "SELECT * FROM " ~ tableNameFor!TType() ~ " WHERE " ~ fieldName ~ " = ?";
416 	foreach(record; db.query(q, id)) {
417 		TType t;
418 		populateFromDbRow(t, record);
419 
420 		return t;
421 		// if there is ever a second record, that's a wtf, but meh.
422 	}
423 	throw new RecordNotFoundException();
424 }
425 
426 private void populateFromDbRow(T)(ref T t, Row record) {
427 	foreach(field, value; record) {
428 		sw: switch(field) {
429 			static foreach(const idx, alias mem; T.tupleof) {
430 				case __traits(identifier, mem):
431 					populateFromDbVal(t.tupleof[idx], value);
432 				break sw;
433 			}
434 			default:
435 				// intentionally blank
436 		}
437 	}
438 }
439 
440 private void populateFromDbVal(V)(ref V val, string /*DatabaseDatum*/ value) {
441 	import std.conv;
442 	static if(is(V == Constraint!constraintSql, string constraintSql)) {
443 
444 	} else static if(is(V == Nullable!P, P)) {
445 		// FIXME
446 		if(value.length && value != "null" && value != "<null>") {
447 			val.isNull = false;
448 			import std.stdio; writeln(value);
449 			val.value = to!P(value);
450 		}
451 	} else static if(is(V == bool)) {
452 		val = value == "t" || value == "1" || value == "true";
453 	} else static if(is(V == int) || is(V == string) || is(V == double)) {
454 		val = to!V(value);
455 	} else static if(is(V == enum)) {
456 		val = cast(V) to!int(value);
457 	} else static if(is(V == Timestamp)) {
458 		val.value = value;
459 	} else static if(is(V == Serial)) {
460 		val.value = to!int(value);
461 	}
462 }
463 
464 unittest
465 {
466 	static struct SomeStruct
467 	{
468 		int a;
469 		void foo() {}
470 		int b;
471 	}
472 
473 	auto rs = new PredefinedResultSet(
474 		[ "a", "b" ],
475 		[ Row([ DatabaseDatum("1"), DatabaseDatum("2") ]) ]
476 	);
477 
478 	SomeStruct s;
479 	populateFromDbRow(s, rs.front);
480 
481 	assert(s.a == 1);
482 	assert(s.b == 2);
483 }
484 /++
485 	Gets all the children of that type. Specifically, it looks in T for a ForeignKey referencing B and queries on that.
486 
487 	To do a join through a many-to-many relationship, you could get the children of the join table, then get the children of that...
488 	Or better yet, use real sql. This is more intended to get info where there is one parent row and then many child
489 	rows, not for a combined thing.
490 +/
491 QueryBuilderHelper!(T[]) children(T, B)(B base) {
492 	int countOfAssociations() {
493 		int count = 0;
494 		static foreach(memberName; __traits(allMembers, T))
495 		static foreach(attr; __traits(getAttributes, __traits(getMember, T, memberName))) {{
496 			static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) {
497 				static if(is(attr.ReferencedTable == B))
498 					count++;
499 			}
500 		}}
501 		return count;
502 	}
503 	static assert(countOfAssociations() == 1, T.stringof ~ " does not have exactly one foreign key of type " ~ B.stringof);
504 	string keyName() {
505 		static foreach(memberName; __traits(allMembers, T))
506 		static foreach(attr; __traits(getAttributes, __traits(getMember, T, memberName))) {{
507 			static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) {
508 				static if(is(attr.ReferencedTable == B))
509 					return memberName;
510 			}
511 		}}
512 	}
513 
514 	// return QueryBuilderHelper!(T[])(toTableName(T.stringof)).where!(mixin(keyName ~ " => base.id"));
515 
516 	// changing mixin cuz of regression in dmd 2.088
517 	mixin("return QueryBuilderHelper!(T[])(tableNameFor!T()).where!("~keyName ~ " => base.id);");
518 }
519 
520 /++
521 	Finds the single row associated with a foreign key in `base`.
522 
523 	`T` is used to find the key, unless ambiguous, in which case you must pass `key`.
524 
525 	To do a join through a many-to-many relationship, go to [children] or use real sql.
526 +/
527 T associated(B, T, string key = null)(B base, Database db) {
528 	int countOfAssociations() {
529 		int count = 0;
530 		static foreach(memberName; __traits(allMembers, B))
531 		static foreach(attr; __traits(getAttributes, __traits(getMember, B, memberName))) {
532 			static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) {
533 				static if(is(attr.ReferencedTable == T))
534 					static if(key is null || key == memberName)
535 						count++;
536 			}
537 		}
538 		return count;
539 	}
540 
541 	static if(key is null) {
542 		enum coa = countOfAssociations();
543 		static assert(coa != 0, B.stringof ~ " has no association of type " ~ T);
544 		static assert(coa == 1, B.stringof ~ " has multiple associations of type " ~ T ~ "; please specify the key you want");
545 		static foreach(memberName; __traits(allMembers, B))
546 		static foreach(attr; __traits(getAttributes, __traits(getMember, B, memberName))) {
547 			static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) {
548 				static if(is(attr.ReferencedTable == T))
549 					return db.find!T(__traits(getMember, base, memberName));
550 			}
551 		}
552 	} else {
553 		static assert(countOfAssociations() == 1, B.stringof ~ " does not have a key named " ~ key ~ " of type " ~ T);
554 		static foreach(attr; __traits(getAttributes, __traits(getMember, B, memberName))) {
555 			static if(is(attr == ForeignKey!(K, policy), alias K, string policy)) {
556 				static if(is(attr.ReferencedTable == T)) {
557 					return db.find!T(__traits(getMember, base, key));
558 				}
559 			}
560 		}
561 		assert(0);
562 	}
563 }
564 
565 
566 /++
567 	It will return an aggregate row with a member of type of each table in the join.
568 
569 	Could do an anonymous object for other things in the sql...
570 +/
571 auto join(TableA, TableB, ThroughTable = void)() {}
572 
573 /++
574 
575 +/
576 struct QueryBuilderHelper(T) {
577 	static if(is(T == R[], R))
578 		alias TType = R;
579 	else
580 		alias TType = T;
581 
582 	SelectBuilder selectBuilder;
583 
584 	this(string tableName) {
585 		selectBuilder = new SelectBuilder();
586 		selectBuilder.table = tableName;
587 		selectBuilder.fields = ["*"];
588 	}
589 
590 	T execute(Database db) {
591 		selectBuilder.db = db;
592 		static if(is(T == R[], R)) {
593 
594 		} else {
595 			selectBuilder.limit = 1;
596 		}
597 
598 		T ret;
599 		bool first = true;
600 		foreach(row; db.query(selectBuilder.toString())) {
601 			TType t;
602 			populateFromDbRow(t, row);
603 
604 			static if(is(T == R[], R))
605 				ret ~= t;
606 			else {
607 				if(first) {
608 					ret = t;
609 					first = false;
610 				} else {
611 					assert(0);
612 				}
613 			}
614 		}
615 		return ret;
616 	}
617 
618 	///
619 	typeof(this) orderBy(string criterion)() {
620 		string name() {
621 			int idx = 0;
622 			while(idx < criterion.length && criterion[idx] != ' ')
623 				idx++;
624 			return criterion[0 .. idx];
625 		}
626 
627 		string direction() {
628 			int idx = 0;
629 			while(idx < criterion.length && criterion[idx] != ' ')
630 				idx++;
631 			import std.string;
632 			return criterion[idx .. $].strip;
633 		}
634 
635 		static assert(is(typeof(__traits(getMember, TType, name()))), TType.stringof ~ " has no field " ~ name());
636 		static assert(direction().length == 0 || direction() == "ASC" || direction() == "DESC", "sort direction must be empty, ASC, or DESC");
637 
638 		selectBuilder.orderBys ~= criterion;
639 		return this;
640 	}
641 }
642 
643 QueryBuilderHelper!(T[]) from(T)() {
644 	return QueryBuilderHelper!(T[])(tableNameFor!T());
645 }
646 
647 /// ditto
648 template where(conditions...) {
649 	Qbh where(Qbh)(Qbh this_, string[] sqlCondition...) {
650 		assert(this_.selectBuilder !is null);
651 
652 		static string extractName(string s) {
653 			if(s.length == 0) assert(0);
654 			auto i = s.length - 1;
655 			while(i) {
656 				if(s[i] == ')') {
657 					// got to close paren, now backward to non-identifier char to get name
658 					auto end = i;
659 					while(i) {
660 						if(s[i] == ' ')
661 							return s[i + 1 .. end];
662 						i--;
663 					}
664 					assert(0);
665 				}
666 				i--;
667 			}
668 			assert(0);
669 		}
670 
671 		static foreach(idx, cond; conditions) {{
672 			// I hate this but __parameters doesn't work here for some reason
673 			// see my old thread: https://forum.dlang.org/post/awjuoemsnmxbfgzhgkgx@forum.dlang.org
674 			enum name = extractName(typeof(cond!int).stringof);
675 			auto value = cond(null);
676 
677 			// FIXME: convert the value as necessary
678 			static if(is(typeof(value) == Serial))
679 				auto dbvalue = value.value;
680 			else static if(is(typeof(value) == enum))
681 				auto dbvalue = cast(int) value;
682 			else
683 				auto dbvalue = value;
684 
685 			import std.conv;
686 
687 			static assert(is(typeof(__traits(getMember, Qbh.TType, name))), Qbh.TType.stringof ~ " has no member " ~ name);
688 			static if(is(typeof(__traits(getMember, Qbh.TType, name)) == int)) {
689 				static if(is(typeof(value) : const(int)[])) {
690 					string s;
691 					foreach(v; value) {
692 						if(s.length) s ~= ", ";
693 						s ~= to!string(v);
694 					}
695 					this_.selectBuilder.wheres ~= name ~ " IN (" ~ s ~ ")";
696 				} else {
697 					static assert(is(typeof(value) : const(int)) || is(typeof(value) == Serial), Qbh.TType.stringof ~ " is a integer key, but you passed an incompatible " ~ typeof(value).stringof);
698 
699 					auto placeholder = "?_internal" ~ to!string(idx);
700 					this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder;
701 					this_.selectBuilder.setVariable(placeholder, dbvalue);
702 				}
703 			} else static if(is(typeof(__traits(getMember, Qbh.TType, name)) == Nullable!int)) {
704 				static if(is(typeof(value) : const(int)[])) {
705 					string s;
706 					foreach(v; value) {
707 						if(s.length) s ~= ", ";
708 						s ~= to!string(v);
709 					}
710 					this_.selectBuilder.wheres ~= name ~ " IN (" ~ s ~ ")";
711 				} else {
712 					static assert(is(typeof(value) : const(int)) || is(typeof(value) == Serial), Qbh.TType.stringof ~ " is a integer key, but you passed an incompatible " ~ typeof(value).stringof);
713 
714 					auto placeholder = "?_internal" ~ to!string(idx);
715 					this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder;
716 					this_.selectBuilder.setVariable(placeholder, dbvalue);
717 				}
718 			} else static if(is(typeof(__traits(getMember, Qbh.TType, name)) == Serial)) {
719 				static if(is(typeof(value) : const(int)[])) {
720 					string s;
721 					foreach(v; value) {
722 						if(s.length) s ~= ", ";
723 						s ~= to!string(v);
724 					}
725 					this_.selectBuilder.wheres ~= name ~ " IN (" ~ s ~ ")";
726 				} else {
727 					static assert(is(typeof(value) : const(int)) || is(typeof(value) == Serial), Qbh.TType.stringof ~ " is a integer key, but you passed an incompatible " ~ typeof(value).stringof);
728 
729 					auto placeholder = "?_internal" ~ to!string(idx);
730 					this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder;
731 					this_.selectBuilder.setVariable(placeholder, dbvalue);
732 				}
733 
734 
735 			} else {
736 				static assert(is(typeof(__traits(getMember, Qbh.TType, name)) == typeof(value)), Qbh.TType.stringof ~ "." ~ name ~ " is not of type " ~ typeof(value).stringof);
737 
738 				auto placeholder = "?_internal" ~ to!string(idx);
739 				this_.selectBuilder.wheres ~= name ~ " = " ~ placeholder;
740 				this_.selectBuilder.setVariable(placeholder, dbvalue);
741 			}
742 		}}
743 
744 		this_.selectBuilder.wheres ~= sqlCondition;
745 		return this_;
746 	}
747 }
748 
749 // Basically a wrapper for a ResultSet
750 struct TabResultSet(T)
751 {
752 	this(ResultSet result)
753 	{
754 		this.result = result;
755 	}
756 
757 	bool empty() @property
758 	{
759 		return this.result.empty;
760 	}
761 
762 	T front() @property
763 	{
764 		T row;
765 		row.populateFromDbRow(this.result.front);
766 		return row;
767 	}
768 
769 	void popFront()
770 	{
771 		this.result.popFront();
772 	}
773 
774 	size_t length() @property
775 	{
776 		return this.result.length;
777 	}
778 
779 	private ResultSet result;
780 }
781 
782 // ditto
783 TabResultSet!T to_table_rows(T)(ResultSet res)
784 {
785 	return TabResultSet!T(res);
786 }
787 
788 private template FieldReference(alias field_)
789 {
790 	alias Table = __traits(parent, field_);
791 	alias field = field_;
792 }
793 
794 private template isFieldRefInAttributes(Attributes...)
795 {
796 	static if (Attributes.length == 0) {
797 		static immutable bool isFieldRefInAttributes = false;
798 	}
799 	else {
800 		alias attr = Attributes[0];
801 		static if (is(attr == ForeignKey!(field, s), alias field, string s)) {
802 			static immutable bool isFieldRefInAttributes = true;
803 		}
804 		else {
805 			static immutable bool fieldRefInAttributes =
806 				isFieldRefInAttributes!(Attributes[1..$]);
807 		}
808 	}
809 }
810 
811 private template getFieldRefInAttributes(Attributes...)
812 {
813 	alias attr = Attributes[0];
814 	static if (is(attr == ForeignKey!(field, s), alias field, string s)) {
815 		alias getFieldRefInAttributes = FieldReference!(field);
816 	}
817 	else {
818 		alias fieldRefInAttributes =
819 			getFieldRefInAttributes!(RT, Attributes[1..$]);
820 	}
821 }
822 
823 private alias getRefToField(alias fk_field) =
824 	getFieldRefInAttributes!(__traits(getAttributes, fk_field));
825 
826 unittest
827 {
828 	struct Role { int id; }
829 
830 	struct User
831 	{
832 		int id;
833 		@ForeignKey!(Role.id, "") int role_id;
834 	}
835 
836 	alias FieldRef = getRefToField!(User.role_id);
837 	assert(is(FieldRef.Table == Role));
838 	assert(__traits(isSame, FieldRef.field, Role.id));
839 }
840 
841 string toFieldName(T)(string s, bool isPlural = false)
842 {
843 	int cnt = isPlural ? 2 : 1;
844 	if (s is null)
845 		return plural(cnt, beautify(tableNameFor!T(), '_', true));
846 	return s;
847 }
848 
849 /++
850 	generates get functions for a one-to-many relationship with the form
851 	`T2 get_<t2>(T1 row, Database db)` and
852 	`TabResultSet!T1 get_<t1>(T2 row, Database db)`
853 
854 
855 	[children] also works with a one-to-many relationship, but they are different in that [children] only gives you the many in the one-to-many relationship and only works with a single foreign key at a time.
856 
857 	Say you have a User and Role tables where each User has a role and a Role can be used by multiple users, with:
858 
859 	---
860 	/*
861 	This would give you all of the users with the Role `role`.
862 	*/
863 	auto res = role.children!(User, Role).execute(db);
864 	---
865 
866 	However if you wanted to get the Role of a user there would be no way of doing so with children. It doesn't work the other way around.
867 
868 	Also the big thing that one_to_many can do and children can not do is handle multiple relationships(Multiple foreign keys pointing to the same Table for example:
869 
870 	---
871 	import std.stdio;
872 	import arsd.sqlite;
873 	import arsd.database_generation;
874 
875 	alias FK(alias toWhat) = ForeignKey!(toWhat, null);
876 
877 	@DBName("Professor") struct Professor
878 	{
879 	    int id;
880 	    string name;
881 	}
882 
883 	@DBName("Course") struct Course
884 	{
885 	    int id;
886 	    @FK!(Professor.id) int professor_id;
887 	    @FK!(Professor.id) int assistant_id;
888 	}
889 
890 	mixin(one_to_many!(Course.professor_id, "prof", "courses_taught"));
891 	mixin(one_to_many!(Course.assistant_id, "assistant", "courses_assisted"));
892 
893 	void main()
894 	{
895 	    Database db = new Sqlite("test2.db");
896 
897 	    Course course = db.find!Course(1);
898 	    Professor prof = course.get_prof(db);
899 
900 	    writeln(prof.get_courses_taught(db));
901 	    writeln(prof.get_courses_assisted(db));
902 	}
903 	---
904 
905 	Here there are 2 relationships from Course to Professor here. One of them you can get from get_courses_taught and the other one with get_courses_assisted.
906 	If you attempt to use children like so
907 
908 	---
909 	writeln(prof.children!(Course, Professor).execute(db));
910 	---
911 
912 	You would get:
913 	$(CONSOLE
914 		source/arsd/database_generation.d(489,2): Error: static assert: "Course does not have exactly one foreign key of type Professor"
915 	)
916 
917 	In conclusion, children is nice in that its simple, doesn't require mixins to create extra symbols(functions). However it doesn't handle the one in one-to-many relationships at all, and it also doesn't work in tables with more than one relationship to a table. And finally, you might prefer the syntax of `prof.get_courses(db)` over `prof.children!(Course, Professor).execute(db)`.
918 
919 	Examples:
920 
921 	---
922 	Struct Role { int id; }
923 	struct User {
924 		@ForeignKey!(Role.id, "") int role_id;
925 	}
926 
927 	mixin(one_to_many!(User.role_id, "role", "users"));
928 	void main()
929 	{
930 		Database db = ...
931 		User user = db.find!User(1);
932 		Role role = user.get_role(db);
933 		auto users = role.get_users(db);
934 	}
935 	---
936 
937 	if t2 or t1 are set as "" the get function will not be generated
938 	(the name will not be inferred), if set as null they will be inferred from
939 	either the `DBName` attribute or from the name of the Table.
940 
941 	History:
942 		Added November 5, 2022 (dub v10.10)
943 +/
944 template one_to_many(alias fk_field, string t2 = null, string t1 = null)
945 {
946 	alias T1 = __traits(parent, fk_field);
947 
948 	static assert(
949 		isFieldRefInAttributes!(__traits(getAttributes, fk_field)),
950 		T1.stringof ~ "." ~ fk_field.stringof ~ " does't have a ForeignKey");
951 
952 	alias FieldRef = getRefToField!(fk_field);
953 	alias T2 = FieldRef.Table;
954 	alias ref_field = FieldRef.field;
955 
956 	immutable string t2_name = toFieldName!T2(t2);
957 	immutable string t1_name = toFieldName!T1(t1, true);
958 
959 	static immutable string one = (t2 is "") ? "" :
960 		T2.stringof~` get_`~t2_name~`(`~T1.stringof~` row, Database db)
961 		{
962 			import std.exception;
963 
964 			enforce(db !is null, "Database must not be null");
965 			auto fk_id = row.`~fk_field.stringof~`;
966 
967 			auto res = db.query(
968 				"select * from `~tableNameFor!T2()~`" ~
969 				" where `~ref_field.stringof~` = ?", fk_id
970 			).to_table_rows!`~T2.stringof~`;
971 
972 			return res.front();
973 		}`;
974 	static immutable string many = (t1 is "") ? "" : `
975 		TabResultSet!`~T1.stringof~` get_`~t1_name~`(`~T2.stringof~` row, Database db)
976 		{
977 			import std.exception;
978 
979 			enforce(db !is null, "Database must not be null");
980 			auto id = row.`~ref_field.stringof~`;
981 
982 			auto res = db.query(
983 				"select * from `~tableNameFor!T1()~`"~
984 				" where `~fk_field.stringof~` = ?", id
985 			).to_table_rows!`~T1.stringof~`;
986 
987 			return res;
988 		}`;
989 	static immutable string one_to_many = one ~ many;
990 }