88 lines
4.2 KiB
PHP
88 lines
4.2 KiB
PHP
<?php
|
|
use Index\Db\DbConnection;
|
|
use Index\Db\Migration\DbMigration;
|
|
|
|
final class PoolsSystemWithRules_20241225_123334 implements DbMigration {
|
|
public function migrate(DbConnection $conn): void {
|
|
// Create pools tables LOL HOW COULD I FORGET THIS WHAT
|
|
$conn->execute(<<<SQL
|
|
CREATE TABLE prm_pools (
|
|
pool_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
pool_name VARCHAR(32) NOT NULL COLLATE 'ascii_general_ci',
|
|
pool_secret VARCHAR(255) NULL DEFAULT NULL COLLATE 'ascii_bin',
|
|
pool_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
|
|
pool_deprecated TIMESTAMP NULL DEFAULT NULL,
|
|
PRIMARY KEY (pool_id) USING BTREE,
|
|
UNIQUE INDEX prm_pools_name_unique (pool_name) USING BTREE,
|
|
INDEX prm_pools_created_index (pool_created) USING BTREE,
|
|
INDEX prm_pools_deprecated_index (pool_deprecated) USING BTREE
|
|
) ENGINE=InnoDB COLLATE='utf8mb4_bin'
|
|
SQL);
|
|
|
|
$conn->execute(<<<SQL
|
|
CREATE TABLE prm_pools_rules (
|
|
rule_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
pool_id INT(10) UNSIGNED NOT NULL,
|
|
rule_type VARCHAR(64) NOT NULL COLLATE 'ascii_general_ci',
|
|
rule_params LONGTEXT NOT NULL DEFAULT '{}' COLLATE 'utf8mb4_bin',
|
|
PRIMARY KEY (rule_id) USING BTREE,
|
|
INDEX prm_pools_rules_pool_foreign (pool_id) USING BTREE,
|
|
INDEX prm_pools_rules_type_index (rule_type) USING BTREE,
|
|
CONSTRAINT prm_pools_rules_pool_foreign
|
|
FOREIGN KEY (pool_id)
|
|
REFERENCES prm_pools (pool_id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT prm_pools_rules_params_ensure_object
|
|
CHECK (json_type(rule_params) = 'OBJECT')
|
|
) ENGINE=InnoDB COLLATE='utf8mb4_bin'
|
|
SQL);
|
|
|
|
// Create things that used to exist as apps
|
|
$conn->execute('INSERT INTO prm_pools (pool_name) VALUES ("chat"), ("forum-public"), ("forum-private")');
|
|
|
|
// Set NULL access timestamps to the created timestamp
|
|
$conn->execute('UPDATE prm_uploads SET upload_accessed = upload_created WHERE upload_accessed IS NULL');
|
|
|
|
// Remove stuff that'll now be handled by pool rules
|
|
$conn->execute(<<<SQL
|
|
ALTER TABLE prm_uploads
|
|
DROP FOREIGN KEY prm_uploads_application_foreign
|
|
SQL);
|
|
$conn->execute(<<<SQL
|
|
ALTER TABLE prm_uploads
|
|
CHANGE COLUMN app_id pool_id INT(10) UNSIGNED NOT NULL AFTER user_id,
|
|
CHANGE COLUMN upload_accessed upload_accessed TIMESTAMP NOT NULL DEFAULT current_timestamp() AFTER upload_created,
|
|
DROP COLUMN upload_bump,
|
|
DROP COLUMN upload_expires;
|
|
SQL);
|
|
$conn->execute(<<<SQL
|
|
ALTER TABLE prm_uploads
|
|
ADD INDEX prm_uploads_pool_foreign (pool_id) USING BTREE,
|
|
ADD CONSTRAINT prm_uploads_pool_foreign
|
|
FOREIGN KEY (pool_id)
|
|
REFERENCES prm_pools (pool_id)
|
|
ON UPDATE CASCADE
|
|
ON DELETE CASCADE;
|
|
SQL);
|
|
|
|
// bye bye apps
|
|
$conn->execute('DROP TABLE prm_applications');
|
|
|
|
// shh
|
|
$conn->execute('RENAME TABLE prm_blacklist TO prm_denylist');
|
|
$conn->execute(<<<SQL
|
|
ALTER TABLE prm_denylist
|
|
CHANGE COLUMN bl_hash deny_hash BINARY(32) NOT NULL FIRST,
|
|
CHANGE COLUMN bl_reason deny_reason ENUM('copyright','rules','other') NOT NULL COLLATE 'ascii_general_ci' AFTER deny_hash,
|
|
CHANGE COLUMN bl_created deny_created TIMESTAMP NOT NULL DEFAULT current_timestamp() AFTER deny_reason,
|
|
DROP PRIMARY KEY,
|
|
ADD UNIQUE INDEX prm_denylist_hash_unique (deny_hash);
|
|
SQL);
|
|
$conn->execute(<<<SQL
|
|
ALTER TABLE prm_denylist
|
|
ADD COLUMN deny_id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
|
|
ADD PRIMARY KEY (deny_id)
|
|
SQL);
|
|
}
|
|
}
|