Re: [boost] Boost.SQL (was Re: [relational] Relational Model Library)

2 Oct 2005

      Reece Dunn wrote:
...
As I mentioned above, having a Boost.Spirit-style SQL syntax would 
complement the direction that C++/Boost is going with respect to 
describing external constructs (RegExes, BNFL grammars) within C++.
Thinking about this some more, I now have:

[NOTE: These are some ideas I am throwing around and have no idea yet as 
to their implementability or useability.]

CREATE TABLE:

    typedef tuple
            <
               std::string,
               std::string,
               int
            >
            people_row;

    static const int first_name  = 0;
    static const int second_name = 1;
    static const int age         = 2;

But see the discussion about people::first_name = 2 and 
customer::first_name = 3.

    typedef sql::table
            <
               std::string,
               std::string,
               int
            >
            people_table;

    people_table database;

people_table::row_type would evaluate to
    tuple< std::string, std::string, int >
and people_table would hold a std::vector< row_type >-like
object.

SELECT:

    sql::statement query =
       sql::select
       (
          sql::item< first_name > &&
          sql::item< second_name >
       ).where
       [
          sql::item< age >() >= 30
       ];

sql::select( ... ) would create a tuple -> tuple mapping
that defines the result type. This would define an
sql::table<> type.

.where[ ... ] would define the binary predicate functor
that returns true if the given tuple matches the criteria.
Thus, you could do something like:

    if( query( database[ 0 ])) ...;

ROWSET:

    sql::rowset results = database.query( query );
    BOOST_FOREACH( row, results )
    {
       std::cout << get<first_name>( row ) << std::endl;
    }

JOIN:

A join command could be something like:

    sql::statement query =
       sql::select
       (
          sql::item< first_name, 0 > &&
          sql::item< postcode, 1 >
       )
       .where
       [
          sql::item< age, 0 > == 21
       ];

sql::id< id_value > defines an identifier for multi-table
selects.

sql::item< column_index, table_id = 0 > identifies a
column to apply.

Then you would do:

    results = query.join
       (
          id<0>( people ) * id<1>( addresses )
       );

- Reece