A scripted SQL query generation framework with IDE: SQLpp (v1.4)

• Download IDE (1.0 beta)
• Download Source (1.6 beta)
• Download page at Sourceforge

Table of Contents

• Introduction
  • SQLpp
  • The article
• Features
• Example database
• Create a SQLpp database
• Creating queries
• Specifying fields
  • A simple query
  • Add some fields
  • Specify field alias
  • Aggregate functions: count(...), etc.
  • Value expressions
• Table joins
  • Joining tables
  • Joining tables with multiple instance of the same table
  • Complex joins
• Predicates
  • Field - value comparaison
  • Field - field comparaison
  • Null: is null, is not null
  • Set selection: in (...)
  • Set sub query selection: in (select ...)
  • Range selection: between ... and ...
  • String matching: like ...
  • Logical operators: and, or, xor, not
  • Case
• Set operations: union, except, intersect
• Miscellanous
  • Smart pointers
  • Exceptions
  • SQL Parser
• Using it in your project
  • Requisites
  • Projects
  • Installation
• References

Introduction

SQLpp

SQLpp objective is to simplify the creation of SQL queries, and more specifically in C++. A .NET wrapper is under construction.

SQLpp uses the Boost Graph Library (BGL), see [2], to make an internal graph presentation of the table and constraints, in order to simplify query generation.

SQLpp follows the article proposed by Andreas Saurwein, SQL WHERE - the easy way, see [1]. The author presents a small class helper for easily formatting where clauses in C++. Starting from his idea, SQLpp has been built in order to simplify the tedious and error-prone writing of SQL queries.

The latest informations and downloads are available at http://sqlpp.sourceforge.net/.

The article

A full documentation of the classes is given (using Doxygen), therefore, I will not go into details about the classes' methods but rather give a general overview of SQLpp and various examples to illustrate it.

In this article, it is assumed that the reader has some basic knowledge about SQL and SQL queries.

SQLpp is still a work in progress, I present it here in order to have construtive suggestions. In the future SQLpp should be embedded into some GUI...

Features

Here is a list of SQLpp features:

• 1.6
  • Dot (graphviz) generator
• 1.5
  • managed C++ wrapper
  • C# wrapper generator
• 1.4
  • Entirely rewritten the core of the framework,
  • subqueries
  • in subqueries
  • case predicate
  • typed constant
  • date, time, timestamp
  • a lot of new aggregate functions
  • added a new join method to link two query tables
  • multiple database support
• 1.1
  • names and alias handling,
  • multiple field primary key, unique and reference constraint (foreign key),
  • self reference support,
  • inner join, outer join (left, right, full),
  • join resolution if possible*,
  • field prefix helper,
  • field value comparison,
  • field-field comparison,
  • and, or, xor, not,
  • is (not) null,
  • exists,
  • union, excepts, intersects
  • aggregate functions: count, avg, max, min, sum, mean
  • in predicate,
  • sub queries,
  • SQL creation string parser (using Spirit)

* Given two tables, SQLpp checks for the corresponding reference constraints. If this constraint is unique, SQLpp knows it is the constraint to use to perform the join.

Example database

Before getting into the details of SQLpp, let me illustrate it with a simple example. Suppose that we have the following database:

There are a few remarks to make about it:

• All tables have primary keys,
• EmployeeStores has a multiple field primary key,
• Constraints have multiple keys (see unique in Clients, or remark above),
• Employees contains a self reference (ChiefID)

Creating a SQLpp database

In order to help the user, SQLpp must learn the database structure. This can be done by hard coding the tables, fields and constraints or more easily by using the built-in SQL parser (written using Spirit 7.0).

New in 1.4: You need to provide a database adapter to the constructor. A database adapter takes care of handling the difference between the different databases available, differences like date format, forward declaration support, subqueries support, etc...

• C++

  //creating a database adaptor adapters::database_adaptor_shared_ptr mysql (new adapters::mysql_adaptor()); database_shared_ptr db = database::create(mysql); if(!parse_sql( sql )) ...// could not parse the stringLua 

Remarks:

• mssql_adaptor is also available

This is about all you need to do. Internally, SQLpp has built a graph, using the BGL, where the vertices are the table and the edges are the reference constraints as depicted in the picture below.

You can also verify the DB structure by generating its SQL creating statement:

• C++

  cout<<db->get_create_statement()

Multiple database support

Adaptors are used to match the particularity of each database engine. They have two main properties:

1. Conversion method that take care of formatting dates, strings, etc,
2. Support flags that tell the framework if a particular feature is supported (e.g. forward declaration)

Adaptors are in the adaptors namespace.

Creating queries

Once the database is constructed, it can be used to create queries. A query object can be constructed from the database:

select_query_shared_ptr q = db.create_query();

and the SQL code is generated by

cout<<q->get_sql();

Once the query is allocated, you need to take the following steps:

1. Add and join tables,
2. Add fields (optional),
3. Specify where, groupby, having and orderby conditions (optional).

The steps above will be illustrated by a number of SQL queries ranging from very simple to more elaborate.

Specifying fields

A simple query

We want to list all the clients:

• Desired SQL

  SELECT * FROM Clients

• C++

  q->add_table( db->get_table("Clients") ); 

Remarks:

1. database::get_table retreives the table Clients (as a shared pointer). If table Clients is not found, an exception is thrown.
2. select_query::add_table adds the Clients table to the query and returns are query table pointer. A query table is an instance of a table which has to be used later to specify field belongings. Note that we need to differentiate query table and table in order to handle self-referenced tables.

Specify some fields

select all clients last name :

• Desired SQL

  SELECT C.ClientLastName AS 'Last Name' FROM Clients AS C

• C++

  query_table_shared_ptr tc=q->add_table( db->get_table("Clients"), "C" ); q->add_field( tc->get_field("ClientLastName"), "Last Name" );

Remarks:

1. C is the Clients table alias
2. query_table::get_field retrieve the field ClientLastName (as a shared pointer),
3. select_query::add_field adds the field ClientLastName with alias Last Name. The alias is optional.
4. SQLpp will automatically add the table name or alias in the field description.

Aggregate functions

select count( * ) from clients :

• Desired SQL

  SELECT COUNT(ClientID) FROM Clients

• C++

  query_table_shared_ptr tc=q->add_table( db->get_table("Clients")); q->add_field( count( tc ) );

Remarks:

1. count takes any query_field pointer. If NULL, * is used.
2. sum, avg, max, min, sum are also available
3. To have DISTINCT, put true as second parameter:

   q->add_field( count( tc->get_field("ClientID"), true ) );

Value expression

select product price * order quantity from orders and products:

• Desired SQL

  SELECT O.OrderQuantity * P.ProductPrice FROM ...

• C++

  query_table_shared_ptr to=q->add_table( db->get_table("Orders"), "C" ); query_table_shared_ptr tp=q->join( to, db->get_table("Products"), "C" ); q->add_field( to->get_field("OrderQuantity") * tp->get_field("ProductPrice") );

Remarks:

1. join will be explained later. It performs the innerjoin.
2. This is an operation between two query fields.
3. Allowed operators are: +, *, -, /
4. Not implemented yet in Lua

select product price * VAT from products :

• Desired SQL

  SELECT P.ProductPrice * 1.21FROM Products

• C++

  double vat = 1.21; q->add_field( tp->get_field("ProductPrice") * vat );

Remarks

1. This is an operation between a numerical value and a query field.
2. Allowed numerical values are: float, double, int, uint, long, ulong,
3. Of course, you can mix all to build complex arithmetic expression

select sum( product price * order quantity * VAT ) from products and orders :

• Desired SQL

  SELECT SUM( P.ProductPrice * O.OrderQuantity * 1.21 ) FROM ...

• C++

  double vat = 1.21; q->add_field( sum( tp->get_field("ProductPrice") * tp->get_field("ProductPrice") * vat ) );

Table joins

Joining tables

select orders date and client name:

• Desired SQL

  SELECT C.ClientLastName AS 'Name', O.OrderDate FROM Orders AS O INNERJOIN Clients AS C USING(ClientID)

• C++

  query_table_shared_ptr to=q->add_table( db->get_table("Orders"), "O" ); query_table_shared_ptr tc=q->join( to, db->get_table("Clients"), "C" ); q->add_field( tc->get_field("ClientLastName"), "Name" ); q->add_field( to->get_field("OrderDate"));

Remarks:

1. select_query::join is used to link Orders table with Clients table. Internally, SQLpp first checks that a reference constraint exists between the table, using the database graph. If not, exception is thrown.
2. When using join you must always the source table first (that contains foreign keys) and then target table (that is referenced),
3. Internally, select_query builds a graph of the query tables and the joins: query tables are the vertices, joins are the edges,
4. We have not specified the join type so INNERJOIN has been defaulted,
5. SQLpp will detect if the USING clause (foreign key and primary key have same name) can be used, if not it switches to ON.

Joining tables with multiple instance of the same table

select the employees name and their respective chief name (referenced by ChiefID):

• Desired SQL

  SELECT E.EmployeeLastName, Ch.EmployeeLastName FROM Employees AS E INNERJOIN Employees AS Ch ON E.ChiefID = Ch.EmployeeID

• C++

  //let disable using select_query::set_support_using(false); query_table_shared_ptr te= q->add_table( db->get_table("Employees"), "E" ); query_table_shared_ptr tch= q->add_table( db->get_table("Employees"), "Ch" ); q->join( te, // joined table (contains the foreing key) tch // referenced table ); q->add_field( tc->get_field("LastName")); q->add_field( tch->get_field("LastName"));

Remarks

1. You can see in this example why we need to use query table: Employees has a self reference (ChiefID).
2. we add two fields EmployeeLastName but link them to different query tables.
3. join can be used to link to tables already added to the query
4. You can display the table joins by using select_query::get_table_joins().

Employees AS E -> Employees AS Ch 

Complex joins

select order date, product name, client name, vendor name and store address:

The two previous examples where presenting simple joins between two tables. What happens when you have more complex joins involving a number of tables? Several problems arise:

1. Are all tables connected?
2. How to choose the table order to perform the join ?

These two questions are easily answered using graph theory and the BGL. For example, the second question can be translated into: How do I find a path that explores incrementally all the vertices (see picture)? The answer is use undirected_dfs from the BGL.

• Desired SQL

  SELECT O.OrderDate, P.ProductName, C.ClientLastName, E.EmployeeLastName, S.StoreAddress FROM (((Orders AS O INNERJOIN Clients AS C USING(ClientID)) INNERJOIN Products AS P USING(ProductID)) INNERJOIN Employees AS E USING(EmployeeID)) LEFT OUTER JOIN Stores AS S Using(StoreID)

• C++

  query_table_shared_ptr to=q->add_table( db->get_table("Orders"), "O" ); query_table_shared_ptr tc=q->join( to, db->get_table("Clients"), "C" ); query_table_shared_ptr tp=q->join( to, db->get_table("Products"), "P" ); query_table_shared_ptr te=q->join( to, db->get_table("Employee"), "E" ); query_table_shared_ptr ts= q->join( tp, db->get_table("Stores"), "S" , LeftOuterJoin ); q->add_field( to->get_field("OrderDate")); q->add_field( tp->get_field("ProductName")); q->add_field( tc->get_field("ClientLastName")); q->add_field( te->get_field("EmployeeLastName")); q->add_field( ts->get_field("StoreAddress")); 

Remarks

1. LeftOuterJoin has been added to illustrate how you specify different join types. Other supported types are: LeftJoin, RightOuterJoin, FullJoin.

Predicates

In the previous examples, we didn't specify a where condition. To do, just do:

q->set_where( ... );

In the following, for each example, we suppose that tables have been added and joined.

Field - value comparaison

select from clients where client first name = "John"

• Desired SQL

  SELECT * FROM Clients

• C++

  q->set_where( t_clients->get_field("ClientFirstName") == "John" );

Remarks

1. The == operator is a template function that create class holding the field and the value
2. Not type checking is made.
3. We can simplify things and store the ClientFirstNamequery_field pointer:

   query_field_shared_ptr qf_cfn = t_clients->get_field("ClientFirstName");

Field field comparaison

select from clients, orders where cliendid = order.clientid

• Desired SQL

  SELECT * FROM Clients AS C, Orders AS O WHERE C.ClientID = O.ClientID

• C++

  q->set_where(equal( qf_cfn, t_orders->get_field("ClientID") ) );

Remarks

1. We use a equal method and no the == operator because it clashes with the smart pointer == operator.
2. not_equal can be used for !=.

Null: is null, is not null

select from clients where client first name is null

• Desired SQL

  SELECT * FROM Clients WHERE ClientFirstName ISNOTNULL

• C++

  q->set_where(is_not_null(qf_cfn) );

Remarks

1. is_null can be used to generate ISNULL

Set selection: in (...)

select from clients where client first name in ('John' or 'Bill')

1. Desired SQL

   SELECT * FROM Clients WHERE ClientFirstName in ('John','Bill')

2. C++

   vector<string> v; v.push_back("John"); v.push_back("Bill"); q->set_where(in( qf_cfn, v.begin(), v.end() ) );

Remarks

1. The method in takes an iterator range (v.begin(), v.end()). Since it is a template function, it does not depend on the container type (must support forward iterators).
2. The container can contain any (see data types section) type of data

Set sub query selection: in (select ...)

select from clients where client first name in (select client first name from clients where ClientID > 10)

1. Desired SQL

   SELECT * FROM Clients WHERE ClientFirstName in (SELECT ClientFirstName FROM Clients WHERE ClientID > 10)

2. C++

   // creating subquery select_query_shared_ptr sub_q = db::create_query();... t_sub_client= sub_q->add_table( db->get_table("Clients")); sub_q->add_field( t_sub_client, t_sub_client->get_field("ClientFirstName") ); // creating main query q->set_where(in( qf_cfn, sub_q ) );

Range selection: between ... and ...

select from clients where clientid between 10 and 50

• Desired SQL

  SELECT * FROM Clients WHERE ClientID BETWEEN 10AND50

• C++

  q->set_where(between( qf_cid,10,50 ) );

String matching: like ...

• Desired SQL

  SELECT * FROM Clients WHERE ClientFirstName LIKE 'John%'

• C++

  q->set_where(like(qf_cfn, "John%"));

Logical operators: and, or, xor, not

select from clients where client first name = "John" and not client first like 'M%' "Martin"

• Desired SQL

  SELECT * FROM Clients WHERE ClientFirstName = 'John' ANDNOT ClientFirstName LIKE 'M%'

• C++

  q->set_where(and( qf_cfnq == "John", not( like(qf_cfln, "M%") ) ) );

Remarks

1. or and xor also available,
2. not is an unary operator.

Case : case ... when ... else... end or case when ... else ... end (new in 1.4)

select case clientID when 1 then 'found' else 'not found' end from clients

• Desired SQL

  SELECT CASE C.ClientID WHEN 1 THEN 'found' ELSE 'not found' ENDFROM Clients AS C

• C++

  q->add_field( case_( qc->get_field("ClientID") ) ->when( to_expression(1) , to_expression("found") ) ->else( to_expression("not found") ); 

Remarks

1. The other form of case is also available, use case_ without argument.

Set operations: union, except, intersect

Unions, difference and intersections on queries are done as:

query_shared_ptr q1, q2, q; q=union_(q1,q2); q=except(q1,q2); q=intersect(q1,q2, true); // add ALL

Remarks

1. union_ has a underscore because union is a C++ reserved word,
2. You can specify ALL by setting the third parameter to true

Miscellanous C++

Smart pointer

All SQLpp objects are manipulated as smart pointers using Boost shared_ptr implementation. Internally, some are stored as weak_ptr to break cycles.

Exceptions

A lot of methods throw exception when the desired behavior is flawed: a wrong field name, bad join, etc... All classes throw a sqlpp_exception (derived from std::exception):

try { // proted here the SQLpp code } catch (std::exception& e) { cout<<e.what()<<endl; }

SQL Parser

The SQL parser is a "home made" parser built with Spirit 1.7. As always, use it at your own risk. It supports:

• forward table declaration (foreign key can refer to table not yet created),
• multiple field constraints,
• constraint naming,
• a lot of SQL types:
• inline primary key (specify a primary key in the field definition)
• altertable ... addconstraint ... declaration

Include sqlpp/parsers/sql_parser.hpp to use it.

Using it in your project

Requisites

1. VC7.0 or VC 7.1 (better)
2. Boost 1.30 installed and added to the include directories,
3. Spirit 1.7: you can download from spirit web site and copy the files directly in your boost directly. It might also work with 1.6.1

Namespaces

The namespaces mimic the include file directory structure as in C#. All SQLpp lives in sqlpp namespace, select_query and query_predicate live in queries, parsers live in parsers.

History

• v1.6, 8-09-2003
  • Removed Lua bindings,
  • Added Managed C++ wrapper
  • Added Dot and C# wrapper output
• v 1.4, 28-08-2003,
  • Entirely rewritten the core of the framework,
  • subqueries
  • in subqueries
  • case predicate
  • typed constant
  • date, time, timestamp
  • a lot of new aggregate functions
  • added a new join method to link two query tables
  • multiple database support
• v1.3.2, 14-08-2003,
  • fixed bug when computing query joins (now using undirected graph)
  • added select_query::get_table_joins
• v1.3.1, 8-08-2003
  • Added vc7.0 projects
• v1.3, 4-08-2003,
  • Added LuaIDE
• v1.2, 29-07-2003,
  • Added Lua scripting, not quite finished but a subset of the framework is already wrapped
• v1.1, 24-07-2003,
  • Simplified query_field semantics: tc + tc->get_field becomes tc->get_field,
  • Added value expressions,
  • Added aggregate functions,
  • Fixed bug in in_predicate,
  • Separated tests in small files
  • Moved a lot of code to cpp files
• v1.0, 23-07-2003, Initial release.

References

• [1] - SQL WHERE - the easy way, Andreas Saurwein
• [2] - The Boost Graph Library (BGL)
• [3] - Lua Scripting Language
• [4] - LuaBind
• [5] - LuaIDE by Tristan Rybak