[Caml-list] [ANN?] Sequoia: MySQL query builder via syntax tree manipulation

[Caml-list] [ANN?] Sequoia: MySQL query builder via syntax tree manipulation

[Andre Nathan]

[-- Attachment #1: Type: text/plain, Size: 2414 bytes --]

Hello

So this was mostly an experiment for me to learn how to work with PPX
extensions. It's a hack that I'm sure will affect people's sensibilities
about what should be done with PPX, though maybe someone will find it
useful. It's far from complete so I didn't bother creating an OPAM
package yet.

  https://github.com/andrenth/sequoia

The idea is to have a safe query builder for MySQL. You map a database
using an OCaml module like so:

module MyDB = struct
  type my_table = {
     id   : int    [@sql];
     name : string [@sql];
  } [@@sql]
end [@@sql]

The @sql/@@sql attributes can take an extra string that will map to the
real MySQL column/table/database names in case they differ from their
OCaml names.

With that definition you can then build a query:

let%sql query =
  select
    t.name
  from
    MyDB.my_table as_ t

Which will build the query below via `Sequoia.Select.to_string query`:

  SELECT `t`.`name` FROM `MyDB`.`my_table` AS `t`

Variable substitution is supported using the ref syntax:

let pat = Sequoia.string "a%"
let%sql query =
  select
    t.name
  from
    MyDB.my_table as_ t
  where
    (t.name like !pat && t.id > 1)

If you reference a database, table or column that isn't declared via the
@sql/@@sql attributes or call a nonexistent MySQL function, you get a
compile-time error:

let%sql query =
  select
    (frob(t.name))
  from
    MyDB.my_table as_ t

  => This expression has type [> `Frob of [> `Column of string list ] ]
     but an expression was expected of type Sequoia.Function.t
     The second variant type does not allow tag(s) `Frob

If you try to select a column from a table that's not in a from or join
clause, you get an error too:

let%sql query = select MyDB.my_table.name

  => Table `my_table` not selected in query

There are many problems though. Off the top of my head:

* Everything must be lowercase;
* Error reporting sucks;
* You need parentheses around OCaml expressions that wouldn't be needed
  in real SQL;
* Lots of MySQL functions are not defined yet so you'll get bogus
  errors;
* Due to syntax clash you need stuff like "as_" or "in_";
* Need to find a way to make it work when the DB definitions are in
  separate files;
* Conversion to string doesn't indent the query and is really
  parenthesis-happy.
* Everybody hates MySQL.

Cheers!
Andre


[-- Attachment #2: OpenPGP digital signature --]
[-- Type: application/pgp-signature, Size: 490 bytes --]