« back to all changes in this revision
Viewing changes to priv/odbc/mssql2005.sql
- browse files at revision 24
- compare with another revision
- download diff
- download tarball
- view history from revision 24
- Committer: Bazaar Package Importer
- Author(s): Konstantin Khomoutov, Konstantin Khomoutov
- Date: 2011-10-03 20:27:12 UTC
- mfrom: (1.1.19 upstream)
- Revision ID: james.westby@ubuntu.com-20111003202712-w9071rc7vleldxwx
Tags: 2.1.9-1
[ Konstantin Khomoutov ]
* New upstream release.
* Remove obsoleted version.patch.
* New upstream release.
* Remove obsoleted version.patch.
- files added:
- .gitignore
- files removed:
- debian/patches/version.patch
- ebin
- priv
- priv/bin
- priv/lib
- priv/msgs
- priv/odbc
- files modified:
- debian/changelog
added
removed
priv/odbc/mssql2005.sql
1
/*
2
* ejabberd, Copyright (C) 2002-2011 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].[vcard_search]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
108
drop table [dbo].[vcard_search]
109
GO
110
111
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
112
drop table [dbo].[private_storage]
113
GO
114
115
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[privacy_default_list]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
116
drop table [dbo].[privacy_default_list]
117
GO
118
119
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[privacy_list]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
120
drop table [dbo].[privacy_list]
121
GO
122
123
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[privacy_list_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
124
drop table [dbo].[privacy_list_data]
125
GO
126
127
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[roster_version]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
128
drop table [dbo].[roster_version]
129
GO
130
131
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_node_option]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
132
drop table [dbo].[pubsub_node_option]
133
GO
134
135
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_node_owner]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
136
drop table [dbo].[pubsub_node_owner]
137
GO
138
139
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_state]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
140
drop table [dbo].[pubsub_state]
141
GO
142
143
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
144
drop table [dbo].[pubsub_item]
145
GO
146
147
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_subscription_opt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
148
drop table [dbo].[pubsub_subscription_opt]
149
GO
150
151
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubsub_node]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
152
drop table [dbo].[pubsub_node]
153
GO
154
155
CREATE TABLE [dbo].[last] (
156
[username] [varchar] (250) NOT NULL ,
157
[seconds] [varchar] (50) NOT NULL ,
158
[state] [varchar] (100) NOT NULL ,
159
[Modify_Date] [datetime] NOT NULL
160
) ON [PRIMARY]
161
GO
162
163
CREATE TABLE [dbo].[rostergroups] (
164
[username] [varchar] (250) NOT NULL ,
165
[jid] [varchar] (250) NOT NULL ,
166
[grp] [varchar] (100) NOT NULL
167
) ON [PRIMARY]
168
GO
169
170
CREATE TABLE [dbo].[rosterusers] (
171
[username] [varchar] (250) NOT NULL ,
172
[jid] [varchar] (250) NOT NULL ,
173
[nick] [varchar] (50) NOT NULL ,
174
[subscription] [char] (1) NOT NULL ,
175
[ask] [char] (1) NOT NULL ,
176
[askmessage] [varchar] (250) NOT NULL ,
177
[server] [char] (1) NOT NULL ,
178
[subscribe] [varchar] (200) NULL ,
179
[type] [varchar] (50) NULL ,
180
CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED
181
(
182
[username] ASC,
183
[jid] ASC
184
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
185
) ON [PRIMARY]
186
GO
187
188
CREATE TABLE [dbo].[spool] (
189
[id] [numeric](19, 0) IDENTITY (1, 1) NOT NULL ,
190
[username] [varchar] (250) NOT NULL ,
191
[xml] [text] NOT NULL ,
192
[notifyprocessed] [bit] NULL ,
193
[created] [datetime] NULL ,
194
[MustDelete] [bit] NOT NULL
195
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
196
GO
197
198
CREATE TABLE [dbo].[users] (
199
[username] [varchar] (250) NOT NULL ,
200
[password] [varchar] (50) NOT NULL ,
201
[created] [datetime] NULL
202
) ON [PRIMARY]
203
GO
204
205
CREATE TABLE [dbo].[vcard] (
206
[username] [varchar] (250) NOT NULL ,
207
[vcard] [text] NOT NULL
208
) ON [PRIMARY]
209
GO
210
211
CREATE TABLE [dbo].[vcard_search] (
212
[username] [varchar] (250) NOT NULL ,
213
[lusername] [varchar] (250) NOT NULL ,
214
[fn] [text] NOT NULL ,
215
[lfn] [varchar] (250) NOT NULL ,
216
[family] [text] NOT NULL ,
217
[lfamily] [varchar] (250) NOT NULL ,
218
[given] [text] NOT NULL ,
219
[lgiven] [varchar] (250) NOT NULL ,
220
[middle] [text] NOT NULL ,
221
[lmiddle] [varchar] (250) NOT NULL ,
222
[nickname] [text] NOT NULL ,
223
[lnickname] [varchar] (250) NOT NULL ,
224
[bday] [text] NOT NULL ,
225
[lbday] [varchar] (250) NOT NULL ,
226
[ctry] [text] NOT NULL ,
227
[lctry] [varchar] (250) NOT NULL ,
228
[locality] [text] NOT NULL ,
229
[llocality] [varchar] (250) NOT NULL ,
230
[email] [text] NOT NULL ,
231
[lemail] [varchar] (250) NOT NULL ,
232
[orgname] [text] NOT NULL ,
233
[lorgname] [varchar] (250) NOT NULL ,
234
[orgunit] [text] NOT NULL ,
235
[lorgunit] [varchar] (250) NOT NULL
236
) ON [PRIMARY]
237
GO
238
239
CREATE TABLE [dbo].[private_storage] (
240
[username] [varchar] (250) NOT NULL ,
241
[namespace] [varchar] (250) NOT NULL ,
242
[data] [text] NOT NULL
243
) ON [PRIMARY]
244
GO
245
246
CREATE TABLE [dbo].[privacy_default_list] (
247
[username] [varchar] (250) NOT NULL,
248
[name] [varchar] (250) NOT NULL
249
) ON [PRIMARY]
250
GO
251
252
CREATE TABLE [dbo].[privacy_list](
253
[username] [varchar](250) NOT NULL,
254
[name] [varchar](250) NOT NULL,
255
[id] [bigint] IDENTITY(1,1) NOT NULL,
256
CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED
257
(
258
[id] ASC
259
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
260
) ON [PRIMARY]
261
GO
262
263
CREATE TABLE [dbo].[privacy_list_data] (
264
[id] [bigint] NOT NULL,
265
[t] [character] (1) NOT NULL,
266
[value] [text] NOT NULL,
267
[action] [character] (1) NOT NULL,
268
[ord] [NUMERIC] NOT NULL,
269
[match_all] [bit] NOT NULL,
270
[match_iq] [bit] NOT NULL,
271
[match_message] [bit] NOT NULL,
272
[match_presence_in] [bit] NOT NULL,
273
[match_presence_out] [bit] NOT NULL
274
) ON [PRIMARY]
275
GO
276
277
CREATE TABLE [dbo].[roster_version] (
278
[username] [varchar](250) PRIMARY KEY,
279
[version] [text] NOT NULL
280
) ON [PRIMARY]
281
GO
282
283
CREATE TABLE [dbo].[pubsub_node] (
284
[host] [varchar](250),
285
[node] [varchar](250),
286
[parent] [varchar](250),
287
[type] [varchar](250),
288
[nodeid] [bigint] IDENTITY(1,1) PRIMARY KEY
289
) ON [PRIMARY]
290
GO
291
292
CREATE TABLE [dbo].[pubsub_node_option] (
293
[nodeid] [bigint],
294
[name] [varchar](250),
295
[val] [varchar](250)
296
) ON [PRIMARY]
297
GO
298
299
CREATE TABLE [dbo].[pubsub_node_owner] (
300
[nodeid] [bigint],
301
[owner] [varchar](250)
302
) ON [PRIMARY]
303
GO
304
305
CREATE TABLE [dbo].[pubsub_state] (
306
[nodeid] [bigint],
307
[jid] [varchar](250),
308
[affiliation] [CHAR](1),
309
[subscriptions] [text],
310
[stateid] [bigint] IDENTITY(1,1) PRIMARY KEY
311
) ON [PRIMARY]
312
GO
313
314
CREATE TABLE [dbo].[pubsub_item] (
315
[nodeid] [bigint],
316
[itemid] [varchar](250),
317
[publisher] [text],
318
[creation] [text],
319
[modification] [text],
320
[payload] [text]
321
) ON [PRIMARY]
322
GO
323
324
CREATE TABLE [dbo].[pubsub_subscription_opt] (
325
[subid] [varchar](250),
326
[opt_name] [varchar](32),
327
[opt_value] [text]
328
) ON [PRIMARY]
329
GO
330
331
/* Constraints to add:
332
- id in privacy_list is a SERIAL autogenerated number
333
- id in privacy_list_data must exist in the table privacy_list */
334
335
ALTER TABLE [dbo].[last] WITH NOCHECK ADD
336
CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED
337
(
338
[username]
339
) WITH FILLFACTOR = 90 ON [PRIMARY]
340
GO
341
342
ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD
343
CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED
344
(
345
[username],
346
[jid],
347
[grp]
348
) WITH FILLFACTOR = 90 ON [PRIMARY]
349
GO
350
351
ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
352
CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED
353
(
354
[username],
355
[id]
356
) WITH FILLFACTOR = 90 ON [PRIMARY]
357
GO
358
359
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
360
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
361
(
362
[username]
363
) WITH FILLFACTOR = 90 ON [PRIMARY]
364
GO
365
366
ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD
367
CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED
368
(
369
[username]
370
) WITH FILLFACTOR = 90 ON [PRIMARY]
371
GO
372
373
ALTER TABLE [dbo].[pubsub_node_option] WITH NOCHECK ADD
374
CONSTRAINT [FK_pubsub_node_option] FOREIGN KEY
375
(
376
[nodeid]
377
) REFERENCES [dbo].[pubsub_node]
378
(
379
[nodeid]
380
)
381
ON DELETE CASCADE
382
GO
383
384
ALTER TABLE [dbo].[pubsub_node_owner] WITH NOCHECK ADD
385
CONSTRAINT [FK_pubsub_node_owner] FOREIGN KEY
386
(
387
[nodeid]
388
) REFERENCES [pubsub_node]
389
(
390
[nodeid]
391
)
392
ON DELETE CASCADE
393
GO
394
395
ALTER TABLE [dbo].[pubsub_state] WITH NOCHECK ADD
396
CONSTRAINT [FK_pubsub_state] FOREIGN KEY
397
(
398
[nodeid]
399
) REFERENCES [pubsub_node]
400
(
401
[nodeid]
402
)
403
ON DELETE CASCADE
404
GO
405
406
ALTER TABLE [dbo].[pubsub_item] WITH NOCHECK ADD
407
CONSTRAINT [FK_pubsub_item] FOREIGN KEY
408
(
409
[nodeid]
410
) REFERENCES [pubsub_node]
411
(
412
[nodeid]
413
)
414
ON DELETE CASCADE
415
GO
416
417
CREATE INDEX [IX_vcard_search_lfn] ON [dbo].[vcard_search]([lfn]) WITH FILLFACTOR = 90 ON [PRIMARY]
418
GO
419
CREATE INDEX [IX_vcard_search_lfamily] ON [dbo].[vcard_search]([lfamily]) WITH FILLFACTOR = 90 ON [PRIMARY]
420
GO
421
CREATE INDEX [IX_vcard_search_lgiven] ON [dbo].[vcard_search]([lgiven]) WITH FILLFACTOR = 90 ON [PRIMARY]
422
GO
423
CREATE INDEX [IX_vcard_search_lmiddle] ON [dbo].[vcard_search]([lmiddle]) WITH FILLFACTOR = 90 ON [PRIMARY]
424
GO
425
CREATE INDEX [IX_vcard_search_lnickname] ON [dbo].[vcard_search]([lnickname]) WITH FILLFACTOR = 90 ON [PRIMARY]
426
GO
427
CREATE INDEX [IX_vcard_search_lbday] ON [dbo].[vcard_search]([lbday]) WITH FILLFACTOR = 90 ON [PRIMARY]
428
GO
429
CREATE INDEX [IX_vcard_search_lctry] ON [dbo].[vcard_search]([lctry]) WITH FILLFACTOR = 90 ON [PRIMARY]
430
GO
431
CREATE INDEX [IX_vcard_search_llocality] ON [dbo].[vcard_search]([llocality]) WITH FILLFACTOR = 90 ON [PRIMARY]
432
GO
433
CREATE INDEX [IX_vcard_search_lemail] ON [dbo].[vcard_search]([lemail]) WITH FILLFACTOR = 90 ON [PRIMARY]
434
GO
435
CREATE INDEX [IX_vcard_search_lorgname] ON [dbo].[vcard_search]([lorgname]) WITH FILLFACTOR = 90 ON [PRIMARY]
436
GO
437
CREATE INDEX [IX_vcard_search_lorgunit] ON [dbo].[vcard_search]([lorgunit]) WITH FILLFACTOR = 90 ON [PRIMARY]
438
GO
439
440
441
CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
442
GO
443
444
ALTER TABLE [dbo].[last] WITH NOCHECK ADD
445
CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date]
446
GO
447
448
ALTER TABLE [dbo].[spool] WITH NOCHECK ADD
449
CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed],
450
CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created],
451
CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete]
452
GO
453
454
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
455
CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
456
GO
457
458
ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD
459
CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED
460
(
461
[username]
462
) WITH FILLFACTOR = 90 ON [PRIMARY]
463
GO
464
465
CREATE INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
466
GO
467
468
CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
469
GO
470
471
CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
472
GO
473
474
CREATE INDEX [IX_spool_process] ON [dbo].[spool]([created], [notifyprocessed]) WITH FILLFACTOR = 90 ON [PRIMARY]
475
GO
476
477
CREATE INDEX [IK_Spool_Del] ON [dbo].[spool]([MustDelete]) WITH FILLFACTOR = 90 ON [PRIMARY]
478
GO
479
480
CREATE INDEX [IK_Spool_Created] ON [dbo].[spool]([created]) WITH FILLFACTOR = 90 ON [PRIMARY]
481
GO
482
483
CREATE INDEX [IX_private_user] ON [dbo].[private_storage]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
484
GO
485
486
CREATE INDEX [IX_private_user_ns] ON [dbo].[private_storage]([username], [namespace]) WITH FILLFACTOR = 90 ON [PRIMARY]
487
GO
488
489
CREATE INDEX [IX_privacy_list_username] ON [dbo].[privacy_list]([username]) WITH FILLFACTOR = 90 ON [PRIMARY]
490
GO
491
492
CREATE INDEX [IX_privacy_list_username_name] ON [dbo].[privacy_list]([username], [name]) WITH FILLFACTOR = 90 ON [PRIMARY]
493
GO
494
495
CREATE INDEX [IX_pubsub_node_parent] ON [dbo].[pubsub_node]([parent]) WITH FILLFACTOR = 90 ON [PRIMARY]
496
GO
497
498
CREATE INDEX [IX_pubsub_node_tuple] ON [dbo].[pubsub_node]([host], [node]) WITH FILLFACTOR = 90 ON [PRIMARY]
499
GO
500
501
CREATE INDEX [IX_pubsub_node_option_nodeid] ON [dbo].[pubsub_node_option]([nodeid]) WITH FILLFACTOR = 90 ON [PRIMARY]
502
GO
503
504
CREATE INDEX [IX_pubsub_node_owner_nodeid] ON [dbo].[pubsub_node_owner]([nodeid]) WITH FILLFACTOR = 90 ON [PRIMARY]
505
GO
506
507
CREATE INDEX [IX_pubsub_state_jid] ON [dbo].[pubsub_state]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
508
GO
509
510
CREATE INDEX [IX_pubsub_state_tuple] ON [dbo].[pubsub_state]([nodeid], [jid]) WITH FILLFACTOR = 90 ON [PRIMARY]
511
GO
512
513
CREATE INDEX [IX_pubsub_item_itemid] ON [dbo].[pubsub_item]([itemid]) WITH FILLFACTOR = 90 ON [PRIMARY]
514
GO
515
516
CREATE INDEX [IX_pubsub_item_tuple] ON [dbo].[pubsub_item]([nodeid], [itemid]) WITH FILLFACTOR = 90 ON [PRIMARY]
517
GO
518
519
CREATE INDEX [IX_pubsub_subscription_opt] ON [dbo].[pubsub_subscription_opt]([subid], [opt_name]) WITH FILLFACTOR = 90 ON [PRIMARY]
520
Go
521
522
/*********************************************************/
523
/** These store procedures are for use with ejabberd **/
524
/** 1.1 and Microsoft Sql Server 2000 **/
525
/** **/
526
/** The stored procedures reduce the need to sql **/
527
/** compilation of the database and also allow for also **/
528
/** provide each of database integration. The stored **/
529
/** procedure have been optimized to increase database **/
530
/** performance and a reduction of 80% in CPU was **/
531
/** achieved over the use of standard sql. **/
532
/*********************************************************/
533
534
/****** Object: StoredProcedure [dbo].[add_roster] ******/
535
/** Add or update user entries in the roster **/
536
/*********************************************************/
537
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_roster]') AND type in (N'P', N'PC'))
538
DROP PROCEDURE [dbo].[add_roster]
539
GO
540
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_roster_group]') AND type in (N'P', N'PC'))
541
DROP PROCEDURE [dbo].[add_roster_group]
542
GO
543
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_roster_user]') AND type in (N'P', N'PC'))
544
DROP PROCEDURE [dbo].[add_roster_user]
545
GO
546
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_roster_groups]') AND type in (N'P', N'PC'))
547
DROP PROCEDURE [dbo].[del_roster_groups]
548
GO
549
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_spool]') AND type in (N'P', N'PC'))
550
DROP PROCEDURE [dbo].[add_spool]
551
GO
552
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_user]') AND type in (N'P', N'PC'))
553
DROP PROCEDURE [dbo].[add_user]
554
GO
555
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_password]') AND type in (N'P', N'PC'))
556
DROP PROCEDURE [dbo].[set_password]
557
GO
558
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[clean_spool_msg]') AND type in (N'P', N'PC'))
559
DROP PROCEDURE [dbo].[clean_spool_msg]
560
GO
561
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_password]') AND type in (N'P', N'PC'))
562
DROP PROCEDURE [dbo].[get_password]
563
GO
564
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_last]') AND type in (N'P', N'PC'))
565
DROP PROCEDURE [dbo].[del_last]
566
GO
567
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_roster]') AND type in (N'P', N'PC'))
568
DROP PROCEDURE [dbo].[del_roster]
569
GO
570
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_spool_msg]') AND type in (N'P', N'PC'))
571
DROP PROCEDURE [dbo].[del_spool_msg]
572
GO
573
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user]') AND type in (N'P', N'PC'))
574
DROP PROCEDURE [dbo].[del_user]
575
GO
576
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user_return_password]') AND type in (N'P', N'PC'))
577
DROP PROCEDURE [dbo].[del_user_return_password]
578
GO
579
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user_roster]') AND type in (N'P', N'PC'))
580
DROP PROCEDURE [dbo].[del_user_roster]
581
GO
582
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'))
583
DROP PROCEDURE [dbo].[get_and_del_spool_msg]
584
GO
585
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_last]') AND type in (N'P', N'PC'))
586
DROP PROCEDURE [dbo].[get_last]
587
GO
588
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster]') AND type in (N'P', N'PC'))
589
DROP PROCEDURE [dbo].[get_roster]
590
GO
591
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster_by_jid]') AND type in (N'P', N'PC'))
592
DROP PROCEDURE [dbo].[get_roster_by_jid]
593
GO
594
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster_jid_groups]') AND type in (N'P', N'PC'))
595
DROP PROCEDURE [dbo].[get_roster_jid_groups]
596
GO
597
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_roster_groups]') AND type in (N'P', N'PC'))
598
DROP PROCEDURE [dbo].[get_roster_groups]
599
GO
600
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_rostergroup_by_jid]') AND type in (N'P', N'PC'))
601
DROP PROCEDURE [dbo].[get_rostergroup_by_jid]
602
GO
603
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_subscription]') AND type in (N'P', N'PC'))
604
DROP PROCEDURE [dbo].[get_subscription]
605
GO
606
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[list_users]') AND type in (N'P', N'PC'))
607
DROP PROCEDURE [dbo].[list_users]
608
GO
609
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_last]') AND type in (N'P', N'PC'))
610
DROP PROCEDURE [dbo].[set_last]
611
GO
612
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_private_data]') AND type in (N'P', N'PC'))
613
DROP PROCEDURE [dbo].[set_private_data]
614
GO
615
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_private_data]') AND type in (N'P', N'PC'))
616
DROP PROCEDURE [dbo].[get_private_data]
617
GO
618
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_user_storage]') AND type in (N'P', N'PC'))
619
DROP PROCEDURE [dbo].[del_user_storage]
620
GO
621
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_vcard]') AND type in (N'P', N'PC'))
622
DROP PROCEDURE [dbo].[set_vcard]
623
GO
624
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_vcard]') AND type in (N'P', N'PC'))
625
DROP PROCEDURE [dbo].[get_vcard]
626
GO
627
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_default_privacy_list]') AND type in (N'P', N'PC'))
628
DROP PROCEDURE [dbo].[get_default_privacy_list]
629
GO
630
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_privacy_list_names]') AND type in (N'P', N'PC'))
631
DROP PROCEDURE [dbo].[get_privacy_list_names]
632
GO
633
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_privacy_list_id]') AND type in (N'P', N'PC'))
634
DROP PROCEDURE [dbo].[get_privacy_list_id]
635
GO
636
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_privacy_list_data]') AND type in (N'P', N'PC'))
637
DROP PROCEDURE [dbo].[get_privacy_list_data]
638
GO
639
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'))
640
DROP PROCEDURE [dbo].[get_privacy_list_data_by_id]
641
GO
642
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_default_privacy_list]') AND type in (N'P', N'PC'))
643
DROP PROCEDURE [dbo].[set_default_privacy_list]
644
GO
645
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[unset_default_privacy_list]') AND type in (N'P', N'PC'))
646
DROP PROCEDURE [dbo].[unset_default_privacy_list]
647
GO
648
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[remove_privacy_list]') AND type in (N'P', N'PC'))
649
DROP PROCEDURE [dbo].[remove_privacy_list]
650
GO
651
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[add_privacy_list]') AND type in (N'P', N'PC'))
652
DROP PROCEDURE [dbo].[add_privacy_list]
653
GO
654
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_privacy_list]') AND type in (N'P', N'PC'))
655
DROP PROCEDURE [dbo].[set_privacy_list]
656
GO
657
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'))
658
DROP PROCEDURE [dbo].[del_privacy_list_by_id]
659
GO
660
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[del_privacy_lists]') AND type in (N'P', N'PC'))
661
DROP PROCEDURE [dbo].[del_privacy_lists]
662
GO
663
664
CREATE PROCEDURE [dbo].[add_roster]
665
@Username varchar(250),
666
@JID varchar(250),
667
@Nick varchar(50),
668
@Subscription char(1),
669
@Ask char(1),
670
@AskMessage varchar(250),
671
@Server char(1),
672
@Subscribe varchar(200),
673
@Type varchar(50),
674
@Grp varchar(100)
675
AS
676
BEGIN
677
BEGIN TRANSACTION
678
--- Update Roster if user exist else add roster item
679
IF EXISTS (SELECT username FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
680
BEGIN
681
UPDATE rosterusers
682
SET rosterusers.username=@Username,
683
rosterusers.jid=@JID,
684
rosterusers.nick=@Nick,
685
rosterusers.subscription=@Subscription,
686
rosterusers.ask=@Ask,
687
rosterusers.askmessage=@AskMessage,
688
rosterusers.server=@Server,
689
rosterusers.subscribe=@Subscribe,
690
rosterusers.type=@Type
691
WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
692
END
693
ELSE
694
BEGIN
695
INSERT INTO rosterusers
696
( rosterusers.username,
697
rosterusers.jid,
698
rosterusers.nick,
699
rosterusers.subscription,
700
rosterusers.ask,
701
rosterusers.askmessage,
702
rosterusers.server,
703
rosterusers.subscribe,
704
rosterusers.type
705
)
706
VALUES
707
( @Username,
708
@JID,
709
@Nick,
710
@Subscription,
711
@Ask,
712
@AskMessage,
713
@Server,
714
@Subscribe,
715
@Type
716
);
717
END
718
719
--- Update Roster Groups if exist else add group entry
720
IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
721
BEGIN
722
INSERT INTO rostergroups
723
( rostergroups.username,
724
rostergroups.jid,
725
rostergroups.grp
726
)
727
VALUES
728
( @Username,
729
@JID,
730
@Grp
731
);
732
END
733
734
COMMIT
735
END
736
GO
737
738
/***************************************************************/
739
/****** Object: StoredProcedure [dbo].[add_roster_group] ******/
740
/** Add or update user group entries in the roster groups **/
741
/***************************************************************/
742
CREATE PROCEDURE [dbo].[add_roster_group]
743
@Username varchar(250),
744
@JID varchar(250),
745
@Grp varchar(100)
746
AS
747
BEGIN
748
--- Update Roster Groups if exist else add group
749
IF NOT EXISTS (SELECT username FROM rostergroups WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp)
750
BEGIN
751
INSERT INTO rostergroups
752
( rostergroups.username,
753
rostergroups.jid,
754
rostergroups.grp
755
)
756
VALUES
757
( @Username,
758
@JID,
759
@Grp
760
)
761
END
762
END
763
GO
764
765
/***************************************************************/
766
/****** Object: StoredProcedure [dbo].[add_roster_user] ******/
767
/** Add or update user entries in the roster **/
768
/***************************************************************/
769
CREATE PROCEDURE [dbo].[add_roster_user]
770
@Username varchar(250),
771
@JID varchar(250),
772
@Nick varchar(50),
773
@Subscription char(1),
774
@Ask char(1),
775
@AskMessage varchar(250),
776
@Server char(1),
777
@Subscribe varchar(200),
778
@Type varchar(50),
779
@Grp varchar(100) = Null
780
AS
781
BEGIN
782
BEGIN TRANSACTION
783
--- Update Roster Users if exist of add new user
784
IF EXISTS (SELECT username FROM rosterusers WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
785
BEGIN
786
UPDATE rosterusers
787
SET rosterusers.username=@Username,
788
rosterusers.jid=@JID,
789
rosterusers.nick=@Nick,
790
rosterusers.subscription=@Subscription,
791
rosterusers.ask=@Ask,
792
rosterusers.askmessage=@AskMessage,
793
rosterusers.server=@Server,
794
rosterusers.subscribe=@Subscribe,
795
rosterusers.type=@Type
796
WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID);
797
END
798
ELSE
799
BEGIN
800
INSERT INTO rosterusers
801
( rosterusers.username,
802
rosterusers.jid,
803
rosterusers.nick,
804
rosterusers.subscription,
805
rosterusers.ask,
806
rosterusers.askmessage,
807
rosterusers.server,
808
rosterusers.subscribe,
809
rosterusers.type
810
)
811
VALUES
812
( @Username,
813
@JID,
814
@Nick,
815
@Subscription,
816
@Ask,
817
@AskMessage,
818
@Server,
819
@Subscribe,
820
@Type
821
);
822
END
823
824
--- Update Roster Group if exist of add new group
825
IF @Grp IS NOT NULL
826
EXECUTE [dbo].[add_roster_group] @Username, @JID, @Grp
827
828
COMMIT
829
END
830
GO
831
832
/***************************************************************/
833
/****** Object: StoredProcedure [dbo].[del_roster_groups] ******/
834
/** Remove user group entries from the roster groups table **/
835
/***************************************************************/
836
CREATE PROCEDURE [dbo].[del_roster_groups]
837
@Username varchar(250),
838
@JID varchar(250)
839
AS
840
BEGIN
841
DELETE FROM rostergroups
842
WITH (ROWLOCK)
843
WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
844
END
845
GO
846
847
/***************************************************************/
848
/****** Object: StoredProcedure [dbo].[add_spool] ******/
849
/** Add a entry to the spool table **/
850
/***************************************************************/
851
CREATE PROCEDURE [dbo].[add_spool]
852
@Username varchar(250),
853
@XML varchar(8000)
854
AS
855
BEGIN
856
INSERT INTO spool
857
( spool.username,
858
spool.xml
859
)
860
VALUES
861
( @Username,
862
@XML
863
)
864
END
865
GO
866
867
/***************************************************************/
868
/****** Object: StoredProcedure [dbo].[add_user] ******/
869
/** Add or update user entries to jabber **/
870
/***************************************************************/
871
CREATE PROCEDURE [dbo].[add_user]
872
@Username varchar(200),
873
@Password varchar(50)
874
AS
875
BEGIN
876
INSERT INTO users
877
( [username],
878
[password]
879
)
880
VALUES
881
( @Username,
882
@Password
883
);
884
END
885
GO
886
887
/******************************************************************/
888
/****** Object: StoredProcedure [dbo].[set_password] **/
889
/** Update users password **/
890
/******************************************************************/
891
CREATE PROCEDURE [dbo].[set_password]
892
@Username varchar(200),
893
@Password varchar(50)
894
AS
895
BEGIN
896
IF EXISTS (SELECT username FROM users WITH (NOLOCK) WHERE username=@Username)
897
BEGIN
898
UPDATE users SET username=@Username, password=@Password WHERE username=@Username;
899
END
900
ELSE
901
BEGIN
902
INSERT INTO users (username, password) VALUES (@Username, @Password);
903
END
904
END
905
GO
906
907
/******************************************************************/
908
/****** Object: StoredProcedure [dbo].[get_password] **/
909
/** Retrive the user password **/
910
/******************************************************************/
911
CREATE PROCEDURE [dbo].[get_password]
912
@Username varchar(200)
913
AS
914
BEGIN
915
SELECT users.password as password
916
FROM users WITH (NOLOCK)
917
WHERE username=@Username;
918
END
919
GO
920
921
/******************************************************************/
922
/****** Object: StoredProcedure [dbo].[set_roster_version] **/
923
/** Update users roster_version **/
924
/******************************************************************/
925
CREATE PROCEDURE [dbo].[set_roster_version]
926
@Username varchar(200),
927
@Version varchar(8000)
928
AS
929
BEGIN
930
IF EXISTS (SELECT username FROM roster_version WITH (NOLOCK) WHERE username=@Username)
931
BEGIN
932
UPDATE roster_version SET username=@Username, version=@Version WHERE username=@Username;
933
END
934
ELSE
935
BEGIN
936
INSERT INTO roster_version (username, version) VALUES (@Username, @Version);
937
END
938
END
939
GO
940
941
/******************************************************************/
942
/****** Object: StoredProcedure [dbo].[get_roster_version] **/
943
/** Retrive the user roster_version **/
944
/******************************************************************/
945
CREATE PROCEDURE [dbo].[get_roster_version]
946
@Username varchar(200)
947
AS
948
BEGIN
949
SELECT roster_version.version as version
950
FROM roster_version WITH (NOLOCK)
951
WHERE username=@Username;
952
END
953
GO
954
955
/***************************************************************/
956
/****** Object: StoredProcedure [dbo].[clean_spool_msg] ******/
957
/** Delete messages older that 3 days from spool **/
958
/***************************************************************/
959
CREATE PROCEDURE [dbo].[clean_spool_msg]
960
AS
961
DECLARE
962
@dt datetime,
963
@myRowCount int
964
BEGIN
965
-- Delete small amounts because if locks the database table
966
SET ROWCOUNT 500
967
SET @myRowCount = 1
968
969
WHILE (@myRowCount) > 0
970
BEGIN
971
BEGIN TRANSACTION
972
SELECT @dt = DATEADD(d, -3, GETDATE())
973
DELETE FROM spool
974
WITH (ROWLOCK)
975
WHERE (MustDelete=1) OR (Created < @dt);
976
977
SET @myRowCount = @@RowCount
978
COMMIT
979
END
980
END
981
GO
982
983
/***************************************************************/
984
/****** Object: StoredProcedure [dbo].[del_last] ******/
985
/** Delete an entry from the last table **/
986
/***************************************************************/
987
CREATE PROCEDURE [dbo].[del_last]
988
@Username varchar(250)
989
AS
990
BEGIN
991
DELETE FROM [last]
992
WITH (ROWLOCK)
993
WHERE [last].username=@Username;
994
END
995
GO
996
997
/***************************************************************/
998
/****** Object: StoredProcedure [dbo].[del_roster] ******/
999
/** Delete an entry from the roster **/
1000
/***************************************************************/
1001
CREATE PROCEDURE [dbo].[del_roster]
1002
@Username varchar(250),
1003
@JID varchar(250)
1004
AS
1005
BEGIN
1006
BEGIN TRANSACTION
1007
DELETE FROM rosterusers
1008
WITH (ROWLOCK)
1009
WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
1010
1011
DELETE FROM rostergroups
1012
WITH (ROWLOCK)
1013
WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
1014
COMMIT
1015
END
1016
GO
1017
1018
1019
/***************************************************************/
1020
/****** Object: StoredProcedure [dbo].[del_spool_msg] ******/
1021
/** Delete an entry from the spool table **/
1022
/***************************************************************/
1023
CREATE PROCEDURE [dbo].[del_spool_msg]
1024
@Username varchar(250)
1025
AS
1026
BEGIN
1027
DELETE FROM spool
1028
WITH (ROWLOCK)
1029
WHERE spool.username=@Username;
1030
END
1031
GO
1032
1033
/***************************************************************/
1034
/****** Object: StoredProcedure [dbo].[del_user] ******/
1035
/** Delete an entry from the user table **/
1036
/***************************************************************/
1037
CREATE PROCEDURE [dbo].[del_user]
1038
@Username varchar(200)
1039
AS
1040
BEGIN
1041
DELETE FROM users
1042
WITH (ROWLOCK)
1043
WHERE username=@Username;
1044
END
1045
GO
1046
1047
/******************************************************************/
1048
/****** Object: StoredProcedure [dbo].[del_user_return_password]**/
1049
/** Delete an entry from the user table and return user password **/
1050
/******************************************************************/
1051
CREATE PROCEDURE [dbo].[del_user_return_password]
1052
@Username varchar(250)
1053
AS
1054
DECLARE
1055
@Pwd varchar(50)
1056
BEGIN
1057
EXECUTE @Pwd = dbo.get_password @Username
1058
DELETE FROM users
1059
WITH (ROWLOCK)
1060
WHERE username=@Username
1061
1062
SELECT @Pwd;
1063
END
1064
GO
1065
1066
1067
/******************************************************************/
1068
/****** Object: StoredProcedure [dbo].[del_user_roster] **/
1069
/** Delete the users roster **/
1070
/******************************************************************/
1071
CREATE PROCEDURE [dbo].[del_user_roster]
1072
@Username varchar(250)
1073
AS
1074
BEGIN
1075
BEGIN TRANSACTION
1076
DELETE FROM rosterusers
1077
WITH (ROWLOCK)
1078
WHERE rosterusers.username = @Username;
1079
1080
DELETE FROM rostergroups
1081
WITH (ROWLOCK)
1082
WHERE rostergroups.username = @Username;
1083
COMMIT
1084
END
1085
GO
1086
1087
1088
/******************************************************************/
1089
/****** Object: StoredProcedure [dbo].[get_and_del_spool_msg] **/
1090
/** Fetch and delete the users offline messages **/
1091
/******************************************************************/
1092
CREATE PROCEDURE [dbo].[get_and_del_spool_msg]
1093
@Username varchar(250)
1094
AS
1095
DECLARE
1096
@vSpool table( username varchar(1),
1097
xml varchar(1))
1098
BEGIN
1099
IF EXISTS (SELECT username FROM spool with (nolock) WHERE spool.username=@Username)
1100
BEGIN
1101
SELECT spool.username AS username,
1102
spool.xml AS xml
1103
FROM spool WITH (NOLOCK)
1104
WHERE spool.username=@Username;
1105
1106
DELETE spool
1107
WITH (ROWLOCK)
1108
WHERE spool.username=@Username
1109
END
1110
ELSE
1111
BEGIN
1112
SELECT * FROM @vSpool;
1113
END
1114
END
1115
GO
1116
1117
/******************************************************************/
1118
/****** Object: StoredProcedure [dbo].[get_last] **/
1119
/** Retrive the last user login **/
1120
/******************************************************************/
1121
CREATE PROCEDURE [dbo].[get_last]
1122
@Username varchar(250)
1123
AS
1124
BEGIN
1125
SELECT last.seconds AS seconds,
1126
last.state AS state
1127
FROM last WITH (NOLOCK)
1128
WHERE last.username=@Username;
1129
END
1130
GO
1131
1132
/******************************************************************/
1133
/****** Object: StoredProcedure [dbo].[get_roster] **/
1134
/** Retrive the user roster **/
1135
/******************************************************************/
1136
CREATE PROCEDURE [dbo].[get_roster]
1137
@Username varchar(250)
1138
AS
1139
DECLARE
1140
@vRosterusers table( username varchar(1),
1141
jid varchar(1),
1142
nick varchar(1),
1143
subscription varchar(1),
1144
ask varchar(1),
1145
askmessage varchar(1),
1146
server varchar(1),
1147
subscribe varchar(1),
1148
type varchar(1))
1149
BEGIN
1150
IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username)
1151
BEGIN
1152
SELECT rosterusers.username AS username,
1153
rosterusers.jid AS jid,
1154
rosterusers.nick AS nick,
1155
rosterusers.subscription AS subscription,
1156
rosterusers.ask AS ask,
1157
rosterusers.askmessage AS askmessage,
1158
rosterusers.server AS server,
1159
rosterusers.subscribe AS subscribe,
1160
rosterusers.type AS type
1161
FROM rosterusers WITH (NOLOCK)
1162
WHERE rosterusers.username = @Username;
1163
END
1164
ELSE
1165
BEGIN
1166
SELECT * FROM @vRosterusers
1167
END
1168
END
1169
GO
1170
1171
/******************************************************************/
1172
/****** Object: StoredProcedure [dbo].[get_roster_by_jid] **/
1173
/** Retrive the user roster via JID **/
1174
/******************************************************************/
1175
CREATE PROCEDURE [dbo].[get_roster_by_jid]
1176
@Username varchar(200),
1177
@JID varchar(250)
1178
AS
1179
DECLARE
1180
@vRosterusers table( username varchar(1),
1181
jid varchar(1),
1182
nick varchar(1),
1183
subscription varchar(1),
1184
ask varchar(1),
1185
askmessage varchar(1),
1186
server varchar(1),
1187
subscribe varchar(1),
1188
type varchar(1))
1189
BEGIN
1190
IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID))
1191
BEGIN
1192
SELECT rosterusers.username AS username,
1193
rosterusers.jid AS jid,
1194
rosterusers.nick AS nick,
1195
rosterusers.subscription AS subscription,
1196
rosterusers.ask AS ask,
1197
rosterusers.askmessage AS askmessage,
1198
rosterusers.server AS server,
1199
rosterusers.subscribe AS subscribe,
1200
rosterusers.type AS type
1201
FROM rosterusers WITH (NOLOCK)
1202
WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID);
1203
END
1204
ELSE
1205
BEGIN
1206
SELECT * FROM @vRosterusers
1207
END
1208
END
1209
GO
1210
1211
/******************************************************************/
1212
/****** Object: StoredProcedure [dbo].[get_roster_jid_groups] **/
1213
/** Retrieve the user roster groups **/
1214
/******************************************************************/
1215
CREATE PROCEDURE [dbo].[get_roster_jid_groups]
1216
@Username varchar(200)
1217
AS
1218
DECLARE
1219
@vrostergroups table( jid varchar(1),
1220
grp varchar(1))
1221
BEGIN
1222
IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
1223
BEGIN
1224
SELECT rostergroups.jid AS jid,
1225
rostergroups.grp AS grp
1226
FROM rostergroups WITH (NOLOCK)
1227
WHERE rostergroups.username = @Username;
1228
END
1229
ELSE
1230
BEGIN
1231
SELECT * FROM @vrostergroups
1232
END
1233
END
1234
GO
1235
1236
/******************************************************************/
1237
/****** Object: StoredProcedure [dbo].[get_roster_groups] **/
1238
/** Retrive the user roster groups **/
1239
/******************************************************************/
1240
CREATE PROCEDURE [dbo].[get_roster_groups]
1241
@Username varchar(200),
1242
@JID varchar(250)
1243
AS
1244
DECLARE
1245
@vrostergroups table( grp varchar(1))
1246
BEGIN
1247
IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username)
1248
BEGIN
1249
SELECT rostergroups.grp AS grp
1250
FROM rostergroups WITH (NOLOCK)
1251
WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);
1252
END
1253
ELSE
1254
BEGIN
1255
SELECT * FROM @vrostergroups
1256
END
1257
END
1258
GO
1259
1260
/******************************************************************/
1261
/****** Object: StoredProcedure [dbo].[get_rostergroup_by_jid] **/
1262
/** Retrive the user roster groups via JID **/
1263
/******************************************************************/
1264
CREATE PROCEDURE [dbo].[get_rostergroup_by_jid]
1265
@Username varchar(250),
1266
@JID varchar(250)
1267
AS
1268
DECLARE
1269
@vrostergroups table(grp varchar(1))
1270
BEGIN
1271
IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID)
1272
BEGIN
1273
SELECT rostergroups.grp AS grp
1274
FROM rostergroups WITH (NOLOCK)
1275
WHERE rostergroups.username=@Username AND rostergroups.jid=@JID;
1276
END
1277
ELSE
1278
BEGIN
1279
SELECT * FROM @vrostergroups
1280
END
1281
END
1282
GO
1283
1284
/******************************************************************/
1285
/****** Object: StoredProcedure [dbo].[get_subscription] **/
1286
/** Retrive the user subscription requests **/
1287
/******************************************************************/
1288
CREATE PROCEDURE [dbo].[get_subscription]
1289
@Username varchar(250),
1290
@JID varchar(250)
1291
AS
1292
DECLARE
1293
@vrosterusers table( subscription varchar(1))
1294
BEGIN
1295
IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID)
1296
BEGIN
1297
SELECT rosterusers.subscription AS subscription
1298
FROM rosterusers WITH (NOLOCK)
1299
WHERE rosterusers.username=@Username AND rosterusers.jid=@JID;
1300
END
1301
ELSE
1302
BEGIN
1303
SELECT * FROM @vrosterusers
1304
END
1305
END
1306
GO
1307
1308
/******************************************************************/
1309
/****** Object: StoredProcedure [dbo].[list_users] **/
1310
/** Retrieve a list of all users **/
1311
/******************************************************************/
1312
CREATE PROCEDURE [dbo].[list_users]
1313
AS
1314
BEGIN
1315
SELECT users.username AS username FROM users WITH (NOLOCK);
1316
END
1317
GO
1318
1319
/******************************************************************/
1320
/****** Object: StoredProcedure [dbo].[set_last] **/
1321
/** Update users last login status **/
1322
/******************************************************************/
1323
CREATE PROCEDURE [dbo].[set_last]
1324
@Username varchar(250),
1325
@Seconds varchar(50),
1326
@State varchar(100)
1327
AS
1328
BEGIN
1329
IF EXISTS (SELECT username FROM [last] WITH (NOLOCK) WHERE username=@Username)
1330
BEGIN
1331
UPDATE [last]
1332
SET [last].username = @Username,
1333
[last].seconds = @Seconds,
1334
[last].state = @State
1335
WHERE last.username=@Username;
1336
END
1337
ELSE
1338
BEGIN
1339
INSERT INTO [last]
1340
( [last].username,
1341
[last].seconds,
1342
[last].state
1343
)
1344
VALUES
1345
( @Username,
1346
@Seconds,
1347
@State
1348
)
1349
END
1350
END
1351
GO
1352
1353
/******************************************************************/
1354
/****** Object: StoredProcedure [dbo].[set_private_data] **/
1355
/** store user private data by namespace **/
1356
/******************************************************************/
1357
CREATE PROCEDURE [dbo].[set_private_data]
1358
@Username varchar(250),
1359
@Namespace varchar(250),
1360
@Data varchar(8000)
1361
AS
1362
BEGIN
1363
IF EXISTS (SELECT username FROM private_storage with (nolock) WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace)
1364
BEGIN
1365
UPDATE [private_storage]
1366
SET [private_storage].username = @Username,
1367
[private_storage].namespace = @Namespace,
1368
[private_storage].data = @Data
1369
WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace;
1370
END
1371
ELSE
1372
BEGIN
1373
INSERT INTO [private_storage]
1374
( [private_storage].username,
1375
[private_storage].namespace,
1376
[private_storage].data
1377
)
1378
VALUES
1379
( @Username,
1380
@Namespace,
1381
@Data
1382
)
1383
END
1384
END
1385
GO
1386
1387
/******************************************************************/
1388
/****** Object: StoredProcedure [dbo].[get_private_data] **/
1389
/** Retrieve user private data by namespace **/
1390
/******************************************************************/
1391
CREATE PROCEDURE [dbo].[get_private_data]
1392
@Username varchar(250),
1393
@Namespace varchar(250)
1394
AS
1395
BEGIN
1396
SELECT private_storage.data AS data
1397
FROM private_storage WITH (NOLOCK)
1398
WHERE username=@Username and namespace=@Namespace;
1399
END
1400
GO
1401
1402
/***************************************************************/
1403
/****** Object: StoredProcedure [dbo].[del_user_storage] ******/
1404
/** Delete private storage area for a given user **/
1405
/***************************************************************/
1406
CREATE PROCEDURE [dbo].[del_user_storage]
1407
@Username varchar(250)
1408
AS
1409
BEGIN
1410
DELETE FROM [private_storage]
1411
WITH (ROWLOCK)
1412
WHERE [private_storage].username=@Username;
1413
END
1414
GO
1415
1416
1417
/******************************************************************/
1418
/****** Object: StoredProcedure [dbo].[set_vcard] **/
1419
/** Set the user's vCard **/
1420
/******************************************************************/
1421
CREATE PROCEDURE [dbo].[set_vcard]
1422
@VCard varchar(8000),
1423
@Username varchar(250),
1424
@Lusername varchar(250),
1425
@Fn varchar(8000),
1426
@Lfn varchar(250),
1427
@Family varchar(8000),
1428
@Lfamily varchar(250),
1429
@Given varchar(8000),
1430
@Lgiven varchar(250),
1431
@Middle varchar(8000),
1432
@Lmiddle varchar(250),
1433
@Nickname varchar(8000),
1434
@Lnickname varchar(250),
1435
@Bday varchar(8000),
1436
@Lbday varchar(250),
1437
@Ctry varchar(8000),
1438
@Lctry varchar(250),
1439
@Locality varchar(8000),
1440
@Llocality varchar(250),
1441
@Email varchar(8000),
1442
@Lemail varchar(250),
1443
@Orgname varchar(8000),
1444
@Lorgname varchar(250),
1445
@Orgunit varchar(8000),
1446
@Lorgunit varchar(250)
1447
AS
1448
BEGIN
1449
IF EXISTS (SELECT username FROM vcard with (nolock) WHERE vcard.username = @Username)
1450
BEGIN
1451
UPDATE [vcard]
1452
SET [vcard].username = @LUsername,
1453
[vcard].vcard = @Vcard
1454
WHERE vcard.username = @LUsername;
1455
1456
UPDATE [vcard_search]
1457
SET [vcard_search].username = @Username,
1458
[vcard_search].lusername = @Lusername,
1459
[vcard_search].fn = @Fn,
1460
[vcard_search].lfn = @Lfn,
1461
[vcard_search].family = @Family,
1462
[vcard_search].lfamily = @Lfamily,
1463
[vcard_search].given = @Given,
1464
[vcard_search].lgiven = @Lgiven,
1465
[vcard_search].middle = @Middle,
1466
[vcard_search].lmiddle = @Lmiddle,
1467
[vcard_search].nickname = @Nickname,
1468
[vcard_search].lnickname = @Lnickname,
1469
[vcard_search].bday = @Bday,
1470
[vcard_search].lbday = @Lbday,
1471
[vcard_search].ctry = @Ctry,
1472
[vcard_search].lctry = @Lctry,
1473
[vcard_search].locality = @Locality,
1474
[vcard_search].llocality = @Llocality,
1475
[vcard_search].email = @Email,
1476
[vcard_search].lemail = @Lemail,
1477
[vcard_search].orgname = @Orgname,
1478
[vcard_search].lorgname = @Lorgname,
1479
[vcard_search].orgunit = @Orgunit,
1480
[vcard_search].lorgunit = @Lorgunit
1481
WHERE vcard_search.lusername = @LUsername;
1482
END
1483
ELSE
1484
BEGIN
1485
INSERT INTO [vcard]
1486
( [vcard].username,
1487
[vcard].vcard
1488
)
1489
VALUES
1490
( @lUsername,
1491
@Vcard
1492
);
1493
1494
INSERT INTO [vcard_search]
1495
(
1496
[vcard_search].username ,
1497
[vcard_search].lusername ,
1498
[vcard_search].fn ,
1499
[vcard_search].lfn ,
1500
[vcard_search].family ,
1501
[vcard_search].lfamily ,
1502
[vcard_search].given ,
1503
[vcard_search].lgiven ,
1504
[vcard_search].middle ,
1505
[vcard_search].lmiddle ,
1506
[vcard_search].nickname,
1507
[vcard_search].lnickname,
1508
[vcard_search].bday,
1509
[vcard_search].lbday,
1510
[vcard_search].ctry,
1511
[vcard_search].lctry,
1512
[vcard_search].locality,
1513
[vcard_search].llocality,
1514
[vcard_search].email,
1515
[vcard_search].lemail,
1516
[vcard_search].orgname,
1517
[vcard_search].lorgname,
1518
[vcard_search].orgunit,
1519
[vcard_search].lorgunit
1520
)
1521
VALUES
1522
(
1523
@Username,
1524
@Lusername,
1525
@Fn,
1526
@Lfn,
1527
@Family,
1528
@Lfamily,
1529
@Given,
1530
@Lgiven,
1531
@Middle,
1532
@Lmiddle,
1533
@Nickname,
1534
@Lnickname,
1535
@Bday,
1536
@Lbday,
1537
@Ctry,
1538
@Lctry,
1539
@Locality,
1540
@Llocality,
1541
@Email,
1542
@Lemail,
1543
@Orgname,
1544
@Lorgname,
1545
@Orgunit,
1546
@Lorgunit
1547
)
1548
END
1549
END
1550
GO
1551
1552
/******************************************************************/
1553
/****** Object: StoredProcedure [dbo].[get_vcard] **/
1554
/** Retrive the user's vCard **/
1555
/******************************************************************/
1556
CREATE PROCEDURE [dbo].[get_vcard]
1557
@Username varchar(250)
1558
AS
1559
BEGIN
1560
SELECT vcard.vcard as vcard
1561
FROM vcard WITH (NOLOCK)
1562
WHERE username=@Username;
1563
END
1564
GO
1565
1566
/******************************************************************/
1567
/****** Object: StoredProcedure [dbo].[get_default_privacy_list]**/
1568
/** Retrive the user's default privacy list **/
1569
/******************************************************************/
1570
CREATE PROCEDURE [dbo].[get_default_privacy_list]
1571
@Username varchar(250)
1572
AS
1573
BEGIN
1574
SELECT list.name
1575
FROM privacy_default_list list WITH (NOLOCK)
1576
WHERE list.username=@Username
1577
END
1578
GO
1579
1580
/******************************************************************/
1581
/****** Object: StoredProcedure [dbo].[get_privacy_list_names] **/
1582
/** Retrive the user's default privacy list names **/
1583
/******************************************************************/
1584
CREATE PROCEDURE [dbo].[get_privacy_list_names]
1585
@username varchar(250)
1586
AS
1587
BEGIN
1588
SELECT list.name
1589
FROM privacy_list list WITH (NOLOCK)
1590
WHERE list.username=@Username
1591
END
1592
GO
1593
1594
/******************************************************************/
1595
/****** Object: StoredProcedure [dbo].[get_privacy_list_id] **/
1596
/** **/
1597
/******************************************************************/
1598
CREATE PROCEDURE [dbo].[get_privacy_list_id]
1599
@username varchar(250),
1600
@SName varchar(250)
1601
AS
1602
BEGIN
1603
SELECT id FROM privacy_list
1604
WHERE username=@Username
1605
AND name=@SName
1606
END
1607
GO
1608
1609
/******************************************************************/
1610
/****** Object: StoredProcedure [dbo].[get_privacy_list_data] **/
1611
/** **/
1612
/******************************************************************/
1613
CREATE PROCEDURE [dbo].[get_privacy_list_data]
1614
@username varchar(250),
1615
@SName varchar(250)
1616
AS
1617
BEGIN
1618
SELECT l_data.t,
1619
l_data.value,
1620
l_data.action,
1621
l_data.ord,
1622
l_data.match_all,
1623
l_data.match_iq,
1624
l_data.match_message,
1625
l_data.match_presence_in,
1626
l_data.match_presence_out
1627
FROM privacy_list_data l_data (NOLOCK)
1628
WHERE l_data.id = (SELECT list.id
1629
FROM privacy_list list
1630
WHERE list.username=@username
1631
AND list.name=@SName)
1632
ORDER BY l_data.ord
1633
END
1634
GO
1635
1636
/******************************************************************/
1637
/****** Object: StoredProcedure [dbo].[get_privacy_list_data_by_id]**/
1638
/** **/
1639
/******************************************************************/
1640
CREATE PROCEDURE [dbo].[get_privacy_list_data_by_id]
1641
@Id bigint
1642
AS
1643
BEGIN
1644
SELECT l_data.t,
1645
l_data.value,
1646
l_data.action,
1647
l_data.ord,
1648
l_data.match_all,
1649
l_data.match_iq,
1650
l_data.match_message,
1651
l_data.match_presence_in,
1652
l_data.match_presence_out
1653
FROM privacy_list_data l_data (NOLOCK)
1654
WHERE l_data.id=@ID
1655
ORDER BY l_data.ord
1656
END
1657
GO
1658
1659
/******************************************************************/
1660
/****** Object: StoredProcedure [dbo].[set_default_privacy_list]**/
1661
/** **/
1662
/******************************************************************/
1663
CREATE PROCEDURE [dbo].[set_default_privacy_list]
1664
@username varchar(250),
1665
@Sname varchar(250)
1666
AS
1667
BEGIN
1668
IF EXISTS (SELECT username FROM privacy_default_list with (nolock) WHERE privacy_default_list.username = @Username AND privacy_default_list.name = @Sname)
1669
BEGIN
1670
UPDATE [privacy_default_list]
1671
SET [privacy_default_list].username = @Username,
1672
[privacy_default_list].name = @Sname
1673
WHERE privacy_default_list.username = @Username
1674
END
1675
ELSE
1676
BEGIN
1677
INSERT INTO [privacy_default_list]
1678
( [privacy_default_list].username,
1679
[privacy_default_list].name
1680
)
1681
VALUES
1682
( @Username,
1683
@SName
1684
)
1685
END
1686
END
1687
GO
1688
1689
/******************************************************************/
1690
/****** Object: StoredProcedure [dbo].[unset_default_privacy_list]**/
1691
/** **/
1692
/******************************************************************/
1693
CREATE PROCEDURE [dbo].[unset_default_privacy_list]
1694
@username varchar(250)
1695
AS
1696
BEGIN
1697
DELETE
1698
FROM privacy_default_list
1699
WHERE privacy_default_list.username=@username
1700
END
1701
GO
1702
1703
/******************************************************************/
1704
/****** Object: StoredProcedure [dbo].[remove_privacy_list] **/
1705
/** **/
1706
/******************************************************************/
1707
CREATE PROCEDURE [dbo].[remove_privacy_list]
1708
@username varchar(250),
1709
@SName varchar(250)
1710
AS
1711
BEGIN
1712
DELETE
1713
FROM privacy_list
1714
WHERE privacy_list.username=@username
1715
AND privacy_list.name=@SName
1716
END
1717
GO
1718
1719
/******************************************************************/
1720
/****** Object: StoredProcedure [dbo].[add_privacy_list] **/
1721
/** **/
1722
/******************************************************************/
1723
CREATE PROCEDURE [dbo].[add_privacy_list]
1724
@username varchar(250),
1725
@SName varchar(250)
1726
AS
1727
BEGIN
1728
INSERT INTO privacy_list(username, name)
1729
VALUES (@username, @SName)
1730
END
1731
GO
1732
1733
/******************************************************************/
1734
/****** Object: StoredProcedure [dbo].[set_privacy_list] **/
1735
/** **/
1736
/******************************************************************/
1737
CREATE PROCEDURE [dbo].[set_privacy_list]
1738
@Id bigint,
1739
@t char(1),
1740
@value text,
1741
@action char(1),
1742
@ord numeric,
1743
@match_all bit,
1744
@match_iq bit,
1745
@match_message bit,
1746
@match_presence_in bit,
1747
@match_presence_out bit
1748
AS
1749
BEGIN
1750
insert into privacy_list_data (
1751
id,
1752
t,
1753
value,
1754
action,
1755
ord,
1756
match_all,
1757
match_iq,
1758
match_message,
1759
match_presence_in,
1760
match_presence_out
1761
)
1762
values (@Id,
1763
@t,
1764
@value,
1765
@action,
1766
@ord,
1767
@match_all,
1768
@match_iq,
1769
@match_message,
1770
@match_presence_in,
1771
@match_presence_out
1772
)
1773
1774
END
1775
GO
1776
1777
/******************************************************************/
1778
/****** Object: StoredProcedure [dbo].[del_privacy_list_by_id] **/
1779
/** **/
1780
/******************************************************************/
1781
CREATE PROCEDURE [dbo].[del_privacy_list_by_id]
1782
@Id bigint
1783
AS
1784
BEGIN
1785
DELETE FROM privacy_list_data
1786
WHERE privacy_list_data.id=@Id
1787
END
1788
GO
1789
1790
/******************************************************************/
1791
/****** Object: StoredProcedure [dbo].[del_privacy_lists] **/
1792
/** **/
1793
/******************************************************************/
1794
CREATE PROCEDURE [dbo].[del_privacy_lists]
1795
@Server varchar(250),
1796
@username varchar(250)
1797
AS
1798
BEGIN
1799
DELETE FROM privacy_list WHERE username=@username
1800
DELETE FROM privacy_list_data WHERE convert(varchar,value)=@username+'@'+@Server
1801
DELETE FROM privacy_default_list WHERE username=@username
1802
END
1803
GO
Loggerhead is a web-based interface for Breezy
Version: 2.0.1