monopampam
My own corner of monopam
Star
Fork
Watch
monopampam ocaml-sql
3 folders · 6 files
doc
lib
test
.ocamlformat
dune
dune-project
LICENSE.md
README.md
sql.opam
README.md

sql #

A SQLite-dialect SQL frontend and statement engine for any storage backend.

An OCaml application that wants SQL usually links the SQLite C library and takes its storage format along with its query language. This package keeps the two apart. It parses the SQLite dialect, resolves names, and lowers queries to the register VM of the catalog package, which does not depend on any backend. The queries then run over any store that implements Catalog.Spi, such as a set of tables in memory, a B-tree file in the SQLite format, or an Irmin tree. The value semantics (type affinity, collation, three-valued logic, the built-in functions, date and time, and json1) follow the sqlite3 documentation.

Lowering a query compiles its resolved tree into a lower-level form. Here that form is first the positional intermediate representation Catalog.Ir and then a program for Catalog.Vdbe, a small virtual machine modelled on SQLite's VDBE. Its instructions work on numbered registers: open a cursor, read a column into a register, compare two registers, emit a row. A backend that implements the cursor and write contract of Catalog.Spi can then run every query without an SQL executor of its own.

Two terms from SQLite need a definition. Type affinity is the rule that a column declares a preferred type (INTEGER, REAL, TEXT, BLOB or NUMERIC) and that an inserted value is converted to it when nothing is lost; values are not required to have that type. Three-valued logic is how SQL treats NULL: a comparison with NULL is unknown, neither true nor false, and WHERE keeps only the rows where the condition is true.

Installation #

$ opam repo add samoht https://tangled.org/gazagnaire.org/opam-overlay.git
$ opam install sql

Usage #

Sql.Engine.exec store text parses the statements in text, separated by ;, and runs them in order over the Catalog.Spi.t store. It returns the rows of the last statement (Rows) or Done. A backend with its own write path builds a read view with Sql.Engine.catalog and calls Sql.Engine.run_select for each SELECT.

let store = Catalog.of_mem []

let () =
  match
    Sql.Engine.exec store
      "CREATE TABLE t (a INTEGER, b TEXT);\n\
       INSERT INTO t VALUES (1, 'x'), (2, 'y'), (3, 'z');\n\
       SELECT b, a * 10 FROM t WHERE a > 1 ORDER BY a DESC"
  with
  | Sql.Engine.Rows rows ->
      assert (
        rows
        = Catalog.Value.[ [ Text "z"; Int 30L ]; [ Text "y"; Int 20L ] ])
  | Sql.Engine.Done -> assert false

SELECT supports the whole read dialect: joins, GROUP BY and HAVING, window functions, compound queries, recursive WITH, correlated and FROM subqueries, and table-valued functions. Engine.exec also runs CREATE TABLE, DROP TABLE, INSERT, UPDATE and DELETE. It does not yet enforce CHECK constraints, triggers, foreign keys or DEFAULT values, and it does not run transactions; the sqlite package adds those on top of this engine for its B-tree files. The plan is for the engine to enforce all of them itself, over any Catalog.Spi store, as doc/engine-lift.md describes.

API #

The main modules are:

  • Sql.Lexer parses SQL text into Sql.Ast statements, and Sql.Render prints an AST back as SQL that parses again to the same query.
  • Sql.Resolve resolves names and correlated subqueries against a schema, and Sql.Plan chooses the index access path of each source of a SELECT.
  • Sql.Lower lowers a resolved query to Catalog.Ir, and Sql.Engine executes statements over a Catalog.Spi.t.
  • Sql.Value_op, Sql.Func, Sql.Datetime, Sql.Jsonval, Sql.Collation and Sql.Window implement sqlite3's operators, built-in functions, date and time functions, json1 values, collating sequences and window functions.
  • Sql_shell (library sql.shell) is an interactive shell that reads one line at a time; a backend instantiates it with its own way of running statements.

License #

ISC. See LICENSE.md.