path: root/tests/analyser_query_test.ml

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