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