Re: [boost] SQL client library ?

Subject: Re: [boost] SQL client library ?
From: Tomas (pecholt_at_[hidden])
Date: 2009-09-09 17:00:32

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