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