SqliteTools.h 5.11 KB
Newer Older
1 2 3
#ifndef SQLITETOOLS_H
#define SQLITETOOLS_H

4
#include <cstring>
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
5
#include <memory>
6 7
#include <sqlite3.h>
#include <string>
8
#include <vector>
9
#include <iostream>
10

11 12 13 14 15 16 17 18
// Have a base case for integral type only
// Use specialization to define other cases, and fail for the rest.
template <typename T>
struct Traits
{
    static constexpr
    typename std::enable_if<std::is_integral<T>::value, int>::type
    (*Bind)(sqlite3_stmt *, int, int) = &sqlite3_bind_int;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
19 20 21 22

    static constexpr
    typename std::enable_if<std::is_integral<T>::value, int>::type
    (*Load)(sqlite3_stmt *, int) = &sqlite3_column_int;
23 24 25 26 27 28 29 30
};

template <>
struct Traits<std::string>
{
    static int Bind(sqlite3_stmt* stmt, int pos, const std::string& value )
    {
        char* tmp = strdup( value.c_str() );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
31 32 33 34 35 36
        return sqlite3_bind_text( stmt, pos, tmp, -1, free );
    }

    static const char* Load( sqlite3_stmt* stmt, int pos )
    {
        return (const char*)sqlite3_column_text( stmt, pos );
37 38 39
    }
};

40 41
class SqliteTools
{
42 43
    private:
        typedef std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)> StmtPtr;
44
    public:
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
45 46 47 48 49 50 51
        /**
         * Will fetch all records of type IMPL and return them as a shared_ptr to INTF
         * This WILL add all fetched records to the cache
         *
         * @param results   A reference to the result vector. All existing elements will
         *                  be discarded.
         */
52 53
        template <typename IMPL, typename INTF, typename... Args>
        static bool fetchAll( sqlite3* dbConnection, const char* req, std::vector<std::shared_ptr<INTF> >& results, const Args&... args )
54
        {
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
55
            results.clear();
56
            auto stmt = prepareRequest( dbConnection, req, args...);
57
            if ( stmt == nullptr )
58
                return false;
59
            int res = sqlite3_step( stmt.get() );
60 61
            while ( res == SQLITE_ROW )
            {
62
                auto row = IMPL::load( dbConnection, stmt.get() );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
63
                results.push_back( row );
64
                res = sqlite3_step( stmt.get() );
65 66 67 68
            }
            return true;
        }

69 70 71 72
        template <typename T, typename... Args>
        static std::shared_ptr<T> fetchOne( sqlite3* dbConnection, const char* req, const Args&... args )
        {
            std::shared_ptr<T> result;
73
            auto stmt = prepareRequest( dbConnection, req, args... );
74
            if ( stmt == nullptr )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
75
                return nullptr;
76
            if ( sqlite3_step( stmt.get() ) != SQLITE_ROW )
77
                return result;
78
            result = T::load( dbConnection, stmt.get() );
79 80 81
            return result;
        }

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
82
        template <typename... Args>
83
        static bool executeDelete( sqlite3* dbConnection, const char* req, const Args&... args )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
84
        {
85
            if ( executeRequest( dbConnection, req, args... ) == false )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
86 87 88 89
                return false;
            return sqlite3_changes( dbConnection ) > 0;
        }

90 91 92 93
        /**
         * This is meant to be used for "write only" requests, as the statement is not
         * exposed to the caller.
         */
94
        template <typename... Args>
95
        static bool executeRequest( sqlite3* dbConnection, const char* req, const Args&... args )
96
        {
97 98 99 100 101 102 103
            auto stmt = prepareRequest( dbConnection, req, args... );
            if ( stmt == nullptr )
                return false;
            int res;
            do
            {
                res = sqlite3_step( stmt.get() );
104
            } while ( res == SQLITE_ROW );
105
            return res == SQLITE_DONE;
106 107
        }

108 109 110 111 112 113 114 115 116 117 118 119
        /**
         * Inserts a record to the DB and return the newly created primary key.
         * Returns 0 (which is an invalid sqlite primary key) when insertion fails.
         */
        template <typename... Args>
        static unsigned int insert( sqlite3* dbConnection, const char* req, const Args&... args )
        {
            if ( executeRequest( dbConnection, req, args... ) == false )
                return 0;
            return sqlite3_last_insert_rowid( dbConnection );
        }

120
    private:
121 122 123 124 125 126
        template <typename... Args>
        static StmtPtr prepareRequest( sqlite3* dbConnection, const char* req, const Args&... args )
        {
            return _prepareRequest<1>( dbConnection, req, args... );
        }

127
        template <unsigned int>
128
        static StmtPtr _prepareRequest( sqlite3* dbConnection, const char* req )
129
        {
130
            sqlite3_stmt* stmt = nullptr;
131 132
            int res = sqlite3_prepare_v2( dbConnection, req, -1, &stmt, NULL );
            if ( res != SQLITE_OK )
133 134 135 136
            {
                std::cerr << "Failed to execute request: " << req << std::endl;
                std::cerr << sqlite3_errmsg( dbConnection ) << std::endl;
            }
137
            return StmtPtr( stmt, &sqlite3_finalize );
138 139
        }

140
        template <unsigned int COLIDX, typename T, typename... Args>
141
        static StmtPtr _prepareRequest( sqlite3* dbConnection, const char* req, const T& arg, const Args&... args )
142
        {
143
            auto stmt = _prepareRequest<COLIDX + 1>( dbConnection, req, args... );
144
            Traits<T>::Bind( stmt.get(), COLIDX, arg );
145 146
            return stmt;
        }
147 148 149
};

#endif // SQLITETOOLS_H