src/core/abstractsqlstorage.cpp

   1 /***************************************************************************
   2  *   Copyright (C) 2005-2020 by the Quassel Project                        *
   3  *   devel@quassel-irc.org                                                 *
   4  *                                                                         *
   5  *   This program is free software; you can redistribute it and/or modify  *
   6  *   it under the terms of the GNU General Public License as published by  *
   7  *   the Free Software Foundation; either version 2 of the License, or     *
   8  *   (at your option) version 3.                                           *
   9  *                                                                         *
  10  *   This program is distributed in the hope that it will be useful,       *
  11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  13  *   GNU General Public License for more details.                          *
  14  *                                                                         *
  15  *   You should have received a copy of the GNU General Public License     *
  16  *   along with this program; if not, write to the                         *
  17  *   Free Software Foundation, Inc.,                                       *
  18  *   51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.         *
  19  ***************************************************************************/
  20
  21 #include "abstractsqlstorage.h"
  22
  23 #include <QDir>
  24 #include <QFileInfo>
  25 #include <QMutexLocker>
  26 #include <QSqlDriver>
  27 #include <QSqlError>
  28 #include <QSqlField>
  29 #include <QSqlQuery>
  30 #include <QThread>
  31
  32 #include "quassel.h"
  33
  34 int AbstractSqlStorage::_nextConnectionId = 0;
  35 AbstractSqlStorage::AbstractSqlStorage(QObject* parent)
  36     : Storage(parent)
  37 {}
  38
  39 AbstractSqlStorage::~AbstractSqlStorage()
  40 {
  41     // disconnect the connections, so their deletion is no longer interessting for us
  42     QHash<QThread*, Connection*>::iterator conIter;
  43     for (conIter = _connectionPool.begin(); conIter != _connectionPool.end(); ++conIter) {
  44         QSqlDatabase::removeDatabase(conIter.value()->name());
  45         disconnect(conIter.value(), nullptr, this, nullptr);
  46     }
  47 }
  48
  49 QSqlDatabase AbstractSqlStorage::logDb()
  50 {
  51     if (!_connectionPool.contains(QThread::currentThread()))
  52         addConnectionToPool();
  53
  54     QSqlDatabase db = QSqlDatabase::database(_connectionPool[QThread::currentThread()]->name(), false);
  55
  56     if (!db.isOpen()) {
  57         qWarning() << "Database connection" << displayName() << "for thread" << QThread::currentThread()
  58                    << "was lost, attempting to reconnect...";
  59         dbConnect(db);
  60     }
  61
  62     return db;
  63 }
  64
  65 void AbstractSqlStorage::addConnectionToPool()
  66 {
  67     QMutexLocker locker(&_connectionPoolMutex);
  68     // we have to recheck if the connection pool already contains a connection for
  69     // this thread. Since now (after the lock) we can only tell for sure
  70     if (_connectionPool.contains(QThread::currentThread()))
  71         return;
  72
  73     QThread* currentThread = QThread::currentThread();
  74
  75     int connectionId = _nextConnectionId++;
  76
  77     Connection* connection = new Connection(QLatin1String(QString("quassel_%1_con_%2").arg(driverName()).arg(connectionId).toLatin1()));
  78     connection->moveToThread(currentThread);
  79     connect(this, &QObject::destroyed, connection, &QObject::deleteLater);
  80     connect(currentThread, &QObject::destroyed, connection, &QObject::deleteLater);
  81     connect(connection, &QObject::destroyed, this, &AbstractSqlStorage::connectionDestroyed);
  82     _connectionPool[currentThread] = connection;
  83
  84     QSqlDatabase db = QSqlDatabase::addDatabase(driverName(), connection->name());
  85     db.setDatabaseName(databaseName());
  86
  87     if (!hostName().isEmpty())
  88         db.setHostName(hostName());
  89
  90     if (port() != -1)
  91         db.setPort(port());
  92
  93     if (!userName().isEmpty()) {
  94         db.setUserName(userName());
  95         db.setPassword(password());
  96     }
  97
  98     dbConnect(db);
  99 }
 100
 101 void AbstractSqlStorage::dbConnect(QSqlDatabase& db)
 102 {
 103     if (!db.open()) {
 104         qWarning() << "Unable to open database" << displayName() << "for thread" << QThread::currentThread();
 105         qWarning() << "-" << db.lastError().text();
 106     }
 107     else {
 108         if (!initDbSession(db)) {
 109             qWarning() << "Unable to initialize database" << displayName() << "for thread" << QThread::currentThread();
 110             db.close();
 111         }
 112     }
 113 }
 114
 115 Storage::State AbstractSqlStorage::init(const QVariantMap& settings, const QProcessEnvironment& environment, bool loadFromEnvironment)
 116 {
 117     setConnectionProperties(settings, environment, loadFromEnvironment);
 118
 119     _debug = Quassel::isOptionSet("debug");
 120
 121     QSqlDatabase db = logDb();
 122     if (!db.isValid() || !db.isOpen())
 123         return NotAvailable;
 124
 125     if (installedSchemaVersion() == -1) {
 126         qCritical() << "Storage Schema is missing!";
 127         return NeedsSetup;
 128     }
 129
 130     if (installedSchemaVersion() > schemaVersion()) {
 131         qCritical() << "Installed Schema is newer then any known Version.";
 132         return NotAvailable;
 133     }
 134
 135     if (installedSchemaVersion() < schemaVersion()) {
 136         qInfo() << qPrintable(tr("Installed database schema (version %1) is not up to date. Upgrading to "
 137                                   "version %2...  This may take a while for major upgrades.")
 138                                    .arg(installedSchemaVersion())
 139                                    .arg(schemaVersion()));
 140         emit dbUpgradeInProgress(true);
 141         auto upgradeResult = upgradeDb();
 142         emit dbUpgradeInProgress(false);
 143         if (!upgradeResult) {
 144             qWarning() << qPrintable(tr("Upgrade failed..."));
 145             return NotAvailable;
 146         }
 147         // Add a message when migration succeeds to avoid confusing folks by implying the schema upgrade failed if
 148         // later functionality does not work.
 149         qInfo() << qPrintable(tr("Installed database schema successfully upgraded to version %1.").arg(schemaVersion()));
 150     }
 151
 152     qInfo() << qPrintable(displayName()) << "storage backend is ready. Schema version:" << installedSchemaVersion();
 153     return IsReady;
 154 }
 155
 156 QString AbstractSqlStorage::queryString(const QString& queryName, int version)
 157 {
 158     QFileInfo queryInfo;
 159
 160     // The current schema is stored in the root folder, while upgrade queries are stored in the
 161     // 'versions/##' subfolders.
 162     if (version == 0) {
 163         // Use the current SQL schema, not a versioned request
 164         queryInfo = QFileInfo(QString(":/SQL/%1/%2.sql").arg(displayName()).arg(queryName));
 165         // If version is needed later, get it via version = schemaVersion();
 166     }
 167     else {
 168         // Use the specified schema version, not the general folder
 169         queryInfo = QFileInfo(QString(":/SQL/%1/version/%2/%3.sql").arg(displayName()).arg(version).arg(queryName));
 170     }
 171
 172     if (!queryInfo.exists() || !queryInfo.isFile() || !queryInfo.isReadable()) {
 173         qCritical() << "Unable to read SQL-Query" << queryName << "for engine" << displayName();
 174         return QString();
 175     }
 176
 177     QFile queryFile(queryInfo.filePath());
 178     if (!queryFile.open(QIODevice::ReadOnly | QIODevice::Text))
 179         return QString();
 180     QString query = QTextStream(&queryFile).readAll();
 181     queryFile.close();
 182
 183     return query.trimmed();
 184 }
 185
 186 std::vector<AbstractSqlStorage::SqlQueryResource> AbstractSqlStorage::setupQueries()
 187 {
 188     std::vector<SqlQueryResource> queries;
 189     // The current schema is stored in the root folder, including setup scripts.
 190     QDir dir = QDir(QString(":/SQL/%1/").arg(displayName()));
 191     foreach (QFileInfo fileInfo, dir.entryInfoList(QStringList() << "setup*", QDir::NoFilter, QDir::Name)) {
 192         queries.emplace_back(queryString(fileInfo.baseName()), fileInfo.baseName());
 193     }
 194     return queries;
 195 }
 196
 197 bool AbstractSqlStorage::setup(const QVariantMap& settings, const QProcessEnvironment& environment, bool loadFromEnvironment)
 198 {
 199     setConnectionProperties(settings, environment, loadFromEnvironment);
 200     QSqlDatabase db = logDb();
 201     if (!db.isOpen()) {
 202         qCritical() << "Unable to setup Logging Backend!";
 203         return false;
 204     }
 205
 206     db.transaction();
 207     foreach (auto queryResource, setupQueries()) {
 208         QSqlQuery query = db.exec(queryResource.queryString);
 209         if (!watchQuery(query)) {
 210             qCritical() << qPrintable(QString("Unable to setup Logging Backend!  Setup query failed (step: %1).")
 211                                       .arg(queryResource.queryFilename));
 212             db.rollback();
 213             return false;
 214         }
 215     }
 216     bool success = setupSchemaVersion(schemaVersion());
 217     if (success)
 218         db.commit();
 219     else
 220         db.rollback();
 221     return success;
 222 }
 223
 224 std::vector<AbstractSqlStorage::SqlQueryResource> AbstractSqlStorage::upgradeQueries(int version)
 225 {
 226     std::vector<SqlQueryResource> queries;
 227     // Upgrade queries are stored in the 'version/##' subfolders.
 228     QDir dir = QDir(QString(":/SQL/%1/version/%2/").arg(displayName()).arg(version));
 229     foreach (QFileInfo fileInfo, dir.entryInfoList(QStringList() << "upgrade*", QDir::NoFilter, QDir::Name)) {
 230         queries.emplace_back(queryString(fileInfo.baseName(), version), fileInfo.baseName());
 231     }
 232     return queries;
 233 }
 234
 235 bool AbstractSqlStorage::upgradeDb()
 236 {
 237     if (schemaVersion() <= installedSchemaVersion())
 238         return true;
 239
 240     QSqlDatabase db = logDb();
 241
 242     // TODO: For databases that support it (e.g. almost only PostgreSQL), wrap upgrades in a
 243     // transaction.  This will need careful testing of potential additional space requirements and
 244     // any database modifications that might not be allowed in a transaction.
 245
 246     // Check if we're resuming an interrupted multi-step upgrade: is an upgrade step stored?
 247     const QString previousLaunchUpgradeStep = schemaVersionUpgradeStep();
 248     bool resumingUpgrade = !previousLaunchUpgradeStep.isEmpty();
 249
 250     for (int ver = installedSchemaVersion() + 1; ver <= schemaVersion(); ver++) {
 251         foreach (auto queryResource, upgradeQueries(ver)) {
 252             if (resumingUpgrade) {
 253                 // An upgrade was interrupted.  Check if this matches the the last successful query.
 254                 if (previousLaunchUpgradeStep == queryResource.queryFilename) {
 255                     // Found the matching query!
 256                     qInfo() << qPrintable(QString("Resuming interrupted upgrade for schema version %1 (last step: %2)")
 257                                           .arg(QString::number(ver), previousLaunchUpgradeStep));
 258
 259                     // Stop searching for queries
 260                     resumingUpgrade = false;
 261                     // Continue past the previous query with the next not-yet-tried query
 262                     continue;
 263                 }
 264                 else {
 265                     // Not yet matched, keep looking
 266                     continue;
 267                 }
 268             }
 269
 270             // Run the upgrade query
 271             QSqlQuery query = db.exec(queryResource.queryString);
 272             if (!watchQuery(query)) {
 273                 // Individual upgrade query failed, bail out
 274                 qCritical() << qPrintable(QString("Unable to upgrade Logging Backend!  Upgrade query in schema version %1 failed (step: %2).")
 275                                           .arg(QString::number(ver), queryResource.queryFilename));
 276                 return false;
 277             }
 278             else {
 279                 // Mark as successful
 280                 setSchemaVersionUpgradeStep(queryResource.queryFilename);
 281             }
 282         }
 283
 284         if (resumingUpgrade) {
 285             // Something went wrong and the last successful SQL query to resume from couldn't be
 286             // found.
 287             // 1.  The storage of successful query glitched, or the database was manually changed
 288             // 2.  Quassel changed the filenames of upgrade queries, and the local Quassel core
 289             //     version was replaced during an interrupted schema upgrade
 290             //
 291             // Both are unlikely, but it's a good idea to handle it anyways.
 292
 293             qCritical() << qPrintable(QString("Unable to resume interrupted upgrade in Logging "
 294                                               "Backend!  Missing upgrade step in schema version %1 "
 295                                               "(expected step: %2)")
 296                                       .arg(QString::number(ver), previousLaunchUpgradeStep));
 297             return false;
 298         }
 299
 300         // Update the schema version for each intermediate step and mark the step as done.  This
 301         // ensures that any interrupted upgrades have a greater chance of resuming correctly after
 302         // core restart.
 303         //
 304         // Almost all databases make single queries atomic (fully works or fully fails, no partial),
 305         // and with many of the longest migrations being a single query, this makes upgrade
 306         // interruptions much more likely to leave the database in a valid intermediate schema
 307         // version.
 308         if (!updateSchemaVersion(ver, true)) {
 309             // Updating the schema version failed, bail out
 310             qCritical() << "Unable to upgrade Logging Backend!  Setting schema version" << ver << "failed.";
 311             return false;
 312         }
 313     }
 314
 315     // If we made it here, everything seems to have worked!
 316     return true;
 317 }
 318
 319 int AbstractSqlStorage::schemaVersion()
 320 {
 321     // returns the newest Schema Version!
 322     // not the currently used one! (though it can be the same)
 323     if (_schemaVersion > 0)
 324         return _schemaVersion;
 325
 326     int version;
 327     bool ok;
 328     // Schema versions are stored in the 'version/##' subfolders.
 329     QDir dir = QDir(QString(":/SQL/%1/version/").arg(displayName()));
 330     foreach (QFileInfo fileInfo, dir.entryInfoList()) {
 331         if (!fileInfo.isDir())
 332             continue;
 333
 334         version = fileInfo.fileName().toInt(&ok);
 335         if (!ok)
 336             continue;
 337
 338         if (version > _schemaVersion)
 339             _schemaVersion = version;
 340     }
 341     return _schemaVersion;
 342 }
 343
 344
 345 QString AbstractSqlStorage::schemaVersionUpgradeStep()
 346 {
 347     // By default, assume there's no pending upgrade
 348     return {};
 349 }
 350
 351
 352 bool AbstractSqlStorage::watchQuery(QSqlQuery& query)
 353 {
 354     bool queryError = query.lastError().isValid();
 355     if (queryError || _debug) {
 356         if (queryError)
 357             qCritical() << "unhandled Error in QSqlQuery!";
 358         qCritical() << "                  last Query:\n" << qPrintable(query.lastQuery());
 359         qCritical() << "              executed Query:\n" << qPrintable(query.executedQuery());
 360         QVariantMap boundValues = query.boundValues();
 361         QStringList valueStrings;
 362         QVariantMap::const_iterator iter;
 363         for (iter = boundValues.constBegin(); iter != boundValues.constEnd(); ++iter) {
 364             QString value;
 365             QSqlField field;
 366             if (query.driver()) {
 367                 // let the driver do the formatting
 368                 field.setType(iter.value().type());
 369                 if (iter.value().isNull())
 370                     field.clear();
 371                 else
 372                     field.setValue(iter.value());
 373                 value = query.driver()->formatValue(field);
 374             }
 375             else {
 376                 switch (iter.value().type()) {
 377                 case QVariant::Invalid:
 378                     value = "NULL";
 379                     break;
 380                 case QVariant::Int:
 381                     value = iter.value().toString();
 382                     break;
 383                 default:
 384                     value = QString("'%1'").arg(iter.value().toString());
 385                 }
 386             }
 387             valueStrings << QString("%1=%2").arg(iter.key(), value);
 388         }
 389         qCritical() << "                bound Values:" << qPrintable(valueStrings.join(", "));
 390         qCritical() << "                  Error Code:" << qPrintable(query.lastError().nativeErrorCode());
 391         qCritical() << "               Error Message:" << qPrintable(query.lastError().text());
 392         qCritical() << "              Driver Message:" << qPrintable(query.lastError().driverText());
 393         qCritical() << "                  DB Message:" << qPrintable(query.lastError().databaseText());
 394
 395         return !queryError;
 396     }
 397     return true;
 398 }
 399
 400 void AbstractSqlStorage::connectionDestroyed()
 401 {
 402     QMutexLocker locker(&_connectionPoolMutex);
 403     _connectionPool.remove(sender()->thread());
 404 }
 405
 406 // ========================================
 407 //  AbstractSqlStorage::Connection
 408 // ========================================
 409 AbstractSqlStorage::Connection::Connection(const QString& name, QObject* parent)
 410     : QObject(parent)
 411     , _name(name.toLatin1())
 412 {}
 413
 414 AbstractSqlStorage::Connection::~Connection()
 415 {
 416     {
 417         QSqlDatabase db = QSqlDatabase::database(name(), false);
 418         if (db.isOpen()) {
 419             db.commit();
 420             db.close();
 421         }
 422     }
 423     QSqlDatabase::removeDatabase(name());
 424 }
 425
 426 // ========================================
 427 //  AbstractSqlMigrator
 428 // ========================================
 429
 430 void AbstractSqlMigrator::newQuery(const QString& query, QSqlDatabase db)
 431 {
 432     Q_ASSERT(!_query);
 433     _query = new QSqlQuery(db);
 434     _query->prepare(query);
 435 }
 436
 437 void AbstractSqlMigrator::resetQuery()
 438 {
 439     delete _query;
 440     _query = nullptr;
 441 }
 442
 443 bool AbstractSqlMigrator::exec()
 444 {
 445     Q_ASSERT(_query);
 446     _query->exec();
 447     return !_query->lastError().isValid();
 448 }
 449
 450 QString AbstractSqlMigrator::migrationObject(MigrationObject moType)
 451 {
 452     switch (moType) {
 453     case QuasselUser:
 454         return "QuasselUser";
 455     case Sender:
 456         return "Sender";
 457     case Identity:
 458         return "Identity";
 459     case IdentityNick:
 460         return "IdentityNick";
 461     case Network:
 462         return "Network";
 463     case Buffer:
 464         return "Buffer";
 465     case Backlog:
 466         return "Backlog";
 467     case IrcServer:
 468         return "IrcServer";
 469     case UserSetting:
 470         return "UserSetting";
 471     case CoreState:
 472         return "CoreState";
 473     };
 474     return QString();
 475 }
 476
 477 QVariantList AbstractSqlMigrator::boundValues()
 478 {
 479     QVariantList values;
 480     if (!_query)
 481         return values;
 482
 483     int numValues = _query->boundValues().count();
 484     for (int i = 0; i < numValues; i++) {
 485         values << _query->boundValue(i);
 486     }
 487     return values;
 488 }
 489
 490 void AbstractSqlMigrator::dumpStatus()
 491 {
 492     qWarning() << "  executed Query:";
 493     qWarning() << qPrintable(executedQuery());
 494     qWarning() << "  bound Values:";
 495     QList<QVariant> list = boundValues();
 496     for (int i = 0; i < list.size(); ++i)
 497         qWarning() << i << ": " << list.at(i).toString().toLatin1().data();
 498     qWarning() << "  Error Code:" << qPrintable(lastError().nativeErrorCode());
 499     qWarning() << "  Error Message:" << lastError().text();
 500 }
 501
 502 // ========================================
 503 //  AbstractSqlMigrationReader
 504 // ========================================
 505 AbstractSqlMigrationReader::AbstractSqlMigrationReader()
 506     : AbstractSqlMigrator()
 507 {}
 508
 509 bool AbstractSqlMigrationReader::migrateTo(AbstractSqlMigrationWriter* writer)
 510 {
 511     if (!transaction()) {
 512         qWarning() << "AbstractSqlMigrationReader::migrateTo(): unable to start reader's transaction!";
 513         return false;
 514     }
 515     if (!writer->transaction()) {
 516         qWarning() << "AbstractSqlMigrationReader::migrateTo(): unable to start writer's transaction!";
 517         rollback();  // close the reader transaction;
 518         return false;
 519     }
 520
 521     _writer = writer;
 522
 523     // due to the incompatibility across Migration objects we can't run this in a loop... :/
 524     QuasselUserMO quasselUserMo;
 525     if (!transferMo(QuasselUser, quasselUserMo))
 526         return false;
 527
 528     IdentityMO identityMo;
 529     if (!transferMo(Identity, identityMo))
 530         return false;
 531
 532     IdentityNickMO identityNickMo;
 533     if (!transferMo(IdentityNick, identityNickMo))
 534         return false;
 535
 536     NetworkMO networkMo;
 537     if (!transferMo(Network, networkMo))
 538         return false;
 539
 540     BufferMO bufferMo;
 541     if (!transferMo(Buffer, bufferMo))
 542         return false;
 543
 544     SenderMO senderMo;
 545     if (!transferMo(Sender, senderMo))
 546         return false;
 547
 548     BacklogMO backlogMo;
 549     if (!transferMo(Backlog, backlogMo))
 550         return false;
 551
 552     IrcServerMO ircServerMo;
 553     if (!transferMo(IrcServer, ircServerMo))
 554         return false;
 555
 556     UserSettingMO userSettingMo;
 557     if (!transferMo(UserSetting, userSettingMo))
 558         return false;
 559
 560     CoreStateMO coreStateMO;
 561     if (!transferMo(CoreState, coreStateMO))
 562         return false;
 563
 564     if (!_writer->postProcess())
 565         abortMigration();
 566     return finalizeMigration();
 567 }
 568
 569 void AbstractSqlMigrationReader::abortMigration(const QString& errorMsg)
 570 {
 571     qWarning() << "Migration Failed!";
 572     if (!errorMsg.isNull()) {
 573         qWarning() << qPrintable(errorMsg);
 574     }
 575     if (lastError().isValid()) {
 576         qWarning() << "ReaderError:";
 577         dumpStatus();
 578     }
 579
 580     if (_writer->lastError().isValid()) {
 581         qWarning() << "WriterError:";
 582         _writer->dumpStatus();
 583     }
 584
 585     rollback();
 586     _writer->rollback();
 587     _writer = nullptr;
 588 }
 589
 590 bool AbstractSqlMigrationReader::finalizeMigration()
 591 {
 592     resetQuery();
 593     _writer->resetQuery();
 594
 595     commit();
 596     if (!_writer->commit()) {
 597         _writer = nullptr;
 598         return false;
 599     }
 600     _writer = nullptr;
 601     return true;
 602 }
 603
 604 template<typename T>
 605 bool AbstractSqlMigrationReader::transferMo(MigrationObject moType, T& mo)
 606 {
 607     resetQuery();
 608     _writer->resetQuery();
 609
 610     if (!prepareQuery(moType)) {
 611         abortMigration(QString("AbstractSqlMigrationReader::migrateTo(): unable to prepare reader query of type %1!")
 612                            .arg(AbstractSqlMigrator::migrationObject(moType)));
 613         return false;
 614     }
 615     if (!_writer->prepareQuery(moType)) {
 616         abortMigration(QString("AbstractSqlMigrationReader::migrateTo(): unable to prepare writer query of type %1!")
 617                            .arg(AbstractSqlMigrator::migrationObject(moType)));
 618         return false;
 619     }
 620
 621     qDebug() << qPrintable(QString("Transferring %1...").arg(AbstractSqlMigrator::migrationObject(moType)));
 622     int i = 0;
 623     QFile file;
 624     file.open(stdout, QIODevice::WriteOnly);
 625
 626     while (readMo(mo)) {
 627         if (!_writer->writeMo(mo)) {
 628             abortMigration(QString("AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type %1!")
 629                                .arg(AbstractSqlMigrator::migrationObject(moType)));
 630             return false;
 631         }
 632         i++;
 633         if (i % 1000 == 0) {
 634             file.write("*");
 635             file.flush();
 636         }
 637     }
 638     if (i > 1000) {
 639         file.write("\n");
 640         file.flush();
 641     }
 642
 643     qDebug() << "Done.";
 644     return true;
 645 }
 646
 647 uint qHash(const SenderData& key)
 648 {
 649     return qHash(QString(key.sender + "\n" + key.realname + "\n" + key.avatarurl));
 650 }
 651
 652 bool operator==(const SenderData& a, const SenderData& b)
 653 {
 654     return a.sender == b.sender && a.realname == b.realname && a.avatarurl == b.avatarurl;
 655 }