From 752f270cf81bc8c88cb4bf428e5aceb51405c06a Mon Sep 17 00:00:00 2001 From: jaseg Date: Sun, 9 Aug 2020 22:31:43 +0200 Subject: Straighten out sqlite backend --- numberator.pro | 6 ---- sqlitebackend.cpp | 106 ++++++++++++++++++++++++++++-------------------------- sqlitebackend.h | 2 +- 3 files changed, 56 insertions(+), 58 deletions(-) diff --git a/numberator.pro b/numberator.pro index 124dd61..1b2dd1f 100644 --- a/numberator.pro +++ b/numberator.pro @@ -4,12 +4,6 @@ greaterThan(QT_MAJOR_VERSION, 4): QT += widgets CONFIG += c++2a - -win32:INCLUDEPATH += $$[QT_INSTALL_PREFIX]/../Src/qtbase/src/3rdparty/sqlite -win32:LIBS += -L$$[QT_INSTALL_PLUGINS]/sqldrivers -lqsqlite -#win32:SOURCES += $$[QT_INSTALL_PREFIX]/../Src/qtbase/src/3rdparty/sqlite/sqlite3.c -unix:LIBS += -lsqlite3 - # The following define makes your compiler emit warnings if you use # any Qt feature that has been marked deprecated (the exact warnings # depend on your compiler). Please consult the documentation of the diff --git a/sqlitebackend.cpp b/sqlitebackend.cpp index 434f1e6..89b7b07 100644 --- a/sqlitebackend.cpp +++ b/sqlitebackend.cpp @@ -1,7 +1,5 @@ #include -#include - #include "sqlitebackend.h" SQLiteSaveFile::SQLiteSaveFile(QObject *parent) : @@ -28,6 +26,7 @@ bool SQLiteSaveFile::open(const QString &filename) 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; @@ -38,8 +37,6 @@ bool SQLiteSaveFile::open(const QString &filename) /* Try to load image, ignore if image is unset */ QSqlQuery q("SELECT data FROM blobs WHERE name = 'image'", m_db); - if (!q.exec()) - return setDatabaseError(q); if (!q.next()) return setDatabaseError(q); @@ -62,6 +59,7 @@ bool SQLiteSaveFile::clearNew() new_db.setDatabaseName(":memory:"); if (!new_db.open()) { setDatabaseError(new_db); + qWarning() << "Cannot open new mem db, closing"; new_db.close(); return false; } @@ -70,6 +68,7 @@ bool SQLiteSaveFile::clearNew() m_db = new_db; if (!initDb(true)) { + qWarning() << "Cannot init new mem db, closing"; m_db.close(); m_db = old_db; return false; @@ -85,13 +84,15 @@ bool SQLiteSaveFile::clearNew() return true; } -bool SQLiteSaveFile::initDb(bool setCreationDate) +bool SQLiteSaveFile::initDb(bool setCreationDate, const QString &schema_name) { for (auto const &q: { - "CREATE TABLE IF NOT EXISTS metadata (key TEXT, value TEXT)", - "CREATE TABLE IF NOT EXISTS tags (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, anchor_x REAL, anchor_y REAL, meta TEXT)", - "CREATE TABLE IF NOT EXISTS blobs (name TEXT, data BLOB)"}) { - if (!runSql(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; } @@ -103,45 +104,46 @@ bool SQLiteSaveFile::initDb(bool setCreationDate) return true; } -static sqlite3 *getSqliteHandle(QSqlDatabase &db) { - QVariant v = db.driver()->handle(); - assert (v.isValid()); - assert (!qstrcmp(v.typeName(), "sqlite3*")); - return *static_cast(v.data()); -} - 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 f(filename); - QSqlDatabase new_db(QSqlDatabase::addDatabase("QSQLITE", QUuid::createUuid().toString())); - { - new_db.setDatabaseName(f.fileName()); - if (!new_db.open()) - goto err_cleanup; + QFile::remove(filename); + qInfo() << "removed existing file" << filename; - sqlite3 *old_handle = getSqliteHandle(m_db); - sqlite3 *new_handle = getSqliteHandle(new_db); - sqlite3_backup *bck = sqlite3_backup_init(new_handle, "main", old_handle, "main"); - if (!bck) - goto err_cleanup; - - if (sqlite3_backup_step(bck, -1) != SQLITE_DONE) - goto err_cleanup; + if (!runSql("ATTACH DATABASE ? AS target", {filename})) + return false; - if (sqlite3_backup_finish(bck) != SQLITE_DONE) - goto err_cleanup; + if (!initDb(false, "target")) { + /* Fire and forget */ + QSqlQuery q("DETACH DATABASE target"); + return false; + } - m_db.close(); - m_db = new_db; - m_memory = false; - return true; + 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; + } } -err_cleanup: - setDatabaseError(new_db); - new_db.close(); - 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 SQLiteSaveFile::getAllTags() @@ -152,10 +154,8 @@ QList SQLiteSaveFile::getAllTags() QList rv; QSqlQuery q("SELECT id, name, anchor_x, anchor_y, meta FROM tags", m_db); - if (!q.exec()) { - setDatabaseError(q); + if (!setDatabaseError(q)) return QList(); - } while (q.next()) { rv << Tag { @@ -202,14 +202,15 @@ bool SQLiteSaveFile::createTag(Tag tag) qDebug() << "createTag"; QMutexLocker l(&m_dbMut); resetError(); - QSqlQuery q("INSERT INTO tags(name, anchor_x, anchor_y, meta) VALUES (?, ?, ?, ?)", m_db); + 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 (!q.exec()) - return setDatabaseError(q); + if (!setDatabaseError(q)) + return false; Tag created_tag(q.lastInsertId().toLongLong(), tag); m_dirty = true; @@ -252,7 +253,8 @@ const QVariant SQLiteSaveFile::getMetaLocked(const QString &key) const { qDebug() << "getMeta " << key; resetError(); - QSqlQuery q("SELECT value FROM metadata WHERE key=?", m_db); + QSqlQuery q(m_db); + q.prepare("SELECT value FROM metadata WHERE key=?"); q.addBindValue(key); if (!q.exec()) { @@ -270,9 +272,10 @@ const QVariant SQLiteSaveFile::getMetaLocked(const QString &key) const bool SQLiteSaveFile::runSql(QString query, std::initializer_list bindings) { - qDebug() << "runSql " << query; + qDebug() << "runSql:" << query << "db: open=" << m_db.isOpen() << "valid=" << m_db.isValid() << "error state" << m_db.lastError().text(); resetError(); - QSqlQuery q(query, m_db); + QSqlQuery q(m_db); + q.prepare(query); for (const QVariant &v : bindings) { q.addBindValue(v); } @@ -346,8 +349,9 @@ bool SQLiteSaveFile::setDatabaseError(const QSqlQuery &q) const if (!q.lastError().isValid()) return true; - qDebug() << "Query error: " << q.lastError().text(); + 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; } @@ -356,7 +360,7 @@ bool SQLiteSaveFile::setDatabaseError(const QSqlDatabase &db) const if (!db.lastError().isValid()) return true; - qDebug() << "Database error: " << db.lastError().text(); + qWarning() << "Database error: " << db.lastError().text(); setError(SQLiteError, QString("Project file database error: %1").arg(db.lastError().text())); return false; } diff --git a/sqlitebackend.h b/sqlitebackend.h index 52faedc..8bfce00 100644 --- a/sqlitebackend.h +++ b/sqlitebackend.h @@ -90,7 +90,7 @@ signals: private: bool connect(); - bool initDb(bool setCreationDate=true); + bool initDb(bool setCreationDate=true, const QString &schema_name="main"); bool runSql(QString query, std::initializer_list bindings={}); bool setMetaLocked(const QString &key, const QVariant &value); -- cgit