path: root/tests/sql_db.ml

(** Test the behavior of the sqlite with a in-memory database *)

open StdLabels
open Test_migration

let result = Alcotest.(result Test_migration.csv_result reject)
let check = Alcotest.check result

let ( let* ) res cont =
  match res with
  | Ok value -> cont value
  | Error e -> Error e

(** Test a process with a simple configuration in-memory. Only one table is
    handlded *)
let run_test ~configuration ~input ~expected name =
  name >:: fun () ->
  (* We expect a valid configuration *)
  let conf = ConfLoader.load configuration in

  let exec db =
    let table = List.hd @@ ImportAnalyser.Dependency.get_process_order conf in
    let* () = ImportSQL.Db.create_table db table in

    (* Prepare the statement in order to import data *)
    let* stmt = ImportSQL.Db.prepare_insert db table in

    (* Inject some data into the table *)
    let result, _ =
      List.fold_left ~init:(Ok (), 0) input ~f:(fun (_, i) data ->
          let result =
            let* () = ImportSQL.Db.insert ~id:i db stmt data in
            let* () = ImportSQL.Db.reset stmt in
            Ok ()
          in
          (result, i + 1))
    in
    let* () = result in
    let* () = ImportSQL.Db.finalize stmt in

    (* Collect the data *)
    let data = ref [] in
    let* () =
      ImportSQL.Db.query db conf ~f:(fun rows ->
          let values = Array.map ~f:snd rows in
          data := values :: !data)
    in

    Ok (List.rev !data)
  in

  (* Use a magic keyword for in-memory database *)
  let result = ImportSQL.Db.with_db ":memory:" exec in
  check name expected result

(** Simple test used to check the process *)
let simple_extraction =
  run_test "simple_extraction"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A ^ '_'", 
    ":B",
    ":E"]|}
    ~input:
      ImportCSV.DataType.
        [ [ (0, Integer 123); (1, Integer 2); (4, Integer 5) ] ]
    ~expected:
      (Ok ImportCSV.DataType.[ [| Content "123_"; Integer 2; Integer 5 |] ])

(** Ensure the behavior of the sum function when a filter is given. It is
    expected to accumulate the values over each line *)
let sum_sort =
  run_test "sum_sort"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
    "sum(:C, [:B], [:A])",
]|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (1, Content "A"); (2, Integer 100) ];
          [ (0, Integer 2); (1, Content "A"); (2, Integer 100) ];
          [ (0, Integer 3); (1, Content "A"); (2, Integer 100) ];
        ]
    ~expected:
      (Ok
         ImportCSV.DataType.
           [
             [| Integer 1; Integer 100 |];
             [| Integer 2; Integer 200 |];
             [| Integer 3; Integer 300 |];
           ])

let sum_total =
  run_test "sum_total"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
    "sum(:C, [], [])",
]|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (2, Integer 100) ];
          [ (0, Integer 2); (2, Integer 100) ];
        ]
    ~expected:
      (Ok [ [| ImportCSV.DataType.Integer 1; ImportCSV.DataType.Integer 200 |] ])

(** Ensure the behavior of the sum function when no filter is given. It is
    expected to get the total sum for each line *)
let sum_unfiltered =
  run_test "sum_unfiltered"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
    "sum(:C, [], [:A])",
]|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (2, Integer 100) ];
          [ (0, Integer 2); (2, Integer 100) ];
        ]
    ~expected:
      (Ok [ [| ImportCSV.DataType.Integer 1; ImportCSV.DataType.Integer 200 |] ])

let sum_group =
  run_test "sum_group"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
    "sum(:C, [:A], [])",
]|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (2, Integer 100) ];
          [ (0, Integer 1); (2, Integer 100) ];
          [ (0, Integer 2); (2, Integer 100) ];
        ]
    ~expected:
      (Ok
         ImportCSV.DataType.
           [
             [| Integer 1; Integer 200 |];
             [| Integer 1; Integer 200 |];
             [| Integer 2; Integer 100 |];
           ])

(** Adding a uniq filter on column A does not change the sum of the values *)
let sum_group_uniq =
  run_test "sum_group_uniq"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
uniq = [":A"]
columns = [
    ":A", 
    "sum(:C, [:A], [])",
]|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (2, Integer 100) ];
          [ (0, Integer 1); (2, Integer 100) ];
          [ (0, Integer 2); (2, Integer 100) ];
        ]
    ~expected:
      (Ok
         ImportCSV.DataType.
           [ [| Integer 1; Integer 200 |]; [| Integer 2; Integer 100 |] ])

let filter_group =
  run_test "filter_group"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
]

filters = [
    "max(:B, [:C], [:B]) = :B",
]

|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (1, Integer 100); (2, Integer 0) ];
          [ (0, Integer 2); (1, Integer 150); (2, Integer 0) ];
          [ (0, Integer 3); (1, Integer 200); (2, Integer 0) ];
        ]
    ~expected:(Ok ImportCSV.DataType.[ [| Integer 3 |] ])

(** The first filter will prevent the max value to pop, and only the second one
    will be reported *)
let filter_expression_and_group =
  run_test "filter expression then group"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
]

filters = [
    ":B <> 200",
    "max(:B, [:C], [:B]) = :B",
]

|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (1, Integer 100); (2, Integer 0) ];
          [ (0, Integer 2); (1, Integer 150); (2, Integer 0) ];
          [ (0, Integer 3); (1, Integer 200); (2, Integer 0) ];
        ]
    ~expected:(Ok ImportCSV.DataType.[ [| Integer 2 |] ])

(** In this case, we first filter the line and keep only the max value, but the
    second filter will match the result and will produce an empty list *)
let filter_group_and_expression =
  run_test "filter group then expression"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
]

filters = [
    "max(:B, [:C], [:B]) = :B",
    ":B <> 200",
]

|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (1, Integer 100); (2, Integer 0) ];
          [ (0, Integer 2); (1, Integer 150); (2, Integer 0) ];
          [ (0, Integer 3); (1, Integer 200); (2, Integer 0) ];
        ]
    ~expected:(Ok [])

(** In this case, each filter remove the line with the higher value in B.

    After the application of the two filters, the only remaining result is the
    first line. *)
let filter_group_and_group =
  run_test "filter group then group"
    ~configuration:
      {|[source]
name = "source_name"
file = "source_file"

[sheet]
columns = [
    ":A", 
]

filters = [
    "max(:B, [:C], [:B]) <> :B",
    "max(:B, [:C], [:B]) <> :B",
]

|}
    ~input:
      ImportCSV.DataType.
        [
          [ (0, Integer 1); (1, Integer 100); (2, Integer 0) ];
          [ (0, Integer 2); (1, Integer 150); (2, Integer 0) ];
          [ (0, Integer 3); (1, Integer 200); (2, Integer 0) ];
        ]
    ~expected:(Ok [ [| ImportCSV.DataType.Integer 1 |] ])

let test_suit =
  [
    simple_extraction;
    sum_sort;
    sum_total;
    sum_unfiltered;
    sum_group;
    sum_group_uniq;
    filter_group;
    filter_expression_and_group;
    filter_group_and_expression;
    filter_group_and_group;
  ]

let tests = "sql_db" >::: test_suit