seria/database/2023_09_15_233145_existing_structure.php

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

final class ExistingStructure_20230915_233145 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $hasTorrents = false;
        $hasTorrentsFiles = false;
        $hasTorrentsPeers = false;
        $hasTorrentsPieces = false;
        $hasUsers = false;

        // check if the old tables exist
        $tables = $conn->query('SHOW TABLES');
        while($tables->next()) {
            $tableName = $tables->getString(0);
            if($tableName === 'ser_torrents')
                $hasTorrents = true;
            elseif($tableName === 'ser_torrents_files')
                $hasTorrentsFiles = true;
            elseif($tableName === 'ser_torrents_peers')
                $hasTorrentsPeers = true;
            elseif($tableName === 'ser_torrents_pieces')
                $hasTorrentsPieces = true;
            elseif($tableName === 'ser_users')
                $hasUsers = true;
        }

        if(!$hasUsers)
            $conn->execute('
                CREATE TABLE ser_users (
                    user_id               INT(10) UNSIGNED    NOT NULL,
                    user_name             VARCHAR(255)        NOT NULL COLLATE "utf8mb4_unicode_520_ci",
                    user_colour           INT(10) UNSIGNED    NOT NULL,
                    user_rank             INT(11)             NOT NULL,
                    user_permissions      INT(10) UNSIGNED    NOT NULL,
                    user_pass_key         BINARY(48)          NULL DEFAULT NULL,
                    user_bytes_downloaded BIGINT(20) UNSIGNED NOT NULL DEFAULT "0",
                    user_bytes_uploaded   BIGINT(20) UNSIGNED NOT NULL DEFAULT "1000000",
                    PRIMARY KEY (user_id),
                    UNIQUE KEY ser_users_name_unique (user_name),
                    UNIQUE KEY ser_users_pass_key_unique (user_pass_key)
                ) ENGINE=InnoDB COLLATE="utf8mb4_bin";
            ');

        if(!$hasTorrents)
            $conn->execute('
                CREATE TABLE ser_torrents (
                    torrent_id           INT(10) UNSIGNED    NOT NULL AUTO_INCREMENT,
                    user_id              INT(10) UNSIGNED    NULL     DEFAULT NULL,
                    torrent_hash         BINARY(20)          NOT NULL,
                    torrent_active       TINYINT(4) UNSIGNED NOT NULL DEFAULT "1",
                    torrent_name         VARCHAR(255)        NOT NULL COLLATE "utf8mb4_bin",
                    torrent_created      TIMESTAMP           NOT NULL DEFAULT current_timestamp(),
                    torrent_approved     TIMESTAMP           NULL     DEFAULT NULL,
                    torrent_piece_length INT(10) UNSIGNED    NOT NULL DEFAULT "0",
                    torrent_private      TINYINT(3) UNSIGNED NOT NULL DEFAULT "0",
                    torrent_comment      TEXT                NOT NULL DEFAULT "" COLLATE "utf8mb4_bin",
                    PRIMARY KEY (torrent_id),
                    UNIQUE KEY ser_torrents_hash_unique (torrent_hash),
                    KEY ser_torrents_active_index (torrent_active),
                    KEY ser_torrents_user_foreign (user_id),
                    KEY ser_torrents_approved_index (torrent_approved),
                    KEY ser_torrents_private_index (torrent_private),
                    CONSTRAINT ser_torrents_user_foreign
                        FOREIGN KEY (user_id)
                        REFERENCES ser_users (user_id)
                        ON UPDATE CASCADE
                        ON DELETE SET NULL
                ) ENGINE=InnoDB COLLATE="utf8mb4_bin";
            ');

        if(!$hasTorrentsFiles)
            $conn->execute('
                CREATE TABLE ser_torrents_files (
                    file_id     BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                    torrent_id  INT(10) UNSIGNED    NOT NULL,
                    file_length BIGINT(20) UNSIGNED NOT NULL,
                    file_path   VARCHAR(255)        NOT NULL COLLATE "utf8mb4_bin",
                    PRIMARY KEY (file_id),
                    UNIQUE KEY ser_torrents_files_path_unique (file_id, file_path),
                    KEY ser_torrents_files_torrent_foreign (torrent_id),
                    CONSTRAINT ser_torrents_files_torrent_foreign
                        FOREIGN KEY (torrent_id)
                        REFERENCES ser_torrents (torrent_id)
                        ON UPDATE CASCADE
                        ON DELETE CASCADE
                ) ENGINE=InnoDB COLLATE="utf8mb4_bin";
            ');

        if(!$hasTorrentsPieces)
            $conn->execute('
                CREATE TABLE ser_torrents_pieces (
                    piece_id   BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                    torrent_id INT(10) UNSIGNED NOT NULL,
                    piece_hash BINARY(20) NOT NULL,
                    PRIMARY KEY (piece_id),
                    KEY ser_torrents_pieces_torrent_foreign (torrent_id),
                    CONSTRAINT ser_torrents_pieces_torrent_foreign
                        FOREIGN KEY (torrent_id)
                        REFERENCES ser_torrents (torrent_id)
                        ON UPDATE CASCADE
                        ON DELETE CASCADE
                ) ENGINE=InnoDB COLLATE="utf8mb4_bin";
            ');

        if(!$hasTorrentsPeers)
            $conn->execute('
                CREATE TABLE ser_torrents_peers (
                    peer_id         BINARY(20)           NOT NULL,
                    torrent_id      INT(10) UNSIGNED     NOT NULL,
                    user_id         INT(10) UNSIGNED     NOT NULL,
                    peer_address    BINARY(4)            NOT NULL,
                    peer_port       SMALLINT(5) UNSIGNED NOT NULL,
                    peer_updated    TIMESTAMP            NOT NULL DEFAULT current_timestamp(),
                    peer_expires    TIMESTAMP            NOT NULL DEFAULT from_unixtime(0),
                    peer_agent      VARCHAR(255)         NOT NULL DEFAULT "" COLLATE "utf8mb4_bin",
                    peer_key        VARBINARY(64)        NOT NULL DEFAULT "",
                    peer_uploaded   BIGINT(20) UNSIGNED  NOT NULL,
                    peer_downloaded BIGINT(20) UNSIGNED  NOT NULL,
                    peer_left       BIGINT(20) UNSIGNED  NOT NULL,
                    PRIMARY KEY (peer_id, torrent_id),
                    KEY ser_peers_expires_index (peer_expires),
                    KEY ser_peers_torrent_foreign (torrent_id),
                    KEY ser_peers_left_index (peer_left),
                    KEY ser_peers_user_foreign (user_id),
                    CONSTRAINT ser_peers_torrent_foreign
                        FOREIGN KEY (torrent_id)
                        REFERENCES ser_torrents (torrent_id)
                        ON UPDATE CASCADE
                        ON DELETE CASCADE,
                    CONSTRAINT ser_peers_user_foreign
                        FOREIGN KEY (user_id)
                        REFERENCES ser_users (user_id)
                        ON UPDATE CASCADE
                        ON DELETE CASCADE
                ) ENGINE=InnoDB COLLATE="utf8mb4_bin";
            ');
    }
}