From 6e8895f9e9d9864709f41ca84669de54342bd881 Mon Sep 17 00:00:00 2001 From: Evgeniy Khramtsov Date: Tue, 23 May 2017 12:25:13 +0300 Subject: [PATCH] Get rid of sql_queries.erl --- src/ejabberd_auth_sql.erl | 142 ++++++++- src/ejabberd_sql.erl | 17 +- src/mod_last_sql.erl | 14 +- src/mod_offline_sql.erl | 53 ++-- src/mod_privacy_sql.erl | 88 +++++- src/mod_private_sql.erl | 30 +- src/mod_roster_sql.erl | 152 +++++++-- src/mod_vcard_sql.erl | 43 ++- src/sql_queries.erl | 640 -------------------------------------- 9 files changed, 438 insertions(+), 741 deletions(-) delete mode 100644 src/sql_queries.erl diff --git a/src/ejabberd_auth_sql.erl b/src/ejabberd_auth_sql.erl index d682634f0..0d7c7b375 100644 --- a/src/ejabberd_auth_sql.erl +++ b/src/ejabberd_auth_sql.erl @@ -30,11 +30,12 @@ -author('alexey@process-one.net'). -behaviour(ejabberd_auth). +-behaviour(ejabberd_config). -export([start/1, stop/1, set_password/3, try_register/3, get_users/2, count_users/2, get_password/2, remove_user/2, store_type/1, plain_password_required/1, - convert_to_scram/1]). + convert_to_scram/1, opt_type/1]). -include("ejabberd.hrl"). -include("logger.hrl"). @@ -56,15 +57,17 @@ store_type(Server) -> ejabberd_auth:password_format(Server). set_password(User, Server, Password) -> - Res = if is_record(Password, scram) -> - sql_queries:set_password_scram_t( - Server, User, - Password#scram.storedkey, Password#scram.serverkey, - Password#scram.salt, Password#scram.iterationcount); - true -> - sql_queries:set_password_t(Server, User, Password) - end, - case Res of + F = fun() -> + if is_record(Password, scram) -> + set_password_scram_t( + User, + Password#scram.storedkey, Password#scram.serverkey, + Password#scram.salt, Password#scram.iterationcount); + true -> + set_password_t(User, Password) + end + end, + case ejabberd_sql:sql_transaction(Server, F) of {atomic, _} -> ok; {aborted, Reason} -> @@ -74,12 +77,12 @@ set_password(User, Server, Password) -> try_register(User, Server, Password) -> Res = if is_record(Password, scram) -> - sql_queries:add_user_scram( + add_user_scram( Server, User, Password#scram.storedkey, Password#scram.serverkey, Password#scram.salt, Password#scram.iterationcount); true -> - sql_queries:add_user(Server, User, Password) + add_user(Server, User, Password) end, case Res of {updated, 1} -> ok; @@ -87,21 +90,21 @@ try_register(User, Server, Password) -> end. get_users(Server, Opts) -> - case sql_queries:list_users(Server, Opts) of + case list_users(Server, Opts) of {selected, Res} -> [{U, Server} || {U} <- Res]; _ -> [] end. count_users(Server, Opts) -> - case sql_queries:users_number(Server, Opts) of + case users_number(Server, Opts) of {selected, [{Res}]} -> Res; _Other -> 0 end. get_password(User, Server) -> - case sql_queries:get_password_scram(Server, User) of + case get_password_scram(Server, User) of {selected, [{Password, <<>>, <<>>, 0}]} -> {ok, Password}; {selected, [{StoredKey, ServerKey, Salt, IterationCount}]} -> @@ -118,7 +121,7 @@ get_password(User, Server) -> end. remove_user(User, Server) -> - case sql_queries:del_user(Server, User) of + case del_user(Server, User) of {updated, _} -> ok; Err -> @@ -139,6 +142,105 @@ set_password_scram_t(LUser, "salt=%(Salt)s", "iterationcount=%(IterationCount)d"]). +set_password_t(LUser, Password) -> + ?SQL_UPSERT_T( + "users", + ["!username=%(LUser)s", + "password=%(Password)s"]). + +get_password_scram(LServer, LUser) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(password)s, @(serverkey)s, @(salt)s, @(iterationcount)d" + " from users" + " where username=%(LUser)s")). + +add_user_scram(LServer, LUser, + StoredKey, ServerKey, Salt, IterationCount) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("insert into users(username, password, serverkey, salt, " + "iterationcount) " + "values (%(LUser)s, %(StoredKey)s, %(ServerKey)s," + " %(Salt)s, %(IterationCount)d)")). + +add_user(LServer, LUser, Password) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("insert into users(username, password) " + "values (%(LUser)s, %(Password)s)")). + +del_user(LServer, LUser) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("delete from users where username=%(LUser)s")). + +list_users(LServer, []) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(username)s 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_sql:sql_query( + LServer, + ?SQL("select @(username)s from users " + "order by username " + "limit %(Limit)d offset %(Offset)d")); +list_users(LServer, + [{prefix, Prefix}, {limit, Limit}, {offset, Offset}]) + when is_binary(Prefix) and is_integer(Limit) and + is_integer(Offset) -> + SPrefix = ejabberd_sql:escape_like_arg_circumflex(Prefix), + SPrefix2 = <>, + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(username)s from users " + "where username like %(SPrefix2)s escape '^' " + "order by username " + "limit %(Limit)d offset %(Offset)d")). + +users_number(LServer) -> + ejabberd_sql:sql_query( + LServer, + fun(pgsql, _) -> + case + ejabberd_config:get_option( + {pgsql_users_number_estimate, LServer}, false) of + true -> + ejabberd_sql:sql_query_t( + ?SQL("select @(reltuples :: bigint)d from pg_class" + " where oid = 'users'::regclass::oid")); + _ -> + ejabberd_sql:sql_query_t( + ?SQL("select @(count(*))d from users")) + end; + (_Type, _) -> + ejabberd_sql:sql_query_t( + ?SQL("select @(count(*))d from users")) + end). + +users_number(LServer, [{prefix, Prefix}]) + when is_binary(Prefix) -> + SPrefix = ejabberd_sql:escape_like_arg_circumflex(Prefix), + SPrefix2 = <>, + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(count(*))d from users " + "where username like %(SPrefix2)s escape '^'")); +users_number(LServer, []) -> + users_number(LServer). + convert_to_scram(Server) -> LServer = jid:nameprep(Server), if @@ -178,10 +280,16 @@ convert_to_scram(Server) -> Err -> {bad_reply, Err} end end, - case sql_queries:sql_transaction(LServer, F) of + case ejabberd_sql:sql_transaction(LServer, F) of {atomic, ok} -> ok; {atomic, continue} -> convert_to_scram(Server); {atomic, Error} -> {error, Error}; Error -> Error end end. + +-spec opt_type(pgsql_users_number_estimate) -> fun((boolean()) -> boolean()); + (atom()) -> [atom()]. +opt_type(pgsql_users_number_estimate) -> + fun (V) when is_boolean(V) -> V end; +opt_type(_) -> [pgsql_users_number_estimate]. diff --git a/src/ejabberd_sql.erl b/src/ejabberd_sql.erl index 3b252631e..35d970291 100644 --- a/src/ejabberd_sql.erl +++ b/src/ejabberd_sql.erl @@ -194,9 +194,20 @@ abort(Reason) -> restart(Reason) -> throw({aborted, Reason}). -%% Escape character that will confuse an SQL engine +-spec escape_char(char()) -> binary(). +escape_char($\000) -> <<"\\0">>; +escape_char($\n) -> <<"\\n">>; +escape_char($\t) -> <<"\\t">>; +escape_char($\b) -> <<"\\b">>; +escape_char($\r) -> <<"\\r">>; +escape_char($') -> <<"''">>; +escape_char($") -> <<"\\\"">>; +escape_char($\\) -> <<"\\\\">>; +escape_char(C) -> <>. + +-spec escape(binary()) -> binary(). escape(S) -> - << <<(sql_queries:escape(Char))/binary>> || <> <= S >>. + << <<(escape_char(Char))/binary>> || <> <= S >>. %% Escape character that will confuse an SQL engine %% Percent and underscore only need to be escaped for pattern matching like @@ -206,7 +217,7 @@ escape_like(S) when is_binary(S) -> escape_like($%) -> <<"\\%">>; escape_like($_) -> <<"\\_">>; escape_like($\\) -> <<"\\\\\\\\">>; -escape_like(C) when is_integer(C), C >= 0, C =< 255 -> sql_queries:escape(C). +escape_like(C) when is_integer(C), C >= 0, C =< 255 -> escape_char(C). escape_like_arg(S) when is_binary(S) -> << <<(escape_like_arg(C))/binary>> || <> <= S >>; diff --git a/src/mod_last_sql.erl b/src/mod_last_sql.erl index 2e3c3dd15..b777ba30d 100644 --- a/src/mod_last_sql.erl +++ b/src/mod_last_sql.erl @@ -43,7 +43,10 @@ init(_Host, _Opts) -> ok. get_last(LUser, LServer) -> - case catch sql_queries:get_last(LServer, LUser) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(seconds)d, @(state)s from last" + " where username=%(LUser)s")) of {selected, []} -> error; {selected, [{TimeStamp, Status}]} -> @@ -55,7 +58,10 @@ get_last(LUser, LServer) -> end. store_last_info(LUser, LServer, TimeStamp, Status) -> - case sql_queries:set_last_t(LServer, LUser, TimeStamp, Status) of + case ?SQL_UPSERT(LServer, "last", + ["!username=%(LUser)s", + "seconds=%(TimeStamp)d", + "state=%(Status)s"]) of ok -> ok; Err -> @@ -65,7 +71,9 @@ store_last_info(LUser, LServer, TimeStamp, Status) -> end. remove_user(LUser, LServer) -> - sql_queries:del_last(LServer, LUser). + ejabberd_sql:sql_query( + LServer, + ?SQL("delete from last where username=%(LUser)s")). export(_Server) -> [{last_activity, diff --git a/src/mod_offline_sql.erl b/src/mod_offline_sql.erl index 48b32be81..bcf5b7aad 100644 --- a/src/mod_offline_sql.erl +++ b/src/mod_offline_sql.erl @@ -54,7 +54,10 @@ store_message(#offline_msg{us = {LUser, LServer}} = M) -> <<"Offline Storage">>), XML = fxml:element_to_binary( xmpp:encode(NewPacket)), - case sql_queries:add_spool(LUser, LServer, XML) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("insert into spool(username, xml) values " + "(%(LUser)s, %(XML)s)")) of {updated, _} -> ok; _ -> @@ -62,7 +65,7 @@ store_message(#offline_msg{us = {LUser, LServer}} = M) -> end. pop_messages(LUser, LServer) -> - case sql_queries:get_and_del_spool_msg_t(LServer, LUser) of + case get_and_del_spool_msg_t(LServer, LUser) of {atomic, {selected, Rs}} -> {ok, lists:flatmap( fun({_, XML}) -> @@ -82,12 +85,12 @@ remove_expired_messages(_LServer) -> {atomic, ok}. remove_old_messages(Days, LServer) -> - case catch ejabberd_sql:sql_query( - LServer, - [<<"DELETE FROM spool" - " WHERE created_at < " - "NOW() - INTERVAL '">>, - integer_to_list(Days), <<"' DAY;">>]) of + case ejabberd_sql:sql_query( + LServer, + [<<"DELETE FROM spool" + " WHERE created_at < " + "NOW() - INTERVAL '">>, + integer_to_list(Days), <<"' DAY;">>]) of {updated, N} -> ?INFO_MSG("~p message(s) deleted from offline spool", [N]); _Error -> @@ -96,13 +99,15 @@ remove_old_messages(Days, LServer) -> {atomic, ok}. remove_user(LUser, LServer) -> - sql_queries:del_spool_msg(LServer, LUser). + ejabberd_sql:sql_query( + LServer, + ?SQL("delete from spool where username=%(LUser)s")). read_message_headers(LUser, LServer) -> - case catch ejabberd_sql:sql_query( - LServer, - ?SQL("select @(xml)s, @(seq)d from spool" - " where username=%(LUser)s order by seq")) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(xml)s, @(seq)d from spool" + " where username=%(LUser)s order by seq")) of {selected, Rows} -> lists:flatmap( fun({XML, Seq}) -> @@ -144,10 +149,10 @@ remove_message(LUser, LServer, Seq) -> ok. read_all_messages(LUser, LServer) -> - case catch ejabberd_sql:sql_query( - LServer, - ?SQL("select @(xml)s from spool where " - "username=%(LUser)s order by seq")) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(xml)s from spool where " + "username=%(LUser)s order by seq")) of {selected, Rs} -> lists:flatmap( fun({XML}) -> @@ -161,7 +166,7 @@ read_all_messages(LUser, LServer) -> end. remove_all_messages(LUser, LServer) -> - sql_queries:del_spool_msg(LServer, LUser), + remove_user(LUser, LServer), {atomic, ok}. count_messages(LUser, LServer) -> @@ -232,3 +237,15 @@ el_to_offline_msg(El) -> ?ERROR_MSG("failed to get 'from' JID from offline XML ~p", [El]), {error, bad_jid_from} end. + +get_and_del_spool_msg_t(LServer, LUser) -> + F = fun () -> + Result = + ejabberd_sql:sql_query_t( + ?SQL("select @(username)s, @(xml)s from spool where " + "username=%(LUser)s order by seq;")), + ejabberd_sql:sql_query_t( + ?SQL("delete from spool where username=%(LUser)s;")), + Result + end, + ejabberd_sql:sql_transaction(LServer, F). diff --git a/src/mod_privacy_sql.erl b/src/mod_privacy_sql.erl index 45c86e12c..b19c95fe5 100644 --- a/src/mod_privacy_sql.erl +++ b/src/mod_privacy_sql.erl @@ -74,12 +74,12 @@ remove_list(LUser, LServer, Name) -> F = fun () -> case get_default_privacy_list_t(LUser) of {selected, []} -> - remove_privacy_list(LUser, Name); + remove_privacy_list_t(LUser, Name); {selected, [{Default}]} -> if Name == Default -> {error, conflict}; true -> - remove_privacy_list(LUser, Name) + remove_privacy_list_t(LUser, Name) end end end, @@ -309,47 +309,101 @@ item_to_raw(#listitem{type = Type, value = Value, MatchMessage, MatchPresenceIn, MatchPresenceOut}. get_default_privacy_list(LUser, LServer) -> - sql_queries:get_default_privacy_list(LServer, LUser). + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(name)s from privacy_default_list " + "where username=%(LUser)s")). get_default_privacy_list_t(LUser) -> - sql_queries:get_default_privacy_list_t(LUser). + ejabberd_sql:sql_query_t( + ?SQL("select @(name)s from privacy_default_list " + "where username=%(LUser)s")). get_privacy_list_names(LUser, LServer) -> - sql_queries:get_privacy_list_names(LServer, LUser). + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(name)s from privacy_list" + " where username=%(LUser)s")). get_privacy_list_names_t(LUser) -> - sql_queries:get_privacy_list_names_t(LUser). + ejabberd_sql:sql_query_t( + ?SQL("select @(name)s from privacy_list" + " where username=%(LUser)s")). get_privacy_list_id_t(LUser, Name) -> - sql_queries:get_privacy_list_id_t(LUser, Name). + ejabberd_sql:sql_query_t( + ?SQL("select @(id)d from privacy_list" + " where username=%(LUser)s and name=%(Name)s")). get_privacy_list_data(LUser, LServer, Name) -> - sql_queries:get_privacy_list_data(LServer, LUser, Name). + ejabberd_sql: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")). set_default_privacy_list(LUser, Name) -> - sql_queries:set_default_privacy_list(LUser, Name). + ?SQL_UPSERT_T( + "privacy_default_list", + ["!username=%(LUser)s", + "name=%(Name)s"]). unset_default_privacy_list(LUser, LServer) -> - case sql_queries:unset_default_privacy_list(LServer, LUser) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("delete from privacy_default_list" + " where username=%(LUser)s")) of {updated, _} -> ok; Err -> Err end. -remove_privacy_list(LUser, Name) -> - case sql_queries:remove_privacy_list(LUser, Name) of +remove_privacy_list_t(LUser, Name) -> + case ejabberd_sql:sql_query_t( + ?SQL("delete from privacy_list where" + " username=%(LUser)s and name=%(Name)s")) of {updated, 0} -> {error, notfound}; {updated, _} -> ok; Err -> Err end. add_privacy_list(LUser, Name) -> - sql_queries:add_privacy_list(LUser, Name). + ejabberd_sql:sql_query_t( + ?SQL("insert into privacy_list(username, name) " + "values (%(LUser)s, %(Name)s)")). set_privacy_list(ID, RItems) -> - sql_queries:set_privacy_list(ID, RItems). + ejabberd_sql: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_sql: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(LUser, LServer) -> - case sql_queries:del_privacy_lists(LServer, LUser) of - {updated, _} -> ok; - Err -> Err + case ejabberd_sql:sql_query( + LServer, + ?SQL("delete from privacy_list where username=%(LUser)s")) of + {updated, _} -> + case ejabberd_sql:sql_query( + LServer, + ?SQL("delete from privacy_default_list " + "where username=%(LUser)s")) of + {updated, _} -> ok; + Err -> Err + end; + Err -> + Err end. diff --git a/src/mod_private_sql.erl b/src/mod_private_sql.erl index d7e4b3aef..907eeaf3a 100644 --- a/src/mod_private_sql.erl +++ b/src/mod_private_sql.erl @@ -23,7 +23,7 @@ %%%---------------------------------------------------------------------- -module(mod_private_sql). - +-compile([{parse_transform, ejabberd_sql_pt}]). -behaviour(mod_private). %% API @@ -32,6 +32,7 @@ -include("xmpp.hrl"). -include("mod_private.hrl"). +-include("ejabberd_sql_pt.hrl"). -include("logger.hrl"). %%%=================================================================== @@ -45,8 +46,11 @@ set_data(LUser, LServer, Data) -> lists:foreach( fun({XMLNS, El}) -> SData = fxml:element_to_binary(El), - sql_queries:set_private_data( - LServer, LUser, XMLNS, SData) + ?SQL_UPSERT_T( + "private_storage", + ["!username=%(LUser)s", + "!namespace=%(XMLNS)s", + "data=%(SData)s"]) end, Data) end, case ejabberd_sql:sql_transaction(LServer, F) of @@ -57,7 +61,10 @@ set_data(LUser, LServer, Data) -> end. get_data(LUser, LServer, XMLNS) -> - case sql_queries:get_private_data(LServer, LUser, XMLNS) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(data)s from private_storage" + " where username=%(LUser)s and namespace=%(XMLNS)s")) of {selected, [{SData}]} -> parse_element(LUser, LServer, SData); {selected, []} -> @@ -67,7 +74,10 @@ get_data(LUser, LServer, XMLNS) -> end. get_all_data(LUser, LServer) -> - case catch sql_queries:get_private_data(LServer, LUser) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(namespace)s, @(data)s from private_storage" + " where username=%(LUser)s")) of {selected, []} -> error; {selected, Res} -> @@ -83,7 +93,9 @@ get_all_data(LUser, LServer) -> end. del_data(LUser, LServer) -> - case sql_queries:del_user_private_storage(LServer, LUser) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("delete from private_storage where username=%(LUser)s")) of {updated, _} -> ok; _ -> @@ -96,7 +108,11 @@ export(_Server) -> xml = Data}) when LServer == Host -> SData = fxml:element_to_binary(Data), - sql_queries:set_private_data_sql(LUser, XMLNS, SData); + [?SQL("delete from private_storage where" + " username=%(LUser)s and namespace=%(XMLNS)s;"), + ?SQL("insert into private_storage(username, " + "namespace, data) values (" + "%(LUser)s, %(XMLNS)s, %(SData)s);")]; (_Host, _R) -> [] end}]. diff --git a/src/mod_roster_sql.erl b/src/mod_roster_sql.erl index 7c516568c..77899624a 100644 --- a/src/mod_roster_sql.erl +++ b/src/mod_roster_sql.erl @@ -46,27 +46,32 @@ init(_Host, _Opts) -> ok. read_roster_version(LUser, LServer) -> - case sql_queries:get_roster_version(LServer, LUser) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(version)s from roster_version" + " where username = %(LUser)s")) of {selected, [{Version}]} -> {ok, Version}; - {selected, []} -> error + {selected, []} -> error; + _ -> {error, db_failure} end. write_roster_version(LUser, LServer, InTransaction, Ver) -> if InTransaction -> - sql_queries:set_roster_version(LUser, Ver); + set_roster_version(LUser, Ver); true -> - sql_queries:sql_transaction( + transaction( LServer, - fun () -> - sql_queries:set_roster_version(LUser, Ver) - end) + fun () -> set_roster_version(LUser, Ver) end) end. get_roster(LUser, LServer) -> - case catch sql_queries:get_roster(LServer, LUser) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(username)s, @(jid)s, @(nick)s, @(subscription)s, " + "@(ask)s, @(askmessage)s, @(server)s, @(subscribe)s, " + "@(type)s from rosterusers where username=%(LUser)s")) of {selected, Items} when is_list(Items) -> - JIDGroups = case catch sql_queries:get_roster_jid_groups( - LServer, LUser) of + JIDGroups = case get_roster_jid_groups(LServer, LUser) of {selected, JGrps} when is_list(JGrps) -> JGrps; _ -> @@ -96,25 +101,24 @@ get_roster(LUser, LServer) -> roster_subscribe(_LUser, _LServer, _LJID, Item) -> ItemVals = record_to_row(Item), - sql_queries:roster_subscribe(ItemVals). + roster_subscribe(ItemVals). transaction(LServer, F) -> ejabberd_sql:sql_transaction(LServer, F). get_roster_item(LUser, LServer, LJID) -> SJID = jid:encode(LJID), - case sql_queries:get_roster_by_jid(LServer, LUser, SJID) of + case get_roster_by_jid(LServer, LUser, SJID) of {selected, [I]} -> case raw_to_record(LServer, I) of error -> error; R -> - Groups = - case sql_queries:get_roster_groups(LServer, LUser, SJID) of - {selected, JGrps} when is_list(JGrps) -> - [JGrp || {JGrp} <- JGrps]; - _ -> [] - end, + Groups = case get_roster_groups(LServer, LUser, SJID) of + {selected, JGrps} when is_list(JGrps) -> + [JGrp || {JGrp} <- JGrps]; + _ -> [] + end, {ok, R#roster{groups = Groups}} end; {selected, []} -> @@ -122,23 +126,44 @@ get_roster_item(LUser, LServer, LJID) -> end. remove_user(LUser, LServer) -> - sql_queries:del_user_roster_t(LServer, LUser), + transaction( + LServer, + fun () -> + ejabberd_sql:sql_query_t( + ?SQL("delete from rosterusers where username=%(LUser)s")), + ejabberd_sql:sql_query_t( + ?SQL("delete from rostergroups where username=%(LUser)s")) + end), ok. -update_roster(LUser, LServer, LJID, Item) -> +update_roster(LUser, _LServer, LJID, Item) -> SJID = jid:encode(LJID), ItemVals = record_to_row(Item), ItemGroups = Item#roster.groups, - sql_queries:update_roster(LServer, LUser, SJID, ItemVals, - ItemGroups). + roster_subscribe(ItemVals), + ejabberd_sql:sql_query_t( + ?SQL("delete from rostergroups" + " where username=%(LUser)s and jid=%(SJID)s")), + lists:foreach( + fun(ItemGroup) -> + ejabberd_sql:sql_query_t( + ?SQL("insert into rostergroups(username, jid, grp) " + "values (%(LUser)s, %(SJID)s, %(ItemGroup)s)")) + end, + ItemGroups). -del_roster(LUser, LServer, LJID) -> +del_roster(LUser, _LServer, LJID) -> SJID = jid:encode(LJID), - sql_queries:del_roster(LServer, LUser, SJID). + ejabberd_sql:sql_query_t( + ?SQL("delete from rosterusers" + " where username=%(LUser)s and jid=%(SJID)s")), + ejabberd_sql:sql_query_t( + ?SQL("delete from rostergroups" + " where username=%(LUser)s and jid=%(SJID)s")). read_subscription_and_groups(LUser, LServer, LJID) -> SJID = jid:encode(LJID), - case catch sql_queries:get_subscription(LServer, LUser, SJID) of + case get_subscription(LServer, LUser, SJID) of {selected, [{SSubscription}]} -> Subscription = case SSubscription of <<"B">> -> both; @@ -152,8 +177,7 @@ read_subscription_and_groups(LUser, LServer, LJID) -> {subscription, SSubscription})]), none end, - Groups = case catch sql_queries:get_rostergroup_by_jid( - LServer, LUser, SJID) of + Groups = case get_rostergroup_by_jid(LServer, LUser, SJID) of {selected, JGrps} when is_list(JGrps) -> [JGrp || {JGrp} <- JGrps]; _ -> [] @@ -169,7 +193,7 @@ export(_Server) -> when LServer == Host -> ItemVals = record_to_row(R), ItemGroups = R#roster.groups, - sql_queries:update_roster_sql(ItemVals, ItemGroups); + update_roster_sql(ItemVals, ItemGroups); (_Host, _R) -> [] end}, @@ -189,6 +213,78 @@ import(_, _, _) -> %%%=================================================================== %%% Internal functions %%%=================================================================== +set_roster_version(LUser, Version) -> + ?SQL_UPSERT_T( + "roster_version", + ["!username=%(LUser)s", + "version=%(Version)s"]). + +get_roster_jid_groups(LServer, LUser) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(jid)s, @(grp)s from rostergroups where " + "username=%(LUser)s")). + +get_roster_groups(_LServer, LUser, SJID) -> + ejabberd_sql:sql_query_t( + ?SQL("select @(grp)s from rostergroups" + " where username=%(LUser)s and jid=%(SJID)s")). + +roster_subscribe({LUser, SJID, Name, SSubscription, SAsk, AskMessage}) -> + ?SQL_UPSERT_T( + "rosterusers", + ["!username=%(LUser)s", + "!jid=%(SJID)s", + "nick=%(Name)s", + "subscription=%(SSubscription)s", + "ask=%(SAsk)s", + "askmessage=%(AskMessage)s", + "server='N'", + "subscribe=''", + "type='item'"]). + +get_roster_by_jid(_LServer, LUser, SJID) -> + ejabberd_sql:sql_query_t( + ?SQL("select @(username)s, @(jid)s, @(nick)s, @(subscription)s," + " @(ask)s, @(askmessage)s, @(server)s, @(subscribe)s," + " @(type)s from rosterusers" + " where username=%(LUser)s and jid=%(SJID)s")). + +get_rostergroup_by_jid(LServer, LUser, SJID) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(grp)s from rostergroups" + " where username=%(LUser)s and jid=%(SJID)s")). + +get_subscription(LServer, LUser, SJID) -> + ejabberd_sql:sql_query( + LServer, + ?SQL("select @(subscription)s from rosterusers " + "where username=%(LUser)s and jid=%(SJID)s")). + +update_roster_sql({LUser, SJID, Name, SSubscription, SAsk, AskMessage}, + ItemGroups) -> + [?SQL("delete from rosterusers where" + " username=%(LUser)s and jid=%(SJID)s;"), + ?SQL("insert into rosterusers(" + " username, jid, nick," + " subscription, ask, askmessage," + " server, subscribe, type) " + "values (" + "%(LUser)s, " + "%(SJID)s, " + "%(Name)s, " + "%(SSubscription)s, " + "%(SAsk)s, " + "%(AskMessage)s, " + "'N', '', 'item');"), + ?SQL("delete from rostergroups where" + " username=%(LUser)s and jid=%(SJID)s;")] + ++ + [?SQL("insert into rostergroups(username, jid, grp) " + "values (%(LUser)s, %(SJID)s, %(ItemGroup)s);") + || ItemGroup <- ItemGroups]. + raw_to_record(LServer, [User, SJID, Nick, SSubscription, SAsk, SAskMessage, _SServer, _SSubscribe, _SType]) -> diff --git a/src/mod_vcard_sql.erl b/src/mod_vcard_sql.erl index 3b38536ea..fd1d05478 100644 --- a/src/mod_vcard_sql.erl +++ b/src/mod_vcard_sql.erl @@ -51,7 +51,9 @@ is_search_supported(_LServer) -> true. get_vcard(LUser, LServer) -> - case catch sql_queries:get_vcard(LServer, LUser) of + case ejabberd_sql:sql_query( + LServer, + ?SQL("select @(vcard)s from vcard where username=%(LUser)s")) of {selected, [{SVCARD}]} -> case fxml_stream:parse_element(SVCARD) of {error, _Reason} -> error; @@ -86,13 +88,38 @@ set_vcard(LUser, LServer, VCARD, orgunit = OrgUnit, lorgunit = LOrgUnit}) -> SVCARD = fxml:element_to_binary(VCARD), - sql_queries:set_vcard(LServer, LUser, BDay, CTRY, - EMail, FN, Family, Given, LBDay, - LCTRY, LEMail, LFN, LFamily, - LGiven, LLocality, LMiddle, - LNickname, LOrgName, LOrgUnit, - Locality, Middle, Nickname, OrgName, - OrgUnit, SVCARD, User). + ejabberd_sql:sql_transaction( + LServer, + fun() -> + ?SQL_UPSERT(LServer, "vcard", + ["!username=%(LUser)s", + "vcard=%(SVCARD)s"]), + ?SQL_UPSERT(LServer, "vcard_search", + ["username=%(User)s", + "!lusername=%(LUser)s", + "fn=%(FN)s", + "lfn=%(LFN)s", + "family=%(Family)s", + "lfamily=%(LFamily)s", + "given=%(Given)s", + "lgiven=%(LGiven)s", + "middle=%(Middle)s", + "lmiddle=%(LMiddle)s", + "nickname=%(Nickname)s", + "lnickname=%(LNickname)s", + "bday=%(BDay)s", + "lbday=%(LBDay)s", + "ctry=%(CTRY)s", + "lctry=%(LCTRY)s", + "locality=%(Locality)s", + "llocality=%(LLocality)s", + "email=%(EMail)s", + "lemail=%(LEMail)s", + "orgname=%(OrgName)s", + "lorgname=%(LOrgName)s", + "orgunit=%(OrgUnit)s", + "lorgunit=%(LOrgUnit)s"]) + end). search(LServer, Data, AllowReturnAll, MaxMatch) -> MatchSpec = make_matchspec(LServer, Data), diff --git a/src/sql_queries.erl b/src/sql_queries.erl deleted file mode 100644 index 0cf595bdf..000000000 --- a/src/sql_queries.erl +++ /dev/null @@ -1,640 +0,0 @@ -%%%---------------------------------------------------------------------- -%%% File : sql_queries.erl -%%% Author : Mickael Remond -%%% Purpose : ODBC queries dependind on back-end -%%% Created : by Mickael Remond -%%% -%%% -%%% ejabberd, Copyright (C) 2002-2017 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., -%%% 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. -%%% -%%%---------------------------------------------------------------------- - --module(sql_queries). - --compile([{parse_transform, ejabberd_sql_pt}]). - --behaviour(ejabberd_config). - --author("mremond@process-one.net"). - --export([get_db_type/0, update/5, update_t/4, - sql_transaction/2, get_last/2, set_last_t/4, del_last/2, - get_password/2, get_password_scram/2, set_password_t/3, - set_password_scram_t/6, add_user/3, add_user_scram/6, - del_user/2, del_user_return_password/3, list_users/1, - list_users/2, users_number/1, users_number/2, - add_spool/3, 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/2, roster_subscribe/1, - 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_t/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/2, set_vcard/26, get_vcard/2, - escape/1, count_records_where/3, get_roster_version/2, - set_roster_version/2, opt_type/1]). - --include("ejabberd.hrl"). --include("logger.hrl"). --include("ejabberd_sql_pt.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]]. - -get_db_type() -> generic. - -%% Safe atomic update. -update_t(Table, Fields, Vals, Where) -> - UPairs = lists:zipwith(fun (A, B) -> - <> - end, - Fields, Vals), - case ejabberd_sql:sql_query_t([<<"update ">>, Table, - <<" set ">>, join(UPairs, <<", ">>), - <<" where ">>, Where, <<";">>]) - of - {updated, 1} -> ok; - _ -> - Res = ejabberd_sql:sql_query_t([<<"insert into ">>, Table, - <<"(">>, join(Fields, <<", ">>), - <<") values ('">>, join(Vals, <<"', '">>), - <<"');">>]), - case Res of - {updated,1} -> ok; - _ -> Res - end - end. - -update(LServer, Table, Fields, Vals, Where) -> - UPairs = lists:zipwith(fun (A, B) -> - <> - end, - Fields, Vals), - case ejabberd_sql:sql_query(LServer, - [<<"update ">>, Table, <<" set ">>, - join(UPairs, <<", ">>), <<" where ">>, Where, - <<";">>]) - of - {updated, 1} -> ok; - _ -> - Res = ejabberd_sql:sql_query(LServer, - [<<"insert into ">>, Table, <<"(">>, - join(Fields, <<", ">>), <<") values ('">>, - join(Vals, <<"', '">>), <<"');">>]), - case Res of - {updated,1} -> ok; - _ -> Res - end - 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_sql module to this one (sql_queries) -sql_transaction(LServer, F) -> - ejabberd_sql:sql_transaction(LServer, F). - -get_last(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(seconds)d, @(state)s from last" - " where username=%(LUser)s")). - -set_last_t(LServer, LUser, TimeStamp, Status) -> - ?SQL_UPSERT(LServer, "last", - ["!username=%(LUser)s", - "seconds=%(TimeStamp)d", - "state=%(Status)s"]). - -del_last(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("delete from last where username=%(LUser)s")). - -get_password(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(password)s from users where username=%(LUser)s")). - -get_password_scram(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(password)s, @(serverkey)s, @(salt)s, @(iterationcount)d" - " from users" - " where username=%(LUser)s")). - -set_password_t(LServer, LUser, Password) -> - ejabberd_sql:sql_transaction( - LServer, - fun () -> - ?SQL_UPSERT_T( - "users", - ["!username=%(LUser)s", - "password=%(Password)s"]) - end). - -set_password_scram_t(LServer, LUser, - StoredKey, ServerKey, Salt, IterationCount) -> - ejabberd_sql: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, LUser, Password) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("insert into users(username, password) " - "values (%(LUser)s, %(Password)s)")). - -add_user_scram(LServer, LUser, - StoredKey, ServerKey, Salt, IterationCount) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("insert into users(username, password, serverkey, salt, " - "iterationcount) " - "values (%(LUser)s, %(StoredKey)s, %(ServerKey)s," - " %(Salt)s, %(IterationCount)d)")). - -del_user(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("delete from users where username=%(LUser)s")). - -del_user_return_password(_LServer, LUser, Password) -> - P = - ejabberd_sql:sql_query_t( - ?SQL("select @(password)s from users where username=%(LUser)s")), - ejabberd_sql:sql_query_t( - ?SQL("delete from users" - " where username=%(LUser)s and password=%(Password)s")), - P. - -list_users(LServer) -> - list_users(LServer, []). - -list_users(LServer, []) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(username)s 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_sql:sql_query( - LServer, - ?SQL("select @(username)s from users " - "order by username " - "limit %(Limit)d offset %(Offset)d")); -list_users(LServer, - [{prefix, Prefix}, {limit, Limit}, {offset, Offset}]) - when is_binary(Prefix) and is_integer(Limit) and - is_integer(Offset) -> - SPrefix = ejabberd_sql:escape_like_arg_circumflex(Prefix), - SPrefix2 = <>, - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(username)s from users " - "where username like %(SPrefix2)s escape '^' " - "order by username " - "limit %(Limit)d offset %(Offset)d")). - -users_number(LServer) -> - ejabberd_sql:sql_query( - LServer, - fun(pgsql, _) -> - case - ejabberd_config:get_option( - {pgsql_users_number_estimate, LServer}, false) of - true -> - ejabberd_sql:sql_query_t( - ?SQL("select @(reltuples :: bigint)d from pg_class" - " where oid = 'users'::regclass::oid")); - _ -> - ejabberd_sql:sql_query_t( - ?SQL("select @(count(*))d from users")) - end; - (_Type, _) -> - ejabberd_sql:sql_query_t( - ?SQL("select @(count(*))d from users")) - end). - -users_number(LServer, [{prefix, Prefix}]) - when is_binary(Prefix) -> - SPrefix = ejabberd_sql:escape_like_arg_circumflex(Prefix), - SPrefix2 = <>, - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(count(*))d from users " - "where username like %(SPrefix2)s escape '^'")); -users_number(LServer, []) -> - users_number(LServer). - -add_spool(LUser, LServer, XML) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("insert into spool(username, xml) values (%(LUser)s, %(XML)s)")). - -get_and_del_spool_msg_t(LServer, LUser) -> - F = fun () -> - Result = - ejabberd_sql:sql_query_t( - ?SQL("select @(username)s, @(xml)s from spool where " - "username=%(LUser)s order by seq;")), - ejabberd_sql:sql_query_t( - ?SQL("delete from spool where username=%(LUser)s;")), - Result - end, - ejabberd_sql:sql_transaction(LServer, F). - -del_spool_msg(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("delete from spool where username=%(LUser)s")). - -get_roster(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(username)s, @(jid)s, @(nick)s, @(subscription)s, " - "@(ask)s, @(askmessage)s, @(server)s, @(subscribe)s, " - "@(type)s from rosterusers where username=%(LUser)s")). - -get_roster_jid_groups(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(jid)s, @(grp)s from rostergroups where " - "username=%(LUser)s")). - -get_roster_groups(_LServer, LUser, SJID) -> - ejabberd_sql:sql_query_t( - ?SQL("select @(grp)s from rostergroups" - " where username=%(LUser)s and jid=%(SJID)s")). - -del_user_roster_t(LServer, LUser) -> - ejabberd_sql:sql_transaction( - LServer, - fun () -> - ejabberd_sql:sql_query_t( - ?SQL("delete from rosterusers where username=%(LUser)s")), - ejabberd_sql:sql_query_t( - ?SQL("delete from rostergroups where username=%(LUser)s")) - end). - -get_roster_by_jid(_LServer, LUser, SJID) -> - ejabberd_sql:sql_query_t( - ?SQL("select @(username)s, @(jid)s, @(nick)s, @(subscription)s," - " @(ask)s, @(askmessage)s, @(server)s, @(subscribe)s," - " @(type)s from rosterusers" - " where username=%(LUser)s and jid=%(SJID)s")). - -get_rostergroup_by_jid(LServer, LUser, SJID) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(grp)s from rostergroups" - " where username=%(LUser)s and jid=%(SJID)s")). - -del_roster(_LServer, LUser, SJID) -> - ejabberd_sql:sql_query_t( - ?SQL("delete from rosterusers" - " where username=%(LUser)s and jid=%(SJID)s")), - ejabberd_sql:sql_query_t( - ?SQL("delete from rostergroups" - " where username=%(LUser)s and jid=%(SJID)s")). - -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, LUser, SJID, ItemVals, - ItemGroups) -> - roster_subscribe(ItemVals), - ejabberd_sql:sql_query_t( - ?SQL("delete from rostergroups" - " where username=%(LUser)s and jid=%(SJID)s")), - lists:foreach( - fun(ItemGroup) -> - ejabberd_sql:sql_query_t( - ?SQL("insert into rostergroups(username, jid, grp) " - "values (%(LUser)s, %(SJID)s, %(ItemGroup)s)")) - end, - ItemGroups). - -update_roster_sql({LUser, SJID, Name, SSubscription, SAsk, AskMessage}, - ItemGroups) -> - [?SQL("delete from rosterusers where" - " username=%(LUser)s and jid=%(SJID)s;"), - ?SQL("insert into rosterusers(" - " username, jid, nick," - " subscription, ask, askmessage," - " server, subscribe, type) " - "values (" - "%(LUser)s, " - "%(SJID)s, " - "%(Name)s, " - "%(SSubscription)s, " - "%(SAsk)s, " - "%(AskMessage)s, " - "'N', '', 'item');"), - ?SQL("delete from rostergroups where" - " username=%(LUser)s and jid=%(SJID)s;")] - ++ - [?SQL("insert into rostergroups(username, jid, grp) " - "values (%(LUser)s, %(SJID)s, %(ItemGroup)s);") - || ItemGroup <- ItemGroups]. - -roster_subscribe({LUser, SJID, Name, SSubscription, SAsk, AskMessage}) -> - ?SQL_UPSERT_T( - "rosterusers", - ["!username=%(LUser)s", - "!jid=%(SJID)s", - "nick=%(Name)s", - "subscription=%(SSubscription)s", - "ask=%(SAsk)s", - "askmessage=%(AskMessage)s", - "server='N'", - "subscribe=''", - "type='item'"]). - -get_subscription(LServer, LUser, SJID) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(subscription)s from rosterusers " - "where username=%(LUser)s and jid=%(SJID)s")). - -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(LUser, XMLNS, SData) -> - [?SQL("delete from private_storage where" - " username=%(LUser)s and namespace=%(XMLNS)s;"), - ?SQL("insert into private_storage(username, " - "namespace, data) values (" - "%(LUser)s, %(XMLNS)s, %(SData)s);")]. - -get_private_data(LServer, LUser, XMLNS) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(data)s from private_storage" - " where username=%(LUser)s and namespace=%(XMLNS)s")). - -get_private_data(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(namespace)s, @(data)s from private_storage" - " where username=%(LUser)s")). - -del_user_private_storage(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("delete from private_storage" - " where username=%(LUser)s")). - -set_vcard(LServer, LUser, BDay, CTRY, EMail, FN, - Family, Given, LBDay, LCTRY, LEMail, LFN, - LFamily, LGiven, LLocality, LMiddle, LNickname, - LOrgName, LOrgUnit, Locality, Middle, Nickname, - OrgName, OrgUnit, SVCARD, User) -> - ejabberd_sql:sql_transaction( - LServer, - fun() -> - ?SQL_UPSERT(LServer, "vcard", - ["!username=%(LUser)s", - "vcard=%(SVCARD)s"]), - ?SQL_UPSERT(LServer, "vcard_search", - ["username=%(User)s", - "!lusername=%(LUser)s", - "fn=%(FN)s", - "lfn=%(LFN)s", - "family=%(Family)s", - "lfamily=%(LFamily)s", - "given=%(Given)s", - "lgiven=%(LGiven)s", - "middle=%(Middle)s", - "lmiddle=%(LMiddle)s", - "nickname=%(Nickname)s", - "lnickname=%(LNickname)s", - "bday=%(BDay)s", - "lbday=%(LBDay)s", - "ctry=%(CTRY)s", - "lctry=%(LCTRY)s", - "locality=%(Locality)s", - "llocality=%(LLocality)s", - "email=%(EMail)s", - "lemail=%(LEMail)s", - "orgname=%(OrgName)s", - "lorgname=%(LOrgName)s", - "orgunit=%(OrgUnit)s", - "lorgunit=%(LOrgUnit)s"]) - end). - -get_vcard(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(vcard)s from vcard where username=%(LUser)s")). - -get_default_privacy_list(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(name)s from privacy_default_list " - "where username=%(LUser)s")). - -get_default_privacy_list_t(LUser) -> - ejabberd_sql:sql_query_t( - ?SQL("select @(name)s from privacy_default_list " - "where username=%(LUser)s")). - -get_privacy_list_names(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(name)s from privacy_list" - " where username=%(LUser)s")). - -get_privacy_list_names_t(LUser) -> - ejabberd_sql:sql_query_t( - ?SQL("select @(name)s from privacy_list" - " where username=%(LUser)s")). - -get_privacy_list_id(LServer, LUser, Name) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(id)d from privacy_list" - " where username=%(LUser)s and name=%(Name)s")). - -get_privacy_list_id_t(LUser, Name) -> - ejabberd_sql:sql_query_t( - ?SQL("select @(id)d from privacy_list" - " where username=%(LUser)s and name=%(Name)s")). - -get_privacy_list_data(LServer, LUser, Name) -> - ejabberd_sql: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_sql: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_sql: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_sql: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_sql:sql_query( - LServer, - ?SQL("delete from privacy_default_list" - " where username=%(LUser)s")). - -remove_privacy_list(LUser, Name) -> - ejabberd_sql:sql_query_t( - ?SQL("delete from privacy_list where" - " username=%(LUser)s and name=%(Name)s")). - -add_privacy_list(LUser, Name) -> - ejabberd_sql:sql_query_t( - ?SQL("insert into privacy_list(username, name) " - "values (%(LUser)s, %(Name)s)")). - -set_privacy_list(ID, RItems) -> - ejabberd_sql: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_sql: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, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("delete from privacy_list where username=%(LUser)s")), - %US = <>, - %ejabberd_sql:sql_query( - % LServer, - % ?SQL("delete from privacy_list_data where value=%(US)s")), - ejabberd_sql: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">>; -escape($\b) -> <<"\\b">>; -escape($\r) -> <<"\\r">>; -escape($') -> <<"''">>; -escape($") -> <<"\\\"">>; -escape($\\) -> <<"\\\\">>; -escape(C) -> <>. - -%% Count number of records in a table given a where clause -count_records_where(LServer, Table, WhereClause) -> - ejabberd_sql:sql_query(LServer, - [<<"select count(*) from ">>, Table, <<" ">>, - WhereClause, <<";">>]). - -get_roster_version(LServer, LUser) -> - ejabberd_sql:sql_query( - LServer, - ?SQL("select @(version)s from roster_version" - " where username = %(LUser)s")). - -set_roster_version(LUser, Version) -> - ?SQL_UPSERT_T( - "roster_version", - ["!username=%(LUser)s", - "version=%(Version)s"]). - --spec opt_type(pgsql_users_number_estimate) -> fun((boolean()) -> boolean()); - (atom()) -> [atom()]. -opt_type(pgsql_users_number_estimate) -> - fun (V) when is_boolean(V) -> V end; -opt_type(_) -> [pgsql_users_number_estimate]. -- 2.40.0