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     private:
  68       struct params_1
  69       {
  70         [...]
  71       }; // struct params_1
  72
  73     public:
  74       static bool Add(typename Traits::connection db, typename Traits::Text const& name, typename Traits::Text const& descr)
  75       {
  76         return Traits::do_execute(db,_T("INSERT INTO test(name,descr) VALUES (?,?)"),params_1(name, descr));
  77       }
  78
  79       // SELECT name,descr FROM test WHERE name = @name LIMIT @limit
  80     private:
  81       template <class T>
  82       struct output_2
  83       {
  84         static void of_stmt(typename Traits::statement stmt, T& obj)
  85         {
  86           Traits::get_column_Text(stmt, 0, obj.name);
  87           Traits::get_column_Text(stmt, 1, obj.descr);
  88         }
  89       }; // struct output_2
  90
  91     private:
  92       struct params_2
  93       {
  94         [...]
  95       }; // struct params_2
  96
  97     public:
  98       struct data_2
  99       {
 100         typename Traits::Text name;
 101         typename Traits::Text descr;
 102       }; // struct data_2
 103
 104     public:
 105       template<class T>
 106       static bool select_2(typename Traits::connection db, T& result, typename Traits::Text const& name, typename Traits::Int const& limit)
 107       {
 108         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));
 109       }
 110
 111       // SELECT name,z FROM
 112     //   (SELECT name,
 113     //           city || @delim || descr as y,
 114     //           max(length(city),random(*)) as z
 115     //    FROM test
 116     //    LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))
 117     // WHERE z < @level
 118     private:
 119       template <class T>
 120       struct output_3
 121       {
 122         static void of_stmt(typename Traits::statement stmt, T& obj)
 123         {
 124           Traits::get_column_Text(stmt, 0, obj.name);
 125           Traits::get_column_Text(stmt, 1, obj.z);
 126         }
 127       }; // struct output_3
 128
 129     private:
 130       struct params_3
 131       {
 132         [...]
 133       }; // struct params_3
 134
 135     public:
 136       struct data_3
 137       {
 138         typename Traits::Text name;
 139         typename Traits::Text z;
 140       }; // struct data_3
 141
 142     public:
 143       template<class T>
 144       static bool select_3(typename Traits::connection db, T& result, typename Traits::Any const& delim, typename Traits::Int const& id, typename Traits::Text const& level)
 145       {
 146         return Traits::do_select(db,result,_T("SELECT name,z FROM \
 147       (SELECT name,\
 148               city || @delim || descr as y,\
 149               max(length(city),random(*)) as z \
 150        FROM test \
 151        LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))\
 152     WHERE z < @level"),output_3<typename T::value_type>(),params_3(delim, id, level));
 153       }
 154
 155     }; // struct sql2cpp
 156
 157 Things to note above:
 158
 159 1. The generated code is parametrized by database-specific class `Traits`. It specifies the
 160                 correspondence between SQL and native types, provides types for database connection and other
 161                 details. `Traits` also implements actual code to execute statements. It should be implemented
 162                 once for every specific database API.
 163 2. The annotation `[sql2cpp] name=Add` before the INSERT query specifies the name of the generated
 164     function. NB: there is no need to write (?,?) after VALUES.
 165 1. `Add()` function takes two data parameters, the values to INSERT into table (`params_1` is the
 166     boilerplate code to bind these parameters into query).
 167 3. `select_2()` returns data via `result` parameter. Hidden auxiliary class `output_2` is used to
 168     bind columns of rowset to the fields of `T::value_type`, which should have fields `name` and
 169     `descr` of type `Traits::Text` (otherwise it will fail to compile). For convenience a structure
 170     satisfying the requirements for output type is generated alongside the function, `data_2` in
 171     this particular case, so `std::vector<data_2>` for `result` is fine.
 172 4. The types of parameters for `select_2` were inferred correctly (`limit` is `Int` and `name` is
 173     `Text`. For now the type-inferrer is rather primitive and will handle only simple expressions
 174     (see parameter types in `select_3`). It is being worked on.
 175 5. Statement of arbitrary depth across many tables should be supported.
 176 6. Statements are checked for correctness as far as generator is concerned, so it will detect
 177                 syntax errors, non-existant columns in expressions, mismatched rowsets in compound statements,
 178                 ambigous column names etc.
 179
 180 Details
 181 -------
 182
 183 This is work in progress and there is plenty of room for improvement.
 184 The generator is already used for some simple database-access code. It uses
 185 [sqlite3](http://sqlite.org) as a database and implements suitable `sqlite3_traits`
 186 helper.
 187
 188 Online version will be made available soon.
 189
 190 TODO
 191 ----
 192
 193 * type check expressions, infer type for parameters
 194 * validate expressions with regard to scheme in their scope
 195 * detect statements on single tables and group the corresponding generated code in one class
 196 * check names (functions and bindings) for uniqueness
 197 * support/test other SQL engines
 198 * generate code for more languages
 199 * read SQL spec
 200
 201 ----
 202 2009-05-03
 203
 204 <style>
 205 code { font-family: monospace; font-style: italic; }
 206 pre { background-color: #eee; color: black; border: 1px dashed #0c5; }
 207 /*body { padding-bottom: 200px; }*/
 208 </style>