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