aboutsummaryrefslogtreecommitdiff
path: root/sql/mssql.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/mssql.sql')
-rw-r--r--sql/mssql.sql140
1 files changed, 97 insertions, 43 deletions
diff --git a/sql/mssql.sql b/sql/mssql.sql
index 0dfaa7161..7c3713b76 100644
--- a/sql/mssql.sql
+++ b/sql/mssql.sql
@@ -1,5 +1,5 @@
--
--- ejabberd, Copyright (C) 2002-2015 ProcessOne
+-- 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
@@ -38,16 +38,16 @@ CREATE TABLE [dbo].[archive] (
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) TEXTIMAGE_ON [PRIMARY];
-CREATE INDEX [archive_username] ON [archive] (username)
+CREATE INDEX [archive_username_timestamp] ON [archive] (username, timestamp)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE INDEX [archive_timestamp] ON [archive] (timestamp)
+CREATE INDEX [archive_username_peer] ON [archive] (username, peer)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE INDEX [archive_peer] ON [archive] (peer)
+CREATE INDEX [archive_username_bare_peer] ON [archive] (username, bare_peer)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE INDEX [archive_bare_peer] ON [archive] (bare_peer)
+CREATE INDEX [archive_timestamp] ON [archive] (timestamp)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[archive_prefs] (
@@ -72,16 +72,6 @@ CREATE TABLE [dbo].[caps_features] (
CREATE CLUSTERED INDEX [caps_features_node_subnode] ON [caps_features] (node, subnode)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE TABLE [dbo].[irc_custom] (
- [jid] [varchar] (255) NOT NULL,
- [host] [varchar] (255) NOT NULL,
- [data] [text] NOT NULL,
- [created_at] [datetime] NOT NULL DEFAULT GETDATE()
-) TEXTIMAGE_ON [PRIMARY];
-
-CREATE UNIQUE CLUSTERED INDEX [irc_custom_jid_host] ON [irc_custom] (jid, host)
-WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-
CREATE TABLE [dbo].[last] (
[username] [varchar] (250) NOT NULL,
[seconds] [text] NOT NULL,
@@ -125,6 +115,42 @@ 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 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].[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,
@@ -182,11 +208,11 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_item] (
[nodeid] [bigint] NULL,
- [itemid] [varchar] (255) NULL,
- [publisher] [text] NULL,
- [creation] [text] NULL,
- [modification] [varchar] (255) NULL,
- [payload] [text] NULL
+ [itemid] [varchar] (255) NOT NULL,
+ [publisher] [text] NOT NULL,
+ [creation] [varchar] (32) NOT NULL,
+ [modification] [varchar] (32) NOT NULL,
+ [payload] [text] NOT NULL DEFAULT ''
) TEXTIMAGE_ON [PRIMARY];
CREATE INDEX [pubsub_item_itemid] ON [pubsub_item] (itemid)
@@ -197,8 +223,8 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_node_option] (
[nodeid] [bigint] NULL,
- [name] [text] NULL,
- [val] [text] NULL
+ [name] [text] NOT NULL,
+ [val] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE CLUSTERED INDEX [pubsub_node_option_nodeid] ON [pubsub_node_option] (nodeid)
@@ -206,7 +232,7 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_node_owner] (
[nodeid] [bigint] NULL,
- [owner] [text] NULL
+ [owner] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE CLUSTERED INDEX [pubsub_node_owner_nodeid] ON [pubsub_node_owner] (nodeid)
@@ -214,9 +240,9 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE TABLE [dbo].[pubsub_state] (
[nodeid] [bigint] NULL,
- [jid] [varchar] (255) NULL,
- [affiliation] [char] (1) NULL,
- [subscriptions] [text] NULL,
+ [jid] [varchar] (255) NOT NULL,
+ [affiliation] [char] (1) NOT NULL,
+ [subscriptions] [text] NOT NULL DEFAULT '',
[stateid] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pubsub_state_PRIMARY] PRIMARY KEY CLUSTERED
(
@@ -231,19 +257,19 @@ CREATE UNIQUE INDEX [pubsub_state_nodeid_jid] ON [pubsub_state] (nodeid, jid)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[pubsub_subscription_opt] (
- [subid] [varchar] (255) NULL,
- [opt_name] [varchar] (32) NULL,
- [opt_value] [text] NULL
+ [subid] [varchar] (255) NOT NULL,
+ [opt_name] [varchar] (32) NOT NULL,
+ [opt_value] [text] NOT NULL
) TEXTIMAGE_ON [PRIMARY];
CREATE UNIQUE CLUSTERED INDEX [pubsub_subscription_opt_subid_opt_name] ON [pubsub_subscription_opt] (subid, opt_name)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE TABLE [dbo].[pubsub_node] (
- [host] [varchar] (255) NULL,
- [node] [varchar] (255) NULL,
- [parent] [varchar] (255) NULL,
- [type] [text] NULL,
+ [host] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [parent] [varchar] (255) NOT NULL DEFAULT '',
+ [plugin] [text] NOT NULL,
[nodeid] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pubsub_node_PRIMARY] PRIMARY KEY CLUSTERED
(
@@ -446,16 +472,6 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW
CREATE INDEX [vcard_search_lorgunit] ON [vcard_search] (lorgunit)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
-CREATE TABLE [dbo].[vcard_xupdate] (
- [username] [varchar] (250) NOT NULL,
- [hash] [text] NOT NULL,
- [created_at] [datetime] NOT NULL DEFAULT GETDATE(),
- CONSTRAINT [vcard_xupdate_PRIMARY] PRIMARY KEY CLUSTERED
-(
- [username] ASC
-)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
-) TEXTIMAGE_ON [PRIMARY];
-
ALTER TABLE [dbo].[pubsub_item] WITH CHECK ADD CONSTRAINT [pubsub_item_ibfk_1] FOREIGN KEY([nodeid])
REFERENCES [dbo].[pubsub_node] ([nodeid])
ON DELETE CASCADE;
@@ -490,3 +506,41 @@ CREATE TABLE [dbo].[oauth_token] (
[token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) TEXTIMAGE_ON [PRIMARY];
+
+CREATE TABLE [dbo].[route] (
+ [domain] [varchar] (255) NOT NULL,
+ [server_host] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [pid] [varchar](100) NOT NULL,
+ [local_hint] [text] NOT NULL
+);
+
+CREATE UNIQUE CLUSTERED INDEX [route_i] ON [route] (domain, server_host, node, pid)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE INDEX [route_domain] ON [route] (domain)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE TABLE [dbo].[bosh] (
+ [sid] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [pid] [varchar](100) NOT NULL
+ CONSTRAINT [bosh_PRIMARY] PRIMARY KEY CLUSTERED
+(
+ [sid] ASC
+)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
+);
+
+CREATE TABLE [dbo].[push_session] (
+ [username] [varchar] (255) NOT NULL,
+ [timestamp] [bigint] NOT NULL,
+ [service] [varchar] (255) NOT NULL,
+ [node] [varchar] (255) NOT NULL,
+ [xml] [varchar] (255) NOT NULL
+);
+
+CREATE UNIQUE CLUSTERED INDEX [i_push_usn] ON [push_session] (username, service, node)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
+
+CREATE UNIQUE INDEX [i_push_ut] ON [push_session] (username, timestamp)
+WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
generated by cgit v1.2.3 (git 2.25.1) at 2025-09-08 23:37:31 +0000