eeprom/database/2023_10_31_192440_initial_structure.php

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

final class InitialStructure_20231031_192440 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $existingTables = [];
        $result = $conn->query('SHOW TABLES');
        while($result->next())
            $existingTables[] = $result->getString(0);

        if(!in_array('prm_applications', $existingTables))
            $conn->execute('
                CREATE TABLE prm_applications (
                    app_id                    INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                    app_name                  VARCHAR(50)      NOT NULL COLLATE "utf8mb4_unicode_ci",
                    app_created               TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                    app_size_limit            INT(11)          NOT NULL,
                    app_allow_size_multiplier TINYINT(4)       NOT NULL DEFAULT "0",
                    app_expiry                INT(11)          NOT NULL,
                    PRIMARY KEY (app_id),
                    UNIQUE KEY prm_applications_name_unique (app_name),
                    KEY prm_applications_created_index (app_created)
                ) ENGINE=InnoDB COLLATE="utf8mb4_bin";
            ');

        if(!in_array('prm_users', $existingTables))
            $conn->execute('
                CREATE TABLE prm_users (
                    user_id              INT(10) UNSIGNED    NOT NULL,
                    user_created         TIMESTAMP           NOT NULL DEFAULT current_timestamp(),
                    user_restricted      TIMESTAMP           NULL     DEFAULT NULL,
                    user_size_multiplier TINYINT(3) UNSIGNED NOT NULL DEFAULT "1",
                    PRIMARY KEY (user_id),
                    KEY prm_users_created_index (user_created),
                    KEY prm_users_restricted_index (user_restricted)
                ) ENGINE=InnoDB COLLATE="latin1_swedish_ci";
            ');

        if(!in_array('prm_uploads', $existingTables))
            $conn->execute('
                CREATE TABLE prm_uploads (
                    upload_id       BINARY(32)       NOT NULL,
                    user_id         INT(10) UNSIGNED NULL     DEFAULT NULL,
                    app_id          INT(10) UNSIGNED NULL     DEFAULT NULL,
                    upload_hash     BINARY(32)       NOT NULL,
                    upload_ip       VARBINARY(16)    NOT NULL,
                    upload_created  TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                    upload_accessed TIMESTAMP        NULL     DEFAULT NULL,
                    upload_expires  TIMESTAMP        NULL     DEFAULT NULL,
                    upload_deleted  TIMESTAMP        NULL     DEFAULT NULL,
                    upload_dmca     TIMESTAMP        NULL     DEFAULT NULL,
                    upload_bump     INT(10) UNSIGNED NOT NULL DEFAULT "0",
                    upload_name     VARCHAR(255)     NOT NULL COLLATE "utf8mb4_bin",
                    upload_type     VARCHAR(255)     NOT NULL COLLATE "utf8mb4_bin",
                    upload_size     INT(11) UNSIGNED NOT NULL,
                    PRIMARY KEY (upload_id),
                    UNIQUE KEY prm_uploads_unique (user_id, upload_hash, app_id),
                    KEY prm_uploads_user_foreign (user_id),
                    KEY prm_uploads_ip_index (upload_ip),
                    KEY prm_uploads_created_index (upload_created),
                    KEY prm_uploads_expires_index (upload_expires),
                    KEY prm_uploads_deleted_index (upload_deleted),
                    KEY prm_uploads_dmca_index (upload_dmca),
                    KEY prm_uploads_accessed_index (upload_accessed),
                    KEY prm_uploads_application_foreign (app_id),
                    KEY prm_uploads_hash_index (upload_hash),
                    CONSTRAINT prm_uploads_application_foreign
                        FOREIGN KEY (app_id)
                        REFERENCES prm_applications (app_id)
                        ON UPDATE CASCADE
                        ON DELETE SET NULL,
                    CONSTRAINT prm_uploads_user_foreign
                        FOREIGN KEY (user_id)
                        REFERENCES prm_users (user_id)
                        ON UPDATE CASCADE
                        ON DELETE SET NULL
                ) ENGINE=InnoDB COLLATE="utf8mb4_bin";
            ');
    }
}