From 12e01a51196a656955d79869a05ec3db1fc2f9bb Mon Sep 17 00:00:00 2001
From: Evgeniy Khramtsov <ekhramtsov@process-one.net>
Date: Wed, 29 Mar 2017 12:58:01 +0300
Subject: Add SQL as mod_muc RAM backend

---
 sql/lite.sql  | 21 +++++++++++++++++++++
 sql/mssql.sql | 24 ++++++++++++++++++++++++
 sql/mysql.sql | 21 +++++++++++++++++++++
 sql/pg.sql    | 21 +++++++++++++++++++++
 4 files changed, 87 insertions(+)

(limited to 'sql')

diff --git a/sql/lite.sql b/sql/lite.sql
index c17d80ec5..8b59ef0a1 100644
--- a/sql/lite.sql
+++ b/sql/lite.sql
@@ -275,6 +275,27 @@ 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 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 (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 (username, server, resource, name, host);
+CREATE INDEX i_muc_online_users_us ON muc_online_users (username, server);
+
 CREATE TABLE irc_custom (
     jid text NOT NULL,
     host text NOT NULL,
diff --git a/sql/mssql.sql b/sql/mssql.sql
index 7535da5ac..59c192b98 100644
--- a/sql/mssql.sql
+++ b/sql/mssql.sql
@@ -125,6 +125,30 @@ 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 CLUSTERED 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].[privacy_default_list] (
         [username] [varchar] (250) NOT NULL,
         [name] [varchar] (250) NOT NULL,
diff --git a/sql/mysql.sql b/sql/mysql.sql
index 982df7ff5..edd205f0d 100644
--- a/sql/mysql.sql
+++ b/sql/mysql.sql
@@ -291,6 +291,27 @@ 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 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 irc_custom (
     jid text NOT NULL,
     host text NOT NULL,
diff --git a/sql/pg.sql b/sql/pg.sql
index 8a8b77b1f..6c099fc6b 100644
--- a/sql/pg.sql
+++ b/sql/pg.sql
@@ -293,6 +293,27 @@ 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 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 irc_custom (
     jid text NOT NULL,
     host text NOT NULL,
-- 
cgit v1.2.3