1: %%==============================================================================
2: %% Copyright 2016 Erlang Solutions Ltd.
3: %%
4: %% Licensed under the Apache License, Version 2.0 (the "License");
5: %% you may not use this file except in compliance with the License.
6: %% You may obtain a copy of the License at
7: %%
8: %% http://www.apache.org/licenses/LICENSE-2.0
9: %%
10: %% Unless required by applicable law or agreed to in writing, software
11: %% distributed under the License is distributed on an "AS IS" BASIS,
12: %% WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13: %% See the License for the specific language governing permissions and
14: %% limitations under the License.
15: %%==============================================================================
16:
17: -module(rdbms_SUITE).
18: -compile([export_all, nowarn_export_all]).
19:
20: -include_lib("common_test/include/ct.hrl").
21: -include_lib("eunit/include/eunit.hrl").
22:
23: %% We need assert from it
24: -include("mam_helper.hrl").
25:
26: -import(domain_helper, [host_type/0]).
27:
28: -import(distributed_helper, [mim/0, rpc/4]).
29:
30: %%--------------------------------------------------------------------
31: %% Suite configuration
32: %%--------------------------------------------------------------------
33:
34: all() ->
35: [
36: {group, global_rdbms_queries},
37: {group, tagged_rdbms_queries}
38: ].
39:
40: groups() ->
41: [
42: {global_rdbms_queries, [], rdbms_queries_cases()},
43: {tagged_rdbms_queries, [], rdbms_queries_cases()}
44: ].
45:
46: rdbms_queries_cases() ->
47: [select_one_works_case,
48: select_ascii_string_works_case,
49: read_int32_case,
50: read_int64_case,
51: read_unicode_case,
52: read_unicode250_case,
53: read_ascii_char_case,
54: read_ascii_string_case,
55: read_binary_8k_case,
56: read_binary_65k_case,
57: read_binary_16m_case,
58: read_enum_char_case,
59: read_boolean_case,
60:
61: read_prep_int32_case,
62: read_prep_int64_case,
63: read_prep_unicode_case,
64: read_prep_unicode250_case,
65: read_prep_ascii_char_case,
66: read_prep_ascii_string_case,
67: read_prep_binary_8k_case,
68: read_prep_binary_65k_case,
69: read_prep_binary_16m_case,
70: read_prep_enum_char_case,
71: read_prep_boolean_case,
72: select_current_timestamp_case,
73:
74: select_like_case,
75: select_like_prep_case,
76:
77: insert_batch_with_null_case,
78: test_cast_insert,
79: test_request_insert,
80: test_wrapped_request,
81: test_failed_wrapper,
82: test_request_transaction,
83: test_restart_transaction_with_execute,
84: test_restart_transaction_with_execute_eventually_passes,
85: test_failed_transaction_with_execute_wrapped,
86: test_failed_wrapper_transaction,
87: test_incremental_upsert,
88: arguments_from_two_tables].
89:
90: suite() ->
91: escalus:suite().
92:
93: %%--------------------------------------------------------------------
94: %% Init & teardown
95: %%--------------------------------------------------------------------
96: init_per_suite(Config) ->
97: case not ct_helper:is_ct_running()
98: orelse mongoose_helper:is_rdbms_enabled(host_type()) of
99: false -> {skip, rdbms_or_ct_not_running};
100: true ->
101: %% Warning: inject_module does not really work well with --rerun-big-tests flag
102: mongoose_helper:inject_module(?MODULE),
103: escalus:init_per_suite(Config)
104: end.
105:
106: end_per_suite(Config) ->
107: escalus:end_per_suite(Config).
108:
109: init_per_group(tagged_rdbms_queries, Config) ->
110: ExtraConfig = stop_global_default_pool(),
111: start_local_host_type_pool(ExtraConfig),
112: ExtraConfig ++ Config;
113: init_per_group(global_rdbms_queries, Config) ->
114: [{tag, global} | Config].
115:
116: end_per_group(tagged_rdbms_queries, Config) ->
117: restart_global_default_pool(Config);
118: end_per_group(global_rdbms_queries, Config) ->
119: Config.
120:
121: init_per_testcase(test_incremental_upsert, Config) ->
122: erase_inbox(Config),
123: escalus:init_per_testcase(test_incremental_upsert, Config);
124: init_per_testcase(CaseName, Config) ->
125: escalus:init_per_testcase(CaseName, Config).
126:
127: end_per_testcase(CaseName, Config)
128: when CaseName =:= test_restart_transaction_with_execute;
129: CaseName =:= test_restart_transaction_with_execute_eventually_passes;
130: CaseName =:= test_failed_transaction_with_execute_wrapped;
131: CaseName =:= test_failed_wrapper;
132: CaseName =:= test_failed_wrapper_transaction ->
133: rpc(mim(), meck, unload, []),
134: escalus:end_per_testcase(CaseName, Config);
135: end_per_testcase(test_incremental_upsert, Config) ->
136: erase_inbox(Config),
137: escalus:end_per_testcase(test_incremental_upsert, Config);
138: end_per_testcase(CaseName, Config) ->
139: escalus:end_per_testcase(CaseName, Config).
140:
141: %%--------------------------------------------------------------------
142: %% Data for cases
143: %%--------------------------------------------------------------------
144:
145: int32_values() ->
146: [1, -1, 0, 42, 2147483647, null].
147:
148: int64_values() ->
149: [9223372036854775807,
150: null].
151:
152: ascii_string_values() ->
153: [<<>>, <<"1">>, <<"test">>,
154: <<"\\">>, <<"\\\\">>,
155: <<"'">>, <<"''">>, <<"'''">>,
156: <<"\"">>, <<"\"\"">>,
157: <<"\r\n">>, <<"\r">>, <<"\n">>,
158: binary:copy(<<"a">>, 250), null].
159:
160: unicode_values() ->
161: ascii_string_values() ++
162: [<<"юникод"/utf8>>, <<"😁"/utf8>>]
163: ++
164: %% Would fail with binary_data_8k and mssql.
165: %% For some reason mssql returns string "7878...." of length 4000.
166: %% What is 78? 16#78 = 120 = $x.
167: %% i.e. half of 8000 bytes for data.
168: %% Probably 2 bytes encoding is used for this.
169: % [binary:copy(<<$x>>, 4001),
170: %% Helps to debug if we don't consume all data from a buffer.
171: %% Than there would be a gap of missing numbers in the middle.
172: %% 1000 of 1-es, 1000 of 2-s, ..., 1000 of 10-s.
173: %%
174: %% In one version of eodbc, it returns 5,5,5,5... instead of 1,1,1,1...
175: %%
176: %% Also,
177: %% eodbc:sql_query(Conn, "SELECT convert(varbinary(max), binary_data_8k) FROM test_types") = gives correct result.
178: %% but
179: %% eodbc:sql_query(Conn, "SELECT binary_data_8k FROM test_types") = gives not correct result.
180: %%
181: %% eodbc:sql_query(Conn, "SELECT convert(varbinary(1000), binary_data_8k) FROM test_types") = gives correct result.
182: %% gives 010101.... as expected
183: [iolist_to_binary([lists:duplicate(1000, X) || X <- lists:seq(1, 10)]),
184: binary:copy(<<$a>>, 10000),
185: %% There is a bug with 8001 chars limit in upstream odbc
186: %% We use a fork arcusfelis/eodbc, that has the bug fixed
187: %% https://bugs.erlang.org/browse/ERL-421
188: binary:copy(<<10>>, 10000), null].
189:
190: binary_values() ->
191: [<<0>>, <<"255">>,
192: <<240,159>>, %% Incomplete unicode
193: <<240,159,0>>, %% Invalid unicode
194: iolist_to_binary(lists:seq(0, 255)),
195: %% one kilobyte
196: binary:copy(<<1>>, 1024),
197: %% two kilobytes
198: binary:copy(<<2>>, 2048),
199: binary:copy(<<5>>, 1024*5),
200: %% There is a bug with 8001 chars limit in upstream odbc
201: %% We use a fork arcusfelis/eodbc, that has the bug fixed
202: %% https://bugs.erlang.org/browse/ERL-421
203: binary:copy(<<8>>, 8002),
204: binary:copy(<<0>>, 100000),
205: null
206: ] ++
207: case is_odbc() orelse is_pgsql() of
208: true ->
209: [];
210: false ->
211: %% FIXME long data causes timeout with mssql
212: %%
213: %% FIXME %% epgsql_sock:handle_info/2 is not optimized
214: %% The query takes 30 seconds on Postgres
215: %% mongoose_rdbms:sql_query(<<"localhost">>, <<"SELECT binary_data_16m FROM test_types">>).
216: [binary:copy(<<16>>, 16777215)]
217: end.
218:
219: binary_8k_values() ->
220: truncate_binaries(8000, unicode_values() ++ binary_values()).
221:
222: binary_65k_values() ->
223: truncate_binaries(65535, unicode_values() ++ binary_values()).
224:
225: binary_16m_values() ->
226: truncate_binaries(16777215, unicode_values() ++ binary_values()).
227:
228: ascii_char_values() ->
229: [<<"a">>, <<"b">>].
230:
231: enum_char_values() ->
232: [<<"A">>, <<"B">>, <<"C">>].
233:
234: simple_like_texts() ->
235: [#{text => <<"hello user!">>,
236: not_matching => [<<"hi">>, <<"help">>],
237: matching => [<<"hello">>, <<"user">>, <<"hell">>]},
238: #{text => <<60,79,67,32,59,48,63,58,48>>,
239: not_matching => [<<62,66,64,48,65,66,53,66>>],
240: matching => [<<60,79,67>>]}].
241:
242: like_texts() ->
243: simple_like_texts() ++
244: [#{text => <<"abc%">>,
245: not_matching => [<<"ab%">>, <<"%bc%">>],
246: matching => [<<"abc%">>, <<"abc">>]},
247: #{text => <<"żółć_"/utf8>>,
248: not_matching => [<<"_ółć_"/utf8>>],
249: matching => [<<"żół"/utf8>>, <<"ółć_"/utf8>>]}].
250:
251: %%--------------------------------------------------------------------
252: %% Test cases
253: %%--------------------------------------------------------------------
254:
255: %% Checks, that we at least can connect to db
256: select_one_works_case(Config) ->
257: ?assert_equal({selected, [{<<"1">>}]},
258: %% Postgres direct driver returns <<"1">>
259: %% Everyone else return 1
260: selected_to_binary(sql_query(Config, <<"SELECT 1">>))).
261:
262: %% Should be binary.
263: %% Unicode is another case.
264: select_ascii_string_works_case(Config) ->
265: ?assert_equal({selected, [{<<"ascii">>}]},
266: sql_query(Config, <<"SELECT 'ascii'">>)).
267:
268: %% Writes and reads values of this datatype back
269: read_int32_case(Config) ->
270: Values = int32_values(),
271: [check_int32(Config, Value) || Value <- Values].
272:
273: read_int64_case(Config) ->
274: Values = int32_values() ++ int64_values(),
275: [check_int64(Config, Value) || Value <- Values].
276:
277: read_unicode_case(Config) ->
278: [check_unicode(Config, Value) || Value <- unicode_values()].
279:
280: read_unicode250_case(Config) ->
281: [check_unicode250(Config, Value) || Value <- unicode_values(), byte_size(Value) < 250].
282:
283: read_ascii_char_case(Config) ->
284: [check_ascii_char(Config, Value) || Value <- ascii_char_values()].
285:
286: read_ascii_string_case(Config) ->
287: [check_ascii_string(Config, Value)
288: || Value <- ascii_char_values() ++ ascii_string_values()].
289:
290: read_binary_8k_case(Config) ->
291: [check_binary_8k(Config, Value) || Value <- binary_8k_values()].
292:
293: read_binary_65k_case(Config) ->
294: [check_binary_65k(Config, Value) || Value <- binary_65k_values()].
295:
296: read_binary_16m_case(Config) ->
297: [check_binary_16m(Config, Value) || Value <- binary_16m_values()].
298:
299: read_enum_char_case(Config) ->
300: [check_enum_char(Config, Value) || Value <- enum_char_values()].
301:
302: read_boolean_case(Config) ->
303: [check_boolean(Config, Value) || Value <- [true, false]].
304:
305: %%--------------------------------------------------------------------
306: %% Prepared cases
307: %%--------------------------------------------------------------------
308:
309: read_prep_int32_case(Config) ->
310: Values = int32_values(),
311: [check_prep_int32(Config, Value) || Value <- Values].
312:
313: read_prep_int64_case(Config) ->
314: Values = int32_values() ++ int64_values(),
315: [check_prep_int64(Config, Value) || Value <- Values].
316:
317: read_prep_unicode_case(Config) ->
318: [check_prep_unicode(Config, Value) || Value <- unicode_values()].
319:
320: read_prep_unicode250_case(Config) ->
321: [check_prep_unicode250(Config, Value) || Value <- unicode_values(), byte_size(Value) < 250].
322:
323: read_prep_ascii_char_case(Config) ->
324: [check_prep_ascii_char(Config, Value) || Value <- ascii_char_values()].
325:
326: read_prep_ascii_string_case(Config) ->
327: [check_prep_ascii_string(Config, Value)
328: || Value <- ascii_char_values() ++ ascii_string_values()].
329:
330: read_prep_binary_8k_case(Config) ->
331: [check_prep_binary_8k(Config, Value) || Value <- binary_8k_values()].
332:
333: read_prep_binary_65k_case(Config) ->
334: [check_prep_binary_65k(Config, Value) || Value <- binary_65k_values()].
335:
336: read_prep_binary_16m_case(Config) ->
337: [check_prep_binary_16m(Config, Value) || Value <- binary_16m_values()].
338:
339: read_prep_enum_char_case(Config) ->
340: [check_prep_enum_char(Config, Value) || Value <- enum_char_values()].
341:
342: read_prep_boolean_case(Config) ->
343: [check_prep_boolean(Config, Value) || Value <- [0, 1]].
344:
345: select_current_timestamp_case(Config) ->
346: ok = rpc(mim(), mongoose_rdbms_timestamp, prepare, []),
347: Res = case ?config(tag, Config) of
348: global ->
349: rpc(mim(), mongoose_rdbms_timestamp, select, []);
350: Tag ->
351: rpc(mim(), mongoose_rdbms_timestamp, select, [host_type(), Tag])
352: end,
353: assert_is_integer(Res).
354:
355: assert_is_integer(X) when is_integer(X) ->
356: X.
357:
358: truncate_binaries(Len, List) ->
359: [truncate_binary(Len, Bin) || Bin <- List].
360:
361: truncate_binary(Len, Bin) when byte_size(Bin) > Len ->
362: binary:part(Bin, {0,Len});
363: truncate_binary(_Len, Bin) ->
364: Bin.
365:
366: safe_binary(Len, Bin) when byte_size(Bin) > Len ->
367: #{what => truncated_safe_binary,
368: truncated_length => Len,
369: total_length => byte_size(Bin),
370: truncated_binary => binary:part(Bin, {0,Len})};
371: safe_binary(_Len, Bin) ->
372: Bin.
373:
374: arguments_from_two_tables(Config) ->
375: erase_users(Config),
376: sql_prepare(Config, select_multi_args, users, [password, 'last.seconds'],
377: <<"SELECT users.username from users "
378: " LEFT JOIN last ON (last.username = users.username) "
379: " WHERE users.password = ? AND last.seconds > ?">>),
380: UserInsert = "INSERT INTO users (username, server, password) VALUES ",
381: sql_query(Config, UserInsert ++ "('alice', 'domain', 'secret')"),
382: sql_query(Config, UserInsert ++ "('bob', 'domain', 'billy')"),
383: LastInsert = "INSERT INTO last (username, server, seconds, state) VALUES ",
384: sql_query(Config, LastInsert ++ "('alice', 'domain', 1615368268, 'ok')"),
385: sql_query(Config, LastInsert ++ "('bob', 'domain', 1610000000, 'cool')"),
386: SelectResult = sql_execute(Config, select_multi_args, [<<"secret">>, 1611000000]),
387: ?assert_equal({selected, [{<<"alice">>}]}, SelectResult),
388: erase_users(Config),
389: ok.
390:
391: %% Ensures that ODBC uses a correct type when encoding NULL
392: %% and it does not interfere with non-null values
393: insert_batch_with_null_case(Config) ->
394: erase_table(Config),
395: sql_prepare(Config, insert_batch, test_types, [unicode, unicode],
396: <<"INSERT INTO test_types(unicode) VALUES (?), (?)">>),
397: sql_execute(Config, insert_batch, [null, <<"check1">>]),
398: sql_execute(Config, insert_batch, [<<"check2">>, null]),
399: SelectResult = sql_query(Config, "SELECT unicode FROM test_types"),
400: %% Sorting with null values is DB specific, so sort it with Erlang
401: ?assert_equal({selected, [{null}, {null}, {<<"check1">>}, {<<"check2">>}]},
402: selected_to_sorted(SelectResult)).
403:
404: test_cast_insert(Config) ->
405: erase_table(Config),
406: sql_prepare(Config, insert_one, test_types, [unicode],
407: <<"INSERT INTO test_types(unicode) VALUES (?)">>),
408: sql_execute_cast(Config, insert_one, [<<"check1">>]),
409: sql_query_cast(Config, <<"INSERT INTO test_types(unicode) VALUES ('check2')">>),
410: mongoose_helper:wait_until(
411: fun() ->
412: SelectResult = sql_query(Config, "SELECT unicode FROM test_types"),
413: ?assertEqual({selected, [{<<"check1">>}, {<<"check2">>}]},
414: selected_to_sorted(SelectResult))
415: end, ok, #{name => cast_queries}).
416:
417: test_request_insert(Config) ->
418: erase_table(Config),
419: sql_prepare(Config, insert_one, test_types, [unicode],
420: <<"INSERT INTO test_types(unicode) VALUES (?)">>),
421: sql_execute_request(Config, insert_one, [<<"check1">>]),
422: sql_query_request(Config, <<"INSERT INTO test_types(unicode) VALUES ('check2')">>),
423: mongoose_helper:wait_until(
424: fun() ->
425: SelectResult = sql_query(Config, "SELECT unicode FROM test_types"),
426: ?assertEqual({selected, [{<<"check1">>}, {<<"check2">>}]},
427: selected_to_sorted(SelectResult))
428: end, ok, #{name => request_queries}).
429:
430: test_wrapped_request(Config) ->
431: % given
432: erase_table(Config),
433: sql_prepare(Config, insert_one, test_types, [unicode],
434: <<"INSERT INTO test_types(unicode) VALUES (?)">>),
435: rpc(mim(), mongoose_metrics, ensure_metric, [global, [test_metric], histogram]),
436: WrapperFun = fun(SqlExecute) ->
437: {Time, Result} = timer:tc(SqlExecute),
438: mongoose_metrics:update(global, [test_metric], Time),
439: Result
440: end,
441:
442: % when
443: sql_execute_wrapped_request_and_wait_response(Config, insert_one, [<<"check1">>], WrapperFun),
444:
445: % then
446: mongoose_helper:wait_until(
447: fun() ->
448: SelectResult = sql_query(Config, "SELECT unicode FROM test_types"),
449: ?assertEqual({selected, [{<<"check1">>}]}, selected_to_sorted(SelectResult))
450: end, ok, #{name => request_queries}),
451:
452: {ok, Metric} = rpc(mim(), mongoose_metrics, get_metric_value, [global, [test_metric]]),
453: MetricValue = proplists:get_value(mean, Metric),
454: ?assert(MetricValue > 0).
455:
456: test_failed_wrapper(Config) ->
457: % given
458: erase_table(Config),
459: sql_prepare(Config, insert_one, test_types, [unicode],
460: <<"INSERT INTO test_types(unicode) VALUES (?)">>),
461: ok = rpc(mim(), meck, new, [supervisor, [passthrough, no_link, unstick]]),
462: WrapperFun = fun(_SqlExecute) ->
463: error(wrapper_crashed)
464: end,
465:
466: % when
467: Result = sql_execute_wrapped_request_and_wait_response(Config, insert_one, [<<"check1">>], WrapperFun),
468:
469: % then
470: ?assertEqual({reply,{error,wrapper_crashed}}, Result),
471: ?assertEqual([], rpc(mim(), meck, history, [supervisor])).
472:
473: test_request_transaction(Config) ->
474: erase_table(Config),
475: Queries = [<<"INSERT INTO test_types(unicode) VALUES ('check1')">>,
476: <<"INSERT INTO test_types(unicode) VALUES ('check2')">>],
477: sql_transaction_request(Config, Queries),
478: mongoose_helper:wait_until(
479: fun() ->
480: SelectResult = sql_query(Config, "SELECT unicode FROM test_types"),
481: ?assertEqual({selected, [{<<"check1">>}, {<<"check2">>}]},
482: selected_to_sorted(SelectResult))
483: end, ok, #{name => request_queries}).
484:
485: test_restart_transaction_with_execute(Config) ->
486: erase_table(Config),
487: prepare_insert_int8(Config),
488: ok = rpc(mim(), meck, new, [mongoose_rdbms_backend, [passthrough, no_link]]),
489: ok = rpc(mim(), meck, expect, [mongoose_rdbms_backend, execute, 4,
490: {error, simulated_db_error}]),
491: %% Check that mocking works
492: {error, simulated_db_error} = sql_execute(Config, insert_int8, [1]),
493: %% Executed by the MIM node
494: HostType = host_type(),
495: Pid = self(),
496: F = fun() -> Pid ! called, mongoose_rdbms:execute(HostType, insert_int8, [2]) end,
497: {aborted, #{reason := simulated_db_error}} = sql_transaction(Config, F),
498: called_times(11), %% 1 first run + 10 restarts
499: ok.
500:
501: test_restart_transaction_with_execute_eventually_passes(Config) ->
502: erase_table(Config),
503: prepare_insert_int8(Config),
504: ok = rpc(mim(), meck, new, [mongoose_rdbms_backend, [passthrough, no_link]]),
505: ok = rpc(mim(), meck, expect, [mongoose_rdbms_backend, execute, 4,
506: {error, simulated_db_error}]),
507: %% Check that mocking works
508: {error, simulated_db_error} = sql_execute(Config, insert_int8, [1]),
509: %% Executed by the MIM node
510: HostType = host_type(),
511: Pid = self(),
512: F = fun() -> Pid ! called, fail_times(3, Pid, HostType) end,
513: {atomic, ok} = sql_transaction(Config, F),
514: called_times(3),
515: ok.
516:
517: test_failed_transaction_with_execute_wrapped(Config) ->
518: % given
519: HostType = host_type(),
520: Pid = self(),
521: erase_table(Config),
522: prepare_insert_int8(Config),
523: ok = rpc(mim(), meck, new, [mongoose_rdbms_backend, [passthrough, no_link]]),
524: ok = rpc(mim(), meck, expect, [mongoose_rdbms_backend, execute, 4,
525: {error, simulated_db_error}]),
526: WrapperFun = fun(SqlExecute) ->
527: Pid ! msg_before,
528: Result = SqlExecute(),
529: Pid ! msg_after,
530: Result
531: end,
532:
533: % when
534: F = fun() -> mongoose_rdbms:execute_wrapped_request(HostType, insert_int8, [2], WrapperFun) end,
535: {aborted, #{reason := simulated_db_error}} = sql_transaction(Config, F),
536:
537: % then
538: check_not_received(msg_after).
539:
540: test_failed_wrapper_transaction(Config) ->
541: % given
542: erase_table(Config),
543: prepare_insert_int8(Config),
544: ok = rpc(mim(), meck, new, [supervisor, [passthrough, no_link, unstick]]),
545: WrapperFun = fun(_SqlExecute) ->
546: error(wrapper_crashed)
547: end,
548:
549: % when
550: ScopeAndTag = scope_and_tag(Config),
551: F = fun() -> sql_execute_wrapped_request(ScopeAndTag, insert_one, [<<"check1">>], WrapperFun) end,
552: sql_transaction(Config, F),
553:
554: % then
555: ?assertEqual([], rpc(mim(), meck, history, [supervisor])).
556:
557: prepare_insert_int8(Config) ->
558: Q = <<"INSERT INTO test_types(", (escape_column(<<"int8">>))/binary, ") VALUES (?)">>,
559: sql_prepare(Config, insert_int8, test_types, [int8], Q).
560:
561: fail_times(N, Pid, HostType) ->
562: case update_counter(Pid) + 1 of
563: N ->
564: ok;
565: _ ->
566: mongoose_rdbms:execute(HostType, insert_int8, [2])
567: end.
568:
569: %% Returns old value
570: update_counter(Pid) ->
571: Key = {test_counter, Pid},
572: N = case get(Key) of undefined -> 0; X -> X end,
573: put(Key, N + 1),
574: N.
575:
576: called_times(0) ->
577: %% Check that there are no more calls
578: receive called -> error(unexpected) after 0 -> ok end;
579: called_times(N) ->
580: receive called -> ok after 5000 -> error({called_times_timeout, N}) end,
581: called_times(N - 1).
582:
583: test_incremental_upsert(Config) ->
584: case is_odbc() of
585: true ->
586: ok;
587: false ->
588: do_test_incremental_upsert(Config)
589: end.
590:
591: do_test_incremental_upsert(Config) ->
592: KeyFields = [<<"luser">>, <<"lserver">>, <<"remote_bare_jid">>],
593: InsertFields = KeyFields ++ [<<"msg_id">>, <<"content">>, <<"unread_count">>, <<"timestamp">>],
594:
595: Key = [<<"alice">>, <<"localhost">>, <<"bob@localhost">>],
596: Insert = [<<"alice">>, <<"localhost">>, <<"bob@localhost">>, <<"msg_id">>, <<"content">>, 1],
597: sql_prepare_upsert(Config, upsert_incr, inbox,
598: InsertFields, [<<"timestamp">>], KeyFields, <<"timestamp">>),
599: sql_execute_upsert(Config, upsert_incr, Insert ++ [42], [42], Key),
600: sql_execute_upsert(Config, upsert_incr, Insert ++ [43], [43], Key),
601: sql_execute_upsert(Config, upsert_incr, Insert ++ [0], [0], Key),
602: SelectResult = sql_query(Config, <<"SELECT timestamp FROM inbox">>),
603: ?assertEqual({selected, [{<<"43">>}]}, selected_to_binary(SelectResult)).
604:
605: %%--------------------------------------------------------------------
606: %% Text searching
607: %%--------------------------------------------------------------------
608:
609: select_like_case(Config) ->
610: %% Non-prepared queries don't support proper LIKE escaping
611: [check_like(Config, TextMap) || TextMap <- simple_like_texts()].
612:
613: select_like_prep_case(Config) ->
614: [check_like_prep(Config, TextMap) || TextMap <- like_texts()].
615:
616: %%--------------------------------------------------------------------
617: %% Helpers
618: %%--------------------------------------------------------------------
619: tag() ->
620: extra_tag.
621:
622: scope_and_tag(Config) ->
623: case ?config(tag, Config) of
624: global -> [host_type()];
625: Tag -> [host_type(), Tag]
626: end.
627:
628: sql_query(Config, Query) ->
629: ScopeAndTag = scope_and_tag(Config),
630: slow_rpc(mongoose_rdbms, sql_query, ScopeAndTag ++ [Query]).
631:
632: sql_prepare(_Config, Name, Table, Fields, Query) ->
633: escalus_ejabberd:rpc(mongoose_rdbms, prepare, [Name, Table, Fields, Query]).
634:
635: sql_prepare_upsert(_Config, Name, Table, Insert, Update, Unique, Incr) ->
636: escalus_ejabberd:rpc(rdbms_queries, prepare_upsert, [host_type(), Name, Table, Insert, Update, Unique, Incr]).
637:
638: sql_execute(Config, Name, Parameters) ->
639: ScopeAndTag = scope_and_tag(Config),
640: slow_rpc(mongoose_rdbms, execute, ScopeAndTag ++ [Name, Parameters]).
641:
642: sql_execute_cast(Config, Name, Parameters) ->
643: ScopeAndTag = scope_and_tag(Config),
644: slow_rpc(mongoose_rdbms, execute_cast, ScopeAndTag ++ [Name, Parameters]).
645:
646: sql_query_cast(Config, Query) ->
647: ScopeAndTag = scope_and_tag(Config),
648: slow_rpc(mongoose_rdbms, sql_query_cast, ScopeAndTag ++ [Query]).
649:
650: sql_execute_request(Config, Name, Parameters) ->
651: ScopeAndTag = scope_and_tag(Config),
652: slow_rpc(mongoose_rdbms, execute_request, ScopeAndTag ++ [Name, Parameters]).
653:
654: sql_execute_wrapped_request(ScopeAndTag, Name, Parameters, WrapperFun) ->
655: slow_rpc(mongoose_rdbms, execute_wrapped_request, ScopeAndTag ++ [Name, Parameters, WrapperFun]).
656:
657: sql_execute_wrapped_request_and_wait_response(Config, Name, Parameters, WrapperFun) ->
658: ScopeAndTag = scope_and_tag(Config),
659: slow_rpc(?MODULE, execute_wrapped_request_and_wait_response, ScopeAndTag ++ [Name, Parameters, WrapperFun]).
660:
661: execute_wrapped_request_and_wait_response(HostType, Tag, Name, Parameters, WrapperFun) ->
662: RequestId = mongoose_rdbms:execute_wrapped_request(HostType, Tag, Name, Parameters, WrapperFun),
663: gen_server:wait_response(RequestId, 100).
664:
665: execute_wrapped_request_and_wait_response(HostType, Name, Parameters, WrapperFun) ->
666: RequestId = mongoose_rdbms:execute_wrapped_request(HostType, Name, Parameters, WrapperFun),
667: gen_server:wait_response(RequestId, 100).
668:
669: sql_execute_upsert(Config, Name, Insert, Update, Unique) ->
670: ScopeAndTag = scope_and_tag(Config),
671: slow_rpc(rdbms_queries, execute_upsert, ScopeAndTag ++ [Name, Insert, Update, Unique]).
672:
673: sql_query_request(Config, Query) ->
674: ScopeAndTag = scope_and_tag(Config),
675: slow_rpc(mongoose_rdbms, sql_query_request, ScopeAndTag ++ [Query]).
676:
677: sql_transaction_request(Config, Query) ->
678: ScopeAndTag = scope_and_tag(Config),
679: slow_rpc(mongoose_rdbms, sql_transaction_request, ScopeAndTag ++ [Query]).
680:
681: sql_transaction(Config, F) ->
682: ScopeAndTag = scope_and_tag(Config),
683: slow_rpc(mongoose_rdbms, sql_transaction, ScopeAndTag ++ [F]).
684:
685: escape_null(_Config) ->
686: escalus_ejabberd:rpc(mongoose_rdbms, escape_null, []).
687:
688: escape_string(_Config, Value) ->
689: escalus_ejabberd:rpc(mongoose_rdbms, escape_string, [Value]).
690:
691: escape_binary(_Config, Value) ->
692: slow_rpc(mongoose_rdbms, escape_binary, [host_type(), Value]).
693:
694: escape_boolean(_Config, Value) ->
695: escalus_ejabberd:rpc(mongoose_rdbms, escape_boolean, [Value]).
696:
697: escape_like(_Config, Value) ->
698: escalus_ejabberd:rpc(mongoose_rdbms, escape_like, [Value]).
699:
700: escape_prepared_like(_Config, Value) ->
701: escalus_ejabberd:rpc(mongoose_rdbms, escape_prepared_like, [Value]).
702:
703: unescape_binary(_Config, Value) ->
704: escalus_ejabberd:rpc(mongoose_rdbms, unescape_binary, [host_type(), Value]).
705:
706: use_escaped(_Config, Value) ->
707: escalus_ejabberd:rpc(mongoose_rdbms, use_escaped, [Value]).
708:
709: use_escaped_like(_Config, Value) ->
710: escalus_ejabberd:rpc(mongoose_rdbms, use_escaped_like, [Value]).
711:
712: escape_string_or_null(Config, null) ->
713: escape_null(Config);
714: escape_string_or_null(Config, TextValue) ->
715: escape_string(Config, TextValue).
716:
717: escape_binary_or_null(Config, null) ->
718: escape_null(Config);
719: escape_binary_or_null(Config, Value) ->
720: escape_binary(Config, Value).
721:
722: decode_boolean(_Config, Value) ->
723: escalus_ejabberd:rpc(mongoose_rdbms, to_bool, [Value]).
724:
725: erase_table(Config) ->
726: {updated, _} = sql_query(Config, <<"DELETE FROM test_types">>).
727:
728: erase_users(Config) ->
729: {updated, _} = sql_query(Config, <<"DELETE FROM users">>),
730: {updated, _} = sql_query(Config, <<"DELETE FROM last">>).
731:
732: erase_inbox(Config) ->
733: {updated, _} = sql_query(Config, <<"DELETE FROM inbox">>).
734:
735: check_int32(Config, Value) ->
736: check_generic_integer(Config, Value, <<"int32">>).
737:
738: check_int64(Config, Value) ->
739: check_generic_integer(Config, Value, <<"int64">>).
740:
741: check_generic_integer(Config, Value, Column) ->
742: EraseResult = erase_table(Config),
743: InsertQuery = <<"INSERT INTO test_types (", Column/binary, ") "
744: "VALUES (", (integer_or_null_to_binary(Value))/binary, ")">>,
745: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>,
746: InsertResult = sql_query(Config, InsertQuery),
747: SelectResult = sql_query(Config, SelectQuery),
748: %% Compare as binaries
749: ?assert_equal_extra({selected, [{integer_to_binary_or_null(Value)}]},
750: selected_to_binary(SelectResult),
751: #{column => Column,
752: erase_result => EraseResult,
753: test_value => Value,
754: insert_query => InsertQuery,
755: select_query => SelectQuery,
756: select_result => SelectResult,
757: insert_result => InsertResult}).
758:
759: integer_or_null_to_binary(null) -> <<"NULL">>;
760: integer_or_null_to_binary(X) -> integer_to_binary(X).
761:
762: integer_to_binary_or_null(null) -> null;
763: integer_to_binary_or_null(X) -> integer_to_binary(X).
764:
765: %% Helper function to transform values to an uniform format.
766: %% Single tuple, single element case.
767: %% In ODBC int32 is integer, but int64 is binary.
768: selected_to_binary({selected, [{Value}]}) when is_integer(Value) ->
769: {selected, [{integer_to_binary(Value)}]};
770: selected_to_binary(Other) ->
771: Other.
772:
773: selected_to_sorted({selected, Rows}) ->
774: {selected, lists:sort(Rows)};
775: selected_to_sorted(Other) ->
776: Other.
777:
778: value_to_binary(Value) when is_integer(Value) ->
779: integer_to_binary(Value);
780: value_to_binary(Value) ->
781: Value.
782:
783: check_unicode250(Config, Value) ->
784: check_unicode_generic(Config, Value, <<"unicode250">>).
785:
786: check_unicode(Config, Value) ->
787: check_unicode_generic(Config, Value, <<"unicode">>).
788:
789: check_unicode_generic(Config, Value, Column) ->
790: SValue = escape_string_or_null(Config, Value),
791: EraseResult = erase_table(Config),
792: InsertQuery = ["INSERT INTO test_types (", Column, ") "
793: "VALUES (", use_escaped(Config, SValue), ")"],
794: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>,
795: InsertResult = sql_query(Config, InsertQuery),
796: SelectResult = sql_query(Config, SelectQuery),
797: %% Compare as binaries
798: ?assert_equal_extra({selected, [{Value}]},
799: SelectResult,
800: #{column => Column,
801: erase_result => EraseResult,
802: expected_length => byte_size_or_null(Value),
803: selected_length => maybe_selected_length(Config, SelectResult),
804: compare_selected => compare_selected(Config, SelectResult, Value),
805: test_value => Value,
806: insert_query => InsertQuery,
807: insert_query_binary => iolist_to_binary(InsertQuery),
808: select_query => SelectQuery,
809: select_result => SelectResult,
810: insert_result => InsertResult}).
811:
812: check_ascii_char(Config, Value) ->
813: SValue = escape_string_or_null(Config, Value),
814: EraseResult = erase_table(Config),
815: InsertQuery = ["INSERT INTO test_types (ascii_char) "
816: "VALUES (", use_escaped(Config, SValue), ")"],
817: SelectQuery = <<"SELECT ascii_char FROM test_types">>,
818: InsertResult = sql_query(Config, InsertQuery),
819: SelectResult = sql_query(Config, SelectQuery),
820: %% Compare as binaries
821: ?assert_equal_extra({selected, [{Value}]},
822: SelectResult,
823: #{erase_result => EraseResult,
824: test_value => Value,
825: insert_query => InsertQuery,
826: insert_query_binary => iolist_to_binary(InsertQuery),
827: select_query => SelectQuery,
828: select_result => SelectResult,
829: insert_result => InsertResult}).
830:
831: check_ascii_string(Config, Value) ->
832: SValue = escape_string_or_null(Config, Value),
833: EraseResult = erase_table(Config),
834: InsertQuery = ["INSERT INTO test_types (ascii_string) "
835: "VALUES (", use_escaped(Config, SValue), ")"],
836: SelectQuery = <<"SELECT ascii_string FROM test_types">>,
837: InsertResult = sql_query(Config, InsertQuery),
838: SelectResult = sql_query(Config, SelectQuery),
839: %% Compare as binaries
840: ?assert_equal_extra({selected, [{Value}]},
841: SelectResult,
842: #{erase_result => EraseResult,
843: test_value => Value,
844: insert_query => InsertQuery,
845: insert_query_binary => iolist_to_binary(InsertQuery),
846: select_query => SelectQuery,
847: select_result => SelectResult,
848: insert_result => InsertResult}).
849:
850: check_binary_8k(Config, Value) ->
851: check_binary(Config, Value, <<"binary_data_8k">>).
852:
853: check_binary_65k(Config, Value) ->
854: check_binary(Config, Value, <<"binary_data_65k">>).
855:
856: check_binary_16m(Config, Value) ->
857: check_binary(Config, Value, <<"binary_data_16m">>).
858:
859: check_binary(Config, Value, Column) ->
860: SValue = escape_binary_or_null(Config, Value),
861: EraseResult = erase_table(Config),
862: InsertQuery = ["INSERT INTO test_types (", Column, ") "
863: "VALUES (", use_escaped(Config, SValue), ")"],
864: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>,
865: InsertResult = sql_query(Config, InsertQuery),
866: SelectResult = sql_query(Config, SelectQuery),
867: %% Compare as binaries
868: ?assert_equal_extra({selected, [{Value}]},
869: selected_unescape(Config, SelectResult),
870: #{erase_result => EraseResult,
871: inserted_length => byte_size_or_null(Value),
872: %% pgsql+odbc can truncate binaries
873: maybe_selected_length => maybe_selected_length(Config, SelectResult),
874: maybe_selected_tail => maybe_selected_tail(Config, SelectResult),
875: compare_selected => compare_selected(Config, selected_unescape(Config, SelectResult), Value),
876: test_value => Value,
877: insert_query_binary => iolist_to_binary(InsertQuery),
878: select_query => SelectQuery,
879: select_result => SelectResult,
880: insert_result => InsertResult}).
881:
882: byte_size_or_null(null) ->
883: null;
884: byte_size_or_null(Value) ->
885: byte_size(Value).
886:
887: check_enum_char(Config, Value) when is_binary(Value) ->
888: SValue = escape_string_or_null(Config, Value),
889: EraseResult = erase_table(Config),
890: InsertQuery = ["INSERT INTO test_types (enum_char) "
891: "VALUES (", use_escaped(Config, SValue), ")"],
892: SelectQuery = <<"SELECT enum_char FROM test_types">>,
893: InsertResult = sql_query(Config, InsertQuery),
894: SelectResult = sql_query(Config, SelectQuery),
895: %% Compare as binaries
896: ?assert_equal_extra({selected, [{Value}]},
897: SelectResult,
898: #{erase_result => EraseResult,
899: test_value => Value,
900: insert_query => InsertQuery,
901: insert_query_binary => iolist_to_binary(InsertQuery),
902: select_query => SelectQuery,
903: select_result => SelectResult,
904: insert_result => InsertResult}).
905:
906: check_boolean(Config, Value) when is_boolean(Value) ->
907: SValue = escape_boolean(Config, Value),
908: EraseResult = erase_table(Config),
909: InsertQuery = ["INSERT INTO test_types (bool_flag) "
910: "VALUES (", use_escaped(Config, SValue), ")"],
911: SelectQuery = <<"SELECT bool_flag FROM test_types">>,
912: InsertResult = sql_query(Config, InsertQuery),
913: SelectResult = sql_query(Config, SelectQuery),
914: %% Compare as binaries
915: ?assert_equal_extra({selected, [{Value}]},
916: selected_decode_boolean(Config, SelectResult),
917: #{erase_result => EraseResult,
918: test_value => Value,
919: insert_query => InsertQuery,
920: insert_query_binary => iolist_to_binary(InsertQuery),
921: select_query => SelectQuery,
922: select_result => SelectResult,
923: insert_result => InsertResult}).
924:
925: selected_unescape(_Config, {selected, [{null}]}) ->
926: {selected, [{null}]};
927: selected_unescape(Config, {selected, [{Value}]}) ->
928: {selected, [{unescape_binary(Config, Value)}]};
929: selected_unescape(_Config, Other) ->
930: Other.
931:
932: selected_decode_boolean(Config, {selected, [{Value}]}) ->
933: {selected, [{decode_boolean(Config, Value)}]};
934: selected_decode_boolean(_Config, Other) ->
935: Other.
936:
937: selected_boolean_to_binary_int(Config, {selected, [{Value}]}) ->
938: {selected, [{boolean_to_binary_int(decode_boolean(Config, Value))}]};
939: selected_boolean_to_binary_int(_Config, Other) ->
940: Other.
941:
942: boolean_to_binary_int(true) -> <<"1">>;
943: boolean_to_binary_int(false) -> <<"0">>.
944:
945: maybe_selected_length(_Config, {selected, [{Value}]}) when is_binary(Value) ->
946: byte_size(Value);
947: maybe_selected_length(_Config, _Other) ->
948: unknown.
949:
950: maybe_selected_tail(Config, Selected) ->
951: maybe_selected_tail(Config, Selected, 100).
952:
953: maybe_selected_tail(_Config, {selected, [{Value}]}, TailLen)
954: when is_binary(Value), byte_size(Value) > TailLen ->
955: binary:part(Value, {byte_size(Value), -TailLen});
956: maybe_selected_tail(_Config, {selected, [{Value}]}, _TailLen) ->
957: Value;
958: maybe_selected_tail(_Config, _Other, _TailLen) ->
959: unknown.
960:
961: check_prep_int32(Config, Value) ->
962: check_generic_prep_integer(Config, Value, <<"int32">>).
963:
964: check_prep_int64(Config, Value) ->
965: check_generic_prep_integer(Config, Value, <<"int64">>).
966:
967: check_prep_unicode(Config, Value) ->
968: check_generic_prep(Config, Value, <<"unicode">>).
969:
970: check_prep_unicode250(Config, Value) ->
971: check_generic_prep(Config, Value, <<"unicode250">>).
972:
973: %% Char is ascii string of length one
974: check_prep_ascii_char(Config, Value) ->
975: check_generic_prep(Config, Value, <<"unicode">>).
976:
977: %% Varchar
978: check_prep_ascii_string(Config, Value) ->
979: check_generic_prep(Config, Value, <<"ascii_string">>).
980:
981: check_prep_binary_65k(Config, Value) ->
982: %% MSSQL returns binaries in HEX encoding
983: check_generic_prep(Config, Value, <<"binary_data_65k">>, unescape_binary).
984:
985: check_prep_binary_8k(Config, Value) ->
986: %% MSSQL returns binaries in HEX encoding
987: check_generic_prep(Config, Value, <<"binary_data_8k">>, unescape_binary).
988:
989: check_prep_binary_16m(Config, Value) ->
990: %% MSSQL returns binaries in HEX encoding
991: check_generic_prep(Config, Value, <<"binary_data_16m">>, unescape_binary).
992:
993: check_generic_prep_integer(Config, Value, Column) ->
994: check_generic_prep(Config, Value, Column).
995:
996: check_prep_enum_char(Config, Value) ->
997: check_generic_prep(Config, Value, <<"enum_char">>).
998:
999: check_prep_boolean(Config, Value) ->
1000: check_generic_prep(Config, Value, <<"bool_flag">>, boolean_to_binary_int).
1001:
1002: %% Data types
1003: %% {ok, Conn} = odbc:connect("DSN=mongoose-mssql;UID=sa;PWD=mongooseim_secret+ESL123", []).
1004: %% odbc:describe_table(Conn, "test_types").
1005: %% [{"unicode",{sql_wvarchar,536870911}},
1006: %% {"binary_data_65k",'SQL_VARBINARY'},
1007: %% {"ascii_char",{sql_char,1}},
1008: %% {"ascii_string",{sql_varchar,250}},
1009: %% {"int32",sql_integer},
1010: %% {"int64",'SQL_BIGINT'},
1011: %% {"int8",sql_tinyint}]
1012:
1013: check_generic_prep(Config, Value, Column) ->
1014: check_generic_prep(Config, Value, Column, to_binary).
1015:
1016: check_generic_prep(Config, Value, Column, TransformResult) ->
1017: EraseResult = erase_table(Config),
1018:
1019: InsertQuery = <<"INSERT INTO test_types (", Column/binary, ") "
1020: "VALUES (?)">>,
1021: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>,
1022: Name = list_to_atom("insert_" ++ binary_to_list(Column)),
1023: Table = test_types,
1024: Fields = [binary_to_atom(Column, utf8)],
1025: PrepareResult = sql_prepare(Config, Name, Table, Fields, InsertQuery),
1026: Parameters = [Value],
1027: InsertResult = sql_execute(Config, Name, Parameters),
1028: SelectResult = sql_query(Config, SelectQuery),
1029: %% Compare as binaries
1030: ?assert_equal_extra({selected, [{value_to_binary(Value)}]},
1031: transform_selected(TransformResult, Config, SelectResult),
1032: #{column => Column,
1033: erase_result => EraseResult,
1034: test_value => Value,
1035: insert_query => InsertQuery,
1036: prepare_result => PrepareResult,
1037: select_query => SelectQuery,
1038: select_result => SelectResult,
1039: insert_result => InsertResult}),
1040: check_generic_filtered_prep(Config, Value, Column, TransformResult),
1041: case is_odbc() of
1042: true ->
1043: %% TOP is mssql feature, all other databases use LIMIT.
1044: check_generic_filtered_top_prep(Config, Value, Column, TransformResult);
1045: false ->
1046: ok
1047: end.
1048:
1049: %% We want to ensure that variable substitution works in SELECTS too.
1050: %% We also want to check the result value is encoded correctly.
1051: check_generic_filtered_prep(_Config, null, _Column, _TransformResult) ->
1052: skip_null_test;
1053: check_generic_filtered_prep(Config, Value, Column, TransformResult) ->
1054: SelectQuery = <<"SELECT ", Column/binary,
1055: " FROM test_types WHERE ", Column/binary, " = ?">>,
1056: Name = list_to_atom("select_filtered_" ++ binary_to_list(Column)),
1057: Table = test_types,
1058: Fields = [binary_to_atom(Column, utf8)],
1059: PrepareResult = sql_prepare(Config, Name, Table, Fields, SelectQuery),
1060: Parameters = [Value],
1061: SelectResult = sql_execute(Config, Name, Parameters),
1062: %% Compare as binaries
1063: ?assert_equal_extra({selected, [{value_to_binary(Value)}]},
1064: transform_selected(TransformResult, Config, SelectResult),
1065: #{column => Column,
1066: test_value => Value,
1067: prepare_result => PrepareResult,
1068: select_query => SelectQuery,
1069: select_result => SelectResult}).
1070:
1071: check_generic_filtered_top_prep(_Config, null, _Column, _TransformResult) ->
1072: skip_null_test;
1073: check_generic_filtered_top_prep(Config, Value, Column, TransformResult) ->
1074: %% SQL Server requires you to place parenthesis around the argument to top if you pass in a variable:
1075: %% https://stackoverflow.com/questions/7038818/ms-sql-exception-incorrect-syntax-near-p0
1076: SelectQuery = <<"SELECT TOP (?) ", Column/binary,
1077: " FROM test_types WHERE ", Column/binary, " = ?">>,
1078: Name = list_to_atom("select_filtered_top_" ++ binary_to_list(Column)),
1079: Table = test_types,
1080: Fields = [limit, binary_to_atom(Column, utf8)],
1081: PrepareResult = sql_prepare(Config, Name, Table, Fields, SelectQuery),
1082: Parameters = [30, Value],
1083: SelectResult = sql_execute(Config, Name, Parameters),
1084: %% Compare as binaries
1085: ?assert_equal_extra({selected, [{value_to_binary(Value)}]},
1086: transform_selected(TransformResult, Config, SelectResult),
1087: #{column => Column,
1088: test_value => Value,
1089: prepare_result => PrepareResult,
1090: select_query => SelectQuery,
1091: select_result => SelectResult}).
1092:
1093:
1094:
1095: transform_selected(to_binary, _Config, SelectResult) ->
1096: selected_to_binary(SelectResult);
1097: transform_selected(unescape_binary, Config, SelectResult) ->
1098: selected_unescape(Config, SelectResult);
1099: transform_selected(boolean_to_binary_int, Config, SelectResult) ->
1100: selected_boolean_to_binary_int(Config, SelectResult).
1101:
1102: %% To KISS, we just test on a table with one row.
1103: check_like(Config, TextMap = #{text := TextValue,
1104: matching := MatchingList,
1105: not_matching := NotMatchingList}) ->
1106: SValue = escape_string_or_null(Config, TextValue),
1107: EraseResult = erase_table(Config),
1108: InsertQuery = ["INSERT INTO test_types (unicode) "
1109: "VALUES (", use_escaped(Config, SValue), ")"],
1110: InsertResult = sql_query(Config, InsertQuery),
1111: Info = #{erase_result => EraseResult,
1112: insert_query => InsertQuery,
1113: insert_query_binary => iolist_to_binary(InsertQuery),
1114: insert_result => InsertResult,
1115: text_map => TextMap},
1116: [check_like_matching(Config, TextValue, Matching, Info)
1117: || Matching <- MatchingList],
1118: [check_like_not_matching(Config, TextValue, NotMatching, Info)
1119: || NotMatching <- NotMatchingList].
1120:
1121: check_like_matching(Config, TextValue, Matching, Info) ->
1122: SLike = escape_like(Config, Matching),
1123: SelectQuery = ["SELECT unicode FROM test_types "
1124: "WHERE unicode LIKE ", use_escaped_like(Config, SLike)],
1125: SelectResult = sql_query(Config, SelectQuery),
1126: %% Compare as binaries
1127: ?assert_equal_extra({selected, [{TextValue}]},
1128: SelectResult,
1129: Info#{pattern => Matching,
1130: select_query => SelectQuery,
1131: select_result => SelectResult}).
1132:
1133: check_like_not_matching(Config, _TextValue, NotMatching, Info) ->
1134: SLike = escape_like(Config, NotMatching),
1135: SelectQuery = ["SELECT unicode FROM test_types "
1136: "WHERE unicode LIKE ", use_escaped_like(Config, SLike)],
1137: SelectResult = sql_query(Config, SelectQuery),
1138: %% Compare as binaries
1139: ?assert_equal_extra({selected, []},
1140: SelectResult,
1141: Info#{pattern => NotMatching,
1142: select_query => SelectQuery,
1143: select_result => SelectResult}).
1144:
1145: compare_selected(_Config, {selected, [{SelValue}]}, Value) ->
1146: drop_common_prefix(0, SelValue, Value);
1147: compare_selected(_Config, _, _Value) ->
1148: nomatch.
1149:
1150: drop_common_prefix(Pos, <<X, SelValue/binary>>, <<X, Value/binary>>) ->
1151: drop_common_prefix(Pos+1, SelValue, Value);
1152: drop_common_prefix(Pos, SelValue, Value) ->
1153: #{pos => Pos,
1154: selected_suffix => safe_binary(100, SelValue),
1155: expected_suffix => safe_binary(100, Value)}.
1156:
1157: db_engine() ->
1158: escalus_ejabberd:rpc(mongoose_rdbms, db_engine, [host_type()]).
1159:
1160: is_odbc() ->
1161: db_engine() == odbc.
1162:
1163: is_pgsql() ->
1164: db_engine() == pgsql.
1165:
1166: is_mysql() ->
1167: db_engine() == mysql.
1168:
1169: stop_global_default_pool() ->
1170: Pools = rpc(mim(), mongoose_config, get_opt, [outgoing_pools]),
1171: [GlobalRdbmsPool] = [Pool || Pool = #{type := rdbms, scope := global, tag := default} <- Pools],
1172: ok = rpc(mim(), mongoose_wpool, stop, [rdbms, global, default]),
1173: Extra = maybe_stop_service_domain_db(),
1174: [{tag, tag()}, {global_default_rdbms_pool, GlobalRdbmsPool} | Extra].
1175:
1176: restart_global_default_pool(Config) ->
1177: GlobalRdbmsPool = ?config(global_default_rdbms_pool, Config),
1178: rpc(mim(), mongoose_wpool, start_configured_pools, [[GlobalRdbmsPool]]),
1179: maybe_restart_service_domain_db(Config).
1180:
1181: maybe_stop_service_domain_db() ->
1182: case rpc(mim(), erlang, whereis, [service_domain_db]) of
1183: undefined ->
1184: [];
1185: ServiceDomainDB when is_pid(ServiceDomainDB) ->
1186: ok = rpc(mim(), sys, suspend, [ServiceDomainDB]),
1187: [{service_domain_db, ServiceDomainDB}]
1188: end.
1189:
1190: maybe_restart_service_domain_db(Config) ->
1191: case ?config(service_domain_db, Config) of
1192: undefined ->
1193: ok;
1194: ServiceDomainDB ->
1195: ok = rpc(mim(), sys, resume, [ServiceDomainDB])
1196: end.
1197:
1198: start_local_host_type_pool(Config) ->
1199: GlobalRdbmsPool = ?config(global_default_rdbms_pool, Config),
1200: LocalHostTypePool = GlobalRdbmsPool#{scope := host_type(), tag := tag()},
1201: rpc(mim(), mongoose_wpool, start_configured_pools, [[LocalHostTypePool], [host_type()]]).
1202:
1203: escape_column(Name) ->
1204: case is_mysql() of
1205: true ->
1206: <<"`", Name/binary, "`">>;
1207: false ->
1208: Name
1209: end.
1210:
1211: slow_rpc(M, F, A) ->
1212: Node = ct:get_config({hosts, mim, node}),
1213: Cookie = escalus_ct:get_config(ejabberd_cookie),
1214: Res = escalus_rpc:call(Node, M, F, A, timer:seconds(30), Cookie),
1215: case Res of
1216: {badrpc, timeout} ->
1217: {badrpc, {timeout, M, F}};
1218: _ ->
1219: Res
1220: end.
1221:
1222: check_not_received(Msg) ->
1223: receive
1224: Msg ->
1225: error({msg_received, Msg});
1226: _ ->
1227: check_not_received(Msg)
1228: after 0 ->
1229: ok
1230: end.
1231:
1232: check_like_prep(Config, TextMap = #{text := TextValue,
1233: matching := MatchingList,
1234: not_matching := NotMatchingList}) ->
1235: EraseResult = erase_table(Config),
1236: Name = insert_unicode_prep,
1237: SelName = select_unicode_prep,
1238: Table = test_types,
1239: Fields = [<<"unicode">>],
1240: InsertQuery = <<"INSERT INTO test_types (unicode) VALUES (?)">>,
1241: SelectQuery = <<"SELECT unicode FROM test_types WHERE unicode LIKE ? ESCAPE '$'">>,
1242: PrepareResult = sql_prepare(Config, Name, Table, Fields, InsertQuery),
1243: PrepareSelResult = sql_prepare(Config, SelName, Table, Fields, SelectQuery),
1244: Parameters = [TextValue],
1245: InsertResult = sql_execute(Config, Name, Parameters),
1246: Info = #{erase_result => EraseResult,
1247: insert_query => InsertQuery,
1248: prepare_result => PrepareResult,
1249: insert_result => InsertResult,
1250: prepare_select_result => PrepareSelResult,
1251: text_map => TextMap},
1252: [check_like_matching_prep(SelName, Config, TextValue, Matching, Info)
1253: || Matching <- MatchingList],
1254: [check_like_not_matching_prep(SelName, Config, TextValue, NotMatching, Info)
1255: || NotMatching <- NotMatchingList].
1256:
1257: check_like_matching_prep(SelName, Config, TextValue, Matching, Info) ->
1258: SMatching = escape_prepared_like(Config, Matching),
1259: Parameters = [<<"%", SMatching/binary, "%">>],
1260: SelectResult = sql_execute(Config, SelName, Parameters),
1261: %% Compare as binaries
1262: ?assert_equal_extra({selected, [{TextValue}]},
1263: SelectResult,
1264: Info#{pattern => Matching,
1265: select_result => SelectResult}).
1266:
1267: check_like_not_matching_prep(SelName, Config, _TextValue, NotMatching, Info) ->
1268: SNotMatching = escape_prepared_like(Config, NotMatching),
1269: Parameters = [<<"%", SNotMatching/binary, "%">>],
1270: SelectResult = sql_execute(Config, SelName, Parameters),
1271: %% Compare as binaries
1272: ?assert_equal_extra({selected, []},
1273: SelectResult,
1274: Info#{pattern => NotMatching,
1275: select_result => SelectResult}).