sharp-chat/SharpChat.MariaDB/MariaDBMigrations.cs

using Microsoft.Extensions.Logging;
using MySqlConnector;
using ZLogger;

namespace SharpChat.MariaDB;

public class MariaDBMigrations(ILogger logger, MariaDBConnection conn) {
    private async Task DoMigration(string name, Func<Task> action) {
        bool done = await conn.RunQueryValue<long>(
            "SELECT COUNT(*) FROM sqc_migrations WHERE migration_name = @name",
            new MySqlParameter("name", name)
        ) > 0;
        if(!done) {
            logger.ZLogInformation($@"Running migration ""{name}""...");
            await action();
            await conn.RunCommand(
                "INSERT INTO sqc_migrations (migration_name) VALUES (@name)",
                new MySqlParameter("name", name)
            );
        }
    }

    public async Task RunMigrations() {
        await conn.RunCommand(
            "CREATE TABLE IF NOT EXISTS sqc_migrations ("
            + "migration_name VARCHAR(255) NOT NULL,"
            + "migration_completed TIMESTAMP NOT NULL DEFAULT current_timestamp(),"
            + "UNIQUE INDEX migration_name (migration_name),"
            + "INDEX migration_completed (migration_completed)"
            + ") COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;"
        );

        await DoMigration("create_events_table", CreateEventsTable);
        await DoMigration("allow_null_target", AllowNullTarget);
        await DoMigration("event_data_as_medium_blob", EventDataAsMediumBlob);
        await DoMigration("event_user_and_nick_name_to_1000", EventUserAndNickNameTo1000);
        await DoMigration("no_more_flags_field", NoMoreFlagsField);
        await DoMigration("update_event_type_names", UpdateEventTypeNames);
        await DoMigration("update_collations_and_use_json_type", UpdateCollationsAndUseJsonType);
    }

    private async Task UpdateCollationsAndUseJsonType() {
        await conn.RunCommand("UPDATE sqc_events SET event_target = LOWER(CONVERT(event_target USING ascii))");
        await conn.RunCommand("UPDATE sqc_events SET event_sender_nick = NULL WHERE event_sender_nick = ''");
        await conn.RunCommand(
            "ALTER TABLE sqc_events COLLATE='utf8mb4_unicode_520_ci',"
            + " CHANGE COLUMN event_type event_type VARCHAR(255) NOT NULL COLLATE 'ascii_general_ci' AFTER event_id,"
            + " CHANGE COLUMN event_created event_created TIMESTAMP NOT NULL DEFAULT current_timestamp() AFTER event_type,"
            + " CHANGE COLUMN event_deleted event_deleted TIMESTAMP NULL DEFAULT NULL AFTER event_created,"
            + " CHANGE COLUMN event_target event_channel VARCHAR(255) NULL DEFAULT NULL COLLATE 'ascii_general_ci' AFTER event_deleted,"
            + " CHANGE COLUMN event_sender event_sender VARCHAR(255) NULL DEFAULT NULL COLLATE 'ascii_bin' AFTER event_channel,"
            + " CHANGE COLUMN event_sender_name event_sender_name VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_520_ci' AFTER event_sender,"
            + " CHANGE COLUMN event_sender_nick event_sender_nick VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_520_ci' AFTER event_sender_rank,"
            + " CHANGE COLUMN event_data event_data JSON NOT NULL DEFAULT '{}' AFTER event_sender_perms,"
            + " DROP INDEX event_target, ADD INDEX event_channel (event_channel)"
        );
    }

    private async Task UpdateEventTypeNames() {
        await conn.RunCommand(@"UPDATE sqc_events SET event_type = ""msg:add"" WHERE event_type = ""SharpChat.Events.ChatMessage""");
        await conn.RunCommand(@"UPDATE sqc_events SET event_type = ""user:connect"" WHERE event_type = ""SharpChat.Events.UserConnectEvent""");
        await conn.RunCommand(@"UPDATE sqc_events SET event_type = ""user:disconnect"" WHERE event_type = ""SharpChat.Events.UserDisconnectEvent""");
        await conn.RunCommand(@"UPDATE sqc_events SET event_type = ""chan:join"" WHERE event_type = ""SharpChat.Events.UserChannelJoinEvent""");
        await conn.RunCommand(@"UPDATE sqc_events SET event_type = ""chan:leave"" WHERE event_type = ""SharpChat.Events.UserChannelLeaveEvent""");
    }

    private async Task NoMoreFlagsField() {
        // MessageFlags.Action is just a field in the data object
        await conn.RunCommand("UPDATE sqc_events SET event_data = JSON_MERGE_PATCH(event_data, JSON_OBJECT('act', true)) WHERE event_flags & 1");

        // MessageFlags.Broadcast can be implied by just having a NULL as the channel name
        await conn.RunCommand("UPDATE sqc_events SET event_target = NULL WHERE event_flags & 2");

        // MessageFlags.Log was never meaningfully used by anything and basically just meant "not-msg:add"
        // MessageFlags.Private was also never meaningfully used, can be determined by checking if the channel name starts with @
        await conn.RunCommand("ALTER TABLE sqc_events DROP COLUMN event_flags");
    }

    private async Task EventUserAndNickNameTo1000() {
        await conn.RunCommand(
            "ALTER TABLE sqc_events"
            + " CHANGE COLUMN event_sender_name event_sender_name VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_520_ci' AFTER event_sender,"
            + " CHANGE COLUMN event_sender_nick event_sender_nick VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_520_ci' AFTER event_sender_rank;"
        );
    }

    private async Task EventDataAsMediumBlob() {
        await conn.RunCommand(
            "ALTER TABLE sqc_events"
            + " CHANGE COLUMN event_data event_data MEDIUMBLOB NULL DEFAULT NULL AFTER event_flags;"
        );
    }

    private async Task AllowNullTarget() {
        await conn.RunCommand(
            "ALTER TABLE sqc_events"
            + " CHANGE COLUMN event_target event_target VARBINARY(255) NULL AFTER event_type;"
        );
    }

    private async Task CreateEventsTable() {
        await conn.RunCommand(
            "CREATE TABLE sqc_events ("
            + "event_id BIGINT(20) NOT NULL,"
            + "event_sender BIGINT(20) UNSIGNED NULL DEFAULT NULL,"
            + "event_sender_name VARCHAR(255) NULL DEFAULT NULL,"
            + "event_sender_colour INT(11) NULL DEFAULT NULL,"
            + "event_sender_rank INT(11) NULL DEFAULT NULL,"
            + "event_sender_nick VARCHAR(255) NULL DEFAULT NULL,"
            + "event_sender_perms INT(11) NULL DEFAULT NULL,"
            + "event_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),"
            + "event_deleted TIMESTAMP NULL DEFAULT NULL,"
            + "event_type VARBINARY(255) NOT NULL,"
            + "event_target VARBINARY(255) NOT NULL,"
            + "event_flags TINYINT(3) UNSIGNED NOT NULL,"
            + "event_data BLOB NULL DEFAULT NULL,"
            + "PRIMARY KEY (event_id),"
            + "INDEX event_target (event_target),"
            + "INDEX event_type (event_type),"
            + "INDEX event_sender (event_sender),"
            + "INDEX event_datetime (event_created),"
            + "INDEX event_deleted (event_deleted)"
            + ") COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;"
        );
    }
}