Straighten out sqlite backend

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 <QMessageBox>

 

-#include<sqlite3.h>

-

 #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<sqlite3 **>(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<Tag> SQLiteSaveFile::getAllTags()

@@ -152,10 +154,8 @@ QList<Tag> SQLiteSaveFile::getAllTags()
     QList<Tag> 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<Tag>();

-    }

 

     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<QVariant> 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<QVariant> bindings={});

 

     bool setMetaLocked(const QString &key, const QVariant &value);