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