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 import std.typecons;
19 
20 import ae.sys.sqlite3;
21 public import ae.sys.sqlite3 : SQLiteException;
22 debug(DATABASE) import std.stdio : stderr;
23 
24 /// A higher-level wrapper around `SQLite`,
25 /// providing automatic initialization,
26 /// cached prepared statements,
27 /// and schema migrations.
28 struct Database
29 {
30 	/// Database file name.
31 	string dbFileName;
32 
33 	/// Schema definition, starting with the initial version, and followed by migration instructions.
34 	/// SQLite `user_version` is used to keep track of the current version.
35 	/// Successive versions of applications should only extend this array by adding new items at the end.
36 	string[] schema;
37 
38 	this(string dbFileName, string[] schema = null)
39 	{
40 		this.dbFileName = dbFileName;
41 		this.schema = schema;
42 	} ///
43 
44 	/// Return an `SQLite.PreparedStatement`, caching it.
45 	SQLite.PreparedStatement stmt(string sql)()
46 	{
47 		debug(DATABASE) stderr.writeln(sql);
48 		static SQLite.PreparedStatement statement = null;
49 		static Database* statementDatabase; // Ensure the statement belongs to the current database
50 		if (statementDatabase !is &this)
51 		{
52 			statement = db.prepare(sql).enforce("Statement compilation failed: " ~ sql);
53 			statementDatabase = &this;
54 		}
55 		return statement;
56 	}
57 
58 	private SQLite.PreparedStatement[const(void)*] cache;
59 
60 	/// ditto
61 	SQLite.PreparedStatement stmt(string sql)
62 	{
63 		debug(DATABASE) stderr.writeln(sql);
64 		auto pstatement = sql.ptr in cache;
65 		if (pstatement)
66 			return *pstatement;
67 
68 		auto statement = db.prepare(sql);
69 		enforce(statement, "Statement compilation failed: " ~ sql);
70 		return cache[sql.ptr] = statement;
71 	}
72 
73 	private struct SQLiteContainer
74 	{
75 		typeof(scoped!SQLite(null)) ptr;
76 	}
77 	private RefCounted!(SQLiteContainer, RefCountedAutoInitialize.no) instance;
78 
79 	/// Return a handle to the database, creating it first if necessary.
80 	@property SQLite db()
81 	{
82 		if (instance !is typeof(instance).init)
83 			return instance.ptr;
84 
85 		instance = refCounted(SQLiteContainer(scoped!SQLite(dbFileName)));
86 		scope(failure) instance = typeof(instance).init;
87 
88 		auto db = &instance.ptr;
89 
90 		// Protect against locked database due to queries from command
91 		// line or cron
92 		db.exec("PRAGMA busy_timeout = 100;");
93 
94 		if (schema !is null)
95 		{
96 			auto userVersion = stmt!"PRAGMA user_version".iterate().selectValue!int;
97 			if (userVersion != schema.length)
98 			{
99 				enforce(userVersion <= schema.length, "Database schema version newer than latest supported by this program!");
100 				while (userVersion < schema.length)
101 				{
102 					auto upgradeInstruction = schema[userVersion];
103 					db.exec("BEGIN TRANSACTION;");
104 					db.exec(upgradeInstruction);
105 					userVersion++;
106 					db.exec("PRAGMA user_version = " ~ text(userVersion));
107 					db.exec("COMMIT TRANSACTION;");
108 				}
109 			}
110 		}
111 
112 		return *db;
113 	}
114 }
115 
116 /// Return the first value of the given iterator.
117 /// Can be used to select the only value of an SQL query
118 /// (such as `"SELECT COUNT(*) FROM ..."`).
119 T selectValue(T, Iter)(Iter iter)
120 {
121 	foreach (T val; iter)
122 		return val;
123 	throw new Exception("No results for query");
124 }