Re: [Caml-list] looking for "real world" sqlite3 examples

["Mauricio Fernández" <mfp@acm.org>]

On Sun, Jul 05, 2015 at 04:45:14PM +0300, Erkki Seppala wrote:
> Martin DeMello <martindemello@gmail.com> writes:
> 
> > 2. type conversions - in the absence of an orm, do i have to write my
> > own by hand per resultset. or is there some intermediate-level library
> > that i haven't found that would automate some of it?
> >
> > 3. is there a maintained library for generating sql queries in a typed manner?
> 
> You should look into SqlExpr:
> 
>   https://github.com/mfp/ocaml-sqlexpr/
> 
> I used it recently for a very small project, and it seemed to work
> great, even if it doesn't ensure compile-time type safety like PGOCaml.

There's a mechanism to unit test the queries automatically (ensuring that
they're coherent with the DB's schema) which can be integrated in the regular
build process, able to catch many (most) problems (syntax errors,
missing/misnamed/ambiguous columns/tables, etc.), see

  https://github.com/mfp/ocaml-sqlexpr#syntax-extension

It works by collecting all the statements that constitute the schema (marked
as "sqlinit") along with the remaining regular queries ("sql" / "sqlc"), and
then generating functions that initialize an empty DB and prepare all the
statements against it (similarly to what PGOCaml does at compile-time).

This can also serve as "early run-time checking", arguably stronger than
PGOCaml in a way (because you can verify that the queries are valid against
the DB's current schema, not against the one you had when compiling).

If you're using OUnit and all your DB-related code lies in module MyDB,
you can do something like this to unit test the SQL queries:

    open OUnit

    let test_sql f () =
      let b   = Buffer.create 13 in
      let fmt = Format.formatter_of_buffer b in
      let contents b = Format.fprintf fmt "@?"; Buffer.contents b in
        if not (f fmt) then
          assert_failure (contents b)

    let tests =
      [ "SQL tests" >:: test_sql MyDB.auto_check_db;
        ...
      ]
      
    ...
       run_test_tt_main ("All" >::: tests)

-- 
Mauricio Fernández