From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Original-To: caml-list@yquem.inria.fr Delivered-To: caml-list@yquem.inria.fr Received: from concorde.inria.fr (concorde.inria.fr [192.93.2.39]) by yquem.inria.fr (Postfix) with ESMTP id CC2D4BBC4 for ; Tue, 31 Jan 2006 14:39:53 +0100 (CET) Received: from pauillac.inria.fr (pauillac.inria.fr [128.93.11.35]) by concorde.inria.fr (8.13.0/8.13.0) with ESMTP id k0VDdrIi013604 for ; Tue, 31 Jan 2006 14:39:53 +0100 Received: from concorde.inria.fr (concorde.inria.fr [192.93.2.39]) by pauillac.inria.fr (8.7.6/8.7.3) with ESMTP id OAA21519 for ; Tue, 31 Jan 2006 14:39:52 +0100 (MET) Received: from furbychan.cocan.org (furbychan.cocan.org [80.68.91.176]) by concorde.inria.fr (8.13.0/8.13.0) with ESMTP id k0VDdpho013593 (version=TLSv1/SSLv3 cipher=EDH-RSA-DES-CBC3-SHA bits=168 verify=NO) for ; Tue, 31 Jan 2006 14:39:52 +0100 Received: from rich by furbychan.cocan.org with local (Exim 3.35 #1 (Debian)) id 1F3wAP-0000bD-00 for ; Tue, 31 Jan 2006 14:07:33 +0000 Date: Tue, 31 Jan 2006 14:07:33 +0000 To: caml-list@inria.fr Subject: Type-safe interface to Postgres's SQL Message-ID: <20060131140733.GA31772@furbychan.cocan.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.5.9i From: Richard Jones X-Miltered: at concorde with ID 43DF68A9.000 by Joe's j-chkmail (http://j-chkmail.ensmp.fr)! X-Miltered: at concorde with ID 43DF68A7.000 by Joe's j-chkmail (http://j-chkmail.ensmp.fr)! X-Spam: no; 0.00; type-safe:01 caml-list:01 tar:01 type-safe:01 non-trivial:01 camlp:01 non-trivial:01 corresponds:01 inference:01 ocaml:01 printf:01 iter:01 printf:01 notepad:01 abd:98 X-Spam-Checker-Version: SpamAssassin 3.0.3 (2005-04-27) on yquem.inria.fr X-Spam-Level: X-Spam-Status: No, score=0.0 required=5.0 tests=none autolearn=disabled version=3.0.3 Last year I wrote about my idea for a type safe interface to PostgreSQL here: http://caml.inria.fr/pub/ml-archives/caml-list/2005/07/857083d0d55ca642e4452ab82abd02cc.en.html (See also Alex Baretta's criticism in the same thread). Well, I actually implemented this after a lot of pain: http://merjis.com/tmp/pgocaml-0.1.tar.gz A typical type-safe program is attached to the end of this message. There are a number of unanticipated problems with the implementation: (1) Error reporting is a bit confusing. Actually I think this is an issue for any non-trivial camlp4 extension to the language. You get a message which doesn't relate to code which you actually wrote, but instead to the code expanded from the macro. (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. (3) You need to have access to the real database at compile time, as discussed in my original message. This is because we prepare the statements on the database engine and then ask the database engine to tell us the types of the parameters / results, and use this to generate code which makes the magic of type inference work on the OCaml side. Of course we don't _execute_ the statements at compile time - which would have horrible side-effects. Except that in some cases we need to execute the statement at compile time. The particular case is when the statement is "CREATE TEMPORARY TABLE", because subsequent code needs to actually have the temporary table there in order to check later statements which access this table. So I've added a special case to cope with "CREATE TEMPORARY TABLE", which might be regarded as something of a hack. You would probably never need to use this hack in Real Code, but the nature of test scripts like the one attached is that they do create temporary tables. BTW, I don't think any of this is necessarily "better" or "worse" than the existing PG interfaces out there -- just different. Rich. ---------------------------------------------------------------------- (* Example program using typesafe calls to PostgreSQL. * $Id: test.ml,v 1.3 2006/01/31 13:22:36 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 = PGSQL(dbh) "insert into employees (name, salary) values ($name, $salary)" in insert (Some "Ann") (Some 10_000_l); insert (Some "Bob") (Some 45_000_l); insert (Some "Jim") (Some 20_000_l); insert (Some "Mary") (Some 30_000_l); 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