overview.md

   1 SQL to C++ 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 manully 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 C++
  22 code which structures input and output values together as function parameters and assignes
  23 corresponding native data types. So basically you provide an SQL query and generator creates a C++
  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. The most fruitful
  27 consequence of such approach is that the C++ compiler itself guarantees that SQL query will have all
  28 parameters bound with correct types. So if you modify the query and forget to update the code -- the
  29 compiler will point on errorneous parts.
  30
  31 Example
  32 -------
  33
  34 Queries:
  35
  36     CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT);
  37     -- [sql2cpp] name=Add
  38     INSERT INTO test(name,descr) VALUES;
  39     SELECT name,descr FROM test WHERE name = @name LIMIT @limit;
  40     SELECT name,z FROM
  41       (SELECT name,
  42               city || @delim || descr as y,
  43               max(length(city),random(*)) as z
  44        FROM test
  45        LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))
  46     WHERE z < @level;
  47
  48 Generated code (with some boilerplate omitted):
  49
  50     // DO NOT EDIT MANUALLY
  51
  52     // generated by sql2cpp
  53
  54     #pragma once
  55
  56     template <class Traits>
  57     struct sql2cpp
  58     {
  59       // CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT)
  60     public:
  61       static bool create_test(typename Traits::connection db)
  62       {
  63         return Traits::do_execute(db,_T("CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT)"),typename Traits::no_params());
  64       }
  65
  66       // INSERT INTO test(name,descr) VALUES
  67     public:
  68       static bool Add(typename Traits::connection db, typename Traits::Text const& name, typename Traits::Text const& descr)
  69       {
  70         return Traits::do_execute(db,_T("INSERT INTO test(name,descr) VALUES (?,?)"),params_1(name, descr));
  71       }
  72
  73       // SELECT name,descr FROM test WHERE name = @name LIMIT @limit
  74     private:
  75       template <class T>
  76       struct output_2
  77       {
  78         static void of_stmt(typename Traits::statement stmt, T& obj)
  79         {
  80           Traits::get_column_Text(stmt, 0, obj.name);
  81           Traits::get_column_Text(stmt, 1, obj.descr);
  82         }
  83       }; // struct output_2
  84
  85     public:
  86       struct data_2
  87       {
  88         typename Traits::Text name;
  89         typename Traits::Text descr;
  90       }; // struct data_2
  91
  92     public:
  93       template<class T>
  94       static bool select_2(typename Traits::connection db, T& result, typename Traits::Text const& name, typename Traits::Int const& limit)
  95       {
  96         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));
  97       }
  98
  99       // SELECT name,z FROM
 100     //   (SELECT name,
 101     //           city || @delim || descr as y,
 102     //           max(length(city),random(*)) as z
 103     //    FROM test
 104     //    LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))
 105     // WHERE z < @level
 106     private:
 107       template <class T>
 108       struct output_3
 109       {
 110         static void of_stmt(typename Traits::statement stmt, T& obj)
 111         {
 112           Traits::get_column_Text(stmt, 0, obj.name);
 113           Traits::get_column_Int(stmt, 1, obj.z);
 114         }
 115       }; // struct output_3
 116
 117     public:
 118       struct data_3
 119       {
 120         typename Traits::Text name;
 121         typename Traits::Int z;
 122       }; // struct data_3
 123
 124     public:
 125       template<class T>
 126       static bool select_3(typename Traits::connection db, T& result, typename Traits::Any const& delim, typename Traits::Int const& id, typename Traits::Int const& level)
 127       {
 128         return Traits::do_select(db,result,_T("SELECT name,z FROM \
 129       (SELECT name,\
 130               city || @delim || descr as y,\
 131               max(length(city),random(*)) as z \
 132        FROM test \
 133        LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))\
 134     WHERE z < @level"),output_3<typename T::value_type>(),params_3(delim, id, level));
 135       }
 136
 137     }; // struct sql2cpp
 138
 139 Things to note above:
 140
 141 1. The generated code is parametrized by database-specific class `Traits`. It specifies the
 142                 correspondence between SQL and native types, provides types for database connection and other
 143                 details. `Traits` also implements actual code to execute statements. It should be implemented
 144                 once for every specific database API.
 145 2. The annotation `[sql2cpp] name=Add` before the INSERT query specifies the name of the generated
 146     function. NB: there is no need to write (?,?) after VALUES.
 147 1. `Add()` function takes two data parameters, the values to INSERT into table (`params_1` is the
 148     boilerplate code to bind these parameters into query).
 149 3. `select_2()` returns data via `result` parameter. Hidden auxiliary class `output_2` is used to
 150     bind columns of rowset to the fields of `T::value_type`, which should have fields `name` and
 151     `descr` of type `Traits::Text` (otherwise it will fail to compile). For convenience a structure
 152     satisfying the requirements for output type is generated alongside the function, `data_2` in
 153     this particular case, so `std::vector<data_2>` for `result` is fine.
 154 4. The types of parameters for `select_2` were inferred correctly (`limit` is `Int` and `name` is
 155     `Text`. SQL is not a statically-typed language so the inferred types are based on some
 156     reasonable assumptions.
 157 5. Statement of arbitrary depth across many tables should be supported.
 158 6. Statements are checked for correctness as far as generator is concerned, so it will detect
 159                 syntax errors, non-existant columns in expressions, mismatched rowsets in compound statements,
 160                 ambigous column names etc.
 161
 162 Details
 163 -------
 164
 165 The idea is that the generator should take care only of semantic binding between SQL and code sides,
 166 being as unobtrusive as possible. So the choice of the specific database and API is a programmer's
 167 choice. Similarly, queries to the database are expressed in plain SQL, so that the generator can be
 168 easily plugged in any existing project -- just move all SQL statements used in the code to separate
 169 file and feed it to generator.
 170
 171 This is work in progress and there is plenty of room for improvement. For now the status of this
 172 project is *works for me* .  It is used for some simple database-access code with
 173 [sqlite3](http://sqlite.org) engine (using suitable `sqlite3_traits` helper).
 174
 175 Online version will be made available soon.
 176
 177 TODO
 178 ----
 179
 180 * detect statements on single tables and group the corresponding generated code in one class
 181 * check names (functions and bindings) for uniqueness
 182 * support/test other SQL engines
 183 * generate code for more languages
 184 * read SQL spec
 185 * type check expressions
 186
 187 ----
 188 2009-05-04
 189
 190 <style>
 191 code { font-family: monospace; }
 192 pre { background-color: #eee; border: 1px solid #0f0; }
 193 :not(pre) > code { font-size: 1em; }
 194 </style>