Updated some mod_offline SQL queries to the new API

2 files changed, 50 insertions, 74 deletions

diff --git a/src/mod_offline.erl b/src/mod_offline.erl
index 28c2ba39..0db1dd68 100644
--- a/src/mod_offline.erl
+++ b/src/mod_offline.erl
@@ -25,6 +25,8 @@
 
 -module(mod_offline).
 
+-compile([{parse_transform, ejabberd_sql_pt}]).
+
 -author('alexey@process-one.net').
 
 -protocol({xep, 13, '1.2'}).
@@ -79,6 +81,8 @@
 
 -include("mod_offline.hrl").
 
+-include("ejabberd_sql_pt.hrl").
+
 -define(PROCNAME, ejabberd_offline).
 
 -define(OFFLINE_TABLE_LOCK_THRESHOLD, 1000).
@@ -686,13 +690,10 @@ pop_offline_messages(Ls, LUser, LServer, mnesia) ->
       _ -> Ls
     end;
 pop_offline_messages(Ls, LUser, LServer, odbc) ->
-    EUser = ejabberd_odbc:escape(LUser),
-    case odbc_queries:get_and_del_spool_msg_t(LServer,
-					      EUser)
-	of
-      {atomic, {selected, [<<"username">>, <<"xml">>], Rs}} ->
+    case odbc_queries:get_and_del_spool_msg_t(LServer, LUser) of
+      {atomic, {selected, Rs}} ->
 	  Ls ++
-	    lists:flatmap(fun ([_, XML]) ->
+	    lists:flatmap(fun ({_, XML}) ->
 				  case fxml_stream:parse_element(XML) of
 				    {error, _Reason} ->
                                           [];
@@ -811,8 +812,7 @@ remove_user(LUser, LServer, mnesia) ->
     F = fun () -> mnesia:delete({offline_msg, US}) end,
     mnesia:transaction(F);
 remove_user(LUser, LServer, odbc) ->
-    Username = ejabberd_odbc:escape(LUser),
-    odbc_queries:del_spool_msg(LServer, Username);
+    odbc_queries:del_spool_msg(LServer, LUser);
 remove_user(LUser, LServer, riak) ->
     {atomic, ejabberd_riak:delete_by_index(offline_msg,
                                            <<"us">>, {LUser, LServer})}.
@@ -883,13 +883,13 @@ get_offline_els(LUser, LServer, DBType)
               jlib:replace_from_to(From, To, Packet)
       end, Msgs);
 get_offline_els(LUser, LServer, odbc) ->
-    Username = ejabberd_odbc:escape(LUser),
-    case catch ejabberd_odbc:sql_query(LServer,
-				       [<<"select xml from spool  where username='">>,
-					Username, <<"'  order by seq;">>]) of
-        {selected, [<<"xml">>], Rs} ->
+    case catch ejabberd_odbc:sql_query(
+                 LServer,
+                 ?SQL("select @(xml)s from spool where "
+                      "username=%(LUser)s order by seq")) of
+        {selected, Rs} ->
             lists:flatmap(
-              fun([XML]) ->
+              fun({XML}) ->
                       case fxml_stream:parse_element(XML) of
                           #xmlel{} = El ->
                               case offline_msg_to_route(LServer, El) of
@@ -1050,20 +1050,20 @@ read_all_msgs(LUser, LServer, riak) ->
             []
     end;
 read_all_msgs(LUser, LServer, odbc) ->
-    Username = ejabberd_odbc:escape(LUser),
-    case catch ejabberd_odbc:sql_query(LServer,
-				       [<<"select xml from spool  where username='">>,
-					Username, <<"'  order by seq;">>])
-	of
-      {selected, [<<"xml">>], Rs} ->
-	  lists:flatmap(fun ([XML]) ->
-				case fxml_stream:parse_element(XML) of
-				  {error, _Reason} -> [];
-				  El -> [El]
-				end
-			end,
-			Rs);
-      _ -> []
+    case catch ejabberd_odbc:sql_query(
+                 LServer,
+                 ?SQL("select @(xml)s from spool where "
+                      "username=%(LUser)s order by seq")) of
+        {selected, Rs} ->
+            lists:flatmap(
+              fun({XML}) ->
+                      case fxml_stream:parse_element(XML) of
+                          {error, _Reason} -> [];
+                          El -> [El]
+                      end
+              end,
+              Rs);
+        _ -> []
     end.
 
 format_user_queue(Msgs, DBType) when DBType == mnesia; DBType == riak ->
@@ -1246,30 +1246,7 @@ us_to_list({User, Server}) ->
     jid:to_string({User, Server, <<"">>}).
 
 get_queue_length(LUser, LServer) ->
-    get_queue_length(LUser, LServer,
-		     gen_mod:db_type(LServer, ?MODULE)).
-
-get_queue_length(LUser, LServer, mnesia) ->
-    length(mnesia:dirty_read({offline_msg,
-			       {LUser, LServer}}));
-get_queue_length(LUser, LServer, riak) ->
-    case ejabberd_riak:count_by_index(offline_msg,
-                                      <<"us">>, {LUser, LServer}) of
-        {ok, N} ->
-            N;
-        _ ->
-            0
-    end;
-get_queue_length(LUser, LServer, odbc) ->
-    Username = ejabberd_odbc:escape(LUser),
-    case catch ejabberd_odbc:sql_query(LServer,
-				       [<<"select count(*) from spool  where username='">>,
-					Username, <<"';">>])
-	of
-      {selected, [_], [[SCount]]} ->
-	  jlib:binary_to_integer(SCount);
-      _ -> 0
-    end.
+    count_offline_messages(LUser, LServer).
 
 get_messages_subset(User, Host, MsgsAll, DBType) ->
     Access = gen_mod:get_module_opt(Host, ?MODULE, access_max_user_messages,
@@ -1342,8 +1319,7 @@ delete_all_msgs(LUser, LServer, riak) ->
                                         <<"us">>, {LUser, LServer}),
     {atomic, Res};
 delete_all_msgs(LUser, LServer, odbc) ->
-    Username = ejabberd_odbc:escape(LUser),
-    odbc_queries:del_spool_msg(LServer, Username),
+    odbc_queries:del_spool_msg(LServer, LUser),
     {atomic, ok}.
 
 webadmin_user_parse_query(_, <<"removealloffline">>,
@@ -1379,15 +1355,13 @@ count_offline_messages(LUser, LServer, mnesia) ->
       _ -> 0
     end;
 count_offline_messages(LUser, LServer, odbc) ->
-    Username = ejabberd_odbc:escape(LUser),
-    case catch odbc_queries:count_records_where(LServer,
-						<<"spool">>,
-						<<"where username='",
-						  Username/binary, "'">>)
-	of
-      {selected, [_], [[Res]]} ->
-	  jlib:binary_to_integer(Res);
-      _ -> 0
+    case catch ejabberd_odbc:sql_query(
+                 LServer,
+                 ?SQL("select @(count(*))d from spool "
+                      "where username=%(LUser)s")) of
+        {selected, [{Res}]} ->
+            Res;
+        _ -> 0
     end;
 count_offline_messages(LUser, LServer, riak) ->
     case ejabberd_riak:count_by_index(
diff --git a/src/odbc_queries.erl b/src/odbc_queries.erl
index 2f488a0b..ee8fa169 100644
--- a/src/odbc_queries.erl
+++ b/src/odbc_queries.erl
@@ -25,6 +25,8 @@
 
 -module(odbc_queries).
 
+-compile([{parse_transform, ejabberd_sql_pt}]).
+
 -behaviour(ejabberd_config).
 
 -author("mremond@process-one.net").
@@ -60,6 +62,7 @@
 
 -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
@@ -291,23 +294,22 @@ add_spool_sql(Username, XML) ->
 add_spool(LServer, Queries) ->
     ejabberd_odbc:sql_transaction(LServer, Queries).
 
-get_and_del_spool_msg_t(LServer, Username) ->
+get_and_del_spool_msg_t(LServer, LUser) ->
     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, <<"';">>]),
+		    ejabberd_odbc:sql_query_t(
+                      ?SQL("select @(username)s, @(xml)s from spool where "
+                           "username=%(LUser)s order by seq;")),
+		ejabberd_odbc:sql_query_t(
+                  ?SQL("delete from spool where username=%(LUser)s;")),
 		Result
 	end,
     ejabberd_odbc:sql_transaction(LServer, F).
 
-del_spool_msg(LServer, Username) ->
-    ejabberd_odbc:sql_query(LServer,
-			    [<<"delete from spool where username='">>, Username,
-			     <<"';">>]).
+del_spool_msg(LServer, LUser) ->
+    ejabberd_odbc:sql_query(
+      LServer,
+      ?SQL("delete from spool where username=%(LUser)s")).
 
 get_roster(LServer, Username) ->
     ejabberd_odbc:sql_query(LServer,