diff options
Diffstat (limited to 'src/odbc/odbc_queries.erl')
| -rw-r--r-- | src/odbc/odbc_queries.erl | 937 |
1 files changed, 0 insertions, 937 deletions
diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl deleted file mode 100644 index 66da7906f..000000000 --- a/src/odbc/odbc_queries.erl +++ /dev/null @@ -1,937 +0,0 @@ -%%%---------------------------------------------------------------------- -%%% File : odbc_queries.erl -%%% Author : Mickael Remond <mremond@process-one.net> -%%% Purpose : ODBC queries dependind on back-end -%%% Created : by Mickael Remond <mremond@process-one.net> -%%% -%%% -%%% ejabberd, Copyright (C) 2002-2013 ProcessOne -%%% -%%% This program is free software; you can redistribute it and/or -%%% modify it under the terms of the GNU General Public License as -%%% published by the Free Software Foundation; either version 2 of the -%%% License, or (at your option) any later version. -%%% -%%% This program is distributed in the hope that it will be useful, -%%% but WITHOUT ANY WARRANTY; without even the implied warranty of -%%% MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -%%% General Public License for more details. -%%% -%%% You should have received a copy of the GNU General Public License -%%% along with this program; if not, write to the Free Software -%%% Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA -%%% 02111-1307 USA -%%% -%%%---------------------------------------------------------------------- - --module(odbc_queries). - --author("mremond@process-one.net"). - --export([get_db_type/0, update_t/4, 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, list_users/2, - users_number/1, users_number/2, 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, set_private_data/4, - set_private_data_sql/3, get_private_data/3, get_private_data/2, - del_user_private_storage/2, get_default_privacy_list/2, - get_default_privacy_list_t/1, get_privacy_list_names/2, - get_privacy_list_names_t/1, get_privacy_list_id/3, - get_privacy_list_id_t/2, get_privacy_list_data/3, - get_privacy_list_data_by_id/2, - get_privacy_list_data_by_id_t/1, - set_default_privacy_list/2, - unset_default_privacy_list/2, - remove_privacy_list/2, - add_privacy_list/2, - set_privacy_list/2, - del_privacy_lists/3, - set_vcard/26, - get_vcard/2, - escape/1, - count_records_where/3, - get_roster_version/2, - set_roster_version/2]). - -%% We have only two compile time options for db queries: -%-define(generic, true). -%-define(mssql, true). --ifndef(mssql). - --undef(generic). - --define(generic, true). - --endif. - --include("ejabberd.hrl"). - -%% Almost a copy of string:join/2. -%% We use this version because string:join/2 is relatively -%% new function (introduced in R12B-0). -join([], _Sep) -> []; -join([H | T], Sep) -> [H, [[Sep, X] || X <- T]]. - -%% ----------------- -%% Generic queries --ifdef(generic). - -get_db_type() -> generic. - -%% Safe atomic update. -update_t(Table, Fields, Vals, Where) -> - UPairs = lists:zipwith(fun (A, B) -> - <<A/binary, "='", B/binary, "'">> - end, - Fields, Vals), - case ejabberd_odbc:sql_query_t([<<"update ">>, Table, - <<" set ">>, join(UPairs, <<", ">>), - <<" where ">>, Where, <<";">>]) - of - {updated, 1} -> ok; - _ -> - ejabberd_odbc:sql_query_t([<<"insert into ">>, Table, - <<"(">>, join(Fields, <<", ">>), - <<") values ('">>, join(Vals, <<"', '">>), - <<"');">>]) - end. - -update(LServer, Table, Fields, Vals, Where) -> - UPairs = lists:zipwith(fun (A, B) -> - <<A/binary, "='", B/binary, "'">> - end, - Fields, Vals), - case ejabberd_odbc:sql_query(LServer, - [<<"update ">>, Table, <<" set ">>, - join(UPairs, <<", ">>), <<" where ">>, Where, - <<";">>]) - of - {updated, 1} -> ok; - _ -> - ejabberd_odbc:sql_query(LServer, - [<<"insert into ">>, Table, <<"(">>, - join(Fields, <<", ">>), <<") values ('">>, - join(Vals, <<"', '">>), <<"');">>]) - end. - -%% 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). - -get_last(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"select seconds, state from last where " - "username='">>, - Username, <<"'">>]). - -set_last_t(LServer, Username, Seconds, State) -> - update(LServer, <<"last">>, - [<<"username">>, <<"seconds">>, <<"state">>], - [Username, Seconds, State], - [<<"username='">>, Username, <<"'">>]). - -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, - fun () -> - update_t(<<"users">>, - [<<"username">>, - <<"password">>], - [Username, Pass], - [<<"username='">>, Username, - <<"'">>]) - end). - -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) -> - P = - 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, - <<"';">>]), - P. - -list_users(LServer) -> - ejabberd_odbc:sql_query(LServer, - [<<"select username from users">>]). - -list_users(LServer, [{from, Start}, {to, End}]) - when is_integer(Start) and is_integer(End) -> - list_users(LServer, - [{limit, End - Start + 1}, {offset, Start - 1}]); -list_users(LServer, - [{prefix, Prefix}, {from, Start}, {to, End}]) - when is_binary(Prefix) and is_integer(Start) and - is_integer(End) -> - list_users(LServer, - [{prefix, Prefix}, {limit, End - Start + 1}, - {offset, Start - 1}]); -list_users(LServer, [{limit, Limit}, {offset, Offset}]) - when is_integer(Limit) and is_integer(Offset) -> - ejabberd_odbc:sql_query(LServer, - [list_to_binary( - io_lib:format( - "select username from users " ++ - "order by username " ++ - "limit ~w offset ~w", - [Limit, Offset]))]); -list_users(LServer, - [{prefix, Prefix}, {limit, Limit}, {offset, Offset}]) - when is_binary(Prefix) and is_integer(Limit) and - is_integer(Offset) -> - ejabberd_odbc:sql_query(LServer, - [list_to_binary( - io_lib:format( - "select username from users " ++ - "where username like '~s%' " ++ - "order by username " ++ - "limit ~w offset ~w ", - [Prefix, Limit, Offset]))]). - -users_number(LServer) -> - case element(1, - ejabberd_config:get_local_option( - {odbc_server, LServer}, fun(V) -> V end)) - of - pgsql -> - case - ejabberd_config:get_local_option( - {pgsql_users_number_estimate, LServer}, - fun(V) when is_boolean(V) -> V end, - false) - of - true -> - ejabberd_odbc:sql_query(LServer, - [<<"select reltuples from pg_class where " - "oid = 'users'::regclass::oid">>]); - _ -> - ejabberd_odbc:sql_query(LServer, - [<<"select count(*) from users">>]) - end; - _ -> - ejabberd_odbc:sql_query(LServer, - [<<"select count(*) from users">>]) - end. - -users_number(LServer, [{prefix, Prefix}]) - when is_binary(Prefix) -> - ejabberd_odbc:sql_query(LServer, - [list_to_binary( - io_lib:fwrite( - "select count(*) from users " ++ - %% Warning: Escape prefix at higher level to prevent SQL - %% injection. - "where username like '~s%'", - [Prefix]))]); -users_number(LServer, []) -> - users_number(LServer). - - -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) -> - update_t(<<"rosterusers">>, - [<<"username">>, <<"jid">>, <<"nick">>, - <<"subscription">>, <<"ask">>, <<"askmessage">>, - <<"server">>, <<"subscribe">>, <<"type">>], - ItemVals, - [<<"username='">>, Username, <<"' and jid='">>, SJID, - <<"'">>]), - 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 ('">>, - join(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 ('">>, - join(ItemVals, <<"', '">>), <<"');">>], - [<<"delete from rostergroups where " - "username='">>, - Username, <<"' and jid='">>, SJID, <<"';">>]] - ++ - [[<<"insert into rostergroups( " - " username, jid, grp) values ('">>, - join(ItemGroup, <<"', '">>), <<"');">>] - || ItemGroup <- ItemGroups]. - -roster_subscribe(_LServer, Username, SJID, ItemVals) -> - update_t(<<"rosterusers">>, - [<<"username">>, <<"jid">>, <<"nick">>, - <<"subscription">>, <<"ask">>, <<"askmessage">>, - <<"server">>, <<"subscribe">>, <<"type">>], - ItemVals, - [<<"username='">>, Username, <<"' and jid='">>, SJID, - <<"'">>]). - -get_subscription(LServer, Username, SJID) -> - ejabberd_odbc:sql_query(LServer, - [<<"select subscription from rosterusers " - "where username='">>, - Username, <<"' and jid='">>, SJID, <<"'">>]). - -set_private_data(_LServer, Username, LXMLNS, SData) -> - update_t(<<"private_storage">>, - [<<"username">>, <<"namespace">>, <<"data">>], - [Username, LXMLNS, SData], - [<<"username='">>, Username, <<"' and namespace='">>, - LXMLNS, <<"'">>]). - -set_private_data_sql(Username, LXMLNS, SData) -> - [[<<"delete from private_storage where username='">>, - Username, <<"' and namespace='">>, LXMLNS, <<"';">>], - [<<"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, - <<"';">>]). - -get_private_data(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"select namespace, data from private_storage " - "where username='">>, Username, <<"';">>]). - -del_user_private_storage(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"delete from private_storage where username='">>, - Username, <<"';">>]). - -set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, - SFamily, SGiven, SLBDay, SLCTRY, SLEMail, SLFN, - SLFamily, SLGiven, SLLocality, SLMiddle, SLNickname, - SLOrgName, SLOrgUnit, SLocality, SMiddle, SNickname, - SOrgName, SOrgUnit, SVCARD, Username) -> - ejabberd_odbc:sql_transaction(LServer, - fun () -> - update_t(<<"vcard">>, - [<<"username">>, - <<"vcard">>], - [LUsername, SVCARD], - [<<"username='">>, LUsername, - <<"'">>]), - update_t(<<"vcard_search">>, - [<<"username">>, - <<"lusername">>, <<"fn">>, - <<"lfn">>, <<"family">>, - <<"lfamily">>, <<"given">>, - <<"lgiven">>, <<"middle">>, - <<"lmiddle">>, - <<"nickname">>, - <<"lnickname">>, <<"bday">>, - <<"lbday">>, <<"ctry">>, - <<"lctry">>, <<"locality">>, - <<"llocality">>, - <<"email">>, <<"lemail">>, - <<"orgname">>, - <<"lorgname">>, - <<"orgunit">>, - <<"lorgunit">>], - [Username, LUsername, SFN, - SLFN, SFamily, SLFamily, - SGiven, SLGiven, SMiddle, - SLMiddle, SNickname, - SLNickname, SBDay, SLBDay, - SCTRY, SLCTRY, SLocality, - SLLocality, SEMail, SLEMail, - SOrgName, SLOrgName, - SOrgUnit, SLOrgUnit], - [<<"lusername='">>, - LUsername, <<"'">>]) - end). - -get_vcard(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"select vcard from vcard where username='">>, - Username, <<"';">>]). - -get_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"select name from privacy_default_list " - "where username='">>, - Username, <<"';">>]). - -get_default_privacy_list_t(Username) -> - ejabberd_odbc:sql_query_t([<<"select name from privacy_default_list " - "where username='">>, - Username, <<"';">>]). - -get_privacy_list_names(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"select name from privacy_list where " - "username='">>, - Username, <<"';">>]). - -get_privacy_list_names_t(Username) -> - ejabberd_odbc:sql_query_t([<<"select name from privacy_list where " - "username='">>, - Username, <<"';">>]). - -get_privacy_list_id(LServer, Username, SName) -> - ejabberd_odbc:sql_query(LServer, - [<<"select id from privacy_list where username='">>, - Username, <<"' and name='">>, SName, <<"';">>]). - -get_privacy_list_id_t(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"select id from privacy_list where username='">>, - Username, <<"' and name='">>, SName, <<"';">>]). - -get_privacy_list_data(LServer, Username, SName) -> - ejabberd_odbc:sql_query(LServer, - [<<"select t, value, action, ord, match_all, " - "match_iq, match_message, match_presence_in, " - "match_presence_out from privacy_list_data " - "where id = (select id from privacy_list " - "where username='">>, - Username, <<"' and name='">>, SName, - <<"') order by ord;">>]). - -get_privacy_list_data_by_id(LServer, ID) -> - ejabberd_odbc:sql_query(LServer, - [<<"select t, value, action, ord, match_all, " - "match_iq, match_message, match_presence_in, " - "match_presence_out from privacy_list_data " - "where id='">>, - ID, <<"' order by ord;">>]). - -get_privacy_list_data_by_id_t(ID) -> - ejabberd_odbc:sql_query_t([<<"select t, value, action, ord, match_all, " - "match_iq, match_message, match_presence_in, " - "match_presence_out from privacy_list_data " - "where id='">>, - ID, <<"' order by ord;">>]). - -set_default_privacy_list(Username, SName) -> - update_t(<<"privacy_default_list">>, - [<<"username">>, <<"name">>], [Username, SName], - [<<"username='">>, Username, <<"'">>]). - -unset_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"delete from privacy_default_list " - " where username='">>, - Username, <<"';">>]). - -remove_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"delete from privacy_list where username='">>, - Username, <<"' and name='">>, SName, <<"';">>]). - -add_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"insert into privacy_list(username, name) " - "values ('">>, - Username, <<"', '">>, SName, <<"');">>]). - -set_privacy_list(ID, RItems) -> - ejabberd_odbc:sql_query_t([<<"delete from privacy_list_data where " - "id='">>, - ID, <<"';">>]), - lists:foreach(fun (Items) -> - ejabberd_odbc:sql_query_t([<<"insert into privacy_list_data(id, t, " - "value, action, ord, match_all, match_iq, " - "match_message, match_presence_in, match_prese" - "nce_out ) values ('">>, - ID, <<"', '">>, - join(Items, <<"', '">>), - <<"');">>]) - end, - RItems). - -del_privacy_lists(LServer, Server, Username) -> -%% Characters to escape -%% Count number of records in a table given a where clause - ejabberd_odbc:sql_query(LServer, - [<<"delete from privacy_list where username='">>, - Username, <<"';">>]), - ejabberd_odbc:sql_query(LServer, - [<<"delete from privacy_list_data where " - "value='">>, - <<Username/binary, "@", Server/binary>>, - <<"';">>]), - ejabberd_odbc:sql_query(LServer, - [<<"delete from privacy_default_list where " - "username='">>, - Username, <<"';">>]). - -escape($\000) -> <<"\\0">>; -escape($\n) -> <<"\\n">>; -escape($\t) -> <<"\\t">>; -escape($\b) -> <<"\\b">>; -escape($\r) -> <<"\\r">>; -escape($') -> <<"''">>; -escape($") -> <<"\\\"">>; -escape($\\) -> <<"\\\\">>; -escape(C) -> <<C>>. - -count_records_where(LServer, Table, WhereClause) -> - ejabberd_odbc:sql_query(LServer, - [<<"select count(*) from ">>, Table, <<" ">>, - WhereClause, <<";">>]). - -get_roster_version(LServer, LUser) -> - ejabberd_odbc:sql_query(LServer, - [<<"select version from roster_version where " - "username = '">>, - LUser, <<"'">>]). - -set_roster_version(LUser, Version) -> - update_t(<<"roster_version">>, - [<<"username">>, <<"version">>], [LUser, Version], - [<<"username = '">>, LUser, <<"'">>]). - --endif. - -%% ----------------- -%% MSSQL queries --ifdef(mssql). - -%% Queries can be either a fun or a list of queries -get_db_type() -> mssql. - -sql_transaction(LServer, Queries) - when is_list(Queries) -> - F = fun () -> - lists:foreach(fun (Query) -> - ejabberd_odbc:sql_query(LServer, Query) - end, - Queries) - end, - {atomic, catch F()}; -sql_transaction(_LServer, FQueries) -> - {atomic, catch FQueries()}. - -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">>). - -list_users(LServer, _) -> list_users(LServer). - -users_number(LServer) -> - ejabberd_odbc:sql_query(LServer, - <<"select count(*) from users with (nolock)">>). - -users_number(LServer, _) -> users_number(LServer). - -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 is_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, <<"'">>]). - -set_private_data(LServer, Username, LXMLNS, SData) -> - ejabberd_odbc:sql_query(LServer, - 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(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, - <<"'">>]). - -set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, - SFamily, SGiven, SLBDay, SLCTRY, SLEMail, SLFN, - SLFamily, SLGiven, SLLocality, SLMiddle, SLNickname, - SLOrgName, SLOrgUnit, SLocality, SMiddle, SNickname, - SOrgName, SOrgUnit, SVCARD, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.set_vcard '">>, SVCARD, <<"' , '">>, - Username, <<"' , '">>, LUsername, <<"' , '">>, SFN, - <<"' , '">>, SLFN, <<"' , '">>, SFamily, - <<"' , '">>, SLFamily, <<"' , '">>, SGiven, - <<"' , '">>, SLGiven, <<"' , '">>, SMiddle, - <<"' , '">>, SLMiddle, <<"' , '">>, SNickname, - <<"' , '">>, SLNickname, <<"' , '">>, SBDay, - <<"' , '">>, SLBDay, <<"' , '">>, SCTRY, - <<"' , '">>, SLCTRY, <<"' , '">>, SLocality, - <<"' , '">>, SLLocality, <<"' , '">>, SEMail, - <<"' , '">>, SLEMail, <<"' , '">>, SOrgName, - <<"' , '">>, SLOrgName, <<"' , '">>, SOrgUnit, - <<"' , '">>, SLOrgUnit, <<"'">>]). - -get_vcard(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.get_vcard '">>, Username, <<"'">>]). - -get_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.get_default_privacy_list '">>, - Username, <<"'">>]). - -get_default_privacy_list_t(Username) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_default_privacy_list '">>, - Username, <<"'">>]). - -get_privacy_list_names(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.get_privacy_list_names '">>, - Username, <<"'">>]). - -get_privacy_list_names_t(Username) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_privacy_list_names '">>, - Username, <<"'">>]). - -get_privacy_list_id(LServer, Username, SName) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.get_privacy_list_id '">>, Username, - <<"' , '">>, SName, <<"'">>]). - -get_privacy_list_id_t(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_privacy_list_id '">>, - Username, <<"' , '">>, SName, <<"'">>]). - -get_privacy_list_data(LServer, Username, SName) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.get_privacy_list_data '">>, - Username, <<"' , '">>, SName, <<"'">>]). - -get_privacy_list_data_by_id(LServer, ID) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.get_privacy_list_data_by_id '">>, - ID, <<"'">>]). - -get_privacy_list_data_by_id_t(ID) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_privacy_list_data_by_id '">>, - ID, <<"'">>]). - -set_default_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.set_default_privacy_list '">>, - Username, <<"' , '">>, SName, <<"'">>]). - -unset_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.unset_default_privacy_list '">>, - Username, <<"'">>]). - -remove_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.remove_privacy_list '">>, - Username, <<"' , '">>, SName, <<"'">>]). - -add_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.add_privacy_list '">>, - Username, <<"' , '">>, SName, <<"'">>]). - -set_privacy_list(ID, RItems) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.del_privacy_list_by_id '">>, - ID, <<"'">>]), - lists:foreach(fun (Items) -> - ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.set_privacy_list '">>, - ID, <<"', '">>, - join(Items, <<"', '">>), - <<"'">>]) - end, - RItems). - -del_privacy_lists(LServer, Server, Username) -> -%% Characters to escape -%% Count number of records in a table given a where clause - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.del_privacy_lists @Server='">>, - Server, <<"' @username='">>, Username, <<"'">>]). - -escape($\000) -> <<"\\0">>; -escape($\t) -> <<"\\t">>; -escape($\b) -> <<"\\b">>; -escape($\r) -> <<"\\r">>; -escape($') -> <<"''">>; -escape($") -> <<"\\\"">>; -escape(C) -> C. - -count_records_where(LServer, Table, WhereClause) -> - ejabberd_odbc:sql_query(LServer, - [<<"select count(*) from ">>, Table, - <<" with (nolock) ">>, WhereClause]). - -get_roster_version(LServer, LUser) -> - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.get_roster_version '">>, LUser, - <<"'">>]). - -set_roster_version(Username, Version) -> - LServer = (?MYNAME), - ejabberd_odbc:sql_query(LServer, - [<<"EXECUTE dbo.set_roster_version '">>, Username, - <<"', '">>, Version, <<"'">>]). - --endif. |