SQLite OOP

Before continuing with the FileListener project, I want to share a class with you. I am currently working on a project that is heavy into storing data, so I wanted to use some database. Yet I do not want to force the user to set up a certain database framework just for this project. Because of this, I finally chose to use SQLite. SQLite comes with an C API, but I was really missing the comfort of object oriented structures. I ended up writing my own class for a very simple database management that suffices my needs. It does not have a lot (in fact, nearly nothing) to do with the Boost libraries this blog is dedicated to, but if someone else is looking for an object oriented approach for SQLite, this might come in handy, so I will share it anyways:

database.hpp:

---

#ifndef DATABASE_HPP
#define DATABASE_HPP

#include <sqlite3.h>
#include <boost/optional.hpp>
#include <string>
#include <map>
#include <vector>

class Database {
public:
    Database() : connected_(false) {}
    bool connect(const std::string &filename) throw();
    virtual ~Database();

    typedef std::map<std::string, boost::optional<std::string> > Row;
    bool sql_query(const std::string &query, std::vector<Row> *rows=0) throw();

    std::string error() const throw();
private:
    bool connected_;
    sqlite3 *database_;
};

#endif // DATABASE_HPP

database.cpp:

---

#include "database.hpp"

bool Database::connect(const std::string &filename) throw() {
    if(connected_) {
        sqlite3_close(database_);
        connected_ = false;
    }
    if(sqlite3_open(filename.data(), &database_) == SQLITE_OK) {
        connected_ = true;
        return true;
    }
    return false;
}

Database::~Database() {
    sqlite3_close(database_);
}

bool Database::sql_query(const std::string &query, std::vector<Row> *rows) throw() {
    struct call_struct {
        sqlite3_stmt *statement;
        call_struct() : statement(0) {}
        ~call_struct() { sqlite3_finalize(statement); }
    } call;

    if(sqlite3_prepare(database_, query.data(), -1, &call.statement, 0) != SQLITE_OK) {
        return false;
    }

    int retval;
    do {
        retval = sqlite3_step(call.statement);
        if(retval != SQLITE_ROW && retval != SQLITE_DONE) {
            return false;
        }
        if(retval == SQLITE_ROW) {
            Row row;
            const int ncols = sqlite3_column_count(call.statement);
            for(int i = 0; i < ncols; i++) {
                const std::string colname = sqlite3_column_name(call.statement, i);
                const unsigned char *colbytes = sqlite3_column_text(call.statement, i);
                const int nbytes = sqlite3_column_bytes(call.statement, i);
                if(colbytes) {
                    std::string colcontent;
                    colcontent.assign((char*)&colbytes[0], nbytes);
                    row[colname] = colcontent;
                }
                else {
                    row[colname] = boost::optional<std::string>();
                }
            }
            if(rows) {
                rows->push_back(row);
            }
        }
    } while(retval != SQLITE_DONE);

    return true;
}

std::string Database::error() const throw() {
    return sqlite3_errmsg(database_);
}

It is not the most powerful class out there, but it is very easy and straight forward to use. Here are some examples:

#include "database.hpp"
#include <iostream>
#include <sstream>

int main() {
    Database database;
    if(!database.connect("my_database.db")) {
        std::cerr << database.error() << std::endl;
        return 1;
    }
    std::stringstream query;
    query
        << "CREATE TABLE users ("
        << "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        << "name VARCHAR[30] NOT NULL, "
        << "email VARCHAR[100]);";
    if(!database.sql_query(query.str())) {
        std::cerr << "Query failed: " << query.str() << std::endl
            << database.error() << std::endl;
        return 1;
    }
    return 0;
}

---

#include "database.hpp"
#include <iostream>
#include <sstream>

int main() {
    Database database;
    if(!database.connect("my_database.db")) {
        std::cerr << database.error() << std::endl;
        return 1;
    }
    std::stringstream query;
    query
        << "INSERT INTO users "
        << "(name, email) VALUES ('test', 'a@b.com');";
    if(!database.sql_query(query.str())) {
        std::cerr << "Query failed: " << query.str() << std::endl
            << database.error() << std::endl;
        return 1;
    }
    query.str(std::string());
    query
        << "INSERT INTO users "
        << "(name) VALUES ('dummy');";
    if(!database.sql_query(query.str())) {
        std::cerr << "Query failed: " << query.str() << std::endl
            << database.error() << std::endl;
        return 1;
    }
    return 0;
}

---

#include "database.hpp"
#include <iostream>
#include <sstream>

int main() {
    Database database;
    if(!database.connect("my_database.db")) {
        std::cerr << database.error() << std::endl;
        return 1;
    }
    std::stringstream query;
    query
        << "SELECT name, email FROM users;";
    std::vector<Database::Row> rows;
    if(!database.sql_query(query.str(), &rows)) {
        std::cerr << "Query failed: " << query.str() << std::endl
            << database.error() << std::endl;
        return 1;
    }
    int current_row = 1;
    for(std::vector<Database::Row>::const_iterator row = rows.begin();
        row != rows.end(); row++)
    {
        std::cout << "[" << current_row << "]" << std::endl;

        for(Database::Row::const_iterator column = row->begin();
            column != row->end(); column++)
        {
            std::cout << "\t"<< column->first << ": "
                << (column->second ? *column->second : "NULL")
                << std::endl;
        }
        current_row++;
    }

    return 0;
}