src/core/abstractsqlstorage.cpp

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