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