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;
}

filelistener [Part 1]

This time, as promised, we will look at a small project, rather than a snippet. Imagine the following context:

A customer is hosting a website organizing esport tournaments. He is providing us with xml files for each tournament, containing information about the tournament's name, its sponsors, the participating clans and their players, etc. Our job is to write a file listener, that will notice when a new xml file is saved in an input directory, try to parse this file, and sends triggers so someone else can implement something useful to do with the parsed information.

Our task will be split into these parts:

1. Defining the datastructures used within the XML files and write a parser for these files
2. 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
3. Construct a very simple demonstration of how to implement the purely virtual function and embed it into a fully functional program

Lets dive right into datastructures: we can store the information within structs. For example:

struct Sponsor {
    std::string name;
    std::string description;
    std::string url_website;
    std::string url_image;
};

However, we notice by looking through the example XML files provided by our customer that most values are optional, and may not be referenced at all. We will need to deal with these optional values somehow, and the from reading my last post, you already know one possible approach: pointers. While using pointers for optional values is a thoughtful approach, my first post was dedicated to explaining why large pointer structures are inconvenient to work with. There must be a better approach for this. Of course, Boost does have a library for this problem: Boost Optional. It is ridiculously easy to apply:

struct Sponsor {
    std::string name;
    boost::optional<std::string> description;
    boost::optional<std::string> url_website;
    boost::optional<std::string> url_image;
};

Handling these values feels naturally, using the dereference operator makes them feel a lot like the pointers used by gSOAP, but we can, for example, assign values to them directy, do not have to worry about allocating any memory for them, etc. Now we already have the structure in which we can store our information. What is missing is the parsing. Parsing is a rather complicated field, but, of course, Boost has it done for us already with the Boost Property Tree. All we have to do is add a load (and for demonstration purposes also a save) function, adjust it to our structures and we are good to go. Here is an example interface; if you want to compile the project afterwards I recommend saving it to include/datastructures.hpp:

#ifndef DATASTRUCTURES_HPP
#define DATASTRUCTURES_HPP

#include <boost/property_tree/ptree.hpp>
#include <boost/optional.hpp>
#include <string>
#include <vector>

namespace datastructures {
    using boost::property_tree::ptree;

    struct BaseStructure {
        std::string name;
        boost::optional<std::string> description;
        boost::optional<std::string> url_website;
        boost::optional<std::string> url_image;

        virtual void load(const ptree &pt) = 0;
        virtual ptree save() const = 0;
    };

    struct Player : BaseStructure {
        boost::optional<std::string> first_name;
        boost::optional<std::string> last_name;
        boost::optional<int> age;

        void load(const ptree &pt);
        ptree save() const;
    };

    struct Clan : BaseStructure {
        std::vector<Player> players;

        void load(const ptree &pt);
        ptree save() const;
    };

    struct Sponsor : BaseStructure {
        void load(const ptree &pt);
        ptree save() const;
    };

    struct Tournament : BaseStructure {
        std::vector<Sponsor> sponsors;
        std::vector<Clan> clans;

        void load(const ptree &pt);
        void load(const std::string &filename);
        ptree save() const;
        void save(const std::string &filename) const;
    };
}

#endif // DATASTRUCTURES_HPP

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

#include "datastructures.hpp"
#include <boost/property_tree/xml_parser.hpp>
#include <boost/foreach.hpp>

using namespace datastructures;

void Player::load(const ptree &pt) {
    name = pt.get<std::string>("name");
    description = pt.get_optional<std::string>("description");
    url_website = pt.get_optional<std::string>("url_website");
    url_image = pt.get_optional<std::string>("url_image");

    first_name = pt.get_optional<std::string>("first_name");
    last_name = pt.get_optional<std::string>("last_name");
    age = pt.get_optional<int>("age");
}

ptree Player::save() const {
    ptree pt;

    pt.put("name", name);
    if(description) pt.put("description", *description);
    if(url_website) pt.put("url_website", *url_website);
    if(url_image) pt.put("url_image", *url_image);

    if(first_name) pt.put("first_name", *first_name);
    if(last_name) pt.put("last_name", *last_name);
    if(age) pt.put("age", *age);

    return pt;
}

void Clan::load(const ptree &pt) {
    name = pt.get<std::string>("name");
    description = pt.get_optional<std::string>("description");
    url_website = pt.get_optional<std::string>("url_website");
    url_image = pt.get_optional<std::string>("url_image");

    BOOST_FOREACH(const ptree::value_type &val, pt.get_child("players")) {
        Player player;
        player.load(val.second);
        players.push_back(player);
    }
}

ptree Clan::save() const {
    ptree pt;

    pt.put("name", name);
    if(description) pt.put("description", *description);
    if(url_website) pt.put("url_website", *url_website);
    if(url_image) pt.put("url_image", *url_image);

    for(std::vector<Player>::const_iterator player = players.begin();
        player != players.end(); player++)
    {
        pt.add_child("players.player", player->save());
    }

    return pt;
}

void Sponsor::load(const ptree &pt) {
    name = pt.get<std::string>("name");
    description = pt.get_optional<std::string>("description");
    url_website = pt.get_optional<std::string>("url_website");
    url_image = pt.get_optional<std::string>("url_image");
}

ptree Sponsor::save() const {
    ptree pt;

    pt.put("name", name);
    if(description) pt.put("description", *description);
    if(url_website) pt.put("url_website", *url_website);
    if(url_image) pt.put("url_image", *url_image);

    return pt;
}

void Tournament::load(const ptree &pt) {
    name = pt.get<std::string>("name");
    description = pt.get_optional<std::string>("description");
    url_website = pt.get_optional<std::string>("url_website");
    url_image = pt.get_optional<std::string>("url_image");

    BOOST_FOREACH(const ptree::value_type &val, pt.get_child("sponsors")) {
        Sponsor sponsor;
        sponsor.load(val.second);
        sponsors.push_back(sponsor);
    }

    BOOST_FOREACH(const ptree::value_type &val, pt.get_child("clans")) {
        Clan clan;
        clan.load(val.second);
        clans.push_back(clan);
    }
}

void Tournament::load(const std::string &filename) {
    ptree pt;
    read_xml(filename, pt);
    load(pt.get_child("tournament"));
}

ptree Tournament::save() const {
    ptree pt;

    pt.put("name", name);
    if(description) pt.put("description", *description);
    if(url_website) pt.put("url_website", *url_website);
    if(url_image) pt.put("url_image", *url_image);

    for(std::vector<Sponsor>::const_iterator sponsor = sponsors.begin();
        sponsor != sponsors.end(); sponsor++)
    {
        pt.add_child("sponsors.sponsor", sponsor->save());
    }

    for(std::vector<Clan>::const_iterator clan = clans.begin();
        clan != clans.end(); clan++)
    {
        pt.add_child("clans.clan", clan->save());
    }

    return pt;
}

void Tournament::save(const std::string &filename) const {
    ptree pt;
    pt.put_child("tournament", save());
    write_xml(filename, pt);
}

Isn't this incredibly easy? You could, by the way, blame me for breaking the DRY idiom, there is definitely space for improvement in that implementation, but it serves the little demonstration here. If you have not yet worked with property trees, I recommend doing a bit of playing around with these datastructures, and the XML files they produce before we continue to the next step, that will follow up with the next post.

Welcome!

First post, yay. In this blog I am going to share projects, snippets and thoughts about C++ projects mainly focused on solving problems using the Boost C++ Libraries. Basically whenever I write something in a compiled programming language, C++ will be my weapon of choice. The primary reason for this actually are the Boost libraries. I am constantly switching between operating systems (I usually develop stuff on a Linux machine at home, and am using a Windows machine at work), and I require the code I write to be operable on both systems without further ado. The Boost libraries offer me this, and on top of it very generic and straight forward structures and patterns for pretty much everything I want to do. David Johnson from Integrated Computer Solutions put it this way in his talk during the Qt Developer Days 2009:

"If you know the STL, think of Boost as being the STL+."

The things I will be covering here are compiled and unit tested by me, but just by me. There might still be bugs or performance issues with some of them, and I would appreciate if you pointed these out in a comment should you find one. I have compiled and tested the code using Boost 1.49.0. If you are using a different version, you usually are fine as long as the libraries used are part of that version (you can check that here), but if there are problems, this might be the reason for it.

For the first post, I will demonstrate the power of Boost('s smart pointers) with a very short snippet. The context: Nowadays, web services are everywhere and SOAP is widely spread. I am using gSOAP for developing clients for web services. SOAP services are usually defined in form of WSDL files. Parts of structures or whole structures sent or received may be defined as optional, and gSOAP will translate optional as a pointer to that structure. A null pointer evaluates to the optional value has not been set. While this seems convenient at first thought, it leads to the troublesome recognition that you will often have to deal with massive pointer structures. You can, of course, allocate memory for your structures with the new operator, but you might to forget to free the allocated memory afterwards, which would cause your application to leak. We can go around this issue using the Boost smart pointers:

#include <boost shared_ptr.hpp>
#include <vector>

struct DemoStruct {
    int *some_int;
    double *some_double;
};

typedef std::vector<boost::shared_ptr<void> > MemoryManager;

template <class T> T* create_managed(T *value, MemoryManager &mm) {
    mm.push_back(boost::shared_ptr<T>(value));
    return value;
}

int main(int, char**) {
    MemoryManager mm;
    DemoStruct *demo_struct = create_managed(new DemoStruct, mm);
    demo_struct->some_int = create_managed(new int(42), mm);
    demo_struct->some_double = create_managed(new double(4.2), mm);
    return 0;
} 

As you can see, we have written a generic function that deals with the memory management using the Boost smart pointer library with just two lines of code.

Next time, we will look at a more elaborated project - I have in mind a file listener service that parses XML files in a certain directory to do something with the information inside. Thanks for reading and stay tuned for more fun with Boost.