SQL client library ?
What's the situation with SQL client libraries (similar to Rogue Wave's DBTools.h++) ? I know that there's a boost_sql_cli in the Vault, but it doesn't seem to make much progress. I have also heard about plans to bring Soci into Boost. Thanks, Jean-Louis
Jean-Louis Leroy wrote:
What's the situation with SQL client libraries (similar to Rogue Wave's DBTools.h++) ? I know that there's a boost_sql_cli in the Vault, but it doesn't seem to make much progress. I have also heard about plans to bring Soci into Boost.
You may be interested in checking the std::rdb workshop from BoostCon'09 http://www.boostcon.com/community/wiki/show/private/2009/ as well as mailing list dedicated to this subject: http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb-crystalclear... SOCI is one of the topics over there. Best regards, -- Mateusz Loskot, http://mateusz.loskot.net Charter Member of OSGeo, http://osgeo.org
You may be interested in checking the std::rdb workshop from BoostCon'09
Thanks. After a quick look I have two remarks : 1. The list doesn't seem very active. And you need to subscribe just to see the archive (weird). Probably there are many people here who have valuable opinions on the subject and who will never be on that list. 2. It looks like the options they are considering for a syntax are mainly inspired by Soci and pqxx. They have this in common : the query is expressed as SQL text (à la Boost.Regex) that is parsed at run time instead of using language constructs (à la Boost.Xpressive) that can be checked at compile time. This is error prone : you can pass illegal SQL ; and the returned values will incur runtime typecasting, which may fail at run time in case of mismatch. Jean-Louis
Jean-Louis Leroy wrote:
2. It looks like the options they are considering for a syntax are mainly inspired by Soci and pqxx. They have this in common : the query is expressed as SQL text (à la Boost.Regex) that is parsed at run time instead of using language constructs (à la Boost.Xpressive) that can be checked at compile time. This is error prone : you can pass illegal SQL ; and the returned values will incur runtime typecasting, which may fail at run time in case of mismatch. Back at Boost'Con 09 I proposed a function based and not text based SQL interface using proto that looked like :
#include <boost/rdb_proto/rdb.hpp> BOOST_RDB_REGISTER_FIELD(age,int) BOOST_RDB_REGISTER_FIELD(id ,int) BOOST_RDB_REGISTER_NAMED_FIELD(name,std::string,"complete_name") int main() { connection db("some.db.url","login","passwd"); BOOST_RDB_TABLE((id)(name)(age)) employee("employee",db); BOOST_RDB_RESULT_SET((age)(name)) rows; rows = select(age,name).from(employee) .where( age > 45 && name ~= "Dil%" ); // Some random access if( !r.empty() ) cout << r[0][name] << " is " << r[0][age] << " years old." << endl; } I have the whole ppt of my proposal somewhere if soemone's interested. I have alas not much free time for this but I had some prototype code laying somewhere -- ___________________________________________ Joel Falcou - Assistant Professor PARALL Team - LRI - Universite Paris Sud XI Tel : (+33)1 69 15 66 35
Hi, I've been working on an type-safe sql interface geared towards sqlite (and actually based on sqlitepp). Below you find a usage example. If anyone is interested I'll glad to share the source (once I have clarifed copyrights with the company I work for). Best regards, Christoph using namespace ::Examples::LibDb; /// @brief Introductionary example for ::LibDb BOOST_AUTO_TEST_CASE(oneminute) { try { // A database connection is required. This is provided by ::LibDb::SQL::Session. We open a database in memory. Alternatively // you can specify a file to be openend/created. ::LibDb::SQL::Session session(":memory:"); // To access a database, read-only or read/write, we need to tell the library what entities we want to deal with. // Table entities are modelled as classes with public column attributes. The entities for this example are modelled in the // namespace DatabaseEntities. To interact with tables we need instances of the classes that modell the entity. // As interaction with classes is mostly immutable you are safe to query a table instance from the singleton // table factory. const DatabaseEntities::Authors &authors = ::LibDb::Entity::Schema::table<DatabaseEntities::Authors>(); const DatabaseEntities::Books &books = ::LibDb::Entity::Schema::table<DatabaseEntities::Books>(); // As our database is currently empty we proceed to create our tables in the database. ::LibDb::SQL::create_table(authors).execute(session); ::LibDb::SQL::create_table(books).execute(session); // On error the library throws std::runtime_error if a SQLite error occurred or // std::bad_exception. // Inserting records into the database is simple: Values can be assigned to columns (public attributes of our entity classes). // The column itself does not store the value assigned, but rather generates a pair of column and value and methods to concatenate // other column/value pairs. Assignment of values to columns is typesafe. ::LibDb::SQL::insert_into(authors, (authors.first_name = "Agatha", authors.last_name = "Christie")).execute(session); ::LibDb::SQL::insert_into(authors, (authors.first_name = "Lew", authors.last_name = "Tolstoi")).execute(session); // To insert books for Agatha we need to know Agatha's generated primaray key id. This is accomplished by selection // The columns you want to select are specified along with variables that receive the selected values (typesafe). // Additionally the result-set is narrowed down by only selecting those records where first name column corresponds // to Agatha. The column values of the first row are immediately assigned to the provided mutable variables. long long agatha_id, lew_id; ::LibDb::SQL::select(authors.id >> agatha_id).where(authors.first_name == "Agatha").execute(session); ::LibDb::SQL::select(authors.id >> lew_id).where(authors.first_name == "Lew").execute(session); // Now we can insert some books for Agatha and Lew. We skip the image attribute for now (handling blobs is a different example). ::LibDb::SQL::insert_into(books, (books.title = "Sie kamen nach Bagdad", books.price = 12.9, books.author_id = agatha_id)).execute(session); ::LibDb::SQL::insert_into(books, (books.title = "Ein gefaehrlicher Gegner", books.price = 15.9, books.author_id = agatha_id)).execute(session); ::LibDb::SQL::insert_into(books, (books.title = "Krieg und Frieden", books.price = 21.9, books.author_id = lew_id)).execute(session); // Lets query the number of books by Agatha. unsigned books_by_agatha; ::LibDb::SQL::select(::LibDb::SQL::Aggregation::count(books.author_id)
books_by_agatha).where(books.author_id == agatha_id).execute(session); BOOST_CHECK_EQUAL(books_by_agatha, 2);
// Now lets query the sum of book prices per author. We'd like to know the sum of book prices along with the author's last name. std::string author_name; double sum_book_prices; // ::LibDb::SQL::select binds the first record when it is executed. To iterate over all records we need to use its return type: a cursor. // The cursor provides a custom iteration interface with. Below we'll see an example to convert the entire result-set of the cursor // to an STL sequence. ::LibDb::SQL::Cursor cursor = ::LibDb::SQL::select( (authors.last_name >> author_name, ::LibDb::SQL::Aggregation::sum(books.price) >> sum_book_prices) // selection from multiple tables ).where( authors.id == books.author_id // join by author ids ).group_by( books.author_id // group per author, so summing is performed over books per author. ).order_by( authors.last_name, ::LibDb::SQL::OrderModes::DESC // sort descending by authors last name ).execute(session); On Sun, Sep 6, 2009 at 11:14 AM, joel<joel.falcou@lri.fr> wrote:
Jean-Louis Leroy wrote:
2. It looks like the options they are considering for a syntax are mainly inspired by Soci and pqxx. They have this in common : the query is expressed as SQL text (à la Boost.Regex) that is parsed at run time instead of using language constructs (à la Boost.Xpressive) that can be checked at compile time. This is error prone : you can pass illegal SQL ; and the returned values will incur runtime typecasting, which may fail at run time in case of mismatch.
Back at Boost'Con 09 I proposed a function based and not text based SQL interface using proto that looked like :
#include <boost/rdb_proto/rdb.hpp>
BOOST_RDB_REGISTER_FIELD(age,int) BOOST_RDB_REGISTER_FIELD(id ,int) BOOST_RDB_REGISTER_NAMED_FIELD(name,std::string,"complete_name")
int main() { connection db("some.db.url","login","passwd");
BOOST_RDB_TABLE((id)(name)(age)) employee("employee",db); BOOST_RDB_RESULT_SET((age)(name)) rows;
rows = select(age,name).from(employee) .where( age > 45 && name ~= "Dil%" );
// Some random access if( !r.empty() ) cout << r[0][name] << " is " << r[0][age] << " years old." << endl; }
I have the whole ppt of my proposal somewhere if soemone's interested. I have alas not much free time for this but I had some prototype code laying somewhere
-- ___________________________________________ Joel Falcou - Assistant Professor PARALL Team - LRI - Universite Paris Sud XI Tel : (+33)1 69 15 66 35
_______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost
joel skrev:
Jean-Louis Leroy wrote:
2. It looks like the options they are considering for a syntax are mainly inspired by Soci and pqxx. They have this in common : the query is expressed as SQL text (à la Boost.Regex) that is parsed at run time instead of using language constructs (à la Boost.Xpressive) that can be checked at compile time. This is error prone : you can pass illegal SQL ; and the returned values will incur runtime typecasting, which may fail at run time in case of mismatch. Back at Boost'Con 09 I proposed a function based and not text based SQL interface using proto that looked like :
#include <boost/rdb_proto/rdb.hpp>
BOOST_RDB_REGISTER_FIELD(age,int) BOOST_RDB_REGISTER_FIELD(id ,int) BOOST_RDB_REGISTER_NAMED_FIELD(name,std::string,"complete_name")
int main() { connection db("some.db.url","login","passwd");
BOOST_RDB_TABLE((id)(name)(age)) employee("employee",db); BOOST_RDB_RESULT_SET((age)(name)) rows;
rows = select(age,name).from(employee) .where( age > 45 && name ~= "Dil%" );
// Some random access if( !r.empty() ) cout << r[0][name] << " is " << r[0][age] << " years old." << endl; }
I have the whole ppt of my proposal somewhere if soemone's interested. I have alas not much free time for this but I had some prototype code laying somewhere
Consider me interested. I was considering something along those lines when I made a quick'n'dirty wrapper for SQLite, but in the end both 'Quick' and 'Dirty' won over 'good design'. I'd be interested to see your work here.. /Brian
Hi Joel, ----- Original Message ----- From: "joel" <joel.falcou@lri.fr> To: <boost@lists.boost.org> Sent: Sunday, September 06, 2009 11:14 AM Subject: Re: [boost] SQL client library ? Jean-Louis Leroy wrote:
2. It looks like the options they are considering for a syntax are mainly inspired by Soci and pqxx. They have this in common : the query is expressed as SQL text (à la Boost.Regex) that is parsed at run time instead of using language constructs (à la Boost.Xpressive) that can be checked at compile time. This is error prone : you can pass illegal SQL ; and the returned values will incur runtime typecasting, which may fail at run time in case of mismatch. Back at Boost'Con 09 I proposed a function based and not text based SQL interface using proto that looked like :
#include <boost/rdb_proto/rdb.hpp> BOOST_RDB_REGISTER_FIELD(age,int) BOOST_RDB_REGISTER_FIELD(id ,int) BOOST_RDB_REGISTER_NAMED_FIELD(name,std::string,"complete_name") int main() { connection db("some.db.url","login","passwd"); BOOST_RDB_TABLE((id)(name)(age)) employee("employee",db); BOOST_RDB_RESULT_SET((age)(name)) rows; rows = select(age,name).from(employee) .where( age > 45 && name ~= "Dil%" ); // Some random access if( !r.empty() ) cout << r[0][name] << " is " << r[0][age] << " years old." << endl; } I have the whole ppt of my proposal somewhere if soemone's interested. I have alas not much free time for this but I had some prototype code laying somewhere -- ___________________________________________ Joel Falcou - Assistant Professor PARALL Team - LRI - Universite Paris Sud XI Tel : (+33)1 69 15 66 35 _______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost I'm interested on your proposal. Could you share your prototype and the slides? Thanks, Vicente
Here is the slides. I have to dig a few for the code cause it was on another machine. I basically used fusion extension to build fusion comaptoble structure pout of DB schemas then use proto to buidl up the sql statement. -- ___________________________________________ Joel Falcou - Assistant Professor PARALL Team - LRI - Universite Paris Sud XI Tel : (+33)1 69 15 66 35
joel wrote:
Back at Boost'Con 09 I proposed a function based and not text based SQL interface using proto that looked like :
Here's my idea after some experimenting : namespace tables { BOOST_RDB_BEGIN_TABLE(person) BOOST_RDB_COLUMN(int, id) BOOST_RDB_COLUMN(int, name) BOOST_RDB_END_TABLE() BOOST_RDB_BEGIN_TABLE(link) BOOST_RDB_COLUMN(int, husband) BOOST_RDB_COLUMN(int, wife) BOOST-RDB_END_TABLE() } // aliases tables::person husband("h"), wife("w"); // SELECT h.id, w.id, h.name FROM person h, person w, link // WHERE h.id = link.husband AND link.wife = wife.id auto query = select(husband.id, wife.id, husband.name).from(husband, wife, link::_) .where(husband.id == link::_.husband && link::_.wife == wife.id); // less sure about the rest... auto statement = db.prepare(query); auto cursor = statement.execute(); cursor.fetch(); cout << cursor[0] << endl; // positional : husband.id cout << cursor[husband.id] << endl; cout << cursor[husband.name] << endl; // error : not in selected columns int id = cursor[2] << endl; // error : it's a string // SELECT * FROM person WHERE name = ? auto query = select(person::_).from(person::_) .where(person::_.name == _); auto statement = db.prepare(query); statement.values("Simpson"); auto cursor = statement.execute(); // etc
I have the whole ppt of my proposal somewhere if soemone's interested.
I am.
I have alas not much free time for this but I had some prototype code laying somewhere Me neither ;-)
J-L
Jean-Louis Leroy napsal(a):
joel wrote:
Back at Boost'Con 09 I proposed a function based and not text based SQL interface using proto that looked like :
Here's my idea after some experimenting :
namespace tables { BOOST_RDB_BEGIN_TABLE(person) BOOST_RDB_COLUMN(int, id) BOOST_RDB_COLUMN(int, name) BOOST_RDB_END_TABLE()
BOOST_RDB_BEGIN_TABLE(link) BOOST_RDB_COLUMN(int, husband) BOOST_RDB_COLUMN(int, wife) BOOST-RDB_END_TABLE() }
// aliases tables::person husband("h"), wife("w");
// SELECT h.id, w.id, h.name FROM person h, person w, link // WHERE h.id = link.husband AND link.wife = wife.id
auto query = select(husband.id, wife.id, husband.name).from(husband, wife, link::_) .where(husband.id == link::_.husband && link::_.wife == wife.id);
// less sure about the rest...
auto statement = db.prepare(query);
auto cursor = statement.execute(); cursor.fetch(); cout << cursor[0] << endl; // positional : husband.id cout << cursor[husband.id] << endl; cout << cursor[husband.name] << endl; // error : not in selected columns int id = cursor[2] << endl; // error : it's a string
// SELECT * FROM person WHERE name = ?
auto query = select(person::_).from(person::_) .where(person::_.name == _);
auto statement = db.prepare(query); statement.values("Simpson");
auto cursor = statement.execute(); // etc
I have the whole ppt of my proposal somewhere if soemone's interested.
I am.
I have alas not much free time for this but I had some prototype code laying somewhere Me neither ;-)
J-L
_______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost
I like this example. It seems to be more realistic then the previous. The column definition should be indeed local in table definition. Also random-access to result set is rarely required, this solution with cursors will map much better to underlying sql machinery. If I want to fetch records in certain index range I use LIMIT/OFFSET SQL commands. I have few comments: 1) all other common operations like INSERT, DELETE, UPDATE should be supported. Transactions can be controled through lower-level interface. 2) the table definition should be used not only for querying the table but possibly also for creating a table 3) in that case, other needed feature is to mark table columns as null/not null and unique. Maybe also as primary/foreign key. 4) something like BOOST_RDB_FUNCTION(prototype, name) would be very useful. The aggregate functions MIN, MAX, COUNT, SUM and other heavily used functions like COALESCE should be predefined by default (without these no real queries can be made) 5) the use of automatic joining conditions should be at least considered. When defining a column one could mark it as foreign key with reference to other table::column like when writing create table sql script. Then the joining condition could be ommited when referencing table in .join() command. Syntax fow specifying OUTER JOINS is needed. There is a python library named Django which has similar aims. Some of these features are supported there and this framework is becoming very popular. Altough it's expressions syntax in select clausules is ugly, I Am sure there are some interesting ideas for inspiration. Regards Tomas
Tomas wrote:
1) Transactions can be controled through lower-level interface.
Transactions should be represented by transaction objects, so that if they're destroyed before a commit() member function is called, the transaction is automatically rolled back. This is an easy way to make transactions exception-safe. --Jeffrey Bosboom
on Wed Sep 09 2009, Tomas <pecholt-AT-centrum.cz> wrote:
There is a python library named Django which has similar aims. Some of these features are supported there and this framework is becoming very popular. Altough it's expressions syntax in select clausules is ugly, I Am sure there are some interesting ideas for inspiration.
Indeed. See also http://groups.google.com/group/django-developers/browse_frm/thread/ff3631359... -- Dave Abrahams BoostPro Computing http://www.boostpro.com
Tomas :
I like this example. It seems to be more realistic then the previous. The column definition should be indeed local in table definition. Also random-access to result set is rarely required, this solution with cursors will map much better to underlying sql machinery. If I want to fetch records in certain index range I use LIMIT/OFFSET SQL commands.
Unfortunately this : cout << cursor[husband.id] << endl; ...cannot be made to work purely at compile-time. Consider cout << cursor[husband.id] << cursor[wife.id] << endl; Both ids have the same type. Some run-time discrimination would be needed (probably a lookup in a map keyed by the address of the table objects). It is type-safe however. I have ideas for purely compile-time alternatives but the syntax is heavier. OTOH positional access can be purely compile-time, but the syntax would not use brackets, rather tuple-style accessors : cout << cursor.get<0> << endl; // positional : husband.id Incidentally - my real interest is object-relational mapping, but before working on that I'd need a boost sql library. Hence my initial post... J-L
I am writing the library that implements the syntax I described. I will show working code soon (or right now upon request). I am sorely tempted to overload the comma operator for "select", "from" and "in". I know the usual caveats (low precedence, default behavior exists for UDTs). The alternatives for open lists are : use the preprocessor to crank out overloads like in the MPL ; build lists one at a time [i.e. select(t1.id)(t2.name)(t3.age).from(t1)(t2)(t3).where(t1.id.in(1)(2))]...and overload the comma operator on well-targeted types [e.g. template<typename T> operator ,(expression<T>, expression<T>)]. And perhaps make the comma operator available but in a namespace (gives choice but usually alternative syntaxes are a bad idea). Opinions ? J-L
On Wed, Sep 16, 2009 at 7:54 AM, Jean-Louis Leroy <jl@yorel.be> wrote:
Opinions ?
I think overloading , for strongly typed and explicitly constructed stuffs, like a good expression<T> template, is a good idea. This way, we can write select(foo1, foo2, foo3) without fear, since there isn't any precedence related issue here, AFAIK. The same for from and where (it may be trickier for the latter). -- Alp Mestan http://blog.mestan.fr/ http://alp.developpez.com/
Jean-Louis Leroy wrote:
Alp Mestan wrote:
The same for from and where (it may be trickier for the latter).
where takes a single argument so there is no problem here...
On second thought I think I see your point. This should not compile : select(...).from(...).where(predicate1, predicate2) However my plan is to make the comma build an expression_list. select() will take an expression_list but where() will accept only an expression (and a boolean expression at that). J-L
You'll then introduce a "condition" DSEL to generate a predicate used by the where without creating like(), between() functions etc ? On Wed, Sep 16, 2009 at 7:04 PM, Jean-Louis Leroy <jl@yorel.be> wrote:
Alp Mestan wrote:
The same for from and where (it may be trickier for the latter).
where takes a single argument so there is no problem here...
J-L
_______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost
-- Alp Mestan http://blog.mestan.fr/ http://alp.developpez.com/
Alp Mestan wrote:
You'll then introduce a "condition" DSEL to generate a predicate used by the where without creating like(), between() functions etc ?
Like this : select((t.id)).from(t).where(t.name.like("pattern")) select((t1.id, t2.id)).from((t1, t2, p)) .where((t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id) Hmm. The double parentheses are needed because the argument separator role of the comma has priority over its role as an operator. It may become disturbing to have a mixture of cases where double parentheses are mandatory and cases where they are not... J-L
Looks fine to me ! How will you handle backends for many of the RDBMS ? (if you will) On Wed, Sep 16, 2009 at 8:12 PM, Jean-Louis Leroy <jl@yorel.be> wrote:
Alp Mestan wrote:
You'll then introduce a "condition" DSEL to generate a predicate used by the where without creating like(), between() functions etc ?
Like this :
select((t.id)).from(t).where(t.name.like("pattern"))
select((t1.id, t2.id)).from((t1, t2, p)) .where((t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id)
Hmm. The double parentheses are needed because the argument separator role of the comma has priority over its role as an operator. It may become disturbing to have a mixture of cases where double parentheses are mandatory and cases where they are not...
J-L _______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost
-- Alp Mestan http://blog.mestan.fr/ http://alp.developpez.com/
No. I'm rather thinking about connecting all these things to a large spectrum of RDBMS (I guess you plan to do so). On Wed, Sep 16, 2009 at 8:55 PM, Jean-Louis Leroy <jl@yorel.be> wrote:
Alp Mestan wrote:
How will you handle backends for many of the RDBMS ? (if you will)
You mean string e.g. string literals, e.g. 'O''Hara' vs 'O\'Hara' (Postgres) ?
J-L
_______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost
-- Alp Mestan http://blog.mestan.fr/ http://alp.developpez.com/
Alp Mestan wrote:
No. I'm rather thinking about connecting all these things to a large spectrum of RDBMS (I guess you plan to do so).
I would like each backend to be a very thin layer on top of the native API. And make it easy to write, at least the basics (direct exec, prepare statements, bind results and parameters, execute prepared statements and iterate with a cursor, transactions). You can already go a long way with that. I have been able to make a very capable object-relational mapper with just 340 lines of ODBC-specific code. Boost.RDB is likely to be a never-ending projects, what matters is that it's copiously useful and the rest will come with time... J-L
Am Wednesday 16 September 2009 20:39:16 schrieb Alp Mestan:
Looks fine to me !
How will you handle backends for many of the RDBMS ? (if you will)
this seems to be the bigger task to me too, bigger than SQL syntax details. I'd welcome such a library very much, as I'm developing an object persistence library for boost, and my relatively untested storage backend (compared to industrial SQL databases) would probably be a restraint against using my library. but binding my library to one specific database isn't an option either, so it needs a boost database layer to support other backends.
Stefan Strasser wrote:
Am Wednesday 16 September 2009 20:39:16 schrieb Alp Mestan:
Looks fine to me !
How will you handle backends for many of the RDBMS ? (if you will)
this seems to be the bigger task to me too, bigger than SQL syntax details.
I'm targeting a subset of ODBC to start with. The idea is to have a framework that makes it easy to add features piecemeal, and hope that the community helps with the other backends.
I'd welcome such a library very much, as I'm developing an object persistence library for boost
Ironically object-relational mapping is my real focus. I'm toying with this lib as an en-route. Boost has been there for a long time, it's surprising that there is no Boost.RDB yet. J-L
Am Wednesday 16 September 2009 21:13:25 schrieb Jean-Louis Leroy:
I'd welcome such a library very much, as I'm developing an object persistence library for boost
Ironically object-relational mapping is my real focus. I'm toying with this lib as an en-route. Boost has been there for a long time, it's surprising that there is no Boost.RDB yet.
my proposal will be ready for review pretty soon, I'm working on documentation and optimizations. the internals are pretty hardcoded to using an own transaction mechanism and storage backend right now, but there's nothing in the interface that would prevent us from making both libraries work together later on. I've also thought about a query funtionality for my library, like SQL SELECT provides, but concluded its beyond the scope of my library. but it would be great if there would be a query language/syntax once, that covers querying SQL databases, persistent objects and querying from STL containers all at once. conceptually there is little difference between a SQL index column, a std::map and a bunch of objects with an index field.
Stefan Strasser wrote:
I've also thought about a query funtionality for my library, like SQL SELECT provides, but concluded its beyond the scope of my library.
Take a look at this : http://tangram.utsl.gen.nz/ (an old project of mine) - if you accept to glance at Perl code ;-) J-L
By the way, mysql++ provide some sort of object-relational mapping, it may be of interest for you guys to give it a whirl. On Wed, Sep 16, 2009 at 9:26 PM, Jean-Louis Leroy <jl@yorel.be> wrote:
Stefan Strasser wrote:
I've also thought about a query funtionality for my library, like SQL
SELECT provides, but concluded its beyond the scope of my library.
Take a look at this : http://tangram.utsl.gen.nz/ (an old project of mine) - if you accept to glance at Perl code ;-)
J-L
_______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost
-- Alp Mestan http://blog.mestan.fr/ http://alp.developpez.com/
Jean-Louis Leroy wrote:
Like this :
select((t.id)).from(t).where(t.name.like("pattern"))
select((t1.id, t2.id)).from((t1, t2, p)) .where((t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id)
Do you plan to support non trivial projections in select()? For example: SELECT CONCAT(last_name,', ',first_name) AS full_name
Jean-Louis Leroy wrote:
Alp Mestan wrote:
You'll then introduce a "condition" DSEL to generate a predicate used by the where without creating like(), between() functions etc ?
Like this :
select((t.id)).from(t).where(t.name.like("pattern"))
select((t1.id, t2.id)).from((t1, t2, p)) .where((t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id)
Hmm. The double parentheses are needed because the argument separator role of the comma has priority over its role as an operator. It may become disturbing to have a mixture of cases where double parentheses are mandatory and cases where they are not...
I didn't follow the thread but you might consider square brackets instead: select[ t1.id, t2.id ].from[ t1, t2, p ] .where [ (t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id ]
I didn't follow the thread but you might consider square brackets instead:
select[ t1.id, t2.id ].from[ t1, t2, p ] .where [ (t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id ] Interesting idea. The kind that pops up when you consider overloading
Andrey Semashev wrote: the comma :-D Thanks, J-L
On 2009-09-16T20:12:13, Jean-Louis Leroy wrote:
Like this :
select((t.id)).from(t).where(t.name.like("pattern"))
select((t1.id, t2.id)).from((t1, t2, p)) .where((t1.income + t2.income).between(20, 40) && p.husband == t1.id && p.wife == t2.id)
I recently played with most of the perl modules to generate SQL, and none really worked well for some semi-fancy reporting queries. On one hand I wanted a data structure so I could build a query dynamically: add column X to the select and group by expression, then do it for Y. Here is a collection of conditions for the where clause which should be 'AND'ed together etc. However if you build your entire query you are forced into using this new API even if most of features add no value, or when it adds overhead to something you can easily just type out: "select count(*) from tbl" "select (select max(value) from tbl1 where tbl1.id = tbl2.id) from tbl2 where ... group by tbl2.name" It would be great if you could start with a string of SQL, have the library parse it and provide features to refine expressions. Let the library optionally bind and escape values. Provide control over how the output should be normalized (case of keywords, sub query indent, field separators etc), and allow the SQL to be dumped. mysql, for instance, caches the exact string of the query. The SQL generation library should be separate from whatever talks to the database. You may not need to talk to the database, or there might be lots of magic with database handlers (load balancing, pooling, keep-alive etc). I hope it helps. /Allan -- Allan Wind Life Integrity, LLC <http://lifeintegrity.com>
For the curious, I have uploaded stuff in the vault (database section of course). Please note: * It's a moving target. * I develop on Windows with Visual Studio Express 2008. There is a solution file in the msvc dir. Nothing bjam works yet. * First thing to look at is probably the test suite in libs/rdb//test/test.cpp J-L
On Wed, Sep 16, 2009 at 4:30 PM, Jean-Louis Leroy <jl@yorel.be> wrote:
/* snip */
In relation to everything from building up the back-end expressions, optimizing for DB types, etc... Python's SQLAlchemy would be a wonderful thing to emulate, it does it all so well, in the vein of exactly what is being done here.
How do you deal with columns that have the same name but different types in different tables ? And with table aliases (e.g. when dealing with self-joins) ? J-L
Jean-Louis Leroy wrote:
How do you deal with columns that have the same name but different types in different tables ? And with table aliases (e.g. when dealing with self-joins) ?
Currently I don't as I wasn't even aware of this kind of use-case. As I said, it was a mere proof of concept. DB are not my main domain of expertise, so if anyone know what's wrog/right with this design, feel free to tell me and I'll see. -- ___________________________________________ Joel Falcou - Assistant Professor PARALL Team - LRI - Universite Paris Sud XI Tel : (+33)1 69 15 66 35
Joel Falcou wrote:
Jean-Louis Leroy wrote: How do you deal with columns that have the same name but different types in different tables ? And with table aliases (e.g. when dealing with self-joins) ?
Currently I don't as I wasn't even aware of this kind of use-case. As I said, it was a mere proof of concept. DB are not my main domain of expertise, so if anyone know what's wrog/right with this design, feel free to tell me and I'll see.
An interesting DSL for SQL queries was presented in an IBM event in 2007: http://www.haifa.ibm.com/Workshops/ple2007/present/Simple_and_safe_SQL.pdf Best regards Jorge
Jean-Louis Leroy wrote:
They have this in common : the query is expressed as SQL text (à la Boost.Regex) that is parsed at run time instead of using language constructs (à la Boost.Xpressive) that can be checked at compile time. This is error prone : you can pass illegal SQL ; and the returned values will incur runtime typecasting, which may fail at run time in case of mismatch.
While I agree this is indeed a great feature, I don't think it is a necessary one for the first version of a library. Indeed, once you have the necessary features to send requests as strings, you can build this on top of these as a SQL request string generator. Also, a great thing with such a feature that you didn't mention would be the ability of writing portable requests instead of having to rely on specific SQL extensions, since the request could be analyzed and generate different SQL depending on the backend.
Mathias Gaunard wrote:
While I agree this is indeed a great feature, I don't think it is a necessary one for the first version of a library. Indeed, once you have the necessary features to send requests as strings, you can build this on top of these as a SQL request string generator. That's the point I raised at Boost'Con. The high-level layer should be completely indpt of the SQL query generator/excution layer.
-- ___________________________________________ Joel Falcou - Assistant Professor PARALL Team - LRI - Universite Paris Sud XI Tel : (+33)1 69 15 66 35
Jean-Louis Leroy wrore
What's the situation with SQL client libraries (similar to Rogue Wave's DBTools.h++) ? I know that there's a boost_sql_cli in the Vault, but it doesn't seem to make much progress. I have also heard about plans to bring Soci into Boost.
I toiled with some ideas a while ago around this. My goal was to be able to write something like this: using namespace database; field::integer age; field::date dob; field::string name; table employee; connection db("connect_string","user","passwd"); recordset rs(db); rs = SELECT name,age,dob FROM employee WHERE age>45; while (rs.fetch()) { std::cout << name << "\t" << age << "\t" << dob << "\n"; } In the absence of whitespace overloading, the only way to achieve this syntax is to use the preprocessor to define SELECT, FROM and WHERE to be something than can invoke a binary operator. This is less than ideal as the C++ preprocessor is global, so you can't restrict SELECT definition to be in the database namespace. It is impossible to support SQL aliases or tablename prefixes with this syntax, though. With the small problem of macros - which I see no way to oversome - and is actually quite a big problem, I did achieve some success using template expressions to build a query and generate SQL that is executed and bound to a recordset. Regards -- Craig
Craig Henderson wrote:
In the absence of whitespace overloading, the only way to achieve this syntax is to use the preprocessor to define SELECT, FROM and WHERE to be something than can invoke a binary operator. This is less than ideal as the C++ preprocessor is global, so you can't restrict SELECT definition to be in the database namespace. It is impossible to support SQL aliases or tablename prefixes with this syntax, though. I think having select(a,b,c).from() is already concise & readable and not bound to macro limitation.
-- ___________________________________________ Joel Falcou - Assistant Professor PARALL Team - LRI - Universite Paris Sud XI Tel : (+33)1 69 15 66 35
participants (19)
-
Allan Wind -
Alp Mestan -
Andrey Semashev -
Brian Ravnsgaard Riis -
Christoph Heindl -
Craig Henderson -
David Abrahams -
Eric MALENFANT -
Jean-Louis Leroy -
Jeffrey Bosboom -
joel -
Jorge Lodos Vigil -
Mateusz Loskot -
Mathias Gaunard -
OvermindDL1 -
Phil Endecott -
Stefan Strasser -
Tomas -
vicente.botet