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: %%-------------------------------------------------------------------- 28: %% Suite configuration 29: %%-------------------------------------------------------------------- 30: 31: all() -> 32: [{group, rdbms_queries}]. 33: 34: groups() -> 35: [{rdbms_queries, [], rdbms_queries_cases()}]. 36: 37: rdbms_queries_cases() -> 38: [select_one_works_case, 39: select_ascii_string_works_case, 40: read_int32_case, 41: read_int64_case, 42: read_unicode_case, 43: read_unicode250_case, 44: read_ascii_char_case, 45: read_ascii_string_case, 46: read_binary_8k_case, 47: read_binary_65k_case, 48: read_binary_16m_case, 49: read_enum_char_case, 50: read_boolean_case, 51: 52: read_prep_int32_case, 53: read_prep_int64_case, 54: read_prep_unicode_case, 55: read_prep_unicode250_case, 56: read_prep_ascii_char_case, 57: read_prep_ascii_string_case, 58: read_prep_binary_8k_case, 59: read_prep_binary_65k_case, 60: read_prep_binary_16m_case, 61: read_prep_enum_char_case, 62: read_prep_boolean_case, 63: 64: select_like_case, 65: select_like_prep_case, 66: 67: insert_batch_with_null_case, 68: test_cast_insert, 69: arguments_from_two_tables]. 70: 71: suite() -> 72: escalus:suite(). 73: 74: %%-------------------------------------------------------------------- 75: %% Init & teardown 76: %%-------------------------------------------------------------------- 77: init_per_suite(Config) -> 78: case not ct_helper:is_ct_running() 79: orelse mongoose_helper:is_rdbms_enabled(host_type()) of 80: false -> {skip, rdbms_or_ct_not_running}; 81: true -> escalus:init_per_suite(Config) 82: end. 83: 84: end_per_suite(Config) -> 85: escalus:end_per_suite(Config). 86: 87: init_per_testcase(CaseName, Config) -> 88: escalus:init_per_testcase(CaseName, Config). 89: 90: end_per_testcase(CaseName, Config) -> 91: escalus:end_per_testcase(CaseName, Config). 92: 93: %%-------------------------------------------------------------------- 94: %% Data for cases 95: %%-------------------------------------------------------------------- 96: 97: int32_values() -> 98: [1, -1, 0, 42, 2147483647, null]. 99: 100: int64_values() -> 101: [9223372036854775807, 102: null]. 103: 104: ascii_string_values() -> 105: [<<>>, <<"1">>, <<"test">>, 106: <<"\\">>, <<"\\\\">>, 107: <<"'">>, <<"''">>, <<"'''">>, 108: <<"\"">>, <<"\"\"">>, 109: <<"\r\n">>, <<"\r">>, <<"\n">>, 110: binary:copy(<<"a">>, 250), null]. 111: 112: unicode_values() -> 113: ascii_string_values() ++ 114: [<<"юникод"/utf8>>, <<"😁"/utf8>>] 115: ++ 116: %% Would fail with binary_data_8k and mssql. 117: %% For some reason mssql returns string "7878...." of length 4000. 118: %% What is 78? 16#78 = 120 = $x. 119: %% i.e. half of 8000 bytes for data. 120: %% Probably 2 bytes encoding is used for this. 121: % [binary:copy(<<$x>>, 4001), 122: %% Helps to debug if we don't consume all data from a buffer. 123: %% Than there would be a gap of missing numbers in the middle. 124: %% 1000 of 1-es, 1000 of 2-s, ..., 1000 of 10-s. 125: %% 126: %% In one version of eodbc, it returns 5,5,5,5... instead of 1,1,1,1... 127: %% 128: %% Also, 129: %% eodbc:sql_query(Conn, "SELECT convert(varbinary(max), binary_data_8k) FROM test_types") = gives correct result. 130: %% but 131: %% eodbc:sql_query(Conn, "SELECT binary_data_8k FROM test_types") = gives not correct result. 132: %% 133: %% eodbc:sql_query(Conn, "SELECT convert(varbinary(1000), binary_data_8k) FROM test_types") = gives correct result. 134: %% gives 010101.... as expected 135: [iolist_to_binary([lists:duplicate(1000, X) || X <- lists:seq(1, 10)]), 136: binary:copy(<<$a>>, 10000), 137: %% There is a bug with 8001 chars limit in upstream odbc 138: %% We use a fork arcusfelis/eodbc, that has the bug fixed 139: %% https://bugs.erlang.org/browse/ERL-421 140: binary:copy(<<10>>, 10000), null]. 141: 142: binary_values() -> 143: [<<0>>, <<"255">>, 144: <<240,159>>, %% Incomplete unicode 145: <<240,159,0>>, %% Invalid unicode 146: iolist_to_binary(lists:seq(0, 255)), 147: %% one kilobyte 148: binary:copy(<<1>>, 1024), 149: %% two kilobytes 150: binary:copy(<<2>>, 2048), 151: binary:copy(<<5>>, 1024*5), 152: %% There is a bug with 8001 chars limit in upstream odbc 153: %% We use a fork arcusfelis/eodbc, that has the bug fixed 154: %% https://bugs.erlang.org/browse/ERL-421 155: binary:copy(<<8>>, 8002), 156: binary:copy(<<0>>, 100000), 157: null 158: ] ++ 159: case is_odbc() orelse is_pgsql() of 160: true -> 161: []; 162: false -> 163: %% FIXME long data causes timeout with mssql 164: %% 165: %% FIXME %% epgsql_sock:handle_info/2 is not optimized 166: %% The query takes 30 seconds on Postgres 167: %% mongoose_rdbms:sql_query(<<"localhost">>, <<"SELECT binary_data_16m FROM test_types">>). 168: [binary:copy(<<16>>, 16777215)] 169: end. 170: 171: binary_8k_values() -> 172: truncate_binaries(8000, unicode_values() ++ binary_values()). 173: 174: binary_65k_values() -> 175: truncate_binaries(65535, unicode_values() ++ binary_values()). 176: 177: binary_16m_values() -> 178: truncate_binaries(16777215, unicode_values() ++ binary_values()). 179: 180: ascii_char_values() -> 181: [<<"a">>, <<"b">>]. 182: 183: enum_char_values() -> 184: [<<"A">>, <<"B">>, <<"C">>]. 185: 186: simple_like_texts() -> 187: [#{text => <<"hello user!">>, 188: not_matching => [<<"hi">>, <<"help">>], 189: matching => [<<"hello">>, <<"user">>, <<"hell">>]}, 190: #{text => <<60,79,67,32,59,48,63,58,48>>, 191: not_matching => [<<62,66,64,48,65,66,53,66>>], 192: matching => [<<60,79,67>>]}]. 193: 194: like_texts() -> 195: simple_like_texts() ++ 196: [#{text => <<"abc%">>, 197: not_matching => [<<"ab%">>, <<"%bc%">>], 198: matching => [<<"abc%">>, <<"abc">>]}, 199: #{text => <<"żółć_"/utf8>>, 200: not_matching => [<<"_ółć_"/utf8>>], 201: matching => [<<"żół"/utf8>>, <<"ółć_"/utf8>>]}]. 202: 203: %%-------------------------------------------------------------------- 204: %% Test cases 205: %%-------------------------------------------------------------------- 206: 207: %% Checks, that we at least can connect to db 208: select_one_works_case(Config) -> 209: ?assert_equal({selected, [{<<"1">>}]}, 210: %% Postgres direct driver returns <<"1">> 211: %% Everyone else return 1 212: selected_to_binary(sql_query(Config, <<"SELECT 1">>))). 213: 214: %% Should be binary. 215: %% Unicode is another case. 216: select_ascii_string_works_case(Config) -> 217: ?assert_equal({selected, [{<<"ascii">>}]}, 218: sql_query(Config, <<"SELECT 'ascii'">>)). 219: 220: %% Writes and reads values of this datatype back 221: read_int32_case(Config) -> 222: Values = int32_values(), 223: [check_int32(Config, Value) || Value <- Values]. 224: 225: read_int64_case(Config) -> 226: Values = int32_values() ++ int64_values(), 227: [check_int64(Config, Value) || Value <- Values]. 228: 229: read_unicode_case(Config) -> 230: [check_unicode(Config, Value) || Value <- unicode_values()]. 231: 232: read_unicode250_case(Config) -> 233: [check_unicode250(Config, Value) || Value <- unicode_values(), byte_size(Value) < 250]. 234: 235: read_ascii_char_case(Config) -> 236: [check_ascii_char(Config, Value) || Value <- ascii_char_values()]. 237: 238: read_ascii_string_case(Config) -> 239: [check_ascii_string(Config, Value) 240: || Value <- ascii_char_values() ++ ascii_string_values()]. 241: 242: read_binary_8k_case(Config) -> 243: [check_binary_8k(Config, Value) || Value <- binary_8k_values()]. 244: 245: read_binary_65k_case(Config) -> 246: [check_binary_65k(Config, Value) || Value <- binary_65k_values()]. 247: 248: read_binary_16m_case(Config) -> 249: [check_binary_16m(Config, Value) || Value <- binary_16m_values()]. 250: 251: read_enum_char_case(Config) -> 252: [check_enum_char(Config, Value) || Value <- enum_char_values()]. 253: 254: read_boolean_case(Config) -> 255: [check_boolean(Config, Value) || Value <- [true, false]]. 256: 257: %%-------------------------------------------------------------------- 258: %% Prepared cases 259: %%-------------------------------------------------------------------- 260: 261: read_prep_int32_case(Config) -> 262: Values = int32_values(), 263: [check_prep_int32(Config, Value) || Value <- Values]. 264: 265: read_prep_int64_case(Config) -> 266: Values = int32_values() ++ int64_values(), 267: [check_prep_int64(Config, Value) || Value <- Values]. 268: 269: read_prep_unicode_case(Config) -> 270: [check_prep_unicode(Config, Value) || Value <- unicode_values()]. 271: 272: read_prep_unicode250_case(Config) -> 273: [check_prep_unicode250(Config, Value) || Value <- unicode_values(), byte_size(Value) < 250]. 274: 275: read_prep_ascii_char_case(Config) -> 276: [check_prep_ascii_char(Config, Value) || Value <- ascii_char_values()]. 277: 278: read_prep_ascii_string_case(Config) -> 279: [check_prep_ascii_string(Config, Value) 280: || Value <- ascii_char_values() ++ ascii_string_values()]. 281: 282: read_prep_binary_8k_case(Config) -> 283: [check_prep_binary_8k(Config, Value) || Value <- binary_8k_values()]. 284: 285: read_prep_binary_65k_case(Config) -> 286: [check_prep_binary_65k(Config, Value) || Value <- binary_65k_values()]. 287: 288: read_prep_binary_16m_case(Config) -> 289: [check_prep_binary_16m(Config, Value) || Value <- binary_16m_values()]. 290: 291: read_prep_enum_char_case(Config) -> 292: [check_prep_enum_char(Config, Value) || Value <- enum_char_values()]. 293: 294: read_prep_boolean_case(Config) -> 295: [check_prep_boolean(Config, Value) || Value <- [0, 1]]. 296: 297: truncate_binaries(Len, List) -> 298: [truncate_binary(Len, Bin) || Bin <- List]. 299: 300: truncate_binary(Len, Bin) when byte_size(Bin) > Len -> 301: binary:part(Bin, {0,Len}); 302: truncate_binary(_Len, Bin) -> 303: Bin. 304: 305: safe_binary(Len, Bin) when byte_size(Bin) > Len -> 306: #{what => truncated_safe_binary, 307: truncated_length => Len, 308: total_length => byte_size(Bin), 309: truncated_binary => binary:part(Bin, {0,Len})}; 310: safe_binary(_Len, Bin) -> 311: Bin. 312: 313: arguments_from_two_tables(Config) -> 314: erase_users(Config), 315: sql_prepare(Config, select_multi_args, users, [password, 'last.seconds'], 316: <<"SELECT users.username from users " 317: " LEFT JOIN last ON (last.username = users.username) " 318: " WHERE users.password = ? AND last.seconds > ?">>), 319: UserInsert = "INSERT INTO users (username, server, password) VALUES ", 320: sql_query(Config, UserInsert ++ "('alice', 'domain', 'secret')"), 321: sql_query(Config, UserInsert ++ "('bob', 'domain', 'billy')"), 322: LastInsert = "INSERT INTO last (username, server, seconds, state) VALUES ", 323: sql_query(Config, LastInsert ++ "('alice', 'domain', 1615368268, 'ok')"), 324: sql_query(Config, LastInsert ++ "('bob', 'domain', 1610000000, 'cool')"), 325: SelectResult = sql_execute(Config, select_multi_args, [<<"secret">>, 1611000000]), 326: ?assert_equal({selected, [{<<"alice">>}]}, SelectResult), 327: erase_users(Config), 328: ok. 329: 330: %% Ensures that ODBC uses a correct type when encoding NULL 331: %% and it does not interfere with non-null values 332: insert_batch_with_null_case(Config) -> 333: erase_table(Config), 334: sql_prepare(Config, insert_batch, test_types, [unicode, unicode], 335: <<"INSERT INTO test_types(unicode) VALUES (?), (?)">>), 336: sql_execute(Config, insert_batch, [null, <<"check1">>]), 337: sql_execute(Config, insert_batch, [<<"check2">>, null]), 338: SelectResult = sql_query(Config, "SELECT unicode FROM test_types"), 339: %% Sorting with null values is DB specific, so sort it with Erlang 340: ?assert_equal({selected, [{null}, {null}, {<<"check1">>}, {<<"check2">>}]}, 341: selected_to_sorted(SelectResult)). 342: 343: test_cast_insert(Config) -> 344: erase_table(Config), 345: sql_prepare(Config, insert_one, test_types, [unicode], 346: <<"INSERT INTO test_types(unicode) VALUES (?)">>), 347: sql_execute_cast(Config, insert_one, [<<"check1">>]), 348: sql_query_cast(Config, <<"INSERT INTO test_types(unicode) VALUES ('check2')">>), 349: mongoose_helper:wait_until( 350: fun() -> 351: SelectResult = sql_query(Config, "SELECT unicode FROM test_types"), 352: ?assertEqual({selected, [{<<"check1">>}, {<<"check2">>}]}, 353: selected_to_sorted(SelectResult)) 354: end, ok, #{name => cast_queries}). 355: 356: %%-------------------------------------------------------------------- 357: %% Text searching 358: %%-------------------------------------------------------------------- 359: 360: select_like_case(Config) -> 361: %% Non-prepared queries don't support proper LIKE escaping 362: [check_like(Config, TextMap) || TextMap <- simple_like_texts()]. 363: 364: select_like_prep_case(Config) -> 365: [check_like_prep(Config, TextMap) || TextMap <- like_texts()]. 366: 367: %%-------------------------------------------------------------------- 368: %% Helpers 369: %%-------------------------------------------------------------------- 370: 371: sql_query(_Config, Query) -> 372: slow_rpc(mongoose_rdbms, sql_query, [host_type(), Query]). 373: 374: sql_prepare(_Config, Name, Table, Fields, Query) -> 375: escalus_ejabberd:rpc(mongoose_rdbms, prepare, [Name, Table, Fields, Query]). 376: 377: sql_execute(_Config, Name, Parameters) -> 378: slow_rpc(mongoose_rdbms, execute, [host_type(), Name, Parameters]). 379: 380: sql_execute_cast(_Config, Name, Parameters) -> 381: slow_rpc(mongoose_rdbms, execute_cast, [host_type(), Name, Parameters]). 382: 383: sql_query_cast(_Config, Query) -> 384: slow_rpc(mongoose_rdbms, sql_query_cast, [host_type(), Query]). 385: 386: escape_null(_Config) -> 387: escalus_ejabberd:rpc(mongoose_rdbms, escape_null, []). 388: 389: escape_string(_Config, Value) -> 390: escalus_ejabberd:rpc(mongoose_rdbms, escape_string, [Value]). 391: 392: escape_binary(_Config, Value) -> 393: slow_rpc(mongoose_rdbms, escape_binary, [host_type(), Value]). 394: 395: escape_boolean(_Config, Value) -> 396: escalus_ejabberd:rpc(mongoose_rdbms, escape_boolean, [Value]). 397: 398: escape_like(_Config, Value) -> 399: escalus_ejabberd:rpc(mongoose_rdbms, escape_like, [Value]). 400: 401: escape_prepared_like(_Config, Value) -> 402: escalus_ejabberd:rpc(mongoose_rdbms, escape_prepared_like, [Value]). 403: 404: unescape_binary(_Config, Value) -> 405: escalus_ejabberd:rpc(mongoose_rdbms, unescape_binary, [host_type(), Value]). 406: 407: use_escaped(_Config, Value) -> 408: escalus_ejabberd:rpc(mongoose_rdbms, use_escaped, [Value]). 409: 410: use_escaped_like(_Config, Value) -> 411: escalus_ejabberd:rpc(mongoose_rdbms, use_escaped_like, [Value]). 412: 413: escape_string_or_null(Config, null) -> 414: escape_null(Config); 415: escape_string_or_null(Config, TextValue) -> 416: escape_string(Config, TextValue). 417: 418: escape_binary_or_null(Config, null) -> 419: escape_null(Config); 420: escape_binary_or_null(Config, Value) -> 421: escape_binary(Config, Value). 422: 423: decode_boolean(_Config, Value) -> 424: escalus_ejabberd:rpc(mongoose_rdbms, to_bool, [Value]). 425: 426: erase_table(Config) -> 427: sql_query(Config, <<"TRUNCATE TABLE test_types">>). 428: 429: erase_users(Config) -> 430: sql_query(Config, <<"TRUNCATE TABLE users">>), 431: sql_query(Config, <<"TRUNCATE TABLE last">>). 432: 433: check_int32(Config, Value) -> 434: check_generic_integer(Config, Value, <<"int32">>). 435: 436: check_int64(Config, Value) -> 437: check_generic_integer(Config, Value, <<"int64">>). 438: 439: check_generic_integer(Config, Value, Column) -> 440: EraseResult = erase_table(Config), 441: InsertQuery = <<"INSERT INTO test_types (", Column/binary, ") " 442: "VALUES (", (integer_or_null_to_binary(Value))/binary, ")">>, 443: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>, 444: InsertResult = sql_query(Config, InsertQuery), 445: SelectResult = sql_query(Config, SelectQuery), 446: %% Compare as binaries 447: ?assert_equal_extra({selected, [{integer_to_binary_or_null(Value)}]}, 448: selected_to_binary(SelectResult), 449: #{column => Column, 450: erase_result => EraseResult, 451: test_value => Value, 452: insert_query => InsertQuery, 453: select_query => SelectQuery, 454: select_result => SelectResult, 455: insert_result => InsertResult}). 456: 457: integer_or_null_to_binary(null) -> <<"NULL">>; 458: integer_or_null_to_binary(X) -> integer_to_binary(X). 459: 460: integer_to_binary_or_null(null) -> null; 461: integer_to_binary_or_null(X) -> integer_to_binary(X). 462: 463: %% Helper function to transform values to an uniform format. 464: %% Single tuple, single element case. 465: %% In ODBC int32 is integer, but int64 is binary. 466: selected_to_binary({selected, [{Value}]}) when is_integer(Value) -> 467: {selected, [{integer_to_binary(Value)}]}; 468: selected_to_binary(Other) -> 469: Other. 470: 471: selected_to_sorted({selected, Rows}) -> 472: {selected, lists:sort(Rows)}; 473: selected_to_sorted(Other) -> 474: Other. 475: 476: value_to_binary(Value) when is_integer(Value) -> 477: integer_to_binary(Value); 478: value_to_binary(Value) -> 479: Value. 480: 481: check_unicode250(Config, Value) -> 482: check_unicode_generic(Config, Value, <<"unicode250">>). 483: 484: check_unicode(Config, Value) -> 485: check_unicode_generic(Config, Value, <<"unicode">>). 486: 487: check_unicode_generic(Config, Value, Column) -> 488: SValue = escape_string_or_null(Config, Value), 489: EraseResult = erase_table(Config), 490: InsertQuery = ["INSERT INTO test_types (", Column, ") " 491: "VALUES (", use_escaped(Config, SValue), ")"], 492: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>, 493: InsertResult = sql_query(Config, InsertQuery), 494: SelectResult = sql_query(Config, SelectQuery), 495: %% Compare as binaries 496: ?assert_equal_extra({selected, [{Value}]}, 497: SelectResult, 498: #{column => Column, 499: erase_result => EraseResult, 500: expected_length => byte_size_or_null(Value), 501: selected_length => maybe_selected_length(Config, SelectResult), 502: compare_selected => compare_selected(Config, SelectResult, Value), 503: test_value => Value, 504: insert_query => InsertQuery, 505: insert_query_binary => iolist_to_binary(InsertQuery), 506: select_query => SelectQuery, 507: select_result => SelectResult, 508: insert_result => InsertResult}). 509: 510: check_ascii_char(Config, Value) -> 511: SValue = escape_string_or_null(Config, Value), 512: EraseResult = erase_table(Config), 513: InsertQuery = ["INSERT INTO test_types (ascii_char) " 514: "VALUES (", use_escaped(Config, SValue), ")"], 515: SelectQuery = <<"SELECT ascii_char FROM test_types">>, 516: InsertResult = sql_query(Config, InsertQuery), 517: SelectResult = sql_query(Config, SelectQuery), 518: %% Compare as binaries 519: ?assert_equal_extra({selected, [{Value}]}, 520: SelectResult, 521: #{erase_result => EraseResult, 522: test_value => Value, 523: insert_query => InsertQuery, 524: insert_query_binary => iolist_to_binary(InsertQuery), 525: select_query => SelectQuery, 526: select_result => SelectResult, 527: insert_result => InsertResult}). 528: 529: check_ascii_string(Config, Value) -> 530: SValue = escape_string_or_null(Config, Value), 531: EraseResult = erase_table(Config), 532: InsertQuery = ["INSERT INTO test_types (ascii_string) " 533: "VALUES (", use_escaped(Config, SValue), ")"], 534: SelectQuery = <<"SELECT ascii_string FROM test_types">>, 535: InsertResult = sql_query(Config, InsertQuery), 536: SelectResult = sql_query(Config, SelectQuery), 537: %% Compare as binaries 538: ?assert_equal_extra({selected, [{Value}]}, 539: SelectResult, 540: #{erase_result => EraseResult, 541: test_value => Value, 542: insert_query => InsertQuery, 543: insert_query_binary => iolist_to_binary(InsertQuery), 544: select_query => SelectQuery, 545: select_result => SelectResult, 546: insert_result => InsertResult}). 547: 548: check_binary_8k(Config, Value) -> 549: check_binary(Config, Value, <<"binary_data_8k">>). 550: 551: check_binary_65k(Config, Value) -> 552: check_binary(Config, Value, <<"binary_data_65k">>). 553: 554: check_binary_16m(Config, Value) -> 555: check_binary(Config, Value, <<"binary_data_16m">>). 556: 557: check_binary(Config, Value, Column) -> 558: SValue = escape_binary_or_null(Config, Value), 559: EraseResult = erase_table(Config), 560: InsertQuery = ["INSERT INTO test_types (", Column, ") " 561: "VALUES (", use_escaped(Config, SValue), ")"], 562: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>, 563: InsertResult = sql_query(Config, InsertQuery), 564: SelectResult = sql_query(Config, SelectQuery), 565: %% Compare as binaries 566: ?assert_equal_extra({selected, [{Value}]}, 567: selected_unescape(Config, SelectResult), 568: #{erase_result => EraseResult, 569: inserted_length => byte_size_or_null(Value), 570: %% pgsql+odbc can truncate binaries 571: maybe_selected_length => maybe_selected_length(Config, SelectResult), 572: maybe_selected_tail => maybe_selected_tail(Config, SelectResult), 573: compare_selected => compare_selected(Config, selected_unescape(Config, SelectResult), Value), 574: test_value => Value, 575: insert_query_binary => iolist_to_binary(InsertQuery), 576: select_query => SelectQuery, 577: select_result => SelectResult, 578: insert_result => InsertResult}). 579: 580: byte_size_or_null(null) -> 581: null; 582: byte_size_or_null(Value) -> 583: byte_size(Value). 584: 585: check_enum_char(Config, Value) when is_binary(Value) -> 586: SValue = escape_string_or_null(Config, Value), 587: EraseResult = erase_table(Config), 588: InsertQuery = ["INSERT INTO test_types (enum_char) " 589: "VALUES (", use_escaped(Config, SValue), ")"], 590: SelectQuery = <<"SELECT enum_char FROM test_types">>, 591: InsertResult = sql_query(Config, InsertQuery), 592: SelectResult = sql_query(Config, SelectQuery), 593: %% Compare as binaries 594: ?assert_equal_extra({selected, [{Value}]}, 595: SelectResult, 596: #{erase_result => EraseResult, 597: test_value => Value, 598: insert_query => InsertQuery, 599: insert_query_binary => iolist_to_binary(InsertQuery), 600: select_query => SelectQuery, 601: select_result => SelectResult, 602: insert_result => InsertResult}). 603: 604: check_boolean(Config, Value) when is_boolean(Value) -> 605: SValue = escape_boolean(Config, Value), 606: EraseResult = erase_table(Config), 607: InsertQuery = ["INSERT INTO test_types (bool_flag) " 608: "VALUES (", use_escaped(Config, SValue), ")"], 609: SelectQuery = <<"SELECT bool_flag FROM test_types">>, 610: InsertResult = sql_query(Config, InsertQuery), 611: SelectResult = sql_query(Config, SelectQuery), 612: %% Compare as binaries 613: ?assert_equal_extra({selected, [{Value}]}, 614: selected_decode_boolean(Config, SelectResult), 615: #{erase_result => EraseResult, 616: test_value => Value, 617: insert_query => InsertQuery, 618: insert_query_binary => iolist_to_binary(InsertQuery), 619: select_query => SelectQuery, 620: select_result => SelectResult, 621: insert_result => InsertResult}). 622: 623: selected_unescape(_Config, {selected, [{null}]}) -> 624: {selected, [{null}]}; 625: selected_unescape(Config, {selected, [{Value}]}) -> 626: {selected, [{unescape_binary(Config, Value)}]}; 627: selected_unescape(_Config, Other) -> 628: Other. 629: 630: selected_decode_boolean(Config, {selected, [{Value}]}) -> 631: {selected, [{decode_boolean(Config, Value)}]}; 632: selected_decode_boolean(_Config, Other) -> 633: Other. 634: 635: selected_boolean_to_binary_int(Config, {selected, [{Value}]}) -> 636: {selected, [{boolean_to_binary_int(decode_boolean(Config, Value))}]}; 637: selected_boolean_to_binary_int(_Config, Other) -> 638: Other. 639: 640: boolean_to_binary_int(true) -> <<"1">>; 641: boolean_to_binary_int(false) -> <<"0">>. 642: 643: maybe_selected_length(_Config, {selected, [{Value}]}) when is_binary(Value) -> 644: byte_size(Value); 645: maybe_selected_length(_Config, _Other) -> 646: unknown. 647: 648: maybe_selected_tail(Config, Selected) -> 649: maybe_selected_tail(Config, Selected, 100). 650: 651: maybe_selected_tail(_Config, {selected, [{Value}]}, TailLen) 652: when is_binary(Value), byte_size(Value) > TailLen -> 653: binary:part(Value, {byte_size(Value), -TailLen}); 654: maybe_selected_tail(_Config, {selected, [{Value}]}, _TailLen) -> 655: Value; 656: maybe_selected_tail(_Config, _Other, _TailLen) -> 657: unknown. 658: 659: check_prep_int32(Config, Value) -> 660: check_generic_prep_integer(Config, Value, <<"int32">>). 661: 662: check_prep_int64(Config, Value) -> 663: check_generic_prep_integer(Config, Value, <<"int64">>). 664: 665: check_prep_unicode(Config, Value) -> 666: check_generic_prep(Config, Value, <<"unicode">>). 667: 668: check_prep_unicode250(Config, Value) -> 669: check_generic_prep(Config, Value, <<"unicode250">>). 670: 671: %% Char is ascii string of length one 672: check_prep_ascii_char(Config, Value) -> 673: check_generic_prep(Config, Value, <<"unicode">>). 674: 675: %% Varchar 676: check_prep_ascii_string(Config, Value) -> 677: check_generic_prep(Config, Value, <<"ascii_string">>). 678: 679: check_prep_binary_65k(Config, Value) -> 680: %% MSSQL returns binaries in HEX encoding 681: check_generic_prep(Config, Value, <<"binary_data_65k">>, unescape_binary). 682: 683: check_prep_binary_8k(Config, Value) -> 684: %% MSSQL returns binaries in HEX encoding 685: check_generic_prep(Config, Value, <<"binary_data_8k">>, unescape_binary). 686: 687: check_prep_binary_16m(Config, Value) -> 688: %% MSSQL returns binaries in HEX encoding 689: check_generic_prep(Config, Value, <<"binary_data_16m">>, unescape_binary). 690: 691: check_generic_prep_integer(Config, Value, Column) -> 692: check_generic_prep(Config, Value, Column). 693: 694: check_prep_enum_char(Config, Value) -> 695: check_generic_prep(Config, Value, <<"enum_char">>). 696: 697: check_prep_boolean(Config, Value) -> 698: check_generic_prep(Config, Value, <<"bool_flag">>, boolean_to_binary_int). 699: 700: %% Data types 701: %% {ok, Conn} = odbc:connect("DSN=mongoose-mssql;UID=sa;PWD=mongooseim_secret+ESL123", []). 702: %% odbc:describe_table(Conn, "test_types"). 703: %% [{"unicode",{sql_wvarchar,536870911}}, 704: %% {"binary_data_65k",'SQL_VARBINARY'}, 705: %% {"ascii_char",{sql_char,1}}, 706: %% {"ascii_string",{sql_varchar,250}}, 707: %% {"int32",sql_integer}, 708: %% {"int64",'SQL_BIGINT'}, 709: %% {"int8",sql_tinyint}] 710: 711: check_generic_prep(Config, Value, Column) -> 712: check_generic_prep(Config, Value, Column, to_binary). 713: 714: check_generic_prep(Config, Value, Column, TransformResult) -> 715: EraseResult = erase_table(Config), 716: 717: InsertQuery = <<"INSERT INTO test_types (", Column/binary, ") " 718: "VALUES (?)">>, 719: SelectQuery = <<"SELECT ", Column/binary, " FROM test_types">>, 720: Name = list_to_atom("insert_" ++ binary_to_list(Column)), 721: Table = test_types, 722: Fields = [binary_to_atom(Column, utf8)], 723: PrepareResult = sql_prepare(Config, Name, Table, Fields, InsertQuery), 724: Parameters = [Value], 725: InsertResult = sql_execute(Config, Name, Parameters), 726: SelectResult = sql_query(Config, SelectQuery), 727: %% Compare as binaries 728: ?assert_equal_extra({selected, [{value_to_binary(Value)}]}, 729: transform_selected(TransformResult, Config, SelectResult), 730: #{column => Column, 731: erase_result => EraseResult, 732: test_value => Value, 733: insert_query => InsertQuery, 734: prepare_result => PrepareResult, 735: select_query => SelectQuery, 736: select_result => SelectResult, 737: insert_result => InsertResult}), 738: check_generic_filtered_prep(Config, Value, Column, TransformResult), 739: case is_odbc() of 740: true -> 741: %% TOP is mssql feature, all other databases use LIMIT. 742: check_generic_filtered_top_prep(Config, Value, Column, TransformResult); 743: false -> 744: ok 745: end. 746: 747: %% We want to ensure that variable substitution works in SELECTS too. 748: %% We also want to check the result value is encoded correctly. 749: check_generic_filtered_prep(_Config, null, _Column, _TransformResult) -> 750: skip_null_test; 751: check_generic_filtered_prep(Config, Value, Column, TransformResult) -> 752: SelectQuery = <<"SELECT ", Column/binary, 753: " FROM test_types WHERE ", Column/binary, " = ?">>, 754: Name = list_to_atom("select_filtered_" ++ binary_to_list(Column)), 755: Table = test_types, 756: Fields = [binary_to_atom(Column, utf8)], 757: PrepareResult = sql_prepare(Config, Name, Table, Fields, SelectQuery), 758: Parameters = [Value], 759: SelectResult = sql_execute(Config, Name, Parameters), 760: %% Compare as binaries 761: ?assert_equal_extra({selected, [{value_to_binary(Value)}]}, 762: transform_selected(TransformResult, Config, SelectResult), 763: #{column => Column, 764: test_value => Value, 765: prepare_result => PrepareResult, 766: select_query => SelectQuery, 767: select_result => SelectResult}). 768: 769: check_generic_filtered_top_prep(_Config, null, _Column, _TransformResult) -> 770: skip_null_test; 771: check_generic_filtered_top_prep(Config, Value, Column, TransformResult) -> 772: %% SQL Server requires you to place parenthesis around the argument to top if you pass in a variable: 773: %% https://stackoverflow.com/questions/7038818/ms-sql-exception-incorrect-syntax-near-p0 774: SelectQuery = <<"SELECT TOP (?) ", Column/binary, 775: " FROM test_types WHERE ", Column/binary, " = ?">>, 776: Name = list_to_atom("select_filtered_top_" ++ binary_to_list(Column)), 777: Table = test_types, 778: Fields = [limit, binary_to_atom(Column, utf8)], 779: PrepareResult = sql_prepare(Config, Name, Table, Fields, SelectQuery), 780: Parameters = [30, Value], 781: SelectResult = sql_execute(Config, Name, Parameters), 782: %% Compare as binaries 783: ?assert_equal_extra({selected, [{value_to_binary(Value)}]}, 784: transform_selected(TransformResult, Config, SelectResult), 785: #{column => Column, 786: test_value => Value, 787: prepare_result => PrepareResult, 788: select_query => SelectQuery, 789: select_result => SelectResult}). 790: 791: 792: 793: transform_selected(to_binary, _Config, SelectResult) -> 794: selected_to_binary(SelectResult); 795: transform_selected(unescape_binary, Config, SelectResult) -> 796: selected_unescape(Config, SelectResult); 797: transform_selected(boolean_to_binary_int, Config, SelectResult) -> 798: selected_boolean_to_binary_int(Config, SelectResult). 799: 800: %% To KISS, we just test on a table with one row. 801: check_like(Config, TextMap = #{text := TextValue, 802: matching := MatchingList, 803: not_matching := NotMatchingList}) -> 804: SValue = escape_string_or_null(Config, TextValue), 805: EraseResult = erase_table(Config), 806: InsertQuery = ["INSERT INTO test_types (unicode) " 807: "VALUES (", use_escaped(Config, SValue), ")"], 808: InsertResult = sql_query(Config, InsertQuery), 809: Info = #{erase_result => EraseResult, 810: insert_query => InsertQuery, 811: insert_query_binary => iolist_to_binary(InsertQuery), 812: insert_result => InsertResult, 813: text_map => TextMap}, 814: [check_like_matching(Config, TextValue, Matching, Info) 815: || Matching <- MatchingList], 816: [check_like_not_matching(Config, TextValue, NotMatching, Info) 817: || NotMatching <- NotMatchingList]. 818: 819: check_like_matching(Config, TextValue, Matching, Info) -> 820: SLike = escape_like(Config, Matching), 821: SelectQuery = ["SELECT unicode FROM test_types " 822: "WHERE unicode LIKE ", use_escaped_like(Config, SLike)], 823: SelectResult = sql_query(Config, SelectQuery), 824: %% Compare as binaries 825: ?assert_equal_extra({selected, [{TextValue}]}, 826: SelectResult, 827: Info#{pattern => Matching, 828: select_query => SelectQuery, 829: select_result => SelectResult}). 830: 831: check_like_not_matching(Config, _TextValue, NotMatching, Info) -> 832: SLike = escape_like(Config, NotMatching), 833: SelectQuery = ["SELECT unicode FROM test_types " 834: "WHERE unicode LIKE ", use_escaped_like(Config, SLike)], 835: SelectResult = sql_query(Config, SelectQuery), 836: %% Compare as binaries 837: ?assert_equal_extra({selected, []}, 838: SelectResult, 839: Info#{pattern => NotMatching, 840: select_query => SelectQuery, 841: select_result => SelectResult}). 842: 843: compare_selected(_Config, {selected, [{SelValue}]}, Value) -> 844: drop_common_prefix(0, SelValue, Value); 845: compare_selected(_Config, _, _Value) -> 846: nomatch. 847: 848: drop_common_prefix(Pos, <<X, SelValue/binary>>, <<X, Value/binary>>) -> 849: drop_common_prefix(Pos+1, SelValue, Value); 850: drop_common_prefix(Pos, SelValue, Value) -> 851: #{pos => Pos, 852: selected_suffix => safe_binary(100, SelValue), 853: expected_suffix => safe_binary(100, Value)}. 854: 855: is_odbc() -> 856: escalus_ejabberd:rpc(mongoose_rdbms, db_engine, [host_type()]) == odbc. 857: 858: is_pgsql() -> 859: escalus_ejabberd:rpc(mongoose_rdbms, db_engine, [host_type()]) == pgsql. 860: 861: slow_rpc(M, F, A) -> 862: Node = ct:get_config({hosts, mim, node}), 863: Cookie = escalus_ct:get_config(ejabberd_cookie), 864: Res = escalus_rpc:call(Node, M, F, A, timer:seconds(30), Cookie), 865: case Res of 866: {badrpc, timeout} -> 867: {badrpc, {timeout, M, F}}; 868: _ -> 869: Res 870: end. 871: 872: check_like_prep(Config, TextMap = #{text := TextValue, 873: matching := MatchingList, 874: not_matching := NotMatchingList}) -> 875: EraseResult = erase_table(Config), 876: Name = insert_unicode_prep, 877: SelName = select_unicode_prep, 878: Table = test_types, 879: Fields = [<<"unicode">>], 880: InsertQuery = <<"INSERT INTO test_types (unicode) VALUES (?)">>, 881: SelectQuery = <<"SELECT unicode FROM test_types WHERE unicode LIKE ? ESCAPE '$'">>, 882: PrepareResult = sql_prepare(Config, Name, Table, Fields, InsertQuery), 883: PrepareSelResult = sql_prepare(Config, SelName, Table, Fields, SelectQuery), 884: Parameters = [TextValue], 885: InsertResult = sql_execute(Config, Name, Parameters), 886: Info = #{erase_result => EraseResult, 887: insert_query => InsertQuery, 888: prepare_result => PrepareResult, 889: insert_result => InsertResult, 890: prepare_select_result => PrepareSelResult, 891: text_map => TextMap}, 892: [check_like_matching_prep(SelName, Config, TextValue, Matching, Info) 893: || Matching <- MatchingList], 894: [check_like_not_matching_prep(SelName, Config, TextValue, NotMatching, Info) 895: || NotMatching <- NotMatchingList]. 896: 897: check_like_matching_prep(SelName, Config, TextValue, Matching, Info) -> 898: SMatching = escape_prepared_like(Config, Matching), 899: Parameters = [<<"%", SMatching/binary, "%">>], 900: SelectResult = sql_execute(Config, SelName, Parameters), 901: %% Compare as binaries 902: ?assert_equal_extra({selected, [{TextValue}]}, 903: SelectResult, 904: Info#{pattern => Matching, 905: select_result => SelectResult}). 906: 907: check_like_not_matching_prep(SelName, Config, _TextValue, NotMatching, Info) -> 908: SNotMatching = escape_prepared_like(Config, NotMatching), 909: Parameters = [<<"%", SNotMatching/binary, "%">>], 910: SelectResult = sql_execute(Config, SelName, Parameters), 911: %% Compare as binaries 912: ?assert_equal_extra({selected, []}, 913: SelectResult, 914: Info#{pattern => NotMatching, 915: select_result => SelectResult}).