File indexing completed on 2024-05-12 15:59:49
0001 /* 0002 * SPDX-FileCopyrightText: 2018 Boudewijn Rempt <boud@valdyas.org> 0003 * 0004 * SPDX-License-Identifier: LGPL-2.0-or-later 0005 */ 0006 #include "KisResourceCacheDb.h" 0007 0008 #include <QtSql> 0009 #include <QStandardPaths> 0010 #include <QDir> 0011 #include <QDirIterator> 0012 #include <QStringList> 0013 #include <QElapsedTimer> 0014 #include <QDataStream> 0015 #include <QByteArray> 0016 #include <QMessageBox> 0017 0018 #include <KritaVersionWrapper.h> 0019 0020 #include <klocalizedstring.h> 0021 #include <KisBackup.h> 0022 0023 #include <kis_debug.h> 0024 #include <KisUsageLogger.h> 0025 0026 #include "KisResourceLocator.h" 0027 #include "KisResourceLoaderRegistry.h" 0028 0029 #include "ResourceDebug.h" 0030 #include <kis_assert.h> 0031 0032 #include <KisCppQuirks.h> 0033 0034 const QString dbDriver = "QSQLITE"; 0035 0036 const QString KisResourceCacheDb::resourceCacheDbFilename { "resourcecache.sqlite" }; 0037 const QString KisResourceCacheDb::databaseVersion { "0.0.17" }; 0038 QStringList KisResourceCacheDb::storageTypes { QStringList() }; 0039 QStringList KisResourceCacheDb::disabledBundles { QStringList() << "Krita_3_Default_Resources.bundle" }; 0040 0041 bool KisResourceCacheDb::s_valid {false}; 0042 QString KisResourceCacheDb::s_lastError {QString()}; 0043 0044 bool KisResourceCacheDb::isValid() 0045 { 0046 return s_valid; 0047 } 0048 0049 QString KisResourceCacheDb::lastError() 0050 { 0051 return s_lastError; 0052 } 0053 0054 // use in WHERE QSqlQuery clauses 0055 // because if the string is null, the query will also have null there 0056 // and every comparison with null is false, so the query won't find anything 0057 // (especially important for storage location where empty string is common) 0058 QString changeToEmptyIfNull(QString s) 0059 { 0060 return s.isNull() ? QString("") : s; 0061 } 0062 0063 bool updateSchemaVersion() 0064 { 0065 QFile f(":/fill_version_information.sql"); 0066 if (f.open(QFile::ReadOnly)) { 0067 QString sql = f.readAll(); 0068 QSqlQuery q; 0069 q.prepare(sql); 0070 q.addBindValue(KisResourceCacheDb::databaseVersion); 0071 q.addBindValue(KritaVersionWrapper::versionString()); 0072 q.addBindValue(QDateTime::currentDateTimeUtc().toSecsSinceEpoch()); 0073 if (!q.exec()) { 0074 qWarning() << "Could not insert the current version" << q.lastError() << q.boundValues(); 0075 return false; 0076 } 0077 infoResources << "Filled version table"; 0078 } 0079 return true; 0080 } 0081 0082 0083 0084 QSqlError createDatabase(const QString &location) 0085 { 0086 // NOTE: if the id's of Unknown and Memory in the database 0087 // will change, and that will break the queries that 0088 // remove Unknown and Memory storages on start-up. 0089 KisResourceCacheDb::storageTypes << KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(1)) 0090 << KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(2)) 0091 << KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(3)) 0092 << KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(4)) 0093 << KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(5)) 0094 << KisResourceStorage::storageTypeToUntranslatedString(KisResourceStorage::StorageType(6)) 0095 ; 0096 0097 if (!QSqlDatabase::connectionNames().isEmpty()) { 0098 return QSqlError(); 0099 } 0100 0101 QDir dbLocation(location); 0102 if (!dbLocation.exists()) { 0103 dbLocation.mkpath(dbLocation.path()); 0104 } 0105 0106 QSqlDatabase db = QSqlDatabase::addDatabase(dbDriver); 0107 db.setDatabaseName(location + "/" + KisResourceCacheDb::resourceCacheDbFilename); 0108 0109 if (!db.open()) { 0110 qWarning() << "Could not connect to resource cache database"; 0111 return db.lastError(); 0112 } 0113 0114 // will be filled correctly later 0115 QVersionNumber oldSchemaVersionNumber; 0116 QVersionNumber newSchemaVersionNumber = QVersionNumber::fromString(KisResourceCacheDb::databaseVersion); 0117 0118 0119 QStringList tables = QStringList() << "version_information" 0120 << "storage_types" 0121 << "resource_types" 0122 << "storages" 0123 << "tags" 0124 << "resources" 0125 << "versioned_resources" 0126 << "resource_tags" 0127 << "metadata" 0128 << "tags_storages" 0129 << "tag_translations"; 0130 0131 QStringList dbTables; 0132 // Verify whether we should recreate the database 0133 { 0134 bool allTablesPresent = true; 0135 dbTables = db.tables(); 0136 Q_FOREACH(const QString &table, tables) { 0137 if (!dbTables.contains(table)) { 0138 allTablesPresent = false; 0139 break; 0140 } 0141 } 0142 0143 bool schemaIsOutDated = false; 0144 QString schemaVersion = "0.0.0"; 0145 QString kritaVersion = "Unknown"; 0146 int creationDate = 0; 0147 0148 if (dbTables.contains("version_information")) { 0149 // Verify the version number 0150 0151 QSqlQuery q("SELECT database_version\n" 0152 ", krita_version\n" 0153 ", creation_date\n" 0154 "FROM version_information\n" 0155 "ORDER BY id\n" 0156 "DESC\n" 0157 "LIMIT 1;\n"); 0158 0159 if (!q.exec()) { 0160 qWarning() << "Could not retrieve version information from the database." << q.lastError(); 0161 abort(); 0162 } 0163 q.first(); 0164 schemaVersion = q.value(0).toString(); 0165 kritaVersion = q.value(1).toString(); 0166 creationDate = q.value(2).toInt(); 0167 0168 oldSchemaVersionNumber = QVersionNumber::fromString(schemaVersion); 0169 newSchemaVersionNumber = QVersionNumber::fromString(KisResourceCacheDb::databaseVersion); 0170 0171 if (QVersionNumber::compare(oldSchemaVersionNumber, newSchemaVersionNumber) != 0) { 0172 0173 qWarning() << "Old schema:" << schemaVersion << "New schema:" << newSchemaVersionNumber; 0174 0175 schemaIsOutDated = true; 0176 KisBackup::numberedBackupFile(location + "/" + KisResourceCacheDb::resourceCacheDbFilename); 0177 0178 if (newSchemaVersionNumber == QVersionNumber::fromString("0.0.17") 0179 && QVersionNumber::compare(oldSchemaVersionNumber, QVersionNumber::fromString("0.0.14")) > 0 0180 && QVersionNumber::compare(oldSchemaVersionNumber, QVersionNumber::fromString("0.0.17")) < 0) { 0181 bool from14to15 = oldSchemaVersionNumber == QVersionNumber::fromString("0.0.14"); 0182 bool from15to16 = oldSchemaVersionNumber == QVersionNumber::fromString("0.0.14") 0183 || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.15"); 0184 bool from16to17 = oldSchemaVersionNumber == QVersionNumber::fromString("0.0.14") 0185 || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.15") 0186 || oldSchemaVersionNumber == QVersionNumber::fromString("0.0.16"); 0187 0188 bool success = true; 0189 if (from14to15) { 0190 qWarning() << "Going to update resource_tags table"; 0191 0192 QSqlQuery q; 0193 q.prepare("ALTER TABLE resource_tags\n" 0194 "ADD COLUMN active INTEGER NOT NULL DEFAULT 1"); 0195 if (!q.exec()) { 0196 qWarning() << "Could not update the resource_tags table." << q.lastError(); 0197 success = false; 0198 } 0199 else { 0200 qWarning() << "Updated table resource_tags: success."; 0201 } 0202 } 0203 if (from15to16) { 0204 qWarning() << "Going to update indices"; 0205 0206 QStringList indexes = QStringList() << "tags" << "resources" << "tag_translations" << "resource_tags"; 0207 0208 Q_FOREACH(const QString &index, indexes) { 0209 QFile f(":/create_index_" + index + ".sql"); 0210 if (f.open(QFile::ReadOnly)) { 0211 QSqlQuery q; 0212 if (!q.exec(f.readAll())) { 0213 qWarning() << "Could not create index" << index << q.lastError(); 0214 return db.lastError(); 0215 } 0216 infoResources << "Created index" << index; 0217 } 0218 else { 0219 return QSqlError("Error executing SQL", QString("Could not find SQL file %1").arg(index), QSqlError::StatementError); 0220 } 0221 } 0222 } 0223 0224 if (from16to17) { 0225 qWarning() << "Going to update resource signature index"; 0226 0227 QFile f(":/create_index_resources_signature.sql"); 0228 if (f.open(QFile::ReadOnly)) { 0229 QSqlQuery q; 0230 if (!q.exec(f.readAll())) { 0231 qWarning() << "Could not create index for resources signature" << q.lastError(); 0232 return db.lastError(); 0233 } 0234 infoResources << "Created resources signature index"; 0235 } 0236 else { 0237 return QSqlError("Error executing SQL", QString("Could not find SQL file for resources signature index"), QSqlError::StatementError); 0238 } 0239 } 0240 0241 if (success) { 0242 if (!updateSchemaVersion()) { 0243 return QSqlError("Error executing SQL", QString("Could not update schema version."), QSqlError::StatementError); 0244 } 0245 } 0246 0247 schemaIsOutDated = !success; 0248 0249 } 0250 0251 if (schemaIsOutDated) { 0252 QMessageBox::critical(0, i18nc("@title:window", "Krita"), i18n("The resource database scheme has changed. Krita will backup your database and create a new database.")); 0253 if (QVersionNumber::compare(oldSchemaVersionNumber, QVersionNumber::fromString("0.0.14")) > 0) { 0254 KisResourceLocator::instance()->saveTags(); 0255 } 0256 db.close(); 0257 QFile::remove(location + "/" + KisResourceCacheDb::resourceCacheDbFilename); 0258 db.open(); 0259 } 0260 } 0261 0262 } 0263 0264 if (allTablesPresent && !schemaIsOutDated) { 0265 KisUsageLogger::log(QString("Database is up to date. Version: %1, created by Krita %2, at %3") 0266 .arg(schemaVersion) 0267 .arg(kritaVersion) 0268 .arg(QDateTime::fromSecsSinceEpoch(creationDate).toString())); 0269 return QSqlError(); 0270 } 0271 } 0272 0273 KisUsageLogger::log(QString("Creating database from scratch (%1, %2).") 0274 .arg(oldSchemaVersionNumber.toString().isEmpty() ? QString("database didn't exist") : ("old schema version: " + oldSchemaVersionNumber.toString())) 0275 .arg("new schema version: " + newSchemaVersionNumber.toString())); 0276 0277 // Create tables 0278 Q_FOREACH(const QString &table, tables) { 0279 QFile f(":/create_" + table + ".sql"); 0280 if (f.open(QFile::ReadOnly)) { 0281 QSqlQuery q; 0282 if (!q.exec(f.readAll())) { 0283 qWarning() << "Could not create table" << table << q.lastError(); 0284 return db.lastError(); 0285 } 0286 infoResources << "Created table" << table; 0287 } 0288 else { 0289 return QSqlError("Error executing SQL", QString("Could not find SQL file %1").arg(table), QSqlError::StatementError); 0290 } 0291 } 0292 0293 // Create indexes 0294 QStringList indexes; 0295 0296 // these indexes came in version 0.0.16 0297 indexes << "storages" << "versioned_resources" << "tags" << "resources" << "tag_translations" << "resource_tags"; 0298 0299 // this indexes came in version 0.0.17 0300 indexes << "resources_signature"; 0301 0302 Q_FOREACH(const QString &index, indexes) { 0303 QFile f(":/create_index_" + index + ".sql"); 0304 if (f.open(QFile::ReadOnly)) { 0305 QSqlQuery q; 0306 if (!q.exec(f.readAll())) { 0307 qWarning() << "Could not create index" << index; 0308 return db.lastError(); 0309 } 0310 infoResources << "Created table" << index; 0311 } 0312 else { 0313 return QSqlError("Error executing SQL", QString("Could not find SQL file %1").arg(index), QSqlError::StatementError); 0314 } 0315 } 0316 0317 // Fill lookup tables 0318 { 0319 if (dbTables.contains("storage_types")) { 0320 QSqlQuery q; 0321 if (!q.exec("DELETE FROM storage_types;")) { 0322 qWarning() << "Could not clear table storage_types" << db.lastError(); 0323 } 0324 } 0325 0326 QFile f(":/fill_storage_types.sql"); 0327 if (f.open(QFile::ReadOnly)) { 0328 QString sql = f.readAll(); 0329 Q_FOREACH(const QString &originType, KisResourceCacheDb::storageTypes) { 0330 QSqlQuery q(sql); 0331 q.addBindValue(originType); 0332 if (!q.exec()) { 0333 qWarning() << "Could not insert" << originType << db.lastError() << q.executedQuery(); 0334 return db.lastError(); 0335 } 0336 } 0337 infoResources << "Filled lookup table storage_types"; 0338 } 0339 else { 0340 return QSqlError("Error executing SQL", QString("Could not find SQL fill_storage_types.sql."), QSqlError::StatementError); 0341 } 0342 } 0343 0344 { 0345 if (dbTables.contains("resource_types")) { 0346 QSqlQuery q; 0347 if (!q.exec("DELETE FROM resource_types;")) { 0348 qWarning() << "Could not clear table resource_types" << db.lastError(); 0349 } 0350 } 0351 QFile f(":/fill_resource_types.sql"); 0352 if (f.open(QFile::ReadOnly)) { 0353 QString sql = f.readAll(); 0354 Q_FOREACH(const QString &resourceType, KisResourceLoaderRegistry::instance()->resourceTypes()) { 0355 QSqlQuery q(sql); 0356 q.addBindValue(resourceType); 0357 if (!q.exec()) { 0358 qWarning() << "Could not insert" << resourceType << db.lastError() << q.executedQuery(); 0359 return db.lastError(); 0360 } 0361 } 0362 infoResources << "Filled lookup table resource_types"; 0363 } 0364 else { 0365 return QSqlError("Error executing SQL", QString("Could not find SQL fill_resource_types.sql."), QSqlError::StatementError); 0366 } 0367 } 0368 0369 if (!updateSchemaVersion()) { 0370 return QSqlError("Error executing SQL", QString("Could not update schema version."), QSqlError::StatementError); 0371 } 0372 0373 return QSqlError(); 0374 } 0375 0376 bool KisResourceCacheDb::initialize(const QString &location) 0377 { 0378 QSqlError err = createDatabase(location); 0379 0380 s_valid = !err.isValid(); 0381 switch (err.type()) { 0382 case QSqlError::NoError: 0383 s_lastError = QString(); 0384 break; 0385 case QSqlError::ConnectionError: 0386 s_lastError = QString("Could not initialize the resource cache database. Connection error: %1").arg(err.text()); 0387 break; 0388 case QSqlError::StatementError: 0389 s_lastError = QString("Could not initialize the resource cache database. Statement error: %1").arg(err.text()); 0390 break; 0391 case QSqlError::TransactionError: 0392 s_lastError = QString("Could not initialize the resource cache database. Transaction error: %1").arg(err.text()); 0393 break; 0394 case QSqlError::UnknownError: 0395 s_lastError = QString("Could not initialize the resource cache database. Unknown error: %1").arg(err.text()); 0396 break; 0397 } 0398 0399 // Delete all storages that are no longer known to the resource locator (including the memory storages) 0400 deleteTemporaryResources(); 0401 0402 return s_valid; 0403 } 0404 0405 QVector<int> KisResourceCacheDb::resourcesForStorage(const QString &resourceType, const QString &storageLocation) 0406 { 0407 QVector<int> result; 0408 0409 QSqlQuery q; 0410 0411 if (!q.prepare("SELECT resources.id\n" 0412 "FROM resources\n" 0413 ", resource_types\n" 0414 ", storages\n" 0415 "WHERE resources.resource_type_id = resource_types.id\n" 0416 "AND storages.id = resources.storage_id\n" 0417 "AND storages.location = :storage_location\n" 0418 "AND resource_types.name = :resource_type\n")) { 0419 0420 qWarning() << "Could not read and prepare resourcesForStorage" << q.lastError(); 0421 return result; 0422 } 0423 0424 q.bindValue(":resource_type", resourceType); 0425 q.bindValue(":storage_location", changeToEmptyIfNull(storageLocation)); 0426 0427 if (!q.exec()) { 0428 qWarning() << "Could not query resourceIdForResource" << q.boundValues() << q.lastError(); 0429 return result; 0430 } 0431 0432 while (q.next()) { 0433 result << q.value(0).toInt(); 0434 } 0435 0436 return result; 0437 } 0438 0439 int KisResourceCacheDb::resourceIdForResource(const QString &resourceFileName, const QString &resourceType, const QString &storageLocation) 0440 { 0441 //qDebug() << "resourceIdForResource" << resourceName << resourceFileName << resourceType << storageLocation; 0442 0443 QSqlQuery q; 0444 0445 if (!q.prepare("SELECT resources.id\n" 0446 "FROM resources\n" 0447 ", resource_types\n" 0448 ", storages\n" 0449 "WHERE resources.resource_type_id = resource_types.id\n" 0450 "AND storages.id = resources.storage_id\n" 0451 "AND storages.location = :storage_location\n" 0452 "AND resource_types.name = :resource_type\n" 0453 "AND resources.filename = :filename\n")) { 0454 qWarning() << "Could not read and prepare resourceIdForResource" << q.lastError(); 0455 return -1; 0456 } 0457 0458 q.bindValue(":filename", resourceFileName); 0459 q.bindValue(":resource_type", resourceType); 0460 q.bindValue(":storage_location", changeToEmptyIfNull(storageLocation)); 0461 0462 if (!q.exec()) { 0463 qWarning() << "Could not query resourceIdForResource" << q.boundValues() << q.lastError(); 0464 return -1; 0465 } 0466 0467 if (q.first()) { 0468 return q.value(0).toInt(); 0469 } 0470 0471 // couldn't be found in the `resources` table, but can still be in versioned_resources 0472 0473 if (!q.prepare("SELECT versioned_resources.resource_id\n" 0474 "FROM resources\n" 0475 ", resource_types\n" 0476 ", versioned_resources\n" 0477 ", storages\n" 0478 "WHERE resources.resource_type_id = resource_types.id\n" // join resources and resource_types by resource id 0479 "AND versioned_resources.resource_id = resources.id\n" // join versioned_resources and resources by resource id 0480 "AND storages.id = versioned_resources.storage_id\n" // join storages and versioned_resources by storage id 0481 "AND storages.location = :storage_location\n" // storage location must be the same as asked for 0482 "AND resource_types.name = :resource_type\n" // resource type must be the same as asked for 0483 "AND versioned_resources.filename = :filename\n")) { // filename must be the same as asked for 0484 qWarning() << "Could not read and prepare resourceIdForResource (in versioned resources)" << q.lastError(); 0485 return -1; 0486 } 0487 0488 q.bindValue(":filename", resourceFileName); 0489 q.bindValue(":resource_type", resourceType); 0490 q.bindValue(":storage_location", changeToEmptyIfNull(storageLocation)); 0491 0492 if (!q.exec()) { 0493 qWarning() << "Could not query resourceIdForResource (in versioned resources)" << q.boundValues() << q.lastError(); 0494 return -1; 0495 } 0496 0497 if (q.first()) { 0498 return q.value(0).toInt(); 0499 } 0500 0501 // commenting out, because otherwise it spams the console on every new resource in the local resources folder 0502 // qWarning() << "Could not find resource" << resourceName << resourceFileName << resourceType << storageLocation; 0503 return -1; 0504 0505 } 0506 0507 bool KisResourceCacheDb::resourceNeedsUpdating(int resourceId, QDateTime timestamp) 0508 { 0509 QSqlQuery q; 0510 if (!q.prepare("SELECT timestamp\n" 0511 "FROM versioned_resources\n" 0512 "WHERE resource_id = :resource_id\n" 0513 "AND version = (SELECT MAX(version)\n" 0514 " FROM versioned_resources\n" 0515 " WHERE resource_id = :resource_id);")) { 0516 qWarning() << "Could not prepare resourceNeedsUpdating statement" << q.lastError(); 0517 return false; 0518 } 0519 0520 q.bindValue(":resource_id", resourceId); 0521 0522 if (!q.exec()) { 0523 qWarning() << "Could not query for the most recent timestamp" << q.boundValues() << q.lastError(); 0524 return false; 0525 } 0526 0527 if (!q.first()) { 0528 qWarning() << "Inconsistent database: could not find a version for resource with Id" << resourceId; 0529 return false; 0530 } 0531 0532 QVariant resourceTimeStamp = q.value(0); 0533 0534 if (!resourceTimeStamp.isValid()) { 0535 qWarning() << "Could not retrieve timestamp from versioned_resources" << resourceId; 0536 return false; 0537 } 0538 0539 return (timestamp.toSecsSinceEpoch() > resourceTimeStamp.toInt()); 0540 } 0541 0542 bool KisResourceCacheDb::addResourceVersion(int resourceId, QDateTime timestamp, KisResourceStorageSP storage, KoResourceSP resource) 0543 { 0544 bool r = false; 0545 0546 0547 r = addResourceVersionImpl(resourceId, timestamp, storage, resource); 0548 0549 if (!r) return r; 0550 0551 r = makeResourceTheCurrentVersion(resourceId, resource); 0552 0553 return r; 0554 } 0555 0556 bool KisResourceCacheDb::addResourceVersionImpl(int resourceId, QDateTime timestamp, KisResourceStorageSP storage, KoResourceSP resource) 0557 { 0558 bool r = false; 0559 0560 // Create the new version. The resource is expected to have an updated version number, or 0561 // this will fail on the unique index on resource_id, storage_id and version. 0562 // 0563 // This function **only** adds to the versioned_resources table. 0564 // The resources table should be updated by the caller manually using 0565 // updateResourceTableForResourceIfNeeded() 0566 0567 Q_ASSERT(resource->version() >= 0); 0568 0569 QSqlQuery q; 0570 r = q.prepare("INSERT INTO versioned_resources \n" 0571 "(resource_id, storage_id, version, filename, timestamp, md5sum)\n" 0572 "VALUES\n" 0573 "( :resource_id\n" 0574 ", (SELECT id \n" 0575 " FROM storages \n" 0576 " WHERE location = :storage_location)\n" 0577 ", :version\n" 0578 ", :filename\n" 0579 ", :timestamp\n" 0580 ", :md5sum\n" 0581 ");"); 0582 0583 if (!r) { 0584 qWarning() << "Could not prepare addResourceVersion statement" << q.lastError(); 0585 return r; 0586 } 0587 0588 q.bindValue(":resource_id", resourceId); 0589 q.bindValue(":storage_location", changeToEmptyIfNull(KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()))); 0590 q.bindValue(":version", resource->version()); 0591 q.bindValue(":filename", resource->filename()); 0592 q.bindValue(":timestamp", timestamp.toSecsSinceEpoch()); 0593 KIS_SAFE_ASSERT_RECOVER_NOOP(!resource->md5Sum().isEmpty()); 0594 q.bindValue(":md5sum", resource->md5Sum()); 0595 r = q.exec(); 0596 if (!r) { 0597 0598 qWarning() << "Could not execute addResourceVersionImpl statement" << q.lastError() << resourceId << storage->name() << storage->location() << resource->name() << resource->filename() << "version" << resource->version(); 0599 return r; 0600 } 0601 0602 return r; 0603 } 0604 0605 bool KisResourceCacheDb::removeResourceVersionImpl(int resourceId, int version, KisResourceStorageSP storage) 0606 { 0607 bool r = false; 0608 0609 // Remove a version of the resource. This function **only** removes data from 0610 // the versioned_resources table. The resources table should be updated by 0611 // the caller manually using updateResourceTableForResourceIfNeeded() 0612 0613 QSqlQuery q; 0614 r = q.prepare("DELETE FROM versioned_resources \n" 0615 "WHERE resource_id = :resource_id\n" 0616 "AND version = :version\n" 0617 "AND storage_id = (SELECT id \n" 0618 " FROM storages \n" 0619 " WHERE location = :storage_location);"); 0620 0621 if (!r) { 0622 qWarning() << "Could not prepare removeResourceVersionImpl statement" << q.lastError(); 0623 return r; 0624 } 0625 0626 q.bindValue(":resource_id", resourceId); 0627 q.bindValue(":storage_location", changeToEmptyIfNull(KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()))); 0628 q.bindValue(":version", version); 0629 r = q.exec(); 0630 if (!r) { 0631 0632 qWarning() << "Could not execute removeResourceVersionImpl statement" << q.lastError() << resourceId << storage->name() << storage->location() << "version" << version; 0633 return r; 0634 } 0635 0636 return r; 0637 } 0638 0639 bool KisResourceCacheDb::updateResourceTableForResourceIfNeeded(int resourceId, const QString &resourceType, KisResourceStorageSP storage) 0640 { 0641 bool r = false; 0642 0643 int maxVersion = -1; 0644 { 0645 QSqlQuery q; 0646 r = q.prepare("SELECT MAX(version)\n" 0647 "FROM versioned_resources\n" 0648 "WHERE resource_id = :resource_id;"); 0649 if (!r) { 0650 qWarning() << "Could not prepare findMaxVersion statement" << q.lastError(); 0651 return r; 0652 } 0653 0654 q.bindValue(":resource_id", resourceId); 0655 0656 r = q.exec(); 0657 if (!r) { 0658 qWarning() << "Could not execute findMaxVersion query" << q.boundValues() << q.lastError(); 0659 return r; 0660 } 0661 0662 r = q.first(); 0663 KIS_SAFE_ASSERT_RECOVER_RETURN_VALUE(r, false); 0664 0665 maxVersion = q.value(0).toInt(); 0666 } 0667 0668 QString maxVersionFilename; 0669 { 0670 QSqlQuery q; 0671 r = q.prepare("SELECT filename\n" 0672 "FROM versioned_resources\n" 0673 "WHERE resource_id = :resource_id\n" 0674 "AND version = :version;"); 0675 if (!r) { 0676 qWarning() << "Could not prepare findMaxVersionFilename statement" << q.lastError(); 0677 return r; 0678 } 0679 0680 q.bindValue(":resource_id", resourceId); 0681 q.bindValue(":version", maxVersion); 0682 0683 r = q.exec(); 0684 if (!r) { 0685 qWarning() << "Could not execute findMaxVersionFilename query" << q.boundValues() << q.lastError(); 0686 return r; 0687 } 0688 0689 if (!q.first()) { 0690 return removeResourceCompletely(resourceId); 0691 } else { 0692 maxVersionFilename = q.value(0).toString(); 0693 } 0694 } 0695 0696 QString currentFilename; 0697 { 0698 QSqlQuery q; 0699 r = q.prepare("SELECT filename\n" 0700 "FROM resources\n" 0701 "WHERE id = :resource_id;"); 0702 if (!r) { 0703 qWarning() << "Could not prepare findMaxVersion statement" << q.lastError(); 0704 return r; 0705 } 0706 0707 q.bindValue(":resource_id", resourceId); 0708 0709 r = q.exec(); 0710 if (!r) { 0711 qWarning() << "Could not execute findMaxVersion query" << q.boundValues() << q.lastError(); 0712 return r; 0713 } 0714 0715 r = q.first(); 0716 KIS_SAFE_ASSERT_RECOVER_RETURN_VALUE(r, false); 0717 0718 currentFilename = q.value(0).toString(); 0719 } 0720 0721 if (currentFilename != maxVersionFilename) { 0722 const QString url = resourceType + "/" + maxVersionFilename; 0723 KoResourceSP resource = storage->resource(url); 0724 KIS_SAFE_ASSERT_RECOVER_RETURN_VALUE(resource, false); 0725 resource->setVersion(maxVersion); 0726 resource->setMD5Sum(storage->resourceMd5(url)); 0727 r = makeResourceTheCurrentVersion(resourceId, resource); 0728 } 0729 0730 return r; 0731 } 0732 0733 bool KisResourceCacheDb::makeResourceTheCurrentVersion(int resourceId, KoResourceSP resource) 0734 { 0735 bool r = false; 0736 0737 QSqlQuery q; 0738 r = q.prepare("UPDATE resources\n" 0739 "SET name = :name\n" 0740 ", filename = :filename\n" 0741 ", tooltip = :tooltip\n" 0742 ", thumbnail = :thumbnail\n" 0743 ", status = 1\n" 0744 ", md5sum = :md5sum\n" 0745 "WHERE id = :id"); 0746 if (!r) { 0747 qWarning() << "Could not prepare updateResource statement" << q.lastError(); 0748 return r; 0749 } 0750 0751 q.bindValue(":name", resource->name()); 0752 q.bindValue(":filename", resource->filename()); 0753 q.bindValue(":tooltip", i18n(resource->name().toUtf8())); 0754 q.bindValue(":md5sum", resource->md5Sum()); 0755 0756 QBuffer buf; 0757 buf.open(QBuffer::WriteOnly); 0758 resource->thumbnail().save(&buf, "PNG"); 0759 buf.close(); 0760 q.bindValue(":thumbnail", buf.data()); 0761 q.bindValue(":id", resourceId); 0762 0763 r = q.exec(); 0764 if (!r) { 0765 qWarning() << "Could not update resource" << q.boundValues() << q.lastError(); 0766 } 0767 0768 return r; 0769 } 0770 0771 bool KisResourceCacheDb::removeResourceCompletely(int resourceId) 0772 { 0773 bool r = false; 0774 0775 { 0776 QSqlQuery q; 0777 r = q.prepare("DELETE FROM versioned_resources \n" 0778 "WHERE resource_id = :resource_id;"); 0779 0780 if (!r) { 0781 qWarning() << "Could not prepare removeResourceCompletely1 statement" << q.lastError(); 0782 return r; 0783 } 0784 0785 q.bindValue(":resource_id", resourceId); 0786 r = q.exec(); 0787 if (!r) { 0788 qWarning() << "Could not execute removeResourceCompletely1 statement" << q.lastError() << resourceId; 0789 return r; 0790 } 0791 } 0792 0793 { 0794 QSqlQuery q; 0795 r = q.prepare("DELETE FROM resources \n" 0796 "WHERE id = :resource_id;"); 0797 0798 if (!r) { 0799 qWarning() << "Could not prepare removeResourceCompletely2 statement" << q.lastError(); 0800 return r; 0801 } 0802 0803 q.bindValue(":resource_id", resourceId); 0804 r = q.exec(); 0805 if (!r) { 0806 qWarning() << "Could not execute removeResourceCompletely2 statement" << q.lastError() << resourceId; 0807 return r; 0808 } 0809 } 0810 0811 { 0812 QSqlQuery q; 0813 r = q.prepare("DELETE FROM resource_tags \n" 0814 "WHERE resource_id = :resource_id;"); 0815 0816 if (!r) { 0817 qWarning() << "Could not prepare removeResourceCompletely3 statement" << q.lastError(); 0818 return r; 0819 } 0820 0821 q.bindValue(":resource_id", resourceId); 0822 r = q.exec(); 0823 if (!r) { 0824 qWarning() << "Could not execute removeResourceCompletely3 statement" << q.lastError() << resourceId; 0825 return r; 0826 } 0827 } 0828 0829 return r; 0830 } 0831 0832 bool KisResourceCacheDb::getResourceIdFromFilename(QString filename, QString resourceType, QString storageLocation, int &outResourceId) 0833 { 0834 QSqlQuery q; 0835 0836 bool r = q.prepare("SELECT resources.id FROM resources\n" 0837 ", resource_types\n" 0838 ", storages\n" 0839 "WHERE resources.filename = :filename\n" // bind to filename 0840 "AND resource_types.id = resources.resource_type_id\n" // join resources_types + resources 0841 "AND resource_types.name = :resourceType\n" // bind to resource type 0842 "AND resources.storage_id = storages.id\n" // join resources + storages 0843 "AND storages.location = :storageLocation"); // bind to storage location 0844 0845 if (!r) { 0846 qWarning() << "Could not prepare getResourceIdFromFilename statement" << q.lastError() << q.executedQuery(); 0847 return r; 0848 } 0849 0850 q.bindValue(":filename", filename); 0851 q.bindValue(":resourceType", resourceType); 0852 q.bindValue(":storageLocation", changeToEmptyIfNull(storageLocation)); 0853 0854 r = q.exec(); 0855 if (!r) { 0856 qWarning() << "Could not execute getResourceIdFromFilename statement" << q.lastError() << filename << resourceType; 0857 return r; 0858 } 0859 0860 r = q.first(); 0861 if (r) { 0862 outResourceId = q.value("resources.id").toInt(); 0863 } 0864 0865 return r; 0866 } 0867 0868 bool KisResourceCacheDb::getResourceIdFromVersionedFilename(QString filename, QString resourceType, QString storageLocation, int &outResourceId) 0869 { 0870 QSqlQuery q; 0871 0872 bool r = q.prepare("SELECT resource_id FROM versioned_resources\n" 0873 ", resources\n" 0874 ", resource_types\n" 0875 ", storages\n" 0876 "WHERE versioned_resources.filename = :filename\n" // bind to filename 0877 "AND resources.id = versioned_resources.resource_id\n" // join resources + versioned_resources 0878 "AND resource_types.id = resources.resource_type_id\n" // join resources_types + resources 0879 "AND resource_types.name = :resourceType\n" // bind to resource type 0880 "AND resources.storage_id = storages.id\n" // join resources + storages 0881 "AND storages.location = :storageLocation"); // bind to storage location 0882 0883 if (!r) { 0884 qWarning() << "Could not prepare getResourceIdFromVersionedFilename statement" << q.lastError() << q.executedQuery(); 0885 return r; 0886 } 0887 0888 0889 q.bindValue(":filename", filename); 0890 q.bindValue(":resourceType", resourceType); 0891 q.bindValue(":storageLocation", changeToEmptyIfNull(storageLocation)); 0892 0893 r = q.exec(); 0894 if (!r) { 0895 qWarning() << "Could not execute getResourceIdFromVersionedFilename statement" << q.lastError() << filename << resourceType; 0896 return r; 0897 } 0898 0899 r = q.first(); 0900 if (r) { 0901 outResourceId = q.value("resource_id").toInt(); 0902 } 0903 0904 return r; 0905 } 0906 0907 bool KisResourceCacheDb::getAllVersionsLocations(int resourceId, QStringList &outVersionsLocationsList) 0908 { 0909 QSqlQuery q; 0910 bool r = q.prepare("SELECT filename FROM versioned_resources \n" 0911 "WHERE resource_id = :resource_id;"); 0912 0913 if (!r) { 0914 qWarning() << "Could not prepare getAllVersionsLocations statement" << q.lastError(); 0915 return r; 0916 } 0917 0918 q.bindValue(":resource_id", resourceId); 0919 r = q.exec(); 0920 if (!r) { 0921 qWarning() << "Could not execute getAllVersionsLocations statement" << q.lastError() << resourceId; 0922 return r; 0923 } 0924 0925 outVersionsLocationsList = QStringList(); 0926 while (q.next()) { 0927 outVersionsLocationsList << q.value("filename").toString(); 0928 } 0929 0930 return r; 0931 0932 } 0933 0934 bool KisResourceCacheDb::addResource(KisResourceStorageSP storage, QDateTime timestamp, KoResourceSP resource, const QString &resourceType) 0935 { 0936 bool r = false; 0937 0938 if (!s_valid) { 0939 qWarning() << "KisResourceCacheDb::addResource: The database is not valid"; 0940 return false; 0941 } 0942 0943 if (!resource || !resource->valid()) { 0944 qWarning() << "KisResourceCacheDb::addResource: The resource is not valid:" << resource->filename(); 0945 // We don't care about invalid resources and will just ignore them. 0946 return true; 0947 } 0948 bool temporary = (storage->type() == KisResourceStorage::StorageType::Memory); 0949 0950 // Check whether it already exists 0951 int resourceId = resourceIdForResource(resource->filename(), resourceType, KisResourceLocator::instance()->makeStorageLocationRelative(storage->location())); 0952 if (resourceId > -1) { 0953 return true; 0954 } 0955 0956 QSqlQuery q; 0957 r = q.prepare("INSERT INTO resources \n" 0958 "(storage_id, resource_type_id, name, filename, tooltip, thumbnail, status, temporary, md5sum) \n" 0959 "VALUES \n" 0960 "((SELECT id " 0961 " FROM storages " 0962 " WHERE location = :storage_location)\n" 0963 ", (SELECT id\n" 0964 " FROM resource_types\n" 0965 " WHERE name = :resource_type)\n" 0966 ", :name\n" 0967 ", :filename\n" 0968 ", :tooltip\n" 0969 ", :thumbnail\n" 0970 ", :status\n" 0971 ", :temporary\n" 0972 ", :md5sum)"); 0973 0974 if (!r) { 0975 qWarning() << "Could not prepare addResource statement" << q.lastError(); 0976 return r; 0977 } 0978 0979 q.bindValue(":resource_type", resourceType); 0980 q.bindValue(":storage_location", changeToEmptyIfNull(KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()))); 0981 q.bindValue(":name", resource->name()); 0982 q.bindValue(":filename", resource->filename()); 0983 0984 QString translationContext; 0985 if (storage->type() == KisResourceStorage::StorageType::Bundle) { 0986 translationContext = "./krita/data/bundles/" + KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()) 0987 + ":" + resourceType + "/" + resource->filename(); 0988 } else if (storage->location() == "memory") { 0989 translationContext = "memory/" + resourceType + "/" + resource->filename(); 0990 } 0991 else if (resource->filename().endsWith(".myb", Qt::CaseInsensitive)) { 0992 translationContext = "./plugins/paintops/mypaint/brushes/" + resource->filename(); 0993 } else { 0994 translationContext = "./krita/data/" + resourceType + "/" + resource->filename(); 0995 } 0996 0997 { 0998 QByteArray ctx = translationContext.toUtf8(); 0999 QString translatedName = i18nc(ctx, resource->name().toUtf8()); 1000 if (translatedName == resource->name()) { 1001 // Try using the file name without the file extension, and replaces '_' with spaces. 1002 QString altName = QFileInfo(resource->filename()).completeBaseName().replace('_', ' '); 1003 QString altTranslatedName = i18nc(ctx, altName.toUtf8()); 1004 if (altName != altTranslatedName) { 1005 translatedName = altTranslatedName; 1006 } 1007 } 1008 q.bindValue(":tooltip", translatedName); 1009 } 1010 1011 QBuffer buf; 1012 buf.open(QBuffer::WriteOnly); 1013 resource->image().save(&buf, "PNG"); 1014 buf.close(); 1015 q.bindValue(":thumbnail", buf.data()); 1016 1017 q.bindValue(":status", resource->active()); 1018 q.bindValue(":temporary", (temporary ? 1 : 0)); 1019 q.bindValue(":md5sum", resource->md5Sum()); 1020 1021 r = q.exec(); 1022 if (!r) { 1023 qWarning() << "Could not execute addResource statement" << q.lastError() << q.boundValues(); 1024 return r; 1025 } 1026 resourceId = resourceIdForResource(resource->filename(), resourceType, KisResourceLocator::instance()->makeStorageLocationRelative(storage->location())); 1027 1028 if (resourceId < 0) { 1029 1030 qWarning() << "Adding to database failed, resource id after adding is " << resourceId << "! (Probable reason: the resource has the same filename, storage, resource type as an existing resource). Resource is: " 1031 << resource->name() 1032 << resource->filename() 1033 << resourceType 1034 << KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()); 1035 return false; 1036 } 1037 1038 resource->setResourceId(resourceId); 1039 1040 if (!addResourceVersionImpl(resourceId, timestamp, storage, resource)) { 1041 qWarning() << "Could not add resource version" << resource; 1042 return false; 1043 } 1044 1045 if (!resource->metadata().isEmpty()) { 1046 return addMetaDataForId(resource->metadata(), resource->resourceId(), "resources"); 1047 } 1048 1049 return true; 1050 1051 1052 } 1053 1054 bool KisResourceCacheDb::addResources(KisResourceStorageSP storage, QString resourceType) 1055 { 1056 QSqlDatabase::database().transaction(); 1057 QSharedPointer<KisResourceStorage::ResourceIterator> iter = storage->resources(resourceType); 1058 while (iter->hasNext()) { 1059 iter->next(); 1060 1061 QSharedPointer<KisResourceStorage::ResourceIterator> verIt = 1062 iter->versions(); 1063 1064 int resourceId = -1; 1065 1066 while (verIt->hasNext()) { 1067 verIt->next(); 1068 1069 KoResourceSP resource = verIt->resource(); 1070 if (resource && resource->valid()) { 1071 resource->setVersion(verIt->guessedVersion()); 1072 resource->setMD5Sum(storage->resourceMd5(verIt->url())); 1073 1074 if (resourceId < 0) { 1075 if (addResource(storage, iter->lastModified(), resource, iter->type())) { 1076 resourceId = resource->resourceId(); 1077 } else { 1078 qWarning() << "Could not add resource" << resource->filename() << "to the database"; 1079 } 1080 } else { 1081 if (!addResourceVersion(resourceId, iter->lastModified(), storage, resource)) { 1082 qWarning() << "Could not add resource version" << resource->filename() << "to the database"; 1083 } 1084 } 1085 } 1086 } 1087 } 1088 QSqlDatabase::database().commit(); 1089 return true; 1090 } 1091 1092 bool KisResourceCacheDb::setResourceActive(int resourceId, bool active) 1093 { 1094 if (resourceId < 0) { 1095 qWarning() << "Invalid resource id; cannot remove resource"; 1096 return false; 1097 } 1098 QSqlQuery q; 1099 bool r = q.prepare("UPDATE resources\n" 1100 "SET status = :status\n" 1101 "WHERE id = :resource_id"); 1102 if (!r) { 1103 qWarning() << "Could not prepare removeResource query" << q.lastError(); 1104 } 1105 q.bindValue(":status", active); 1106 q.bindValue(":resource_id", resourceId); 1107 if (!q.exec()) { 1108 qWarning() << "Could not update resource" << resourceId << "to inactive" << q.lastError(); 1109 return false; 1110 } 1111 1112 return true; 1113 } 1114 1115 bool KisResourceCacheDb::tagResource(const QString &resourceFileName, KisTagSP tag, const QString &resourceType) 1116 { 1117 // Get tag id 1118 int tagId {-1}; 1119 { 1120 QFile f(":/select_tag.sql"); 1121 if (f.open(QFile::ReadOnly)) { 1122 QSqlQuery q; 1123 if (!q.prepare(f.readAll())) { 1124 qWarning() << "Could not read and prepare select_tag.sql" << q.lastError(); 1125 return false; 1126 } 1127 q.bindValue(":url", tag->url()); 1128 q.bindValue(":resource_type", resourceType); 1129 1130 if (!q.exec()) { 1131 qWarning() << "Could not query tags" << q.boundValues() << q.lastError(); 1132 return false; 1133 } 1134 1135 if (!q.first()) { 1136 qWarning() << "Could not find tag" << q.boundValues() << q.lastError(); 1137 return false; 1138 } 1139 1140 tagId = q.value(0).toInt(); 1141 } 1142 } 1143 1144 1145 // Get resource id 1146 QSqlQuery q; 1147 bool r = q.prepare("SELECT resources.id\n" 1148 "FROM resources\n" 1149 ", resource_types\n" 1150 "WHERE resources.resource_type_id = resource_types.id\n" 1151 "AND resource_types.name = :resource_type\n" 1152 "AND resources.filename = :resource_filename\n"); 1153 if (!r) { 1154 qWarning() << "Could not prepare tagResource query" << q.lastError(); 1155 return false; 1156 } 1157 1158 q.bindValue(":resource_type", resourceType); 1159 q.bindValue(":resource_filename", resourceFileName); 1160 1161 if (!q.exec()) { 1162 qWarning() << "Could not execute tagResource statement" << q.boundValues() << q.lastError(); 1163 return false; 1164 } 1165 1166 1167 while (q.next()) { 1168 1169 int resourceId = q.value(0).toInt(); 1170 1171 if (resourceId < 0) { 1172 qWarning() << "Could not find resource to tag" << resourceFileName << resourceType; 1173 continue; 1174 } 1175 1176 { 1177 QSqlQuery q; 1178 if (!q.prepare("SELECT COUNT(*)\n" 1179 "FROM resource_tags\n" 1180 "WHERE resource_id = :resource_id\n" 1181 "AND tag_id = :tag_id")) { 1182 qWarning() << "Could not prepare tagResource query 2" << q.lastError(); 1183 continue; 1184 } 1185 q.bindValue(":resource_id", resourceId); 1186 q.bindValue(":tag_id", tagId); 1187 1188 if (!q.exec()) { 1189 qWarning() << "Could not execute tagResource query 2" << q.lastError() << q.boundValues(); 1190 continue; 1191 } 1192 1193 q.first(); 1194 int count = q.value(0).toInt(); 1195 if (count > 0) { 1196 continue; 1197 } 1198 } 1199 1200 { 1201 QSqlQuery q; 1202 if (!q.prepare("INSERT INTO resource_tags\n" 1203 "(resource_id, tag_id)\n" 1204 "VALUES\n" 1205 "(:resource_id, :tag_id);")) { 1206 qWarning() << "Could not prepare tagResource insert statement" << q.lastError(); 1207 continue; 1208 } 1209 1210 q.bindValue(":resource_id", resourceId); 1211 q.bindValue(":tag_id", tagId); 1212 1213 if (!q.exec()) { 1214 qWarning() << "Could not execute tagResource stagement" << q.boundValues() << q.lastError(); 1215 continue; 1216 } 1217 } 1218 } 1219 return true; 1220 } 1221 1222 bool KisResourceCacheDb::hasTag(const QString &url, const QString &resourceType) 1223 { 1224 QFile f(":/select_tag.sql"); 1225 if (f.open(QFile::ReadOnly)) { 1226 QSqlQuery q; 1227 if (!q.prepare(f.readAll())) { 1228 qWarning() << "Could not read and prepare select_tag.sql" << q.lastError(); 1229 return false; 1230 } 1231 q.bindValue(":url", url); 1232 q.bindValue(":resource_type", resourceType); 1233 if (!q.exec()) { 1234 qWarning() << "Could not query tags" << q.boundValues() << q.lastError(); 1235 } 1236 return q.first(); 1237 } 1238 qWarning() << "Could not open select_tag.sql"; 1239 return false; 1240 } 1241 1242 bool KisResourceCacheDb::linkTagToStorage(const QString &url, const QString &resourceType, const QString &storageLocation) 1243 { 1244 QSqlQuery q; 1245 if (!q.prepare("INSERT INTO tags_storages\n" 1246 "(tag_id, storage_id)\n" 1247 "VALUES\n" 1248 "(\n" 1249 " ( SELECT id\n" 1250 " FROM tags\n" 1251 " WHERE url = :url\n" 1252 " AND resource_type_id = (SELECT id \n" 1253 " FROM resource_types\n" 1254 " WHERE name = :resource_type)" 1255 " )\n" 1256 ",( SELECT id\n" 1257 " FROM storages\n" 1258 " WHERE location = :storage_location\n" 1259 " )\n" 1260 ");")) { 1261 qWarning() << "Could not prepare add tag/storage statement" << q.lastError(); 1262 return false; 1263 } 1264 1265 q.bindValue(":url", url); 1266 q.bindValue(":resource_type", resourceType); 1267 q.bindValue(":storage_location", changeToEmptyIfNull(KisResourceLocator::instance()->makeStorageLocationRelative(storageLocation))); 1268 1269 if (!q.exec()) { 1270 qWarning() << "Could not insert tag/storage link" << q.boundValues() << q.lastError(); 1271 return false; 1272 } 1273 return true; 1274 } 1275 1276 1277 bool KisResourceCacheDb::addTag(const QString &resourceType, const QString storageLocation, KisTagSP tag) 1278 { 1279 1280 if (hasTag(tag->url(), resourceType)) { 1281 // Check whether this storage is already registered for this tag 1282 QSqlQuery q; 1283 if (!q.prepare("SELECT storages.location\n" 1284 "FROM tags_storages\n" 1285 ", tags\n" 1286 ", storages\n" 1287 "WHERE tags.id = tags_storages.tag_id\n" 1288 "AND storages.id = tags_storages.storage_id\n" 1289 "AND tags.resource_type_id = (SELECT id\n" 1290 " FROM resource_types\n" 1291 " WHERE name = :resource_type)\n" 1292 "AND tags.url = :url")) 1293 { 1294 qWarning() << "Could not prepare select tags from tags_storages query" << q.lastError(); 1295 } 1296 1297 q.bindValue(":url", tag->url()); 1298 q.bindValue(":resource_type", resourceType); 1299 1300 if (!q.exec()) { 1301 qWarning() << "Could not execute tags_storages query" << q.boundValues() << q.lastError(); 1302 } 1303 1304 // If this tag is not yet linked to the storage, link it 1305 if (!q.first()) { 1306 return linkTagToStorage(tag->url(), resourceType, storageLocation); 1307 } 1308 1309 return true; 1310 } 1311 1312 int tagId; 1313 1314 // Insert the tag 1315 { 1316 QSqlQuery q; 1317 if (!q.prepare("INSERT INTO tags\n" 1318 "(url, name, comment, resource_type_id, active, filename)\n" 1319 "VALUES\n" 1320 "( :url\n" 1321 ", :name\n" 1322 ", :comment\n" 1323 ", (SELECT id\n" 1324 " FROM resource_types\n" 1325 " WHERE name = :resource_type)\n" 1326 ", 1\n" 1327 ", :filename\n" 1328 ");")) { 1329 qWarning() << "Could not prepare insert tag statement" << q.lastError(); 1330 return false; 1331 } 1332 1333 q.bindValue(":url", tag->url()); 1334 q.bindValue(":name", tag->name(false)); 1335 q.bindValue(":comment", tag->comment(false)); 1336 q.bindValue(":resource_type", resourceType); 1337 q.bindValue(":filename", tag->filename()); 1338 1339 if (!q.exec()) { 1340 qWarning() << "Could not insert tag" << q.boundValues() << q.lastError(); 1341 } 1342 1343 tagId = q.lastInsertId().toInt(); 1344 } 1345 1346 { 1347 Q_FOREACH(const QString language, tag->names().keys()) { 1348 1349 QString name = tag->names()[language]; 1350 QString comment = name; 1351 if (tag->comments().contains(language)) { 1352 comment = tag->comments()[language]; 1353 } 1354 1355 QSqlQuery q; 1356 if (!q.prepare("INSERT INTO tag_translations\n" 1357 "( tag_id\n" 1358 ", language\n" 1359 ", name\n" 1360 ", comment\n" 1361 ")\n" 1362 "VALUES\n" 1363 "( :id\n" 1364 ", :language\n" 1365 ", :name\n" 1366 ", :comment\n" 1367 ");")) { 1368 qWarning() << "Could not prepare insert tag_translation query" << q.lastError(); 1369 } 1370 1371 q.bindValue(":id", tagId); 1372 q.bindValue(":language", language); 1373 q.bindValue(":name", name); 1374 q.bindValue(":comment", comment); 1375 1376 if (!q.exec()) { 1377 qWarning() << "Could not execute insert tag_transaltion query" << q.lastError() << q.boundValues(); 1378 } 1379 } 1380 1381 } 1382 1383 1384 linkTagToStorage(tag->url(), resourceType, storageLocation); 1385 1386 return true; 1387 } 1388 1389 bool KisResourceCacheDb::addTags(KisResourceStorageSP storage, QString resourceType) 1390 { 1391 QSqlDatabase::database().transaction(); 1392 QSharedPointer<KisResourceStorage::TagIterator> iter = storage->tags(resourceType); 1393 while(iter->hasNext()) { 1394 iter->next(); 1395 KisTagSP tag = iter->tag(); 1396 if (tag && tag->valid()) { 1397 if (!addTag(resourceType, storage->location(), tag)) { 1398 qWarning() << "Could not add tag" << tag << "to the database"; 1399 continue; 1400 } 1401 if (!tag->defaultResources().isEmpty()) { 1402 Q_FOREACH(const QString &resourceFileName, tag->defaultResources()) { 1403 if (!tagResource(resourceFileName, tag, resourceType)) { 1404 qWarning() << "Could not tag resource" << QFileInfo(resourceFileName).baseName() << "from" << storage->name() << "filename" << resourceFileName << "with tag" << iter->tag(); 1405 } 1406 } 1407 } 1408 } 1409 } 1410 QSqlDatabase::database().commit(); 1411 return true; 1412 } 1413 1414 bool KisResourceCacheDb::addStorage(KisResourceStorageSP storage, bool preinstalled) 1415 { 1416 bool r = true; 1417 1418 if (!s_valid) { 1419 qWarning() << "The database is not valid"; 1420 return false; 1421 } 1422 1423 { 1424 QSqlQuery q; 1425 r = q.prepare("SELECT * FROM storages WHERE location = :location"); 1426 q.bindValue(":location", changeToEmptyIfNull(KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()))); 1427 r = q.exec(); 1428 if (!r) { 1429 qWarning() << "Could not select from storages"; 1430 return r; 1431 } 1432 if (q.first()) { 1433 debugResource << "Storage already exists" << storage; 1434 return true; 1435 } 1436 } 1437 1438 // Insert the storage; 1439 { 1440 QSqlQuery q; 1441 1442 r = q.prepare("INSERT INTO storages\n " 1443 "(storage_type_id, location, timestamp, pre_installed, active, thumbnail)\n" 1444 "VALUES\n" 1445 "(:storage_type_id, :location, :timestamp, :pre_installed, :active, :thumbnail);"); 1446 1447 if (!r) { 1448 qWarning() << "Could not prepare query" << q.lastError(); 1449 return r; 1450 } 1451 1452 q.bindValue(":storage_type_id", static_cast<int>(storage->type())); 1453 q.bindValue(":location", changeToEmptyIfNull(KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()))); 1454 q.bindValue(":timestamp", storage->timestamp().toSecsSinceEpoch()); 1455 q.bindValue(":pre_installed", preinstalled ? 1 : 0); 1456 q.bindValue(":active", !disabledBundles.contains(storage->name())); 1457 1458 QBuffer buf; 1459 buf.open(QBuffer::WriteOnly); 1460 storage->thumbnail().save(&buf, "PNG"); 1461 buf.close(); 1462 q.bindValue(":thumbnail", buf.data()); 1463 1464 r = q.exec(); 1465 1466 if (!r) qWarning() << "Could not execute query" << q.lastError(); 1467 1468 } 1469 1470 // Insert the metadata 1471 { 1472 QStringList keys = storage->metaDataKeys(); 1473 if (keys.size() > 0) { 1474 1475 QSqlQuery q; 1476 if (!q.prepare("SELECT MAX(id)\n" 1477 "FROM storages\n")) { 1478 qWarning() << "Could not create select storages query for metadata" << q.lastError(); 1479 } 1480 1481 if (!q.exec()) { 1482 qWarning() << "Could not execute select storages query for metadata" << q.lastError(); 1483 } 1484 1485 q.first(); 1486 int id = q.value(0).toInt(); 1487 1488 QMap<QString, QVariant> metadata; 1489 1490 Q_FOREACH(const QString &key, storage->metaDataKeys()) { 1491 metadata[key] = storage->metaData(key); 1492 } 1493 1494 addMetaDataForId(metadata, id, "storages"); 1495 } 1496 } 1497 1498 Q_FOREACH(const QString &resourceType, KisResourceLoaderRegistry::instance()->resourceTypes()) { 1499 if (!KisResourceCacheDb::addResources(storage, resourceType)) { 1500 qWarning() << "Failed to add all resources for storage" << storage; 1501 r = false; 1502 } 1503 } 1504 1505 return r; 1506 } 1507 1508 bool KisResourceCacheDb::addStorageTags(KisResourceStorageSP storage) 1509 { 1510 1511 bool r = true; 1512 Q_FOREACH(const QString &resourceType, KisResourceLoaderRegistry::instance()->resourceTypes()) { 1513 if (!KisResourceCacheDb::addTags(storage, resourceType)) { 1514 qWarning() << "Failed to add all tags for storage" << storage; 1515 r = false; 1516 } 1517 } 1518 return r; 1519 } 1520 1521 bool KisResourceCacheDb::deleteStorage(QString location) 1522 { 1523 // location is already relative 1524 { 1525 QSqlQuery q; 1526 if (!q.prepare("DELETE FROM resources\n" 1527 "WHERE id IN (SELECT versioned_resources.resource_id\n" 1528 " FROM versioned_resources\n" 1529 " WHERE versioned_resources.storage_id = (SELECT storages.id\n" 1530 " FROM storages\n" 1531 " WHERE storages.location = :location)\n" 1532 " );")) { 1533 qWarning() << "Could not prepare delete resources query in deleteStorage" << q.lastError(); 1534 return false; 1535 } 1536 q.bindValue(":location", changeToEmptyIfNull(location)); 1537 if (!q.exec()) { 1538 qWarning() << "Could not execute delete resources query in deleteStorage" << q.lastError(); 1539 return false; 1540 } 1541 } 1542 1543 { 1544 QSqlQuery q; 1545 if (!q.prepare("DELETE FROM tags \n" 1546 "WHERE id IN (SELECT tags_storages.tag_id \n " 1547 " FROM tags_storages \n" 1548 " WHERE tags_storages.storage_id = \n" 1549 " (SELECT storages.id\n" 1550 " FROM storages\n" 1551 " WHERE storages.location = :location)\n" 1552 " );")) { 1553 qWarning() << "Could not prepare delete tag query" << q.lastError(); 1554 return false; 1555 } 1556 q.bindValue(":location", location); 1557 if (!q.exec()) { 1558 qWarning() << "Could not execute delete tag query" << q.lastError(); 1559 return false; 1560 } 1561 } 1562 1563 { 1564 QSqlQuery q; 1565 if (!q.prepare("DELETE FROM tags_storages \n" 1566 " WHERE tags_storages.storage_id = \n" 1567 " (SELECT storages.id\n" 1568 " FROM storages\n" 1569 " WHERE storages.location = :location);")) { 1570 qWarning() << "Could not prepare delete tag storage query" << q.lastError(); 1571 return false; 1572 } 1573 q.bindValue(":location", location); 1574 if (!q.exec()) { 1575 qWarning() << "Could not execute delete tag storage query" << q.lastError(); 1576 return false; 1577 } 1578 } 1579 1580 { 1581 QSqlQuery q; 1582 if (!q.prepare("DELETE FROM versioned_resources\n" 1583 "WHERE storage_id = (SELECT storages.id\n" 1584 " FROM storages\n" 1585 " WHERE storages.location = :location);")) { 1586 qWarning() << "Could not prepare delete versioned_resources query" << q.lastError(); 1587 return false; 1588 } 1589 q.bindValue(":location", changeToEmptyIfNull(location)); 1590 if (!q.exec()) { 1591 qWarning() << "Could not execute delete versioned_resources query" << q.lastError(); 1592 return false; 1593 } 1594 } 1595 1596 { 1597 QSqlQuery q; 1598 if (!q.prepare("DELETE FROM storages\n" 1599 "WHERE location = :location;")) { 1600 qWarning() << "Could not prepare delete storages query" << q.lastError(); 1601 return false; 1602 } 1603 q.bindValue(":location", changeToEmptyIfNull(location)); 1604 if (!q.exec()) { 1605 qWarning() << "Could not execute delete storages query" << q.lastError(); 1606 return false; 1607 } 1608 } 1609 return true; 1610 } 1611 1612 bool KisResourceCacheDb::deleteStorage(KisResourceStorageSP storage) 1613 { 1614 return deleteStorage(KisResourceLocator::instance()->makeStorageLocationRelative(storage->location())); 1615 } 1616 1617 namespace { 1618 struct ResourceVersion : public boost::less_than_comparable<ResourceVersion> 1619 { 1620 int resourceId = -1; 1621 int version = -1; 1622 QDateTime timestamp; 1623 QString url; 1624 1625 bool operator<(const ResourceVersion &rhs) const { 1626 return resourceId < rhs.resourceId || 1627 (resourceId == rhs.resourceId && version < rhs.version); 1628 } 1629 1630 struct CompareByResourceId { 1631 bool operator() (const ResourceVersion &lhs, const ResourceVersion &rhs) const { 1632 return lhs.resourceId < rhs.resourceId; 1633 } 1634 }; 1635 1636 1637 }; 1638 1639 MAYBE_UNUSED 1640 QDebug operator<<(QDebug dbg, const ResourceVersion &ver) 1641 { 1642 dbg.nospace() << "ResourceVersion(" 1643 << ver.resourceId << ", " 1644 << ver.version << ", " 1645 << ver.timestamp << ", " 1646 << ver.url << ")"; 1647 1648 return dbg.space(); 1649 } 1650 } 1651 1652 bool KisResourceCacheDb::synchronizeStorage(KisResourceStorageSP storage) 1653 { 1654 QElapsedTimer t; 1655 t.start(); 1656 1657 QSqlDatabase::database().transaction(); 1658 1659 if (!s_valid) { 1660 qWarning() << "KisResourceCacheDb::addResource: The database is not valid"; 1661 return false; 1662 } 1663 1664 bool success = true; 1665 1666 // Find the storage in the database 1667 QSqlQuery q; 1668 if (!q.prepare("SELECT id\n" 1669 ", timestamp\n" 1670 ", pre_installed\n" 1671 "FROM storages\n" 1672 "WHERE location = :location\n")) { 1673 qWarning() << "Could not prepare storage timestamp statement" << q.lastError(); 1674 } 1675 1676 q.bindValue(":location", changeToEmptyIfNull(KisResourceLocator::instance()->makeStorageLocationRelative(storage->location()))); 1677 if (!q.exec()) { 1678 qWarning() << "Could not execute storage timestamp statement" << q.boundValues() << q.lastError(); 1679 } 1680 1681 if (!q.first()) { 1682 // This is a new storage, the user must have dropped it in the path before restarting Krita, so add it. 1683 debugResource << "Adding storage to the database:" << storage; 1684 if (!addStorage(storage, false)) { 1685 qWarning() << "Could not add new storage" << storage->name() << "to the database"; 1686 success = false; 1687 } 1688 return true; 1689 } 1690 1691 storage->setStorageId(q.value("id").toInt()); 1692 1693 /// We compare resource versions one-by-one because the storage may have multiple 1694 /// versions of them 1695 1696 Q_FOREACH(const QString &resourceType, KisResourceLoaderRegistry::instance()->resourceTypes()) { 1697 1698 /// Firstly, fetch information about the existing resources 1699 /// in the storage 1700 1701 QVector<ResourceVersion> resourcesInStorage; 1702 1703 /// A fake resourceId to group resources which are not yet present 1704 /// in the database. This value is always negative, therefore it 1705 /// cannot overlap with normal ids. 1706 1707 int nextInexistentResourceId = std::numeric_limits<int>::min(); 1708 1709 QSharedPointer<KisResourceStorage::ResourceIterator> iter = storage->resources(resourceType); 1710 while (iter->hasNext()) { 1711 iter->next(); 1712 1713 const int firstResourceVersionPosition = resourcesInStorage.size(); 1714 1715 int detectedResourceId = nextInexistentResourceId; 1716 QSharedPointer<KisResourceStorage::ResourceIterator> verIt = 1717 iter->versions(); 1718 1719 while (verIt->hasNext()) { 1720 verIt->next(); 1721 1722 // verIt->url() contains paths like "brushes/ink.png" or "brushes/subfolder/splash.png". 1723 // we need to cut off the first part and get "ink.png" in the first case, 1724 // but "subfolder/splash.png" in the second case in order for subfolders to work 1725 // so it cannot just use QFileInfo(verIt->url()).fileName() here. 1726 QString path = QDir::fromNativeSeparators(verIt->url()); // make sure it uses Unix separators 1727 int folderEndIdx = path.indexOf("/"); 1728 QString properFilenameWithSubfolders = path.right(path.length() - folderEndIdx - 1); 1729 int id = resourceIdForResource(properFilenameWithSubfolders, 1730 verIt->type(), 1731 KisResourceLocator::instance()->makeStorageLocationRelative(storage->location())); 1732 1733 ResourceVersion item; 1734 item.url = verIt->url(); 1735 item.version = verIt->guessedVersion(); 1736 1737 // we use lower precision than the normal QDateTime 1738 item.timestamp = QDateTime::fromSecsSinceEpoch(verIt->lastModified().toSecsSinceEpoch()); 1739 1740 item.resourceId = id; 1741 1742 if (detectedResourceId < 0 && id >= 0) { 1743 detectedResourceId = id; 1744 } 1745 1746 resourcesInStorage.append(item); 1747 } 1748 1749 /// Assign the detected resource id to all the versions of 1750 /// this resource (if they are not present in the database). 1751 /// If no id has been detected, then a fake one will be assigned. 1752 1753 for (int i = firstResourceVersionPosition; i < resourcesInStorage.size(); i++) { 1754 if (resourcesInStorage[i].resourceId < 0) { 1755 resourcesInStorage[i].resourceId = detectedResourceId; 1756 } 1757 } 1758 1759 nextInexistentResourceId++; 1760 } 1761 1762 1763 /// Secondly, fetch the resources present in the database 1764 1765 QVector<ResourceVersion> resourcesInDatabase; 1766 1767 QSqlQuery q; 1768 q.setForwardOnly(true); 1769 if (!q.prepare("SELECT versioned_resources.resource_id, versioned_resources.filename, versioned_resources.version, versioned_resources.timestamp\n" 1770 "FROM versioned_resources\n" 1771 ", resource_types\n" 1772 ", resources\n" 1773 "WHERE resources.resource_type_id = resource_types.id\n" 1774 "AND resources.id = versioned_resources.resource_id\n" 1775 "AND resource_types.name = :resource_type\n" 1776 "AND versioned_resources.storage_id == :storage_id")) { 1777 qWarning() << "Could not prepare resource by type query" << q.lastError(); 1778 success = false; 1779 continue; 1780 } 1781 1782 q.bindValue(":resource_type", resourceType); 1783 q.bindValue(":storage_id", int(storage->storageId())); 1784 1785 if (!q.exec()) { 1786 qWarning() << "Could not exec resource by type query" << q.boundValues() << q.lastError(); 1787 success = false; 1788 continue; 1789 } 1790 1791 while (q.next()) { 1792 ResourceVersion item; 1793 item.url = resourceType + "/" + q.value(1).toString(); 1794 item.version = q.value(2).toInt(); 1795 item.timestamp = QDateTime::fromSecsSinceEpoch(q.value(3).toInt()); 1796 item.resourceId = q.value(0).toInt(); 1797 1798 resourcesInDatabase.append(item); 1799 } 1800 1801 QSet<int> resourceIdForUpdate; 1802 1803 std::sort(resourcesInStorage.begin(), resourcesInStorage.end()); 1804 std::sort(resourcesInDatabase.begin(), resourcesInDatabase.end()); 1805 1806 auto itA = resourcesInStorage.begin(); 1807 auto endA = resourcesInStorage.end(); 1808 1809 auto itB = resourcesInDatabase.begin(); 1810 auto endB = resourcesInDatabase.end(); 1811 1812 /// The head of itA array contains some resources with fake 1813 /// (negative) resourceId. These resources are obviously new 1814 /// resources and should be added to the cache database. 1815 1816 while (itA != endA) { 1817 if (itA->resourceId >= 0) break; 1818 1819 KoResourceSP res = storage->resource(itA->url); 1820 1821 if (!res) { 1822 KisUsageLogger::log("Could not load resource " + itA->url); 1823 ++itA; 1824 continue; 1825 } 1826 1827 res->setVersion(itA->version); 1828 res->setMD5Sum(storage->resourceMd5(itA->url)); 1829 if (!res->valid()) { 1830 KisUsageLogger::log("Could not retrieve md5 for resource " + itA->url); 1831 ++itA; 1832 continue; 1833 } 1834 1835 const bool retval = addResource(storage, itA->timestamp, res, resourceType); 1836 if (!retval) { 1837 KisUsageLogger::log("Could not add resource " + itA->url); 1838 ++itA; 1839 continue; 1840 } 1841 1842 const int resourceId = res->resourceId(); 1843 KIS_SAFE_ASSERT_RECOVER(resourceId >= 0) { 1844 KisUsageLogger::log("Could not get id for resource " + itA->url); 1845 ++itA; 1846 continue; 1847 } 1848 1849 auto nextResource = std::upper_bound(itA, endA, *itA, ResourceVersion::CompareByResourceId()); 1850 for (auto it = std::next(itA); it != nextResource; ++it) { 1851 KoResourceSP res = storage->resource(it->url); 1852 res->setVersion(it->version); 1853 res->setMD5Sum(storage->resourceMd5(it->url)); 1854 if (!res->valid()) { 1855 continue; 1856 } 1857 1858 const bool retval = addResourceVersion(resourceId, it->timestamp, storage, res); 1859 KIS_SAFE_ASSERT_RECOVER(retval) { 1860 KisUsageLogger::log("Could not add version for resource " + itA->url); 1861 continue; 1862 } 1863 } 1864 1865 itA = nextResource; 1866 } 1867 1868 /// Now both arrays are sorted in resourceId/version/timestamp 1869 /// order. It lets us easily find the resources that are unique 1870 /// to the storage or database. If *itA < *itB, then the resource 1871 /// is present in the storage only and should be added to the 1872 /// database. If *itA > *itB, then the resource is present in 1873 /// the database only and should be removed (because it has been 1874 /// removed from the storage); 1875 1876 while (itA != endA || itB != endB) { 1877 if ((itA != endA && itB != endB && *itA < *itB) || 1878 itB == endB) { 1879 1880 // add a version to the database 1881 1882 KoResourceSP res = storage->resource(itA->url); 1883 if (res) { 1884 res->setVersion(itA->version); 1885 res->setMD5Sum(storage->resourceMd5(itA->url)); 1886 1887 const bool result = addResourceVersionImpl(itA->resourceId, itA->timestamp, storage, res); 1888 KIS_SAFE_ASSERT_RECOVER_NOOP(result); 1889 1890 resourceIdForUpdate.insert(itA->resourceId); 1891 } 1892 ++itA; 1893 1894 } else if ((itA != endA && itB != endB && *itA > *itB) || 1895 itA == endA) { 1896 1897 // remove a version from the database 1898 const bool result = removeResourceVersionImpl(itB->resourceId, itB->version, storage); 1899 KIS_SAFE_ASSERT_RECOVER_NOOP(result); 1900 resourceIdForUpdate.insert(itB->resourceId); 1901 ++itB; 1902 1903 } else { 1904 // resources are equal, just skip them 1905 ++itA; 1906 ++itB; 1907 } 1908 } 1909 1910 1911 /// In the main loop we modified the versioned_resource table only, 1912 /// now we should update the head resources table with the latest 1913 /// version of the resource (and upload the thumbnail as well) 1914 1915 for (auto it = resourceIdForUpdate.begin(); it != resourceIdForUpdate.end(); ++it) { 1916 updateResourceTableForResourceIfNeeded(*it, resourceType, storage); 1917 } 1918 } 1919 1920 QSqlDatabase::database().commit(); 1921 debugResource << "Synchronizing the storages took" << t.elapsed() << "milliseconds for" << storage->location(); 1922 1923 return success; 1924 } 1925 1926 void KisResourceCacheDb::deleteTemporaryResources() 1927 { 1928 QSqlDatabase::database().transaction(); 1929 1930 QSqlQuery q; 1931 1932 if (!q.prepare("DELETE FROM versioned_resources\n" 1933 "WHERE storage_id in (SELECT id\n" 1934 " FROM storages\n" 1935 " WHERE storage_type_id == :storage_type)")) 1936 { 1937 qWarning() << "Could not prepare delete versioned resources from Unknown or Memory storages query." << q.lastError(); 1938 } 1939 1940 q.bindValue(":storage_type", (int)KisResourceStorage::StorageType::Memory); 1941 1942 if (!q.exec()) { 1943 qWarning() << "Could not execute delete versioned resources from Unknown or Memory storages query." << q.lastError(); 1944 } 1945 1946 if (!q.prepare("DELETE FROM resources\n" 1947 "WHERE storage_id in (SELECT id\n" 1948 " FROM storages\n" 1949 " WHERE storage_type_id == :storage_type)")) 1950 { 1951 qWarning() << "Could not prepare delete resources from Unknown or Memory storages query." << q.lastError(); 1952 } 1953 1954 q.bindValue(":storage_type", (int)KisResourceStorage::StorageType::Memory); 1955 1956 if (!q.exec()) { 1957 qWarning() << "Could not execute delete resources from Unknown or Memory storages query." << q.lastError(); 1958 } 1959 1960 1961 if (!q.prepare("DELETE FROM versioned_resources\n" 1962 "WHERE resource_id IN (SELECT id FROM resources\n" 1963 " WHERE temporary = 1)")) { 1964 qWarning() << "Could not prepare delete temporary versioned resources query." << q.lastError(); 1965 } 1966 1967 if (!q.exec()) { 1968 qWarning() << "Could not execute delete temporary versioned resources query." << q.lastError(); 1969 } 1970 1971 if (!q.prepare("DELETE FROM resources\n" 1972 "WHERE temporary = 1")) { 1973 qWarning() << "Could not prepare delete temporary resources query." << q.lastError(); 1974 return; 1975 } 1976 1977 if (!q.exec()) { 1978 qWarning() << "Could not execute delete temporary resources query." << q.lastError(); 1979 } 1980 1981 if (!q.prepare("DELETE FROM storages\n" 1982 "WHERE storage_type_id == :storage_type\n")) 1983 { 1984 qWarning() << "Could not prepare delete Unknown or Memory storages query." << q.lastError(); 1985 } 1986 1987 q.bindValue(":storage_type", (int)KisResourceStorage::StorageType::Memory); 1988 1989 if (!q.exec()) { 1990 qWarning() << "Could not execute delete Unknown or Memory storages query." << q.lastError(); 1991 } 1992 1993 QSqlDatabase::database().commit(); 1994 } 1995 1996 bool KisResourceCacheDb::registerResourceType(const QString &resourceType) 1997 { 1998 // Check whether the type already exists 1999 { 2000 QSqlQuery q; 2001 if (!q.prepare("SELECT count(*)\n" 2002 "FROM resource_types\n" 2003 "WHERE name = :resource_type\n")) { 2004 qWarning() << "Could not prepare select from resource_types query" << q.lastError(); 2005 return false; 2006 } 2007 q.bindValue(":resource_type", resourceType); 2008 if (!q.exec()) { 2009 qWarning() << "Could not execute select from resource_types query" << q.lastError(); 2010 return false; 2011 } 2012 q.first(); 2013 int rowCount = q.value(0).toInt(); 2014 if (rowCount > 0) { 2015 return true; 2016 } 2017 } 2018 // if not, add it 2019 QFile f(":/fill_resource_types.sql"); 2020 if (f.open(QFile::ReadOnly)) { 2021 QString sql = f.readAll(); 2022 QSqlQuery q(sql); 2023 q.addBindValue(resourceType); 2024 if (!q.exec()) { 2025 qWarning() << "Could not insert" << resourceType << q.lastError(); 2026 return false; 2027 } 2028 return true; 2029 } 2030 qWarning() << "Could not open fill_resource_types.sql"; 2031 return false; 2032 } 2033 2034 QMap<QString, QVariant> KisResourceCacheDb::metaDataForId(int id, const QString &tableName) 2035 { 2036 QMap<QString, QVariant> map; 2037 2038 QSqlQuery q; 2039 q.setForwardOnly(true); 2040 if (!q.prepare("SELECT key\n" 2041 ", value\n" 2042 "FROM metadata\n" 2043 "WHERE foreign_id = :id\n" 2044 "AND table_name = :table")) { 2045 qWarning() << "Could not prepare metadata query" << q.lastError(); 2046 return map; 2047 } 2048 2049 q.bindValue(":id", id); 2050 q.bindValue(":table", tableName); 2051 2052 if (!q.exec()) { 2053 qWarning() << "Could not execute metadata query" << q.lastError(); 2054 return map; 2055 } 2056 2057 while (q.next()) { 2058 QString key = q.value(0).toString(); 2059 QByteArray ba = q.value(1).toByteArray(); 2060 if (!ba.isEmpty()) { 2061 QDataStream ds(QByteArray::fromBase64(ba)); 2062 QVariant value; 2063 ds >> value; 2064 map[key] = value; 2065 } 2066 } 2067 2068 return map; 2069 } 2070 2071 bool KisResourceCacheDb::updateMetaDataForId(const QMap<QString, QVariant> map, int id, const QString &tableName) 2072 { 2073 QSqlDatabase::database().transaction(); 2074 2075 { 2076 QSqlQuery q; 2077 if (!q.prepare("DELETE FROM metadata\n" 2078 "WHERE foreign_id = :id\n" 2079 "AND table_name = :table\n")) { 2080 qWarning() << "Could not prepare delete metadata query" << q.lastError(); 2081 return false; 2082 } 2083 2084 q.bindValue(":id", id); 2085 q.bindValue(":table", tableName); 2086 2087 if (!q.exec()) { 2088 QSqlDatabase::database().rollback(); 2089 qWarning() << "Could not execute delete metadata query" << q.lastError(); 2090 return false; 2091 2092 } 2093 } 2094 2095 if (addMetaDataForId(map, id, tableName)) { 2096 QSqlDatabase::database().commit(); 2097 } 2098 else { 2099 QSqlDatabase::database().rollback(); 2100 } 2101 return true; 2102 } 2103 2104 bool KisResourceCacheDb::addMetaDataForId(const QMap<QString, QVariant> map, int id, const QString &tableName) 2105 { 2106 2107 QSqlQuery q; 2108 if (!q.prepare("INSERT INTO metadata\n" 2109 "(foreign_id, table_name, key, value)\n" 2110 "VALUES\n" 2111 "(:id, :table, :key, :value)")) { 2112 QSqlDatabase::database().rollback(); 2113 qWarning() << "Could not create insert metadata query" << q.lastError(); 2114 return false; 2115 } 2116 2117 QMap<QString, QVariant>::const_iterator iter = map.cbegin(); 2118 while (iter != map.cend()) { 2119 q.bindValue(":id", id); 2120 q.bindValue(":table", tableName); 2121 q.bindValue(":key", iter.key()); 2122 2123 QVariant v = iter.value(); 2124 if (!v.isNull() && v.isValid()) { 2125 QByteArray ba; 2126 QDataStream ds(&ba, QIODevice::WriteOnly); 2127 ds << v; 2128 ba = ba.toBase64(); 2129 q.bindValue(":value", QString::fromLatin1(ba)); 2130 2131 if (!q.exec()) { 2132 qWarning() << "Could not insert metadata" << q.lastError(); 2133 return false; 2134 } 2135 } 2136 ++iter; 2137 } 2138 return true; 2139 }