[Caml-list] Dbi: proposal #1

[Caml-list] Dbi: proposal #1

[Christophe TROESTLER]

[-- Attachment #1: Type: Text/Plain, Size: 1769 bytes --]

Hi all,

Here is the first proposal for a database independent interface (Dbi)
for Ocaml.  The file "dbi.mli" is attached below.  The code and some
experiments with Mysql can be downloaded from

   http://www.umh.ac.be/math/an/software.php#x4-60005

Some sample code:

  module DB = Dbi_mysql  (* select db module *)
  let t = new DB.row_conversion
  let ( ++ ) = Dbi.(^^)

  let dbh = DB.connect ~host ~pwd "my database" in
  let q = dbh#prepare "SELECT Salary, Name from employees" in
  let res = dbh#raw_exec q [||] in
  let s = res#fold (t#int ++ t#string) (fun sum s _ -> sum + s) 0 in
  dbh#disconnect;
  s

This release is to foster discussions, it is not to be considered as
clean/ready-to-use code.  In particular, some points I'd like feedback
on:

* I think the scheme for "parameter binding" (for encoding and
  decoding) is quite convenient (and prevents the often forgotten
  escaping for encoding).  Does it also fit the needs of your
  applications?

* The way "parameter binding" is implemented (a la scanf) has some
  performance penalty.  Is it possible to come up with a better
  implementation or better ideas?  Is it possible to use Camlp4 to
  reduce the cost while providing the same convenience?

* Are you happy with the methods provided by Dbi.result and the
  interaction between "step by step" and "iteration/folding" schemes
  (stateful object)?

* What basic SQL conversion functions should be provided by all DBs
  (class type Dbi.conversion)?

* What exceptions should be defined in order not to have too many of
  them yet to offer flexibility?  The exceptions defined so far are a
  bit basic, they need to be refined.

I am available for questions if things are unclear (but don't hold
your breath, I am pretty busy).

Cheers,
ChriS

[-- Attachment #2: dbi.mli --]
[-- Type: Text/Plain, Size: 10393 bytes --]

(* 	$Id: dbi.mli,v 1.4 2003/05/28 13:22:07 trch Exp $	 *)

(** {6 Data types and exceptions} *)

type date = int * int * int (* (year, month, day) *)
type time = int * int * int (* (hour, min, sec) *)

type row = string option array

type ('a2b, 'b, 'param, 'row) sql_type
  (** SQL type conversions.  'param, 'row stand for an internal type
    used for storage by the database (which also implies that the
    sql_types cannot be used with other db, for which conversion
    functions may be different anyway).  One has to think 'a2b as 'a1
    -> ... -> 'an -> 'b where 'a1,...,'an are the Caml types
    corresponding to the SQL types. *)

exception Connection_error of string
  (** Raised if one tries to perform an operation that requires the
    connection to be established while it is not (because it
    died,...). *)

exception No_row_found
  (** Exception used to report that there are no more rows to be fetched. *)

exception Conversion_error
  (** Raised when an error occurs during a conversion between SQL
    types and Caml types. *)

exception SQL_error of string

(* More exceptions for reporting errors to be defined *)


(** {6 SQL types and conversion functions} *)

val ( ^^ ) : ('a2b2c, 'b2c, 'param, 'row) sql_type ->
  ('b2c, 'c, 'param, 'row) sql_type -> ('a2b2c, 'c, 'param, 'row) sql_type
  (** [t1 ^^ t2] concatenates the sql_types conversions [t1] and [t2]
    to accept first the arguments of [t1] then those of [t2]. *)

class type ['param, 'row] conversion =
object
  method unit : (unit -> 'a, 'a, 'param, 'row) sql_type
  method char : (char -> 'a, 'a, 'param, 'row) sql_type
  method charO : (char option -> 'a, 'a, 'param, 'row) sql_type
  method string : (string -> 'a, 'a, 'param, 'row) sql_type
  method stringO : (string option -> 'a, 'a, 'param, 'row) sql_type
  method int : (int -> 'a, 'a, 'param, 'row) sql_type
  method intO : (int option -> 'a, 'a, 'param, 'row) sql_type
  method float : (float -> 'a, 'a, 'param, 'row) sql_type
  method floatO : (float option -> 'a, 'a, 'param, 'row) sql_type
  method date : (date -> 'a, 'a, 'param, 'row) sql_type
  method dateO : (date option -> 'a, 'a, 'param, 'row) sql_type
  method datetime : (date * time -> 'a, 'a, 'param, 'row) sql_type
  method datetimeO : ((date * time) option -> 'a, 'a, 'param, 'row) sql_type
  method blob : (string -> 'a, 'a, 'param, 'row) sql_type
  method blobO : (string option -> 'a, 'a, 'param, 'row) sql_type

  (* timestamp, int64, decimal, year *)

  method encode : ('a2b, unit, 'param, 'row) sql_type -> 'param -> 'a2b
  method decode : ('a2b, 'b, 'param, 'row) sql_type -> 'row -> 'a2b -> 'b
end

val row_conversion : (row, row) conversion


(** {6 Results of SQL queries} *)

(** This is a stateful object encapsulating the result of a query to
  the database. *)
class virtual ['param, 'row] result :
object
  method virtual raw_fetch : row
    (** [raw_next t f] fetches the next row. *)
  method virtual fetch : ?on_failure:(row -> 'b) ->
    ('a2b, 'b, 'param, 'row) sql_type -> 'a2b -> 'b
    (** [next t f] fetches the next row and apply the function [f] to it.

      If [Failure _] or [Conversion_error] is raised during the
      conversion or by [f], [next] @raise Conversion_error unless the
      parameter [on_failure] is specified in which case the function
      [on_failure] is applied to the current row.

      @raise No_row_found if there are no more rows. *)
  method virtual rewind : unit
    (** [rewind] replace the cursor at the beginning of the results so
      one can use [next] to list them again. *)

(* DISCUSS: would people prefer [next : unit] and [current_row : row]
   methods ? *)

  method raw_fold : ('b -> row -> 'b) -> 'b -> 'b
  method raw_iter : (row -> unit) -> unit

  method fold : ?on_failure:('b -> row -> 'b) ->
    ('a2b, 'b, 'param, 'row) sql_type -> ('b -> 'a2b) -> 'b -> 'b
    (** [fold ?on_failure t f b] folds the function [f : 'b -> 'a1 ->
      ... -> 'an -> 'b] on the remaining rows of the result where the
      conversions of the data returned from the database is specified
      by [t].  More precisely, if r(1),...,r(k) are the rows of the
      result and one has already fetched r(1),...,r(j), [fold f b]
      computes [f (... (f (f b r(j+1)) r(j+2)) ...)  r(k)].

      @raise Conversion_error if the conversion functions of [f] raise
      [Failure _] or [Conversion_error] unless the parameter
      [on_failure] is specified. *)

  method iter : ?on_failure:(row -> unit) ->
    ('a2b, unit, 'param, 'row) sql_type -> 'a2b -> unit
    (** [iter f] apply the function [f : 'a1 -> ... -> 'an -> unit] in
      turn to all the remaining rows of the result.

      @raise Conversion_error if the conversion functions of [f] raise
      Failure unless the parameter [on_failure] is specified. *)

  method virtual nrows : int
    (** Number of rows in the result.  This method returns the number
      of affected rows if the query was not a SELECT. *)
(* DISCUSS: nrows : int64 *)

  method virtual ncols : int
    (** Number of columns in the result; 0 if the query is not a
      SELECT. *)
  method virtual names : string array
    (** Names of the colums -- hold in an array of length [ncols]. *)
  method virtual types : string array
    (** Types of the columns of the result. *)
  method check : ('a2b, 'b, 'param, 'row) sql_type -> bool
    (** [check t] checks that the conversion [t] is *compatible* with
      the data contained in the result. *)
end



(** {6 Database handle & operations} *)

class type ['param, 'row, 'query] connection =
object
  method database : string
    (** The name of the database one is connected to. *)

  method create_db : string -> unit
    (** [create_db name] creates a new database named [name].
      @raise Failure if it fails. *)

  method select_db : string -> unit
    (** [select_db name] selects the database named [name] as the current one.
      @raise Failure if it fails. *)

  method drop_db : string -> unit
    (** [drop_db name] delete the database named [name].
      @raise Failure if it fails. *)

  method tables : string list
    (** [tables] returns a list of the names of the tables of the
      current database. *)

  method prepare : string -> 'query
    (** [prepare stm] prepares the query [stm] to be executed.  All
      the unescaped '?' are placeholders to be replaced by an argument
      when the query is executed.

      Examples:
      prepare "SELECT * FROM table WHERE field = ?"
      prepare "INSERT INTO table(field1, field2) VALUES (?,?)"
    *)

  method raw_exec : 'query -> row -> ('param, 'row) result
    (** [raw_exec q r] executes the query [q] with the '?' replaced by
      the values taken from [r].  The values in [r] must not be
      escaped -- it is the role of this function to do the proper
      escape. *)

  method exec : 'query ->
    ('a, ('param, 'row) result, 'param, 'row) sql_type -> 'a
    (** [exec q in_types] returns a function, lets us call it [f : 'a1
      -> ... -> 'an -> ('param, 'row) result], that will execute the
      prepared query [q] with the '?' interpreted as values of type
      given by [in_types].

      Example: On a table 'people' with a column 'age', one can do

      [let t = new ...conversion
      let q = dbh#prepare "SELECT * FROM people WHERE age = ?"
      let with_age i = dbh#exec q t#int i]
    *)

  method commit : unit
    (** [commit()] commit the changes to the database. *)
  method rollback : unit
    (** [rollback()] roll back the changes made since last commit. *)
  method disconnect : unit
    (** [disconnect()] disconnects from the database.  Trying to use
      this object after will raise [Failure]. *)
end



(** We recommend that the implemetations of Dbi for a particular
  database possesses a function [connect] (so it is enough to replace
  XXX by the database module in a declaration like "module DB = XXX"
  to switch to a particular database engine) of the following type:
*)
type ('param, 'row, 'query) connect
  = ?user:string -> ?pwd:string -> ?host:string -> ?port:int ->
  string -> ('param, 'row, 'query) connection
(** [connect ?user ?pwd ?host ?port db] returns a new connection
  object to database [db].  If [db = ""], no database is selected.

  @param user the unser name under which to connect (default: current user).
  @param pwd  the password for the connection (default: None).
  @param host the hostname of the SGDB server (default: localhost/sockets).
  @param port the port to connect to (default: database dependent)
*)



(**/**)

(* Low level -- for implementors of Dbi interfaces to databases. *)

val register_sql_type :
  string -> ('row -> int -> 'a) -> ('param -> int -> 'a -> unit)
  -> ('a -> 'b, 'b, 'param, 'row) sql_type
  (* [register_sql_type name decode encode] creates a new sql
     conversion function where [name] is the database name for the
     type, [decode r i] converts the content of the [i]th column in
     the database row [r] to a Caml type and [encode r i x] encodes
     [x] into the [i]th column of the row [r].  If the [decode]
     function raise a [Failure _] or a [Conversion_error] exception,
     it is interpreted as an error in the decoding.  The [encode]
     function should not raise any exception. *)

val unsafe_decode : ('a2b, 'b, 'param, 'row) sql_type -> 'row -> 'a2b -> 'b
  (* [unsafe_decode t r f] returns the result of [f a1 ... an] where
     [a1],..., [an] are the values in the row [r] decoded according to
     [t].  It is not checked that the row is as large as [t] expects;
     if it is not, the program can crash. *)

val unsafe_encode : (unit -> 'b) -> ('a2b, 'b, 'param, 'row) sql_type ->
  'param -> 'a2b
  (* [unsafe_encode cont t r a1 ... an] encodes in [r] the values
     [a1],...,[an] according to [t] and then evaluate the continuation
     [cont].  The result of [unsafe_encode] is the result of [cont].
     It is not checked that the row is large enough to contains all
     these values; if it is not, the program can crash. *)

val arity : ('a2b, 'b, 'param, 'row) sql_type -> int
  (* [arity t] gives the number of arguments/colums expected by [t];
     i.e., if [t : ('a1 -> ... -> 'an -> 'b, 'b, 'param, 'row)
     sql_type], then [arity t] is n. *)

val sql_type_to_string : ('a2b, 'b, 'param, 'row) sql_type -> string array
  (* [sql_type_to_string t] returns an array of the SQL types names
     expected by [t]. *)

Re: [Caml-list] Dbi: proposal #1

[Nicolas Cannasse]

> Here is the first proposal for a database independent interface (Dbi)
> for Ocaml.  The file "dbi.mli" is attached below.  The code and some
> experiments with Mysql can be downloaded from
[...]

One question : why using classes ?

Nicolas Cannasse

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Richard Jones]

On Tue, Jun 10, 2003 at 10:10:41PM +0200, Christophe TROESTLER wrote:
> Some sample code:
> 
>   module DB = Dbi_mysql  (* select db module *)
>   let t = new DB.row_conversion
>   let ( ++ ) = Dbi.(^^)
> 
>   let dbh = DB.connect ~host ~pwd "my database" in
>   let q = dbh#prepare "SELECT Salary, Name from employees" in
>   let res = dbh#raw_exec q [||] in
>   let s = res#fold (t#int ++ t#string) (fun sum s _ -> sum + s) 0 in
>   dbh#disconnect;
>   s

I wish I knew enough OCaml to make some really insightful comments
about this, but in general it looks OK. One question: do you provide
placeholders in the string argument to prepare, eg:

let query = dbh#prepare "select salary from employees where salary < ?" in
let res = query#execute 10000 in
...

(Note I couldn't really understand why you would call dbh#raw_exec
instead of some method on query itself, so I changed that).

To make this type-safe you might need to add type information to the
placeholders, eg. "select ... where salary < ?:int"

Rich.

-- 
Richard Jones, Red Hat Inc. (London) and Merjis Ltd. http://www.merjis.com/
http://www.annexia.org/ Freshmeat projects: http://freshmeat.net/users/rwmj
MAKE+ is a sane replacement for GNU autoconf/automake. One script compiles,
RPMs, pkgs etc. Linux, BSD, Solaris. http://www.annexia.org/freeware/makeplus/

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Christophe TROESTLER]

On Wed, 11 Jun 2003, "Nicolas Cannasse" <warplayer@free.fr> wrote:
> 
> > Here is the first proposal for a database independent interface (Dbi)
> [...]
> One question : why using classes ?

To be able to write functions that are not dependent on a particular
database.

Imagine for example that the DB is chosen at runtime.  With this
approach, one has only to write conditional code to create the
[connection] and [conversion] objects, the rest of the source applies
to any DB.  (Some more on this is the file RATIONALE that comes with
the tarball.)

Cheers,
ChriS

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Christophe TROESTLER]

On Wed, 11 Jun 2003, Richard Jones <rich@annexia.org> wrote:
> 
> I wish I knew enough OCaml to make some really insightful comments
> about this, but in general it looks OK. One question: do you provide
> placeholders in the string argument to prepare, eg:
> 
> let query = dbh#prepare "select salary from employees where salary < ?" in

You can do that (even though it is not yet implemented in a safe way;
normally the DB should support that, in case it does not I'd like to
develop some functions to help).

> let res = query#execute 10000 in

Well, you cannot quite do that since (as you noted) you must know the
type of the argument.  Actually you have two choices: either you
convert your arguments manually to the [string option array] format or
you specify the type.  Here you can equivalently write either

let res = dbh#raw_exec query [| Some(string_of_int 10000) |] in

or

let res = dbh#exec query (t#int) 10000 in

> (Note I couldn't really understand why you would call dbh#raw_exec
> instead of some method on query itself, so I changed that).

We could.  I just want to create few objects.  But why not, this is to
be discussed.

> To make this type-safe you might need to add type information to the
> placeholders, eg. "select ... where salary < ?:int"

See above.

Regards,
ChriS

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Damien Pous]

Le jeu 12/06/2003 à 21:07, Christophe TROESTLER a écrit :
> Well, you cannot quite do that since (as you noted) you must know the
> type of the argument.  Actually you have two choices: either you
> convert your arguments manually to the [string option array] format or
> you specify the type.  Here you can equivalently write either
> let res = dbh#raw_exec query [| Some(string_of_int 10000) |] in
> or
> let res = dbh#exec query (t#int) 10000 in

with the CVS Printf.kprintf, you can do something like
<<
type res = int

class ['a] query (q: ('a, unit, string, res) format) = 
object(self)
  method execute = Printf.kprintf self#execute_string q
  method execute_string = String.length (* or sthg more interesting *)
end

class dbh = 
object
  method prepare : 'a. ('a, unit, string, res) format -> 'a query = 
    new query
end

let dbh = new dbh
let q1 = dbh#prepare "toto %d"
let q2 = dbh#prepare "titi %s %f"
let a1 = q1#execute 1000 
let a2 = q2#execute "arf" 3.3
>>

if you are not using the cvs version, you can still use exceptions, but
this becomes quite ugly

hope this helps,
damien


-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Richard Jones]

On Fri, Jun 13, 2003 at 10:12:33AM +0200, Damien Pous wrote:
> let q1 = dbh#prepare "toto %d"
> let q2 = dbh#prepare "titi %s %f"
> let a1 = q1#execute 1000 
> let a2 = q2#execute "arf" 3.3

Cute!

Rich.

-- 
Richard Jones, Red Hat Inc. (London) and Merjis Ltd. http://www.merjis.com/
http://www.annexia.org/ Freshmeat projects: http://freshmeat.net/users/rwmj
PTHRLIB is a library for writing small, efficient and fast servers in C.
HTTP, CGI, DBI, lightweight threads: http://www.annexia.org/freeware/pthrlib/

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Richard Jones]

When I implemented a DBI interface for pthrlib, I added a "serial"
method. This is an extension to the basic Perl DBI, but very useful.
Example (in pseudocode):

Table: create table employees (empid serial, name text, salary int);

let query = dbh#prepare "insert into employees (name) values ('John Smith')";;
query#execute;;
let empid = query#serial ("employees_empid_seq");;     (* returns, eg., 1 *)

The argument to serial is the sequence name, required for PostgreSQL,
but can be ignored on other databases.

Rich.

-- 
Richard Jones, Red Hat Inc. (London) and Merjis Ltd. http://www.merjis.com/
http://www.annexia.org/ Freshmeat projects: http://freshmeat.net/users/rwmj
MAKE+ is a sane replacement for GNU autoconf/automake. One script compiles,
RPMs, pkgs etc. Linux, BSD, Solaris. http://www.annexia.org/freeware/makeplus/

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Christophe TROESTLER]

On 13 Jun 2003, Damien Pous <Damien.Pous@ens-lyon.fr> wrote:
> 
> with the CVS Printf.kprintf, you can do something like
> [...]
> let dbh = new dbh
> let q1 = dbh#prepare "toto %d"
> let q2 = dbh#prepare "titi %s %f"
> let a1 = q1#execute 1000 
> let a2 = q2#execute "arf" 3.3

Well, this has several drawbacks.  First, the strings are not properly
escaped w.r.t. the database rules.  For example, you must write

INSERT INTO table(a,b) VALUES ('a\'b', NULL)

but with kprintf "a'b" will not be properly escaped.  (This is a
common mistake people do with the PHP approach.)  There is also no
nice way to handle NULL values, to support transparently all SQL92
types (what about DATE, TIMESTAMP, DECIMAL,...) nor to add new types
(want to decode Mysql sets; want to store closures?).  No optimization
of the query is possible.  Finally, this syntax is not compatible with
standard prepared queries (prepared queries are even mandatory for
Oracle I've been told) -- if the database supports prepared queries,
the DB mechanism should be used.

Cheers,
ChriS

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Benjamin Geer]

Christophe TROESTLER wrote:
> Here is the first proposal for a database independent interface (Dbi)
> for Ocaml.

It's great to see this!

Two suggestions.  It might be worth thinking, at an early stage, about 
how 2-phase commit (preferably via the XA standard) and connection 
pooling and can be integrated into this framework.  If you try to bolt 
these things on later (which is what happened in Java's JDBC interface), 
it could be more difficult.

Benjamin

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners

Re: [Caml-list] Dbi: proposal #1

[Benjamin Geer]

Christophe TROESTLER wrote:
 > Here is the first proposal for a database independent interface (Dbi)
 > for Ocaml.

It's great to see this!

Two suggestions.  It might be worth thinking, at an early stage, about 
how 2-phase commit (preferably via the XA standard) and connection 
pooling and can be integrated into this framework.  If you try to bolt 
these things on later (which is what happened in Java's JDBC interface), 
it could be more difficult.

Ben



________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email
Security System. For more information on a proactive email security
service working around the clock, around the globe, visit
http://www.messagelabs.com
________________________________________________________________________

-------------------
To unsubscribe, mail caml-list-request@inria.fr Archives: http://caml.inria.fr
Bug reports: http://caml.inria.fr/bin/caml-bugs FAQ: http://caml.inria.fr/FAQ/
Beginner's list: http://groups.yahoo.com/group/ocaml_beginners