1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
(* Define record types for the query outputs *)
type post_summary = {
id: int;
title: string;
content: string;
date: string;
}
type post = {
id: int;
title: string;
content: string;
date: string;
tags: string;
url: string;
}
type comment = {
id: int;
content: string;
date: string;
tags: string;
url: string;
}
module Query = struct
let poasts =
[%rapper
get_many
{sql|
SELECT @int{id}, @string{title}, @string{content}, @string{date}
FROM Posts
ORDER BY id DESC LIMIT 100
|sql}
record_out]
;;
let poast =
[%rapper
get_opt
{sql|
SELECT @int{id}, @string{title}, @string{content}, @string{date}, @string{tags}, @string{url}
FROM Posts
WHERE id = %int{post_id}
|sql}
record_out]
;;
let comment =
[%rapper
get_opt
{sql|
SELECT @int{id}, @string{content}, @string{date}, @string{tags}, @string{url}
FROM Comments
WHERE id = %int{id}
|sql}
record_out]
;;
let user_comments =
[%rapper
get_many
{sql|
SELECT @int{id}, @string{content}, @string{date}, @string{tags}, @string{url}
FROM Comments
WHERE author = %string{username}
|sql}
record_out]
;;
let post_comments =
[%rapper
get_many
{sql|
SELECT @int{id}, @string{content}, @string{date}, @string{tags}, @string{url}
FROM Comments
WHERE post_id = %int{post_id}
|sql}
record_out]
;;
let comment_children =
[%rapper
get_many
{sql|
SELECT @int{id}, @string{content}, @string{date}, @string{tags}, @string{url}
FROM Comments
WHERE parent= %int{post_id}
|sql}
record_out]
;;
end
let get_poasts conn = Query.poasts conn
let get_poast post_id conn = Query.poast ~post_id conn
(* SQLite performance pragmas - to be implemented *)
let sqlite_pragmas = [
"PRAGMA journal_mode = WAL";
"PRAGMA foreign_keys = ON";
"PRAGMA cache_size = -8000";
"PRAGMA temp_store = MEMORY";
"PRAGMA synchronous = NORMAL";
"PRAGMA mmap_size = 30000000000";
]
(* Example of how to execute raw SQL with Caqti *)
let init_connection conn =
let module C = (val conn : Rapper_helper.CONNECTION) in
(* Create a request: unit input -> unit output using Caqti infix operators *)
let pragma_req sql =
let open Caqti_request.Infix in
let open Caqti_type in
(unit ->. unit) sql
in
(* Execute each pragma *)
List.fold_left (fun acc sql ->
match acc with
| Error e -> Error e
| Ok () -> C.exec (pragma_req sql) ()
) (Ok ()) sqlite_pragmas
|
