diff options
Diffstat (limited to 'sql/mysql.sql')
| -rw-r--r-- | sql/mysql.sql | 205 |
1 files changed, 170 insertions, 35 deletions
diff --git a/sql/mysql.sql b/sql/mysql.sql index 3d253c574..7afc2cf1a 100644 --- a/sql/mysql.sql +++ b/sql/mysql.sql @@ -1,5 +1,5 @@ -- --- ejabberd, Copyright (C) 2002-2016 ProcessOne +-- ejabberd, Copyright (C) 2002-2019 ProcessOne -- -- This program is free software; you can redistribute it and/or -- modify it under the terms of the GNU General Public License as @@ -80,7 +80,7 @@ CREATE INDEX i_sr_user_grp ON sr_user(grp); CREATE TABLE spool ( username varchar(191) NOT NULL, - xml BLOB NOT NULL, + xml mediumtext NOT NULL, seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; @@ -93,8 +93,8 @@ CREATE TABLE archive ( timestamp BIGINT UNSIGNED NOT NULL, peer varchar(191) NOT NULL, bare_peer varchar(191) NOT NULL, - xml text NOT NULL, - txt text, + xml mediumtext NOT NULL, + txt mediumtext, id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, kind varchar(10), nick varchar(191), @@ -102,10 +102,10 @@ CREATE TABLE archive ( ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE FULLTEXT INDEX i_text ON archive(txt); -CREATE INDEX i_username USING BTREE ON archive(username); +CREATE INDEX i_username_timestamp USING BTREE ON archive(username(191), timestamp); +CREATE INDEX i_username_peer USING BTREE ON archive(username(191), peer(191)); +CREATE INDEX i_username_bare_peer USING BTREE ON archive(username(191), bare_peer(191)); CREATE INDEX i_timestamp USING BTREE ON archive(timestamp); -CREATE INDEX i_peer USING BTREE ON archive(peer); -CREATE INDEX i_bare_peer USING BTREE ON archive(bare_peer); CREATE TABLE archive_prefs ( username varchar(191) NOT NULL PRIMARY KEY, @@ -121,12 +121,6 @@ CREATE TABLE vcard ( created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -CREATE TABLE vcard_xupdate ( - username varchar(191) PRIMARY KEY, - hash text NOT NULL, - created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - CREATE TABLE vcard_search ( username varchar(191) NOT NULL, lusername varchar(191) PRIMARY KEY, @@ -218,35 +212,35 @@ CREATE TABLE roster_version ( -- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL; CREATE TABLE pubsub_node ( - host text, - node text, - parent text, - type text, + host text NOT NULL, + node text NOT NULL, + parent VARCHAR(191) NOT NULL DEFAULT '', + plugin text NOT NULL, nodeid bigint auto_increment primary key ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_node_parent ON pubsub_node(parent(120)); -CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(20), node(120)); +CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(71), node(120)); CREATE TABLE pubsub_node_option ( nodeid bigint, - name text, - val text + name text NOT NULL, + val text NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option(nodeid); ALTER TABLE `pubsub_node_option` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; CREATE TABLE pubsub_node_owner ( nodeid bigint, - owner text + owner text NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner(nodeid); ALTER TABLE `pubsub_node_owner` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; CREATE TABLE pubsub_state ( nodeid bigint, - jid text, + jid text NOT NULL, affiliation character(1), - subscriptions text, + subscriptions VARCHAR(191) NOT NULL DEFAULT '', stateid bigint auto_increment primary key ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_state_jid ON pubsub_state(jid(60)); @@ -255,20 +249,20 @@ ALTER TABLE `pubsub_state` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` ( CREATE TABLE pubsub_item ( nodeid bigint, - itemid text, - publisher text, - creation text, - modification text, - payload text + itemid text NOT NULL, + publisher text NOT NULL, + creation varchar(32) NOT NULL, + modification varchar(32) NOT NULL, + payload mediumtext NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE INDEX i_pubsub_item_itemid ON pubsub_item(itemid(36)); CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item(nodeid, itemid(36)); ALTER TABLE `pubsub_item` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; CREATE TABLE pubsub_subscription_opt ( - subid text, + subid text NOT NULL, opt_name varchar(32), - opt_value text + opt_value text NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt(subid(32), opt_name(32)); @@ -291,14 +285,38 @@ CREATE TABLE muc_registered ( CREATE INDEX i_muc_registered_nick USING BTREE ON muc_registered(nick(75)); CREATE UNIQUE INDEX i_muc_registered_jid_host USING BTREE ON muc_registered(jid(75), host(75)); -CREATE TABLE irc_custom ( - jid text NOT NULL, +CREATE TABLE muc_online_room ( + name text NOT NULL, host text NOT NULL, - data text NOT NULL, - created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP + node text NOT NULL, + pid text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_muc_online_room_name_host USING BTREE ON muc_online_room(name(75), host(75)); + +CREATE TABLE muc_online_users ( + username text NOT NULL, + server text NOT NULL, + resource text NOT NULL, + name text NOT NULL, + host text NOT NULL, + node text NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -CREATE UNIQUE INDEX i_irc_custom_jid_host USING BTREE ON irc_custom(jid(75), host(75)); +CREATE UNIQUE INDEX i_muc_online_users USING BTREE ON muc_online_users(username(75), server(75), resource(75), name(75), host(75)); +CREATE INDEX i_muc_online_users_us USING BTREE ON muc_online_users(username(75), server(75)); + +CREATE TABLE muc_room_subscribers ( + room varchar(191) NOT NULL, + host varchar(191) NOT NULL, + jid varchar(191) NOT NULL, + nick text NOT NULL, + nodes text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE KEY i_muc_room_subscribers_host_room_jid (host, room, jid) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_muc_room_subscribers_host_jid USING BTREE ON muc_room_subscribers(host, jid); CREATE TABLE motd ( username varchar(191) PRIMARY KEY, @@ -335,3 +353,120 @@ CREATE TABLE oauth_token ( scope text NOT NULL, expire bigint NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE oauth_client ( + client_id varchar(191) NOT NULL PRIMARY KEY, + client_name text NOT NULL, + grant_type text NOT NULL, + options text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE route ( + domain text NOT NULL, + server_host text NOT NULL, + node text NOT NULL, + pid text NOT NULL, + local_hint text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_route ON route(domain(75), server_host(75), node(75), pid(75)); +CREATE INDEX i_route_domain ON route(domain(75)); + +CREATE TABLE bosh ( + sid text NOT NULL, + node text NOT NULL, + pid text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_bosh_sid ON bosh(sid(75)); + +CREATE TABLE proxy65 ( + sid text NOT NULL, + pid_t text NOT NULL, + pid_i text NOT NULL, + node_t text NOT NULL, + node_i text NOT NULL, + jid_i text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_proxy65_sid ON proxy65 (sid(191)); +CREATE INDEX i_proxy65_jid ON proxy65 (jid_i(191)); + +CREATE TABLE push_session ( + username text NOT NULL, + timestamp bigint NOT NULL, + service text NOT NULL, + node text NOT NULL, + xml text NOT NULL +); + +CREATE UNIQUE INDEX i_push_usn ON push_session (username(191), service(191), node(191)); +CREATE UNIQUE INDEX i_push_ut ON push_session (username(191), timestamp); + +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)); + +CREATE TABLE mqtt_pub ( + username 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 (topic(191)) +); |