* src/mod_private_odbc.erl: Support for MySQL and MSSQL.

* src/odbc/odbc_queries.erl: Likewise.
* src/odbc/mysql.sql: Likewise.
* src/odbc/mssql.sql: Likewise.

SVN Revision: 674

5 files changed, 155 insertions, 24 deletions

diff --git a/ChangeLog b/ChangeLog
index e6fe10bfc..c823aad78 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,10 @@
+2006-11-05  Mickael Remond  <mickael.remond@process-one.net>
+
+	* src/mod_private_odbc.erl: Support for MySQL and MSSQL.
+	* src/odbc/odbc_queries.erl: Likewise.
+	* src/odbc/mysql.sql: Likewise.
+	* src/odbc/mssql.sql: Likewise.
+
 2006-11-04  Mickael Remond  <mickael.remond@process-one.net>
 
 	* src/eldap_utils.erl: Fixed missing export.
diff --git a/src/mod_private_odbc.erl b/src/mod_private_odbc.erl
index d6f5805a4..45e07233a 100644
--- a/src/mod_private_odbc.erl
+++ b/src/mod_private_odbc.erl
@@ -46,7 +46,7 @@ process_sm_iq(From, _To, #iq{type = Type, sub_el = SubEl} = IQ) ->
 					  set_data(LUser, LServer, El)
 				  end, Els)
 			end,
-		    ejabberd_odbc:sql_transaction(LServer, F),
+		    odbc_queries:sql_transaction(LServer, F),
 		    IQ#iq{type = result,
 			  sub_el = [{xmlelement, Name, Attrs, []}]};
 		get ->
@@ -64,7 +64,7 @@ process_sm_iq(From, _To, #iq{type = Type, sub_el = SubEl} = IQ) ->
 	    IQ#iq{type = error, sub_el = [SubEl, ?ERR_NOT_ALLOWED]}
     end.
 
-set_data(LUser, _LServer, El) ->
+set_data(LUser, LServer, El) ->
     case El of
 	{xmlelement, _Name, Attrs, _Els} ->
 	    XMLNS = xml:get_attr_s("xmlns", Attrs),
@@ -76,14 +76,7 @@ set_data(LUser, _LServer, El) ->
 		    LXMLNS = ejabberd_odbc:escape(XMLNS),
 		    SData = ejabberd_odbc:escape(
 			       lists:flatten(xml:element_to_string(El))),
-		    ejabberd_odbc:sql_query_t(
-		      ["delete from private_storage "
-		       "where username='", Username, "' and "
-		       "namespace='", LXMLNS, "';"]),
-		    ejabberd_odbc:sql_query_t(
-		      ["insert into private_storage(username, namespace, data) "
-		       "values ('", Username, "', '", LXMLNS, "', "
-		       "'", SData, "');"])
+			odbc_queries:set_private_data(LServer, Username, LXMLNS, SData)
 	    end;
 	_ ->
 	    ignore
@@ -100,17 +93,14 @@ get_data(LUser, LServer, [El | Els], Res) ->
 	    XMLNS = xml:get_attr_s("xmlns", Attrs),
 	    Username = ejabberd_odbc:escape(LUser),
 	    LXMLNS = ejabberd_odbc:escape(XMLNS),
-	    case catch ejabberd_odbc:sql_query(
-			 LServer,
-			 ["select data from private_storage "
-			  "where username='", Username, "' and "
-			  "namespace='", LXMLNS, "';"]) of
+	    case catch odbc_queries:get_private_data(LServer, Username, LXMLNS) of
 		{selected, ["data"], [{SData}]} ->
 		    case xml_stream:parse_element(SData) of
 			Data when element(1, Data) == xmlelement ->
 			    get_data(LUser, LServer, Els,
 				     [Data | Res])
 		    end;
+		%% MREMOND: I wonder when the query could return a vcard ?
 		{selected, ["vcard"], []} ->
 		    get_data(LUser, LServer, Els,
 			     [El | Res])
@@ -124,8 +114,4 @@ remove_user(User, Server) ->
     LUser = jlib:nodeprep(User),
     LServer = jlib:nameprep(Server),
     Username = ejabberd_odbc:escape(LUser),
-    ejabberd_odbc:sql_transaction(
-      LServer,
-      ["delete from private_storage where username='", Username, "';"]).
-
-
+    odbc_queries:del_user_private_storage(LServer, Username).
diff --git a/src/odbc/mssql.sql b/src/odbc/mssql.sql
index c92fa776c..00ad9db45 100644
--- a/src/odbc/mssql.sql
+++ b/src/odbc/mssql.sql
@@ -84,6 +84,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].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

+drop table [dbo].[private_storage]

+GO

+

 CREATE TABLE [dbo].[last] (

 	[username] [varchar] (250) NOT NULL ,

 	[seconds] [varchar] (50) NOT NULL ,

@@ -151,6 +155,13 @@ CREATE TABLE [dbo].[vcard] (
 ) ON [PRIMARY]

 GO

 

+CREATE TABLE [dbo].[private_storage] (

+    [username] [varchar] (250) NOT NULL ,

+    [namespace] [varchar] (250) NOT NULL ,

+    [data] [text] NOT NULL

+) ON [PRIMARY]

+GO

+

 ALTER TABLE [dbo].[last] WITH NOCHECK ADD 

 	CONSTRAINT [PK_last] PRIMARY KEY  CLUSTERED 

 	(

@@ -227,6 +238,12 @@ GO
  CREATE  INDEX [IK_Spool_Created] ON [dbo].[spool]([created]) WITH  FILLFACTOR = 90 ON [PRIMARY]

 GO

 

+ CREATE  INDEX [IX_private_user] ON [dbo].[private_storage]([username]) WITH  FILLFACTOR = 90 ON [PRIMARY]

+GO

+ CREATE  INDEX [IX_private_user_ns] ON [dbo].[private_storage]([username], [namespace]) WITH  FILLFACTOR = 90 ON [PRIMARY]

+GO

+

+

 /*********************************************************/

 /** These store procedures are for use with ejabberd    **/

 /** 1.1 and Microsoft Sql Server 2000                   **/

@@ -484,6 +501,7 @@ BEGIN
     END

 END

 GO

+

 /******************************************************************/

 /****** Object:  StoredProcedure [dbo].[get_password]            **/

 /** Retrive the user password                                    **/

@@ -557,7 +575,6 @@ BEGIN
     DELETE FROM rostergroups 

     WITH (ROWLOCK)

     WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID);

-

   COMMIT

 END

 GO

@@ -627,7 +644,6 @@ BEGIN
     DELETE FROM rostergroups 

     WITH (ROWLOCK)

     WHERE rostergroups.username = @Username;

-

   COMMIT

 END

 GO

@@ -682,7 +698,7 @@ GO
 /** Retrive the user roster                                      **/

 /******************************************************************/

 CREATE PROCEDURE [dbo].[get_roster]

-  @Username varchar(200)

+  @Username varchar(250)

 AS

 DECLARE

   @vRosterusers table( username      varchar(1), 

@@ -855,7 +871,7 @@ GO
 

 /******************************************************************/

 /****** Object:  StoredProcedure [dbo].[list_users]              **/

-/** Retrive a list of all users                                  **/

+/** Retrieve a list of all users                                 **/

 /******************************************************************/

 CREATE PROCEDURE [dbo].[list_users]

 AS

@@ -897,3 +913,76 @@ BEGIN
     END

 END

 GO

+

+/******************************************************************/

+/****** Object:  StoredProcedure [dbo].[set_private_data]        **/

+/** store user private data by namespace                         **/

+/******************************************************************/

+CREATE PROCEDURE [dbo].[set_private_data]

+    @Username  varchar(250),

+    @Namespace varchar(250),

+    @Data varchar(8000)

+AS

+BEGIN

+  IF EXISTS (SELECT username FROM private_storage with (nolock) WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace)

+    BEGIN

+        UPDATE [private_storage]

+        SET [private_storage].username = @Username,

+            [private_storage].namespace = @Namespace,

+            [private_storage].data = @Data

+            WHERE private_storage.username = @Username AND private_storage.namespace = @Namespace;

+    END

+  ELSE

+    BEGIN

+        INSERT INTO [private_storage]

+            ( [private_storage].username,

+              [private_storage].namespace,

+              [private_storage].data

+            )

+        VALUES

+            ( @Username,

+              @Namespace,

+              @Data

+            )

+    END

+END

+GO

+

+ejabberd_odbc:sql_query_t(

+  ["delete from private_storage "

+ "where username='", Username, "' and "

+         "namespace='", LXMLNS, "';"]),

+      ejabberd_odbc:sql_query_t(

+        ["insert into private_storage(username, namespace, data) "

+         "values ('", Username, "', '", LXMLNS, "', "

+         "'", SData, "');"]).

+

+/******************************************************************/

+/****** Object:  StoredProcedure [dbo].[get_private_data]        **/

+/** Retrieve user private data by namespace                      **/

+/******************************************************************/

+CREATE PROCEDURE [dbo].[get_private_data]

+    @Username  varchar(250),

+    @Namespace varchar(250)

+AS

+BEGIN

+  SELECT private_storage.data AS data

+  FROM private_storage WITH (NOLOCK)

+  WHERE username=@Username and namespace=@Namespace;

+END

+GO

+

+/***************************************************************/

+/****** Object:  StoredProcedure [dbo].[del_user_storage] ******/

+/** Delete private storage area for a given user              **/

+/***************************************************************/

+CREATE PROCEDURE [dbo].[del_user_storage]

+  @Username  varchar(250)

+AS

+BEGIN

+  DELETE FROM [private_storage]

+  WITH (ROWLOCK)

+  WHERE [private_storage].username=@Username;

+END

+GO

+

diff --git a/src/odbc/mysql.sql b/src/odbc/mysql.sql
index 2394f9901..607a7407f 100644
--- a/src/odbc/mysql.sql
+++ b/src/odbc/mysql.sql
@@ -93,6 +93,15 @@ CREATE INDEX i_vcard_search_lemail    ON vcard_search(lemail);
 CREATE INDEX i_vcard_search_lorgname  ON vcard_search(lorgname);
 CREATE INDEX i_vcard_search_lorgunit  ON vcard_search(lorgunit);
 
+CREATE TABLE private_storage (
+    username varchar(250) NOT NULL,
+    namespace varchar(250) NOT NULL,
+    data text NOT NULL
+) CHARACTER SET utf8;
+
+CREATE INDEX i_private_storage_username USING BTREE ON private_storage(username);
+CREATE UNIQUE INDEX i_private_storage_username_namespace USING BTREE ON private_storage(username, namespace);
+
 --- To update from 1.x:
 -- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask;
 -- UPDATE rosterusers SET askmessage = '';
diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl
index 7732e62a7..d91a007de 100644
--- a/src/odbc/odbc_queries.erl
+++ b/src/odbc/odbc_queries.erl
@@ -31,6 +31,9 @@
 	 update_roster_sql/4,
 	 roster_subscribe/4,
 	 get_subscription/3,
+	 set_private_data/4,
+	 get_private_data/3,
+	 del_user_private_storage/2,
 	 escape/1]).
 
 %-define(generic, true).
@@ -250,6 +253,28 @@ get_subscription(LServer, Username, SJID) ->
        "where username='", Username, "' "
        "and jid='", SJID, "'"]).
 
+set_private_data(_LServer, Username, LXMLNS, SData) ->
+    ejabberd_odbc:sql_query_t(
+      ["delete from private_storage "
+       "where username='", Username, "' and "
+       "namespace='", LXMLNS, "';"]),
+    ejabberd_odbc:sql_query_t(
+      ["insert into private_storage(username, namespace, data) "
+       "values ('", Username, "', '", LXMLNS, "', "
+       "'", SData, "');"]).
+       			       
+get_private_data(LServer, Username, LXMLNS) ->
+    ejabberd_odbc:sql_query(
+		 LServer,
+		 ["select data from private_storage "
+		  "where username='", Username, "' and "
+		  "namespace='", LXMLNS, "';"]).
+
+del_user_private_storage(LServer, Username) ->
+    ejabberd_odbc:sql_transaction(
+      LServer,
+      ["delete from private_storage where username='", Username, "';"]).
+
 %% Characters to escape
 escape($\0) -> "\\0";
 escape($\n) -> "\\n";
@@ -422,6 +447,21 @@ get_subscription(LServer, Username, SJID) ->
     ejabberd_odbc:sql_query(
       LServer,
       ["EXECUTE dbo.get_subscription '", Username, "' , '", SJID, "'"]).
+      
+set_private_data(_LServer, Username, LXMLNS, SData) ->
+    ejabberd_odbc:sql_query(
+	    LServer,
+	    ["EXECUTE dbo.set_private_data '", Username, "' , '", LXMLNS, "' , '", SData, "'"]).
+	    
+get_private_data(LServer, Username, LXMLNS) ->
+    ejabberd_odbc:sql_query(
+        LServer,
+        ["EXECUTE dbo.get_private_data '", Username, "' , '", LXMLNS, "'"]).
+
+del_user_private_storage(LServer, Username) ->
+    ejabberd_odbc:sql_query(
+        LServer,
+        ["EXECUTE dbo.del_user_storage '", Username, "'"]).
 
 %% Characters to escape
 escape($\0) -> "\\0";