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.

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...

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...

database::get_table retreives the table Clients (as a shared pointer). If table Clients is not found, an exception is thrown.

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.

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.

When using join you must always the source table first
(that contains foreign keys) and then target table (that is referenced),

Internally, select_query builds a graph of the query tables and
the joins: query tables are the vertices, joins are the edges,

We have not specified the join type so INNER JOIN has
been defaulted,

SQLpp will detect if the USING clause (foreign key and
primary key have same name) can be used, if not it switches to
ON.

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:

Are all tables connected?

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

Share

About the Author

Jonathan de Halleux is Civil Engineer in Applied Mathematics. He finished his PhD in 2004 in the rainy country of Belgium. After 2 years in the Common Language Runtime (i.e. .net), he is now working at Microsoft Research on Pex (http://research.microsoft.com/pex).

hello！I found the sqlPP is very helpful to my work.I download the sqlpp_ide_01.zip and sqlpp_16.zip。With the section"Using it in your project",I can rebuild the project in sqlpp_ide_01.zip in my Vs.net2003 environment,but i can't rebuild the project in sqlpp_16.zip.The error is "SQLppNET fatal error LNK1181: can not open input file“lib_iobindd.lib”
" and "SQLppNET fatal error LNK1181: can not open input file“libsqlppd.lib.lib”
",why?What can i do?

Hi...
For some strange reason, I have a IOException trying to run the SQLPPDemo project in VStudio.NET. (I use the 2.4 version)
What can it be?

Thks..

Nicolás

PD: I'm trying to use the library to make a generic query utility for the web, where you can join tables, add where clauses, etc, etc... But I still not understand well how to make simple queries like SELECT * FROM ... WHERE ..., ¿Does exist some example that can ilustrate it?

I think this class is very usefull;). However, commonly used db are missing. Will you support the oracle and ms access database? If yes, can you implement the function decode from oracle and its equivalent iif in ms access?

BTW: After reading the code, I find the ids for the fields in the table are unique. But I can only get the field information throught the table object. Could SQLpp provide a function that allow me to get the field information by this unique id directly?