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