From fe6e8126293619b90bf3378b0466ea45a5e555d2 Mon Sep 17 00:00:00 2001
From: Aaron Schulz <aschulz@wikimedia.org>
Date: Fri, 8 Mar 2019 17:21:18 -0800
Subject: [PATCH] installer: make sqlite installer move the job queue to
 another DB

Also update some of the other separate DBs/files to use IMMEDIATE

Bug: T93097
Change-Id: Ib250b74cb7ff743f155512d7410cb229441cc70a
---
 includes/installer/SqliteInstaller.php | 52 +++++++++++++++++++++++++-
 1 file changed, 51 insertions(+), 1 deletion(-)

diff --git a/includes/installer/SqliteInstaller.php b/includes/installer/SqliteInstaller.php
index a8abba9c08..9de1fea23d 100644
--- a/includes/installer/SqliteInstaller.php
+++ b/includes/installer/SqliteInstaller.php
@@ -231,6 +231,7 @@ class SqliteInstaller extends DatabaseInstaller {
 		$status->merge( $this->makeStubDBFile( $dir, $db ) );
 		$status->merge( $this->makeStubDBFile( $dir, "wikicache" ) );
 		$status->merge( $this->makeStubDBFile( $dir, "{$db}_l10n_cache" ) );
+		$status->merge( $this->makeStubDBFile( $dir, "{$db}_jobqueue" ) );
 		if ( !$status->isOK() ) {
 			return $status;
 		}
@@ -283,6 +284,39 @@ EOT;
 			return Status::newFatal( 'config-sqlite-connection-error', $e->getMessage() );
 		}
 
+		# Create the job queue DB
+		try {
+			$conn = Database::factory(
+				'sqlite', [ 'dbname' => "{$db}_jobqueue", 'dbDirectory' => $dir ] );
+			# @todo: don't duplicate job definition, though it's very static
+			$sql =
+<<<EOT
+	CREATE TABLE job (
+		job_id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
+		job_cmd BLOB NOT NULL default '',
+		job_namespace INTEGER NOT NULL,
+		job_title TEXT  NOT NULL,
+		job_timestamp BLOB NULL default NULL,
+		job_params BLOB NOT NULL,
+		job_random integer  NOT NULL default 0,
+		job_attempts integer  NOT NULL default 0,
+		job_token BLOB NOT NULL default '',
+		job_token_timestamp BLOB NULL default NULL,
+		job_sha1 BLOB NOT NULL default ''
+	);
+	CREATE INDEX job_sha1 ON job (job_sha1);
+	CREATE INDEX job_cmd_token ON job (job_cmd,job_token,job_random);
+	CREATE INDEX job_cmd_token_id ON job (job_cmd,job_token,job_id);
+	CREATE INDEX job_cmd ON job (job_cmd, job_namespace, job_title, job_params);
+	CREATE INDEX job_timestamp ON job (job_timestamp);
+EOT;
+			$conn->query( $sql );
+			$conn->query( "PRAGMA journal_mode=WAL" ); // this is permanent
+			$conn->close();
+		} catch ( DBConnectionError $e ) {
+			return Status::newFatal( 'config-sqlite-connection-error', $e->getMessage() );
+		}
+
 		# Open the main DB
 		return $this->getConnection();
 	}
@@ -340,7 +374,9 @@ EOT;
 	 */
 	public function getLocalSettings() {
 		$dir = LocalSettingsGenerator::escapePhpString( $this->getVar( 'wgSQLiteDataDir' ) );
-
+		// These tables have frequent writes and are thus split off from the main one.
+		// Since the code using these tables only uses transactions for writes then set
+		// them to using BEGIN IMMEDIATE. This avoids frequent lock errors on first write.
 		return "# SQLite-specific settings
 \$wgSQLiteDataDir = \"{$dir}\";
 \$wgObjectCaches[CACHE_DB] = [
@@ -351,6 +387,7 @@ EOT;
 		'dbname' => 'wikicache',
 		'tablePrefix' => '',
 		'dbDirectory' => \$wgSQLiteDataDir,
+		'trxMode' => 'IMMEDIATE',
 		'flags' => 0
 	]
 ];
@@ -359,7 +396,20 @@ EOT;
 	'dbname' => \"{\$wgDBname}_l10n_cache\",
 	'tablePrefix' => '',
 	'dbDirectory' => \$wgSQLiteDataDir,
+	'trxMode' => 'IMMEDIATE',
 	'flags' => 0
+];
+\$wgJobTypeConf['default'] = [
+	'class' => 'JobQueueDB',
+	'claimTTL' => 3600,
+	'server' => [
+		'type' => 'sqlite',
+		'dbname' => \"{\$wgDBname}_jobqueue\",
+		'tablePrefix' => '',
+		'dbDirectory' => \$wgSQLiteDataDir,
+		'trxMode' => 'IMMEDIATE',
+		'flags' => 0
+	]
 ];";
 	}
 }
-- 
2.20.1