src/core/abstractsqlstorage.cpp

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