Implement database schema Migrator

This commit implements the new database schema Migrator which keeps
track of the on-disk schema and the schema used by the codebase. It
tries to makes future database schema upgrades user-friendlier.

10 files changed, 369 insertions, 19 deletions

diff --git a/core/application.php b/core/application.php
index 21ab203..d6c9bd0 100644
--- a/core/application.php
+++ b/core/application.php
@@ -142,6 +142,11 @@ $Language = Application::getLanguage();
 $Database = Application::getDatabase();
 
 #===============================================================================
+# Include migration detection
+#===============================================================================
+require 'migrations.php';
+
+#===============================================================================
 # Check if "304 Not Modified" and ETag header should be sent
 #===============================================================================
 if(Application::get('CORE.SEND_304') === TRUE AND !defined('ADMINISTRATION')) {
diff --git a/core/migrations.php b/core/migrations.php
new file mode 100644
index 0000000..f2c9714
--- /dev/null
+++ b/core/migrations.php
@@ -0,0 +1,43 @@
+<?php
+#===============================================================================
+# Get Migrator singleton
+#===============================================================================
+$Migrator = Application::getMigrator();
+
+#===============================================================================
+# Check for outstanding database schema migrations
+#===============================================================================
+if($Migrator->isMigrationNeeded()) {
+	@session_start();
+
+	Application::set('TEMPLATE.NAME', Application::get('ADMIN.TEMPLATE'));
+	Application::set('TEMPLATE.LANG', Application::get('ADMIN.LANGUAGE'));
+	Application::getLanguage(TRUE); // Force recreation of Language object
+
+	if(HTTP::issetPOST(['token' => Application::getSecurityToken()], 'run')) {
+		if(!$migrated = $Migrator->runMigrations()) {
+			Application::exit('CONFUSED: No migrations were performed?!');
+		}
+	}
+
+	$Template = Template\Factory::build('migration');
+	$Template->set('MIGRATION', [
+		'LIST' => $Migrator->getMigrations(),
+		'SUCCESSFUL' => $migrated ?? [],
+		'SCHEMA_VERSION' => [
+			'DATABASE' => $Migrator->getVersionFromTable(),
+			'CODEBASE' => $Migrator::CURRENT_SCHEMA_VERSION
+		],
+	]);
+
+	Application::exit($Template);
+}
+
+#===============================================================================
+# Check for an unsupported downgrade attempt
+#===============================================================================
+else if($Migrator->isDowngradeAttempt()) {
+	throw new Exception('MIGRATOR: The schema version used by *your* database is
+		higher than the schema version defined in the codebase. It is officially
+		not supported to automatically downgrade the database schema version!');
+}
diff --git a/core/namespace/Application.php b/core/namespace/Application.php
index 3cecf3b..829f758 100644
--- a/core/namespace/Application.php
+++ b/core/namespace/Application.php
@@ -6,6 +6,7 @@ class Application {
 	#===============================================================================
 	private static $Database;
 	private static $Language;
+	private static $Migrator;
 	private static $repositories = [];
 
 	#===============================================================================
@@ -81,6 +82,19 @@ class Application {
 	}
 
 	#===============================================================================
+	# Return singleton Migrator instance
+	#===============================================================================
+	public static function getMigrator(): Migrator {
+		if(!self::$Migrator instanceof Migrator) {
+			$Migrator = new Migrator(self::getDatabase());
+			$Migrator->setMigrationsDir(ROOT.'core/db/migrations/');
+			self::$Migrator = $Migrator;
+		}
+
+		return self::$Migrator;
+	}
+
+	#===============================================================================
 	# Return singleton repository instance
 	#===============================================================================
 	public static function getRepository(string $namespace): Repository {
diff --git a/core/namespace/Migrator.php b/core/namespace/Migrator.php
new file mode 100644
index 0000000..3096862
--- /dev/null
+++ b/core/namespace/Migrator.php
@@ -0,0 +1,182 @@
+<?php
+class Migrator {
+	private $Database;
+	private $version;
+	private $directory;
+	private $migrations = [];
+
+	const CURRENT_SCHEMA_VERSION = 4;
+
+	#===============================================================================
+	# Fetch on-disk schema version from migration table
+	#===============================================================================
+	public function __construct(Database $Database) {
+		$this->Database = $Database;
+
+		try {
+			$Statement = $Database->query('SELECT schema_version FROM migration');
+
+			if(!$this->version = $Statement->fetchColumn()) {
+				throw new Exception('The migration table does exist, but there is
+					no row containing the currently used on-disk schema version!');
+			}
+		} catch(PDOException $Exception) {
+			if($Exception->getCode() === '42S02') /* Table not found */ {
+				$this->version = $this->determineFallbackSchemaVersion();
+				$this->createMigrationTable($this->version);
+			} else {
+				throw $Exception;
+			}
+		}
+	}
+
+	#===============================================================================
+	# Specify the directory which contains the migration files
+	#===============================================================================
+	public function setMigrationsDir(string $directory): void {
+		if(!is_readable($this->directory = rtrim($directory, '/'))) {
+			throw new Exception('Migrator cannot read migration directory.');
+		};
+	}
+
+	#===============================================================================
+	# Check if new migrations needs to be applied
+	#===============================================================================
+	public function isMigrationNeeded(): bool {
+		$databaseSchema = $this->version;
+		$codebaseSchema = self::CURRENT_SCHEMA_VERSION;
+		return $databaseSchema < $codebaseSchema;
+	}
+
+	#===============================================================================
+	# Check if this is an unsupported downgrade attempt
+	#===============================================================================
+	public function isDowngradeAttempt(): bool {
+		$databaseSchema = $this->version;
+		$codebaseSchema = self::CURRENT_SCHEMA_VERSION;
+		return $databaseSchema > $codebaseSchema;
+	}
+
+	#===============================================================================
+	# Add a migration to the queue
+	#===============================================================================
+	private function enqueue(int $sequence, string $migration): void {
+		$this->migrations[$sequence] = $migration;
+	}
+
+	#===============================================================================
+	# Remove a migration from the queue
+	#===============================================================================
+	private function dequeue(int $sequence): void {
+		unset($this->migrations[$sequence]);
+	}
+
+	#===============================================================================
+	# Get the currently used on-disk schema version
+	#===============================================================================
+	public function getVersionFromTable(): int {
+		return $this->version;
+	}
+
+	#===============================================================================
+	# Get an array with all migration commands
+	#===============================================================================
+	public function getMigrations(): array {
+		$databaseSchema = $this->version;
+		$codebaseSchema = self::CURRENT_SCHEMA_VERSION;
+
+		if(!$this->isMigrationNeeded()) {
+			return [];
+		}
+
+		foreach(range($databaseSchema+1, $codebaseSchema) as $number) {
+			$file = sprintf("%s/%d.sql", $this->directory, $number);
+
+			if(!is_readable($file)) {
+				throw new Exception("Migrator cannot read migration file: »{$file}«");
+			}
+			$this->enqueue($number, file_get_contents($file));
+		}
+
+		return $this->migrations;
+	}
+
+	#===============================================================================
+	# Run migrations sequentially
+	#===============================================================================
+	public function runMigrations(): array {
+		foreach($this->getMigrations() as $sequence => $migration) {
+			try {
+				if($this->Database->query($migration)) {
+					$this->dequeue($sequence);
+					$this->updateMigrationTable($sequence);
+					$migrated[] = $sequence;
+				}
+			} catch(PDOException $Exception) {
+				$error = 'Migration from %d to %d failed with PDO error:<br>';
+				$error .= sprintf($error, $sequence-1, $sequence);
+				$error .= sprintf('<code>%s</code>', $Exception->getMessage());
+
+				if(!empty($migrated ?? [])) {
+					$error .= '<br>The following migrations were successful: ';
+					$error .= '<code>'.implode(', ', $migrated).'</code>';
+				}
+
+				throw new Exception($error);
+			}
+		}
+
+		return $migrated ?? [];
+	}
+
+	#===============================================================================
+	# Update the migration table with the specified schema version
+	#===============================================================================
+	private function updateMigrationTable(int $version): int {
+		$query = 'UPDATE migration SET schema_version = ?';
+		$Statement = $this->Database->prepare($query);
+
+		if($Statement->execute([$version])) {
+			return $this->version = $version;
+		}
+	}
+
+	#===============================================================================
+	# Create the migration table with the specified schema version
+	#===============================================================================
+	private function createMigrationTable(int $version): bool {
+		$create = 'CREATE TABLE migration (schema_version smallint(4) NOT NULL)
+			ENGINE=InnoDB DEFAULT CHARSET=utf8mb4';
+		$insert = 'INSERT INTO migration (schema_version) VALUES (?)';
+
+		$this->Database->query($create);
+		$Statement = $this->Database->prepare($insert);
+
+		return $Statement->execute([$version]);
+	}
+
+	#===============================================================================
+	# Determine on-disk schema version if migration table does not exist
+	#===============================================================================
+	private function determineFallbackSchemaVersion(): int {
+		# If the migration table does not yet exist, the user may have upgraded from
+		# an older release of the application and sits somewhere between 0 and 4. So
+		# we run some checks against the tables to determine the schema version.
+		$test[4] = 'SHOW COLUMNS FROM post WHERE Field = "argv" AND Type = "varchar(250)"';
+		$test[3] = 'SHOW INDEX FROM post WHERE Key_name = "search"';
+		$test[2] = 'SHOW INDEX FROM post WHERE Key_name = "time_insert"';
+		$test[1] = 'SHOW COLUMNS FROM post WHERE Field = "argv"';
+
+		foreach($test as $version => $query) {
+			try {
+				$Statement = $this->Database->query($query);
+
+				if ($Statement && $Statement->fetch()) {
+					return $version;
+				}
+			} catch(PDOException $Exception) {}
+		}
+
+		return 0;
+	}
+}
diff --git a/theme/admin/html/main.php b/theme/admin/html/main.php
index a7772ed..db637da 100644
--- a/theme/admin/html/main.php
+++ b/theme/admin/html/main.php
@@ -10,7 +10,7 @@
 </head>
 <body>
 	<header id="main-header">
-		<div class="header-line">
+		<div class="header-line background">
 			<div class="header-content">
 				<a href="<?=Application::getURL()?>">
 					<img id="header-logo" src="<?=Application::getTemplateURL('rsrc/icon-public-domain.svg')?>" alt="Administration" />
diff --git a/theme/admin/html/migration.php b/theme/admin/html/migration.php
new file mode 100644
index 0000000..14405c4
--- /dev/null
+++ b/theme/admin/html/migration.php
@@ -0,0 +1,59 @@
+<!DOCTYPE html>
+<html lang="<?=$BLOGMETA['LANG']?>">
+<head>
+	<meta charset="UTF-8" />
+	<meta name="referrer" content="origin-when-crossorigin" />
+	<meta name="viewport" content="width=device-width, initial-scale=1" />
+	<link rel="stylesheet" href="<?=Application::getTemplateURL('rsrc/css/main.css')?>" />
+	<title><?=$Language->text('maintenance_mode')?></title>
+</head>
+<body>
+<header id="main-header">
+	<div class="header-line background">
+		<div class="header-content">
+			<img id="header-logo" src="<?=Application::getTemplateURL('rsrc/icon-public-domain.svg')?>" alt="Administration" />
+			<div id="header-text"><?=escapeHTML($BLOGMETA['NAME'])?></div>
+			<div id="header-desc"><?=$Language->text('maintenance_mode')?></div>
+		</div>
+	</div>
+</header>
+<?php
+$migrations_list = $MIGRATION['LIST'];
+$database_schema = $MIGRATION['SCHEMA_VERSION']['DATABASE'];
+$codebase_schema = $MIGRATION['SCHEMA_VERSION']['CODEBASE'];
+?>
+<main id="main-content">
+	<h1><?=$Language->text('maintenance_mode')?></h1>
+	<?php if($migrated = $MIGRATION['SUCCESSFUL']): ?>
+		<p><?=$Language->text('migration_successful')?></p>
+		<ul>
+		<?php foreach($migrated as $migration): ?>
+			<li>Migration <code><?=$migration?></code></li>
+		<?php endforeach ?>
+		</ul>
+	<?php else: ?>
+		<p><?=$Language->text("migration_upgrade", [$database_schema, $codebase_schema])?></p>
+		<form action="" method="post">
+			<ul class="no-visual-list">
+			<?php foreach($migrations_list as $migration => $commands): ?>
+				<li>
+					<h2><strong>Migration <code><?=$migration?></code></strong></h2>
+					<p><?=$Language->text('migration_notice', [$migration-1, $migration])?></p>
+					<pre><?=$commands?></pre>
+				</li>
+			<?php endforeach ?>
+			</ul>
+			<input type="hidden" name="token" value="<?=Application::getSecurityToken()?>" ?>
+			<input type="submit" name="run" value="<?=$Language->text('migration_submit')?>" id="delete-button" />
+		</form>
+	<?php endif ?>
+</main>
+<footer id="main-footer">
+	<ul>
+		<li><i class="fa fa-github-square"></i><a href="https://github.com/Nerdmind/Blog/releases" target="_blank">Releases</a></li>
+		<li><i class="fa fa-book"></i><a href="https://github.com/Nerdmind/Blog/wiki" target="_blank">Documentation</a></li>
+		<li><i class="fa fa-bug"></i><a href="https://github.com/Nerdmind/Blog/issues">Bugreport</a></li>
+	</ul>
+</footer>
+</body>
+</html>
diff --git a/theme/admin/lang/de.php b/theme/admin/lang/de.php
index 0a848a6..3c7e834 100644
--- a/theme/admin/lang/de.php
+++ b/theme/admin/lang/de.php
@@ -143,3 +143,13 @@ $LANGUAGE['markdown_code'] = 'Codeblock';
 $LANGUAGE['markdown_quote'] = 'Zitat';
 $LANGUAGE['markdown_list_ul'] = 'Liste [ungeordnet]';
 $LANGUAGE['markdown_list_ol'] = 'Liste [geordnet]';
+
+#===============================================================================
+# Migrations
+#===============================================================================
+$LANGUAGE['maintenance_mode'] = 'Wartungsmodus';
+$LANGUAGE['migration_upgrade'] = 'Eine Migration des Datenbankschemas ist erforderlich!<br>Die Datenbank verwendet die
+	Schema-Version <code>%d</code>, aber die Applikation benutzt die höhere Schema-Version <code>%d</code>.';
+$LANGUAGE['migration_notice'] = 'Die folgenden Kommandos migrieren das Datenbankschema von <code>%d</code> nach <code>%d</code>.';
+$LANGUAGE['migration_successful'] = 'Die folgenden Migrationen waren erfolgreich:';
+$LANGUAGE['migration_submit'] = 'Migrationen ausführen';
diff --git a/theme/admin/lang/en.php b/theme/admin/lang/en.php
index 174a1d7..20482d4 100644
--- a/theme/admin/lang/en.php
+++ b/theme/admin/lang/en.php
@@ -143,3 +143,13 @@ $LANGUAGE['markdown_code'] = 'Code block';
 $LANGUAGE['markdown_quote'] = 'Quote';
 $LANGUAGE['markdown_list_ul'] = 'List [unordered]';
 $LANGUAGE['markdown_list_ol'] = 'List [ordered]';
+
+#===============================================================================
+# Migrations
+#===============================================================================
+$LANGUAGE['maintenance_mode'] = 'Maintenance mode';
+$LANGUAGE['migration_upgrade'] = 'A database schema migration is required!<br>The on-disk schema version is
+	<code>%d</code> but the application uses the higher schema version <code>%d</code>.';
+$LANGUAGE['migration_notice'] = 'The following commands will migrate the database schema from <code>%d</code> to <code>%d</code>.';
+$LANGUAGE['migration_successful'] = 'The following migrations were successful:';
+$LANGUAGE['migration_submit'] = 'Run migrations';
diff --git a/theme/admin/rsrc/css/main.css b/theme/admin/rsrc/css/main.css
index cfc6db9..7d93d94 100644
--- a/theme/admin/rsrc/css/main.css
+++ b/theme/admin/rsrc/css/main.css
@@ -140,18 +140,18 @@ body {
 .header-line {
   padding: 0.5rem 0.75rem;
   overflow: hidden;
-}
-.header-line:first-child {
-  background: #5E819F;
-  position: sticky;
-  top: 0;
-}
-.header-line:last-child {
   background: #EEE;
+  padding: 0.25rem 0.75rem;
+}
+.header-line + .header-line {
   border: 0.05rem solid #AAA;
   border-left: none;
   border-right: none;
-  padding: 0.25rem 0.75rem;
+}
+.header-line.background {
+  background: #5E819F;
+  position: sticky;
+  top: 0;
 }
 
 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
@@ -308,11 +308,18 @@ pre {
   overflow: auto;
   -moz-tab-size: 4;
   tab-size: 4;
+  background: #EEE;
+  padding: 0.5rem;
+  border: 0.05rem solid #AAA;
+  font-style: italic;
 }
 
 code, pre {
   font-family: "monospace";
-  color: #B03060;
+}
+
+code {
+  color: #008B45;
 }
 
 p {
@@ -344,6 +351,12 @@ img {
   display: none;
 }
 
+.no-visual-list {
+  margin: 0;
+  padding: 0;
+  list-style: none;
+}
+
 #database-result {
   color: inherit;
 }
diff --git a/theme/admin/rsrc/css/main.scss b/theme/admin/rsrc/css/main.scss
index d1f43c9..cc9bbb1 100644
--- a/theme/admin/rsrc/css/main.scss
+++ b/theme/admin/rsrc/css/main.scss
@@ -145,19 +145,20 @@ body {
 .header-line {
 	padding: 0.5rem 0.75rem;
 	overflow: hidden;
+	background: #EEE;
 
-	&:first-child {
-		background: #5E819F;
-		position: sticky;
-		top: 0;
-	}
+	padding: 0.25rem 0.75rem;
 
-	&:last-child {
-		background: #EEE;
+	+.header-line {
 		border: 0.05rem solid #AAA;
 		border-left: none;
 		border-right: none;
-		padding: 0.25rem 0.75rem;
+	}
+
+	&.background {
+		background: #5E819F;
+		position: sticky;
+		top: 0;
 	}
 }
 
@@ -341,11 +342,18 @@ pre {
 	overflow: auto;
 	-moz-tab-size: 4;
 	tab-size: 4;
+	background: #EEE;
+	padding: 0.5rem;
+	border: 0.05rem solid #AAA;
+	font-style: italic;
 }
 
 code, pre {
 	font-family: "monospace";
-	color: #B03060;
+}
+
+code {
+	color: #008B45;
 }
 
 p {
@@ -377,6 +385,12 @@ img {
 	display: none;
 }
 
+.no-visual-list {
+	margin: 0;
+	padding: 0;
+	list-style: none;
+}
+
 #database-result {
 	color: inherit;
 }