Re: [boost] Report from Berlin C++ Standards Committee meeting
Steve Hutton wrote:
The idea is that to select all rows, you fetch within a loop. The vector gets resized to contain the number of rows returned.
const int BATCH_SIZE = 30; std::vector<int> valsOut(BATCH_SIZE); Statement st = (sql.prepare << "select value from numbers", into(valsOut)); st.execute(); while (st.fetch()) { std::vector<int>::iterator pos; for(; pos != valsOut.end(); ++pos) { cout << *pos << '\n'; } }
How abouit something like: sql::statement query; query << "select value from numbers"; sql::rowset< int > rows(query); cout << "found = " << rows.size() << endl; std::vector< int > values; std::copy( rows.begin(), rows.end(), values ); Multi-item row searches could use tuple: sql::rowset< int, std::wstring, int > rows( query ); std::tr1::tuple< int, std::wstring, int > value = rows.begin(); - Reece _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
On Sat, Apr 15, 2006 at 10:19:30AM +0100, Reece Dunn <msclrhd@hotmail.com> wrote:
Steve Hutton wrote:
The idea is that to select all rows, you fetch within a loop. The vector gets resized to contain the number of rows returned.
const int BATCH_SIZE = 30; std::vector<int> valsOut(BATCH_SIZE); Statement st = (sql.prepare << "select value from numbers", into(valsOut)); st.execute(); while (st.fetch()) { std::vector<int>::iterator pos; for(; pos != valsOut.end(); ++pos) { cout << *pos << '\n'; } }
How abouit something like:
sql::statement query; query << "select value from numbers";
sql::rowset< int > rows(query); cout << "found = " << rows.size() << endl;
std::vector< int > values; std::copy( rows.begin(), rows.end(), values );
Multi-item row searches could use tuple:
sql::rowset< int, std::wstring, int > rows( query ); std::tr1::tuple< int, std::wstring, int > value = rows.begin();
Slightly offtopic, but what about something like: using std::sql::select; using std::sql::from; auto query = select >> value >> from >> numbers; typedef decltype(query)::tuple_type tuple_type; sql::rowset<tuple_type> rows(query,db); ... or maybe a more generic reational syntax... Regards Andreas Pokorny
On 4/15/06, Andreas Pokorny <andreas.pokorny@gmx.de> wrote: [snipped]
Slightly offtopic, but what about something like:
using std::sql::select; using std::sql::from;
auto query = select >> value >> from >> numbers; typedef decltype(query)::tuple_type tuple_type;
sql::rowset<tuple_type> rows(query,db); ...
or maybe a more generic reational syntax...
I was thinking exactly about something like this. A xpressive/spirit-like way of writing SQL. It should be faster too, I guess.
Regards Andreas Pokorny
-- Felipe Magno de Almeida
On Sat, Apr 15, 2006 at 08:16:01PM -0300, Felipe Magno de Almeida <felipe.m.almeida@gmail.com> wrote:
On 4/15/06, Andreas Pokorny <andreas.pokorny@gmx.de> wrote:
[snipped]
Slightly offtopic, but what about something like:
using std::sql::select; using std::sql::from;
auto query = select >> value >> from >> numbers;
^ numbers.value of course
typedef decltype(query)::tuple_type tuple_type;
sql::rowset<tuple_type> rows(query,db); ...
or maybe a more generic reational syntax...
I was thinking exactly about something like this. A xpressive/spirit-like way of writing SQL. It should be faster too, I guess.
I am not sure if it faster, but errors could be detected earlier. If the dsl query language is defined generic enough, the query expression could be transformed into database specific query strings, or STL/Multindex query functors, boost::rtl queries or whatever (during compiletime). One would need two separate query models for relational and tree like data, to fullfill most needs. Andeas Pokorny
On 2006-04-16, Andreas Pokorny <andreas.pokorny@gmx.de> wrote:
On Sat, Apr 15, 2006 at 08:16:01PM -0300, Felipe Magno de Almeida <felipe.m.almeida@gmail.com> wrote:
On 4/15/06, Andreas Pokorny <andreas.pokorny@gmx.de> wrote:
[snipped]
Slightly offtopic, but what about something like:
using std::sql::select; using std::sql::from;
auto query = select >> value >> from >> numbers;
^ numbers.value of course
typedef decltype(query)::tuple_type tuple_type;
sql::rowset<tuple_type> rows(query,db); ...
or maybe a more generic reational syntax...
I was thinking exactly about something like this. A xpressive/spirit-like way of writing SQL. It should be faster too, I guess.
I am not sure if it faster, but errors could be detected earlier. If the dsl query language is defined generic enough, the query expression could be transformed into database specific query strings, or STL/Multindex query functors, boost::rtl queries or whatever (during compiletime). One would need two separate query models for relational and tree like data, to fullfill most needs.
Others have gone down this path, e.g. Rogue Wave db tools. It certainly has allure as an interesting approach from the perspective of a db library developer. However, in practice as a db application developer, I have found some significant advantages of the pure SQL string approach: - SQL queries can be easily tested in a sql query tool during development. (Using Rogue Wave for non trivial queries, I found myself writing them in SQL first, testing them, then mentally translating them into C++) - SQL queries are more readible (again, think queries which join 4 or 5 tables, multiple conditionals, outer joins, like clauses, etc.) - SQL queries for an application can be stored in a text file, which can be easily read by a non-C++ programmer, e.g. a database administrator. - SQL is an ANSI standard, and it is universally known by every db developer and database administrator. It is taught in schools and entire books are written about it. The SOCI library is firmly based on the SQL approach. That doesn't mean some other library with it's own query language couldn't co-exist, or perhaps be layered on top. But I think having a pure SQL based library in boost would be extremely valuable. Steve
Steve Hutton wrote:
On 2006-04-16, Andreas Pokorny <andreas.pokorny@gmx.de> wrote:
I am not sure if it faster, but errors could be detected earlier. If the dsl query language is defined generic enough, the query expression could be transformed into database specific query strings, or STL/Multindex query functors, boost::rtl queries or whatever (during compiletime). One would need two separate query models for relational and tree like data, to fullfill most needs.
Others have gone down this path, e.g. Rogue Wave db tools. It certainly has allure as an interesting approach from the perspective of a db library developer.
However, in practice as a db application developer, I have found some significant advantages of the pure SQL string approach:
And another advantage. If those queries are structure in the same vein as Boost.Format strings they can be changed without modifying the handling code. A pattern I've used for DB development is to write the program with SQL query clarity in mind first, then go back and performance tune those queries. The optimization step would be considerably more painful if the program needed to be recompiled each time. -- -- Grafik - Don't Assume Anything -- Redshift Software, Inc. - http://redshift-software.com -- rrivera/acm.org - grafik/redshift-software.com -- 102708583/icq - grafikrobot/aim - grafikrobot/yahoo
On Sun, 16 Apr 2006 14:26:45 -0500, Rene Rivera wrote
Steve Hutton wrote:
On 2006-04-16, Andreas Pokorny <andreas.pokorny@gmx.de> wrote:
I am not sure if it faster, but errors could be detected earlier. If the dsl query language is defined generic enough, the query expression could be transformed into database specific query strings, or STL/Multindex query functors, boost::rtl queries or whatever (during compiletime). One would need two separate query models for relational and tree like data, to fullfill most needs.
However, in practice as a db application developer, I have found some significant advantages of the pure SQL string approach:
And another advantage. If those queries are structure in the same vein as Boost.Format strings they can be changed without modifying the handling code. A pattern I've used for DB development is to write the program with SQL query clarity in mind first, then go back and performance tune those queries. The optimization step would be considerably more painful if the program needed to be recompiled each time.
There's no doubt to me that the string approach based on my experiences developing object to database bindings for many of the reasons Steve and Rene mention -- so I think that approach is should be favored. The main thing for more is the simplicity of the interface and as both mentioned the use of other tools to test the SQL queries. BTW, I think there's a more general library possible here which simply binds program variable values to string 'templates' or stand-ins -- ala the perl template toolkit and a gob of others in other languages. With the more general case you can expand binding beyond sql to say html, etc... Jeff
"Steve Hutton" <shutton@featurecomplete.com> wrote in message news:e1u597$iop$1@sea.gmane.org...
- SQL is an ANSI standard, and it is universally known by every db developer and database administrator. It is taught in schools and entire books are written about it.
SQL is also an ISO standard. That is very imporant from the standpoint of the C++ committee. C++ is an ISO standard, so it can easily reference other ISO standards. For SQL, that means that the C++ Standard doesn't have to specify how SQL works - instead it just says "see ISO standard such-and-such". That keeps the LWG, the project editor, and utilimately the gnomes of Geneva all happy. --Beman
On Sun, 16 Apr 2006 22:33:54 -0400, Beman Dawes wrote
"Steve Hutton" <shutton@featurecomplete.com> wrote in message news:e1u597$iop$1@sea.gmane.org...
- SQL is an ANSI standard, and it is universally known by every db developer and database administrator. It is taught in schools and entire books are written about it.
SQL is also an ISO standard. That is very imporant from the standpoint of the C++ committee. C++ is an ISO standard, so it can easily reference other ISO standards. For SQL, that means that the C++ Standard doesn't have to specify how SQL works - instead it just says "see ISO standard such-and-such". That keeps the LWG, the project editor, and utilimately the gnomes of Geneva all happy.
The problem (of course there has to be a problem), is that all the databases have various extensions to the SQL standard. And as far as I understand pretty much all stored procedures are non-portable -- which is enough for me to aviod them. In any case, I don't think we want the library to preclude allowing the use of non-standard extensions in the case that the user is comfortable with the non-portability between databases. So I'm not sure if all this helps or hurts... Jeff
Slightly offtopic, but what about something like:
using std::sql::select; using std::sql::from;
auto query = select >> value >> from >> numbers; typedef decltype(query)::tuple_type tuple_type;
sql::rowset<tuple_type> rows(query,db); ...
or maybe a more generic reational syntax...
I was thinking exactly about something like this. A xpressive/spirit-like way of writing SQL. It should be faster too, I guess.
This has the additional advantage of generating code less prone to SQL injection attacks, since there is no need to concatenate strings. The library itself could check that parameters are used as such and not as a mean to inject SQL commands. Best regards Jorge
On 2006-04-15, Reece Dunn <msclrhd@hotmail.com> wrote:
Steve Hutton wrote:
const int BATCH_SIZE = 30; std::vector<int> valsOut(BATCH_SIZE); Statement st = (sql.prepare << "select value from numbers", into(valsOut)); st.execute(); while (st.fetch()) { std::vector<int>::iterator pos; for(; pos != valsOut.end(); ++pos) { cout << *pos << '\n'; } }
How abouit something like:
sql::statement query; query << "select value from numbers";
sql::rowset< int > rows(query); cout << "found = " << rows.size() << endl;
std::vector< int > values; std::copy( rows.begin(), rows.end(), values );
Multi-item row searches could use tuple:
sql::rowset< int, std::wstring, int > rows( query ); std::tr1::tuple< int, std::wstring, int > value = rows.begin();
The tuple idea is interesting. SOCI supports selecting into a generic Row object, which can iterated over, or interrogated by column name: Session sql("oracle", "service=db1", "user=scott", "password=tiger"); Row r; sql << "select * from Parts", into(r); cout << "first column: " << r.get<string>(0); cout << "column named 'PART_NUMBER': " << r.get<int>("PART_NUMBER"); (Note that you can also interrogate the row to get the names and types of the columns, e.g. to write generic app that dumps a table to xml) SOCI extends this further to provide object relational mapping: struct Part { int num; string description; }; // define O/R mapping via specialization of SOCI::TypeConversion template<> struct TypeConversion<Part> { typedef Values base_type; static Part from(Values const &v) { Parts p; p.num = v.get<int>("PART_NUMBER"); p.description = v.get<string>("DESCRIPTION"); } static Values to(Part &p) { Values v; v.set("PART_NUMBER", p.num); v.set("DESCRIPTION", pdescription); } }; Part p; sql << "select * from Parts", into(p); cout << "got part # " << p.num << "(" << p.description << ")"; So, back to your tuple suggestion - I could see that perhaps being supported in this fashion: tuple t<int, string>; sql << "select * from Parts", into(t); Your suggestion of the use of a RowSet class is interesting. SOCI supports selecting to/from vector<int>, vector<string>, etc. The context in which we added support for these features was to support high performance bulk operations...the fetch() mechanism was already in place as the default way to iterate over a rowset. But we haven't yet added support for vector<Row> or vector<MyType>. So in these cases there's just the one way to interate over rows in a result set - the fetch() function. I was planning on adding support for vector<Row> and vector<MyType>, as a performance optimization for bulk operations. But now I can see that having these two means of iterating over result sets might be a little confusing to users. Steve
On Sun, 16 Apr 2006 19:58:03 +0000 (UTC), Steve Hutton wrote
On 2006-04-15, Reece Dunn <msclrhd@hotmail.com> wrote:
First a question for Reece -- didn't you put together the Boost.Database stuff in the sandbox? It's been awhile since I looked, but is there anything there that could be used to forward a combined proposal?
The tuple idea is interesting. SOCI supports selecting into a generic Row object, which can iterated over, or interrogated by column name:
...snip details
Your suggestion of the use of a RowSet class is interesting. SOCI supports selecting to/from vector<int>, vector<string>, etc. The context in which we added support for these features was to support high performance bulk operations...the fetch() mechanism was already in place as the default way to iterate over a rowset.
But we haven't yet added support for vector<Row> or vector<MyType>. So in these cases there's just the one way to interate over rows in a result set - the fetch() function.
I was planning on adding support for vector<Row> and vector<MyType>, as a performance optimization for bulk operations.
But now I can see that having these two means of iterating over result sets might be a little confusing to users.
Not to make this harder, but I wonder if we need to step back a level. Perhaps an iterator interface that allows users bind the data into an stl collection of their choice? Perhaps allows control over the number of results to pull at a time from the database? In some cases I might want the results in a set or list instead of the other options. As I recall DTL can do this. I'm not precluding the need to have a rowset or some other custom type, but the question of binding to standard collection will surely come up as an issue. Jeff
On Behalf Of Jeff Garland
First a question for Reece -- didn't you put together the Boost.Database stuff in the sandbox?
I put Boost.Database in the sandbox.
It's been awhile since I looked, but is there anything there that could be used to forward a combined proposal?
I just started reading the docs for SOCI, but I don't think my library does much that SOCI doesn't. My library has a very simple iterator based interface with no facilities for exchanging data other than simple gets. Once that had been accepted I had planned on releasing a full fledged serialization library on top of it, but then I got busy with other things... The serialization part was much more exciting than the basic interface. One issue I remember confronting was string representation. How would SOCI handle that? I ended up having to make everything a template. Anyway, if you need a hand with anything in SOCI I'd love to help out. Brock
On 2006-04-17, Brock Peabody <brock.peabody@npcinternational.com> wrote:
I just started reading the docs for SOCI, but I don't think my library does much that SOCI doesn't. My library has a very simple iterator based interface with no facilities for exchanging data other than simple gets. Once that had been accepted I had planned on releasing a full fledged serialization library on top of it, but then I got busy with other things... The serialization part was much more exciting than the basic interface.
One issue I remember confronting was string representation. How would SOCI handle that? I ended up having to make everything a template.
We haven't addresed wide strings/unicode yet. It's one of the things on the TODO list, so insights here would be helpful.
Anyway, if you need a hand with anything in SOCI I'd love to help out.
Thanks! Maciej will be back from holiday on the 25th...I'd like to defer major discussions until then, so he can be involved. We do have project mailing lists on sourceforge: http://lists.sourceforge.net/lists/listinfo/soci-users http://lists.sourceforge.net/lists/listinfo/soci-devel Steve
On Behalf Of Jeff Garland
It's been awhile since I looked, but is there anything there that could be used to forward a combined proposal?
Sorry to reply to this message twice, but I finished looking through the SOCI documentation. It looks really good. I thought I'd show a sample of my serialization DSEL just for contrast: struct job { int type, double rate }; struct employee { int id, string name vector<job> jobs; }; serializer<employee> emp_serializer = table("employee")[ //'*' means key field *field("id") = &employee::id, field("name") = &employee::name, table("job") [ //'-' means dependent field -field("employee"), field("type") = &job::type, field("rate") = &job::rate ] = &employee::jobs ]; vector<employee> emps = s.load<vector<employee> >(database, "SELECT * FROM employee"); .... s.save(database, emps); This small example shows how to serialize two simple structures where one is contained by the other. I don't know if that looks attractive to anyone but I thought it might spark some discussion at least.
participants (9)
-
Andreas Pokorny -
Beman Dawes -
Brock Peabody -
Felipe Magno de Almeida -
Jeff Garland -
Jorge Lodos -
Reece Dunn -
Rene Rivera -
Steve Hutton