1 SQL to C++ code generator
2 =========================
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
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.
36 CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT);
37 INSERT INTO test(name,descr) VALUES (?,?);
38 SELECT name,descr FROM test WHERE name = @name LIMIT @limit;
41 city || ' ' || descr as y,
42 max(length(city),random(*)) as z
44 (SELECT name AS city FROM test WHERE id=@id))
47 Generated code (only prototypes):
49 template <class Traits>
52 static bool create_test(typename Traits::connection db);
53 static bool insert_2(typename Traits::connection db, typename Traits::Text const& name, typename Traits::Text const& descr);
57 typename Traits::Int id;
58 typename Traits::Text name;
59 typename Traits::Text descr;
63 static bool select_3(typename Traits::connection db, T& result, typename Traits::Any const& name, typename Traits::Int const& limit);
67 typename Traits::Text name;
68 typename Traits::Text z;
72 static bool select_4(typename Traits::connection db, T& result, typename Traits::Any const& id, typename Traits::Any const& level);
77 1. The generated code is parametrized by database-specific class `Traits`. It specifies the
78 corresponding between SQL and native types, provides types for database connection and other
79 details. `Traits` also implements actual code to execute statements. It should be implemented
80 once for every specific database API.
81 2. `insert_2()` requires two data parameters, the values to INSERT into table, the binding into
82 statement is done behind the scenes.
83 3. `select_3()` returns data via `result` parameter. It is not visible above, but `T` should be a
84 container with `T::value_type` having at least the fields of `data_3` (otherwise it will fail to
85 compile), e.g. `std::vector<data_3>` is fine.
86 4. The type of limit is `Traits::Int` as expected, though the type of `name` is `Any` which is
87 unfortunate. In the future it will be possible to infer a specific type.
88 5. Statements can be of arbitrary depth across many tables.
89 6. Statements are checked for correctness as far as generator is concerned, so it will detect
90 syntax errors, non-existant columns in expressions, mismatched rowsets in compound statements,
91 ambigous column names etc.
96 This is work in progress and there is plenty of room for improvement.
97 The generator is already used for some simple database-access code. It uses
98 [sqlite3](http://sqlite.org) as a database and implements suitable `sqlite3_traits`
101 Online version will be made available soon.
106 * type check expressions, infer type for parameters
107 * validate expressions with regard to scheme in their scope
108 * detect statements on single tables and group the corresponding generated code in one class
109 * check names (functions and bindings) for uniqueness
110 * support/test other SQL engines
111 * generate code for more languages
118 code { font-family: monospace; font-style: italic; }
119 pre { background-color: #eee; color: black; border: 1px dashed #0c5; }
120 /*body { padding-bottom: 200px; }*/