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