src/core/abstractsqlstorage.cpp

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