hanyuu/database/2024_07_20_185612_apps_tables.php

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

final class AppsTables_20240720_185612 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $conn->execute(<<<SQL
            CREATE TABLE hau_apps (
                app_id              INT(10) UNSIGNED              NOT NULL AUTO_INCREMENT,
                app_name            VARCHAR(64)                   NOT NULL COLLATE 'utf8mb4_unicode_520_ci',
                app_summary         VARCHAR(255)                  NOT NULL COLLATE 'utf8mb4_bin',
                app_website         VARCHAR(255)                  NOT NULL COLLATE 'utf8mb4_bin',
                app_trusted         TINYINT(3) UNSIGNED           NOT NULL DEFAULT '0',
                app_type            ENUM('public','confidential') NOT NULL COLLATE 'ascii_general_ci',
                app_client_id       CHAR(20)                      NOT NULL COLLATE 'ascii_bin',
                app_client_secret   VARCHAR(255)                  NOT NULL COLLATE 'ascii_bin',
                app_created         TIMESTAMP                     NOT NULL DEFAULT current_timestamp(),
                app_updated         TIMESTAMP                     NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                app_deleted         TIMESTAMP                         NULL DEFAULT NULL,
                PRIMARY KEY (app_id),
                UNIQUE INDEX hau_app_client_id_unique (app_client_id),
                UNIQUE INDEX hau_app_name_unique (app_name),
                       INDEX hau_app_created_index (app_created),
                       INDEX hau_app_deleted_index (app_deleted)
            ) COLLATE='utf8mb4_bin' ENGINE=InnoDB;
        SQL);

        $conn->execute(<<<SQL
            CREATE TABLE hau_apps_uris (
                uri_id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                app_id      INT(10) UNSIGNED NOT NULL,
                uri_string  VARCHAR(255)     NOT NULL COLLATE 'ascii_bin',
                uri_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (uri_id),
                INDEX hau_apps_uris_app_id_foreign (app_id),
                INDEX hau_apps_uris_lookup_index (uri_id, uri_string),
                INDEX hau_apps_uri_created_index (uri_created),
                CONSTRAINT hau_apps_uris_app_id_foreign
                    FOREIGN KEY (app_id)
                    REFERENCES hau_apps (app_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE
            ) COLLATE='utf8mb4_bin' ENGINE=InnoDB;
        SQL);
    }
}