1 /++
2 	Implementation of the [arsd.database.Database] interface for
3 	accessing MySQL (and MariaDB) databases. Uses the official MySQL client
4 	library, and thus needs that installed to compile and run.
5 
6 	$(PITFALL
7 		If you're using MySQL client library v5.0 or less,
8 		you must pass this to dmd: `-version=Less_Than_MySQL_51`
9 		This is important - otherwise you will see bizarre segfaults!
10 	)
11 +/
12 module arsd.mysql;
13 
14 
15 //version(MySQL_51) {
16 	// we good
17 /*} else*/ version(Less_Than_MySQL_51) {
18 	// we good
19 } else {
20 	// default now is mysql 5.1 or up - it has been long
21 	// enough that surely most everyone uses it and we don't
22 	// need the pragma warning anymore. Of course, the old is
23 	// still available if you need to explicitly opt in.
24 	version = MySQL_51;
25 }
26 
27 version(Windows) {
28 	pragma(lib, "libmysql");
29 }
30 else {
31 	pragma(lib, "mysqlclient");
32 }
33 
34 public import arsd.database;
35 
36 import std.stdio;
37 import std.exception;
38 import std.string;
39 import std.conv;
40 import std.typecons;
41 import core.stdc.config;
42 
43 /++
44 	Represents a query result. You can loop over this with a
45 	`foreach` statement to access individual [Row|rows].
46 
47 	[Row]s expose both an index and associative array interface,
48 	so you can get `row[0]` for the first item, or `row["name"]`
49 	to get a column by name from the result set.
50 +/
51 class MySqlResult : ResultSet {
52 	private int[string] mapping;
53 	private MYSQL_RES* result;
54 
55 	private int itemsTotal;
56 	private int itemsUsed;
57 
58 	string sql;
59 
60 	this(MYSQL_RES* r, string sql) {
61 		result = r;
62 		itemsTotal = cast(int) length();
63 		itemsUsed = 0;
64 
65 		this.sql = sql;
66 
67 		// prime it
68 		if(itemsTotal)
69 			fetchNext();
70 	}
71 
72 	~this() {
73 		if(result !is null)
74 			mysql_free_result(result);
75 	}
76 
77 
78 	MYSQL_FIELD[] fields() @system {
79 		int numFields = mysql_num_fields(result);
80 		auto fields = mysql_fetch_fields(result);
81 
82 		MYSQL_FIELD[] ret;
83 		for(int i = 0; i < numFields; i++) {
84 			ret ~= fields[i];
85 		}
86 
87 		return ret;
88 	}
89 
90 
91 	/// The number of returned rows
92 	override size_t length() {
93 		if(result is null)
94 			return 0;
95 		return cast(int) mysql_num_rows(result);
96 	}
97 
98 	/// Range primitive used by `foreach`
99 	/// You may also use this to check if there was any result.
100 	override bool empty() {
101 		return itemsUsed == itemsTotal;
102 	}
103 
104 	/// Range primitive used by `foreach`
105 	override Row front() {
106 		return row;
107 	}
108 
109 	/// Range primitive used by `foreach`
110 	override void popFront() {
111 		itemsUsed++;
112 		if(itemsUsed < itemsTotal) {
113 			fetchNext();
114 		}
115 	}
116 
117 	override int getFieldIndex(string field) {
118 		if(mapping is null)
119 			makeFieldMapping();
120 		debug {
121 			if(field !in mapping)
122 				throw new Exception(field ~ " not in result");
123 		}
124 		return mapping[field];
125 	}
126 
127 	private void makeFieldMapping() @system {
128 		int numFields = mysql_num_fields(result);
129 		auto fields = mysql_fetch_fields(result);
130 
131 		if(fields is null)
132 			return;
133 
134 		for(int i = 0; i < numFields; i++) {
135 			if(fields[i].name !is null)
136 				mapping[fromCstring(fields[i].name, fields[i].name_length)] = i;
137 		}
138 	}
139 
140 	private void fetchNext() @system {
141 		assert(result);
142 		auto r = mysql_fetch_row(result);
143 		if(r is null)
144 			throw new Exception("there is no next row");
145 		uint numFields = mysql_num_fields(result);
146 		auto lengths = mysql_fetch_lengths(result);
147 		DatabaseDatum[] row;
148 		// potential FIXME: not really binary safe
149 
150 		columnIsNull.length = numFields;
151 		for(int a = 0; a < numFields; a++) {
152 			if(*(r+a) is null) {
153 				row ~= DatabaseDatum(null);
154 				columnIsNull[a] = true;
155 			} else {
156 				row ~= DatabaseDatum(fromCstring(*(r+a), *(lengths + a)));
157 				columnIsNull[a] = false;
158 			}
159 		}
160 
161 		this.row.row = row;
162 		this.row.resultSet = this;
163 	}
164 
165 
166 	override string[] fieldNames() @system {
167 		int numFields = mysql_num_fields(result);
168 		auto fields = mysql_fetch_fields(result);
169 
170 		string[] names;
171 		for(int i = 0; i < numFields; i++) {
172 			names ~= fromCstring(fields[i].name, fields[i].name_length);
173 		}
174 
175 		return names;
176 	}
177 
178 
179 
180 	bool[] columnIsNull;
181 	Row row;
182 }
183 
184 /++
185 	The main class for accessing the MySql database.
186 
187 	---
188 		// connect to database with the constructor
189 		auto db = new MySql("localhost", "my_user", "my_password", "my_database_name");
190 		// use the query function to execute sql...
191 		// use ? for data placeholders...
192 		db.query("INSERT INTO people (id, name) VALUES (?, ?)", 10, "My Name");
193 		// and use foreach to loop over result sets
194 		foreach(row; db.query("SELECT id, name FROM people ORDER BY name LIMIT 10"))
195 			writeln(row[0], " ", row["name"]); // index and name supported
196 	---
197 +/
198 class MySql : Database {
199 	this(string host, string user, string pass, string db, uint port = 0) {
200 		mysql = enforce!(DatabaseException)(
201 			mysql_init(null),
202 			"Couldn't init mysql");
203 		enforce!(DatabaseException)(
204 			mysql_real_connect(mysql, toCstring(host), toCstring(user), toCstring(pass), toCstring(db), port, null, 0),
205 			error());
206 
207 		dbname = db;
208 
209 		// we want UTF8 for everything
210 
211 		query("SET NAMES 'utf8mb4'");
212 		//query("SET CHARACTER SET utf8mb4");
213 	}
214 
215 	string dbname;
216 
217 	///
218 	override void startTransaction() {
219 		query("START TRANSACTION");
220 	}
221 
222 
223 	string sysTimeToValue(SysTime s) {
224 		return "cast('" ~ escape(s.toISOExtString()) ~ "' as datetime)";
225 	}
226 
227 	string error() {
228 		return fromCstring(mysql_error(mysql));
229 	}
230 
231 	void close() {
232 		if(mysql) {
233 			mysql_close(mysql);
234 			mysql = null;
235 		}
236 	}
237 
238 	~this() {
239 		close();
240 	}
241 
242 	///
243 	int lastInsertId() {
244 		return cast(int) mysql_insert_id(mysql);
245 	}
246 
247 
248 
249 	/// Builds and executes an INERT INTO statement
250 	int insert(string table, MySqlResult result, string[string] columnsToModify, string[] columnsToSkip) {
251 		assert(!result.empty);
252 		string sql = "INSERT INTO `" ~ table ~ "` ";
253 
254 		string cols = "(";
255 		string vals = "(";
256 		bool outputted = false;
257 
258 		string[string] columns;
259 		auto cnames = result.fieldNames;
260 		foreach(i, col; result.front.toStringArray) {
261 			bool skipMe = false;
262 			foreach(skip; columnsToSkip) {
263 				if(cnames[i] == skip) {
264 					skipMe = true;
265 					break;
266 				}
267 			}
268 			if(skipMe)
269 				continue;
270 
271 			if(outputted) {
272 				cols ~= ",";
273 				vals ~= ",";
274 			} else
275 				outputted = true;
276 
277 			cols ~= cnames[i];
278 
279 			if(result.columnIsNull[i] && cnames[i] !in columnsToModify)
280 				vals ~= "NULL";
281 			else {
282 				string v = col;
283 				if(cnames[i] in columnsToModify)
284 					v = columnsToModify[cnames[i]];
285 
286 				vals ~= "'" ~ escape(v) ~ "'";
287 
288 			}
289 		}
290 
291 		cols ~= ")";
292 		vals ~= ")";
293 
294 		sql ~= cols ~ " VALUES " ~ vals;
295 
296 		query(sql);
297 
298 		result.popFront;
299 
300 		return lastInsertId;
301 	}
302 
303 	string escape(string str) {
304 		ubyte[] buffer = new ubyte[str.length * 2 + 1];
305 		buffer.length = mysql_real_escape_string(mysql, buffer.ptr, cast(cstring) str.ptr, cast(uint) str.length);
306 
307 		return cast(string) buffer;
308 	}
309 
310 	string escapeBinaryString(const(ubyte)[] data) {
311 		return tohexsql(data);
312 	}
313 
314 	string escaped(T...)(string sql, T t) {
315 		static if(t.length > 0) {
316 			string fixedup;
317 			int pos = 0;
318 
319 
320 			void escAndAdd(string str, int q) {
321 				ubyte[] buffer = new ubyte[str.length * 2 + 1];
322 				buffer.length = mysql_real_escape_string(mysql, buffer.ptr, cast(cstring) str.ptr, str.length);
323 
324 				fixedup ~= sql[pos..q] ~ '\'' ~ cast(string) buffer ~ '\'';
325 
326 			}
327 
328 			foreach(a; t) {
329 				int q = sql[pos..$].indexOf("?");
330 				if(q == -1)
331 					break;
332 				q += pos;
333 
334 				static if(__traits(compiles, t is null)) {
335 					if(t is null)
336 						fixedup  ~= sql[pos..q] ~ "NULL";
337 					else
338 						escAndAdd(to!string(*a), q);
339 				} else {
340 					string str = to!string(a);
341 					escAndAdd(str, q);
342 				}
343 
344 				pos = q+1;
345 			}
346 
347 			fixedup ~= sql[pos..$];
348 
349 			sql = fixedup;
350 
351 			//writefln("\n\nExecuting sql: %s", sql);
352 		}
353 
354 		return sql;
355 	}
356 
357 
358 	/// Gets a minimal ORM object from a query
359 	ResultByDataObject!R queryDataObject(R = DataObject, T...)(string sql, T t) {
360 		// modify sql for the best data object grabbing
361 		sql = fixupSqlForDataObjectUse(sql);
362 
363 		auto magic = query(sql, t);
364 		return ResultByDataObject!R(cast(MySqlResult) magic, this);
365 	}
366 
367 
368 	/// ditto
369 	ResultByDataObject!R queryDataObjectWithCustomKeys(R = DataObject, T...)(string[string] keyMapping, string sql, T t) {
370 		sql = fixupSqlForDataObjectUse(sql, keyMapping);
371 
372 		auto magic = query(sql, t);
373 		return ResultByDataObject!R(cast(MySqlResult) magic, this);
374 	}
375 
376 
377 
378 	///
379 	int affectedRows() {
380 		return cast(int) mysql_affected_rows(mysql);
381 	}
382 
383 	override ResultSet queryImpl(string sql, Variant[] args...) {
384 		sql = escapedVariants(this, sql, args);
385 
386 		enforce!(DatabaseException)(
387 			!mysql_query(mysql, toCstring(sql)),
388 		error() ~ " :::: " ~ sql);
389 
390 		return new MySqlResult(mysql_store_result(mysql), sql);
391 	}
392 /+
393 	Result queryOld(T...)(string sql, T t) {
394 		sql = escaped(sql, t);
395 
396 		if(sql.length == 0)
397 			throw new DatabaseException("empty query");
398 		/*
399 		static int queryCount = 0;
400 		queryCount++;
401 		if(sql.indexOf("INSERT") != -1)
402 			stderr.writefln("%d: %s", queryCount, sql.replace("\n", " ").replace("\t", ""));
403 		*/
404 
405 		version(dryRun) {
406 			pragma(msg, "This is a dry run compile, no queries will be run");
407 			writeln(sql);
408 			return Result(null, null);
409 		}
410 
411 		enforceEx!(DatabaseException)(
412 			!mysql_query(mysql, toCstring(sql)),
413 		error() ~ " :::: " ~ sql);
414 
415 		return Result(mysql_store_result(mysql), sql);
416 	}
417 +/
418 /+
419 	struct ResultByAssoc {
420 		this(Result* r) {
421 			result = r;
422 			fields = r.fieldNames();
423 		}
424 
425 		ulong length() { return result.length; }
426 		bool empty() { return result.empty; }
427 		void popFront() { result.popFront(); }
428 		string[string] front() {
429 			auto r = result.front;
430 			string[string] ret;
431 			foreach(i, a; r) {
432 				ret[fields[i]] = a;
433 			}
434 
435 			return ret;
436 		}
437 
438 		@disable this(this) { }
439 
440 		string[] fields;
441 		Result* result;
442 	}
443 
444 
445 	struct ResultByStruct(T) {
446 		this(Result* r) {
447 			result = r;
448 			fields = r.fieldNames();
449 		}
450 
451 		ulong length() { return result.length; }
452 		bool empty() { return result.empty; }
453 		void popFront() { result.popFront(); }
454 		T front() {
455 			auto r = result.front;
456 			string[string] ret;
457 			foreach(i, a; r) {
458 				ret[fields[i]] = a;
459 			}
460 
461 			T s;
462 			// FIXME: should use tupleOf
463 			foreach(member; s.tupleof) {
464 				if(member.stringof in ret)
465 					member = to!(typeof(member))(ret[member]);
466 			}
467 
468 			return s;
469 		}
470 
471 		@disable this(this) { }
472 
473 		string[] fields;
474 		Result* result;
475 	}
476 +/
477 
478 /+
479 
480 
481 	struct Result {
482 		private Result* heaped() {
483 			auto r = new Result(result, sql, false);
484 
485 			r.tupleof = this.tupleof;
486 
487 			this.itemsTotal = 0;
488 			this.result = null;
489 
490 			return r;
491 		}
492 
493 		this(MYSQL_RES* r, string sql, bool prime = true) {
494 			result = r;
495 			itemsTotal = length;
496 			itemsUsed = 0;
497 			this.sql = sql;
498 			// prime it here
499 			if(prime && itemsTotal)
500 				fetchNext();
501 		}
502 
503 		string sql;
504 
505 		~this() {
506 			if(result !is null)
507 			mysql_free_result(result);
508 		}
509 
510 		/+
511 		string[string][] fetchAssoc() {
512 
513 		}
514 		+/
515 
516 		ResultByAssoc byAssoc() {
517 			return ResultByAssoc(&this);
518 		}
519 
520 		ResultByStruct!(T) byStruct(T)() {
521 			return ResultByStruct!(T)(&this);
522 		}
523 
524 		string[] fieldNames() {
525 			int numFields = mysql_num_fields(result);
526 			auto fields = mysql_fetch_fields(result);
527 
528 			string[] names;
529 			for(int i = 0; i < numFields; i++) {
530 				names ~= fromCstring(fields[i].name);
531 			}
532 
533 			return names;
534 		}
535 
536 		MYSQL_FIELD[] fields() {
537 			int numFields = mysql_num_fields(result);
538 			auto fields = mysql_fetch_fields(result);
539 
540 			MYSQL_FIELD[] ret;
541 			for(int i = 0; i < numFields; i++) {
542 				ret ~= fields[i];
543 			}
544 
545 			return ret;
546 		}
547 
548 		ulong length() {
549 			if(result is null)
550 				return 0;
551 			return mysql_num_rows(result);
552 		}
553 
554 		bool empty() {
555 			return itemsUsed == itemsTotal;
556 		}
557 
558 		Row front() {
559 			return row;
560 		}
561 
562 		void popFront() {
563 			itemsUsed++;
564 			if(itemsUsed < itemsTotal) {
565 				fetchNext();
566 			}
567 		}
568 
569 		void fetchNext() {
570 			auto r = mysql_fetch_row(result);
571 			uint numFields = mysql_num_fields(result);
572 			uint* lengths = mysql_fetch_lengths(result);
573 			row.length = 0;
574 			// potential FIXME: not really binary safe
575 
576 			columnIsNull.length = numFields;
577 			for(int a = 0; a < numFields; a++) {
578 				if(*(r+a) is null) {
579 					row ~= null;
580 					columnIsNull[a] = true;
581 				} else {
582 					row ~= fromCstring(*(r+a), *(lengths + a));
583 					columnIsNull[a] = false;
584 				}
585 			}
586 		}
587 
588 		@disable this(this) {}
589 		private MYSQL_RES* result;
590 
591 		ulong itemsTotal;
592 		ulong itemsUsed;
593 
594 		alias string[] Row;
595 
596 		Row row;
597 		bool[] columnIsNull; // FIXME: should be part of the row
598 	}
599 +/
600 	MYSQL* getHandle() {
601 		return mysql;
602 	}
603 
604   private:
605 	MYSQL* mysql;
606 }
607 
608 struct ResultByDataObject(ObjType) if (is(ObjType : DataObject)) {
609 	this(MySqlResult r, MySql mysql) {
610 		result = r;
611 		auto fields = r.fields();
612 		this.mysql = mysql;
613 
614 		foreach(i, f; fields) {
615 			string tbl = fromCstring(f.org_table is null ? f.table : f.org_table, f.org_table is null ? f.table_length : f.org_table_length);
616 			mappings[fromCstring(f.name)] = tuple(
617 					tbl,
618 					fromCstring(f.org_name is null ? f.name : f.org_name, f.org_name is null ? f.name_length : f.org_name_length));
619 		}
620 
621 
622 	}
623 
624 	Tuple!(string, string)[string] mappings;
625 
626 	ulong length() { return result.length; }
627 	bool empty() { return result.empty; }
628 	void popFront() { result.popFront(); }
629 	ObjType front() {
630 		return new ObjType(mysql, result.front.toAA, mappings);
631 	}
632 	// would it be good to add a new() method? would be valid even if empty
633 	// it'd just fill in the ID's at random and allow you to do the rest
634 
635 	@disable this(this) { }
636 
637 	MySqlResult result;
638 	MySql mysql;
639 }
640 
641 
642 // thanks to 0xEAB on discord for sending me initial prepared statement support
643 
644 struct Statement
645 {
646     ~this()
647     {
648         if (this.statement !is null)
649         {
650             this.statement.mysql_stmt_close();
651             this.statement = null;
652         }
653     }
654 
655     void reset()
656     {
657 	mysql_stmt_reset(statement);
658     }
659 
660 private:
661     MYSQL_STMT* statement;
662     MYSQL_BIND[] params;
663 }
664 
665 Statement* prepare(MySql m, string query) @trusted
666 {
667     MYSQL_STMT* s = m.getHandle.mysql_stmt_init();
668     immutable x = s.mysql_stmt_prepare(query.toStringz, cast(int) query.length);
669 
670     if (x != 0)
671     {
672         throw new Exception(m.getHandle.mysql_error.fromCstring);
673     }
674 
675     return new Statement(s);
676 }
677 
678 import std.traits : isNumeric;
679 
680 void bindParameter(T)(Statement* s, ref T value) if (isNumeric!T)
681 {
682     import std.traits : isUnsigned;
683 
684     MYSQL_BIND p = MYSQL_BIND();
685 
686     p.buffer = &value;
687     p.buffer_type = mySqlType!T;
688     p.is_unsigned = isUnsigned!T;
689 
690     s.params ~= p;
691     immutable x = s.statement.mysql_stmt_bind_param(&(s.params[$ - 1]));
692 
693     if (x != 0)
694     {
695         throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string);
696     }
697 }
698 
699 void bindParameterNull(Statement* s)
700 {
701     MYSQL_BIND p = MYSQL_BIND();
702 
703     p.buffer_type = enum_field_types.MYSQL_TYPE_NULL;
704 
705     s.params ~= p;
706     immutable x = s.statement.mysql_stmt_bind_param(null);
707 
708     if (x != 0)
709     {
710         throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string);
711     }
712 }
713 
714 void bindParameter(T)(Statement* s, T value) if (is(T == string))
715 {
716     import std.traits : isUnsigned;
717 
718     MYSQL_BIND p = MYSQL_BIND();
719 
720     p.buffer = cast(void*) value.toCstring();
721     p.buffer_type = mySqlType!string;
722     p.buffer_length = value.length;
723 
724     s.params ~= p;
725     immutable x = s.statement.mysql_stmt_bind_param(&s.params[$ - 1]);
726 
727     if (x != 0)
728     {
729         throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string);
730     }
731 }
732 
733 void execute(Statement* s) @trusted
734 {
735     immutable x = s.statement.mysql_stmt_execute();
736 
737     if (x != 0)
738     {
739         throw new Exception(s.statement.mysql_stmt_error.fromStringz.to!string);
740     }
741 }
742 
743 
744 extern(System) {
745 	/*
746 		from <my_alloc.h>
747 		original header actually contains members,
748 		but guess we don't need them here
749 	*/
750 	struct USED_MEM;
751 
752 	/*
753 		from <my_alloc.h>
754 	*/
755 	struct MEM_ROOT
756 	{
757 		USED_MEM* free; /* blocks with free memory in it */
758 		USED_MEM* used; /* blocks almost without free memory */
759 		USED_MEM* pre_alloc; /* preallocated block */
760 		/* if block have less memory it will be put in 'used' list */
761 		size_t min_malloc;
762 		size_t block_size; /* initial block size */
763 		uint block_num; /* allocated blocks counter */
764 		/*
765 		first free block in queue test counter (if it exceed
766 		MAX_BLOCK_USAGE_BEFORE_DROP block will be dropped in 'used' list)
767 		*/
768 		uint first_block_usage;
769 
770 		void function () error_handler;
771 	}
772 
773 	/*
774 		from <mysql_com.h>
775 
776 		original header actually contains members,
777 		but guess we don't need them here
778 	*/
779 	struct NET;
780 
781 	/* from <mysql_com.h> */
782 	enum MYSQL_ERRMSG_SIZE = 512;
783 
784 	/* from <mysql_com.h> */
785 	enum enum_field_types {
786 		MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY,
787 		MYSQL_TYPE_SHORT,  MYSQL_TYPE_LONG,
788 		MYSQL_TYPE_FLOAT,  MYSQL_TYPE_DOUBLE,
789 		MYSQL_TYPE_NULL,   MYSQL_TYPE_TIMESTAMP,
790 		MYSQL_TYPE_LONGLONG,MYSQL_TYPE_INT24,
791 		MYSQL_TYPE_DATE,   MYSQL_TYPE_TIME,
792 		MYSQL_TYPE_DATETIME, MYSQL_TYPE_YEAR,
793 		MYSQL_TYPE_NEWDATE, MYSQL_TYPE_VARCHAR,
794 		MYSQL_TYPE_BIT,
795 
796 			/*
797 				mysql-5.6 compatibility temporal types.
798 				They're only used internally for reading RBR
799 				mysql-5.6 binary log events and mysql-5.6 frm files.
800 				They're never sent to the client.
801 			*/
802 			MYSQL_TYPE_TIMESTAMP2,
803 			MYSQL_TYPE_DATETIME2,
804 			MYSQL_TYPE_TIME2,
805 
806 			MYSQL_TYPE_NEWDECIMAL=246,
807 
808 		MYSQL_TYPE_ENUM=247,
809 		MYSQL_TYPE_SET=248,
810 		MYSQL_TYPE_TINY_BLOB=249,
811 		MYSQL_TYPE_MEDIUM_BLOB=250,
812 		MYSQL_TYPE_LONG_BLOB=251,
813 		MYSQL_TYPE_BLOB=252,
814 		MYSQL_TYPE_VAR_STRING=253,
815 		MYSQL_TYPE_STRING=254,
816 		MYSQL_TYPE_GEOMETRY=255
817 	}
818 
819 	/* from <my_list.h>*/
820 	struct LIST
821 	{
822 		LIST* prev;
823 		LIST* next;
824 		void* data;
825 	}
826 
827 	struct MYSQL;
828 	struct MYSQL_RES;
829 	/* typedef */ alias const(ubyte)* cstring;
830 
831 	alias my_bool = char;
832 	alias my_ulonglong = ulong;
833 
834 	struct MYSQL_FIELD {
835 		  cstring name;                 /* Name of column */
836 		  cstring org_name;             /* Original column name, if an alias */
837 		  cstring table;                /* Table of column if column was a field */
838 		  cstring org_table;            /* Org table name, if table was an alias */
839 		  cstring db;                   /* Database for table */
840 		  cstring catalog;	      /* Catalog for table */
841 		  cstring def;                  /* Default value (set by mysql_list_fields) */
842 		  c_ulong length;       /* Width of column (create length) */
843 		  c_ulong max_length;   /* Max width for selected set */
844 		  uint name_length;
845 		  uint org_name_length;
846 		  uint table_length;
847 		  uint org_table_length;
848 		  uint db_length;
849 		  uint catalog_length;
850 		  uint def_length;
851 		  uint flags;         /* Div flags */
852 		  uint decimals;      /* Number of decimals in field */
853 		  uint charsetnr;     /* Character set */
854 		  uint type; /* Type of field. See mysql_com.h for types */
855 		  // type is actually an enum btw
856 
857 		version(MySQL_51) {
858 			void* extension;
859 		}
860 	}
861 
862 	struct MYSQL_ROWS
863 	{
864 		MYSQL_ROWS* next; /* list of rows */
865 		MYSQL_ROW data;
866 		c_ulong length;
867 	}
868 
869 	alias MYSQL_ROW_OFFSET = MYSQL_ROWS*; /* offset to current row */
870 
871 	struct EMBEDDED_QUERY_RESULT;
872 
873 	struct MYSQL_DATA
874 	{
875 		MYSQL_ROWS* data;
876 		EMBEDDED_QUERY_RESULT* embedded_info;
877 		MEM_ROOT alloc;
878 		my_ulonglong rows;
879 		uint fields;
880 
881 		version(MySQL_51) {
882 			/* extra info for embedded library */
883 			void* extension;
884 		}
885 	}
886 
887 	/* statement state */
888 	enum enum_mysql_stmt_state
889 	{
890 		MYSQL_STMT_INIT_DONE = 1,
891 		MYSQL_STMT_PREPARE_DONE = 2,
892 		MYSQL_STMT_EXECUTE_DONE = 3,
893 		MYSQL_STMT_FETCH_DONE = 4
894 	}
895 
896 	enum enum_stmt_attr_type
897 	{
898 		/**
899 			When doing mysql_stmt_store_result calculate max_length attribute
900 			of statement metadata. This is to be consistent with the old API,
901 			where this was done automatically.
902 			In the new API we do that only by request because it slows down
903 			mysql_stmt_store_result sufficiently.
904 		*/
905 		STMT_ATTR_UPDATE_MAX_LENGTH = 0,
906 		/**
907 			unsigned long with combination of cursor flags (read only, for update, etc)
908 		*/
909 		STMT_ATTR_CURSOR_TYPE = 1,
910 		/**
911 			Amount of rows to retrieve from server per one fetch if using cursors.
912 			Accepts unsigned long attribute in the range 1 - ulong_max
913 		*/
914 		STMT_ATTR_PREFETCH_ROWS = 2
915 	}
916 
917 	struct MYSQL_BIND
918 	{
919 		c_ulong* length; /* output length pointer */
920 		my_bool* is_null; /* Pointer to null indicator */
921 		void* buffer; /* buffer to get/put data */
922 		/* set this if you want to track data truncations happened during fetch */
923 		my_bool* error;
924 		ubyte* row_ptr; /* for the current data position */
925 		void function (NET* net, MYSQL_BIND* param) store_param_func;
926 		void function (MYSQL_BIND*, MYSQL_FIELD*, ubyte** row) fetch_result;
927 		void function (MYSQL_BIND*, MYSQL_FIELD*, ubyte** row) skip_result;
928 		/* output buffer length, must be set when fetching str/binary */
929 		c_ulong buffer_length;
930 		c_ulong offset; /* offset position for char/binary fetch */
931 		c_ulong length_value; /* Used if length is 0 */
932 		uint param_number; /* For null count and error messages */
933 		uint pack_length; /* Internal length for packed data */
934 		enum_field_types buffer_type; /* buffer type */
935 		my_bool error_value; /* used if error is 0 */
936 		my_bool is_unsigned; /* set if integer type is unsigned */
937 		my_bool long_data_used; /* If used with mysql_send_long_data */
938 		my_bool is_null_value; /* Used if is_null is 0 */
939 		void* extension;
940 	}
941 
942 	struct st_mysql_stmt_extension;
943 
944 	/* statement handler */
945 	struct MYSQL_STMT
946 	{
947 		MEM_ROOT mem_root; /* root allocations */
948 		LIST list; /* list to keep track of all stmts */
949 		MYSQL* mysql; /* connection handle */
950 		MYSQL_BIND* params; /* input parameters */
951 		MYSQL_BIND* bind; /* output parameters */
952 		MYSQL_FIELD* fields; /* result set metadata */
953 		MYSQL_DATA result; /* cached result set */
954 		MYSQL_ROWS* data_cursor; /* current row in cached result */
955 		/*
956 		mysql_stmt_fetch() calls this function to fetch one row (it's different
957 		for buffered, unbuffered and cursor fetch).
958 		*/
959 		int function (MYSQL_STMT* stmt, ubyte** row) read_row_func;
960 		/* copy of mysql->affected_rows after statement execution */
961 		my_ulonglong affected_rows;
962 		my_ulonglong insert_id; /* copy of mysql->insert_id */
963 		c_ulong stmt_id; /* Id for prepared statement */
964 		c_ulong flags; /* i.e. type of cursor to open */
965 		c_ulong prefetch_rows; /* number of rows per one COM_FETCH */
966 		/*
967 		Copied from mysql->server_status after execute/fetch to know
968 		server-side cursor status for this statement.
969 		*/
970 		uint server_status;
971 		uint last_errno; /* error code */
972 		uint param_count; /* input parameter count */
973 		uint field_count; /* number of columns in result set */
974 		enum_mysql_stmt_state state; /* statement state */
975 		char[MYSQL_ERRMSG_SIZE] last_error; /* error message */
976 		char[6] sqlstate;
977 		/* Types of input parameters should be sent to server */
978 		my_bool send_types_to_server;
979 		my_bool bind_param_done; /* input buffers were supplied */
980 		ubyte bind_result_done; /* output buffers were supplied */
981 		/* mysql_stmt_close() had to cancel this result */
982 		my_bool unbuffered_fetch_cancelled;
983 		/*
984 		Is set to true if we need to calculate field->max_length for
985 		metadata fields when doing mysql_stmt_store_result.
986 		*/
987 		my_bool update_max_length;
988 		st_mysql_stmt_extension* extension;
989 	}
990 
991 	/* typedef */ alias cstring* MYSQL_ROW;
992 
993 	cstring mysql_get_client_info();
994 	MYSQL* mysql_init(MYSQL*);
995 	uint mysql_errno(MYSQL*);
996 	cstring mysql_error(MYSQL*);
997 
998 	MYSQL* mysql_real_connect(MYSQL*, cstring, cstring, cstring, cstring, uint, cstring, c_ulong);
999 
1000 	int mysql_query(MYSQL*, cstring);
1001 
1002 	void mysql_close(MYSQL*);
1003 
1004 	ulong mysql_num_rows(MYSQL_RES*);
1005 	uint mysql_num_fields(MYSQL_RES*);
1006 	bool mysql_eof(MYSQL_RES*);
1007 
1008 	ulong mysql_affected_rows(MYSQL*);
1009 	ulong mysql_insert_id(MYSQL*);
1010 
1011 	MYSQL_RES* mysql_store_result(MYSQL*);
1012 	MYSQL_RES* mysql_use_result(MYSQL*);
1013 
1014 	MYSQL_ROW mysql_fetch_row(MYSQL_RES *);
1015 	c_ulong* mysql_fetch_lengths(MYSQL_RES*);
1016 	MYSQL_FIELD* mysql_fetch_field(MYSQL_RES*);
1017 	MYSQL_FIELD* mysql_fetch_fields(MYSQL_RES*);
1018 
1019 	uint mysql_real_escape_string(MYSQL*, ubyte* to, cstring from, c_ulong length);
1020 
1021 	void mysql_free_result(MYSQL_RES*);
1022 
1023 	MYSQL_STMT* mysql_stmt_init (MYSQL* mysql);
1024 	int mysql_stmt_prepare (MYSQL_STMT* stmt, const(char)* query, c_ulong length);
1025 	int mysql_stmt_execute (MYSQL_STMT* stmt);
1026 	my_bool mysql_stmt_bind_param (MYSQL_STMT* stmt, MYSQL_BIND* bnd);
1027 	my_bool mysql_stmt_close (MYSQL_STMT* stmt);
1028 	my_bool mysql_stmt_free_result (MYSQL_STMT* stmt);
1029 	my_bool mysql_stmt_reset (MYSQL_STMT* stmt);
1030 	uint mysql_stmt_errno (MYSQL_STMT* stmt);
1031 	const(char)* mysql_stmt_error (MYSQL_STMT* stmt);
1032 	const(char)* mysql_stmt_sqlstate (MYSQL_STMT* stmt);
1033 	my_ulonglong mysql_stmt_num_rows (MYSQL_STMT* stmt);
1034 	my_ulonglong mysql_stmt_affected_rows (MYSQL_STMT* stmt);
1035 	my_ulonglong mysql_stmt_insert_id (MYSQL_STMT* stmt);
1036 
1037 }
1038 
1039 import std.string;
1040 cstring toCstring(string c) {
1041 	return cast(cstring) toStringz(c);
1042 }
1043 
1044 import std.array;
1045 string fromCstring(cstring c, size_t len = size_t.max) @system {
1046 	string ret;
1047 	if(c is null)
1048 		return null;
1049 	if(len == 0)
1050 		return "";
1051 	if(len == size_t.max) {
1052 		auto iterator = c;
1053 		len = 0;
1054 		while(*iterator) {
1055 			iterator++;
1056 			len++;
1057 		}
1058 		assert(len >= 0);
1059 	}
1060 
1061 	ret = cast(string) (c[0 .. len].idup);
1062 
1063 	return ret;
1064 }
1065 
1066 enum_field_types getMySqlType(T)() {
1067 	static if (is(T == bool))
1068 		return enum_field_types.MYSQL_TYPE_TINY;
1069 
1070 	static if (is(T == char))
1071 		return enum_field_types.MYSQL_TYPE_TINY;
1072 
1073 		static if (is(T == byte) || is(T == ubyte))
1074 		return enum_field_types.MYSQL_TYPE_TINY;
1075 
1076 	else static if (is(T == short) || is(T == ushort))
1077 		return enum_field_types.MYSQL_TYPE_SHORT;
1078 
1079 	else static if (is(T == int) || is(T == uint))
1080 		return enum_field_types.MYSQL_TYPE_LONG;
1081 
1082 	else static if (is(T == long) || is(T == ulong))
1083 		return enum_field_types.MYSQL_TYPE_LONGLONG;
1084 
1085 	else static if (is(T == string))
1086 		return enum_field_types.MYSQL_TYPE_STRING;
1087 
1088 	else static if (is(T == float))
1089 		return enum_field_types.MYSQL_TYPE_FLOAT;
1090 
1091 	else static if (is(T == double))
1092 		return enum_field_types.MYSQL_TYPE_DOUBLE;
1093 
1094 	//else static if (is(T == byte[]))
1095 	//	return enum_field_types.MYSQL_TYPE_BLOB;
1096 
1097 	else
1098 		static assert("No MySQL equivalent known for " ~ T);
1099 }
1100 
1101 enum enum_field_types mySqlType(T) = getMySqlType!T;
1102 
1103 // FIXME: this should work generically with all database types and them moved to database.d
1104 ///
1105 Ret queryOneRow(Ret = Row, DB, string file = __FILE__, size_t line = __LINE__, T...)(DB db, string sql, T t) if(
1106 	(is(DB : Database))
1107 	// && (is(Ret == Row) || is(Ret : DataObject)))
1108 	)
1109 {
1110 	static if(is(Ret : DataObject) && is(DB == MySql)) {
1111 		auto res = db.queryDataObject!Ret(sql, t);
1112 		if(res.empty)
1113 			throw new EmptyResultException("result was empty", file, line);
1114 		return res.front;
1115 	} else static if(is(Ret == Row)) {
1116 		auto res = db.query(sql, t);
1117 		if(res.empty)
1118 			throw new EmptyResultException("result was empty", file, line);
1119 		return res.front;
1120 	} else static assert(0, "Unsupported single row query return value, " ~ Ret.stringof);
1121 }
1122 
1123 ///
1124 class EmptyResultException : Exception {
1125 	this(string message, string file = __FILE__, size_t line = __LINE__) {
1126 		super(message, file, line);
1127 	}
1128 }
1129 
1130 
1131 /*
1132 void main() {
1133 	auto mysql = new MySql("localhost", "uname", "password", "test");
1134 	scope(exit) delete mysql;
1135 
1136 	mysql.query("INSERT INTO users (id, password) VALUES (?, ?)", 10, "lol");
1137 
1138 	foreach(row; mysql.query("SELECT * FROM users")) {
1139 		writefln("%s %s %s %s", row["id"], row[0], row[1], row["username"]);
1140 	}
1141 }
1142 */
1143 
1144 /*
1145 struct ResultByStruct(T) {
1146 	this(MySql.Result* r) {
1147 		result = r;
1148 		fields = r.fieldNames();
1149 	}
1150 
1151 	ulong length() { return result.length; }
1152 	bool empty() { return result.empty; }
1153 	void popFront() { result.popFront(); }
1154 	T front() {
1155 		auto r = result.front;
1156 		T ret;
1157 		foreach(i, a; r) {
1158 			ret[fields[i]] = a;
1159 		}
1160 
1161 		return ret;
1162 	}
1163 
1164 	@disable this(this) { }
1165 
1166 	string[] fields;
1167 	MySql.Result* result;
1168 }
1169 */
1170 
1171 
1172 /+
1173 	mysql.linq.tablename.field[key] // select field from tablename where id = key
1174 
1175 	mysql.link["name"].table.field[key] // select field from table where name = key
1176 
1177 
1178 	auto q = mysql.prepQuery("select id from table where something");
1179 	q.sort("name");
1180 	q.limit(start, count);
1181 	q.page(3, pagelength = ?);
1182 
1183 	q.execute(params here); // returns the same Result range as query
1184 +/
1185 
1186 /*
1187 void main() {
1188 	auto db = new MySql("localhost", "uname", "password", "test");
1189 	foreach(item; db.queryDataObject("SELECT users.*, username
1190 		FROM users, password_manager_accounts
1191 		WHERE password_manager_accounts.user_id =  users.id LIMIT 5")) {
1192 		writefln("item: %s, %s", item.id, item.username);
1193 		item.first = "new";
1194 		item.last = "new2";
1195 		item.username = "kill";
1196 		//item.commitChanges();
1197 	}
1198 }
1199 */
1200 
1201 
1202 /*
1203 Copyright: Adam D. Ruppe, 2009 - 2024
1204 License:   GPL (erroneously started to be Boost in 2011)
1205 Authors: Adam D. Ruppe, with contributions from Nick Sabalausky
1206 */
1207