gazagnaire.org
SQL frontend over the catalog engine
1(*---------------------------------------------------------------------------
2 Copyright (c) 2025 Thomas Gazagnaire. All rights reserved.
3 SPDX-License-Identifier: ISC
4 ---------------------------------------------------------------------------*)
5
6(** Tests for {!Sql.Datetime}: SQLite's deterministic date/time functions
7 (lang_datefunc.html). Vectors are the values sqlite3 returns. *)
8
9module D = Sql.Datetime
10
11let text s = Sql.Ast.Text s
12
13(* Lossless rendering of a result value for exact assertions. *)
14let vstr : Sql.Ast.value -> string = function
15 | Null -> "NULL"
16 | Int i -> Fmt.str "int:%Ld" i
17 | Float f -> Fmt.str "float:%.6f" f
18 | Text s -> Fmt.str "text:%s" s
19 | Blob b -> Fmt.str "blob:%s" b
20
21let check label expected fn args =
22 Alcotest.(check string) label expected (vstr (D.call fn args))
23
24let test_explicit_forms () =
25 check "date passes a valid date through" "text:2024-02-29" "date"
26 [ text "2024-02-29" ];
27 check "datetime keeps the time" "text:2024-01-01 12:30:00" "datetime"
28 [ text "2024-01-01 12:30:00" ];
29 check "time drops the date" "text:12:30:00" "time"
30 [ text "2024-01-01 12:30:00" ]
31
32let test_julian_and_unix () =
33 (* 2000-01-01 00:00:00 UTC is Julian Day 2451544.5. *)
34 check "julianday at the J2000 midnight" "float:2451544.500000" "julianday"
35 [ text "2000-01-01" ];
36 (* 2024-01-01 00:00:00 UTC is unix epoch 1704067200. *)
37 check "unixepoch at 2024-01-01" "int:1704067200" "unixepoch"
38 [ text "2024-01-01" ]
39
40let test_modifiers () =
41 check "add one day, crossing a month" "text:2024-02-01" "date"
42 [ text "2024-01-31"; text "+1 day" ];
43 check "start of month truncates" "text:2024-06-01" "date"
44 [ text "2024-06-15"; text "start of month" ]
45
46let test_strftime () =
47 check "strftime year-month" "text:2024-06" "strftime"
48 [ text "%Y-%m"; text "2024-06-15" ]
49
50let test_null_results () =
51 Alcotest.(check string)
52 "the wall clock is NULL with no clock injected" "NULL"
53 (vstr (D.call "date" [ text "now" ]));
54 Alcotest.(check string)
55 "an unparseable time is NULL" "NULL"
56 (vstr (D.call "date" [ text "not-a-date" ]))
57
58let test_unknown_function () =
59 match D.call "haversine" [] with
60 | _ -> Alcotest.fail "a non-datetime function should raise"
61 | exception Invalid_argument _ -> ()
62
63let suite =
64 ( "datetime",
65 [
66 Alcotest.test_case "explicit date/time/datetime" `Quick
67 test_explicit_forms;
68 Alcotest.test_case "julianday and unixepoch" `Quick test_julian_and_unix;
69 Alcotest.test_case "unit and start-of modifiers" `Quick test_modifiers;
70 Alcotest.test_case "strftime" `Quick test_strftime;
71 Alcotest.test_case "NULL results" `Quick test_null_results;
72 Alcotest.test_case "unknown function raises" `Quick test_unknown_function;
73 ] )