tweak

[sqlgg.git] / overview.md

<title>sqlgg: SQL Guided (code) Generator</title>
<span class="right border">[ygrek.org.ua](http://ygrek.org.ua/) / [p](http://ygrek.org.ua/p/) / [sqlgg](http://ygrek.org.ua/p/sqlgg/)</span>

sqlgg: SQL Guided (code) Generator
==================================

*SQL query parser and binding code generator for C#, C++, Java, Objective Caml.*

* [Problem](#problem)
* [Solution](#solution)
* [Complete example](#example)
* [Details](#details)
* [Download](#download)
* [TODO](#todo)

<a id="problem"/>
Problem
-------

Writing database layer code is usually tedious and error-prone, due to the mix of different
languages. SQL queries constructed dynamically need to bind external data (from application), and
the resulting rowset must be decomposed into application native data. The impedance mismatch between
database layer and application code, as well as the absence of static (compile-time) checking, 
frequently leads to runtime failures (and sometimes even security holes). One
can factor out all common database communication code, hide the database under some
application-specific abstraction, but one always needs to manually specify correspondence between
SQL query binding slots (or resulting rowset columns) and code variables. This mapping should be
updated manually every time SQL query is modified.

<a id="solution"/>
Solution
--------

SQL query parser and code generator which ensures that application code and database queries are in
sync. It analyzes SQL query and determines the set of input parameters (values for INSERT, run-time
substitution parameters) and the set of resulting columns (for SELECT). Then it generates the code
in host language, mapping query parameters on function arguments with corresponding native data
types. So basically you provide an SQL query and generator creates a function which takes the set of
typed parameters as required to fill slots in a query. Generated code binds provided parameters into
query and executes it. The code for SELECT statements additionally invokes a strongly-typed callback
function for every row of the resulting rowset. The most fruitful consequence of such
approach is that the host language compiler will itself check that functions generated from SQL
queries will be called correctly (i.e. all parameters bound with correct types). So if you modify
the query and forget to update the code -- the compiler will point on erroneous parts.

<a id="example"/>
Complete example
----------------

Suppose we have the database of some money relationships for the group of people. We use the
following SQL tables and queries:

    -- @create_person
    CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,surname TEXT);
    -- @add_person
    INSERT INTO person (name,surname) VALUES;

    -- @create_money
    CREATE TABLE money (src INTEGER, dst INTEGER, amount INTEGER);
    -- @add_money
    INSERT INTO money VALUES;

    -- @calc_total
    SELECT name || ' ' || surname AS fullname, SUM(amount) as total FROM person JOIN money ON src = id GROUP BY id;
    -- @list_donors
    SELECT DISTINCT surname FROM person JOIN money ON src = id AND dst = (SELECT id FROM person WHERE surname LIKE ?) LIMIT ?;

    DROP TABLE IF EXISTS person;
    DROP TABLE IF EXISTS money;

Generate the binding C++ code (boilerplate omitted, only function prototypes shown):
<span class="right also">
(corresponding [C#][demo_csharp_gen] [C++][demo_cxx_gen] [Java][demo_java_gen]
[OCaml][demo_caml_gen] [XML][demo_xml_gen])
</span>

    // DO NOT EDIT MANUALLY
    // 
    // generated by sqlgg 0.2.2+ (f5b76ac7) on 2009-05-30T19:22Z
    // visit http://ygrek.org.ua/p/sqlgg/

    #pragma once

    template <class Traits>
    struct sqlgg
    {
      struct create_person
      {
        create_person(typename Traits::connection db);
        bool operator()();
      }; // struct create_person

      struct add_person
      {
        add_person(typename Traits::connection db);
        bool operator()(typename Traits::Text const& name, typename Traits::Text const& surname);
      }; // struct add_person

      struct create_money
      {
        create_money(typename Traits::connection db);
        bool operator()();
      }; // struct create_money

      struct add_money
      {
        add_money(typename Traits::connection db);
        bool operator()(typename Traits::Int const& src, typename Traits::Int const& dst, typename Traits::Int const& amount);
      }; // struct add_money

      struct calc_total
      {
        calc_total(typename Traits::connection db);
        template<class T>
        bool operator()(T result);
      }; // struct calc_total

      struct list_donors
      {
        list_donors(typename Traits::connection db);
        template<class T>
        bool operator()(typename Traits::Text const& _0, typename Traits::Int const& _1, T result);
      }; // struct list_donors

      struct drop_person
      {
        drop_person(typename Traits::connection db);
        bool operator()();
      }; // struct drop_person

      struct drop_money
      {
        drop_money(typename Traits::connection db);
        bool operator()();
      }; // struct drop_money

      create_person create_person;
      add_person add_person;
      create_money create_money;
      add_money add_money;
      calc_total calc_total;
      list_donors list_donors;
      drop_person drop_person;
      drop_money drop_money;

      sqlgg(typename Traits::connection db) : create_person(db), add_person(db), create_money(db), add_money(db), calc_total(db), list_donors(db), drop_person(db), drop_money(db)
      {
      }
    }; // struct sqlgg

Things to note above:

2. Syntax. All queries are written in plain SQL. Every statement should be terminated with
    semicolon. There is no need to write (?,?) after VALUES in INSERT statement. The annotation
    `[sqlgg] name=function_name` (or simply `@function_name`) before the query specifies the name
    of the generated function (this annotation is optional but very convenient).
1. The generated code is parametrized by database-specific class `Traits`. It specifies the
                correspondence between SQL and native types, provides types for database connection and
    implements actual code to execute statements. `Traits` should be implemented
                once for every specific database API.
1.  Each statement has a corresponding `struct` which takes connection object as a constructor
    argument. This allows to reuse prepared statements several times (if database supports this 
    feature).
1. `add_money()` function call operator takes three data parameters -- the values to INSERT into 
    table (note the names and types).
3. `calc_total()` returns data via `result` parameter, which is a callback that gets executed for
    each row of the resulting rowset, with statically typed parameters binding each column of a row.
    In this case the parameters are `fullname` of type `Traits::Text` and `total` of type 
    `Traits::Int`.
4. The types of parameters for `list_donors` were inferred correctly (`limit` is `Int` and `surname`
    is `Text`. SQL is not a statically-typed language so the inferred types are based on some
    reasonable assumptions.
5. Statements of arbitrary depth across many tables are supported.
6. Statements are checked for correctness as far as generator is concerned, so it will detect
                syntax errors, non-existent columns in expressions, mismatched columns in compound statements,
                ambiguous column names etc.

Then manually-written C++ code boils down to (without error-checking):
<span class="right also">
(corresponding [C#][demo_csharp_mysql] [C++][demo_cxx_sqlite3] [Java][demo_java_mysql]
[OCaml][demo_caml_sqlite3])
</span>

    #include "../impl/sqlite3_traits.hpp" // sqlite3 traits
    #include "demo_cxx_gen.hpp" // generated
    #include <iostream>
    #include <vector>

    using namespace std;

    typedef sqlgg<sqlite3_traits> gen_t;
    typedef long long int64;

    struct output_transfers
    {
      void operator()(std::string const& fullname, int total)
      {
         cout << fullname << " = " << total << endl;
      }
    };

    struct output_donors
    {
      void operator()(std::string const& surname)
      {
         cout << surname << endl;
      }
    };

    int main()
    {
      sqlite3* db = NULL;
      sqlite3_open(":memory:", &db);

      gen_t gen(db);

      // create tables
      gen.create_person();
      gen.create_money();

      // add all person records
      gen.add_person("John","Black");
      int64 john = sqlite3_last_insert_rowid(db);
      gen.add_person("Ivan","Petrov");
      int64 ivan = sqlite3_last_insert_rowid(db);
      gen.add_person("Sancho","Alvares");
      int64 sancho = sqlite3_last_insert_rowid(db);

      // add money relations
      gen.add_money(john,ivan,200);
      gen.add_money(john,sancho,100);
      gen.add_money(john,sancho,250);
      gen.add_money(sancho,ivan,300);

      // summarize by person
      cout << "Total transfers:" << endl;
      gen.calc_total(output_transfers());

      // list donors
      cout << "Donors:" << endl;
      gen.list_donors("petrov",100,output_donors());

      // properly close database
      sqlite3_close(db);

      return 0;
    }

The code is straightforward and free of most database-specific details. More importantly it is
statically checked by the compiler -- suppose we change the database schema and add one more field
to `money` table (e.g. timestamp of transfer). Compilation rightfully fails:

    demo_cxx.cpp: In function `int main()':
    demo_cxx.cpp:29: error: no matching function for call to
    `sqlgg<sqlite3_traits>::add_money(sqlite3*&, int64&, int64&, int)'
    demo_cxx_gen.hpp:75: note: candidates are: static bool sqlgg<Traits>::add_money(typename
    Traits::connection, const typename Traits::Int&, const typename Traits::Int&, const typename
    Traits::Int&, const typename Traits::Int&) [with Traits = sqlite3_traits]

<a id="details"/>
Details
-------

Distinguishing feature of **sqlgg** is that it starts off with actual SQL queries, not object models
or SQL table descriptions. Ideally, it generates {meta,template,abstract,generic} code which is later
specialized for the actual database/environment by the means of the target language. The
generated code doesn't require any special runtime support and doesn't impose any restrictions on
the application.

The main idea is that the generator should take care only of semantic binding between SQL and code
sides, being as unobtrusive as possible. So the choice of the specific database and API is a
programmer's choice. Similarly, queries to the database are expressed in plain SQL, so that the
generator can be easily plugged in any existing project -- just move all SQL statements used in the
code to a separate file and feed it to generator. The generated code can be easily inspected just as
any other code in the project, and you can even edit it manually if such need arises (though it is
highly not recommended). In order to keep code and database layer in sync
the generator must be invoked every time SQL queries are modified.

For now the status of this project is **works for me** . It is evolving and there is
plenty of room for improvement. The generated code and traits interfaces may change from time to
time. I am interested in opinions on output code structure/naming best fitting the target
language/platform.

This project was started when I found myself editing existing code
with tons of C++ wrappers for SQL queries, each binding several parameters and decomposing results.
I used it for C++ desktop applications with [sqlite3](http://sqlite.org) engine and 
C#/[Mono](http://mono-project.com) server-side 
component with [mysql](http://dev.mysql.com) database.

**Sqlgg** understands the subset of the standard SQL language with arbitrary database-specific
extensions. The goal is to cover as much SQL as possible in order to accept most of the real-world
SQL statements unmodified.

Available output languages:

* **C#**: code for System.Data ([generated code example][demo_csharp_gen],
  [usage example][demo_csharp_mysql]).
* **C++**: code is parametrized with template class for database specific code
  ([generated code example][demo_cxx_gen], [sqlite3 traits][sqlite3_cxx],
  [usage example (sqlite3)][demo_cxx_sqlite3], [mysql traits][mysql_cxx],
  [usage example (mysql)][demo_cxx_mysql]).
* **Java**: code for JDBC ([generated code example][demo_java_gen],
        [usage example][demo_java_mysql]).
* **OCaml**: functor parametrized with module for database specific code
        [Sqlgg\_traits.M][sqlgg_caml] ([generated code example][demo_caml_gen],
  [OCaml-SQLite3 traits][sqlite3_caml], [usage example][demo_caml_sqlite3]).
* **XML**: input parameters and rowset schema with types and names -- everything needed
  to further generate code (e.g. using XSLT) in some other language
  ([generated xml example][demo_xml_gen])

[OCaml-SQLite3]:        http://caml.inria.fr/cgi-bin/hump.en.cgi?contrib=471

[sqlite3_cxx]:  impl/sqlite3_traits.hpp
[mysql_cxx]: impl/mysql_traits.hpp
[sqlite3_caml]: impl/sqlgg_sqlite3.ml
[sqlgg_caml]: impl/sqlgg_traits.ml

[demo_cxx_gen]: demo/demo_cxx_gen.hpp
[demo_caml_gen]: demo/demo_caml_gen.ml
[demo_xml_gen]: demo/demo_xml_gen.xml
[demo_java_gen]: demo/demo_java_gen.java
[demo_csharp_gen]: demo/demo_csharp_gen.cs

[demo_cxx_sqlite3]:     demo/demo_cxx.cpp
[demo_cxx_mysql]: demo/demo_cxx_mysql.cpp
[demo_caml_sqlite3]: demo/demo_caml.ml
[demo_java_mysql]: demo/demo_java.java
[demo_csharp_mysql]: demo/demo_csharp.cs

<a id="download"/>
Download
--------

sqlgg 0.2.2 for [Windows](dist/sqlgg-0.2.2.zip) and [Linux](dist/sqlgg-0.2.2.tar.gz) (released on 29 June 2009). [Changes history](dist/changelog).

Try it [online](sqlgg.cgi).

Get the [source](http://repo.or.cz/w/sqlgg.git).
<!--: `git clone git://repo.or.cz/sqlgg.git`
([rss](http://repo.or.cz/w/sqlgg.git?a=rss))-->

<a id="todo"/>
TODO
----

* allow to parametrize SQL syntax itself (ORDER BY ASC|DESC) : unsafe and enumeration
* allow to tolerate errors in query
* special code for queries returning single values (COUNT(*) and the like)
* support BULK INSERTs (how?)
* decide what to do with unquoted identifiers matching keywords
* provide ways to extend lexer with tokens in runtime
* split overview into several pages
* some database API are inadequately strict about data types (notably ADO.NET), need native-type annotations in queries
* choose better names for some common cases (WHERE id = ? etc)
* fix line numbers in error output
* enhance error reporting
* calculate types more precisely, type-inferrer is too primitive
* track NULLs
* detect statements on single tables and group the corresponding generated code together
* check function names for uniqueness
* support/test other SQL engines
* generate code for more languages
* read SQL spec
* distinguish predicates and expressions
* type check expressions

----
2009-07-12
<span class="right">[sqlgg&#x40;ygrek&#x2E;org&#x2E;ua](mailto:sqlgg&#x40;ygrek&#x2E;org&#x2E;ua)</span>

<style>
.right { position: absolute; right: 1em; }
.also { margin-top: 1em; font-style: italic; font-size: 80%; }
.border { padding: 0.5em; border: 1px solid black; }
code { font-family: monospace; }
pre { background-color: #eee; border: 1px solid #0f0; }
:not(pre) > code { font-size: 1em; }
html { font-family: sans-serif; }
</style>