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