./ct_report/coverage/mongoose_domain_sql.COVER.html

1 -module(mongoose_domain_sql).
2
3 -export([start/1]).
4
5 -export([insert_domain/2,
6 delete_domain/2,
7 set_domain_for_deletion/2,
8 set_status/2]).
9
10 -export([select_domain_admin/1,
11 set_domain_admin/2,
12 delete_domain_admin/1]).
13
14 -export([select_domain/1,
15 get_minmax_event_id/0,
16 count_events_between_ids/2,
17 get_event_ids_between/2,
18 select_from/2,
19 select_updates_between/2,
20 get_enabled_dynamic/0,
21 delete_events_older_than/1,
22 insert_dummy_event/1]).
23
24 %% interfaces only for integration tests
25 -export([prepare_test_queries/0,
26 erase_database/1,
27 insert_full_event/2,
28 insert_domain_settings_without_event/2]).
29
30 -ignore_xref([erase_database/1, prepare_test_queries/0, get_enabled_dynamic/0,
31 insert_full_event/2, insert_domain_settings_without_event/2]).
32
33 -import(mongoose_rdbms, [prepare/4, execute_successfully/3]).
34
35 -type event_id() :: non_neg_integer().
36 -type domain() :: jid:lserver().
37 -type row() :: {event_id(), domain(), mongooseim:host_type() | null}.
38 -export_type([row/0]).
39
40 start(_) ->
41 259 {LimitSQL, LimitMSSQL} = rdbms_queries:get_db_specific_limits_binaries(),
42 259 Enabled = integer_to_binary(status_to_int(enabled)),
43 %% Settings
44 259 prepare(domain_insert_settings, domain_settings, [domain, host_type],
45 <<"INSERT INTO domain_settings (domain, host_type) "
46 "VALUES (?, ?)">>),
47 259 prepare(domain_update_settings_status, domain_settings,
48 [status, domain],
49 <<"UPDATE domain_settings "
50 "SET status = ? "
51 "WHERE domain = ?">>),
52 259 prepare(domain_delete_settings, domain_settings, [domain],
53 <<"DELETE FROM domain_settings WHERE domain = ?">>),
54 259 prepare(domain_select, domain_settings, [domain],
55 <<"SELECT host_type, status "
56 "FROM domain_settings WHERE domain = ?">>),
57 259 prepare(domain_select_from, domain_settings,
58 rdbms_queries:add_limit_arg(limit, [id]),
59 <<"SELECT ", LimitMSSQL/binary,
60 " id, domain, host_type "
61 " FROM domain_settings "
62 " WHERE id > ? AND status = ", Enabled/binary, " "
63 " ORDER BY id ",
64 LimitSQL/binary>>),
65 %% Events
66 259 prepare(domain_insert_event, domain_events, [domain],
67 <<"INSERT INTO domain_events (domain) VALUES (?)">>),
68 259 prepare(domain_insert_full_event, domain_events, [id, domain],
69 <<"INSERT INTO domain_events (id, domain) VALUES (?, ?)">>),
70 259 prepare(domain_events_minmax, domain_events, [],
71 <<"SELECT MIN(id), MAX(id) FROM domain_events">>),
72 259 prepare(domain_count_event_between, domain_events, [id, id],
73 <<"SELECT COUNT(*) FROM domain_events WHERE id >= ? AND id <= ?">>),
74 259 prepare(domain_event_ids_between, domain_events, [id, id],
75 <<"SELECT id FROM domain_events WHERE id >= ? AND id <= ? ORDER BY id">>),
76 259 prepare(domain_events_delete_older_than, domain_events, [id],
77 <<"DELETE FROM domain_events WHERE id < ?">>),
78 259 prepare(domain_select_events_between, domain_events, [id, id],
79 <<"SELECT "
80 " domain_events.id, domain_events.domain, domain_settings.host_type "
81 " FROM domain_events "
82 " LEFT JOIN domain_settings ON "
83 "(domain_settings.domain = domain_events.domain AND "
84 "domain_settings.status = ", Enabled/binary, ") "
85 " WHERE domain_events.id >= ? AND domain_events.id <= ? "
86 " ORDER BY domain_events.id ">>),
87 %% Admins
88 259 prepare(domain_insert_admin, domain_admins, [domain, pass_details],
89 <<"INSERT INTO domain_admins (domain, pass_details) VALUES (?, ?)">>),
90 259 prepare(domain_update_admin, domain_admins, [pass_details, domain],
91 <<"UPDATE domain_admins"
92 " SET pass_details = ? "
93 " WHERE domain = ?">>),
94 259 prepare(domain_delete_admin, domain_admins, [domain],
95 <<"DELETE FROM domain_admins WHERE domain = ?">>),
96 259 prepare(domain_select_admin, domain_admins, [domain],
97 <<"SELECT domain, pass_details"
98 " FROM domain_admins WHERE domain = ?">>),
99 259 ok.
100
101 prepare_test_queries() ->
102 43 Enabled = integer_to_binary(status_to_int(enabled)),
103 43 prepare(domain_erase_admins, domain_admins, [],
104 <<"DELETE FROM domain_admins">>),
105 43 prepare(domain_erase_settings, domain_settings, [],
106 <<"DELETE FROM domain_settings">>),
107 43 prepare(domain_erase_events, domain_events, [],
108 <<"DELETE FROM domain_events">>),
109 43 prepare(domain_get_status_dynamic, domain_settings, [],
110 <<"SELECT "
111 " domain, host_type "
112 " FROM domain_settings "
113 " WHERE status = ", Enabled/binary, " "
114 " ORDER BY id">>),
115 43 prepare(domain_events_get_all, domain_events, [],
116 <<"SELECT id, domain FROM domain_events ORDER BY id">>).
117
118 %% ----------------------------------------------------------------------------
119 %% API
120 insert_domain(Domain, HostType) ->
121 50 transaction(fun(Pool) ->
122 50 case select_domain(Domain) of
123 {error, not_found} ->
124 46 insert_domain_settings(Pool, Domain, HostType),
125 46 insert_domain_event(Pool, Domain),
126 46 ok;
127 {ok, _} ->
128 4 {error, duplicate}
129 end
130 end).
131
132 select_domain(Domain) ->
133 113 Pool = get_db_pool(),
134 113 case execute_successfully(Pool, domain_select, [Domain]) of
135 {selected, []} ->
136 59 {error, not_found};
137 {selected, [Row]} ->
138 54 {ok, row_to_map(Row)}
139 end.
140
141 delete_domain(Domain, HostType) ->
142 11 transaction(fun(Pool) ->
143 11 case select_domain(Domain) of
144 {ok, #{host_type := HT}} when HT =:= HostType ->
145 11 {updated, 1} = delete_domain_settings(Pool, Domain),
146 11 insert_domain_event(Pool, Domain),
147 11 ok;
148 {ok, _} ->
149 
:-(
 {error, wrong_host_type};
150 {error, not_found} ->
151 
:-(
 ok
152 end
153 end).
154
155 set_domain_for_deletion(Domain, HostType) ->
156 18 transaction(fun(Pool) ->
157 18 case select_domain(Domain) of
158 {ok, #{host_type := HT}} when HT =:= HostType ->
159 11 {updated, 1} = set_domain_for_deletion_settings(Pool, Domain),
160 11 insert_domain_event(Pool, Domain),
161 11 ok;
162 {ok, _} ->
163 3 {error, wrong_host_type};
164 {error, not_found} ->
165 4 {error, not_found}
166 end
167 end).
168
169 select_domain_admin(Domain) ->
170 515 Pool = get_db_pool(),
171 515 case execute_successfully(Pool, domain_select_admin, [Domain]) of
172 {selected, []} ->
173 24 {error, not_found};
174 {selected, [Row]} ->
175 491 {ok, Row}
176 end.
177
178 set_domain_admin(Domain, Password) ->
179 28 Iterations = mongoose_scram:iterations(),
180 28 HashMap = mongoose_scram:password_to_scram_sha(Password, Iterations, sha512),
181 28 PassDetails = mongoose_scram:serialize(HashMap),
182 28 transaction(fun(Pool) ->
183 28 case select_domain_admin(Domain) of
184 {ok, _} ->
185 20 update_domain_admin(Pool, Domain, PassDetails),
186 20 ok;
187 {error, not_found} ->
188 8 insert_domain_admin(Pool, Domain, PassDetails),
189 8 ok
190 end
191 end).
192
193 delete_domain_admin(Domain) ->
194 19 transaction(fun(Pool) ->
195 19 case select_domain_admin(Domain) of
196 {ok, _} ->
197 6 {updated, 1} = delete_domain_admin(Pool, Domain),
198 6 ok;
199 {error, not_found} ->
200 13 {error, not_found}
201 end
202 end).
203
204 insert_domain_admin(Pool, Domain, PassDetails) ->
205 8 execute_successfully(Pool, domain_insert_admin, [Domain, PassDetails]).
206
207 update_domain_admin(Pool, Domain, PassDetails) ->
208 20 execute_successfully(Pool, domain_update_admin, [PassDetails, Domain]).
209
210 delete_domain_admin(Pool, Domain) ->
211 6 execute_successfully(Pool, domain_delete_admin, [Domain]).
212
213 %% Returns smallest id first
214 select_from(FromId, Limit) ->
215 274 Pool = get_db_pool(),
216 274 Args = rdbms_queries:add_limit_arg(Limit, [FromId]),
217 274 {selected, Rows} = execute_successfully(Pool, domain_select_from, Args),
218 274 Rows.
219
220 get_enabled_dynamic() ->
221 
:-(
 Pool = get_db_pool(),
222 
:-(
 prepare_test_queries(),
223 
:-(
 {selected, Rows} = execute_successfully(Pool, domain_get_status_dynamic, []),
224 
:-(
 Rows.
225
226 %% FromId, ToId are included into the result
227 -spec select_updates_between(event_id(), event_id()) -> [row()].
228 select_updates_between(FromId, ToId) ->
229 208 Pool = get_db_pool(),
230 208 Args = [FromId, ToId],
231 208 {selected, Rows} = execute_successfully(Pool, domain_select_events_between, Args),
232 207 Rows.
233
234 get_minmax_event_id() ->
235 1121 Pool = get_db_pool(),
236 1121 {selected, [{Min, Max}]} = execute_successfully(Pool, domain_events_minmax, []),
237 1115 case Min of
238 null ->
239 595 {null, null};
240 _ ->
241 520 {mongoose_rdbms:result_to_integer(Min),
242 mongoose_rdbms:result_to_integer(Max)}
243 end.
244
245 count_events_between_ids(Min, Max) ->
246 4 Pool = get_db_pool(),
247 4 Selected = execute_successfully(Pool, domain_count_event_between, [Min, Max]),
248 4 mongoose_rdbms:selected_to_integer(Selected).
249
250 %% Min and Max are included in the result set
251 get_event_ids_between(Min, Max) ->
252 80 Pool = get_db_pool(),
253 80 {selected, Rows} = execute_successfully(Pool, domain_event_ids_between, [Min, Max]),
254 80 [mongoose_rdbms:result_to_integer(Id) || {Id} <- Rows].
255
256 delete_events_older_than(Id) ->
257 3 transaction(fun(Pool) ->
258 3 execute_successfully(Pool, domain_events_delete_older_than, [Id])
259 end).
260
261 insert_dummy_event(EventId) ->
262 22 insert_full_event(EventId, <<>>).
263
264 insert_full_event(EventId, Domain) ->
265 31 case mongoose_rdbms:db_type() of
266 mssql ->
267 31 insert_full_event_mssql(EventId, Domain);
268 _ ->
269 
:-(
 Pool = get_db_pool(),
270 
:-(
 execute_successfully(Pool, domain_insert_full_event, [EventId, Domain])
271 end.
272
273 insert_full_event_mssql(EventId, Domain) ->
274 %% MSSQL does not allow to specify ids,
275 %% that are supposed to be autoincremented, easily
276 %% https://docs.microsoft.com/pl-pl/sql/t-sql/statements/set-identity-insert-transact-sql
277 31 transaction(fun(Pool) ->
278 %% This query could not be a prepared query
279 %% You will get an error:
280 %% "No SQL-driver information available."
281 %% when trying to execute
282 105 mongoose_rdbms:sql_query(Pool, <<"SET IDENTITY_INSERT domain_events ON">>),
283 105 try
284 105 execute_successfully(Pool, domain_insert_full_event, [EventId, Domain])
285 after
286 105 mongoose_rdbms:sql_query(Pool, <<"SET IDENTITY_INSERT domain_events OFF">>)
287 end
288 end).
289
290 %% ----------------------------------------------------------------------------
291 %% For testing
292
293 erase_database(Pool) ->
294 41 execute_successfully(Pool, domain_erase_events, []),
295 41 execute_successfully(Pool, domain_erase_settings, []),
296 41 execute_successfully(Pool, domain_erase_admins, []).
297
298 insert_domain_settings_without_event(Domain, HostType) ->
299 
:-(
 Pool = get_db_pool(),
300 
:-(
 execute_successfully(Pool, domain_insert_settings, [Domain, HostType]).
301
302 %% ----------------------------------------------------------------------------
303
304 %% Inserts a new event with an autoincremented EventId.
305 %% Rows would not appear in the EventId order, RDBMS likes to rearrange them.
306 %% Example of rearranging:
307 %% Events with ids [1, 2, 3] could appear as:
308 %% 1. [1]
309 %% 2. [1, 3] - at this step record with EventId=2 is not visible yet.
310 %% 3. [1, 2, 3] - and finally everything is fine.
311 insert_domain_event(Pool, Domain) ->
312 79 execute_successfully(Pool, domain_insert_event, [Domain]).
313
314 insert_domain_settings(Pool, Domain, HostType) ->
315 46 execute_successfully(Pool, domain_insert_settings, [Domain, HostType]).
316
317 delete_domain_settings(Pool, Domain) ->
318 11 execute_successfully(Pool, domain_delete_settings, [Domain]).
319
320 set_domain_for_deletion_settings(Pool, Domain) ->
321 11 ExtStatus = status_to_int(deleting),
322 11 execute_successfully(Pool, domain_update_settings_status, [ExtStatus, Domain]).
323
324 -spec set_status(domain(), enabled | disabled) -> ok | {error, term()}.
325 set_status(Domain, Status) ->
326 17 transaction(fun(Pool) ->
327 17 case select_domain(Domain) of
328 {error, Reason} ->
329 4 {error, Reason};
330 {ok, #{status := CurrentStatus, host_type := HostType}} ->
331 13 case mongoose_domain_core:is_host_type_allowed(HostType) of
332 false ->
333 2 {error, unknown_host_type};
334 true when deleting =:= CurrentStatus ->
335 
:-(
 {error, domain_deleted};
336 true when Status =:= CurrentStatus ->
337 
:-(
 ok;
338 true ->
339 11 update_domain_enabled(Pool, Domain, Status),
340 11 insert_domain_event(Pool, Domain),
341 11 ok
342 end
343 end
344 end).
345
346 update_domain_enabled(Pool, Domain, Status) ->
347 11 ExtStatus = status_to_int(Status),
348 11 execute_successfully(Pool, domain_update_settings_status, [ExtStatus, Domain]).
349
350 row_to_map({HostType, Status}) ->
351 54 IntStatus = mongoose_rdbms:result_to_integer(Status),
352 54 #{host_type => HostType, status => int_to_status(IntStatus)}.
353
354 -spec int_to_status(0..2) -> mongoose_domain_api:status().
355 8 int_to_status(0) -> disabled;
356 35 int_to_status(1) -> enabled;
357 11 int_to_status(2) -> deleting.
358
359 -spec status_to_int(mongoose_domain_api:status()) -> 0..2.
360 7 status_to_int(disabled) -> 0;
361 306 status_to_int(enabled) -> 1;
362 11 status_to_int(deleting) -> 2.
363
364 get_db_pool() ->
365 2496 mongoose_config:get_opt([services, service_domain_db, db_pool]).
366
367 transaction(F) ->
368 177 transaction(F, 3, []).
369
370 %% MSSQL especially likes to kill a connection deadlocked by tablock connections.
371 %% But that's fine, we could just restart
372 %% (there is no logic, that would suffer by a restart of a transaction).
373 transaction(_F, 0, Errors) ->
374 2 {error, {db_error, Errors}};
375 transaction(F, Retries, Errors) when Retries > 0 ->
376 181 Pool = get_db_pool(),
377 181 Result = rdbms_queries:sql_transaction(Pool, fun() -> F(Pool) end),
378 181 case Result of
379 {aborted, _} -> %% Restart any rolled back transaction
380 7 timer:sleep(100), %% Small break before retry
381 6 transaction(F, Retries - 1, [Result|Errors]);
382 _ ->
383 174 simple_result(Result)
384 end.
385
386 174 simple_result({atomic, Result}) -> Result;
387 
:-(
 simple_result(Other) -> {error, {db_error, Other}}.
Line Hits Source