diff options
Diffstat (limited to 'src/odbc/mssql.sql')
| -rw-r--r-- | src/odbc/mssql.sql | 63 |
1 files changed, 50 insertions, 13 deletions
diff --git a/src/odbc/mssql.sql b/src/odbc/mssql.sql index 00ad9db45..581a21182 100644 --- a/src/odbc/mssql.sql +++ b/src/odbc/mssql.sql @@ -162,6 +162,37 @@ CREATE TABLE [dbo].[private_storage] ( ) ON [PRIMARY]
GO
+CREATE TABLE [dbo].[privacy_default_list] (
+ [username] [varchar] (250) NOT NULL,
+ [name] [varchar] (250) NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[privacy_list] (
+ [username] [varchar] (250) NOT NULL,
+ [name] [varchar] (250) NOT NULL,
+ id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[privacy_list_data] (
+ [id] [bigint] NOT NULL,
+ [t] [character] (1) NOT NULL,
+ [value] [text] NOT NULL,
+ [action] [character] (1) NOT NULL,
+ [ord] [NUMERIC] NOT NULL,
+ [match_all] [boolean] NOT NULL,
+ [match_iq] [boolean] NOT NULL,
+ [match_message] [boolean] NOT NULL,
+ [match_presence_in] [boolean] NOT NULL,
+ [match_presence_out] [boolean] NOT NULL
+) ON [PRIMARY]
+GO
+
+/* Constraints to add:
+- id in privacy_list is a SERIAL autogenerated number
+- id in privacy_list_data must exist in the table privacy_list */
+
ALTER TABLE [dbo].[last] WITH NOCHECK ADD
CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED
(
@@ -200,7 +231,7 @@ ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
- CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[last] WITH NOCHECK ADD
@@ -217,6 +248,13 @@ ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
GO
+ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD
+ CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
CREATE INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
@@ -240,9 +278,15 @@ GO CREATE INDEX [IX_private_user] ON [dbo].[private_storage]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
+
CREATE INDEX [IX_private_user_ns] ON [dbo].[private_storage]([username], [namespace]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
+ CREATE INDEX [IX_privacy_list_username] ON [dbo].[privacy_list]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_privacy_list_username_name] ON [dbo].[privacy_list]([username], [name]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
/*********************************************************/
/** These store procedures are for use with ejabberd **/
@@ -250,10 +294,10 @@ GO /** **/
/** The stored procedures reduce the need to sql **/
/** compilation of the database and also allow for also **/
-/** provide each of database integration. The store **/
+/** provide each of database integration. The stored **/
/** procedure have been optimized to increase database **/
/** performance and a reduction of 80% in CPU was **/
-/** achive over the use of standard sql. **/
+/** achieved over the use of standard sql. **/
/*********************************************************/
/****** Object: StoredProcedure [dbo].[add_roster] ******/
@@ -472,7 +516,7 @@ CREATE PROCEDURE [dbo].[add_user] AS
BEGIN
INSERT INTO users
- ( username,
+ ( [username],
[password]
)
VALUES
@@ -948,15 +992,6 @@ BEGIN END
GO
-ejabberd_odbc:sql_query_t(
- ["delete from private_storage "
- "where username='", Username, "' and "
- "namespace='", LXMLNS, "';"]),
- ejabberd_odbc:sql_query_t(
- ["insert into private_storage(username, namespace, data) "
- "values ('", Username, "', '", LXMLNS, "', "
- "'", SData, "');"]).
-
/******************************************************************/
/****** Object: StoredProcedure [dbo].[get_private_data] **/
/** Retrieve user private data by namespace **/
@@ -986,3 +1021,5 @@ BEGIN END
GO
+
+
|