Something went wrong. Try again.
a database layer insipred by caqti and ecto
Something went wrong. Try again.
7.6 kB · 216 lines
OCaml
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217(** SQLite driver for Repodb.
This package exposes a concrete {!Repodb.Driver.S} implementation backed by the [sqlite3] OCaml bindings. It is the easiest backend for tests, local tools, and small applications.
Basic use:
{[ module Repo = Repodb.Repo.Make (Repodb_sqlite.Driver)
let with_db path f = Repodb_sqlite.with_connection path (fun conn -> f conn) ]}
[connect] accepts the SQLite database path. Use [":memory:"] for an in-memory database. SQL placeholders are [?], [RETURNING] is enabled, and upsert rendering uses SQLite syntax.
SQLite has dynamic typing. The driver maps booleans to integer [0]/[1], timestamps and dates to text, UUIDs to blobs when encoded through {!Repodb.Types}, and JSON to text. Keep those representations in mind when writing custom SQL or inspecting raw rows.
The [Raw] module is public for advanced integrations and tests, but most application code should use the values re-exported at the top level or the {!Driver} module with {!Repodb.Repo.Make}. *)let default_cache_size = 100
module Raw = struct type connection = { db : Sqlite3.db; cache : Sqlite3.stmt Repodb.Stmt_cache.t; }
type error = string
let dialect = Repodb.Driver.SQLite let error_message e = e let error_of_string s = s
let connect path = try let db = Sqlite3.db_open path in let cache = Repodb.Stmt_cache.create ~max_size:default_cache_size ~finalize:(fun stmt -> ignore (Sqlite3.finalize stmt)) in Ok { db; cache } with Sqlite3.Error msg -> Error msg
let close conn = Repodb.Stmt_cache.clear conn.cache; let _ = Sqlite3.db_close conn.db in ()
let value_to_data (v : Repodb.Driver.Value.t) : Sqlite3.Data.t = match v with | Repodb.Driver.Value.Null -> Sqlite3.Data.NULL | Repodb.Driver.Value.Int n -> Sqlite3.Data.INT (Int64.of_int n) | Repodb.Driver.Value.Int64 n -> Sqlite3.Data.INT n | Repodb.Driver.Value.Float f -> Sqlite3.Data.FLOAT f | Repodb.Driver.Value.Text s -> Sqlite3.Data.TEXT s | Repodb.Driver.Value.Blob s -> Sqlite3.Data.BLOB s | Repodb.Driver.Value.Bool b -> Sqlite3.Data.INT (if b then 1L else 0L)
let data_to_value (d : Sqlite3.Data.t) : Repodb.Driver.Value.t = match d with | Sqlite3.Data.NONE -> Repodb.Driver.Value.Null | Sqlite3.Data.NULL -> Repodb.Driver.Value.Null | Sqlite3.Data.INT n -> Repodb.Driver.Value.Int64 n | Sqlite3.Data.FLOAT f -> Repodb.Driver.Value.Float f | Sqlite3.Data.TEXT s -> Repodb.Driver.Value.Text s | Sqlite3.Data.BLOB s -> Repodb.Driver.Value.Blob s
let bind_params stmt params = Array.iteri (fun i v -> let rc = Sqlite3.bind stmt (i + 1) (value_to_data v) in if rc <> Sqlite3.Rc.OK then failwith (Printf.sprintf "bind failed at %d" i)) params
let collect_rows stmt = let columns = Array.init (Sqlite3.column_count stmt) (fun i -> Sqlite3.column_name stmt i) in let rec loop acc = match Sqlite3.step stmt with | Sqlite3.Rc.ROW -> let values = Array.init (Sqlite3.data_count stmt) (fun i -> data_to_value (Sqlite3.column stmt i)) in loop ({ Repodb.Driver.columns; values } :: acc) | Sqlite3.Rc.DONE -> List.rev acc | rc -> failwith (Sqlite3.Rc.to_string rc) in loop []
let run_statement (conn : connection) (sql : string) ~(params : Repodb.Driver.Value.t array) = let stmt = Repodb.Stmt_cache.get_or_prepare conn.cache ~sql ~prepare:(fun () -> Sqlite3.prepare conn.db sql) in let _ = Sqlite3.reset stmt in let rec step () = match Sqlite3.step stmt with | Sqlite3.Rc.DONE -> Ok () | Sqlite3.Rc.ROW -> step () | rc -> Error (Sqlite3.Rc.to_string rc) in (* Reset as soon as the statement finishes, on the raising path too: a cached statement left un-reset keeps SQLite's implicit read transaction open, which makes concurrent writers on other connections see SQLITE_BUSY. *) Fun.protect ~finally:(fun () -> ignore (Sqlite3.reset stmt)) (fun () -> bind_params stmt params; step ())
let exec_raw (conn : connection) (sql : string) ~(params : Repodb.Driver.Value.t array) = try run_statement conn sql ~params with | Failure msg -> Error msg | Sqlite3.Error msg -> Error msg
(* SQLite has no per-statement "generated id"; [last_insert_rowid] is a connection-level register that only an INSERT updates. Comparing it across the statement therefore reports a generated id exactly when the statement inserted a row with a new rowid. *) let exec_result_raw (conn : connection) (sql : string) ~(params : Repodb.Driver.Value.t array) = try let before = Sqlite3.last_insert_rowid conn.db in match run_statement conn sql ~params with | Error _ as err -> err | Ok () -> let after = Sqlite3.last_insert_rowid conn.db in Ok { Repodb.Driver.affected_rows = Int64.of_int (Sqlite3.changes conn.db); generated_id = (if Int64.equal after before then None else Some after); } with | Failure msg -> Error msg | Sqlite3.Error msg -> Error msg
let query_raw (conn : connection) (sql : string) ~(params : Repodb.Driver.Value.t array) = try let stmt = Repodb.Stmt_cache.get_or_prepare conn.cache ~sql ~prepare:(fun () -> Sqlite3.prepare conn.db sql) in let _ = Sqlite3.reset stmt in Fun.protect ~finally:(fun () -> ignore (Sqlite3.reset stmt)) (fun () -> bind_params stmt params; Ok (collect_rows stmt)) with | Failure msg -> Error msg | Sqlite3.Error msg -> Error msg
let placeholder _ = "?" let returning_supported = false let upsert_syntax = `SQLite let last_insert_id conn = Ok (Sqlite3.last_insert_rowid conn.db) let ddl_capabilities = Repodb.Driver.default_ddl_capabilities dialect
(* SQLite transaction control is plain SQL, but it is issued outside the statement cache so that no transaction-control statement is ever prepared or retained. *) let txn_statement conn sql = match Sqlite3.exec conn.db sql with | Sqlite3.Rc.OK -> Ok () | rc -> Error (Sqlite3.Rc.to_string rc) | exception Sqlite3.Error msg -> Error msg
let begin_transaction conn = txn_statement conn "BEGIN" let commit conn = txn_statement conn "COMMIT" let rollback conn = txn_statement conn "ROLLBACK"end
module Driver = Repodb.Driver_common.Make_full (Raw)
type connection = Driver.connectiontype error = Driver.error
let dialect = Driver.dialectlet error_message = Driver.error_messagelet connect = Driver.connectlet close = Driver.closelet with_connection = Driver.with_connectionlet exec = Driver.execlet query = Driver.querylet query_one = Driver.query_onelet query_fold = Driver.query_foldlet query_iter = Driver.query_iterlet transaction = Driver.transactionlet begin_transaction = Driver.begin_transactionlet commit = Driver.commitlet rollback = Driver.rollbacklet exec_result = Driver.exec_resultlet ddl_capabilities = Driver.ddl_capabilitieslet placeholder = Driver.placeholderlet returning_supported = Driver.returning_supportedlet upsert_syntax = Driver.upsert_syntaxlet last_insert_id = Driver.last_insert_idlet driver : Repodb.Driver.driver = (module Driver)let stmt_cache_size (conn : connection) = Repodb.Stmt_cache.size conn.cache