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);
38 INSERT INTO test(name,descr) VALUES;
39 SELECT name,descr FROM test WHERE name = @name LIMIT @limit;
42 city || @delim || descr as y,
43 max(length(city),random(*)) as z
45 LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))
48 Generated code (with some boilerplate omitted):
50 // DO NOT EDIT MANUALLY
52 // generated by sql2cpp
56 template <class Traits>
59 // CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT)
61 static bool create_test(typename Traits::connection db)
63 return Traits::do_execute(db,_T("CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,descr TEXT)"),typename Traits::no_params());
66 // INSERT INTO test(name,descr) VALUES
68 static bool Add(typename Traits::connection db, typename Traits::Text const& name, typename Traits::Text const& descr)
70 return Traits::do_execute(db,_T("INSERT INTO test(name,descr) VALUES (?,?)"),params_1(name, descr));
73 // SELECT name,descr FROM test WHERE name = @name LIMIT @limit
78 static void of_stmt(typename Traits::statement stmt, T& obj)
80 Traits::get_column_Text(stmt, 0, obj.name);
81 Traits::get_column_Text(stmt, 1, obj.descr);
88 typename Traits::Text name;
89 typename Traits::Text descr;
94 static bool select_2(typename Traits::connection db, T& result, typename Traits::Text const& name, typename Traits::Int const& limit)
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));
101 // city || @delim || descr as y,
102 // max(length(city),random(*)) as z
104 // LEFT JOIN (SELECT name AS city FROM test WHERE id=@id))
110 static void of_stmt(typename Traits::statement stmt, T& obj)
112 Traits::get_column_Text(stmt, 0, obj.name);
113 Traits::get_column_Int(stmt, 1, obj.z);
115 }; // struct output_3
120 typename Traits::Text name;
121 typename Traits::Int z;
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)
128 return Traits::do_select(db,result,_T("SELECT name,z FROM \
130 city || @delim || descr as y,\
131 max(length(city),random(*)) as z \
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));
139 Things to note above:
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.
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.
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](sqlite3_helper.hpp) helper).
175 Try it [online](sql.cgi).
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
185 * type check expressions
191 code { font-family: monospace; }
192 pre { background-color: #eee; border: 1px solid #0f0; }
193 :not(pre) > code { font-size: 1em; }