From 6b377719c10d5ab3343fd5221f99a4a21008e25a Mon Sep 17 00:00:00 2001 From: Sébastien Dailly Date: Thu, 14 Mar 2024 08:26:58 +0100 Subject: Initial commit --- tests/analyser_query_test.ml | 304 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 304 insertions(+) create mode 100644 tests/analyser_query_test.ml (limited to 'tests/analyser_query_test.ml') diff --git a/tests/analyser_query_test.ml b/tests/analyser_query_test.ml new file mode 100644 index 0000000..3559de4 --- /dev/null +++ b/tests/analyser_query_test.ml @@ -0,0 +1,304 @@ +open OUnit2 +open StdLabels +module A = ImportAnalyser.Dependency +module Q = ImportAnalyser.Query +module C = ImportConf +module Syntax = ImportConf.Syntax +module Expr = Expression_builder + +let show_source (source : ImportDataTypes.Table.t) = + Printf.sprintf "%s:%d" source.ImportDataTypes.Table.file source.tab + +let show_sources sources = + let b = Buffer.create 16 in + Buffer.add_string b "["; + List.iter sources ~f:(fun source -> + Buffer.add_string b (show_source source); + Buffer.add_string b ","); + + let len = Buffer.length b in + if len > 1 then Buffer.truncate b (len - 1); + Buffer.add_string b "]"; + + Buffer.contents b + +(** This is sample configuration used in the tests *) +let conf = + Syntax. + { + version = 1; + source = { file = "source.xlsx"; tab = 1; name = "source" }; + externals = + [ + { + intern_key = Path { alias = None; column = 1 }; + target = { file = "other.xlsx"; tab = 1; name = "other" }; + extern_key = Path 3; + allow_missing = false; + match_rule = None; + }; + { + intern_key = Path { alias = Some "other"; column = 1 }; + target = { file = "last.xlsx"; tab = 1; name = "last_file" }; + extern_key = Path 3; + allow_missing = true; + match_rule = None; + }; + ]; + columns = + [ + Concat [ Path { alias = None; column = 1 }; Literal "_"; Empty ]; + Path { alias = None; column = 2 }; + Path { alias = Some "last_file"; column = 5 }; + ]; + filters = []; + sort = []; + uniq = []; + } + +let create_table = + "Create table" >:: fun _ -> + let out = A.get_process_order conf in + + let query = Q.create_table (List.hd out) in + + assert_equal ~printer:Fun.id + "CREATE TABLE 'last' (id INTEGER PRIMARY KEY,'key_last_file','col_5')" query + +let select = + "Select" >:: fun _ -> + let query, _ = Q.select conf in + let expected_query = + {|SELECT COALESCE('source'.col_1,'') || ? || '' AS result_0, +'source'.col_2 AS result_1, +'last_file'.col_5 AS result_2 +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.col_1)) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.col_1)) = 'last_file'.'key_last_file'|} + in + + assert_equal ~printer:Fun.id expected_query query.q + +let check_externals = + "Check external" >:: fun _ -> + let query = Q.check_external conf (List.hd conf.externals) in + + let expected_query = + "SELECT 'source'.'id', 'source'.col_1 FROM\n\ + 'source' AS 'source' LEFT JOIN 'other' AS 'other' ON \ + rtrim(upper('source'.col_1)) = 'other'.'key_other' WHERE \ + 'other'.'key_other' IS NULL AND 'source'.col_1 IS NOT NULL AND \ + 'source'.col_1 <> ''" + in + + assert_equal ~printer:Fun.id expected_query query.q + +let previous = + "Test window previous" >:: fun _ -> + (* This is sample configuration used in the tests *) + let conf = + Syntax. + { + version = 1; + source = { file = "source.xlsx"; tab = 1; name = "previous" }; + externals = []; + columns = + [ + Window + ( Previous (Path { alias = None; column = 5 }), + [ Path { alias = None; column = 1 } ], + [ Path { alias = None; column = 3 } ] ); + ]; + filters = []; + sort = []; + uniq = []; + } + in + + let res, _ = ImportAnalyser.Query.select conf in + let query = + "SELECT LAG('previous'.col_5) OVER (PARTITION BY 'previous'.col_1 ORDER BY \ + 'previous'.col_3) AS result_0\n\ + FROM 'source' AS 'previous'" + in + assert_equal ~printer:Fun.id query res.q + +let sum = + "Test window sum" >:: fun _ -> + (* This is sample configuration used in the tests *) + let conf = + Syntax. + { + version = 1; + source = { file = "source.xlsx"; tab = 1; name = "previous" }; + externals = []; + columns = + [ + Window + ( Sum (Path { alias = None; column = 5 }), + [ Path { alias = None; column = 1 } ], + [] ); + ]; + filters = []; + sort = []; + uniq = []; + } + in + + let res, _ = ImportAnalyser.Query.select conf in + let query = + "SELECT SUM('previous'.col_5) OVER (PARTITION BY 'previous'.col_1) AS \ + result_0\n\ + FROM 'source' AS 'previous'" + in + assert_equal ~printer:Fun.id query res.q + +let sum_total = + "Test sum over the whole range" >:: fun _ -> + (* This is sample configuration used in the tests *) + let conf = + Syntax. + { + version = 1; + source = { file = "source.xlsx"; tab = 1; name = "previous" }; + externals = []; + columns = [ Window (Sum (Path { alias = None; column = 5 }), [], []) ]; + filters = []; + sort = []; + uniq = []; + } + in + + let res, _ = ImportAnalyser.Query.select conf in + let query = + "SELECT SUM('previous'.col_5) AS result_0\nFROM 'source' AS 'previous'" + in + assert_equal ~printer:Fun.id query res.q + +let sum_unfiltered = + "Test sum over the whole range" >:: fun _ -> + (* This is sample configuration used in the tests *) + let conf = + Syntax. + { + version = 1; + source = { file = "source.xlsx"; tab = 1; name = "previous" }; + externals = []; + columns = + [ + Window + ( Sum (Path { alias = None; column = 5 }), + [], + [ Path { alias = None; column = 1 } ] ); + ]; + filters = []; + sort = []; + uniq = []; + } + in + + let res, _ = ImportAnalyser.Query.select conf in + let query = + "SELECT SUM('previous'.col_5) AS result_0\nFROM 'source' AS 'previous'" + in + assert_equal ~printer:Fun.id query res.q + +let prepare_insert = + "Test prepare_insert" >:: fun _ -> + let key = + ImportAnalyser.Dependency. + { + name = "key_test"; + expression = Concat [ Path 1; Literal "_"; Empty ]; + columns = lazy (ImportContainers.IntSet.singleton 1); + } + in + + let buffer = Buffer.create 16 in + let () = ImportAnalyser.Query.build_key_insert buffer key in + let contents = Buffer.contents buffer in + + let expected = "rtrim(upper(COALESCE(:col_1,'') || '_' || ''))" in + + assert_equal ~printer:Fun.id expected contents + +(** Test a request with a group in a filter. + +This generate a CTE expression in order to evaluate the group before loading +the results from the query. *) +let filter_group = + "Test filter_group" >:: fun _ -> + let c col = Expr.path ImportDataTypes.Path.{ alias = None; column = col } in + let conf = + { + conf with + columns = [ c 1 ]; + filters = [ Expr.(max (c 3) [ c 1 ] [ c 1 ]) ]; + } + in + let contents, _ = ImportAnalyser.Query.select conf in + + let expected = + {|WITH cte AS (SELECT source.id, LAST_VALUE('source'.col_3) OVER (PARTITION BY 'source'.col_1 ORDER BY 'source'.col_1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS group0 +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.col_1)) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.col_1)) = 'last_file'.'key_last_file') +SELECT 'source'.col_1 AS result_0 +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.col_1)) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.col_1)) = 'last_file'.'key_last_file' +INNER JOIN 'cte' ON cte.id = source.id +WHERE (cte.group0)|} + in + + assert_equal ~printer:(fun s -> Printf.sprintf "\n%s" s) expected contents.q + +(** Test a request with a group in a filter. + +This generate a CTE expression in order to evaluate the group before loading +the results from the query. *) +let filter_group2 = + "Test filter_group" >:: fun _ -> + let c col = Expr.path ImportDataTypes.Path.{ alias = None; column = col } in + let conf = + { + conf with + columns = [ c 1 ]; + filters = + [ Expr.(max (c 3) [ c 1 ] [ c 1 ]); Expr.equal (c 3) Expr.integer_zero ]; + } + in + let contents, _ = ImportAnalyser.Query.select conf in + + let expected = + {|WITH cte AS (SELECT source.id, LAST_VALUE('source'.col_3) OVER (PARTITION BY 'source'.col_1 ORDER BY 'source'.col_1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS group0 +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.col_1)) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.col_1)) = 'last_file'.'key_last_file' +WHERE COALESCE('source'.col_3,0)=0) +SELECT 'source'.col_1 AS result_0 +FROM 'source' AS 'source' +LEFT JOIN 'other' AS 'other' ON rtrim(upper('source'.col_1)) = 'other'.'key_other' +LEFT JOIN 'last' AS 'last_file' ON rtrim(upper('other'.col_1)) = 'last_file'.'key_last_file' +INNER JOIN 'cte' ON cte.id = source.id +WHERE COALESCE('source'.col_3,0)=0 +AND (cte.group0)|} + in + + assert_equal ~printer:(fun s -> Printf.sprintf "\n%s" s) expected contents.q + +let test_suit = + [ + create_table; + select; + check_externals; + previous; + sum; + sum_total; + sum_unfiltered; + prepare_insert; + filter_group; + filter_group2; + ] + +let tests = "analyser_query_test" >::: test_suit -- cgit v1.2.3