Idea for another type safe PostgreSQL interface

Idea for another type safe PostgreSQL interface

[Richard Jones]

  [I just throwing this idea out there to see if people find it
  interesting, or want to shoot it down ...  There're only fragments of
  working code at the moment]

I'm thinking about a type safe interface to PostgreSQL.  One such
interface at the moment is in Xcaml, but it only supports a very small
subset of SQL, and I like to use complex SQL.  It seems that there is
a way to support the whole of PostgreSQL's language from within OCaml
programs, in a type safe way.

The general plan would be to have a camlp4 extension which would use
Postgres's new "PREPARE" feature to actually prepare the statements,
and Postgres's other new feature, "Describe Statement", to pull out
the parameter types and result types from the prepared statement.
This allows the camlp4 extension to replace the statement string with
a type safe expression, and allow type inference to find mismatches.
How a typical program would look is shown at the end of this message.

The advantages are:

 * Complete support for PostgreSQL statements with hardly any
   programming effort.
 * Type safe.
 * Schema is stored in one place - the database - and doesn't need
   to be duplicated.

The obvious disadvantages stem from the fact that at _compile time_,
you need to have access to the database.  Perhaps others will think of
other disadvantages.

(I currently don't care about databases which aren't Postgres ...)

Thoughts?

Rich.

----------------------------------------------------------------------
open Printf
open Postgresql_typed

(*
  create temporary test (
    id serial not null primary key,
    str text not null,
    num int4 not null
  )
*)

let () =
  let dbh = new Postgresql.connection ~dbname:"rich" in
  let insert t i =
    PGSQL(dbh) "insert into test (str, num) values ($t, $i)"
  in
  insert "hello" 10;
  insert "world" 20;
  let rows = PGSQL(dbh) "select id, str, num from test order by 3, 2, 1" in
  (* 'rows' would have a type like (int, string, int) Rows.t *)
  Rows.iter (
    fun (id, text, num) ->
      printf "id = %ld, text = %s, num = %ld\n" id text num
  ) rows

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

Re: [Caml-list] Idea for another type safe PostgreSQL interface

[Alex Baretta]

Richard Jones wrote:
>   [I just throwing this idea out there to see if people find it
>   interesting, or want to shoot it down ...  There're only fragments of
>   working code at the moment]
> 
> I'm thinking about a type safe interface to PostgreSQL.  One such
> interface at the moment is in Xcaml, but it only supports a very small
> subset of SQL, and I like to use complex SQL.  It seems that there is
> a way to support the whole of PostgreSQL's language from within OCaml
> programs, in a type safe way.

Every once in a while we extend the Embedded SQL with a new feature, but
we never planned to support all of PostgreSQL. In fact, what we want to
have is abstraction over the actual DB implementation.

> The general plan would be to have a camlp4 extension which would use
> Postgres's new "PREPARE" feature to actually prepare the statements,
> and Postgres's other new feature, "Describe Statement", to pull out
> the parameter types and result types from the prepared statement.
> This allows the camlp4 extension to replace the statement string with
> a type safe expression, and allow type inference to find mismatches.
> How a typical program would look is shown at the end of this message.

I really think XDBS is the the way to go. You define the schema in a
high level language (OO-Entity-Relationship modeling), supporting lower
level refinements (logical, physical and virtual schema refinements) and
compiling to Ocaml and SQL-DDL. This way, the type safety can be
established at compile time without need for a database connection.
Also, the type safety does not depend on a specific implementation of
SQL, which is usually desireable.

Alex

-- 
*********************************************************************
http://www.barettadeit.com/
Baretta DE&IT
A division of Baretta SRL

tel. +39 02 370 111 55
fax. +39 02 370 111 54

Our technology:

The Application System/Xcaml (AS/Xcaml)
<http://www.asxcaml.org/>

The FreerP Project
<http://www.freerp.org/>

Re: [Caml-list] Idea for another type safe PostgreSQL interface

[Richard Jones]

On Tue, Jul 19, 2005 at 08:26:30AM +0200, Alex Baretta wrote:
> I really think XDBS is the the way to go. You define the schema in a
> high level language (OO-Entity-Relationship modeling), supporting lower
> level refinements (logical, physical and virtual schema refinements) and
> compiling to Ocaml and SQL-DDL. This way, the type safety can be
> established at compile time without need for a database connection.

I think I completely understand this approach, having worked on and
with a Java technology called PDL, part of Red Hat's now defunct CMS
offering.  So I'm interested to know how you solve some of the issues
we had with PDL.

(1) Changes to the database schema.  Does your product allow you to
generate the appropriate ALTER TABLE ... statements when the schema
changes?  How about upgrading an existing live database between
versions of the schema?

(2) How is the OR mapping handled?  PDL had a complex compiler which
(supposedly) generated optimal SQL statements from object methods.  In
practice the developers seemed to spend a lot of time writing
hand-optimised queries.  I don't really understand what the fuss is
about just writing SQL queries directly into code - it's the fastest
way I've found to achieve results (if only it were type safe), seems
reasonably maintainable, and gets rid of layers of obscure
abstraction.

In the end I stopped using PDL because the mooted "benefits" - that
everything was written in a bunch of obscure files in their case -
really didn't have any bearing on real development.  I can quite
happily tap CREATE TABLE and ALTER TABLE statements directly into a
development database, export the schema to CVS to keep track of
changes, and use a tool to diff the dev and live schemas for roll-outs
and upgrades.

> Also, the type safety does not depend on a specific implementation of
> SQL, which is usually desireable.

I take your point, but really PostgreSQL already does everything I'm
ever likely to want to do with a database, it's Free, stable and fast,
and actively under development.

Rich.

PS. Something about PDL here:
http://www.redhat.com/docs/manuals/waf/rhea-dg-waf-en-6.0/s1-do-beginning.html

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

Re: [Caml-list] Idea for another type safe PostgreSQL interface

[Alex Baretta]

Richard Jones wrote:
> On Tue, Jul 19, 2005 at 08:26:30AM +0200, Alex Baretta wrote:
> 
> (1) Changes to the database schema.  Does your product allow you to
> generate the appropriate ALTER TABLE ... statements when the schema
> changes?  How about upgrading an existing live database between
> versions of the schema?

I have been working on it. I was almost finished with it when I had to
switch to something else. I count on working on this during the August
break, when the rate of interrupts is much lower.

The basic technique is the following: I compare two XDBS models and
generate an /tentative/ refinement model, which, applied to the older
XDBS model, yields the second. The model is only tentative in that the
diff operator is not unique: there are many different refinements
between the same two endpoint models. The programmer is then able to
look at what `xdbs_diff model1.xdbs model2.xdbs` has generated and maybe
modify by hand to match his intuition of what the refinement should be.
The /final/ refinement is fed together with the original schema and
endpoint schema to xdbs_patch, which verifies that the refinement
applied the first schema yields the second, and, if this test is passed,
it generates the SQL code transforming the datastructure according to
the refinement.

This is the only sensibile approach, in that it allows the programmer to
stick his nose in what the automated code generation process *and* keep
track of his work on top of the automated tools' work by commiting the
refinement model to the repository.


> (2) How is the OR mapping handled?  PDL had a complex compiler which

OR? What is it?

> (supposedly) generated optimal SQL statements from object methods.  In
> practice the developers seemed to spend a lot of time writing
> hand-optimised queries.  I don't really understand what the fuss is
> about just writing SQL queries directly into code - it's the fastest
> way I've found to achieve results (if only it were type safe), seems
> reasonably maintainable, and gets rid of layers of obscure
> abstraction.

SQL is very sensible language for the manipulation of relations. General
purpose languages don't have joins as a native language construct, for
one thing. For this reason we have committed to SQL as the "Way To Go"
for accessing permanent storage from the Xcaml system. Of course, I
could not tolerate the "query-as-a-string" approach of PHP. We had to
have it compiled and typechecked, or we could not expect FreerP to scale
beyond a few dozen tables.

>>Also, the type safety does not depend on a specific implementation of
>>SQL, which is usually desireable.
> 
> I take your point, but really PostgreSQL already does everything I'm
> ever likely to want to do with a database, it's Free, stable and fast,
> and actively under development.

PostgreSQL is also my database of choice. But I know that I must also
live with "legacy" RDBMS out there--Oracle, iSeries--where I might have
to run a query every once in a while. And I don't want to do it without
typechecking.

Alex


-- 
*********************************************************************
http://www.barettadeit.com/
Baretta DE&IT
A division of Baretta SRL

tel. +39 02 370 111 55
fax. +39 02 370 111 54

Our technology:

The Application System/Xcaml (AS/Xcaml)
<http://www.asxcaml.org/>

The FreerP Project
<http://www.freerp.org/>

Re: Idea for another type safe PostgreSQL interface

[jean-claude]

Alex Baretta <alex <at> barettadeit.com> writes:
> 
> Richard Jones wrote:
> >   [I just throwing this idea out there to see if people find it
> >   interesting, or want to shoot it down ...  There're only fragments of
> >   working code at the moment]
> > 
> > I'm thinking about a type safe interface to PostgreSQL.  One such
> > interface at the moment is in Xcaml, but it only supports a very small
> > subset of SQL, and I like to use complex SQL.  It seems that there is
> > a way to support the whole of PostgreSQL's language from within OCaml
> > programs, in a type safe way.
> 
> Every once in a while we extend the Embedded SQL with a new feature, but
> we never planned to support all of PostgreSQL. In fact, what we want to
> have is abstraction over the actual DB implementation.
> 
> > The general plan would be to have a camlp4 extension which would use
> > Postgres's new "PREPARE" feature to actually prepare the statements,
> > and Postgres's other new feature, "Describe Statement", to pull out
> > the parameter types and result types from the prepared statement.
> > This allows the camlp4 extension to replace the statement string with
> > a type safe expression, and allow type inference to find mismatches.
> > How a typical program would look is shown at the end of this message.
> 

Back in the pre-internet era, Dec implemented a DBMS (Rdb I think), a C++
compiler and a "compile time" coherency check between C++ and Rdb.

Their implementation had the following features
-1) C++ compilation would read Rdb schema,
-2) There was a strong coupling between database schema and C++ program.
-3) Moving from test environment to production lead us to rebuild the code,
(That’s silly but I could not find a way around it).

We just gave up using it.


> I really think XDBS is the the way to go. You define the schema in a
> high level language (OO-Entity-Relationship modeling), supporting lower
> level refinements (logical, physical and virtual schema refinements) and
> compiling to Ocaml and SQL-DDL. This way, the type safety can be
> established at compile time without need for a database connection.
> Also, the type safety does not depend on a specific implementation of
> SQL, which is usually desireable.
> 
> Alex
> 

Nb: I have never worked with PostgreSQL, BUT, with Oracle, Informix, DB2,
sybase, mssql, the full name of a table ( databasename.login.table ) only binds
to an entry inside a system catalog.

If your application uses several logins, then, checking program structures
against database schema can not really occur before login time.

If the goal is only a sanity check, then using any reference definition can
help, but I don't think it will replase the run time check.

Regards,