./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 disable_domain/1,
8 enable_domain/1]).
9
10 -export([select_domain/1,
11 get_minmax_event_id/0,
12 count_events_between_ids/2,
13 get_event_ids_between/2,
14 select_from/2,
15 select_updates_between/2,
16 get_enabled_dynamic/0,
17 delete_events_older_than/1,
18 insert_dummy_event/1]).
19
20 %% interfaces only for integration tests
21 -export([prepare_test_queries/0,
22 erase_database/0,
23 insert_full_event/2,
24 insert_domain_settings_without_event/2]).
25
26 -ignore_xref([erase_database/0, prepare_test_queries/0, get_enabled_dynamic/0,
27 insert_full_event/2, insert_domain_settings_without_event/2]).
28
29 -include("mongoose_logger.hrl").
30
31 -import(mongoose_rdbms, [prepare/4, execute_successfully/3]).
32
33 -type event_id() :: non_neg_integer().
34 -type domain() :: binary().
35 -type row() :: {event_id(), domain(), mongooseim:host_type() | null}.
36 -export_type([row/0]).
37
38 start(_Opts) ->
39 225 {LimitSQL, LimitMSSQL} = rdbms_queries:get_db_specific_limits_binaries(),
40 225 Pool = get_db_pool(),
41 225 True = sql_true(Pool),
42 %% Settings
43 225 prepare(domain_insert_settings, domain_settings, [domain, host_type],
44 <<"INSERT INTO domain_settings (domain, host_type) "
45 "VALUES (?, ?)">>),
46 225 prepare(domain_update_settings_enabled, domain_settings,
47 [enabled, domain],
48 <<"UPDATE domain_settings "
49 "SET enabled = ? "
50 "WHERE domain = ?">>),
51 225 prepare(domain_delete_settings, domain_settings, [domain],
52 <<"DELETE FROM domain_settings WHERE domain = ?">>),
53 225 prepare(domain_select, domain_settings, [domain],
54 <<"SELECT host_type, enabled "
55 "FROM domain_settings WHERE domain = ?">>),
56 225 prepare(domain_select_from, domain_settings,
57 rdbms_queries:add_limit_arg(limit, [id]),
58 <<"SELECT ", LimitMSSQL/binary,
59 " id, domain, host_type "
60 " FROM domain_settings "
61 " WHERE id > ? AND enabled = ", True/binary, " "
62 " ORDER BY id ",
63 LimitSQL/binary>>),
64 %% Events
65 225 prepare(domain_insert_event, domain_events, [domain],
66 <<"INSERT INTO domain_events (domain) VALUES (?)">>),
67 225 prepare(domain_insert_full_event, domain_events, [id, domain],
68 <<"INSERT INTO domain_events (id, domain) VALUES (?, ?)">>),
69 225 prepare(domain_events_minmax, domain_events, [],
70 <<"SELECT MIN(id), MAX(id) FROM domain_events">>),
71 225 prepare(domain_count_event_between, domain_events, [id, id],
72 <<"SELECT COUNT(*) FROM domain_events WHERE id >= ? AND id <= ?">>),
73 225 prepare(domain_event_ids_between, domain_events, [id, id],
74 <<"SELECT id FROM domain_events WHERE id >= ? AND id <= ? ORDER BY id">>),
75 225 prepare(domain_events_delete_older_than, domain_events, [id],
76 <<"DELETE FROM domain_events WHERE id < ?">>),
77 225 prepare(domain_select_events_between, domain_events, [id, id],
78 <<"SELECT "
79 " domain_events.id, domain_events.domain, domain_settings.host_type "
80 " FROM domain_events "
81 " LEFT JOIN domain_settings ON "
82 "(domain_settings.domain = domain_events.domain AND "
83 "domain_settings.enabled = ", True/binary, ") "
84 " WHERE domain_events.id >= ? AND domain_events.id <= ? "
85 " ORDER BY domain_events.id ">>),
86 225 ok.
87
88 prepare_test_queries() ->
89 38 Pool = get_db_pool(),
90 38 True = sql_true(Pool),
91 38 prepare(domain_erase_settings, domain_settings, [],
92 <<"DELETE FROM domain_settings">>),
93 38 prepare(domain_erase_events, domain_events, [],
94 <<"DELETE FROM domain_events">>),
95 38 prepare(domain_get_enabled_dynamic, domain_settings, [],
96 <<"SELECT "
97 " domain, host_type "
98 " FROM domain_settings "
99 " WHERE enabled = ", True/binary, " "
100 " ORDER BY id">>),
101 38 prepare(domain_events_get_all, domain_events, [],
102 <<"SELECT id, domain FROM domain_events ORDER BY id">>).
103
104 sql_true(Pool) ->
105 263 case mongoose_rdbms:db_engine(Pool) of
106 
:-(
 pgsql -> <<"true">>;
107 263 _ -> <<"1">>
108 end.
109
110 %% ----------------------------------------------------------------------------
111 %% API
112 insert_domain(Domain, HostType) ->
113 48 transaction(fun(Pool) ->
114 48 case select_domain(Domain) of
115 {ok, #{host_type := HT}} when HT =:= HostType ->
116 3 ok; %% ignore second call
117 {error, not_found} ->
118 43 insert_domain_settings(Pool, Domain, HostType),
119 43 insert_domain_event(Pool, Domain),
120 43 ok;
121 {ok, _} ->
122 2 {error, duplicate}
123 end
124 end).
125
126 select_domain(Domain) ->
127 82 Pool = get_db_pool(),
128 82 case execute_successfully(Pool, domain_select, [Domain]) of
129 {selected, []} ->
130 48 {error, not_found};
131 {selected, [Row]} ->
132 34 {ok, row_to_map(Row)}
133 end.
134
135 delete_domain(Domain, HostType) ->
136 10 transaction(fun(Pool) ->
137 10 case select_domain(Domain) of
138 {ok, #{host_type := HT}} when HT =:= HostType ->
139 7 {updated, 1} = delete_domain_settings(Pool, Domain),
140 7 insert_domain_event(Pool, Domain),
141 7 ok;
142 {ok, _} ->
143 2 {error, wrong_host_type};
144 {error, not_found} ->
145 1 ok
146 end
147 end).
148
149 disable_domain(Domain) ->
150 8 set_enabled(Domain, false).
151
152 enable_domain(Domain) ->
153 5 set_enabled(Domain, true).
154
155 %% Returns smallest id first
156 select_from(FromId, Limit) ->
157 296 Pool = get_db_pool(),
158 296 Args = rdbms_queries:add_limit_arg(Limit, [FromId]),
159 296 {selected, Rows} = execute_successfully(Pool, domain_select_from, Args),
160 295 Rows.
161
162 get_enabled_dynamic() ->
163 
:-(
 prepare_test_queries(),
164 
:-(
 Pool = get_db_pool(),
165 
:-(
 {selected, Rows} = execute_successfully(Pool, domain_get_enabled_dynamic, []),
166 
:-(
 Rows.
167
168 %% FromId, ToId are included into the result
169 -spec select_updates_between(event_id(), event_id()) -> [row()].
170 select_updates_between(FromId, ToId) ->
171 170 Pool = get_db_pool(),
172 170 Args = [FromId, ToId],
173 170 {selected, Rows} = execute_successfully(Pool, domain_select_events_between, Args),
174 167 Rows.
175
176 get_minmax_event_id() ->
177 944 Pool = get_db_pool(),
178 944 {selected, [{Min, Max}]} = execute_successfully(Pool, domain_events_minmax, []),
179 943 case Min of
180 null ->
181 453 {null, null};
182 _ ->
183 490 {mongoose_rdbms:result_to_integer(Min),
184 mongoose_rdbms:result_to_integer(Max)}
185 end.
186
187 count_events_between_ids(Min, Max) ->
188 4 Pool = get_db_pool(),
189 4 Selected = execute_successfully(Pool, domain_count_event_between, [Min, Max]),
190 4 mongoose_rdbms:selected_to_integer(Selected).
191
192 %% Min and Max are included in the result set
193 get_event_ids_between(Min, Max) ->
194 80 Pool = get_db_pool(),
195 80 {selected, Rows} = execute_successfully(Pool, domain_event_ids_between, [Min, Max]),
196 80 [mongoose_rdbms:result_to_integer(Id) || {Id} <- Rows].
197
198 delete_events_older_than(Id) ->
199 3 transaction(fun(Pool) ->
200 3 execute_successfully(Pool, domain_events_delete_older_than, [Id])
201 end).
202
203 insert_dummy_event(EventId) ->
204 5 insert_full_event(EventId, <<>>).
205
206 insert_full_event(EventId, Domain) ->
207 13 case mongoose_rdbms:db_type() of
208 mssql ->
209 13 insert_full_event_mssql(EventId, Domain);
210 _ ->
211 
:-(
 Pool = get_db_pool(),
212 
:-(
 execute_successfully(Pool, domain_insert_full_event, [EventId, Domain])
213 end.
214
215 insert_full_event_mssql(EventId, Domain) ->
216 %% MSSQL does not allow to specify ids,
217 %% that are supposed to be autoincremented, easily
218 %% https://docs.microsoft.com/pl-pl/sql/t-sql/statements/set-identity-insert-transact-sql
219 13 transaction(fun(Pool) ->
220 %% This query could not be a prepared query
221 %% You will get an error:
222 %% "No SQL-driver information available."
223 %% when trying to execute
224 17 mongoose_rdbms:sql_query(Pool, <<"SET IDENTITY_INSERT domain_events ON">>),
225 17 try
226 17 execute_successfully(Pool, domain_insert_full_event, [EventId, Domain])
227 after
228 17 mongoose_rdbms:sql_query(Pool, <<"SET IDENTITY_INSERT domain_events OFF">>)
229 end
230 end).
231
232 %% ----------------------------------------------------------------------------
233 %% For testing
234
235 erase_database() ->
236 36 Pool = get_db_pool(),
237 36 execute_successfully(Pool, domain_erase_events, []),
238 36 execute_successfully(Pool, domain_erase_settings, []).
239
240 insert_domain_settings_without_event(Domain, HostType) ->
241 
:-(
 Pool = get_db_pool(),
242 
:-(
 execute_successfully(Pool, domain_insert_settings, [Domain, HostType]).
243
244 %% ----------------------------------------------------------------------------
245
246 %% Inserts a new event with an autoincremented EventId.
247 %% Rows would not appear in the EventId order, RDBMS likes to rearrange them.
248 %% Example of rearranging:
249 %% Events with ids [1, 2, 3] could appear as:
250 %% 1. [1]
251 %% 2. [1, 3] - at this step record with EventId=2 is not visible yet.
252 %% 3. [1, 2, 3] - and finally everything is fine.
253 insert_domain_event(Pool, Domain) ->
254 59 execute_successfully(Pool, domain_insert_event, [Domain]).
255
256 insert_domain_settings(Pool, Domain, HostType) ->
257 43 execute_successfully(Pool, domain_insert_settings, [Domain, HostType]).
258
259 delete_domain_settings(Pool, Domain) ->
260 7 execute_successfully(Pool, domain_delete_settings, [Domain]).
261
262 set_enabled(Domain, Enabled) when is_boolean(Enabled) ->
263 13 transaction(fun(Pool) ->
264 13 case select_domain(Domain) of
265 {error, Reason} ->
266 2 {error, Reason};
267 {ok, #{enabled := En, host_type := HostType}} ->
268 11 case mongoose_domain_core:is_host_type_allowed(HostType) of
269 false ->
270 2 {error, unknown_host_type};
271 true when Enabled =:= En ->
272 
:-(
 ok;
273 true ->
274 9 update_domain_enabled(Pool, Domain, Enabled),
275 9 insert_domain_event(Pool, Domain),
276 9 ok
277 end
278 end
279 end).
280
281 update_domain_enabled(Pool, Domain, Enabled) ->
282 9 ExtEnabled = bool_to_ext(Pool, Enabled),
283 9 execute_successfully(Pool, domain_update_settings_enabled, [ExtEnabled, Domain]).
284
285 %% MySQL needs booleans as integers
286 bool_to_ext(Pool, Bool) when is_boolean(Bool) ->
287 9 case mongoose_rdbms:db_engine(Pool) of
288 pgsql ->
289 
:-(
 Bool;
290 _ ->
291 9 bool_to_int(Bool)
292 end.
293
294 3 bool_to_int(true) -> 1;
295 6 bool_to_int(false) -> 0.
296
297 row_to_map({HostType, Enabled}) ->
298 34 #{host_type => HostType, enabled => mongoose_rdbms:to_bool(Enabled)}.
299
300 get_db_pool() ->
301 1966 proplists:get_value(db_pool, get_service_opts(), global).
302
303 get_service_opts() ->
304 1966 mongoose_service:get_service_opts(service_domain_db).
305
306 transaction(F) ->
307 87 transaction(F, 3, []).
308
309 %% MSSQL especially likes to kill a connection deadlocked by tablock connections.
310 %% But that's fine, we could just restart
311 %% (there is no logic, that would suffer by a restart of a transaction).
312 transaction(_F, 0, Errors) ->
313 2 {error, {db_error, Errors}};
314 transaction(F, Retries, Errors) when Retries > 0 ->
315 91 Pool = get_db_pool(),
316 91 Result = rdbms_queries:sql_transaction(Pool, fun() -> F(Pool) end),
317 91 case Result of
318 {aborted, _} -> %% Restart any rolled back transaction
319 8 timer:sleep(100), %% Small break before retry
320 6 transaction(F, Retries - 1, [Result|Errors]);
321 _ ->
322 83 simple_result(Result)
323 end.
324
325 83 simple_result({atomic, Result}) -> Result;
326 
:-(
 simple_result(Other) -> {error, {db_error, Other}}.
Line Hits Source