src/core/abstractsqlstorage.cpp

   1 /***************************************************************************
   2  *   Copyright (C) 2005-2015 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 {
 121     setConnectionProperties(settings);
 122
 123     _debug = Quassel::isOptionSet("debug");
 124
 125     QSqlDatabase db = logDb();
 126     if (!db.isValid() || !db.isOpen())
 127         return NotAvailable;
 128
 129     if (installedSchemaVersion() == -1) {
 130         qCritical() << "Storage Schema is missing!";
 131         return NeedsSetup;
 132     }
 133
 134     if (installedSchemaVersion() > schemaVersion()) {
 135         qCritical() << "Installed Schema is newer then any known Version.";
 136         return NotAvailable;
 137     }
 138
 139     if (installedSchemaVersion() < schemaVersion()) {
 140         qWarning() << qPrintable(tr("Installed Schema (version %1) is not up to date. Upgrading to version %2...").arg(installedSchemaVersion()).arg(schemaVersion()));
 141         if (!upgradeDb()) {
 142             qWarning() << qPrintable(tr("Upgrade failed..."));
 143             return NotAvailable;
 144         }
 145     }
 146
 147     quInfo() << qPrintable(displayName()) << "Storage Backend is ready. Quassel Schema Version:" << installedSchemaVersion();
 148     return IsReady;
 149 }
 150
 151
 152 QString AbstractSqlStorage::queryString(const QString &queryName, int version)
 153 {
 154     if (version == 0)
 155         version = schemaVersion();
 156
 157     QFileInfo queryInfo(QString(":/SQL/%1/%2/%3.sql").arg(displayName()).arg(version).arg(queryName));
 158     if (!queryInfo.exists() || !queryInfo.isFile() || !queryInfo.isReadable()) {
 159         qCritical() << "Unable to read SQL-Query" << queryName << "for engine" << displayName();
 160         return QString();
 161     }
 162
 163     QFile queryFile(queryInfo.filePath());
 164     if (!queryFile.open(QIODevice::ReadOnly | QIODevice::Text))
 165         return QString();
 166     QString query = QTextStream(&queryFile).readAll();
 167     queryFile.close();
 168
 169     return query.trimmed();
 170 }
 171
 172
 173 QStringList AbstractSqlStorage::setupQueries()
 174 {
 175     QStringList queries;
 176     QDir dir = QDir(QString(":/SQL/%1/%2/").arg(displayName()).arg(schemaVersion()));
 177     foreach(QFileInfo fileInfo, dir.entryInfoList(QStringList() << "setup*", QDir::NoFilter, QDir::Name)) {
 178         queries << queryString(fileInfo.baseName());
 179     }
 180     return queries;
 181 }
 182
 183
 184 bool AbstractSqlStorage::setup(const QVariantMap &settings)
 185 {
 186     setConnectionProperties(settings);
 187     QSqlDatabase db = logDb();
 188     if (!db.isOpen()) {
 189         qCritical() << "Unable to setup Logging Backend!";
 190         return false;
 191     }
 192
 193     db.transaction();
 194     foreach(QString queryString, setupQueries()) {
 195         QSqlQuery query = db.exec(queryString);
 196         if (!watchQuery(query)) {
 197             qCritical() << "Unable to setup Logging Backend!";
 198             db.rollback();
 199             return false;
 200         }
 201     }
 202     bool success = setupSchemaVersion(schemaVersion());
 203     if (success)
 204         db.commit();
 205     else
 206         db.rollback();
 207     return success;
 208 }
 209
 210
 211 QStringList AbstractSqlStorage::upgradeQueries(int version)
 212 {
 213     QStringList queries;
 214     QDir dir = QDir(QString(":/SQL/%1/%2/").arg(displayName()).arg(version));
 215     foreach(QFileInfo fileInfo, dir.entryInfoList(QStringList() << "upgrade*", QDir::NoFilter, QDir::Name)) {
 216         queries << queryString(fileInfo.baseName(), version);
 217     }
 218     return queries;
 219 }
 220
 221
 222 bool AbstractSqlStorage::upgradeDb()
 223 {
 224     if (schemaVersion() <= installedSchemaVersion())
 225         return true;
 226
 227     QSqlDatabase db = logDb();
 228
 229     for (int ver = installedSchemaVersion() + 1; ver <= schemaVersion(); ver++) {
 230         foreach(QString queryString, upgradeQueries(ver)) {
 231             QSqlQuery query = db.exec(queryString);
 232             if (!watchQuery(query)) {
 233                 qCritical() << "Unable to upgrade Logging Backend!";
 234                 return false;
 235             }
 236         }
 237     }
 238     return updateSchemaVersion(schemaVersion());
 239 }
 240
 241
 242 int AbstractSqlStorage::schemaVersion()
 243 {
 244     // returns the newest Schema Version!
 245     // not the currently used one! (though it can be the same)
 246     if (_schemaVersion > 0)
 247         return _schemaVersion;
 248
 249     int version;
 250     bool ok;
 251     QDir dir = QDir(":/SQL/" + displayName());
 252     foreach(QFileInfo fileInfo, dir.entryInfoList()) {
 253         if (!fileInfo.isDir())
 254             continue;
 255
 256         version = fileInfo.fileName().toInt(&ok);
 257         if (!ok)
 258             continue;
 259
 260         if (version > _schemaVersion)
 261             _schemaVersion = version;
 262     }
 263     return _schemaVersion;
 264 }
 265
 266
 267 bool AbstractSqlStorage::watchQuery(QSqlQuery &query)
 268 {
 269     bool queryError = query.lastError().isValid();
 270     if (queryError || _debug) {
 271         if (queryError)
 272             qCritical() << "unhandled Error in QSqlQuery!";
 273         qCritical() << "                  last Query:\n" << qPrintable(query.lastQuery());
 274         qCritical() << "              executed Query:\n" << qPrintable(query.executedQuery());
 275         QVariantMap boundValues = query.boundValues();
 276         QStringList valueStrings;
 277         QVariantMap::const_iterator iter;
 278         for (iter = boundValues.constBegin(); iter != boundValues.constEnd(); iter++) {
 279             QString value;
 280             QSqlField field;
 281             if (query.driver()) {
 282                 // let the driver do the formatting
 283                 field.setType(iter.value().type());
 284                 if (iter.value().isNull())
 285                     field.clear();
 286                 else
 287                     field.setValue(iter.value());
 288                 value =  query.driver()->formatValue(field);
 289             }
 290             else {
 291                 switch (iter.value().type()) {
 292                 case QVariant::Invalid:
 293                     value = "NULL";
 294                     break;
 295                 case QVariant::Int:
 296                     value = iter.value().toString();
 297                     break;
 298                 default:
 299                     value = QString("'%1'").arg(iter.value().toString());
 300                 }
 301             }
 302             valueStrings << QString("%1=%2").arg(iter.key(), value);
 303         }
 304         qCritical() << "                bound Values:" << qPrintable(valueStrings.join(", "));
 305         qCritical() << "                Error Number:" << query.lastError().number();
 306         qCritical() << "               Error Message:" << qPrintable(query.lastError().text());
 307         qCritical() << "              Driver Message:" << qPrintable(query.lastError().driverText());
 308         qCritical() << "                  DB Message:" << qPrintable(query.lastError().databaseText());
 309
 310         return !queryError;
 311     }
 312     return true;
 313 }
 314
 315
 316 void AbstractSqlStorage::connectionDestroyed()
 317 {
 318     QMutexLocker locker(&_connectionPoolMutex);
 319     _connectionPool.remove(sender()->thread());
 320 }
 321
 322
 323 // ========================================
 324 //  AbstractSqlStorage::Connection
 325 // ========================================
 326 AbstractSqlStorage::Connection::Connection(const QString &name, QObject *parent)
 327     : QObject(parent),
 328     _name(name.toLatin1())
 329 {
 330 }
 331
 332
 333 AbstractSqlStorage::Connection::~Connection()
 334 {
 335     {
 336         QSqlDatabase db = QSqlDatabase::database(name(), false);
 337         if (db.isOpen()) {
 338             db.commit();
 339             db.close();
 340         }
 341     }
 342     QSqlDatabase::removeDatabase(name());
 343 }
 344
 345
 346 // ========================================
 347 //  AbstractSqlMigrator
 348 // ========================================
 349 AbstractSqlMigrator::AbstractSqlMigrator()
 350     : _query(0)
 351 {
 352 }
 353
 354
 355 void AbstractSqlMigrator::newQuery(const QString &query, QSqlDatabase db)
 356 {
 357     Q_ASSERT(!_query);
 358     _query = new QSqlQuery(db);
 359     _query->prepare(query);
 360 }
 361
 362
 363 void AbstractSqlMigrator::resetQuery()
 364 {
 365     delete _query;
 366     _query = 0;
 367 }
 368
 369
 370 bool AbstractSqlMigrator::exec()
 371 {
 372     Q_ASSERT(_query);
 373     _query->exec();
 374     return !_query->lastError().isValid();
 375 }
 376
 377
 378 QString AbstractSqlMigrator::migrationObject(MigrationObject moType)
 379 {
 380     switch (moType) {
 381     case QuasselUser:
 382         return "QuasselUser";
 383     case Sender:
 384         return "Sender";
 385     case Identity:
 386         return "Identity";
 387     case IdentityNick:
 388         return "IdentityNick";
 389     case Network:
 390         return "Network";
 391     case Buffer:
 392         return "Buffer";
 393     case Backlog:
 394         return "Backlog";
 395     case IrcServer:
 396         return "IrcServer";
 397     case UserSetting:
 398         return "UserSetting";
 399     };
 400     return QString();
 401 }
 402
 403
 404 QVariantList AbstractSqlMigrator::boundValues()
 405 {
 406     QVariantList values;
 407     if (!_query)
 408         return values;
 409
 410     int numValues = _query->boundValues().count();
 411     for (int i = 0; i < numValues; i++) {
 412         values << _query->boundValue(i);
 413     }
 414     return values;
 415 }
 416
 417
 418 void AbstractSqlMigrator::dumpStatus()
 419 {
 420     qWarning() << "  executed Query:";
 421     qWarning() << qPrintable(executedQuery());
 422     qWarning() << "  bound Values:";
 423     QList<QVariant> list = boundValues();
 424     for (int i = 0; i < list.size(); ++i)
 425         qWarning() << i << ": " << list.at(i).toString().toLatin1().data();
 426     qWarning() << "  Error Number:"   << lastError().number();
 427     qWarning() << "  Error Message:"   << lastError().text();
 428 }
 429
 430
 431 // ========================================
 432 //  AbstractSqlMigrationReader
 433 // ========================================
 434 AbstractSqlMigrationReader::AbstractSqlMigrationReader()
 435     : AbstractSqlMigrator(),
 436     _writer(0)
 437 {
 438 }
 439
 440
 441 bool AbstractSqlMigrationReader::migrateTo(AbstractSqlMigrationWriter *writer)
 442 {
 443     if (!transaction()) {
 444         qWarning() << "AbstractSqlMigrationReader::migrateTo(): unable to start reader's transaction!";
 445         return false;
 446     }
 447     if (!writer->transaction()) {
 448         qWarning() << "AbstractSqlMigrationReader::migrateTo(): unable to start writer's transaction!";
 449         rollback(); // close the reader transaction;
 450         return false;
 451     }
 452
 453     _writer = writer;
 454
 455     // due to the incompatibility across Migration objects we can't run this in a loop... :/
 456     QuasselUserMO quasselUserMo;
 457     if (!transferMo(QuasselUser, quasselUserMo))
 458         return false;
 459
 460     IdentityMO identityMo;
 461     if (!transferMo(Identity, identityMo))
 462         return false;
 463
 464     IdentityNickMO identityNickMo;
 465     if (!transferMo(IdentityNick, identityNickMo))
 466         return false;
 467
 468     NetworkMO networkMo;
 469     if (!transferMo(Network, networkMo))
 470         return false;
 471
 472     BufferMO bufferMo;
 473     if (!transferMo(Buffer, bufferMo))
 474         return false;
 475
 476     SenderMO senderMo;
 477     if (!transferMo(Sender, senderMo))
 478         return false;
 479
 480     BacklogMO backlogMo;
 481     if (!transferMo(Backlog, backlogMo))
 482         return false;
 483
 484     IrcServerMO ircServerMo;
 485     if (!transferMo(IrcServer, ircServerMo))
 486         return false;
 487
 488     UserSettingMO userSettingMo;
 489     if (!transferMo(UserSetting, userSettingMo))
 490         return false;
 491
 492     if (!_writer->postProcess())
 493         abortMigration();
 494     return finalizeMigration();
 495 }
 496
 497
 498 void AbstractSqlMigrationReader::abortMigration(const QString &errorMsg)
 499 {
 500     qWarning() << "Migration Failed!";
 501     if (!errorMsg.isNull()) {
 502         qWarning() << qPrintable(errorMsg);
 503     }
 504     if (lastError().isValid()) {
 505         qWarning() << "ReaderError:";
 506         dumpStatus();
 507     }
 508
 509     if (_writer->lastError().isValid()) {
 510         qWarning() << "WriterError:";
 511         _writer->dumpStatus();
 512     }
 513
 514     rollback();
 515     _writer->rollback();
 516     _writer = 0;
 517 }
 518
 519
 520 bool AbstractSqlMigrationReader::finalizeMigration()
 521 {
 522     resetQuery();
 523     _writer->resetQuery();
 524
 525     commit();
 526     if (!_writer->commit()) {
 527         _writer = 0;
 528         return false;
 529     }
 530     _writer = 0;
 531     return true;
 532 }
 533
 534
 535 template<typename T>
 536 bool AbstractSqlMigrationReader::transferMo(MigrationObject moType, T &mo)
 537 {
 538     resetQuery();
 539     _writer->resetQuery();
 540
 541     if (!prepareQuery(moType)) {
 542         abortMigration(QString("AbstractSqlMigrationReader::migrateTo(): unable to prepare reader query of type %1!").arg(AbstractSqlMigrator::migrationObject(moType)));
 543         return false;
 544     }
 545     if (!_writer->prepareQuery(moType)) {
 546         abortMigration(QString("AbstractSqlMigrationReader::migrateTo(): unable to prepare writer query of type %1!").arg(AbstractSqlMigrator::migrationObject(moType)));
 547         return false;
 548     }
 549
 550     qDebug() << qPrintable(QString("Transferring %1...").arg(AbstractSqlMigrator::migrationObject(moType)));
 551     int i = 0;
 552     QFile file;
 553     file.open(stdout, QIODevice::WriteOnly);
 554
 555     while (readMo(mo)) {
 556         if (!_writer->writeMo(mo)) {
 557             abortMigration(QString("AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type %1!").arg(AbstractSqlMigrator::migrationObject(moType)));
 558             return false;
 559         }
 560         i++;
 561         if (i % 1000 == 0) {
 562             file.write("*");
 563             file.flush();
 564         }
 565     }
 566     if (i > 1000) {
 567         file.write("\n");
 568         file.flush();
 569     }
 570
 571     qDebug() << "Done.";
 572     return true;
 573 }