src/core/abstractsqlstorage.cpp

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