nice nice. lsp still doesnt work tho

1 files changed, 186 insertions, 0 deletions

diff --git a/lib/shared/query.ml b/lib/shared/query.ml
new file mode 100644
index 0000000..bd1f11c
--- /dev/null
+++ b/lib/shared/query.ml
@@ -0,0 +1,186 @@
+open Rapper_helper
+
+type conn = (module CONNECTION)
+type pool = ((module CONNECTION), Caqti_error.t) Caqti_eio.Pool.t
+type 'a result_promise = ('a, Caqti_error.t) result Eio.Promise.t
+
+(* 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 pool = Caqti_eio.Pool.use (Query.poasts ()) pool *)
+
+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
+  (* For PRAGMA commands, we don't know the return type, so use a custom approach *)
+  (* Some return strings, some return ints, some return nothing *)
+  let exec_pragma sql =
+    let open Caqti_request.Infix in
+    let open Caqti_type in
+    (* Try to execute as a simple exec first (for pragmas that return nothing) *)
+    match C.exec ((unit ->. unit) sql) () with
+    | Ok () -> Ok ()
+    | Error _ ->
+      (* If that fails, try as a query that returns a string *)
+      (match C.find_opt ((unit ->? string) sql) () with
+       | Ok _ -> Ok ()
+       | Error _ ->
+         (* If that also fails, try as a query that returns an int *)
+         (match C.find_opt ((unit ->? int) sql) () with
+          | Ok _ -> Ok ()
+          | Error e -> Error e))
+  in
+  (* Execute each pragma *)
+  List.fold_left
+    (fun acc sql ->
+       match acc with
+       | Error e -> Error e
+       | Ok () -> exec_pragma sql)
+    (Ok ())
+    sqlite_pragmas
+;;