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

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

13
#include "Types.h"
14
#include "database/SqliteConnection.h"
15
#include "logging/Logger.h"
16

17
18
19
namespace sqlite
{

20
21
22
23
24
25
struct ForeignKey
{
    constexpr ForeignKey(unsigned int v) : value(v) {}
    unsigned int value;
};

26
27
28
template <typename ToCheck, typename T>
using IsSameDecay = std::is_same<typename std::decay<ToCheck>::type, T>;

29
30
31
template <typename T, typename Enable = void>
struct Traits;

32
template <typename T>
33
34
35
36
struct Traits<T, typename std::enable_if<
        std::is_integral<typename std::decay<T>::type>::value
        && ! IsSameDecay<T, int64_t>::value
    >::type>
37
38
{
    static constexpr
39
    int (*Bind)(sqlite3_stmt *, int, int) = &sqlite3_bind_int;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
40
41

    static constexpr
42
    int (*Load)(sqlite3_stmt *, int) = &sqlite3_column_int;
43
44
};

45
46
template <typename T>
struct Traits<T, typename std::enable_if<IsSameDecay<T, ForeignKey>::value>::type>
47
48
49
50
51
52
53
54
55
{
    static int Bind( sqlite3_stmt *stmt, int pos, ForeignKey fk)
    {
        if ( fk.value != 0 )
            return Traits<unsigned int>::Bind( stmt, pos, fk.value );
        return sqlite3_bind_null( stmt, pos );
    }
};

56
57
template <typename T>
struct Traits<T, typename std::enable_if<IsSameDecay<T, std::string>::value>::type>
58
59
60
{
    static int Bind(sqlite3_stmt* stmt, int pos, const std::string& value )
    {
61
        return sqlite3_bind_text( stmt, pos, value.c_str(), -1, SQLITE_STATIC );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
62
63
    }

64
    static std::string Load( sqlite3_stmt* stmt, int pos )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
65
    {
66
67
68
69
        auto tmp = (const char*)sqlite3_column_text( stmt, pos );
        if ( tmp != nullptr )
            return std::string( tmp );
        return std::string();
70
71
72
    }
};

73
template <typename T>
74
75
76
struct Traits<T, typename std::enable_if<std::is_floating_point<
        typename std::decay<T>::type
    >::value>::type>
77
78
79
80
81
82
83
84
{
        static constexpr int
        (*Bind)(sqlite3_stmt *, int, double) = &sqlite3_bind_double;

        static constexpr double
        (*Load)(sqlite3_stmt *, int) = &sqlite3_column_double;
};

85
86
87
88
89
90
91
92
93
template <>
struct Traits<std::nullptr_t>
{
    static int Bind(sqlite3_stmt* stmt, int idx, std::nullptr_t)
    {
        return sqlite3_bind_null( stmt, idx );
    }
};

94
95
96
97
98
99
100
101
102
103
template <typename T>
struct Traits<T, typename std::enable_if<IsSameDecay<T, int64_t>::value>::type>
{
    static constexpr int
    (*Bind)(sqlite3_stmt *, int, sqlite_int64) = &sqlite3_bind_int64;

    static constexpr sqlite_int64
    (*Load)(sqlite3_stmt *, int) = &sqlite3_column_int64;
};

104
class Tools
105
{
106
107
    private:
        typedef std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)> StmtPtr;
108
    public:
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
109
110
111
112
113
114
115
        /**
         * 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.
         */
116
        template <typename IMPL, typename INTF, typename... Args>
117
        static std::vector<std::shared_ptr<INTF> > fetchAll( DBConnection dbConnection, const std::string& req, Args&&... args )
118
        {
119
120
            auto ctx = dbConnection->acquireContext();

121
            std::vector<std::shared_ptr<INTF>> results;
122
            auto stmt = prepareRequest( dbConnection, req, std::forward<Args>( args )...);
123
            if ( stmt == nullptr )
124
                throw std::runtime_error("Failed to execute SQlite request");
125
            int res = sqlite3_step( stmt.get() );
126
127
            while ( res == SQLITE_ROW )
            {
128
                auto row = IMPL::load( dbConnection, stmt.get() );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
129
                results.push_back( row );
130
                res = sqlite3_step( stmt.get() );
131
            }
132
            return results;
133
134
        }

135
        template <typename T, typename... Args>
136
        static std::shared_ptr<T> fetchOne( DBConnection dbConnection, const std::string& req, Args&&... args )
137
        {
138
139
            auto ctx = dbConnection->acquireContext();

140
            auto stmt = prepareRequest( dbConnection, req, std::forward<Args>( args )... );
141
            if ( stmt == nullptr )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
142
                return nullptr;
143
144
145
146
            int res = sqlite3_step( stmt.get() );
            if ( res != SQLITE_ROW )
                return nullptr;
            return T::load( dbConnection, stmt.get() );
147
148
        }

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
149
        template <typename... Args>
150
        static bool executeRequest( DBConnection dbConnection, const std::string& req, Args&&... args )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
151
        {
152
153
            auto ctx = dbConnection->acquireContext();
            return executeRequestLocked( dbConnection, req, std::forward<Args>( args )... );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
154
155
        }

156
        template <typename... Args>
157
        static bool executeDelete( DBConnection dbConnection, const std::string& req, Args&&... args )
158
        {
159
160
            auto ctx = dbConnection->acquireContext();
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
161
162
                return false;
            return sqlite3_changes( dbConnection->getConn() ) > 0;
163
164
        }

165
        template <typename... Args>
166
        static bool executeUpdate( DBConnection dbConnectionWeak, const std::string& req, Args&&... args )
167
        {
168
169
            // The code would be exactly the same, do not freak out because it calls executeDelete :)
            return executeDelete( dbConnectionWeak, req, std::forward<Args>( args )... );
170
171
        }

172
173
174
175
176
        /**
         * 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>
177
        static unsigned int insert( DBConnection dbConnection, const std::string& req, Args&&... args )
178
        {
179
180
            auto ctx = dbConnection->acquireContext();
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
181
                return 0;
182
            return sqlite3_last_insert_rowid( dbConnection->getConn() );
183
184
        }

185
    private:
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
        template <typename... Args>
        static bool executeRequestLocked( DBConnection dbConnection, const std::string& req, Args&&... args )
        {
            auto stmt = prepareRequest( dbConnection, req, std::forward<Args>( args )... );
            if ( stmt == nullptr )
                return false;
            int res;
            do
            {
                res = sqlite3_step( stmt.get() );
            } while ( res == SQLITE_ROW );
            if ( res != SQLITE_DONE )
            {
#if SQLITE_VERSION_NUMBER >= 3007015
                auto err = sqlite3_errstr( res );
#else
                auto err = res;
#endif
                LOG_ERROR( "Failed to execute <", req, ">\nInvalid result: ", err,
                          ": ", sqlite3_errmsg( dbConnection->getConn() ) );
                return false;
            }
            return true;
        }

211
        template <typename... Args>
212
        static StmtPtr prepareRequest( DBConnection dbConnection, const std::string& req, Args&&... args )
213
        {
214
            return _prepareRequest<1>( dbConnection, req, std::forward<Args>( args )... );
215
216
        }

217
        template <unsigned int>
218
        static StmtPtr _prepareRequest( DBConnection dbConnection, const std::string& req )
219
        {
220
            sqlite3_stmt* stmt = nullptr;
221
            int res = sqlite3_prepare_v2( dbConnection->getConn(), req.c_str(), -1, &stmt, NULL );
222
            if ( res != SQLITE_OK )
223
            {
224
225
                LOG_ERROR( "Failed to execute request: ", req, ' ',
                            sqlite3_errmsg( dbConnection->getConn() ) );
226
            }
227
            return StmtPtr( stmt, &sqlite3_finalize );
228
229
        }

230
        template <unsigned int COLIDX, typename T, typename... Args>
231
        static StmtPtr _prepareRequest( DBConnection dbConnection, const std::string& req, T&& arg, Args&&... args )
232
        {
233
            auto stmt = _prepareRequest<COLIDX + 1>( dbConnection, req, std::forward<Args>( args )... );
234
            Traits<T>::Bind( stmt.get(), COLIDX, std::forward<T>( arg ) );
235
236
            return stmt;
        }
237
238

        friend SqliteConnection;
239
240
};

241
242
}

243
#endif // SQLITETOOLS_H