From d5e4da54cfde1cf5b7876a192d9fe3b952eb345f Mon Sep 17 00:00:00 2001 From: Evgeny Khramtsov Date: Wed, 5 Dec 2018 13:14:29 +0300 Subject: Update MIX code to reflect newest specification Note that support for older specification is completely dropped, i.e. no backward compatibility is provided since the XEPs are still very experimental and being changed drastically --- sql/lite.new.sql | 54 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/lite.sql | 53 +++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/mysql.new.sql | 54 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/mysql.sql | 53 +++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/pg.new.sql | 54 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/pg.sql | 53 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 6 files changed, 321 insertions(+) (limited to 'sql') diff --git a/sql/lite.new.sql b/sql/lite.new.sql index de62cd169..6ec7ea876 100644 --- a/sql/lite.new.sql +++ b/sql/lite.new.sql @@ -410,3 +410,57 @@ CREATE TABLE push_session ( ); 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); diff --git a/sql/lite.sql b/sql/lite.sql index 50bfec3bd..177454c7b 100644 --- a/sql/lite.sql +++ b/sql/lite.sql @@ -379,3 +379,56 @@ CREATE TABLE push_session ( 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); diff --git a/sql/mysql.new.sql b/sql/mysql.new.sql index 88769ddae..832910d2c 100644 --- a/sql/mysql.new.sql +++ b/sql/mysql.new.sql @@ -426,3 +426,57 @@ CREATE TABLE push_session ( ); 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 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), server_host(191), channel(191), service(191)); +CREATE INDEX i_mix_pam_us ON mix_pam (username(191), server_host(191)); diff --git a/sql/mysql.sql b/sql/mysql.sql index 2fcea38f5..568d2b41c 100644 --- a/sql/mysql.sql +++ b/sql/mysql.sql @@ -395,3 +395,56 @@ CREATE TABLE push_session ( 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)); diff --git a/sql/pg.new.sql b/sql/pg.new.sql index 6244eeea9..721ce1ae9 100644 --- a/sql/pg.new.sql +++ b/sql/pg.new.sql @@ -571,3 +571,57 @@ CREATE TABLE push_session ( ); 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); diff --git a/sql/pg.sql b/sql/pg.sql index ad1e4b9c2..b9797b820 100644 --- a/sql/pg.sql +++ b/sql/pg.sql @@ -399,3 +399,56 @@ CREATE TABLE push_session ( 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); -- cgit v1.2.3