Support of journal_mode=WAL and synchronous=NORMAL in SQLite

[Jun Omae]

Currently, repository resync command is very slow with mirror of ​http://svn.edgewall.org/repos/trac on SQLite. Using ​Write-Ahead Logging feature would be faster. The feature is available since SQLite 3.7.0.

repository resync with mirror of ​http://svn.edgewall.org/repos/trac on SQLite:

journal_mode	synchronous	Elapsed
DELETE	FULL	37m44.746s	default
DELETE	NORMAL	30m02.647s
WAL	FULL	10m09.947s
WAL	NORMAL	2m39.104s

functional-test with SQLite:

journal_mode	synchronous	Elapsed
DELETE	FULL	261.468s	default
DELETE	NORMAL	235.012s
WAL	FULL	205.513s
WAL	NORMAL	177.595s

trac/db/sqlite_backend.py

@@ -207 +207 @@
         else:
             cnx = self.get_connection(path, log, params)
         cursor = cnx.cursor()
+
+        journal_mode = params.get('journal_mode', '').upper()
+        if journal_mode == 'WAL' and sqlite_version < (3, 7, 0):
+            journal_mode = None
+        if journal_mode in ('DELETE', 'TRUNCATE', 'PERSIST', 'MEMORY', 'WAL',
+                            'OFF'):
+            cursor.execute('PRAGMA journal_mode = %s' % journal_mode)
+
         if schema is None:
             from trac.db_default import schema
         for table in schema:
@@ -290 +298 @@
                 cnx.load_extension(ext)
             cnx.enable_load_extension(False)
 
+        synchronous = params.get('synchronous', '').upper()
+        if synchronous == 'NORMAL' and sqlite_version < (2, 8, 0):
+            synchronous = None
+        if synchronous in ('OFF', 'NORMAL'):
+            cursor = cnx.cursor()
+            cursor.execute('PRAGMA synchronous = %s' % synchronous)
+
         ConnectionWrapper.__init__(self, cnx, log)
 
     def cursor(self):