From deacd0dfc195bca41af631114804d29937337cd8 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 17 Jan 2024 02:11:31 +0900
Subject: .

---
 services/app/src/Migrations/MigrationManager.php | 248 +++++++++++++++++++++++
 1 file changed, 248 insertions(+)
 create mode 100644 services/app/src/Migrations/MigrationManager.php

(limited to 'services/app/src/Migrations/MigrationManager.php')

diff --git a/services/app/src/Migrations/MigrationManager.php b/services/app/src/Migrations/MigrationManager.php
new file mode 100644
index 0000000..88073db
--- /dev/null
+++ b/services/app/src/Migrations/MigrationManager.php
@@ -0,0 +1,248 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Nsfisis\Albatross\Migrations;
+
+use Nsfisis\Albatross\Database\Connection;
+
+final class MigrationManager
+{
+    public function __construct(
+        private readonly Connection $conn,
+    ) {
+    }
+
+    public function execute(): void
+    {
+        $this->conn->transaction(function () {
+            $version = $this->fetchSchemaVersion();
+            while (method_exists($this, "execute$version")) {
+                $method = "execute$version";
+                // @phpstan-ignore-next-line
+                $this->$method();
+                $this->conn
+                    ->query()
+                    ->insert('migrations')
+                    ->values([])
+                    ->execute();
+                $version++;
+            }
+        });
+    }
+
+    private function fetchSchemaVersion(): int
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            CREATE TABLE IF NOT EXISTS migrations (
+                migration_id SERIAL PRIMARY KEY
+            );
+        EOSQL);
+        $result = $this->conn
+            ->query()
+            ->select('migrations')
+            ->fields(['COALESCE(MAX(migration_id), 0) + 1 AS schema_version'])
+            ->first()
+            ->execute();
+        assert(isset($result['schema_version']));
+        return (int) $result['schema_version'];
+    }
+
+    /**
+     * Create the initial schema.
+     */
+    private function execute1(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            CREATE TABLE IF NOT EXISTS quizzes (
+                quiz_id SERIAL PRIMARY KEY,
+                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+                title TEXT NOT NULL,
+                summary TEXT NOT NULL,
+                input_description TEXT NOT NULL,
+                output_description TEXT NOT NULL,
+                expected_result TEXT NOT NULL,
+                example_code TEXT NOT NULL
+            );
+
+            CREATE TABLE IF NOT EXISTS answers (
+                answer_id SERIAL PRIMARY KEY,
+                quiz_id INTEGER NOT NULL,
+                answer_number INTEGER NOT NULL,
+                submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+                author_name TEXT NOT NULL,
+                code TEXT NOT NULL,
+                code_size INTEGER NOT NULL,
+                execution_status INTEGER NOT NULL,
+                execution_stdout TEXT,
+                execution_stderr TEXT,
+                UNIQUE (quiz_id, answer_number),
+                FOREIGN KEY (quiz_id) REFERENCES quizzes (quiz_id)
+            );
+        EOSQL);
+    }
+
+    /**
+     * Add "slug" column to quizzes table.
+     */
+    private function execute2(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            ALTER TABLE quizzes ADD COLUMN slug VARCHAR(32);
+            UPDATE quizzes SET slug = title;
+            ALTER TABLE quizzes ALTER COLUMN slug SET NOT NULL;
+            ALTER TABLE quizzes ADD CONSTRAINT uniq_slug UNIQUE (slug);
+        EOSQL);
+    }
+
+    /**
+     * Add "birdie_code_size" column to quizzes table.
+     */
+    private function execute3(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            ALTER TABLE quizzes ADD COLUMN birdie_code_size INTEGER;
+        EOSQL);
+    }
+
+    /**
+     * Remove "input_description" and "summary" columns from quizzes table. Rename "output_description" to "description".
+     */
+    private function execute4(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            ALTER TABLE quizzes DROP COLUMN input_description;
+            ALTER TABLE quizzes DROP COLUMN summary;
+            ALTER TABLE quizzes RENAME COLUMN output_description TO description;
+        EOSQL);
+    }
+
+    /**
+     * Add "started_at", "answers_hidden_at" and "finished_at" columns to quizzes table.
+     */
+    private function execute5(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            ALTER TABLE quizzes ADD COLUMN started_at TIMESTAMP;
+            ALTER TABLE quizzes ADD COLUMN answers_hidden_at TIMESTAMP;
+            ALTER TABLE quizzes ADD COLUMN finished_at TIMESTAMP;
+        EOSQL);
+    }
+
+    /**
+     * Create users table.
+     */
+    private function execute6(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            CREATE TABLE IF NOT EXISTS users (
+                user_id SERIAL PRIMARY KEY,
+                username VARCHAR(64) NOT NULL,
+                password_hash VARCHAR(256) NOT NULL,
+                is_admin BOOLEAN NOT NULL DEFAULT FALSE,
+                UNIQUE (username)
+            );
+        EOSQL);
+    }
+
+    /**
+     * Migrate from author_name to author_id in answers table.
+     */
+    private function execute7(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            ALTER TABLE answers DROP COLUMN author_name;
+            TRUNCATE TABLE answers;
+            ALTER TABLE answers ADD COLUMN author_id INTEGER NOT NULL;
+            ALTER TABLE answers ADD CONSTRAINT fk_author_id FOREIGN KEY (author_id) REFERENCES users (user_id);
+        EOSQL);
+    }
+
+    /**
+     * Rename answers_hidden_at to ranking_hidden_at in quizzes table.
+     */
+    private function execute8(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            ALTER TABLE quizzes RENAME COLUMN answers_hidden_at TO ranking_hidden_at;
+        EOSQL);
+    }
+
+    /**
+     * Make "started_at", "ranking_hidden_at" and "finished_at" columns not null.
+     */
+    private function execute9(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            UPDATE quizzes SET started_at = CURRENT_TIMESTAMP;
+            UPDATE quizzes SET ranking_hidden_at = CURRENT_TIMESTAMP;
+            UPDATE quizzes SET finished_at = CURRENT_TIMESTAMP;
+            ALTER TABLE quizzes ALTER COLUMN started_at SET NOT NULL;
+            ALTER TABLE quizzes ALTER COLUMN ranking_hidden_at SET NOT NULL;
+            ALTER TABLE quizzes ALTER COLUMN finished_at SET NOT NULL;
+        EOSQL);
+    }
+
+    /**
+     * Remove "password_hash" column from "users" table.
+     */
+    private function execute10(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            ALTER TABLE users DROP COLUMN password_hash;
+        EOSQL);
+    }
+
+    /**
+     * Implement multi-testcases.
+     *
+     * - Create "testcases" table and "testcase_executions" table.
+     * - Remove "expected_result" column from "quizzes" table.
+     * - Remove "execution_stdout" and "execution_stderr" columns from "answers" table.
+     */
+    private function execute11(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            CREATE TABLE IF NOT EXISTS testcases (
+                testcase_id SERIAL PRIMARY KEY,
+                quiz_id INTEGER NOT NULL,
+                input TEXT NOT NULL,
+                expected_result TEXT NOT NULL,
+                FOREIGN KEY (quiz_id) REFERENCES quizzes (quiz_id)
+            );
+            CREATE TABLE IF NOT EXISTS testcase_executions (
+                testcase_execution_id SERIAL PRIMARY KEY,
+                testcase_id INTEGER NOT NULL,
+                answer_id INTEGER NOT NULL,
+                status INTEGER NOT NULL,
+                stdout TEXT,
+                stderr TEXT,
+                FOREIGN KEY (testcase_id) REFERENCES testcases (testcase_id),
+                FOREIGN KEY (answer_id) REFERENCES answers (answer_id)
+            );
+
+            INSERT INTO testcases (quiz_id, input, expected_result)
+                SELECT quiz_id, '', expected_result FROM quizzes;
+            ALTER TABLE quizzes DROP COLUMN expected_result;
+
+            INSERT INTO testcase_executions (testcase_id, answer_id, status, stdout, stderr)
+                SELECT testcases.testcase_id, answers.answer_id, answers.execution_status, answers.execution_stdout, answers.execution_stderr
+                FROM answers
+                INNER JOIN testcases ON testcases.quiz_id = answers.quiz_id;
+            ALTER TABLE answers DROP COLUMN execution_stdout;
+            ALTER TABLE answers DROP COLUMN execution_stderr;
+        EOSQL);
+    }
+
+    /**
+     * Migrate value of "execution_status" in "answers" table.
+     */
+    private function execute12(): void
+    {
+        $this->conn->query()->schema(<<<EOSQL
+            UPDATE answers SET execution_status = 1 WHERE execution_status = 0 OR execution_status = 1;
+            UPDATE answers SET execution_status = 2 WHERE execution_status BETWEEN 2 AND 5;
+            UPDATE answers SET execution_status = 3 WHERE execution_status = 6;
+        EOSQL);
+    }
+}
-- 
cgit v1.2.3-70-g09d2