251        throw std::runtime_error("SQLiteDatabase: Cannot reopen an in-memory database");
252    }
253    Open(m_additional_flags);
254}
255
256void SQLiteDatabase::Open(int additional_flags)
257{
258    int flags = SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | additional_flags;
259
260    if (m_db == nullptr) {
261        if (!(flags & SQLITE_OPEN_MEMORY)) {
262            TryCreateDirectories(m_dir_path);
263            if (!IsDirWritable(m_dir_path)) {
264                throw std::runtime_error(strprintf("SQLiteDatabase: Failed to open database in directory '%s': directory is not writable", fs::PathToString(m_dir_path)));
265            }
266        }
267
268        int ret = sqlite3_open_v2(m_file_path.c_str(), &m_db, flags, nullptr);
269        if (ret != SQLITE_OK) {
270            throw std::runtime_error(strprintf("SQLiteDatabase: Failed to open database: %s\n", sqlite3_errstr(ret)));
271        }
272        ret = sqlite3_extended_result_codes(m_db, 1);
273        if (ret != SQLITE_OK) {
274            throw std::runtime_error(strprintf("SQLiteDatabase: Failed to enable extended result codes: %s\n", sqlite3_errstr(ret)));
275        }
276        // Trace SQL statements if tracing is enabled with -debug=walletdb -loglevel=walletdb:trace
277        if (util::log::ShouldTraceLog(BCLog::WALLETDB)) {
278           ret = sqlite3_trace_v2(m_db, SQLITE_TRACE_STMT, TraceSqlCallback, this);
279           if (ret != SQLITE_OK) {
280               LogWarning("Failed to enable SQL tracing for %s", Filename());
281           }
282        }
283    }
284
285    if (sqlite3_db_readonly(m_db, "main") != 0) {
286        throw std::runtime_error("SQLiteDatabase: Database opened in readonly mode but read-write permissions are needed");
287    }
288
289    // Acquire an exclusive lock on the database
290    // First change the locking mode to exclusive
291    SetPragma(m_db, "locking_mode", "exclusive", "Unable to change database locking mode to exclusive");
292    // Now begin a transaction to acquire the exclusive lock. This lock won't be released until we close because of the exclusive locking mode.
293    int ret = sqlite3_exec(m_db, "BEGIN EXCLUSIVE TRANSACTION", nullptr, nullptr, nullptr);
294    if (ret != SQLITE_OK) {
295        throw std::runtime_error("SQLiteDatabase: Unable to obtain an exclusive lock on the database, is it being used by another instance of " CLIENT_NAME "?\n");
296    }
297    ret = sqlite3_exec(m_db, "COMMIT", nullptr, nullptr, nullptr);
298    if (ret != SQLITE_OK) {
299        throw std::runtime_error(strprintf("SQLiteDatabase: Unable to end exclusive lock transaction: %s\n", sqlite3_errstr(ret)));
300    }
301
302    // Enable fullfsync for the platforms that use it
303    SetPragma(m_db, "fullfsync", "true", "Failed to enable fullfsync");
304
305    if (m_use_unsafe_sync) {
306        // Use normal synchronous mode for the journal
307        LogWarning("SQLite is configured to not wait for data to be flushed to disk. Data loss and corruption may occur.");
308        SetPragma(m_db, "synchronous", "OFF", "Failed to set synchronous mode to OFF");
309    }
310
311    // Make the table for our key-value pairs
312    // First check that the main table exists
313    sqlite3_stmt* check_main_stmt{nullptr};
314    ret = sqlite3_prepare_v2(m_db, "SELECT name FROM sqlite_master WHERE type='table' AND name='main'", -1, &check_main_stmt, nullptr);
315    if (ret != SQLITE_OK) {
316        throw std::runtime_error(strprintf("SQLiteDatabase: Failed to prepare statement to check table existence: %s\n", sqlite3_errstr(ret)));
317    }
318    ret = sqlite3_step(check_main_stmt);
319    if (sqlite3_finalize(check_main_stmt) != SQLITE_OK) {
320        throw std::runtime_error(strprintf("SQLiteDatabase: Failed to finalize statement checking table existence: %s\n", sqlite3_errstr(ret)));
321    }
322    bool table_exists;
323    if (ret == SQLITE_DONE) {
324        table_exists = false;
325    } else if (ret == SQLITE_ROW) {
326        table_exists = true;
327    } else {
328        throw std::runtime_error(strprintf("SQLiteDatabase: Failed to execute statement to check table existence: %s\n", sqlite3_errstr(ret)));
329    }
330
331    // Do the db setup things because the table doesn't exist only when we are creating a new wallet
332    if (!table_exists) {
333        ret = sqlite3_exec(m_db, "CREATE TABLE main(key BLOB PRIMARY KEY NOT NULL, value BLOB NOT NULL)", nullptr, nullptr, nullptr);
334        if (ret != SQLITE_OK) {
335            throw std::runtime_error(strprintf("SQLiteDatabase: Failed to create new database: %s\n", sqlite3_errstr(ret)));
336        }
337
338        // Set the application id
339        uint32_t app_id = ReadBE32(Params().MessageStart().data());
340        SetPragma(m_db, "application_id", strprintf("%d", static_cast<int32_t>(app_id)),
341                  "Failed to set the application id");
342
343        // Set the user version
344        SetPragma(m_db, "user_version", strprintf("%d", WALLET_SCHEMA_VERSION),
345                  "Failed to set the wallet schema version");
346    }
347}
348
349bool SQLiteDatabase::Rewrite()
350{
351    // Rewrite the database using the VACUUM command: https://sqlite.org/lang_vacuum.html
352    int ret = sqlite3_exec(m_db, "VACUUM", nullptr, nullptr, nullptr);
353    return ret == SQLITE_OK;
354}
355
356bool SQLiteDatabase::Backup(const std::string& dest) const
357{
358    sqlite3* db_copy;
359    int res = sqlite3_open(dest.c_str(), &db_copy);
360    if (res != SQLITE_OK) {
361        sqlite3_close(db_copy);
362        return false;
363    }
364    sqlite3_backup* backup = sqlite3_backup_init(db_copy, "main", m_db, "main");
365    if (!backup) {
366        LogWarning("Unable to begin sqlite backup: %s", sqlite3_errmsg(m_db));
367        sqlite3_close(db_copy);
368        return false;
369    }
370    // Specifying -1 will copy all of the pages
371    res = sqlite3_backup_step(backup, -1);
372    if (res != SQLITE_DONE) {
373        LogWarning("Unable to continue sqlite backup: %s", sqlite3_errstr(res));
374        sqlite3_backup_finish(backup);
375        sqlite3_close(db_copy);
376        return false;
377    }
378    res = sqlite3_backup_finish(backup);
379    sqlite3_close(db_copy);
380    return res == SQLITE_OK;
381}
382
383void SQLiteDatabase::Close()
384{
385    int res = sqlite3_close(m_db);
386    if (res != SQLITE_OK) {
387        throw std::runtime_error(strprintf("SQLiteDatabase: Failed to close database: %s\n", sqlite3_errstr(res)));
388    }
389    m_db = nullptr;
390}
391
392bool SQLiteDatabase::HasActiveTxn()
393{
394    // 'sqlite3_get_autocommit' returns true by default, and false if a transaction has begun and not been committed or rolled back.
395    return m_db && sqlite3_get_autocommit(m_db) == 0;
396}
397
398int SQliteExecHandler::Exec(SQLiteDatabase& database, const std::string& statement)
399{
400    return sqlite3_exec(database.m_db, statement.data(), nullptr, nullptr, nullptr);
401}
402
403std::unique_ptr<DatabaseBatch> SQLiteDatabase::MakeBatch()
404{
405    // We ignore flush_on_close because we don't do manual flushing for SQLite
406    return std::make_unique<SQLiteBatch>(*this);
407}
408
409SQLiteBatch::SQLiteBatch(SQLiteDatabase& database)
410    : m_database(database)
411{
412    // Make sure we have a db handle
413    assert(m_database.m_db);
414
415    SetupSQLStatements();
416}
417
418void SQLiteBatch::Close()
419{
420    bool force_conn_refresh = false;
421
422    // If we began a transaction, and it wasn't committed, abort the transaction in progress
423    if (m_txn) {
424        if (TxnAbort()) {
425            LogWarning("SQLiteBatch: Batch closed unexpectedly without the transaction being explicitly committed or aborted");
426        } else {
427            // If transaction cannot be aborted, it means there is a bug or there has been data corruption. Try to recover in this case
428            // by closing and reopening the database. Closing the database should also ensure that any changes made since the transaction
429            // was opened will be rolled back and future transactions can succeed without committing old data.
430            force_conn_refresh = true;
431            LogWarning("SQLiteBatch: Batch closed and failed to abort transaction, resetting db connection..");
432        }
433    }
434
435    // Free all of the prepared statements
436    const std::vector<std::pair<sqlite3_stmt**, const char*>> statements{
437        {&m_read_stmt, "read"},
438        {&m_insert_stmt, "insert"},
439        {&m_overwrite_stmt, "overwrite"},
440        {&m_delete_stmt, "delete"},
441        {&m_delete_prefix_stmt, "delete prefix"},
442    };
443
444    for (const auto& [stmt_prepared, stmt_description] : statements) {
445        int res = sqlite3_finalize(*stmt_prepared);
446        if (res != SQLITE_OK) {
447            LogWarning("SQLiteBatch: Batch closed but could not finalize %s statement: %s",
448                      stmt_description, sqlite3_errstr(res));
449        }
450        *stmt_prepared = nullptr;
451    }
452
453    if (force_conn_refresh) {
454        if (m_database.m_additional_flags & SQLITE_OPEN_MEMORY) {
455            throw std::runtime_error("SQLiteDatabase: Cannot recover in-memory database connection");
456        }
457        m_database.Close();
458        try {
459            m_database.Open();
460            // If TxnAbort failed and we refreshed the connection, the semaphore was not released, so release it here to avoid deadlocks on future writes.
461            m_database.m_write_semaphore.release();
462        } catch (const std::runtime_error&) {
463            // If open fails, cleanup this object and rethrow the exception
464            m_database.Close();
465            throw;
466        }
467    }
468}
469
470bool SQLiteBatch::ReadKey(DataStream&& key, DataStream& value)
471{
472    if (!m_database.m_db) return false;
473    assert(m_read_stmt);
474
475    // Bind: leftmost parameter in statement is index 1
476    if (!BindBlobToStatement(m_read_stmt, 1, key, "key")) return false;
477    int res = sqlite3_step(m_read_stmt);
478    if (res != SQLITE_ROW) {
479        if (res != SQLITE_DONE) {
480            // SQLITE_DONE means "not found", don't log an error in that case.
481            LogWarning("Unable to execute read statement: %s", sqlite3_errstr(res));
482        }
483        sqlite3_clear_bindings(m_read_stmt);
484        sqlite3_reset(m_read_stmt);
485        return false;
486    }
487    // Leftmost column in result is index 0
488    value.clear();
489    value.write(SpanFromBlob(m_read_stmt, 0));
490
491    sqlite3_clear_bindings(m_read_stmt);
492    sqlite3_reset(m_read_stmt);
493    return true;
494}
495
496bool SQLiteBatch::WriteKey(DataStream&& key, DataStream&& value, bool overwrite)
497{
498    if (!m_database.m_db) return false;
499    assert(m_insert_stmt && m_overwrite_stmt);
500