Project: ejabberd

Revision: 1558

Author: mremond

Date: 02 Sep 2008 10:14:42

• /trunk/src/odbc/mssql2000.sql

  ......	@@ -0,0 +1,1045 @@
  	1	/*
  	2	* ejabberd, Copyright (C) 2002-2008 ProcessOne
  	3	*
  	4	* This program is free software; you can redistribute it and/or
  	5	* modify it under the terms of the GNU General Public License as
  	6	* published by the Free Software Foundation; either version 2 of the
  	7	* License, or (at your option) any later version.
  	8	*
  	9	* This program is distributed in the hope that it will be useful,
  	10	* but WITHOUT ANY WARRANTY; without even the implied warranty of
  	11	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  	12	* General Public License for more details.
  	13	*
  	14	* You should have received a copy of the GNU General Public License
  	15	* along with this program; if not, write to the Free Software
  	16	* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
  	17	* 02111-1307 USA
  	18	*
  	19	*/
  	20
  	21	SET ANSI_NULLS ON
  	22	GO
  	23	SET QUOTED_IDENTIFIER ON
  	24	GO
  	25
  	26	exec sp_dboption N'ejabberd', N'autoclose', N'false'
  	27	GO
  	28
  	29	exec sp_dboption N'ejabberd', N'bulkcopy', N'true'
  	30	GO
  	31
  	32	exec sp_dboption N'ejabberd', N'trunc. log', N'false'
  	33	GO
  	34
  	35	exec sp_dboption N'ejabberd', N'torn page detection', N'true'
  	36	GO
  	37
  	38	exec sp_dboption N'ejabberd', N'read only', N'false'
  	39	GO
  	40
  	41	exec sp_dboption N'ejabberd', N'dbo use', N'false'
  	42	GO
  	43
  	44	exec sp_dboption N'ejabberd', N'single', N'false'
  	45	GO
  	46
  	47	exec sp_dboption N'ejabberd', N'autoshrink', N'false'
  	48	GO
  	49
  	50	exec sp_dboption N'ejabberd', N'ANSI null default', N'false'
  	51	GO
  	52
  	53	exec sp_dboption N'ejabberd', N'recursive triggers', N'false'
  	54	GO
  	55
  	56	exec sp_dboption N'ejabberd', N'ANSI nulls', N'false'
  	57	GO
  	58
  	59	exec sp_dboption N'ejabberd', N'concat null yields null', N'false'
  	60	GO
  	61
  	62	exec sp_dboption N'ejabberd', N'cursor close on commit', N'false'
  	63	GO
  	64
  	65	exec sp_dboption N'ejabberd', N'default to local cursor', N'false'
  	66	GO
  	67
  	68	exec sp_dboption N'ejabberd', N'quoted identifier', N'false'
  	69	GO
  	70
  	71	exec sp_dboption N'ejabberd', N'ANSI warnings', N'false'
  	72	GO
  	73
  	74	exec sp_dboption N'ejabberd', N'auto create statistics', N'true'
  	75	GO
  	76
  	77	exec sp_dboption N'ejabberd', N'auto update statistics', N'true'
  	78	GO
  	79
  	80	use [ejabberd]
  	81	GO
  	82
  	83	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[last]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	84	drop table [dbo].[last]
  	85	GO
  	86
  	87	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rostergroups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	88	drop table [dbo].[rostergroups]
  	89	GO
  	90
  	91	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rosterusers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	92	drop table [dbo].[rosterusers]
  	93	GO
  	94
  	95	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spool]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	96	drop table [dbo].[spool]
  	97	GO
  	98
  	99	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	100	drop table [dbo].[users]
  	101	GO
  	102
  	103	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	104	drop table [dbo].[vcard]
  	105	GO
  	106
  	107	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	108	drop table [dbo].[private_storage]
  	109	GO
  	110
  	111	CREATE TABLE [dbo].[last] (
  	112	[username] [varchar] (250) NOT NULL ,
  	113	[seconds] [varchar] (50) NOT NULL ,
  	114	[state] [varchar] (100) NOT NULL ,
  	115	) ON [PRIMARY]
  	116	GO
  	117
  	118	CREATE TABLE [dbo].[rostergroups] (
  	119	[username] [varchar] (250) NOT NULL ,
  	120	[jid] [varchar] (250) NOT NULL ,
  	121	[grp] [varchar] (100) NOT NULL
  	122	) ON [PRIMARY]
  	123	GO
  	124
  	125	CREATE TABLE [dbo].[rosterusers] (
  	126	[username] [varchar] (250) NOT NULL ,
  	127	[jid] [varchar] (250) NOT NULL ,
  	128	[nick] [varchar] (50) NOT NULL ,
  	129	[subscription] [char] (1) NOT NULL ,
  	130	[ask] [char] (1) NOT NULL ,
  	131	[askmessage] [varchar] (250) NOT NULL ,
  	132	[server] [char] (1) NOT NULL ,
  	133	[subscribe] [varchar] (200) NULL ,
  	134	[type] [varchar] (50) NULL
  	135	) ON [PRIMARY]
  	136	GO
  	137
  	138	CREATE TABLE [dbo].[spool] (
  	139	[id] [numeric](19, 0) IDENTITY (1, 1) NOT NULL ,
  	140	[username] [varchar] (250) NOT NULL ,
  	141	[xml] [text] NOT NULL ,
  	142	[notifyprocessed] [bit] NULL ,
  	143	[created] [datetime] NULL ,
  	144	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  	145	GO
  	146
  	147	CREATE TABLE [dbo].[users] (
  	148	[username] [varchar] (250) NOT NULL ,
  	149	[password] [varchar] (50) NOT NULL ,
  	150	[created] [datetime] NULL
  	151	) ON [PRIMARY]
  	152	GO
  	153
  	154	CREATE TABLE [dbo].[vcard] (
  	155	[username] [varchar] (250) NOT NULL ,
  	156	[full_name] [varchar] (250) NULL ,
  	157	[first_name] [varchar] (50) NULL ,
  	158	[last_name] [varchar] (50) NULL ,
  	159	[nick_name] [varchar] (50) NULL ,
  	160	[url] [varchar] (1024) NULL ,
  	161	[address1] [varchar] (50) NULL ,
  	162	[address2] [varchar] (50) NULL ,
  	163	[locality] [varchar] (50) NULL ,
  	164	[region] [varchar] (50) NULL ,
  	165	[pcode] [varchar] (50) NULL ,
  	166	[country] [varchar] (50) NULL ,
  	167	[telephone] [varchar] (50) NULL ,
  	168	[email] [varchar] (250) NULL ,
  	169	[orgname] [varchar] (50) NULL ,
  	170	[orgunit] [varchar] (50) NULL ,
  	171	[title] [varchar] (50) NULL ,
  	172	[role] [varchar] (50) NULL ,
  	173	[b_day] [datetime] NULL ,
  	174	[descr] [varchar] (500) NULL
  	175	) ON [PRIMARY]
  	176	GO
  	177
  	178	CREATE TABLE [dbo].[private_storage] (
  	179	[username] [varchar] (250) NOT NULL ,
  	180	[namespace] [varchar] (250) NOT NULL ,
  	181	[data] [text] NOT NULL
  	182	) ON [PRIMARY]
  	183	GO
  	184
  	185	CREATE TABLE [dbo].[privacy_default_list] (
  	186	[username] [varchar] (250) NOT NULL,
  	187	[name] [varchar] (250) NOT NULL
  	188	) ON [PRIMARY]
  	189	GO
  	190
  	191	CREATE TABLE [dbo].[privacy_list] (
  	192	[username] [varchar] (250) NOT NULL,
  	193	[name] [varchar] (250) NOT NULL,
  	194	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
  	195	) ON [PRIMARY]
  	196	GO
  	197
  	198	CREATE TABLE [dbo].[privacy_list_data] (
  	199	[id] [bigint] NOT NULL,
  	200	[t] [character] (1) NOT NULL,
  	201	[value] [text] NOT NULL,
  	202	[action] [character] (1) NOT NULL,
  	203	[ord] [NUMERIC] NOT NULL,
  	204	[match_all] [boolean] NOT NULL,
  	205	[match_iq] [boolean] NOT NULL,
  	206	[match_message] [boolean] NOT NULL,
  	207	[match_presence_in] [boolean] NOT NULL,
  	208	[match_presence_out] [boolean] NOT NULL
  	209	) ON [PRIMARY]
  	210	GO
  	211
  	212	/* Constraints to add:
  	213	- id in privacy_list is a SERIAL autogenerated number
  	214	- id in privacy_list_data must exist in the table privacy_list */
  	215
  	216	ALTER TABLE [dbo].[last] WITH NOCHECK ADD
  	217	CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED
  	218	(
  	219	[username]
  	220	) WITH FILLFACTOR = 90 ON [PRIMARY]
  	221	GO
  	222
  	223	ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD
  	224	CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED
  	225	(
  	226	[username],
  	227	[jid],
  	228	[grp]
  	229	) WITH FILLFACTOR = 90 ON [PRIMARY]
  	230	GO
  	231
  	232	ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
  	233	CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED
  	234	(
  	235	[username],
  	236	[id]
  	237	) WITH FILLFACTOR = 90 ON [PRIMARY]
  	238	GO
  	239
  	240	ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  	241	CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
  	242	(
  	243	[username]
  	244	) WITH FILLFACTOR = 90 ON [PRIMARY]
  	245	GO
  	246
  	247	ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD
  	248	CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED
  	249	(
  	250	[username]
  	251	) WITH FILLFACTOR = 90 ON [PRIMARY]
  	252	GO
  	253
  	254	CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	255	GO
  	256
  	257	ALTER TABLE [dbo].[last] WITH NOCHECK ADD
  	258	CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date]
  	259	GO
  	260
  	261	ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
  	262	CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed],
  	263	CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created],
  	264	CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete]
  	265	GO
  	266
  	267	ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  	268	CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
  	269	GO
  	270
  	271	ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD
  	272	CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED
  	273	(
  	274	[username]
  	275	) WITH FILLFACTOR = 90 ON [PRIMARY]
  	276	GO
  	277
  	278	CREATE INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	279	GO
  	280
  	281	CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	282	GO
  	283
  	284	CREATE INDEX [IX_rosterusers_jid] ON [dbo].[rosterusers]([username], [jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	285	GO
  	286
  	287	CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	288	GO
  	289
  	290	CREATE INDEX [IX_spool_process] ON [dbo].[spool]([created], [notifyprocessed]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	291	GO
  	292
  	293	CREATE INDEX [IK_Spool_Del] ON [dbo].[spool]([MustDelete]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	294	GO
  	295
  	296	CREATE INDEX [IK_Spool_Created] ON [dbo].[spool]([created]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	297	GO
  	298
  	299	CREATE INDEX [IX_private_user] ON [dbo].[private_storage]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	300	GO
  	301
  	302	CREATE INDEX [IX_private_user_ns] ON [dbo].[private_storage]([username], [namespace]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	303	GO
  	304
  	305	CREATE INDEX [IX_privacy_list_username] ON [dbo].[privacy_list]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	306	GO
  	307
  	308	CREATE INDEX [IX_privacy_list_username_name] ON [dbo].[privacy_list]([username], [name]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	309	GO
  	310
  	311	/*********************************************************/
  	312	/** These store procedures are for use with ejabberd **/
  	313	/** 1.1 and Microsoft Sql Server 2000 **/
  	314	/** **/
  	315	/** The stored procedures reduce the need to sql **/
  	316	/** compilation of the database and also allow for also **/
  	317	/** provide each of database integration. The stored **/
  	318	/** procedure have been optimized to increase database **/
  	319	/** performance and a reduction of 80% in CPU was **/
  	320	/** achieved over the use of standard sql. **/
  	321	/*********************************************************/
  	322
  	323	/****** Object: StoredProcedure [dbo].[add_roster] ******/
  	324	/** Add or update user entries in the roster **/
  	325	/*********************************************************/
  	326	CREATE PROCEDURE [dbo].[add_roster]
  	327	@Username varchar(250),
  	328	@JID varchar(250),
  	329	@Nick varchar(50),
  	330	@Subscription char(1),
  	331	@Ask char(1),
  	332	@AskMessage varchar(250),
  	333	@Server char(1),
  	334	@Subscribe varchar(200),
  	335	@Type varchar(50),
  	336	@Grp varchar(100)
  	337	AS
  	338	BEGIN
  	339	BEGIN TRANSACTION
  	340	--- Update Roster if user exist else add roster item
  	341	IF EXISTS (SELECT username FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
  	342	BEGIN
  	343	UPDATE rosterusers
  	344	SET rosterusers.username=@Username,
  	345	rosterusers.jid=@JID,
  	346	rosterusers.nick=@Nick,
  	347	rosterusers.subscription=@Subscription,
  	348	rosterusers.ask=@Ask,
  	349	rosterusers.askmessage=@AskMessage,
  	350	rosterusers.server=@Server,
  	351	rosterusers.subscribe=@Subscribe,
  	352	rosterusers.type=@Type
  	353	WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
  	354	END
  	355	ELSE
  	356	BEGIN
  	357	INSERT INTO rosterusers
  	358	( rosterusers.username,
  	359	rosterusers.jid,
  	360	rosterusers.nick,
  	361	rosterusers.subscription,
  	362	rosterusers.ask,
  	363	rosterusers.askmessage,
  	364	rosterusers.server,
  	365	rosterusers.subscribe,
  	366	rosterusers.type
  	367	)
  	368	VALUES
  	369	( @Username,
  	370	@JID,
  	371	@Nick,
  	372	@Subscription,
  	373	@Ask,
  	374	@AskMessage,
  	375	@Server,
  	376	@Subscribe,
  	377	@Type
  	378	);
  	379	END
  	380
  	381	--- Update Roster Groups if exist else add group entry
  	382	IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
  	383	BEGIN
  	384	INSERT INTO rostergroups
  	385	( rostergroups.username,
  	386	rostergroups.jid,
  	387	rostergroups.grp
  	388	)
  	389	VALUES
  	390	( @Username,
  	391	@JID,
  	392	@Grp
  	393	);
  	394	END
  	395
  	396	COMMIT
  	397	END
  	398	GO
  	399
  	400	/***************************************************************/
  	401	/****** Object: StoredProcedure [dbo].[add_roster_group] ******/
  	402	/** Add or update user group entries in the roster groups **/
  	403	/***************************************************************/
  	404	CREATE PROCEDURE [dbo].[add_roster_group]
  	405	@Username varchar(250),
  	406	@JID varchar(250),
  	407	@Grp varchar(100)
  	408	AS
  	409	BEGIN
  	410	--- Update Roster Groups if exist else add group
  	411	IF NOT EXISTS (SELECT username FROM rostergroups WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
  	412	BEGIN
  	413	INSERT INTO rostergroups
  	414	( rostergroups.username,
  	415	rostergroups.jid,
  	416	rostergroups.grp
  	417	)
  	418	VALUES
  	419	( @Username,
  	420	@JID,
  	421	@Grp
  	422	)
  	423	END
  	424	END
  	425	GO
  	426
  	427	/***************************************************************/
  	428	/****** Object: StoredProcedure [dbo].[add_roster_user] ******/
  	429	/** Add or update user entries in the roster **/
  	430	/***************************************************************/
  	431	CREATE PROCEDURE [dbo].[add_roster_user]
  	432	@Username varchar(250),
  	433	@JID varchar(250),
  	434	@Nick varchar(50),
  	435	@Subscription char(1),
  	436	@Ask char(1),
  	437	@AskMessage varchar(250),
  	438	@Server char(1),
  	439	@Subscribe varchar(200),
  	440	@Type varchar(50),
  	441	@Grp varchar(100) = Null
  	442	AS
  	443	BEGIN
  	444	BEGIN TRANSACTION
  	445	--- Update Roster Users if exist of add new user
  	446	IF EXISTS (SELECT username FROM rosterusers WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
  	447	BEGIN
  	448	UPDATE rosterusers
  	449	SET rosterusers.username=@Username,
  	450	rosterusers.jid=@JID,
  	451	rosterusers.nick=@Nick,
  	452	rosterusers.subscription=@Subscription,
  	453	rosterusers.ask=@Ask,
  	454	rosterusers.askmessage=@AskMessage,
  	455	rosterusers.server=@Server,
  	456	rosterusers.subscribe=@Subscribe,
  	457	rosterusers.type=@Type
  	458	WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
  	459	END
  	460	ELSE
  	461	BEGIN
  	462	INSERT INTO rosterusers
  	463	( rosterusers.username,
  	464	rosterusers.jid,
  	465	rosterusers.nick,
  	466	rosterusers.subscription,
  	467	rosterusers.ask,
  	468	rosterusers.askmessage,
  	469	rosterusers.server,
  	470	rosterusers.subscribe,
  	471	rosterusers.type
  	472	)
  	473	VALUES
  	474	( @Username,
  	475	@JID,
  	476	@Nick,
  	477	@Subscription,
  	478	@Ask,
  	479	@AskMessage,
  	480	@Server,
  	481	@Subscribe,
  	482	@Type
  	483	);
  	484	END
  	485
  	486	--- Update Roster Group if exist of add new group
  	487	IF @Grp IS NOT NULL
  	488	EXECUTE [dbo].[add_roster_group] @Username, @JID, @Grp
  	489
  	490	COMMIT
  	491	END
  	492	GO
  	493
  	494	/***************************************************************/
  	495	/****** Object: StoredProcedure [dbo].[del_roster_groups] ******/
  	496	/** Remove user group entries from the roster groups table **/
  	497	/***************************************************************/
  	498	CREATE PROCEDURE [dbo].[del_roster_groups]
  	499	@Username varchar(250),
  	500	@JID varchar(250)
  	501	AS
  	502	BEGIN
  	503	DELETE FROM rostergroups
  	504	WITH (ROWLOCK)
  	505	WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
  	506	END
  	507	GO
  	508
  	509	/***************************************************************/
  	510	/****** Object: StoredProcedure [dbo].[add_spool] ******/
  	511	/** Add a entry to the spool table **/
  	512	/***************************************************************/
  	513	CREATE PROCEDURE [dbo].[add_spool]
  	514	@Username varchar(250),
  	515	@XML varchar(8000)
  	516	AS
  	517	BEGIN
  	518	INSERT INTO spool
  	519	( spool.username,
  	520	spool.xml
  	521	)
  	522	VALUES
  	523	( @Username,
  	524	@XML
  	525	)
  	526	END
  	527	GO
  	528
  	529	/***************************************************************/
  	530	/****** Object: StoredProcedure [dbo].[add_user] ******/
  	531	/** Add or update user entries to jabber **/
  	532	/***************************************************************/
  	533	CREATE PROCEDURE [dbo].[add_user]
  	534	@Username varchar(200),
  	535	@Password varchar(50)
  	536	AS
  	537	BEGIN
  	538	INSERT INTO users
  	539	( [username],
  	540	[password]
  	541	)
  	542	VALUES
  	543	( @Username,
  	544	@Password
  	545	);
  	546	END
  	547	GO
  	548
  	549	/******************************************************************/
  	550	/****** Object: StoredProcedure [dbo].[set_password] **/
  	551	/** Update users password **/
  	552	/******************************************************************/
  	553	CREATE PROCEDURE [dbo].[set_password]
  	554	@Username varchar(200),
  	555	@Password varchar(50)
  	556	AS
  	557	BEGIN
  	558	IF EXISTS (SELECT username FROM users WITH (NOLOCK) WHERE username=@Username)
  	559	BEGIN
  	560	UPDATE users SET username=@Username, password=@Password WHERE username=@Username;
  	561	END
  	562	ELSE
  	563	BEGIN
  	564	INSERT INTO users (username, password) VALUES (@Username, @Password);
  	565	END
  	566	END
  	567	GO
  	568
  	569	/******************************************************************/
  	570	/****** Object: StoredProcedure [dbo].[get_password] **/
  	571	/** Retrive the user password **/
  	572	/******************************************************************/
  	573	CREATE PROCEDURE [dbo].[get_password]
  	574	@Username varchar(200)
  	575	AS
  	576	BEGIN
  	577	SELECT users.password as password
  	578	FROM users WITH (NOLOCK)
  	579	WHERE username=@Username;
  	580	END
  	581	GO
  	582
  	583	/***************************************************************/
  	584	/****** Object: StoredProcedure [dbo].[clean_spool_msg] ******/
  	585	/** Delete messages older that 3 days from spool **/
  	586	/***************************************************************/
  	587	CREATE PROCEDURE [dbo].[clean_spool_msg]
  	588	AS
  	589	DECLARE
  	590	@dt datetime,
  	591	@myRowCount int
  	592	BEGIN
  	593	-- Delete small amounts because if locks the database table
  	594	SET ROWCOUNT 500
  	595	SET @myRowCount = 1
  	596
  	597	WHILE (@myRowCount) > 0
  	598	BEGIN
  	599	BEGIN TRANSACTION
  	600	SELECT @dt = DATEADD(d, -3, GETDATE())
  	601	DELETE FROM spool
  	602	WITH (ROWLOCK)
  	603	WHERE (MustDelete=1) OR (Created < @dt);
  	604
  	605	SET @myRowCount = @@RowCount
  	606	COMMIT
  	607	END
  	608	END
  	609	GO
  	610
  	611	/***************************************************************/
  	612	/****** Object: StoredProcedure [dbo].[del_last] ******/
  	613	/** Delete an entry from the last table **/
  	614	/***************************************************************/
  	615	CREATE PROCEDURE [dbo].[del_last]
  	616	@Username varchar(250)
  	617	AS
  	618	BEGIN
  	619	DELETE FROM [last]
  	620	WITH (ROWLOCK)
  	621	WHERE [last].username=@Username;
  	622	END
  	623	GO
  	624
  	625	/***************************************************************/
  	626	/****** Object: StoredProcedure [dbo].[del_roster] ******/
  	627	/** Delete an entry from the roster **/
  	628	/***************************************************************/
  	629	CREATE PROCEDURE [dbo].[del_roster]
  	630	@Username varchar(250),
  	631	@JID varchar(250)
  	632	AS
  	633	BEGIN
  	634	BEGIN TRANSACTION
  	635	DELETE FROM rosterusers
  	636	WITH (ROWLOCK)
  	637	WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
  	638
  	639	DELETE FROM rostergroups
  	640	WITH (ROWLOCK)
  	641	WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
  	642	COMMIT
  	643	END
  	644	GO
  	645
  	646
  	647	/***************************************************************/
  	648	/****** Object: StoredProcedure [dbo].[del_spool_msg] ******/
  	649	/** Delete an entry from the spool table **/
  	650	/***************************************************************/
  	651	CREATE PROCEDURE [dbo].[del_spool_msg]
  	652	@Username varchar(250)
  	653	AS
  	654	BEGIN
  	655	DELETE FROM spool
  	656	WITH (ROWLOCK)
  	657	WHERE spool.username=@Username;
  	658	END
  	659	GO
  	660
  	661	/***************************************************************/
  	662	/****** Object: StoredProcedure [dbo].[del_user] ******/
  	663	/** Delete an entry from the user table **/
  	664	/***************************************************************/
  	665	CREATE PROCEDURE [dbo].[del_user]
  	666	@Username varchar(200)
  	667	AS
  	668	BEGIN
  	669	DELETE FROM users
  	670	WITH (ROWLOCK)
  	671	WHERE username=@Username;
  	672	END
  	673	GO
  	674
  	675	/******************************************************************/
  	676	/****** Object: StoredProcedure [dbo].[del_user_return_password]**/
  	677	/** Delete an entry from the user table and return user password **/
  	678	/******************************************************************/
  	679	CREATE PROCEDURE [dbo].[del_user_return_password]
  	680	@Username varchar(250)
  	681	AS
  	682	DECLARE
  	683	@Pwd varchar(50)
  	684	BEGIN
  	685	EXECUTE @Pwd = dbo.get_password @Username
  	686	DELETE FROM users
  	687	WITH (ROWLOCK)
  	688	WHERE username=@Username
  	689
  	690	SELECT @Pwd;
  	691	END
  	692	GO
  	693
  	694
  	695	/******************************************************************/
  	696	/****** Object: StoredProcedure [dbo].[del_user_roster] **/
  	697	/** Delete the users roster **/
  	698	/******************************************************************/
  	699	CREATE PROCEDURE [dbo].[del_user_roster]
  	700	@Username varchar(250)
  	701	AS
  	702	BEGIN
  	703	BEGIN TRANSACTION
  	704	DELETE FROM rosterusers
  	705	WITH (ROWLOCK)
  	706	WHERE rosterusers.username = @Username;
  	707
  	708	DELETE FROM rostergroups
  	709	WITH (ROWLOCK)
  	710	WHERE rostergroups.username = @Username;
  	711	COMMIT
  	712	END
  	713	GO
  	714
  	715
  	716	/******************************************************************/
  	717	/****** Object: StoredProcedure [dbo].[get_and_del_spool_msg] **/
  	718	/** Fetch and delete the users offline messages **/
  	719	/******************************************************************/
  	720	CREATE PROCEDURE [dbo].[get_and_del_spool_msg]
  	721	@Username varchar(250)
  	722	AS
  	723	DECLARE
  	724	@vSpool table( username varchar(1),
  	725	xml varchar(1))
  	726	BEGIN
  	727	IF EXISTS (SELECT username FROM spool with (nolock) WHERE spool.username=@Username)
  	728	BEGIN
  	729	SELECT spool.username AS username,
  	730	spool.xml AS xml
  	731	FROM spool WITH (NOLOCK)
  	732	WHERE spool.username=@Username;
  	733
  	734	DELETE spool
  	735	WITH (ROWLOCK)
  	736	WHERE spool.username=@Username
  	737	END
  	738	ELSE
  	739	BEGIN
  	740	SELECT * FROM @vSpool;
  	741	END
  	742	END
  	743	GO
  	744
  	745	/******************************************************************/
  	746	/****** Object: StoredProcedure [dbo].[get_last] **/
  	747	/** Retrive the last user login **/
  	748	/******************************************************************/
  	749	CREATE PROCEDURE [dbo].[get_last]
  	750	@Username varchar(250)
  	751	AS
  	752	BEGIN
  	753	SELECT last.seconds AS seconds,
  	754	last.state AS state
  	755	FROM last WITH (NOLOCK)
  	756	WHERE last.username=@Username;
  	757	END
  	758	GO
  	759
  	760	/******************************************************************/
  	761	/****** Object: StoredProcedure [dbo].[get_roster] **/
  	762	/** Retrive the user roster **/
  	763	/******************************************************************/
  	764	CREATE PROCEDURE [dbo].[get_roster]
  	765	@Username varchar(250)
  	766	AS
  	767	DECLARE
  	768	@vRosterusers table( username varchar(1),
  	769	jid varchar(1),
  	770	nick varchar(1),
  	771	subscription varchar(1),
  	772	ask varchar(1),
  	773	askmessage varchar(1),
  	774	server varchar(1),
  	775	subscribe varchar(1),
  	776	type varchar(1))
  	777	BEGIN
  	778	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username)
  	779	BEGIN
  	780	SELECT rosterusers.username AS username,
  	781	rosterusers.jid AS jid,
  	782	rosterusers.nick AS nick,
  	783	rosterusers.subscription AS subscription,
  	784	rosterusers.ask AS ask,
  	785	rosterusers.askmessage AS askmessage,
  	786	rosterusers.server AS server,
  	787	rosterusers.subscribe AS subscribe,
  	788	rosterusers.type AS type
  	789	FROM rosterusers WITH (NOLOCK)
  	790	WHERE rosterusers.username = @Username;
  	791	END
  	792	ELSE
  	793	BEGIN
  	794	SELECT * FROM @vRosterusers
  	795	END
  	796	END
  	797	GO
  	798
  	799	/******************************************************************/
  	800	/****** Object: StoredProcedure [dbo].[get_roster_by_jid] **/
  	801	/** Retrive the user roster via JID **/
  	802	/******************************************************************/
  	803	CREATE PROCEDURE [dbo].[get_roster_by_jid]
  	804	@Username varchar(200),
  	805	@JID varchar(250)
  	806	AS
  	807	DECLARE
  	808	@vRosterusers table( username varchar(1),
  	809	jid varchar(1),
  	810	nick varchar(1),
  	811	subscription varchar(1),
  	812	ask varchar(1),
  	813	askmessage varchar(1),
  	814	server varchar(1),
  	815	subscribe varchar(1),
  	816	type varchar(1))
  	817	BEGIN
  	818	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID))
  	819	BEGIN
  	820	SELECT rosterusers.username AS username,
  	821	rosterusers.jid AS jid,
  	822	rosterusers.nick AS nick,
  	823	rosterusers.subscription AS subscription,
  	824	rosterusers.ask AS ask,
  	825	rosterusers.askmessage AS askmessage,
  	826	rosterusers.server AS server,
  	827	rosterusers.subscribe AS subscribe,
  	828	rosterusers.type AS type
  	829	FROM rosterusers WITH (NOLOCK)
  	830	WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
  	831	END
  	832	ELSE
  	833	BEGIN
  	834	SELECT * FROM @vRosterusers
  	835	END
  	836	END
  	837	GO
  	838
  	839	/******************************************************************/
  	840	/****** Object: StoredProcedure [dbo].[get_roster_jid_groups] **/
  	841	/** Retrieve the user roster groups **/
  	842	/******************************************************************/
  	843	CREATE PROCEDURE [dbo].[get_roster_jid_groups]
  	844	@Username varchar(200)
  	845	AS
  	846	DECLARE
  	847	@vrostergroups table( jid varchar(1),
  	848	grp varchar(1))
  	849	BEGIN
  	850	IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
  	851	BEGIN
  	852	SELECT rostergroups.jid AS jid,
  	853	rostergroups.grp AS grp
  	854	FROM rostergroups WITH (NOLOCK)
  	855	WHERE rostergroups.username = @Username;
  	856	END
  	857	ELSE
  	858	BEGIN
  	859	SELECT * FROM @vrostergroups
  	860	END
  	861	END
  	862	GO
  	863
  	864	/******************************************************************/
  	865	/****** Object: StoredProcedure [dbo].[get_roster_groups] **/
  	866	/** Retrive the user roster groups **/
  	867	/******************************************************************/
  	868	CREATE PROCEDURE [dbo].[get_roster_groups]
  	869	@Username varchar(200),
  	870	@JID varchar(250)
  	871	AS
  	872	DECLARE
  	873	@vrostergroups table( grp varchar(1))
  	874	BEGIN
  	875	IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
  	876	BEGIN
  	877	SELECT rostergroups.grp AS grp
  	878	FROM rostergroups WITH (NOLOCK)
  	879	WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
  	880	END
  	881	ELSE
  	882	BEGIN
  	883	SELECT * FROM @vrostergroups
  	884	END
  	885	END
  	886	GO
  	887
  	888	/******************************************************************/
  	889	/****** Object: StoredProcedure [dbo].[get_rostergroup_by_jid] **/
  	890	/** Retrive the user roster groups via JID **/
  	891	/******************************************************************/
  	892	CREATE PROCEDURE [dbo].[get_rostergroup_by_jid]
  	893	@Username varchar(250),
  	894	@JID varchar(250)
  	895	AS
  	896	DECLARE
  	897	@vrostergroups table(grp varchar(1))
  	898	BEGIN
  	899	IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID)
  	900	BEGIN
  	901	SELECT rostergroups.grp AS grp
  	902	FROM rostergroups WITH (NOLOCK)
  	903	WHERE rostergroups.username=@Username AND rostergroups.jid=@JID;
  	904	END
  	905	ELSE
  	906	BEGIN