hanyuu/database/2024_09_03_201657_scope_tables.php

<?php
use Index\Db\DbConnection;
use Index\Db\Migration\DbMigration;

final class ScopeTables_20240903_201657 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $conn->execute(<<<SQL
            CREATE TABLE hau_scopes (
                scope_id         INT(10) UNSIGNED    NOT NULL AUTO_INCREMENT,
                scope_string     VARCHAR(50)         NOT NULL COLLATE 'ascii_bin',
                scope_restricted TINYINT(3) UNSIGNED NOT NULL,
                scope_summary    VARCHAR(255)        NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci',
                scope_created    TIMESTAMP           NOT NULL DEFAULT current_timestamp(),
                scope_deprecated TIMESTAMP               NULL DEFAULT NULL,
                PRIMARY KEY (scope_id),
                UNIQUE INDEX hau_scopes_string_unique (scope_string),
                       INDEX hau_scopes_created_index (scope_created),
                       INDEX hau_scopes_deprecated_index (scope_deprecated)
            ) COLLATE=utf8mb4_bin ENGINE=InnoDB;
        SQL);

        $conn->execute(<<<SQL
            CREATE TABLE hau_apps_scopes (
                app_id        INT(10) UNSIGNED    NOT NULL,
                scope_id      INT(10) UNSIGNED    NOT NULL,
                scope_allowed TINYINT(3) UNSIGNED NOT NULL,
                PRIMARY KEY (app_id, scope_id),
                INDEX hau_apps_scopes_app_foreign (app_id),
                INDEX hau_apps_scopes_scope_foreign (scope_id),
                CONSTRAINT hau_apps_scopes_app_foreign
                    FOREIGN KEY (app_id)
                    REFERENCES hau_apps (app_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE,
                CONSTRAINT hau_apps_scopes_scope_foreign
                    FOREIGN KEY (scope_id)
                    REFERENCES hau_scopes (scope_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE
            ) COLLATE=utf8mb4_bin ENGINE=InnoDB;
        SQL);
    }
}