[boost] Library Proposal: database (boost::db)

From: Tito Villalobos (tvillalobos_at_[hidden])
Date: 2004-10-12 20:54:21

I have been working on a library for database programming.  It currently 
wraps only ODBC API, though the same interface will be able to be 
created over the ADO API as well.  It appears that the MySQL and 
PostgreSQL would be fairly straight forward to wrap as well, though I do 
not know those API's (yet).

The core goal is to reduce the amount of overhead and boilerplate code 
that database programming currently requires.  I wanted the type safety 
and speed of statically typed recordsets without resorting to "each 
recordset is a whole new class" that is so common typesafe ODBC and 
IADORecordBinding (ADO) based recordsets.  On the other hand, latebound 
"variant" based recordsets (ADO) are somewhat painful to use in a 
strongly typed language like C++, and inevitably result in quite a bit 
of code.  Common relational database concepts apply across all of the 
various database API's, and it should be possible to encapsulate them 
all in a way that allows flexibility in what is used underneath, with 
few in client code.

The core of the interesting piece is the recordset/statement, as 
connection and such are pretty standard.  The implementation uses mpl 
and tuples to avoid the need to create a ton of new classes and 
complicated bindings.  It also uses traits to directly map C++ types to 
the correct ODBC bindings without any extra work for the client.

The syntax for declaring the a statement is:
    odbc_statement<
            mpl_typelist, (query/stored procedure parameter types)
            mpl_typelist, (recordset column types)
            mpl_typelist  (options typelist (optional template 
parameter) ) >
        
Some sample code:
            odbc_statement<
                mpl::list0<>,   // same semantics with mpl::void_ or 
tuples::null_type
                mpl::list< int, char[50] > > my_stmt ;
            std::string sql_stmt_text =
                "SELECT employeeID, lastName from Employees "
                "ORDER BY employeeID " ;
            my_stmt.open( my_connection, sql_stmt_text ) ;
            while ( my_stmt.move_next() ) {
               // do stuff
            }

            odbc_statement<
                mpl::list< int, char[50] >,
                mpl::void_ > my_stmt ;
            std::string sql_stmt_text =
                "INSERT INTO Employees (employeeID,lastName) VALUES( ?, ? )"
            my_stmt.parameter<0>() = 50 ;
            my_stmt.parameter<1>() = "Jones" ;  
            my_stmt.open( my_connection, sql_stmt_text ) ;

            odbc_statement<
                mpl::list< int, int >,
                mpl::list< int, char[50] >,
                mpl::list< forward_only, read_only, bulk_row<10> > > 
my_stmt ;
            std::string sql_stmt_text =
                "SELECT employeeID, lastName from Employees "
                "WHERE employeeID BETWEEN ? AND ? "
                "ORDER BY employeeID " ;
            my_stmt.parameters() = tuples::make_tuple( 1, 10 ) ;
            my_stmt.open( my_connection, sql_stmt_text ) ;

            odbc_statement<
                mpl::list< int, out_param<char[50]> >,
                mpl::void_ > my_stmt ;
            std::string sql_stmt_text =
                "{ CALL GetLastNameByID( ?, ? ) }"
            my_stmt.parameter<0>() = 50 ;
            my_stmt.open( my_connection, sql_stmt_text ) ;
            std::string last_name = my_stmt.parameter<1>() ;

Would there be any interest in including this in boost?

Thanks,

Tito Villalobos