init

1 files changed, 159 insertions, 0 deletions

diff --git a/lib/query.ml b/lib/query.ml
new file mode 100644
index 0000000..70285fc
--- /dev/null
+++ b/lib/query.ml
@@ -0,0 +1,159 @@
+(* 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]
+  ;;
+
+  (* Insert queries *)
+  let insert_post =
+    [%rapper
+      execute
+        {sql|
+          INSERT INTO Posts (title, content, date, tags, url)
+          VALUES (%string{title}, %string{content}, %string{date}, %string{tags}, %string{url})
+        |sql}]
+  ;;
+
+  let insert_comment =
+    [%rapper
+      execute
+        {sql|
+          INSERT INTO Comments (content, date, tags, url, post_id, parent, author)
+          VALUES (%string{content}, %string{date}, %string{tags}, %string{url}, %int{post_id}, %int?{parent}, %string{author})
+        |sql}]
+  ;;
+
+  let insert_vote =
+    [%rapper
+      execute
+        {sql|
+          INSERT INTO Votes (post_id, comment_id, user_id, vote_type)
+          VALUES (%int?{post_id}, %int?{comment_id}, %string{user_id}, %string{vote_type})
+        |sql}]
+  ;;
+end
+
+let get_poasts conn = Query.poasts conn
+let get_poast post_id conn = Query.poast ~post_id conn
+
+(* Insert functions *)
+let create_post ~title ~content ~date ~tags ~url conn = 
+  Query.insert_post ~title ~content ~date ~tags ~url conn
+
+let create_comment ~content ~date ~tags ~url ~post_id ?parent ~author conn =
+  Query.insert_comment ~content ~date ~tags ~url ~post_id ~parent ~author conn
+
+let create_vote ~user_id ~vote_type ?post_id ?comment_id conn =
+  Query.insert_vote ~post_id ~comment_id ~user_id ~vote_type 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