Hi, currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are: * Stay close to SQL syntax * Do as much type checking as possible * prevent oversights and typical security holes, e.g. by forgetting to escape strings The basic idea is, that given a table definition, it should be possible to * offer a lot of checks at compile time for building a query * make it hard to misinterpret the results Until now, I have been concentrating on table definitions and select statements. Here's a very simplistic example, assuming a table definition "my_table" and a database connection "db": // ------------------------------------------------------------------------- using boost; typedef my_table<> t; typedef sql::select_record<t> record; std::string name("Peter"); ... std::vector<record> records = db.select<record>( sql::where(t::id() > 1000 && t::first_name() == name), sql::order_by(t::priority()(sql::desc)), sql::limit(17)); // the values can be accessed by their name // and are of the correct type, e.g. records.front().first_name_; // is a string records.front().middle_name_; // optional<string> because the table definition says that it can be NULL // In case of a select from more than one table, the values can also be accessed like this records.front().my_table_.first_name; // ------------------------------------------------------------------------- I wonder if there would be interest for such a library in boost.
On Tue, Sep 14, 2010 at 8:26 AM, Roland Bock <rbock@eudoxos.de> wrote:
currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
* Stay close to SQL syntax * Do as much type checking as possible
Static type checking is always a plus. I tried going that route too, but found it a bit cumbersome (and my C++ lacking too ;) One of the problem with trying to generate queries like this is also the complexity of the SQL language itself, with nested queries, and all the various extensions and idiosyncrasies of the different SQL vendors. I've lurked on the Oracle forums for a few months, and it's open my eyes to the complexity of SQL (the Oracle dialect at least).
* prevent oversights and typical security holes, e.g. by forgetting to escape strings
Using bind values also avoids such issues, AFAIK
The basic idea is, that given a table definition, it should be possible to * offer a lot of checks at compile time for building a query * make it hard to misinterpret the results
The few C++ libraries I've seen in this area take a less ambitious approach, and typically provide instead an easy way to pass typed C++ values as SQL bind values, and get back the records as typed tuples for examples. The SQL text must still be correct, and will be parsed and type checked only at runtime by the DB, but in practice, with unit testing, I find that it works fairly well. A good and established library already mentioned on this list in this domain is http://soci.sourceforge.net/, which uses modern C++ and Boost. With Steven Watanabe's help from this list, I used a little fusion + tuple magic to easily bind and fetch records in a similar manner with SQLite, but that only checks arity of the select clause, and that enough bind values are provided, and it's not checking the values provided or gotten are of the right type at compile time. From my experience though, after having tried the statically type-checked approach as well, I found such an approach to be flexible and "good enough".
Until now, I have been concentrating on table definitions and select statements.
If you go that route, you'll need a way to generate the static C++ definition of the tables from existing Schemas, otherwise maintaining these to be in sync might be problematic. Then again the same applies to all the SQL text in more traditional approaches.
I wonder if there would be interest for such a library in boost.
Getting a library accepted in Boost is a tall order for sure. I'm just a lurker here, but given the recent threads on a GUI lib and a C++ Parser, I'd say you'd need to commit several months of hard work and have a pretty thick skin too. That's not even getting into which backend DB the library would support, the SQL vs NoSQL debate, etc... My own feeling is that you should explore this subject into your own public project for while, and try to attract a community of volunteers. Then again, once you've reached some kind of milestone you may want to revisit a possible submission to Boost. But I'm certainly no expert in the matter. Good luck to you, --DD
On 09/14/2010 04:12 PM, Dominique Devienne wrote:
On Tue, Sep 14, 2010 at 8:26 AM, Roland Bock<rbock@eudoxos.de> wrote:
currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
* Stay close to SQL syntax * Do as much type checking as possible
Static type checking is always a plus. I tried going that route too, but found it a bit cumbersome (and my C++ lacking too ;) One of the problem with trying to generate queries like this is also the complexity of the SQL language itself, with nested queries, and all the various extensions and idiosyncrasies of the different SQL vendors. I've lurked on the Oracle forums for a few months, and it's open my eyes to the complexity of SQL (the Oracle dialect at least).
Yes, the code looks "interesting" so far. And I have learned quite a lot about boost.mpl in the past few weeks :-) I think I have solved (conceptually) the problem of nested queries, but the differences between the different vendors are certainly a huge problem.
The basic idea is, that given a table definition, it should be possible to * offer a lot of checks at compile time for building a query * make it hard to misinterpret the results
The few C++ libraries I've seen in this area take a less ambitious approach, and typically provide instead an easy way to pass typed C++ values as SQL bind values, and get back the records as typed tuples for examples. The SQL text must still be correct, and will be parsed and type checked only at runtime by the DB, but in practice, with unit testing, I find that it works fairly well.
Sure, but you have to do quite some testing to prevent all the possible oversights from happening. For instance, if you have a tuple of strings, it is fairly simple to mix them up. If the compiler could give you names for it, it would be harder to misuse.
A good and established library already mentioned on this list in this domain is http://soci.sourceforge.net/, which uses modern C++ and Boost.
I've seen it. It is certainly one of the best I've seen so far.
If you go that route, you'll need a way to generate the static C++ definition of the tables from existing Schemas, otherwise maintaining these to be in sync might be problematic. Then again the same applies to all the SQL text in more traditional approaches.
Right, as of now, we are using a small DDL->Header conversion program.
My own feeling is that you should explore this subject into your own public project for while, and try to attract a community of volunteers. Then again, once you've reached some kind of milestone you may want to revisit a possible submission to Boost. But I'm certainly no expert in the matter. Good luck to you, --DD
I would have assumed one year minimum before it comes even close. And I am pretty sure I could not cover all aspects all on my own (e.g. OS, DBS, compiler). Regards, Roland
On Tue, Sep 14, 2010 at 9:38 AM, Roland Bock <rbock@eudoxos.de> wrote:
I would have assumed one year minimum before it comes even close. And I am pretty sure I could not cover all aspects all on my own (e.g. OS, DBS, compiler).
You sound motivated, and from your other answers farther along that you initially let out. Given that it's a subject of interest to me, I'd gladly have a closer look once it's available somewhere. As far as back-ends, I have experience with SQLite and Oracle OCI. Talking about Oracle too, what about "Object-Relational" support? This support is mandatory I believe to get into XMLType and all the Oracle Spatial "datatypes". In my experience, you can't wrap / automate everything, and you need to leave the door open for raw access to the underlying API to communicate directly with the back-end "natively". My $0.02. --DD
On 09/14/2010 05:20 PM, Dominique Devienne wrote:
On Tue, Sep 14, 2010 at 9:38 AM, Roland Bock<rbock@eudoxos.de> wrote:
I would have assumed one year minimum before it comes even close. And I am pretty sure I could not cover all aspects all on my own (e.g. OS, DBS, compiler).
You sound motivated, and from your other answers farther along that you initially let out. Given that it's a subject of interest to me, I'd gladly have a closer look once it's available somewhere. As far as back-ends, I have experience with SQLite and Oracle OCI.
I'll let you know :-)
Talking about Oracle too, what about "Object-Relational" support? This support is mandatory I believe to get into XMLType and all the Oracle Spatial "datatypes". In my experience, you can't wrap / automate everything, and you need to leave the door open for raw access to the underlying API to communicate directly with the back-end "natively". My $0.02. --DD
Yes, the library would have to offer several back doors. There is just no way to cover all, I guess.
On 14/09/10 16:38, Roland Bock wrote:
On 09/14/2010 04:12 PM, Dominique Devienne wrote:
A good and established library already mentioned on this list in this domain is http://soci.sourceforge.net/, which uses modern C++ and Boost.
I've seen it. It is certainly one of the best I've seen so far.
FYI, the team is applying final changes and new release is coming very soon. If you'd like to try it, please check the version from Git repo - source code is considered as stable, only floating element is build system. BTW, in case you haven't been aware of this initiative: std::rdb http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb-crystalclear... I'm keenly tracking this thread, looking forward to seeing new SQL ideas. Best regards, -- Mateusz Loskot, http://mateusz.loskot.net Charter Member of OSGeo, http://osgeo.org
On 09/15/2010 12:02 PM, Mateusz Loskot wrote:
On 14/09/10 16:38, Roland Bock wrote:
On 09/14/2010 04:12 PM, Dominique Devienne wrote:
A good and established library already mentioned on this list in this domain ishttp://soci.sourceforge.net/, which uses modern C++ and Boost.
I've seen it. It is certainly one of the best I've seen so far.
FYI, the team is applying final changes and new release is coming very soon. If you'd like to try it, please check the version from Git repo - source code is considered as stable, only floating element is build system.
BTW, in case you haven't been aware of this initiative: std::rdb
http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb-crystalclear...
I wasn't aware of that one. Is it in active development? The mailing list looks a bit forsaken.
I'm keenly tracking this thread, looking forward to seeing new SQL ideas.
OK, taking the first example from the SOCI project page: // --------------------------------------------------- sql<< "select name, salary from persons where id ="<< id, into(name), into(salary); // --------------------------------------------------- This is nice, but still requires attention where the compiler could help, if the table definition was known. Here is what it would look like in my version (assuming that I manage the transition to boost proto): // --------------------------------------------------- typedef table_persons<> persons; typedef select_record<persons, persons::name, persons::salary> record; vector<record> records = db.query() << select<record>() << where(t::id() == id); do_something(records.front().name_, records.front().salary_); // --------------------------------------------------- Here are the pros and cons of my approach that I am aware of * CON: It requires the compiler to have intimate knowledge of the table definition and you don't want to manually manage changes in the C++ table definition (even though it is not too ugly). Thus, some code generator is required in the build process. * CON: If you want to use nested selects, you would have to define "pseudo table classes" to use them (at least that is my current concept for that). Up to now, I cannot think of a way to do all the type-checking and named value provision without that. * PRO: No typo within a string like "select name, salary from persons where id = " will pass the compiler. * PRO: There is no chance that you mismatch the order of retrieved columns, e.g. into(salary), into(name). In this simple example, such an error is unlikely, but if you have, say, ten elements? Maybe it would make sense to add such a layer as an option to SOCI? Regards, Roland
Hi, On 9/15/2010 1:46 PM, Roland Bock wrote:
OK, taking the first example from the SOCI project page:
sql<< "select name, salary from persons where id ="<< id, into(name), into(salary); [...]
This is nice, but still requires attention where the compiler could help, if the table definition was known. Here is what it would look like in my version (assuming that I manage the transition to boost proto): [...]
Maybe it would make sense to add such a layer as an option to SOCI?
In all example programs SOCI takes the string as the query, but in fact it can be anything that is "streamable". This makes it possible to add any imaginable layer that will ultimately end up as an object that will produce the intended text query when streamed out. That is: sql << HERE-YOUR.SMART%STATIC|QUERY&MAKING*ENGINE, into(name), into(salary); The problem is that there is still no relation between the query (however it is made) and the bound data. A little bit more work would be needed to provide this level of integration, but it can be done with relative ease. Please read this part of the SOCI documentation to see where such ideas might fit: http://soci.sourceforge.net/doc/interfaces.html I think that the "core" interface is a proper level for this kind of integration. Regards, -- Maciej Sobczak * www.msobczak.com * www.inspirel.com
On 09/15/2010 03:43 PM, Maciej Sobczak wrote:
Hi,
On 9/15/2010 1:46 PM, Roland Bock wrote:
OK, taking the first example from the SOCI project page:
sql<< "select name, salary from persons where id ="<< id, into(name), into(salary); [...]
This is nice, but still requires attention where the compiler could help, if the table definition was known. Here is what it would look like in my version (assuming that I manage the transition to boost proto): [...]
Maybe it would make sense to add such a layer as an option to SOCI?
In all example programs SOCI takes the string as the query, but in fact it can be anything that is "streamable". This makes it possible to add any imaginable layer that will ultimately end up as an object that will produce the intended text query when streamed out. That is:
sql << HERE-YOUR.SMART%STATIC|QUERY&MAKING*ENGINE, into(name), into(salary); I guess I could also wrap this
rowset<row> rs = (sql.prepare<< "select id, firstname, lastname, gender from person"); into something that immediately returns vector<smart_row> (or boost::range, as proposed by Mathias). Since the "smart&static" engine knows what it asks for, it can assign row[0] to smart_row.id, row[1] to smart_row.firstname , etc. The rowset and its iterators are rather similar to the stuff from libpqxx which I currently use... I'll have a closer look at the interface documentation, soon (struggling with my first steps with Boost::Proto for the moment). Regards, Roland
Here's a thread that might be interesting for anyone thinking of implementing a DSEL for SQL: http://markmail.org/message/rzcdlkquko2htvjp I notice that most of this thread is going in the direction of trying to replicate SQL syntax instead of doing something closer to normal logic. I'd be much happier to use a syntax that just specifies "what I want" rather than "how to get there." In some cases the library could be responsible for conjuring up JOINs, for example, when that's the most efficient approach. -- Dave Abrahams BoostPro Computing http://www.boostpro.com
On 09/15/2010 05:11 PM, Dave Abrahams wrote:
Here's a thread that might be interesting for anyone thinking of implementing a DSEL for SQL: http://markmail.org/message/rzcdlkquko2htvjp
Thanks. Interesting read :-) Taking one of the examples you gave in that thread: // ----------------------------- Entry.objects.filter( _.headline.startswith('What'), _.pub_date<datetime.now(), _.pub_date>=datetime(2005, 1, 1) ); // ----------------------------- Here's what I want to achieve (and my prototype is very close): // ----------------------------- [...] where( t.headline.startswith("What") && t.pub_date < datetime.now(), && t.pub_date >= datetime(2005, 1, 1) ); // ----------------------------- (t being an instance of the table class) Admittedly, I write "where" instead of "filter", but other than that, this is pretty close, right? :-)
I notice that most of this thread is going in the direction of trying to replicate SQL syntax instead of doing something closer to normal logic. I'd be much happier to use a syntax that just specifies "what I want" rather than "how to get there." In some cases the library could be responsible for conjuring up JOINs, for example, when that's the most efficient approach.
My current code automatically determines which tables you need for your query. Thus, the FROM part is not your responsibility. But no other magic is conjured up... Do you have something more specific in mind regarding the syntax? Here is an example from http://sqlzoo.net/select_select SELECT name, A.region FROM bbc AS A JOIN (SELECT region,MAX(population) AS maxpop FROM bbc GROUP BY region) AS B ON (A.region=B.region AND A.population=maxpop) It selects the names of the population-wise biggest countries from each region. How would you want to express that? Regards, Roland
On Sep 15, 2010, at 11:55 AM, Roland Bock wrote:
On 09/15/2010 05:11 PM, Dave Abrahams wrote:
Here's a thread that might be interesting for anyone thinking of implementing a DSEL for SQL: http://markmail.org/message/rzcdlkquko2htvjp
Thanks. Interesting read :-)
Taking one of the examples you gave in that thread:
// ----------------------------- Entry.objects.filter( _.headline.startswith('What'), _.pub_date<datetime.now(), _.pub_date>=datetime(2005, 1, 1) ); // -----------------------------
Here's what I want to achieve (and my prototype is very close):
// ----------------------------- [...] where( t.headline.startswith("What") && t.pub_date < datetime.now(), && t.pub_date >= datetime(2005, 1, 1) ); // -----------------------------
(t being an instance of the table class)
Admittedly, I write "where" instead of "filter", but other than that, this is pretty close, right? :-)
Yup. And I'd have written it with && instead of , if I wasn't addressing a Python audience (can't overload && in Python).
I notice that most of this thread is going in the direction of trying to replicate SQL syntax instead of doing something closer to normal logic. I'd be much happier to use a syntax that just specifies "what I want" rather than "how to get there." In some cases the library could be responsible for conjuring up JOINs, for example, when that's the most efficient approach.
My current code automatically determines which tables you need for your query. Thus, the FROM part is not your responsibility. But no other magic is conjured up...
Do you have something more specific in mind regarding the syntax? Here is an example from http://sqlzoo.net/select_select
SELECT name, A.region FROM bbc AS A JOIN (SELECT region,MAX(population) AS maxpop FROM bbc GROUP BY region) AS B ON (A.region=B.region AND A.population=maxpop)
It selects the names of the population-wise biggest countries from each region.
How would you want to express that?
Jeez, now I have to re-learn SQL again! :-) Let's see... bbc is a table containing name: string region: string population: integer ? I'll have to give that a little thought. -- David Abrahams BoostPro Computing http://boostpro.com
On 09/16/2010 03:30 AM, David Abrahams wrote:
On Sep 15, 2010, at 11:55 AM, Roland Bock wrote:
On 09/15/2010 05:11 PM, Dave Abrahams wrote:
Here's a thread that might be interesting for anyone thinking of implementing a DSEL for SQL: http://markmail.org/message/rzcdlkquko2htvjp
Thanks. Interesting read :-)
Taking one of the examples you gave in that thread:
// ----------------------------- Entry.objects.filter( _.headline.startswith('What'), _.pub_date<datetime.now(), _.pub_date>=datetime(2005, 1, 1) ); // -----------------------------
Here's what I want to achieve (and my prototype is very close):
// ----------------------------- [...] where( t.headline.startswith("What") && t.pub_date< datetime.now(), && t.pub_date>= datetime(2005, 1, 1) ); // -----------------------------
(t being an instance of the table class)
Admittedly, I write "where" instead of "filter", but other than that, this is pretty close, right? :-)
Yup. And I'd have written it with&& instead of , if I wasn't addressing a Python audience (can't overload&& in Python).
I notice that most of this thread is going in the direction of trying to replicate SQL syntax instead of doing something closer to normal logic. I'd be much happier to use a syntax that just specifies "what I want" rather than "how to get there." In some cases the library could be responsible for conjuring up JOINs, for example, when that's the most efficient approach.
My current code automatically determines which tables you need for your query. Thus, the FROM part is not your responsibility. But no other magic is conjured up...
Do you have something more specific in mind regarding the syntax? Here is an example from http://sqlzoo.net/select_select
SELECT name, A.region FROM bbc AS A JOIN (SELECT region,MAX(population) AS maxpop FROM bbc GROUP BY region) AS B ON (A.region=B.region AND A.population=maxpop)
It selects the names of the population-wise biggest countries from each region.
How would you want to express that?
Jeez, now I have to re-learn SQL again! :-)
Let's see...
bbc is a table containing
name: string region: string population: integer
?
Right. Now I want a list/vector/range/whatever allowing me to iterate over name/region pairs containing the population-wise biggest countries from the respective region. The SQL approach is to wrap two filtering actions into one 1) Create a list of struct B region: string maxpop: integer // population of the biggest country of this region 2) Create a list of struct Record name: string region: string in such a way that each record fulfills the following criteria 1. name/region are from the same entry of the bbc table 2. the region/population values of that record correspond to the region/maxpop values of the B-List from step 1 My current concept (not written in code yet) does the following a) Define a pseudo-table containing the logic of SQL-step 1: // --------------------------------------------------- class B { public: // typedefs typedef max<bbc::_population, maxpop_alias> _maxpop typedef [...] _region // public members [...] maxpop [...] region private: // internal logic [...] select<_region, _maxpop>() << group_by(region); } b; // --------------------------------------------------- b) Define the structure of the results you want: // --------------------------------------------------- typedef record<bbc, bbc::_name, bbc::_region> my_record; // --------------------------------------------------- Essentially this creates a struct which looks like this: // --------------------------------------------------- struct my_record { string name; string region; (some misc stuff) }; // --------------------------------------------------- c) Obtain the results: // --------------------------------------------------- vector<record> results = select<my_record>() << where(bbc.region == b.region && bbc.population == b.maxpop); // ---------------------------------------------------
I'll have to give that a little thought.
Looking forward to reading the results :-) Regards, Roland
Hi,
I notice that most of this thread is going in the direction of trying to replicate SQL syntax instead of doing something closer to normal logic.
Hence the name SQL.
From boost::sql i would expect ecactly that: A library that lets me use SQL in a portable way.
I'd be much happier to use a syntax that just specifies "what I want" rather than "how to get there." In some cases the library could be responsible for conjuring up JOINs, for example, when that's the most efficient approach.
Such a library would hide SQL as an implementation detail. It could provide SQL-less backends just as well. In that case SQL wouldn't be an appropriate name, Let's call it RDB instead. An RDB library surely would make a lot of us happier than an SQL library. But this doesn't render an SQL library useless. I fact, it surely would be of use for the RDB library. Cheers, Daniel
On 15/09/2010 16:11, Dave Abrahams wrote:
Here's a thread that might be interesting for anyone thinking of implementing a DSEL for SQL: http://markmail.org/message/rzcdlkquko2htvjp
I notice that most of this thread is going in the direction of trying to replicate SQL syntax instead of doing something closer to normal logic. I'd be much happier to use a syntax that just specifies "what I want" rather than "how to get there." In some cases the library could be responsible for conjuring up JOINs, for example, when that's the most efficient approach.
Relational algebra is the way databases work, and SQL is the standard language to express relational operations. Are you suggesting to create a language to query objects trees rather than work with relations, like Object-Relational mapping does? I think that's a terrible idea.
On Sep 15, 2010, at 1:58 PM, Mathias Gaunard <mathias.gaunard@ens-lyon.org> wrote:
Relational algebra is the way databases work, and SQL is the standard language to express relational operations.
Yup.
Are you suggesting to create a language to query objects trees rather than work with relations, like Object-Relational mapping does?
I was just saying what I, as a non-DB person, would prefer. I hate having to re-learn SQL every time I want to do a simple filtering or query operation.
I think that's a terrible idea.
Noted. -- BoostPro Computing * http://boostpro.com [Sent from coveted but awkward mobile device]
On 09/15/2010 01:36 PM, Dave Abrahams wrote:
On Sep 15, 2010, at 1:58 PM, Mathias Gaunard<mathias.gaunard@ens-lyon.org> wrote:
Relational algebra is the way databases work, and SQL is the standard language to express relational operations.
Yup.
Are you suggesting to create a language to query objects trees rather than work with relations, like Object-Relational mapping does?
I was just saying what I, as a non-DB person, would prefer. I hate having to re-learn SQL every time I want to do a simple filtering or query operation.
You may just shift the problem to having to re-learn a non-standard query language represented in C++ syntax every time you want to do a simple filtering or query operation. :) (I think it is unlikely that something substantially simpler to learn/remember than SQL will be devised.) By sticking with SQL, if you then have to do some query in another language or not using the same library, your knowledge will transfer over.
On Wed, Sep 15, 2010 at 6:58 PM, Mathias Gaunard <mathias.gaunard@ens-lyon.org> wrote:
On 15/09/2010 16:11, Dave Abrahams wrote:
Here's a thread that might be interesting for anyone thinking of implementing a DSEL for SQL: http://markmail.org/message/rzcdlkquko2htvjp
I notice that most of this thread is going in the direction of trying to replicate SQL syntax instead of doing something closer to normal logic. I'd be much happier to use a syntax that just specifies "what I want" rather than "how to get there." In some cases the library could be responsible for conjuring up JOINs, for example, when that's the most efficient approach.
Relational algebra is the way databases work, and SQL is the standard language to express relational operations.
Relational algebra is just one part of the relational model. The other is the Relational Calculus (http://en.wikipedia.org/wiki/Relational_calculus), which might be closer to what David is asking. SQL has aspects of both relational algebra and relational calculus of tuples. Both models are equivalents, but some queries might be easier to express in a model or the other. -- gpd
On 15/09/10 13:46, Roland Bock wrote:
On 09/15/2010 12:02 PM, Mateusz Loskot wrote:
On 14/09/10 16:38, Roland Bock wrote:
On 09/14/2010 04:12 PM, Dominique Devienne wrote:
A good and established library already mentioned on this list in this domain ishttp://soci.sourceforge.net/, which uses modern C++ and Boost.
I've seen it. It is certainly one of the best I've seen so far.
FYI, the team is applying final changes and new release is coming very soon. If you'd like to try it, please check the version from Git repo - source code is considered as stable, only floating element is build system.
BTW, in case you haven't been aware of this initiative: std::rdb
http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb-crystalclear...
I wasn't aware of that one. Is it in active development? The mailing list looks a bit forsaken.
I'm not sure. AFAIK, it was born during BoostCon'09 where Jeff Garland led workshop on this subject http://www.boostcon.com/community/wiki/show/private/2009/ I'd post to the std::rdb list and ask about its status.
I'm keenly tracking this thread, looking forward to seeing new SQL ideas.
OK, taking the first example from the SOCI project page: [...] Maybe it would make sense to add such a layer as an option to SOCI?
Maciej has already addressed this idea. Best regards, -- Mateusz Loskot, http://mateusz.loskot.net Charter Member of OSGeo, http://osgeo.org
On 09/16/2010 02:47 AM, Mateusz Loskot wrote:
On 15/09/10 13:46, Roland Bock wrote:
On 09/15/2010 12:02 PM, Mateusz Loskot wrote:
FYI, the team is applying final changes and new release is coming
very soon. If you'd like to try it, please check the version from Git repo - source code is considered as stable, only floating element is build system.
BTW, in case you haven't been aware of this initiative: std::rdb
http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb-crystalclear...
I wasn't aware of that one. Is it in active development? The mailing list looks a bit forsaken.
I'm not sure. AFAIK, it was born during BoostCon'09 where Jeff Garland led workshop on this subject
Thanks, good read :-)
I'd post to the std::rdb list and ask about its status.
Done Regards, Roland
On 14/09/10 14:26, Roland Bock wrote:
std::vector<record> records =
It probably should just return a range rather than a vector.
db.select<record>( sql::where(t::id() > 1000 && t::first_name() == name), sql::order_by(t::priority()(sql::desc)), sql::limit(17));
Have you considered using Proto to define and check the grammar of that language?
// the values can be accessed by their name // and are of the correct type, e.g. records.front().first_name_; // is a string records.front().middle_name_; // optional<string> because the table definition says that it can be NULL
How are first_name/middle_name/priority/id attached to the record type?
On 09/14/2010 04:29 PM, Mathias Gaunard wrote:
On 14/09/10 14:26, Roland Bock wrote:
std::vector<record> records =
It probably should just return a range rather than a vector.
Yes, thanks for the hint :-)
db.select<record>( sql::where(t::id() > 1000 && t::first_name() == name), sql::order_by(t::priority()(sql::desc)), sql::limit(17));
Have you considered using Proto to define and check the grammar of that language?
Yes, but I would need support for that. I admit that (as of today) Proto is too hard to understand for me.
// the values can be accessed by their name // and are of the correct type, e.g. records.front().first_name_; // is a string records.front().middle_name_; // optional<string> because the table definition says that it can be NULL
How are first_name/middle_name/priority/id attached to the record type?
Via inheritance. The table struct contains a mpl::set of types describing the columns, e.g. // --------------------------------------- struct Id: public sql::type<my_table, Id, sql::serial, sql:primary_key> { Id(): id_(Id::get_default_value()) {} Id(const typename Id::value_type& value): id_(value) {} static const char* get_name() { return "id"; } typename Id::value_type id_; }; // -------------------------------------- The record inherits from the columns of my_table and thus has id_ as a member. Instead of getting ALL columns from my_table, you could also typedef a record like this: typedef sql::record<my_table, my_table::first_name, my_table::last_name> record; In this case, trying to access record.id_ would lead to a compile error, since it is simply not available. Regards, Roland
On 09/14/2010 05:06 PM, Joel Falcou wrote:
On 14/09/10 16:54, Roland Bock wrote:
Have you considered using Proto to define and check the grammar of that language?
Yes, but I would need support for that. I admit that (as of today) Proto is too hard to understand for me.
I can help on this front I guess ;)
Cool! I'll need a few days to re-organize the code to incorporate some current experiments and split the monstrous header file into several smaller chunks. I'd be very interested in seeing how Proto could be brought into this! I'll let you know :-)
On 09/14/2010 08:13 PM, Mathias Gaunard wrote:
On 14/09/2010 15:54, Roland Bock wrote:
Yes, but I would need support for that. I admit that (as of today) Proto is too hard to understand for me.
Watch the Boostcon 2010 video; once you have it should be quite simpler.
Thanks for the hint. That gives me a lot to think about :-)
On 9/14/2010 4:51 PM, Roland Bock wrote:
On 09/14/2010 08:13 PM, Mathias Gaunard wrote:
On 14/09/2010 15:54, Roland Bock wrote:
Yes, but I would need support for that. I admit that (as of today) Proto is too hard to understand for me.
Watch the Boostcon 2010 video; once you have it should be quite simpler.
Thanks for the hint. That gives me a lot to think about :-)
Also, see the series of articles about Proto I'm currently publishing on cpp-next.com. Here's the latest: http://cpp-next.com/archive/2010/09/expressive-c-playing-with-syntax/ More coming soon. -- Eric Niebler BoostPro Computing http://www.boostpro.com
On 09/15/2010 06:09 AM, Eric Niebler wrote:
On 9/14/2010 4:51 PM, Roland Bock wrote:
On 09/14/2010 08:13 PM, Mathias Gaunard wrote:
On 14/09/2010 15:54, Roland Bock wrote:
Yes, but I would need support for that. I admit that (as of today) Proto is too hard to understand for me.
Watch the Boostcon 2010 video; once you have it should be quite simpler.
Thanks for the hint. That gives me a lot to think about :-)
Also, see the series of articles about Proto I'm currently publishing on cpp-next.com. Here's the latest:
http://cpp-next.com/archive/2010/09/expressive-c-playing-with-syntax/
Very nicely written! I'll definitely try to replace the current implementation of the WHERE expression, especially since Joel has offered to help with it :-) I probably also should use proto to build sequences of query building blocks like WHERE, GROUP_BY, ORDER_BY, LIMIT. I guess I could employ boost::mpl::less to make sure that a WHERE can be followed by a GROUP_BY expression, but not vice versa. Sigh, so much to do, and so little time...
More coming soon.
Looking forward to it! Thanks and regards, Roland
Backin Boost'Con 2009, we were discussing a RDB library for boost and I made some mock up sldies of how the interface could look like. I'll try to find it back.
A while back, I did some experiments with prepared statements and boost::function (see http://github.com/purpleKarrot/async-db). Have a look at the mysql example. As I recall, it did work. http://github.com/purpleKarrot/async-db/blob/master/examples/mysql.cpp I currently lack the time to work further on this, but if you concentrate on string generation (which I did not), there might be some interesting combinations. Something like this would be cool: . function<void(int, string, gregorian::date)> insert . = sql::bind(INSERT > INTO > users(id, name, registered), . VALUES(int_, string_, date_)); . insert(1003, "Bob", gregorian::day_clock::local_day()); cheers, Daniel
On 09/15/2010 12:31 PM, Daniel Pfeifer wrote:
A while back, I did some experiments with prepared statements and boost::function (see http://github.com/purpleKarrot/async-db).
Have a look at the mysql example. As I recall, it did work. http://github.com/purpleKarrot/async-db/blob/master/examples/mysql.cpp
I currently lack the time to work further on this, but if you concentrate on string generation (which I did not), there might be some interesting combinations.
Something like this would be cool:
. function<void(int, string, gregorian::date)> insert . = sql::bind(INSERT> INTO> users(id, name, registered), . VALUES(int_, string_, date_));
. insert(1003, "Bob", gregorian::day_clock::local_day());
cheers, Daniel
Nice! I am aiming at a more verbatim syntax, like typedef my_table<> t; sql::insert<t>(t::id(1003), t::name("Bob"), t::date(gregorian::day_clock::local_day()); or (if for instance date could be NULL or had a default value), this should also work: sql::insert<t>(t::name("Bob"), t::id(1003)); Thus, the order of arguments is irrelevant, their meaning is self-explanatory (similar to Boost.Parameter), and some arguments are not required if the table definition says that they are not required. Not sure how far I get, but it is certainly possible :-) How did you intend to maintain your table definitions (keep them in sync with the actual tables of the database)? Regards, Roland
On 9/15/2010 1:38 AM, Roland Bock wrote:
On 09/15/2010 06:09 AM, Eric Niebler wrote:
Also, see the series of articles about Proto I'm currently publishing on cpp-next.com. Here's the latest:
http://cpp-next.com/archive/2010/09/expressive-c-playing-with-syntax/
Very nicely written! I'll definitely try to replace the current implementation of the WHERE expression, especially since Joel has offered to help with it :-)
I probably also should use proto to build sequences of query building blocks like WHERE, GROUP_BY, ORDER_BY, LIMIT. I guess I could employ boost::mpl::less to make sure that a WHERE can be followed by a GROUP_BY expression, but not vice versa.
No, you'd write the grammar of your DSEL using Proto. See the docs for Proto grammars. The article series hasn't gotten around to talking about that yet. -- Eric Niebler BoostPro Computing http://www.boostpro.com
On 09/15/2010 07:38 PM, Eric Niebler wrote:
On 9/15/2010 1:38 AM, Roland Bock wrote:
On 09/15/2010 06:09 AM, Eric Niebler wrote:
Also, see the series of articles about Proto I'm currently publishing on cpp-next.com. Here's the latest:
http://cpp-next.com/archive/2010/09/expressive-c-playing-with-syntax/
Very nicely written! I'll definitely try to replace the current implementation of the WHERE expression, especially since Joel has offered to help with it :-)
I probably also should use proto to build sequences of query building blocks like WHERE, GROUP_BY, ORDER_BY, LIMIT. I guess I could employ boost::mpl::less to make sure that a WHERE can be followed by a GROUP_BY expression, but not vice versa.
No, you'd write the grammar of your DSEL using Proto. See the docs for Proto grammars. The article series hasn't gotten around to talking about that yet.
Ah, it is beginning to dawn on me :-) , see attached code. I admit, it took some time to sort it out, but wow, even after just peeking inside, I say: Proto is pretty cool! Would you have defined the Grammar in a similar way? My solution seems a bit counter-intuitive to me because it describes the allowed c++-code from right to left... Regards, Roland
From: boost-bounces@lists.boost.org [mailto:boost-bounces@lists.boost.org] On Behalf Of Eric Niebler Sent: Wednesday, September 15, 2010 12:10 PM To: Boost mailing list Subject: Re: [boost] Boost.SQL?
On 9/14/2010 4:51 PM, Roland Bock wrote:
On 09/14/2010 08:13 PM, Mathias Gaunard wrote:
On 14/09/2010 15:54, Roland Bock wrote:
Yes, but I would need support for that. I admit that (as of today) Proto is too hard to understand for me.
Watch the Boostcon 2010 video; once you have it should be quite simpler.
Thanks for the hint. That gives me a lot to think about :-)
Also, see the series of articles about Proto I'm currently publishing on cpp-next.com. Here's the latest:
http://cpp-next.com/archive/2010/09/expressive-c-playing-with-syntax/
More coming soon.
Nice articles. Thanks for the pointer. And anyone can tell me where I can download the Boostcon 2010 video? Thanks.
-- Eric Niebler BoostPro Computing http://www.boostpro.com
Best regards Wang Weiwei
On Sep 16, 2010, at 6:55 AM, Max wrote:
From: boost-bounces@lists.boost.org [mailto:boost-bounces@lists.boost.org] On Behalf Of Eric Niebler Sent: Wednesday, September 15, 2010 12:10 PM To: Boost mailing list Subject: Re: [boost] Boost.SQL?
On 9/14/2010 4:51 PM, Roland Bock wrote:
On 09/14/2010 08:13 PM, Mathias Gaunard wrote:
On 14/09/2010 15:54, Roland Bock wrote:
Yes, but I would need support for that. I admit that (as of today) Proto is too hard to understand for me.
Watch the Boostcon 2010 video; once you have it should be quite simpler.
Thanks for the hint. That gives me a lot to think about :-)
Also, see the series of articles about Proto I'm currently publishing on cpp-next.com. Here's the latest:
http://cpp-next.com/archive/2010/09/expressive-c-playing-with-syntax/
More coming soon.
Nice articles. Thanks for the pointer. And anyone can tell me where I can download the Boostcon 2010 video? Thanks.
The BoostCon videos (those that I have put up so far) are on blip.tv. http://blip.tv/search?q=boostcon+2010 -- Marshall P.S. There will be more as time goes on.
On Fri, Sep 17, 2010 at 11:10 AM, Marshall Clow <mclow.lists@gmail.com> wrote:
P.S. There will be more as time goes on.
Hi Marshall, Is there anything I can do to help move that forward? -- Dave Abrahams BoostPro Computing http://www.boostpro.com
I am interested in a SQL library. Making the sql statements should be pretty straight forward. How do you plan on dealing with communicating with the SQL server? I have used two methods before. First is how a web server does it by sending SQL commands to a IP address / hostname for the database server. Second is using the database server's API and linking against a library. Obviously the first is more portable. Stephen Stephen Torri, PhD NAVAL SURFACE WARFARE CENTER DAHLGREN 17214 Ave B Suite 121 Dahlgren, Va. 22448 540-653-1082 I am not a contracting officer. I cannot modify or initiate contracts, nor do I have the authority to financially commit the government in any way.
On 09/14/2010 04:36 PM, Torri, Stephen CIV NSWCDD, W15 wrote:
I am interested in a SQL library. Making the sql statements should be pretty straight forward. How do you plan on Making SQL statements is pretty straight forward, if you do something like
string query = string("select * from my_table where id>1000 and first_name='") + name + "'"; But this construction is prone to errors, and the interpretation of the results as well. How can you determine, if the first item in a result_set is in fact the first_name that you expected? Therefore, the building of SQL statements is what I am concentrating on. Also as was pointed out by Dominique (and in earlier discussions on this list), various vendors support various dialects of SQL. This could turn the generation of queries into a major headache ;-)
dealing with communicating with the SQL server? I have used two methods before. First is how a web server does it by sending SQL commands to a IP address / hostname for the database server. Second is using the database server's API and linking against a library. Obviously the first is more portable.
I am not sure about this yet. As of now, the project is not much more than a prototype (although we have started to migrate code already). Currently I am using libpqxx (a C++ wrapper for libpq) which connects via TCP. It might be a good idea to create a second library which provides a unified communication interface for a variety of vendors and and use that as basis for the stuff I am currently working on. Regards, Roland
On Tue, Sep 14, 2010 at 10:17 AM, Roland Bock <rbock@eudoxos.de> wrote:
dealing with communicating with the SQL server? I have used two methods before. First is how a web server does it by sending SQL commands to a IP address / hostname for the database server. Second is using the database server's API and linking against a library. Obviously the first is more portable.
I am not sure about this yet. As of now, the project is not much more than a prototype (although we have started to migrate code already). Currently I am using libpqxx (a C++ wrapper for libpq) which connects via TCP.
I don't think you'll be able to do that with Oracle. Oracle's wire protocol is proprietary, and it can use different kinds of network transport. You *have* to use Oracle OCI on the client side, AFAIK. (Jonah Harris has kind of reversed engineered that protocol I think, but I don't think you want to get into that).
It might be a good idea to create a second library which provides a unified communication interface for a variety of vendors and and use that as basis for the stuff I am currently working on.
Good luck with that ;) --DD
On 09/14/2010 05:26 PM, Dominique Devienne wrote:
On Tue, Sep 14, 2010 at 10:17 AM, Roland Bock<rbock@eudoxos.de> wrote:
dealing with communicating with the SQL server? I have used two methods before. First is how a web server does it by sending SQL commands to a IP address / hostname for the database server. Second is using the database server's API and linking against a library. Obviously the first is more portable.
I am not sure about this yet. As of now, the project is not much more than a prototype (although we have started to migrate code already). Currently I am using libpqxx (a C++ wrapper for libpq) which connects via TCP.
I don't think you'll be able to do that with Oracle. Oracle's wire protocol is proprietary, and it can use different kinds of network transport. You *have* to use Oracle OCI on the client side, AFAIK. (Jonah Harris has kind of reversed engineered that protocol I think, but I don't think you want to get into that).
It might be a good idea to create a second library which provides a unified communication interface for a variety of vendors and and use that as basis for the stuff I am currently working on.
Good luck with that ;) --DD
Let me rephrase an earlier reply to you: I know that I won't be able to do it on my own :-) The different ways in which database vendors are interpreting SQL and are offering APIs is rather scary...
On 14/09/2010 14:26, Roland Bock wrote:
Hi,
currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
Hi Roland, I believe something similar may already be in progress - see http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb-crystalclear... Cheers,* *Jon Payne
On 09/15/2010 11:33 AM, Jon Payne wrote:
On 14/09/2010 14:26, Roland Bock wrote:
Hi,
currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
Hi Roland,
I believe something similar may already be in progress - see http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb-crystalclear...
Cheers,* *Jon Payne
Just saw that. Do you know the status of that project? There doesn't seem to be much activity on the list, at least. Regards, Roland
currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
I believe something similar may already be in progress - see http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb- crystalclearsoftware.com
There is also this, which seems similar: http://code.google.com/p/boost-rdb/ It was discussed on this mailing list in 2009 and early 2010.
On 09/15/2010 03:25 PM, Eric MALENFANT wrote:
currently I am working on a set of header files which are supposed
to
make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
I believe something similar may already be in progress - see http://mail-lists.crystalclearsoftware.com/listinfo.cgi/std_rdb- crystalclearsoftware.com
There is also this, which seems similar: http://code.google.com/p/boost-rdb/ It was discussed on this mailing list in 2009 and early 2010.
Thanks, from a quick look at the documentation, I'd say that it's quite close to what I am trying to achieve, when it comes to constructing queries, but not really when results are to be handled. I put that on my ToDo list for closer examination. Regards, Roland
On 09/14/2010 03:26 PM, Roland Bock wrote:
Hi,
currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
* Stay close to SQL syntax * Do as much type checking as possible * prevent oversights and typical security holes, e.g. by forgetting to escape strings
The basic idea is, that given a table definition, it should be possible to
* offer a lot of checks at compile time for building a query * make it hard to misinterpret the results
Hi, for those who wait for news here: Due to a change in priorities I am forced to stop working on this topic for now. Thanks for the support. I hope to be able to pick up the thread again, later. Regards, Roland
participants (17)
-
Daniel Pfeifer -
Dave Abrahams -
David Abrahams
-
Dominique Devienne -
Eric MALENFANT -
Eric Niebler -
Giovanni Piero Deretta -
Jeremy Maitin-Shepard -
Joel Falcou -
Jon Payne -
Maciej Sobczak -
Marshall Clow -
Mateusz Loskot -
Mathias Gaunard -
Max -
Roland Bock -
Torri, Stephen CIV NSWCDD, W15