SQLite OOP V2 (C++11)

Before finishing off the filelistener project, I want to share even another SQLite database class with you. The last last class is, while very easy to use, also very limited: it does not support variable escaping, and thus effectively does not allow for user input without risking some form of SQL injection. There are two reasons for this: firstly I did not need to escape any user input in the project I used it for, and secondly because I did not come up with a neat way to put it in without making the class a lot less intuitive to use.

Well, for those of you that can make usage of C++11 code, here is a new and header-only version of the SQLite database class that supports parameter escaping and has a few other improvements without losing simplicity:

/*
    sqlite3 database class
    author: Jan Nikolas Jansen
    date: 2012-04-13
    
    written in c++11, tested with g++4.6.3 using -std=c++0x
    link with -lsqlite3
*/

#ifndef database_HPP
#define database_HPP

// sqlite3 C API header
#include <sqlite3.h>

// boost optional library for handling NULL values in database columns
#include <boost/optional.hpp>

// STL containers used
#include <string>
#include <map>
#include <vector>
#include <sstream>

/**
 * sqlite3 database class.
 * @author nijansen
 *
 * Enhanced C++11 version of the sqlite3 database management class published
 * in my blog earlier. Supports escaping most values to protect against SQL
 * injections by making use of variadic templates. Enables foreign key support
 * by default.
 *
 * Since this class is heavy in templates and thus cannot be neatly divided into
 * a header and source file anyway, it is published as header only.
 */
class database {
public:
    /**
     * Connects to a database.
     *
     * If the database file does not exist, it will be created if possible and
     * then opened. Foreign key support will be enabled. If everything was
     * successful, the class will boolcast to true. If something went wrong, the
     * error function provides more details.
     *
     * @param[in] filename Location of the database file.
     */
    explicit database(const std::string &filename)
        : connected_(false)
    {
        if(sqlite3_open(filename.data(), &database_) == SQLITE_OK
            && sql("PRAGMA foreign_keys = ON;"))
        {
            connected_ = true;
        }
    }

    /**
     * Closes the connection to the database.
     */
    virtual ~database() { sqlite3_close(database_); }

    /**
     * Checks for correct initialization.
     *
     * @return true, if successful. Check the error function otherwise.
     */
    explicit operator bool() const { return connected_; }

    /** Definition of a database table row. **/
    typedef std::map<std::string, boost::optional<std::string>> Row;

    /**
     * SQL Query, non-responsive version.
     *
     * Use this version if you execute statements that do not return a result
     * (like INSERT, UPDATE, etc.). You can use escaped parameters in your query
     * by writing a ? in the string instead of the desired value and appending
     * the values after the string in chronologic order.
     *
     * @param[in] query SQL statement, possibly with placeholders.
     * @param[in] args Argument list of values that need to be escaped.
     * @return true, if successful. Check the error function otherwise.
     */
    template <typename... Args> bool sql(const std::string &query,
        const Args &... args)
    {
        sql_statement call;
        if(sqlite3_prepare_v2(database_, query.data(), -1, &call.statement,
            nullptr) != SQLITE_OK)
        {
            return false;
        }
        if(!bind(call.statement, 1, args...)) {
            return false;
        }
        return sqlite3_step(call.statement) == SQLITE_DONE;
    }

    /**
     * SQL Query, responsive version.
     *
     * Use this version if you execute statements that do return a result (like
     * SELECT). You can use escaped parameters in your query by writing a ? in 
     * the string instead of the desired value and appending the values after 
     * the string in chronologic order.
     *
     * @param[in] query SQL statement, possibly with placeholders.
     * @param[in] rows Container for result that will be modified on success.
     * @param[in] args Argument list of values that need to be escaped.
     * @return true, if successful. Check the error function otherwise.
     */
    template <typename... Args> bool sql(const std::string &query,
        std::vector<Row> &rows, const Args &... args)
    {
        sql_statement call;
        if(sqlite3_prepare_v2(database_, query.data(), -1, &call.statement,
            nullptr) != SQLITE_OK)
        {
            return false;
        }
        if(!bind(call.statement, 1, args...)) {
            return false;
        }
        return get_rows(call.statement, rows);
    }

    /**
     * Is safe to call even when no error has occured yet.
     *
     * @return Last error message.
     */
    const std::string error() const {
        return sqlite3_errmsg(database_);
    }
private:
    /** sqlite3 statement wrapper for finalization **/
    struct sql_statement {
        sqlite3_stmt *statement;
        sql_statement() : statement(nullptr) {}
        ~sql_statement() { sqlite3_finalize(statement); }
    };

    /** bind dummy function for empty argument lists **/
    static bool bind(sqlite3_stmt *, const int) { return true; }

    /** bind delegator function that will call a specialized bind_struct **/
    template <typename T, typename... Args>
    static bool bind(sqlite3_stmt *statement,
        const int current, const T &first, const Args &... args)
    {
        return bind_struct<T, Args...>::f(statement, current,
            first, args...);
    }

    /** most general bind_struct that relies on implicit string conversion **/
    template <typename T, typename... Args>
    struct bind_struct {
        static bool f(sqlite3_stmt *statement, int current,
            const T &first, const Args &... args)
        {
            std::stringstream ss;
            ss << first;
            if(sqlite3_bind_text(statement, current,
                ss.str().data(), ss.str().length(),
                SQLITE_TRANSIENT) != SQLITE_OK)
            {
                return false;
            }
            return bind(statement, current+1, args...);
        }
    };

    /** bind_struct for double values **/
    template <typename... Args>
    struct bind_struct<double, Args...> {
        static bool f(sqlite3_stmt *statement, int current,
            double first, const Args &... args)
        {
            if(sqlite3_bind_double(statement, current, first)
                != SQLITE_OK)
            {
                return false;
            }
            return bind(statement, current+1, args...);
        }
    };

    /** bind_struct for int values **/
    template <typename... Args>
    struct bind_struct<int, Args...> {
        static bool f(sqlite3_stmt *statement, int current,
            int first, const Args &... args)
        {
            if(sqlite3_bind_int(statement, current, first)
                != SQLITE_OK)
            {
                return false;
            }
            return bind(statement, current+1, args...);
        }
    };

    /** bind_struct for byte arrays **/
    template <typename... Args>
    struct bind_struct<std::vector<char>, Args...> {
        static bool f(sqlite3_stmt *statement, int current,
            const std::vector<char> &first, const Args &... args)
        {
            if(sqlite3_bind_blob(statement, current,
                &first[0], first.size(),
                SQLITE_TRANSIENT) != SQLITE_OK)
            {
                return false;
            }
            return bind(statement, current+1, args...);
        }
    };

    /** retrieves rows from a prepared and bound statement **/
    bool get_rows(sqlite3_stmt *statement, std::vector<Row> &rows) {
        int retval = 0;
        do {
            retval = sqlite3_step(statement);
            if(retval != SQLITE_ROW && retval != SQLITE_DONE) {
                return false;
            }
            if(retval == SQLITE_ROW) {
                Row row;
                const auto ncols = sqlite3_column_count(statement);
                for(int i = 0; i < ncols; i++) {
                    const auto colname = sqlite3_column_name(statement, i);
                    const auto colbytes = sqlite3_column_text(statement, i);
                    const auto nbytes = sqlite3_column_bytes(statement, i);
                    if(colbytes) {
                        std::string colcontent;
                        colcontent.assign((char *)&colbytes[0], nbytes);
                        row[colname] = colcontent;
                    }
                    else {
                        row[colname] = boost::optional<std::string>();
                    }
                }
                rows.push_back(row);
            }
        } while(retval != SQLITE_DONE);
        return true;
    }

    /** true if initialized **/
    bool connected_;
    /** sqlite3 database handle **/
    sqlite3 *database_;
};

#endif // database_HPP

---

And here is a demonstration of how to use it:

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

int main() {
    database db("test.db");
    if(!db) {
        std::cerr << db.error() << std::endl;
        return 1;
    }
    std::stringstream query;
    query
        << "CREATE TABLE IF NOT EXISTS test ("
        << "    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
        << "    name VARCHAR[20] NOT NULL, "
        << "    age INTEGER"
        << ")";
    if(!db.sql(query.str())) {
        std::cerr << db.error() << std::endl;
        return 1;
    }

    if(!db.sql("INSERT INTO test (name, age) VALUES (?, ?)", "Nikolas", 23))
    {
        std::cerr << db.error() << std::endl;
        return 1;
    }

    if(!db.sql("INSERT INTO test (name) VALUES (?)", "Bob")) {
        std::cerr << db.error() << std::endl;
        return 1;
    }

    std::vector<database::Row> rows;
    if(!db.sql("SELECT name, age FROM test;", rows)) {
        std::cerr << db.error() << std::endl;
        return 1;
    }
    for(auto row = rows.begin(); row != rows.end(); row++) {
        for(auto column = row->begin(); column != row->end(); column++) {
            std::cout
                << "[" << column->first << "]\t"
                << (column->second ? *column->second : "N/A")
                << std::endl;
        }
    }

    return 0;
}

filelistener [Part 2]

Looking at the last part of the filelistener project, the next thing for us to do is:

"Write a file listener that iterates through an input directory in a given interval and triggers a purely virtual function for someone else to override"

Here is the approach to implement these features, which I recommend saving as include/filelistener.hpp:

#ifndef FILELISTENER_HPP
#define FILELISTENER_HPP

/*
    Link with the following libraries:
    -lboost_filesystem
    -lpthread
*/

#include "datastructures.hpp"
#include <boost/asio.hpp>
#include <boost/optional.hpp>
#include <boost/filesystem/operations.hpp>
#include <boost/date_time/posix_time/posix_time.hpp>

/**
 * Listens on a directory and triggers on parsed files.
 *
 * @author nijansen
 *
 * The listener will monitor the input directory in a given interval. All files within
 * the input directory will be read; if they can be parsed, the listener will call a
 * purely virtual trigger function and pass the parsed information.
 *
 * The listener is not recursive: Directories within the input directory will be
 * ignored. If files cannot be parsed, the listener will move them into an "ignored"
 * directory within the input directory.
 *
 * The listener will not throw exceptions on parsing failures. It will throw if it
 * lacks the necessary rights to read/write within the input directory. Exceptions
 * within the purely virtual trigger function must be caught by the derived class; the
 * listener will NOT catch those. This is intended to prevent unexpected behavior.
 *
 * The listener itself is thread safe. It is strongly recommended to use one io
 * service for each thread. Several listener objects may be registered to one io
 * service if they are in the same thread.
 *
 * @warning Do not set up multiple listener objects on the same input directory!
 */
class FileListener {
public:
    /**
     * Constructor that registers to an Boost Asio io service.
     *
     * The derived class must overload this constructor and call the parent constructor
     * with a valid io service.
     *
     * @param[in] io_service Asio io service the listener will register to.
     */
    FileListener(boost::asio::io_service &io_service);

    /**
     * Sets the input directory.
     *
     * @param[in] input Must be a valid (existing) directory.
     * @return true if successful, false if invalid.
     */
    bool set_input_directory(const boost::filesystem::path &input);

    /**
     * Sets the listening interval.
     *
     * @param[in] interval_ms Listening interval in milliseconds.
     */
    void set_listening_interval(long interval_ms);

    /**
     * Tries to start the listener service.
     *
     * @return true if successful.
     *
     * @warning The input directory and listening interval must be set!
     * @warning You must run the io service after this call!
     */
    bool start_service();

    /**
     * Purely virtual trigger function for derived classes.
     *
     * Derived classes must implement this function. It will be called by the
     * listener when a file was parsed successfully. The return value determines
     * whether the file will be deleted.
     *
     * @param[in] tournament Parsed data from the file.
     * @return 0 if successful (file is deleted!), any other value if failure.
     *
     * @warning On success return value, the file will be deleted permanently!
     */
    virtual int trigger(const datastructures::Tournament &tournament) = 0;
private:
    /**
     * Iterates through the input directory, and triggers on parsed files.
     */
    void listen();

    /** Input directory; default: unset. **/
    boost::optional<boost::filesystem::path> input_;
    /** Listening interval; default: unset. **/
    boost::optional<boost::posix_time::time_duration> interval_;
    /** Timer to listen in intervals. **/
    boost::asio::deadline_timer timer_;
};

#endif // FILELISTENER_HPP

---

And here is the respective implementation source/filelistener.cpp:

#include "filelistener.hpp"
#include <boost/bind.hpp>

FileListener::FileListener(boost::asio::io_service &io_service)
    : timer_(io_service)
{}

bool FileListener::set_input_directory(const boost::filesystem::path &input) {
    // Only directories are valid input paths
    if(boost::filesystem::is_directory(input)) {
        input_ = input;
        return true;
    }
    return false;
}

void FileListener::set_listening_interval(long interval_ms) {
    interval_ = boost::posix_time::milliseconds(interval_ms);
}

bool FileListener::start_service() {
    // Only start the server if the input path and listening interval have been set
    if(input_ && interval_) {
        // Set the timer
        timer_.expires_from_now(*interval_);
        timer_.async_wait(boost::bind(&FileListener::listen, this));
        return true;
    }
    return false;
}

void FileListener::listen() {
    // Iterate through the input directory
    for(boost::filesystem::directory_iterator file(*input_);
        file != boost::filesystem::directory_iterator(); file++)
    {
        boost::filesystem::path current_file = file->path();
        // Ignore directories
        if(boost::filesystem::is_directory(current_file)) {
            continue;
        }
        // Try to parse the file
        datastructures::Tournament tournament;
        try {
            tournament.load(current_file.native());
        }
        catch(...) {
            // For performance reasons, move files that failed parsing into input/ignored,
            // so we will not try to parse them over and over again
            boost::filesystem::path ignored = *input_/"ignored";
            if(!boost::filesystem::exists(ignored)) {
                boost::filesystem::create_directory(ignored);
            }
            boost::filesystem::copy_file(current_file, ignored/current_file.filename());
            boost::filesystem::remove(current_file);
            continue;
        }
        // Call the trigger function of the derived class
        if(trigger(tournament) == 0) {
            // Only delete the file if the trigger function submitted the success value;
            // the file will trigger again later otherwise
            boost::filesystem::remove(current_file);
        }
    }
    // Reset the timer
    timer_.expires_from_now(*interval_);
    timer_.async_wait(boost::bind(&FileListener::listen, this));
}

---

This leaves us with only one more step for this project: an example implementation. Coming up in a future post, of course.