(* 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 (* 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