Re: [boost] SQL client library ?

Subject: Re: [boost] SQL client library ?
From: Christoph Heindl (christoph.heindl_at_[hidden])
Date: 2009-09-06 05:36:37

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_at_[hidden]> 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 : [phone]
>
>
> _______________________________________________
> Unsubscribe & other changes:
> http://lists.boost.org/mailman/listinfo.cgi/boost
>