From d8fbe8a28910b11e276f635cc04642b53e3a326c Mon Sep 17 00:00:00 2001 From: Alexey Shchepin Date: Tue, 23 Feb 2016 02:56:43 +0300 Subject: Update more SQL queries --- src/ejabberd_auth_odbc.erl | 14 +-- src/ejabberd_odbc.erl | 6 +- src/mod_blocking.erl | 40 ++----- src/mod_privacy.erl | 168 ++++++++------------------ src/mod_private.erl | 24 ++-- src/odbc_queries.erl | 292 +++++++++++++++++++++++---------------------- 6 files changed, 227 insertions(+), 317 deletions(-) (limited to 'src') diff --git a/src/ejabberd_auth_odbc.erl b/src/ejabberd_auth_odbc.erl index 608127817..f38c8286c 100644 --- a/src/ejabberd_auth_odbc.erl +++ b/src/ejabberd_auth_odbc.erl @@ -153,26 +153,24 @@ set_password(User, Server, Password) -> (LUser == <<>>) or (LServer == <<>>) -> {error, invalid_jid}; true -> - Username = ejabberd_odbc:escape(LUser), case is_scrammed() of true -> Scram = password_to_scram(Password), case catch odbc_queries:set_password_scram_t( LServer, - Username, - ejabberd_odbc:escape(Scram#scram.storedkey), - ejabberd_odbc:escape(Scram#scram.serverkey), - ejabberd_odbc:escape(Scram#scram.salt), - integer_to_binary(Scram#scram.iterationcount) + LUser, + Scram#scram.storedkey, + Scram#scram.serverkey, + Scram#scram.salt, + Scram#scram.iterationcount ) of {atomic, ok} -> ok; Other -> {error, Other} end; false -> - Pass = ejabberd_odbc:escape(Password), case catch odbc_queries:set_password_t(LServer, - Username, Pass) + LUser, Password) of {atomic, ok} -> ok; Other -> {error, Other} diff --git a/src/ejabberd_odbc.erl b/src/ejabberd_odbc.erl index 4f818f513..b7aff7b14 100644 --- a/src/ejabberd_odbc.erl +++ b/src/ejabberd_odbc.erl @@ -616,9 +616,9 @@ pgsql_prepare(SQLQuery, State) -> pgsql_execute_escape() -> #sql_escape{string = fun(X) -> X end, - integer = fun(X) -> integer_to_binary(X) end, - boolean = fun(true) -> <<"1">>; - (false) -> <<"0">> + integer = fun(X) -> [integer_to_binary(X)] end, + boolean = fun(true) -> "1"; + (false) -> "0" end }. diff --git a/src/mod_blocking.erl b/src/mod_blocking.erl index 981c5735f..815884ff2 100644 --- a/src/mod_blocking.erl +++ b/src/mod_blocking.erl @@ -223,23 +223,18 @@ process_blocklist_block(LUser, LServer, Filter, odbc) -> Default = case mod_privacy:sql_get_default_privacy_list_t(LUser) of - {selected, [<<"name">>], []} -> + {selected, []} -> Name = <<"Blocked contacts">>, mod_privacy:sql_add_privacy_list(LUser, Name), mod_privacy:sql_set_default_privacy_list(LUser, Name), Name; - {selected, [<<"name">>], [[Name]]} -> Name + {selected, [{Name}]} -> Name end, - {selected, [<<"id">>], [[ID]]} = + {selected, [{ID}]} = mod_privacy:sql_get_privacy_list_id_t(LUser, Default), - case mod_privacy:sql_get_privacy_list_data_by_id_t(ID) - of - {selected, - [<<"t">>, <<"value">>, <<"action">>, <<"ord">>, - <<"match_all">>, <<"match_iq">>, <<"match_message">>, - <<"match_presence_in">>, <<"match_presence_out">>], - RItems = [_ | _]} -> + case mod_privacy:sql_get_privacy_list_data_by_id_t(ID) of + {selected, RItems = [_ | _]} -> List = lists:flatmap(fun mod_privacy:raw_to_item/1, RItems); _ -> List = [] end, @@ -345,17 +340,12 @@ unblock_by_filter(LUser, LServer, Filter, odbc) -> F = fun () -> case mod_privacy:sql_get_default_privacy_list_t(LUser) of - {selected, [<<"name">>], []} -> ok; - {selected, [<<"name">>], [[Default]]} -> - {selected, [<<"id">>], [[ID]]} = + {selected, []} -> ok; + {selected, [{Default}]} -> + {selected, [{ID}]} = mod_privacy:sql_get_privacy_list_id_t(LUser, Default), - case mod_privacy:sql_get_privacy_list_data_by_id_t(ID) - of - {selected, - [<<"t">>, <<"value">>, <<"action">>, <<"ord">>, - <<"match_all">>, <<"match_iq">>, <<"match_message">>, - <<"match_presence_in">>, <<"match_presence_out">>], - RItems = [_ | _]} -> + case mod_privacy:sql_get_privacy_list_data_by_id_t(ID) of + {selected, RItems = [_ | _]} -> List = lists:flatmap(fun mod_privacy:raw_to_item/1, RItems), NewList = Filter(List), @@ -435,16 +425,12 @@ process_blocklist_get(LUser, LServer, odbc) -> case catch mod_privacy:sql_get_default_privacy_list(LUser, LServer) of - {selected, [<<"name">>], []} -> []; - {selected, [<<"name">>], [[Default]]} -> + {selected, []} -> []; + {selected, [{Default}]} -> case catch mod_privacy:sql_get_privacy_list_data(LUser, LServer, Default) of - {selected, - [<<"t">>, <<"value">>, <<"action">>, <<"ord">>, - <<"match_all">>, <<"match_iq">>, <<"match_message">>, - <<"match_presence_in">>, <<"match_presence_out">>], - RItems} -> + {selected, RItems} -> lists:flatmap(fun mod_privacy:raw_to_item/1, RItems); {'EXIT', _} -> error end; diff --git a/src/mod_privacy.erl b/src/mod_privacy.erl index e8e6ee7be..193befe82 100644 --- a/src/mod_privacy.erl +++ b/src/mod_privacy.erl @@ -181,16 +181,14 @@ process_lists_get(LUser, LServer, _Active, riak) -> error end; process_lists_get(LUser, LServer, _Active, odbc) -> - Default = case catch sql_get_default_privacy_list(LUser, - LServer) - of - {selected, [<<"name">>], []} -> none; - {selected, [<<"name">>], [[DefName]]} -> DefName; + Default = case catch sql_get_default_privacy_list(LUser, LServer) of + {selected, []} -> none; + {selected, [{DefName}]} -> DefName; _ -> none end, case catch sql_get_privacy_list_names(LUser, LServer) of - {selected, [<<"name">>], Names} -> - LItems = lists:map(fun ([N]) -> + {selected, Names} -> + LItems = lists:map(fun ({N}) -> #xmlel{name = <<"list">>, attrs = [{<<"name">>, N}], children = []} @@ -242,17 +240,11 @@ process_list_get(LUser, LServer, Name, riak) -> error end; process_list_get(LUser, LServer, Name, odbc) -> - case catch sql_get_privacy_list_id(LUser, LServer, Name) - of - {selected, [<<"id">>], []} -> not_found; - {selected, [<<"id">>], [[ID]]} -> - case catch sql_get_privacy_list_data_by_id(ID, LServer) - of - {selected, - [<<"t">>, <<"value">>, <<"action">>, <<"ord">>, - <<"match_all">>, <<"match_iq">>, <<"match_message">>, - <<"match_presence_in">>, <<"match_presence_out">>], - RItems} -> + case catch sql_get_privacy_list_id(LUser, LServer, Name) of + {selected, []} -> not_found; + {selected, [{ID}]} -> + case catch sql_get_privacy_list_data_by_id(ID, LServer) of + {selected, RItems} -> lists:flatmap(fun raw_to_item/1, RItems); _ -> error end; @@ -405,9 +397,9 @@ process_default_set(LUser, LServer, {value, Name}, odbc) -> F = fun () -> case sql_get_privacy_list_names_t(LUser) of - {selected, [<<"name">>], []} -> not_found; - {selected, [<<"name">>], Names} -> - case lists:member([Name], Names) of + {selected, []} -> not_found; + {selected, Names} -> + case lists:member({Name}, Names) of true -> sql_set_default_privacy_list(LUser, Name), ok; false -> not_found end @@ -473,17 +465,11 @@ process_active_set(LUser, LServer, Name, riak) -> error end; process_active_set(LUser, LServer, Name, odbc) -> - case catch sql_get_privacy_list_id(LUser, LServer, Name) - of - {selected, [<<"id">>], []} -> error; - {selected, [<<"id">>], [[ID]]} -> - case catch sql_get_privacy_list_data_by_id(ID, LServer) - of - {selected, - [<<"t">>, <<"value">>, <<"action">>, <<"ord">>, - <<"match_all">>, <<"match_iq">>, <<"match_message">>, - <<"match_presence_in">>, <<"match_presence_out">>], - RItems} -> + case catch sql_get_privacy_list_id(LUser, LServer, Name) of + {selected, []} -> error; + {selected, [{ID}]} -> + case catch sql_get_privacy_list_data_by_id(ID, LServer) of + {selected, RItems} -> lists:flatmap(fun raw_to_item/1, RItems); _ -> error end; @@ -520,9 +506,9 @@ remove_privacy_list(LUser, LServer, Name, riak) -> remove_privacy_list(LUser, LServer, Name, odbc) -> F = fun () -> case sql_get_default_privacy_list_t(LUser) of - {selected, [<<"name">>], []} -> + {selected, []} -> sql_remove_privacy_list(LUser, Name), ok; - {selected, [<<"name">>], [[Default]]} -> + {selected, [{Default}]} -> if Name == Default -> conflict; true -> sql_remove_privacy_list(LUser, Name), ok end @@ -590,12 +576,12 @@ set_privacy_list(LUser, LServer, Name, List, odbc) -> RItems = lists:map(fun item_to_raw/1, List), F = fun () -> ID = case sql_get_privacy_list_id_t(LUser, Name) of - {selected, [<<"id">>], []} -> + {selected, []} -> sql_add_privacy_list(LUser, Name), - {selected, [<<"id">>], [[I]]} = + {selected, [{I}]} = sql_get_privacy_list_id_t(LUser, Name), I; - {selected, [<<"id">>], [[I]]} -> I + {selected, [{I}]} -> I end, sql_set_privacy_list(ID, RItems), ok @@ -785,16 +771,11 @@ get_user_list(_, LUser, LServer, riak) -> get_user_list(_, LUser, LServer, odbc) -> case catch sql_get_default_privacy_list(LUser, LServer) of - {selected, [<<"name">>], []} -> {none, []}; - {selected, [<<"name">>], [[Default]]} -> + {selected, []} -> {none, []}; + {selected, [{Default}]} -> case catch sql_get_privacy_list_data(LUser, LServer, - Default) - of - {selected, - [<<"t">>, <<"value">>, <<"action">>, <<"ord">>, - <<"match_all">>, <<"match_iq">>, <<"match_message">>, - <<"match_presence_in">>, <<"match_presence_out">>], - RItems} -> + Default) of + {selected, RItems} -> {Default, lists:flatmap(fun raw_to_item/1, RItems)}; _ -> {none, []} end; @@ -822,26 +803,21 @@ get_user_lists(LUser, LServer, riak) -> end; get_user_lists(LUser, LServer, odbc) -> Default = case catch sql_get_default_privacy_list(LUser, LServer) of - {selected, [<<"name">>], []} -> + {selected, []} -> none; - {selected, [<<"name">>], [[DefName]]} -> + {selected, [{DefName}]} -> DefName; _ -> none end, case catch sql_get_privacy_list_names(LUser, LServer) of - {selected, [<<"name">>], Names} -> + {selected, Names} -> Lists = lists:flatmap( - fun([Name]) -> + fun({Name}) -> case catch sql_get_privacy_list_data( LUser, LServer, Name) of - {selected, - [<<"t">>, <<"value">>, <<"action">>, - <<"ord">>, <<"match_all">>, <<"match_iq">>, - <<"match_message">>, <<"match_presence_in">>, - <<"match_presence_out">>], - RItems} -> + {selected, RItems} -> [{Name, lists:flatmap(fun raw_to_item/1, RItems)}]; _ -> [] @@ -994,9 +970,9 @@ updated_list(_, #userlist{name = OldName} = Old, true -> Old end. -raw_to_item([SType, SValue, SAction, SOrder, SMatchAll, - SMatchIQ, SMatchMessage, SMatchPresenceIn, - SMatchPresenceOut] = Row) -> +raw_to_item({SType, SValue, SAction, Order, MatchAll, + MatchIQ, MatchMessage, MatchPresenceIn, + MatchPresenceOut} = Row) -> try {Type, Value} = case SType of <<"n">> -> {none, none}; @@ -1018,12 +994,6 @@ raw_to_item([SType, SValue, SAction, SOrder, SMatchAll, <<"a">> -> allow; <<"d">> -> deny end, - Order = jlib:binary_to_integer(SOrder), - MatchAll = ejabberd_odbc:to_bool(SMatchAll), - MatchIQ = ejabberd_odbc:to_bool(SMatchIQ), - MatchMessage = ejabberd_odbc:to_bool(SMatchMessage), - MatchPresenceIn = ejabberd_odbc:to_bool(SMatchPresenceIn), - MatchPresenceOut = ejabberd_odbc:to_bool(SMatchPresenceOut), [#listitem{type = Type, value = Value, action = Action, order = Order, match_all = MatchAll, match_iq = MatchIQ, match_message = MatchMessage, @@ -1057,58 +1027,29 @@ item_to_raw(#listitem{type = Type, value = Value, allow -> <<"a">>; deny -> <<"d">> end, - SOrder = iolist_to_binary(integer_to_list(Order)), - SMatchAll = if MatchAll -> <<"1">>; - true -> <<"0">> - end, - SMatchIQ = if MatchIQ -> <<"1">>; - true -> <<"0">> - end, - SMatchMessage = if MatchMessage -> <<"1">>; - true -> <<"0">> - end, - SMatchPresenceIn = if MatchPresenceIn -> <<"1">>; - true -> <<"0">> - end, - SMatchPresenceOut = if MatchPresenceOut -> <<"1">>; - true -> <<"0">> - end, - [SType, SValue, SAction, SOrder, SMatchAll, SMatchIQ, - SMatchMessage, SMatchPresenceIn, SMatchPresenceOut]. + {SType, SValue, SAction, Order, MatchAll, MatchIQ, + MatchMessage, MatchPresenceIn, MatchPresenceOut}. sql_get_default_privacy_list(LUser, LServer) -> - Username = ejabberd_odbc:escape(LUser), - odbc_queries:get_default_privacy_list(LServer, - Username). + odbc_queries:get_default_privacy_list(LServer, LUser). sql_get_default_privacy_list_t(LUser) -> - Username = ejabberd_odbc:escape(LUser), - odbc_queries:get_default_privacy_list_t(Username). + odbc_queries:get_default_privacy_list_t(LUser). sql_get_privacy_list_names(LUser, LServer) -> - Username = ejabberd_odbc:escape(LUser), - odbc_queries:get_privacy_list_names(LServer, Username). + odbc_queries:get_privacy_list_names(LServer, LUser). sql_get_privacy_list_names_t(LUser) -> - Username = ejabberd_odbc:escape(LUser), - odbc_queries:get_privacy_list_names_t(Username). + odbc_queries:get_privacy_list_names_t(LUser). sql_get_privacy_list_id(LUser, LServer, Name) -> - Username = ejabberd_odbc:escape(LUser), - SName = ejabberd_odbc:escape(Name), - odbc_queries:get_privacy_list_id(LServer, Username, - SName). + odbc_queries:get_privacy_list_id(LServer, LUser, Name). sql_get_privacy_list_id_t(LUser, Name) -> - Username = ejabberd_odbc:escape(LUser), - SName = ejabberd_odbc:escape(Name), - odbc_queries:get_privacy_list_id_t(Username, SName). + odbc_queries:get_privacy_list_id_t(LUser, Name). sql_get_privacy_list_data(LUser, LServer, Name) -> - Username = ejabberd_odbc:escape(LUser), - SName = ejabberd_odbc:escape(Name), - odbc_queries:get_privacy_list_data(LServer, Username, - SName). + odbc_queries:get_privacy_list_data(LServer, LUser, Name). sql_get_privacy_list_data_t(LUser, Name) -> Username = ejabberd_odbc:escape(LUser), @@ -1122,33 +1063,22 @@ sql_get_privacy_list_data_by_id_t(ID) -> odbc_queries:get_privacy_list_data_by_id_t(ID). sql_set_default_privacy_list(LUser, Name) -> - Username = ejabberd_odbc:escape(LUser), - SName = ejabberd_odbc:escape(Name), - odbc_queries:set_default_privacy_list(Username, SName). + odbc_queries:set_default_privacy_list(LUser, Name). sql_unset_default_privacy_list(LUser, LServer) -> - Username = ejabberd_odbc:escape(LUser), - odbc_queries:unset_default_privacy_list(LServer, - Username). + odbc_queries:unset_default_privacy_list(LServer, LUser). sql_remove_privacy_list(LUser, Name) -> - Username = ejabberd_odbc:escape(LUser), - SName = ejabberd_odbc:escape(Name), - odbc_queries:remove_privacy_list(Username, SName). + odbc_queries:remove_privacy_list(LUser, Name). sql_add_privacy_list(LUser, Name) -> - Username = ejabberd_odbc:escape(LUser), - SName = ejabberd_odbc:escape(Name), - odbc_queries:add_privacy_list(Username, SName). + odbc_queries:add_privacy_list(LUser, Name). sql_set_privacy_list(ID, RItems) -> odbc_queries:set_privacy_list(ID, RItems). sql_del_privacy_lists(LUser, LServer) -> - Username = ejabberd_odbc:escape(LUser), - Server = ejabberd_odbc:escape(LServer), - odbc_queries:del_privacy_lists(LServer, Server, - Username). + odbc_queries:del_privacy_lists(LServer, LUser). update_table() -> Fields = record_info(fields, privacy), diff --git a/src/mod_private.erl b/src/mod_private.erl index ee3a6c1f3..f3dceeaaf 100644 --- a/src/mod_private.erl +++ b/src/mod_private.erl @@ -152,11 +152,8 @@ set_data(LUser, LServer, {XmlNS, Xmlel}, mnesia) -> {LUser, LServer, XmlNS}, xml = Xmlel}); set_data(LUser, LServer, {XMLNS, El}, odbc) -> - Username = ejabberd_odbc:escape(LUser), - LXMLNS = ejabberd_odbc:escape(XMLNS), - SData = ejabberd_odbc:escape(fxml:element_to_binary(El)), - odbc_queries:set_private_data(LServer, Username, LXMLNS, - SData); + SData = fxml:element_to_binary(El), + odbc_queries:set_private_data(LServer, LUser, XMLNS, SData); set_data(LUser, LServer, {XMLNS, El}, riak) -> ejabberd_riak:put(#private_storage{usns = {LUser, LServer, XMLNS}, xml = El}, @@ -184,12 +181,10 @@ get_data(LUser, LServer, mnesia, end; get_data(LUser, LServer, odbc, [{XMLNS, El} | Els], Res) -> - Username = ejabberd_odbc:escape(LUser), - LXMLNS = ejabberd_odbc:escape(XMLNS), case catch odbc_queries:get_private_data(LServer, - Username, LXMLNS) + LUser, XMLNS) of - {selected, [<<"data">>], [[SData]]} -> + {selected, [{SData}]} -> case fxml_stream:parse_element(SData) of Data when is_record(Data, xmlel) -> get_data(LUser, LServer, odbc, Els, [Data | Res]) @@ -217,11 +212,10 @@ get_all_data(LUser, LServer, mnesia) -> xml = '$1'}, [], ['$1']}])); get_all_data(LUser, LServer, odbc) -> - Username = ejabberd_odbc:escape(LUser), - case catch odbc_queries:get_private_data(LServer, Username) of - {selected, [<<"namespace">>, <<"data">>], Res} -> + case catch odbc_queries:get_private_data(LServer, LUser) of + {selected, Res} -> lists:flatmap( - fun([_, SData]) -> + fun({_, SData}) -> case fxml_stream:parse_element(SData) of #xmlel{} = El -> [El]; @@ -269,9 +263,7 @@ remove_user(LUser, LServer, mnesia) -> end, mnesia:transaction(F); remove_user(LUser, LServer, odbc) -> - Username = ejabberd_odbc:escape(LUser), - odbc_queries:del_user_private_storage(LServer, - Username); + odbc_queries:del_user_private_storage(LServer, LUser); remove_user(LUser, LServer, riak) -> {atomic, ejabberd_riak:delete_by_index(private_storage, <<"us">>, {LUser, LServer})}. diff --git a/src/odbc_queries.erl b/src/odbc_queries.erl index 283e33dfe..73abc2a0a 100644 --- a/src/odbc_queries.erl +++ b/src/odbc_queries.erl @@ -56,7 +56,7 @@ 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, + del_privacy_lists/2, set_vcard/26, get_vcard/2, escape/1, count_records_where/3, get_roster_version/2, set_roster_version/2, opt_type/1]). @@ -151,33 +151,29 @@ get_password_scram(LServer, LUser) -> " from users" " where username=%(LUser)s")). -set_password_t(LServer, Username, Pass) -> - ejabberd_odbc:sql_transaction(LServer, - fun () -> - update_t(<<"users">>, - [<<"username">>, - <<"password">>], - [Username, Pass], - [<<"username='">>, Username, - <<"'">>]) - end). +set_password_t(LServer, LUser, Password) -> + ejabberd_odbc:sql_transaction( + LServer, + fun () -> + ?SQL_UPSERT_T( + "users", + ["!username=%(LUser)s", + "password=%(Password)s"]) + end). -set_password_scram_t(LServer, Username, +set_password_scram_t(LServer, LUser, StoredKey, ServerKey, Salt, IterationCount) -> - ejabberd_odbc:sql_transaction(LServer, - fun () -> - update_t(<<"users">>, - [<<"username">>, - <<"password">>, - <<"serverkey">>, - <<"salt">>, - <<"iterationcount">>], - [Username, StoredKey, - ServerKey, Salt, - IterationCount], - [<<"username='">>, Username, - <<"'">>]) - end). + ejabberd_odbc:sql_transaction( + LServer, + fun () -> + ?SQL_UPSERT_T( + "users", + ["!username=%(LUser)s", + "password=%(StoredKey)s", + "serverkey=%(ServerKey)s", + "salt=%(Salt)s", + "iterationcount=%(IterationCount)d"]) + end). add_user(LServer, Username, Pass) -> ejabberd_odbc:sql_query(LServer, @@ -428,12 +424,12 @@ get_subscription(LServer, LUser, SJID) -> ?SQL("select @(subscription)s from rosterusers " "where username=%(LUser)s and jid=%(SJID)s")). -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(_LServer, LUser, XMLNS, SData) -> + ?SQL_UPSERT_T( + "private_storage", + ["!username=%(LUser)s", + "!namespace=%(XMLNS)s", + "data=%(SData)s"]). set_private_data_sql(Username, LXMLNS, SData) -> [[<<"delete from private_storage where username='">>, @@ -443,22 +439,23 @@ set_private_data_sql(Username, LXMLNS, SData) -> 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, LUser, XMLNS) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("select @(data)s from private_storage" + " where username=%(LUser)s and namespace=%(XMLNS)s")). -get_private_data(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"select namespace, data from private_storage " - "where username='">>, Username, <<"';">>]). +get_private_data(LServer, LUser) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("select @(namespace)s, @(data)s from private_storage" + " where username=%(LUser)s")). -del_user_private_storage(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"delete from private_storage where username='">>, - Username, <<"';">>]). +del_user_private_storage(LServer, LUser) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("delete from private_storage" + " where username=%(LUser)s")). set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, SFamily, SGiven, SLBDay, SLCTRY, SLEMail, SLFN, @@ -508,122 +505,128 @@ get_vcard(LServer, Username) -> [<<"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(LServer, LUser) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("select @(name)s from privacy_default_list " + "where username=%(LUser)s")). -get_default_privacy_list_t(Username) -> - ejabberd_odbc:sql_query_t([<<"select name from privacy_default_list " - "where username='">>, - Username, <<"';">>]). +get_default_privacy_list_t(LUser) -> + ejabberd_odbc:sql_query_t( + ?SQL("select @(name)s from privacy_default_list " + "where username=%(LUser)s")). -get_privacy_list_names(LServer, Username) -> - ejabberd_odbc:sql_query(LServer, - [<<"select name from privacy_list where " - "username='">>, - Username, <<"';">>]). +get_privacy_list_names(LServer, LUser) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("select @(name)s from privacy_list" + " where username=%(LUser)s")). -get_privacy_list_names_t(Username) -> - ejabberd_odbc:sql_query_t([<<"select name from privacy_list where " - "username='">>, - Username, <<"';">>]). +get_privacy_list_names_t(LUser) -> + ejabberd_odbc:sql_query_t( + ?SQL("select @(name)s from privacy_list" + " where username=%(LUser)s")). -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(LServer, LUser, Name) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("select @(id)d from privacy_list" + " where username=%(LUser)s and name=%(Name)s")). -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_id_t(LUser, Name) -> + ejabberd_odbc:sql_query_t( + ?SQL("select @(id)d from privacy_list" + " where username=%(LUser)s and name=%(Name)s")). -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_t(Username, SName) -> - 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 = (select id from privacy_list " - "where username='">>, - Username, <<"' and name='">>, SName, - <<"') order by ord;">>]). +get_privacy_list_data(LServer, LUser, Name) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("select @(t)s, @(value)s, @(action)s, @(ord)d, @(match_all)b, " + "@(match_iq)b, @(match_message)b, @(match_presence_in)b, " + "@(match_presence_out)b from privacy_list_data " + "where id =" + " (select id from privacy_list" + " where username=%(LUser)s and name=%(Name)s) " + "order by ord")). + +%% Not used? +get_privacy_list_data_t(LUser, Name) -> + ejabberd_odbc:sql_query_t( + ?SQL("select @(t)s, @(value)s, @(action)s, @(ord)d, @(match_all)b, " + "@(match_iq)b, @(match_message)b, @(match_presence_in)b, " + "@(match_presence_out)b from privacy_list_data " + "where id =" + " (select id from privacy_list" + " where username=%(LUser)s and name=%(Name)s) " + "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;">>]). + ejabberd_odbc:sql_query( + LServer, + ?SQL("select @(t)s, @(value)s, @(action)s, @(ord)d, @(match_all)b, " + "@(match_iq)b, @(match_message)b, @(match_presence_in)b, " + "@(match_presence_out)b from privacy_list_data " + "where id=%(ID)d 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, <<"';">>]). + ejabberd_odbc:sql_query_t( + ?SQL("select @(t)s, @(value)s, @(action)s, @(ord)d, @(match_all)b, " + "@(match_iq)b, @(match_message)b, @(match_presence_in)b, " + "@(match_presence_out)b from privacy_list_data " + "where id=%(ID)d order by ord")). + +set_default_privacy_list(LUser, Name) -> + ?SQL_UPSERT_T( + "privacy_default_list", + ["!username=%(LUser)s", + "name=%(Name)s"]). + +unset_default_privacy_list(LServer, LUser) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("delete from privacy_default_list" + " where username=%(LUser)s")). -remove_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"delete from privacy_list where username='">>, - Username, <<"' and name='">>, SName, <<"';">>]). +remove_privacy_list(LUser, Name) -> + ejabberd_odbc:sql_query_t( + ?SQL("delete from privacy_list where" + " username=%(LUser)s and name=%(Name)s")). -add_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t([<<"insert into privacy_list(username, name) " - "values ('">>, - Username, <<"', '">>, SName, <<"');">>]). +add_privacy_list(LUser, Name) -> + ejabberd_odbc:sql_query_t( + ?SQL("insert into privacy_list(username, name) " + "values (%(LUser)s, %(Name)s)")). 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, <<"', '">>), - <<"');">>]) + ejabberd_odbc:sql_query_t( + ?SQL("delete from privacy_list_data where id=%(ID)d")), + lists:foreach( + fun({SType, SValue, SAction, Order, MatchAll, MatchIQ, + MatchMessage, MatchPresenceIn, MatchPresenceOut}) -> + ejabberd_odbc:sql_query_t( + ?SQL("insert into privacy_list_data(id, t, " + "value, action, ord, match_all, match_iq, " + "match_message, match_presence_in, match_presence_out) " + "values (%(ID)d, %(SType)s, %(SValue)s, %(SAction)s," + " %(Order)d, %(MatchAll)b, %(MatchIQ)b," + " %(MatchMessage)b, %(MatchPresenceIn)b," + " %(MatchPresenceOut)b)")) 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='">>, - <>, - <<"';">>]), - ejabberd_odbc:sql_query(LServer, - [<<"delete from privacy_default_list where " - "username='">>, - Username, <<"';">>]). +del_privacy_lists(LServer, LUser) -> + ejabberd_odbc:sql_query( + LServer, + ?SQL("delete from privacy_list where username=%(LUser)s")), + %US = <>, + %ejabberd_odbc:sql_query( + % LServer, + % ?SQL("delete from privacy_list_data where value=%(US)s")), + ejabberd_odbc:sql_query( + LServer, + ?SQL("delete from privacy_default_list where username=%(LUser)s")). +%% Characters to escape escape($\000) -> <<"\\0">>; escape($\n) -> <<"\\n">>; escape($\t) -> <<"\\t">>; @@ -634,6 +637,7 @@ escape($") -> <<"\\\"">>; escape($\\) -> <<"\\\\">>; escape(C) -> <>. +%% Count number of records in a table given a where clause count_records_where(LServer, Table, WhereClause) -> ejabberd_odbc:sql_query(LServer, [<<"select count(*) from ">>, Table, <<" ">>, -- cgit v1.2.3