Reworked RecordSet and implemented the SQLite Data Provider

9 files changed, 421 insertions, 180 deletions

diff --git a/src/dal/dalexcept.h b/src/dal/dalexcept.h
index e11ec9d1..047cb51d 100644
--- a/src/dal/dalexcept.h
+++ b/src/dal/dalexcept.h
@@ -205,6 +205,14 @@ class DbSqlQueryExecFailure: public DbException
 };
 
 
+/**
+ * Already set exception.
+ */
+class AlreadySetException: public std::exception
+{
+};
+
+
 } // namespace dal
 } // namespace tmw
 
diff --git a/src/dal/dataprovider.cpp b/src/dal/dataprovider.cpp
index c72ce50d..5475ad53 100644
--- a/src/dal/dataprovider.cpp
+++ b/src/dal/dataprovider.cpp
@@ -36,7 +36,7 @@ namespace dal
 DataProvider::DataProvider(void)
     throw()
         : mIsConnected(false),
-          mRecordSet(RecordSet())
+          mRecordSet()
 {
     // NOOP
 }
diff --git a/src/dal/dataprovider.h b/src/dal/dataprovider.h
index b5542e49..4b3f2ecf 100644
--- a/src/dal/dataprovider.h
+++ b/src/dal/dataprovider.h
@@ -111,9 +111,30 @@ class DataProvider
 
 
         /**
+         * Create a connection to the database.
+         *
+         * @param dbName the database name.
+         * @param dbPath the database file path.
+         * @param userName the user name.
+         * @param password the user password.
+         *
+         * @exception DbConnectionFailure if unsuccessful connection.
+         * @exception std::exception if unexpected exception.
+         */
+        virtual void
+        connect(const std::string& dbName,
+                const std::string& dbPath,
+                const std::string& userName,
+                const std::string& password)
+            throw(DbConnectionFailure,
+                  std::exception) = 0;
+
+
+        /**
          * Execute a SQL query.
          *
          * @param sql the SQL query.
+         * @param refresh if true, refresh the cache (optional)
          *
          * @return a recordset.
          *
@@ -121,7 +142,8 @@ class DataProvider
          * @exception std::exception if unexpected exception.
          */
         virtual const RecordSet&
-        execSql(const std::string& sql)
+        execSql(const std::string& sql,
+                const bool refresh = false)
             throw(DbSqlQueryExecFailure,
                   std::exception) = 0;
 
@@ -150,6 +172,18 @@ class DataProvider
 
     protected:
         /**
+         * The database name.
+         */
+        std::string mDbName;
+
+
+        /**
+         * The database path.
+         */
+        std::string mDbPath;
+
+
+        /**
          * The connection status.
          */
         bool mIsConnected;
diff --git a/src/dal/mysqldataprovider.cpp b/src/dal/mysqldataprovider.cpp
index 6ddabe07..de3418f4 100644
--- a/src/dal/mysqldataprovider.cpp
+++ b/src/dal/mysqldataprovider.cpp
@@ -84,6 +84,21 @@ MySqlDataProvider::connect(const std::string& dbName,
     throw(DbConnectionFailure,
           std::exception)
 {
+    connect(dbName, "", userName, password);
+}
+
+
+/**
+ * Create a connection to the database.
+ */
+void
+MySqlDataProvider::connect(const std::string& dbName,
+                           const std::string& dbPath,
+                           const std::string& userName,
+                           const std::string& password)
+    throw(DbConnectionFailure,
+          std::exception)
+{
     // TODO
 }
 
@@ -92,13 +107,15 @@ MySqlDataProvider::connect(const std::string& dbName,
  * Execute a SQL query.
  */
 const RecordSet&
-MySqlDataProvider::execSql(const std::string& sql)
+MySqlDataProvider::execSql(const std::string& sql,
+                           const bool refresh)
     throw(DbSqlQueryExecFailure,
           std::exception)
 {
     // do something only if the query is different from the previous
+    // or if the cache must be refreshed
     // otherwise just return the recordset from cache.
-    if (sql != mSql) {
+    if (refresh || (sql != mSql)) {
         // TODO
     }
 
diff --git a/src/dal/mysqldataprovider.h b/src/dal/mysqldataprovider.h
index 687ed38f..967e9ce2 100644
--- a/src/dal/mysqldataprovider.h
+++ b/src/dal/mysqldataprovider.h
@@ -101,9 +101,30 @@ class MySqlDataProvider: public DataProvider
 
 
         /**
+         * Create a connection to the database.
+         *
+         * @param dbName the database name.
+         * @param dbPath the database file path.
+         * @param userName the user name.
+         * @param password the user password.
+         *
+         * @exception DbConnectionFailure if unsuccessful connection.
+         * @exception std::exception if unexpected exception.
+         */
+        void
+        connect(const std::string& dbName,
+                const std::string& dbPath,
+                const std::string& userName,
+                const std::string& password)
+            throw(DbConnectionFailure,
+                  std::exception);
+
+
+        /**
          * Execute a SQL query.
          *
          * @param sql the SQL query.
+         * @param refresh if true, refresh the cache (optional)
          *
          * @return a recordset.
          *
@@ -111,7 +132,8 @@ class MySqlDataProvider: public DataProvider
          * @exception std::exception if unexpected exception.
          */
         const RecordSet&
-        execSql(const std::string& sql)
+        execSql(const std::string& sql,
+                const bool refresh = false)
             throw(DbSqlQueryExecFailure,
                   std::exception);
 
diff --git a/src/dal/recordset.cpp b/src/dal/recordset.cpp
index fdaf6614..25862d3d 100644
--- a/src/dal/recordset.cpp
+++ b/src/dal/recordset.cpp
@@ -35,7 +35,7 @@ namespace dal
 /**
  * Default constructor.
  */
-Record::Record(void)
+RecordSet::RecordSet(void)
     throw()
 {
     // NOOP
@@ -45,7 +45,7 @@ Record::Record(void)
 /**
  * Destructor.
  */
-Record::~Record(void)
+RecordSet::~RecordSet(void)
     throw()
 {
     // NOOP
@@ -53,144 +53,169 @@ Record::~Record(void)
 
 
 /**
- * Add a new field.
+ * Remove all the Records.
  */
 void
-Record::addField(const std::string& name,
-                 const std::string& value)
+RecordSet::clear(void)
     throw()
 {
-    mFields.insert(Fields::value_type(name, value));
+    mHeaders.clear();
+    mRows.clear();
 }
 
 
 /**
- * Add a new field.
+ * Get the number of rows.
+ *
+ * @return the number of rows.
  */
-void
-Record::addField(const std::string& name,
-                 const double value)
+unsigned int
+RecordSet::rows(void)
     throw()
 {
-    // convert the number into a string.
-    std::ostringstream os;
-    os << value << std::ends;
-
-    mFields.insert(Fields::value_type(name, os.str()));
+    return mRows.size();
 }
 
 
 /**
- * Get the field value.
+ * Get the number of columns.
+ *
+ * @return the number of columns.
  */
-const std::string&
-Record::get(const std::string& name) const
-    throw(std::invalid_argument)
+unsigned int
+RecordSet::cols(void)
+    throw()
 {
-    return getAsString(name);
+    return mHeaders.size();
 }
 
 
 /**
- * Get the field value as string.
+ * Set the column headers.
  */
-const std::string&
-Record::getAsString(const std::string& name) const
-    throw(std::invalid_argument)
+void
+RecordSet::setColumnHeaders(const Row& headers)
+    throw(AlreadySetException)
 {
-    Fields::const_iterator it = mFields.find(name);
-
-    if (it == mFields.end()) {
-        std::ostringstream msg;
-        msg << "unknown field name: " << name << std::ends;
-
-        throw std::invalid_argument(msg.str());
+    if (mHeaders.size() > 0) {
+        throw AlreadySetException();
     }
 
-    return it->second;
+    mHeaders = headers;
 }
 
 
 /**
- * Get the field value as a number.
+ * Add a new row.
  */
-const double
-Record::getAsNumber(const std::string& name) const
+void
+RecordSet::add(const Row& row)
     throw(std::invalid_argument)
 {
-    std::istringstream is(getAsString(name));
-    double doubleValue;
-
-    // convert the string to a number.
-    is >> doubleValue;
+    if (row.size() != mHeaders.size()) {
+        throw std::invalid_argument(
+            "the new row does not have the required number of columns.");
+    }
 
-    return doubleValue;
+    mRows.push_back(row);
 }
 
 
 /**
- * Default constructor.
+ * Operator()
  */
-RecordSet::RecordSet(void)
-    throw()
+const std::string&
+RecordSet::operator()(const unsigned int row,
+                      const unsigned int col) const
+    throw(std::out_of_range)
 {
-    // NOOP
+    if ((row >= mRows.size()) || (col >= mHeaders.size())) {
+        std::ostringstream os;
+        os << "(" << row << ", " << col << ") is out of range; "
+           << "max rows: " << mRows.size()
+           << ", max cols: " << mHeaders.size() << std::ends;
+
+        throw std::out_of_range(os.str());
+    }
+
+    return mRows[row][col];
 }
 
 
 /**
- * Destructor.
+ * Operator()
  */
-RecordSet::~RecordSet(void)
-    throw()
+const std::string&
+RecordSet::operator()(const unsigned int row,
+                      const std::string& name) const
+    throw(std::out_of_range,
+          std::invalid_argument)
 {
-    Rows::iterator it = mRows.begin();
-    Rows::iterator it_end = mRows.end();
+    if (row >= mRows.size()) {
+        std::ostringstream os;
+        os << "row " << row << " is out of range; "
+           << "max rows: " << mRows.size() << std::ends;
 
-    for(; it != it_end; ++it) {
-        delete *it;
+        throw std::out_of_range(os.str());
     }
-}
 
+    Row::const_iterator it = std::find(mHeaders.begin(),
+                                       mHeaders.end(),
+                                       name);
+    if (it == mHeaders.end()) {
+        std::ostringstream os;
+        os << "field " << name << " does not exist." << std::ends;
 
-/**
- * Add a new Record.
- */
-void
-RecordSet::addRecord(const Record* record)
-    throw()
-{
-    mRows.push_back(record);
-}
+        throw std::invalid_argument(os.str());
+    }
 
+    // find the field index.
+    const int nCols = mHeaders.size();
+    int i;
+    for (i = 0; i < nCols; ++i) {
+        if (mHeaders[i] == name) {
+            break;
+        }
+    }
 
-/**
- * Get all the rows.
- */
-const Rows&
-RecordSet::getRows(void) const
-    throw()
-{
-    return mRows;
+    return mRows[row][i];
 }
 
 
 /**
- * Get a particular row.
+ * Operator<<
  */
-const Record&
-RecordSet::getRow(const unsigned int row) const
-    throw(std::out_of_range)
+std::ostream&
+operator<<(std::ostream& out, const RecordSet& rhs)
 {
-    if (row >= mRows.size()) {
-        std::ostringstream msg;
-        msg << "index out of range: " << row << "; "
-            << "num. of records: " << mRows.size() << std::ends;
+    using namespace tmw::dal;
+
+    // print the field names first.
+    if (rhs.mHeaders.size() > 0) {
+        Row::const_iterator it = rhs.mHeaders.begin();
+        out << "[ " << (*it);
+        it++;
+        for (; it != rhs.mHeaders.end(); ++it) {
+            out << " | " << (*it);
+        }
+        out << " ]" << std::endl;
+    }
 
-        throw std::out_of_range(msg.str());
+    // and then print every line.
+    for (RecordSet::Rows::const_iterator it = rhs.mRows.begin();
+         it != rhs.mRows.end();
+         ++it)
+    {
+        Row::const_iterator it2 = (*it).begin();
+        out << "[ " << (*it2);
+        it2++;
+        for (; it2 != (*it).end(); ++it2) {
+            out << " | " << (*it2);
+        }
+        out << " ]" << std::endl;
     }
 
-    return *(mRows[row]);
+    return out;
 }
 
 
diff --git a/src/dal/recordset.h b/src/dal/recordset.h
index 8804588c..1eed47dc 100644
--- a/src/dal/recordset.h
+++ b/src/dal/recordset.h
@@ -25,10 +25,12 @@
 #define _TMW_RECORDSET_H_
 
 
-#include <map>
+#include <iostream>
 #include <vector>
 #include <stdexcept>
 
+#include "dalexcept.h"
+
 
 namespace tmw
 {
@@ -37,166 +39,168 @@ namespace dal
 
 
 /**
- * A Record.
+ * A record from the RecordSet.
  */
-class Record
+typedef std::vector<std::string> Row;
+
+
+/**
+ * A RecordSet to store the result of a SQL query.
+ *
+ * Limitations: the field values are stored and returned as string,
+ * no information about the field data types are stored.
+ */
+class RecordSet
 {
     public:
         /**
          * Default constructor.
          */
-        Record(void)
+        RecordSet(void)
             throw();
 
 
         /**
          * Destructor.
          */
-        ~Record(void)
+        ~RecordSet(void)
             throw();
 
 
         /**
-         * Add a new field.
-         *
-         * @param name the field name.
-         * @param value the field value
+         * Remove all the Records.
          */
         void
-        addField(const std::string& name,
-                 const std::string& value)
+        clear(void)
             throw();
 
 
         /**
-         * Add a new field.
+         * Get the number of rows.
          *
-         * @param name the field name.
-         * @param value the field value
+         * @return the number of rows.
          */
-        void
-        addField(const std::string& name,
-                 const double value)
+        unsigned int
+        rows(void)
             throw();
 
 
         /**
-         * Get the field value.
-         *
-         * This method is an alias of getAsString().
-         *
-         * @param name the field name.
-         *
-         * @return the value as string.
+         * Get the number of columns.
          *
-         * @exception std::invalid_argument if the field is not found.
+         * @return the number of columns.
          */
-        const std::string&
-        get(const std::string& name) const
-            throw(std::invalid_argument);
+        unsigned int
+        cols(void)
+            throw();
 
 
         /**
-         * Get the field value as string.
-         *
-         * @param name the field name.
+         * Set the column headers.
          *
-         * @return the value as string.
+         * @param headers the column headers.
          *
-         * @exception std::invalid_argument if the field is not found.
+         * @exception AlreadySetException if the column headers
+         *            are already set.
          */
-        const std::string&
-        getAsString(const std::string& name) const
-            throw(std::invalid_argument);
+        void
+        setColumnHeaders(const Row& headers)
+            throw(AlreadySetException);
 
 
         /**
-         * Get the field value as a number.
+         * Add a new row.
          *
-         * @param name the field name.
+         * This method does not check the field data types, only the number
+         * of columns is checked.
          *
-         * @return the value as float.
+         * @param row the new row.
          *
-         * @exception std::invalid_argument if the field is not found.
+         * @exception std::invalid_argument if the number of columns in the
+         *            new row is not equal to the number of column headers.
          */
-        const double
-        getAsNumber(const std::string& name) const
+        void
+        add(const Row& row)
             throw(std::invalid_argument);
 
 
-    private:
         /**
-         * A map to hold the field names and their associated values.
+         * Operator()
+         * Get the value of a particular field of a particular row
+         * by field index.
+         *
+         * @param row the row index.
+         * @param col the field index.
+         *
+         * @return the field value.
+         *
+         * @exception std::out_of_range if row or col are out of range.
          */
-        typedef std::map<std::string, std::string> Fields;
-        Fields mFields;
-};
-
-
-/**
- * A list of Records.
- */
-typedef std::vector<const Record*> Rows;
+        const std::string&
+        operator()(const unsigned int row,
+                   const unsigned int col) const
+            throw(std::out_of_range);
 
 
-/**
- * A RecordSet to store the result of a SQL query.
- */
-class RecordSet
-{
-    public:
         /**
-         * Default constructor.
+         * Operator()
+         * Get the value of a particular field of a particular row
+         * by field name (slower than by field index).
+         *
+         * @param row the row index.
+         * @param name the field name.
+         *
+         * @return the field value.
+         *
+         * @exception std::out_of_range if the row index is out of range.
+         * @exception std::invalid_argument if the field name is not found.
          */
-        RecordSet(void)
-            throw();
+        const std::string&
+        operator()(const unsigned int row,
+                   const std::string& name) const
+            throw(std::out_of_range,
+                  std::invalid_argument);
 
 
         /**
-         * Destructor.
+         * Operator<<
+         * Append the stringified RecordSet to the output stream.
+         *
+         * @param out the output stream.
+         * @param rhs the right hand side.
+         *
+         * @return the output stream for chaining.
          */
-        ~RecordSet(void)
-            throw();
+        friend std::ostream&
+        operator<<(std::ostream& out, const RecordSet& rhs);
 
 
+    private:
         /**
-         * Add a new Record.
-         *
-         * @param record the new record.
+         * Copy constructor.
          */
-        void
-        addRecord(const Record*)
+        RecordSet(const RecordSet& rhs)
             throw();
 
 
         /**
-         * Get all the rows.
-         *
-         * @return all the rows of the RecordSet.
+         * Operator=
          */
-        const Rows&
-        getRows(void) const
+        RecordSet&
+        operator=(const RecordSet& rhs)
             throw();
 
 
         /**
-         * Get a particular row.
-         *
-         * @param row the row index.
-         *
-         * @return the Record at the specified row index.
-         *
-         * @exception std::out_of_range
+         * A list of field names.
          */
-        const Record&
-        getRow(const unsigned int row) const
-            throw(std::out_of_range);
+        Row mHeaders;
 
 
-    private:
         /**
-         * A list of Records.
+         * A list of records.
          */
+        typedef std::vector<Row> Rows;
         Rows mRows;
 };
 
diff --git a/src/dal/sqlitedataprovider.cpp b/src/dal/sqlitedataprovider.cpp
index 03e7acf7..f30d0762 100644
--- a/src/dal/sqlitedataprovider.cpp
+++ b/src/dal/sqlitedataprovider.cpp
@@ -21,6 +21,8 @@
  */
 
 
+#include <iostream>
+
 #include "sqlitedataprovider.h"
 
 
@@ -35,6 +37,7 @@ namespace dal
  */
 SqLiteDataProvider::SqLiteDataProvider(void)
     throw()
+        : mDb(0)
 {
     // NOOP
 }
@@ -46,7 +49,15 @@ SqLiteDataProvider::SqLiteDataProvider(void)
 SqLiteDataProvider::~SqLiteDataProvider(void)
     throw()
 {
-    // NOOP
+    try {
+        // make sure that the database is closed.
+        // disconnect() calls sqlite3_close() which takes care of freeing
+        // the memory held by the class attribute mDb.
+        disconnect();
+    }
+    catch (const DbDisconnectionFailure& e) {
+        // TODO
+    }
 }
 
 
@@ -70,7 +81,18 @@ SqLiteDataProvider::createDb(const std::string& dbName,
     throw(DbCreationFailure,
           std::exception)
 {
-    // TODO
+    // TODO: handle dbPath
+
+    // sqlite3_open creates the database file if it does not exist
+    // and hence createDb is not very useful here.
+    if (sqlite3_open(dbName.c_str(), &mDb) != SQLITE_OK) {
+        throw DbCreationFailure(sqlite3_errmsg(mDb));
+    }
+
+    // nothing else to do, close the database.
+    if (sqlite3_close(mDb) != SQLITE_OK) {
+        throw DbCreationFailure(sqlite3_errmsg(mDb));
+    }
 }
 
 
@@ -84,7 +106,33 @@ SqLiteDataProvider::connect(const std::string& dbName,
     throw(DbConnectionFailure,
           std::exception)
 {
-    // TODO
+    connect(dbName, "", userName, password);
+}
+
+
+/**
+ * Create a connection to the database.
+ */
+void
+SqLiteDataProvider::connect(const std::string& dbName,
+                            const std::string& dbPath,
+                            const std::string& userName,
+                            const std::string& password)
+    throw(DbConnectionFailure,
+          std::exception)
+{
+    // TODO: handle dbPath
+
+    // sqlite3_open creates the database file if it does not exist.
+    if (sqlite3_open(dbName.c_str(), &mDb) != SQLITE_OK) {
+        // there is no need to check for the error code returned by
+        // sqlite3_close() as we are going to throw an exception anyway
+        sqlite3_close(mDb);
+
+        throw DbConnectionFailure(sqlite3_errmsg(mDb));
+    }
+
+    mIsConnected = true;
 }
 
 
@@ -92,14 +140,58 @@ SqLiteDataProvider::connect(const std::string& dbName,
  * Execute a SQL query.
  */
 const RecordSet&
-SqLiteDataProvider::execSql(const std::string& sql)
+SqLiteDataProvider::execSql(const std::string& sql,
+                            const bool refresh)
     throw(DbSqlQueryExecFailure,
           std::exception)
 {
     // do something only if the query is different from the previous
+    // or if the cache must be refreshed
     // otherwise just return the recordset from cache.
-    if (sql != mSql) {
-        // TODO
+    if (refresh || (sql != mSql)) {
+        char** result;
+        int nRows;
+        int nCols;
+        char* errMsg;
+
+        int errCode = sqlite3_get_table(
+  			              mDb,          // an open database
+  			              sql.c_str(),  // SQL to be executed
+                          &result,      // result of the query
+                          &nRows,       // number of result rows
+                          &nCols,       // number of result columns
+                          &errMsg       // error msg
+                      );
+
+        if (errCode != SQLITE_OK) {
+            std::cout << sqlite3_errmsg(mDb) << std::endl;
+            throw DbSqlQueryExecFailure();
+        }
+
+        mRecordSet.clear();
+
+        // the first row of result[] contains the field names.
+        Row fieldNames;
+        for(int col = 0; col < nCols; ++col) {
+            fieldNames.push_back(result[col]);
+        }
+        mRecordSet.setColumnHeaders(fieldNames);
+
+        // populate the RecordSet
+        for (int row = 0; row < nRows; ++row) {
+            Row r;
+
+            for(int col = 0; col < nCols; ++col) {
+                r.push_back(result[nCols + (row * nCols) + col]);
+
+            }
+
+            mRecordSet.add(r);
+        }
+
+        // free memory
+        sqlite3_free_table(result);
+        delete errMsg;
     }
 
     return mRecordSet;
@@ -114,7 +206,15 @@ SqLiteDataProvider::disconnect(void)
     throw(DbDisconnectionFailure,
           std::exception)
 {
-    // TODO
+    if (!isConnected()) {
+        return;
+    }
+
+    if (sqlite3_close(mDb) != SQLITE_OK) {
+        throw DbDisconnectionFailure(sqlite3_errmsg(mDb));
+    }
+
+    mIsConnected = false;
 }
 
 
diff --git a/src/dal/sqlitedataprovider.h b/src/dal/sqlitedataprovider.h
index 4780bfbf..f113bd54 100644
--- a/src/dal/sqlitedataprovider.h
+++ b/src/dal/sqlitedataprovider.h
@@ -27,6 +27,8 @@
 
 #include <string>
 
+#include "sqlite3.h"
+
 #include "dataprovider.h"
 
 
@@ -101,9 +103,30 @@ class SqLiteDataProvider: public DataProvider
 
 
         /**
+         * Create a connection to the database.
+         *
+         * @param dbName the database name.
+         * @param dbPath the database file path.
+         * @param userName the user name.
+         * @param password the user password.
+         *
+         * @exception DbConnectionFailure if unsuccessful connection.
+         * @exception std::exception if unexpected exception.
+         */
+        void
+        connect(const std::string& dbName,
+                const std::string& dbPath,
+                const std::string& userName,
+                const std::string& password)
+            throw(DbConnectionFailure,
+                  std::exception);
+
+
+        /**
          * Execute a SQL query.
          *
          * @param sql the SQL query.
+         * @param refresh if true, refresh the cache (optional)
          *
          * @return a recordset.
          *
@@ -111,7 +134,8 @@ class SqLiteDataProvider: public DataProvider
          * @exception std::exception if unexpected exception.
          */
         const RecordSet&
-        execSql(const std::string& sql)
+        execSql(const std::string& sql,
+                const bool refresh = false)
             throw(DbSqlQueryExecFailure,
                   std::exception);
 
@@ -126,6 +150,13 @@ class SqLiteDataProvider: public DataProvider
         disconnect(void)
             throw(DbDisconnectionFailure,
                   std::exception);
+
+
+    private:
+        /**
+         * The database.
+         */
+        sqlite3* mDb;
 };