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 		static Database statementDatabase; // Ensure the statement belongs to the current database
49 		if (statementDatabase !is this)
50 		{
51 			statement = db.prepare(sql).enforce("Statement compilation failed: " ~ sql);
52 			statementDatabase = this;
53 		}
54 		return statement;
55 	}
56 
57 	private SQLite.PreparedStatement[const(void)*] cache;
58 
59 	/// ditto
60 	SQLite.PreparedStatement stmt(string sql)
61 	{
62 		debug(DATABASE) stderr.writeln(sql);
63 		auto pstatement = sql.ptr in cache;
64 		if (pstatement)
65 			return *pstatement;
66 
67 		auto statement = db.prepare(sql);
68 		enforce(statement, "Statement compilation failed: " ~ sql);
69 		return cache[sql.ptr] = statement;
70 	}
71 
72 	private SQLite instance;
73 
74 	/// Return a handle to the database, creating it first if necessary.
75 	@property SQLite db()
76 	{
77 		if (instance)
78 			return instance;
79 
80 		instance = new SQLite(dbFileName);
81 		scope(failure) instance = null;
82 
83 		// Protect against locked database due to queries from command
84 		// line or cron
85 		instance.exec("PRAGMA busy_timeout = 100;");
86 
87 		if (schema !is null)
88 		{
89 			auto userVersion = stmt!"PRAGMA user_version".iterate().selectValue!int;
90 			if (userVersion != schema.length)
91 			{
92 				enforce(userVersion <= schema.length, "Database schema version newer than latest supported by this program!");
93 				while (userVersion < schema.length)
94 				{
95 					auto upgradeInstruction = schema[userVersion];
96 					instance.exec("BEGIN TRANSACTION;");
97 					instance.exec(upgradeInstruction);
98 					userVersion++;
99 					instance.exec("PRAGMA user_version = " ~ text(userVersion));
100 					instance.exec("COMMIT TRANSACTION;");
101 				}
102 			}
103 		}
104 
105 		return instance;
106 	}
107 }
108 
109 /// Return the first value of the given iterator.
110 /// Can be used to select the only value of an SQL query
111 /// (such as `"SELECT COUNT(*) FROM ..."`).
112 T selectValue(T, Iter)(Iter iter)
113 {
114 	foreach (T val; iter)
115 		return val;
116 	throw new Exception("No results for query");
117 }