2006-11-20 Mickael Remond <mickael.remond@process-one.net>

* src/ejd2odbc.erl: Copy mod_private data from Mnesia to
relational database.  
* src/odbc/odbc_queries.erl: Refactoring.  
* src/odbc/mssql.sql: Likewise.
* doc/guide.tex: Update to mention mod_private_odbc.

* src/odbc/mysql.sql: Started integration of mod_privacy_odbc for
MySQL and MSSQL.
* src/odbc/mssql.sql: Likewise.

SVN Revision: 678

4 files changed, 136 insertions, 27 deletions

diff --git a/src/ejd2odbc.erl b/src/ejd2odbc.erl
index 8a7f6ada0..6dfc3a406 100644
--- a/src/ejd2odbc.erl
+++ b/src/ejd2odbc.erl
@@ -16,7 +16,8 @@
 	 export_offline/2,
 	 export_last/2,
 	 export_vcard/2,
-	 export_vcard_search/2]).
+	 export_vcard_search/2,
+	 export_private_storage/2]).
 
 -include("ejabberd.hrl").
 -include("jlib.hrl").
@@ -39,17 +40,24 @@
 		       orgname,	 lorgname,
 		       orgunit,	 lorgunit
 		      }).
+-record(private_storage, {usns, xml}).
 
 -define(MAX_RECORDS_PER_TRANSACTION, 1000).
 
 %%%----------------------------------------------------------------------
 %%% API
 %%%----------------------------------------------------------------------
+%%% How to use:
+%%% A table can be converted from Mnesia to an ODBC database by calling
+%%% one of the API function with the following parameters:
+%%% - Server is the server domain you want to convert
+%%% - Output can be either odbc to export to the configured relational
+%%%   database or "Filename" to export to text file.
 
 export_passwd(Server, Output) ->
     export_common(
       Server, passwd, Output,
-      fun(Host, {passwd, {LUser, LServer}, Password} = R)
+      fun(Host, {passwd, {LUser, LServer}, Password} = _R)
 	 when LServer == Host ->
 	      Username = ejabberd_odbc:escape(LUser),
 	      Pass = ejabberd_odbc:escape(Password),
@@ -219,6 +227,20 @@ export_vcard_search(Server, Output) ->
 	      []
       end).
 
+export_private_storage(Server, Output) ->
+    export_common(
+        Server, private_storage, Output,
+        fun(Host, #private_storage{usns = {LUser, LServer, XMLNS},
+      			       xml = Data})
+      	  when LServer == Host ->
+      	      Username = ejabberd_odbc:escape(LUser),
+      	      LXMLNS = ejabberd_odbc:escape(XMLNS),
+      	      SData = ejabberd_odbc:escape(Data),
+      	      odbc_queries:set_private_data_sql(Username, LXMLNS, SData);
+      	   (_Host, _R) ->
+      	      []
+    end).
+
 %%%----------------------------------------------------------------------
 %%% Internal functions
 %%%----------------------------------------------------------------------
@@ -246,6 +268,7 @@ export_common(Server, Table, Output, ConvertFun) ->
 					N < ?MAX_RECORDS_PER_TRANSACTION - 1 ->
 					    {N + 1, [SQL | SQLs]};
 					true ->
+					    %% Execute full SQL transaction
 					    output(LServer, IO,
 						   ["begin;",
 						    lists:reverse([SQL | SQLs]),
@@ -254,6 +277,7 @@ export_common(Server, Table, Output, ConvertFun) ->
 				    end
 			    end
 		    end, {0, []}, Table),
+		  %% Execute SQL transaction with remaining records
 	      output(LServer, IO,
 		     ["begin;",
 		      lists:reverse(SQLs),
@@ -268,7 +292,7 @@ output(LServer, IO, SQL) ->
 	    file:write(IO, [SQL, $;, $\n])
     end.
 
-record_to_string(#roster{usj = {User, Server, JID},
+record_to_string(#roster{usj = {User, _Server, JID},
 			 name = Name,
 			 subscription = Subscription,
 			 ask = Ask,
@@ -299,7 +323,7 @@ record_to_string(#roster{usj = {User, Server, JID},
      "'", AskMessage, "',"
      "'N', '', 'item')"].
 
-groups_to_string(#roster{usj = {User, Server, JID},
+groups_to_string(#roster{usj = {User, _Server, JID},
 			 groups = Groups}) ->
     Username = ejabberd_odbc:escape(User),
     SJID = ejabberd_odbc:escape(jlib:jid_to_string(JID)),
@@ -307,4 +331,3 @@ groups_to_string(#roster{usj = {User, Server, JID},
       "'", Username, "',"
       "'", SJID, "',"
       "'", ejabberd_odbc:escape(Group), "')"] || Group <- Groups].
-
diff --git a/src/odbc/mssql.sql b/src/odbc/mssql.sql
index 00ad9db45..581a21182 100644
--- a/src/odbc/mssql.sql
+++ b/src/odbc/mssql.sql
@@ -162,6 +162,37 @@ CREATE TABLE [dbo].[private_storage] (
 ) ON [PRIMARY]

 GO

 

+CREATE TABLE [dbo].[privacy_default_list] (

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

+    [name] [varchar] (250) NOT NULL

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[privacy_list] (

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

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

+    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

+) ON [PRIMARY]

+GO

+

+CREATE TABLE [dbo].[privacy_list_data] (

+    [id] [bigint] NOT NULL,

+    [t] [character] (1) NOT NULL,

+    [value] [text] NOT NULL,

+    [action] [character] (1) NOT NULL,

+    [ord] [NUMERIC] NOT NULL,

+    [match_all] [boolean] NOT NULL,

+    [match_iq] [boolean] NOT NULL,

+    [match_message] [boolean] NOT NULL,

+    [match_presence_in] [boolean] NOT NULL,

+    [match_presence_out] [boolean] NOT NULL

+) ON [PRIMARY]

+GO

+

+/* Constraints to add:

+- 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 

 	(

@@ -200,7 +231,7 @@ ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD
 	) WITH  FILLFACTOR = 90  ON [PRIMARY] 

 GO

 

- CREATE  CLUSTERED  INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH  FILLFACTOR = 90 ON [PRIMARY]

+CREATE  CLUSTERED  INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH  FILLFACTOR = 90 ON [PRIMARY]

 GO

 

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

@@ -217,6 +248,13 @@ 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 

+	(

+		[username]

+	) WITH  FILLFACTOR = 90  ON [PRIMARY] 

+GO

+

  CREATE  INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH  FILLFACTOR = 90 ON [PRIMARY]

 GO

 

@@ -240,9 +278,15 @@ 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

 

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

+GO

+

+ CREATE INDEX [IX_privacy_list_username_name] ON [dbo].[privacy_list]([username], [name]) WITH  FILLFACTOR = 90 ON [PRIMARY]

+GO

 

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

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

@@ -250,10 +294,10 @@ GO
 /**                                                     **/

 /** The stored procedures reduce the need to sql        **/

 /** compilation of the database and also allow for also **/

-/** provide each of database integration. The store     **/

+/** provide each of database integration. The stored    **/

 /** procedure have been optimized to increase database  **/

 /** performance and a reduction of 80% in CPU was       **/

-/** achive over the use of standard sql.                **/

+/** achieved over the use of standard sql.              **/

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

 

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

@@ -472,7 +516,7 @@ CREATE PROCEDURE [dbo].[add_user]
 AS

 BEGIN

   INSERT INTO users 

-    ( username, 

+    ( [username], 

       [password]

     ) 

   VALUES 

@@ -948,15 +992,6 @@ BEGIN
 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                      **/

@@ -986,3 +1021,5 @@ BEGIN
 END

 GO

 

+

+

diff --git a/src/odbc/mysql.sql b/src/odbc/mysql.sql
index edb060a59..3af2f65fd 100644
--- a/src/odbc/mysql.sql
+++ b/src/odbc/mysql.sql
@@ -93,6 +93,33 @@ 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 privacy_default_list (
+    username varchar(250) PRIMARY KEY,
+    name varchar(250) NOT NULL
+) CHARACTER SET utf8;
+
+CREATE TABLE privacy_list (
+    username varchar(250) NOT NULL,
+    name varchar(250) NOT NULL,
+    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
+) CHARACTER SET utf8;
+
+CREATE INDEX i_privacy_list_username  USING BTREE ON privacy_list(username);
+CREATE UNIQUE INDEX i_privacy_list_username_name USING BTREE ON privacy_list (username(75), name(75));
+
+CREATE TABLE privacy_list_data (
+    id bigint,
+    t character(1) NOT NULL,
+    value text NOT NULL,
+    action character(1) NOT NULL,
+    ord NUMERIC NOT NULL,
+    match_all boolean NOT NULL,
+    match_iq boolean NOT NULL,
+    match_message boolean NOT NULL,
+    match_presence_in boolean NOT NULL,
+    match_presence_out boolean NOT NULL
+) CHARACTER SET utf8;
+
 CREATE TABLE private_storage (
     username varchar(250) NOT NULL,
     namespace varchar(250) NOT NULL,
diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl
index d91a007de..f42a62a80 100644
--- a/src/odbc/odbc_queries.erl
+++ b/src/odbc/odbc_queries.erl
@@ -32,6 +32,7 @@
 	 roster_subscribe/4,
 	 get_subscription/3,
 	 set_private_data/4,
+	 set_private_data_sql/3,
 	 get_private_data/3,
 	 del_user_private_storage/2,
 	 escape/1]).
@@ -46,6 +47,9 @@
 get_db_type() ->
     generic.
 
+%% F can be either a fun or a list of queries
+%% TODO: We should probably move the list of queries transaction wrapper from the ejabberd_odbc module
+%%       to this one (odbc_queries)
 sql_transaction(LServer, F) ->
     ejabberd_odbc:sql_transaction(LServer, F).
 
@@ -254,14 +258,18 @@ get_subscription(LServer, Username, SJID) ->
        "and jid='", SJID, "'"]).
 
 set_private_data(_LServer, Username, LXMLNS, SData) ->
-    ejabberd_odbc:sql_query_t(
-      ["delete from private_storage "
+    lists:foreach(fun(Query) ->
+            ejabberd_odbc:sql_query_t(Query)
+        end,
+        set_private_data_sql(Username, LXMLNS, SData)).
+       
+set_private_data_sql(Username, LXMLNS, SData) ->
+    [["delete from private_storage "
        "where username='", Username, "' and "
-       "namespace='", LXMLNS, "';"]),
-    ejabberd_odbc:sql_query_t(
+       "namespace='", LXMLNS, "';"],
       ["insert into private_storage(username, namespace, data) "
        "values ('", Username, "', '", LXMLNS, "', "
-       "'", SData, "');"]).
+       "'", SData, "');"]].    
        			       
 get_private_data(LServer, Username, LXMLNS) ->
     ejabberd_odbc:sql_query(
@@ -295,8 +303,19 @@ escape(C)   -> C.
 get_db_type() ->
     mssql.
 
-sql_transaction(_LServer, F) ->
-    {atomic, catch F()}.
+%% Queries can be either a fun or a list of queries
+sql_transaction(_LServer, Queries) when is_list(Queries) ->
+    %% SQL transaction based on a list of queries
+    %% This function automatically 
+    F = fun() ->
+    	lists:foreach(fun(Query) ->
+    			      sql_query(Query)
+    		      end,
+    		      Queries)
+      end,
+    {atomic, catch F()};
+sql_transaction(_LServer, FQueries) ->
+    {atomic, catch FQueries()}.
 
 get_last(LServer, Username) ->
     ejabberd_odbc:sql_query(
@@ -447,11 +466,14 @@ 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, "'"]).
+	    set_private_data_sql(Username, LXMLNS, SData)).
+
+set_private_data_sql(Username, LXMLNS, SData) ->
+    ["EXECUTE dbo.set_private_data '", Username, "' , '", LXMLNS, "' , '", SData, "'"].
 	    
 get_private_data(LServer, Username, LXMLNS) ->
     ejabberd_odbc:sql_query(