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