./ct_report/coverage/mod_vcard_rdbms.COVER.html

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 179 mongoose_rdbms:prepare(vcard_remove, vcard, [username, server],
52 <<"DELETE FROM vcard WHERE username=? AND server=?">>),
53 179 mongoose_rdbms:prepare(vcard_search_remove, vcard_search, [lusername, server],
54 <<"DELETE FROM vcard_search WHERE lusername=? AND server=?">>),
55 179 mongoose_rdbms:prepare(vcard_remove_domain, vcard, [server],
56 <<"DELETE FROM vcard WHERE server=?">>),
57 179 mongoose_rdbms:prepare(vcard_search_remove_domain, vcard_search, [server],
58 <<"DELETE FROM vcard_search WHERE server=?">>),
59 179 mongoose_rdbms:prepare(vcard_select, vcard,
60 [username, server],
61 <<"SELECT vcard FROM vcard WHERE username=? AND server=?">>),
62 179 rdbms_queries:prepare_upsert(HostType, vcard_upsert, vcard,
63 [<<"username">>, <<"server">>, <<"vcard">>],
64 [<<"vcard">>],
65 [<<"username">>, <<"server">>]),
66 179 SearchColumns = search_columns(),
67 179 rdbms_queries:prepare_upsert(HostType, vcard_search_upsert, vcard_search,
68 [<<"lusername">>, <<"server">>|SearchColumns],
69 SearchColumns,
70 [<<"lusername">>, <<"server">>]),
71 179 ok.
72
73 %% Remove user callback
74 remove_user(HostType, LUser, LServer) ->
75 5397 F = fun() -> remove_user_t(HostType, LUser, LServer) end,
76 5397 mongoose_rdbms:sql_transaction(HostType, F).
77
78 remove_user_t(HostType, LUser, LServer) ->
79 5397 mongoose_rdbms:execute(HostType, vcard_remove, [LUser, LServer]),
80 5397 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 20 F = fun() -> remove_domain_t(HostType, Domain) end,
86 20 mongoose_rdbms:sql_transaction(HostType, F),
87 20 ok.
88
89 remove_domain_t(HostType, Domain) ->
90 20 mongoose_rdbms:execute_successfully(HostType, vcard_remove_domain, [Domain]),
91 20 mongoose_rdbms:execute_successfully(HostType, vcard_search_remove_domain, [Domain]).
92
93 %% Get a single vCard callback
94 get_vcard(HostType, LUser, LServer) ->
95 114 Res = mongoose_rdbms:execute(HostType, vcard_select, [LUser, LServer]),
96 114 case Res of
97 {selected, [{SVCARD}]} ->
98 37 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 37 {ok, [VCARD]}
107 end;
108 {selected, []} ->
109 77 {error, mongoose_xmpp_errors:item_not_found()}
110 end.
111
112 %% Set a vCard callback
113 set_vcard(HostType, User, LServer, VCard, Search) ->
114 35 LUser = jid:nodeprep(User),
115 35 SearchArgs = assert_binaries(search_args(User, Search)),
116 35 XML = exml:to_binary(VCard),
117 35 F = fun() ->
118 35 update_vcard_t(HostType, LUser, LServer, XML),
119 35 update_vcard_search_t(HostType, LUser, LServer, SearchArgs),
120 35 ok
121 end,
122 35 Result = handle_result(rdbms_queries:sql_transaction(HostType, F)),
123 35 log_upsert_result(HostType, LServer, LUser, VCard, XML, Result),
124 35 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 35 case lists:all(fun is_binary/1, Bins) of
132 true ->
133 35 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 35 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 35 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 35 InsertParams = [LUser, LServer, XML],
151 35 UpdateParams = [XML],
152 35 UniqueKeyValues = [LUser, LServer],
153 35 rdbms_queries:execute_upsert(HostType, vcard_upsert, InsertParams, UpdateParams, UniqueKeyValues).
154
155 update_vcard_search_t(HostType, LUser, LServer, SearchArgs) ->
156 35 InsertParams = [LUser, LServer|SearchArgs],
157 35 UpdateParams = SearchArgs,
158 35 UniqueKeyValues = [LUser, LServer],
159 35 rdbms_queries:execute_upsert(HostType, vcard_search_upsert, InsertParams, UpdateParams, UniqueKeyValues).
160
161 %% Search vCards fields callback
162 search_fields(_HostType, _VHost) ->
163 2 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 21 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 23 Filters = make_filters(LServer, Data),
175 23 case Filters of
176 [] ->
177 2 [];
178 _ ->
179 21 Limit = mod_vcard:get_results_limit(HostType),
180 21 LimitType = limit_type(Limit),
181 21 StmtName = filters_to_statement_name(Filters, LimitType),
182 21 case mongoose_rdbms:prepared(StmtName) of
183 false ->
184 %% Create a new type of a query
185 5 SQL = search_sql_binary(Filters, LimitType),
186 5 Columns = filters_to_columns(Filters, LimitType),
187 5 mongoose_rdbms:prepare(StmtName, vcard_search, Columns, SQL);
188 true ->
189 16 ok
190 end,
191 21 Args = filters_to_args(Filters, LimitType, Limit),
192 21 try mongoose_rdbms:execute(HostType, StmtName, Args) of
193 {selected, Rs} when is_list(Rs) ->
194 21 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 21 case mongoose_rdbms:db_type() of
214 
:-(
 mssql -> top;
215 21 _ -> limit
216 end.
217
218 %% Encodes filter column names using short format
219 filters_to_statement_name(Filters, LimitType) ->
220 21 Ids = [type_to_id(Type) ++ column_to_id(Col) || {Type, Col, _Val} <- Filters],
221 21 LimitId = limit_type_to_id(LimitType),
222 21 list_to_atom("vcard_search_" ++ LimitId ++ "_" ++ lists:append(Ids)).
223
224 filters_to_columns(Filters, LimitType) ->
225 5 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 5 case LimitType of
229 
:-(
 infinity -> Columns;
230 
:-(
 top -> [<<"limit">>|Columns];
231 5 limit -> Columns ++ [<<"limit">>]
232 end.
233
234 filters_to_args(Filters, LimitType, Limit) ->
235 21 Args = [Val || {_Type, _Col, Val} <- Filters],
236 21 case LimitType of
237 
:-(
 infinity -> Args;
238 
:-(
 top -> [Limit|Args];
239 21 limit -> Args ++ [Limit]
240 end.
241
242 search_sql_binary(Filters, LimitType) ->
243 5 iolist_to_binary(search_sql(Filters, LimitType)).
244
245 search_sql(Filters, LimitType) ->
246 5 {TopSQL, LimitSQL} = limit_type_to_sql(LimitType),
247 5 RestrictionSQL = filters_to_sql(Filters),
248 5 [<<"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 5 {<<>>, <<" LIMIT ? ">>}.
262
263 filters_to_sql([Filter|Filters]) ->
264 5 [" WHERE ", filter_to_sql(Filter)|
265 6 [[" AND ", filter_to_sql(F)] || F <- Filters]].
266
267 filter_to_sql({equal, Col, _}) ->
268 10 [Col, "=?"];
269 filter_to_sql({like, Col, _}) ->
270 1 [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 23 Filters = only_tuples([filter_field(Var, Val) || {Var, [Val]} <- Data]),
276 23 case Filters of
277 [] ->
278 2 [];
279 _ ->
280 21 HostFilter = {equal, <<"server">>, LServer},
281 21 lists:sort([HostFilter|Filters])
282 end.
283
284 only_tuples(List) ->
285 23 [X || X <- List, is_tuple(X)].
286
287 filter_field(Var, Val) when is_binary(Val) and (Val /= <<"">>) ->
288 22 case xmpp_field_to_column(Var) of
289 false ->
290 
:-(
 false;
291 Field ->
292 22 Type = value_type(Val),
293 22 LVal = prepare_value(Val, Type),
294 22 {Type, Field, LVal}
295 end;
296 filter_field(_, _) ->
297 
:-(
 false.
298
299 prepare_value(Val, like) ->
300 11 LVal = without_last_byte(jid:str_tolower(Val)),
301 11 <<LVal/binary, "%">>;
302 prepare_value(Val, equal) ->
303 11 jid:str_tolower(Val).
304
305 value_type(Val) ->
306 22 case binary:last(Val) of
307 $* ->
308 11 like;
309 _ ->
310 11 equal
311 end.
312
313 
:-(
 limit_type_to_id(infinity) -> "inf";
314 21 limit_type_to_id(limit) -> "lim";
315 
:-(
 limit_type_to_id(top) -> "top".
316
317 11 type_to_id(like) -> "l";
318 32 type_to_id(equal) -> "e".
319
320 
:-(
 xmpp_field_to_column(<<"user">>) -> <<"lusername">>;
321 18 xmpp_field_to_column(<<"fn">>) -> <<"lfn">>;
322 2 xmpp_field_to_column(<<"last">>) -> <<"lfamily">>;
323 1 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 1 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 21 column_to_id(<<"server">>) -> "s";
335 
:-(
 column_to_id(<<"lusername">>) -> "u";
336 18 column_to_id(<<"lfn">>) -> "f";
337 2 column_to_id(<<"lfamily">>) -> "l";
338 1 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 1 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 179 [<<"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 35 [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 11 binary:part(Bin, 0, byte_size(Bin)-1).
378
379 record_to_items(Records) ->
380 21 [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 27 [
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 ].
Line Hits Source