1 /// Use a DB via SQL prepared statements.
2 module mysql.prepared;
3 
4 import std.exception;
5 import std.range;
6 import std.traits;
7 import std.typecons;
8 import std.variant;
9 
10 import mysql.commands;
11 import mysql.exceptions;
12 import mysql.protocol.comms;
13 import mysql.protocol.constants;
14 import mysql.protocol.packets;
15 import mysql.result;
16 debug(MYSQLN_TESTS)
17 	import mysql.test.common;
18 
19 /++
20 A struct to represent specializations of prepared statement parameters.
21 
22 If you need to send large objects to the database it might be convenient to
23 send them in pieces. The `chunkSize` and `chunkDelegate` variables allow for this.
24 If both are provided then the corresponding column will be populated by calling the delegate repeatedly.
25 The source should fill the indicated slice with data and arrange for the delegate to
26 return the length of the data supplied (in bytes). If that is less than the `chunkSize`
27 then the chunk will be assumed to be the last one.
28 +/
29 struct ParameterSpecialization
30 {
31 	import mysql.protocol.constants;
32 	
33 	size_t pIndex;    //parameter number 0 - number of params-1
34 	SQLType type = SQLType.INFER_FROM_D_TYPE;
35 	uint chunkSize; /// In bytes
36 	uint delegate(ubyte[]) chunkDelegate;
37 }
38 ///ditto
39 alias PSN = ParameterSpecialization;
40 
41 @("paramSpecial")
42 debug(MYSQLN_TESTS)
43 unittest
44 {
45 	import std.array;
46 	import std.range;
47 	import mysql.connection;
48 	import mysql.test.common;
49 	mixin(scopedCn);
50 
51 	// Setup
52 	cn.exec("DROP TABLE IF EXISTS `paramSpecial`");
53 	cn.exec("CREATE TABLE `paramSpecial` (
54 		`data` LONGBLOB
55 	) ENGINE=InnoDB DEFAULT CHARSET=utf8");
56 
57 	immutable totalSize = 1000; // Deliberately not a multiple of chunkSize below
58 	auto alph = cast(const(ubyte)[]) "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
59 	auto data = alph.cycle.take(totalSize).array;
60 
61 	int chunkSize;
62 	const(ubyte)[] dataToSend;
63 	bool finished;
64 	uint sender(ubyte[] chunk)
65 	{
66 		assert(!finished);
67 		assert(chunk.length == chunkSize);
68 
69 		if(dataToSend.length < chunkSize)
70 		{
71 			auto actualSize = cast(uint) dataToSend.length;
72 			chunk[0..actualSize] = dataToSend[];
73 			finished = true;
74 			dataToSend.length = 0;
75 			return actualSize;
76 		}
77 		else
78 		{
79 			chunk[] = dataToSend[0..chunkSize];
80 			dataToSend = dataToSend[chunkSize..$];
81 			return chunkSize;
82 		}
83 	}
84 
85 	immutable selectSQL = "SELECT `data` FROM `paramSpecial`";
86 
87 	// Sanity check
88 	cn.exec("INSERT INTO `paramSpecial` VALUES (\""~(cast(string)data)~"\")");
89 	auto value = cn.queryValue(selectSQL);
90 	assert(!value.isNull);
91 	assert(value.get == data);
92 
93 	{
94 		// Clear table
95 		cn.exec("DELETE FROM `paramSpecial`");
96 		value = cn.queryValue(selectSQL); // Ensure deleted
97 		assert(value.isNull);
98 
99 		// Test: totalSize as a multiple of chunkSize
100 		chunkSize = 100;
101 		assert(cast(int)(totalSize / chunkSize) * chunkSize == totalSize);
102 		auto paramSpecial = ParameterSpecialization(0, SQLType.INFER_FROM_D_TYPE, chunkSize, &sender);
103 
104 		finished = false;
105 		dataToSend = data;
106 		auto prepared = cn.prepare("INSERT INTO `paramSpecial` VALUES (?)");
107 		prepared.setArg(0, cast(ubyte[])[], paramSpecial);
108 		assert(cn.exec(prepared) == 1);
109 		value = cn.queryValue(selectSQL);
110 		assert(!value.isNull);
111 		assert(value.get == data);
112 	}
113 
114 	{
115 		// Clear table
116 		cn.exec("DELETE FROM `paramSpecial`");
117 		value = cn.queryValue(selectSQL); // Ensure deleted
118 		assert(value.isNull);
119 
120 		// Test: totalSize as a non-multiple of chunkSize
121 		chunkSize = 64;
122 		assert(cast(int)(totalSize / chunkSize) * chunkSize != totalSize);
123 		auto paramSpecial = ParameterSpecialization(0, SQLType.INFER_FROM_D_TYPE, chunkSize, &sender);
124 
125 		finished = false;
126 		dataToSend = data;
127 		auto prepared = cn.prepare("INSERT INTO `paramSpecial` VALUES (?)");
128 		prepared.setArg(0, cast(ubyte[])[], paramSpecial);
129 		assert(cn.exec(prepared) == 1);
130 		value = cn.queryValue(selectSQL);
131 		assert(!value.isNull);
132 		assert(value.get == data);
133 	}
134 }
135 
136 /++
137 Encapsulation of a prepared statement.
138 
139 Create this via the function `mysql.connection.prepare`. Set your arguments (if any) via
140 the functions provided, and then run the statement by passing it to
141 `mysql.commands.exec`/`mysql.commands.query`/etc in place of the sql string parameter.
142 
143 Commands that are expected to return a result set - queries - have distinctive
144 methods that are enforced. That is it will be an error to call such a method
145 with an SQL command that does not produce a result set. So for commands like
146 SELECT, use the `mysql.commands.query` functions. For other commands, like
147 INSERT/UPDATE/CREATE/etc, use `mysql.commands.exec`.
148 +/
149 struct Prepared
150 {
151 private:
152 	const(char)[] _sql;
153 
154 package:
155 	ushort _numParams; /// Number of parameters this prepared statement takes
156 	PreparedStmtHeaders _headers;
157 	Variant[] _inParams;
158 	ParameterSpecialization[] _psa;
159 	ColumnSpecialization[] _columnSpecials;
160 	ulong _lastInsertID;
161 
162 	ExecQueryImplInfo getExecQueryImplInfo(uint statementId)
163 	{
164 		return ExecQueryImplInfo(true, null, statementId, _headers, _inParams, _psa);
165 	}
166 	
167 public:
168 	/++
169 	Constructor. You probably want `mysql.connection.prepare` instead of this.
170  	
171 	Call `mysqln.connection.prepare` instead of this, unless you are creating
172 	your own transport bypassing `mysql.connection.Connection` entirely.
173 	The prepared statement must be registered on the server BEFORE this is
174 	called (which `mysqln.connection.prepare` does).
175 
176 	Internally, the result of a successful outcome will be a statement handle - an ID -
177 	for the prepared statement, a count of the parameters required for
178 	execution of the statement, and a count of the columns that will be present
179 	in any result set that the command generates.
180 
181 	The server will then proceed to send prepared statement headers,
182 	including parameter descriptions, and result set field descriptions,
183 	followed by an EOF packet.
184 	+/
185 	this(const(char[]) sql, PreparedStmtHeaders headers, ushort numParams)
186 	{
187 		this._sql        = sql;
188 		this._headers    = headers;
189 		this._numParams  = numParams;
190 		_inParams.length = numParams;
191 		_psa.length      = numParams;
192 	}
193 
194 	/++
195 	Prepared statement parameter setter.
196 
197 	The value may, but doesn't have to be, wrapped in a Variant. If so,
198 	null is handled correctly.
199 	
200 	The value may, but doesn't have to be, a pointer to the desired value.
201 
202 	The value may, but doesn't have to be, wrapped in a Nullable!T. If so,
203 	null is handled correctly.
204 
205 	The value can be null.
206 
207 	Parameter specializations (ie, for chunked transfer) can be added if required.
208 	If you wish to use chunked transfer (via `psn`), note that you must supply
209 	a dummy value for `val` that's typed `ubyte[]`. For example: `cast(ubyte[])[]`.
210 	
211 	Type_Mappings: $(TYPE_MAPPINGS)
212 
213 	Params: index = The zero based index
214 	+/
215 	void setArg(T)(size_t index, T val, ParameterSpecialization psn = PSN(0, SQLType.INFER_FROM_D_TYPE, 0, null))
216 		if(!isInstanceOf!(Nullable, T))
217 	{
218 		// Now in theory we should be able to check the parameter type here, since the
219 		// protocol is supposed to send us type information for the parameters, but this
220 		// capability seems to be broken. This assertion is supported by the fact that
221 		// the same information is not available via the MySQL C API either. It is up
222 		// to the programmer to ensure that appropriate type information is embodied
223 		// in the variant array, or provided explicitly. This sucks, but short of
224 		// having a client side SQL parser I don't see what can be done.
225 
226 		enforce!MYX(index < _numParams, "Parameter index out of range.");
227 
228 		_inParams[index] = val;
229 		psn.pIndex = index;
230 		_psa[index] = psn;
231 	}
232 
233 	///ditto
234 	void setArg(T)(size_t index, Nullable!T val, ParameterSpecialization psn = PSN(0, SQLType.INFER_FROM_D_TYPE, 0, null))
235 	{
236 		if(val.isNull)
237 			setArg(index, null, psn);
238 		else
239 			setArg(index, val.get(), psn);
240 	}
241 
242 	@("setArg-typeMods")
243 	debug(MYSQLN_TESTS)
244 	unittest
245 	{
246 		import mysql.test.common;
247 		mixin(scopedCn);
248 
249 		// Setup
250 		cn.exec("DROP TABLE IF EXISTS `setArg-typeMods`");
251 		cn.exec("CREATE TABLE `setArg-typeMods` (
252 			`i` INTEGER
253 		) ENGINE=InnoDB DEFAULT CHARSET=utf8");
254 
255 		auto insertSQL = "INSERT INTO `setArg-typeMods` VALUES (?)";
256 
257 		// Sanity check
258 		{
259 			int i = 111;
260 			assert(cn.exec(insertSQL, i) == 1);
261 			auto value = cn.queryValue("SELECT `i` FROM `setArg-typeMods`");
262 			assert(!value.isNull);
263 			assert(value.get == i);
264 		}
265 
266 		// Test const(int)
267 		{
268 			const(int) i = 112;
269 			assert(cn.exec(insertSQL, i) == 1);
270 		}
271 
272 		// Test immutable(int)
273 		{
274 			immutable(int) i = 113;
275 			assert(cn.exec(insertSQL, i) == 1);
276 		}
277 
278 		// Note: Variant doesn't seem to support
279 		// `shared(T)` or `shared(const(T)`. Only `shared(immutable(T))`.
280 
281 		// Test shared immutable(int)
282 		{
283 			shared immutable(int) i = 113;
284 			assert(cn.exec(insertSQL, i) == 1);
285 		}
286 	}
287 
288 	/++
289 	Bind a tuple of D variables to the parameters of a prepared statement.
290 	
291 	You can use this method to bind a set of variables if you don't need any specialization,
292 	that is chunked transfer is not neccessary.
293 	
294 	The tuple must match the required number of parameters, and it is the programmer's
295 	responsibility to ensure that they are of appropriate types.
296 
297 	Type_Mappings: $(TYPE_MAPPINGS)
298 	+/
299 	void setArgs(T...)(T args)
300 		if(T.length == 0 || !is(T[0] == Variant[]))
301 	{
302 		enforce!MYX(args.length == _numParams, "Argument list supplied does not match the number of parameters.");
303 
304 		foreach (size_t i, arg; args)
305 			setArg(i, arg);
306 	}
307 
308 	/++
309 	Bind a Variant[] as the parameters of a prepared statement.
310 	
311 	You can use this method to bind a set of variables in Variant form to
312 	the parameters of a prepared statement.
313 	
314 	Parameter specializations (ie, for chunked transfer) can be added if required.
315 	If you wish to use chunked transfer (via `psn`), note that you must supply
316 	a dummy value for `val` that's typed `ubyte[]`. For example: `cast(ubyte[])[]`.
317 
318 	This method could be
319 	used to add records from a data entry form along the lines of
320 	------------
321 	auto stmt = conn.prepare("INSERT INTO `table42` VALUES(?, ?, ?)");
322 	DataRecord dr;    // Some data input facility
323 	ulong ra;
324 	do
325 	{
326 	    dr.get();
327 	    stmt.setArgs(dr("Name"), dr("City"), dr("Whatever"));
328 	    ulong rowsAffected = conn.exec(stmt);
329 	} while(!dr.done);
330 	------------
331 
332 	Type_Mappings: $(TYPE_MAPPINGS)
333 
334 	Params:
335 	args = External list of Variants to be used as parameters
336 	psnList = Any required specializations
337 	+/
338 	void setArgs(Variant[] args, ParameterSpecialization[] psnList=null)
339 	{
340 		enforce!MYX(args.length == _numParams, "Param count supplied does not match prepared statement");
341 		_inParams[] = args[];
342 		if (psnList !is null)
343 		{
344 			foreach (PSN psn; psnList)
345 				_psa[psn.pIndex] = psn;
346 		}
347 	}
348 
349 	/++
350 	Prepared statement parameter getter.
351 
352 	Type_Mappings: $(TYPE_MAPPINGS)
353 
354 	Params: index = The zero based index
355 	+/
356 	Variant getArg(size_t index)
357 	{
358 		enforce!MYX(index < _numParams, "Parameter index out of range.");
359 		return _inParams[index];
360 	}
361 
362 	/++
363 	Sets a prepared statement parameter to NULL.
364 	
365 	This is here mainly for legacy reasons. You can set a field to null
366 	simply by saying `prepared.setArg(index, null);`
367 
368 	Type_Mappings: $(TYPE_MAPPINGS)
369 
370 	Params: index = The zero based index
371 	+/
372 	void setNullArg(size_t index)
373 	{
374 		setArg(index, null);
375 	}
376 
377 	/// Gets the SQL command for this prepared statement.
378 	const(char)[] sql()
379 	{
380 		return _sql;
381 	}
382 
383 	@("setNullArg")
384 	debug(MYSQLN_TESTS)
385 	unittest
386 	{
387 		import mysql.connection;
388 		import mysql.test.common;
389 		mixin(scopedCn);
390 
391 		cn.exec("DROP TABLE IF EXISTS `setNullArg`");
392 		cn.exec("CREATE TABLE `setNullArg` (
393 			`val` INTEGER
394 		) ENGINE=InnoDB DEFAULT CHARSET=utf8");
395 
396 		immutable insertSQL = "INSERT INTO `setNullArg` VALUES (?)";
397 		immutable selectSQL = "SELECT * FROM `setNullArg`";
398 		auto preparedInsert = cn.prepare(insertSQL);
399 		assert(preparedInsert.sql == insertSQL);
400 		Row[] rs;
401 
402 		{
403 			Nullable!int nullableInt;
404 			nullableInt.nullify();
405 			preparedInsert.setArg(0, nullableInt);
406 			assert(preparedInsert.getArg(0).type == typeid(typeof(null)));
407 			nullableInt = 7;
408 			preparedInsert.setArg(0, nullableInt);
409 			assert(preparedInsert.getArg(0) == 7);
410 
411 			nullableInt.nullify();
412 			preparedInsert.setArgs(nullableInt);
413 			assert(preparedInsert.getArg(0).type == typeid(typeof(null)));
414 			nullableInt = 7;
415 			preparedInsert.setArgs(nullableInt);
416 			assert(preparedInsert.getArg(0) == 7);
417 		}
418 
419 		preparedInsert.setArg(0, 5);
420 		cn.exec(preparedInsert);
421 		rs = cn.query(selectSQL).array;
422 		assert(rs.length == 1);
423 		assert(rs[0][0] == 5);
424 
425 		preparedInsert.setArg(0, null);
426 		cn.exec(preparedInsert);
427 		rs = cn.query(selectSQL).array;
428 		assert(rs.length == 2);
429 		assert(rs[0][0] == 5);
430 		assert(rs[1].isNull(0));
431 		assert(rs[1][0].type == typeid(typeof(null)));
432 
433 		preparedInsert.setArg(0, Variant(null));
434 		cn.exec(preparedInsert);
435 		rs = cn.query(selectSQL).array;
436 		assert(rs.length == 3);
437 		assert(rs[0][0] == 5);
438 		assert(rs[1].isNull(0));
439 		assert(rs[2].isNull(0));
440 		assert(rs[1][0].type == typeid(typeof(null)));
441 		assert(rs[2][0].type == typeid(typeof(null)));
442 	}
443 
444 	/// Gets the number of arguments this prepared statement expects to be passed in.
445 	@property ushort numArgs() pure const nothrow
446 	{
447 		return _numParams;
448 	}
449 
450 	/// After a command that inserted a row into a table with an auto-increment
451 	/// ID column, this method allows you to retrieve the last insert ID generated
452 	/// from this prepared statement.
453 	@property ulong lastInsertID() pure const nothrow { return _lastInsertID; }
454 
455 	@("lastInsertID")
456 	debug(MYSQLN_TESTS)
457 	unittest
458 	{
459 		import mysql.connection;
460 		mixin(scopedCn);
461 		cn.exec("DROP TABLE IF EXISTS `testPreparedLastInsertID`");
462 		cn.exec("CREATE TABLE `testPreparedLastInsertID` (
463 			`a` INTEGER NOT NULL AUTO_INCREMENT,
464 			PRIMARY KEY (a)
465 		) ENGINE=InnoDB DEFAULT CHARSET=utf8");
466 		
467 		auto stmt = cn.prepare("INSERT INTO `testPreparedLastInsertID` VALUES()");
468 		cn.exec(stmt);
469 		assert(stmt.lastInsertID == 1);
470 		cn.exec(stmt);
471 		assert(stmt.lastInsertID == 2);
472 		cn.exec(stmt);
473 		assert(stmt.lastInsertID == 3);
474 	}
475 
476 	/// Gets the prepared header's field descriptions.
477 	@property FieldDescription[] preparedFieldDescriptions() pure { return _headers.fieldDescriptions; }
478 
479 	/// Gets the prepared header's param descriptions.
480 	@property ParamDescription[] preparedParamDescriptions() pure { return _headers.paramDescriptions; }
481 
482 	/// Get/set the column specializations.
483 	@property ColumnSpecialization[] columnSpecials() pure { return _columnSpecials; }
484 
485 	///ditto
486 	@property void columnSpecials(ColumnSpecialization[] csa) pure { _columnSpecials = csa; }
487 }
488 
489 /// Template constraint for `PreparedRegistrations`
490 private enum isPreparedRegistrationsPayload(Payload) =
491 	__traits(compiles, (){
492 			static assert(Payload.init.queuedForRelease == false);
493 			Payload p;
494 			p.queuedForRelease = true;
495 		});
496 
497 /++
498 Common functionality for recordkeeping of prepared statement registration
499 and queueing for unregister.
500 
501 Used by `Connection` and `MySQLPool`.
502 
503 Templated on payload type. The payload should be an aggregate that includes
504 the field: `bool queuedForRelease = false;`
505 
506 Allowing access to `directLookup` from other parts of mysql-native IS intentional.
507 `PreparedRegistrations` isn't intended as 100% encapsulation, it's mainly just
508 to factor out common functionality needed by both `Connection` and `MySQLPool`.
509 +/
510 package struct PreparedRegistrations(Payload)
511 	if(	isPreparedRegistrationsPayload!Payload)
512 {
513 	/++
514 	Lookup payload by sql string.
515 
516 	Allowing access to `directLookup` from other parts of mysql-native IS intentional.
517 	`PreparedRegistrations` isn't intended as 100% encapsulation, it's mainly just
518 	to factor out common functionality needed by both `Connection` and `MySQLPool`.
519 	+/
520 	Payload[const(char[])] directLookup;
521 	
522 	/// Returns null if not found
523 	Nullable!Payload opIndex(const(char[]) sql) pure nothrow
524 	{
525 		Nullable!Payload result;
526 		
527 		auto pInfo = sql in directLookup;
528 		if(pInfo)
529 			result = *pInfo;
530 		
531 		return result;
532 	}
533 
534 	/// Set `queuedForRelease` flag for a statement in `directLookup`.
535 	/// Does nothing if statement not in `directLookup`.
536 	private void setQueuedForRelease(const(char[]) sql, bool value)
537 	{
538 		if(auto pInfo = sql in directLookup)
539 		{
540 			pInfo.queuedForRelease = value;
541 			directLookup[sql] = *pInfo;
542 		}
543 	}
544 
545 	/// Queue a prepared statement for release.
546 	void queueForRelease(const(char[]) sql)
547 	{
548 		setQueuedForRelease(sql, true);
549 	}
550 
551 	/// Remove a statement from the queue to be released.
552 	void unqueueForRelease(const(char[]) sql)
553 	{
554 		setQueuedForRelease(sql, false);
555 	}
556 	
557 	/// Queues all prepared statements for release.
558 	void queueAllForRelease()
559 	{
560 		foreach(sql, info; directLookup)
561 			queueForRelease(sql);
562 	}
563 
564 	/// Eliminate all records of both registered AND queued-for-release statements.
565 	void clear()
566 	{
567 		static if(__traits(compiles, (){ int[int] aa; aa.clear(); }))
568 			directLookup.clear();
569 		else
570 			directLookup = null;
571 	}
572 
573 	/// If already registered, simply returns the cached Payload.
574 	Payload registerIfNeeded(const(char[]) sql, Payload delegate(const(char[])) doRegister)
575 	out(info)
576 	{
577 		// I'm confident this can't currently happen, but
578 		// let's make sure that doesn't change.
579 		assert(!info.queuedForRelease);
580 	}
581 	body
582 	{
583 		if(auto pInfo = sql in directLookup)
584 		{
585 			// The statement is registered. It may, or may not, be queued
586 			// for release. Either way, all we need to do is make sure it's
587 			// un-queued and then return.
588 			pInfo.queuedForRelease = false;
589 			return *pInfo;
590 		}
591 
592 		auto info = doRegister(sql);
593 		directLookup[sql] = info;
594 
595 		return info;
596 	}
597 }
598 
599 // Test PreparedRegistrations
600 debug(MYSQLN_TESTS)
601 {
602 	// Test template constraint
603 	struct TestPreparedRegistrationsBad1 { }
604 	struct TestPreparedRegistrationsBad2 { bool foo = false; }
605 	struct TestPreparedRegistrationsBad3 { int queuedForRelease = 1; }
606 	struct TestPreparedRegistrationsBad4 { bool queuedForRelease = true; }
607 	struct TestPreparedRegistrationsGood1 { bool queuedForRelease = false; }
608 	struct TestPreparedRegistrationsGood2 { bool queuedForRelease = false; const(char)[] id; }
609 	
610 	static assert(!isPreparedRegistrationsPayload!int);
611 	static assert(!isPreparedRegistrationsPayload!bool);
612 	static assert(!isPreparedRegistrationsPayload!TestPreparedRegistrationsBad1);
613 	static assert(!isPreparedRegistrationsPayload!TestPreparedRegistrationsBad2);
614 	static assert(!isPreparedRegistrationsPayload!TestPreparedRegistrationsBad3);
615 	static assert(!isPreparedRegistrationsPayload!TestPreparedRegistrationsBad4);
616 	//static assert(isPreparedRegistrationsPayload!TestPreparedRegistrationsGood1);
617 	//static assert(isPreparedRegistrationsPayload!TestPreparedRegistrationsGood2);
618 	PreparedRegistrations!TestPreparedRegistrationsGood1 testPreparedRegistrationsGood1;
619 	PreparedRegistrations!TestPreparedRegistrationsGood2 testPreparedRegistrationsGood2;
620 
621 	@("PreparedRegistrations")
622 	unittest
623 	{
624 		// Test init
625 		PreparedRegistrations!TestPreparedRegistrationsGood2 pr;
626 		assert(pr.directLookup.keys.length == 0);
627 
628 		void resetData(bool isQueued1, bool isQueued2, bool isQueued3)
629 		{
630 			pr.directLookup["1"] = TestPreparedRegistrationsGood2(isQueued1, "1");
631 			pr.directLookup["2"] = TestPreparedRegistrationsGood2(isQueued2, "2");
632 			pr.directLookup["3"] = TestPreparedRegistrationsGood2(isQueued3, "3");
633 			assert(pr.directLookup.keys.length == 3);
634 		}
635 
636 		// Test resetData (sanity check)
637 		resetData(false, true, false);
638 		assert(pr.directLookup["1"] == TestPreparedRegistrationsGood2(false, "1"));
639 		assert(pr.directLookup["2"] == TestPreparedRegistrationsGood2(true,  "2"));
640 		assert(pr.directLookup["3"] == TestPreparedRegistrationsGood2(false, "3"));
641 
642 		// Test opIndex
643 		resetData(false, true, false);
644 		pr.directLookup["1"] = TestPreparedRegistrationsGood2(false, "1");
645 		pr.directLookup["2"] = TestPreparedRegistrationsGood2(true,  "2");
646 		pr.directLookup["3"] = TestPreparedRegistrationsGood2(false, "3");
647 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
648 		assert(pr["2"] == TestPreparedRegistrationsGood2(true,  "2"));
649 		assert(pr["3"] == TestPreparedRegistrationsGood2(false, "3"));
650 		assert(pr["4"].isNull);
651 
652 		// Test queueForRelease
653 		resetData(false, true, false);
654 		pr.queueForRelease("2");
655 		assert(pr.directLookup.keys.length == 3);
656 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
657 		assert(pr["2"] == TestPreparedRegistrationsGood2(true,  "2"));
658 		assert(pr["3"] == TestPreparedRegistrationsGood2(false, "3"));
659 		
660 		pr.queueForRelease("3");
661 		assert(pr.directLookup.keys.length == 3);
662 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
663 		assert(pr["2"] == TestPreparedRegistrationsGood2(true,  "2"));
664 		assert(pr["3"] == TestPreparedRegistrationsGood2(true,  "3"));
665 
666 		pr.queueForRelease("4");
667 		assert(pr.directLookup.keys.length == 3);
668 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
669 		assert(pr["2"] == TestPreparedRegistrationsGood2(true,  "2"));
670 		assert(pr["3"] == TestPreparedRegistrationsGood2(true,  "3"));
671 
672 		// Test unqueueForRelease
673 		resetData(false, true, false);
674 		pr.unqueueForRelease("1");
675 		assert(pr.directLookup.keys.length == 3);
676 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
677 		assert(pr["2"] == TestPreparedRegistrationsGood2(true,  "2"));
678 		assert(pr["3"] == TestPreparedRegistrationsGood2(false, "3"));
679 
680 		pr.unqueueForRelease("2");
681 		assert(pr.directLookup.keys.length == 3);
682 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
683 		assert(pr["2"] == TestPreparedRegistrationsGood2(false, "2"));
684 		assert(pr["3"] == TestPreparedRegistrationsGood2(false, "3"));
685 
686 		pr.unqueueForRelease("4");
687 		assert(pr.directLookup.keys.length == 3);
688 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
689 		assert(pr["2"] == TestPreparedRegistrationsGood2(false, "2"));
690 		assert(pr["3"] == TestPreparedRegistrationsGood2(false, "3"));
691 
692 		// Test queueAllForRelease
693 		resetData(false, true, false);
694 		pr.queueAllForRelease();
695 		assert(pr["1"] == TestPreparedRegistrationsGood2(true,  "1"));
696 		assert(pr["2"] == TestPreparedRegistrationsGood2(true,  "2"));
697 		assert(pr["3"] == TestPreparedRegistrationsGood2(true,  "3"));
698 		assert(pr["4"].isNull);
699 
700 		// Test clear
701 		resetData(false, true, false);
702 		pr.clear();
703 		assert(pr.directLookup.keys.length == 0);
704 		
705 		// Test registerIfNeeded
706 		auto doRegister(const(char[]) sql) { return TestPreparedRegistrationsGood2(false, sql); }
707 		pr.registerIfNeeded("1", &doRegister);
708 		assert(pr.directLookup.keys.length == 1);
709 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
710 
711 		pr.registerIfNeeded("1", &doRegister);
712 		assert(pr.directLookup.keys.length == 1);
713 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
714 
715 		pr.registerIfNeeded("2", &doRegister);
716 		assert(pr.directLookup.keys.length == 2);
717 		assert(pr["1"] == TestPreparedRegistrationsGood2(false, "1"));
718 		assert(pr["2"] == TestPreparedRegistrationsGood2(false, "2"));
719 	}
720 }