From 86da0b3e1beeb34cbca660ce39adec44a1358bb4 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Micka=C3=ABl=20R=C3=A9mond?= Date: Mon, 20 Nov 2006 13:20:47 +0000 Subject: 2006-11-20 Mickael Remond * 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 --- src/ejd2odbc.erl | 33 +++++++++++++++++++++---- src/odbc/mssql.sql | 63 +++++++++++++++++++++++++++++++++++++---------- src/odbc/mysql.sql | 27 ++++++++++++++++++++ src/odbc/odbc_queries.erl | 40 +++++++++++++++++++++++------- 4 files changed, 136 insertions(+), 27 deletions(-) (limited to 'src') 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( -- cgit v1.2.3