diff options
Diffstat (limited to 'src/odbc/mssql2005.sql')
| -rw-r--r-- | src/odbc/mssql2005.sql | 382 |
1 files changed, 284 insertions, 98 deletions
diff --git a/src/odbc/mssql2005.sql b/src/odbc/mssql2005.sql index ca5007f15..f61980c0a 100644 --- a/src/odbc/mssql2005.sql +++ b/src/odbc/mssql2005.sql @@ -10,7 +10,7 @@ * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * General Public License for more details. - * + * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA @@ -104,6 +104,10 @@ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard]') a drop table [dbo].[vcard] GO +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard_search]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[vcard_search] +GO + if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[private_storage] GO @@ -119,7 +123,7 @@ GO CREATE TABLE [dbo].[rostergroups] ( [username] [varchar] (250) NOT NULL , [jid] [varchar] (250) NOT NULL , - [grp] [varchar] (100) NOT NULL + [grp] [varchar] (100) NOT NULL ) ON [PRIMARY] GO @@ -133,7 +137,7 @@ CREATE TABLE [dbo].[rosterusers] ( [server] [char] (1) NOT NULL , [subscribe] [varchar] (200) NULL , [type] [varchar] (50) NULL , -CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED +CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED ( [username] ASC, [jid] ASC @@ -154,31 +158,41 @@ GO CREATE TABLE [dbo].[users] ( [username] [varchar] (250) NOT NULL , [password] [varchar] (50) NOT NULL , - [created] [datetime] NULL + [created] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[vcard] ( [username] [varchar] (250) NOT NULL , - [full_name] [varchar] (250) NULL , - [first_name] [varchar] (50) NULL , - [last_name] [varchar] (50) NULL , - [nick_name] [varchar] (50) NULL , - [url] [varchar] (1024) NULL , - [address1] [varchar] (50) NULL , - [address2] [varchar] (50) NULL , - [locality] [varchar] (50) NULL , - [region] [varchar] (50) NULL , - [pcode] [varchar] (50) NULL , - [country] [varchar] (50) NULL , - [telephone] [varchar] (50) NULL , - [email] [varchar] (250) NULL , - [orgname] [varchar] (50) NULL , - [orgunit] [varchar] (50) NULL , - [title] [varchar] (50) NULL , - [role] [varchar] (50) NULL , - [b_day] [datetime] NULL , - [descr] [varchar] (500) NULL + [vcard] [text] NOT NULL +) ON [PRIMARY] +GO + +CREATE TABLE [dbo].[vcard_search] ( + [username] [varchar] (250) NOT NULL , + [lusername] [varchar] (250) NOT NULL , + [fn] [text] NOT NULL , + [lfn] [varchar] (250) NOT NULL , + [family] [text] NOT NULL , + [lfamily] [varchar] (250) NOT NULL , + [given] [text] NOT NULL , + [lgiven] [varchar] (250) NOT NULL , + [middle] [text] NOT NULL , + [lmiddle] [varchar] (250) NOT NULL , + [nickname] [text] NOT NULL , + [lnickname] [varchar] (250) NOT NULL , + [bday] [text] NOT NULL , + [lbday] [varchar] (250) NOT NULL , + [ctry] [text] NOT NULL , + [lctry] [varchar] (250) NOT NULL , + [locality] [text] NOT NULL , + [llocality] [varchar] (250) NOT NULL , + [email] [text] NOT NULL , + [lemail] [varchar] (250) NOT NULL , + [orgname] [text] NOT NULL , + [lorgname] [varchar] (250) NOT NULL , + [orgunit] [text] NOT NULL , + [lorgunit] [varchar] (250) NOT NULL ) ON [PRIMARY] GO @@ -199,7 +213,7 @@ CREATE TABLE [dbo].[privacy_list]( [username] [varchar](250) NOT NULL, [name] [varchar](250) NOT NULL, [id] [bigint] IDENTITY(1,1) NOT NULL, - CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED + CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] @@ -224,73 +238,98 @@ GO - id in privacy_list is a SERIAL autogenerated number - id in privacy_list_data must exist in the table privacy_list */ -ALTER TABLE [dbo].[last] WITH NOCHECK ADD - CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED +ALTER TABLE [dbo].[last] WITH NOCHECK ADD + CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED ( [username] - ) WITH FILLFACTOR = 90 ON [PRIMARY] + ) WITH FILLFACTOR = 90 ON [PRIMARY] GO -ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD - CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED +ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD + CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED ( [username], [jid], [grp] - ) WITH FILLFACTOR = 90 ON [PRIMARY] + ) WITH FILLFACTOR = 90 ON [PRIMARY] GO -ALTER TABLE [dbo].[spool] WITH NOCHECK ADD - CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED +ALTER TABLE [dbo].[spool] WITH NOCHECK ADD + CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED ( [username], [id] - ) WITH FILLFACTOR = 90 ON [PRIMARY] + ) WITH FILLFACTOR = 90 ON [PRIMARY] GO -ALTER TABLE [dbo].[users] WITH NOCHECK ADD - CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED +ALTER TABLE [dbo].[users] WITH NOCHECK ADD + CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [username] - ) WITH FILLFACTOR = 90 ON [PRIMARY] + ) WITH FILLFACTOR = 90 ON [PRIMARY] GO -ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD - CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED +ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD + CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED ( [username] - ) WITH FILLFACTOR = 90 ON [PRIMARY] + ) WITH FILLFACTOR = 90 ON [PRIMARY] GO + +CREATE INDEX [IX_vcard_search_lfn] ON [dbo].[vcard_search]([lfn]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lfamily] ON [dbo].[vcard_search]([lfamily]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lgiven] ON [dbo].[vcard_search]([lgiven]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lmiddle] ON [dbo].[vcard_search]([lmiddle]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lnickname] ON [dbo].[vcard_search]([lnickname]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lbday] ON [dbo].[vcard_search]([lbday]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lctry] ON [dbo].[vcard_search]([lctry]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_llocality] ON [dbo].[vcard_search]([llocality]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lemail] ON [dbo].[vcard_search]([lemail]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lorgname] ON [dbo].[vcard_search]([lorgname]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO +CREATE INDEX [IX_vcard_search_lorgunit] ON [dbo].[vcard_search]([lorgunit]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] GO -ALTER TABLE [dbo].[last] WITH NOCHECK ADD +ALTER TABLE [dbo].[last] WITH NOCHECK ADD CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date] GO -ALTER TABLE [dbo].[spool] WITH NOCHECK ADD +ALTER TABLE [dbo].[spool] WITH NOCHECK ADD CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed], CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created], CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete] GO -ALTER TABLE [dbo].[users] WITH NOCHECK ADD +ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created] GO -ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD - CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED +ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD + CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED ( [username] - ) WITH FILLFACTOR = 90 ON [PRIMARY] + ) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY] GO CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] -GO +GO CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] GO @@ -385,7 +424,7 @@ BEGIN @Type ); END - + --- Update Roster Groups if exist else add group entry IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp) BEGIN @@ -543,12 +582,12 @@ CREATE PROCEDURE [dbo].[add_user] @Password varchar(50) AS BEGIN - INSERT INTO users - ( [username], + INSERT INTO users + ( [username], [password] - ) - VALUES - ( @Username, + ) + VALUES + ( @Username, @Password ); END @@ -559,7 +598,7 @@ GO /** Update users password **/ /******************************************************************/ CREATE PROCEDURE [dbo].[set_password] - @Username varchar(200), + @Username varchar(200), @Password varchar(50) AS BEGIN @@ -582,7 +621,7 @@ CREATE PROCEDURE [dbo].[get_password] @Username varchar(200) AS BEGIN - SELECT users.password as password + SELECT users.password as password FROM users WITH (NOLOCK) WHERE username=@Username; END @@ -594,7 +633,7 @@ GO /***************************************************************/ CREATE PROCEDURE [dbo].[clean_spool_msg] AS -DECLARE +DECLARE @dt datetime, @myRowCount int BEGIN @@ -606,7 +645,7 @@ BEGIN BEGIN BEGIN TRANSACTION SELECT @dt = DATEADD(d, -3, GETDATE()) - DELETE FROM spool + DELETE FROM spool WITH (ROWLOCK) WHERE (MustDelete=1) OR (Created < @dt); @@ -640,11 +679,11 @@ CREATE PROCEDURE [dbo].[del_roster] AS BEGIN BEGIN TRANSACTION - DELETE FROM rosterusers + DELETE FROM rosterusers WITH (ROWLOCK) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID); - - DELETE FROM rostergroups + + DELETE FROM rostergroups WITH (ROWLOCK) WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID); COMMIT @@ -660,7 +699,7 @@ CREATE PROCEDURE [dbo].[del_spool_msg] @Username varchar(250) AS BEGIN - DELETE FROM spool + DELETE FROM spool WITH (ROWLOCK) WHERE spool.username=@Username; END @@ -674,7 +713,7 @@ CREATE PROCEDURE [dbo].[del_user] @Username varchar(200) AS BEGIN - DELETE FROM users + DELETE FROM users WITH (ROWLOCK) WHERE username=@Username; END @@ -691,7 +730,7 @@ DECLARE @Pwd varchar(50) BEGIN EXECUTE @Pwd = dbo.get_password @Username - DELETE FROM users + DELETE FROM users WITH (ROWLOCK) WHERE username=@Username @@ -709,11 +748,11 @@ CREATE PROCEDURE [dbo].[del_user_roster] AS BEGIN BEGIN TRANSACTION - DELETE FROM rosterusers + DELETE FROM rosterusers WITH (ROWLOCK) WHERE rosterusers.username = @Username; - - DELETE FROM rostergroups + + DELETE FROM rostergroups WITH (ROWLOCK) WHERE rostergroups.username = @Username; COMMIT @@ -739,9 +778,9 @@ BEGIN FROM spool WITH (NOLOCK) WHERE spool.username=@Username; - DELETE spool + DELETE spool WITH (ROWLOCK) - WHERE spool.username=@Username + WHERE spool.username=@Username END ELSE BEGIN @@ -758,7 +797,7 @@ CREATE PROCEDURE [dbo].[get_last] @Username varchar(250) AS BEGIN - SELECT last.seconds AS seconds, + SELECT last.seconds AS seconds, last.state AS state FROM last WITH (NOLOCK) WHERE last.username=@Username; @@ -773,26 +812,26 @@ CREATE PROCEDURE [dbo].[get_roster] @Username varchar(250) AS DECLARE - @vRosterusers table( username varchar(1), - jid varchar(1), - nick varchar(1), - subscription varchar(1), - ask varchar(1), - askmessage varchar(1), - server varchar(1), - subscribe varchar(1), + @vRosterusers table( username varchar(1), + jid varchar(1), + nick varchar(1), + subscription varchar(1), + ask varchar(1), + askmessage varchar(1), + server varchar(1), + subscribe varchar(1), type varchar(1)) BEGIN IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username) BEGIN - SELECT rosterusers.username AS username, - rosterusers.jid AS jid, - rosterusers.nick AS nick, - rosterusers.subscription AS subscription, + SELECT rosterusers.username AS username, + rosterusers.jid AS jid, + rosterusers.nick AS nick, + rosterusers.subscription AS subscription, rosterusers.ask AS ask, rosterusers.askmessage AS askmessage, - rosterusers.server AS server, - rosterusers.subscribe AS subscribe, + rosterusers.server AS server, + rosterusers.subscribe AS subscribe, rosterusers.type AS type FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username = @Username; @@ -813,26 +852,26 @@ CREATE PROCEDURE [dbo].[get_roster_by_jid] @JID varchar(250) AS DECLARE - @vRosterusers table( username varchar(1), - jid varchar(1), - nick varchar(1), - subscription varchar(1), + @vRosterusers table( username varchar(1), + jid varchar(1), + nick varchar(1), + subscription varchar(1), ask varchar(1), askmessage varchar(1), - server varchar(1), - subscribe varchar(1), + server varchar(1), + subscribe varchar(1), type varchar(1)) BEGIN IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID)) BEGIN - SELECT rosterusers.username AS username, - rosterusers.jid AS jid, - rosterusers.nick AS nick, - rosterusers.subscription AS subscription, + SELECT rosterusers.username AS username, + rosterusers.jid AS jid, + rosterusers.nick AS nick, + rosterusers.subscription AS subscription, rosterusers.ask AS ask, rosterusers.askmessage AS askmessage, - rosterusers.server AS server, - rosterusers.subscribe AS subscribe, + rosterusers.server AS server, + rosterusers.subscribe AS subscribe, rosterusers.type AS type FROM rosterusers WITH (NOLOCK) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID); @@ -852,12 +891,12 @@ CREATE PROCEDURE [dbo].[get_roster_jid_groups] @Username varchar(200) AS DECLARE - @vrostergroups table( jid varchar(1), + @vrostergroups table( jid varchar(1), grp varchar(1)) BEGIN IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username) BEGIN - SELECT rostergroups.jid AS jid, + SELECT rostergroups.jid AS jid, rostergroups.grp AS grp FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username = @Username; @@ -930,7 +969,7 @@ DECLARE BEGIN IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID) BEGIN - SELECT rosterusers.subscription AS subscription + SELECT rosterusers.subscription AS subscription FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID; END @@ -958,7 +997,7 @@ GO /******************************************************************/ CREATE PROCEDURE [dbo].[set_last] @Username varchar(250), - @Seconds varchar(50), + @Seconds varchar(50), @State varchar(100) AS BEGIN @@ -1050,4 +1089,151 @@ END GO +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[set_vcard] **/ +/** Set the user's vCard **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[set_vcard] + @VCard varchar(8000), + @Username varchar(250), + @Lusername varchar(250), + @Fn varchar(8000), + @Lfn varchar(250), + @Family varchar(8000), + @Lfamily varchar(250), + @Given varchar(8000), + @Lgiven varchar(250), + @Middle varchar(8000), + @Lmiddle varchar(250), + @Nickname varchar(8000), + @Lnickname varchar(250), + @Bday varchar(8000), + @Lbday varchar(250), + @Ctry varchar(8000), + @Lctry varchar(250), + @Locality varchar(8000), + @Llocality varchar(250), + @Email varchar(8000), + @Lemail varchar(250), + @Orgname varchar(8000), + @Lorgname varchar(250), + @Orgunit varchar(8000), + @Lorgunit varchar(250) +AS +BEGIN + IF EXISTS (SELECT username FROM vcard with (nolock) WHERE vcard.username = @Username) + BEGIN + UPDATE [vcard] + SET [vcard].username = @LUsername, + [vcard].vcard = @Vcard + WHERE vcard.username = @LUsername; + + UPDATE [vcard_search] + SET [vcard_search].username = @Username, + [vcard_search].lusername = @Lusername, + [vcard_search].fn = @Fn, + [vcard_search].lfn = @Lfn, + [vcard_search].family = @Family, + [vcard_search].lfamily = @Lfamily, + [vcard_search].given = @Given, + [vcard_search].lgiven = @Lgiven, + [vcard_search].middle = @Middle, + [vcard_search].lmiddle = @Lmiddle, + [vcard_search].nickname = @Nickname, + [vcard_search].lnickname = @Lnickname, + [vcard_search].bday = @Bday, + [vcard_search].lbday = @Lbday, + [vcard_search].ctry = @Ctry, + [vcard_search].lctry = @Lctry, + [vcard_search].locality = @Locality, + [vcard_search].llocality = @Llocality, + [vcard_search].email = @Email, + [vcard_search].lemail = @Lemail, + [vcard_search].orgname = @Orgname, + [vcard_search].lorgname = @Lorgname, + [vcard_search].orgunit = @Orgunit, + [vcard_search].lorgunit = @Lorgunit + WHERE vcard_search.lusername = @LUsername; + END + ELSE + BEGIN + INSERT INTO [vcard] + ( [vcard].username, + [vcard].vcard + ) + VALUES + ( @lUsername, + @Vcard + ); + + INSERT INTO [vcard_search] + ( + [vcard_search].username , + [vcard_search].lusername , + [vcard_search].fn , + [vcard_search].lfn , + [vcard_search].family , + [vcard_search].lfamily , + [vcard_search].given , + [vcard_search].lgiven , + [vcard_search].middle , + [vcard_search].lmiddle , + [vcard_search].nickname, + [vcard_search].lnickname, + [vcard_search].bday, + [vcard_search].lbday, + [vcard_search].ctry, + [vcard_search].lctry, + [vcard_search].locality, + [vcard_search].llocality, + [vcard_search].email, + [vcard_search].lemail, + [vcard_search].orgname, + [vcard_search].lorgname, + [vcard_search].orgunit, + [vcard_search].lorgunit + ) + VALUES + ( + @Username, + @Lusername, + @Fn, + @Lfn, + @Family, + @Lfamily, + @Given, + @Lgiven, + @Middle, + @Lmiddle, + @Nickname, + @Lnickname, + @Bday, + @Lbday, + @Ctry, + @Lctry, + @Locality, + @Llocality, + @Email, + @Lemail, + @Orgname, + @Lorgname, + @Orgunit, + @Lorgunit + ) + END +END +GO +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_vcard] **/ +/** Retrive the user's vCard **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_vcard] + @Username varchar(250) +AS +BEGIN + SELECT vcard.vcard as vcard + FROM vcard WITH (NOLOCK) + WHERE username=@Username; +END +GO |