KisResourceCacheDb.cpp File Reference

#include "KisResourceCacheDb.h"
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlDatabase>
#include <QBuffer>
#include <QVersionNumber>
#include <QStandardPaths>
#include <QDir>
#include <QDirIterator>
#include <QStringList>
#include <QElapsedTimer>
#include <QDataStream>
#include <QByteArray>
#include <QMessageBox>
#include <KritaVersionWrapper.h>
#include <klocalizedstring.h>
#include <KisBackup.h>
#include <kis_debug.h>
#include <KisUsageLogger.h>
#include <KisSqlQueryLoader.h>
#include <KisDatabaseTransactionLock.h>
#include "KisResourceLocator.h"
#include "KisResourceLoaderRegistry.h"
#include "ResourceDebug.h"
#include <kis_assert.h>
#include <KisCppQuirks.h>

Go to the source code of this file.

Functions
QString	changeToEmptyIfNull (QString s)
QSqlError	createDatabase (const QString &location)
QSqlError	runUpdateScript (const QString &script, const QString &message)
QSqlError	runUpdateScriptFile (const QString &path, const QString &message)
bool	updateSchemaVersion ()

Variables
const QString	dbDriver = "QSQLITE"
const QString	METADATA_RESOURCES = "resources"
const QString	METADATA_STORAGES = "storages"

Function Documentation

changeToEmptyIfNull()

QString changeToEmptyIfNull

(

QString

s

)

Definition at line 67 of file KisResourceCacheDb.cpp.

{
    return s.isNull() ? QString("") : s;
}

createDatabase()

QSqlError createDatabase

(

const QString &

location

)

initialization is completed, transaction is over, now enable the foreign_keys constraint if necessary

initialization is completed, transaction is over, now enable the foreign_keys constraint if necessary

Definition at line 142 of file KisResourceCacheDb.cpp.

{
    // NOTE: if the id's of Unknown and Memory in the database
    //       will change, and that will break the queries that
    //       remove Unknown and Memory storages on start-up.
    KisResourceCacheDb::storageTypes = QStringList {
        KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(1)),
        KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(2)),
        KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(3)),
        KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(4)),
        KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(5)),
        KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(6)),
        KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(7))};
 
    QDir dbLocation(location);
    if (!dbLocation.exists()) {
        dbLocation.mkpath(dbLocation.path());
    }
 
    std::optional<QSqlDatabase> existingDatabase =
        QSqlDatabase::database(QSqlDatabase::defaultConnection, false);
 
    const bool databaseConnectionExists = !QSqlDatabase::connectionNames().isEmpty()
        && existingDatabase->isValid() && existingDatabase->isOpen();
 
    if (databaseConnectionExists && existingDatabase->tables().contains("version_information")) {
        return QSqlError();
    }
 
    existingDatabase = std::nullopt;
 
    QSqlDatabase db;
 
    if (!databaseConnectionExists) {
        db = QSqlDatabase::addDatabase(dbDriver);
        db.setDatabaseName(location + "/" + KisResourceCacheDb::resourceCacheDbFilename);
 
        if (!db.open()) {
            warnDbMigration << "Could not connect to resource cache database";
            return db.lastError();
        }
    } else {
        db = QSqlDatabase::database();
    }
 
    // will be filled correctly later
    QVersionNumber oldSchemaVersionNumber;
    QVersionNumber newSchemaVersionNumber = QVersionNumber::fromString(KisResourceCacheDb::databaseVersion);
 
 
    QStringList tables = QStringList() << "version_information"
                                       << "storage_types"
                                       << "resource_types"
                                       << "storages"
                                       << "tags"
                                       << "resources"
                                       << "versioned_resources"
                                       << "resource_tags"
                                       << "metadata"
                                       << "tags_storages"
                                       << "tag_translations";
 
    QStringList dbTables;
    // Verify whether we should recreate the database
    {
        bool allTablesPresent = true;
        dbTables = db.tables();
        Q_FOREACH(const QString &table, tables) {
            if (!dbTables.contains(table)) {
                allTablesPresent = false;
                break;
            }
        }
 
        bool schemaIsOutDated = false;
        QString schemaVersion = "0.0.0";
        QString kritaVersion = "Unknown";
        int creationDate = 0;
 
        if (dbTables.contains("version_information")) {
            // Verify the version number
 
            {
                QSqlQuery q(
                    "SELECT database_version\n"
                    ",      krita_version\n"
                    ",      creation_date\n"
                    "FROM version_information\n"
                    "ORDER BY id\n"
                    "DESC\n"
                    "LIMIT 1;\n");
 
                if (!q.exec()) {
                    warnDbMigration << "Could not retrieve version information from the database." << q.lastError();
                    abort();
                }
                q.first();
                schemaVersion = q.value(0).toString();
                kritaVersion = q.value(1).toString();
                creationDate = q.value(2).toInt();
            }
 
            oldSchemaVersionNumber = QVersionNumber::fromString(schemaVersion);
            newSchemaVersionNumber = QVersionNumber::fromString(KisResourceCacheDb::databaseVersion);
 
            if (QVersionNumber::compare(oldSchemaVersionNumber, newSchemaVersionNumber) != 0) {
 
                infoDbMigration << "Old schema:" << schemaVersion << "New schema:" << newSchemaVersionNumber;
 
                schemaIsOutDated = true;
                KisBackup::numberedBackupFile(location + "/" + KisResourceCacheDb::resourceCacheDbFilename);
 
                if (newSchemaVersionNumber == QVersionNumber::fromString("0.0.18")
                        && QVersionNumber::compare(oldSchemaVersionNumber, QVersionNumber::fromString("0.0.14")) >= 0
                        && QVersionNumber::compare(oldSchemaVersionNumber, QVersionNumber::fromString("0.0.18")) < 0) {
 
                    bool from14to15 = oldSchemaVersionNumber == QVersionNumber::fromString("0.0.14");
 
                    bool from15to16 = oldSchemaVersionNumber == QVersionNumber::fromString("0.0.14")
                            || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.15");
 
                    bool from16to17 = oldSchemaVersionNumber == QVersionNumber::fromString("0.0.14")
                            || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.15")
                            || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.16");
 
                    bool from17to18 = oldSchemaVersionNumber == QVersionNumber::fromString("0.0.14")
                            || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.15")
                            || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.16")
                            || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.17");
 
                    KisDatabaseTransactionLock transactionLock(QSqlDatabase::database());
 
                    bool success = true;
                    if (from14to15) {
                        QSqlError error = runUpdateScript(
                            "ALTER TABLE  resource_tags\n"
                            "ADD   COLUMN active INTEGER NOT NULL DEFAULT 1", 
                            "Update resource tags table (add \'active\' column)");
                        if (error.type() != QSqlError::NoError) {
                            success = false;
                        }
                    }
                    if (success && from15to16) {
                        infoDbMigration << "Going to update indices";
 
                        QStringList indexes = QStringList() << "tags" << "resources" << "tag_translations" << "resource_tags";
 
                        Q_FOREACH(const QString &index, indexes) {
                            QSqlError error = runUpdateScriptFile(":/create_index_" + index + ".sql",
                                                                  QString("Create index for %1").arg(index));
                            if (error.type() != QSqlError::NoError) {
                                success = false;
                            }
                        }
                    }
 
                    if (success && from16to17) {
                        QSqlError error = runUpdateScriptFile(":/create_index_resources_signature.sql",
                                                              "Create index for resources_signature");
                        if (error.type() != QSqlError::NoError) {
                            success = false;
                        }
                    }
 
                    if (success && from17to18) {
                        {
                            QSqlError error = runUpdateScriptFile(":/0_0_18_0001_cleanup_metadata_table.sql",
                                                                  "Cleanup and deduplicate metadata table");
                            if (error.type() != QSqlError::NoError) {
                                success = false;
                            }
                        }
                        if (success) {
                            QSqlError error = runUpdateScriptFile(":/0_0_18_0002_update_metadata_table_constraints.sql",
                                                                  "Update metadata table constraints");
                            if (error.type() != QSqlError::NoError) {
                                success = false;
                            }
                        }
                        if (success) {
                            QSqlError error = runUpdateScriptFile(":/create_index_metadata_key.sql",
                                                                  "Create index for metadata_key");
                            if (error.type() != QSqlError::NoError) {
                                success = false;
                            }
                        }
                    }
 
                    if (success) {
                        if (!updateSchemaVersion()) {
                            success = false;
                        }
 
                        transactionLock.commit();
 
                        if (success) {
                            QSqlError error = runUpdateScript("VACUUM",
                                                              "Vacuum database after updating schema");
                            if (error.type() != QSqlError::NoError) {
                                success = false;
                            }
                        }
                    } else {
                        transactionLock.rollback();
                    }
 
                    schemaIsOutDated = !success;
 
                }
 
                if (schemaIsOutDated) {
                    QMessageBox::critical(0, i18nc("@title:window", "Krita"), i18n("The resource database scheme has changed. Krita will backup your database and create a new database."));
                    if (QVersionNumber::compare(oldSchemaVersionNumber, QVersionNumber::fromString("0.0.14")) > 0) {
                        KisResourceLocator::instance()->saveTags();
                    }
                    db.close();
                    QFile::remove(location + "/" + KisResourceCacheDb::resourceCacheDbFilename);
                    db.open();
                }
            }
 
        }
 
        if (allTablesPresent && !schemaIsOutDated) {
            KisUsageLogger::log(QString("Database is up to date. Version: %1, created by Krita %2, at %3")
                                .arg(schemaVersion)
                                .arg(kritaVersion)
                                .arg(QDateTime::fromSecsSinceEpoch(creationDate).toString()));
 
            KisResourceCacheDb::synchronizeForeignKeysState();
 
            return QSqlError();
        }
    }
 
    KisUsageLogger::log(QString("Creating database from scratch (%1, %2).")
                        .arg(oldSchemaVersionNumber.toString().isEmpty() ? QString("database didn't exist") : ("old schema version: " + oldSchemaVersionNumber.toString()))
                        .arg("new schema version: " + newSchemaVersionNumber.toString()));
 
    KisDatabaseTransactionLock transactionLock(QSqlDatabase::database());
 
    // Create tables
    Q_FOREACH(const QString &table, tables) {
        QSqlError error =
            runUpdateScriptFile(":/create_" + table + ".sql", QString("Create table %1").arg(table));
        if (error.type() != QSqlError::NoError) {
            return error;
        }
    }
 
    {
        // metadata table constraints were updated in version 0.0.18
        QSqlError error = runUpdateScriptFile(":/0_0_18_0002_update_metadata_table_constraints.sql",
                                              "Update metadata table constraints");
 
        if (error.type() != QSqlError::NoError) {
            return error;
        }
    }
 
    // Create indexes
    QStringList indexes;
 
    // these indexes came in version 0.0.16
    indexes << "storages" << "versioned_resources" << "tags" << "resources" << "tag_translations" << "resource_tags";
 
    // this index came in version 0.0.17
    indexes << "resources_signature";
 
    // this index came in version 0.0.18
    indexes << "metadata_key";
 
    Q_FOREACH(const QString &index, indexes) {
        QSqlError error = runUpdateScriptFile(":/create_index_" + index + ".sql",
                                              QString("Create index for %1").arg(index));
        if (error.type() != QSqlError::NoError) {
            return error;
        }
    }
 
    // Fill lookup tables
    {
        QFile f(":/fill_storage_types.sql");
        if (f.open(QFile::ReadOnly)) {
            QString sql = f.readAll();
            Q_FOREACH(const QString &originType, KisResourceCacheDb::storageTypes) {
                const QString updateStep = QString("Register storage type: %1").arg(originType);
                QSqlQuery q(sql);
                q.addBindValue(originType);
                if (!q.exec()) {
                    warnDbMigration << "Could execute DB update step:" << updateStep << q.lastError();
                    warnDbMigration << "    faulty statement:" << sql;
                    return db.lastError();
                }
                infoDbMigration << "Completed DB update step:" << updateStep;
            }
        }
        else {
            return QSqlError("Error executing SQL", QString("Could not find SQL fill_storage_types.sql."), QSqlError::StatementError);
        }
    }
 
    {
        QFile f(":/fill_resource_types.sql");
        if (f.open(QFile::ReadOnly)) {
            QString sql = f.readAll();
            Q_FOREACH(const QString &resourceType, KisResourceLoaderRegistry::instance()->resourceTypes()) {
                const QString updateStep = QString("Register resource type: %1").arg(resourceType);
                QSqlQuery q(sql);
                q.addBindValue(resourceType);
                if (!q.exec()) {
                    warnDbMigration << "Could execute DB update step:" << updateStep << q.lastError();
                    warnDbMigration << "    faulty statement:" << sql;
                    return db.lastError();
                }
                infoDbMigration << "Completed DB update step:" << updateStep;
            }
        }
        else {
            return QSqlError("Error executing SQL", QString("Could not find SQL fill_resource_types.sql."), QSqlError::StatementError);
        }
    }
 
    if (!updateSchemaVersion()) {
       return QSqlError("Error executing SQL", QString("Could not update schema version."), QSqlError::StatementError);
    }
 
    transactionLock.commit();
 
    KisResourceCacheDb::synchronizeForeignKeysState();
 
    return QSqlError();
}

References detail::KisDatabaseTransactionLockAdapter::commit(), KisResourceCacheDb::databaseVersion, dbDriver, infoDbMigration, KisResourceLoaderRegistry::instance(), KisResourceLocator::instance(), KisUsageLogger::log(), KisBackup::numberedBackupFile(), KisResourceCacheDb::resourceCacheDbFilename, KisDatabaseTransactionLock::rollback(), runUpdateScript(), runUpdateScriptFile(), KisResourceLocator::saveTags(), KisResourceCacheDb::storageTypes, KisResourceStorage::storageTypeToUntranslatedString(), KisResourceCacheDb::synchronizeForeignKeysState(), updateSchemaVersion(), and warnDbMigration.

runUpdateScript()

QSqlError runUpdateScript	(	const QString &	script,
		const QString &	message )

Definition at line 124 of file KisResourceCacheDb.cpp.

{
    try {
 
        KisSqlQueryLoader loader("", script);
        loader.exec();
 
    } catch (const KisSqlQueryLoader::SQLException &e) {
        warnDbMigration.noquote() << "ERROR: Could execute DB update step:" << message;
        warnDbMigration.noquote() << "       error" << e.message;
        warnDbMigration.noquote() << "       sql-error:" << e.sqlError.text();
        return e.sqlError;
    }
 
    infoDbMigration << "Completed DB update step:" << message;
    return QSqlError();
}

References KisSqlQueryLoader::exec(), infoDbMigration, KisSqlQueryLoader::SQLException::message, KisSqlQueryLoader::SQLException::sqlError, and warnDbMigration.

runUpdateScriptFile()

QSqlError runUpdateScriptFile	(	const QString &	path,
		const QString &	message )

Definition at line 95 of file KisResourceCacheDb.cpp.

{
    try {
 
        KisSqlQueryLoader loader(path);
        loader.exec();
 
    } catch (const KisSqlQueryLoader::FileException &e) {
        warnDbMigration.noquote() << "ERROR: Could not execute DB update step:" << message;
        warnDbMigration.noquote() << "       error" << e.message;
        warnDbMigration.noquote() << "       file:" << e.filePath;
        warnDbMigration.noquote() << "       file-error:" << e.fileErrorString;
        return
            QSqlError("Error executing SQL",
                QString("Could not find SQL file %1").arg(e.filePath),
                QSqlError::StatementError);
    } catch (const KisSqlQueryLoader::SQLException &e) {
        warnDbMigration.noquote() << "ERROR: Could not execute DB update step:" << message;
        warnDbMigration.noquote() << "       error" << e.message;
        warnDbMigration.noquote() << "       file:" << e.filePath;
        warnDbMigration.noquote() << "       statement:" << e.statementIndex;
        warnDbMigration.noquote() << "       sql-error:" << e.sqlError.text();
        return e.sqlError;
    }
 
    infoDbMigration << "Completed DB update step:" << message;
    return QSqlError();
}

References KisSqlQueryLoader::exec(), KisSqlQueryLoader::FileException::fileErrorString, KisSqlQueryLoader::FileException::filePath, infoDbMigration, KisSqlQueryLoader::FileException::message, and warnDbMigration.

updateSchemaVersion()

bool updateSchemaVersion

(

)

Definition at line 72 of file KisResourceCacheDb.cpp.

{
    QFile f(":/fill_version_information.sql");
    if (f.open(QFile::ReadOnly)) {
        QString sql = f.readAll();
        QSqlQuery q;
        if (!q.prepare(sql)) {
            warnDbMigration << "Could not prepare the schema information query" << q.lastError() << q.boundValues();
            return false;
        }
        q.addBindValue(KisResourceCacheDb::databaseVersion);
        q.addBindValue(KritaVersionWrapper::versionString());
        q.addBindValue(QDateTime::currentDateTimeUtc().toSecsSinceEpoch());
        if (!q.exec()) {
            warnDbMigration << "Could not insert the current version" << q.lastError() << q.boundValues();
            return false;
        }
 
        infoDbMigration << "Filled version table";
    }
    return true;
}

References KisResourceCacheDb::databaseVersion, infoDbMigration, KritaVersionWrapper::versionString(), and warnDbMigration.

Variable Documentation

dbDriver

const QString dbDriver = "QSQLITE"

Definition at line 41 of file KisResourceCacheDb.cpp.

METADATA_RESOURCES

const QString METADATA_RESOURCES = "resources"

Definition at line 42 of file KisResourceCacheDb.cpp.

METADATA_STORAGES

const QString METADATA_STORAGES = "storages"

Definition at line 43 of file KisResourceCacheDb.cpp.