| 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, pass_details], |
| 89 |
|
<<"INSERT INTO domain_admins (domain, pass_details) VALUES (?, ?)">>), |
| 90 |
:-( |
prepare(domain_update_admin, domain_admins, [pass_details, domain], |
| 91 |
|
<<"UPDATE domain_admins" |
| 92 |
|
" SET pass_details = ? " |
| 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, pass_details" |
| 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 |
:-( |
Iterations = mongoose_scram:iterations(), |
| 180 |
:-( |
HashMap = mongoose_scram:password_to_scram_sha(Password, Iterations, sha512), |
| 181 |
:-( |
PassDetails = mongoose_scram:serialize(HashMap), |
| 182 |
:-( |
transaction(fun(Pool) -> |
| 183 |
:-( |
case select_domain_admin(Domain) of |
| 184 |
|
{ok, _} -> |
| 185 |
:-( |
update_domain_admin(Pool, Domain, PassDetails), |
| 186 |
:-( |
ok; |
| 187 |
|
{error, not_found} -> |
| 188 |
:-( |
insert_domain_admin(Pool, Domain, PassDetails), |
| 189 |
:-( |
ok |
| 190 |
|
end |
| 191 |
|
end). |
| 192 |
|
|
| 193 |
|
delete_domain_admin(Domain) -> |
| 194 |
:-( |
transaction(fun(Pool) -> |
| 195 |
:-( |
case select_domain_admin(Domain) of |
| 196 |
|
{ok, _} -> |
| 197 |
:-( |
{updated, 1} = delete_domain_admin(Pool, Domain), |
| 198 |
:-( |
ok; |
| 199 |
|
{error, not_found} -> |
| 200 |
:-( |
ok |
| 201 |
|
end |
| 202 |
|
end). |
| 203 |
|
|
| 204 |
|
insert_domain_admin(Pool, Domain, PassDetails) -> |
| 205 |
:-( |
execute_successfully(Pool, domain_insert_admin, [Domain, PassDetails]). |
| 206 |
|
|
| 207 |
|
update_domain_admin(Pool, Domain, PassDetails) -> |
| 208 |
:-( |
execute_successfully(Pool, domain_update_admin, [PassDetails, Domain]). |
| 209 |
|
|
| 210 |
|
delete_domain_admin(Pool, Domain) -> |
| 211 |
:-( |
execute_successfully(Pool, domain_delete_admin, [Domain]). |
| 212 |
|
|
| 213 |
|
%% Returns smallest id first |
| 214 |
|
select_from(FromId, Limit) -> |
| 215 |
:-( |
Pool = get_db_pool(), |
| 216 |
:-( |
Args = rdbms_queries:add_limit_arg(Limit, [FromId]), |
| 217 |
:-( |
{selected, Rows} = execute_successfully(Pool, domain_select_from, Args), |
| 218 |
:-( |
Rows. |
| 219 |
|
|
| 220 |
|
get_enabled_dynamic() -> |
| 221 |
:-( |
Pool = get_db_pool(), |
| 222 |
:-( |
prepare_test_queries(Pool), |
| 223 |
:-( |
{selected, Rows} = execute_successfully(Pool, domain_get_enabled_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 |
:-( |
Pool = get_db_pool(), |
| 230 |
:-( |
Args = [FromId, ToId], |
| 231 |
:-( |
{selected, Rows} = execute_successfully(Pool, domain_select_events_between, Args), |
| 232 |
:-( |
Rows. |
| 233 |
|
|
| 234 |
|
get_minmax_event_id() -> |
| 235 |
:-( |
Pool = get_db_pool(), |
| 236 |
:-( |
{selected, [{Min, Max}]} = execute_successfully(Pool, domain_events_minmax, []), |
| 237 |
:-( |
case Min of |
| 238 |
|
null -> |
| 239 |
:-( |
{null, null}; |
| 240 |
|
_ -> |
| 241 |
:-( |
{mongoose_rdbms:result_to_integer(Min), |
| 242 |
|
mongoose_rdbms:result_to_integer(Max)} |
| 243 |
|
end. |
| 244 |
|
|
| 245 |
|
count_events_between_ids(Min, Max) -> |
| 246 |
:-( |
Pool = get_db_pool(), |
| 247 |
:-( |
Selected = execute_successfully(Pool, domain_count_event_between, [Min, Max]), |
| 248 |
:-( |
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 |
:-( |
Pool = get_db_pool(), |
| 253 |
:-( |
{selected, Rows} = execute_successfully(Pool, domain_event_ids_between, [Min, Max]), |
| 254 |
:-( |
[mongoose_rdbms:result_to_integer(Id) || {Id} <- Rows]. |
| 255 |
|
|
| 256 |
|
delete_events_older_than(Id) -> |
| 257 |
:-( |
transaction(fun(Pool) -> |
| 258 |
:-( |
execute_successfully(Pool, domain_events_delete_older_than, [Id]) |
| 259 |
|
end). |
| 260 |
|
|
| 261 |
|
insert_dummy_event(EventId) -> |
| 262 |
:-( |
insert_full_event(EventId, <<>>). |
| 263 |
|
|
| 264 |
|
insert_full_event(EventId, Domain) -> |
| 265 |
:-( |
case mongoose_rdbms:db_type() of |
| 266 |
|
mssql -> |
| 267 |
:-( |
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 |
:-( |
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 |
:-( |
mongoose_rdbms:sql_query(Pool, <<"SET IDENTITY_INSERT domain_events ON">>), |
| 283 |
:-( |
try |
| 284 |
:-( |
execute_successfully(Pool, domain_insert_full_event, [EventId, Domain]) |
| 285 |
|
after |
| 286 |
:-( |
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 |
:-( |
execute_successfully(Pool, domain_erase_events, []), |
| 295 |
:-( |
execute_successfully(Pool, domain_erase_settings, []), |
| 296 |
:-( |
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 |
:-( |
execute_successfully(Pool, domain_insert_event, [Domain]). |
| 313 |
|
|
| 314 |
|
insert_domain_settings(Pool, Domain, HostType) -> |
| 315 |
:-( |
execute_successfully(Pool, domain_insert_settings, [Domain, HostType]). |
| 316 |
|
|
| 317 |
|
delete_domain_settings(Pool, Domain) -> |
| 318 |
:-( |
execute_successfully(Pool, domain_delete_settings, [Domain]). |
| 319 |
|
|
| 320 |
|
set_enabled(Domain, Enabled) when is_boolean(Enabled) -> |
| 321 |
:-( |
transaction(fun(Pool) -> |
| 322 |
:-( |
case select_domain(Domain) of |
| 323 |
|
{error, Reason} -> |
| 324 |
:-( |
{error, Reason}; |
| 325 |
|
{ok, #{enabled := En, host_type := HostType}} -> |
| 326 |
:-( |
case mongoose_domain_core:is_host_type_allowed(HostType) of |
| 327 |
|
false -> |
| 328 |
:-( |
{error, unknown_host_type}; |
| 329 |
|
true when Enabled =:= En -> |
| 330 |
:-( |
ok; |
| 331 |
|
true -> |
| 332 |
:-( |
update_domain_enabled(Pool, Domain, Enabled), |
| 333 |
:-( |
insert_domain_event(Pool, Domain), |
| 334 |
:-( |
ok |
| 335 |
|
end |
| 336 |
|
end |
| 337 |
|
end). |
| 338 |
|
|
| 339 |
|
update_domain_enabled(Pool, Domain, Enabled) -> |
| 340 |
:-( |
ExtEnabled = bool_to_ext(Pool, Enabled), |
| 341 |
:-( |
execute_successfully(Pool, domain_update_settings_enabled, [ExtEnabled, Domain]). |
| 342 |
|
|
| 343 |
|
%% MySQL needs booleans as integers |
| 344 |
|
bool_to_ext(Pool, Bool) when is_boolean(Bool) -> |
| 345 |
:-( |
case mongoose_rdbms:db_engine(Pool) of |
| 346 |
|
pgsql -> |
| 347 |
:-( |
Bool; |
| 348 |
|
_ -> |
| 349 |
:-( |
bool_to_int(Bool) |
| 350 |
|
end. |
| 351 |
|
|
| 352 |
:-( |
bool_to_int(true) -> 1; |
| 353 |
:-( |
bool_to_int(false) -> 0. |
| 354 |
|
|
| 355 |
|
row_to_map({HostType, Enabled}) -> |
| 356 |
:-( |
#{host_type => HostType, enabled => mongoose_rdbms:to_bool(Enabled)}. |
| 357 |
|
|
| 358 |
|
get_db_pool() -> |
| 359 |
:-( |
mongoose_config:get_opt([services, service_domain_db, db_pool]). |
| 360 |
|
|
| 361 |
|
transaction(F) -> |
| 362 |
:-( |
transaction(F, 3, []). |
| 363 |
|
|
| 364 |
|
%% MSSQL especially likes to kill a connection deadlocked by tablock connections. |
| 365 |
|
%% But that's fine, we could just restart |
| 366 |
|
%% (there is no logic, that would suffer by a restart of a transaction). |
| 367 |
|
transaction(_F, 0, Errors) -> |
| 368 |
:-( |
{error, {db_error, Errors}}; |
| 369 |
|
transaction(F, Retries, Errors) when Retries > 0 -> |
| 370 |
:-( |
Pool = get_db_pool(), |
| 371 |
:-( |
Result = rdbms_queries:sql_transaction(Pool, fun() -> F(Pool) end), |
| 372 |
:-( |
case Result of |
| 373 |
|
{aborted, _} -> %% Restart any rolled back transaction |
| 374 |
:-( |
timer:sleep(100), %% Small break before retry |
| 375 |
:-( |
transaction(F, Retries - 1, [Result|Errors]); |
| 376 |
|
_ -> |
| 377 |
:-( |
simple_result(Result) |
| 378 |
|
end. |
| 379 |
|
|
| 380 |
:-( |
simple_result({atomic, Result}) -> Result; |
| 381 |
:-( |
simple_result(Other) -> {error, {db_error, Other}}. |