openbsd-ports/net/ejabberd/files/mysql-update.sql

-- 18.09
ALTER TABLE spool MODIFY xml mediumtext NOT NULL;
ALTER TABLE archive MODIFY xml mediumtext NOT NULL;
ALTER TABLE archive MODIFY txt mediumtext;
ALTER TABLE pubsub_item MODIFY payload mediumtext NOT NULL;


-- 18.12
ALTER TABLE pubsub_item MODIFY payload mediumtext NOT NULL;

DROP INDEX i_pubsub_node_tuple ON pubsub_node;
CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(71), node(120));

-- MIX setup
CREATE TABLE mix_channel (
    channel text NOT NULL,
    service text NOT NULL,
    username text NOT NULL,
    domain text NOT NULL,
    jid text NOT NULL,
    hidden boolean NOT NULL,
    hmac_key text NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel(191), service(191));
CREATE INDEX i_mix_channel_serv ON mix_channel (service(191));

CREATE TABLE mix_participant (
    channel text NOT NULL,
    service text NOT NULL,
    username text NOT NULL,
    domain text NOT NULL,
    jid text NOT NULL,
    id text NOT NULL,
    nick text NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel(191), service(191), username(191), domain(191));
CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel(191), service(191));

CREATE TABLE mix_subscription (
    channel text NOT NULL,
    service text NOT NULL,
    username text NOT NULL,
    domain text NOT NULL,
    node text NOT NULL,
    jid text NOT NULL
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel(153), service(153), username(153), domain(153), node(153));
CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel(191), service(191), username(191), domain(191));
CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel(191), service(191), node(191));
CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel(191), service(191));

CREATE TABLE mix_pam (
    username text NOT NULL,
    channel text NOT NULL,
    service text NOT NULL,
    id text NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username(191), channel(191), service(191));
CREATE INDEX i_mix_pam_u ON mix_pam (username(191));

-- MQTT

CREATE TABLE mqtt_pub (
    username varchar(191) NOT NULL,
    server_host varchar(191) NOT NULL,
    resource varchar(191) NOT NULL,
    topic text NOT NULL,
    qos tinyint NOT NULL,
    payload blob NOT NULL,
    payload_format tinyint NOT NULL,
    content_type text NOT NULL,
    response_topic text NOT NULL,
    correlation_data blob NOT NULL,
    user_properties blob NOT NULL,
    expiry int unsigned NOT NULL,
    UNIQUE KEY i_mqtt_topic_server (topic(191))
);


-- 19.08

ALTER TABLE users MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE last MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE rosterusers MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE rostergroups MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE sr_group MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE sr_user MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE spool MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE archive MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE archive_prefs MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE vcard MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE vcard_search MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE privacy_default_list MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE privacy_list MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE private_storage MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE roster_version MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE muc_room MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE muc_registered MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE muc_online_room MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE muc_online_users MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE motd MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE sm MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE route MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE push_session MODIFY server_host varchar(191) NOT NULL;
ALTER TABLE mix_pam MODIFY server_host varchar(191) NOT NULL;


-- 20.01

ALTER TABLE  oauth_client CHANGE `client` `client_id` text PRIMARY KEY;
ALTER TABLE  oauth_client CHANGE `secret` `client_name` text NOT NULL;