src/core/postgresqlstorage.cpp

   1 /***************************************************************************
   2  *   Copyright (C) 2005-2019 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 "postgresqlstorage.h"
  22
  23 #include <QtSql>
  24
  25 #include "network.h"
  26 #include "quassel.h"
  27
  28 PostgreSqlStorage::PostgreSqlStorage(QObject* parent)
  29     : AbstractSqlStorage(parent)
  30 {}
  31
  32 std::unique_ptr<AbstractSqlMigrationWriter> PostgreSqlStorage::createMigrationWriter()
  33 {
  34     auto writer = new PostgreSqlMigrationWriter();
  35     QVariantMap properties;
  36     properties["Username"] = _userName;
  37     properties["Password"] = _password;
  38     properties["Hostname"] = _hostName;
  39     properties["Port"] = _port;
  40     properties["Database"] = _databaseName;
  41     writer->setConnectionProperties(properties, {}, false);
  42     return std::unique_ptr<AbstractSqlMigrationWriter>{writer};
  43 }
  44
  45 bool PostgreSqlStorage::isAvailable() const
  46 {
  47     if (!QSqlDatabase::isDriverAvailable("QPSQL")) {
  48         qWarning() << qPrintable(tr("PostgreSQL driver plugin not available for Qt. Installed drivers:"))
  49                     << qPrintable(QSqlDatabase::drivers().join(", "));
  50         return false;
  51     }
  52     return true;
  53 }
  54
  55 QString PostgreSqlStorage::backendId() const
  56 {
  57     return QString("PostgreSQL");
  58 }
  59
  60 QString PostgreSqlStorage::displayName() const
  61 {
  62     return backendId();  // Note: Pre-0.13 clients use the displayName property for backend idenfication
  63 }
  64
  65 QString PostgreSqlStorage::description() const
  66 {
  67     // FIXME: proper description
  68     return tr("PostgreSQL Turbo Bomber HD!");
  69 }
  70
  71 QVariantList PostgreSqlStorage::setupData() const
  72 {
  73     QVariantList data;
  74     data << "Username" << tr("Username") << QString("quassel") << "Password" << tr("Password") << QString() << "Hostname" << tr("Hostname")
  75          << QString("localhost") << "Port" << tr("Port") << 5432 << "Database" << tr("Database") << QString("quassel");
  76     return data;
  77 }
  78
  79 bool PostgreSqlStorage::initDbSession(QSqlDatabase& db)
  80 {
  81     // check whether the Qt driver performs string escaping or not.
  82     // i.e. test if it doubles slashes.
  83     QSqlField testField;
  84     testField.setType(QVariant::String);
  85     testField.setValue("\\");
  86     QString formattedString = db.driver()->formatValue(testField);
  87     switch (formattedString.count('\\')) {
  88     case 2:
  89         // yes it does... and we cannot do anything to change the behavior of Qt.
  90         // If this is a legacy DB (Postgres < 8.2), then everything is already ok,
  91         // as this is the expected behavior.
  92         // If it is a newer version, switch to legacy mode.
  93
  94         qWarning() << "Switching Postgres to legacy mode. (set standard conforming strings to off)";
  95         // If the following calls fail, it is a legacy DB anyways, so it doesn't matter
  96         // and no need to check the outcome.
  97         db.exec("set standard_conforming_strings = off");
  98         db.exec("set escape_string_warning = off");
  99         break;
 100     case 1:
 101         // ok, so Qt does not escape...
 102         // That means we have to ensure that postgres uses standard conforming strings...
 103         {
 104             QSqlQuery query = db.exec("set standard_conforming_strings = on");
 105             if (query.lastError().isValid()) {
 106                 // We cannot enable standard conforming strings...
 107                 // since Quassel does no escaping by itself, this would yield a major vulnerability.
 108                 qCritical() << "Failed to enable standard_conforming_strings for the Postgres db!";
 109                 return false;
 110             }
 111         }
 112         break;
 113     default:
 114         // The slash got replaced with 0 or more than 2 slashes! o_O
 115         qCritical() << "Your version of Qt does something _VERY_ strange to slashes in QSqlQueries! You should consult your trusted doctor!";
 116         return false;
 117         break;
 118     }
 119
 120     // Set the PostgreSQL session timezone to UTC, since we want timestamps stored in UTC
 121     QSqlQuery tzQuery = db.exec("SET timezone = 'UTC'");
 122     if (tzQuery.lastError().isValid()) {
 123         qCritical() << "Failed to set timezone to UTC!";
 124         return false;
 125     }
 126
 127     return true;
 128 }
 129
 130 void PostgreSqlStorage::setConnectionProperties(const QVariantMap& properties, const QProcessEnvironment& environment, bool loadFromEnvironment)
 131 {
 132     if (loadFromEnvironment) {
 133         _userName = environment.value("DB_PGSQL_USERNAME");
 134         _password = environment.value("DB_PGSQL_PASSWORD");
 135         _hostName = environment.value("DB_PGSQL_HOSTNAME");
 136         _port = environment.value("DB_PGSQL_PORT").toInt();
 137         _databaseName = environment.value("DB_PGSQL_DATABASE");
 138     }
 139     else {
 140         _userName = properties["Username"].toString();
 141         _password = properties["Password"].toString();
 142         _hostName = properties["Hostname"].toString();
 143         _port = properties["Port"].toInt();
 144         _databaseName = properties["Database"].toString();
 145     }
 146 }
 147
 148 int PostgreSqlStorage::installedSchemaVersion()
 149 {
 150     QSqlQuery query(logDb());
 151     query.prepare("SELECT value FROM coreinfo WHERE key = 'schemaversion'");
 152     safeExec(query);
 153     watchQuery(query);
 154     if (query.first())
 155         return query.value(0).toInt();
 156
 157     // maybe it's really old... (schema version 0)
 158     query.prepare("SELECT MAX(version) FROM coreinfo");
 159     safeExec(query);
 160     watchQuery(query);
 161     if (query.first())
 162         return query.value(0).toInt();
 163
 164     return AbstractSqlStorage::installedSchemaVersion();
 165 }
 166
 167 bool PostgreSqlStorage::updateSchemaVersion(int newVersion, bool clearUpgradeStep)
 168 {
 169     // Atomically update the schema version and clear the upgrade step, if specified
 170     // Note: This will need reworked if "updateSchemaVersion" is ever called within a transaction.
 171     QSqlDatabase db = logDb();
 172     if (!beginTransaction(db)) {
 173         qWarning() << "PostgreSqlStorage::updateSchemaVersion(int, bool): cannot start transaction!";
 174         qWarning() << " -" << qPrintable(db.lastError().text());
 175         return false;
 176     }
 177
 178     QSqlQuery query(db);
 179     query.prepare("UPDATE coreinfo SET value = :version WHERE key = 'schemaversion'");
 180     query.bindValue(":version", newVersion);
 181     safeExec(query);
 182
 183     if (!watchQuery(query)) {
 184         qCritical() << "PostgreSqlStorage::updateSchemaVersion(int, bool): Updating schema version failed!";
 185         db.rollback();
 186         return false;
 187     }
 188
 189     if (clearUpgradeStep) {
 190         // Try clearing the upgrade step if requested
 191         if (!setSchemaVersionUpgradeStep("")) {
 192             db.rollback();
 193             return false;
 194         }
 195     }
 196
 197     // Successful, commit and return true
 198     db.commit();
 199     return true;
 200 }
 201
 202 bool PostgreSqlStorage::setupSchemaVersion(int version)
 203 {
 204     QSqlQuery query(logDb());
 205     query.prepare("INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)");
 206     query.bindValue(":version", version);
 207     safeExec(query);
 208
 209     bool success = true;
 210     if (!watchQuery(query)) {
 211         qCritical() << "PostgreSqlStorage::setupSchemaVersion(int): Updating schema version failed!";
 212         success = false;
 213     }
 214     return success;
 215 }
 216
 217 QString PostgreSqlStorage::schemaVersionUpgradeStep()
 218 {
 219     QSqlQuery query(logDb());
 220     query.prepare("SELECT value FROM coreinfo WHERE key = 'schemaupgradestep'");
 221     safeExec(query);
 222     watchQuery(query);
 223     if (query.first())
 224         return query.value(0).toString();
 225
 226     // Fall back to the default value
 227     return AbstractSqlStorage::schemaVersionUpgradeStep();
 228 }
 229
 230 bool PostgreSqlStorage::setSchemaVersionUpgradeStep(QString upgradeQuery)
 231 {
 232     // Intentionally do not wrap in a transaction so other functions can include multiple operations
 233
 234     QSqlQuery query(logDb());
 235     query.prepare("UPDATE coreinfo SET value = :upgradestep WHERE key = 'schemaupgradestep'");
 236     query.bindValue(":upgradestep", upgradeQuery);
 237     safeExec(query);
 238
 239     // Make sure that the query didn't fail (shouldn't ever happen), and that some non-zero number
 240     // of rows were affected
 241     bool success = watchQuery(query) && query.numRowsAffected() != 0;
 242
 243     if (!success) {
 244         // The key might not exist (Quassel 0.13.0 and older).  Try inserting it...
 245         query = QSqlQuery(logDb());
 246         query.prepare("INSERT INTO coreinfo (key, value) VALUES ('schemaupgradestep', :upgradestep)");
 247         query.bindValue(":upgradestep", upgradeQuery);
 248         safeExec(query);
 249
 250         if (!watchQuery(query)) {
 251             qCritical() << Q_FUNC_INFO << "Setting schema upgrade step failed!";
 252             success = false;
 253         }
 254         else {
 255             success = true;
 256         }
 257     }
 258     return success;
 259 }
 260
 261 UserId PostgreSqlStorage::addUser(const QString& user, const QString& password, const QString& authenticator)
 262 {
 263     QSqlQuery query(logDb());
 264     query.prepare(queryString("insert_quasseluser"));
 265     query.bindValue(":username", user);
 266     query.bindValue(":password", hashPassword(password));
 267     query.bindValue(":hashversion", Storage::HashVersion::Latest);
 268     query.bindValue(":authenticator", authenticator);
 269     safeExec(query);
 270     if (!watchQuery(query))
 271         return 0;
 272
 273     query.first();
 274     UserId uid = query.value(0).toInt();
 275     emit userAdded(uid, user);
 276     return uid;
 277 }
 278
 279 bool PostgreSqlStorage::updateUser(UserId user, const QString& password)
 280 {
 281     QSqlQuery query(logDb());
 282     query.prepare(queryString("update_userpassword"));
 283     query.bindValue(":userid", user.toInt());
 284     query.bindValue(":password", hashPassword(password));
 285     query.bindValue(":hashversion", Storage::HashVersion::Latest);
 286     safeExec(query);
 287     watchQuery(query);
 288     return query.numRowsAffected() != 0;
 289 }
 290
 291 void PostgreSqlStorage::renameUser(UserId user, const QString& newName)
 292 {
 293     QSqlQuery query(logDb());
 294     query.prepare(queryString("update_username"));
 295     query.bindValue(":userid", user.toInt());
 296     query.bindValue(":username", newName);
 297     safeExec(query);
 298     watchQuery(query);
 299     emit userRenamed(user, newName);
 300 }
 301
 302 UserId PostgreSqlStorage::validateUser(const QString& user, const QString& password)
 303 {
 304     QSqlQuery query(logDb());
 305     query.prepare(queryString("select_authuser"));
 306     query.bindValue(":username", user);
 307     safeExec(query);
 308     watchQuery(query);
 309
 310     if (query.first()
 311         && checkHashedPassword(query.value(0).toInt(),
 312                                password,
 313                                query.value(1).toString(),
 314                                static_cast<Storage::HashVersion>(query.value(2).toInt()))) {
 315         return query.value(0).toInt();
 316     }
 317     else {
 318         return 0;
 319     }
 320 }
 321
 322 UserId PostgreSqlStorage::getUserId(const QString& user)
 323 {
 324     QSqlQuery query(logDb());
 325     query.prepare(queryString("select_userid"));
 326     query.bindValue(":username", user);
 327     safeExec(query);
 328     watchQuery(query);
 329
 330     if (query.first()) {
 331         return query.value(0).toInt();
 332     }
 333     else {
 334         return 0;
 335     }
 336 }
 337
 338 QString PostgreSqlStorage::getUserAuthenticator(const UserId userid)
 339 {
 340     QSqlQuery query(logDb());
 341     query.prepare(queryString("select_authenticator"));
 342     query.bindValue(":userid", userid.toInt());
 343     safeExec(query);
 344     watchQuery(query);
 345
 346     if (query.first()) {
 347         return query.value(0).toString();
 348     }
 349     else {
 350         return QString("");
 351     }
 352 }
 353
 354 UserId PostgreSqlStorage::internalUser()
 355 {
 356     QSqlQuery query(logDb());
 357     query.prepare(queryString("select_internaluser"));
 358     safeExec(query);
 359     watchQuery(query);
 360
 361     if (query.first()) {
 362         return query.value(0).toInt();
 363     }
 364     else {
 365         return 0;
 366     }
 367 }
 368
 369 void PostgreSqlStorage::delUser(UserId user)
 370 {
 371     QSqlDatabase db = logDb();
 372     if (!beginTransaction(db)) {
 373         qWarning() << "PostgreSqlStorage::delUser(): cannot start transaction!";
 374         return;
 375     }
 376
 377     QSqlQuery query(db);
 378     query.prepare(queryString("delete_quasseluser"));
 379     query.bindValue(":userid", user.toInt());
 380     safeExec(query);
 381     if (!watchQuery(query)) {
 382         db.rollback();
 383         return;
 384     }
 385     else {
 386         db.commit();
 387         emit userRemoved(user);
 388     }
 389 }
 390
 391 void PostgreSqlStorage::setUserSetting(UserId userId, const QString& settingName, const QVariant& data)
 392 {
 393     QByteArray rawData;
 394     QDataStream out(&rawData, QIODevice::WriteOnly);
 395     out.setVersion(QDataStream::Qt_4_2);
 396     out << data;
 397
 398     QSqlDatabase db = logDb();
 399     QSqlQuery selectQuery(db);
 400     selectQuery.prepare(queryString("select_user_setting"));
 401     selectQuery.bindValue(":userid", userId.toInt());
 402     selectQuery.bindValue(":settingname", settingName);
 403     safeExec(selectQuery);
 404     watchQuery(selectQuery);
 405
 406     QString setQueryString;
 407     if (!selectQuery.first()) {
 408         setQueryString = queryString("insert_user_setting");
 409     }
 410     else {
 411         setQueryString = queryString("update_user_setting");
 412     }
 413
 414     QSqlQuery setQuery(db);
 415     setQuery.prepare(setQueryString);
 416     setQuery.bindValue(":userid", userId.toInt());
 417     setQuery.bindValue(":settingname", settingName);
 418     setQuery.bindValue(":settingvalue", rawData);
 419     safeExec(setQuery);
 420     watchQuery(setQuery);
 421 }
 422
 423 QVariant PostgreSqlStorage::getUserSetting(UserId userId, const QString& settingName, const QVariant& defaultData)
 424 {
 425     QSqlQuery query(logDb());
 426     query.prepare(queryString("select_user_setting"));
 427     query.bindValue(":userid", userId.toInt());
 428     query.bindValue(":settingname", settingName);
 429     safeExec(query);
 430     watchQuery(query);
 431
 432     if (query.first()) {
 433         QVariant data;
 434         QByteArray rawData = query.value(0).toByteArray();
 435         QDataStream in(&rawData, QIODevice::ReadOnly);
 436         in.setVersion(QDataStream::Qt_4_2);
 437         in >> data;
 438         return data;
 439     }
 440     else {
 441         return defaultData;
 442     }
 443 }
 444
 445 void PostgreSqlStorage::setCoreState(const QVariantList& data)
 446 {
 447     QByteArray rawData;
 448     QDataStream out(&rawData, QIODevice::WriteOnly);
 449     out.setVersion(QDataStream::Qt_4_2);
 450     out << data;
 451
 452     QSqlDatabase db = logDb();
 453     QSqlQuery selectQuery(db);
 454     selectQuery.prepare(queryString("select_core_state"));
 455     selectQuery.bindValue(":key", "active_sessions");
 456     safeExec(selectQuery);
 457     watchQuery(selectQuery);
 458
 459     QString setQueryString;
 460     if (!selectQuery.first()) {
 461         setQueryString = queryString("insert_core_state");
 462     }
 463     else {
 464         setQueryString = queryString("update_core_state");
 465     }
 466
 467     QSqlQuery setQuery(db);
 468     setQuery.prepare(setQueryString);
 469     setQuery.bindValue(":key", "active_sessions");
 470     setQuery.bindValue(":value", rawData);
 471     safeExec(setQuery);
 472     watchQuery(setQuery);
 473 }
 474
 475 QVariantList PostgreSqlStorage::getCoreState(const QVariantList& defaultData)
 476 {
 477     QSqlQuery query(logDb());
 478     query.prepare(queryString("select_core_state"));
 479     query.bindValue(":key", "active_sessions");
 480     safeExec(query);
 481     watchQuery(query);
 482
 483     if (query.first()) {
 484         QVariantList data;
 485         QByteArray rawData = query.value(0).toByteArray();
 486         QDataStream in(&rawData, QIODevice::ReadOnly);
 487         in.setVersion(QDataStream::Qt_4_2);
 488         in >> data;
 489         return data;
 490     }
 491     else {
 492         return defaultData;
 493     }
 494 }
 495
 496 IdentityId PostgreSqlStorage::createIdentity(UserId user, CoreIdentity& identity)
 497 {
 498     IdentityId identityId;
 499
 500     QSqlDatabase db = logDb();
 501     if (!beginTransaction(db)) {
 502         qWarning() << "PostgreSqlStorage::createIdentity(): Unable to start Transaction!";
 503         qWarning() << " -" << qPrintable(db.lastError().text());
 504         return identityId;
 505     }
 506
 507     QSqlQuery query(db);
 508     query.prepare(queryString("insert_identity"));
 509     query.bindValue(":userid", user.toInt());
 510     query.bindValue(":identityname", identity.identityName());
 511     query.bindValue(":realname", identity.realName());
 512     query.bindValue(":awaynick", identity.awayNick());
 513     query.bindValue(":awaynickenabled", identity.awayNickEnabled());
 514     query.bindValue(":awayreason", identity.awayReason());
 515     query.bindValue(":awayreasonenabled", identity.awayReasonEnabled());
 516     query.bindValue(":autoawayenabled", identity.awayReasonEnabled());
 517     query.bindValue(":autoawaytime", identity.autoAwayTime());
 518     query.bindValue(":autoawayreason", identity.autoAwayReason());
 519     query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled());
 520     query.bindValue(":detachawayenabled", identity.detachAwayEnabled());
 521     query.bindValue(":detachawayreason", identity.detachAwayReason());
 522     query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled());
 523     query.bindValue(":ident", identity.ident());
 524     query.bindValue(":kickreason", identity.kickReason());
 525     query.bindValue(":partreason", identity.partReason());
 526     query.bindValue(":quitreason", identity.quitReason());
 527 #ifdef HAVE_SSL
 528     query.bindValue(":sslcert", identity.sslCert().toPem());
 529     query.bindValue(":sslkey", identity.sslKey().toPem());
 530 #else
 531     query.bindValue(":sslcert", QByteArray());
 532     query.bindValue(":sslkey", QByteArray());
 533 #endif
 534     safeExec(query);
 535     if (!watchQuery(query)) {
 536         db.rollback();
 537         return {};
 538     }
 539
 540     query.first();
 541     identityId = query.value(0).toInt();
 542     identity.setId(identityId);
 543
 544     if (!identityId.isValid()) {
 545         db.rollback();
 546         return {};
 547     }
 548
 549     QSqlQuery insertNickQuery(db);
 550     insertNickQuery.prepare(queryString("insert_nick"));
 551     foreach (QString nick, identity.nicks()) {
 552         insertNickQuery.bindValue(":identityid", identityId.toInt());
 553         insertNickQuery.bindValue(":nick", nick);
 554         safeExec(insertNickQuery);
 555         if (!watchQuery(insertNickQuery)) {
 556             db.rollback();
 557             return {};
 558         }
 559     }
 560
 561     if (!db.commit()) {
 562         qWarning() << "PostgreSqlStorage::createIdentity(): committing data failed!";
 563         qWarning() << " -" << qPrintable(db.lastError().text());
 564         return {};
 565     }
 566     return identityId;
 567 }
 568
 569 bool PostgreSqlStorage::updateIdentity(UserId user, const CoreIdentity& identity)
 570 {
 571     QSqlDatabase db = logDb();
 572     if (!beginTransaction(db)) {
 573         qWarning() << "PostgreSqlStorage::updateIdentity(): Unable to start Transaction!";
 574         qWarning() << " -" << qPrintable(db.lastError().text());
 575         return false;
 576     }
 577
 578     QSqlQuery checkQuery(db);
 579     checkQuery.prepare(queryString("select_checkidentity"));
 580     checkQuery.bindValue(":identityid", identity.id().toInt());
 581     checkQuery.bindValue(":userid", user.toInt());
 582     safeExec(checkQuery);
 583     watchQuery(checkQuery);
 584
 585     // there should be exactly one identity for the given id and user
 586     if (!checkQuery.first() || checkQuery.value(0).toInt() != 1) {
 587         db.rollback();
 588         return false;
 589     }
 590
 591     QSqlQuery query(db);
 592     query.prepare(queryString("update_identity"));
 593     query.bindValue(":identityname", identity.identityName());
 594     query.bindValue(":realname", identity.realName());
 595     query.bindValue(":awaynick", identity.awayNick());
 596     query.bindValue(":awaynickenabled", identity.awayNickEnabled());
 597     query.bindValue(":awayreason", identity.awayReason());
 598     query.bindValue(":awayreasonenabled", identity.awayReasonEnabled());
 599     query.bindValue(":autoawayenabled", identity.awayReasonEnabled());
 600     query.bindValue(":autoawaytime", identity.autoAwayTime());
 601     query.bindValue(":autoawayreason", identity.autoAwayReason());
 602     query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled());
 603     query.bindValue(":detachawayenabled", identity.detachAwayEnabled());
 604     query.bindValue(":detachawayreason", identity.detachAwayReason());
 605     query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled());
 606     query.bindValue(":ident", identity.ident());
 607     query.bindValue(":kickreason", identity.kickReason());
 608     query.bindValue(":partreason", identity.partReason());
 609     query.bindValue(":quitreason", identity.quitReason());
 610 #ifdef HAVE_SSL
 611     query.bindValue(":sslcert", identity.sslCert().toPem());
 612     query.bindValue(":sslkey", identity.sslKey().toPem());
 613 #else
 614     query.bindValue(":sslcert", QByteArray());
 615     query.bindValue(":sslkey", QByteArray());
 616 #endif
 617     query.bindValue(":identityid", identity.id().toInt());
 618
 619     safeExec(query);
 620     if (!watchQuery(query)) {
 621         db.rollback();
 622         return false;
 623     }
 624
 625     QSqlQuery deleteNickQuery(db);
 626     deleteNickQuery.prepare(queryString("delete_nicks"));
 627     deleteNickQuery.bindValue(":identityid", identity.id().toInt());
 628     safeExec(deleteNickQuery);
 629     if (!watchQuery(deleteNickQuery)) {
 630         db.rollback();
 631         return false;
 632     }
 633
 634     QSqlQuery insertNickQuery(db);
 635     insertNickQuery.prepare(queryString("insert_nick"));
 636     foreach (QString nick, identity.nicks()) {
 637         insertNickQuery.bindValue(":identityid", identity.id().toInt());
 638         insertNickQuery.bindValue(":nick", nick);
 639         safeExec(insertNickQuery);
 640         if (!watchQuery(insertNickQuery)) {
 641             db.rollback();
 642             return false;
 643         }
 644     }
 645
 646     if (!db.commit()) {
 647         qWarning() << "PostgreSqlStorage::updateIdentity(): committing data failed!";
 648         qWarning() << " -" << qPrintable(db.lastError().text());
 649         return false;
 650     }
 651     return true;
 652 }
 653
 654 void PostgreSqlStorage::removeIdentity(UserId user, IdentityId identityId)
 655 {
 656     QSqlDatabase db = logDb();
 657     if (!beginTransaction(db)) {
 658         qWarning() << "PostgreSqlStorage::removeIdentity(): Unable to start Transaction!";
 659         qWarning() << " -" << qPrintable(db.lastError().text());
 660         return;
 661     }
 662
 663     QSqlQuery query(db);
 664     query.prepare(queryString("delete_identity"));
 665     query.bindValue(":identityid", identityId.toInt());
 666     query.bindValue(":userid", user.toInt());
 667     safeExec(query);
 668     if (!watchQuery(query)) {
 669         db.rollback();
 670     }
 671     else {
 672         db.commit();
 673     }
 674 }
 675
 676 QList<CoreIdentity> PostgreSqlStorage::identities(UserId user)
 677 {
 678     QList<CoreIdentity> identities;
 679
 680     QSqlDatabase db = logDb();
 681     if (!beginReadOnlyTransaction(db)) {
 682         qWarning() << "PostgreSqlStorage::identites(): cannot start read only transaction!";
 683         qWarning() << " -" << qPrintable(db.lastError().text());
 684         return identities;
 685     }
 686
 687     QSqlQuery query(db);
 688     query.prepare(queryString("select_identities"));
 689     query.bindValue(":userid", user.toInt());
 690
 691     QSqlQuery nickQuery(db);
 692     nickQuery.prepare(queryString("select_nicks"));
 693
 694     safeExec(query);
 695     watchQuery(query);
 696
 697     while (query.next()) {
 698         CoreIdentity identity(IdentityId(query.value(0).toInt()));
 699
 700         identity.setIdentityName(query.value(1).toString());
 701         identity.setRealName(query.value(2).toString());
 702         identity.setAwayNick(query.value(3).toString());
 703         identity.setAwayNickEnabled(!!query.value(4).toInt());
 704         identity.setAwayReason(query.value(5).toString());
 705         identity.setAwayReasonEnabled(!!query.value(6).toInt());
 706         identity.setAutoAwayEnabled(!!query.value(7).toInt());
 707         identity.setAutoAwayTime(query.value(8).toInt());
 708         identity.setAutoAwayReason(query.value(9).toString());
 709         identity.setAutoAwayReasonEnabled(!!query.value(10).toInt());
 710         identity.setDetachAwayEnabled(!!query.value(11).toInt());
 711         identity.setDetachAwayReason(query.value(12).toString());
 712         identity.setDetachAwayReasonEnabled(!!query.value(13).toInt());
 713         identity.setIdent(query.value(14).toString());
 714         identity.setKickReason(query.value(15).toString());
 715         identity.setPartReason(query.value(16).toString());
 716         identity.setQuitReason(query.value(17).toString());
 717 #ifdef HAVE_SSL
 718         identity.setSslCert(query.value(18).toByteArray());
 719         identity.setSslKey(query.value(19).toByteArray());
 720 #endif
 721
 722         nickQuery.bindValue(":identityid", identity.id().toInt());
 723         QList<QString> nicks;
 724         safeExec(nickQuery);
 725         watchQuery(nickQuery);
 726         while (nickQuery.next()) {
 727             nicks << nickQuery.value(0).toString();
 728         }
 729         identity.setNicks(nicks);
 730         identities << identity;
 731     }
 732     db.commit();
 733     return identities;
 734 }
 735
 736 NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo& info)
 737 {
 738     NetworkId networkId;
 739
 740     QSqlDatabase db = logDb();
 741     if (!beginTransaction(db)) {
 742         qWarning() << "PostgreSqlStorage::createNetwork(): failed to begin transaction!";
 743         qWarning() << " -" << qPrintable(db.lastError().text());
 744         return false;
 745     }
 746
 747     QSqlQuery query(db);
 748     query.prepare(queryString("insert_network"));
 749     query.bindValue(":userid", user.toInt());
 750     bindNetworkInfo(query, info);
 751     safeExec(query);
 752     if (!watchQuery(query)) {
 753         db.rollback();
 754         return {};
 755     }
 756
 757     query.first();
 758     networkId = query.value(0).toInt();
 759
 760     if (!networkId.isValid()) {
 761         db.rollback();
 762         return {};
 763     }
 764
 765     QSqlQuery insertServersQuery(db);
 766     insertServersQuery.prepare(queryString("insert_server"));
 767     foreach (Network::Server server, info.serverList) {
 768         insertServersQuery.bindValue(":userid", user.toInt());
 769         insertServersQuery.bindValue(":networkid", networkId.toInt());
 770         bindServerInfo(insertServersQuery, server);
 771         safeExec(insertServersQuery);
 772         if (!watchQuery(insertServersQuery)) {
 773             db.rollback();
 774             return {};
 775         }
 776     }
 777
 778     if (!db.commit()) {
 779         qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!";
 780         qWarning() << " -" << qPrintable(db.lastError().text());
 781         return {};
 782     }
 783     return networkId;
 784 }
 785
 786 void PostgreSqlStorage::bindNetworkInfo(QSqlQuery& query, const NetworkInfo& info)
 787 {
 788     query.bindValue(":networkname", info.networkName);
 789     query.bindValue(":identityid", info.identity.isValid() ? info.identity.toInt() : QVariant());
 790     query.bindValue(":encodingcodec", QString(info.codecForEncoding));
 791     query.bindValue(":decodingcodec", QString(info.codecForDecoding));
 792     query.bindValue(":servercodec", QString(info.codecForServer));
 793     query.bindValue(":userandomserver", info.useRandomServer);
 794     query.bindValue(":perform", info.perform.join("\n"));
 795     query.bindValue(":useautoidentify", info.useAutoIdentify);
 796     query.bindValue(":autoidentifyservice", info.autoIdentifyService);
 797     query.bindValue(":autoidentifypassword", info.autoIdentifyPassword);
 798     query.bindValue(":usesasl", info.useSasl);
 799     query.bindValue(":saslaccount", info.saslAccount);
 800     query.bindValue(":saslpassword", info.saslPassword);
 801     query.bindValue(":useautoreconnect", info.useAutoReconnect);
 802     query.bindValue(":autoreconnectinterval", info.autoReconnectInterval);
 803     query.bindValue(":autoreconnectretries", info.autoReconnectRetries);
 804     query.bindValue(":unlimitedconnectretries", info.unlimitedReconnectRetries);
 805     query.bindValue(":rejoinchannels", info.rejoinChannels);
 806     // Custom rate limiting
 807     query.bindValue(":usecustomessagerate", info.useCustomMessageRate);
 808     query.bindValue(":messagerateburstsize", info.messageRateBurstSize);
 809     query.bindValue(":messageratedelay", info.messageRateDelay);
 810     query.bindValue(":unlimitedmessagerate", info.unlimitedMessageRate);
 811     if (info.networkId.isValid())
 812         query.bindValue(":networkid", info.networkId.toInt());
 813 }
 814
 815 void PostgreSqlStorage::bindServerInfo(QSqlQuery& query, const Network::Server& server)
 816 {
 817     query.bindValue(":hostname", server.host);
 818     query.bindValue(":port", server.port);
 819     query.bindValue(":password", server.password);
 820     query.bindValue(":ssl", server.useSsl);
 821     query.bindValue(":sslversion", server.sslVersion);
 822     query.bindValue(":useproxy", server.useProxy);
 823     query.bindValue(":proxytype", server.proxyType);
 824     query.bindValue(":proxyhost", server.proxyHost);
 825     query.bindValue(":proxyport", server.proxyPort);
 826     query.bindValue(":proxyuser", server.proxyUser);
 827     query.bindValue(":proxypass", server.proxyPass);
 828     query.bindValue(":sslverify", server.sslVerify);
 829 }
 830
 831 bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo& info)
 832 {
 833     QSqlDatabase db = logDb();
 834     if (!beginTransaction(db)) {
 835         qWarning() << "PostgreSqlStorage::updateNetwork(): failed to begin transaction!";
 836         qWarning() << " -" << qPrintable(db.lastError().text());
 837         return false;
 838     }
 839
 840     QSqlQuery updateQuery(db);
 841     updateQuery.prepare(queryString("update_network"));
 842     updateQuery.bindValue(":userid", user.toInt());
 843     bindNetworkInfo(updateQuery, info);
 844     safeExec(updateQuery);
 845     if (!watchQuery(updateQuery)) {
 846         db.rollback();
 847         return false;
 848     }
 849     if (updateQuery.numRowsAffected() != 1) {
 850         // seems this is not our network...
 851         db.rollback();
 852         return false;
 853     }
 854
 855     QSqlQuery dropServersQuery(db);
 856     dropServersQuery.prepare("DELETE FROM ircserver WHERE networkid = :networkid");
 857     dropServersQuery.bindValue(":networkid", info.networkId.toInt());
 858     safeExec(dropServersQuery);
 859     if (!watchQuery(dropServersQuery)) {
 860         db.rollback();
 861         return false;
 862     }
 863
 864     QSqlQuery insertServersQuery(db);
 865     insertServersQuery.prepare(queryString("insert_server"));
 866     foreach (Network::Server server, info.serverList) {
 867         insertServersQuery.bindValue(":userid", user.toInt());
 868         insertServersQuery.bindValue(":networkid", info.networkId.toInt());
 869         bindServerInfo(insertServersQuery, server);
 870         safeExec(insertServersQuery);
 871         if (!watchQuery(insertServersQuery)) {
 872             db.rollback();
 873             return false;
 874         }
 875     }
 876
 877     if (!db.commit()) {
 878         qWarning() << "PostgreSqlStorage::updateNetwork(): committing data failed!";
 879         qWarning() << " -" << qPrintable(db.lastError().text());
 880         return false;
 881     }
 882     return true;
 883 }
 884
 885 bool PostgreSqlStorage::removeNetwork(UserId user, const NetworkId& networkId)
 886 {
 887     QSqlDatabase db = logDb();
 888     if (!beginTransaction(db)) {
 889         qWarning() << "PostgreSqlStorage::removeNetwork(): cannot start transaction!";
 890         qWarning() << " -" << qPrintable(db.lastError().text());
 891         return false;
 892     }
 893
 894     QSqlQuery query(db);
 895     query.prepare(queryString("delete_network"));
 896     query.bindValue(":userid", user.toInt());
 897     query.bindValue(":networkid", networkId.toInt());
 898     safeExec(query);
 899     if (!watchQuery(query)) {
 900         db.rollback();
 901         return false;
 902     }
 903
 904     db.commit();
 905     return true;
 906 }
 907
 908 QList<NetworkInfo> PostgreSqlStorage::networks(UserId user)
 909 {
 910     QList<NetworkInfo> nets;
 911
 912     QSqlDatabase db = logDb();
 913     if (!beginReadOnlyTransaction(db)) {
 914         qWarning() << "PostgreSqlStorage::networks(): cannot start read only transaction!";
 915         qWarning() << " -" << qPrintable(db.lastError().text());
 916         return nets;
 917     }
 918
 919     QSqlQuery networksQuery(db);
 920     networksQuery.prepare(queryString("select_networks_for_user"));
 921     networksQuery.bindValue(":userid", user.toInt());
 922
 923     QSqlQuery serversQuery(db);
 924     serversQuery.prepare(queryString("select_servers_for_network"));
 925
 926     safeExec(networksQuery);
 927     if (!watchQuery(networksQuery)) {
 928         db.rollback();
 929         return nets;
 930     }
 931
 932     while (networksQuery.next()) {
 933         NetworkInfo net;
 934         net.networkId = networksQuery.value(0).toInt();
 935         net.networkName = networksQuery.value(1).toString();
 936         net.identity = networksQuery.value(2).toInt();
 937         net.codecForServer = networksQuery.value(3).toString().toLatin1();
 938         net.codecForEncoding = networksQuery.value(4).toString().toLatin1();
 939         net.codecForDecoding = networksQuery.value(5).toString().toLatin1();
 940         net.useRandomServer = networksQuery.value(6).toBool();
 941         net.perform = networksQuery.value(7).toString().split("\n");
 942         net.useAutoIdentify = networksQuery.value(8).toBool();
 943         net.autoIdentifyService = networksQuery.value(9).toString();
 944         net.autoIdentifyPassword = networksQuery.value(10).toString();
 945         net.useAutoReconnect = networksQuery.value(11).toBool();
 946         net.autoReconnectInterval = networksQuery.value(12).toUInt();
 947         net.autoReconnectRetries = networksQuery.value(13).toInt();
 948         net.unlimitedReconnectRetries = networksQuery.value(14).toBool();
 949         net.rejoinChannels = networksQuery.value(15).toBool();
 950         net.useSasl = networksQuery.value(16).toBool();
 951         net.saslAccount = networksQuery.value(17).toString();
 952         net.saslPassword = networksQuery.value(18).toString();
 953         // Custom rate limiting
 954         net.useCustomMessageRate = networksQuery.value(19).toBool();
 955         net.messageRateBurstSize = networksQuery.value(20).toUInt();
 956         net.messageRateDelay = networksQuery.value(21).toUInt();
 957         net.unlimitedMessageRate = networksQuery.value(22).toBool();
 958
 959         serversQuery.bindValue(":networkid", net.networkId.toInt());
 960         safeExec(serversQuery);
 961         if (!watchQuery(serversQuery)) {
 962             db.rollback();
 963             return nets;
 964         }
 965
 966         Network::ServerList servers;
 967         while (serversQuery.next()) {
 968             Network::Server server;
 969             server.host = serversQuery.value(0).toString();
 970             server.port = serversQuery.value(1).toUInt();
 971             server.password = serversQuery.value(2).toString();
 972             server.useSsl = serversQuery.value(3).toBool();
 973             server.sslVersion = serversQuery.value(4).toInt();
 974             server.useProxy = serversQuery.value(5).toBool();
 975             server.proxyType = serversQuery.value(6).toInt();
 976             server.proxyHost = serversQuery.value(7).toString();
 977             server.proxyPort = serversQuery.value(8).toUInt();
 978             server.proxyUser = serversQuery.value(9).toString();
 979             server.proxyPass = serversQuery.value(10).toString();
 980             server.sslVerify = serversQuery.value(11).toBool();
 981             servers << server;
 982         }
 983         net.serverList = servers;
 984         nets << net;
 985     }
 986     db.commit();
 987     return nets;
 988 }
 989
 990 QList<NetworkId> PostgreSqlStorage::connectedNetworks(UserId user)
 991 {
 992     QList<NetworkId> connectedNets;
 993
 994     QSqlDatabase db = logDb();
 995     if (!beginReadOnlyTransaction(db)) {
 996         qWarning() << "PostgreSqlStorage::connectedNetworks(): cannot start read only transaction!";
 997         qWarning() << " -" << qPrintable(db.lastError().text());
 998         return connectedNets;
 999     }
1000
1001     QSqlQuery query(db);
1002     query.prepare(queryString("select_connected_networks"));
1003     query.bindValue(":userid", user.toInt());
1004     safeExec(query);
1005     watchQuery(query);
1006
1007     while (query.next()) {
1008         connectedNets << query.value(0).toInt();
1009     }
1010
1011     db.commit();
1012     return connectedNets;
1013 }
1014
1015 void PostgreSqlStorage::setNetworkConnected(UserId user, const NetworkId& networkId, bool isConnected)
1016 {
1017     QSqlQuery query(logDb());
1018     query.prepare(queryString("update_network_connected"));
1019     query.bindValue(":userid", user.toInt());
1020     query.bindValue(":networkid", networkId.toInt());
1021     query.bindValue(":connected", isConnected);
1022     safeExec(query);
1023     watchQuery(query);
1024 }
1025
1026 QHash<QString, QString> PostgreSqlStorage::persistentChannels(UserId user, const NetworkId& networkId)
1027 {
1028     QHash<QString, QString> persistentChans;
1029
1030     QSqlDatabase db = logDb();
1031     if (!beginReadOnlyTransaction(db)) {
1032         qWarning() << "PostgreSqlStorage::persistentChannels(): cannot start read only transaction!";
1033         qWarning() << " -" << qPrintable(db.lastError().text());
1034         return persistentChans;
1035     }
1036
1037     QSqlQuery query(db);
1038     query.prepare(queryString("select_persistent_channels"));
1039     query.bindValue(":userid", user.toInt());
1040     query.bindValue(":networkid", networkId.toInt());
1041     safeExec(query);
1042     watchQuery(query);
1043
1044     while (query.next()) {
1045         persistentChans[query.value(0).toString()] = query.value(1).toString();
1046     }
1047
1048     db.commit();
1049     return persistentChans;
1050 }
1051
1052 void PostgreSqlStorage::setChannelPersistent(UserId user, const NetworkId& networkId, const QString& channel, bool isJoined)
1053 {
1054     QSqlQuery query(logDb());
1055     query.prepare(queryString("update_buffer_persistent_channel"));
1056     query.bindValue(":userid", user.toInt());
1057     query.bindValue(":networkid", networkId.toInt());
1058     query.bindValue(":buffercname", channel.toLower());
1059     query.bindValue(":joined", isJoined);
1060     safeExec(query);
1061     watchQuery(query);
1062 }
1063
1064 void PostgreSqlStorage::setPersistentChannelKey(UserId user, const NetworkId& networkId, const QString& channel, const QString& key)
1065 {
1066     QSqlQuery query(logDb());
1067     query.prepare(queryString("update_buffer_set_channel_key"));
1068     query.bindValue(":userid", user.toInt());
1069     query.bindValue(":networkid", networkId.toInt());
1070     query.bindValue(":buffercname", channel.toLower());
1071     query.bindValue(":key", key);
1072     safeExec(query);
1073     watchQuery(query);
1074 }
1075
1076 QString PostgreSqlStorage::awayMessage(UserId user, NetworkId networkId)
1077 {
1078     QSqlQuery query(logDb());
1079     query.prepare(queryString("select_network_awaymsg"));
1080     query.bindValue(":userid", user.toInt());
1081     query.bindValue(":networkid", networkId.toInt());
1082     safeExec(query);
1083     watchQuery(query);
1084     QString awayMsg;
1085     if (query.first())
1086         awayMsg = query.value(0).toString();
1087     return awayMsg;
1088 }
1089
1090 void PostgreSqlStorage::setAwayMessage(UserId user, NetworkId networkId, const QString& awayMsg)
1091 {
1092     QSqlQuery query(logDb());
1093     query.prepare(queryString("update_network_set_awaymsg"));
1094     query.bindValue(":userid", user.toInt());
1095     query.bindValue(":networkid", networkId.toInt());
1096     query.bindValue(":awaymsg", awayMsg);
1097     safeExec(query);
1098     watchQuery(query);
1099 }
1100
1101 QString PostgreSqlStorage::userModes(UserId user, NetworkId networkId)
1102 {
1103     QSqlQuery query(logDb());
1104     query.prepare(queryString("select_network_usermode"));
1105     query.bindValue(":userid", user.toInt());
1106     query.bindValue(":networkid", networkId.toInt());
1107     safeExec(query);
1108     watchQuery(query);
1109     QString modes;
1110     if (query.first())
1111         modes = query.value(0).toString();
1112     return modes;
1113 }
1114
1115 void PostgreSqlStorage::setUserModes(UserId user, NetworkId networkId, const QString& userModes)
1116 {
1117     QSqlQuery query(logDb());
1118     query.prepare(queryString("update_network_set_usermode"));
1119     query.bindValue(":userid", user.toInt());
1120     query.bindValue(":networkid", networkId.toInt());
1121     query.bindValue(":usermode", userModes);
1122     safeExec(query);
1123     watchQuery(query);
1124 }
1125
1126 BufferInfo PostgreSqlStorage::bufferInfo(UserId user, const NetworkId& networkId, BufferInfo::Type type, const QString& buffer, bool create)
1127 {
1128     QSqlDatabase db = logDb();
1129     if (!beginTransaction(db)) {
1130         qWarning() << "PostgreSqlStorage::bufferInfo(): cannot start read only transaction!";
1131         qWarning() << " -" << qPrintable(db.lastError().text());
1132         return {};
1133     }
1134
1135     QSqlQuery query(db);
1136     query.prepare(queryString("select_bufferByName"));
1137     query.bindValue(":networkid", networkId.toInt());
1138     query.bindValue(":userid", user.toInt());
1139     query.bindValue(":buffercname", buffer.toLower());
1140     safeExec(query);
1141     watchQuery(query);
1142
1143     if (query.first()) {
1144         BufferInfo bufferInfo = BufferInfo(query.value(0).toInt(), networkId, (BufferInfo::Type)query.value(1).toInt(), 0, buffer);
1145         if (query.next()) {
1146             qCritical() << "PostgreSqlStorage::bufferInfo(): received more then one Buffer!";
1147             qCritical() << "         Query:" << query.lastQuery();
1148             qCritical() << "  bound Values:";
1149             QList<QVariant> list = query.boundValues().values();
1150             for (int i = 0; i < list.size(); ++i)
1151                 qCritical() << i << ":" << list.at(i).toString().toLatin1().data();
1152             Q_ASSERT(false);
1153         }
1154         db.commit();
1155         return bufferInfo;
1156     }
1157
1158     if (!create) {
1159         db.rollback();
1160         return {};
1161     }
1162
1163     QSqlQuery createQuery(db);
1164     createQuery.prepare(queryString("insert_buffer"));
1165     createQuery.bindValue(":userid", user.toInt());
1166     createQuery.bindValue(":networkid", networkId.toInt());
1167     createQuery.bindValue(":buffertype", (int)type);
1168     createQuery.bindValue(":buffername", buffer);
1169     createQuery.bindValue(":buffercname", buffer.toLower());
1170     createQuery.bindValue(":joined", type & BufferInfo::ChannelBuffer ? true : false);
1171
1172     safeExec(createQuery);
1173
1174     if (!watchQuery(createQuery)) {
1175         qWarning() << "PostgreSqlStorage::bufferInfo(): unable to create buffer";
1176         db.rollback();
1177         return BufferInfo();
1178     }
1179
1180     createQuery.first();
1181
1182     BufferInfo bufferInfo = BufferInfo(createQuery.value(0).toInt(), networkId, type, 0, buffer);
1183     db.commit();
1184     return bufferInfo;
1185 }
1186
1187 BufferInfo PostgreSqlStorage::getBufferInfo(UserId user, const BufferId& bufferId)
1188 {
1189     QSqlQuery query(logDb());
1190     query.prepare(queryString("select_buffer_by_id"));
1191     query.bindValue(":userid", user.toInt());
1192     query.bindValue(":bufferid", bufferId.toInt());
1193     safeExec(query);
1194     if (!watchQuery(query))
1195         return {};
1196
1197     if (!query.first())
1198         return {};
1199
1200     BufferInfo bufferInfo(query.value(0).toInt(),
1201                           query.value(1).toInt(),
1202                           (BufferInfo::Type)query.value(2).toInt(),
1203                           0,
1204                           query.value(4).toString());
1205     Q_ASSERT(!query.next());
1206
1207     return bufferInfo;
1208 }
1209
1210 QList<BufferInfo> PostgreSqlStorage::requestBuffers(UserId user)
1211 {
1212     QList<BufferInfo> bufferlist;
1213
1214     QSqlDatabase db = logDb();
1215     if (!beginReadOnlyTransaction(db)) {
1216         qWarning() << "PostgreSqlStorage::requestBuffers(): cannot start read only transaction!";
1217         qWarning() << " -" << qPrintable(db.lastError().text());
1218         return bufferlist;
1219     }
1220
1221     QSqlQuery query(db);
1222     query.prepare(queryString("select_buffers"));
1223     query.bindValue(":userid", user.toInt());
1224
1225     safeExec(query);
1226     watchQuery(query);
1227     while (query.next()) {
1228         bufferlist << BufferInfo(query.value(0).toInt(),
1229                                  query.value(1).toInt(),
1230                                  (BufferInfo::Type)query.value(2).toInt(),
1231                                  query.value(3).toInt(),
1232                                  query.value(4).toString());
1233     }
1234     db.commit();
1235     return bufferlist;
1236 }
1237
1238 QList<BufferId> PostgreSqlStorage::requestBufferIdsForNetwork(UserId user, NetworkId networkId)
1239 {
1240     QList<BufferId> bufferList;
1241
1242     QSqlDatabase db = logDb();
1243     if (!beginReadOnlyTransaction(db)) {
1244         qWarning() << "PostgreSqlStorage::requestBufferIdsForNetwork(): cannot start read only transaction!";
1245         qWarning() << " -" << qPrintable(db.lastError().text());
1246         return bufferList;
1247     }
1248
1249     QSqlQuery query(db);
1250     query.prepare(queryString("select_buffers_for_network"));
1251     query.bindValue(":networkid", networkId.toInt());
1252     query.bindValue(":userid", user.toInt());
1253
1254     safeExec(query);
1255     watchQuery(query);
1256     while (query.next()) {
1257         bufferList << BufferId(query.value(0).toInt());
1258     }
1259     db.commit();
1260     return bufferList;
1261 }
1262
1263 bool PostgreSqlStorage::removeBuffer(const UserId& user, const BufferId& bufferId)
1264 {
1265     QSqlDatabase db = logDb();
1266     if (!beginTransaction(db)) {
1267         qWarning() << "PostgreSqlStorage::removeBuffer(): cannot start transaction!";
1268         return false;
1269     }
1270
1271     QSqlQuery query(db);
1272     query.prepare(queryString("delete_buffer_for_bufferid"));
1273     query.bindValue(":userid", user.toInt());
1274     query.bindValue(":bufferid", bufferId.toInt());
1275     safeExec(query);
1276     if (!watchQuery(query)) {
1277         db.rollback();
1278         return false;
1279     }
1280
1281     int numRows = query.numRowsAffected();
1282     switch (numRows) {
1283     case 0:
1284         db.commit();
1285         return false;
1286     case 1:
1287         db.commit();
1288         return true;
1289     default:
1290         // there was more then one buffer deleted...
1291         qWarning() << "PostgreSqlStorage::removeBuffer(): Userid" << user << "BufferId"
1292                    << "caused deletion of" << numRows << "Buffers! Rolling back transaction...";
1293         db.rollback();
1294         return false;
1295     }
1296 }
1297
1298 bool PostgreSqlStorage::renameBuffer(const UserId& user, const BufferId& bufferId, const QString& newName)
1299 {
1300     QSqlDatabase db = logDb();
1301     if (!beginTransaction(db)) {
1302         qWarning() << "PostgreSqlStorage::renameBuffer(): cannot start transaction!";
1303         return false;
1304     }
1305
1306     QSqlQuery query(db);
1307     query.prepare(queryString("update_buffer_name"));
1308     query.bindValue(":buffername", newName);
1309     query.bindValue(":buffercname", newName.toLower());
1310     query.bindValue(":userid", user.toInt());
1311     query.bindValue(":bufferid", bufferId.toInt());
1312     safeExec(query);
1313     if (!watchQuery(query)) {
1314         db.rollback();
1315         return false;
1316     }
1317
1318     int numRows = query.numRowsAffected();
1319     switch (numRows) {
1320     case 0:
1321         db.commit();
1322         return false;
1323     case 1:
1324         db.commit();
1325         return true;
1326     default:
1327         // there was more then one buffer deleted...
1328         qWarning() << "PostgreSqlStorage::renameBuffer(): Userid" << user << "BufferId"
1329                    << "affected" << numRows << "Buffers! Rolling back transaction...";
1330         db.rollback();
1331         return false;
1332     }
1333 }
1334
1335 bool PostgreSqlStorage::mergeBuffersPermanently(const UserId& user, const BufferId& bufferId1, const BufferId& bufferId2)
1336 {
1337     QSqlDatabase db = logDb();
1338     if (!beginTransaction(db)) {
1339         qWarning() << "PostgreSqlStorage::mergeBuffersPermanently(): cannot start transaction!";
1340         qWarning() << " -" << qPrintable(db.lastError().text());
1341         return false;
1342     }
1343
1344     QSqlQuery checkQuery(db);
1345     checkQuery.prepare("SELECT count(*) FROM buffer "
1346                        "WHERE userid = :userid AND bufferid IN (:buffer1, :buffer2)");
1347     checkQuery.bindValue(":userid", user.toInt());
1348     checkQuery.bindValue(":buffer1", bufferId1.toInt());
1349     checkQuery.bindValue(":buffer2", bufferId2.toInt());
1350     safeExec(checkQuery);
1351     if (!watchQuery(checkQuery)) {
1352         db.rollback();
1353         return false;
1354     }
1355     checkQuery.first();
1356     if (checkQuery.value(0).toInt() != 2) {
1357         db.rollback();
1358         return false;
1359     }
1360
1361     QSqlQuery query(db);
1362     query.prepare(queryString("update_backlog_bufferid"));
1363     query.bindValue(":oldbufferid", bufferId2.toInt());
1364     query.bindValue(":newbufferid", bufferId1.toInt());
1365     safeExec(query);
1366     if (!watchQuery(query)) {
1367         db.rollback();
1368         return false;
1369     }
1370
1371     QSqlQuery delBufferQuery(logDb());
1372     delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
1373     delBufferQuery.bindValue(":userid", user.toInt());
1374     delBufferQuery.bindValue(":bufferid", bufferId2.toInt());
1375     safeExec(delBufferQuery);
1376     if (!watchQuery(delBufferQuery)) {
1377         db.rollback();
1378         return false;
1379     }
1380
1381     db.commit();
1382     return true;
1383 }
1384
1385 void PostgreSqlStorage::setBufferLastSeenMsg(UserId user, const BufferId& bufferId, const MsgId& msgId)
1386 {
1387     QSqlQuery query(logDb());
1388     query.prepare(queryString("update_buffer_lastseen"));
1389
1390     query.bindValue(":userid", user.toInt());
1391     query.bindValue(":bufferid", bufferId.toInt());
1392     query.bindValue(":lastseenmsgid", msgId.toQint64());
1393     safeExec(query);
1394     watchQuery(query);
1395 }
1396
1397 QHash<BufferId, MsgId> PostgreSqlStorage::bufferLastSeenMsgIds(UserId user)
1398 {
1399     QHash<BufferId, MsgId> lastSeenHash;
1400
1401     QSqlDatabase db = logDb();
1402     if (!beginReadOnlyTransaction(db)) {
1403         qWarning() << "PostgreSqlStorage::bufferLastSeenMsgIds(): cannot start read only transaction!";
1404         qWarning() << " -" << qPrintable(db.lastError().text());
1405         return lastSeenHash;
1406     }
1407
1408     QSqlQuery query(db);
1409     query.prepare(queryString("select_buffer_lastseen_messages"));
1410     query.bindValue(":userid", user.toInt());
1411     safeExec(query);
1412     if (!watchQuery(query)) {
1413         db.rollback();
1414         return lastSeenHash;
1415     }
1416
1417     while (query.next()) {
1418         lastSeenHash[query.value(0).toInt()] = query.value(1).toLongLong();
1419     }
1420
1421     db.commit();
1422     return lastSeenHash;
1423 }
1424
1425 void PostgreSqlStorage::setBufferMarkerLineMsg(UserId user, const BufferId& bufferId, const MsgId& msgId)
1426 {
1427     QSqlQuery query(logDb());
1428     query.prepare(queryString("update_buffer_markerlinemsgid"));
1429
1430     query.bindValue(":userid", user.toInt());
1431     query.bindValue(":bufferid", bufferId.toInt());
1432     query.bindValue(":markerlinemsgid", msgId.toQint64());
1433     safeExec(query);
1434     watchQuery(query);
1435 }
1436
1437 QHash<BufferId, MsgId> PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user)
1438 {
1439     QHash<BufferId, MsgId> markerLineHash;
1440
1441     QSqlDatabase db = logDb();
1442     if (!beginReadOnlyTransaction(db)) {
1443         qWarning() << "PostgreSqlStorage::bufferMarkerLineMsgIds(): cannot start read only transaction!";
1444         qWarning() << " -" << qPrintable(db.lastError().text());
1445         return markerLineHash;
1446     }
1447
1448     QSqlQuery query(db);
1449     query.prepare(queryString("select_buffer_markerlinemsgids"));
1450     query.bindValue(":userid", user.toInt());
1451     safeExec(query);
1452     if (!watchQuery(query)) {
1453         db.rollback();
1454         return markerLineHash;
1455     }
1456
1457     while (query.next()) {
1458         markerLineHash[query.value(0).toInt()] = query.value(1).toLongLong();
1459     }
1460
1461     db.commit();
1462     return markerLineHash;
1463 }
1464
1465 void PostgreSqlStorage::setBufferActivity(UserId user, BufferId bufferId, Message::Types bufferActivity)
1466 {
1467     QSqlQuery query(logDb());
1468     query.prepare(queryString("update_buffer_bufferactivity"));
1469
1470     query.bindValue(":userid", user.toInt());
1471     query.bindValue(":bufferid", bufferId.toInt());
1472     query.bindValue(":bufferactivity", (int)bufferActivity);
1473     safeExec(query);
1474     watchQuery(query);
1475 }
1476
1477 QHash<BufferId, Message::Types> PostgreSqlStorage::bufferActivities(UserId user)
1478 {
1479     QHash<BufferId, Message::Types> bufferActivityHash;
1480
1481     QSqlDatabase db = logDb();
1482     if (!beginReadOnlyTransaction(db)) {
1483         qWarning() << "PostgreSqlStorage::bufferActivities(): cannot start read only transaction!";
1484         qWarning() << " -" << qPrintable(db.lastError().text());
1485         return bufferActivityHash;
1486     }
1487
1488     QSqlQuery query(db);
1489     query.prepare(queryString("select_buffer_bufferactivities"));
1490     query.bindValue(":userid", user.toInt());
1491     safeExec(query);
1492     if (!watchQuery(query)) {
1493         db.rollback();
1494         return bufferActivityHash;
1495     }
1496
1497     while (query.next()) {
1498         bufferActivityHash[query.value(0).toInt()] = Message::Types(query.value(1).toInt());
1499     }
1500
1501     db.commit();
1502     return bufferActivityHash;
1503 }
1504
1505 Message::Types PostgreSqlStorage::bufferActivity(BufferId bufferId, MsgId lastSeenMsgId)
1506 {
1507     QSqlQuery query(logDb());
1508     query.prepare(queryString("select_buffer_bufferactivity"));
1509     query.bindValue(":bufferid", bufferId.toInt());
1510     query.bindValue(":lastseenmsgid", lastSeenMsgId.toQint64());
1511     safeExec(query);
1512     watchQuery(query);
1513     Message::Types result = Message::Types(nullptr);
1514     if (query.first())
1515         result = Message::Types(query.value(0).toInt());
1516     return result;
1517 }
1518
1519 QHash<QString, QByteArray> PostgreSqlStorage::bufferCiphers(UserId user, const NetworkId& networkId)
1520 {
1521     QHash<QString, QByteArray> bufferCiphers;
1522
1523     QSqlDatabase db = logDb();
1524     if (!beginReadOnlyTransaction(db)) {
1525         qWarning() << "PostgreSqlStorage::persistentChannels(): cannot start read only transaction!";
1526         qWarning() << " -" << qPrintable(db.lastError().text());
1527         return bufferCiphers;
1528     }
1529
1530     QSqlQuery query(db);
1531     query.prepare(queryString("select_buffer_ciphers"));
1532     query.bindValue(":userid", user.toInt());
1533     query.bindValue(":networkid", networkId.toInt());
1534     safeExec(query);
1535     watchQuery(query);
1536
1537     while (query.next()) {
1538         bufferCiphers[query.value(0).toString()] = QByteArray::fromHex(query.value(1).toString().toUtf8());
1539     }
1540
1541     db.commit();
1542     return bufferCiphers;
1543 }
1544
1545 void PostgreSqlStorage::setBufferCipher(UserId user, const NetworkId& networkId, const QString& bufferName, const QByteArray& cipher)
1546 {
1547     QSqlQuery query(logDb());
1548     query.prepare(queryString("update_buffer_cipher"));
1549     query.bindValue(":userid", user.toInt());
1550     query.bindValue(":networkid", networkId.toInt());
1551     query.bindValue(":buffercname", bufferName.toLower());
1552     query.bindValue(":cipher", QString(cipher.toHex()));
1553     safeExec(query);
1554     watchQuery(query);
1555 }
1556
1557 void PostgreSqlStorage::setHighlightCount(UserId user, BufferId bufferId, int highlightcount)
1558 {
1559     QSqlQuery query(logDb());
1560     query.prepare(queryString("update_buffer_highlightcount"));
1561
1562     query.bindValue(":userid", user.toInt());
1563     query.bindValue(":bufferid", bufferId.toInt());
1564     query.bindValue(":highlightcount", highlightcount);
1565     safeExec(query);
1566     watchQuery(query);
1567 }
1568
1569 QHash<BufferId, int> PostgreSqlStorage::highlightCounts(UserId user)
1570 {
1571     QHash<BufferId, int> highlightCountHash;
1572
1573     QSqlDatabase db = logDb();
1574     if (!beginReadOnlyTransaction(db)) {
1575         qWarning() << "PostgreSqlStorage::highlightCounts(): cannot start read only transaction!";
1576         qWarning() << " -" << qPrintable(db.lastError().text());
1577         return highlightCountHash;
1578     }
1579
1580     QSqlQuery query(db);
1581     query.prepare(queryString("select_buffer_highlightcounts"));
1582     query.bindValue(":userid", user.toInt());
1583     safeExec(query);
1584     if (!watchQuery(query)) {
1585         db.rollback();
1586         return highlightCountHash;
1587     }
1588
1589     while (query.next()) {
1590         highlightCountHash[query.value(0).toInt()] = query.value(1).toInt();
1591     }
1592
1593     db.commit();
1594     return highlightCountHash;
1595 }
1596
1597 int PostgreSqlStorage::highlightCount(BufferId bufferId, MsgId lastSeenMsgId)
1598 {
1599     QSqlQuery query(logDb());
1600     query.prepare(queryString("select_buffer_highlightcount"));
1601     query.bindValue(":bufferid", bufferId.toInt());
1602     query.bindValue(":lastseenmsgid", lastSeenMsgId.toQint64());
1603     safeExec(query);
1604     watchQuery(query);
1605     auto result = int(0);
1606     if (query.first())
1607         result = query.value(0).toInt();
1608     return result;
1609 }
1610
1611 bool PostgreSqlStorage::logMessage(Message& msg)
1612 {
1613     QSqlDatabase db = logDb();
1614     if (!beginTransaction(db)) {
1615         qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!";
1616         qWarning() << " -" << qPrintable(db.lastError().text());
1617         return false;
1618     }
1619
1620     QVariantList senderParams;
1621     senderParams << msg.sender() << msg.realName() << msg.avatarUrl();
1622     QSqlQuery getSenderIdQuery = executePreparedQuery("select_senderid", senderParams, db);
1623     qint64 senderId;
1624     if (getSenderIdQuery.first()) {
1625         senderId = getSenderIdQuery.value(0).toLongLong();
1626     }
1627     else {
1628         // it's possible that the sender was already added by another thread
1629         // since the insert might fail we're setting a savepoint
1630         savePoint("sender_sp1", db);
1631         QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", senderParams, db);
1632
1633         if (addSenderQuery.lastError().isValid()) {
1634             rollbackSavePoint("sender_sp1", db);
1635             getSenderIdQuery = executePreparedQuery("select_senderid", senderParams, db);
1636             watchQuery(getSenderIdQuery);
1637             getSenderIdQuery.first();
1638             senderId = getSenderIdQuery.value(0).toLongLong();
1639         }
1640         else {
1641             releaseSavePoint("sender_sp1", db);
1642             addSenderQuery.first();
1643             senderId = addSenderQuery.value(0).toLongLong();
1644         }
1645     }
1646
1647     QVariantList params;
1648     // PostgreSQL handles QDateTime()'s serialized format by default, and QDateTime() serializes
1649     // to a 64-bit time compatible format by default.
1650     params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() << senderId << msg.senderPrefixes()
1651            << msg.contents();
1652     QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db);
1653
1654     if (!watchQuery(logMessageQuery)) {
1655         db.rollback();
1656         return false;
1657     }
1658
1659     logMessageQuery.first();
1660     MsgId msgId = logMessageQuery.value(0).toLongLong();
1661     db.commit();
1662     if (msgId.isValid()) {
1663         msg.setMsgId(msgId);
1664         return true;
1665     }
1666     else {
1667         return false;
1668     }
1669 }
1670
1671 bool PostgreSqlStorage::logMessages(MessageList& msgs)
1672 {
1673     QSqlDatabase db = logDb();
1674     if (!beginTransaction(db)) {
1675         qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!";
1676         qWarning() << " -" << qPrintable(db.lastError().text());
1677         return false;
1678     }
1679
1680     QList<int> senderIdList;
1681     QHash<SenderData, qint64> senderIds;
1682     QSqlQuery addSenderQuery;
1683     QSqlQuery selectSenderQuery;
1684     ;
1685     for (int i = 0; i < msgs.count(); i++) {
1686         auto& msg = msgs.at(i);
1687         SenderData sender = {msg.sender(), msg.realName(), msg.avatarUrl()};
1688         if (senderIds.contains(sender)) {
1689             senderIdList << senderIds[sender];
1690             continue;
1691         }
1692
1693         QVariantList senderParams;
1694         senderParams << sender.sender << sender.realname << sender.avatarurl;
1695
1696         selectSenderQuery = executePreparedQuery("select_senderid", senderParams, db);
1697         if (selectSenderQuery.first()) {
1698             senderIdList << selectSenderQuery.value(0).toLongLong();
1699             senderIds[sender] = selectSenderQuery.value(0).toLongLong();
1700         }
1701         else {
1702             savePoint("sender_sp", db);
1703             addSenderQuery = executePreparedQuery("insert_sender", senderParams, db);
1704             if (addSenderQuery.lastError().isValid()) {
1705                 // seems it was inserted meanwhile... by a different thread
1706                 rollbackSavePoint("sender_sp", db);
1707                 selectSenderQuery = executePreparedQuery("select_senderid", senderParams, db);
1708                 watchQuery(selectSenderQuery);
1709                 selectSenderQuery.first();
1710                 senderIdList << selectSenderQuery.value(0).toLongLong();
1711                 senderIds[sender] = selectSenderQuery.value(0).toLongLong();
1712             }
1713             else {
1714                 releaseSavePoint("sender_sp", db);
1715                 addSenderQuery.first();
1716                 senderIdList << addSenderQuery.value(0).toLongLong();
1717                 senderIds[sender] = addSenderQuery.value(0).toLongLong();
1718             }
1719         }
1720     }
1721
1722     // yes we loop twice over the same list. This avoids alternating queries.
1723     bool error = false;
1724     for (int i = 0; i < msgs.count(); i++) {
1725         Message& msg = msgs[i];
1726         QVariantList params;
1727         // PostgreSQL handles QDateTime()'s serialized format by default, and QDateTime() serializes
1728         // to a 64-bit time compatible format by default.
1729         params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() << senderIdList.at(i)
1730                << msg.senderPrefixes() << msg.contents();
1731         QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db);
1732         if (!watchQuery(logMessageQuery)) {
1733             db.rollback();
1734             error = true;
1735             break;
1736         }
1737         else {
1738             logMessageQuery.first();
1739             msg.setMsgId(logMessageQuery.value(0).toLongLong());
1740         }
1741     }
1742
1743     if (error) {
1744         // we had a rollback in the db so we need to reset all msgIds
1745         for (int i = 0; i < msgs.count(); i++) {
1746             msgs[i].setMsgId(MsgId());
1747         }
1748         return false;
1749     }
1750
1751     db.commit();
1752     return true;
1753 }
1754
1755 QList<Message> PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit)
1756 {
1757     QList<Message> messagelist;
1758
1759     QSqlDatabase db = logDb();
1760     if (!beginReadOnlyTransaction(db)) {
1761         qWarning() << "PostgreSqlStorage::requestMsgs(): cannot start read only transaction!";
1762         qWarning() << " -" << qPrintable(db.lastError().text());
1763         return messagelist;
1764     }
1765
1766     BufferInfo bufferInfo = getBufferInfo(user, bufferId);
1767     if (!bufferInfo.isValid()) {
1768         db.rollback();
1769         return messagelist;
1770     }
1771
1772     QString queryName;
1773     QVariantList params;
1774     if (last == -1 && first == -1) {
1775         queryName = "select_messagesNewestK";
1776     }
1777     else if (last == -1) {
1778         queryName = "select_messagesNewerThan";
1779         params << first.toQint64();
1780     }
1781     else {
1782         queryName = "select_messagesRange";
1783         params << first.toQint64();
1784         params << last.toQint64();
1785     }
1786     params << bufferId.toInt();
1787     if (limit != -1)
1788         params << limit;
1789     else
1790         params << QVariant(QVariant::Int);
1791
1792     QSqlQuery query = executePreparedQuery(queryName, params, db);
1793
1794     if (!watchQuery(query)) {
1795         qDebug() << "select_messages failed";
1796         db.rollback();
1797         return messagelist;
1798     }
1799
1800     QDateTime timestamp;
1801     while (query.next()) {
1802         // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed
1803         // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
1804         timestamp = query.value(1).toDateTime();
1805         timestamp.setTimeSpec(Qt::UTC);
1806         Message msg(timestamp,
1807                     bufferInfo,
1808                     (Message::Type)query.value(2).toInt(),
1809                     query.value(8).toString(),
1810                     query.value(4).toString(),
1811                     query.value(5).toString(),
1812                     query.value(6).toString(),
1813                     query.value(7).toString(),
1814                     (Message::Flags)query.value(3).toInt());
1815         msg.setMsgId(query.value(0).toLongLong());
1816         messagelist << msg;
1817     }
1818
1819     db.commit();
1820     return messagelist;
1821 }
1822
1823 QList<Message> PostgreSqlStorage::requestMsgsFiltered(
1824     UserId user, BufferId bufferId, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags)
1825 {
1826     QList<Message> messagelist;
1827
1828     QSqlDatabase db = logDb();
1829     if (!beginReadOnlyTransaction(db)) {
1830         qWarning() << "PostgreSqlStorage::requestMsgs(): cannot start read only transaction!";
1831         qWarning() << " -" << qPrintable(db.lastError().text());
1832         return messagelist;
1833     }
1834
1835     BufferInfo bufferInfo = getBufferInfo(user, bufferId);
1836     if (!bufferInfo.isValid()) {
1837         db.rollback();
1838         return messagelist;
1839     }
1840
1841     QSqlQuery query(db);
1842     if (last == -1 && first == -1) {
1843         query.prepare(queryString("select_messagesNewestK_filtered"));
1844     }
1845     else if (last == -1) {
1846         query.prepare(queryString("select_messagesNewerThan_filtered"));
1847         query.bindValue(":first", first.toQint64());
1848     }
1849     else {
1850         query.prepare(queryString("select_messagesRange_filtered"));
1851         query.bindValue(":last", last.toQint64());
1852         query.bindValue(":first", first.toQint64());
1853     }
1854     query.bindValue(":buffer", bufferId.toInt());
1855     query.bindValue(":limit", limit);
1856     int typeRaw = type;
1857     query.bindValue(":type", typeRaw);
1858     int flagsRaw = flags;
1859     query.bindValue(":flags", flagsRaw);
1860
1861     safeExec(query);
1862     if (!watchQuery(query)) {
1863         qDebug() << "select_messages failed";
1864         db.rollback();
1865         return messagelist;
1866     }
1867
1868     QDateTime timestamp;
1869     while (query.next()) {
1870         // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed
1871         // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
1872         timestamp = query.value(1).toDateTime();
1873         timestamp.setTimeSpec(Qt::UTC);
1874         Message msg(timestamp,
1875                     bufferInfo,
1876                     (Message::Type)query.value(2).toInt(),
1877                     query.value(8).toString(),
1878                     query.value(4).toString(),
1879                     query.value(5).toString(),
1880                     query.value(6).toString(),
1881                     query.value(7).toString(),
1882                     Message::Flags{query.value(3).toInt()});
1883         msg.setMsgId(query.value(0).toLongLong());
1884         messagelist << msg;
1885     }
1886
1887     db.commit();
1888     return messagelist;
1889 }
1890
1891 QList<Message> PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit)
1892 {
1893     QList<Message> messagelist;
1894
1895     // requestBuffers uses it's own transaction.
1896     QHash<BufferId, BufferInfo> bufferInfoHash;
1897     foreach (BufferInfo bufferInfo, requestBuffers(user)) {
1898         bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
1899     }
1900
1901     QSqlDatabase db = logDb();
1902     if (!beginReadOnlyTransaction(db)) {
1903         qWarning() << "PostgreSqlStorage::requestAllMsgs(): cannot start read only transaction!";
1904         qWarning() << " -" << qPrintable(db.lastError().text());
1905         return messagelist;
1906     }
1907
1908     QSqlQuery query(db);
1909     if (last == -1) {
1910         query.prepare(queryString("select_messagesAllNew"));
1911     }
1912     else {
1913         query.prepare(queryString("select_messagesAll"));
1914         query.bindValue(":lastmsg", last.toQint64());
1915     }
1916     query.bindValue(":userid", user.toInt());
1917     query.bindValue(":firstmsg", first.toQint64());
1918     safeExec(query);
1919     if (!watchQuery(query)) {
1920         db.rollback();
1921         return messagelist;
1922     }
1923
1924     QDateTime timestamp;
1925     for (int i = 0; i < limit && query.next(); i++) {
1926         // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed
1927         // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
1928         timestamp = query.value(2).toDateTime();
1929         timestamp.setTimeSpec(Qt::UTC);
1930         Message msg(timestamp,
1931                     bufferInfoHash[query.value(1).toInt()],
1932                     (Message::Type)query.value(3).toInt(),
1933                     query.value(9).toString(),
1934                     query.value(5).toString(),
1935                     query.value(6).toString(),
1936                     query.value(7).toString(),
1937                     query.value(8).toString(),
1938                     (Message::Flags)query.value(4).toInt());
1939         msg.setMsgId(query.value(0).toLongLong());
1940         messagelist << msg;
1941     }
1942
1943     db.commit();
1944     return messagelist;
1945 }
1946
1947 QList<Message> PostgreSqlStorage::requestAllMsgsFiltered(
1948     UserId user, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags)
1949 {
1950     QList<Message> messagelist;
1951
1952     // requestBuffers uses it's own transaction.
1953     QHash<BufferId, BufferInfo> bufferInfoHash;
1954     foreach (BufferInfo bufferInfo, requestBuffers(user)) {
1955         bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
1956     }
1957
1958     QSqlDatabase db = logDb();
1959     if (!beginReadOnlyTransaction(db)) {
1960         qWarning() << "PostgreSqlStorage::requestAllMsgs(): cannot start read only transaction!";
1961         qWarning() << " -" << qPrintable(db.lastError().text());
1962         return messagelist;
1963     }
1964
1965     QSqlQuery query(db);
1966     if (last == -1) {
1967         query.prepare(queryString("select_messagesAllNew_filtered"));
1968     }
1969     else {
1970         query.prepare(queryString("select_messagesAll_filtered"));
1971         query.bindValue(":lastmsg", last.toQint64());
1972     }
1973     query.bindValue(":userid", user.toInt());
1974     query.bindValue(":firstmsg", first.toQint64());
1975
1976     int typeRaw = type;
1977     query.bindValue(":type", typeRaw);
1978
1979     int flagsRaw = flags;
1980     query.bindValue(":flags", flagsRaw);
1981
1982     safeExec(query);
1983     if (!watchQuery(query)) {
1984         db.rollback();
1985         return messagelist;
1986     }
1987
1988     QDateTime timestamp;
1989     for (int i = 0; i < limit && query.next(); i++) {
1990         // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed
1991         // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
1992         timestamp = query.value(2).toDateTime();
1993         timestamp.setTimeSpec(Qt::UTC);
1994         Message msg(timestamp,
1995                     bufferInfoHash[query.value(1).toInt()],
1996                     (Message::Type)query.value(3).toInt(),
1997                     query.value(9).toString(),
1998                     query.value(5).toString(),
1999                     query.value(6).toString(),
2000                     query.value(7).toString(),
2001                     query.value(8).toString(),
2002                     Message::Flags{query.value(4).toInt()});
2003         msg.setMsgId(query.value(0).toLongLong());
2004         messagelist << msg;
2005     }
2006
2007     db.commit();
2008     return messagelist;
2009 }
2010
2011 QMap<UserId, QString> PostgreSqlStorage::getAllAuthUserNames()
2012 {
2013     QMap<UserId, QString> authusernames;
2014     QSqlQuery query(logDb());
2015     query.prepare(queryString("select_all_authusernames"));
2016     safeExec(query);
2017     watchQuery(query);
2018
2019     while (query.next()) {
2020         authusernames[query.value(0).toInt()] = query.value(1).toString();
2021     }
2022     return authusernames;
2023 }
2024
2025 // void PostgreSqlStorage::safeExec(QSqlQuery &query) {
2026 //   qDebug() << "PostgreSqlStorage::safeExec";
2027 //   qDebug() << "   executing:\n" << query.executedQuery();
2028 //   qDebug() << "   bound Values:";
2029 //   QList<QVariant> list = query.boundValues().values();
2030 //   for (int i = 0; i < list.size(); ++i)
2031 //     qCritical() << i << ": " << list.at(i).toString().toLatin1().data();
2032
2033 //   query.exec();
2034
2035 //   qDebug() << "Success:" << !query.lastError().isValid();
2036 //   qDebug();
2037
2038 //   if(!query.lastError().isValid())
2039 //     return;
2040
2041 //   qDebug() << "==================== ERROR ====================";
2042 //   watchQuery(query);
2043 //   qDebug() << "===============================================";
2044 //   qDebug();
2045 //   return;
2046 // }
2047
2048 bool PostgreSqlStorage::beginTransaction(QSqlDatabase& db)
2049 {
2050     bool result = db.transaction();
2051     if (!db.isOpen()) {
2052         db = logDb();
2053         result = db.transaction();
2054     }
2055     return result;
2056 }
2057
2058 bool PostgreSqlStorage::beginReadOnlyTransaction(QSqlDatabase& db)
2059 {
2060     QSqlQuery query = db.exec("BEGIN TRANSACTION READ ONLY");
2061     if (!db.isOpen()) {
2062         db = logDb();
2063         query = db.exec("BEGIN TRANSACTION READ ONLY");
2064     }
2065     return !query.lastError().isValid();
2066 }
2067
2068 QSqlQuery PostgreSqlStorage::prepareAndExecuteQuery(const QString& queryname, const QString& paramstring, QSqlDatabase& db)
2069 {
2070     // Query preparing is done lazily. That means that instead of always checking if the query is already prepared
2071     // we just EXECUTE and catch the error
2072     QSqlQuery query;
2073
2074     db.exec("SAVEPOINT quassel_prepare_query");
2075     if (paramstring.isNull()) {
2076         query = db.exec(QString("EXECUTE quassel_%1").arg(queryname));
2077     }
2078     else {
2079         query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring));
2080     }
2081
2082     if (!db.isOpen() || db.lastError().isValid()) {
2083         // If the query failed because the DB connection was down, reopen the connection and start a new transaction.
2084         if (!db.isOpen()) {
2085             db = logDb();
2086             if (!beginTransaction(db)) {
2087                 qWarning()
2088                     << "PostgreSqlStorage::prepareAndExecuteQuery(): cannot start transaction while recovering from connection loss!";
2089                 qWarning() << " -" << qPrintable(db.lastError().text());
2090                 return query;
2091             }
2092             db.exec("SAVEPOINT quassel_prepare_query");
2093         }
2094         else {
2095             db.exec("ROLLBACK TO SAVEPOINT quassel_prepare_query");
2096         }
2097
2098         // and once again: Qt leaves us without error codes so we either parse (language dependent(!)) strings
2099         // or we just guess the error. As we're only interested in unprepared queries, this will be our guess. :)
2100         QSqlQuery checkQuery = db.exec(
2101             QString("SELECT count(name) FROM pg_prepared_statements WHERE name = 'quassel_%1' AND from_sql = TRUE").arg(queryname.toLower()));
2102         checkQuery.first();
2103         if (checkQuery.value(0).toInt() == 0) {
2104             db.exec(QString("PREPARE quassel_%1 AS %2").arg(queryname).arg(queryString(queryname)));
2105             if (db.lastError().isValid()) {
2106                 qWarning() << "PostgreSqlStorage::prepareQuery(): unable to prepare query:" << queryname << "AS" << queryString(queryname);
2107                 qWarning() << "  Error:" << db.lastError().text();
2108                 return QSqlQuery(db);
2109             }
2110         }
2111         // we always execute the query again, even if the query was already prepared.
2112         // this ensures, that the error is properly propagated to the calling function
2113         // (otherwise the last call would be the testing select to pg_prepared_statements
2114         // which always gives a proper result and the error would be lost)
2115         if (paramstring.isNull()) {
2116             query = db.exec(QString("EXECUTE quassel_%1").arg(queryname));
2117         }
2118         else {
2119             query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring));
2120         }
2121     }
2122     else {
2123         // only release the SAVEPOINT
2124         db.exec("RELEASE SAVEPOINT quassel_prepare_query");
2125     }
2126     return query;
2127 }
2128
2129 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString& queryname, const QVariantList& params, QSqlDatabase& db)
2130 {
2131     QSqlDriver* driver = db.driver();
2132
2133     QStringList paramStrings;
2134     QSqlField field;
2135     for (int i = 0; i < params.count(); i++) {
2136         const QVariant& value = params.at(i);
2137         field.setType(value.type());
2138         if (value.isNull())
2139             field.clear();
2140         else
2141             field.setValue(value);
2142
2143         paramStrings << driver->formatValue(field);
2144     }
2145
2146     if (params.isEmpty()) {
2147         return prepareAndExecuteQuery(queryname, db);
2148     }
2149     else {
2150         return prepareAndExecuteQuery(queryname, paramStrings.join(", "), db);
2151     }
2152 }
2153
2154 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString& queryname, const QVariant& param, QSqlDatabase& db)
2155 {
2156     QSqlField field;
2157     field.setType(param.type());
2158     if (param.isNull())
2159         field.clear();
2160     else
2161         field.setValue(param);
2162
2163     QString paramString = db.driver()->formatValue(field);
2164     return prepareAndExecuteQuery(queryname, paramString, db);
2165 }
2166
2167 void PostgreSqlStorage::deallocateQuery(const QString& queryname, const QSqlDatabase& db)
2168 {
2169     db.exec(QString("DEALLOCATE quassel_%1").arg(queryname));
2170 }
2171
2172 void PostgreSqlStorage::safeExec(QSqlQuery& query)
2173 {
2174     // If the query fails due to the connection being gone, it seems to cause
2175     // exec() to return false but no lastError to be set
2176     if (!query.exec() && !query.lastError().isValid()) {
2177         QSqlDatabase db = logDb();
2178         QSqlQuery retryQuery(db);
2179         retryQuery.prepare(query.lastQuery());
2180         QMapIterator<QString, QVariant> i(query.boundValues());
2181         while (i.hasNext()) {
2182             i.next();
2183             retryQuery.bindValue(i.key(), i.value());
2184         }
2185         query = retryQuery;
2186         query.exec();
2187     }
2188 }
2189
2190 // ========================================
2191 //  PostgreSqlMigrationWriter
2192 // ========================================
2193 PostgreSqlMigrationWriter::PostgreSqlMigrationWriter()
2194     : PostgreSqlStorage()
2195 {}
2196
2197 bool PostgreSqlMigrationWriter::prepareQuery(MigrationObject mo)
2198 {
2199     QString query;
2200     switch (mo) {
2201     case QuasselUser:
2202         query = queryString("migrate_write_quasseluser");
2203         break;
2204     case Sender:
2205         query = queryString("migrate_write_sender");
2206         break;
2207     case Identity:
2208         _validIdentities.clear();
2209         query = queryString("migrate_write_identity");
2210         break;
2211     case IdentityNick:
2212         query = queryString("migrate_write_identity_nick");
2213         break;
2214     case Network:
2215         query = queryString("migrate_write_network");
2216         break;
2217     case Buffer:
2218         query = queryString("migrate_write_buffer");
2219         break;
2220     case Backlog:
2221         query = queryString("migrate_write_backlog");
2222         break;
2223     case IrcServer:
2224         query = queryString("migrate_write_ircserver");
2225         break;
2226     case UserSetting:
2227         query = queryString("migrate_write_usersetting");
2228         break;
2229     case CoreState:
2230         query = queryString("migrate_write_corestate");
2231         break;
2232     }
2233     newQuery(query, logDb());
2234     return true;
2235 }
2236
2237 // bool PostgreSqlMigrationWriter::writeUser(const QuasselUserMO &user) {
2238 bool PostgreSqlMigrationWriter::writeMo(const QuasselUserMO& user)
2239 {
2240     bindValue(0, user.id.toInt());
2241     bindValue(1, user.username);
2242     bindValue(2, user.password);
2243     bindValue(3, user.hashversion);
2244     bindValue(4, user.authenticator);
2245     return exec();
2246 }
2247
2248 // bool PostgreSqlMigrationWriter::writeSender(const SenderMO &sender) {
2249 bool PostgreSqlMigrationWriter::writeMo(const SenderMO& sender)
2250 {
2251     bindValue(0, sender.senderId);
2252     bindValue(1, sender.sender);
2253     bindValue(2, sender.realname);
2254     bindValue(3, sender.avatarurl);
2255     return exec();
2256 }
2257
2258 // bool PostgreSqlMigrationWriter::writeIdentity(const IdentityMO &identity) {
2259 bool PostgreSqlMigrationWriter::writeMo(const IdentityMO& identity)
2260 {
2261     _validIdentities << identity.id.toInt();
2262     bindValue(0, identity.id.toInt());
2263     bindValue(1, identity.userid.toInt());
2264     bindValue(2, identity.identityname);
2265     bindValue(3, identity.realname);
2266     bindValue(4, identity.awayNick);
2267     bindValue(5, identity.awayNickEnabled);
2268     bindValue(6, identity.awayReason);
2269     bindValue(7, identity.awayReasonEnabled);
2270     bindValue(8, identity.autoAwayEnabled);
2271     bindValue(9, identity.autoAwayTime);
2272     bindValue(10, identity.autoAwayReason);
2273     bindValue(11, identity.autoAwayReasonEnabled);
2274     bindValue(12, identity.detachAwayEnabled);
2275     bindValue(13, identity.detachAwayReason);
2276     bindValue(14, identity.detachAwayReasonEnabled);
2277     bindValue(15, identity.ident);
2278     bindValue(16, identity.kickReason);
2279     bindValue(17, identity.partReason);
2280     bindValue(18, identity.quitReason);
2281     bindValue(19, identity.sslCert);
2282     bindValue(20, identity.sslKey);
2283     return exec();
2284 }
2285
2286 // bool PostgreSqlMigrationWriter::writeIdentityNick(const IdentityNickMO &identityNick) {
2287 bool PostgreSqlMigrationWriter::writeMo(const IdentityNickMO& identityNick)
2288 {
2289     bindValue(0, identityNick.nickid);
2290     bindValue(1, identityNick.identityId.toInt());
2291     bindValue(2, identityNick.nick);
2292     return exec();
2293 }
2294
2295 // bool PostgreSqlMigrationWriter::writeNetwork(const NetworkMO &network) {
2296 bool PostgreSqlMigrationWriter::writeMo(const NetworkMO& network)
2297 {
2298     bindValue(0, network.networkid.toInt());
2299     bindValue(1, network.userid.toInt());
2300     bindValue(2, network.networkname);
2301     if (_validIdentities.contains(network.identityid.toInt()))
2302         bindValue(3, network.identityid.toInt());
2303     else
2304         bindValue(3, QVariant());
2305     bindValue(4, network.encodingcodec);
2306     bindValue(5, network.decodingcodec);
2307     bindValue(6, network.servercodec);
2308     bindValue(7, network.userandomserver);
2309     bindValue(8, network.perform);
2310     bindValue(9, network.useautoidentify);
2311     bindValue(10, network.autoidentifyservice);
2312     bindValue(11, network.autoidentifypassword);
2313     bindValue(12, network.useautoreconnect);
2314     bindValue(13, network.autoreconnectinterval);
2315     bindValue(14, network.autoreconnectretries);
2316     bindValue(15, network.unlimitedconnectretries);
2317     bindValue(16, network.rejoinchannels);
2318     bindValue(17, network.connected);
2319     bindValue(18, network.usermode);
2320     bindValue(19, network.awaymessage);
2321     bindValue(20, network.attachperform);
2322     bindValue(21, network.detachperform);
2323     bindValue(22, network.usesasl);
2324     bindValue(23, network.saslaccount);
2325     bindValue(24, network.saslpassword);
2326     // Custom rate limiting
2327     bindValue(25, network.usecustommessagerate);
2328     bindValue(26, network.messagerateburstsize);
2329     bindValue(27, network.messageratedelay);
2330     bindValue(28, network.unlimitedmessagerate);
2331     return exec();
2332 }
2333
2334 // bool PostgreSqlMigrationWriter::writeBuffer(const BufferMO &buffer) {
2335 bool PostgreSqlMigrationWriter::writeMo(const BufferMO& buffer)
2336 {
2337     bindValue(0, buffer.bufferid.toInt());
2338     bindValue(1, buffer.userid.toInt());
2339     bindValue(2, buffer.groupid);
2340     bindValue(3, buffer.networkid.toInt());
2341     bindValue(4, buffer.buffername);
2342     bindValue(5, buffer.buffercname);
2343     bindValue(6, (int)buffer.buffertype);
2344     bindValue(7, buffer.lastmsgid);
2345     bindValue(8, buffer.lastseenmsgid);
2346     bindValue(9, buffer.markerlinemsgid);
2347     bindValue(10, buffer.bufferactivity);
2348     bindValue(11, buffer.highlightcount);
2349     bindValue(12, buffer.key);
2350     bindValue(13, buffer.joined);
2351     bindValue(14, buffer.cipher);
2352     return exec();
2353 }
2354
2355 // bool PostgreSqlMigrationWriter::writeBacklog(const BacklogMO &backlog) {
2356 bool PostgreSqlMigrationWriter::writeMo(const BacklogMO& backlog)
2357 {
2358     bindValue(0, backlog.messageid.toQint64());
2359     bindValue(1, backlog.time);
2360     bindValue(2, backlog.bufferid.toInt());
2361     bindValue(3, backlog.type);
2362     bindValue(4, (int)backlog.flags);
2363     bindValue(5, backlog.senderid);
2364     bindValue(6, backlog.senderprefixes);
2365     bindValue(7, backlog.message);
2366     return exec();
2367 }
2368
2369 // bool PostgreSqlMigrationWriter::writeIrcServer(const IrcServerMO &ircserver) {
2370 bool PostgreSqlMigrationWriter::writeMo(const IrcServerMO& ircserver)
2371 {
2372     bindValue(0, ircserver.serverid);
2373     bindValue(1, ircserver.userid.toInt());
2374     bindValue(2, ircserver.networkid.toInt());
2375     bindValue(3, ircserver.hostname);
2376     bindValue(4, ircserver.port);
2377     bindValue(5, ircserver.password);
2378     bindValue(6, ircserver.ssl);
2379     bindValue(7, ircserver.sslversion);
2380     bindValue(8, ircserver.useproxy);
2381     bindValue(9, ircserver.proxytype);
2382     bindValue(10, ircserver.proxyhost);
2383     bindValue(11, ircserver.proxyport);
2384     bindValue(12, ircserver.proxyuser);
2385     bindValue(13, ircserver.proxypass);
2386     bindValue(14, ircserver.sslverify);
2387     return exec();
2388 }
2389
2390 // bool PostgreSqlMigrationWriter::writeUserSetting(const UserSettingMO &userSetting) {
2391 bool PostgreSqlMigrationWriter::writeMo(const UserSettingMO& userSetting)
2392 {
2393     bindValue(0, userSetting.userid.toInt());
2394     bindValue(1, userSetting.settingname);
2395     bindValue(2, userSetting.settingvalue);
2396     return exec();
2397 }
2398
2399 bool PostgreSqlMigrationWriter::writeMo(const CoreStateMO& coreState)
2400 {
2401     bindValue(0, coreState.key);
2402     bindValue(1, coreState.value);
2403     return exec();
2404 }
2405
2406 bool PostgreSqlMigrationWriter::postProcess()
2407 {
2408     QSqlDatabase db = logDb();
2409     QList<Sequence> sequences;
2410     sequences << Sequence("backlog", "messageid") << Sequence("buffer", "bufferid") << Sequence("identity", "identityid")
2411               << Sequence("identity_nick", "nickid") << Sequence("ircserver", "serverid") << Sequence("network", "networkid")
2412               << Sequence("quasseluser", "userid") << Sequence("sender", "senderid");
2413     QList<Sequence>::const_iterator iter;
2414     for (iter = sequences.constBegin(); iter != sequences.constEnd(); ++iter) {
2415         resetQuery();
2416         newQuery(QString("SELECT setval('%1_%2_seq', max(%2)) FROM %1").arg(iter->table, iter->field), db);
2417         if (!exec())
2418             return false;
2419     }
2420
2421     // Update the lastmsgid for all existing buffers.
2422     resetQuery();
2423     newQuery(QString("SELECT populate_lastmsgid()"), db);
2424     if (!exec())
2425         return false;
2426     return true;
2427 }