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

3 files changed, 108 insertions, 22 deletions

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(