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