Optimize muc subscriptions handling

4 files changed, 48 insertions, 0 deletions

diff --git a/sql/lite.sql b/sql/lite.sql
index 44df0586d..c2bc126fc 100644
--- a/sql/lite.sql
+++ b/sql/lite.sql
@@ -290,6 +290,18 @@ CREATE TABLE muc_online_users (
 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 USING BTREE ON muc_room_subscribers(host, jid);
+CREATE UNIQUE INDEX i_muc_room_subscribers_host_room_jid USING BTREE ON muc_room_subscribers(host, room, jid);
+
 CREATE TABLE irc_custom (
     jid text NOT NULL,
     host text NOT NULL,
diff --git a/sql/mssql.sql b/sql/mssql.sql
index f49246ec3..83b219298 100644
--- a/sql/mssql.sql
+++ b/sql/mssql.sql
@@ -149,6 +149,18 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
 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,

diff --git a/sql/mysql.sql b/sql/mysql.sql
index 4fd70f382..acf2bfe1a 100644
--- a/sql/mysql.sql
+++ b/sql/mysql.sql
@@ -306,6 +306,18 @@ CREATE TABLE muc_online_users (
 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 irc_custom (
     jid text NOT NULL,
     host text NOT NULL,
diff --git a/sql/pg.sql b/sql/pg.sql
index db46111f5..fd56ba39d 100644
--- a/sql/pg.sql
+++ b/sql/pg.sql
@@ -308,6 +308,18 @@ CREATE TABLE muc_online_users (
 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 irc_custom (
     jid text NOT NULL,
     host text NOT NULL,