Skip to content

FinceptTerminal is a modern finance application offering advanced market analytics, investment research, and economic data tools, designed for interactive exploration and data-driven decision-making in a user-friendly environment.

9.1 KB
Raw Blame
#include "storage/sqlite/Database.h"

#include "core/logging/Logger.h"
#include "storage/sqlite/migrations/MigrationRunner.h"

#include <QSqlError>
#include <QThread>

namespace fincept {

namespace {
constexpr const char* kMainConnectionName = "fincept_main";
constexpr const char* kDbTag = "DB";

QString thread_label(QThread* t) {
    if (!t)
        return QStringLiteral("?");
    const QString name = t->objectName();
    return name.isEmpty() ? QStringLiteral("0x%1").arg(reinterpret_cast<quintptr>(t), 0, 16) : name;
}
} // namespace

Database& Database::instance() {
    static Database s;
    return s;
}

Result<void> Database::open(const QString& path) {
    main_thread_ = QThread::currentThread();
    db_path_ = path;

    db_ = QSqlDatabase::addDatabase("QSQLITE", kMainConnectionName);
    db_.setDatabaseName(path);
    if (!db_.open()) {
        return Result<void>::err(db_.lastError().text().toStdString());
    }
    LOG_INFO(kDbTag, "Opened database: " + path);

    auto pr = apply_pragmas(db_, /*include_database_wide=*/true);
    if (pr.is_err())
        return pr;

    // Run versioned migrations on the main connection.
    MigrationRunner runner(db_);
    return runner.run();
}

void Database::close() {
    if (db_.isOpen())
        db_.close();
}

bool Database::is_open() const {
    return db_.isOpen();
}

QSqlDatabase Database::connection() {
    QThread* t = QThread::currentThread();

    // Main-thread fast path — the connection opened by `open()`.
    if (t == main_thread_)
        return db_;

    // Per-thread cloned connection. The thread_local Guard owns the
    // connection-name lifetime so the registry entry is removed when the
    // thread exits. This handles QtConcurrent thread-pool workers correctly:
    // they keep their connection across tasks and clean up at pool shutdown.
    struct Guard {
        QString name;
        ~Guard() {
            if (!name.isEmpty()) {
                // Remove from Qt's connection registry so file descriptors
                // and the QSqlDriver are released. Logs an inert warning if
                // any QSqlDatabase handle is still alive — harmless.
                QSqlDatabase::removeDatabase(name);
            }
        }
    };
    thread_local Guard guard;

    if (guard.name.isEmpty()) {
        if (db_path_.isEmpty()) {
            LOG_ERROR(kDbTag, "connection() called before open() — refusing to clone");
            return {};
        }

        // Unique connection name per thread. Encode pointer + objectName so
        // log messages are debuggable.
        guard.name =
            QStringLiteral("fincept_thread_%1_%2").arg(thread_label(t)).arg(reinterpret_cast<quintptr>(t), 0, 16);

        // Clone BY CONNECTION NAME, not by QSqlDatabase object. The object
        // overload dereferences `db_` — the main thread's handle and its live
        // QSqlDriver — from this worker thread, which is exactly the cross-thread
        // access the per-thread scheme exists to avoid. Qt added this overload
        // for precisely this case: it resolves the source inside the registry
        // under Qt's own lock.
        QSqlDatabase clone = QSqlDatabase::cloneDatabase(QString::fromLatin1(kMainConnectionName), guard.name);
        if (!clone.open()) {
            const QString err = clone.lastError().text();
            LOG_ERROR(kDbTag, QString("Failed to open per-thread connection on [%1]: %2").arg(thread_label(t), err));
            // Clean up the registry entry we created via cloneDatabase.
            QSqlDatabase::removeDatabase(guard.name);
            guard.name.clear();
            return {};
        }
        // Database-wide PRAGMAs (journal_mode) are already set by the main
        // connection — only re-apply per-connection ones.
        apply_pragmas(clone, /*include_database_wide=*/false);
        const int n = ++per_thread_connections_;
        LOG_INFO(kDbTag, QString("Opened per-thread connection on [%1] (total=%2)").arg(thread_label(t)).arg(n));
    }
    return QSqlDatabase::database(guard.name, /*open=*/false);
}

Result<QSqlQuery> Database::execute(const QString& sql, const QVariantList& params) {
    QSqlDatabase conn = connection();
    if (!conn.isOpen()) {
        return Result<QSqlQuery>::err("DB connection unavailable on this thread");
    }
    QSqlQuery query(conn);

    // Check prepare(). Ignoring it hid the real error on every failing
    // statement in the app: when prepare() fails, bindValue() silently does
    // nothing and the subsequent exec() reports the *symptom* — "Parameter
    // count mismatch", or "No query Unable to fetch row" when results are then
    // read — instead of the actual cause, which is usually "no such table" or
    // "no such column". Those two useless strings were what every repository
    // logged, so a schema mismatch was undiagnosable from the log alone.
    if (!query.prepare(sql)) {
        return Result<QSqlQuery>::err("prepare failed: " + query.lastError().text().toStdString());
    }

    for (int i = 0; i < params.size(); ++i) {
        query.bindValue(i, params[i]);
    }
    if (!query.exec()) {
        return Result<QSqlQuery>::err(query.lastError().text().toStdString());
    }
    return Result<QSqlQuery>::ok(std::move(query));
}

Result<void> Database::execute_many(const QString& sql, const QVector<QVariantList>& rows) {
    if (rows.isEmpty())
        return Result<void>::ok();

    QSqlDatabase conn = connection();
    if (!conn.isOpen()) {
        return Result<void>::err("DB connection unavailable on this thread");
    }

    // Prepare ONCE. `execute()` re-prepares per call, which costs one
    // sqlite3_prepare_v2 per row — a 100k-row broker instrument import paid
    // 100k compilations of the same statement.
    QSqlQuery query(conn);
    if (!query.prepare(sql)) {
        return Result<void>::err(query.lastError().text().toStdString());
    }

    int index = 0;
    for (const auto& row : rows) {
        for (int i = 0; i < row.size(); ++i)
            query.bindValue(i, row[i]);
        if (!query.exec()) {
            return Result<void>::err("row " + std::to_string(index) + ": " +
                                     query.lastError().text().toStdString());
        }
        ++index;
    }
    return Result<void>::ok();
}

Result<void> Database::exec(const QString& sql) {
    QSqlDatabase conn = connection();
    if (!conn.isOpen()) {
        return Result<void>::err("DB connection unavailable on this thread");
    }
    QSqlQuery query(conn);
    if (!query.exec(sql)) {
        return Result<void>::err(query.lastError().text().toStdString());
    }
    return Result<void>::ok();
}

Result<void> Database::begin_transaction() {
    QSqlDatabase conn = connection();
    if (!conn.isOpen())
        return Result<void>::err("DB connection unavailable on this thread");
    QSqlQuery q(conn);
    if (!q.exec("BEGIN IMMEDIATE")) {
        return Result<void>::err(q.lastError().text().toStdString());
    }
    return Result<void>::ok();
}

Result<void> Database::commit() {
    QSqlDatabase conn = connection();
    if (!conn.isOpen())
        return Result<void>::err("DB connection unavailable on this thread");
    QSqlQuery q(conn);
    if (!q.exec("COMMIT")) {
        return Result<void>::err(q.lastError().text().toStdString());
    }
    return Result<void>::ok();
}

Result<void> Database::rollback() {
    QSqlDatabase conn = connection();
    if (!conn.isOpen())
        return Result<void>::err("DB connection unavailable on this thread");
    QSqlQuery q(conn);
    if (!q.exec("ROLLBACK")) {
        return Result<void>::err(q.lastError().text().toStdString());
    }
    return Result<void>::ok();
}

Result<void> Database::apply_pragmas(QSqlDatabase& conn, bool include_database_wide) {
    // Database-wide PRAGMAs only need to be set once (any connection sets them
    // for the file). Per-connection PRAGMAs (foreign_keys, busy_timeout, …)
    // have to be re-applied for every cloned connection or cascade deletes
    // and contention behaviour will silently regress.
    static const char* kDatabaseWide[] = {
        "PRAGMA journal_mode = WAL",
    };
    // journal_size_limit is per-connection but bounds a file-global artefact: a
    // bulk import (100k instruments) grows -wal to hundreds of MB and, without a
    // limit, SQLite never truncates it back after a checkpoint — the file stays
    // at its high-water mark for the life of the install. 64 MB.
    static const char* kPerConnection[] = {
        "PRAGMA synchronous = NORMAL",  "PRAGMA cache_size = -20000",
        "PRAGMA temp_store = MEMORY",   "PRAGMA mmap_size = 268435456",
        "PRAGMA foreign_keys = ON",     "PRAGMA busy_timeout = 5000",
        "PRAGMA journal_size_limit = 67108864",
    };

    auto run = [&conn](const char* sql) {
        QSqlQuery q(conn);
        if (!q.exec(QLatin1String(sql))) {
            LOG_WARN(kDbTag, QString("PRAGMA failed: %1 — %2").arg(sql, q.lastError().text()));
        }
    };

    if (include_database_wide) {
        for (auto* p : kDatabaseWide)
            run(p);
    }
    for (auto* p : kPerConnection)
        run(p);

    if (include_database_wide)
        LOG_INFO(kDbTag, "PRAGMAs applied (WAL, foreign_keys, cache_size=20MB)");
    return Result<void>::ok();
}

} // namespace fincept