Module Ask.Sql

SQL helpers.

Statements

module Stmt : sig ... end

Typed SQL statements.

Schema definition

type Table.param +=
| Table of string
| Table_constraint of string(*

Additional table parameters. See also Table.param.

  • Sql sql is the complete CREATE TABLE statement. All other parameters are ignored, you are in control.
  • Sql_constraint sql is an SQL table constraint added at the end of the table definition. Can be repeated.
*)
type Col.param +=
| Col of string
| Col_constraint of string(*

The type for column parameters. See also Col.param.

  • Col sql is the full SQL column definition between the name and the comma (includes the type). All other parameters are ignored, you are in control.
  • Col_constraint sql appends sql at the end of the column definition. Can be repeated.
*)
val drop_table : ?schema:string -> ?if_exists:bool -> 'a Table.t -> unit Stmt.t

drop_table ~if_exist ~schema t is an SQL DROP TABLE statement to drops table t of schema schema. If if_exists is true no error is reported if the table does not exist (default to false).

val create_table : ?schema:string -> ?if_not_exists:bool -> 'a Table.t -> unit Stmt.t

create_table t is an SQL CREATE TABLE statement for t. If if_not_exists is true no error is reported if the table does not exist (defaults to false).

val create_index : ?schema:string -> ?if_not_exists:bool -> 'a Table.t -> 'a Index.t -> unit Stmt.t

create_index t i is an SQL CREATE INDEX statement for index i on table t. If if_not_exists is true no error is reported if the index does not exist (defaults to false).

val drop_index : ?schema:string -> ?if_exists:bool -> 'a Table.t -> 'a Index.t -> unit Stmt.t

drop_index t i is an SQL DROP INDEX statement for index i on table t. If if_exist is true not error is reported if the index does not exist (defaults to false).

val create_schema : ?schema:string -> ?drop_if_exists:bool -> Table.v list -> unit Stmt.t

create_schema ~drop_if_exists ts are multiple SQL statements to create the tables ts and their indices if they don't exist unless drop_if_exists is true in which case drop_table and drop_index statements are issued to clear the way (defaults to false).

Make sure to use Ask_sqlite3.exec otherwise only the first statement gets executed, wrapping the whole thing in Ask_sqlite3.with_transaction is a good idea as well.

Inserting, updating and deleting

val insert_into : ?schema:string -> ?ignore:'r Col.v list -> 'r Table.t -> 'r -> unit Stmt.t

insert_into ~ignore t is an SQL INSERT INTO statement which inserts i t values draw from an value values drawn from a provided OCaml table row. Columns mentioned in col of the row are ignored for the insertion.

val insert_into_cols : ?schema:string -> ?ignore:'r Col.v list -> 'r Table.t -> 'r Col.value list -> unit Stmt.t

insert_into_cols is like insert_into but uses the given column values for the insertion.

val update : ?schema:string -> 'r Table.t -> set:'r Col.value list -> where:'r Col.value list -> unit Stmt.t

update_cols t ~set:cols ~where is an SQL UPDATE statement which updates columns values cols of the rows of t where columns have all the values in where (AND). FIXME. The where should become ('r Bag.t -> bool value).

val delete_from : ?schema:string -> 'r Table.t -> where:'r Col.value list -> unit Stmt.t

delete_from t ~where is an SQL DELETE FROM statement which deletes rows where columns have all the values in where (AND). FIXME. The where should become ('r Bag.t -> bool value).

Bags

FIXME. Can't we get rid of the 'a Row.t argument in of_bag. Related to the comment of specializing yield on Row.t. Maybe the Bag.t combinator should take a row rather than an arbitrary function.

val of_bag : 'a Row.t -> ('a'b) Bag.t -> 'a Stmt.t
val of_bag' : 'a Table.t -> ('a'b) Bag.t -> 'a Stmt.t
module Bag : sig ... end

FIXME can't we merge that into Stmt ?

TODO multibackend SQL support

The current interface won't do it for multiple backends. We need to be able to specify the SQL dialect at some point. The questions is where to do it so that it remains efficient for backend prepared statement caches but convenient for clients.

One idea is to back Sql.Stmt.t by an SQL AST rather than by an constant (sqlite3) SQL string as we have now (!Sql.Stmt.src). Backends would then generate the SQL from the AST, the nice thing is that except for actual execution there would be no need for backend abstraction for statement definition (no additional parameter or functorization).

Points to consider.