SqliteTools.h 9.37 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

34
#include "Types.h"
35
#include "database/SqliteConnection.h"
36
#include "database/SqliteErrors.h"
37
#include "database/SqliteTraits.h"
38
#include "database/SqliteTransaction.h"
39
#include "logging/Logger.h"
40

41
42
43
namespace sqlite
{

44
45
46
47
48
49
class Row
{
public:
    Row( sqlite3_stmt* stmt )
        : m_stmt( stmt )
        , m_idx( 0 )
50
        , m_nbColumns( sqlite3_column_count( stmt ) )
51
52
53
    {
    }

54
55
56
57
58
59
    Row()
        : m_stmt( nullptr )
        , m_idx( 0 )
    {
    }

60
61
62
63
64
65
    /**
     * @brief operator >> Extracts the next column from this result row.
     */
    template <typename T>
    Row& operator>>(T& t)
    {
66
67
        if ( m_idx + 1 > m_nbColumns )
            throw errors::ColumnOutOfRange( m_idx, m_nbColumns );
68
69
70
71
72
73
74
75
76
        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>
77
    T load(unsigned int idx) const
78
    {
79
80
        if ( m_idx + 1 > m_nbColumns )
            throw errors::ColumnOutOfRange( m_idx, m_nbColumns );
81
82
83
        return sqlite::Traits<T>::Load( m_stmt, idx );
    }

84
85
86
87
88
89
90
91
92
93
    bool operator==(std::nullptr_t)
    {
        return m_stmt == nullptr;
    }

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

94
95
96
private:
    sqlite3_stmt* m_stmt;
    unsigned int m_idx;
97
    unsigned int m_nbColumns;
98
99
};

100
101
102
103
104
class Statement
{
public:
    Statement( DBConnection dbConnection, const std::string& req )
        : m_stmt( nullptr, &sqlite3_finalize )
105
        , m_dbConn( dbConnection )
106
        , m_req( req )
107
        , m_bindIdx( 0 )
108
109
110
111
112
113
114
115
116
117
118
119
120
121
    {
        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)
    {
122
123
        m_bindIdx = 1;
        (void)std::initializer_list<bool>{ _bind( std::forward<Args>( args ) )... };
124
125
126
127
128
    }

    Row row()
    {
        auto res = sqlite3_step( m_stmt.get() );
129
        if ( res == SQLITE_ROW )
130
            return Row( m_stmt.get() );
131
        else if ( res == SQLITE_DONE )
132
            return Row();
133
        else
134
        {
135
136
137
138
139
140
            std::string errMsg = sqlite3_errmsg( m_dbConn->getConn() );
            switch ( res )
            {
                case SQLITE_CONSTRAINT:
                    throw errors::ConstraintViolation( m_req, errMsg );
                default:
141
                    throw std::runtime_error( m_req + ": " + errMsg );
142
            }
143
        }
144
145
146
    }

private:
147
148
    template <typename T>
    bool _bind( T&& value )
149
    {
150
        auto res = Traits<T>::Bind( m_stmt.get(), m_bindIdx, std::forward<T>( value ) );
151
152
        if ( res != SQLITE_OK )
            throw std::runtime_error( "Failed to bind parameter" );
153
154
        m_bindIdx++;
        return true;
155
156
157
158
    }

private:
    std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)> m_stmt;
159
    DBConnection m_dbConn;
160
    std::string m_req;
161
    unsigned int m_bindIdx;
162
163
};

164
class Tools
165
166
{
    public:
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
167
168
169
170
171
172
173
        /**
         * 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.
         */
174
        template <typename IMPL, typename INTF, typename... Args>
175
        static std::vector<std::shared_ptr<INTF> > fetchAll( DBConnection dbConnection, const std::string& req, Args&&... args )
176
        {
177
            assert(Transaction::transactionInProgress() == false);
178
            auto ctx = dbConnection->acquireContext();
179
            auto chrono = std::chrono::steady_clock::now();
180

181
            std::vector<std::shared_ptr<INTF>> results;
182
183
184
185
            auto stmt = Statement( dbConnection, req );
            stmt.execute( std::forward<Args>( args )... );
            Row sqliteRow;
            while ( ( sqliteRow = stmt.row() ) != nullptr )
186
            {
187
                auto row = IMPL::load( dbConnection, sqliteRow );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
188
                results.push_back( row );
189
            }
190
191
192
            auto duration = std::chrono::steady_clock::now() - chrono;
            LOG_DEBUG("Executed ", req, " in ",
                     std::chrono::duration_cast<std::chrono::microseconds>( duration ).count(), "µs" );
193
            return results;
194
195
        }

196
        template <typename T, typename... Args>
197
        static std::shared_ptr<T> fetchOne( DBConnection dbConnection, const std::string& req, Args&&... args )
198
        {
199
            assert(Transaction::transactionInProgress() == false);
200
            auto ctx = dbConnection->acquireContext();
201
            auto chrono = std::chrono::steady_clock::now();
202

203
204
205
206
            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
207
                return nullptr;
208
209
210
211
212
            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;
213
214
        }

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
215
        template <typename... Args>
216
        static bool executeRequest( DBConnection dbConnection, const std::string& req, Args&&... args )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
217
        {
218
219
220
            SqliteConnection::RequestContext ctx;
            if (Transaction::transactionInProgress() == false)
                ctx = dbConnection->acquireContext();
221
            return executeRequestLocked( dbConnection, req, std::forward<Args>( args )... );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
222
223
        }

224
        template <typename... Args>
225
        static bool executeDelete( DBConnection dbConnection, const std::string& req, Args&&... args )
226
        {
227
228
229
            SqliteConnection::RequestContext ctx;
            if (Transaction::transactionInProgress() == false)
                ctx = dbConnection->acquireContext();
230
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
231
232
                return false;
            return sqlite3_changes( dbConnection->getConn() ) > 0;
233
234
        }

235
        template <typename... Args>
236
        static bool executeUpdate( DBConnection dbConnectionWeak, const std::string& req, Args&&... args )
237
        {
238
239
            // The code would be exactly the same, do not freak out because it calls executeDelete :)
            return executeDelete( dbConnectionWeak, req, std::forward<Args>( args )... );
240
241
        }

242
243
244
245
246
        /**
         * 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>
247
        static unsigned int insert( DBConnection dbConnection, const std::string& req, Args&&... args )
248
        {
249
250
251
            SqliteConnection::RequestContext ctx;
            if (Transaction::transactionInProgress() == false)
                ctx = dbConnection->acquireContext();
252
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
253
                return 0;
254
            return sqlite3_last_insert_rowid( dbConnection->getConn() );
255
256
        }

257
    private:
258
259
260
        template <typename... Args>
        static bool executeRequestLocked( DBConnection dbConnection, const std::string& req, Args&&... args )
        {
261
            auto chrono = std::chrono::steady_clock::now();
262
263
264
265
            auto stmt = Statement( dbConnection, req );
            stmt.execute( std::forward<Args>( args )... );
            while ( stmt.row() != nullptr )
                ;
266
267
268
            auto duration = std::chrono::steady_clock::now() - chrono;
            LOG_DEBUG("Executed ", req, " in ",
                     std::chrono::duration_cast<std::chrono::microseconds>( duration ).count(), "µs" );
269
270
271
            return true;
        }

272
        // Let SqliteConnection access executeRequestLocked
273
        friend SqliteConnection;
274
275
};

276
277
}

278
#endif // SQLITETOOLS_H