RE: [Caml-list] Type-safe interface to Postgres's SQL

RE: [Caml-list] Type-safe interface to Postgres's SQL

[Quinn, Chris]

A few years ago I implemented a type safe interface to db2 (odbc
compliant).
My approach was to extend the caml compiler with a Type.typeof : 'a ->
typeinfo function
which marshalled the internal type info into a string.
This is then used at runtime to compare with the database's notion of
what a query produces
(the check is performed only once during the execution life of the
program, thereby minimising cost)
So any mismatch with the db schema is caught (as long as that bit of the
code is actually run!)

/C

RE: [Caml-list] Type-safe interface to Postgres's SQL

[brogoff]

On Tue, 31 Jan 2006, Quinn, Chris wrote:
> A few years ago I implemented a type safe interface to db2 (odbc
> compliant).
> My approach was to extend the caml compiler with a Type.typeof : 'a ->
> typeinfo function
> which marshalled the internal type info into a string.
> This is then used at runtime to compare with the database's notion of
> what a query produces
> (the check is performed only once during the execution life of the
> program, thereby minimising cost)
> So any mismatch with the db schema is caught (as long as that bit of the
> code is actually run!)

Sounds like GCaml (http://www.y1.is.s.u-tokyo.ac.jp/~furuse/gcaml) would have
allowed you to express this directly. The better typed marshalling has been
on my wishlist for a while

-- Brian

Re: [Caml-list] Type-safe interface to Postgres's SQL

[Richard Jones]

On Tue, Jan 31, 2006 at 02:07:33PM +0000, Richard Jones wrote:
> (2) PostgreSQL doesn't track NULL types properly so we have to do a
> bit of non-trivial guesswork to try to find out if a database field
> could contain a NULL or not (and therefore whether to map its type to
> 'a or 'a option).  For parameters this isn't possible at all, so all
> parameter variables must have an option type.  For result fields, it
> is usually possible to tell if the result corresponds to a known
> column in the database, which is the case in ninety percent of
> queries.

FWIW I have solved this problem with a small syntactic change.  You
now use $varname when you don't want NULLs and $?varname when you
do.[1] A modified example program is attached.

You can also download the full program from here:

http://merjis.com/tmp/pgocaml-0.2.tar.gz

Rich.

[1] It is still possible to insert a NULL into a NOT NULL field, as
was the case before, but I can't see any way to solve that without
rewriting Postgres.

----------------------------------------------------------------------
(* Example program using typesafe calls to PostgreSQL.
 * $Id: test.ml,v 1.4 2006/02/18 15:54:06 rich Exp $
 *)

open Printf

let () =
  let dbh = PGOCaml.connect () in

  PGSQL_EXECUTE_ON_COMPILE(dbh) "create temporary table employees (
     id serial not null primary key,
     name text not null,
     salary int4 not null,
     email text
  )";

  let insert name salary email =
    PGSQL(dbh) "insert into employees (name, salary, email)
                values ($name, $salary, $?email)"
  in
  insert "Ann" 10_000_l None;
  insert "Bob" 45_000_l None;
  insert "Jim" 20_000_l None;
  insert "Mary" 30_000_l (Some "mary@example.com");

  let rows = PGSQL(dbh) "select id, name, salary, email from employees" in
  List.iter (
    fun (id, name, salary, email) ->
      let email = match email with Some email -> email | None -> "-" in
      printf "%ld %S %ld %S\n" id name salary email
  ) rows;

  PGOCaml.close dbh


-- 
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com