Database connection abstraction layers.

This is a recurring issue: How to design an abstraction layer on top of an underlying database connection. And how to design the abstraction layer to be general enough to fit with many, if not all, types of databases. We have Sqlite, PostgreSQL, MySQL, Oracle, ... and they all have their own engines.

In this post will I give a design example on an abstraction layer that could be useful for this situation.

The Google Gears API have a nice and simple way of solving this issue for Sqlite. The following is an excerpt from their documentation:

Database class
   void      open([name])
   ResultSet execute(sqlStatement, [argArray])
   void      close()
   void      remove()
   readonly attribute int lastInsertRowId
   readonly attribute int rowsAffected

ResultSet class
   boolean isValidRow()
   void    next()
   void    close()
   int     fieldCount()
   string  fieldName(int fieldIndex)
   variant field(int fieldIndex)
   variant fieldByName(string fieldName)

In C++, since we are only reading data when iterating forward through the result dataset, the ResultsSet object could be nicely implemented as an InputIterator which would be dereferenced to a row in the ResultSet.

So, have a look at the following C++ class ResultSet that is an InputIterator.

#ifndef DBAPI_RESULTSET_H
#define DBAPI_RESULTSET_H

class ResultRow;

class ResultSet {
  public:
    class iterator {
      public:
        iterator();
        virtual ~iterator();
        iterator(const iterator & );
        iterator & operator=(const iterator & source);
        void& operator++();
        ResultRow& operator*();
        bool operator==(const iterator & other);
        inline bool operator!=(const iterator & other);
    };
    /// Return an iterator pointing to the beginning of this object.
    iterator begin();
    /// Return an iterator pointing to past-the-end of this object.
    iterator end();
    /// Get the number of fields in this object (when it is the
    /// result of a SELECT).
    virtual int GetFieldCount() const = 0;
};
inline bool ResultSet::iterator::operator!=(const ResultSet::iterator & other) {
  return !(operator==(other);
}
#endif

The ResultSet class is the result when executing a SQL statement on a Database (connection):

#ifndef DBAPI_DATABASE_H
#define DBAPI_DATABASE_H

#include "ResultSet.h"

class Database {
  public:
    virtual ResultSet Execute(const char* statement) = 0;
};
#endif

Observe that the above code have not been tested or compiled in any way.

So, will the ResultSet class be generic enough to be useful as a base class when connecting to all types of database engines? How should we design the ResultRow class, do we need to iterate through the columns in a result row as well? As usual, all comments from you readers are welcome.