From d69d7e82b8bbb567668c935ace848c7dcf750b08 Mon Sep 17 00:00:00 2001
From: Thomas Lange <code@nerdmind.de>
Date: Thu, 24 Jun 2021 21:09:24 +0200
Subject: 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.
---
core/namespace/Migrator.php | 182 ++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 182 insertions(+)
create mode 100644 core/namespace/Migrator.php
(limited to 'core/namespace/Migrator.php')
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;
+ }
+}
--
cgit v1.2.3