path: root/sqlitebackend.cpp

#include <QMessageBox>

#include "sqlitebackend.h"

SQLiteSaveFile::SQLiteSaveFile(QObject *parent) :
    QObject(parent)
  , m_lastError(NoError)
  , m_lastErrorString(QString())
  , m_open(false)
  , m_dirty(false)
{
    clearNew();
}

bool SQLiteSaveFile::open(const QString &filename)
{
    qDebug() << "open";
    {
        QMutexLocker l(&m_dbMut);
        QFile f(filename);
        if (!f.exists()) {
            setError(FileNotFoundError, QString("File \"%1\"does not exist.").arg(filename));
            return false;
        }

        QSqlDatabase new_db(QSqlDatabase::addDatabase("QSQLITE", QUuid::createUuid().toString()));
        new_db.setDatabaseName(f.fileName());
        if (!new_db.open()) {
            qWarning() << "Cannot open db, closing" << filename;
            setDatabaseError(new_db);
            new_db.close();
            return false;
        }

        m_db.close();
        m_db = new_db;

        /* Try to load image, ignore if image is unset */
        QSqlQuery q("SELECT data FROM blobs WHERE name = 'image'", m_db);
        if (!q.next())
            return setDatabaseError(q);

        m_memory = false;
        m_dirty = false;
        m_open = true;
        m_image = q.value(0).toByteArray();
    }
    fileReload(); /* Call after unlocking mutex to allow accesses by receivers */
    return true;
}

bool SQLiteSaveFile::clearNew()
{
    qDebug() << "clearNew";
    {
        QMutexLocker l(&m_dbMut);

        QSqlDatabase new_db(QSqlDatabase::addDatabase("QSQLITE", QUuid::createUuid().toString()));
        new_db.setDatabaseName(":memory:");
        if (!new_db.open()) {
            setDatabaseError(new_db);
            qWarning() << "Cannot open new mem db, closing";
            new_db.close();
            return false;
        }

        QSqlDatabase old_db = m_db;
        m_db = new_db;

        if (!initDb(true)) {
            qWarning() << "Cannot init new mem db, closing";
            m_db.close();
            m_db = old_db;
            return false;
        }

        old_db.close();
        m_memory = true;
        m_dirty = false;
        m_open = true;
        m_image = QByteArray();
    }
    fileReload(); /* Call after unlocking mutex to allow accesses by receivers */
    return true;
}

bool SQLiteSaveFile::initDb(bool setCreationDate, const QString &schema_name)
{
    for (auto const &q: {
         "CREATE TABLE IF NOT EXISTS %1.metadata (key TEXT PRIMARY KEY, value TEXT)",
         "CREATE TABLE IF NOT EXISTS %1.tags (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, anchor_x REAL, anchor_y REAL, meta TEXT)",
         "CREATE TABLE IF NOT EXISTS %1.blobs (name TEXT, data BLOB)"}) {
        /* We cannot use sqlite's value binding for the table/schema name. This is an sqlite API restriction.
         * The schema_name parameter here is not user-supplied, so this is safe. */
        if (!runSql(QString(q).arg(schema_name)))
            return false;
    }

    if (setCreationDate) {
        if (!setMetaLocked("creationTime", QDateTime::currentDateTimeUtc().toMSecsSinceEpoch()))
            return false;
    }

    return true;
}

bool SQLiteSaveFile::saveAs(const QString &filename)
{
    /* Using the SQLite backup API is a royal PITA on windows due to Qt not including an actual sqlite3.dll there, so we just emulate its functionality. */
    qDebug() << "saveAs" << filename;
    QMutexLocker l(&m_dbMut);
    QFile::remove(filename);
    qInfo() << "removed existing file" << filename;

    if (!runSql("ATTACH DATABASE ? AS target", {filename}))
        return false;

    if (!initDb(false, "target")) {
        /* Fire and forget */
        QSqlQuery q("DETACH DATABASE target");
        return false;
    }

    for (const auto &table : {"metadata", "tags", "blobs"}) {
        /* We cannot use sqlite's value binding for the table/schema name. This is an sqlite API restriction.
         * The schema_name parameter here is not user-supplied, so this is safe. */
        if (!runSql(QString("INSERT INTO target.%1 SELECT * FROM main.%1").arg(table))) {
            /* Fire and forget */
            QSqlQuery q("DETACH DATABASE target");
            return false;
        }
    }

    m_db.close();

    /* Re-create DB. We do not want to make any assumptions about the insides of Qt's driver.
     * It might cache things internally and we might throw it off-balance if we swap out the DB attachment underneath. */
    QSqlDatabase new_db(QSqlDatabase::addDatabase("QSQLITE", QUuid::createUuid().toString()));
    new_db.setDatabaseName(filename);
    if (!new_db.open())
        return setDatabaseError(new_db);

    m_db = new_db;
    m_memory = false;
    m_dirty = false;
    return true;
}

QList<Tag> SQLiteSaveFile::getAllTags()
{
    qDebug() << "getAllTags";
    QMutexLocker l(&m_dbMut);
    resetError();
    QList<Tag> rv;

    QSqlQuery q("SELECT id, name, anchor_x, anchor_y, meta FROM tags", m_db);
    if (!setDatabaseError(q))
        return QList<Tag>();

    while (q.next()) {
        rv << Tag {
               q.value(0).toLongLong(),
               q.value(1).toString(),
               q.value(2).toFloat(),
               q.value(3).toFloat(),
               q.value(4).toByteArray()
        };
    }

    if (!setDatabaseError(q))
        return QList<Tag>();

    return rv;
}

bool SQLiteSaveFile::updateTag(Tag tag)
{
    QMutexLocker l(&m_dbMut);
    if (!runSql("UPDATE tags SET name=?, anchor_x=?, anchor_y=?, meta=? WHERE id=?", {
               tag.name, tag.anchor.x(), tag.anchor.y(), QJsonDocument::fromVariant(tag.metadata).toJson(), tag.id
           }))
        return false;

    m_dirty = true;
    tagChange(TagChange::CHANGED, tag);
    return true;
}

bool SQLiteSaveFile::deleteTag(Tag tag)
{
    QMutexLocker l(&m_dbMut);
    if (!runSql("DELETE FROM tags WHERE id=?", {tag.id}))
        return false;

    m_dirty = true;
    tagChange(TagChange::DELETED, tag);
    return true;
}

bool SQLiteSaveFile::createTag(Tag tag)
{
    qDebug() << "createTag";
    QMutexLocker l(&m_dbMut);
    resetError();
    QSqlQuery q(m_db);
    q.prepare("INSERT INTO tags(name, anchor_x, anchor_y, meta) VALUES (?, ?, ?, ?)");
    q.addBindValue(tag.name);
    q.addBindValue(tag.anchor.x());
    q.addBindValue(tag.anchor.y());
    q.addBindValue(QJsonDocument::fromVariant(tag.metadata).toJson());

    if (!setDatabaseError(q))
        return false;

    Tag created_tag(q.lastInsertId().toLongLong(), tag);
    m_dirty = true;
    tagChange(TagChange::CREATED, created_tag);
    return true;
}

bool SQLiteSaveFile::setMetaLocked(const QString &key, const QVariant &value)
{
    return runSql("INSERT OR REPLACE INTO metadata(key, value) VALUES (?, ?)", {key, value});
}

bool SQLiteSaveFile::setMetaLocked(std::initializer_list<QPair<QString, QVariant>> metas)
{
    for (const auto &meta : metas) {
        if (!setMetaLocked(meta.first, meta.second))
            return false;
    }
    return true;
}

bool SQLiteSaveFile::setMeta(const QString &key, const QVariant &value) {
    QMutexLocker l(&m_dbMut);
    m_dirty = true;
    return setMetaLocked(key, value);
}

bool SQLiteSaveFile::setMeta(std::initializer_list<QPair<QString, QVariant>> metas) {
    QMutexLocker l(&m_dbMut);
    m_dirty = true;
    return setMetaLocked(metas);
}

const QVariant SQLiteSaveFile::getMeta(const QString &key) const {
    QMutexLocker l(&m_dbMut);
    return getMetaLocked(key);
}

const QVariant SQLiteSaveFile::getMetaLocked(const QString &key) const
{
    qDebug() << "getMeta " << key;
    resetError();
    QSqlQuery q(m_db);
    q.prepare("SELECT value FROM metadata WHERE key=?");
    q.addBindValue(key);

    if (!q.exec()) {
        setDatabaseError(q);
        return QVariant();
    }

    if (!q.next()) {
        setDatabaseError(q);
        return QVariant();
    }

    return q.value(0);
}

bool SQLiteSaveFile::runSql(QString query, std::initializer_list<QVariant> bindings)
{
    qDebug() << "runSql:" << query << "db: open=" << m_db.isOpen() << "valid=" << m_db.isValid() << "error state" << m_db.lastError().text();
    resetError();
    QSqlQuery q(m_db);
    q.prepare(query);
    for (const QVariant &v : bindings) {
        q.addBindValue(v);
    }

    if (!q.exec()) {
        return setDatabaseError(q);
    }
    return true;
}

bool SQLiteSaveFile::loadImageFromDisk(const QString &filename)
{
    QMutexLocker l(&m_dbMut);
    QFile f(filename);
    resetError();

    if (!f.open(QIODevice::ReadOnly)) {
        setError(ImageOpenError, QString("Failed to open image: %1").arg(f.errorString()));
        return false;
    }

    m_image = f.readAll();
    if (f.error() != QFileDevice::NoError) {
        setError(ImageReadError, QString("Failed to read image: %1").arg(f.errorString()));
        return false;
    }

    if (!setMetaLocked({
                 {"imagePathOriginal", f.fileName()},
                 {"imagePathAbsolute", QFileInfo(f).absoluteFilePath()},
                 {"imageLoadedTime", QDateTime::currentDateTimeUtc().toMSecsSinceEpoch()}}))
        return false;

    m_dirty = true;
    return runSql("INSERT OR REPLACE INTO blobs(name, data) VALUES ('image', ?)", {m_image});
}

bool SQLiteSaveFile::reloadImageFromDisk()
{
    const QString &p = getMeta("imagePathOriginal").toString();
    if (QFile(p).exists())
        return loadImageFromDisk(p);

    const QString &q = getMeta("imagePathAbsolute").toString();
    if (QFile(q).exists())
        return loadImageFromDisk(q);

    return false;
}

Tag::Tag(long long id, QString name, qreal anchor_x, qreal anchor_y, QByteArray metadata)
    : id(id)
    , name(name)
    , anchor(QPointF(anchor_x, anchor_y))
    , metadata(QJsonDocument::fromJson(metadata).object().toVariantMap())
    , valid(true)
{
}

Tag::Tag(long long id, const Tag &other)
    : id(id)
    , name(other.name)
    , anchor(other.anchor)
    , metadata(other.metadata)
    , valid(true)
{
}

bool SQLiteSaveFile::setDatabaseError(const QSqlQuery &q) const
{
    if (!q.lastError().isValid())
        return true;

    qWarning() << "Query error: " << q.lastError().text();
    setError(SQLiteError, QString("Project file database error executing %1: %2").arg(q.executedQuery()).arg(q.lastError().text()));

    return false;
}

bool SQLiteSaveFile::setDatabaseError(const QSqlDatabase &db) const
{
    if (!db.lastError().isValid())
        return true;

    qWarning() << "Database error: " << db.lastError().text();
    setError(SQLiteError, QString("Project file database error: %1").arg(db.lastError().text()));
    return false;
}