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