From 0ea0ba3004282636c95bc86c3aac8755fcf7f8b1 Mon Sep 17 00:00:00 2001 From: Alexey Shchepin Date: Wed, 4 May 2016 21:01:05 +0300 Subject: Update more SQL queries --- src/mod_shared_roster_sql.erl | 129 +++++++++++++++++++----------------------- 1 file changed, 59 insertions(+), 70 deletions(-) (limited to 'src/mod_shared_roster_sql.erl') diff --git a/src/mod_shared_roster_sql.erl b/src/mod_shared_roster_sql.erl index bd123cd4..2b186fd0 100644 --- a/src/mod_shared_roster_sql.erl +++ b/src/mod_shared_roster_sql.erl @@ -8,6 +8,8 @@ %%%------------------------------------------------------------------- -module(mod_shared_roster_sql). +-compile([{parse_transform, ejabberd_sql_pt}]). + -behaviour(mod_shared_roster). %% API @@ -21,6 +23,7 @@ -include("jlib.hrl"). -include("mod_roster.hrl"). -include("mod_shared_roster.hrl"). +-include("ejabberd_sql_pt.hrl"). %%%=================================================================== %%% API @@ -30,38 +33,39 @@ init(_Host, _Opts) -> list_groups(Host) -> case ejabberd_sql:sql_query( - Host, [<<"select name from sr_group;">>]) of - {selected, [<<"name">>], Rs} -> [G || [G] <- Rs]; + Host, + ?SQL("select @(name)s from sr_group")) of + {selected, Rs} -> [G || {G} <- Rs]; _ -> [] end. groups_with_opts(Host) -> - case ejabberd_sql:sql_query(Host, - [<<"select name, opts from sr_group;">>]) + case ejabberd_sql:sql_query( + Host, + ?SQL("select @(name)s, @(opts)s from sr_group")) of - {selected, [<<"name">>, <<"opts">>], Rs} -> + {selected, Rs} -> [{G, mod_shared_roster:opts_to_binary(ejabberd_sql:decode_term(Opts))} - || [G, Opts] <- Rs]; + || {G, Opts} <- Rs]; _ -> [] end. create_group(Host, Group, Opts) -> - SGroup = ejabberd_sql:escape(Group), - SOpts = ejabberd_sql:encode_term(Opts), + SOpts = jlib:term_to_expr(Opts), F = fun () -> - sql_queries:update_t(<<"sr_group">>, - [<<"name">>, <<"opts">>], [SGroup, SOpts], - [<<"name='">>, SGroup, <<"'">>]) + ?SQL_UPSERT_T( + "sr_group", + ["!name=%(Group)s", + "opts=%(SOpts)s"]) end, ejabberd_sql:sql_transaction(Host, F). delete_group(Host, Group) -> - SGroup = ejabberd_sql:escape(Group), F = fun () -> - ejabberd_sql:sql_query_t([<<"delete from sr_group where name='">>, - SGroup, <<"';">>]), - ejabberd_sql:sql_query_t([<<"delete from sr_user where grp='">>, - SGroup, <<"';">>]) + ejabberd_sql:sql_query_t( + ?SQL("delete from sr_group where name=%(Group)s")), + ejabberd_sql:sql_query_t( + ?SQL("delete from sr_user where grp=%(Group)s")) end, case ejabberd_sql:sql_transaction(Host, F) of {atomic,{updated,_}} -> {atomic, ok}; @@ -69,23 +73,21 @@ delete_group(Host, Group) -> end. get_group_opts(Host, Group) -> - SGroup = ejabberd_sql:escape(Group), case catch ejabberd_sql:sql_query( Host, - [<<"select opts from sr_group where name='">>, - SGroup, <<"';">>]) of - {selected, [<<"opts">>], [[SOpts]]} -> + ?SQL("select @(opts)s from sr_group where name=%(Group)s")) of + {selected, [{SOpts}]} -> mod_shared_roster:opts_to_binary(ejabberd_sql:decode_term(SOpts)); _ -> error end. set_group_opts(Host, Group, Opts) -> - SGroup = ejabberd_sql:escape(Group), - SOpts = ejabberd_sql:encode_term(Opts), + SOpts = jlib:term_to_expr(Opts), F = fun () -> - sql_queries:update_t(<<"sr_group">>, - [<<"name">>, <<"opts">>], [SGroup, SOpts], - [<<"name='">>, SGroup, <<"'">>]) + ?SQL_UPSERT_T( + "sr_group", + ["!name=%(Group)s", + "opts=%(SOpts)s"]) end, ejabberd_sql:sql_transaction(Host, F). @@ -93,21 +95,18 @@ get_user_groups(US, Host) -> SJID = make_jid_s(US), case catch ejabberd_sql:sql_query( Host, - [<<"select grp from sr_user where jid='">>, - SJID, <<"';">>]) of - {selected, [<<"grp">>], Rs} -> [G || [G] <- Rs]; + ?SQL("select @(grp)s from sr_user where jid=%(SJID)s")) of + {selected, Rs} -> [G || {G} <- Rs]; _ -> [] end. get_group_explicit_users(Host, Group) -> - SGroup = ejabberd_sql:escape(Group), case catch ejabberd_sql:sql_query( Host, - [<<"select jid from sr_user where grp='">>, - SGroup, <<"';">>]) of - {selected, [<<"jid">>], Rs} -> + ?SQL("select @(jid)s from sr_user where grp=%(Group)s")) of + {selected, Rs} -> lists:map( - fun([JID]) -> + fun({JID}) -> {U, S, _} = jid:tolower(jid:from_string(JID)), {U, S} end, Rs); @@ -119,43 +118,36 @@ get_user_displayed_groups(LUser, LServer, GroupsOpts) -> SJID = make_jid_s(LUser, LServer), case catch ejabberd_sql:sql_query( LServer, - [<<"select grp from sr_user where jid='">>, - SJID, <<"';">>]) of - {selected, [<<"grp">>], Rs} -> + ?SQL("select @(grp)s from sr_user where jid=%(SJID)s")) of + {selected, Rs} -> [{Group, proplists:get_value(Group, GroupsOpts, [])} - || [Group] <- Rs]; + || {Group} <- Rs]; _ -> [] end. is_user_in_group(US, Group, Host) -> SJID = make_jid_s(US), - SGroup = ejabberd_sql:escape(Group), - case catch ejabberd_sql:sql_query(Host, - [<<"select * from sr_user where jid='">>, - SJID, <<"' and grp='">>, SGroup, - <<"';">>]) of - {selected, _, []} -> false; + case catch ejabberd_sql:sql_query( + Host, + ?SQL("select @(jid)s from sr_user where jid=%(SJID)s" + " and grp=%(Group)s")) of + {selected, []} -> false; _ -> true end. add_user_to_group(Host, US, Group) -> SJID = make_jid_s(US), - SGroup = ejabberd_sql:escape(Group), - F = fun () -> - sql_queries:update_t(<<"sr_user">>, - [<<"jid">>, <<"grp">>], [SJID, SGroup], - [<<"jid='">>, SJID, <<"' and grp='">>, - SGroup, <<"'">>]) - end, - ejabberd_sql:sql_transaction(Host, F). + ejabberd_sql:sql_query( + Host, + ?SQL("insert into sr_user(jid, grp) values (" + "%(SJID)s, %(Group)s)")). remove_user_from_group(Host, US, Group) -> SJID = make_jid_s(US), - SGroup = ejabberd_sql:escape(Group), F = fun () -> - ejabberd_sql:sql_query_t([<<"delete from sr_user where jid='">>, - SJID, <<"' and grp='">>, SGroup, - <<"';">>]), + ejabberd_sql:sql_query_t( + ?SQL("delete from sr_user where jid=%(SJID)s and" + " grp=%(Group)s")), ok end, ejabberd_sql:sql_transaction(Host, F). @@ -164,26 +156,23 @@ export(_Server) -> [{sr_group, fun(Host, #sr_group{group_host = {Group, LServer}, opts = Opts}) when LServer == Host -> - SGroup = ejabberd_sql:escape(Group), - SOpts = ejabberd_sql:encode_term(Opts), - [[<<"delete from sr_group where name='">>, Group, <<"';">>], - [<<"insert into sr_group(name, opts) values ('">>, - SGroup, <<"', '">>, SOpts, <<"');">>]]; + SOpts = jlib:term_to_expr(Opts), + [?SQL("delete from sr_group where name=%(Group)s;"), + ?SQL("insert into sr_group(name, opts) values (" + "%(Group)s, %(SOpts)s);")]; (_Host, _R) -> [] end}, {sr_user, fun(Host, #sr_user{us = {U, S}, group_host = {Group, LServer}}) when LServer == Host -> - SGroup = ejabberd_sql:escape(Group), - SJID = ejabberd_sql:escape( - jid:to_string( - jid:tolower( - jid:make(U, S, <<"">>)))), - [[<<"delete from sr_user where jid='">>, SJID, - <<"'and grp='">>, Group, <<"';">>], - [<<"insert into sr_user(jid, grp) values ('">>, - SJID, <<"', '">>, SGroup, <<"');">>]]; + SJID = jid:to_string( + jid:tolower( + jid:make(U, S, <<"">>))), + [?SQL("select @(jid)s from sr_user where jid=%(SJID)s" + " and grp=%(Group)s;"), + ?SQL("insert into sr_user(jid, grp) values (" + "%(SJID)s, %(Group)s);")]; (_Host, _R) -> [] end}]. @@ -207,6 +196,6 @@ import(_, _) -> %%% Internal functions %%%=================================================================== make_jid_s(U, S) -> - ejabberd_sql:escape(jid:to_string(jid:tolower(jid:make(U, S, <<"">>)))). + jid:to_string(jid:tolower(jid:make(U, S, <<"">>))). make_jid_s({U, S}) -> make_jid_s(U, S). -- cgit v1.2.3