119 lines
7.2 KiB
PHP
119 lines
7.2 KiB
PHP
<?php
|
|
use Index\Data\IDbConnection;
|
|
use Index\Data\Migration\IDbMigration;
|
|
|
|
final class OauthTables_20240720_185921 implements IDbMigration {
|
|
public function migrate(IDbConnection $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);
|
|
}
|
|
}
|