hanyuu/database/2024_07_20_185921_oauth_tables.php

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

final class OauthTables_20240720_185921 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $conn->execute(<<<SQL
            CREATE TABLE hau_oauth2_authorise (
                auth_id               INT(10) UNSIGNED                    NOT NULL AUTO_INCREMENT,
                app_id                INT(10) UNSIGNED                    NOT NULL,
                user_id               INT(10) UNSIGNED                    NOT NULL,
                uri_id                INT(10) UNSIGNED                    NOT NULL,
                auth_state            VARBINARY(255)                      NOT NULL,
                auth_challenge_code   VARCHAR(128)                        NOT NULL                   COLLATE 'ascii_bin',
                auth_challenge_method ENUM('plain','S256')                NOT NULL DEFAULT 'plain'   COLLATE 'ascii_bin',
                auth_scope            TEXT                                NOT NULL                   COLLATE 'ascii_bin',
                auth_code             CHAR(60)                            NOT NULL                   COLLATE 'ascii_bin',
                auth_approval         ENUM('pending','approved','denied') NOT NULL DEFAULT 'pending' COLLATE 'ascii_general_ci',
                auth_created          TIMESTAMP                           NOT NULL DEFAULT current_timestamp(),
                auth_expires          TIMESTAMP                           NOT NULL DEFAULT (current_timestamp() + interval 10 minute),
                PRIMARY KEY (auth_id),
                UNIQUE INDEX hau_oauth2_authorise_code_unique (auth_code),
                       INDEX hau_oauth2_authorise_app_foreign (app_id),
                       INDEX hau_oauth2_authorise_uri_foreign (uri_id),
                       INDEX hau_oauth2_authorise_user_index (user_id),
                       INDEX hau_oauth2_authorise_expires_index (auth_expires),
                CONSTRAINT hau_oauth2_authorise_app_foreign
                    FOREIGN KEY (app_id)
                    REFERENCES hau_apps (app_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE,
                CONSTRAINT hau_oauth2_authorise_uri_foreign
                    FOREIGN KEY (uri_id)
                    REFERENCES hau_apps_uris (uri_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE
            ) COLLATE='utf8mb4_bin' ENGINE=InnoDB;
        SQL);

        $conn->execute(<<<SQL
            CREATE TABLE hau_oauth2_device (
                dev_id        INT(10) UNSIGNED                    NOT NULL AUTO_INCREMENT,
                app_id        INT(10) UNSIGNED                    NOT NULL,
                user_id       INT(10) UNSIGNED                        NULL DEFAULT NULL,
                dev_code      CHAR(60)                            NOT NULL                   COLLATE 'ascii_bin',
                dev_user_code CHAR(8)                             NOT NULL                   COLLATE 'ascii_general_ci',
                dev_attempts  TINYINT(3) UNSIGNED                 NOT NULL DEFAULT '5',
                dev_interval  TINYINT(3) UNSIGNED                 NOT NULL DEFAULT '5',
                dev_polled    TIMESTAMP                           NOT NULL DEFAULT current_timestamp(),
                dev_scope     TEXT                                NOT NULL                   COLLATE 'ascii_bin',
                dev_approval  ENUM('pending','approved','denied') NOT NULL DEFAULT 'pending' COLLATE 'ascii_general_ci',
                dev_created   TIMESTAMP                           NOT NULL DEFAULT current_timestamp(),
                dev_expires   TIMESTAMP                           NOT NULL DEFAULT (current_timestamp() + interval 10 minute),
                PRIMARY KEY (dev_id),
                UNIQUE INDEX hau_oauth2_device_user_code_unique (dev_user_code),
                UNIQUE INDEX hau_oauth2_device_code_unique (dev_code),
                       INDEX hau_oauth2_device_expires_index (dev_expires),
                       INDEX hau_oauth2_device_app_foreign (app_id),
                       INDEX hau_oauth2_device_user_index (user_id),
                CONSTRAINT hau_oauth2_device_app_foreign
                    FOREIGN KEY (app_id)
                    REFERENCES hau_apps (app_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE
            ) COLLATE='utf8mb4_bin' ENGINE=InnoDB;
        SQL);

        $conn->execute(<<<SQL
            CREATE TABLE hau_oauth2_access (
                acc_id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                app_id      INT(10) UNSIGNED NOT NULL,
                user_id     INT(10) UNSIGNED     NULL DEFAULT NULL,
                acc_token   VARCHAR(255)     NOT NULL COLLATE 'ascii_bin',
                acc_scope   TEXT             NOT NULL COLLATE 'ascii_bin',
                acc_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                acc_expires TIMESTAMP        NOT NULL DEFAULT (current_timestamp() + interval 1 hour),
                PRIMARY KEY (acc_id),
                UNIQUE INDEX hau_oauth2_access_token_unique (acc_token),
                       INDEX hau_oauth2_access_user_index (user_id),
                       INDEX hau_oauth2_access_app_foreign (app_id),
                       INDEX hau_oauth2_access_expires_index (acc_expires),
                CONSTRAINT hau_oauth2_access_app_foreign
                    FOREIGN KEY (app_id)
                    REFERENCES hau_apps (app_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE
            ) COLLATE='utf8mb4_bin' ENGINE=InnoDB;
        SQL);

        $conn->execute(<<<SQL
            CREATE TABLE hau_oauth2_refresh (
                ref_id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                app_id      INT(10) UNSIGNED NOT NULL,
                user_id     INT(10) UNSIGNED     NULL DEFAULT NULL,
                acc_id      INT(10) UNSIGNED     NULL DEFAULT NULL,
                ref_token   VARCHAR(255)     NOT NULL COLLATE 'ascii_bin',
                ref_scope   TEXT             NOT NULL COLLATE 'ascii_bin',
                ref_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                ref_expires TIMESTAMP        NOT NULL DEFAULT (current_timestamp() + interval 1 month),
                PRIMARY KEY (ref_id),
                UNIQUE INDEX hau_oauth2_refresh_token_unique (ref_token),
                UNIQUE INDEX hau_oauth2_refresh_access_foreign (acc_id),
                       INDEX hau_oauth2_refresh_expires_index (ref_expires),
                       INDEX hau_oauth2_refresh_app_foreign (app_id),
                       INDEX hau_oauth2_refresh_user_index (user_id),
                CONSTRAINT hau_oauth2_refresh_access_foreign
                    FOREIGN KEY (acc_id)
                    REFERENCES hau_oauth2_access (acc_id)
                    ON UPDATE CASCADE
                    ON DELETE SET NULL,
                CONSTRAINT hau_oauth2_refresh_app_foreign
                    FOREIGN KEY (app_id)
                    REFERENCES hau_apps (app_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE
            ) COLLATE='utf8mb4_bin' ENGINE=InnoDB;
        SQL);
    }
}