aboutsummaryrefslogtreecommitdiff
path: root/tests/analyser_query_test.ml
diff options
context:
space:
mode:
Diffstat (limited to 'tests/analyser_query_test.ml')
-rw-r--r--tests/analyser_query_test.ml304
1 files changed, 304 insertions, 0 deletions
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
generated by cgit v1.2.3 (git 2.39.1) at 2024-07-07 13:42:12 +0000