sqlitepp.h

Go to the documentation of this file.

// -*- C++ -*-
/*
  Phrasehunter - index and query text corpora
  Copyright (C) 2006  Torsten Marek (shlomme@gmx.de) &
  Armin Schmidt (armin.sch@gmail.com)
  
  This program is free software; you can redistribute it and/or
  modify it under the terms of the GNU General Public License
  as published by the Free Software Foundation; either version 2
  of the License, or (at your option) any later version.
  
  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.
  
  You should have received a copy of the GNU General Public License
  along with this program; if not, write to the Free Software
  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/

#ifndef SQLITEPP_H
#define SQLITEPP_H SQLITEPP_H

#include <iostream>
#include <string>
#include <cassert>
#include <sqlite3.h>
#include <boost/preprocessor/arithmetic/inc.hpp>
#include <boost/preprocessor/punctuation/comma_if.hpp>
#include <boost/preprocessor/repetition.hpp>
#include <boost/static_assert.hpp>

#include <tr1/unordered_map>
#include <boost/shared_ptr.hpp>

#include "support/debug.h"
#include "support/unicodehelpers.h"



namespace SQLitePP {

typedef std::pair<const void*, size_t> Blob;

void showSqlite3Error(const char* infunction, sqlite3* db);
sqlite3_stmt* prepareStatement(const char *sqlcode, sqlite3* db);

/**
   \brief Class for iterating over the result of a database query.
*/
class ResultIterator
{
private:
    sqlite3_stmt* m_stmt;
    int state;
    
public:
    /**
       \brief Constructor.
       \param stmt The Statement containing the sql query.
    */
    ResultIterator(sqlite3_stmt* stmt): m_stmt(stmt), state(0)
    {
        next();
    }
    
    ~ResultIterator() 
    {
        sqlite3_reset(m_stmt);
    }
    //! Returns true if there are more results. False otherwise.
    bool hasMoreRows()
    {
        return state == SQLITE_ROW;
    }
    //! Steps to the result.
    void next() {
        state = sqlite3_step(m_stmt);
        p_assert(state == SQLITE_ROW || state == SQLITE_DONE, "error in statement");
    }
    /**
       \brief Get the current result.
       template parameter T needs to be of one of the following types: int, unsigned,
       const char*, Blob, std::string, PhraseHunter::UnicodePtr.
       \param column The column in the result table.
    */
    template<typename T>
    T get(int column)
    {
        BOOST_STATIC_ASSERT(sizeof(T) == 0);
    }
};

template<>
int ResultIterator::get<int>(int column);

template<>
unsigned int ResultIterator::get<unsigned int>(int column);

template<>
const char* ResultIterator::get<const char*>(int column);

template<>
Blob ResultIterator::get<Blob>(int column) ;

template<>
std::string ResultIterator::get<std::string>(int column);

template<>
schma::UnicodePtr ResultIterator::get<schma::UnicodePtr>(int column);


class SqliteDB;

//! Class that contains a statement for querying the sqlite db.
class Statement
{
    sqlite3_stmt* m_stmt;
    sqlite3* m_dbhandle;
    int m_argpos;
    bool m_finalize;

    friend class SqliteDB;
    
    inline Statement(const char* sql, sqlite3* db)
        : m_stmt(prepareStatement(sql, db)), m_dbhandle(db), 
          m_argpos(0), m_finalize(true)
    { }
    
    inline Statement(sqlite3_stmt* stmt, sqlite3* db)
        : m_stmt(stmt), m_dbhandle(db), 
          m_argpos(0), m_finalize(false)
    { }

public:
    typedef boost::shared_ptr<Statement> Pointer;
    
    
    // sorry, i'm retarded...
#ifndef MAX_ARGUMENTS
#define MAX_ARGUMENTS 9
#endif

#define BINDARG(Z, N, _)                        \
    bindArg(BOOST_PP_CAT(a, N));
    
#define BINDARGS(Z, N, _)                                               \
    template<BOOST_PP_ENUM_PARAMS(BOOST_PP_INC(N), typename T)>         \
    inline Statement& bindArgs(BOOST_PP_ENUM_BINARY_PARAMS(BOOST_PP_INC(N), T, a)) \
    {                                                                   \
        BOOST_PP_REPEAT(BOOST_PP_INC(N), BINDARG, _)                    \
            return *this;                                               \
    }

    BOOST_PP_REPEAT(BOOST_PP_INC(MAX_ARGUMENTS), BINDARGS, _)

#undef BINDARGS
#undef BINDARG

        ~Statement()
    {
        if(m_finalize) {
            sqlite3_finalize(m_stmt);
        } else {
            reset();
        }
    }
    
    //! Bind an argument to a Statement.
    inline Statement& bindArg(const Blob& argument, int pos = 0)
    {
        if(pos == 0) {
            pos = ++m_argpos;
        }
        sqlite3_bind_blob(m_stmt, pos, argument.first, argument.second, SQLITE_STATIC);
        return *this;
    }
    //! Bind an argument to a Statement.
    inline Statement& bindArg(const std::string& argument, int pos = 0)
    {
        if(pos == 0) {
            pos = ++m_argpos;
        }
        sqlite3_bind_text(m_stmt, pos, argument.c_str(), argument.length(), SQLITE_TRANSIENT);
        return *this;
    }
    //! Bind an argument to a Statement.
    inline Statement& bindArg(const char* argument, int pos = 0)
    {
        if(pos == 0) {
            pos = ++m_argpos;
        }
        sqlite3_bind_text(m_stmt, pos, argument, -1, SQLITE_TRANSIENT);
        return *this;
    }
    //! Bind an argument to a Statement.
    inline Statement& bindArg(int argument, unsigned int pos = 0)
    {
        if(pos == 0) {
            pos = ++m_argpos;
        }
        sqlite3_bind_int(m_stmt, pos, argument);
        return *this;
    }
    //! Bind an argument to a Statement.
    inline Statement& bindArg(schma::UnicodePtr argument, int pos = 0)
    {
        if(pos == 0) {
            pos = ++m_argpos;
        }
        sqlite3_bind_text(m_stmt, pos, schma::toCharArray(argument).get(), argument->length(), SQLITE_TRANSIENT);
        return *this;
    }
    inline Statement& exec()
    {
        int rcode = sqlite3_step(m_stmt);
        if(rcode != SQLITE_DONE) {
            showSqlite3Error(__PRETTY_FUNCTION__, m_dbhandle);
        }
        reset();
        return *this;
    }
    
    //! Returns a ResultIterator to be used to iterate over the result of the db query.
    inline ResultIterator query() 
    {
        m_argpos = 0;
        return ResultIterator(m_stmt);
    }
    
    template<typename T>
    inline T queryScalar()
    {
        ResultIterator ri = query();
      
        p_assert(ri.hasMoreRows(), "error");

        return ri.get<T>(0);
    }

    //! Reset this Statement. Use this before binding other arguments to this Statement.
    inline void reset()
    {
        m_argpos = 0;
        sqlite3_reset(m_stmt);
    }

    inline int lastInsertId() 
    {
        return sqlite3_last_insert_rowid(m_dbhandle);
    }

    inline int affectedRows() 
    {
        return sqlite3_changes(m_dbhandle);
    }
};

//! Connect to and query sqlite database
class SqliteDB
{
private:
    SqliteDB(const SqliteDB& db);
    SqliteDB& operator=(const SqliteDB& db);

public:
    /**
       \brief Constructor.
       \param filename The path to the sqlite3 database
    */
    SqliteDB(const std::string& filename);

    /**
       \brief Use this factory function to initialze a Statement.
       \param sql A string containing a valid, well-formed sql query.
    */
    Statement::Pointer statement(const char* sql) 
    {
        return Statement::Pointer(new Statement(sql, m_dbhandle));
    }

    /**
       \brief Use this factory function to initialze a cached Statement.
       \param sql A string containing a valid, well-formed sql query.
    */
    Statement::Pointer cachedStatement(const char* sql)
    {
        CacheMap::iterator it = m_cached.find(sql);
        if(it != m_cached.end()) {
            return Statement::Pointer(new Statement(it->second, m_dbhandle));
        } else {
            sqlite3_stmt* stmt =
                prepareStatement(sql, m_dbhandle);
            m_cached[sql] = stmt;
            return Statement::Pointer(new Statement(stmt, m_dbhandle));
        }
    }
    
    void begin()
    {
        cachedStatement("BEGIN")->exec();
    }
    
    void commit() 
    {
        cachedStatement("COMMIT")->exec();
    }
    
    void rollback() 
    {
        cachedStatement("ROLLBACK")->exec();
    }
    
    ~SqliteDB()
    {
        sqlite3_close(m_dbhandle);
        for(CacheMap::iterator it = m_cached.begin();
            it != m_cached.end();
            ++it) {
            sqlite3_finalize(it->second);
        }
    }
    
private:
    sqlite3* m_dbhandle;
    
    typedef std::tr1::unordered_map<const char*, sqlite3_stmt*> CacheMap;
    CacheMap m_cached;
    
};


}

#endif //SQLITEPP_H

---