Don't use WAL mode & extend schema with metadata

gwicke · gwicke · commit 57df47907f99 · 2015-04-07T16:13:13.000-07:00
The WAL mode is persistent and only works with sqlite &gt;= 3.7, which isn't
great for our distribution use case. It does help performance a bit with
synchronous=NORMAL by only requiring an fsync on checkpoint (every 1000 pages
or so), but we might as well set synchronous=OFF for dumps.

This patch also adds revision metadata in the schema, but does not actually
fill this yet. We'll need to fetch this information separately, or extract
(some of) it from the HTML head. The former will work for other formats like
wikitext or JSON as well, so should be preferable.
diff --git a/sqlitestore.js b/sqlitestore.js
@@ -7,13 +7,23 @@ var pragmas = [
     'PRAGMA main.page_size = 4096',
     'PRAGMA main.cache_size=10000',
     'PRAGMA main.locking_mode=EXCLUSIVE',
-    'PRAGMA main.synchronous=NORMAL',
-    'PRAGMA main.journal_mode=WAL',
-    'PRAGMA main.cache_size=5000'
+    'PRAGMA main.synchronous=OFF', // more dangerous, but fast
+    //'PRAGMA main.synchronous=NORMAL',
+    //'PRAGMA main.journal_mode=WAL', // WAL is annoying for distributed files
 ];
 var createTableQuery = 'CREATE TABLE IF NOT EXISTS data('
-        + 'title TEXT, revision INTEGER, body BLOB, namespace INTEGER'
-        + ', PRIMARY KEY(title ASC, revision DESC)'
+        + 'title TEXT,'
+        + 'revision INTEGER,'
+        + 'body BLOB,'
+        + 'bigendian_v1_uuid text,' // etag header, reordered
+        // Metadata that we'll have to extract from the revision or HTML head.
+        + 'page_id INTEGER,' // missing from revision metadata
+        + 'namespace INTEGER,'
+        + 'timestamp TEXT,' // missing from revision metadata
+        + 'comment TEXT,'
+        + 'user_name TEXT,'
+        + 'user_id INTEGER,'
+        + 'PRIMARY KEY(title ASC, revision DESC)'
         + ')';
 var checkQuery = 'select revision from data where title = ? and revision = ? limit 1';
 var purgeTitleQuery = 'delete from data where title = ?';
