SqliteTools.h 12.4 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/*****************************************************************************
 * Media Library
 *****************************************************************************
 * Copyright (C) 2015 Hugo Beauzée-Luyssen, Videolabs
 *
 * Authors: Hugo Beauzée-Luyssen<hugo@beauzee.fr>
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation; either version 2.1 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 Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser 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.
 *****************************************************************************/

23
24
25
#ifndef SQLITETOOLS_H
#define SQLITETOOLS_H

26
#include <cassert>
27
#include <chrono>
28
#include <cstring>
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
29
#include <memory>
30
31
#include <sqlite3.h>
#include <string>
32
#include <vector>
33
#include <thread>
34

35
#include "Types.h"
36
#include "database/SqliteConnection.h"
37
#include "logging/Logger.h"
38

39
40
41
namespace sqlite
{

42
43
struct ForeignKey
{
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
44
    constexpr explicit ForeignKey(unsigned int v) : value(v) {}
45
46
47
    unsigned int value;
};

48
49
50
template <typename ToCheck, typename T>
using IsSameDecay = std::is_same<typename std::decay<ToCheck>::type, T>;

51
52
53
template <typename T, typename Enable = void>
struct Traits;

54
template <typename T>
55
56
57
58
struct Traits<T, typename std::enable_if<
        std::is_integral<typename std::decay<T>::type>::value
        && ! IsSameDecay<T, int64_t>::value
    >::type>
59
60
{
    static constexpr
61
    int (*Bind)(sqlite3_stmt *, int, int) = &sqlite3_bind_int;
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
62
63

    static constexpr
64
    int (*Load)(sqlite3_stmt *, int) = &sqlite3_column_int;
65
66
};

67
68
template <typename T>
struct Traits<T, typename std::enable_if<IsSameDecay<T, ForeignKey>::value>::type>
69
70
71
72
73
74
75
76
77
{
    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 );
    }
};

78
79
template <typename T>
struct Traits<T, typename std::enable_if<IsSameDecay<T, std::string>::value>::type>
80
81
82
{
    static int Bind(sqlite3_stmt* stmt, int pos, const std::string& value )
    {
83
        return sqlite3_bind_text( stmt, pos, value.c_str(), -1, SQLITE_STATIC );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
84
85
    }

86
    static std::string Load( sqlite3_stmt* stmt, int pos )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
87
    {
88
89
90
91
        auto tmp = (const char*)sqlite3_column_text( stmt, pos );
        if ( tmp != nullptr )
            return std::string( tmp );
        return std::string();
92
93
94
    }
};

95
template <typename T>
96
97
98
struct Traits<T, typename std::enable_if<std::is_floating_point<
        typename std::decay<T>::type
    >::value>::type>
99
100
101
102
103
104
105
106
{
        static constexpr int
        (*Bind)(sqlite3_stmt *, int, double) = &sqlite3_bind_double;

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

107
108
109
110
111
112
113
114
115
template <>
struct Traits<std::nullptr_t>
{
    static int Bind(sqlite3_stmt* stmt, int idx, std::nullptr_t)
    {
        return sqlite3_bind_null( stmt, idx );
    }
};

116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
template <typename T>
struct Traits<T, typename std::enable_if<std::is_enum<
        typename std::decay<T>::type
    >::value>::type>
{
    using type_t = typename std::underlying_type<typename std::decay<T>::type>::type;
    static int Bind(sqlite3_stmt* stmt, int pos, T value )
    {
        return sqlite3_bind_int( stmt, pos, static_cast<type_t>( value ) );
    }

    static T Load( sqlite3_stmt* stmt, int pos )
    {
        return static_cast<T>( sqlite3_column_int( stmt, pos ) );
    }
};


134
135
136
137
138
139
140
141
142
143
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;
};

144
145
namespace errors
{
146
class ConstraintViolation : public std::exception
147
148
{
public:
149
    ConstraintViolation( const std::string& req, const std::string& err )
150
151
    {
        m_reason = std::string( "Request <" ) + req + "> aborted due to "
152
                "constraint violation (" + err + ")";
153
154
155
156
157
158
159
160
161
    }

    virtual const char* what() const noexcept override
    {
        return m_reason.c_str();
    }
private:
    std::string m_reason;
};
162

163
class ColumnOutOfRange : public std::exception
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
{
public:
    ColumnOutOfRange( unsigned int idx, unsigned int nbColumns )
    {
        m_reason = "Attempting to extract column at index " + std::to_string( idx ) +
                " from a request with " + std::to_string( nbColumns ) + "columns";
    }

    virtual const char* what() const noexcept override
    {
        return m_reason.c_str();
    }

private:
    std::string m_reason;
};
180
181
}

182
183
184
185
186
187
class Row
{
public:
    Row( sqlite3_stmt* stmt )
        : m_stmt( stmt )
        , m_idx( 0 )
188
        , m_nbColumns( sqlite3_column_count( stmt ) )
189
190
191
    {
    }

192
193
194
195
196
197
    Row()
        : m_stmt( nullptr )
        , m_idx( 0 )
    {
    }

198
199
200
201
202
203
    /**
     * @brief operator >> Extracts the next column from this result row.
     */
    template <typename T>
    Row& operator>>(T& t)
    {
204
205
        if ( m_idx + 1 > m_nbColumns )
            throw errors::ColumnOutOfRange( m_idx, m_nbColumns );
206
207
208
209
210
211
212
213
214
215
216
        t = sqlite::Traits<T>::Load( m_stmt, m_idx );
        m_idx++;
        return *this;
    }

    /**
     * @brief Returns the value in column idx, but doesn't advance to the next column
     */
    template <typename T>
    T load(unsigned int idx)
    {
217
218
        if ( m_idx + 1 > m_nbColumns )
            throw errors::ColumnOutOfRange( m_idx, m_nbColumns );
219
220
221
        return sqlite::Traits<T>::Load( m_stmt, idx );
    }

222
223
224
225
226
227
228
229
230
231
    bool operator==(std::nullptr_t)
    {
        return m_stmt == nullptr;
    }

    bool operator!=(std::nullptr_t)
    {
        return m_stmt != nullptr;
    }

232
233
234
private:
    sqlite3_stmt* m_stmt;
    unsigned int m_idx;
235
    unsigned int m_nbColumns;
236
237
};

238
239
240
241
242
class Statement
{
public:
    Statement( DBConnection dbConnection, const std::string& req )
        : m_stmt( nullptr, &sqlite3_finalize )
243
        , m_dbConn( dbConnection )
244
        , m_req( req )
245
        , m_bindIdx( 0 )
246
247
248
249
250
251
252
253
254
255
256
257
258
259
    {
        sqlite3_stmt* stmt;
        int res = sqlite3_prepare_v2( dbConnection->getConn(), req.c_str(), -1, &stmt, NULL );
        if ( res != SQLITE_OK )
        {
            throw std::runtime_error( std::string( "Failed to execute request: " ) + req + " " +
                        sqlite3_errmsg( dbConnection->getConn() ) );
        }
        m_stmt.reset( stmt );
    }

    template <typename... Args>
    void execute(Args&&... args)
    {
260
261
        m_bindIdx = 1;
        (void)std::initializer_list<bool>{ _bind( std::forward<Args>( args ) )... };
262
263
264
265
266
    }

    Row row()
    {
        auto res = sqlite3_step( m_stmt.get() );
267
        if ( res == SQLITE_ROW )
268
            return Row( m_stmt.get() );
269
        else if ( res == SQLITE_DONE )
270
            return Row();
271
        else
272
        {
273
274
275
276
277
278
            std::string errMsg = sqlite3_errmsg( m_dbConn->getConn() );
            switch ( res )
            {
                case SQLITE_CONSTRAINT:
                    throw errors::ConstraintViolation( m_req, errMsg );
                default:
279
                    throw std::runtime_error( errMsg );
280
            }
281
        }
282
283
284
    }

private:
285
286
    template <typename T>
    bool _bind( T&& value )
287
    {
288
        auto res = Traits<T>::Bind( m_stmt.get(), m_bindIdx, std::forward<T>( value ) );
289
290
        if ( res != SQLITE_OK )
            throw std::runtime_error( "Failed to bind parameter" );
291
292
        m_bindIdx++;
        return true;
293
294
295
296
    }

private:
    std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)> m_stmt;
297
    DBConnection m_dbConn;
298
    std::string m_req;
299
    unsigned int m_bindIdx;
300
301
};

302
class Tools
303
304
{
    public:
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
305
306
307
308
309
310
311
        /**
         * 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.
         */
312
        template <typename IMPL, typename INTF, typename... Args>
313
        static std::vector<std::shared_ptr<INTF> > fetchAll( DBConnection dbConnection, const std::string& req, Args&&... args )
314
        {
315
            auto ctx = dbConnection->acquireContext();
316
            auto chrono = std::chrono::steady_clock::now();
317

318
            std::vector<std::shared_ptr<INTF>> results;
319
320
321
322
            auto stmt = Statement( dbConnection, req );
            stmt.execute( std::forward<Args>( args )... );
            Row sqliteRow;
            while ( ( sqliteRow = stmt.row() ) != nullptr )
323
            {
324
                auto row = IMPL::load( dbConnection, sqliteRow );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
325
                results.push_back( row );
326
            }
327
328
329
            auto duration = std::chrono::steady_clock::now() - chrono;
            LOG_DEBUG("Executed ", req, " in ",
                     std::chrono::duration_cast<std::chrono::microseconds>( duration ).count(), "µs" );
330
            return results;
331
332
        }

333
        template <typename T, typename... Args>
334
        static std::shared_ptr<T> fetchOne( DBConnection dbConnection, const std::string& req, Args&&... args )
335
        {
336
            auto ctx = dbConnection->acquireContext();
337
            auto chrono = std::chrono::steady_clock::now();
338

339
340
341
342
            auto stmt = Statement( dbConnection, req );
            stmt.execute( std::forward<Args>( args )... );
            auto row = stmt.row();
            if ( row == nullptr )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
343
                return nullptr;
344
345
346
347
348
            auto res = T::load( dbConnection, row );
            auto duration = std::chrono::steady_clock::now() - chrono;
            LOG_DEBUG("Executed ", req, " in ",
                     std::chrono::duration_cast<std::chrono::microseconds>( duration ).count(), "µs" );
            return res;
349
350
        }

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
351
        template <typename... Args>
352
        static bool executeRequest( DBConnection dbConnection, const std::string& req, Args&&... args )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
353
        {
354
355
            auto ctx = dbConnection->acquireContext();
            return executeRequestLocked( dbConnection, req, std::forward<Args>( args )... );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
356
357
        }

358
        template <typename... Args>
359
        static bool executeDelete( DBConnection dbConnection, const std::string& req, Args&&... args )
360
        {
361
362
            auto ctx = dbConnection->acquireContext();
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
363
364
                return false;
            return sqlite3_changes( dbConnection->getConn() ) > 0;
365
366
        }

367
        template <typename... Args>
368
        static bool executeUpdate( DBConnection dbConnectionWeak, const std::string& req, Args&&... args )
369
        {
370
371
            // The code would be exactly the same, do not freak out because it calls executeDelete :)
            return executeDelete( dbConnectionWeak, req, std::forward<Args>( args )... );
372
373
        }

374
375
376
377
378
        /**
         * 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>
379
        static unsigned int insert( DBConnection dbConnection, const std::string& req, Args&&... args )
380
        {
381
382
            auto ctx = dbConnection->acquireContext();
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
383
                return 0;
384
            return sqlite3_last_insert_rowid( dbConnection->getConn() );
385
386
        }

387
    private:
388
389
390
        template <typename... Args>
        static bool executeRequestLocked( DBConnection dbConnection, const std::string& req, Args&&... args )
        {
391
            auto chrono = std::chrono::steady_clock::now();
392
393
394
395
            auto stmt = Statement( dbConnection, req );
            stmt.execute( std::forward<Args>( args )... );
            while ( stmt.row() != nullptr )
                ;
396
397
398
            auto duration = std::chrono::steady_clock::now() - chrono;
            LOG_DEBUG("Executed ", req, " in ",
                     std::chrono::duration_cast<std::chrono::microseconds>( duration ).count(), "µs" );
399
400
401
            return true;
        }

402
        // Let SqliteConnection access executeRequestLocked
403
        friend SqliteConnection;
404
405
};

406
407
}

408
#endif // SQLITETOOLS_H