ae.sys.database source code

1 /**
2  * Higher-level wrapper around ae.sys.sqlite3.
3  *
4  * License:
5  *   This Source Code Form is subject to the terms of
6  *   the Mozilla Public License, v. 2.0. If a copy of
7  *   the MPL was not distributed with this file, You
8  *   can obtain one at http://mozilla.org/MPL/2.0/.
9  *
10  * Authors:
11  *   Vladimir Panteleev <ae@cy.md>
12  */
13 
14 module ae.sys.database;
15 
16 import std.conv;
17 import std.exception;
18 
19 import ae.sys.sqlite3;
20 public import ae.sys.sqlite3 : SQLiteException;
21 debug(DATABASE) import std.stdio : stderr;
22 
23 /// A higher-level wrapper around `SQLite`,
24 /// providing automatic initialization,
25 /// cached prepared statements,
26 /// and schema migrations.
27 struct Database
28 {
29 	/// Database file name.
30 	string dbFileName;
31 
32 	/// Schema definition, starting with the initial version, and followed by migration instructions.
33 	/// SQLite `user_version` is used to keep track of the current version.
34 	/// Successive versions of applications should only extend this array by adding new items at the end.
35 	string[] schema;
36 
37 	this(string dbFileName, string[] schema = null)
38 	{
39 		this.dbFileName = dbFileName;
40 		this.schema = schema;
41 	} ///
42 
43 	/// Return an `SQLite.PreparedStatement`, caching it.
44 	SQLite.PreparedStatement stmt(string sql)()
45 	{
46 		debug(DATABASE) stderr.writeln(sql);
47 		static SQLite.PreparedStatement statement = null;
48 		if (!statement)
49 			statement = db.prepare(sql).enforce("Statement compilation failed: " ~ sql);
50 		return statement;
51 	}
52 
53 	/// ditto
54 	SQLite.PreparedStatement stmt(string sql)
55 	{
56 		debug(DATABASE) stderr.writeln(sql);
57 		static SQLite.PreparedStatement[const(void)*] cache;
58 		auto pstatement = sql.ptr in cache;
59 		if (pstatement)
60 			return *pstatement;
61 
62 		auto statement = db.prepare(sql);
63 		enforce(statement, "Statement compilation failed: " ~ sql);
64 		return cache[sql.ptr] = statement;
65 	}
66 
67 	private SQLite instance;
68 
69 	/// Return a handle to the database, creating it first if necessary.
70 	@property SQLite db()
71 	{
72 		if (instance)
73 			return instance;
74 
75 		instance = new SQLite(dbFileName);
76 		scope(failure) instance = null;
77 
78 		// Protect against locked database due to queries from command
79 		// line or cron
80 		instance.exec("PRAGMA busy_timeout = 100;");
81 
82 		if (schema !is null)
83 		{
84 			auto userVersion = stmt!"PRAGMA user_version".iterate().selectValue!int;
85 			if (userVersion != schema.length)
86 			{
87 				enforce(userVersion <= schema.length, "Database schema version newer than latest supported by this program!");
88 				while (userVersion < schema.length)
89 				{
90 					auto upgradeInstruction = schema[userVersion];
91 					instance.exec("BEGIN TRANSACTION;");
92 					instance.exec(upgradeInstruction);
93 					userVersion++;
94 					instance.exec("PRAGMA user_version = " ~ text(userVersion));
95 					instance.exec("COMMIT TRANSACTION;");
96 				}
97 			}
98 		}
99 
100 		return instance;
101 	}
102 }
103 
104 /// Return the first value of the given iterator.
105 /// Can be used to select the only value of an SQL query
106 /// (such as `"SELECT COUNT(*) FROM ..."`).
107 T selectValue(T, Iter)(Iter iter)
108 {
109 	foreach (T val; iter)
110 		return val;
111 	throw new Exception("No results for query");
112 }