1 sqlgg: SQL Guided (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 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
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.
35 Suppose we have the database of some money relationships for the group of people. We use the
36 following SQL tables and queries:
39 CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,surname TEXT);
41 INSERT INTO person (name,surname) VALUES;
44 CREATE TABLE money (src INTEGER, dst INTEGER, amount INTEGER);
46 INSERT INTO money VALUES;
49 SELECT name || ' ' || surname AS fullname, SUM(amount) as total FROM person JOIN money ON src = id GROUP BY id;
51 SELECT DISTINCT surname FROM person JOIN money ON src = id AND dst = (SELECT id FROM person WHERE surname LIKE @surname) LIMIT @limit;
53 Generate the binding C++ code (boilerplate omitted, only function prototypes shown):
55 // DO NOT EDIT MANUALLY
57 // generated by sqlgg 0.2.0 (735e2815)
61 template <class Traits>
64 // CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,surname TEXT)
65 static bool create_person(typename Traits::connection db);
67 // INSERT INTO person (name,surname) VALUES
68 static bool add_person(typename Traits::connection db, typename Traits::Text const& name, typename Traits::Text const& surname);
70 // CREATE TABLE money (src INTEGER, dst INTEGER, amount INTEGER)
71 static bool create_money(typename Traits::connection db);
73 // INSERT INTO money VALUES
74 static bool add_money(typename Traits::connection db, typename Traits::Int const& src, typename Traits::Int const& dst, typename Traits::Int const& amount);
76 // SELECT name || ' ' || surname AS fullname, SUM(amount) as total FROM person JOIN money ON src = id GROUP BY id
79 typename Traits::Text fullname;
80 typename Traits::Int total;
84 static bool calc_total(typename Traits::connection db, T& result);
86 // SELECT DISTINCT surname FROM person JOIN money ON src = id AND dst = (SELECT id FROM person WHERE surname LIKE @surname)
89 typename Traits::Text surname;
93 static bool list_donors(typename Traits::connection db, T& result, typename Traits::Text const& surname, typename Traits::Int const& limit);
99 2. Syntax. All queries are written in plain SQL. Every statement should be terminated with
100 semicolon. There is no need to write (?,?) after VALUES in INSERT statement. The annotation
101 `[sqlgg] name=function_name` (or simply `@function_name`) before the query specifies the name
102 of the generated function (this annotation optional but very convenient).
103 1. The generated code is parametrized by database-specific class `Traits`. It specifies the
104 correspondence between SQL and native types, provides types for database connection and
105 implements actual code to execute statements. `Traits` should be implemented
106 once for every specific database API.
107 1. `add_money()` function takes three data parameters -- the values to INSERT into table (note the
109 3. `calc_total()` returns data via `result` parameter. Under the scenes it will
110 bind columns of resulting rowset to the fields of `T::value_type`, which should provide fields
111 `fullname` of type `Traits::Text` and
112 `total` of type `Traits::Int` (otherwise it will fail to compile). For convenience a structure
113 satisfying the requirements for output type is generated alongside the function, `data_4` in
114 this particular case, so `std::vector<data_4>` for `result` is fine.
115 4. The types of parameters for `list_donors` were inferred correctly (`limit` is `Int` and `surname`
116 is `Text`. SQL is not a statically-typed language so the inferred types are based on some
117 reasonable assumptions.
118 5. Statements of arbitrary depth across many tables should be supported.
119 6. Statements are checked for correctness as far as generator is concerned, so it will detect
120 syntax errors, non-existent columns in expressions, mismatched columns in compound statements,
121 ambiguous column names etc.
123 Then manually-written C++ code boils down to:
125 #include "../sqlite3_helper.hpp" // sqlite3 traits
126 #include "demo_cxx_gen.hpp" // generated
132 typedef sqlgg<sqlite3_traits> gen;
133 typedef long long int64;
138 sqlite3_open(":memory:", &db);
141 gen::create_person(db);
142 gen::create_money(db);
144 // add all person records
145 gen::add_person(db,"John","Black");
146 int64 john = sqlite3_last_insert_rowid(db);
147 gen::add_person(db,"Ivan","Petrov");
148 int64 ivan = sqlite3_last_insert_rowid(db);
149 gen::add_person(db,"Sancho","Alvares");
150 int64 sancho = sqlite3_last_insert_rowid(db);
152 // add money relations
153 gen::add_money(db,john,ivan,200);
154 gen::add_money(db,john,sancho,100);
155 gen::add_money(db,john,sancho,250);
156 gen::add_money(db,sancho,ivan,300);
158 // summarize by person
159 typedef vector<gen::data_4> collection;
161 gen::calc_total(db,all);
164 cout << "Total transfers:" << endl;
165 for (collection::const_iterator i = all.begin(), end = all.end(); i != end; ++i)
167 cout << i->fullname << " = " << i->total << endl;
171 typedef vector<gen::data_5> people;
173 gen::list_donors(db,p,"petrov",100);
175 cout << "Donors:" << endl;
176 for (people::const_iterator i = p.begin(), end = p.end(); i != end; ++i)
178 cout << i->surname << endl;
181 // properly close database
187 The code is straightforward and free of any SQL-specific details. More importantly it is statically
188 checked by the compiler -- suppose we change the database scheme and add one more field to `money`
189 table (e.g. timestamp of transfer). Compilation rightfully fails:
191 demo_cxx.cpp: In function `int main()':
192 demo_cxx.cpp:29: error: no matching function for call to
193 `sqlgg<sqlite3_traits>::add_money(sqlite3*&, int64&, int64&, int)'
194 demo_cxx_gen.hpp:75: note: candidates are: static bool sqlgg<Traits>::add_money(typename
195 Traits::connection, const typename Traits::Int&, const typename Traits::Int&, const typename
196 Traits::Int&, const typename Traits::Int&) [with Traits = sqlite3_traits]
201 The main idea is that the generator should take care only of semantic binding between SQL and code
202 sides, being as unobtrusive as possible. So the choice of the specific database and API is a
203 programmer's choice. Similarly, queries to the database are expressed in plain SQL, so that the
204 generator can be easily plugged in any existing project -- just move all SQL statements used in the
205 code to separate file and feed it to generator.
207 Distinguishing feature of **sqlgg** is that it starts off with actual SQL queries, not object models
208 or SQL table descriptions.
210 This is work in progress and there is plenty of room for improvement. For now the status of this
211 project is **works for me** . I use it for some simple database-access code with
212 [sqlite3](http://sqlite.org) engine (using suitable [sqlite3_traits](sqlite3_helper.hpp) helper).
213 This project was started when I found myself editing existing code with tons of C++ wrappers for SQL
214 queries, each binding several parameters and decomposing results.
216 For now it can generate C++ and OCaml code. Generated C++ code is parametrized with template class
217 for database specific code. Generated OCaml code is a functor
218 `module Sqlgg (T:`[Sqlgg\_traits.M](sqlgg_traits.ml)`)` (sample [sqlgg\_sqlite3](sqlgg_sqlite3.ml)
219 for [OCaml-SQLite3][]).
221 [OCaml-SQLite3]: http://caml.inria.fr/cgi-bin/hump.en.cgi?contrib=471
223 Try it [online](sql.cgi).
228 * distinguish predicates and expressions (research)
229 * choose better names for some common cases (WHERE id = ? etc)
230 * fix line numbers in error output
231 * resolve conflicts in grammar, check precedences
232 * type-inference is too primitive
233 * detect statements on single tables and group the corresponding generated code in one class
234 * check names (functions and bindings) for uniqueness
235 * support/test other SQL engines
236 * generate code for more languages
238 * type check expressions
244 code { font-family: monospace; }
245 pre { background-color: #eee; border: 1px solid #0f0; }
246 :not(pre) > code { font-size: 1em; }