aboutsummaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/lite.new.sql54
-rw-r--r--sql/lite.sql53
-rw-r--r--sql/mysql.new.sql54
-rw-r--r--sql/mysql.sql53
-rw-r--r--sql/pg.new.sql54
-rw-r--r--sql/pg.sql53
6 files changed, 321 insertions, 0 deletions
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);
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-04 19:03:41 +0000