overview.md

   1 sqlgg: SQL Guided (code) Generator
   2 ==================================
   3
   4 Problem
   5 -------
   6
   7 Writing database layer code is usually tedious and error-prone, due to the mix of different
   8 languages. SQL queries constructed dynamically need to bind external data (from application), and
   9 the resulting rowset must be decomposed into application native data. Data crossing these
  10 application-to-database boundaries is what causes troubles. One can factor out all common database
  11 communication code, hide the database under some application-specific abstraction, but one always
  12 needs to manually specify correspondence between SQL query binding slots (or resulting rowset
  13 columns) and code variables. This mapping should be updated manually every time SQL query is
  14 modified.
  15
  16 Solution
  17 --------
  18
  19 SQL parser and code generator which ensures that application code and database queries are in sync.
  20 It analyzes SQL query and determines the set of input parameters (values for INSERT, run-time
  21 substitution parameters) and the set of resulting columns (for SELECT). Then it generates the
  22 code in host language, matching query input and output to function parameters and return values with
  23 corresponding native data types. So basically you provide an SQL query and generator creates a
  24 function which takes the set of typed parameters as required to fill slots in a query. Generated
  25 code binds provided parameters into query and executes it. SELECT statements additionally return the
  26 collection of structures with fields representing columns of resulting rowset (or pass those
  27 structures to callback-function). The most fruitful consequence of such approach is that
  28 the host language compiler will itself check that functions generated from SQL queries will be
  29 called correctly (i.e. all parameters bound with correct types). So if you modify the query and
  30 forget to update the code -- the compiler will point on erroneous parts.
  31
  32 Example
  33 -------
  34
  35 Queries:
  36
  37     CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT);
  38     -- [sqlgg] name=Add
  39     INSERT INTO test(name,descr) VALUES;
  40     SELECT name,descr FROM test WHERE name = @name LIMIT @limit;
  41     SELECT name,z FROM
  42       (SELECT name,
  43               city || @delim || descr as y,
  44               max(length(city),random(*)) as z
  45        FROM test
  46        LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))
  47     WHERE z < @level;
  48
  49 Generated code (with some boilerplate omitted):
  50
  51     // DO NOT EDIT MANUALLY
  52
  53     // generated by sqlgg 0.2.0 (3a96042c)
  54
  55     #pragma once
  56
  57     template <class Traits>
  58     struct sqlgg
  59     {
  60       // CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT)
  61     public:
  62       static bool create_test(typename Traits::connection db)
  63       {
  64         return Traits::do_execute(db,_T("CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT)"),typename Traits::no_params());
  65       }
  66
  67       // INSERT INTO test(name,descr) VALUES
  68     private:
  69       struct params_1;
  70
  71     public:
  72       static bool Add(typename Traits::connection db, typename Traits::Text const& name, typename Traits::Text const& descr)
  73       {
  74         return Traits::do_execute(db,_T("INSERT INTO test(name,descr) VALUES (?,?)"),params_1(name, descr));
  75       }
  76
  77       // SELECT name,descr FROM test WHERE name = @name LIMIT @limit
  78     private:
  79       template <class T>
  80       struct output_2
  81       {
  82         static void of_stmt(typename Traits::statement stmt, T& obj)
  83         {
  84           Traits::get_column_Text(stmt, 0, obj.name);
  85           Traits::get_column_Text(stmt, 1, obj.descr);
  86         }
  87       }; // struct output_2
  88
  89     private:
  90       struct params_2;
  91
  92     public:
  93       struct data_2
  94       {
  95         typename Traits::Text name;
  96         typename Traits::Text descr;
  97       }; // struct data_2
  98
  99     public:
 100       template<class T>
 101       static bool select_2(typename Traits::connection db, T& result, typename Traits::Text const& name, typename Traits::Int const& limit)
 102       {
 103         return Traits::do_select(db,result,_T("SELECT name,descr FROM test WHERE name = @name LIMIT @limit"),output_2<typename T::value_type>(),params_2(name, limit));
 104       }
 105
 106       // SELECT name,z FROM
 107     //   (SELECT name,
 108     //           city || @delim || descr as y,
 109     //           max(length(city),random(*)) as z
 110     //    FROM test
 111     //    LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))
 112     // WHERE z < @level
 113     private:
 114       template <class T>
 115       struct output_3
 116       {
 117         static void of_stmt(typename Traits::statement stmt, T& obj)
 118         {
 119           Traits::get_column_Text(stmt, 0, obj.name);
 120           Traits::get_column_Int(stmt, 1, obj.z);
 121         }
 122       }; // struct output_3
 123
 124     private:
 125       struct params_3;
 126
 127     public:
 128       struct data_3
 129       {
 130         typename Traits::Text name;
 131         typename Traits::Int z;
 132       }; // struct data_3
 133
 134     public:
 135       template<class T>
 136       static bool select_3(typename Traits::connection db, T& result, typename Traits::Text const& delim, typename Traits::Int const& id, typename Traits::Int const& level)
 137       {
 138         return Traits::do_select(db,result,_T("SELECT name,z FROM \
 139       (SELECT name,\
 140               city || @delim || descr as y,\
 141               max(length(city),random(*)) as z \
 142        FROM test \
 143        LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))\
 144     WHERE z < @level"),output_3<typename T::value_type>(),params_3(delim, id, level));
 145       }
 146
 147     }; // struct sqlgg
 148
 149 Things to note above:
 150
 151 1. The generated code is parametrized by database-specific class `Traits`. It specifies the
 152                 correspondence between SQL and native types, provides types for database connection and other
 153                 details. `Traits` also implements actual code to execute statements. It should be implemented
 154                 once for every specific database API.
 155 2. The annotation `[sqlgg] name=Add` before the INSERT query specifies the name of the generated
 156     function. NB: there is no need to write (?,?) after VALUES.
 157 1. `Add()` function takes two data parameters, the values to INSERT into table (`params_1` is the
 158     boilerplate code to bind these parameters into query).
 159 3. `select_2()` returns data via `result` parameter. Hidden auxiliary class `output_2` is used to
 160     bind columns of rowset to the fields of `T::value_type`, which should have fields `name` and
 161     `descr` of type `Traits::Text` (otherwise it will fail to compile). For convenience a structure
 162     satisfying the requirements for output type is generated alongside the function, `data_2` in
 163     this particular case, so `std::vector<data_2>` for `result` is fine.
 164 4. The types of parameters for `select_2` were inferred correctly (`limit` is `Int` and `name` is
 165     `Text`. SQL is not a statically-typed language so the inferred types are based on some
 166     reasonable assumptions.
 167 5. Statement of arbitrary depth across many tables should be supported.
 168 6. Statements are checked for correctness as far as generator is concerned, so it will detect
 169                 syntax errors, non-existent columns in expressions, mismatched columns in compound statements,
 170                 ambiguous column names etc.
 171
 172 Details
 173 -------
 174
 175 The idea is that the generator should take care only of semantic binding between SQL and code sides,
 176 being as unobtrusive as possible. So the choice of the specific database and API is a programmer's
 177 choice. Similarly, queries to the database are expressed in plain SQL, so that the generator can be
 178 easily plugged in any existing project -- just move all SQL statements used in the code to separate
 179 file and feed it to generator.
 180
 181 Distinguishing feature of **sqlgg** is that it starts off with SQL queries, not object models
 182 or SQL table descriptions.
 183
 184 This is work in progress and there is plenty of room for improvement. For now the status of this
 185 project is **works for me** .  I use it for some simple database-access code with
 186 [sqlite3](http://sqlite.org) engine (using suitable [sqlite3_traits](sqlite3_helper.hpp) helper).
 187 This project was started when I found myself editing existing code with tons of C++ wrappers for SQL
 188 queries, each binding several parameters and decomposing results.
 189
 190 For now it can generate C++ and OCaml code. Generated C++ code is parametrized with template class
 191 for database specific code. Generated OCaml code is a functor
 192 `module Sqlgg (T:`[Sqlgg\_traits.M](sqlgg_traits.ml)`)` (sample [sqlgg\_sqlite3](sqlgg_sqlite3.ml)
 193 for [OCaml-SQLite3][]).
 194
 195 [OCaml-SQLite3]:        http://caml.inria.fr/cgi-bin/hump.en.cgi?contrib=471
 196
 197 A framework should be a tool to save writing repetitive code, rather than a tool you use to avoid
 198 understanding 'what lies beneath'. <http://c2.com/cgi/wiki?PerniciousIngrownSql>
 199
 200 Try it [online](sql.cgi).
 201
 202 TODO
 203 ----
 204
 205 * distinguish predicates and expressions (research)
 206 * choose better names for some common cases (WHERE id = ? etc)
 207 * fix line numbers in error output
 208 * resolve conflicts in grammar, check precedences
 209 * type-inference is too primitive
 210 * detect statements on single tables and group the corresponding generated code in one class
 211 * check names (functions and bindings) for uniqueness
 212 * support/test other SQL engines
 213 * generate code for more languages
 214 * read SQL spec
 215 * type check expressions
 216
 217 ----
 218 2009-05-16
 219
 220 <style>
 221 code { font-family: monospace; }
 222 pre { background-color: #eee; border: 1px solid #0f0; }
 223 :not(pre) > code { font-size: 1em; }
 224 </style>