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 |
235 |
{LimitSQL, LimitMSSQL} = rdbms_queries:get_db_specific_limits_binaries(), |
40 |
235 |
Pool = get_db_pool(), |
41 |
235 |
True = sql_true(Pool), |
42 |
|
%% Settings |
43 |
235 |
prepare(domain_insert_settings, domain_settings, [domain, host_type], |
44 |
|
<<"INSERT INTO domain_settings (domain, host_type) " |
45 |
|
"VALUES (?, ?)">>), |
46 |
235 |
prepare(domain_update_settings_enabled, domain_settings, |
47 |
|
[enabled, domain], |
48 |
|
<<"UPDATE domain_settings " |
49 |
|
"SET enabled = ? " |
50 |
|
"WHERE domain = ?">>), |
51 |
235 |
prepare(domain_delete_settings, domain_settings, [domain], |
52 |
|
<<"DELETE FROM domain_settings WHERE domain = ?">>), |
53 |
235 |
prepare(domain_select, domain_settings, [domain], |
54 |
|
<<"SELECT host_type, enabled " |
55 |
|
"FROM domain_settings WHERE domain = ?">>), |
56 |
235 |
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 |
235 |
prepare(domain_insert_event, domain_events, [domain], |
66 |
|
<<"INSERT INTO domain_events (domain) VALUES (?)">>), |
67 |
235 |
prepare(domain_insert_full_event, domain_events, [id, domain], |
68 |
|
<<"INSERT INTO domain_events (id, domain) VALUES (?, ?)">>), |
69 |
235 |
prepare(domain_events_minmax, domain_events, [], |
70 |
|
<<"SELECT MIN(id), MAX(id) FROM domain_events">>), |
71 |
235 |
prepare(domain_count_event_between, domain_events, [id, id], |
72 |
|
<<"SELECT COUNT(*) FROM domain_events WHERE id >= ? AND id <= ?">>), |
73 |
235 |
prepare(domain_event_ids_between, domain_events, [id, id], |
74 |
|
<<"SELECT id FROM domain_events WHERE id >= ? AND id <= ? ORDER BY id">>), |
75 |
235 |
prepare(domain_events_delete_older_than, domain_events, [id], |
76 |
|
<<"DELETE FROM domain_events WHERE id < ?">>), |
77 |
235 |
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 |
235 |
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 |
273 |
case mongoose_rdbms:db_engine(Pool) of |
106 |
:-( |
pgsql -> <<"true">>; |
107 |
273 |
_ -> <<"1">> |
108 |
|
end. |
109 |
|
|
110 |
|
%% ---------------------------------------------------------------------------- |
111 |
|
%% API |
112 |
|
insert_domain(Domain, HostType) -> |
113 |
43 |
transaction(fun(Pool) -> |
114 |
43 |
case select_domain(Domain) of |
115 |
|
{ok, #{host_type := HT}} when HT =:= HostType -> |
116 |
1 |
ok; %% ignore second call |
117 |
|
{error, not_found} -> |
118 |
40 |
insert_domain_settings(Pool, Domain, HostType), |
119 |
40 |
insert_domain_event(Pool, Domain), |
120 |
40 |
ok; |
121 |
|
{ok, _} -> |
122 |
2 |
{error, duplicate} |
123 |
|
end |
124 |
|
end). |
125 |
|
|
126 |
|
select_domain(Domain) -> |
127 |
76 |
Pool = get_db_pool(), |
128 |
76 |
case execute_successfully(Pool, domain_select, [Domain]) of |
129 |
|
{selected, []} -> |
130 |
44 |
{error, not_found}; |
131 |
|
{selected, [Row]} -> |
132 |
32 |
{ok, row_to_map(Row)} |
133 |
|
end. |
134 |
|
|
135 |
|
delete_domain(Domain, HostType) -> |
136 |
9 |
transaction(fun(Pool) -> |
137 |
9 |
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 |
:-( |
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 |
250 |
Pool = get_db_pool(), |
158 |
250 |
Args = rdbms_queries:add_limit_arg(Limit, [FromId]), |
159 |
250 |
{selected, Rows} = execute_successfully(Pool, domain_select_from, Args), |
160 |
250 |
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 |
171 |
Pool = get_db_pool(), |
172 |
171 |
Args = [FromId, ToId], |
173 |
171 |
{selected, Rows} = execute_successfully(Pool, domain_select_events_between, Args), |
174 |
168 |
Rows. |
175 |
|
|
176 |
|
get_minmax_event_id() -> |
177 |
1051 |
Pool = get_db_pool(), |
178 |
1051 |
{selected, [{Min, Max}]} = execute_successfully(Pool, domain_events_minmax, []), |
179 |
1050 |
case Min of |
180 |
|
null -> |
181 |
609 |
{null, null}; |
182 |
|
_ -> |
183 |
441 |
{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 |
54 |
Pool = get_db_pool(), |
195 |
54 |
{selected, Rows} = execute_successfully(Pool, domain_event_ids_between, [Min, Max]), |
196 |
54 |
[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 |
56 |
execute_successfully(Pool, domain_insert_event, [Domain]). |
255 |
|
|
256 |
|
insert_domain_settings(Pool, Domain, HostType) -> |
257 |
40 |
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 |
32 |
#{host_type => HostType, enabled => mongoose_rdbms:to_bool(Enabled)}. |
299 |
|
|
300 |
|
get_db_pool() -> |
301 |
2000 |
proplists:get_value(db_pool, get_service_opts(), global). |
302 |
|
|
303 |
|
get_service_opts() -> |
304 |
2000 |
mongoose_service:get_service_opts(service_domain_db). |
305 |
|
|
306 |
|
transaction(F) -> |
307 |
81 |
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 |
85 |
Pool = get_db_pool(), |
316 |
85 |
Result = rdbms_queries:sql_transaction(Pool, fun() -> F(Pool) end), |
317 |
85 |
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 |
77 |
simple_result(Result) |
323 |
|
end. |
324 |
|
|
325 |
77 |
simple_result({atomic, Result}) -> Result; |
326 |
:-( |
simple_result(Other) -> {error, {db_error, Other}}. |