aboutsummaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/mssql2000.sql1096
-rw-r--r--sql/mssql2005.sql1803
-rw-r--r--sql/mysql.sql284
-rw-r--r--sql/pg.sql285
4 files changed, 3468 insertions, 0 deletions
diff --git a/sql/mssql2000.sql b/sql/mssql2000.sql
new file mode 100644
index 000000000..d5381bafa
--- /dev/null
+++ b/sql/mssql2000.sql
@@ -0,0 +1,1096 @@
+/*
+ * ejabberd, Copyright (C) 2002-2013 ProcessOne
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License as
+ * published by the Free Software Foundation; either version 2 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
+ * 02111-1307 USA
+ *
+ */
+
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+
+exec sp_dboption N'ejabberd', N'autoclose', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'bulkcopy', N'true'
+GO
+
+exec sp_dboption N'ejabberd', N'trunc. log', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'torn page detection', N'true'
+GO
+
+exec sp_dboption N'ejabberd', N'read only', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'dbo use', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'single', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'autoshrink', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'ANSI null default', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'recursive triggers', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'ANSI nulls', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'concat null yields null', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'cursor close on commit', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'default to local cursor', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'quoted identifier', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'ANSI warnings', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'auto create statistics', N'true'
+GO
+
+exec sp_dboption N'ejabberd', N'auto update statistics', N'true'
+GO
+
+use [ejabberd]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[last]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[last]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rostergroups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[rostergroups]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rosterusers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[rosterusers]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spool]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[spool]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[users]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[vcard]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[private_storage]
+GO
+
+CREATE TABLE [dbo].[last] (
+ [username] [varchar] (250) NOT NULL ,
+ [seconds] [varchar] (50) NOT NULL ,
+ [state] [varchar] (100) NOT NULL ,
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[rostergroups] (
+ [username] [varchar] (250) NOT NULL ,
+ [jid] [varchar] (250) NOT NULL ,
+ [grp] [varchar] (100) NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[rosterusers] (
+ [username] [varchar] (250) NOT NULL ,
+ [jid] [varchar] (250) NOT NULL ,
+ [nick] [varchar] (50) NOT NULL ,
+ [subscription] [char] (1) NOT NULL ,
+ [ask] [char] (1) NOT NULL ,
+ [askmessage] [varchar] (250) NOT NULL ,
+ [server] [char] (1) NOT NULL ,
+ [subscribe] [varchar] (200) NULL ,
+ [type] [varchar] (50) NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[spool] (
+ [id] [numeric](19, 0) IDENTITY (1, 1) NOT NULL ,
+ [username] [varchar] (250) NOT NULL ,
+ [xml] [text] NOT NULL ,
+ [notifyprocessed] [bit] NULL ,
+ [created] [datetime] NULL ,
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[users] (
+ [username] [varchar] (250) NOT NULL ,
+ [password] [varchar] (50) NOT NULL ,
+ [created] [datetime] NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[vcard] (
+ [username] [varchar] (250) NOT NULL ,
+ [full_name] [varchar] (250) NULL ,
+ [first_name] [varchar] (50) NULL ,
+ [last_name] [varchar] (50) NULL ,
+ [nick_name] [varchar] (50) NULL ,
+ [url] [varchar] (1024) NULL ,
+ [address1] [varchar] (50) NULL ,
+ [address2] [varchar] (50) NULL ,
+ [locality] [varchar] (50) NULL ,
+ [region] [varchar] (50) NULL ,
+ [pcode] [varchar] (50) NULL ,
+ [country] [varchar] (50) NULL ,
+ [telephone] [varchar] (50) NULL ,
+ [email] [varchar] (250) NULL ,
+ [orgname] [varchar] (50) NULL ,
+ [orgunit] [varchar] (50) NULL ,
+ [title] [varchar] (50) NULL ,
+ [role] [varchar] (50) NULL ,
+ [b_day] [datetime] NULL ,
+ [descr] [varchar] (500) NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[private_storage] (
+ [username] [varchar] (250) NOT NULL ,
+ [namespace] [varchar] (250) NOT NULL ,
+ [data] [text] NOT NULL
+) 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
+
+/* Not tested on mssql */
+CREATE TABLE [dbo].[roster_version] (
+ [username] [varchar] (250) NOT NULL ,
+ [version] [varchar] (64) 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
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD
+ CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED
+ (
+ [username],
+ [jid],
+ [grp]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
+ CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED
+ (
+ [username],
+ [id]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[users] WITH NOCHECK ADD
+ CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[roster_version] WITH NOCHECK ADD
+ CONSTRAINT [PK_roster_version] PRIMARY KEY CLUSTERED
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD
+ CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[last] WITH NOCHECK ADD
+ CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date]
+GO
+
+ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
+ CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed],
+ CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created],
+ CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete]
+GO
+
+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
+
+ CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_rosterusers_jid] ON [dbo].[rosterusers]([username], [jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_spool_process] ON [dbo].[spool]([created], [notifyprocessed]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IK_Spool_Del] ON [dbo].[spool]([MustDelete]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IK_Spool_Created] ON [dbo].[spool]([created]) WITH FILLFACTOR = 90 ON [PRIMARY]
+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 **/
+/** 1.1 and Microsoft Sql Server 2000 **/
+/** **/
+/** The stored procedures reduce the need to sql **/
+/** compilation of the database and also allow for also **/
+/** provide each of database integration. The stored **/
+/** procedure have been optimized to increase database **/
+/** performance and a reduction of 80% in CPU was **/
+/** achieved over the use of standard sql. **/
+/*********************************************************/
+
+/****** Object: StoredProcedure [dbo].[add_roster] ******/
+/** Add or update user entries in the roster **/
+/*********************************************************/
+CREATE PROCEDURE [dbo].[add_roster]
+ @Username varchar(250),
+ @JID varchar(250),
+ @Nick varchar(50),
+ @Subscription char(1),
+ @Ask char(1),
+ @AskMessage varchar(250),
+ @Server char(1),
+ @Subscribe varchar(200),
+ @Type varchar(50),
+ @Grp varchar(100)
+AS
+BEGIN
+ BEGIN TRANSACTION
+ --- Update Roster if user exist else add roster item
+ IF EXISTS (SELECT username FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
+ BEGIN
+ UPDATE rosterusers
+ SET rosterusers.username=@Username,
+ rosterusers.jid=@JID,
+ rosterusers.nick=@Nick,
+ rosterusers.subscription=@Subscription,
+ rosterusers.ask=@Ask,
+ rosterusers.askmessage=@AskMessage,
+ rosterusers.server=@Server,
+ rosterusers.subscribe=@Subscribe,
+ rosterusers.type=@Type
+ WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
+ END
+ ELSE
+ BEGIN
+ INSERT INTO rosterusers
+ ( rosterusers.username,
+ rosterusers.jid,
+ rosterusers.nick,
+ rosterusers.subscription,
+ rosterusers.ask,
+ rosterusers.askmessage,
+ rosterusers.server,
+ rosterusers.subscribe,
+ rosterusers.type
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Nick,
+ @Subscription,
+ @Ask,
+ @AskMessage,
+ @Server,
+ @Subscribe,
+ @Type
+ );
+ END
+
+ --- Update Roster Groups if exist else add group entry
+ IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
+ BEGIN
+ INSERT INTO rostergroups
+ ( rostergroups.username,
+ rostergroups.jid,
+ rostergroups.grp
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Grp
+ );
+ END
+
+ COMMIT
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_roster_group] ******/
+/** Add or update user group entries in the roster groups **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_roster_group]
+ @Username varchar(250),
+ @JID varchar(250),
+ @Grp varchar(100)
+AS
+BEGIN
+ --- Update Roster Groups if exist else add group
+ IF NOT EXISTS (SELECT username FROM rostergroups WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
+ BEGIN
+ INSERT INTO rostergroups
+ ( rostergroups.username,
+ rostergroups.jid,
+ rostergroups.grp
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Grp
+ )
+ END
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_roster_user] ******/
+/** Add or update user entries in the roster **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_roster_user]
+ @Username varchar(250),
+ @JID varchar(250),
+ @Nick varchar(50),
+ @Subscription char(1),
+ @Ask char(1),
+ @AskMessage varchar(250),
+ @Server char(1),
+ @Subscribe varchar(200),
+ @Type varchar(50),
+ @Grp varchar(100) = Null
+AS
+BEGIN
+ BEGIN TRANSACTION
+ --- Update Roster Users if exist of add new user
+ IF EXISTS (SELECT username FROM rosterusers WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
+ BEGIN
+ UPDATE rosterusers
+ SET rosterusers.username=@Username,
+ rosterusers.jid=@JID,
+ rosterusers.nick=@Nick,
+ rosterusers.subscription=@Subscription,
+ rosterusers.ask=@Ask,
+ rosterusers.askmessage=@AskMessage,
+ rosterusers.server=@Server,
+ rosterusers.subscribe=@Subscribe,
+ rosterusers.type=@Type
+ WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
+ END
+ ELSE
+ BEGIN
+ INSERT INTO rosterusers
+ ( rosterusers.username,
+ rosterusers.jid,
+ rosterusers.nick,
+ rosterusers.subscription,
+ rosterusers.ask,
+ rosterusers.askmessage,
+ rosterusers.server,
+ rosterusers.subscribe,
+ rosterusers.type
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Nick,
+ @Subscription,
+ @Ask,
+ @AskMessage,
+ @Server,
+ @Subscribe,
+ @Type
+ );
+ END
+
+ --- Update Roster Group if exist of add new group
+ IF @Grp IS NOT NULL
+ EXECUTE [dbo].[add_roster_group] @Username, @JID, @Grp
+
+ COMMIT
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_roster_groups] ******/
+/** Remove user group entries from the roster groups table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_roster_groups]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+BEGIN
+ DELETE FROM rostergroups
+ WITH (ROWLOCK)
+ WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_spool] ******/
+/** Add a entry to the spool table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_spool]
+ @Username varchar(250),
+ @XML varchar(8000)
+AS
+BEGIN
+ INSERT INTO spool
+ ( spool.username,
+ spool.xml
+ )
+ VALUES
+ ( @Username,
+ @XML
+ )
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_user] ******/
+/** Add or update user entries to jabber **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_user]
+ @Username varchar(200),
+ @Password varchar(50)
+AS
+BEGIN
+ INSERT INTO users
+ ( [username],
+ [password]
+ )
+ VALUES
+ ( @Username,
+ @Password
+ );
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_password] **/
+/** Update users password **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_password]
+ @Username varchar(200),
+ @Password varchar(50)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM users WITH (NOLOCK) WHERE username=@Username)
+ BEGIN
+ UPDATE users SET username=@Username, password=@Password WHERE username=@Username;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO users (username, password) VALUES (@Username, @Password);
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_password] **/
+/** Retrive the user password **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_password]
+ @Username varchar(200)
+AS
+BEGIN
+ SELECT users.password as password
+ FROM users WITH (NOLOCK)
+ WHERE username=@Username;
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_roster_version] **/
+/** Update users roster_version **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_roster_version]
+ @Username varchar(200),
+ @Version varchar(50)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM roster_version WITH (NOLOCK) WHERE username=@Username)
+ BEGIN
+ UPDATE roster_version SET username=@Username, version=@Version WHERE username=@Username;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO roster_version (username, version) VALUES (@Username, @Version);
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_version] **/
+/** Retrive the user roster_version **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_version]
+ @Username varchar(200)
+AS
+BEGIN
+ SELECT roster_version.version as version
+ FROM roster_version WITH (NOLOCK)
+ WHERE username=@Username;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[clean_spool_msg] ******/
+/** Delete messages older that 3 days from spool **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[clean_spool_msg]
+AS
+DECLARE
+ @dt datetime,
+ @myRowCount int
+BEGIN
+ -- Delete small amounts because if locks the database table
+ SET ROWCOUNT 500
+ SET @myRowCount = 1
+
+ WHILE (@myRowCount) > 0
+ BEGIN
+ BEGIN TRANSACTION
+ SELECT @dt = DATEADD(d, -3, GETDATE())
+ DELETE FROM spool
+ WITH (ROWLOCK)
+ WHERE (MustDelete=1) OR (Created < @dt);
+
+ SET @myRowCount = @@RowCount
+ COMMIT
+ END
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_last] ******/
+/** Delete an entry from the last table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_last]
+ @Username varchar(250)
+AS
+BEGIN
+ DELETE FROM [last]
+ WITH (ROWLOCK)
+ WHERE [last].username=@Username;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_roster] ******/
+/** Delete an entry from the roster **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_roster]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+BEGIN
+ BEGIN TRANSACTION
+ DELETE FROM rosterusers
+ WITH (ROWLOCK)
+ WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
+
+ DELETE FROM rostergroups
+ WITH (ROWLOCK)
+ WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
+ COMMIT
+END
+GO
+
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_spool_msg] ******/
+/** Delete an entry from the spool table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_spool_msg]
+ @Username varchar(250)
+AS
+BEGIN
+ DELETE FROM spool
+ WITH (ROWLOCK)
+ WHERE spool.username=@Username;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user] ******/
+/** Delete an entry from the user table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_user]
+ @Username varchar(200)
+AS
+BEGIN
+ DELETE FROM users
+ WITH (ROWLOCK)
+ WHERE username=@Username;
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user_return_password]**/
+/** Delete an entry from the user table and return user password **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[del_user_return_password]
+ @Username varchar(250)
+AS
+DECLARE
+ @Pwd varchar(50)
+BEGIN
+ EXECUTE @Pwd = dbo.get_password @Username
+ DELETE FROM users
+ WITH (ROWLOCK)
+ WHERE username=@Username
+
+ SELECT @Pwd;
+END
+GO
+
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user_roster] **/
+/** Delete the users roster **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[del_user_roster]
+ @Username varchar(250)
+AS
+BEGIN
+ BEGIN TRANSACTION
+ DELETE FROM rosterusers
+ WITH (ROWLOCK)
+ WHERE rosterusers.username = @Username;
+
+ DELETE FROM rostergroups
+ WITH (ROWLOCK)
+ WHERE rostergroups.username = @Username;
+ COMMIT
+END
+GO
+
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_and_del_spool_msg] **/
+/** Fetch and delete the users offline messages **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_and_del_spool_msg]
+ @Username varchar(250)
+AS
+DECLARE
+ @vSpool table( username varchar(1),
+ xml varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM spool with (nolock) WHERE spool.username=@Username)
+ BEGIN
+ SELECT spool.username AS username,
+ spool.xml AS xml
+ FROM spool WITH (NOLOCK)
+ WHERE spool.username=@Username;
+
+ DELETE spool
+ WITH (ROWLOCK)
+ WHERE spool.username=@Username
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vSpool;
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_last] **/
+/** Retrive the last user login **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_last]
+ @Username varchar(250)
+AS
+BEGIN
+ SELECT last.seconds AS seconds,
+ last.state AS state
+ FROM last WITH (NOLOCK)
+ WHERE last.username=@Username;
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster] **/
+/** Retrive the user roster **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster]
+ @Username varchar(250)
+AS
+DECLARE
+ @vRosterusers table( username varchar(1),
+ jid varchar(1),
+ nick varchar(1),
+ subscription varchar(1),
+ ask varchar(1),
+ askmessage varchar(1),
+ server varchar(1),
+ subscribe varchar(1),
+ type varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username)
+ BEGIN
+ SELECT rosterusers.username AS username,
+ rosterusers.jid AS jid,
+ rosterusers.nick AS nick,
+ rosterusers.subscription AS subscription,
+ rosterusers.ask AS ask,
+ rosterusers.askmessage AS askmessage,
+ rosterusers.server AS server,
+ rosterusers.subscribe AS subscribe,
+ rosterusers.type AS type
+ FROM rosterusers WITH (NOLOCK)
+ WHERE rosterusers.username = @Username;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vRosterusers
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_by_jid] **/
+/** Retrive the user roster via JID **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_by_jid]
+ @Username varchar(200),
+ @JID varchar(250)
+AS
+DECLARE
+ @vRosterusers table( username varchar(1),
+ jid varchar(1),
+ nick varchar(1),
+ subscription varchar(1),
+ ask varchar(1),
+ askmessage varchar(1),
+ server varchar(1),
+ subscribe varchar(1),
+ type varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID))
+ BEGIN
+ SELECT rosterusers.username AS username,
+ rosterusers.jid AS jid,
+ rosterusers.nick AS nick,
+ rosterusers.subscription AS subscription,
+ rosterusers.ask AS ask,
+ rosterusers.askmessage AS askmessage,
+ rosterusers.server AS server,
+ rosterusers.subscribe AS subscribe,
+ rosterusers.type AS type
+ FROM rosterusers WITH (NOLOCK)
+ WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vRosterusers
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_jid_groups] **/
+/** Retrieve the user roster groups **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_jid_groups]
+ @Username varchar(200)
+AS
+DECLARE
+ @vrostergroups table( jid varchar(1),
+ grp varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
+ BEGIN
+ SELECT rostergroups.jid AS jid,
+ rostergroups.grp AS grp
+ FROM rostergroups WITH (NOLOCK)
+ WHERE rostergroups.username = @Username;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrostergroups
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_groups] **/
+/** Retrive the user roster groups **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_groups]
+ @Username varchar(200),
+ @JID varchar(250)
+AS
+DECLARE
+ @vrostergroups table( grp varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
+ BEGIN
+ SELECT rostergroups.grp AS grp
+ FROM rostergroups WITH (NOLOCK)
+ WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrostergroups
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_rostergroup_by_jid] **/
+/** Retrive the user roster groups via JID **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_rostergroup_by_jid]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+DECLARE
+ @vrostergroups table(grp varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID)
+ BEGIN
+ SELECT rostergroups.grp AS grp
+ FROM rostergroups WITH (NOLOCK)
+ WHERE rostergroups.username=@Username AND rostergroups.jid=@JID;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrostergroups
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_subscription] **/
+/** Retrive the user subscription requests **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_subscription]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+DECLARE
+ @vrosterusers table( subscription varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
+ BEGIN
+ SELECT rosterusers.subscription AS subscription
+ FROM rosterusers WITH (NOLOCK)
+ WHERE rosterusers.username=@Username AND rosterusers.jid=@JID;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrosterusers
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[list_users] **/
+/** Retrieve a list of all users **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[list_users]
+AS
+BEGIN
+ SELECT users.username AS username FROM users WITH (NOLOCK);
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_last] **/
+/** Update users last login status **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_last]
+ @Username varchar(250),
+ @Seconds varchar(50),
+ @State varchar(100)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM [last] WITH (NOLOCK) WHERE username=@Username)
+ BEGIN
+ UPDATE [last]
+ SET [last].username = @Username,
+ [last].seconds = @Seconds,
+ [last].state = @State
+ WHERE last.username=@Username;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO [last]
+ ( [last].username,
+ [last].seconds,
+ [last].state
+ )
+ VALUES
+ ( @Username,
+ @Seconds,
+ @State
+ )
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_private_data] **/
+/** store user private data by namespace **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_private_data]
+ @Username varchar(250),
+ @Namespace varchar(250),
+ @Data varchar(8000)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM private_storage with (nolock) WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace)
+ BEGIN
+ UPDATE [private_storage]
+ SET [private_storage].username = @Username,
+ [private_storage].namespace = @Namespace,
+ [private_storage].data = @Data
+ WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO [private_storage]
+ ( [private_storage].username,
+ [private_storage].namespace,
+ [private_storage].data
+ )
+ VALUES
+ ( @Username,
+ @Namespace,
+ @Data
+ )
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_private_data] **/
+/** Retrieve user private data by namespace **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_private_data]
+ @Username varchar(250),
+ @Namespace varchar(250)
+AS
+BEGIN
+ SELECT private_storage.data AS data
+ FROM private_storage WITH (NOLOCK)
+ WHERE username=@Username and namespace=@Namespace;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user_storage] ******/
+/** Delete private storage area for a given user **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_user_storage]
+ @Username varchar(250)
+AS
+BEGIN
+ DELETE FROM [private_storage]
+ WITH (ROWLOCK)
+ WHERE [private_storage].username=@Username;
+END
+GO
+
+
+
diff --git a/sql/mssql2005.sql b/sql/mssql2005.sql
new file mode 100644
index 000000000..2cfb078e2
--- /dev/null
+++ b/sql/mssql2005.sql
@@ -0,0 +1,1803 @@
+/*
+ * ejabberd, Copyright (C) 2002-2013 ProcessOne
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License as
+ * published by the Free Software Foundation; either version 2 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
+ * 02111-1307 USA
+ *
+ */
+
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+
+exec sp_dboption N'ejabberd', N'autoclose', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'bulkcopy', N'true'
+GO
+
+exec sp_dboption N'ejabberd', N'trunc. log', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'torn page detection', N'true'
+GO
+
+exec sp_dboption N'ejabberd', N'read only', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'dbo use', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'single', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'autoshrink', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'ANSI null default', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'recursive triggers', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'ANSI nulls', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'concat null yields null', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'cursor close on commit', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'default to local cursor', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'quoted identifier', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'ANSI warnings', N'false'
+GO
+
+exec sp_dboption N'ejabberd', N'auto create statistics', N'true'
+GO
+
+exec sp_dboption N'ejabberd', N'auto update statistics', N'true'
+GO
+
+use [ejabberd]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[last]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[last]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rostergroups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[rostergroups]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rosterusers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[rosterusers]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spool]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[spool]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[users]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[vcard]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard_search]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[vcard_search]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[private_storage]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[privacy_default_list]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[privacy_default_list]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[privacy_list]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[privacy_list]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[privacy_list_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[privacy_list_data]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[roster_version]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[roster_version]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_node_option]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[pubsub_node_option]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_node_owner]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[pubsub_node_owner]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_state]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[pubsub_state]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[pubsub_item]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_subscription_opt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[pubsub_subscription_opt]
+GO
+
+if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_node]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
+drop table [dbo].[pubsub_node]
+GO
+
+CREATE TABLE [dbo].[last] (
+ [username] [varchar] (250) NOT NULL ,
+ [seconds] [varchar] (50) NOT NULL ,
+ [state] [varchar] (100) NOT NULL ,
+ [Modify_Date] [datetime] NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[rostergroups] (
+ [username] [varchar] (250) NOT NULL ,
+ [jid] [varchar] (250) NOT NULL ,
+ [grp] [varchar] (100) NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[rosterusers] (
+ [username] [varchar] (250) NOT NULL ,
+ [jid] [varchar] (250) NOT NULL ,
+ [nick] [varchar] (50) NOT NULL ,
+ [subscription] [char] (1) NOT NULL ,
+ [ask] [char] (1) NOT NULL ,
+ [askmessage] [varchar] (250) NOT NULL ,
+ [server] [char] (1) NOT NULL ,
+ [subscribe] [varchar] (200) NULL ,
+ [type] [varchar] (50) NULL ,
+CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED
+(
+ [username] ASC,
+ [jid] ASC
+)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[spool] (
+ [id] [numeric](19, 0) IDENTITY (1, 1) NOT NULL ,
+ [username] [varchar] (250) NOT NULL ,
+ [xml] [text] NOT NULL ,
+ [notifyprocessed] [bit] NULL ,
+ [created] [datetime] NULL ,
+ [MustDelete] [bit] NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[users] (
+ [username] [varchar] (250) NOT NULL ,
+ [password] [varchar] (50) NOT NULL ,
+ [created] [datetime] NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[vcard] (
+ [username] [varchar] (250) NOT NULL ,
+ [vcard] [text] NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[vcard_search] (
+ [username] [varchar] (250) NOT NULL ,
+ [lusername] [varchar] (250) NOT NULL ,
+ [fn] [text] NOT NULL ,
+ [lfn] [varchar] (250) NOT NULL ,
+ [family] [text] NOT NULL ,
+ [lfamily] [varchar] (250) NOT NULL ,
+ [given] [text] NOT NULL ,
+ [lgiven] [varchar] (250) NOT NULL ,
+ [middle] [text] NOT NULL ,
+ [lmiddle] [varchar] (250) NOT NULL ,
+ [nickname] [text] NOT NULL ,
+ [lnickname] [varchar] (250) NOT NULL ,
+ [bday] [text] NOT NULL ,
+ [lbday] [varchar] (250) NOT NULL ,
+ [ctry] [text] NOT NULL ,
+ [lctry] [varchar] (250) NOT NULL ,
+ [locality] [text] NOT NULL ,
+ [llocality] [varchar] (250) NOT NULL ,
+ [email] [text] NOT NULL ,
+ [lemail] [varchar] (250) NOT NULL ,
+ [orgname] [text] NOT NULL ,
+ [lorgname] [varchar] (250) NOT NULL ,
+ [orgunit] [text] NOT NULL ,
+ [lorgunit] [varchar] (250) NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[private_storage] (
+ [username] [varchar] (250) NOT NULL ,
+ [namespace] [varchar] (250) NOT NULL ,
+ [data] [text] NOT NULL
+) 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] IDENTITY(1,1) NOT NULL,
+ CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED
+(
+ [id] ASC
+)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
+) 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] [bit] NOT NULL,
+ [match_iq] [bit] NOT NULL,
+ [match_message] [bit] NOT NULL,
+ [match_presence_in] [bit] NOT NULL,
+ [match_presence_out] [bit] NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[roster_version] (
+ [username] [varchar](250) PRIMARY KEY,
+ [version] [text] NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[pubsub_node] (
+ [host] [varchar](250),
+ [node] [varchar](250),
+ [parent] [varchar](250),
+ [type] [varchar](250),
+ [nodeid] [bigint] IDENTITY(1,1) PRIMARY KEY
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[pubsub_node_option] (
+ [nodeid] [bigint],
+ [name] [varchar](250),
+ [val] [varchar](250)
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[pubsub_node_owner] (
+ [nodeid] [bigint],
+ [owner] [varchar](250)
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[pubsub_state] (
+ [nodeid] [bigint],
+ [jid] [varchar](250),
+ [affiliation] [CHAR](1),
+ [subscriptions] [text],
+ [stateid] [bigint] IDENTITY(1,1) PRIMARY KEY
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[pubsub_item] (
+ [nodeid] [bigint],
+ [itemid] [varchar](250),
+ [publisher] [text],
+ [creation] [text],
+ [modification] [text],
+ [payload] [text]
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[pubsub_subscription_opt] (
+ [subid] [varchar](250),
+ [opt_name] [varchar](32),
+ [opt_value] [text]
+) 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
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD
+ CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED
+ (
+ [username],
+ [jid],
+ [grp]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
+ CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED
+ (
+ [username],
+ [id]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[users] WITH NOCHECK ADD
+ CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD
+ CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED
+ (
+ [username]
+ ) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[pubsub_node_option] WITH NOCHECK ADD
+ CONSTRAINT [FK_pubsub_node_option] FOREIGN KEY
+ (
+ [nodeid]
+ ) REFERENCES [dbo].[pubsub_node]
+ (
+ [nodeid]
+ )
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[pubsub_node_owner] WITH NOCHECK ADD
+ CONSTRAINT [FK_pubsub_node_owner] FOREIGN KEY
+ (
+ [nodeid]
+ ) REFERENCES [pubsub_node]
+ (
+ [nodeid]
+ )
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[pubsub_state] WITH NOCHECK ADD
+ CONSTRAINT [FK_pubsub_state] FOREIGN KEY
+ (
+ [nodeid]
+ ) REFERENCES [pubsub_node]
+ (
+ [nodeid]
+ )
+ON DELETE CASCADE
+GO
+
+ALTER TABLE [dbo].[pubsub_item] WITH NOCHECK ADD
+ CONSTRAINT [FK_pubsub_item] FOREIGN KEY
+ (
+ [nodeid]
+ ) REFERENCES [pubsub_node]
+ (
+ [nodeid]
+ )
+ON DELETE CASCADE
+GO
+
+CREATE INDEX [IX_vcard_search_lfn] ON [dbo].[vcard_search]([lfn]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lfamily] ON [dbo].[vcard_search]([lfamily]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lgiven] ON [dbo].[vcard_search]([lgiven]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lmiddle] ON [dbo].[vcard_search]([lmiddle]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lnickname] ON [dbo].[vcard_search]([lnickname]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lbday] ON [dbo].[vcard_search]([lbday]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lctry] ON [dbo].[vcard_search]([lctry]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_llocality] ON [dbo].[vcard_search]([llocality]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lemail] ON [dbo].[vcard_search]([lemail]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lorgname] ON [dbo].[vcard_search]([lorgname]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+CREATE INDEX [IX_vcard_search_lorgunit] ON [dbo].[vcard_search]([lorgunit]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+
+CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[last] WITH NOCHECK ADD
+ CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date]
+GO
+
+ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
+ CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed],
+ CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created],
+ CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete]
+GO
+
+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
+
+ CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_spool_process] ON [dbo].[spool]([created], [notifyprocessed]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IK_Spool_Del] ON [dbo].[spool]([MustDelete]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IK_Spool_Created] ON [dbo].[spool]([created]) WITH FILLFACTOR = 90 ON [PRIMARY]
+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
+
+ CREATE INDEX [IX_pubsub_node_parent] ON [dbo].[pubsub_node]([parent]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_node_tuple] ON [dbo].[pubsub_node]([host], [node]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_node_option_nodeid] ON [dbo].[pubsub_node_option]([nodeid]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_node_owner_nodeid] ON [dbo].[pubsub_node_owner]([nodeid]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_state_jid] ON [dbo].[pubsub_state]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_state_tuple] ON [dbo].[pubsub_state]([nodeid], [jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_item_itemid] ON [dbo].[pubsub_item]([itemid]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_item_tuple] ON [dbo].[pubsub_item]([nodeid], [itemid]) WITH FILLFACTOR = 90 ON [PRIMARY]
+GO
+
+ CREATE INDEX [IX_pubsub_subscription_opt] ON [dbo].[pubsub_subscription_opt]([subid], [opt_name]) WITH FILLFACTOR = 90 ON [PRIMARY]
+Go
+
+/*********************************************************/
+/** These store procedures are for use with ejabberd **/
+/** 1.1 and Microsoft Sql Server 2000 **/
+/** **/
+/** The stored procedures reduce the need to sql **/
+/** compilation of the database and also allow for also **/
+/** provide each of database integration. The stored **/
+/** procedure have been optimized to increase database **/
+/** performance and a reduction of 80% in CPU was **/
+/** achieved over the use of standard sql. **/
+/*********************************************************/
+
+/****** Object: StoredProcedure [dbo].[add_roster] ******/
+/** Add or update user entries in the roster **/
+/*********************************************************/
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_roster]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[add_roster]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_roster_group]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[add_roster_group]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_roster_user]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[add_roster_user]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_roster_groups]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_roster_groups]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_spool]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[add_spool]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_user]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[add_user]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_password]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[set_password]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[clean_spool_msg]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[clean_spool_msg]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_password]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_password]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_last]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_last]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_roster]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_roster]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_spool_msg]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_spool_msg]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_user]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user_return_password]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_user_return_password]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user_roster]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_user_roster]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_and_del_spool_msg]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_and_del_spool_msg]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_last]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_last]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_roster]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster_by_jid]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_roster_by_jid]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster_jid_groups]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_roster_jid_groups]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster_groups]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_roster_groups]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_rostergroup_by_jid]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_rostergroup_by_jid]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_subscription]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_subscription]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[list_users]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[list_users]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_last]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[set_last]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_private_data]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[set_private_data]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_private_data]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_private_data]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user_storage]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_user_storage]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_vcard]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[set_vcard]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_vcard]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_vcard]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_default_privacy_list]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_default_privacy_list]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_privacy_list_names]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_privacy_list_names]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_privacy_list_id]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_privacy_list_id]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_privacy_list_data]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_privacy_list_data]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_privacy_list_data_by_id]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[get_privacy_list_data_by_id]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_default_privacy_list]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[set_default_privacy_list]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[unset_default_privacy_list]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[unset_default_privacy_list]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[remove_privacy_list]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[remove_privacy_list]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_privacy_list]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[add_privacy_list]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_privacy_list]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[set_privacy_list]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_privacy_list_by_id]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_privacy_list_by_id]
+GO
+IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_privacy_lists]') AND type in (N'P', N'PC'))
+DROP PROCEDURE [dbo].[del_privacy_lists]
+GO
+
+CREATE PROCEDURE [dbo].[add_roster]
+ @Username varchar(250),
+ @JID varchar(250),
+ @Nick varchar(50),
+ @Subscription char(1),
+ @Ask char(1),
+ @AskMessage varchar(250),
+ @Server char(1),
+ @Subscribe varchar(200),
+ @Type varchar(50),
+ @Grp varchar(100)
+AS
+BEGIN
+ BEGIN TRANSACTION
+ --- Update Roster if user exist else add roster item
+ IF EXISTS (SELECT username FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
+ BEGIN
+ UPDATE rosterusers
+ SET rosterusers.username=@Username,
+ rosterusers.jid=@JID,
+ rosterusers.nick=@Nick,
+ rosterusers.subscription=@Subscription,
+ rosterusers.ask=@Ask,
+ rosterusers.askmessage=@AskMessage,
+ rosterusers.server=@Server,
+ rosterusers.subscribe=@Subscribe,
+ rosterusers.type=@Type
+ WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
+ END
+ ELSE
+ BEGIN
+ INSERT INTO rosterusers
+ ( rosterusers.username,
+ rosterusers.jid,
+ rosterusers.nick,
+ rosterusers.subscription,
+ rosterusers.ask,
+ rosterusers.askmessage,
+ rosterusers.server,
+ rosterusers.subscribe,
+ rosterusers.type
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Nick,
+ @Subscription,
+ @Ask,
+ @AskMessage,
+ @Server,
+ @Subscribe,
+ @Type
+ );
+ END
+
+ --- Update Roster Groups if exist else add group entry
+ IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
+ BEGIN
+ INSERT INTO rostergroups
+ ( rostergroups.username,
+ rostergroups.jid,
+ rostergroups.grp
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Grp
+ );
+ END
+
+ COMMIT
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_roster_group] ******/
+/** Add or update user group entries in the roster groups **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_roster_group]
+ @Username varchar(250),
+ @JID varchar(250),
+ @Grp varchar(100)
+AS
+BEGIN
+ --- Update Roster Groups if exist else add group
+ IF NOT EXISTS (SELECT username FROM rostergroups WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
+ BEGIN
+ INSERT INTO rostergroups
+ ( rostergroups.username,
+ rostergroups.jid,
+ rostergroups.grp
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Grp
+ )
+ END
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_roster_user] ******/
+/** Add or update user entries in the roster **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_roster_user]
+ @Username varchar(250),
+ @JID varchar(250),
+ @Nick varchar(50),
+ @Subscription char(1),
+ @Ask char(1),
+ @AskMessage varchar(250),
+ @Server char(1),
+ @Subscribe varchar(200),
+ @Type varchar(50),
+ @Grp varchar(100) = Null
+AS
+BEGIN
+ BEGIN TRANSACTION
+ --- Update Roster Users if exist of add new user
+ IF EXISTS (SELECT username FROM rosterusers WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
+ BEGIN
+ UPDATE rosterusers
+ SET rosterusers.username=@Username,
+ rosterusers.jid=@JID,
+ rosterusers.nick=@Nick,
+ rosterusers.subscription=@Subscription,
+ rosterusers.ask=@Ask,
+ rosterusers.askmessage=@AskMessage,
+ rosterusers.server=@Server,
+ rosterusers.subscribe=@Subscribe,
+ rosterusers.type=@Type
+ WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
+ END
+ ELSE
+ BEGIN
+ INSERT INTO rosterusers
+ ( rosterusers.username,
+ rosterusers.jid,
+ rosterusers.nick,
+ rosterusers.subscription,
+ rosterusers.ask,
+ rosterusers.askmessage,
+ rosterusers.server,
+ rosterusers.subscribe,
+ rosterusers.type
+ )
+ VALUES
+ ( @Username,
+ @JID,
+ @Nick,
+ @Subscription,
+ @Ask,
+ @AskMessage,
+ @Server,
+ @Subscribe,
+ @Type
+ );
+ END
+
+ --- Update Roster Group if exist of add new group
+ IF @Grp IS NOT NULL
+ EXECUTE [dbo].[add_roster_group] @Username, @JID, @Grp
+
+ COMMIT
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_roster_groups] ******/
+/** Remove user group entries from the roster groups table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_roster_groups]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+BEGIN
+ DELETE FROM rostergroups
+ WITH (ROWLOCK)
+ WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_spool] ******/
+/** Add a entry to the spool table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_spool]
+ @Username varchar(250),
+ @XML varchar(8000)
+AS
+BEGIN
+ INSERT INTO spool
+ ( spool.username,
+ spool.xml
+ )
+ VALUES
+ ( @Username,
+ @XML
+ )
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[add_user] ******/
+/** Add or update user entries to jabber **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[add_user]
+ @Username varchar(200),
+ @Password varchar(50)
+AS
+BEGIN
+ INSERT INTO users
+ ( [username],
+ [password]
+ )
+ VALUES
+ ( @Username,
+ @Password
+ );
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_password] **/
+/** Update users password **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_password]
+ @Username varchar(200),
+ @Password varchar(50)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM users WITH (NOLOCK) WHERE username=@Username)
+ BEGIN
+ UPDATE users SET username=@Username, password=@Password WHERE username=@Username;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO users (username, password) VALUES (@Username, @Password);
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_password] **/
+/** Retrive the user password **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_password]
+ @Username varchar(200)
+AS
+BEGIN
+ SELECT users.password as password
+ FROM users WITH (NOLOCK)
+ WHERE username=@Username;
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_roster_version] **/
+/** Update users roster_version **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_roster_version]
+ @Username varchar(200),
+ @Version varchar(8000)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM roster_version WITH (NOLOCK) WHERE username=@Username)
+ BEGIN
+ UPDATE roster_version SET username=@Username, version=@Version WHERE username=@Username;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO roster_version (username, version) VALUES (@Username, @Version);
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_version] **/
+/** Retrive the user roster_version **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_version]
+ @Username varchar(200)
+AS
+BEGIN
+ SELECT roster_version.version as version
+ FROM roster_version WITH (NOLOCK)
+ WHERE username=@Username;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[clean_spool_msg] ******/
+/** Delete messages older that 3 days from spool **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[clean_spool_msg]
+AS
+DECLARE
+ @dt datetime,
+ @myRowCount int
+BEGIN
+ -- Delete small amounts because if locks the database table
+ SET ROWCOUNT 500
+ SET @myRowCount = 1
+
+ WHILE (@myRowCount) > 0
+ BEGIN
+ BEGIN TRANSACTION
+ SELECT @dt = DATEADD(d, -3, GETDATE())
+ DELETE FROM spool
+ WITH (ROWLOCK)
+ WHERE (MustDelete=1) OR (Created < @dt);
+
+ SET @myRowCount = @@RowCount
+ COMMIT
+ END
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_last] ******/
+/** Delete an entry from the last table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_last]
+ @Username varchar(250)
+AS
+BEGIN
+ DELETE FROM [last]
+ WITH (ROWLOCK)
+ WHERE [last].username=@Username;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_roster] ******/
+/** Delete an entry from the roster **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_roster]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+BEGIN
+ BEGIN TRANSACTION
+ DELETE FROM rosterusers
+ WITH (ROWLOCK)
+ WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
+
+ DELETE FROM rostergroups
+ WITH (ROWLOCK)
+ WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
+ COMMIT
+END
+GO
+
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_spool_msg] ******/
+/** Delete an entry from the spool table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_spool_msg]
+ @Username varchar(250)
+AS
+BEGIN
+ DELETE FROM spool
+ WITH (ROWLOCK)
+ WHERE spool.username=@Username;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user] ******/
+/** Delete an entry from the user table **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_user]
+ @Username varchar(200)
+AS
+BEGIN
+ DELETE FROM users
+ WITH (ROWLOCK)
+ WHERE username=@Username;
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user_return_password]**/
+/** Delete an entry from the user table and return user password **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[del_user_return_password]
+ @Username varchar(250)
+AS
+DECLARE
+ @Pwd varchar(50)
+BEGIN
+ EXECUTE @Pwd = dbo.get_password @Username
+ DELETE FROM users
+ WITH (ROWLOCK)
+ WHERE username=@Username
+
+ SELECT @Pwd;
+END
+GO
+
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user_roster] **/
+/** Delete the users roster **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[del_user_roster]
+ @Username varchar(250)
+AS
+BEGIN
+ BEGIN TRANSACTION
+ DELETE FROM rosterusers
+ WITH (ROWLOCK)
+ WHERE rosterusers.username = @Username;
+
+ DELETE FROM rostergroups
+ WITH (ROWLOCK)
+ WHERE rostergroups.username = @Username;
+ COMMIT
+END
+GO
+
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_and_del_spool_msg] **/
+/** Fetch and delete the users offline messages **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_and_del_spool_msg]
+ @Username varchar(250)
+AS
+DECLARE
+ @vSpool table( username varchar(1),
+ xml varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM spool with (nolock) WHERE spool.username=@Username)
+ BEGIN
+ SELECT spool.username AS username,
+ spool.xml AS xml
+ FROM spool WITH (NOLOCK)
+ WHERE spool.username=@Username;
+
+ DELETE spool
+ WITH (ROWLOCK)
+ WHERE spool.username=@Username
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vSpool;
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_last] **/
+/** Retrive the last user login **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_last]
+ @Username varchar(250)
+AS
+BEGIN
+ SELECT last.seconds AS seconds,
+ last.state AS state
+ FROM last WITH (NOLOCK)
+ WHERE last.username=@Username;
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster] **/
+/** Retrive the user roster **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster]
+ @Username varchar(250)
+AS
+DECLARE
+ @vRosterusers table( username varchar(1),
+ jid varchar(1),
+ nick varchar(1),
+ subscription varchar(1),
+ ask varchar(1),
+ askmessage varchar(1),
+ server varchar(1),
+ subscribe varchar(1),
+ type varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username)
+ BEGIN
+ SELECT rosterusers.username AS username,
+ rosterusers.jid AS jid,
+ rosterusers.nick AS nick,
+ rosterusers.subscription AS subscription,
+ rosterusers.ask AS ask,
+ rosterusers.askmessage AS askmessage,
+ rosterusers.server AS server,
+ rosterusers.subscribe AS subscribe,
+ rosterusers.type AS type
+ FROM rosterusers WITH (NOLOCK)
+ WHERE rosterusers.username = @Username;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vRosterusers
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_by_jid] **/
+/** Retrive the user roster via JID **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_by_jid]
+ @Username varchar(200),
+ @JID varchar(250)
+AS
+DECLARE
+ @vRosterusers table( username varchar(1),
+ jid varchar(1),
+ nick varchar(1),
+ subscription varchar(1),
+ ask varchar(1),
+ askmessage varchar(1),
+ server varchar(1),
+ subscribe varchar(1),
+ type varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID))
+ BEGIN
+ SELECT rosterusers.username AS username,
+ rosterusers.jid AS jid,
+ rosterusers.nick AS nick,
+ rosterusers.subscription AS subscription,
+ rosterusers.ask AS ask,
+ rosterusers.askmessage AS askmessage,
+ rosterusers.server AS server,
+ rosterusers.subscribe AS subscribe,
+ rosterusers.type AS type
+ FROM rosterusers WITH (NOLOCK)
+ WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vRosterusers
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_jid_groups] **/
+/** Retrieve the user roster groups **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_jid_groups]
+ @Username varchar(200)
+AS
+DECLARE
+ @vrostergroups table( jid varchar(1),
+ grp varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
+ BEGIN
+ SELECT rostergroups.jid AS jid,
+ rostergroups.grp AS grp
+ FROM rostergroups WITH (NOLOCK)
+ WHERE rostergroups.username = @Username;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrostergroups
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_roster_groups] **/
+/** Retrive the user roster groups **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_roster_groups]
+ @Username varchar(200),
+ @JID varchar(250)
+AS
+DECLARE
+ @vrostergroups table( grp varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
+ BEGIN
+ SELECT rostergroups.grp AS grp
+ FROM rostergroups WITH (NOLOCK)
+ WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrostergroups
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_rostergroup_by_jid] **/
+/** Retrive the user roster groups via JID **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_rostergroup_by_jid]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+DECLARE
+ @vrostergroups table(grp varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID)
+ BEGIN
+ SELECT rostergroups.grp AS grp
+ FROM rostergroups WITH (NOLOCK)
+ WHERE rostergroups.username=@Username AND rostergroups.jid=@JID;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrostergroups
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_subscription] **/
+/** Retrive the user subscription requests **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_subscription]
+ @Username varchar(250),
+ @JID varchar(250)
+AS
+DECLARE
+ @vrosterusers table( subscription varchar(1))
+BEGIN
+ IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
+ BEGIN
+ SELECT rosterusers.subscription AS subscription
+ FROM rosterusers WITH (NOLOCK)
+ WHERE rosterusers.username=@Username AND rosterusers.jid=@JID;
+ END
+ ELSE
+ BEGIN
+ SELECT * FROM @vrosterusers
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[list_users] **/
+/** Retrieve a list of all users **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[list_users]
+AS
+BEGIN
+ SELECT users.username AS username FROM users WITH (NOLOCK);
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_last] **/
+/** Update users last login status **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_last]
+ @Username varchar(250),
+ @Seconds varchar(50),
+ @State varchar(100)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM [last] WITH (NOLOCK) WHERE username=@Username)
+ BEGIN
+ UPDATE [last]
+ SET [last].username = @Username,
+ [last].seconds = @Seconds,
+ [last].state = @State
+ WHERE last.username=@Username;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO [last]
+ ( [last].username,
+ [last].seconds,
+ [last].state
+ )
+ VALUES
+ ( @Username,
+ @Seconds,
+ @State
+ )
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_private_data] **/
+/** store user private data by namespace **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_private_data]
+ @Username varchar(250),
+ @Namespace varchar(250),
+ @Data varchar(8000)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM private_storage with (nolock) WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace)
+ BEGIN
+ UPDATE [private_storage]
+ SET [private_storage].username = @Username,
+ [private_storage].namespace = @Namespace,
+ [private_storage].data = @Data
+ WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO [private_storage]
+ ( [private_storage].username,
+ [private_storage].namespace,
+ [private_storage].data
+ )
+ VALUES
+ ( @Username,
+ @Namespace,
+ @Data
+ )
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_private_data] **/
+/** Retrieve user private data by namespace **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_private_data]
+ @Username varchar(250),
+ @Namespace varchar(250)
+AS
+BEGIN
+ SELECT private_storage.data AS data
+ FROM private_storage WITH (NOLOCK)
+ WHERE username=@Username and namespace=@Namespace;
+END
+GO
+
+/***************************************************************/
+/****** Object: StoredProcedure [dbo].[del_user_storage] ******/
+/** Delete private storage area for a given user **/
+/***************************************************************/
+CREATE PROCEDURE [dbo].[del_user_storage]
+ @Username varchar(250)
+AS
+BEGIN
+ DELETE FROM [private_storage]
+ WITH (ROWLOCK)
+ WHERE [private_storage].username=@Username;
+END
+GO
+
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_vcard] **/
+/** Set the user's vCard **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_vcard]
+ @VCard varchar(8000),
+ @Username varchar(250),
+ @Lusername varchar(250),
+ @Fn varchar(8000),
+ @Lfn varchar(250),
+ @Family varchar(8000),
+ @Lfamily varchar(250),
+ @Given varchar(8000),
+ @Lgiven varchar(250),
+ @Middle varchar(8000),
+ @Lmiddle varchar(250),
+ @Nickname varchar(8000),
+ @Lnickname varchar(250),
+ @Bday varchar(8000),
+ @Lbday varchar(250),
+ @Ctry varchar(8000),
+ @Lctry varchar(250),
+ @Locality varchar(8000),
+ @Llocality varchar(250),
+ @Email varchar(8000),
+ @Lemail varchar(250),
+ @Orgname varchar(8000),
+ @Lorgname varchar(250),
+ @Orgunit varchar(8000),
+ @Lorgunit varchar(250)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM vcard with (nolock) WHERE vcard.username = @Username)
+ BEGIN
+ UPDATE [vcard]
+ SET [vcard].username = @LUsername,
+ [vcard].vcard = @Vcard
+ WHERE vcard.username = @LUsername;
+
+ UPDATE [vcard_search]
+ SET [vcard_search].username = @Username,
+ [vcard_search].lusername = @Lusername,
+ [vcard_search].fn = @Fn,
+ [vcard_search].lfn = @Lfn,
+ [vcard_search].family = @Family,
+ [vcard_search].lfamily = @Lfamily,
+ [vcard_search].given = @Given,
+ [vcard_search].lgiven = @Lgiven,
+ [vcard_search].middle = @Middle,
+ [vcard_search].lmiddle = @Lmiddle,
+ [vcard_search].nickname = @Nickname,
+ [vcard_search].lnickname = @Lnickname,
+ [vcard_search].bday = @Bday,
+ [vcard_search].lbday = @Lbday,
+ [vcard_search].ctry = @Ctry,
+ [vcard_search].lctry = @Lctry,
+ [vcard_search].locality = @Locality,
+ [vcard_search].llocality = @Llocality,
+ [vcard_search].email = @Email,
+ [vcard_search].lemail = @Lemail,
+ [vcard_search].orgname = @Orgname,
+ [vcard_search].lorgname = @Lorgname,
+ [vcard_search].orgunit = @Orgunit,
+ [vcard_search].lorgunit = @Lorgunit
+ WHERE vcard_search.lusername = @LUsername;
+ END
+ ELSE
+ BEGIN
+ INSERT INTO [vcard]
+ ( [vcard].username,
+ [vcard].vcard
+ )
+ VALUES
+ ( @lUsername,
+ @Vcard
+ );
+
+ INSERT INTO [vcard_search]
+ (
+ [vcard_search].username ,
+ [vcard_search].lusername ,
+ [vcard_search].fn ,
+ [vcard_search].lfn ,
+ [vcard_search].family ,
+ [vcard_search].lfamily ,
+ [vcard_search].given ,
+ [vcard_search].lgiven ,
+ [vcard_search].middle ,
+ [vcard_search].lmiddle ,
+ [vcard_search].nickname,
+ [vcard_search].lnickname,
+ [vcard_search].bday,
+ [vcard_search].lbday,
+ [vcard_search].ctry,
+ [vcard_search].lctry,
+ [vcard_search].locality,
+ [vcard_search].llocality,
+ [vcard_search].email,
+ [vcard_search].lemail,
+ [vcard_search].orgname,
+ [vcard_search].lorgname,
+ [vcard_search].orgunit,
+ [vcard_search].lorgunit
+ )
+ VALUES
+ (
+ @Username,
+ @Lusername,
+ @Fn,
+ @Lfn,
+ @Family,
+ @Lfamily,
+ @Given,
+ @Lgiven,
+ @Middle,
+ @Lmiddle,
+ @Nickname,
+ @Lnickname,
+ @Bday,
+ @Lbday,
+ @Ctry,
+ @Lctry,
+ @Locality,
+ @Llocality,
+ @Email,
+ @Lemail,
+ @Orgname,
+ @Lorgname,
+ @Orgunit,
+ @Lorgunit
+ )
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_vcard] **/
+/** Retrive the user's vCard **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_vcard]
+ @Username varchar(250)
+AS
+BEGIN
+ SELECT vcard.vcard as vcard
+ FROM vcard WITH (NOLOCK)
+ WHERE username=@Username;
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_default_privacy_list]**/
+/** Retrive the user's default privacy list **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_default_privacy_list]
+ @Username varchar(250)
+AS
+BEGIN
+ SELECT list.name
+ FROM privacy_default_list list WITH (NOLOCK)
+ WHERE list.username=@Username
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_privacy_list_names] **/
+/** Retrive the user's default privacy list names **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_privacy_list_names]
+ @username varchar(250)
+AS
+BEGIN
+ SELECT list.name
+ FROM privacy_list list WITH (NOLOCK)
+ WHERE list.username=@Username
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_privacy_list_id] **/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_privacy_list_id]
+ @username varchar(250),
+ @SName varchar(250)
+AS
+BEGIN
+ SELECT id FROM privacy_list
+ WHERE username=@Username
+ AND name=@SName
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_privacy_list_data] **/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_privacy_list_data]
+ @username varchar(250),
+ @SName varchar(250)
+AS
+BEGIN
+ SELECT l_data.t,
+ l_data.value,
+ l_data.action,
+ l_data.ord,
+ l_data.match_all,
+ l_data.match_iq,
+ l_data.match_message,
+ l_data.match_presence_in,
+ l_data.match_presence_out
+ FROM privacy_list_data l_data (NOLOCK)
+ WHERE l_data.id = (SELECT list.id
+ FROM privacy_list list
+ WHERE list.username=@username
+ AND list.name=@SName)
+ ORDER BY l_data.ord
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[get_privacy_list_data_by_id]**/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[get_privacy_list_data_by_id]
+ @Id bigint
+AS
+BEGIN
+ SELECT l_data.t,
+ l_data.value,
+ l_data.action,
+ l_data.ord,
+ l_data.match_all,
+ l_data.match_iq,
+ l_data.match_message,
+ l_data.match_presence_in,
+ l_data.match_presence_out
+ FROM privacy_list_data l_data (NOLOCK)
+ WHERE l_data.id=@ID
+ ORDER BY l_data.ord
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_default_privacy_list]**/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_default_privacy_list]
+ @username varchar(250),
+ @Sname varchar(250)
+AS
+BEGIN
+ IF EXISTS (SELECT username FROM privacy_default_list with (nolock) WHERE privacy_default_list.username = @Username AND privacy_default_list.name = @Sname)
+ BEGIN
+ UPDATE [privacy_default_list]
+ SET [privacy_default_list].username = @Username,
+ [privacy_default_list].name = @Sname
+ WHERE privacy_default_list.username = @Username
+ END
+ ELSE
+ BEGIN
+ INSERT INTO [privacy_default_list]
+ ( [privacy_default_list].username,
+ [privacy_default_list].name
+ )
+ VALUES
+ ( @Username,
+ @SName
+ )
+ END
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[unset_default_privacy_list]**/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[unset_default_privacy_list]
+ @username varchar(250)
+AS
+BEGIN
+ DELETE
+ FROM privacy_default_list
+ WHERE privacy_default_list.username=@username
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[remove_privacy_list] **/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[remove_privacy_list]
+ @username varchar(250),
+ @SName varchar(250)
+AS
+BEGIN
+ DELETE
+ FROM privacy_list
+ WHERE privacy_list.username=@username
+ AND privacy_list.name=@SName
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[add_privacy_list] **/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[add_privacy_list]
+ @username varchar(250),
+ @SName varchar(250)
+AS
+BEGIN
+ INSERT INTO privacy_list(username, name)
+ VALUES (@username, @SName)
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[set_privacy_list] **/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[set_privacy_list]
+ @Id bigint,
+ @t char(1),
+ @value text,
+ @action char(1),
+ @ord numeric,
+ @match_all bit,
+ @match_iq bit,
+ @match_message bit,
+ @match_presence_in bit,
+ @match_presence_out bit
+AS
+BEGIN
+ insert into privacy_list_data (
+ id,
+ t,
+ value,
+ action,
+ ord,
+ match_all,
+ match_iq,
+ match_message,
+ match_presence_in,
+ match_presence_out
+ )
+ values (@Id,
+ @t,
+ @value,
+ @action,
+ @ord,
+ @match_all,
+ @match_iq,
+ @match_message,
+ @match_presence_in,
+ @match_presence_out
+ )
+
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[del_privacy_list_by_id] **/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[del_privacy_list_by_id]
+ @Id bigint
+AS
+BEGIN
+ DELETE FROM privacy_list_data
+ WHERE privacy_list_data.id=@Id
+END
+GO
+
+/******************************************************************/
+/****** Object: StoredProcedure [dbo].[del_privacy_lists] **/
+/** **/
+/******************************************************************/
+CREATE PROCEDURE [dbo].[del_privacy_lists]
+ @Server varchar(250),
+ @username varchar(250)
+AS
+BEGIN
+ DELETE FROM privacy_list WHERE username=@username
+ DELETE FROM privacy_list_data WHERE convert(varchar,value)=@username+'@'+@Server
+ DELETE FROM privacy_default_list WHERE username=@username
+END
+GO
diff --git a/sql/mysql.sql b/sql/mysql.sql
new file mode 100644
index 000000000..976230117
--- /dev/null
+++ b/sql/mysql.sql
@@ -0,0 +1,284 @@
+--
+-- ejabberd, Copyright (C) 2002-2013 ProcessOne
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License as
+-- published by the Free Software Foundation; either version 2 of the
+-- License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+-- General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
+-- 02111-1307 USA
+--
+
+-- Needs MySQL (at least 4.0.x) with innodb back-end
+SET table_type=InnoDB;
+
+CREATE TABLE users (
+ username varchar(250) PRIMARY KEY,
+ password text NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+
+CREATE TABLE last (
+ username varchar(250) PRIMARY KEY,
+ seconds text NOT NULL,
+ state text NOT NULl
+) CHARACTER SET utf8;
+
+
+CREATE TABLE rosterusers (
+ username varchar(250) NOT NULL,
+ jid varchar(250) NOT NULL,
+ nick text NOT NULL,
+ subscription character(1) NOT NULL,
+ ask character(1) NOT NULL,
+ askmessage text NOT NULL,
+ server character(1) NOT NULL,
+ subscribe text NOT NULL,
+ type text,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers(username(75), jid(75));
+CREATE INDEX i_rosteru_username ON rosterusers(username);
+CREATE INDEX i_rosteru_jid ON rosterusers(jid);
+
+CREATE TABLE rostergroups (
+ username varchar(250) NOT NULL,
+ jid varchar(250) NOT NULL,
+ grp text NOT NULL
+) CHARACTER SET utf8;
+
+CREATE INDEX pk_rosterg_user_jid ON rostergroups(username(75), jid(75));
+
+CREATE TABLE sr_group (
+ name varchar(250) NOT NULL,
+ opts text NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE TABLE sr_user (
+ jid varchar(250) NOT NULL,
+ grp varchar(250) NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE UNIQUE INDEX i_sr_user_jid_group ON sr_user(jid(75), grp(75));
+CREATE INDEX i_sr_user_jid ON sr_user(jid);
+CREATE INDEX i_sr_user_grp ON sr_user(grp);
+
+CREATE TABLE spool (
+ username varchar(250) NOT NULL,
+ xml text NOT NULL,
+ seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE INDEX i_despool USING BTREE ON spool(username);
+
+
+CREATE TABLE vcard (
+ username varchar(250) PRIMARY KEY,
+ vcard mediumtext NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE TABLE vcard_xupdate (
+ username varchar(250) PRIMARY KEY,
+ hash text NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE TABLE vcard_search (
+ username varchar(250) NOT NULL,
+ lusername varchar(250) PRIMARY KEY,
+ fn text NOT NULL,
+ lfn varchar(250) NOT NULL,
+ family text NOT NULL,
+ lfamily varchar(250) NOT NULL,
+ given text NOT NULL,
+ lgiven varchar(250) NOT NULL,
+ middle text NOT NULL,
+ lmiddle varchar(250) NOT NULL,
+ nickname text NOT NULL,
+ lnickname varchar(250) NOT NULL,
+ bday text NOT NULL,
+ lbday varchar(250) NOT NULL,
+ ctry text NOT NULL,
+ lctry varchar(250) NOT NULL,
+ locality text NOT NULL,
+ llocality varchar(250) NOT NULL,
+ email text NOT NULL,
+ lemail varchar(250) NOT NULL,
+ orgname text NOT NULL,
+ lorgname varchar(250) NOT NULL,
+ orgunit text NOT NULL,
+ lorgunit varchar(250) NOT NULL
+) CHARACTER SET utf8;
+
+CREATE INDEX i_vcard_search_lfn ON vcard_search(lfn);
+CREATE INDEX i_vcard_search_lfamily ON vcard_search(lfamily);
+CREATE INDEX i_vcard_search_lgiven ON vcard_search(lgiven);
+CREATE INDEX i_vcard_search_lmiddle ON vcard_search(lmiddle);
+CREATE INDEX i_vcard_search_lnickname ON vcard_search(lnickname);
+CREATE INDEX i_vcard_search_lbday ON vcard_search(lbday);
+CREATE INDEX i_vcard_search_lctry ON vcard_search(lctry);
+CREATE INDEX i_vcard_search_llocality ON vcard_search(llocality);
+CREATE INDEX i_vcard_search_lemail ON vcard_search(lemail);
+CREATE INDEX i_vcard_search_lorgname ON vcard_search(lorgname);
+CREATE INDEX i_vcard_search_lorgunit ON vcard_search(lorgunit);
+
+CREATE TABLE privacy_default_list (
+ username varchar(250) PRIMARY KEY,
+ name varchar(250) NOT NULL
+) CHARACTER SET utf8;
+
+CREATE TABLE privacy_list (
+ username varchar(250) NOT NULL,
+ name varchar(250) NOT NULL,
+ id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE INDEX i_privacy_list_username USING BTREE ON privacy_list(username);
+CREATE UNIQUE INDEX i_privacy_list_username_name USING BTREE ON privacy_list (username(75), name(75));
+
+CREATE TABLE privacy_list_data (
+ id bigint,
+ 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
+) CHARACTER SET utf8;
+
+CREATE TABLE private_storage (
+ username varchar(250) NOT NULL,
+ namespace varchar(250) NOT NULL,
+ data text NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE INDEX i_private_storage_username USING BTREE ON private_storage(username);
+CREATE UNIQUE INDEX i_private_storage_username_namespace USING BTREE ON private_storage(username(75), namespace(75));
+
+-- Not tested in mysql
+CREATE TABLE roster_version (
+ username varchar(250) PRIMARY KEY,
+ version text NOT NULL
+) CHARACTER SET utf8;
+
+-- To update from 1.x:
+-- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask;
+-- UPDATE rosterusers SET askmessage = '';
+-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
+
+CREATE TABLE pubsub_node (
+ host text,
+ node text,
+ parent text,
+ type text,
+ nodeid bigint auto_increment primary key
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_node_parent ON pubsub_node(parent(120));
+CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(20), node(120));
+
+CREATE TABLE pubsub_node_option (
+ nodeid bigint,
+ name text,
+ val text
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option(nodeid);
+ALTER TABLE `pubsub_node_option` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_node_owner (
+ nodeid bigint,
+ owner text
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner(nodeid);
+ALTER TABLE `pubsub_node_owner` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_state (
+ nodeid bigint,
+ jid text,
+ affiliation character(1),
+ subscriptions text,
+ stateid bigint auto_increment primary key
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_state_jid ON pubsub_state(jid(60));
+CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state(nodeid, jid(60));
+ALTER TABLE `pubsub_state` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_item (
+ nodeid bigint,
+ itemid text,
+ publisher text,
+ creation text,
+ modification text,
+ payload text
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_item_itemid ON pubsub_item(itemid(36));
+CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item(nodeid, itemid(36));
+ALTER TABLE `pubsub_item` ADD FOREIGN KEY (`nodeid`) REFERENCES `pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_subscription_opt (
+ subid text,
+ opt_name varchar(32),
+ opt_value text
+);
+CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt(subid(32), opt_name(32));
+
+CREATE TABLE muc_room (
+ name text NOT NULL,
+ host text NOT NULL,
+ opts text NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE UNIQUE INDEX i_muc_room_name_host USING BTREE ON muc_room(name(75), host(75));
+
+CREATE TABLE muc_registered (
+ jid text NOT NULL,
+ host text NOT NULL,
+ nick text NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+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 irc_custom (
+ jid text NOT NULL,
+ host text NOT NULL,
+ data text NOT NULL,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE UNIQUE INDEX i_irc_custom_jid_host USING BTREE ON irc_custom(jid(75), host(75));
+
+CREATE TABLE motd (
+ username varchar(250) PRIMARY KEY,
+ xml text,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE TABLE caps_features (
+ node varchar(250) NOT NULL,
+ subnode varchar(250) NOT NULL,
+ feature text,
+ created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
+) CHARACTER SET utf8;
+
+CREATE INDEX i_caps_features_node_subnode ON caps_features(node(75), subnode(75));
diff --git a/sql/pg.sql b/sql/pg.sql
new file mode 100644
index 000000000..0b641d575
--- /dev/null
+++ b/sql/pg.sql
@@ -0,0 +1,285 @@
+--
+-- ejabberd, Copyright (C) 2002-2013 ProcessOne
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License as
+-- published by the Free Software Foundation; either version 2 of the
+-- License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+-- General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
+-- 02111-1307 USA
+--
+
+CREATE TABLE users (
+ username text PRIMARY KEY,
+ "password" text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+
+CREATE TABLE last (
+ username text PRIMARY KEY,
+ seconds text NOT NULL,
+ state text NOT NULL
+);
+
+
+CREATE TABLE rosterusers (
+ username text NOT NULL,
+ jid text NOT NULL,
+ nick text NOT NULL,
+ subscription character(1) NOT NULL,
+ ask character(1) NOT NULL,
+ askmessage text NOT NULL,
+ server character(1) NOT NULL,
+ subscribe text,
+ "type" text,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers USING btree (username, jid);
+CREATE INDEX i_rosteru_username ON rosterusers USING btree (username);
+CREATE INDEX i_rosteru_jid ON rosterusers USING btree (jid);
+
+
+CREATE TABLE rostergroups (
+ username text NOT NULL,
+ jid text NOT NULL,
+ grp text NOT NULL
+);
+
+CREATE INDEX pk_rosterg_user_jid ON rostergroups USING btree (username, jid);
+
+CREATE TABLE sr_group (
+ name text NOT NULL,
+ opts text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE TABLE sr_user (
+ jid text NOT NULL,
+ grp text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE UNIQUE INDEX i_sr_user_jid_grp ON sr_user USING btree (jid, grp);
+CREATE INDEX i_sr_user_jid ON sr_user USING btree (jid);
+CREATE INDEX i_sr_user_grp ON sr_user USING btree (grp);
+
+CREATE TABLE spool (
+ username text NOT NULL,
+ xml text NOT NULL,
+ seq SERIAL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_despool ON spool USING btree (username);
+
+
+CREATE TABLE vcard (
+ username text PRIMARY KEY,
+ vcard text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE TABLE vcard_xupdate (
+ username text PRIMARY KEY,
+ hash text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE TABLE vcard_search (
+ username text NOT NULL,
+ lusername text PRIMARY KEY,
+ fn text NOT NULL,
+ lfn text NOT NULL,
+ family text NOT NULL,
+ lfamily text NOT NULL,
+ given text NOT NULL,
+ lgiven text NOT NULL,
+ middle text NOT NULL,
+ lmiddle text NOT NULL,
+ nickname text NOT NULL,
+ lnickname text NOT NULL,
+ bday text NOT NULL,
+ lbday text NOT NULL,
+ ctry text NOT NULL,
+ lctry text NOT NULL,
+ locality text NOT NULL,
+ llocality text NOT NULL,
+ email text NOT NULL,
+ lemail text NOT NULL,
+ orgname text NOT NULL,
+ lorgname text NOT NULL,
+ orgunit text NOT NULL,
+ lorgunit text NOT NULL
+);
+
+CREATE INDEX i_vcard_search_lfn ON vcard_search(lfn);
+CREATE INDEX i_vcard_search_lfamily ON vcard_search(lfamily);
+CREATE INDEX i_vcard_search_lgiven ON vcard_search(lgiven);
+CREATE INDEX i_vcard_search_lmiddle ON vcard_search(lmiddle);
+CREATE INDEX i_vcard_search_lnickname ON vcard_search(lnickname);
+CREATE INDEX i_vcard_search_lbday ON vcard_search(lbday);
+CREATE INDEX i_vcard_search_lctry ON vcard_search(lctry);
+CREATE INDEX i_vcard_search_llocality ON vcard_search(llocality);
+CREATE INDEX i_vcard_search_lemail ON vcard_search(lemail);
+CREATE INDEX i_vcard_search_lorgname ON vcard_search(lorgname);
+CREATE INDEX i_vcard_search_lorgunit ON vcard_search(lorgunit);
+
+CREATE TABLE privacy_default_list (
+ username text PRIMARY KEY,
+ name text NOT NULL
+);
+
+CREATE TABLE privacy_list (
+ username text NOT NULL,
+ name text NOT NULL,
+ id SERIAL UNIQUE,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_privacy_list_username ON privacy_list USING btree (username);
+CREATE UNIQUE INDEX i_privacy_list_username_name ON privacy_list USING btree (username, name);
+
+CREATE TABLE privacy_list_data (
+ id bigint REFERENCES privacy_list(id) ON DELETE CASCADE,
+ 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
+);
+
+CREATE TABLE private_storage (
+ username text NOT NULL,
+ namespace text NOT NULL,
+ data text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_private_storage_username ON private_storage USING btree (username);
+CREATE UNIQUE INDEX i_private_storage_username_namespace ON private_storage USING btree (username, namespace);
+
+
+CREATE TABLE roster_version (
+ username text PRIMARY KEY,
+ version text NOT NULL
+);
+
+-- To update from 0.9.8:
+-- CREATE SEQUENCE spool_seq_seq;
+-- ALTER TABLE spool ADD COLUMN seq integer;
+-- ALTER TABLE spool ALTER COLUMN seq SET DEFAULT nextval('spool_seq_seq');
+-- UPDATE spool SET seq = DEFAULT;
+-- ALTER TABLE spool ALTER COLUMN seq SET NOT NULL;
+
+-- To update from 1.x:
+-- ALTER TABLE rosterusers ADD COLUMN askmessage text;
+-- UPDATE rosterusers SET askmessage = '';
+-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
+
+CREATE TABLE pubsub_node (
+ host text,
+ node text,
+ parent text,
+ "type" text,
+ nodeid SERIAL UNIQUE
+);
+CREATE INDEX i_pubsub_node_parent ON pubsub_node USING btree (parent);
+CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node USING btree (host, node);
+
+CREATE TABLE pubsub_node_option (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ name text,
+ val text
+);
+CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option USING btree (nodeid);
+
+CREATE TABLE pubsub_node_owner (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ owner text
+);
+CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner USING btree (nodeid);
+
+CREATE TABLE pubsub_state (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ jid text,
+ affiliation character(1),
+ subscriptions text,
+ stateid SERIAL UNIQUE
+);
+CREATE INDEX i_pubsub_state_jid ON pubsub_state USING btree (jid);
+CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state USING btree (nodeid, jid);
+
+CREATE TABLE pubsub_item (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ itemid text,
+ publisher text,
+ creation text,
+ modification text,
+ payload text
+);
+CREATE INDEX i_pubsub_item_itemid ON pubsub_item USING btree (itemid);
+CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item USING btree (nodeid, itemid);
+
+CREATE TABLE pubsub_subscription_opt (
+ subid text,
+ opt_name varchar(32),
+ opt_value text
+);
+CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt USING btree (subid, opt_name);
+
+CREATE TABLE muc_room (
+ name text NOT NULL,
+ host text NOT NULL,
+ opts text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room USING btree (name, host);
+
+CREATE TABLE muc_registered (
+ jid text NOT NULL,
+ host text NOT NULL,
+ nick text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+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 irc_custom (
+ jid text NOT NULL,
+ host text NOT NULL,
+ data text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE UNIQUE INDEX i_irc_custom_jid_host ON irc_custom USING btree (jid, host);
+
+CREATE TABLE motd (
+ username text PRIMARY KEY,
+ xml text,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE TABLE caps_features (
+ node text NOT NULL,
+ subnode text NOT NULL,
+ feature text,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_caps_features_node_subnode ON caps_features USING btree (node, subnode);
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-05 02:05:08 +0000