a database layer insipred by caqti and ecto
gdiazlo.tngl.sh
1open Repodb
2open Common
3
4let posts_table = Schema.table "posts"
5
6let test_from () =
7 let q = Query.from posts_table in
8 let sql = Query.to_sql q in
9 Alcotest.(check bool) "basic SELECT" true (String.sub sql 0 6 = "SELECT")
10
11let test_select_all () =
12 let q = Query.from posts_table |> Query.select_all in
13 let sql = Query.to_sql q in
14 Alcotest.(check bool) "SELECT *" true (String.length sql > 0)
15
16let test_where () =
17 let q = Query.from posts_table |> Query.where Expr.(int 1 = int 1) in
18 let sql = Query.to_sql q in
19 Alcotest.(check bool) "has WHERE" true (String.length sql > 0)
20
21let test_limit () =
22 let q = Query.from posts_table |> Query.limit 10 in
23 let sql = Query.to_sql q in
24 Alcotest.(check bool) "has LIMIT" true (String.length sql > 0)
25
26let test_offset () =
27 let q = Query.from posts_table |> Query.offset 5 in
28 let sql = Query.to_sql q in
29 Alcotest.(check bool) "has OFFSET" true (String.length sql > 0)
30
31let test_order_by_asc () =
32 let q = Query.from posts_table |> Query.asc (Expr.raw "title") in
33 let sql = Query.to_sql q in
34 Alcotest.(check bool) "has ORDER BY" true (String.length sql > 0)
35
36let test_order_by_desc () =
37 let q = Query.from posts_table |> Query.desc (Expr.raw "created_at") in
38 let sql = Query.to_sql q in
39 Alcotest.(check bool) "has DESC" true (String.length sql > 0)
40
41let test_distinct () =
42 let q = Query.from posts_table |> Query.distinct in
43 let sql = Query.to_sql q in
44 Alcotest.(check bool) "has DISTINCT" true (String.length sql > 0)
45
46let test_insert_into () =
47 let q =
48 Query.insert_into posts_table
49 |> Query.values [ name_field ] [ [ Expr.string "Hello" ] ]
50 in
51 let sql = Query.to_sql q in
52 Alcotest.(check bool) "INSERT INTO" true (String.sub sql 0 6 = "INSERT")
53
54let test_update () =
55 let q =
56 Query.update posts_table |> Query.set name_field (Expr.string "Updated")
57 in
58 let sql = Query.to_sql q in
59 Alcotest.(check bool) "UPDATE" true (String.sub sql 0 6 = "UPDATE")
60
61let test_delete_from () =
62 let q = Query.delete_from posts_table in
63 let sql = Query.to_sql q in
64 Alcotest.(check bool) "DELETE FROM" true (String.sub sql 0 6 = "DELETE")
65
66let test_join () =
67 let comments_table = Schema.table "comments" in
68 let q =
69 Query.from posts_table |> Query.join ~on:Expr.(int 1 = int 1) comments_table
70 in
71 let sql = Query.to_sql q in
72 Alcotest.(check bool) "has JOIN" true (String.length sql > 0)
73
74let test_left_join () =
75 let comments_table = Schema.table "comments" in
76 let q =
77 Query.from posts_table
78 |> Query.left_join ~on:Expr.(int 1 = int 1) comments_table
79 in
80 let sql = Query.to_sql q in
81 Alcotest.(check bool) "has LEFT JOIN" true (String.length sql > 0)
82
83let contains_substring haystack needle =
84 let needle_len = String.length needle in
85 let haystack_len = String.length haystack in
86 if needle_len > haystack_len then false
87 else
88 let rec check i =
89 if i > haystack_len - needle_len then false
90 else if String.sub haystack i needle_len = needle then true
91 else check (i + 1)
92 in
93 check 0
94
95let test_inner_join_spacing () =
96 let comments_table = Schema.table "comments" in
97 let q =
98 Query.from posts_table
99 |> Query.inner_join ~on:Expr.(int 1 = int 1) comments_table
100 in
101 let sql = Query.to_sql q in
102 let has_bad_spacing = contains_substring sql "postsINNER" in
103 Alcotest.(check bool)
104 "no missing space before INNER JOIN" false has_bad_spacing;
105 let has_proper_spacing = contains_substring sql "posts INNER JOIN comments" in
106 Alcotest.(check bool)
107 "proper spacing: 'posts INNER JOIN comments'" true has_proper_spacing
108
109let test_on_conflict_do_nothing () =
110 let q =
111 Query.insert_into posts_table
112 |> Query.values [ name_field ] [ [ Expr.string "Hello" ] ]
113 |> Query.on_conflict_do_nothing
114 in
115 let sql = Query.to_sql q in
116 Alcotest.(check bool) "has ON CONFLICT" true (String.length sql > 0)
117
118let tests =
119 [
120 ("from", `Quick, test_from);
121 ("select_all", `Quick, test_select_all);
122 ("where", `Quick, test_where);
123 ("limit", `Quick, test_limit);
124 ("offset", `Quick, test_offset);
125 ("order_by asc", `Quick, test_order_by_asc);
126 ("order_by desc", `Quick, test_order_by_desc);
127 ("distinct", `Quick, test_distinct);
128 ("insert_into", `Quick, test_insert_into);
129 ("update", `Quick, test_update);
130 ("delete_from", `Quick, test_delete_from);
131 ("join", `Quick, test_join);
132 ("left_join", `Quick, test_left_join);
133 ("inner_join_spacing", `Quick, test_inner_join_spacing);
134 ("on_conflict_do_nothing", `Quick, test_on_conflict_do_nothing);
135 ]