diff options
Diffstat (limited to 'test/docker')
| -rw-r--r-- | test/docker/README.md | 46 | ||||
| -rw-r--r-- | test/docker/db/mysql/initdb/mysql.sql | 465 | ||||
| -rw-r--r-- | test/docker/db/postgres/initdb/pg.sql | 470 | ||||
| -rw-r--r-- | test/docker/docker-compose.yml | 37 |
4 files changed, 1018 insertions, 0 deletions
diff --git a/test/docker/README.md b/test/docker/README.md new file mode 100644 index 000000000..22324a6e0 --- /dev/null +++ b/test/docker/README.md @@ -0,0 +1,46 @@ +# Docker database images to run ejabberd tests + +## Starting databases + +You can start the Docker environment with Docker Compose, from ejabberd repository root. + +The following command will launch MySQL, PostgreSQL, Redis and keep the console +attached to it. + +``` +mkdir test/docker/db/mysql/data +mkdir test/docker/db/postgres/data +(cd test/docker; docker-compose up) +``` + +You can stop all the databases with CTRL-C. + +## Running tests + +Before running the test, you can ensure there is no running instance of Erlang common test tool. You can run the following +command, especially if all test are skipped with an `eaddrinuse` error: + +``` +pkill -9 ct_run +``` + +You can run tests with (from ejabberd repository root): + +``` +make test +``` + +## Cleaning up the test environment + +You can fully clean up the environment with: + +``` +(cd test/docker; docker-compose down) +``` + +If you want to clean the data, you can remove the data directories after the `docker-compose down` command: + +``` +rm -rf test/docker/db/mysql/data +rm -rf test/docker/db/postgres/data +``` diff --git a/test/docker/db/mysql/initdb/mysql.sql b/test/docker/db/mysql/initdb/mysql.sql new file mode 100644 index 000000000..a05f8c86c --- /dev/null +++ b/test/docker/db/mysql/initdb/mysql.sql @@ -0,0 +1,465 @@ +-- +-- 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) PRIMARY KEY, + 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 +) 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) PRIMARY KEY, + seconds text NOT NULL, + state text NOT NULl +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + + +CREATE TABLE rosterusers ( + username 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_user_jid ON rosterusers(username(75), jid(75)); +CREATE INDEX i_rosteru_username ON rosterusers(username); +CREATE INDEX i_rosteru_jid ON rosterusers(jid); + +CREATE TABLE rostergroups ( + username varchar(191) NOT NULL, + jid varchar(191) NOT NULL, + grp text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX pk_rosterg_user_jid ON rostergroups(username(75), jid(75)); + +CREATE TABLE sr_group ( + name varchar(191) NOT NULL, + opts text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE sr_user ( + jid varchar(191) NOT NULL, + grp varchar(191) NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_sr_user_jid_group ON sr_user(jid(75), grp(75)); +CREATE INDEX i_sr_user_jid ON sr_user(jid); +CREATE INDEX i_sr_user_grp ON sr_user(grp); + +CREATE TABLE spool ( + username 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_despool USING BTREE ON spool(username); +CREATE INDEX i_spool_created_at USING BTREE ON spool(created_at); + +CREATE TABLE archive ( + username 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_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 TABLE archive_prefs ( + username varchar(191) NOT NULL PRIMARY KEY, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE vcard ( + username varchar(191) PRIMARY KEY, + vcard mediumtext 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, + 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 +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_vcard_search_lfn ON vcard_search(lfn); +CREATE INDEX i_vcard_search_lfamily ON vcard_search(lfamily); +CREATE INDEX i_vcard_search_lgiven ON vcard_search(lgiven); +CREATE INDEX i_vcard_search_lmiddle ON vcard_search(lmiddle); +CREATE INDEX i_vcard_search_lnickname ON vcard_search(lnickname); +CREATE INDEX i_vcard_search_lbday ON vcard_search(lbday); +CREATE INDEX i_vcard_search_lctry ON vcard_search(lctry); +CREATE INDEX i_vcard_search_llocality ON vcard_search(llocality); +CREATE INDEX i_vcard_search_lemail ON vcard_search(lemail); +CREATE INDEX i_vcard_search_lorgname ON vcard_search(lorgname); +CREATE INDEX i_vcard_search_lorgunit ON vcard_search(lorgunit); + +CREATE TABLE privacy_default_list ( + username varchar(191) PRIMARY KEY, + name varchar(191) NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE TABLE privacy_list ( + username 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_username USING BTREE ON privacy_list(username); +CREATE UNIQUE INDEX i_privacy_list_username_name USING BTREE ON privacy_list (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, + namespace varchar(191) NOT NULL, + data text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE INDEX i_private_storage_username USING BTREE ON private_storage(username); +CREATE UNIQUE INDEX i_private_storage_username_namespace USING BTREE ON private_storage(username(75), namespace(75)); + +-- Not tested in mysql +CREATE TABLE roster_version ( + username varchar(191) PRIMARY KEY, + version text NOT NULL +) 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, + 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, + 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, + 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_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, + xml text, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) 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, + resource varchar(191) NOT NULL, + priority text NOT NULL, + info text NOT NULL +) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX i_sid ON sm(usec, pid(75)); +CREATE INDEX i_node ON sm(node(75)); +CREATE INDEX i_username ON sm(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 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/test/docker/db/postgres/initdb/pg.sql b/test/docker/db/postgres/initdb/pg.sql new file mode 100644 index 000000000..eae98d3f0 --- /dev/null +++ b/test/docker/db/postgres/initdb/pg.sql @@ -0,0 +1,470 @@ +-- +-- 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 PRIMARY KEY, + "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() +); + +-- 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 PRIMARY KEY, + seconds text NOT NULL, + state text NOT NULL +); + + +CREATE TABLE rosterusers ( + username 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_user_jid ON rosterusers USING btree (username, jid); +CREATE INDEX i_rosteru_username ON rosterusers USING btree (username); +CREATE INDEX i_rosteru_jid ON rosterusers USING btree (jid); + + +CREATE TABLE rostergroups ( + username text NOT NULL, + jid text NOT NULL, + grp text NOT NULL +); + +CREATE INDEX pk_rosterg_user_jid ON rostergroups USING btree (username, jid); + +CREATE TABLE sr_group ( + name text NOT NULL, + opts text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE TABLE sr_user ( + jid text NOT NULL, + grp text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE UNIQUE INDEX i_sr_user_jid_grp ON sr_user USING btree (jid, grp); +CREATE INDEX i_sr_user_jid ON sr_user USING btree (jid); +CREATE INDEX i_sr_user_grp ON sr_user USING btree (grp); + +CREATE TABLE spool ( + username text NOT NULL, + xml text NOT NULL, + seq SERIAL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_despool ON spool USING btree (username); + +CREATE TABLE archive ( + username 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_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 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 now() +); + +CREATE TABLE vcard ( + username text PRIMARY KEY, + vcard 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, + 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 +); + +CREATE INDEX i_vcard_search_lfn ON vcard_search(lfn); +CREATE INDEX i_vcard_search_lfamily ON vcard_search(lfamily); +CREATE INDEX i_vcard_search_lgiven ON vcard_search(lgiven); +CREATE INDEX i_vcard_search_lmiddle ON vcard_search(lmiddle); +CREATE INDEX i_vcard_search_lnickname ON vcard_search(lnickname); +CREATE INDEX i_vcard_search_lbday ON vcard_search(lbday); +CREATE INDEX i_vcard_search_lctry ON vcard_search(lctry); +CREATE INDEX i_vcard_search_llocality ON vcard_search(llocality); +CREATE INDEX i_vcard_search_lemail ON vcard_search(lemail); +CREATE INDEX i_vcard_search_lorgname ON vcard_search(lorgname); +CREATE INDEX i_vcard_search_lorgunit ON vcard_search(lorgunit); + +CREATE TABLE privacy_default_list ( + username text PRIMARY KEY, + name text NOT NULL +); + +CREATE TABLE privacy_list ( + username text NOT NULL, + name text NOT NULL, + id SERIAL UNIQUE, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_privacy_list_username ON privacy_list USING btree (username); +CREATE UNIQUE INDEX i_privacy_list_username_name ON privacy_list USING btree (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, + namespace text NOT NULL, + data text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_private_storage_username ON private_storage USING btree (username); +CREATE UNIQUE INDEX i_private_storage_username_namespace ON private_storage USING btree (username, namespace); + + +CREATE TABLE roster_version ( + username text PRIMARY KEY, + version text NOT NULL +); + +-- 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, + 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, + 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, + 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 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, + xml text, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +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, + resource text NOT NULL, + priority text NOT NULL, + info text NOT NULL +); + +CREATE UNIQUE INDEX i_sm_sid ON sm USING btree (usec, pid); +CREATE INDEX i_sm_node ON sm USING btree (node); +CREATE INDEX i_sm_username ON sm USING btree (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 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); diff --git a/test/docker/docker-compose.yml b/test/docker/docker-compose.yml new file mode 100644 index 000000000..f98612036 --- /dev/null +++ b/test/docker/docker-compose.yml @@ -0,0 +1,37 @@ +version: '3.7' + +services: + mysql: + image: mysql:latest + container_name: ejabberd-mysql + volumes: + - ./db/mysql/data:/var/lib/mysql + - ./db/mysql/initdb:/docker-entrypoint-initdb.d:ro + command: --default-authentication-plugin=mysql_native_password + restart: always + ports: + - 3306:3306 + environment: + MYSQL_ROOT_PASSWORD: root + MYSQL_DATABASE: ejabberd_test + MYSQL_USER: ejabberd_test + MYSQL_PASSWORD: ejabberd_test + + postgres: + image: postgres:latest + container_name: ejabberd-postgres + volumes: + - ./db/postgres/data:/var/lib/postgresql/data + - ./db/postgres/initdb:/docker-entrypoint-initdb.d:ro + ports: + - 5432:5432 + environment: + POSTGRES_PASSWORD: ejabberd_test + POSTGRES_USER: ejabberd_test + POSTGRES_DB: ejabberd_test + + redis: + image: redis:latest + container_name: ejabberd-redis + ports: + - 6379:6379 |