aboutsummaryrefslogtreecommitdiff
path: root/src/odbc/odbc_queries.erl
diff options
context:
space:
mode:
Diffstat (limited to 'src/odbc/odbc_queries.erl')
-rw-r--r--src/odbc/odbc_queries.erl288
1 files changed, 242 insertions, 46 deletions
diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl
index bef9837c6..f70b8fb35 100644
--- a/src/odbc/odbc_queries.erl
+++ b/src/odbc/odbc_queries.erl
@@ -5,7 +5,7 @@
%%% Created : by Mickael Remond <mremond@process-one.net>
%%%
%%%
-%%% ejabberd, Copyright (C) 2002-2008 Process-one
+%%% ejabberd, Copyright (C) 2002-2009 ProcessOne
%%%
%%% This program is free software; you can redistribute it and/or
%%% modify it under the terms of the GNU General Public License as
@@ -61,8 +61,26 @@
set_private_data_sql/3,
get_private_data/3,
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,
+ 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]).
+ 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).
@@ -72,6 +90,14 @@
-define(generic, true).
-endif.
+%% 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).
@@ -79,9 +105,25 @@
get_db_type() ->
generic.
+%% Safe atomic update.
+update_t(Table, Fields, Vals, Where) ->
+ UPairs = lists:zipwith(fun(A, B) -> A ++ "='" ++ B ++ "'" 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.
+
%% 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)
+%% 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).
@@ -95,9 +137,11 @@ set_last_t(LServer, Username, Seconds, State) ->
%% MREMOND: I think this should be turn into a non transactional behaviour
ejabberd_odbc:sql_transaction(
LServer,
- [["delete from last where username='", Username, "';"],
- ["insert into last(username, seconds, state) "
- "values ('", Username, "', '", Seconds, "', '", State, "');"]]).
+ fun() ->
+ update_t("last", ["username", "seconds", "state"],
+ [Username, Seconds, State],
+ ["username='", Username, "'"])
+ end).
del_last(LServer, Username) ->
ejabberd_odbc:sql_query(
@@ -113,9 +157,11 @@ get_password(LServer, Username) ->
set_password_t(LServer, Username, Pass) ->
ejabberd_odbc:sql_transaction(
LServer,
- [["delete from users where username='", Username ,"';"],
- ["insert into users(username, password) "
- "values ('", Username, "', '", Pass, "');"]]).
+ fun() ->
+ update_t("users", ["username", "password"],
+ [Username, Pass],
+ ["username='", Username ,"'"])
+ end).
add_user(LServer, Username, Pass) ->
ejabberd_odbc:sql_query(
@@ -129,12 +175,13 @@ del_user(LServer, Username) ->
["delete from users where username='", Username ,"';"]).
del_user_return_password(_LServer, Username, Pass) ->
- ejabberd_odbc:sql_query_t(
- ["select password from users where username='",
- Username, "';"]),
+ 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, "';"]).
+ "' and password='", Pass, "';"]),
+ P.
list_users(LServer) ->
ejabberd_odbc:sql_query(
@@ -293,16 +340,11 @@ del_roster_sql(Username, SJID) ->
" and jid='", SJID, "';"]].
update_roster(_LServer, Username, SJID, ItemVals, ItemGroups) ->
- ejabberd_odbc:sql_query_t(
- ["delete from rosterusers "
- " where username='", Username, "' "
- " and jid='", SJID, "';"]),
- ejabberd_odbc:sql_query_t(
- ["insert into rosterusers("
- " username, jid, nick, "
- " subscription, ask, askmessage, "
- " server, subscribe, type) "
- " values (", ItemVals, ");"]),
+ 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, "' "
@@ -311,7 +353,7 @@ update_roster(_LServer, Username, SJID, ItemVals, ItemGroups) ->
ejabberd_odbc:sql_query_t(
["insert into rostergroups("
" username, jid, grp) "
- " values (", ItemGroup, ");"])
+ " values ('", join(ItemGroup, "', '"), "');"])
end,
ItemGroups).
@@ -321,28 +363,23 @@ update_roster_sql(Username, SJID, ItemVals, ItemGroups) ->
" and jid='", SJID, "';"],
["insert into rosterusers("
" username, jid, nick, "
- " subscription, ask, askmessage"
+ " subscription, ask, askmessage, "
" server, subscribe, type) "
- " values (", ItemVals, ");"],
+ " values ('", join(ItemVals, "', '"), "');"],
["delete from rostergroups "
" where username='", Username, "' "
- " and jid='", SJID, "';"],
+ " and jid='", SJID, "';"]] ++
[["insert into rostergroups("
" username, jid, grp) "
- " values (", ItemGroup, ");"] ||
- ItemGroup <- ItemGroups]].
+ " values ('", join(ItemGroup, "', '"), "');"] ||
+ ItemGroup <- ItemGroups].
roster_subscribe(_LServer, Username, SJID, ItemVals) ->
- ejabberd_odbc:sql_query_t(
- ["delete from rosterusers "
- " where username='", Username, "' "
- " and jid='", SJID, "';"]),
- ejabberd_odbc:sql_query_t(
- ["insert into rosterusers("
- " username, jid, nick, "
- " subscription, ask, askmessage, "
- " server, subscribe, type) "
- " values (", 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(
@@ -352,10 +389,10 @@ get_subscription(LServer, Username, SJID) ->
"and jid='", SJID, "'"]).
set_private_data(_LServer, Username, LXMLNS, SData) ->
- lists:foreach(fun(Query) ->
- ejabberd_odbc:sql_query_t(Query)
- end,
- set_private_data_sql(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 "
@@ -373,10 +410,137 @@ get_private_data(LServer, Username, LXMLNS) ->
"namespace='", LXMLNS, "';"]).
del_user_private_storage(LServer, Username) ->
- ejabberd_odbc:sql_transaction(
+ 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;"]).
+
+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_presence_out "
+ ") "
+ "values ('", ID, "', '",
+ join(Items, "', '"), "');"])
+ end, RItems).
+
+del_privacy_lists(LServer, Server, Username) ->
+ 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++"@"++Server, "';"]),
+ ejabberd_odbc:sql_query(
+ LServer,
+ ["delete from privacy_default_list where username='", Username, "';"]).
+
%% Characters to escape
escape($\0) -> "\\0";
escape($\n) -> "\\n";
@@ -393,6 +557,13 @@ 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.
%% -----------------
@@ -490,7 +661,7 @@ 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 list(Rs) ->
+ Rs when is_list(Rs) ->
lists:filter(fun({selected, _Header, _Row}) ->
true;
({updated, _N}) ->
@@ -596,6 +767,25 @@ del_user_private_storage(LServer, Username) ->
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, "'"]).
+
%% Characters to escape
escape($\0) -> "\\0";
escape($\t) -> "\\t";
@@ -610,4 +800,10 @@ count_records_where(LServer, Table, WhereClause) ->
ejabberd_odbc:sql_query(
LServer,
["select count(*) from ", Table, " ", WhereClause, " with (nolock)"]).
+
+get_roster_version(LServer, LUser) ->
+ ejabberd_odbc:sql_query(LServer,
+ ["select version from dbo.roster_version where username = '", LUser, "'"]).
+set_roster_version(LUser, Version) ->
+ update_t("dbo.roster_version", ["username", "version"], [LUser, Version], ["username = '", LUser, "'"]).
-endif.
generated by cgit v1.2.3 (git 2.25.1) at 2025-09-24 12:01:31 +0000