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