SqliteTools.h 11.1 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 <unordered_map>
33
#include <vector>
34

35
#include <compat/Mutex.h>
36
#include "database/SqliteConnection.h"
37
#include "database/SqliteErrors.h"
38
#include "database/SqliteTraits.h"
39
#include "database/SqliteTransaction.h"
40
#include "logging/Logger.h"
41
#include "MediaLibrary.h"
42

43
44
45
namespace medialibrary
{

46
47
48
namespace sqlite
{

49
50
51
52
53
54
class Row
{
public:
    Row( sqlite3_stmt* stmt )
        : m_stmt( stmt )
        , m_idx( 0 )
55
        , m_nbColumns( sqlite3_column_count( stmt ) )
56
57
58
    {
    }

59
    constexpr Row()
60
61
        : m_stmt( nullptr )
        , m_idx( 0 )
62
        , m_nbColumns( 0 )
63
64
65
    {
    }

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

90
    bool operator==(std::nullptr_t) const
91
92
93
94
    {
        return m_stmt == nullptr;
    }

95
    bool operator!=(std::nullptr_t) const
96
97
98
99
    {
        return m_stmt != nullptr;
    }

100
101
102
private:
    sqlite3_stmt* m_stmt;
    unsigned int m_idx;
103
    unsigned int m_nbColumns;
104
105
};

106
107
108
class Statement
{
public:
109
    Statement( SqliteConnection::Handle dbConnection, const std::string& req )
110
111
112
113
        : m_stmt( nullptr, [](sqlite3_stmt* stmt) {
                sqlite3_clear_bindings( stmt );
                sqlite3_reset( stmt );
            })
114
        , m_dbConn( dbConnection )
115
        , m_bindIdx( 0 )
116
    {
117
        std::lock_guard<compat::Mutex> lock( StatementsCacheLock );
118
119
120
        auto& connMap = StatementsCache[ dbConnection ];
        auto it = connMap.find( req );
        if ( it == end( connMap ) )
121
122
        {
            sqlite3_stmt* stmt;
123
            int res = sqlite3_prepare_v2( dbConnection, req.c_str(), -1, &stmt, NULL );
124
125
            if ( res != SQLITE_OK )
            {
126
                throw std::runtime_error( std::string( "Failed to compile request: " ) + req + " " +
127
                            sqlite3_errmsg( dbConnection ) );
128
129
            }
            m_stmt.reset( stmt );
130
            connMap.emplace( req, CachedStmtPtr( stmt, &sqlite3_finalize ) );
131
132
        }
        else
133
        {
134
            m_stmt.reset( it->second.get() );
135
        }
136
137
        if ( req == "COMMIT" )
            m_isCommit = true;
138
139
140
141
142
    }

    template <typename... Args>
    void execute(Args&&... args)
    {
143
144
        m_bindIdx = 1;
        (void)std::initializer_list<bool>{ _bind( std::forward<Args>( args ) )... };
145
146
147
148
    }

    Row row()
    {
149
        auto maxRetries = 10;
150
        while ( true )
151
        {
152
153
154
155
156
157
            auto res = sqlite3_step( m_stmt.get() );
            if ( res == SQLITE_ROW )
                return Row( m_stmt.get() );
            else if ( res == SQLITE_DONE )
                return Row();
            else if ( res == SQLITE_BUSY && ( Transaction::transactionInProgress() == false ||
158
                                              m_isCommit == true ) && maxRetries-- > 0 )
159
160
161
162
163
164
165
166
167
168
                continue;
            std::string errMsg = sqlite3_errmsg( m_dbConn );
            switch ( res )
            {
                case SQLITE_CONSTRAINT:
                    throw errors::ConstraintViolation( sqlite3_sql( m_stmt.get() ), errMsg );
                default:
                    throw std::runtime_error( std::string{ sqlite3_sql( m_stmt.get() ) }
                                              + ": " + errMsg );
            }
169
        }
170
171
    }

172
173
    static void FlushStatementCache()
    {
174
        std::lock_guard<compat::Mutex> lock( StatementsCacheLock );
175
176
177
        StatementsCache.clear();
    }

178
private:
179
180
    template <typename T>
    bool _bind( T&& value )
181
    {
182
        auto res = Traits<T>::Bind( m_stmt.get(), m_bindIdx, std::forward<T>( value ) );
183
184
        if ( res != SQLITE_OK )
            throw std::runtime_error( "Failed to bind parameter" );
185
186
        m_bindIdx++;
        return true;
187
188
189
    }

private:
190
    // Used during the connection lifetime. This holds a compiled request
191
    using CachedStmtPtr = std::unique_ptr<sqlite3_stmt, int (*)(sqlite3_stmt*)>;
192
193
194
195
    // Used for the current statement execution, this
    // basically holds the state of the currently executed request.
    using StatementPtr = std::unique_ptr<sqlite3_stmt, void(*)(sqlite3_stmt*)>;
    StatementPtr m_stmt;
196
    SqliteConnection::Handle m_dbConn;
197
    unsigned int m_bindIdx;
198
    bool m_isCommit;
199
    static compat::Mutex StatementsCacheLock;
200
201
    static std::unordered_map<SqliteConnection::Handle,
                            std::unordered_map<std::string, CachedStmtPtr>> StatementsCache;
202
203
};

204
class Tools
205
206
{
    public:
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
207
208
209
210
211
212
213
        /**
         * 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.
         */
214
        template <typename IMPL, typename INTF, typename... Args>
215
        static std::vector<std::shared_ptr<INTF> > fetchAll( MediaLibraryPtr ml, const std::string& req, Args&&... args )
216
        {
217
            auto dbConnection = ml->getConn();
218
            SqliteConnection::ReadContext ctx;
219
            if (Transaction::transactionInProgress() == false)
220
                ctx = dbConnection->acquireReadContext();
221
            auto chrono = std::chrono::steady_clock::now();
222

223
            std::vector<std::shared_ptr<INTF>> results;
224
            auto stmt = Statement( dbConnection->getConn(), req );
225
226
227
            stmt.execute( std::forward<Args>( args )... );
            Row sqliteRow;
            while ( ( sqliteRow = stmt.row() ) != nullptr )
228
            {
229
                auto row = IMPL::load( ml, sqliteRow );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
230
                results.push_back( row );
231
            }
232
233
234
            auto duration = std::chrono::steady_clock::now() - chrono;
            LOG_DEBUG("Executed ", req, " in ",
                     std::chrono::duration_cast<std::chrono::microseconds>( duration ).count(), "µs" );
235
            return results;
236
237
        }

238
        template <typename T, typename... Args>
239
        static std::shared_ptr<T> fetchOne( MediaLibraryPtr ml, const std::string& req, Args&&... args )
240
        {
241
            auto dbConnection = ml->getConn();
242
            SqliteConnection::ReadContext ctx;
243
            if (Transaction::transactionInProgress() == false)
244
                ctx = dbConnection->acquireReadContext();
245
            auto chrono = std::chrono::steady_clock::now();
246

247
            auto stmt = Statement( dbConnection->getConn(), req );
248
249
250
            stmt.execute( std::forward<Args>( args )... );
            auto row = stmt.row();
            if ( row == nullptr )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
251
                return nullptr;
252
            auto res = T::load( ml, row );
253
254
255
256
            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;
257
258
        }

Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
259
        template <typename... Args>
260
        static bool executeRequest( DBConnection dbConnection, const std::string& req, Args&&... args )
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
261
        {
262
            SqliteConnection::WriteContext ctx;
263
            if (Transaction::transactionInProgress() == false)
264
                ctx = dbConnection->acquireWriteContext();
265
            return executeRequestLocked( dbConnection, req, std::forward<Args>( args )... );
Hugo Beauzée-Luyssen's avatar
Hugo Beauzée-Luyssen committed
266
267
        }

268
        template <typename... Args>
269
        static bool executeDelete( DBConnection dbConnection, const std::string& req, Args&&... args )
270
        {
271
            SqliteConnection::WriteContext ctx;
272
            if (Transaction::transactionInProgress() == false)
273
                ctx = dbConnection->acquireWriteContext();
274
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
275
276
                return false;
            return sqlite3_changes( dbConnection->getConn() ) > 0;
277
278
        }

279
        template <typename... Args>
280
        static bool executeUpdate( DBConnection dbConnection, const std::string& req, Args&&... args )
281
        {
282
            // The code would be exactly the same, do not freak out because it calls executeDelete :)
283
            return executeDelete( dbConnection, req, std::forward<Args>( args )... );
284
285
        }

286
287
288
289
290
        /**
         * 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>
291
        static int64_t executeInsert( DBConnection dbConnection, const std::string& req, Args&&... args )
292
        {
293
            SqliteConnection::WriteContext ctx;
294
            if (Transaction::transactionInProgress() == false)
295
                ctx = dbConnection->acquireWriteContext();
296
            if ( executeRequestLocked( dbConnection, req, std::forward<Args>( args )... ) == false )
297
                return 0;
298
            return sqlite3_last_insert_rowid( dbConnection->getConn() );
299
300
        }

301
    private:
302
303
304
        template <typename... Args>
        static bool executeRequestLocked( DBConnection dbConnection, const std::string& req, Args&&... args )
        {
305
            auto chrono = std::chrono::steady_clock::now();
306
            auto stmt = Statement( dbConnection->getConn(), req );
307
308
309
            stmt.execute( std::forward<Args>( args )... );
            while ( stmt.row() != nullptr )
                ;
310
311
312
            auto duration = std::chrono::steady_clock::now() - chrono;
            LOG_DEBUG("Executed ", req, " in ",
                     std::chrono::duration_cast<std::chrono::microseconds>( duration ).count(), "µs" );
313
314
            return true;
        }
315
316
};

317
318
}

319
320
}

321
#endif // SQLITETOOLS_H