gazagnaire.org
SQL frontend over the catalog engine
1(* The statement engine runs SQL end to end over a Catalog.Spi backend with no
2 sqlite involved: CREATE TABLE, INSERT, and SELECT against the in-memory
3 reference store. This is the proof that execution lives in [sql] over the
4 storage contract, not in the backend. *)
5
6module E = Sql.Engine
7module Spi = Catalog.Spi
8
9let rows = function E.Rows r -> r | E.Done -> []
10
11(* a result row of an int and a text column, for exact comparison *)
12let pair = function
13 | [ Catalog.Value.Int a; Catalog.Value.Text b ] -> (Int64.to_int a, b)
14 | _ -> (-1, "?")
15
16let test_create_insert_select () =
17 let cat = Catalog.of_mem [] in
18 ignore (E.exec cat "CREATE TABLE t (a INTEGER, b TEXT)");
19 Alcotest.(check (list string))
20 "CREATE TABLE registered the columns" [ "a"; "b" ] (Spi.columns cat "t");
21 Alcotest.(check bool)
22 "and listed the table" true
23 (List.mem "t" (Spi.tables cat));
24 ignore (E.exec cat "INSERT INTO t VALUES (1, 'x'), (3, 'z'), (2, 'y')");
25 Alcotest.(check int)
26 "INSERT appended every row" 3
27 (List.length (Spi.rows cat "t"));
28 let got =
29 List.map pair
30 (rows (E.exec cat "SELECT a, b FROM t WHERE a >= 2 ORDER BY a"))
31 in
32 Alcotest.(check (list (pair int string)))
33 "SELECT projects, filters and orders on the engine"
34 [ (2, "y"); (3, "z") ]
35 got
36
37(* UPDATE and DELETE run on the engine over Spi: the WHERE filter and the SET
38 expressions are evaluated on the register VM per row, written through
39 Spi.update / Spi.delete. *)
40let test_update_delete () =
41 let cat = Catalog.of_mem [] in
42 ignore (E.exec cat "CREATE TABLE t (a INTEGER, b INTEGER)");
43 ignore (E.exec cat "INSERT INTO t VALUES (1, 10), (2, 20), (3, 30), (4, 40)");
44 ignore (E.exec cat "UPDATE t SET b = b + 1 WHERE a >= 3");
45 let int_pair = function
46 | [ Catalog.Value.Int a; Catalog.Value.Int b ] ->
47 (Int64.to_int a, Int64.to_int b)
48 | _ -> (-1, -1)
49 in
50 let after_update =
51 List.map int_pair (rows (E.exec cat "SELECT a, b FROM t ORDER BY a"))
52 in
53 Alcotest.(check (list (pair int int)))
54 "SET b = b + 1 applied only to a >= 3"
55 [ (1, 10); (2, 20); (3, 31); (4, 41) ]
56 after_update;
57 ignore (E.exec cat "DELETE FROM t WHERE b > 25");
58 let after_delete =
59 List.map int_pair (rows (E.exec cat "SELECT a, b FROM t ORDER BY a"))
60 in
61 Alcotest.(check (list (pair int int)))
62 "DELETE removed the b > 25 rows"
63 [ (1, 10); (2, 20) ]
64 after_delete
65
66let test_drop_table () =
67 let cat = Catalog.of_mem [] in
68 ignore (E.exec cat "CREATE TABLE g (x INTEGER)");
69 Alcotest.(check bool) "created" true (List.mem "g" (Spi.tables cat));
70 ignore (E.exec cat "DROP TABLE g");
71 Alcotest.(check bool) "dropped" false (List.mem "g" (Spi.tables cat))
72
73(* A nested-loop join over two Spi base tables, with the [ON] folded into the
74 scan filter and the projection resolving columns across both sources. *)
75let test_join () =
76 let cat = Catalog.of_mem [] in
77 ignore (E.exec cat "CREATE TABLE a (k INTEGER, x TEXT)");
78 ignore (E.exec cat "CREATE TABLE b (k INTEGER, y TEXT)");
79 ignore (E.exec cat "INSERT INTO a VALUES (1, 'ax'), (2, 'a2')");
80 ignore (E.exec cat "INSERT INTO b VALUES (1, 'by'), (2, 'b2')");
81 let got =
82 List.map
83 (function
84 | [ Catalog.Value.Text x; Catalog.Value.Text y ] -> (x, y)
85 | _ -> ("?", "?"))
86 (rows
87 (E.exec cat "SELECT a.x, b.y FROM a JOIN b ON a.k = b.k ORDER BY a.k"))
88 in
89 Alcotest.(check (list (pair string string)))
90 "a.x, b.y on the matched key"
91 [ ("ax", "by"); ("a2", "b2") ]
92 got
93
94(* Aggregates, compounds, and WITH all run on the engine now -- the complete
95 read dialect over Spi. *)
96let test_aggregate_compound_cte () =
97 let cat = Catalog.of_mem [] in
98 ignore (E.exec cat "CREATE TABLE t (k TEXT, v INTEGER)");
99 ignore
100 (E.exec cat "INSERT INTO t VALUES ('a', 1), ('a', 2), ('b', 10), ('b', 20)");
101 Alcotest.(check (list (pair string int)))
102 "GROUP BY with HAVING and ORDER BY"
103 [ ("b", 30) ]
104 (List.map
105 (function
106 | [ Catalog.Value.Text k; Catalog.Value.Int s ] -> (k, Int64.to_int s)
107 | _ -> ("?", -1))
108 (rows
109 (E.exec cat
110 "SELECT k, sum(v) FROM t GROUP BY k HAVING sum(v) > 5 ORDER BY k")));
111 Alcotest.(check (list int))
112 "UNION dedups and orders" [ 1; 2; 10; 20; 99 ]
113 (List.map
114 (function [ Catalog.Value.Int n ] -> Int64.to_int n | _ -> -1)
115 (rows
116 (E.exec cat
117 "SELECT v FROM t UNION SELECT * FROM (VALUES (99)) ORDER BY v")));
118 Alcotest.(check (list int))
119 "a recursive WITH counts to 4" [ 1; 2; 3; 4 ]
120 (List.map
121 (function [ Catalog.Value.Int n ] -> Int64.to_int n | _ -> -1)
122 (rows
123 (E.exec cat
124 "WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c \
125 WHERE n<4) SELECT n FROM c")))
126
127(* An unsupported shape fails loud rather than silently doing nothing. *)
128let test_unsupported_is_loud () =
129 let cat = Catalog.of_mem [] in
130 ignore (E.exec cat "CREATE TABLE t (x)");
131 Alcotest.check_raises "INSERT DEFAULT VALUES is rejected, not silently wrong"
132 (Failure "engine: INSERT DEFAULT VALUES is not supported yet") (fun () ->
133 ignore (E.exec cat "INSERT INTO t DEFAULT VALUES"))
134
135let suite =
136 ( "engine",
137 [
138 Alcotest.test_case "CREATE / INSERT / SELECT over Spi" `Quick
139 test_create_insert_select;
140 Alcotest.test_case "UPDATE / DELETE over Spi" `Quick test_update_delete;
141 Alcotest.test_case "DROP TABLE" `Quick test_drop_table;
142 Alcotest.test_case "join over Spi base tables" `Quick test_join;
143 Alcotest.test_case "aggregate / compound / recursive WITH" `Quick
144 test_aggregate_compound_cte;
145 Alcotest.test_case "unsupported shapes fail loud" `Quick
146 test_unsupported_is_loud;
147 ] )