The OpenD Programming Language

1 /++
2 	Generic interface for RDBMS access. Use with one of the implementations in [arsd.mysql], [arsd.sqlite], [arsd.postgres], or [arsd.mssql]. I'm sorry the docs are not good, but a little bit goes a long way:
3 
4 	---
5 	auto db = new Sqlite("file.db"); // see the implementations for constructors
6 	// then the interface, for any impl can be as simple as:
7 
8 	foreach(row; db.query("SELECT id, name FROM people")) {
9              string id = row[0];
10 	     string name = row[1];
11 	}
12 
13 	db.query("INSERT INTO people (id, name) VALUES (?, ?)", 5, "Adam");
14 	---
15 
16 	To convert to other types, just use [std.conv.to] since everything comes out of this as simple strings with the exception of binary data,
17 	which you'll want to cast to const(ubyte)[].
18 
19 	History:
20 		Originally written prior to 2011.
21 
22 		On August 2, 2022, the behavior of BLOB (or BYTEA in postgres) changed significantly.
23 		Before, it would convert to strings with `to!string(bytes)` on insert and platform specific
24 		on query. It didn't really work at all.
25 
26 		It now actually stores ubyte[] as a blob and retrieves it without modification. Note you need to
27 		cast it.
28 
29 		This is potentially breaking, but since it didn't work much before I doubt anyone was using it successfully
30 		but this might be a problem. I advise you to retest.
31 
32 		Be aware I don't like this string interface much anymore and want to change it significantly but idk
33 		how to work it in without breaking a decade of code.
34 
35 		On June 7, 2023 (dub 11.0), I started the process of moving away from strings as the inner storage unit. This is a potentially breaking change, but you can use `.toString` to convert as needed and `alias this` will try to do this automatically in many situations. See [DatabaseDatum] for details. This transition is not yet complete.
36 
37 		Notably, passing it to some std.string functions will cause errors referencing DatabaseDatum like:
38 
39 		$(CONSOLE
40 		Error: template `std.array.replace` cannot deduce function from argument types `!()(string, string, DatabaseDatum)`
41 		path/phobos/std/array.d(2459):        Candidates are: `replace(E, R1, R2)(E[] subject, R1 from, R2 to)`
42 		  with `E = immutable(char),
43 		       R1 = string,
44 		       R2 = DatabaseDatum`
45 		)
46 
47 		Because templates do not trigger alias this - you will need to call `.toString()` yourself at the usage site.
48 +/
49 module arsd.database;
50 
51 // FIXME: add some kind of connection pool thing we can easily use
52 
53 // I should do a prepared statement as a template string arg
54 
55 public import std.variant;
56 import std.string;
57 public import std.datetime;
58 
59 static import arsd.core;
60 private import arsd.core : LimitedVariant;
61 
62 /*
63 	Database 2.0 plan, WIP:
64 
65 	// Do I want to do some kind of RAII?
66 	auto database = Database(new MySql("connection info"));
67 
68 	* Prepared statement support
69 	* Queries with separate args whenever we can with consistent interface
70 	* Query returns some typed info when we can.
71 	* ....?
72 
73 
74 	PreparedStatement prepareStatement(string sql);
75 
76 	Might be worth looking at doing the preparations in static ctors
77 	so they are always done once per program...
78 */
79 
80 ///
81 interface Database {
82 	/// Actually implements the query for the database. The query() method
83 	/// below might be easier to use.
84 	ResultSet queryImpl(string sql, Variant[] args...);
85 
86 	/// Escapes data for inclusion into an sql string literal
87 	string escape(string sqlData);
88 	/// Escapes binary data for inclusion into a sql string. Note that unlike `escape`, the returned string here SHOULD include the quotes.
89 	string escapeBinaryString(const(ubyte)[] sqlData);
90 
91 	/// query to start a transaction, only here because sqlite is apparently different in syntax...
92 	void startTransaction();
93 
94 	/// Just executes a query. It supports placeholders for parameters
95 	final ResultSet query(T...)(string sql, T t) {
96 		Variant[] args;
97 		foreach(arg; t) {
98 			Variant a;
99 			static if(__traits(compiles, a = arg))
100 				a = arg;
101 			else
102 				a = to!string(t);
103 			args ~= a;
104 		}
105 		return queryImpl(sql, args);
106 	}
107 
108 	final ResultSet query(Args...)(arsd.core.InterpolationHeader header, Args args, arsd.core.InterpolationFooter footer) {
109 		import arsd.core;
110 		Variant[] vargs;
111 		string sql;
112 		foreach(arg; args) {
113 			static if(is(typeof(arg) == InterpolatedLiteral!str, string str)) {
114 				sql ~= str;
115 			} else static if(is(typeof(arg) == InterpolatedExpression!str, string str)) {
116 				// intentionally blank
117 			} else static if(is(typeof(arg) == InterpolationHeader) || is(typeof(arg) == InterpolationFooter)) {
118 				static assert(0, "Nested interpolations not allowed at this time");
119 			} else {
120 				sql ~= "?";
121 				vargs ~= Variant(arg);
122 			}
123 		}
124 		return queryImpl(sql, vargs);
125 	}
126 
127 	/// turns a systime into a value understandable by the target database as a timestamp to be concated into a query. so it should be quoted and escaped etc as necessary
128 	string sysTimeToValue(SysTime);
129 
130 	/// Prepared statement api
131 	/*
132 	PreparedStatement prepareStatement(string sql, int numberOfArguments);
133 
134 	*/
135 }
136 import std.stdio;
137 
138 // Added Oct 26, 2021
139 Row queryOneRow(string file = __FILE__, size_t line = __LINE__, T...)(Database db, string sql, T t) {
140 	auto res = db.query(sql, t);
141 	import arsd.core;
142 	if(res.empty)
143 		throw ArsdException!("no row in result")(sql, t, file, line);
144 	auto row = res.front;
145 	return row;
146 }
147 
148 Ret queryOneColumn(Ret, string file = __FILE__, size_t line = __LINE__, T...)(Database db, string sql, T t) {
149 	auto row = queryOneRow(db, sql, t);
150 	return to!Ret(row[0]);
151 }
152 
153 struct Query {
154 	ResultSet result;
155 	this(T...)(Database db, string sql, T t) if(T.length!=1 || !is(T[0]==Variant[])) {
156 		result = db.query(sql, t);
157 	}
158     // Version for dynamic generation of args: (Needs to be a template for coexistence with other constructor.
159     this(T...)(Database db, string sql, T args) if (T.length==1 && is(T[0] == Variant[])) {
160         result = db.queryImpl(sql, args);
161     }
162 
163 	int opApply(T)(T dg) if(is(T == delegate)) {
164 		import std.traits;
165 		foreach(row; result) {
166 			ParameterTypeTuple!dg tuple;
167 
168 			foreach(i, item; tuple) {
169 				tuple[i] = to!(typeof(item))(row[i]);
170 			}
171 
172 			if(auto result = dg(tuple))
173 				return result;
174 		}
175 
176 		return 0;
177 	}
178 }
179 
180 /++
181 	Represents a single item in a result. A row is a set of these `DatabaseDatum`s.
182 
183 	History:
184 		Added June 2, 2023 (dub v11.0). Prior to this, it would always use `string`. This has `alias toString this` to try to maintain compatibility.
185 +/
186 struct DatabaseDatum {
187 	int platformSpecificTag;
188 	LimitedVariant storage;
189 
190 	/++
191 		These are normally constructed by the library, so you shouldn't need these constructors. If you're writing a new database implementation though, here it is.
192 	+/
193 	package this(string s) {
194 		storage = s;
195 	}
196 
197 	/++
198 		Returns `true` if the item was `NULL` in the database.
199 	+/
200 	bool isNull() {
201 		return storage.contains == LimitedVariant.Contains.null_;
202 	}
203 
204 	/++
205 		Converts the datum to a string in a format specified by the database.
206 	+/
207 	string toString() {
208 		if(isNull())
209 			return null;
210 		return storage.toString();
211 	}
212 	/++
213 		For compatibility with earlier versions of the api, all data can easily convert to string implicitly and opCast keeps to!x(this) working.
214 
215 		The toArsdJsVar one is in particular subject to change.
216 	+/
217 	alias toString this;
218 
219 	/// ditto
220 	T opCast(T)() {
221 		import std.conv;
222 		return to!T(this.toString);
223 	}
224 
225 	/// ditto
226 	string toArsdJsVar() { return this.toString; }
227 }
228 
229 /++
230 	A row in a result set from a query.
231 
232 	You can access this as either an array or associative array:
233 
234 	---
235 		foreach(Row row; db.query("SELECT id, name FROM mytable")) {
236 			// can access by index or by name
237 			row[0] == row["id"];
238 			row[1] == row["name"];
239 
240 			// can also iterate over the results
241 			foreach(name, data; row) {
242 				 // will send name = "id", data = the thing
243 				 // and then next loop will be name = "name", data = the thing
244 			}
245 		}
246 	---
247 +/
248 struct Row {
249 	package DatabaseDatum[] row;
250 	package ResultSet resultSet;
251 
252 	/++
253 		Allows for access by index or column name.
254 	+/
255 	DatabaseDatum opIndex(size_t idx, string file = __FILE__, int line = __LINE__) {
256 		if(idx >= row.length)
257 			throw new Exception(text("index ", idx, " is out of bounds on result"), file, line);
258 		return row[idx];
259 	}
260 
261 	/// ditto
262 	DatabaseDatum opIndex(string name, string file = __FILE__, int line = __LINE__) {
263 		auto idx = resultSet.getFieldIndex(name);
264 		if(idx >= row.length)
265 			throw new Exception(text("no field ", name, " in result"), file, line);
266 		return row[idx];
267 	}
268 
269 	/++
270 		Provides a string representation of the row, for quick eyeball debugging. You probably won't want the format this prints in (and don't rely upon it, as it is subject to change at any time without notice!), but it might be useful for use with `writeln`.
271 	+/
272 	string toString() {
273 		return to!string(row);
274 	}
275 
276 	/++
277 		Allows iteration over the columns with the `foreach` statement.
278 
279 		History:
280 			Prior to June 11, 2023 (dub v11.0), the order of iteration was undefined. It is now guaranteed to be in the same order as it was returned by the database (which is determined by your original query). Additionally, prior to this date, the datum was typed `string`. `DatabaseDatum` should implicitly convert to string, so your code is unlikely to break, but if you did specify the type explicitly you may need to update your code.
281 
282 			The overload with one argument, having just the datum without the name, was also added on June 11, 2023 (dub v11.0).
283 	+/
284 	int opApply(int delegate(string, DatabaseDatum) dg) {
285 		string[] fn = resultSet.fieldNames();
286 		foreach(idx, item; row)
287 			mixin(yield("fn[idx], item"));
288 
289 		return 0;
290 	}
291 
292 	/// ditto
293 	int opApply(int delegate(DatabaseDatum) dg) {
294 		foreach(item; row)
295 			mixin(yield("item"));
296 		return 0;
297 	}
298 
299 	/++
300 		Hacky conversion to simpler types.
301 
302 		I'd recommend against using these in new code. I wrote them back around 2011 as a hack for something I was doing back then. Among the downsides of these is type information loss in both functions (since strings discard the information tag) and column order loss in `toAA` (since D associative arrays do not maintain any defined order). Additionally, they to make an additional copy of the result row, which you may be able to avoid by looping over it directly.
303 
304 		I may formally deprecate them in a future release.
305 	+/
306 	string[] toStringArray() {
307 		string[] row;
308 		foreach(item; this.row)
309 			row ~= item;
310 		return row;
311 	}
312 
313 	/// ditto
314 	string[string] toAA() {
315 		string[string] a;
316 
317 		string[] fn = resultSet.fieldNames();
318 
319 		foreach(i, r; row)
320 			a[fn[i]] = r;
321 
322 		return a;
323 	}
324 
325 }
326 import std.conv;
327 
328 interface ResultSet {
329 	// name for associative array to result index
330 	int getFieldIndex(string field);
331 	string[] fieldNames();
332 
333 	// this is a range that can offer other ranges to access it
334 	bool empty() @property;
335 	Row front() @property;
336 	void popFront() ;
337 	size_t length() @property;
338 
339 	/* deprecated */ final ResultSet byAssoc() { return this; }
340 }
341 
342 class DatabaseException : Exception {
343 	this(string msg, string file = __FILE__, size_t line = __LINE__) {
344 		super(msg, file, line);
345 	}
346 }
347 
348 
349 
350 abstract class SqlBuilder { }
351 
352 class InsertBuilder : SqlBuilder {
353 	private string table;
354 	private string[] fields;
355 	private string[] fieldsSetSql;
356 	private Variant[] values;
357 
358 	///
359 	void setTable(string table) {
360 		this.table = table;
361 	}
362 
363 	/// same as adding the arr as values one by one. assumes DB column name matches AA key.
364 	void addVariablesFromAssociativeArray(in string[string] arr, string[] names...) {
365 		foreach(name; names) {
366 			fields ~= name;
367 			if(name in arr) {
368 				fieldsSetSql ~= "?";
369 				values ~= Variant(arr[name]);
370 			} else {
371 				fieldsSetSql ~= "null";
372 			}
373 		}
374 	}
375 
376 	///
377 	void addVariable(T)(string name, T value) {
378 		fields ~= name;
379 		fieldsSetSql ~= "?";
380 		values ~= Variant(value);
381 	}
382 
383 	/// if you use a placeholder, be sure to [addValueForHandWrittenPlaceholder] immediately
384 	void addFieldWithSql(string name, string sql) {
385 		fields ~= name;
386 		fieldsSetSql ~= sql;
387 	}
388 
389 	/// for addFieldWithSql that includes a placeholder
390 	void addValueForHandWrittenPlaceholder(T)(T value) {
391 		values ~= Variant(value);
392 	}
393 
394 	/// executes the query
395 	auto execute(Database db, string supplementalSql = null) {
396 		return db.queryImpl(this.toSql() ~ supplementalSql, values);
397 	}
398 
399 	string toSql() {
400 		string sql = "INSERT INTO\n";
401 		sql ~= "\t" ~ table ~ " (\n";
402 		foreach(idx, field; fields) {
403 			sql ~= "\t\t" ~ field ~ ((idx != fields.length - 1) ? ",\n" : "\n");
404 		}
405 		sql ~= "\t) VALUES (\n";
406 		foreach(idx, field; fieldsSetSql) {
407 			sql ~= "\t\t" ~ field ~ ((idx != fieldsSetSql.length - 1) ? ",\n" : "\n");
408 		}
409 		sql ~= "\t)\n";
410 		return sql;
411 	}
412 }
413 
414 /// WARNING: this is as susceptible to SQL injections as you would be writing it out by hand
415 class SelectBuilder : SqlBuilder {
416 	string[] fields;
417 	string table;
418 	string[] joins;
419 	string[] wheres;
420 	string[] orderBys;
421 	string[] groupBys;
422 
423 	int limit;
424 	int limitStart;
425 
426 	Variant[string] vars;
427 	void setVariable(T)(string name, T value) {
428 		assert(name.length);
429 		if(name[0] == '?')
430 			name = name[1 .. $];
431 		vars[name] = Variant(value);
432 	}
433 
434 	Database db;
435 	this(Database db = null) {
436 		this.db = db;
437 	}
438 
439 	/*
440 		It would be nice to put variables right here in the builder
441 
442 		?name
443 
444 		will prolly be the syntax, and we'll do a Variant[string] of them.
445 
446 		Anything not translated here will of course be in the ending string too
447 	*/
448 
449 	SelectBuilder cloned() {
450 		auto s = new SelectBuilder(this.db);
451 		s.fields = this.fields.dup;
452 		s.table = this.table;
453 		s.joins = this.joins.dup;
454 		s.wheres = this.wheres.dup;
455 		s.orderBys = this.orderBys.dup;
456 		s.groupBys = this.groupBys.dup;
457 		s.limit = this.limit;
458 		s.limitStart = this.limitStart;
459 
460 		foreach(k, v; this.vars)
461 			s.vars[k] = v;
462 
463 		return s;
464 	}
465 
466 	override string toString() {
467 		string sql = "SELECT ";
468 
469 		// the fields first
470 		{
471 			bool outputted = false;
472 			foreach(field; fields) {
473 				if(outputted)
474 					sql ~= ", ";
475 				else
476 					outputted = true;
477 
478 				sql ~= field; // "`" ~ field ~ "`";
479 			}
480 		}
481 
482 		sql ~= " FROM " ~ table;
483 
484 		if(joins.length) {
485 			foreach(join; joins)
486 				sql ~= " " ~ join;
487 		}
488 
489 		if(wheres.length) {
490 			bool outputted = false;
491 			sql ~= " WHERE ";
492 			foreach(w; wheres) {
493 				if(outputted)
494 					sql ~= " AND ";
495 				else
496 					outputted = true;
497 				sql ~= "(" ~ w ~ ")";
498 			}
499 		}
500 
501 		if(groupBys.length) {
502 			bool outputted = false;
503 			sql ~= " GROUP BY ";
504 			foreach(o; groupBys) {
505 				if(outputted)
506 					sql ~= ", ";
507 				else
508 					outputted = true;
509 				sql ~= o;
510 			}
511 		}
512 
513 		if(orderBys.length) {
514 			bool outputted = false;
515 			sql ~= " ORDER BY ";
516 			foreach(o; orderBys) {
517 				if(outputted)
518 					sql ~= ", ";
519 				else
520 					outputted = true;
521 				sql ~= o;
522 			}
523 		}
524 
525 		if(limit) {
526 			sql ~= " LIMIT ";
527 			if(limitStart)
528 				sql ~= to!string(limitStart) ~ ", ";
529 			sql ~= to!string(limit);
530 		}
531 
532 		if(db is null)
533 			return sql;
534 
535 		return escapedVariants(db, sql, vars);
536 	}
537 }
538 
539 
540 // /////////////////////sql//////////////////////////////////
541 
542 package string tohexsql(const(ubyte)[] b) {
543 	char[] x;
544 	x.length = b.length * 2 + 3;
545 	int pos = 0;
546 	x[pos++] = 'x';
547 	x[pos++] = '\'';
548 
549 	char tohex(ubyte a) {
550 		if(a < 10)
551 			return cast(char)(a + '0');
552 		else
553 			return cast(char)(a - 10 + 'A');
554 	}
555 
556 	foreach(item; b) {
557 		x[pos++] = tohex(item >> 4);
558 		x[pos++] = tohex(item & 0x0f);
559 	}
560 
561 	x[pos++] = '\'';
562 
563 	return cast(string) x;
564 }
565 
566 // used in the internal placeholder thing
567 string toSql(Database db, Variant a) {
568 
569 	string binary(const(ubyte)[] b) {
570 		if(b is null)
571 			return "NULL";
572 		else
573 			return db.escapeBinaryString(b);
574 	}
575 
576 	auto v = a.peek!(void*);
577 	if(v && (*v is null)) {
578 		return "NULL";
579 	} else if(auto t = a.peek!(SysTime)) {
580 		return db.sysTimeToValue(*t);
581 	} else if(auto t = a.peek!(DateTime)) {
582 		// FIXME: this might be broken cuz of timezones!
583 		return db.sysTimeToValue(cast(SysTime) *t);
584 	} else if(auto t = a.peek!(ubyte[])) {
585 		return binary(*t);
586 	} else if(auto t = a.peek!(immutable(ubyte)[])) {
587 		return binary(*t);
588 	} else if(auto t = a.peek!string) {
589 		auto str = *t;
590 		if(str is null)
591 			return "NULL";
592 		else
593 			return '\'' ~ db.escape(str) ~ '\'';
594 	} else {
595 		string str = to!string(a);
596 		return '\'' ~ db.escape(str) ~ '\'';
597 	}
598 
599 	assert(0);
600 }
601 
602 // just for convenience; "str".toSql(db);
603 string toSql(string s, Database db) {
604 	//if(s is null)
605 		//return "NULL";
606 	return '\'' ~ db.escape(s) ~ '\'';
607 }
608 
609 string toSql(long s, Database db) {
610 	return to!string(s);
611 }
612 
613 string escapedVariants(Database db, in string sql, Variant[string] t) {
614 	if(t.keys.length <= 0 || sql.indexOf("?") == -1) {
615 		return sql;
616 	}
617 
618 	string fixedup;
619 	int currentStart = 0;
620 // FIXME: let's make ?? render as ? so we have some escaping capability
621 	foreach(i, dchar c; sql) {
622 		if(c == '?') {
623 			fixedup ~= sql[currentStart .. i];
624 
625 			int idxStart = cast(int) i + 1;
626 			int idxLength;
627 
628 			bool isFirst = true;
629 
630 			while(idxStart + idxLength < sql.length) {
631 				char C = sql[idxStart + idxLength];
632 
633 				if((C >= 'a' && C <= 'z') || (C >= 'A' && C <= 'Z') || C == '_' || (!isFirst && C >= '0' && C <= '9'))
634 					idxLength++;
635 				else
636 					break;
637 
638 				isFirst = false;
639 			}
640 
641 			auto idx = sql[idxStart .. idxStart + idxLength];
642 
643 			if(idx in t) {
644 				fixedup ~= toSql(db, t[idx]);
645 				currentStart = idxStart + idxLength;
646 			} else {
647 				// just leave it there, it might be done on another layer
648 				currentStart = cast(int) i;
649 			}
650 		}
651 	}
652 
653 	fixedup ~= sql[currentStart .. $];
654 
655 	return fixedup;
656 }
657 
658 /// Note: ?n params are zero based!
659 string escapedVariants(Database db, in string sql, Variant[] t) {
660 // FIXME: let's make ?? render as ? so we have some escaping capability
661 	// if nothing to escape or nothing to escape with, don't bother
662 	if(t.length > 0 && sql.indexOf("?") != -1) {
663 		string fixedup;
664 		int currentIndex;
665 		int currentStart = 0;
666 		foreach(i, dchar c; sql) {
667 			if(c == '?') {
668 				fixedup ~= sql[currentStart .. i];
669 
670 				int idx = -1;
671 				currentStart = cast(int) i + 1;
672 				if((i + 1) < sql.length) {
673 					auto n = sql[i + 1];
674 					if(n >= '0' && n <= '9') {
675 						currentStart = cast(int) i + 2;
676 						idx = n - '0';
677 					}
678 				}
679 				if(idx == -1) {
680 					idx = currentIndex;
681 					currentIndex++;
682 				}
683 
684 				if(idx < 0 || idx >= t.length)
685 					throw new Exception("SQL Parameter index is out of bounds: " ~ to!string(idx) ~ " at `"~sql[0 .. i]~"`");
686 
687 				fixedup ~= toSql(db, t[idx]);
688 			}
689 		}
690 
691 		fixedup ~= sql[currentStart .. $];
692 
693 		return fixedup;
694 		/*
695 		string fixedup;
696 		int pos = 0;
697 
698 
699 		void escAndAdd(string str, int q) {
700 			fixedup ~= sql[pos..q] ~ '\'' ~ db.escape(str) ~ '\'';
701 
702 		}
703 
704 		foreach(a; t) {
705 			int q = sql[pos..$].indexOf("?");
706 			if(q == -1)
707 				break;
708 			q += pos;
709 
710 			auto v = a.peek!(void*);
711 			if(v && (*v is null))
712 				fixedup  ~= sql[pos..q] ~ "NULL";
713 			else {
714 				string str = to!string(a);
715 				escAndAdd(str, q);
716 			}
717 
718 			pos = q+1;
719 		}
720 
721 		fixedup ~= sql[pos..$];
722 
723 		sql = fixedup;
724 		*/
725 	}
726 
727 	return sql;
728 }
729 
730 
731 
732 
733 
734 
735 enum UpdateOrInsertMode {
736 	CheckForMe,
737 	AlwaysUpdate,
738 	AlwaysInsert
739 }
740 
741 
742 // BIG FIXME: this should really use prepared statements
743 int updateOrInsert(Database db, string table, string[string] values, string where, UpdateOrInsertMode mode = UpdateOrInsertMode.CheckForMe, string key = "id") {
744 
745 	string identifierQuote = "";
746 
747 	bool insert = false;
748 
749 	final switch(mode) {
750 		case UpdateOrInsertMode.CheckForMe:
751 			auto res = db.query("SELECT "~key~" FROM "~identifierQuote~db.escape(table)~identifierQuote~" WHERE " ~ where);
752 			insert = res.empty;
753 
754 		break;
755 		case UpdateOrInsertMode.AlwaysInsert:
756 			insert = true;
757 		break;
758 		case UpdateOrInsertMode.AlwaysUpdate:
759 			insert = false;
760 		break;
761 	}
762 
763 
764 	if(insert) {
765 		string insertSql = "INSERT INTO " ~identifierQuote ~ db.escape(table) ~ identifierQuote ~ " ";
766 
767 		bool outputted = false;
768 		string vs, cs;
769 		foreach(column, value; values) {
770 			if(column is null)
771 				continue;
772 			if(outputted) {
773 				vs ~= ", ";
774 				cs ~= ", ";
775 			} else
776 				outputted = true;
777 
778 			//cs ~= "`" ~ db.escape(column) ~ "`";
779 			cs ~= identifierQuote ~ column ~ identifierQuote; // FIXME: possible insecure
780 			if(value is null)
781 				vs ~= "NULL";
782 			else
783 				vs ~= "'" ~ db.escape(value) ~ "'";
784 		}
785 
786 		if(!outputted)
787 			return 0;
788 
789 
790 		insertSql ~= "(" ~ cs ~ ")";
791 		insertSql ~= " VALUES ";
792 		insertSql ~= "(" ~ vs ~ ")";
793 
794 		db.query(insertSql);
795 
796 		return 0; // db.lastInsertId;
797 	} else {
798 		string updateSql = "UPDATE "~identifierQuote~db.escape(table)~identifierQuote~" SET ";
799 
800 		bool outputted = false;
801 		foreach(column, value; values) {
802 			if(column is null)
803 				continue;
804 			if(outputted)
805 				updateSql ~= ", ";
806 			else
807 				outputted = true;
808 
809 			if(value is null)
810 				updateSql ~= identifierQuote ~ db.escape(column) ~ identifierQuote ~ " = NULL";
811 			else
812 				updateSql ~= identifierQuote ~ db.escape(column) ~ identifierQuote ~ " = '" ~ db.escape(value) ~ "'";
813 		}
814 
815 		if(!outputted)
816 			return 0;
817 
818 		updateSql ~= " WHERE " ~ where;
819 
820 		db.query(updateSql);
821 		return 0;
822 	}
823 }
824 
825 
826 
827 
828 
829 string fixupSqlForDataObjectUse(string sql, string[string] keyMapping = null) {
830 
831 	string[] tableNames;
832 
833 	string piece = sql;
834 	sizediff_t idx;
835 	while((idx = piece.indexOf("JOIN")) != -1) {
836 		auto start = idx + 5;
837 		auto i = start;
838 		while(piece[i] != ' ' && piece[i] != '\n' && piece[i] != '\t' && piece[i] != ',')
839 			i++;
840 		auto end = i;
841 
842 		tableNames ~= strip(piece[start..end]);
843 
844 		piece = piece[end..$];
845 	}
846 
847 	idx = sql.indexOf("FROM");
848 	if(idx != -1) {
849 		auto start = idx + 5;
850 		auto i = start;
851 		start = i;
852 		while(i < sql.length && !(sql[i] > 'A' && sql[i] <= 'Z')) // if not uppercase, except for A (for AS) to avoid SQL keywords (hack)
853 			i++;
854 
855 		auto from = sql[start..i];
856 		auto pieces = from.split(",");
857 		foreach(p; pieces) {
858 			p = p.strip();
859 			start = 0;
860 			i = 0;
861 			while(i < p.length && p[i] != ' ' && p[i] != '\n' && p[i] != '\t' && p[i] != ',')
862 				i++;
863 
864 			tableNames ~= strip(p[start..i]);
865 		}
866 
867 		string sqlToAdd;
868 		foreach(tbl; tableNames) {
869 			if(tbl.length) {
870 				string keyName = "id";
871 				if(tbl in keyMapping)
872 					keyName = keyMapping[tbl];
873 				sqlToAdd ~= ", " ~ tbl ~ "." ~ keyName ~ " AS " ~ "id_from_" ~ tbl;
874 			}
875 		}
876 
877 		sqlToAdd ~= " ";
878 
879 		sql = sql[0..idx] ~ sqlToAdd ~ sql[idx..$];
880 	}
881 
882 	return sql;
883 }
884 
885 
886 
887 
888 
889 /*
890 	This is like a result set
891 
892 
893 	DataObject res = [...];
894 
895 	res.name = "Something";
896 
897 	res.commit; // runs the actual update or insert
898 
899 
900 	res = new DataObject(fields, tables
901 
902 
903 
904 
905 
906 
907 
908 	when doing a select, we need to figure out all the tables and modify the query to include the ids we need
909 
910 
911 	search for FROM and JOIN
912 	the next token is the table name
913 
914 	right before the FROM, add the ids of each table
915 
916 
917 	given:
918 		SELECT name, phone FROM customers LEFT JOIN phones ON customer.id = phones.cust_id
919 
920 	we want:
921 		SELECT name, phone, customers.id AS id_from_customers, phones.id AS id_from_phones FROM customers LEFT JOIN phones ON customer.id[...];
922 
923 */
924 
925 mixin template DataObjectConstructors() {
926 	this(Database db, string[string] res, Tuple!(string, string)[string] mappings) {
927 		super(db, res, mappings);
928 	}
929 }
930 
931 private string yield(string what) { return `if(auto result = dg(`~what~`)) return result;`; }
932 
933 import std.typecons;
934 import std.json; // for json value making
935 class DataObject {
936 	// lets you just free-form set fields, assuming they all come from the given table
937 	// note it doesn't try to handle joins for new rows. you've gotta do that yourself
938 	this(Database db, string table, UpdateOrInsertMode mode = UpdateOrInsertMode.CheckForMe) {
939 		assert(db !is null);
940 		this.db = db;
941 		this.table = table;
942 
943 		this.mode = mode;
944 	}
945 
946 	JSONValue makeJsonValue() {
947 		JSONValue val;
948 		JSONValue[string] valo;
949 		//val.type = JSON_TYPE.OBJECT;
950 		foreach(k, v; fields) {
951 			JSONValue s;
952 			//s.type = JSON_TYPE.STRING;
953 			s.str = v;
954 			valo[k] = s;
955 		}
956 		val = valo;
957 		return val;
958 	}
959 
960 	this(Database db, string[string] res, Tuple!(string, string)[string] mappings) {
961 		this.db = db;
962 		this.mappings = mappings;
963 		this.fields = res;
964 
965 		mode = UpdateOrInsertMode.AlwaysUpdate;
966 	}
967 
968 	string table;
969 	//     table,  column  [alias]
970 	Tuple!(string, string)[string] mappings;
971 
972 	// value [field] [table]
973 	string[string][string] multiTableKeys; // note this is not set internally tight now
974 						// but it can be set manually to do multi table mappings for automatic update
975 
976 
977 	string opDispatch(string field, string file = __FILE__, size_t line = __LINE__)()
978 		if((field.length < 8 || field[0..8] != "id_from_") && field != "popFront")
979 	{
980 		if(field !in fields)
981 			throw new Exception("no such field " ~ field, file, line);
982 
983 		return fields[field];
984 	}
985 
986 	string opDispatch(string field, T)(T t)
987 		if((field.length < 8 || field[0..8] != "id_from_") && field != "popFront")
988 	{
989 		static if(__traits(compiles, t is null)) {
990 			if(t is null)
991 				setImpl(field, null);
992 			else
993 				setImpl(field, to!string(t));
994 		} else
995 			setImpl(field, to!string(t));
996 
997 		return fields[field];
998 	}
999 
1000 
1001 	private void setImpl(string field, string value) {
1002 		if(field in fields) {
1003 			if(fields[field] != value)
1004 				changed[field] = true;
1005 		} else {
1006 			changed[field] = true;
1007 		}
1008 
1009 		fields[field] = value;
1010 	}
1011 
1012 	public void setWithoutChange(string field, string value) {
1013 		fields[field] = value;
1014 	}
1015 
1016 	int opApply(int delegate(ref string) dg) {
1017 		foreach(a; fields)
1018 			mixin(yield("a"));
1019 
1020 		return 0;
1021 	}
1022 
1023 	int opApply(int delegate(ref string, ref string) dg) {
1024 		foreach(a, b; fields)
1025 			mixin(yield("a, b"));
1026 
1027 		return 0;
1028 	}
1029 
1030 
1031 	string opIndex(string field, string file = __FILE__, size_t line = __LINE__) {
1032 		if(field !in fields)
1033 			throw new DatabaseException("No such field in data object: " ~ field, file, line);
1034 		return fields[field];
1035 	}
1036 
1037 	string opIndexAssign(string value, string field) {
1038 		setImpl(field, value);
1039 		return value;
1040 	}
1041 
1042 	string* opBinary(string op)(string key)  if(op == "in") {
1043 		return key in fields;
1044 	}
1045 
1046 	string[string] fields;
1047 	bool[string] changed;
1048 
1049 	void commitChanges() {
1050 		commitChanges(cast(string) null, null);
1051 	}
1052 
1053 	void commitChanges(string key, string keyField) {
1054 		commitChanges(key is null ? null : [key], keyField is null ? null : [keyField]);
1055 	}
1056 
1057 	void commitChanges(string[] keys, string[] keyFields = null) {
1058 		string[string][string] toUpdate;
1059 		int updateCount = 0;
1060 		foreach(field, c; changed) {
1061 			if(c) {
1062 				string tbl, col;
1063 				if(mappings is null) {
1064 					tbl = this.table;
1065 					col = field;
1066 				} else {
1067 					if(field !in mappings)
1068 						assert(0, "no such mapping for " ~ field);
1069 					auto m = mappings[field];
1070 					tbl = m[0];
1071 					col = m[1];
1072 				}
1073 
1074 				toUpdate[tbl][col] = fields[field];
1075 				updateCount++;
1076 			}
1077 		}
1078 
1079 		if(updateCount) {
1080 			db.startTransaction();
1081 			scope(success) db.query("COMMIT");
1082 			scope(failure) db.query("ROLLBACK");
1083 
1084 			foreach(tbl, values; toUpdate) {
1085 				string where, keyFieldToPass;
1086 
1087 				if(keys is null) {
1088 					keys = [null];
1089 				}
1090 
1091 				if(multiTableKeys is null || tbl !in multiTableKeys)
1092 				foreach(i, key; keys) {
1093 					string keyField;
1094 
1095 					if(key is null) {
1096 						key = "id_from_" ~ tbl;
1097 						if(key !in fields)
1098 							key = "id";
1099 					}
1100 
1101 					if(i >= keyFields.length || keyFields[i] is null) {
1102 						if(key == "id_from_" ~ tbl)
1103 							keyField = "id";
1104 						else
1105 							keyField = key;
1106 					} else {
1107 						keyField = keyFields[i];
1108 					}
1109 
1110 
1111 					if(where.length)
1112 						where ~= " AND ";
1113 
1114 					auto f = key in fields ? fields[key] : null;
1115 					if(f is null)
1116 						where ~= keyField ~ " = NULL";
1117 					else
1118 						where ~= keyField ~ " = '"~db.escape(f)~"'" ;
1119 					if(keyFieldToPass.length)
1120 						keyFieldToPass ~= ", ";
1121 
1122 					keyFieldToPass ~= keyField;
1123 				}
1124 				else {
1125 					foreach(keyField, v; multiTableKeys[tbl]) {
1126 						if(where.length)
1127 							where ~= " AND ";
1128 
1129 						where ~= keyField ~ " = '"~db.escape(v)~"'" ;
1130 						if(keyFieldToPass.length)
1131 							keyFieldToPass ~= ", ";
1132 
1133 						keyFieldToPass ~= keyField;
1134 					}
1135 				}
1136 
1137 
1138 
1139 				updateOrInsert(db, tbl, values, where, mode, keyFieldToPass);
1140 			}
1141 
1142 			changed = null;
1143 		}
1144 	}
1145 
1146 	void commitDelete() {
1147 		if(mode == UpdateOrInsertMode.AlwaysInsert)
1148 			throw new Exception("Cannot delete an item not in the database");
1149 
1150 		assert(table.length); // FIXME, should work with fancy items too
1151 
1152 		// FIXME: escaping and primary key questions
1153 		db.query("DELETE FROM " ~ table ~ " WHERE id = '" ~ db.escape(fields["id"]) ~ "'");
1154 	}
1155 
1156 	string getAlias(string table, string column) {
1157 		string ali;
1158 		if(mappings is null) {
1159 			if(this.table is null) {
1160 				mappings[column] = tuple(table, column);
1161 				return column;
1162 			} else {
1163 				assert(table == this.table);
1164 				ali = column;
1165 			}
1166 		} else {
1167 			foreach(a, what; mappings)
1168 				if(what[0] == table && what[1] == column
1169 				  && a.indexOf("id_from_") == -1) {
1170 					ali = a;
1171 					break;
1172 				}
1173 		}
1174 
1175 		return ali;
1176 	}
1177 
1178 	void set(string table, string column, string value) {
1179 		string ali = getAlias(table, column);
1180 		//assert(ali in fields);
1181 		setImpl(ali, value);
1182 	}
1183 
1184 	string select(string table, string column) {
1185 		string ali = getAlias(table, column);
1186 		//assert(ali in fields);
1187 		if(ali in fields)
1188 			return fields[ali];
1189 		return null;
1190 	}
1191 
1192 	DataObject addNew() {
1193 		auto n = new DataObject(db, null);
1194 
1195 		n.db = this.db;
1196 		n.table = this.table;
1197 		n.mappings = this.mappings;
1198 
1199 		foreach(k, v; this.fields)
1200 			if(k.indexOf("id_from_") == -1)
1201 				n.fields[k] = v;
1202 			else
1203 				n.fields[k] = null; // don't copy ids
1204 
1205 		n.mode = UpdateOrInsertMode.AlwaysInsert;
1206 
1207 		return n;
1208 	}
1209 
1210 	Database db;
1211 	UpdateOrInsertMode mode;
1212 }
1213 
1214 /**
1215 	You can subclass DataObject if you want to
1216 	get some compile time checks or better types.
1217 
1218 	You'll want to disable opDispatch, then forward your
1219 	properties to the super opDispatch.
1220 */
1221 
1222 /*mixin*/ string DataObjectField(T, string table, string column, string aliasAs = null)() {
1223 	string aliasAs_;
1224 	if(aliasAs is null)
1225 		aliasAs_ = column;
1226 	else
1227 		aliasAs_ = aliasAs;
1228 	return `
1229 		@property void `~aliasAs_~`(`~T.stringof~` setTo) {
1230 			super.set("`~table~`", "`~column~`", to!string(setTo));
1231 		}
1232 
1233 		@property `~T.stringof~` `~aliasAs_~` () {
1234 			return to!(`~T.stringof~`)(super.select("`~table~`", "`~column~`"));
1235 		}
1236 	`;
1237 }
1238 
1239 mixin template StrictDataObject() {
1240 	// disable opdispatch
1241 	string opDispatch(string name)(...) if (0) {}
1242 }
1243 
1244 
1245 string createDataObjectFieldsFromAlias(string table, fieldsToUse)() {
1246 	string ret;
1247 
1248 	fieldsToUse f;
1249 	foreach(member; __traits(allMembers, fieldsToUse)) {
1250 		ret ~= DataObjectField!(typeof(__traits(getMember, f, member)), table, member);
1251 	}
1252 
1253 	return ret;
1254 }
1255 
1256 
1257 /**
1258 	This creates an editable data object out of a simple struct.
1259 
1260 	struct MyFields {
1261 		int id;
1262 		string name;
1263 	}
1264 
1265 	alias SimpleDataObject!("my_table", MyFields) User;
1266 
1267 
1268 	User a = new User(db);
1269 
1270 	a.id = 30;
1271 	a.name = "hello";
1272 	a.commitChanges(); // tries an update or insert on the my_table table
1273 
1274 
1275 	Unlike the base DataObject class, this template provides compile time
1276 	checking for types and names, based on the struct you pass in:
1277 
1278 	a.id = "aa"; // compile error
1279 
1280 	a.notAField; // compile error
1281 */
1282 class SimpleDataObject(string tableToUse, fieldsToUse) : DataObject {
1283 	mixin StrictDataObject!();
1284 
1285 	mixin(createDataObjectFieldsFromAlias!(tableToUse, fieldsToUse)());
1286 
1287 	this(Database db) {
1288 		super(db, tableToUse);
1289 	}
1290 }
1291 
1292 /**
1293 	Given some SQL, it finds the CREATE TABLE
1294 	instruction for the given tableName.
1295 	(this is so it can find one entry from
1296 	a file with several SQL commands. But it
1297 	may break on a complex file, so try to only
1298 	feed it simple sql files.)
1299 
1300 	From that, it pulls out the members to create a
1301 	simple struct based on it.
1302 
1303 	It's not terribly smart, so it will probably
1304 	break on complex tables.
1305 
1306 	Data types handled:
1307 
1308 	```
1309 		INTEGER, SMALLINT, MEDIUMINT -> D's int
1310 		TINYINT -> D's bool
1311 		BIGINT -> D's long
1312 		TEXT, VARCHAR -> D's string
1313 		FLOAT, DOUBLE -> D's double
1314 	```
1315 
1316 	It also reads DEFAULT values to pass to D, except for NULL.
1317 	It ignores any length restrictions.
1318 
1319 	Bugs:
1320 	$(LIST
1321 		* Skips all constraints
1322 		* Doesn't handle nullable fields, except with strings
1323 		* It only handles SQL keywords if they are all caps
1324 	)
1325 
1326 	This, when combined with SimpleDataObject!(),
1327 	can automatically create usable D classes from
1328 	SQL input.
1329 */
1330 struct StructFromCreateTable(string sql, string tableName) {
1331 	mixin(getCreateTable(sql, tableName));
1332 }
1333 
1334 string getCreateTable(string sql, string tableName) {
1335    skip:
1336 	while(readWord(sql) != "CREATE") {}
1337 
1338 	assert(readWord(sql) == "TABLE");
1339 
1340 	if(readWord(sql) != tableName)
1341 		goto skip;
1342 
1343 	assert(readWord(sql) == "(");
1344 
1345 	int state;
1346 	int parens;
1347 
1348 	struct Field {
1349 		string name;
1350 		string type;
1351 		string defaultValue;
1352 	}
1353 	Field*[] fields;
1354 
1355 	string word = readWord(sql);
1356 	Field* current = new Field(); // well, this is interesting... under new DMD, not using new breaks it in CTFE because it overwrites the one entry!
1357 	while(word != ")" || parens) {
1358 		if(word == ")") {
1359 			parens --;
1360 			word = readWord(sql);
1361 			continue;
1362 		}
1363 		if(word == "(") {
1364 			parens ++;
1365 			word = readWord(sql);
1366 			continue;
1367 		}
1368 		switch(state) {
1369 		    default: assert(0);
1370 		    case 0:
1371 		    	if(word[0] >= 'A' && word[0] <= 'Z') {
1372 				state = 4;
1373 				break; // we want to skip this since it starts with a keyword (we hope)
1374 			}
1375 			current.name = word;
1376 			state = 1;
1377 		    break;
1378 		    case 1:
1379 		    	current.type ~= word;
1380 			state = 2;
1381 		    break;
1382 		    case 2:
1383 		    	if(word == "DEFAULT")
1384 				state = 3;
1385 			else if (word == ",") {
1386 				fields ~= current;
1387 				current = new Field();
1388 				state = 0; // next
1389 			}
1390 		    break;
1391 		    case 3:
1392 		    	current.defaultValue = word;
1393 			state = 2; // back to skipping
1394 		    break;
1395 		    case 4:
1396 		    	if(word == ",")
1397 				state = 0;
1398 		}
1399 
1400 		word = readWord(sql);
1401 	}
1402 
1403 	if(current.name !is null)
1404 		fields ~= current;
1405 
1406 
1407 	string structCode;
1408 	foreach(field; fields) {
1409 		structCode ~= "\t";
1410 
1411 		switch(field.type) {
1412 			case "INTEGER":
1413 			case "SMALLINT":
1414 			case "MEDIUMINT":
1415 			case "SERIAL": // added Oct 23, 2021
1416 				structCode ~= "int";
1417 			break;
1418 			case "BOOLEAN":
1419 			case "TINYINT":
1420 				structCode ~= "bool";
1421 			break;
1422 			case "BIGINT":
1423 				structCode ~= "long";
1424 			break;
1425 			case "CHAR":
1426 			case "char":
1427 			case "VARCHAR":
1428 			case "varchar":
1429 			case "TEXT":
1430 			case "text":
1431 			case "TIMESTAMPTZ": // added Oct 23, 2021
1432 				structCode ~= "string";
1433 			break;
1434 			case "FLOAT":
1435 			case "DOUBLE":
1436 				structCode ~= "double";
1437 			break;
1438 			default:
1439 				assert(0, "unknown type " ~ field.type ~ " for " ~ field.name);
1440 		}
1441 
1442 		structCode ~= " ";
1443 		structCode ~= field.name;
1444 
1445 		if(field.defaultValue !is null) {
1446 			structCode ~= " = " ~ field.defaultValue;
1447 		}
1448 
1449 		structCode ~= ";\n";
1450 	}
1451 
1452 	return structCode;
1453 }
1454 
1455 string readWord(ref string src) {
1456    reset:
1457 	while(src[0] == ' ' || src[0] == '\t' || src[0] == '\n')
1458 		src = src[1..$];
1459 	if(src.length >= 2 && src[0] == '-' && src[1] == '-') { // a comment, skip it
1460 		while(src[0] != '\n')
1461 			src = src[1..$];
1462 		goto reset;
1463 	}
1464 
1465 	int start, pos;
1466 	if(src[0] == '`') {
1467 		src = src[1..$];
1468 		while(src[pos] != '`')
1469 			pos++;
1470 		goto gotit;
1471 	}
1472 
1473 
1474 	while(
1475 		(src[pos] >= 'A' && src[pos] <= 'Z')
1476 		||
1477 		(src[pos] >= 'a' && src[pos] <= 'z')
1478 		||
1479 		(src[pos] >= '0' && src[pos] <= '9')
1480 		||
1481 		src[pos] == '_'
1482 	)
1483 		pos++;
1484 	gotit:
1485 	if(pos == 0)
1486 		pos = 1;
1487 
1488 	string tmp = src[0..pos];
1489 
1490 	if(src[pos] == '`')
1491 		pos++; // skip the ending quote;
1492 
1493 	src = src[pos..$];
1494 
1495 	return tmp;
1496 }
1497 
1498 /// Combines StructFromCreateTable and SimpleDataObject into a one-stop template.
1499 /// alias DataObjectFromSqlCreateTable(import("file.sql"), "my_table") MyTable;
1500 template DataObjectFromSqlCreateTable(string sql, string tableName) {
1501 	alias SimpleDataObject!(tableName, StructFromCreateTable!(sql, tableName)) DataObjectFromSqlCreateTable;
1502 }
1503 
1504 /+
1505 class MyDataObject : DataObject {
1506 	this() {
1507 		super(new Database("localhost", "root", "pass", "social"), null);
1508 	}
1509 
1510 	mixin StrictDataObject!();
1511 
1512 	mixin(DataObjectField!(int, "users", "id"));
1513 }
1514 
1515 void main() {
1516 	auto a = new MyDataObject;
1517 
1518 	a.fields["id"] = "10";
1519 
1520 	a.id = 34;
1521 
1522 	a.commitChanges;
1523 }
1524 +/
1525 
1526 /*
1527 alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") Test;
1528 
1529 void main() {
1530 	auto a = new Test(null);
1531 
1532 	a.cool = "way";
1533 	a.value = 100;
1534 }
1535 */
1536 
1537 void typeinfoBugWorkaround() {
1538 	assert(0, to!string(typeid(immutable(char[])[immutable(char)[]])));
1539 }
1540 
1541 mixin template DatabaseOperations(string table) {
1542 	DataObject getAsDb(Database db) {
1543 		return objectToDataObject!(typeof(this))(this, db, table);
1544 	}
1545 
1546 	static typeof(this) fromRow(Row row) {
1547 		return rowToObject!(typeof(this))(row);
1548 	}
1549 
1550 	static typeof(this) fromId(Database db, long id) {
1551 		auto query = new SelectBuilder(db);
1552 		query.table = table;
1553 		query.fields ~= "*";
1554 		query.wheres ~= "id = ?0";
1555 		auto res = db.query(query.toString(), id);
1556 		if(res.empty)
1557 			throw new Exception("no such row");
1558 		return fromRow(res.front);
1559 	}
1560 
1561 }
1562 
1563 string toDbName(string s) {
1564 	import std.string;
1565 	return s.toLower ~ "s";
1566 }
1567 
1568 /++
1569 	Easy interop with [arsd.cgi] serveRestObject classes.
1570 
1571 	History:
1572 		Added October 31, 2021.
1573 
1574 	Warning: not stable/supported at this time.
1575 +/
1576 mixin template DatabaseRestObject(alias getDb) {
1577 	override void save() {
1578 		this.id = this.saveToDatabase(getDb());
1579 	}
1580 
1581 	override void load(string urlId) {
1582 		import std.conv;
1583 		this.id = to!int(urlId);
1584 		this.loadFromDatabase(getDb());
1585 	}
1586 }
1587 
1588 void loadFromDatabase(T)(T t, Database database, string tableName = toDbName(__traits(identifier, T))) {
1589 	static assert(is(T == class), "structs wont work for this function, try rowToObject instead for now and complain to me adding struct support is easy enough");
1590 	auto query = new SelectBuilder(database);
1591 	query.table = tableName;
1592 	query.fields ~= "*";
1593 	query.wheres ~= "id = ?0";
1594 	auto res = database.query(query.toString(), t.id);
1595 	if(res.empty)
1596 		throw new Exception("no such row");
1597 
1598 	rowToObject(res.front, t);
1599 }
1600 
1601 auto saveToDatabase(T)(T t, Database database, string tableName = toDbName(__traits(identifier, T))) {
1602 	DataObject obj = objectToDataObject(t, database, tableName, t.id ? UpdateOrInsertMode.AlwaysUpdate : UpdateOrInsertMode.AlwaysInsert);
1603 	if(!t.id) {
1604 		import std.random; // omg i hate htis
1605 		obj.id = uniform(2, int.max);
1606 	}
1607 	obj.commitChanges;
1608 	return t.id;
1609 }
1610 
1611 /+ +
1612 	auto builder = UpdateBuilder("rooms");
1613 	builder.player_one_selection = challenge;
1614 	builder.execute(db, id);
1615 +/
1616 private struct UpdateBuilder {
1617 	this(T)(string table, T id) {
1618 		this.table = table;
1619 		import std.conv;
1620 		this.id = to!string(id);
1621 	}
1622 
1623 }
1624 
1625 import std.traits, std.datetime;
1626 enum DbSave;
1627 enum DbNullable;
1628 alias AliasHelper(alias T) = T;
1629 
1630 T rowToObject(T)(Row row) {
1631 	T t;
1632 	static if(is(T == class))
1633 		t = new T();
1634 	rowToObject(row, t);
1635 	return t;
1636 }
1637 
1638 void rowToObject(T)(Row row, ref T t) {
1639 	import arsd.dom, arsd.cgi;
1640 
1641 	foreach(memberName; __traits(allMembers, T)) {
1642 		alias member = AliasHelper!(__traits(getMember, t, memberName));
1643 		foreach(attr; __traits(getAttributes, member)) {
1644 			static if(is(attr == DbSave)) {
1645 				static if(is(typeof(member) == enum))
1646 					__traits(getMember, t, memberName) = cast(typeof(member)) to!int(row[memberName]);
1647 				else static if(is(typeof(member) == bool)) {
1648 					__traits(getMember, t, memberName) = row[memberName][0] == 't';
1649 				} else static if(is(typeof(member) == Html)) {
1650 					__traits(getMember, t, memberName).source = row[memberName];
1651 				} else static if(is(typeof(member) == DateTime))
1652 					__traits(getMember, t, memberName) = cast(DateTime) dTimeToSysTime(to!long(row[memberName]));
1653 				else {
1654 					if(row[memberName].length)
1655 						__traits(getMember, t, memberName) = to!(typeof(member))(row[memberName]);
1656 					// otherwise, we'll leave it as .init - most likely null
1657 				}
1658 			}
1659 		}
1660 	}
1661 }
1662 
1663 DataObject objectToDataObject(T)(T t, Database db, string table, UpdateOrInsertMode mode = UpdateOrInsertMode.CheckForMe) {
1664 	import arsd.dom, arsd.cgi;
1665 
1666 	DataObject obj = new DataObject(db, table, mode);
1667 	foreach(memberName; __traits(allMembers, T)) {
1668 		alias member = AliasHelper!(__traits(getMember, t, memberName));
1669 		foreach(attr; __traits(getAttributes, member)) {
1670 			static if(is(attr == DbSave)) {
1671 				static if(is(typeof(member) == enum))
1672 					obj.opDispatch!memberName(cast(int) __traits(getMember, t, memberName));
1673 				else static if(is(typeof(member) == Html)) {
1674 					obj.opDispatch!memberName(__traits(getMember, t, memberName).source);
1675 				} else static if(is(typeof(member) == DateTime))
1676 					obj.opDispatch!memberName(dateTimeToDTime(__traits(getMember, t, memberName)));
1677 				else {
1678 					bool done;
1679 					foreach(attr2; __traits(getAttributes, member)) {
1680 						static if(is(attr2 == DbNullable)) {
1681 							if(__traits(getMember, t, memberName) == 0)
1682 								done = true;
1683 						}
1684 					}
1685 
1686 					if(!done) {
1687 						static if(memberName == "id") {
1688 							if(__traits(getMember, t, memberName)) {
1689 								// maybe i shouldn't actually set the id but idk
1690 								obj.opDispatch!memberName(__traits(getMember, t, memberName));
1691 							} else {
1692 								// it is null, let the system do something about it like auto increment
1693 
1694 							}
1695 						} else
1696 							obj.opDispatch!memberName(__traits(getMember, t, memberName));
1697 					}
1698 				}
1699 			}
1700 		}
1701 	}
1702 	return obj;
1703 }
1704 
1705 
1706 
1707 void fillData(T)(string delegate(string, string) setter, T obj, string name) {
1708 	fillData( (k, v) { setter(k, v); }, obj, name);
1709 }
1710 
1711 void fillData(T)(void delegate(string, string) setter, T obj, string name) {
1712 	import arsd.dom, arsd.cgi;
1713 
1714 	import std.traits;
1715 	static if(!isSomeString!T && isArray!T) {
1716 		// FIXME: indexing
1717 		foreach(o; obj)
1718 			fillData(setter, o, name);
1719 	} else static if(is(T == DateTime)) {
1720 		 fillData(setter, obj.toISOExtString(), name);
1721 	} else static if(is(T == Html)) {
1722 		 fillData(setter, obj.source, name);
1723 	} else static if(is(T == struct)) {
1724 		foreach(idx, memberName; __traits(allMembers, T)) {
1725 			alias member = AliasHelper!(__traits(getMember, obj, memberName));
1726 			static if(!is(typeof(member) == function))
1727 				fillData(setter, __traits(getMember, obj, memberName), name ~ "." ~ memberName);
1728 			else static if(is(typeof(member) == function)) {
1729 				static if(functionAttributes!member & FunctionAttribute.property) {
1730 					fillData(setter, __traits(getMember, obj, memberName)(), name ~ "." ~ memberName);
1731 				}
1732 			}
1733 		}
1734 	} else {
1735 		auto value = to!string(obj);
1736 		setter(name, value);
1737 	}
1738 }
1739 
1740 struct varchar(size_t max) {
1741 	private string payload;
1742 
1743 	this(string s, string file = __FILE__, size_t line = __LINE__) {
1744 		opAssign(s, file, line);
1745 	}
1746 
1747 	typeof(this) opAssign(string s, string file = __FILE__, size_t line = __LINE__) {
1748 		if(s.length > max)
1749 			throw new Exception(s ~ " :: too long", file, line);
1750 		payload = s;
1751 
1752 		return this;
1753 	}
1754 
1755 	string asString() {
1756 		return payload;
1757 
1758 	}
1759 	alias asString this;
1760 }
1761 
1762 version (unittest)
1763 {
1764 	/// Unittest utility that returns a predefined set of values
1765 	package (arsd) final class PredefinedResultSet : ResultSet
1766 	{
1767 		string[] fields;
1768 		Row[] rows;
1769 		size_t current;
1770 
1771 		this(string[] fields, Row[] rows)
1772 		{
1773 			this.fields = fields;
1774 			this.rows = rows;
1775 			foreach (ref row; rows)
1776 				row.resultSet = this;
1777 		}
1778 
1779 		int getFieldIndex(const string field) const
1780 		{
1781 			foreach (const idx, const val; fields)
1782 				if (val == field)
1783 					return cast(int) idx;
1784 
1785 			assert(false, "No field with name: " ~ field);
1786 		}
1787 
1788 		string[] fieldNames()
1789 		{
1790 			return fields;
1791 		}
1792 
1793 		@property bool empty() const
1794 		{
1795 			return current == rows.length;
1796 		}
1797 
1798 		Row front() @property
1799 		{
1800 			assert(!empty);
1801 			return rows[current];
1802 		}
1803 
1804 		void popFront()
1805 		{
1806 			assert(!empty);
1807 			current++;
1808 		}
1809 
1810 		size_t length() @property
1811 		{
1812 			return rows.length - current;
1813 		}
1814 	}
1815 }