1 |
|
%%%---------------------------------------------------------------------- |
2 |
|
%%% File : mod_vcard.erl |
3 |
|
%%% Author : Alexey Shchepin <alexey@process-one.net> |
4 |
|
%%% Purpose : vCard support via RDBMS |
5 |
|
%%% Created : 2 Jan 2003 by Alexey Shchepin <alexey@process-one.net> |
6 |
|
%%% |
7 |
|
%%% |
8 |
|
%%% ejabberd, Copyright (C) 2002-2011 ProcessOne |
9 |
|
%%% |
10 |
|
%%% This program is free software; you can redistribute it and/or |
11 |
|
%%% modify it under the terms of the GNU General Public License as |
12 |
|
%%% published by the Free Software Foundation; either version 2 of the |
13 |
|
%%% License, or (at your option) any later version. |
14 |
|
%%% |
15 |
|
%%% This program is distributed in the hope that it will be useful, |
16 |
|
%%% but WITHOUT ANY WARRANTY; without even the implied warranty of |
17 |
|
%%% MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
18 |
|
%%% General Public License for more details. |
19 |
|
%%% |
20 |
|
%%% You should have received a copy of the GNU General Public License |
21 |
|
%%% along with this program; if not, write to the Free Software |
22 |
|
%%% Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA |
23 |
|
%%% |
24 |
|
%%%---------------------------------------------------------------------- |
25 |
|
|
26 |
|
-module(mod_vcard_rdbms). |
27 |
|
|
28 |
|
-behaviour(mod_vcard_backend). |
29 |
|
|
30 |
|
%% mod_vcards callbacks |
31 |
|
-export([init/2, |
32 |
|
remove_user/3, |
33 |
|
remove_domain/2, |
34 |
|
get_vcard/3, |
35 |
|
set_vcard/5, |
36 |
|
search/3, |
37 |
|
search_fields/2, |
38 |
|
search_reported_fields/3]). |
39 |
|
|
40 |
|
-include("mongoose.hrl"). |
41 |
|
-include("jlib.hrl"). |
42 |
|
-include("mod_vcard.hrl"). |
43 |
|
|
44 |
|
-type filter_type() :: equal | like. |
45 |
|
-type limit_type() :: infinity | top | limit. |
46 |
|
-type sql_column() :: binary(). |
47 |
|
-type sql_value() :: binary(). |
48 |
|
-type sql_filter() :: {filter_type(), sql_column(), sql_value()}. |
49 |
|
|
50 |
|
init(HostType, _Options) -> |
51 |
:-( |
mongoose_rdbms:prepare(vcard_remove, vcard, [username, server], |
52 |
|
<<"DELETE FROM vcard WHERE username=? AND server=?">>), |
53 |
:-( |
mongoose_rdbms:prepare(vcard_search_remove, vcard_search, [lusername, server], |
54 |
|
<<"DELETE FROM vcard_search WHERE lusername=? AND server=?">>), |
55 |
:-( |
mongoose_rdbms:prepare(vcard_remove_domain, vcard, [server], |
56 |
|
<<"DELETE FROM vcard WHERE server=?">>), |
57 |
:-( |
mongoose_rdbms:prepare(vcard_search_remove_domain, vcard_search, [server], |
58 |
|
<<"DELETE FROM vcard_search WHERE server=?">>), |
59 |
:-( |
mongoose_rdbms:prepare(vcard_select, vcard, |
60 |
|
[username, server], |
61 |
|
<<"SELECT vcard FROM vcard WHERE username=? AND server=?">>), |
62 |
:-( |
rdbms_queries:prepare_upsert(HostType, vcard_upsert, vcard, |
63 |
|
[<<"username">>, <<"server">>, <<"vcard">>], |
64 |
|
[<<"vcard">>], |
65 |
|
[<<"username">>, <<"server">>]), |
66 |
:-( |
SearchColumns = search_columns(), |
67 |
:-( |
rdbms_queries:prepare_upsert(HostType, vcard_search_upsert, vcard_search, |
68 |
|
[<<"lusername">>, <<"server">>|SearchColumns], |
69 |
|
SearchColumns, |
70 |
|
[<<"lusername">>, <<"server">>]), |
71 |
:-( |
ok. |
72 |
|
|
73 |
|
%% Remove user callback |
74 |
|
remove_user(HostType, LUser, LServer) -> |
75 |
:-( |
F = fun() -> remove_user_t(HostType, LUser, LServer) end, |
76 |
:-( |
mongoose_rdbms:sql_transaction(HostType, F). |
77 |
|
|
78 |
|
remove_user_t(HostType, LUser, LServer) -> |
79 |
:-( |
mongoose_rdbms:execute(HostType, vcard_remove, [LUser, LServer]), |
80 |
:-( |
mongoose_rdbms:execute(HostType, vcard_search_remove, [LUser, LServer]). |
81 |
|
|
82 |
|
%% Remove domain callback |
83 |
|
-spec remove_domain(mongooseim:host_type(), jid:lserver()) -> ok. |
84 |
|
remove_domain(HostType, Domain) -> |
85 |
:-( |
F = fun() -> remove_domain_t(HostType, Domain) end, |
86 |
:-( |
mongoose_rdbms:sql_transaction(HostType, F), |
87 |
:-( |
ok. |
88 |
|
|
89 |
|
remove_domain_t(HostType, Domain) -> |
90 |
:-( |
mongoose_rdbms:execute_successfully(HostType, vcard_remove_domain, [Domain]), |
91 |
:-( |
mongoose_rdbms:execute_successfully(HostType, vcard_search_remove_domain, [Domain]). |
92 |
|
|
93 |
|
%% Get a single vCard callback |
94 |
|
get_vcard(HostType, LUser, LServer) -> |
95 |
:-( |
Res = mongoose_rdbms:execute(HostType, vcard_select, [LUser, LServer]), |
96 |
:-( |
case Res of |
97 |
|
{selected, [{SVCARD}]} -> |
98 |
:-( |
case exml:parse(SVCARD) of |
99 |
|
{error, Reason} -> |
100 |
:-( |
?LOG_WARNING(#{what => vcard_corrupted, |
101 |
|
text => <<"Not sending back bad vCard XML">>, |
102 |
|
reason => Reason, svcard => SVCARD, |
103 |
:-( |
user => LUser, host => LServer}), |
104 |
:-( |
{error, mongoose_xmpp_errors:service_unavailable()}; |
105 |
|
{ok, VCARD} -> |
106 |
:-( |
{ok, [VCARD]} |
107 |
|
end; |
108 |
|
{selected, []} -> |
109 |
:-( |
{error, mongoose_xmpp_errors:item_not_found()} |
110 |
|
end. |
111 |
|
|
112 |
|
%% Set a vCard callback |
113 |
|
set_vcard(HostType, User, LServer, VCard, Search) -> |
114 |
:-( |
LUser = jid:nodeprep(User), |
115 |
:-( |
SearchArgs = assert_binaries(search_args(User, Search)), |
116 |
:-( |
XML = exml:to_binary(VCard), |
117 |
:-( |
F = fun() -> |
118 |
:-( |
update_vcard_t(HostType, LUser, LServer, XML), |
119 |
:-( |
update_vcard_search_t(HostType, LUser, LServer, SearchArgs), |
120 |
:-( |
ok |
121 |
|
end, |
122 |
:-( |
Result = handle_result(rdbms_queries:sql_transaction(HostType, F)), |
123 |
:-( |
log_upsert_result(HostType, LServer, LUser, VCard, XML, Result), |
124 |
:-( |
Result. |
125 |
|
|
126 |
|
%% Do not pass unicode strings as a list of bytes into MySQL driver. |
127 |
|
%% MySQL driver treats lists of integers as lists of codepoints. |
128 |
|
%% So, it wouldn't be encoded properly. |
129 |
|
%% Only binaries should be used to avoid confusion. |
130 |
|
assert_binaries(Bins) -> |
131 |
:-( |
case lists:all(fun is_binary/1, Bins) of |
132 |
|
true -> |
133 |
:-( |
Bins; |
134 |
|
false -> |
135 |
:-( |
error(#{what => assert_binaries_failed, binaries => Bins}) |
136 |
|
end. |
137 |
|
|
138 |
|
log_upsert_result(HostType, LServer, LUser, VCard, _XML, ok) -> |
139 |
:-( |
mongoose_hooks:vcard_set(HostType, LServer, LUser, VCard); |
140 |
|
log_upsert_result(HostType, LServer, LUser, _VCard, XML, {error, Reason}) -> |
141 |
:-( |
?LOG_WARNING(#{what => vcard_update_failed, reason => Reason, |
142 |
|
host_type => HostType, |
143 |
:-( |
user => LUser, server => LServer, vcard_xml => XML}). |
144 |
|
|
145 |
:-( |
handle_result({atomic, ok}) -> ok; |
146 |
:-( |
handle_result({aborted, Reason}) -> {error, {aborted, Reason}}; |
147 |
:-( |
handle_result({error, Reason}) -> {error, Reason}. |
148 |
|
|
149 |
|
update_vcard_t(HostType, LUser, LServer, XML) -> |
150 |
:-( |
InsertParams = [LUser, LServer, XML], |
151 |
:-( |
UpdateParams = [XML], |
152 |
:-( |
UniqueKeyValues = [LUser, LServer], |
153 |
:-( |
rdbms_queries:execute_upsert(HostType, vcard_upsert, InsertParams, UpdateParams, UniqueKeyValues). |
154 |
|
|
155 |
|
update_vcard_search_t(HostType, LUser, LServer, SearchArgs) -> |
156 |
:-( |
InsertParams = [LUser, LServer|SearchArgs], |
157 |
:-( |
UpdateParams = SearchArgs, |
158 |
:-( |
UniqueKeyValues = [LUser, LServer], |
159 |
:-( |
rdbms_queries:execute_upsert(HostType, vcard_search_upsert, InsertParams, UpdateParams, UniqueKeyValues). |
160 |
|
|
161 |
|
%% Search vCards fields callback |
162 |
|
search_fields(_HostType, _VHost) -> |
163 |
:-( |
mod_vcard:default_search_fields(). |
164 |
|
|
165 |
|
%% Search vCards reported fields callback |
166 |
|
-spec search_reported_fields(mongooseim:host_type(), jid:lserver(), ejabberd:lang()) -> |
167 |
|
[mongoose_data_forms:field()]. |
168 |
|
search_reported_fields(_HostType, _VHost, Lang) -> |
169 |
:-( |
mod_vcard:get_default_reported_fields(Lang). |
170 |
|
|
171 |
|
%% Search vCards callback |
172 |
|
-spec search(mongooseim:host_type(), jid:lserver(), term()) -> [[mongoose_data_forms:field()]]. |
173 |
|
search(HostType, LServer, Data) -> |
174 |
:-( |
Filters = make_filters(LServer, Data), |
175 |
:-( |
case Filters of |
176 |
|
[] -> |
177 |
:-( |
[]; |
178 |
|
_ -> |
179 |
:-( |
Limit = mod_vcard:get_results_limit(HostType), |
180 |
:-( |
LimitType = limit_type(Limit), |
181 |
:-( |
StmtName = filters_to_statement_name(Filters, LimitType), |
182 |
:-( |
case mongoose_rdbms:prepared(StmtName) of |
183 |
|
false -> |
184 |
|
%% Create a new type of a query |
185 |
:-( |
SQL = search_sql_binary(Filters, LimitType), |
186 |
:-( |
Columns = filters_to_columns(Filters, LimitType), |
187 |
:-( |
mongoose_rdbms:prepare(StmtName, vcard_search, Columns, SQL); |
188 |
|
true -> |
189 |
:-( |
ok |
190 |
|
end, |
191 |
:-( |
Args = filters_to_args(Filters, LimitType, Limit), |
192 |
:-( |
try mongoose_rdbms:execute(HostType, StmtName, Args) of |
193 |
|
{selected, Rs} when is_list(Rs) -> |
194 |
:-( |
record_to_items(Rs); |
195 |
|
Error -> |
196 |
:-( |
?LOG_ERROR(#{what => vcard_db_search_failed, statement => StmtName, |
197 |
|
sql_query => search_sql_binary(Filters, LimitType), |
198 |
:-( |
reason => Error, host => LServer}), |
199 |
:-( |
[] |
200 |
|
catch Class:Error:Stacktrace -> |
201 |
:-( |
?LOG_ERROR(#{what => vcard_db_search_failed, statement => StmtName, |
202 |
|
sql_query => search_sql_binary(Filters, LimitType), |
203 |
|
class => Class, stacktrace => Stacktrace, |
204 |
:-( |
reason => Error, host => LServer}), |
205 |
:-( |
[] |
206 |
|
end |
207 |
|
end. |
208 |
|
|
209 |
|
-spec limit_type(infinity | non_neg_integer()) -> limit_type(). |
210 |
|
limit_type(infinity) -> |
211 |
:-( |
infinity; |
212 |
|
limit_type(_Limit) -> |
213 |
:-( |
case mongoose_rdbms:db_type() of |
214 |
:-( |
mssql -> top; |
215 |
:-( |
_ -> limit |
216 |
|
end. |
217 |
|
|
218 |
|
%% Encodes filter column names using short format |
219 |
|
filters_to_statement_name(Filters, LimitType) -> |
220 |
:-( |
Ids = [type_to_id(Type) ++ column_to_id(Col) || {Type, Col, _Val} <- Filters], |
221 |
:-( |
LimitId = limit_type_to_id(LimitType), |
222 |
:-( |
list_to_atom("vcard_search_" ++ LimitId ++ "_" ++ lists:append(Ids)). |
223 |
|
|
224 |
|
filters_to_columns(Filters, LimitType) -> |
225 |
:-( |
Columns = [Col || {_Type, Col, _Val} <- Filters], |
226 |
|
%% <<"limit">> is a pseudocolumn: does not exist in the schema, |
227 |
|
%% but mssql's driver code needs to know which type to use for the placeholder. |
228 |
:-( |
case LimitType of |
229 |
:-( |
infinity -> Columns; |
230 |
:-( |
top -> [<<"limit">>|Columns]; |
231 |
:-( |
limit -> Columns ++ [<<"limit">>] |
232 |
|
end. |
233 |
|
|
234 |
|
filters_to_args(Filters, LimitType, Limit) -> |
235 |
:-( |
Args = [Val || {_Type, _Col, Val} <- Filters], |
236 |
:-( |
case LimitType of |
237 |
:-( |
infinity -> Args; |
238 |
:-( |
top -> [Limit|Args]; |
239 |
:-( |
limit -> Args ++ [Limit] |
240 |
|
end. |
241 |
|
|
242 |
|
search_sql_binary(Filters, LimitType) -> |
243 |
:-( |
iolist_to_binary(search_sql(Filters, LimitType)). |
244 |
|
|
245 |
|
search_sql(Filters, LimitType) -> |
246 |
:-( |
{TopSQL, LimitSQL} = limit_type_to_sql(LimitType), |
247 |
:-( |
RestrictionSQL = filters_to_sql(Filters), |
248 |
:-( |
[<<"SELECT ">>, TopSQL, |
249 |
|
<<" username, server, fn, family, given, middle, " |
250 |
|
"nickname, bday, ctry, locality, " |
251 |
|
"email, orgname, orgunit " |
252 |
|
"FROM vcard_search ">>, |
253 |
|
RestrictionSQL, LimitSQL]. |
254 |
|
|
255 |
|
-spec limit_type_to_sql(limit_type()) -> {binary(), binary()}. |
256 |
|
limit_type_to_sql(infinity) -> |
257 |
:-( |
{<<>>, <<>>}; |
258 |
|
limit_type_to_sql(top) -> |
259 |
:-( |
{<<" TOP (?) ">>, <<>>}; |
260 |
|
limit_type_to_sql(limit) -> |
261 |
:-( |
{<<>>, <<" LIMIT ? ">>}. |
262 |
|
|
263 |
|
filters_to_sql([Filter|Filters]) -> |
264 |
:-( |
[" WHERE ", filter_to_sql(Filter)| |
265 |
:-( |
[[" AND ", filter_to_sql(F)] || F <- Filters]]. |
266 |
|
|
267 |
|
filter_to_sql({equal, Col, _}) -> |
268 |
:-( |
[Col, "=?"]; |
269 |
|
filter_to_sql({like, Col, _}) -> |
270 |
:-( |
[Col, " LIKE ?"]. |
271 |
|
|
272 |
|
%% The result defines everything that is needed to prepare an SQL query. |
273 |
|
-spec make_filters(jid:lserver(), list()) -> [sql_filter()]. |
274 |
|
make_filters(LServer, Data) -> |
275 |
:-( |
Filters = only_tuples([filter_field(Var, Val) || {Var, [Val]} <- Data]), |
276 |
:-( |
case Filters of |
277 |
|
[] -> |
278 |
:-( |
[]; |
279 |
|
_ -> |
280 |
:-( |
HostFilter = {equal, <<"server">>, LServer}, |
281 |
:-( |
lists:sort([HostFilter|Filters]) |
282 |
|
end. |
283 |
|
|
284 |
|
only_tuples(List) -> |
285 |
:-( |
[X || X <- List, is_tuple(X)]. |
286 |
|
|
287 |
|
filter_field(Var, Val) when is_binary(Val) and (Val /= <<"">>) -> |
288 |
:-( |
case xmpp_field_to_column(Var) of |
289 |
|
false -> |
290 |
:-( |
false; |
291 |
|
Field -> |
292 |
:-( |
Type = value_type(Val), |
293 |
:-( |
LVal = prepare_value(Val, Type), |
294 |
:-( |
{Type, Field, LVal} |
295 |
|
end; |
296 |
|
filter_field(_, _) -> |
297 |
:-( |
false. |
298 |
|
|
299 |
|
prepare_value(Val, like) -> |
300 |
:-( |
LVal = without_last_byte(jid:str_tolower(Val)), |
301 |
:-( |
<<LVal/binary, "%">>; |
302 |
|
prepare_value(Val, equal) -> |
303 |
:-( |
jid:str_tolower(Val). |
304 |
|
|
305 |
|
value_type(Val) -> |
306 |
:-( |
case binary:last(Val) of |
307 |
|
$* -> |
308 |
:-( |
like; |
309 |
|
_ -> |
310 |
:-( |
equal |
311 |
|
end. |
312 |
|
|
313 |
:-( |
limit_type_to_id(infinity) -> "inf"; |
314 |
:-( |
limit_type_to_id(limit) -> "lim"; |
315 |
:-( |
limit_type_to_id(top) -> "top". |
316 |
|
|
317 |
:-( |
type_to_id(like) -> "l"; |
318 |
:-( |
type_to_id(equal) -> "e". |
319 |
|
|
320 |
:-( |
xmpp_field_to_column(<<"user">>) -> <<"lusername">>; |
321 |
:-( |
xmpp_field_to_column(<<"fn">>) -> <<"lfn">>; |
322 |
:-( |
xmpp_field_to_column(<<"last">>) -> <<"lfamily">>; |
323 |
:-( |
xmpp_field_to_column(<<"first">>) -> <<"lgiven">>; |
324 |
:-( |
xmpp_field_to_column(<<"middle">>) -> <<"lmiddle">>; |
325 |
:-( |
xmpp_field_to_column(<<"nick">>) -> <<"lnickname">>; |
326 |
:-( |
xmpp_field_to_column(<<"bday">>) -> <<"lbday">>; |
327 |
:-( |
xmpp_field_to_column(<<"ctry">>) -> <<"lctry">>; |
328 |
:-( |
xmpp_field_to_column(<<"locality">>) -> <<"llocality">>; |
329 |
:-( |
xmpp_field_to_column(<<"email">>) -> <<"lemail">>; |
330 |
:-( |
xmpp_field_to_column(<<"orgname">>) -> <<"lorgname">>; |
331 |
:-( |
xmpp_field_to_column(<<"orgunit">>) -> <<"lorgunit">>; |
332 |
:-( |
xmpp_field_to_column(_) -> false. |
333 |
|
|
334 |
:-( |
column_to_id(<<"server">>) -> "s"; |
335 |
:-( |
column_to_id(<<"lusername">>) -> "u"; |
336 |
:-( |
column_to_id(<<"lfn">>) -> "f"; |
337 |
:-( |
column_to_id(<<"lfamily">>) -> "l"; |
338 |
:-( |
column_to_id(<<"lgiven">>) -> "f"; |
339 |
:-( |
column_to_id(<<"lmiddle">>) -> "m"; |
340 |
:-( |
column_to_id(<<"lnickname">>) -> "n"; |
341 |
:-( |
column_to_id(<<"lbday">>) -> "b"; |
342 |
:-( |
column_to_id(<<"lctry">>) -> "c"; |
343 |
:-( |
column_to_id(<<"llocality">>) -> "L"; |
344 |
:-( |
column_to_id(<<"lemail">>) -> "e"; |
345 |
:-( |
column_to_id(<<"lorgname">>) -> "N"; |
346 |
:-( |
column_to_id(<<"lorgunit">>) -> "U". |
347 |
|
|
348 |
|
search_columns() -> |
349 |
:-( |
[<<"username">>, |
350 |
|
<<"fn">>, <<"lfn">>, |
351 |
|
<<"family">>, <<"lfamily">>, |
352 |
|
<<"given">>, <<"lgiven">>, |
353 |
|
<<"middle">>, <<"lmiddle">>, |
354 |
|
<<"nickname">>, <<"lnickname">>, |
355 |
|
<<"bday">>, <<"lbday">>, |
356 |
|
<<"ctry">>, <<"lctry">>, |
357 |
|
<<"locality">>, <<"llocality">>, |
358 |
|
<<"email">>, <<"lemail">>, |
359 |
|
<<"orgname">>, <<"lorgname">>, |
360 |
|
<<"orgunit">>, <<"lorgunit">>]. |
361 |
|
|
362 |
|
search_args(User, Search) -> |
363 |
:-( |
[User, |
364 |
|
Search#vcard_search.fn, Search#vcard_search.lfn, |
365 |
|
Search#vcard_search.family, Search#vcard_search.lfamily, |
366 |
|
Search#vcard_search.given, Search#vcard_search.lgiven, |
367 |
|
Search#vcard_search.middle, Search#vcard_search.lmiddle, |
368 |
|
Search#vcard_search.nickname, Search#vcard_search.lnickname, |
369 |
|
Search#vcard_search.bday, Search#vcard_search.lbday, |
370 |
|
Search#vcard_search.ctry, Search#vcard_search.lctry, |
371 |
|
Search#vcard_search.locality, Search#vcard_search.llocality, |
372 |
|
Search#vcard_search.email, Search#vcard_search.lemail, |
373 |
|
Search#vcard_search.orgname, Search#vcard_search.lorgname, |
374 |
|
Search#vcard_search.orgunit, Search#vcard_search.lorgunit]. |
375 |
|
|
376 |
|
without_last_byte(Bin) -> |
377 |
:-( |
binary:part(Bin, 0, byte_size(Bin)-1). |
378 |
|
|
379 |
|
record_to_items(Records) -> |
380 |
:-( |
[record_to_item(Record) || Record <- Records]. |
381 |
|
|
382 |
|
record_to_item({Username, VCardVHost, FN, Family, Given, Middle, |
383 |
|
Nickname, BDay, CTRY, Locality, |
384 |
|
EMail, OrgName, OrgUnit}) -> |
385 |
:-( |
[ |
386 |
|
?FIELD(<<"jid">>, <<Username/binary, "@", VCardVHost/binary>>), |
387 |
|
?FIELD(<<"fn">>, FN), |
388 |
|
?FIELD(<<"last">>, Family), |
389 |
|
?FIELD(<<"first">>, Given), |
390 |
|
?FIELD(<<"middle">>, Middle), |
391 |
|
?FIELD(<<"nick">>, Nickname), |
392 |
|
?FIELD(<<"bday">>, BDay), |
393 |
|
?FIELD(<<"ctry">>, CTRY), |
394 |
|
?FIELD(<<"locality">>, Locality), |
395 |
|
?FIELD(<<"email">>, EMail), |
396 |
|
?FIELD(<<"orgname">>, OrgName), |
397 |
|
?FIELD(<<"orgunit">>, OrgUnit) |
398 |
|
]. |