Project: ejabberd

Revision: 1595

Author: jsautret

Date: 03 Oct 2008 11:29:48

• /trunk/src/odbc/mssql2005.sql

  ......	@@ -10,7 +10,7 @@
  10	10	* but WITHOUT ANY WARRANTY; without even the implied warranty of
  11	11	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  12	12	* General Public License for more details.
  13		*
  	13	*
  14	14	* You should have received a copy of the GNU General Public License
  15	15	* along with this program; if not, write to the Free Software
  16	16	* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
  ......	@@ -104,6 +104,10 @@
  104	104	drop table [dbo].[vcard]
  105	105	GO
  106	106
  	107	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard_search]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  	108	drop table [dbo].[vcard_search]
  	109	GO
  	110
  107	111	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  108	112	drop table [dbo].[private_storage]
  109	113	GO
  ......	@@ -119,7 +123,7 @@
  119	123	CREATE TABLE [dbo].[rostergroups] (
  120	124	[username] [varchar] (250) NOT NULL ,
  121	125	[jid] [varchar] (250) NOT NULL ,
  122		[grp] [varchar] (100) NOT NULL
  	126	[grp] [varchar] (100) NOT NULL
  123	127	) ON [PRIMARY]
  124	128	GO
  125	129
  ......	@@ -133,7 +137,7 @@
  133	137	[server] [char] (1) NOT NULL ,
  134	138	[subscribe] [varchar] (200) NULL ,
  135	139	[type] [varchar] (50) NULL ,
  136		CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED
  	140	CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED
  137	141	(
  138	142	[username] ASC,
  139	143	[jid] ASC
  ......	@@ -154,31 +158,41 @@
  154	158	CREATE TABLE [dbo].[users] (
  155	159	[username] [varchar] (250) NOT NULL ,
  156	160	[password] [varchar] (50) NOT NULL ,
  157		[created] [datetime] NULL
  	161	[created] [datetime] NULL
  158	162	) ON [PRIMARY]
  159	163	GO
  160	164
  161	165	CREATE TABLE [dbo].[vcard] (
  162	166	[username] [varchar] (250) NOT NULL ,
  163		[full_name] [varchar] (250) NULL ,
  164		[first_name] [varchar] (50) NULL ,
  165		[last_name] [varchar] (50) NULL ,
  166		[nick_name] [varchar] (50) NULL ,
  167		[url] [varchar] (1024) NULL ,
  168		[address1] [varchar] (50) NULL ,
  169		[address2] [varchar] (50) NULL ,
  170		[locality] [varchar] (50) NULL ,
  171		[region] [varchar] (50) NULL ,
  172		[pcode] [varchar] (50) NULL ,
  173		[country] [varchar] (50) NULL ,
  174		[telephone] [varchar] (50) NULL ,
  175		[email] [varchar] (250) NULL ,
  176		[orgname] [varchar] (50) NULL ,
  177		[orgunit] [varchar] (50) NULL ,
  178		[title] [varchar] (50) NULL ,
  179		[role] [varchar] (50) NULL ,
  180		[b_day] [datetime] NULL ,
  181		[descr] [varchar] (500) NULL
  	167	[vcard] [text] NOT NULL
  	168	) ON [PRIMARY]
  	169	GO
  	170
  	171	CREATE TABLE [dbo].[vcard_search] (
  	172	[username] [varchar] (250) NOT NULL ,
  	173	[lusername] [varchar] (250) NOT NULL ,
  	174	[fn] [text] NOT NULL ,
  	175	[lfn] [varchar] (250) NOT NULL ,
  	176	[family] [text] NOT NULL ,
  	177	[lfamily] [varchar] (250) NOT NULL ,
  	178	[given] [text] NOT NULL ,
  	179	[lgiven] [varchar] (250) NOT NULL ,
  	180	[middle] [text] NOT NULL ,
  	181	[lmiddle] [varchar] (250) NOT NULL ,
  	182	[nickname] [text] NOT NULL ,
  	183	[lnickname] [varchar] (250) NOT NULL ,
  	184	[bday] [text] NOT NULL ,
  	185	[lbday] [varchar] (250) NOT NULL ,
  	186	[ctry] [text] NOT NULL ,
  	187	[lctry] [varchar] (250) NOT NULL ,
  	188	[locality] [text] NOT NULL ,
  	189	[llocality] [varchar] (250) NOT NULL ,
  	190	[email] [text] NOT NULL ,
  	191	[lemail] [varchar] (250) NOT NULL ,
  	192	[orgname] [text] NOT NULL ,
  	193	[lorgname] [varchar] (250) NOT NULL ,
  	194	[orgunit] [text] NOT NULL ,
  	195	[lorgunit] [varchar] (250) NOT NULL
  182	196	) ON [PRIMARY]
  183	197	GO
  184	198
  ......	@@ -199,7 +213,7 @@
  199	213	[username] [varchar](250) NOT NULL,
  200	214	[name] [varchar](250) NOT NULL,
  201	215	[id] [bigint] IDENTITY(1,1) NOT NULL,
  202		CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED
  	216	CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED
  203	217	(
  204	218	[id] ASC
  205	219	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ......	@@ -224,73 +238,98 @@
  224	238	- id in privacy_list is a SERIAL autogenerated number
  225	239	- id in privacy_list_data must exist in the table privacy_list */
  226	240
  227		ALTER TABLE [dbo].[last] WITH NOCHECK ADD
  228		CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED
  	241	ALTER TABLE [dbo].[last] WITH NOCHECK ADD
  	242	CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED
  229	243	(
  230	244	[username]
  231		) WITH FILLFACTOR = 90 ON [PRIMARY]
  	245	) WITH FILLFACTOR = 90 ON [PRIMARY]
  232	246	GO
  233	247
  234		ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD
  235		CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED
  	248	ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD
  	249	CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED
  236	250	(
  237	251	[username],
  238	252	[jid],
  239	253	[grp]
  240		) WITH FILLFACTOR = 90 ON [PRIMARY]
  	254	) WITH FILLFACTOR = 90 ON [PRIMARY]
  241	255	GO
  242	256
  243		ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
  244		CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED
  	257	ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
  	258	CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED
  245	259	(
  246	260	[username],
  247	261	[id]
  248		) WITH FILLFACTOR = 90 ON [PRIMARY]
  	262	) WITH FILLFACTOR = 90 ON [PRIMARY]
  249	263	GO
  250	264
  251		ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  252		CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
  	265	ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  	266	CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
  253	267	(
  254	268	[username]
  255		) WITH FILLFACTOR = 90 ON [PRIMARY]
  	269	) WITH FILLFACTOR = 90 ON [PRIMARY]
  256	270	GO
  257	271
  258		ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD
  259		CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED
  	272	ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD
  	273	CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED
  260	274	(
  261	275	[username]
  262		) WITH FILLFACTOR = 90 ON [PRIMARY]
  	276	) WITH FILLFACTOR = 90 ON [PRIMARY]
  263	277	GO
  264	278
  	279
  	280	CREATE INDEX [IX_vcard_search_lfn] ON [dbo].[vcard_search]([lfn]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	281	GO
  	282	CREATE INDEX [IX_vcard_search_lfamily] ON [dbo].[vcard_search]([lfamily]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	283	GO
  	284	CREATE INDEX [IX_vcard_search_lgiven] ON [dbo].[vcard_search]([lgiven]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	285	GO
  	286	CREATE INDEX [IX_vcard_search_lmiddle] ON [dbo].[vcard_search]([lmiddle]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	287	GO
  	288	CREATE INDEX [IX_vcard_search_lnickname] ON [dbo].[vcard_search]([lnickname]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	289	GO
  	290	CREATE INDEX [IX_vcard_search_lbday] ON [dbo].[vcard_search]([lbday]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	291	GO
  	292	CREATE INDEX [IX_vcard_search_lctry] ON [dbo].[vcard_search]([lctry]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	293	GO
  	294	CREATE INDEX [IX_vcard_search_llocality] ON [dbo].[vcard_search]([llocality]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	295	GO
  	296	CREATE INDEX [IX_vcard_search_lemail] ON [dbo].[vcard_search]([lemail]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	297	GO
  	298	CREATE INDEX [IX_vcard_search_lorgname] ON [dbo].[vcard_search]([lorgname]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	299	GO
  	300	CREATE INDEX [IX_vcard_search_lorgunit] ON [dbo].[vcard_search]([lorgunit]) WITH FILLFACTOR = 90 ON [PRIMARY]
  	301	GO
  	302
  	303
  265	304	CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  266	305	GO
  267	306
  268		ALTER TABLE [dbo].[last] WITH NOCHECK ADD
  	307	ALTER TABLE [dbo].[last] WITH NOCHECK ADD
  269	308	CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date]
  270	309	GO
  271	310
  272		ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
  	311	ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
  273	312	CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed],
  274	313	CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created],
  275	314	CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete]
  276	315	GO
  277	316
  278		ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  	317	ALTER TABLE [dbo].[users] WITH NOCHECK ADD
  279	318	CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
  280	319	GO
  281	320
  282		ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD
  283		CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED
  	321	ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD
  	322	CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED
  284	323	(
  285	324	[username]
  286		) WITH FILLFACTOR = 90 ON [PRIMARY]
  	325	) WITH FILLFACTOR = 90 ON [PRIMARY]
  287	326	GO
  288	327
  289	328	CREATE INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
  290	329	GO
  291	330
  292	331	CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  293		GO
  	332	GO
  294	333
  295	334	CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
  296	335	GO
  ......	@@ -385,7 +424,7 @@
  385	424	@Type
  386	425	);
  387	426	END
  388
  	427
  389	428	--- Update Roster Groups if exist else add group entry
  390	429	IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
  391	430	BEGIN
  ......	@@ -543,12 +582,12 @@
  543	582	@Password varchar(50)
  544	583	AS
  545	584	BEGIN
  546		INSERT INTO users
  547		( [username],
  	585	INSERT INTO users
  	586	( [username],
  548	587	[password]
  549		)
  550		VALUES
  551		( @Username,
  	588	)
  	589	VALUES
  	590	( @Username,
  552	591	@Password
  553	592	);
  554	593	END
  ......	@@ -559,7 +598,7 @@
  559	598	/** Update users password **/
  560	599	/******************************************************************/
  561	600	CREATE PROCEDURE [dbo].[set_password]
  562		@Username varchar(200),
  	601	@Username varchar(200),
  563	602	@Password varchar(50)
  564	603	AS
  565	604	BEGIN
  ......	@@ -582,7 +621,7 @@
  582	621	@Username varchar(200)
  583	622	AS
  584	623	BEGIN
  585		SELECT users.password as password
  	624	SELECT users.password as password
  586	625	FROM users WITH (NOLOCK)
  587	626	WHERE username=@Username;
  588	627	END
  ......	@@ -594,7 +633,7 @@
  594	633	/***************************************************************/
  595	634	CREATE PROCEDURE [dbo].[clean_spool_msg]
  596	635	AS
  597		DECLARE
  	636	DECLARE
  598	637	@dt datetime,
  599	638	@myRowCount int
  600	639	BEGIN
  ......	@@ -606,7 +645,7 @@
  606	645	BEGIN
  607	646	BEGIN TRANSACTION
  608	647	SELECT @dt = DATEADD(d, -3, GETDATE())
  609		DELETE FROM spool
  	648	DELETE FROM spool
  610	649	WITH (ROWLOCK)
  611	650	WHERE (MustDelete=1) OR (Created < @dt);
  612	651
  ......	@@ -640,11 +679,11 @@
  640	679	AS
  641	680	BEGIN
  642	681	BEGIN TRANSACTION
  643		DELETE FROM rosterusers
  	682	DELETE FROM rosterusers
  644	683	WITH (ROWLOCK)
  645	684	WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
  646
  647		DELETE FROM rostergroups
  	685
  	686	DELETE FROM rostergroups
  648	687	WITH (ROWLOCK)
  649	688	WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
  650	689	COMMIT
  ......	@@ -660,7 +699,7 @@
  660	699	@Username varchar(250)
  661	700	AS
  662	701	BEGIN
  663		DELETE FROM spool
  	702	DELETE FROM spool
  664	703	WITH (ROWLOCK)
  665	704	WHERE spool.username=@Username;
  666	705	END
  ......	@@ -674,7 +713,7 @@
  674	713	@Username varchar(200)
  675	714	AS
  676	715	BEGIN
  677		DELETE FROM users
  	716	DELETE FROM users
  678	717	WITH (ROWLOCK)
  679	718	WHERE username=@Username;
  680	719	END
  ......	@@ -691,7 +730,7 @@
  691	730	@Pwd varchar(50)
  692	731	BEGIN
  693	732	EXECUTE @Pwd = dbo.get_password @Username
  694		DELETE FROM users
  	733	DELETE FROM users
  695	734	WITH (ROWLOCK)
  696	735	WHERE username=@Username
  697	736
  ......	@@ -709,11 +748,11 @@
  709	748	AS
  710	749	BEGIN
  711	750	BEGIN TRANSACTION
  712		DELETE FROM rosterusers
  	751	DELETE FROM rosterusers
  713	752	WITH (ROWLOCK)
  714	753	WHERE rosterusers.username = @Username;
  715
  716		DELETE FROM rostergroups
  	754
  	755	DELETE FROM rostergroups
  717	756	WITH (ROWLOCK)
  718	757	WHERE rostergroups.username = @Username;
  719	758	COMMIT
  ......	@@ -739,9 +778,9 @@
  739	778	FROM spool WITH (NOLOCK)
  740	779	WHERE spool.username=@Username;
  741	780
  742		DELETE spool
  	781	DELETE spool
  743	782	WITH (ROWLOCK)
  744		WHERE spool.username=@Username
  	783	WHERE spool.username=@Username
  745	784	END
  746	785	ELSE
  747	786	BEGIN
  ......	@@ -758,7 +797,7 @@
  758	797	@Username varchar(250)
  759	798	AS
  760	799	BEGIN
  761		SELECT last.seconds AS seconds,
  	800	SELECT last.seconds AS seconds,
  762	801	last.state AS state
  763	802	FROM last WITH (NOLOCK)
  764	803	WHERE last.username=@Username;
  ......	@@ -773,26 +812,26 @@
  773	812	@Username varchar(250)
  774	813	AS
  775	814	DECLARE
  776		@vRosterusers table( username varchar(1),
  777		jid varchar(1),
  778		nick varchar(1),
  779		subscription varchar(1),
  780		ask varchar(1),
  781		askmessage varchar(1),
  782		server varchar(1),
  783		subscribe varchar(1),
  	815	@vRosterusers table( username varchar(1),
  	816	jid varchar(1),
  	817	nick varchar(1),
  	818	subscription varchar(1),
  	819	ask varchar(1),
  	820	askmessage varchar(1),
  	821	server varchar(1),
  	822	subscribe varchar(1),
  784	823	type varchar(1))
  785	824	BEGIN
  786	825	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username)
  787	826	BEGIN
  788		SELECT rosterusers.username AS username,
  789		rosterusers.jid AS jid,
  790		rosterusers.nick AS nick,
  791		rosterusers.subscription AS subscription,
  	827	SELECT rosterusers.username AS username,
  	828	rosterusers.jid AS jid,
  	829	rosterusers.nick AS nick,
  	830	rosterusers.subscription AS subscription,
  792	831	rosterusers.ask AS ask,
  793	832	rosterusers.askmessage AS askmessage,
  794		rosterusers.server AS server,
  795		rosterusers.subscribe AS subscribe,
  	833	rosterusers.server AS server,
  	834	rosterusers.subscribe AS subscribe,
  796	835	rosterusers.type AS type
  797	836	FROM rosterusers WITH (NOLOCK)
  798	837	WHERE rosterusers.username = @Username;
  ......	@@ -813,26 +852,26 @@
  813	852	@JID varchar(250)
  814	853	AS
  815	854	DECLARE
  816		@vRosterusers table( username varchar(1),
  817		jid varchar(1),
  818		nick varchar(1),
  819		subscription varchar(1),
  	855	@vRosterusers table( username varchar(1),
  	856	jid varchar(1),
  	857	nick varchar(1),
  	858	subscription varchar(1),
  820	859	ask varchar(1),
  821	860	askmessage varchar(1),
  822		server varchar(1),
  823		subscribe varchar(1),
  	861	server varchar(1),
  	862	subscribe varchar(1),
  824	863	type varchar(1))
  825	864	BEGIN
  826	865	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID))
  827	866	BEGIN
  828		SELECT rosterusers.username AS username,
  829		rosterusers.jid AS jid,
  830		rosterusers.nick AS nick,
  831		rosterusers.subscription AS subscription,
  	867	SELECT rosterusers.username AS username,
  	868	rosterusers.jid AS jid,
  	869	rosterusers.nick AS nick,
  	870	rosterusers.subscription AS subscription,
  832	871	rosterusers.ask AS ask,
  833	872	rosterusers.askmessage AS askmessage,
  834		rosterusers.server AS server,
  835		rosterusers.subscribe AS subscribe,
  	873	rosterusers.server AS server,
  	874	rosterusers.subscribe AS subscribe,
  836	875	rosterusers.type AS type
  837	876	FROM rosterusers WITH (NOLOCK)
  838	877	WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
  ......	@@ -852,12 +891,12 @@
  852	891	@Username varchar(200)
  853	892	AS
  854	893	DECLARE
  855		@vrostergroups table( jid varchar(1),
  	894	@vrostergroups table( jid varchar(1),
  856	895	grp varchar(1))
  857	896	BEGIN
  858	897	IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
  859	898	BEGIN
  860		SELECT rostergroups.jid AS jid,
  	899	SELECT rostergroups.jid AS jid,
  861	900	rostergroups.grp AS grp
  862	901	FROM rostergroups WITH (NOLOCK)
  863	902	WHERE rostergroups.username = @Username;
  ......	@@ -930,7 +969,7 @@
  930	969	BEGIN
  931	970	IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
  932	971	BEGIN
  933		SELECT rosterusers.subscription AS subscription
  	972	SELECT rosterusers.subscription AS subscription
  934	973	FROM rosterusers WITH (NOLOCK)
  935	974	WHERE rosterusers.username=@Username AND rosterusers.jid=@JID;
  936	975	END
  ......	@@ -958,7 +997,7 @@
  958	997	/******************************************************************/
  959	998	CREATE PROCEDURE [dbo].[set_last]
  960	999	@Username varchar(250),
  961		@Seconds varchar(50),
  	1000	@Seconds varchar(50),
  962	1001	@State varchar(100)
  963	1002	AS
  964	1003	BEGIN
  ......	@@ -1050,4 +1089,151 @@
  1050	1089	GO
  1051	1090
  1052	1091
  	1092	/******************************************************************/
  	1093	/****** Object: StoredProcedure [dbo].[set_vcard] **/
  	1094	/** Set the user's vCard **/
  	1095	/******************************************************************/
  	1096	CREATE PROCEDURE [dbo].[set_vcard]
  	1097	@VCard varchar(8000),
  	1098	@Username varchar(250),
  	1099	@Lusername varchar(250),
  	1100	@Fn varchar(8000),
  	1101	@Lfn varchar(250),
  	1102	@Family varchar(8000),
  	1103	@Lfamily varchar(250),
  	1104	@Given varchar(8000),
  	1105	@Lgiven varchar(250),
  	1106	@Middle varchar(8000),
  	1107	@Lmiddle varchar(250),
  	1108	@Nickname varchar(8000),
  	1109	@Lnickname varchar(250),
  	1110	@Bday varchar(8000),
  	1111	@Lbday varchar(250),
  	1112	@Ctry varchar(8000),
  	1113	@Lctry varchar(250),
  	1114	@Locality varchar(8000),
  	1115	@Llocality varchar(250),
  	1116	@Email varchar(8000),
  	1117	@Lemail varchar(250),
  	1118	@Orgname varchar(8000),
  	1119	@Lorgname varchar(250),
  	1120	@Orgunit varchar(8000),
  	1121	@Lorgunit varchar(250)
  	1122	AS
  	1123	BEGIN
  	1124	IF EXISTS (SELECT username FROM vcard with (nolock) WHERE vcard.username = @Username)
  	1125	BEGIN
  	1126	UPDATE [vcard]
  	1127	SET [vcard].username = @LUsername,
  	1128	[vcard].vcard = @Vcard
  	1129	WHERE vcard.username = @LUsername;
  	1130
  	1131	UPDATE [vcard_search]
  	1132	SET [vcard_search].username = @Username,
  	1133	[vcard_search].lusername = @Lusername,
  	1134	[vcard_search].fn = @Fn,
  	1135	[vcard_search].lfn = @Lfn,
  	1136	[vcard_search].family = @Family,
  	1137	[vcard_search].lfamily = @Lfamily,
  	1138	[vcard_search].given = @Given,
  	1139	[vcard_search].lgiven = @Lgiven,
  	1140	[vcard_search].middle = @Middle,
  	1141	[vcard_search].lmiddle = @Lmiddle,
  	1142	[vcard_search].nickname = @Nickname,
  	1143	[vcard_search].lnickname = @Lnickname,
  	1144	[vcard_search].bday = @Bday,
  	1145	[vcard_search].lbday = @Lbday,
  	1146	[vcard_search].ctry = @Ctry,
  	1147	[vcard_search].lctry = @Lctry,
  	1148	[vcard_search].locality = @Locality,
  	1149	[vcard_search].llocality = @Llocality,
  	1150	[vcard_search].email = @Email,
  	1151	[vcard_search].lemail = @Lemail,
  	1152	[vcard_search].orgname = @Orgname,
  	1153	[vcard_search].lorgname = @Lorgname,
  	1154	[vcard_search].orgunit = @Orgunit,
  	1155	[vcard_search].lorgunit = @Lorgunit
  	1156	WHERE vcard_search.lusername = @LUsername;
  	1157	END
  	1158	ELSE
  	1159	BEGIN
  	1160	INSERT INTO [vcard]
  	1161	( [vcard].username,
  	1162	[vcard].vcard
  	1163	)
  	1164	VALUES
  	1165	( @lUsername,
  	1166	@Vcard
  	1167	);
  1053	1168
  	1169	INSERT INTO [vcard_search]
  	1170	(
  	1171	[vcard_search].username ,
  	1172	[vcard_search].lusername ,
  	1173	[vcard_search].fn ,
  	1174	[vcard_search].lfn ,
  	1175	[vcard_search].family ,
  	1176	[vcard_search].lfamily ,
  	1177	[vcard_search].given ,
  	1178	[vcard_search].lgiven ,
  	1179	[vcard_search].middle ,
  	1180	[vcard_search].lmiddle ,
  	1181	[vcard_search].nickname,
  	1182	[vcard_search].lnickname,
  	1183	[vcard_search].bday,
  	1184	[vcard_search].lbday,
  	1185	[vcard_search].ctry,
  	1186	[vcard_search].lctry,
  	1187	[vcard_search].locality,
  	1188	[vcard_search].llocality,
  	1189	[vcard_search].email,
  	1190	[vcard_search].lemail,
  	1191	[vcard_search].orgname,
  	1192	[vcard_search].lorgname,
  	1193	[vcard_search].orgunit,
  	1194	[vcard_search].lorgunit
  	1195	)
  	1196	VALUES
  	1197	(
  	1198	@Username,
  	1199	@Lusername,
  	1200	@Fn,
  	1201	@Lfn,
  	1202	@Family,
  	1203	@Lfamily,
  	1204	@Given,
  	1205	@Lgiven,
  	1206	@Middle,
  	1207	@Lmiddle,
  	1208	@Nickname,
  	1209	@Lnickname,
  	1210	@Bday,
  	1211	@Lbday,
  	1212	@Ctry,
  	1213	@Lctry,
  	1214	@Locality,
  	1215	@Llocality,
  	1216	@Email,
  	1217	@Lemail,
  	1218	@Orgname,
  	1219	@Lorgname,
  	1220	@Orgunit,
  	1221	@Lorgunit
  	1222	)
  	1223	END
  	1224	END
  	1225	GO
  	1226
  	1227	/******************************************************************/
  	1228	/****** Object: StoredProcedure [dbo].[get_vcard] **/
  	1229	/** Retrive the user's vCard **/
  	1230	/******************************************************************/
  	1231	CREATE PROCEDURE [dbo].[get_vcard]
  	1232	@Username varchar(250)
  	1233	AS
  	1234	BEGIN
  	1235	SELECT vcard.vcard as vcard
  	1236	FROM vcard WITH (NOLOCK)
  	1237	WHERE username=@Username;
  	1238	END
  	1239	GO

• /trunk/src/odbc/odbc_queries.erl

  ......	@@ -61,6 +61,8 @@
  61	61	set_private_data_sql/3,
  62	62	get_private_data/3,
  63	63	del_user_private_storage/2,
  	64	set_vcard/26,
  	65	get_vcard/2,
  64	66	escape/1,
  65	67	count_records_where/3]).
  66	68
  ......	@@ -378,6 +380,42 @@
  378	380	LServer,
  379	381	["delete from private_storage where username='", Username, "';"]).
  380	382
  	383
  	384	set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, SFamily, SGiven,
  	385	SLBDay, SLCTRY, SLEMail, SLFN, SLFamily, SLGiven, SLLocality,
  	386	SLMiddle, SLNickname, SLOrgName, SLOrgUnit, SLocality, SMiddle,
  	387	SNickname, SOrgName, SOrgUnit, SVCARD, Username) ->
  	388	ejabberd_odbc:sql_transaction(
  	389	LServer,
  	390	[["delete from vcard where username='", LUsername, "';"],
  	391	["insert into vcard(username, vcard) "
  	392	"values ('", LUsername, "', '", SVCARD, "');"],
  	393	["delete from vcard_search where lusername='", LUsername, "';"],
  	394	["insert into vcard_search("
  	395	" username, lusername, fn, lfn, family, lfamily,"
  	396	" given, lgiven, middle, lmiddle, nickname, lnickname,"
  	397	" bday, lbday, ctry, lctry, locality, llocality,"
  	398	" email, lemail, orgname, lorgname, orgunit, lorgunit)"
  	399	"values (",
  	400	" '", Username, "', '", LUsername, "',"
  	401	" '", SFN, "', '", SLFN, "',"
  	402	" '", SFamily, "', '", SLFamily, "',"
  	403	" '", SGiven, "', '", SLGiven, "',"
  	404	" '", SMiddle, "', '", SLMiddle, "',"
  	405	" '", SNickname, "', '", SLNickname, "',"
  	406	" '", SBDay, "', '", SLBDay, "',"
  	407	" '", SCTRY, "', '", SLCTRY, "',"
  	408	" '", SLocality, "', '", SLLocality, "',"
  	409	" '", SEMail, "', '", SLEMail, "',"
  	410	" '", SOrgName, "', '", SLOrgName, "',"
  	411	" '", SOrgUnit, "', '", SLOrgUnit, "');"]]).
  	412
  	413	get_vcard(LServer, Username) ->
  	414	ejabberd_odbc:sql_query(
  	415	LServer,
  	416	["select vcard from vcard "
  	417	"where username='", Username, "';"]).
  	418
  381	419	%% Characters to escape
  382	420	escape($\0) -> "\\0";
  383	421	escape($\n) -> "\\n";
  ......	@@ -597,6 +635,25 @@
  597	635	LServer,
  598	636	["EXECUTE dbo.del_user_storage '", Username, "'"]).
  599	637
  	638	set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, SFamily, SGiven,
  	639	SLBDay, SLCTRY, SLEMail, SLFN, SLFamily, SLGiven, SLLocality,
  	640	SLMiddle, SLNickname, SLOrgName, SLOrgUnit, SLocality, SMiddle,
  	641	SNickname, SOrgName, SOrgUnit, SVCARD, Username) ->
  	642	ejabberd_odbc:sql_query(
  	643	LServer,
  	644	["EXECUTE dbo.set_vcard '", SVCARD, "' , '", Username, "' , '", LUsername, "' , '",
  	645	SFN, "' , '", SLFN, "' , '", SFamily, "' , '", SLFamily, "' , '",
  	646	SGiven, "' , '", SLGiven, "' , '", SMiddle, "' , '", SLMiddle, "' , '",
  	647	SNickname, "' , '", SLNickname, "' , '", SBDay, "' , '", SLBDay, "' , '",
  	648	SCTRY, "' , '", SLCTRY, "' , '", SLocality, "' , '", SLLocality, "' , '",
  	649	SEMail, "' , '", SLEMail, "' , '", SOrgName, "' , '", SLOrgName, "' , '",
  	650	SOrgUnit, "' , '", SLOrgUnit, "'"]).
  	651
  	652	get_vcard(LServer, Username) ->
  	653	ejabberd_odbc:sql_query(
  	654	LServer,
  	655	["EXECUTE dbo.get_vcard '", Username, "'"]).
  	656
  600	657	%% Characters to escape
  601	658	escape($\0) -> "\\0";
  602	659	escape($\t) -> "\\t";

• /trunk/ChangeLog

  ......	@@ -1,3 +1,9 @@
  	1	2008-10-03 Jerome Sautret <jerome.sautret@process-one.net>
  	2
  	3	* src/mod_vcard_odbc: added vCard support for MS SQL Server 2005.
  	4	* src/odbc/odbc_queries.erl: likewise.
  	5	* src/odbc/mssql2005.sql: likewise.
  	6
  1	7	2008-10-01 Mickael Remond <mremond@process-one.net>
  2	8
  3	9	* src/mod_shared_roster.erl: Correct roster push when changing
  ......	@@ -8,7 +14,7 @@
  8	14	* src/*/Makefile.win32: Provide explicit beam filenames because
  9	15	nmake does not accept wildcards (thanks to Attila
  10	16	Vangel)(EJAB-543)
  11
  	17
  12	18	2008-09-24 Christophe Romain <christophe.romain@process-one.net>
  13	19
  14	20	* src/mod_pubsub/mod_pubsub.erl: Allow PEP node type to be mapped with