From 4d8f7706240a1603468968f47fc7b150b788d62f Mon Sep 17 00:00:00 2001 From: Evgeniy Khramtsov Date: Mon, 8 Apr 2013 11:12:54 +0200 Subject: Switch to rebar build tool Use dynamic Rebar configuration Make iconv dependency optional Disable transient_supervisors compile option Add hipe compilation support Only compile ibrowse and lhttpc when needed Make it possible to generate an OTP application release Add --enable-debug compile option Add --enable-all compiler option Add --enable-tools configure option Add --with-erlang configure option. Add --enable-erlang-version-check configure option. Add lager support Improve the test suite --- sql/mssql2000.sql | 1096 ++++++++++++++++++++++++++++++++ sql/mssql2005.sql | 1803 +++++++++++++++++++++++++++++++++++++++++++++++++++++ sql/mysql.sql | 284 +++++++++ sql/pg.sql | 285 +++++++++ 4 files changed, 3468 insertions(+) create mode 100644 sql/mssql2000.sql create mode 100644 sql/mssql2005.sql create mode 100644 sql/mysql.sql create mode 100644 sql/pg.sql (limited to 'sql') 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); -- cgit v1.2.3