diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/lite.new.sql | 483 | ||||
| -rw-r--r-- | sql/lite.sql | 245 | ||||
| -rw-r--r-- | sql/mssql.sql | 140 | ||||
| -rw-r--r-- | sql/mysql.new.sql | 505 | ||||
| -rw-r--r-- | sql/mysql.sql | 205 | ||||
| -rw-r--r-- | sql/pg.new.sql | 651 | ||||
| -rw-r--r-- | sql/pg.sql | 204 |
7 files changed, 2266 insertions, 167 deletions
diff --git a/sql/lite.new.sql b/sql/lite.new.sql new file mode 100644 index 000000000..7264e58cb --- /dev/null +++ b/sql/lite.new.sql @@ -0,0 +1,483 @@ +-- +-- 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 +-- published by the Free Software Foundation; either version 2 of the +-- License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +-- General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along +-- with this program; if not, write to the Free Software Foundation, Inc., +-- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +-- + +CREATE TABLE users ( + username text NOT NULL, + server_host text NOT NULL, + password text NOT NULL, + serverkey text NOT NULL DEFAULT '', + salt text NOT NULL DEFAULT '', + iterationcount integer NOT NULL DEFAULT 0, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host, username) +); + + +CREATE TABLE last ( + username text NOT NULL, + server_host text NOT NULL, + seconds text NOT NULL, + state text NOT NULL, + PRIMARY KEY (server_host, username) +); + + +CREATE TABLE rosterusers ( + username text NOT NULL, + server_host text NOT NULL, + jid text NOT NULL, + nick text NOT NULL, + subscription character(1) NOT NULL, + ask character(1) NOT NULL, + askmessage text NOT NULL, + server character(1) NOT NULL, + subscribe text NOT NULL, + type text, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE UNIQUE INDEX i_rosteru_sh_user_jid ON rosterusers (server_host, username, jid); +CREATE INDEX i_rosteru_sh_username ON rosterusers (server_host, username); +CREATE INDEX i_rosteru_sh_jid ON rosterusers (server_host, jid); + + +CREATE TABLE rostergroups ( + username text NOT NULL, + server_host text NOT NULL, + jid text NOT NULL, + grp text NOT NULL +); + +CREATE INDEX i_rosterg_sh_user_jid ON rostergroups (server_host, username, jid); + +CREATE TABLE sr_group ( + name text NOT NULL, + server_host text NOT NULL, + opts text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host, name) +); + +CREATE TABLE sr_user ( + jid text NOT NULL, + server_host text NOT NULL, + grp text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host, jid, grp) +); + +CREATE INDEX i_sr_user_sh_jid ON sr_user (server_host, jid); +CREATE INDEX i_sr_user_sh_grp ON sr_user (server_host, grp); + +CREATE TABLE spool ( + username text NOT NULL, + server_host text NOT NULL, + xml text NOT NULL, + seq INTEGER PRIMARY KEY AUTOINCREMENT, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_spool_sh_username ON spool (server_host, username); + +CREATE TABLE archive ( + username text NOT NULL, + server_host text NOT NULL, + timestamp BIGINT UNSIGNED NOT NULL, + peer text NOT NULL, + bare_peer text NOT NULL, + xml text NOT NULL, + txt text, + id INTEGER PRIMARY KEY AUTOINCREMENT, + kind text, + nick text, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_archive_sh_username_timestamp ON archive (server_host, username, timestamp); +CREATE INDEX i_archive_sh_username_peer ON archive (server_host, username, peer); +CREATE INDEX i_archive_sh_username_bare_peer ON archive (server_host, username, bare_peer); +CREATE INDEX i_archive_sh_timestamp ON archive (server_host, timestamp); + +CREATE TABLE archive_prefs ( + username text NOT NULL, + server_host text NOT NULL, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host, username) +); + +CREATE TABLE vcard ( + username text NOT NULL, + server_host text NOT NULL, + vcard text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host, username) +); + +CREATE TABLE vcard_search ( + username text NOT NULL, + lusername text NOT NULL, + server_host text NOT NULL, + fn text NOT NULL, + lfn text NOT NULL, + family text NOT NULL, + lfamily text NOT NULL, + given text NOT NULL, + lgiven text NOT NULL, + middle text NOT NULL, + lmiddle text NOT NULL, + nickname text NOT NULL, + lnickname text NOT NULL, + bday text NOT NULL, + lbday text NOT NULL, + ctry text NOT NULL, + lctry text NOT NULL, + locality text NOT NULL, + llocality text NOT NULL, + email text NOT NULL, + lemail text NOT NULL, + orgname text NOT NULL, + lorgname text NOT NULL, + orgunit text NOT NULL, + lorgunit text NOT NULL, + PRIMARY KEY (server_host, lusername) +); + +CREATE INDEX i_vcard_search_sh_lfn ON vcard_search(server_host, lfn); +CREATE INDEX i_vcard_search_sh_lfamily ON vcard_search(server_host, lfamily); +CREATE INDEX i_vcard_search_sh_lgiven ON vcard_search(server_host, lgiven); +CREATE INDEX i_vcard_search_sh_lmiddle ON vcard_search(server_host, lmiddle); +CREATE INDEX i_vcard_search_sh_lnickname ON vcard_search(server_host, lnickname); +CREATE INDEX i_vcard_search_sh_lbday ON vcard_search(server_host, lbday); +CREATE INDEX i_vcard_search_sh_lctry ON vcard_search(server_host, lctry); +CREATE INDEX i_vcard_search_sh_llocality ON vcard_search(server_host, llocality); +CREATE INDEX i_vcard_search_sh_lemail ON vcard_search(server_host, lemail); +CREATE INDEX i_vcard_search_sh_lorgname ON vcard_search(server_host, lorgname); +CREATE INDEX i_vcard_search_sh_lorgunit ON vcard_search(server_host, lorgunit); + +CREATE TABLE privacy_default_list ( + username text NOT NULL, + server_host text NOT NULL, + name text NOT NULL, + PRIMARY KEY (server_host, username) +); + +CREATE TABLE privacy_list ( + username text NOT NULL, + server_host text NOT NULL, + name text NOT NULL, + id INTEGER PRIMARY KEY AUTOINCREMENT, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_privacy_list_sh_username ON privacy_list (server_host, username); +CREATE UNIQUE INDEX i_privacy_list_sh_username_name ON privacy_list (server_host, username, name); + +CREATE TABLE privacy_list_data ( + id bigint REFERENCES privacy_list(id) ON DELETE CASCADE, + t character(1) NOT NULL, + value text NOT NULL, + action character(1) NOT NULL, + ord NUMERIC NOT NULL, + match_all boolean NOT NULL, + match_iq boolean NOT NULL, + match_message boolean NOT NULL, + match_presence_in boolean NOT NULL, + match_presence_out boolean NOT NULL +); + +CREATE TABLE private_storage ( + username text NOT NULL, + server_host text NOT NULL, + namespace text NOT NULL, + data text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host, username, namespace) +); + +CREATE INDEX i_private_storage_sh_username ON private_storage (server_host, username); + + +CREATE TABLE roster_version ( + username text NOT NULL, + server_host text NOT NULL, + version text NOT NULL, + PRIMARY KEY (server_host, username) +); + +CREATE TABLE pubsub_node ( + host text NOT NULL, + node text NOT NULL, + parent text NOT NULL DEFAULT '', + plugin text NOT NULL, + nodeid INTEGER PRIMARY KEY AUTOINCREMENT +); +CREATE INDEX i_pubsub_node_parent ON pubsub_node (parent); +CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node (host, node); + +CREATE TABLE pubsub_node_option ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + name text NOT NULL, + val text NOT NULL +); +CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option (nodeid); + +CREATE TABLE pubsub_node_owner ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + owner text NOT NULL +); +CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner (nodeid); + +CREATE TABLE pubsub_state ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + jid text NOT NULL, + affiliation character(1), + subscriptions text NOT NULL DEFAULT '', + stateid INTEGER PRIMARY KEY AUTOINCREMENT +); +CREATE INDEX i_pubsub_state_jid ON pubsub_state (jid); +CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state (nodeid, jid); + +CREATE TABLE pubsub_item ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + itemid text NOT NULL, + publisher text NOT NULL, + creation varchar(32) NOT NULL, + modification varchar(32) NOT NULL, + payload text NOT NULL DEFAULT '' +); +CREATE INDEX i_pubsub_item_itemid ON pubsub_item (itemid); +CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item (nodeid, itemid); + + CREATE TABLE pubsub_subscription_opt ( + subid text NOT NULL, + opt_name varchar(32), + opt_value text NOT NULL +); +CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt (subid, opt_name); + +CREATE TABLE muc_room ( + name text NOT NULL, + server_host text NOT NULL, + host text NOT NULL, + opts text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room (name, host); + +CREATE TABLE muc_registered ( + jid text NOT NULL, + host text NOT NULL, + server_host text NOT NULL, + nick text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_muc_registered_nick ON muc_registered (nick); +CREATE UNIQUE INDEX i_muc_registered_jid_host ON muc_registered (jid, host); + +CREATE TABLE muc_online_room ( + name text NOT NULL, + host text NOT NULL, + server_host text NOT NULL, + node text NOT NULL, + pid text NOT NULL +); + +CREATE UNIQUE INDEX i_muc_online_room_name_host ON muc_online_room (name, host); + +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, + server_host text NOT NULL, + node text NOT NULL +); + +CREATE UNIQUE INDEX i_muc_online_users ON muc_online_users (username, server, resource, name, host); +CREATE INDEX i_muc_online_users_us ON muc_online_users (username, server); + +CREATE TABLE muc_room_subscribers ( + room text NOT NULL, + host text NOT NULL, + jid text NOT NULL, + nick text NOT NULL, + nodes text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_muc_room_subscribers_host_jid ON muc_room_subscribers(host, jid); +CREATE UNIQUE INDEX i_muc_room_subscribers_host_room_jid ON muc_room_subscribers(host, room, jid); + +CREATE TABLE motd ( + username text NOT NULL, + server_host text NOT NULL, + xml text, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host, username) +); + +CREATE TABLE caps_features ( + node text NOT NULL, + subnode text NOT NULL, + feature text, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_caps_features_node_subnode ON caps_features (node, subnode); + +CREATE TABLE sm ( + usec bigint NOT NULL, + pid text NOT NULL, + node text NOT NULL, + username text NOT NULL, + server_host text NOT NULL, + resource text NOT NULL, + priority text NOT NULL, + info text NOT NULL, + PRIMARY KEY (usec, pid) +); + +CREATE INDEX i_sm_node ON sm(node); +CREATE INDEX i_sm_sh_username ON sm (server_host, username); + +CREATE TABLE oauth_token ( + token text NOT NULL PRIMARY KEY, + jid text NOT NULL, + scope text NOT NULL, + expire bigint NOT NULL +); + +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 +); + +CREATE UNIQUE INDEX i_route ON route(domain, server_host, node, pid); +CREATE INDEX i_route_domain ON route(domain); + +CREATE TABLE bosh ( + sid text NOT NULL, + node text NOT NULL, + pid text NOT NULL +); + +CREATE UNIQUE INDEX i_bosh_sid ON bosh(sid); + +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 +); + +CREATE UNIQUE INDEX i_proxy65_sid ON proxy65 (sid); +CREATE INDEX i_proxy65_jid ON proxy65 (jid_i); + +CREATE TABLE push_session ( + username text NOT NULL, + server_host text NOT NULL, + timestamp bigint NOT NULL, + service text NOT NULL, + node text NOT NULL, + xml text NOT NULL, + PRIMARY KEY (server_host, username, timestamp) +); + +CREATE UNIQUE INDEX i_push_session_susn ON push_session (server_host, username, service, node); + +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 +); + +CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel, service); +CREATE INDEX i_mix_channel_serv ON mix_channel (service); + +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 +); + +CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel, service, username, domain); +CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel, service); + +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 +); + +CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel, service, username, domain, node); +CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel, service, username, domain); +CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel, service, node); +CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel, service); + +CREATE TABLE mix_pam ( + username text NOT NULL, + server_host text NOT NULL, + channel text NOT NULL, + service text NOT NULL, + id text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username, server_host, channel, service); +CREATE INDEX i_mix_pam_us ON mix_pam (username, server_host); + +CREATE TABLE mqtt_pub ( + username text NOT NULL, + server_host text NOT NULL, + resource text NOT NULL, + topic text NOT NULL, + qos smallint NOT NULL, + payload blob NOT NULL, + payload_format smallint NOT NULL, + content_type text NOT NULL, + response_topic text NOT NULL, + correlation_data blob NOT NULL, + user_properties blob NOT NULL, + expiry bigint NOT NULL +); + +CREATE UNIQUE INDEX i_mqtt_topic_server ON mqtt_pub (topic, server_host); diff --git a/sql/lite.sql b/sql/lite.sql index aacea11e7..0b6bb12c1 100644 --- a/sql/lite.sql +++ b/sql/lite.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 @@ -41,7 +41,7 @@ CREATE TABLE rosterusers ( ask character(1) NOT NULL, askmessage text NOT NULL, server character(1) NOT NULL, - subscribe text, + subscribe text NOT NULL, type text, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); @@ -84,16 +84,35 @@ CREATE TABLE spool ( CREATE INDEX i_despool ON spool (username); +CREATE TABLE archive ( + username text NOT NULL, + timestamp BIGINT UNSIGNED NOT NULL, + peer text NOT NULL, + bare_peer text NOT NULL, + xml text NOT NULL, + txt text, + id INTEGER PRIMARY KEY AUTOINCREMENT, + kind text, + nick text, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); -CREATE TABLE vcard ( - username text PRIMARY KEY, - vcard text NOT NULL, - created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +CREATE INDEX i_username_timestamp ON archive(username, timestamp); +CREATE INDEX i_archive_username_peer ON archive (username, peer); +CREATE INDEX i_archive_username_bare_peer ON archive (username, bare_peer); +CREATE INDEX i_timestamp ON archive(timestamp); + +CREATE TABLE archive_prefs ( + username text NOT NULL PRIMARY KEY, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); -CREATE TABLE vcard_xupdate ( +CREATE TABLE vcard ( username text PRIMARY KEY, - hash text NOT NULL, + vcard text NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); @@ -181,10 +200,10 @@ CREATE TABLE roster_version ( ); CREATE TABLE pubsub_node ( - host text, - node text, - parent text, - type text, + host text NOT NULL, + node text NOT NULL, + parent text NOT NULL DEFAULT '', + plugin text NOT NULL, nodeid INTEGER PRIMARY KEY AUTOINCREMENT ); CREATE INDEX i_pubsub_node_parent ON pubsub_node (parent); @@ -192,22 +211,22 @@ CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node (host, node); CREATE TABLE pubsub_node_option ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - name text, - val text + name text NOT NULL, + val text NOT NULL ); CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option (nodeid); CREATE TABLE pubsub_node_owner ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - owner text + owner text NOT NULL ); CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner (nodeid); CREATE TABLE pubsub_state ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - jid text, + jid text NOT NULL, affiliation character(1), - subscriptions text, + subscriptions text NOT NULL DEFAULT '', stateid INTEGER PRIMARY KEY AUTOINCREMENT ); CREATE INDEX i_pubsub_state_jid ON pubsub_state (jid); @@ -215,19 +234,19 @@ CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state (nodeid, jid); CREATE TABLE pubsub_item ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - 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 text NOT NULL DEFAULT '' ); CREATE INDEX i_pubsub_item_itemid ON pubsub_item (itemid); CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item (nodeid, itemid); CREATE TABLE pubsub_subscription_opt ( - subid text, + subid text NOT NULL, opt_name varchar(32), - opt_value text + opt_value text NOT NULL ); CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt (subid, opt_name); @@ -250,14 +269,38 @@ CREATE TABLE muc_registered ( CREATE INDEX i_muc_registered_nick ON muc_registered (nick); CREATE UNIQUE INDEX i_muc_registered_jid_host ON muc_registered (jid, host); -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 +); + +CREATE UNIQUE INDEX i_muc_online_room_name_host ON muc_online_room (name, host); + +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 ); -CREATE UNIQUE INDEX i_irc_custom_jid_host ON irc_custom (jid, host); +CREATE UNIQUE INDEX i_muc_online_users ON muc_online_users (username, server, resource, name, host); +CREATE INDEX i_muc_online_users_us ON muc_online_users (username, server); + +CREATE TABLE muc_room_subscribers ( + room text NOT NULL, + host text NOT NULL, + jid text NOT NULL, + nick text NOT NULL, + nodes text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_muc_room_subscribers_host_jid ON muc_room_subscribers(host, jid); +CREATE UNIQUE INDEX i_muc_room_subscribers_host_room_jid ON muc_room_subscribers(host, room, jid); CREATE TABLE motd ( username text PRIMARY KEY, @@ -274,32 +317,6 @@ CREATE TABLE caps_features ( CREATE INDEX i_caps_features_node_subnode ON caps_features (node, subnode); -CREATE TABLE archive ( - username text NOT NULL, - timestamp BIGINT UNSIGNED NOT NULL, - peer text NOT NULL, - bare_peer text NOT NULL, - xml text NOT NULL, - txt text, - id INTEGER PRIMARY KEY AUTOINCREMENT, - kind text, - nick text, - created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -); - -CREATE INDEX i_username ON archive(username); -CREATE INDEX i_timestamp ON archive(timestamp); -CREATE INDEX i_peer ON archive(peer); -CREATE INDEX i_bare_peer ON archive(bare_peer); - -CREATE TABLE archive_prefs ( - username text NOT NULL PRIMARY KEY, - def text NOT NULL, - always text NOT NULL, - never text NOT NULL, - created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP -); - CREATE TABLE sm ( usec bigint NOT NULL, pid text NOT NULL, @@ -320,3 +337,121 @@ CREATE TABLE oauth_token ( scope text NOT NULL, expire bigint NOT NULL ); + +CREATE TABLE oauth_client ( + client_id text PRIMARY KEY, + client_name text NOT NULL, + grant_type text NOT NULL, + options text NOT NULL +); + +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 +); + +CREATE UNIQUE INDEX i_route ON route(domain, server_host, node, pid); +CREATE INDEX i_route_domain ON route(domain); + +CREATE TABLE bosh ( + sid text NOT NULL, + node text NOT NULL, + pid text NOT NULL +); + +CREATE UNIQUE INDEX i_bosh_sid ON bosh(sid); + +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 +); + +CREATE UNIQUE INDEX i_proxy65_sid ON proxy65 (sid); +CREATE INDEX i_proxy65_jid ON proxy65 (jid_i); + +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, service, node); +CREATE UNIQUE INDEX i_push_ut ON push_session (username, 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 +); + +CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel, service); +CREATE INDEX i_mix_channel_serv ON mix_channel (service); + +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 +); + +CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel, service, username, domain); +CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel, service); + +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 +); + +CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel, service, username, domain, node); +CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel, service, username, domain); +CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel, service, node); +CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel, service); + +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 +); + +CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username, channel, service); +CREATE INDEX i_mix_pam_us ON mix_pam (username); + +CREATE TABLE mqtt_pub ( + username text NOT NULL, + resource text NOT NULL, + topic text NOT NULL, + qos smallint NOT NULL, + payload blob NOT NULL, + payload_format smallint NOT NULL, + content_type text NOT NULL, + response_topic text NOT NULL, + correlation_data blob NOT NULL, + user_properties blob NOT NULL, + expiry bigint NOT NULL +); + +CREATE UNIQUE INDEX i_mqtt_topic ON mqtt_pub (topic); diff --git a/sql/mssql.sql b/sql/mssql.sql index 0dfaa7161..7c3713b76 100644 --- a/sql/mssql.sql +++ b/sql/mssql.sql @@ -1,5 +1,5 @@ --
--- ejabberd, Copyright (C) 2002-2015 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
@@ -38,16 +38,16 @@ CREATE TABLE [dbo].[archive] ( )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) TEXTIMAGE_ON [PRIMARY];
-CREATE INDEX [archive_username] ON [archive] (username)
+CREATE INDEX [archive_username_timestamp] ON [archive] (username, timestamp)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE INDEX [archive_timestamp] ON [archive] (timestamp)
+CREATE INDEX [archive_username_peer] ON [archive] (username, peer)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE INDEX [archive_peer] ON [archive] (peer)
+CREATE INDEX [archive_username_bare_peer] ON [archive] (username, bare_peer)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE INDEX [archive_bare_peer] ON [archive] (bare_peer)
+CREATE INDEX [archive_timestamp] ON [archive] (timestamp)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[archive_prefs] (
@@ -72,16 +72,6 @@ CREATE TABLE [dbo].[caps_features] ( CREATE CLUSTERED INDEX [caps_features_node_subnode] ON [caps_features] (node, subnode)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE TABLE [dbo].[irc_custom] (
- [jid] [varchar] (255) NOT NULL,
- [host] [varchar] (255) NOT NULL,
- [data] [text] NOT NULL,
- [created_at] [datetime] NOT NULL DEFAULT GETDATE()
-) TEXTIMAGE_ON [PRIMARY];
-
-CREATE UNIQUE CLUSTERED INDEX [irc_custom_jid_host] ON [irc_custom] (jid, host)
-WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-
CREATE TABLE [dbo].[last] (
[username] [varchar] (250) NOT NULL,
[seconds] [text] NOT NULL,
@@ -125,6 +115,42 @@ CREATE TABLE [dbo].[muc_room] ( CREATE UNIQUE CLUSTERED INDEX [muc_room_name_host] ON [muc_room] (name, host)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+CREATE TABLE [dbo].[muc_online_room] (
+ [name] [varchar] (250) NOT NULL,
+ [host] [varchar] (250) NOT NULL,
+ [node] [text] NOT NULL,
+ [pid] [text] NOT NULL
+);
+
+CREATE UNIQUE CLUSTERED INDEX [muc_online_room_name_host] ON [muc_online_room] (name, host)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE TABLE [dbo].[muc_online_users] (
+ [username] [varchar] (250) NOT NULL,
+ [server] [varchar] (250) NOT NULL,
+ [resource] [varchar] (250) NOT NULL,
+ [name] [varchar] (250) NOT NULL,
+ [host] [varchar] (250) NOT NULL,
+ node text NOT NULL
+);
+
+CREATE UNIQUE INDEX [muc_online_users_i] ON [muc_online_users] (username, server, resource, name, host)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+CREATE UNIQUE CLUSTERED INDEX [muc_online_users_us] ON [muc_online_users] (username, server)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE TABLE [dbo].[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] [datetime] NOT NULL DEFAULT GETDATE()
+);
+
+CREATE UNIQUE CLUSTERED INDEX [muc_room_subscribers_host_room_jid] ON [muc_room_subscribers] (host, room, jid);
+CREATE INDEX [muc_room_subscribers_host_jid] ON [muc_room_subscribers] (host, jid);
+
CREATE TABLE [dbo].[privacy_default_list] (
[username] [varchar] (250) NOT NULL,
[name] [varchar] (250) NOT NULL,
@@ -182,11 +208,11 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW CREATE TABLE [dbo].[pubsub_item] (
[nodeid] [bigint] NULL,
- [itemid] [varchar] (255) NULL,
- [publisher] [text] NULL,
- [creation] [text] NULL,
- [modification] [varchar] (255) NULL,
- [payload] [text] NULL
+ [itemid] [varchar] (255) NOT NULL,
+ [publisher] [text] NOT NULL,
+ [creation] [varchar] (32) NOT NULL,
+ [modification] [varchar] (32) NOT NULL,
+ [payload] [text] NOT NULL DEFAULT ''
) TEXTIMAGE_ON [PRIMARY];
CREATE INDEX [pubsub_item_itemid] ON [pubsub_item] (itemid)
@@ -197,8 +223,8 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW CREATE TABLE [dbo].[pubsub_node_option] (
[nodeid] [bigint] NULL,
- [name] [text] NULL,
- [val] [text] NULL
+ [name] [text] NOT NULL,
+ [val] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE CLUSTERED INDEX [pubsub_node_option_nodeid] ON [pubsub_node_option] (nodeid)
@@ -206,7 +232,7 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW CREATE TABLE [dbo].[pubsub_node_owner] (
[nodeid] [bigint] NULL,
- [owner] [text] NULL
+ [owner] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE CLUSTERED INDEX [pubsub_node_owner_nodeid] ON [pubsub_node_owner] (nodeid)
@@ -214,9 +240,9 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW CREATE TABLE [dbo].[pubsub_state] (
[nodeid] [bigint] NULL,
- [jid] [varchar] (255) NULL,
- [affiliation] [char] (1) NULL,
- [subscriptions] [text] NULL,
+ [jid] [varchar] (255) NOT NULL,
+ [affiliation] [char] (1) NOT NULL,
+ [subscriptions] [text] NOT NULL DEFAULT '',
[stateid] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pubsub_state_PRIMARY] PRIMARY KEY CLUSTERED
(
@@ -231,19 +257,19 @@ CREATE UNIQUE INDEX [pubsub_state_nodeid_jid] ON [pubsub_state] (nodeid, jid) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[pubsub_subscription_opt] (
- [subid] [varchar] (255) NULL,
- [opt_name] [varchar] (32) NULL,
- [opt_value] [text] NULL
+ [subid] [varchar] (255) NOT NULL,
+ [opt_name] [varchar] (32) NOT NULL,
+ [opt_value] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [pubsub_subscription_opt_subid_opt_name] ON [pubsub_subscription_opt] (subid, opt_name)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[pubsub_node] (
- [host] [varchar] (255) NULL,
- [node] [varchar] (255) NULL,
- [parent] [varchar] (255) NULL,
- [type] [text] NULL,
+ [host] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [parent] [varchar] (255) NOT NULL DEFAULT '',
+ [plugin] [text] NOT NULL,
[nodeid] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pubsub_node_PRIMARY] PRIMARY KEY CLUSTERED
(
@@ -446,16 +472,6 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW CREATE INDEX [vcard_search_lorgunit] ON [vcard_search] (lorgunit)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE TABLE [dbo].[vcard_xupdate] (
- [username] [varchar] (250) NOT NULL,
- [hash] [text] NOT NULL,
- [created_at] [datetime] NOT NULL DEFAULT GETDATE(),
- CONSTRAINT [vcard_xupdate_PRIMARY] PRIMARY KEY CLUSTERED
-(
- [username] ASC
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-) TEXTIMAGE_ON [PRIMARY];
-
ALTER TABLE [dbo].[pubsub_item] WITH CHECK ADD CONSTRAINT [pubsub_item_ibfk_1] FOREIGN KEY([nodeid])
REFERENCES [dbo].[pubsub_node] ([nodeid])
ON DELETE CASCADE;
@@ -490,3 +506,41 @@ CREATE TABLE [dbo].[oauth_token] ( [token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) TEXTIMAGE_ON [PRIMARY];
+
+CREATE TABLE [dbo].[route] (
+ [domain] [varchar] (255) NOT NULL,
+ [server_host] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [pid] [varchar](100) NOT NULL,
+ [local_hint] [text] NOT NULL
+);
+
+CREATE UNIQUE CLUSTERED INDEX [route_i] ON [route] (domain, server_host, node, pid)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE INDEX [route_domain] ON [route] (domain)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE TABLE [dbo].[bosh] (
+ [sid] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [pid] [varchar](100) NOT NULL
+ CONSTRAINT [bosh_PRIMARY] PRIMARY KEY CLUSTERED
+(
+ [sid] ASC
+)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
+);
+
+CREATE TABLE [dbo].[push_session] (
+ [username] [varchar] (255) NOT NULL,
+ [timestamp] [bigint] NOT NULL,
+ [service] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [xml] [varchar] (255) NOT NULL
+);
+
+CREATE UNIQUE CLUSTERED INDEX [i_push_usn] ON [push_session] (username, service, node)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE UNIQUE INDEX [i_push_ut] ON [push_session] (username, timestamp)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
diff --git a/sql/mysql.new.sql b/sql/mysql.new.sql new file mode 100644 index 000000000..15843fc7d --- /dev/null +++ b/sql/mysql.new.sql @@ -0,0 +1,505 @@ +-- +-- 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 +-- published by the Free Software Foundation; either version 2 of the +-- License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +-- General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along +-- with this program; if not, write to the Free Software Foundation, Inc., +-- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +-- + +CREATE TABLE users ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + password text NOT NULL, + serverkey varchar(64) NOT NULL DEFAULT '', + salt varchar(64) NOT NULL DEFAULT '', + iterationcount integer NOT NULL DEFAULT 0, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host(191), username) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +-- Add support for SCRAM auth to a database created before ejabberd 16.03: +-- ALTER TABLE users ADD COLUMN serverkey varchar(64) NOT NULL DEFAULT ''; +-- ALTER TABLE users ADD COLUMN salt varchar(64) NOT NULL DEFAULT ''; +-- ALTER TABLE users ADD COLUMN iterationcount integer NOT NULL DEFAULT 0; + +CREATE TABLE last ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + seconds text NOT NULL, + state text NOT NULL, + PRIMARY KEY (server_host(191), username) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +CREATE TABLE rosterusers ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + jid varchar(191) NOT NULL, + nick text NOT NULL, + subscription character(1) NOT NULL, + ask character(1) NOT NULL, + askmessage text NOT NULL, + server character(1) NOT NULL, + subscribe text NOT NULL, + type text, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_rosteru_sh_user_jid ON rosterusers(server_host(191), username(75), jid(75)); +CREATE INDEX i_rosteru_sh_username ON rosterusers(server_host(191), username); +CREATE INDEX i_rosteru_sh_jid ON rosterusers(server_host(191), jid); + +CREATE TABLE rostergroups ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + jid varchar(191) NOT NULL, + grp text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_rosterg_sh_user_jid ON rostergroups(server_host(191), username(75), jid(75)); + +CREATE TABLE sr_group ( + name varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + opts text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host(191), name) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE sr_user ( + jid varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + grp varchar(191) NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host(191), jid, grp) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_sr_user_sh_jid ON sr_user(server_host(191), jid); +CREATE INDEX i_sr_user_sh_grp ON sr_user(server_host(191), grp); + +CREATE TABLE spool ( + username varchar(191) NOT NULL, + server_host varchar(191) 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; + +CREATE INDEX i_spool_sh_username USING BTREE ON spool(server_host(191), username); +CREATE INDEX i_spool_created_at USING BTREE ON spool(created_at); + +CREATE TABLE archive ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + timestamp BIGINT UNSIGNED NOT NULL, + peer varchar(191) NOT NULL, + bare_peer varchar(191) NOT NULL, + xml mediumtext NOT NULL, + txt mediumtext, + id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, + kind varchar(10), + nick varchar(191), + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE FULLTEXT INDEX i_text ON archive(txt); +CREATE INDEX i_archive_sh_username_timestamp USING BTREE ON archive(server_host(191), username(191), timestamp); +CREATE INDEX i_archive_sh_username_peer USING BTREE ON archive(server_host(191), username(191), peer(191)); +CREATE INDEX i_archive_sh_username_bare_peer USING BTREE ON archive(server_host(191), username(191), bare_peer(191)); +CREATE INDEX i_archive_sh_timestamp USING BTREE ON archive(server_host(191), timestamp); + +CREATE TABLE archive_prefs ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host(191), username) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE vcard ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + vcard mediumtext NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host(191), username) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE vcard_search ( + username varchar(191) NOT NULL, + lusername varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + fn text NOT NULL, + lfn varchar(191) NOT NULL, + family text NOT NULL, + lfamily varchar(191) NOT NULL, + given text NOT NULL, + lgiven varchar(191) NOT NULL, + middle text NOT NULL, + lmiddle varchar(191) NOT NULL, + nickname text NOT NULL, + lnickname varchar(191) NOT NULL, + bday text NOT NULL, + lbday varchar(191) NOT NULL, + ctry text NOT NULL, + lctry varchar(191) NOT NULL, + locality text NOT NULL, + llocality varchar(191) NOT NULL, + email text NOT NULL, + lemail varchar(191) NOT NULL, + orgname text NOT NULL, + lorgname varchar(191) NOT NULL, + orgunit text NOT NULL, + lorgunit varchar(191) NOT NULL, + PRIMARY KEY (server_host(191), lusername) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_vcard_search_sh_lfn ON vcard_search(server_host(191), lfn); +CREATE INDEX i_vcard_search_sh_lfamily ON vcard_search(server_host(191), lfamily); +CREATE INDEX i_vcard_search_sh_lgiven ON vcard_search(server_host(191), lgiven); +CREATE INDEX i_vcard_search_sh_lmiddle ON vcard_search(server_host(191), lmiddle); +CREATE INDEX i_vcard_search_sh_lnickname ON vcard_search(server_host(191), lnickname); +CREATE INDEX i_vcard_search_sh_lbday ON vcard_search(server_host(191), lbday); +CREATE INDEX i_vcard_search_sh_lctry ON vcard_search(server_host(191), lctry); +CREATE INDEX i_vcard_search_sh_llocality ON vcard_search(server_host(191), llocality); +CREATE INDEX i_vcard_search_sh_lemail ON vcard_search(server_host(191), lemail); +CREATE INDEX i_vcard_search_sh_lorgname ON vcard_search(server_host(191), lorgname); +CREATE INDEX i_vcard_search_sh_lorgunit ON vcard_search(server_host(191), lorgunit); + +CREATE TABLE privacy_default_list ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + name varchar(191) NOT NULL, + PRIMARY KEY (server_host(191), username) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE privacy_list ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + name varchar(191) NOT NULL, + id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_privacy_list_sh_username USING BTREE ON privacy_list(server_host(191), username); +CREATE UNIQUE INDEX i_privacy_list_sh_username_name USING BTREE ON privacy_list (server_host(191), username(75), name(75)); + +CREATE TABLE privacy_list_data ( + id bigint, + t character(1) NOT NULL, + value text NOT NULL, + action character(1) NOT NULL, + ord NUMERIC NOT NULL, + match_all boolean NOT NULL, + match_iq boolean NOT NULL, + match_message boolean NOT NULL, + match_presence_in boolean NOT NULL, + match_presence_out boolean NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_privacy_list_data_id ON privacy_list_data(id); + +CREATE TABLE private_storage ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + namespace varchar(191) NOT NULL, + data text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host(191), username, namespace) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_private_storage_sh_username USING BTREE ON private_storage(server_host(191), username); + +-- Not tested in mysql +CREATE TABLE roster_version ( + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + version text NOT NULL, + PRIMARY KEY (server_host(191), username) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +-- To update from 1.x: +-- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask; +-- UPDATE rosterusers SET askmessage = ''; +-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL; + +CREATE TABLE pubsub_node ( + 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(71), node(120)); + +CREATE TABLE pubsub_node_option ( + nodeid bigint, + 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 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 NOT NULL, + affiliation character(1), + 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)); +CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state(nodeid, jid(60)); +ALTER TABLE `pubsub_state` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE; + +CREATE TABLE pubsub_item ( + nodeid bigint, + 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 NOT NULL, + opt_name varchar(32), + 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)); + +CREATE TABLE muc_room ( + name text NOT NULL, + host text NOT NULL, + server_host varchar(191) NOT NULL, + opts mediumtext NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_muc_room_name_host USING BTREE ON muc_room(name(75), host(75)); + +CREATE TABLE muc_registered ( + jid text NOT NULL, + host text NOT NULL, + server_host varchar(191) NOT NULL, + nick text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +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 muc_online_room ( + name text NOT NULL, + host text NOT NULL, + server_host varchar(191) NOT NULL, + 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, + server_host varchar(191) NOT NULL, + node text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +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) NOT NULL, + server_host varchar(191) NOT NULL, + xml text, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (server_host(191), username) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE caps_features ( + node varchar(191) NOT NULL, + subnode varchar(191) NOT NULL, + feature text, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_caps_features_node_subnode ON caps_features(node(75), subnode(75)); + +CREATE TABLE sm ( + usec bigint NOT NULL, + pid text NOT NULL, + node text NOT NULL, + username varchar(191) NOT NULL, + server_host varchar(191) NOT NULL, + resource varchar(191) NOT NULL, + priority text NOT NULL, + info text NOT NULL, + PRIMARY KEY (usec, pid(75)) +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_sm_node ON sm(node(75)); +CREATE INDEX i_sm_sh_username ON sm(server_host(191), username); + +CREATE TABLE oauth_token ( + token varchar(191) NOT NULL PRIMARY KEY, + jid text NOT NULL, + 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 varchar(191) 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, + server_host varchar(191) NOT NULL, + timestamp bigint NOT NULL, + service text NOT NULL, + node text NOT NULL, + xml text NOT NULL, + PRIMARY KEY (server_host(191), username(191), timestamp) +); + +CREATE UNIQUE INDEX i_push_session_susn ON push_session (server_host(191), username(191), service(191), node(191)); + +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, + server_host varchar(191) 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), server_host(191), channel(191), service(191)); +CREATE INDEX i_mix_pam_us ON mix_pam (username(191), server_host(191)); + +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), server_host) +); 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)) +); diff --git a/sql/pg.new.sql b/sql/pg.new.sql new file mode 100644 index 000000000..59fdfc75d --- /dev/null +++ b/sql/pg.new.sql @@ -0,0 +1,651 @@ +-- +-- 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 +-- published by the Free Software Foundation; either version 2 of the +-- License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +-- General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along +-- with this program; if not, write to the Free Software Foundation, Inc., +-- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +-- + +-- To update from the old schema, replace <HOST> with the host's domain: + +-- ALTER TABLE users ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE users DROP CONSTRAINT users_pkey; +-- ALTER TABLE users ADD PRIMARY KEY (server_host, username); +-- ALTER TABLE users ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE last ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE last DROP CONSTRAINT last_pkey; +-- ALTER TABLE last ADD PRIMARY KEY (server_host, username); +-- ALTER TABLE last ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE rosterusers ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX i_rosteru_user_jid; +-- DROP INDEX i_rosteru_username; +-- DROP INDEX i_rosteru_jid; +-- CREATE UNIQUE INDEX i_rosteru_sh_user_jid ON rosterusers USING btree (server_host, username, jid); +-- CREATE INDEX i_rosteru_sh_username ON rosterusers USING btree (server_host, username); +-- CREATE INDEX i_rosteru_sh_jid ON rosterusers USING btree (server_host, jid); +-- ALTER TABLE rosterusers ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE rostergroups ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX pk_rosterg_user_jid; +-- CREATE INDEX i_rosterg_sh_user_jid ON rostergroups USING btree (server_host, username, jid); +-- ALTER TABLE rostergroups ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE sr_group ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE sr_group ADD PRIMARY KEY (server_host, name); +-- ALTER TABLE sr_group ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE sr_user ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX i_sr_user_jid_grp; +-- DROP INDEX i_sr_user_jid; +-- DROP INDEX i_sr_user_grp; +-- ALTER TABLE sr_user ADD PRIMARY KEY (server_host, jid, grp); +-- CREATE INDEX i_sr_user_sh_jid ON sr_user USING btree (server_host, jid); +-- CREATE INDEX i_sr_user_sh_grp ON sr_user USING btree (server_host, grp); +-- ALTER TABLE sr_user ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE spool ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX i_despool; +-- CREATE INDEX i_spool_sh_username ON spool USING btree (server_host, username); +-- ALTER TABLE spool ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE archive ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX i_username_timestamp; +-- DROP INDEX i_username_peer; +-- DROP INDEX i_username_bare_peer; +-- DROP INDEX i_timestamp; +-- CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp); +-- CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer); +-- CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer); +-- CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp); +-- ALTER TABLE archive ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE archive_prefs ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE archive_prefs DROP CONSTRAINT archive_prefs_pkey; +-- ALTER TABLE archive_prefs ADD PRIMARY KEY (server_host, username); +-- ALTER TABLE archive_prefs ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE vcard ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE vcard DROP CONSTRAINT vcard_pkey; +-- ALTER TABLE vcard ADD PRIMARY KEY (server_host, username); +-- ALTER TABLE vcard ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE vcard_search ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE vcard_search DROP CONSTRAINT vcard_search_pkey; +-- DROP INDEX i_vcard_search_lfn; +-- DROP INDEX i_vcard_search_lfamily; +-- DROP INDEX i_vcard_search_lgiven; +-- DROP INDEX i_vcard_search_lmiddle; +-- DROP INDEX i_vcard_search_lnickname; +-- DROP INDEX i_vcard_search_lbday; +-- DROP INDEX i_vcard_search_lctry; +-- DROP INDEX i_vcard_search_llocality; +-- DROP INDEX i_vcard_search_lemail; +-- DROP INDEX i_vcard_search_lorgname; +-- DROP INDEX i_vcard_search_lorgunit; +-- ALTER TABLE vcard_search ADD PRIMARY KEY (server_host, username); +-- CREATE INDEX i_vcard_search_sh_lfn ON vcard_search(server_host, lfn); +-- CREATE INDEX i_vcard_search_sh_lfamily ON vcard_search(server_host, lfamily); +-- CREATE INDEX i_vcard_search_sh_lgiven ON vcard_search(server_host, lgiven); +-- CREATE INDEX i_vcard_search_sh_lmiddle ON vcard_search(server_host, lmiddle); +-- CREATE INDEX i_vcard_search_sh_lnickname ON vcard_search(server_host, lnickname); +-- CREATE INDEX i_vcard_search_sh_lbday ON vcard_search(server_host, lbday); +-- CREATE INDEX i_vcard_search_sh_lctry ON vcard_search(server_host, lctry); +-- CREATE INDEX i_vcard_search_sh_llocality ON vcard_search(server_host, llocality); +-- CREATE INDEX i_vcard_search_sh_lemail ON vcard_search(server_host, lemail); +-- CREATE INDEX i_vcard_search_sh_lorgname ON vcard_search(server_host, lorgname); +-- CREATE INDEX i_vcard_search_sh_lorgunit ON vcard_search(server_host, lorgunit); +-- ALTER TABLE vcard_search ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE privacy_default_list ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE privacy_default_list DROP CONSTRAINT privacy_default_list_pkey; +-- ALTER TABLE privacy_default_list ADD PRIMARY KEY (server_host, username); +-- ALTER TABLE privacy_default_list ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE privacy_list ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX i_privacy_list_username; +-- DROP INDEX i_privacy_list_username_name; +-- CREATE INDEX i_privacy_list_sh_username ON privacy_list USING btree (server_host, username); +-- CREATE UNIQUE INDEX i_privacy_list_sh_username_name ON privacy_list USING btree (server_host, username, name); +-- ALTER TABLE privacy_list ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE private_storage ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX i_private_storage_username; +-- DROP INDEX i_private_storage_username_namespace; +-- ALTER TABLE private_storage ADD PRIMARY KEY (server_host, username, namespace); +-- CREATE INDEX i_private_storage_sh_username ON private_storage USING btree (server_host, username); +-- ALTER TABLE private_storage ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE roster_version ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE roster_version DROP CONSTRAINT roster_version_pkey; +-- ALTER TABLE roster_version ADD PRIMARY KEY (server_host, username); +-- ALTER TABLE roster_version ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE muc_room ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE muc_room ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE muc_registered ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE muc_registered ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE muc_online_room ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE muc_online_room ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE muc_online_users ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE muc_online_users ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE motd ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- ALTER TABLE motd DROP CONSTRAINT motd_pkey; +-- ALTER TABLE motd ADD PRIMARY KEY (server_host, username); +-- ALTER TABLE motd ALTER COLUMN server_host DROP DEFAULT; + +-- ALTER TABLE sm ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>'; +-- DROP INDEX i_sm_sid; +-- DROP INDEX i_sm_username; +-- ALTER TABLE sm ADD PRIMARY KEY (usec, pid); +-- CREATE INDEX i_sm_sh_username ON sm USING btree (server_host, username); +-- ALTER TABLE sm ALTER COLUMN server_host DROP DEFAULT; + + +CREATE TABLE users ( + username text NOT NULL, + server_host text NOT NULL, + "password" text NOT NULL, + serverkey text NOT NULL DEFAULT '', + salt text NOT NULL DEFAULT '', + iterationcount integer NOT NULL DEFAULT 0, + created_at TIMESTAMP NOT NULL DEFAULT now(), + PRIMARY KEY (server_host, username) +); + +-- Add support for SCRAM auth to a database created before ejabberd 16.03: +-- ALTER TABLE users ADD COLUMN serverkey text NOT NULL DEFAULT ''; +-- ALTER TABLE users ADD COLUMN salt text NOT NULL DEFAULT ''; +-- ALTER TABLE users ADD COLUMN iterationcount integer NOT NULL DEFAULT 0; + +CREATE TABLE last ( + username text NOT NULL, + server_host text NOT NULL, + seconds text NOT NULL, + state text NOT NULL, + PRIMARY KEY (server_host, username) +); + + +CREATE TABLE rosterusers ( + username text NOT NULL, + server_host text NOT NULL, + jid text NOT NULL, + nick text NOT NULL, + subscription character(1) NOT NULL, + ask character(1) NOT NULL, + askmessage text NOT NULL, + server character(1) NOT NULL, + subscribe text NOT NULL, + "type" text, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE UNIQUE INDEX i_rosteru_sh_user_jid ON rosterusers USING btree (server_host, username, jid); +CREATE INDEX i_rosteru_sh_username ON rosterusers USING btree (server_host, username); +CREATE INDEX i_rosteru_sh_jid ON rosterusers USING btree (server_host, jid); + + +CREATE TABLE rostergroups ( + username text NOT NULL, + server_host text NOT NULL, + jid text NOT NULL, + grp text NOT NULL +); + +CREATE INDEX i_rosterg_sh_user_jid ON rostergroups USING btree (server_host, username, jid); + +CREATE TABLE sr_group ( + name text NOT NULL, + server_host text NOT NULL, + opts text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now(), + PRIMARY KEY (server_host, name) +); + +CREATE TABLE sr_user ( + jid text NOT NULL, + server_host text NOT NULL, + grp text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now(), + PRIMARY KEY (server_host, jid, grp) +); + +CREATE INDEX i_sr_user_sh_jid ON sr_user USING btree (server_host, jid); +CREATE INDEX i_sr_user_sh_grp ON sr_user USING btree (server_host, grp); + +CREATE TABLE spool ( + username text NOT NULL, + server_host text NOT NULL, + xml text NOT NULL, + seq SERIAL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_spool_sh_username ON spool USING btree (server_host, username); + +CREATE TABLE archive ( + username text NOT NULL, + server_host text NOT NULL, + timestamp BIGINT NOT NULL, + peer text NOT NULL, + bare_peer text NOT NULL, + xml text NOT NULL, + txt text, + id SERIAL, + kind text, + nick text, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp); +CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer); +CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer); +CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp); + +CREATE TABLE archive_prefs ( + username text NOT NULL, + server_host text NOT NULL, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now(), + PRIMARY KEY (server_host, username) +); + +CREATE TABLE vcard ( + username text NOT NULL, + server_host text NOT NULL, + vcard text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now(), + PRIMARY KEY (server_host, username) +); + +CREATE TABLE vcard_search ( + username text NOT NULL, + lusername text NOT NULL, + server_host text NOT NULL, + fn text NOT NULL, + lfn text NOT NULL, + "family" text NOT NULL, + lfamily text NOT NULL, + given text NOT NULL, + lgiven text NOT NULL, + middle text NOT NULL, + lmiddle text NOT NULL, + nickname text NOT NULL, + lnickname text NOT NULL, + bday text NOT NULL, + lbday text NOT NULL, + ctry text NOT NULL, + lctry text NOT NULL, + locality text NOT NULL, + llocality text NOT NULL, + email text NOT NULL, + lemail text NOT NULL, + orgname text NOT NULL, + lorgname text NOT NULL, + orgunit text NOT NULL, + lorgunit text NOT NULL, + PRIMARY KEY (server_host, username) +); + +CREATE INDEX i_vcard_search_sh_lfn ON vcard_search(server_host, lfn); +CREATE INDEX i_vcard_search_sh_lfamily ON vcard_search(server_host, lfamily); +CREATE INDEX i_vcard_search_sh_lgiven ON vcard_search(server_host, lgiven); +CREATE INDEX i_vcard_search_sh_lmiddle ON vcard_search(server_host, lmiddle); +CREATE INDEX i_vcard_search_sh_lnickname ON vcard_search(server_host, lnickname); +CREATE INDEX i_vcard_search_sh_lbday ON vcard_search(server_host, lbday); +CREATE INDEX i_vcard_search_sh_lctry ON vcard_search(server_host, lctry); +CREATE INDEX i_vcard_search_sh_llocality ON vcard_search(server_host, llocality); +CREATE INDEX i_vcard_search_sh_lemail ON vcard_search(server_host, lemail); +CREATE INDEX i_vcard_search_sh_lorgname ON vcard_search(server_host, lorgname); +CREATE INDEX i_vcard_search_sh_lorgunit ON vcard_search(server_host, lorgunit); + +CREATE TABLE privacy_default_list ( + username text NOT NULL, + server_host text NOT NULL, + name text NOT NULL, + PRIMARY KEY (server_host, username) +); + +CREATE TABLE privacy_list ( + username text NOT NULL, + server_host text NOT NULL, + name text NOT NULL, + id SERIAL UNIQUE, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_privacy_list_sh_username ON privacy_list USING btree (server_host, username); +CREATE UNIQUE INDEX i_privacy_list_sh_username_name ON privacy_list USING btree (server_host, username, name); + +CREATE TABLE privacy_list_data ( + id bigint REFERENCES privacy_list(id) ON DELETE CASCADE, + t character(1) NOT NULL, + value text NOT NULL, + action character(1) NOT NULL, + ord NUMERIC NOT NULL, + match_all boolean NOT NULL, + match_iq boolean NOT NULL, + match_message boolean NOT NULL, + match_presence_in boolean NOT NULL, + match_presence_out boolean NOT NULL +); + +CREATE INDEX i_privacy_list_data_id ON privacy_list_data USING btree (id); + +CREATE TABLE private_storage ( + username text NOT NULL, + server_host text NOT NULL, + namespace text NOT NULL, + data text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now(), + PRIMARY KEY (server_host, username, namespace) +); + +CREATE INDEX i_private_storage_sh_username ON private_storage USING btree (server_host, username); + + +CREATE TABLE roster_version ( + username text NOT NULL, + server_host text NOT NULL, + version text NOT NULL, + PRIMARY KEY (server_host, username) +); + +-- To update from 0.9.8: +-- CREATE SEQUENCE spool_seq_seq; +-- ALTER TABLE spool ADD COLUMN seq integer; +-- ALTER TABLE spool ALTER COLUMN seq SET DEFAULT nextval('spool_seq_seq'); +-- UPDATE spool SET seq = DEFAULT; +-- ALTER TABLE spool ALTER COLUMN seq SET NOT NULL; + +-- To update from 1.x: +-- ALTER TABLE rosterusers ADD COLUMN askmessage text; +-- UPDATE rosterusers SET askmessage = ''; +-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL; + +CREATE TABLE pubsub_node ( + host text NOT NULL, + node text NOT NULL, + parent text NOT NULL DEFAULT '', + plugin text NOT NULL, + nodeid SERIAL UNIQUE +); +CREATE INDEX i_pubsub_node_parent ON pubsub_node USING btree (parent); +CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node USING btree (host, node); + +CREATE TABLE pubsub_node_option ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + name text NOT NULL, + val text NOT NULL +); +CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option USING btree (nodeid); + +CREATE TABLE pubsub_node_owner ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + owner text NOT NULL +); +CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner USING btree (nodeid); + +CREATE TABLE pubsub_state ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + jid text NOT NULL, + affiliation character(1), + subscriptions text NOT NULL DEFAULT '', + stateid SERIAL UNIQUE +); +CREATE INDEX i_pubsub_state_jid ON pubsub_state USING btree (jid); +CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state USING btree (nodeid, jid); + +CREATE TABLE pubsub_item ( + nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, + itemid text NOT NULL, + publisher text NOT NULL, + creation varchar(32) NOT NULL, + modification varchar(32) NOT NULL, + payload text NOT NULL DEFAULT '' +); +CREATE INDEX i_pubsub_item_itemid ON pubsub_item USING btree (itemid); +CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item USING btree (nodeid, itemid); + +CREATE TABLE pubsub_subscription_opt ( + subid text NOT NULL, + opt_name varchar(32), + opt_value text NOT NULL +); +CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt USING btree (subid, opt_name); + +CREATE TABLE muc_room ( + name text NOT NULL, + host text NOT NULL, + server_host text NOT NULL, + opts text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room USING btree (name, host); + +CREATE TABLE muc_registered ( + jid text NOT NULL, + host text NOT NULL, + server_host text NOT NULL, + nick text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_muc_registered_nick ON muc_registered USING btree (nick); +CREATE UNIQUE INDEX i_muc_registered_jid_host ON muc_registered USING btree (jid, host); + +CREATE TABLE muc_online_room ( + name text NOT NULL, + host text NOT NULL, + server_host text NOT NULL, + node text NOT NULL, + pid text NOT NULL +); + +CREATE UNIQUE INDEX i_muc_online_room_name_host ON muc_online_room USING btree (name, host); + +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, + server_host text NOT NULL, + node text NOT NULL +); + +CREATE UNIQUE INDEX i_muc_online_users ON muc_online_users USING btree (username, server, resource, name, host); +CREATE INDEX i_muc_online_users_us ON muc_online_users USING btree (username, server); + +CREATE TABLE muc_room_subscribers ( + room text NOT NULL, + host text NOT NULL, + jid text NOT NULL, + nick text NOT NULL, + nodes text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_muc_room_subscribers_host_jid ON muc_room_subscribers USING btree (host, jid); +CREATE UNIQUE INDEX i_muc_room_subscribers_host_room_jid ON muc_room_subscribers USING btree (host, room, jid); + +CREATE TABLE motd ( + username text NOT NULL, + server_host text NOT NULL, + xml text, + created_at TIMESTAMP NOT NULL DEFAULT now(), + PRIMARY KEY (server_host, username) +); + +CREATE TABLE caps_features ( + node text NOT NULL, + subnode text NOT NULL, + feature text, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_caps_features_node_subnode ON caps_features USING btree (node, subnode); + +CREATE TABLE sm ( + usec bigint NOT NULL, + pid text NOT NULL, + node text NOT NULL, + username text NOT NULL, + server_host text NOT NULL, + resource text NOT NULL, + priority text NOT NULL, + info text NOT NULL, + PRIMARY KEY (usec, pid) +); + +CREATE INDEX i_sm_node ON sm USING btree (node); +CREATE INDEX i_sm_sh_username ON sm USING btree (server_host, username); + +CREATE TABLE oauth_token ( + token text NOT NULL, + jid text NOT NULL, + scope text NOT NULL, + expire bigint NOT NULL +); + +CREATE UNIQUE INDEX i_oauth_token_token ON oauth_token USING btree (token); + +CREATE TABLE oauth_client ( + client_id text PRIMARY KEY, + client_name text NOT NULL, + grant_type text NOT NULL, + options text NOT NULL +); + +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 +); + +CREATE UNIQUE INDEX i_route ON route USING btree (domain, server_host, node, pid); +CREATE INDEX i_route_domain ON route USING btree (domain); + +CREATE TABLE bosh ( + sid text NOT NULL, + node text NOT NULL, + pid text NOT NULL +); + +CREATE UNIQUE INDEX i_bosh_sid ON bosh USING btree (sid); + +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 +); + +CREATE UNIQUE INDEX i_proxy65_sid ON proxy65 USING btree (sid); +CREATE INDEX i_proxy65_jid ON proxy65 USING btree (jid_i); + +CREATE TABLE push_session ( + username text NOT NULL, + server_host text NOT NULL, + timestamp bigint NOT NULL, + service text NOT NULL, + node text NOT NULL, + xml text NOT NULL, + PRIMARY KEY (server_host, username, timestamp) +); + +CREATE UNIQUE INDEX i_push_session_susn ON push_session USING btree (server_host, username, service, node); + +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 +); + +CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel, service); +CREATE INDEX i_mix_channel_serv ON mix_channel (service); + +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 +); + +CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel, service, username, domain); +CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel, service); + +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 +); + +CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel, service, username, domain, node); +CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel, service, username, domain); +CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel, service, node); +CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel, service); + +CREATE TABLE mix_pam ( + username text NOT NULL, + server_host text NOT NULL, + channel text NOT NULL, + service text NOT NULL, + id text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username, server_host, channel, service); +CREATE INDEX i_mix_pam_us ON mix_pam (username, server_host); + +CREATE TABLE mqtt_pub ( + username text NOT NULL, + server_host text NOT NULL, + resource text NOT NULL, + topic text NOT NULL, + qos smallint NOT NULL, + payload bytea NOT NULL, + payload_format smallint NOT NULL, + content_type text NOT NULL, + response_topic text NOT NULL, + correlation_data bytea NOT NULL, + user_properties bytea NOT NULL, + expiry bigint NOT NULL +); + +CREATE UNIQUE INDEX i_mqtt_topic_server ON mqtt_pub (topic, server_host); diff --git a/sql/pg.sql b/sql/pg.sql index 3d7de4285..2382338cc 100644 --- a/sql/pg.sql +++ b/sql/pg.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 @@ -10,7 +10,7 @@ -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -- General Public License for more details. --- +-- -- You should have received a copy of the GNU General Public License along -- with this program; if not, write to the Free Software Foundation, Inc., -- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. @@ -45,7 +45,7 @@ CREATE TABLE rosterusers ( ask character(1) NOT NULL, askmessage text NOT NULL, server character(1) NOT NULL, - subscribe text, + subscribe text NOT NULL, "type" text, created_at TIMESTAMP NOT NULL DEFAULT now() ); @@ -101,10 +101,10 @@ CREATE TABLE archive ( created_at TIMESTAMP NOT NULL DEFAULT now() ); -CREATE INDEX i_username ON archive USING btree (username); +CREATE INDEX i_username_timestamp ON archive USING btree (username, timestamp); +CREATE INDEX i_username_peer ON archive USING btree (username, peer); +CREATE INDEX i_username_bare_peer ON archive USING btree (username, bare_peer); CREATE INDEX i_timestamp ON archive USING btree (timestamp); -CREATE INDEX i_peer ON archive USING btree (peer); -CREATE INDEX i_bare_peer ON archive USING btree (bare_peer); CREATE TABLE archive_prefs ( username text NOT NULL PRIMARY KEY, @@ -120,18 +120,12 @@ CREATE TABLE vcard ( created_at TIMESTAMP NOT NULL DEFAULT now() ); -CREATE TABLE vcard_xupdate ( - username text PRIMARY KEY, - hash text NOT NULL, - created_at TIMESTAMP NOT NULL DEFAULT now() -); - CREATE TABLE vcard_search ( username text NOT NULL, lusername text PRIMARY KEY, fn text NOT NULL, lfn text NOT NULL, - family text NOT NULL, + "family" text NOT NULL, lfamily text NOT NULL, given text NOT NULL, lgiven text NOT NULL, @@ -224,10 +218,10 @@ 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 text NOT NULL DEFAULT '', + plugin text NOT NULL, nodeid SERIAL UNIQUE ); CREATE INDEX i_pubsub_node_parent ON pubsub_node USING btree (parent); @@ -235,22 +229,22 @@ CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node USING btree (host, node); CREATE TABLE pubsub_node_option ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - name text, - val text + name text NOT NULL, + val text NOT NULL ); CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option USING btree (nodeid); CREATE TABLE pubsub_node_owner ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - owner text + owner text NOT NULL ); CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner USING btree (nodeid); CREATE TABLE pubsub_state ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - jid text, + jid text NOT NULL, affiliation character(1), - subscriptions text, + subscriptions text NOT NULL DEFAULT '', stateid SERIAL UNIQUE ); CREATE INDEX i_pubsub_state_jid ON pubsub_state USING btree (jid); @@ -258,19 +252,19 @@ CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state USING btree (nodeid, ji CREATE TABLE pubsub_item ( nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE, - 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 text NOT NULL DEFAULT '' ); CREATE INDEX i_pubsub_item_itemid ON pubsub_item USING btree (itemid); CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item USING btree (nodeid, itemid); CREATE TABLE pubsub_subscription_opt ( - subid text, + subid text NOT NULL, opt_name varchar(32), - opt_value text + opt_value text NOT NULL ); CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt USING btree (subid, opt_name); @@ -293,14 +287,38 @@ CREATE TABLE muc_registered ( CREATE INDEX i_muc_registered_nick ON muc_registered USING btree (nick); CREATE UNIQUE INDEX i_muc_registered_jid_host ON muc_registered USING btree (jid, host); -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 now() + node text NOT NULL, + pid text NOT NULL +); + +CREATE UNIQUE INDEX i_muc_online_room_name_host ON muc_online_room USING btree (name, host); + +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 +); + +CREATE UNIQUE INDEX i_muc_online_users ON muc_online_users USING btree (username, server, resource, name, host); +CREATE INDEX i_muc_online_users_us ON muc_online_users USING btree (username, server); + +CREATE TABLE muc_room_subscribers ( + room text NOT NULL, + host text NOT NULL, + jid text NOT NULL, + nick text NOT NULL, + nodes text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() ); -CREATE UNIQUE INDEX i_irc_custom_jid_host ON irc_custom USING btree (jid, host); +CREATE INDEX i_muc_room_subscribers_host_jid ON muc_room_subscribers USING btree (host, jid); +CREATE UNIQUE INDEX i_muc_room_subscribers_host_room_jid ON muc_room_subscribers USING btree (host, room, jid); CREATE TABLE motd ( username text PRIMARY KEY, @@ -339,3 +357,121 @@ CREATE TABLE oauth_token ( ); CREATE UNIQUE INDEX i_oauth_token_token ON oauth_token USING btree (token); + +CREATE TABLE oauth_client ( + client_id text PRIMARY KEY, + client_name text NOT NULL, + grant_type text NOT NULL, + options text NOT NULL +); + +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 +); + +CREATE UNIQUE INDEX i_route ON route USING btree (domain, server_host, node, pid); +CREATE INDEX i_route_domain ON route USING btree (domain); + +CREATE TABLE bosh ( + sid text NOT NULL, + node text NOT NULL, + pid text NOT NULL +); + +CREATE UNIQUE INDEX i_bosh_sid ON bosh USING btree (sid); + +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 +); + +CREATE UNIQUE INDEX i_proxy65_sid ON proxy65 USING btree (sid); +CREATE INDEX i_proxy65_jid ON proxy65 USING btree (jid_i); + +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 USING btree (username, service, node); +CREATE UNIQUE INDEX i_push_ut ON push_session USING btree (username, 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 +); + +CREATE UNIQUE INDEX i_mix_channel ON mix_channel (channel, service); +CREATE INDEX i_mix_channel_serv ON mix_channel (service); + +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 +); + +CREATE UNIQUE INDEX i_mix_participant ON mix_participant (channel, service, username, domain); +CREATE INDEX i_mix_participant_chan_serv ON mix_participant (channel, service); + +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 +); + +CREATE UNIQUE INDEX i_mix_subscription ON mix_subscription (channel, service, username, domain, node); +CREATE INDEX i_mix_subscription_chan_serv_ud ON mix_subscription (channel, service, username, domain); +CREATE INDEX i_mix_subscription_chan_serv_node ON mix_subscription (channel, service, node); +CREATE INDEX i_mix_subscription_chan_serv ON mix_subscription (channel, service); + +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 +); + +CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username, channel, service); +CREATE INDEX i_mix_pam_us ON mix_pam (username); + +CREATE TABLE mqtt_pub ( + username text NOT NULL, + resource text NOT NULL, + topic text NOT NULL, + qos smallint NOT NULL, + payload bytea NOT NULL, + payload_format smallint NOT NULL, + content_type text NOT NULL, + response_topic text NOT NULL, + correlation_data bytea NOT NULL, + user_properties bytea NOT NULL, + expiry bigint NOT NULL +); + +CREATE UNIQUE INDEX i_mqtt_topic ON mqtt_pub (topic); |