From d0e2ac9ed2432e877e1a55f46752b614d29fafb9 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Micka=C3=ABl=20R=C3=A9mond?= Date: Sun, 3 Sep 2006 15:15:46 +0000 Subject: * src/odbc/odbc_queries.erl: Support for Microsoft SQL Server as a database backend (via ODBC). * src/odbc/ejabberd_odbc.erl: Likewise. * src/odbc/mssql.sql: Likewise. * src/odbc/Makefile.in: Likewise. * src/ejabberd_auth_odbc.erl: Likewise. * src/mod_offline_odbc.erl: Likewise. * src/mod_roster_odbc.erl: Likewise. * src/mod_last_odbc.erl: Likewise. * src/configure.ac: Likewise * doc/guide.tex: Likewise. * doc/introduction.tex: Likewise. * src/odbc/pg.sql: Minor fix. SVN Revision: 598 --- src/odbc/Makefile.in | 5 +- src/odbc/Makefile.win32 | 7 +- src/odbc/ejabberd_odbc.erl | 13 +- src/odbc/mssql.sql | 901 +++++++++++++++++++++++++++++++++++++++++++++ src/odbc/odbc_queries.erl | 434 ++++++++++++++++++++++ src/odbc/pg.sql | 2 +- 6 files changed, 1347 insertions(+), 15 deletions(-) create mode 100644 src/odbc/mssql.sql create mode 100644 src/odbc/odbc_queries.erl (limited to 'src/odbc') diff --git a/src/odbc/Makefile.in b/src/odbc/Makefile.in index 8e291bebf..af2a2463f 100644 --- a/src/odbc/Makefile.in +++ b/src/odbc/Makefile.in @@ -13,12 +13,13 @@ OUTDIR = .. EFLAGS = -I .. -pz .. OBJS = \ $(OUTDIR)/ejabberd_odbc.beam \ - $(OUTDIR)/ejabberd_odbc_sup.beam + $(OUTDIR)/ejabberd_odbc_sup.beam \ + $(OUTDIR)/odbc_queries.beam all: $(OBJS) $(OUTDIR)/%.beam: %.erl - @ERLC@ -W $(EFLAGS) -o $(OUTDIR) $< + @ERLC@ -W $(EFLAGS) -D@db_type@ -o $(OUTDIR) $< clean: diff --git a/src/odbc/Makefile.win32 b/src/odbc/Makefile.win32 index 072813563..cd9fbfbe2 100644 --- a/src/odbc/Makefile.win32 +++ b/src/odbc/Makefile.win32 @@ -6,7 +6,8 @@ EFLAGS = -I .. -pz .. OBJS = \ $(OUTDIR)\ejabberd_odbc.beam \ - $(OUTDIR)\ejabberd_odbc_sup.beam + $(OUTDIR)\ejabberd_odbc_sup.beam \ + $(OUTDIR)\odbc_queries.beam ALL : $(OBJS) @@ -18,3 +19,7 @@ $(OUTDIR)\ejabberd_odbc.beam : ejabberd_odbc.erl $(OUTDIR)\ejabberd_odbc_sup.beam : ejabberd_odbc_sup.erl erlc -W $(EFLAGS) -o $(OUTDIR) ejabberd_odbc_sup.erl + +$(OUTDIR)\odbc_queries.beam : odbc_queries.erl + erlc -W $(EFLAGS) -o $(OUTDIR) -D$(DBTYPE) odbc_queries.erl + diff --git a/src/odbc/ejabberd_odbc.erl b/src/odbc/ejabberd_odbc.erl index b0776c5cb..b4fd41244 100644 --- a/src/odbc/ejabberd_odbc.erl +++ b/src/odbc/ejabberd_odbc.erl @@ -87,16 +87,7 @@ sql_query_t(Query) -> %% Escape character that will confuse an SQL engine escape(S) when is_list(S) -> - [escape(C) || C <- S]; -escape($\0) -> "\\0"; -escape($\n) -> "\\n"; -escape($\t) -> "\\t"; -escape($\b) -> "\\b"; -escape($\r) -> "\\r"; -escape($') -> "\\'"; -escape($") -> "\\\""; -escape($\\) -> "\\\\"; -escape(C) -> C. + [odbc_queries:escape(C) || C <- S]. %% Escape character that will confuse an SQL engine %% Percent and underscore only need to be escaped for pattern matching like @@ -105,7 +96,7 @@ escape_like(S) when is_list(S) -> [escape_like(C) || C <- S]; escape_like($%) -> "\\%"; escape_like($_) -> "\\_"; -escape_like(C) -> escape(C). +escape_like(C) -> odbc_queries:escape(C). %%%---------------------------------------------------------------------- diff --git a/src/odbc/mssql.sql b/src/odbc/mssql.sql new file mode 100644 index 000000000..6a8a298d9 --- /dev/null +++ b/src/odbc/mssql.sql @@ -0,0 +1,901 @@ +SET ANSI_NULLS ON +GO +SET QUOTED_IDENTIFIER ON +GO + +exec sp_dboption N'ejabberd', N'autoclose', N'false' +GO + +exec sp_dboption N'ejabberd', N'bulkcopy', N'true' +GO + +exec sp_dboption N'ejabberd', N'trunc. log', N'false' +GO + +exec sp_dboption N'ejabberd', N'torn page detection', N'true' +GO + +exec sp_dboption N'ejabberd', N'read only', N'false' +GO + +exec sp_dboption N'ejabberd', N'dbo use', N'false' +GO + +exec sp_dboption N'ejabberd', N'single', N'false' +GO + +exec sp_dboption N'ejabberd', N'autoshrink', N'false' +GO + +exec sp_dboption N'ejabberd', N'ANSI null default', N'false' +GO + +exec sp_dboption N'ejabberd', N'recursive triggers', N'false' +GO + +exec sp_dboption N'ejabberd', N'ANSI nulls', N'false' +GO + +exec sp_dboption N'ejabberd', N'concat null yields null', N'false' +GO + +exec sp_dboption N'ejabberd', N'cursor close on commit', N'false' +GO + +exec sp_dboption N'ejabberd', N'default to local cursor', N'false' +GO + +exec sp_dboption N'ejabberd', N'quoted identifier', N'false' +GO + +exec sp_dboption N'ejabberd', N'ANSI warnings', N'false' +GO + +exec sp_dboption N'ejabberd', N'auto create statistics', N'true' +GO + +exec sp_dboption N'ejabberd', N'auto update statistics', N'true' +GO + +use [ejabberd] +GO + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[last]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[last] +GO + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rostergroups]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[rostergroups] +GO + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rosterusers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[rosterusers] +GO + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spool]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[spool] +GO + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[users] +GO + +if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) +drop table [dbo].[vcard] +GO + +CREATE TABLE [dbo].[last] ( + [username] [varchar] (250) NOT NULL , + [seconds] [varchar] (50) NOT NULL , + [state] [varchar] (100) NULL , + [Modify_Date] [datetime] NULL +) ON [PRIMARY] +GO + +CREATE TABLE [dbo].[rostergroups] ( + [username] [varchar] (250) NOT NULL , + [jid] [varchar] (250) NOT NULL , + [grp] [varchar] (100) NOT NULL +) ON [PRIMARY] +GO + +CREATE TABLE [dbo].[rosterusers] ( + [username] [varchar] (250) NOT NULL , + [jid] [varchar] (250) NOT NULL , + [nick] [varchar] (50) NULL , + [subscription] [char] (1) NOT NULL , + [ask] [char] (1) NOT NULL , + [askmessage] [varchar] (250) NULL , + [server] [char] (1) NOT NULL , + [subscribe] [varchar] (200) NULL , + [type] [varchar] (50) NULL +) ON [PRIMARY] +GO + +CREATE TABLE [dbo].[spool] ( + [id] [numeric](19, 0) IDENTITY (1, 1) NOT NULL , + [username] [varchar] (250) NOT NULL , + [xml] [text] NULL , + [notifyprocessed] [bit] NULL , + [created] [datetime] NULL , + [MustDelete] [bit] NULL +) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] +GO + +CREATE TABLE [dbo].[users] ( + [username] [varchar] (250) NOT NULL , + [password] [varchar] (50) NOT NULL , + [created] [datetime] NULL +) ON [PRIMARY] +GO + +CREATE TABLE [dbo].[vcard] ( + [username] [varchar] (250) NOT NULL , + [full_name] [varchar] (250) NULL , + [first_name] [varchar] (50) NULL , + [last_name] [varchar] (50) NULL , + [nick_name] [varchar] (50) NULL , + [url] [varchar] (1024) NULL , + [address1] [varchar] (50) NULL , + [address2] [varchar] (50) NULL , + [locality] [varchar] (50) NULL , + [region] [varchar] (50) NULL , + [pcode] [varchar] (50) NULL , + [country] [varchar] (50) NULL , + [telephone] [varchar] (50) NULL , + [email] [varchar] (250) NULL , + [orgname] [varchar] (50) NULL , + [orgunit] [varchar] (50) NULL , + [title] [varchar] (50) NULL , + [role] [varchar] (50) NULL , + [b_day] [datetime] NULL , + [descr] [varchar] (500) NULL +) ON [PRIMARY] +GO + +ALTER TABLE [dbo].[last] WITH NOCHECK ADD + CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED + ( + [username] + ) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + +ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD + CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED + ( + [username], + [jid], + [grp] + ) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + +ALTER TABLE [dbo].[spool] WITH NOCHECK ADD + CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED + ( + [username], + [id] + ) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + +ALTER TABLE [dbo].[users] WITH NOCHECK ADD + CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED + ( + [username] + ) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + +ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD + CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED + ( + [username] + ) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + +ALTER TABLE [dbo].[last] WITH NOCHECK ADD + CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date] +GO + +ALTER TABLE [dbo].[spool] WITH NOCHECK ADD + CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed], + CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created], + CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete] +GO + +ALTER TABLE [dbo].[users] WITH NOCHECK ADD + CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created] +GO + + CREATE INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE INDEX [IX_rosterusers_jid] ON [dbo].[rosterusers]([username], [jid]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE INDEX [IX_spool_process] ON [dbo].[spool]([created], [notifyprocessed]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE INDEX [IK_Spool_Del] ON [dbo].[spool]([MustDelete]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + + CREATE INDEX [IK_Spool_Created] ON [dbo].[spool]([created]) WITH FILLFACTOR = 90 ON [PRIMARY] +GO + +/*********************************************************/ +/** These store procedures are for use with ejabberd **/ +/** 1.1 and Microsoft Sql Server 2000 **/ +/** **/ +/** The stored procedures reduce the need to sql **/ +/** compilation of the database and also allow for also **/ +/** provide each of database integration. The store **/ +/** procedure have been optimized to increase database **/ +/** performance and a reduction of 80% in CPU was **/ +/** achive over the use of standard sql. **/ +/*********************************************************/ + +/****** Object: StoredProcedure [dbo].[add_roster] ******/ +/** Add or update user entries in the roster **/ +/*********************************************************/ +CREATE PROCEDURE [dbo].[add_roster] + @Username varchar(250), + @JID varchar(250), + @Nick varchar(50), + @Subscription char(1), + @Ask char(1), + @AskMessage varchar(250), + @Server char(1), + @Subscribe varchar(200), + @Type varchar(50), + @Grp varchar(100) +AS +BEGIN + BEGIN TRANSACTION + --- Update Roster if user exist else add roster item + IF EXISTS (SELECT username FROM rosterusers WITH (NOLOCK) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID) + BEGIN + UPDATE rosterusers + SET rosterusers.username=@Username, + rosterusers.jid=@JID, + rosterusers.nick=@Nick, + rosterusers.subscription=@Subscription, + rosterusers.ask=@Ask, + rosterusers.askmessage=@AskMessage, + rosterusers.server=@Server, + rosterusers.subscribe=@Subscribe, + rosterusers.type=@Type + WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID); + END + ELSE + BEGIN + INSERT INTO rosterusers + ( rosterusers.username, + rosterusers.jid, + rosterusers.nick, + rosterusers.subscription, + rosterusers.ask, + rosterusers.askmessage, + rosterusers.server, + rosterusers.subscribe, + rosterusers.type + ) + VALUES + ( @Username, + @JID, + @Nick, + @Subscription, + @Ask, + @AskMessage, + @Server, + @Subscribe, + @Type + ); + END + + --- Update Roster Groups if exist else add group entry + IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp) + BEGIN + INSERT INTO rostergroups + ( rostergroups.username, + rostergroups.jid, + rostergroups.grp + ) + VALUES + ( @Username, + @JID, + @Grp + ); + END + + COMMIT +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[add_roster_group] ******/ +/** Add or update user group entries in the roster groups **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[add_roster_group] + @Username varchar(250), + @JID varchar(250), + @Grp varchar(100) +AS +BEGIN + --- Update Roster Groups if exist else add group + IF NOT EXISTS (SELECT username FROM rostergroups WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp) + BEGIN + INSERT INTO rostergroups + ( rostergroups.username, + rostergroups.jid, + rostergroups.grp + ) + VALUES + ( @Username, + @JID, + @Grp + ) + END +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[add_roster_user] ******/ +/** Add or update user entries in the roster **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[add_roster_user] + @Username varchar(250), + @JID varchar(250), + @Nick varchar(50), + @Subscription char(1), + @Ask char(1), + @AskMessage varchar(250), + @Server char(1), + @Subscribe varchar(200), + @Type varchar(50), + @Grp varchar(100) = Null +AS +BEGIN + BEGIN TRANSACTION + --- Update Roster Users if exist of add new user + IF EXISTS (SELECT username FROM rosterusers WHERE rosterusers.username=@Username AND rosterusers.jid=@JID) + BEGIN + UPDATE rosterusers + SET rosterusers.username=@Username, + rosterusers.jid=@JID, + rosterusers.nick=@Nick, + rosterusers.subscription=@Subscription, + rosterusers.ask=@Ask, + rosterusers.askmessage=@AskMessage, + rosterusers.server=@Server, + rosterusers.subscribe=@Subscribe, + rosterusers.type=@Type + WHERE (rosterusers.username=@Username) AND (rosterusers.jid=@JID); + END + ELSE + BEGIN + INSERT INTO rosterusers + ( rosterusers.username, + rosterusers.jid, + rosterusers.nick, + rosterusers.subscription, + rosterusers.ask, + rosterusers.askmessage, + rosterusers.server, + rosterusers.subscribe, + rosterusers.type + ) + VALUES + ( @Username, + @JID, + @Nick, + @Subscription, + @Ask, + @AskMessage, + @Server, + @Subscribe, + @Type + ); + END + + --- Update Roster Group if exist of add new group + IF @Grp IS NOT NULL + EXECUTE [dbo].[add_roster_group] @Username, @JID, @Grp + + COMMIT +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[del_roster_groups] ******/ +/** Remove user group entries from the roster groups table **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[del_roster_groups] + @Username varchar(250), + @JID varchar(250) +AS +BEGIN + DELETE FROM rostergroups + WITH (ROWLOCK) + WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID); +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[add_spool] ******/ +/** Add a entry to the spool table **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[add_spool] + @Username varchar(250), + @XML varchar(8000) +AS +BEGIN + INSERT INTO spool + ( spool.username, + spool.xml + ) + VALUES + ( @Username, + @XML + ) +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[add_user] ******/ +/** Add or update user entries to jabber **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[add_user] + @Username varchar(200), + @Password varchar(50) +AS +BEGIN + INSERT INTO users + ( username, + [password] + ) + VALUES + ( @Username, + @Password + ); +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[set_password] **/ +/** Update users password **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[set_password] + @Username varchar(200), + @Password varchar(50) +AS +BEGIN + IF EXISTS (SELECT username FROM users WITH (NOLOCK) WHERE username=@Username) + BEGIN + UPDATE users SET username=@Username, password=@Password WHERE username=@Username; + END + ELSE + BEGIN + INSERT INTO users (username, password) VALUES (@Username, @Password); + END +END +GO +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_password] **/ +/** Retrive the user password **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_password] + @Username varchar(200) +AS +BEGIN + SELECT users.password as password + FROM users WITH (NOLOCK) + WHERE username=@Username; +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[clean_spool_msg] ******/ +/** Delete messages older that 3 days from spool **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[clean_spool_msg] +AS +DECLARE + @dt datetime, + @myRowCount int +BEGIN + -- Delete small amounts because if locks the database table + SET ROWCOUNT 500 + SET @myRowCount = 1 + + WHILE (@myRowCount) > 0 + BEGIN + BEGIN TRANSACTION + SELECT @dt = DATEADD(d, -3, GETDATE()) + DELETE FROM spool + WITH (ROWLOCK) + WHERE (MustDelete=1) OR (Created < @dt); + + SET @myRowCount = @@RowCount + COMMIT + END +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[del_last] ******/ +/** Delete an entry from the last table **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[del_last] + @Username varchar(250) +AS +BEGIN + DELETE FROM [last] + WITH (ROWLOCK) + WHERE [last].username=@Username; +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[del_roster] ******/ +/** Delete an entry from the roster **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[del_roster] + @Username varchar(250), + @JID varchar(250) +AS +BEGIN + BEGIN TRANSACTION + DELETE FROM rosterusers + WITH (ROWLOCK) + WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID); + + DELETE FROM rostergroups + WITH (ROWLOCK) + WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID); + + COMMIT +END +GO + + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[del_spool_msg] ******/ +/** Delete an entry from the spool table **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[del_spool_msg] + @Username varchar(250) +AS +BEGIN + DELETE FROM spool + WITH (ROWLOCK) + WHERE spool.username=@Username; +END +GO + +/***************************************************************/ +/****** Object: StoredProcedure [dbo].[del_user] ******/ +/** Delete an entry from the user table **/ +/***************************************************************/ +CREATE PROCEDURE [dbo].[del_user] + @Username varchar(200) +AS +BEGIN + DELETE FROM users + WITH (ROWLOCK) + WHERE username=@Username; +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[del_user_return_password]**/ +/** Delete an entry from the user table and return user password **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[del_user_return_password] + @Username varchar(250) +AS +DECLARE + @Pwd varchar(50) +BEGIN + EXECUTE @Pwd = dbo.get_password @Username + DELETE FROM users + WITH (ROWLOCK) + WHERE username=@Username + + SELECT @Pwd; +END +GO + + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[del_user_roster] **/ +/** Delete the users roster **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[del_user_roster] + @Username varchar(250) +AS +BEGIN + BEGIN TRANSACTION + DELETE FROM rosterusers + WITH (ROWLOCK) + WHERE rosterusers.username = @Username; + + DELETE FROM rostergroups + WITH (ROWLOCK) + WHERE rostergroups.username = @Username; + + COMMIT +END +GO + + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_and_del_spool_msg] **/ +/** Fetch and delete the users offline messages **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_and_del_spool_msg] + @Username varchar(250) +AS +DECLARE + @vSpool table( username varchar(1), + xml varchar(1)) +BEGIN + IF EXISTS (SELECT username FROM spool with (nolock) WHERE spool.username=@Username) + BEGIN + SELECT spool.username AS username, + spool.xml AS xml + FROM spool WITH (NOLOCK) + WHERE spool.username=@Username; + + DELETE spool + WITH (ROWLOCK) + WHERE spool.username=@Username + END + ELSE + BEGIN + SELECT * FROM @vSpool; + END +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_last] **/ +/** Retrive the last user login **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_last] + @Username varchar(250) +AS +BEGIN + SELECT last.seconds AS seconds, + last.state AS state + FROM last WITH (NOLOCK) + WHERE last.username=@Username; +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_roster] **/ +/** Retrive the user roster **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_roster] + @Username varchar(200) +AS +DECLARE + @vRosterusers table( username varchar(1), + jid varchar(1), + nick varchar(1), + subscription varchar(1), + ask varchar(1), + askmessage varchar(1), + server varchar(1), + subscribe varchar(1), + type varchar(1)) +BEGIN + IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username) + BEGIN + SELECT rosterusers.username AS username, + rosterusers.jid AS jid, + rosterusers.nick AS nick, + rosterusers.subscription AS subscription, + rosterusers.ask AS ask, + rosterusers.askmessage AS askmessage, + rosterusers.server AS server, + rosterusers.subscribe AS subscribe, + rosterusers.type AS type + FROM rosterusers WITH (NOLOCK) + WHERE rosterusers.username = @Username; + END + ELSE + BEGIN + SELECT * FROM @vRosterusers + END +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_roster_by_jid] **/ +/** Retrive the user roster via JID **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_roster_by_jid] + @Username varchar(200), + @JID varchar(250) +AS +DECLARE + @vRosterusers table( username varchar(1), + jid varchar(1), + nick varchar(1), + subscription varchar(1), + ask varchar(1), + askmessage varchar(1), + server varchar(1), + subscribe varchar(1), + type varchar(1)) +BEGIN + IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID)) + BEGIN + SELECT rosterusers.username AS username, + rosterusers.jid AS jid, + rosterusers.nick AS nick, + rosterusers.subscription AS subscription, + rosterusers.ask AS ask, + rosterusers.askmessage AS askmessage, + rosterusers.server AS server, + rosterusers.subscribe AS subscribe, + rosterusers.type AS type + FROM rosterusers WITH (NOLOCK) + WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID); + END + ELSE + BEGIN + SELECT * FROM @vRosterusers + END +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_roster_jid_groups] **/ +/** Retrieve the user roster groups **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_roster_jid_groups] + @Username varchar(200) +AS +DECLARE + @vrostergroups table( jid varchar(1), + grp varchar(1)) +BEGIN + IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username) + BEGIN + SELECT rostergroups.jid AS jid, + rostergroups.grp AS grp + FROM rostergroups WITH (NOLOCK) + WHERE rostergroups.username = @Username; + END + ELSE + BEGIN + SELECT * FROM @vrostergroups + END +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_roster_groups] **/ +/** Retrive the user roster groups **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_roster_groups] + @Username varchar(200), + @JID varchar(250) +AS +DECLARE + @vrostergroups table( grp varchar(1)) +BEGIN + IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username) + BEGIN + SELECT rostergroups.grp AS grp + FROM rostergroups WITH (NOLOCK) + WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID); + END + ELSE + BEGIN + SELECT * FROM @vrostergroups + END +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_rostergroup_by_jid] **/ +/** Retrive the user roster groups via JID **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_rostergroup_by_jid] + @Username varchar(250), + @JID varchar(250) +AS +DECLARE + @vrostergroups table(grp varchar(1)) +BEGIN + IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID) + BEGIN + SELECT rostergroups.grp AS grp + FROM rostergroups WITH (NOLOCK) + WHERE rostergroups.username=@Username AND rostergroups.jid=@JID; + END + ELSE + BEGIN + SELECT * FROM @vrostergroups + END +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[get_subscription] **/ +/** Retrive the user subscription requests **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[get_subscription] + @Username varchar(250), + @JID varchar(250) +AS +DECLARE + @vrosterusers table( subscription varchar(1)) +BEGIN + IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID) + BEGIN + SELECT rosterusers.subscription AS subscription + FROM rosterusers WITH (NOLOCK) + WHERE rosterusers.username=@Username AND rosterusers.jid=@JID; + END + ELSE + BEGIN + SELECT * FROM @vrosterusers + END +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[list_users] **/ +/** Retrive a list of all users **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[list_users] +AS +BEGIN + SELECT users.username AS username FROM users WITH (NOLOCK); +END +GO + +/******************************************************************/ +/****** Object: StoredProcedure [dbo].[set_last] **/ +/** Update users last login status **/ +/******************************************************************/ +CREATE PROCEDURE [dbo].[set_last] + @Username varchar(250), + @Seconds varchar(50), + @State varchar(100) +AS +BEGIN + IF EXISTS (SELECT username FROM [last] WITH (NOLOCK) WHERE username=@Username) + BEGIN + UPDATE [last] + SET [last].username = @Username, + [last].seconds = @Seconds, + [last].state = @State + WHERE last.username=@Username; + END + ELSE + BEGIN + INSERT INTO [last] + ( [last].username, + [last].seconds, + [last].state + ) + VALUES + ( @Username, + @Seconds, + @State + ) + END +END +GO diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl new file mode 100644 index 000000000..7732e62a7 --- /dev/null +++ b/src/odbc/odbc_queries.erl @@ -0,0 +1,434 @@ +%% Copyrigh 2006, Process-one +%% This module is intended to take into account relational databases behaviour +%% differences +-module(odbc_queries). +-author("mickael.remond@process-one.net"). + +-export([get_db_type/0, + sql_transaction/2, + get_last/2, + set_last_t/4, + del_last/2, + get_password/2, + set_password_t/3, + add_user/3, + del_user/2, + del_user_return_password/3, + list_users/1, + add_spool_sql/2, + add_spool/2, + get_and_del_spool_msg_t/2, + del_spool_msg/2, + get_roster/2, + get_roster_jid_groups/2, + get_roster_groups/3, + del_user_roster_t/2, + get_roster_by_jid/3, + get_rostergroup_by_jid/3, + del_roster/3, + del_roster_sql/2, + update_roster/5, + update_roster_sql/4, + roster_subscribe/4, + get_subscription/3, + escape/1]). + +%-define(generic, true). +%-define(mssql, true). + +%% ----------------- +%% Generic queries +-ifdef(generic). + +get_db_type() -> + generic. + +sql_transaction(LServer, F) -> + ejabberd_odbc:sql_transaction(LServer, F). + +get_last(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["select seconds, state from last " + "where username='", Username, "'"]). + +set_last_t(LServer, Username, Seconds, State) -> + %% MREMOND: I think this should be turn into a non transactional behaviour + ejabberd_odbc:sql_transaction( + LServer, + [["delete from last where username='", Username, "';"], + ["insert into last(username, seconds, state) " + "values ('", Username, "', '", Seconds, "', '", State, "');"]]). + +del_last(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["delete from last where username='", Username, "'"]). + +get_password(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["select password from users " + "where username='", Username, "';"]). + +set_password_t(LServer, Username, Pass) -> + ejabberd_odbc:sql_transaction( + LServer, + [["delete from users where username='", Username ,"';"], + ["insert into users(username, password) " + "values ('", Username, "', '", Pass, "');"]]). + +add_user(LServer, Username, Pass) -> + ejabberd_odbc:sql_query( + LServer, + ["insert into users(username, password) " + "values ('", Username, "', '", Pass, "');"]). + +del_user(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["delete from users where username='", Username ,"';"]). + +del_user_return_password(_LServer, Username, Pass) -> + ejabberd_odbc:sql_query_t( + ["select password from users where username='", + Username, "';"]), + ejabberd_odbc:sql_query_t(["delete from users " + "where username='", Username, + "' and password='", Pass, "';"]). + +list_users(LServer) -> + ejabberd_odbc:sql_query( + LServer, + "select username from users"). + +add_spool_sql(Username, XML) -> + ["insert into spool(username, xml) " + "values ('", Username, "', '", + XML, + "');"]. + +add_spool(LServer, Queries) -> + ejabberd_odbc:sql_transaction( + LServer, Queries). + +get_and_del_spool_msg_t(LServer, Username) -> + F = fun() -> + Result = ejabberd_odbc:sql_query_t( + ["select username, xml from spool where username='", Username, "'" + " order by seq;"]), + ejabberd_odbc:sql_query_t( + ["delete from spool where username='", Username, "';"]), + Result + end, + ejabberd_odbc:sql_transaction(LServer,F). + +del_spool_msg(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["delete from spool where username='", Username, "';"]). + +get_roster(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["select username, jid, nick, subscription, ask, " + "askmessage, server, subscribe, type from rosterusers " + "where username='", Username, "'"]). + +get_roster_jid_groups(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["select jid, grp from rostergroups " + "where username='", Username, "'"]). + +get_roster_groups(_LServer, Username, SJID) -> + ejabberd_odbc:sql_query_t( + ["select grp from rostergroups " + "where username='", Username, "' " + "and jid='", SJID, "';"]). + +del_user_roster_t(LServer, Username) -> + ejabberd_odbc:sql_transaction( + LServer, + fun() -> + ejabberd_odbc:sql_query_t( + ["delete from rosterusers " + " where username='", Username, "';"]), + ejabberd_odbc:sql_query_t( + ["delete from rostergroups " + " where username='", Username, "';"]) + end). + +get_roster_by_jid(_LServer, Username, SJID) -> + ejabberd_odbc:sql_query_t( + ["select username, jid, nick, subscription, " + "ask, askmessage, server, subscribe, type from rosterusers " + "where username='", Username, "' " + "and jid='", SJID, "';"]). + +get_rostergroup_by_jid(LServer, Username, SJID) -> + ejabberd_odbc:sql_query( + LServer, + ["select grp from rostergroups " + "where username='", Username, "' " + "and jid='", SJID, "'"]). + +del_roster(_LServer, Username, SJID) -> + ejabberd_odbc:sql_query_t( + ["delete from rosterusers " + " where username='", Username, "' " + " and jid='", SJID, "';"]), + ejabberd_odbc:sql_query_t( + ["delete from rostergroups " + " where username='", Username, "' " + " and jid='", SJID, "';"]). + +del_roster_sql(Username, SJID) -> + [["delete from rosterusers " + " where username='", Username, "' " + " and jid='", SJID, "';"], + ["delete from rostergroups " + " where username='", Username, "' " + " and jid='", SJID, "';"]]. + +update_roster(_LServer, Username, SJID, ItemVals, ItemGroups) -> + ejabberd_odbc:sql_query_t( + ["delete from rosterusers " + " where username='", Username, "' " + " and jid='", SJID, "';"]), + ejabberd_odbc:sql_query_t( + ["insert into rosterusers(" + " username, jid, nick, " + " subscription, ask, askmessage, " + " server, subscribe, type) " + " values (", ItemVals, ");"]), + ejabberd_odbc:sql_query_t( + ["delete from rostergroups " + " where username='", Username, "' " + " and jid='", SJID, "';"]), + lists:foreach(fun(ItemGroup) -> + ejabberd_odbc:sql_query_t( + ["insert into rostergroups(" + " username, jid, grp) " + " values (", ItemGroup, ");"]) + end, + ItemGroups). + +update_roster_sql(Username, SJID, ItemVals, ItemGroups) -> + [["delete from rosterusers " + " where username='", Username, "' " + " and jid='", SJID, "';"], + ["insert into rosterusers(" + " username, jid, nick, " + " subscription, ask, askmessage" + " server, subscribe, type) " + " values (", ItemVals, ");"], + ["delete from rostergroups " + " where username='", Username, "' " + " and jid='", SJID, "';"], + [["insert into rostergroups(" + " username, jid, grp) " + " values (", ItemGroup, ");"] || + ItemGroup <- ItemGroups]]. + +roster_subscribe(_LServer, Username, SJID, ItemVals) -> + ejabberd_odbc:sql_query_t( + ["delete from rosterusers " + " where username='", Username, "' " + " and jid='", SJID, "';"]), + ejabberd_odbc:sql_query_t( + ["insert into rosterusers(" + " username, jid, nick, " + " subscription, ask, askmessage, " + " server, subscribe, type) " + " values (", ItemVals, ");"]). + +get_subscription(LServer, Username, SJID) -> + ejabberd_odbc:sql_query( + LServer, + ["select subscription from rosterusers " + "where username='", Username, "' " + "and jid='", SJID, "'"]). + +%% Characters to escape +escape($\0) -> "\\0"; +escape($\n) -> "\\n"; +escape($\t) -> "\\t"; +escape($\b) -> "\\b"; +escape($\r) -> "\\r"; +escape($') -> "\\'"; +escape($") -> "\\\""; +escape($\\) -> "\\\\"; +escape(C) -> C. + +-endif. + +%% ----------------- +%% MSSQL queries +-ifdef(mssql). + +get_db_type() -> + mssql. + +sql_transaction(_LServer, F) -> + {atomic, catch F()}. + +get_last(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_last '", Username, "'"]). + +set_last_t(LServer, Username, Seconds, State) -> + Result = ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.set_last '", Username, "', '", Seconds, + "', '", State, "'"]), + {atomic, Result}. + +del_last(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.del_last '", Username, "'"]). + +get_password(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_password '", Username, "'"]). + +set_password_t(LServer, Username, Pass) -> + Result = ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.set_password '", Username, "', '", Pass, "'"]), + {atomic, Result}. + +add_user(LServer, Username, Pass) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.add_user '", Username, "', '", Pass, "'"]). + +del_user(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.del_user '", Username ,"'"]). + +del_user_return_password(LServer, Username, Pass) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.del_user_return_password '", Username, "'"]), + Pass. + +list_users(LServer) -> + ejabberd_odbc:sql_query( + LServer, + "EXECUTE dbo.list_users"). + +add_spool_sql(Username, XML) -> + ["EXECUTE dbo.add_spool '", Username, "' , '",XML,"'"]. + +add_spool(LServer, Queries) -> + lists:foreach(fun(Query) -> + ejabberd_odbc:sql_query(LServer, Query) + end, + Queries). + +get_and_del_spool_msg_t(LServer, Username) -> + [Result] = case ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_and_del_spool_msg '", Username, "'"]) of + Rs when list(Rs) -> + lists:filter(fun({selected, _Header, _Row}) -> + true; + ({updated, _N}) -> + false + end, + Rs); + Rs -> [Rs] + end, + {atomic, Result}. + +del_spool_msg(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.del_spool_msg '", Username, "'"]). + +get_roster(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_roster '", Username, "'"]). + +get_roster_jid_groups(LServer, Username) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_roster_jid_groups '", Username, "'"]). + +get_roster_groups(LServer, Username, SJID) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_roster_groups '", Username, "' , '", SJID, "'"]). + +del_user_roster_t(LServer, Username) -> + Result = ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.del_user_roster '", Username, "'"]), + {atomic, Result}. + +get_roster_by_jid(LServer, Username, SJID) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_roster_by_jid '", Username, "' , '", SJID, "'"]). + +get_rostergroup_by_jid(LServer, Username, SJID) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_rostergroup_by_jid '", Username, "' , '", SJID, "'"]). + +del_roster(LServer, Username, SJID) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.del_roster '", Username, "', '", SJID, "'"]). + +del_roster_sql(Username, SJID) -> + ["EXECUTE dbo.del_roster '", Username, "', '", SJID, "'"]. + +update_roster(LServer, Username, SJID, ItemVals, ItemGroups) -> + Query1 = ["EXECUTE dbo.del_roster '", Username, "', '", SJID, "' "], + ejabberd_odbc:sql_query(LServer, lists:flatten(Query1)), + Query2 = ["EXECUTE dbo.add_roster_user ", ItemVals], + ejabberd_odbc:sql_query(LServer, lists:flatten(Query2)), + Query3 = ["EXECUTE dbo.del_roster_groups '", Username, "', '", SJID, "' "], + ejabberd_odbc:sql_query(LServer, lists:flatten(Query3)), + lists:foreach(fun(ItemGroup) -> + Query = ["EXECUTE dbo.add_roster_group ", + ItemGroup], + ejabberd_odbc:sql_query(LServer, + lists:flatten(Query)) + end, + ItemGroups). + +update_roster_sql(Username, SJID, ItemVals, ItemGroups) -> + ["BEGIN TRANSACTION ", + "EXECUTE dbo.del_roster_groups '", Username, "','", SJID, "' ", + "EXECUTE dbo.add_roster_user ", ItemVals, " "] ++ + [lists:flatten("EXECUTE dbo.add_roster_group ", ItemGroup, " ") + || ItemGroup <- ItemGroups] ++ + ["COMMIT"]. + +roster_subscribe(LServer, _Username, _SJID, ItemVals) -> + catch ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.add_roster_user ", ItemVals]). + +get_subscription(LServer, Username, SJID) -> + ejabberd_odbc:sql_query( + LServer, + ["EXECUTE dbo.get_subscription '", Username, "' , '", SJID, "'"]). + +%% Characters to escape +escape($\0) -> "\\0"; +escape($\t) -> "\\t"; +escape($\b) -> "\\b"; +escape($\r) -> "\\r"; +escape($') -> "\''"; +escape($") -> "\\\""; +escape(C) -> C. +-endif. diff --git a/src/odbc/pg.sql b/src/odbc/pg.sql index a7059d503..32f20fdd0 100644 --- a/src/odbc/pg.sql +++ b/src/odbc/pg.sql @@ -18,7 +18,7 @@ CREATE TABLE rosterusers ( nick text, subscription character(1) NOT NULL, ask character(1) NOT NULL, - askmessage text NOT NULL, + askmessage text, server character(1) NOT NULL, subscribe text, "type" text -- cgit v1.2.3