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}).