1 /***************************************************************************
2 * Copyright (C) 2005-2019 by the Quassel Project *
3 * devel@quassel-irc.org *
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. *
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. *
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 ***************************************************************************/
21 #include "postgresqlstorage.h"
28 PostgreSqlStorage::PostgreSqlStorage(QObject* parent)
29 : AbstractSqlStorage(parent)
32 std::unique_ptr<AbstractSqlMigrationWriter> PostgreSqlStorage::createMigrationWriter()
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};
45 bool PostgreSqlStorage::isAvailable() const
47 if (!QSqlDatabase::isDriverAvailable("QPSQL")) {
48 qWarning() << qPrintable(tr("PostgreSQL driver plugin not available for Qt. Installed drivers:"))
49 << qPrintable(QSqlDatabase::drivers().join(", "));
55 QString PostgreSqlStorage::backendId() const
57 return QString("PostgreSQL");
60 QString PostgreSqlStorage::displayName() const
62 return backendId(); // Note: Pre-0.13 clients use the displayName property for backend idenfication
65 QString PostgreSqlStorage::description() const
67 // FIXME: proper description
68 return tr("PostgreSQL Turbo Bomber HD!");
71 QVariantList PostgreSqlStorage::setupData() const
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");
79 bool PostgreSqlStorage::initDbSession(QSqlDatabase& db)
81 // check whether the Qt driver performs string escaping or not.
82 // i.e. test if it doubles slashes.
84 testField.setType(QVariant::String);
85 testField.setValue("\\");
86 QString formattedString = db.driver()->formatValue(testField);
87 switch (formattedString.count('\\')) {
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.
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");
101 // ok, so Qt does not escape...
102 // That means we have to ensure that postgres uses standard conforming strings...
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!";
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!";
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!";
130 void PostgreSqlStorage::setConnectionProperties(const QVariantMap& properties, const QProcessEnvironment& environment, bool loadFromEnvironment)
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");
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();
148 int PostgreSqlStorage::installedSchemaVersion()
150 QSqlQuery query(logDb());
151 query.prepare("SELECT value FROM coreinfo WHERE key = 'schemaversion'");
155 return query.value(0).toInt();
157 // maybe it's really old... (schema version 0)
158 query.prepare("SELECT MAX(version) FROM coreinfo");
162 return query.value(0).toInt();
164 return AbstractSqlStorage::installedSchemaVersion();
167 bool PostgreSqlStorage::updateSchemaVersion(int newVersion)
169 QSqlQuery query(logDb());
170 query.prepare("UPDATE coreinfo SET value = :version WHERE key = 'schemaversion'");
171 query.bindValue(":version", newVersion);
175 if (!watchQuery(query)) {
176 qCritical() << "PostgreSqlStorage::updateSchemaVersion(int): Updating schema version failed!";
182 bool PostgreSqlStorage::setupSchemaVersion(int version)
184 QSqlQuery query(logDb());
185 query.prepare("INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)");
186 query.bindValue(":version", version);
190 if (!watchQuery(query)) {
191 qCritical() << "PostgreSqlStorage::setupSchemaVersion(int): Updating schema version failed!";
197 UserId PostgreSqlStorage::addUser(const QString& user, const QString& password, const QString& authenticator)
199 QSqlQuery query(logDb());
200 query.prepare(queryString("insert_quasseluser"));
201 query.bindValue(":username", user);
202 query.bindValue(":password", hashPassword(password));
203 query.bindValue(":hashversion", Storage::HashVersion::Latest);
204 query.bindValue(":authenticator", authenticator);
206 if (!watchQuery(query))
210 UserId uid = query.value(0).toInt();
211 emit userAdded(uid, user);
215 bool PostgreSqlStorage::updateUser(UserId user, const QString& password)
217 QSqlQuery query(logDb());
218 query.prepare(queryString("update_userpassword"));
219 query.bindValue(":userid", user.toInt());
220 query.bindValue(":password", hashPassword(password));
221 query.bindValue(":hashversion", Storage::HashVersion::Latest);
224 return query.numRowsAffected() != 0;
227 void PostgreSqlStorage::renameUser(UserId user, const QString& newName)
229 QSqlQuery query(logDb());
230 query.prepare(queryString("update_username"));
231 query.bindValue(":userid", user.toInt());
232 query.bindValue(":username", newName);
235 emit userRenamed(user, newName);
238 UserId PostgreSqlStorage::validateUser(const QString& user, const QString& password)
240 QSqlQuery query(logDb());
241 query.prepare(queryString("select_authuser"));
242 query.bindValue(":username", user);
247 && checkHashedPassword(query.value(0).toInt(),
249 query.value(1).toString(),
250 static_cast<Storage::HashVersion>(query.value(2).toInt()))) {
251 return query.value(0).toInt();
258 UserId PostgreSqlStorage::getUserId(const QString& user)
260 QSqlQuery query(logDb());
261 query.prepare(queryString("select_userid"));
262 query.bindValue(":username", user);
267 return query.value(0).toInt();
274 QString PostgreSqlStorage::getUserAuthenticator(const UserId userid)
276 QSqlQuery query(logDb());
277 query.prepare(queryString("select_authenticator"));
278 query.bindValue(":userid", userid.toInt());
283 return query.value(0).toString();
290 UserId PostgreSqlStorage::internalUser()
292 QSqlQuery query(logDb());
293 query.prepare(queryString("select_internaluser"));
298 return query.value(0).toInt();
305 void PostgreSqlStorage::delUser(UserId user)
307 QSqlDatabase db = logDb();
308 if (!beginTransaction(db)) {
309 qWarning() << "PostgreSqlStorage::delUser(): cannot start transaction!";
314 query.prepare(queryString("delete_quasseluser"));
315 query.bindValue(":userid", user.toInt());
317 if (!watchQuery(query)) {
323 emit userRemoved(user);
327 void PostgreSqlStorage::setUserSetting(UserId userId, const QString& settingName, const QVariant& data)
330 QDataStream out(&rawData, QIODevice::WriteOnly);
331 out.setVersion(QDataStream::Qt_4_2);
334 QSqlDatabase db = logDb();
335 QSqlQuery selectQuery(db);
336 selectQuery.prepare(queryString("select_user_setting"));
337 selectQuery.bindValue(":userid", userId.toInt());
338 selectQuery.bindValue(":settingname", settingName);
339 safeExec(selectQuery);
340 watchQuery(selectQuery);
342 QString setQueryString;
343 if (!selectQuery.first()) {
344 setQueryString = queryString("insert_user_setting");
347 setQueryString = queryString("update_user_setting");
350 QSqlQuery setQuery(db);
351 setQuery.prepare(setQueryString);
352 setQuery.bindValue(":userid", userId.toInt());
353 setQuery.bindValue(":settingname", settingName);
354 setQuery.bindValue(":settingvalue", rawData);
356 watchQuery(setQuery);
359 QVariant PostgreSqlStorage::getUserSetting(UserId userId, const QString& settingName, const QVariant& defaultData)
361 QSqlQuery query(logDb());
362 query.prepare(queryString("select_user_setting"));
363 query.bindValue(":userid", userId.toInt());
364 query.bindValue(":settingname", settingName);
370 QByteArray rawData = query.value(0).toByteArray();
371 QDataStream in(&rawData, QIODevice::ReadOnly);
372 in.setVersion(QDataStream::Qt_4_2);
381 void PostgreSqlStorage::setCoreState(const QVariantList& data)
384 QDataStream out(&rawData, QIODevice::WriteOnly);
385 out.setVersion(QDataStream::Qt_4_2);
388 QSqlDatabase db = logDb();
389 QSqlQuery selectQuery(db);
390 selectQuery.prepare(queryString("select_core_state"));
391 selectQuery.bindValue(":key", "active_sessions");
392 safeExec(selectQuery);
393 watchQuery(selectQuery);
395 QString setQueryString;
396 if (!selectQuery.first()) {
397 setQueryString = queryString("insert_core_state");
400 setQueryString = queryString("update_core_state");
403 QSqlQuery setQuery(db);
404 setQuery.prepare(setQueryString);
405 setQuery.bindValue(":key", "active_sessions");
406 setQuery.bindValue(":value", rawData);
408 watchQuery(setQuery);
411 QVariantList PostgreSqlStorage::getCoreState(const QVariantList& defaultData)
413 QSqlQuery query(logDb());
414 query.prepare(queryString("select_core_state"));
415 query.bindValue(":key", "active_sessions");
421 QByteArray rawData = query.value(0).toByteArray();
422 QDataStream in(&rawData, QIODevice::ReadOnly);
423 in.setVersion(QDataStream::Qt_4_2);
432 IdentityId PostgreSqlStorage::createIdentity(UserId user, CoreIdentity& identity)
434 IdentityId identityId;
436 QSqlDatabase db = logDb();
437 if (!beginTransaction(db)) {
438 qWarning() << "PostgreSqlStorage::createIdentity(): Unable to start Transaction!";
439 qWarning() << " -" << qPrintable(db.lastError().text());
444 query.prepare(queryString("insert_identity"));
445 query.bindValue(":userid", user.toInt());
446 query.bindValue(":identityname", identity.identityName());
447 query.bindValue(":realname", identity.realName());
448 query.bindValue(":awaynick", identity.awayNick());
449 query.bindValue(":awaynickenabled", identity.awayNickEnabled());
450 query.bindValue(":awayreason", identity.awayReason());
451 query.bindValue(":awayreasonenabled", identity.awayReasonEnabled());
452 query.bindValue(":autoawayenabled", identity.awayReasonEnabled());
453 query.bindValue(":autoawaytime", identity.autoAwayTime());
454 query.bindValue(":autoawayreason", identity.autoAwayReason());
455 query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled());
456 query.bindValue(":detachawayenabled", identity.detachAwayEnabled());
457 query.bindValue(":detachawayreason", identity.detachAwayReason());
458 query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled());
459 query.bindValue(":ident", identity.ident());
460 query.bindValue(":kickreason", identity.kickReason());
461 query.bindValue(":partreason", identity.partReason());
462 query.bindValue(":quitreason", identity.quitReason());
464 query.bindValue(":sslcert", identity.sslCert().toPem());
465 query.bindValue(":sslkey", identity.sslKey().toPem());
467 query.bindValue(":sslcert", QByteArray());
468 query.bindValue(":sslkey", QByteArray());
471 if (!watchQuery(query)) {
477 identityId = query.value(0).toInt();
478 identity.setId(identityId);
480 if (!identityId.isValid()) {
485 QSqlQuery insertNickQuery(db);
486 insertNickQuery.prepare(queryString("insert_nick"));
487 foreach (QString nick, identity.nicks()) {
488 insertNickQuery.bindValue(":identityid", identityId.toInt());
489 insertNickQuery.bindValue(":nick", nick);
490 safeExec(insertNickQuery);
491 if (!watchQuery(insertNickQuery)) {
498 qWarning() << "PostgreSqlStorage::createIdentity(): committing data failed!";
499 qWarning() << " -" << qPrintable(db.lastError().text());
505 bool PostgreSqlStorage::updateIdentity(UserId user, const CoreIdentity& identity)
507 QSqlDatabase db = logDb();
508 if (!beginTransaction(db)) {
509 qWarning() << "PostgreSqlStorage::updateIdentity(): Unable to start Transaction!";
510 qWarning() << " -" << qPrintable(db.lastError().text());
514 QSqlQuery checkQuery(db);
515 checkQuery.prepare(queryString("select_checkidentity"));
516 checkQuery.bindValue(":identityid", identity.id().toInt());
517 checkQuery.bindValue(":userid", user.toInt());
518 safeExec(checkQuery);
519 watchQuery(checkQuery);
521 // there should be exactly one identity for the given id and user
522 if (!checkQuery.first() || checkQuery.value(0).toInt() != 1) {
528 query.prepare(queryString("update_identity"));
529 query.bindValue(":identityname", identity.identityName());
530 query.bindValue(":realname", identity.realName());
531 query.bindValue(":awaynick", identity.awayNick());
532 query.bindValue(":awaynickenabled", identity.awayNickEnabled());
533 query.bindValue(":awayreason", identity.awayReason());
534 query.bindValue(":awayreasonenabled", identity.awayReasonEnabled());
535 query.bindValue(":autoawayenabled", identity.awayReasonEnabled());
536 query.bindValue(":autoawaytime", identity.autoAwayTime());
537 query.bindValue(":autoawayreason", identity.autoAwayReason());
538 query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled());
539 query.bindValue(":detachawayenabled", identity.detachAwayEnabled());
540 query.bindValue(":detachawayreason", identity.detachAwayReason());
541 query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled());
542 query.bindValue(":ident", identity.ident());
543 query.bindValue(":kickreason", identity.kickReason());
544 query.bindValue(":partreason", identity.partReason());
545 query.bindValue(":quitreason", identity.quitReason());
547 query.bindValue(":sslcert", identity.sslCert().toPem());
548 query.bindValue(":sslkey", identity.sslKey().toPem());
550 query.bindValue(":sslcert", QByteArray());
551 query.bindValue(":sslkey", QByteArray());
553 query.bindValue(":identityid", identity.id().toInt());
556 if (!watchQuery(query)) {
561 QSqlQuery deleteNickQuery(db);
562 deleteNickQuery.prepare(queryString("delete_nicks"));
563 deleteNickQuery.bindValue(":identityid", identity.id().toInt());
564 safeExec(deleteNickQuery);
565 if (!watchQuery(deleteNickQuery)) {
570 QSqlQuery insertNickQuery(db);
571 insertNickQuery.prepare(queryString("insert_nick"));
572 foreach (QString nick, identity.nicks()) {
573 insertNickQuery.bindValue(":identityid", identity.id().toInt());
574 insertNickQuery.bindValue(":nick", nick);
575 safeExec(insertNickQuery);
576 if (!watchQuery(insertNickQuery)) {
583 qWarning() << "PostgreSqlStorage::updateIdentity(): committing data failed!";
584 qWarning() << " -" << qPrintable(db.lastError().text());
590 void PostgreSqlStorage::removeIdentity(UserId user, IdentityId identityId)
592 QSqlDatabase db = logDb();
593 if (!beginTransaction(db)) {
594 qWarning() << "PostgreSqlStorage::removeIdentity(): Unable to start Transaction!";
595 qWarning() << " -" << qPrintable(db.lastError().text());
600 query.prepare(queryString("delete_identity"));
601 query.bindValue(":identityid", identityId.toInt());
602 query.bindValue(":userid", user.toInt());
604 if (!watchQuery(query)) {
612 QList<CoreIdentity> PostgreSqlStorage::identities(UserId user)
614 QList<CoreIdentity> identities;
616 QSqlDatabase db = logDb();
617 if (!beginReadOnlyTransaction(db)) {
618 qWarning() << "PostgreSqlStorage::identites(): cannot start read only transaction!";
619 qWarning() << " -" << qPrintable(db.lastError().text());
624 query.prepare(queryString("select_identities"));
625 query.bindValue(":userid", user.toInt());
627 QSqlQuery nickQuery(db);
628 nickQuery.prepare(queryString("select_nicks"));
633 while (query.next()) {
634 CoreIdentity identity(IdentityId(query.value(0).toInt()));
636 identity.setIdentityName(query.value(1).toString());
637 identity.setRealName(query.value(2).toString());
638 identity.setAwayNick(query.value(3).toString());
639 identity.setAwayNickEnabled(!!query.value(4).toInt());
640 identity.setAwayReason(query.value(5).toString());
641 identity.setAwayReasonEnabled(!!query.value(6).toInt());
642 identity.setAutoAwayEnabled(!!query.value(7).toInt());
643 identity.setAutoAwayTime(query.value(8).toInt());
644 identity.setAutoAwayReason(query.value(9).toString());
645 identity.setAutoAwayReasonEnabled(!!query.value(10).toInt());
646 identity.setDetachAwayEnabled(!!query.value(11).toInt());
647 identity.setDetachAwayReason(query.value(12).toString());
648 identity.setDetachAwayReasonEnabled(!!query.value(13).toInt());
649 identity.setIdent(query.value(14).toString());
650 identity.setKickReason(query.value(15).toString());
651 identity.setPartReason(query.value(16).toString());
652 identity.setQuitReason(query.value(17).toString());
654 identity.setSslCert(query.value(18).toByteArray());
655 identity.setSslKey(query.value(19).toByteArray());
658 nickQuery.bindValue(":identityid", identity.id().toInt());
659 QList<QString> nicks;
661 watchQuery(nickQuery);
662 while (nickQuery.next()) {
663 nicks << nickQuery.value(0).toString();
665 identity.setNicks(nicks);
666 identities << identity;
672 NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo& info)
676 QSqlDatabase db = logDb();
677 if (!beginTransaction(db)) {
678 qWarning() << "PostgreSqlStorage::createNetwork(): failed to begin transaction!";
679 qWarning() << " -" << qPrintable(db.lastError().text());
684 query.prepare(queryString("insert_network"));
685 query.bindValue(":userid", user.toInt());
686 bindNetworkInfo(query, info);
688 if (!watchQuery(query)) {
694 networkId = query.value(0).toInt();
696 if (!networkId.isValid()) {
701 QSqlQuery insertServersQuery(db);
702 insertServersQuery.prepare(queryString("insert_server"));
703 foreach (Network::Server server, info.serverList) {
704 insertServersQuery.bindValue(":userid", user.toInt());
705 insertServersQuery.bindValue(":networkid", networkId.toInt());
706 bindServerInfo(insertServersQuery, server);
707 safeExec(insertServersQuery);
708 if (!watchQuery(insertServersQuery)) {
715 qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!";
716 qWarning() << " -" << qPrintable(db.lastError().text());
722 void PostgreSqlStorage::bindNetworkInfo(QSqlQuery& query, const NetworkInfo& info)
724 query.bindValue(":networkname", info.networkName);
725 query.bindValue(":identityid", info.identity.isValid() ? info.identity.toInt() : QVariant());
726 query.bindValue(":encodingcodec", QString(info.codecForEncoding));
727 query.bindValue(":decodingcodec", QString(info.codecForDecoding));
728 query.bindValue(":servercodec", QString(info.codecForServer));
729 query.bindValue(":userandomserver", info.useRandomServer);
730 query.bindValue(":perform", info.perform.join("\n"));
731 query.bindValue(":useautoidentify", info.useAutoIdentify);
732 query.bindValue(":autoidentifyservice", info.autoIdentifyService);
733 query.bindValue(":autoidentifypassword", info.autoIdentifyPassword);
734 query.bindValue(":usesasl", info.useSasl);
735 query.bindValue(":saslaccount", info.saslAccount);
736 query.bindValue(":saslpassword", info.saslPassword);
737 query.bindValue(":useautoreconnect", info.useAutoReconnect);
738 query.bindValue(":autoreconnectinterval", info.autoReconnectInterval);
739 query.bindValue(":autoreconnectretries", info.autoReconnectRetries);
740 query.bindValue(":unlimitedconnectretries", info.unlimitedReconnectRetries);
741 query.bindValue(":rejoinchannels", info.rejoinChannels);
742 // Custom rate limiting
743 query.bindValue(":usecustomessagerate", info.useCustomMessageRate);
744 query.bindValue(":messagerateburstsize", info.messageRateBurstSize);
745 query.bindValue(":messageratedelay", info.messageRateDelay);
746 query.bindValue(":unlimitedmessagerate", info.unlimitedMessageRate);
747 if (info.networkId.isValid())
748 query.bindValue(":networkid", info.networkId.toInt());
751 void PostgreSqlStorage::bindServerInfo(QSqlQuery& query, const Network::Server& server)
753 query.bindValue(":hostname", server.host);
754 query.bindValue(":port", server.port);
755 query.bindValue(":password", server.password);
756 query.bindValue(":ssl", server.useSsl);
757 query.bindValue(":sslversion", server.sslVersion);
758 query.bindValue(":useproxy", server.useProxy);
759 query.bindValue(":proxytype", server.proxyType);
760 query.bindValue(":proxyhost", server.proxyHost);
761 query.bindValue(":proxyport", server.proxyPort);
762 query.bindValue(":proxyuser", server.proxyUser);
763 query.bindValue(":proxypass", server.proxyPass);
764 query.bindValue(":sslverify", server.sslVerify);
767 bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo& info)
769 QSqlDatabase db = logDb();
770 if (!beginTransaction(db)) {
771 qWarning() << "PostgreSqlStorage::updateNetwork(): failed to begin transaction!";
772 qWarning() << " -" << qPrintable(db.lastError().text());
776 QSqlQuery updateQuery(db);
777 updateQuery.prepare(queryString("update_network"));
778 updateQuery.bindValue(":userid", user.toInt());
779 bindNetworkInfo(updateQuery, info);
780 safeExec(updateQuery);
781 if (!watchQuery(updateQuery)) {
785 if (updateQuery.numRowsAffected() != 1) {
786 // seems this is not our network...
791 QSqlQuery dropServersQuery(db);
792 dropServersQuery.prepare("DELETE FROM ircserver WHERE networkid = :networkid");
793 dropServersQuery.bindValue(":networkid", info.networkId.toInt());
794 safeExec(dropServersQuery);
795 if (!watchQuery(dropServersQuery)) {
800 QSqlQuery insertServersQuery(db);
801 insertServersQuery.prepare(queryString("insert_server"));
802 foreach (Network::Server server, info.serverList) {
803 insertServersQuery.bindValue(":userid", user.toInt());
804 insertServersQuery.bindValue(":networkid", info.networkId.toInt());
805 bindServerInfo(insertServersQuery, server);
806 safeExec(insertServersQuery);
807 if (!watchQuery(insertServersQuery)) {
814 qWarning() << "PostgreSqlStorage::updateNetwork(): committing data failed!";
815 qWarning() << " -" << qPrintable(db.lastError().text());
821 bool PostgreSqlStorage::removeNetwork(UserId user, const NetworkId& networkId)
823 QSqlDatabase db = logDb();
824 if (!beginTransaction(db)) {
825 qWarning() << "PostgreSqlStorage::removeNetwork(): cannot start transaction!";
826 qWarning() << " -" << qPrintable(db.lastError().text());
831 query.prepare(queryString("delete_network"));
832 query.bindValue(":userid", user.toInt());
833 query.bindValue(":networkid", networkId.toInt());
835 if (!watchQuery(query)) {
844 QList<NetworkInfo> PostgreSqlStorage::networks(UserId user)
846 QList<NetworkInfo> nets;
848 QSqlDatabase db = logDb();
849 if (!beginReadOnlyTransaction(db)) {
850 qWarning() << "PostgreSqlStorage::networks(): cannot start read only transaction!";
851 qWarning() << " -" << qPrintable(db.lastError().text());
855 QSqlQuery networksQuery(db);
856 networksQuery.prepare(queryString("select_networks_for_user"));
857 networksQuery.bindValue(":userid", user.toInt());
859 QSqlQuery serversQuery(db);
860 serversQuery.prepare(queryString("select_servers_for_network"));
862 safeExec(networksQuery);
863 if (!watchQuery(networksQuery)) {
868 while (networksQuery.next()) {
870 net.networkId = networksQuery.value(0).toInt();
871 net.networkName = networksQuery.value(1).toString();
872 net.identity = networksQuery.value(2).toInt();
873 net.codecForServer = networksQuery.value(3).toString().toLatin1();
874 net.codecForEncoding = networksQuery.value(4).toString().toLatin1();
875 net.codecForDecoding = networksQuery.value(5).toString().toLatin1();
876 net.useRandomServer = networksQuery.value(6).toBool();
877 net.perform = networksQuery.value(7).toString().split("\n");
878 net.useAutoIdentify = networksQuery.value(8).toBool();
879 net.autoIdentifyService = networksQuery.value(9).toString();
880 net.autoIdentifyPassword = networksQuery.value(10).toString();
881 net.useAutoReconnect = networksQuery.value(11).toBool();
882 net.autoReconnectInterval = networksQuery.value(12).toUInt();
883 net.autoReconnectRetries = networksQuery.value(13).toInt();
884 net.unlimitedReconnectRetries = networksQuery.value(14).toBool();
885 net.rejoinChannels = networksQuery.value(15).toBool();
886 net.useSasl = networksQuery.value(16).toBool();
887 net.saslAccount = networksQuery.value(17).toString();
888 net.saslPassword = networksQuery.value(18).toString();
889 // Custom rate limiting
890 net.useCustomMessageRate = networksQuery.value(19).toBool();
891 net.messageRateBurstSize = networksQuery.value(20).toUInt();
892 net.messageRateDelay = networksQuery.value(21).toUInt();
893 net.unlimitedMessageRate = networksQuery.value(22).toBool();
895 serversQuery.bindValue(":networkid", net.networkId.toInt());
896 safeExec(serversQuery);
897 if (!watchQuery(serversQuery)) {
902 Network::ServerList servers;
903 while (serversQuery.next()) {
904 Network::Server server;
905 server.host = serversQuery.value(0).toString();
906 server.port = serversQuery.value(1).toUInt();
907 server.password = serversQuery.value(2).toString();
908 server.useSsl = serversQuery.value(3).toBool();
909 server.sslVersion = serversQuery.value(4).toInt();
910 server.useProxy = serversQuery.value(5).toBool();
911 server.proxyType = serversQuery.value(6).toInt();
912 server.proxyHost = serversQuery.value(7).toString();
913 server.proxyPort = serversQuery.value(8).toUInt();
914 server.proxyUser = serversQuery.value(9).toString();
915 server.proxyPass = serversQuery.value(10).toString();
916 server.sslVerify = serversQuery.value(11).toBool();
919 net.serverList = servers;
926 QList<NetworkId> PostgreSqlStorage::connectedNetworks(UserId user)
928 QList<NetworkId> connectedNets;
930 QSqlDatabase db = logDb();
931 if (!beginReadOnlyTransaction(db)) {
932 qWarning() << "PostgreSqlStorage::connectedNetworks(): cannot start read only transaction!";
933 qWarning() << " -" << qPrintable(db.lastError().text());
934 return connectedNets;
938 query.prepare(queryString("select_connected_networks"));
939 query.bindValue(":userid", user.toInt());
943 while (query.next()) {
944 connectedNets << query.value(0).toInt();
948 return connectedNets;
951 void PostgreSqlStorage::setNetworkConnected(UserId user, const NetworkId& networkId, bool isConnected)
953 QSqlQuery query(logDb());
954 query.prepare(queryString("update_network_connected"));
955 query.bindValue(":userid", user.toInt());
956 query.bindValue(":networkid", networkId.toInt());
957 query.bindValue(":connected", isConnected);
962 QHash<QString, QString> PostgreSqlStorage::persistentChannels(UserId user, const NetworkId& networkId)
964 QHash<QString, QString> persistentChans;
966 QSqlDatabase db = logDb();
967 if (!beginReadOnlyTransaction(db)) {
968 qWarning() << "PostgreSqlStorage::persistentChannels(): cannot start read only transaction!";
969 qWarning() << " -" << qPrintable(db.lastError().text());
970 return persistentChans;
974 query.prepare(queryString("select_persistent_channels"));
975 query.bindValue(":userid", user.toInt());
976 query.bindValue(":networkid", networkId.toInt());
980 while (query.next()) {
981 persistentChans[query.value(0).toString()] = query.value(1).toString();
985 return persistentChans;
988 void PostgreSqlStorage::setChannelPersistent(UserId user, const NetworkId& networkId, const QString& channel, bool isJoined)
990 QSqlQuery query(logDb());
991 query.prepare(queryString("update_buffer_persistent_channel"));
992 query.bindValue(":userid", user.toInt());
993 query.bindValue(":networkid", networkId.toInt());
994 query.bindValue(":buffercname", channel.toLower());
995 query.bindValue(":joined", isJoined);
1000 void PostgreSqlStorage::setPersistentChannelKey(UserId user, const NetworkId& networkId, const QString& channel, const QString& key)
1002 QSqlQuery query(logDb());
1003 query.prepare(queryString("update_buffer_set_channel_key"));
1004 query.bindValue(":userid", user.toInt());
1005 query.bindValue(":networkid", networkId.toInt());
1006 query.bindValue(":buffercname", channel.toLower());
1007 query.bindValue(":key", key);
1012 QString PostgreSqlStorage::awayMessage(UserId user, NetworkId networkId)
1014 QSqlQuery query(logDb());
1015 query.prepare(queryString("select_network_awaymsg"));
1016 query.bindValue(":userid", user.toInt());
1017 query.bindValue(":networkid", networkId.toInt());
1022 awayMsg = query.value(0).toString();
1026 void PostgreSqlStorage::setAwayMessage(UserId user, NetworkId networkId, const QString& awayMsg)
1028 QSqlQuery query(logDb());
1029 query.prepare(queryString("update_network_set_awaymsg"));
1030 query.bindValue(":userid", user.toInt());
1031 query.bindValue(":networkid", networkId.toInt());
1032 query.bindValue(":awaymsg", awayMsg);
1037 QString PostgreSqlStorage::userModes(UserId user, NetworkId networkId)
1039 QSqlQuery query(logDb());
1040 query.prepare(queryString("select_network_usermode"));
1041 query.bindValue(":userid", user.toInt());
1042 query.bindValue(":networkid", networkId.toInt());
1047 modes = query.value(0).toString();
1051 void PostgreSqlStorage::setUserModes(UserId user, NetworkId networkId, const QString& userModes)
1053 QSqlQuery query(logDb());
1054 query.prepare(queryString("update_network_set_usermode"));
1055 query.bindValue(":userid", user.toInt());
1056 query.bindValue(":networkid", networkId.toInt());
1057 query.bindValue(":usermode", userModes);
1062 BufferInfo PostgreSqlStorage::bufferInfo(UserId user, const NetworkId& networkId, BufferInfo::Type type, const QString& buffer, bool create)
1064 QSqlDatabase db = logDb();
1065 if (!beginTransaction(db)) {
1066 qWarning() << "PostgreSqlStorage::bufferInfo(): cannot start read only transaction!";
1067 qWarning() << " -" << qPrintable(db.lastError().text());
1071 QSqlQuery query(db);
1072 query.prepare(queryString("select_bufferByName"));
1073 query.bindValue(":networkid", networkId.toInt());
1074 query.bindValue(":userid", user.toInt());
1075 query.bindValue(":buffercname", buffer.toLower());
1079 if (query.first()) {
1080 BufferInfo bufferInfo = BufferInfo(query.value(0).toInt(), networkId, (BufferInfo::Type)query.value(1).toInt(), 0, buffer);
1082 qCritical() << "PostgreSqlStorage::bufferInfo(): received more then one Buffer!";
1083 qCritical() << " Query:" << query.lastQuery();
1084 qCritical() << " bound Values:";
1085 QList<QVariant> list = query.boundValues().values();
1086 for (int i = 0; i < list.size(); ++i)
1087 qCritical() << i << ":" << list.at(i).toString().toLatin1().data();
1099 QSqlQuery createQuery(db);
1100 createQuery.prepare(queryString("insert_buffer"));
1101 createQuery.bindValue(":userid", user.toInt());
1102 createQuery.bindValue(":networkid", networkId.toInt());
1103 createQuery.bindValue(":buffertype", (int)type);
1104 createQuery.bindValue(":buffername", buffer);
1105 createQuery.bindValue(":buffercname", buffer.toLower());
1106 createQuery.bindValue(":joined", type & BufferInfo::ChannelBuffer ? true : false);
1108 safeExec(createQuery);
1110 if (!watchQuery(createQuery)) {
1111 qWarning() << "PostgreSqlStorage::bufferInfo(): unable to create buffer";
1113 return BufferInfo();
1116 createQuery.first();
1118 BufferInfo bufferInfo = BufferInfo(createQuery.value(0).toInt(), networkId, type, 0, buffer);
1123 BufferInfo PostgreSqlStorage::getBufferInfo(UserId user, const BufferId& bufferId)
1125 QSqlQuery query(logDb());
1126 query.prepare(queryString("select_buffer_by_id"));
1127 query.bindValue(":userid", user.toInt());
1128 query.bindValue(":bufferid", bufferId.toInt());
1130 if (!watchQuery(query))
1136 BufferInfo bufferInfo(query.value(0).toInt(),
1137 query.value(1).toInt(),
1138 (BufferInfo::Type)query.value(2).toInt(),
1140 query.value(4).toString());
1141 Q_ASSERT(!query.next());
1146 QList<BufferInfo> PostgreSqlStorage::requestBuffers(UserId user)
1148 QList<BufferInfo> bufferlist;
1150 QSqlDatabase db = logDb();
1151 if (!beginReadOnlyTransaction(db)) {
1152 qWarning() << "PostgreSqlStorage::requestBuffers(): cannot start read only transaction!";
1153 qWarning() << " -" << qPrintable(db.lastError().text());
1157 QSqlQuery query(db);
1158 query.prepare(queryString("select_buffers"));
1159 query.bindValue(":userid", user.toInt());
1163 while (query.next()) {
1164 bufferlist << BufferInfo(query.value(0).toInt(),
1165 query.value(1).toInt(),
1166 (BufferInfo::Type)query.value(2).toInt(),
1167 query.value(3).toInt(),
1168 query.value(4).toString());
1174 QList<BufferId> PostgreSqlStorage::requestBufferIdsForNetwork(UserId user, NetworkId networkId)
1176 QList<BufferId> bufferList;
1178 QSqlDatabase db = logDb();
1179 if (!beginReadOnlyTransaction(db)) {
1180 qWarning() << "PostgreSqlStorage::requestBufferIdsForNetwork(): cannot start read only transaction!";
1181 qWarning() << " -" << qPrintable(db.lastError().text());
1185 QSqlQuery query(db);
1186 query.prepare(queryString("select_buffers_for_network"));
1187 query.bindValue(":networkid", networkId.toInt());
1188 query.bindValue(":userid", user.toInt());
1192 while (query.next()) {
1193 bufferList << BufferId(query.value(0).toInt());
1199 bool PostgreSqlStorage::removeBuffer(const UserId& user, const BufferId& bufferId)
1201 QSqlDatabase db = logDb();
1202 if (!beginTransaction(db)) {
1203 qWarning() << "PostgreSqlStorage::removeBuffer(): cannot start transaction!";
1207 QSqlQuery query(db);
1208 query.prepare(queryString("delete_buffer_for_bufferid"));
1209 query.bindValue(":userid", user.toInt());
1210 query.bindValue(":bufferid", bufferId.toInt());
1212 if (!watchQuery(query)) {
1217 int numRows = query.numRowsAffected();
1226 // there was more then one buffer deleted...
1227 qWarning() << "PostgreSqlStorage::removeBuffer(): Userid" << user << "BufferId"
1228 << "caused deletion of" << numRows << "Buffers! Rolling back transaction...";
1234 bool PostgreSqlStorage::renameBuffer(const UserId& user, const BufferId& bufferId, const QString& newName)
1236 QSqlDatabase db = logDb();
1237 if (!beginTransaction(db)) {
1238 qWarning() << "PostgreSqlStorage::renameBuffer(): cannot start transaction!";
1242 QSqlQuery query(db);
1243 query.prepare(queryString("update_buffer_name"));
1244 query.bindValue(":buffername", newName);
1245 query.bindValue(":buffercname", newName.toLower());
1246 query.bindValue(":userid", user.toInt());
1247 query.bindValue(":bufferid", bufferId.toInt());
1249 if (!watchQuery(query)) {
1254 int numRows = query.numRowsAffected();
1263 // there was more then one buffer deleted...
1264 qWarning() << "PostgreSqlStorage::renameBuffer(): Userid" << user << "BufferId"
1265 << "affected" << numRows << "Buffers! Rolling back transaction...";
1271 bool PostgreSqlStorage::mergeBuffersPermanently(const UserId& user, const BufferId& bufferId1, const BufferId& bufferId2)
1273 QSqlDatabase db = logDb();
1274 if (!beginTransaction(db)) {
1275 qWarning() << "PostgreSqlStorage::mergeBuffersPermanently(): cannot start transaction!";
1276 qWarning() << " -" << qPrintable(db.lastError().text());
1280 QSqlQuery checkQuery(db);
1281 checkQuery.prepare("SELECT count(*) FROM buffer "
1282 "WHERE userid = :userid AND bufferid IN (:buffer1, :buffer2)");
1283 checkQuery.bindValue(":userid", user.toInt());
1284 checkQuery.bindValue(":buffer1", bufferId1.toInt());
1285 checkQuery.bindValue(":buffer2", bufferId2.toInt());
1286 safeExec(checkQuery);
1287 if (!watchQuery(checkQuery)) {
1292 if (checkQuery.value(0).toInt() != 2) {
1297 QSqlQuery query(db);
1298 query.prepare(queryString("update_backlog_bufferid"));
1299 query.bindValue(":oldbufferid", bufferId2.toInt());
1300 query.bindValue(":newbufferid", bufferId1.toInt());
1302 if (!watchQuery(query)) {
1307 QSqlQuery delBufferQuery(logDb());
1308 delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
1309 delBufferQuery.bindValue(":userid", user.toInt());
1310 delBufferQuery.bindValue(":bufferid", bufferId2.toInt());
1311 safeExec(delBufferQuery);
1312 if (!watchQuery(delBufferQuery)) {
1321 void PostgreSqlStorage::setBufferLastSeenMsg(UserId user, const BufferId& bufferId, const MsgId& msgId)
1323 QSqlQuery query(logDb());
1324 query.prepare(queryString("update_buffer_lastseen"));
1326 query.bindValue(":userid", user.toInt());
1327 query.bindValue(":bufferid", bufferId.toInt());
1328 query.bindValue(":lastseenmsgid", msgId.toQint64());
1333 QHash<BufferId, MsgId> PostgreSqlStorage::bufferLastSeenMsgIds(UserId user)
1335 QHash<BufferId, MsgId> lastSeenHash;
1337 QSqlDatabase db = logDb();
1338 if (!beginReadOnlyTransaction(db)) {
1339 qWarning() << "PostgreSqlStorage::bufferLastSeenMsgIds(): cannot start read only transaction!";
1340 qWarning() << " -" << qPrintable(db.lastError().text());
1341 return lastSeenHash;
1344 QSqlQuery query(db);
1345 query.prepare(queryString("select_buffer_lastseen_messages"));
1346 query.bindValue(":userid", user.toInt());
1348 if (!watchQuery(query)) {
1350 return lastSeenHash;
1353 while (query.next()) {
1354 lastSeenHash[query.value(0).toInt()] = query.value(1).toLongLong();
1358 return lastSeenHash;
1361 void PostgreSqlStorage::setBufferMarkerLineMsg(UserId user, const BufferId& bufferId, const MsgId& msgId)
1363 QSqlQuery query(logDb());
1364 query.prepare(queryString("update_buffer_markerlinemsgid"));
1366 query.bindValue(":userid", user.toInt());
1367 query.bindValue(":bufferid", bufferId.toInt());
1368 query.bindValue(":markerlinemsgid", msgId.toQint64());
1373 QHash<BufferId, MsgId> PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user)
1375 QHash<BufferId, MsgId> markerLineHash;
1377 QSqlDatabase db = logDb();
1378 if (!beginReadOnlyTransaction(db)) {
1379 qWarning() << "PostgreSqlStorage::bufferMarkerLineMsgIds(): cannot start read only transaction!";
1380 qWarning() << " -" << qPrintable(db.lastError().text());
1381 return markerLineHash;
1384 QSqlQuery query(db);
1385 query.prepare(queryString("select_buffer_markerlinemsgids"));
1386 query.bindValue(":userid", user.toInt());
1388 if (!watchQuery(query)) {
1390 return markerLineHash;
1393 while (query.next()) {
1394 markerLineHash[query.value(0).toInt()] = query.value(1).toLongLong();
1398 return markerLineHash;
1401 void PostgreSqlStorage::setBufferActivity(UserId user, BufferId bufferId, Message::Types bufferActivity)
1403 QSqlQuery query(logDb());
1404 query.prepare(queryString("update_buffer_bufferactivity"));
1406 query.bindValue(":userid", user.toInt());
1407 query.bindValue(":bufferid", bufferId.toInt());
1408 query.bindValue(":bufferactivity", (int)bufferActivity);
1413 QHash<BufferId, Message::Types> PostgreSqlStorage::bufferActivities(UserId user)
1415 QHash<BufferId, Message::Types> bufferActivityHash;
1417 QSqlDatabase db = logDb();
1418 if (!beginReadOnlyTransaction(db)) {
1419 qWarning() << "PostgreSqlStorage::bufferActivities(): cannot start read only transaction!";
1420 qWarning() << " -" << qPrintable(db.lastError().text());
1421 return bufferActivityHash;
1424 QSqlQuery query(db);
1425 query.prepare(queryString("select_buffer_bufferactivities"));
1426 query.bindValue(":userid", user.toInt());
1428 if (!watchQuery(query)) {
1430 return bufferActivityHash;
1433 while (query.next()) {
1434 bufferActivityHash[query.value(0).toInt()] = Message::Types(query.value(1).toInt());
1438 return bufferActivityHash;
1441 Message::Types PostgreSqlStorage::bufferActivity(BufferId bufferId, MsgId lastSeenMsgId)
1443 QSqlQuery query(logDb());
1444 query.prepare(queryString("select_buffer_bufferactivity"));
1445 query.bindValue(":bufferid", bufferId.toInt());
1446 query.bindValue(":lastseenmsgid", lastSeenMsgId.toQint64());
1449 Message::Types result = Message::Types(nullptr);
1451 result = Message::Types(query.value(0).toInt());
1455 QHash<QString, QByteArray> PostgreSqlStorage::bufferCiphers(UserId user, const NetworkId& networkId)
1457 QHash<QString, QByteArray> bufferCiphers;
1459 QSqlDatabase db = logDb();
1460 if (!beginReadOnlyTransaction(db)) {
1461 qWarning() << "PostgreSqlStorage::persistentChannels(): cannot start read only transaction!";
1462 qWarning() << " -" << qPrintable(db.lastError().text());
1463 return bufferCiphers;
1466 QSqlQuery query(db);
1467 query.prepare(queryString("select_buffer_ciphers"));
1468 query.bindValue(":userid", user.toInt());
1469 query.bindValue(":networkid", networkId.toInt());
1473 while (query.next()) {
1474 bufferCiphers[query.value(0).toString()] = QByteArray::fromHex(query.value(1).toString().toUtf8());
1478 return bufferCiphers;
1481 void PostgreSqlStorage::setBufferCipher(UserId user, const NetworkId& networkId, const QString& bufferName, const QByteArray& cipher)
1483 QSqlQuery query(logDb());
1484 query.prepare(queryString("update_buffer_cipher"));
1485 query.bindValue(":userid", user.toInt());
1486 query.bindValue(":networkid", networkId.toInt());
1487 query.bindValue(":buffercname", bufferName.toLower());
1488 query.bindValue(":cipher", QString(cipher.toHex()));
1493 void PostgreSqlStorage::setHighlightCount(UserId user, BufferId bufferId, int highlightcount)
1495 QSqlQuery query(logDb());
1496 query.prepare(queryString("update_buffer_highlightcount"));
1498 query.bindValue(":userid", user.toInt());
1499 query.bindValue(":bufferid", bufferId.toInt());
1500 query.bindValue(":highlightcount", highlightcount);
1505 QHash<BufferId, int> PostgreSqlStorage::highlightCounts(UserId user)
1507 QHash<BufferId, int> highlightCountHash;
1509 QSqlDatabase db = logDb();
1510 if (!beginReadOnlyTransaction(db)) {
1511 qWarning() << "PostgreSqlStorage::highlightCounts(): cannot start read only transaction!";
1512 qWarning() << " -" << qPrintable(db.lastError().text());
1513 return highlightCountHash;
1516 QSqlQuery query(db);
1517 query.prepare(queryString("select_buffer_highlightcounts"));
1518 query.bindValue(":userid", user.toInt());
1520 if (!watchQuery(query)) {
1522 return highlightCountHash;
1525 while (query.next()) {
1526 highlightCountHash[query.value(0).toInt()] = query.value(1).toInt();
1530 return highlightCountHash;
1533 int PostgreSqlStorage::highlightCount(BufferId bufferId, MsgId lastSeenMsgId)
1535 QSqlQuery query(logDb());
1536 query.prepare(queryString("select_buffer_highlightcount"));
1537 query.bindValue(":bufferid", bufferId.toInt());
1538 query.bindValue(":lastseenmsgid", lastSeenMsgId.toQint64());
1541 auto result = int(0);
1543 result = query.value(0).toInt();
1547 bool PostgreSqlStorage::logMessage(Message& msg)
1549 QSqlDatabase db = logDb();
1550 if (!beginTransaction(db)) {
1551 qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!";
1552 qWarning() << " -" << qPrintable(db.lastError().text());
1556 QVariantList senderParams;
1557 senderParams << msg.sender() << msg.realName() << msg.avatarUrl();
1558 QSqlQuery getSenderIdQuery = executePreparedQuery("select_senderid", senderParams, db);
1560 if (getSenderIdQuery.first()) {
1561 senderId = getSenderIdQuery.value(0).toLongLong();
1564 // it's possible that the sender was already added by another thread
1565 // since the insert might fail we're setting a savepoint
1566 savePoint("sender_sp1", db);
1567 QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", senderParams, db);
1569 if (addSenderQuery.lastError().isValid()) {
1570 rollbackSavePoint("sender_sp1", db);
1571 getSenderIdQuery = executePreparedQuery("select_senderid", senderParams, db);
1572 watchQuery(getSenderIdQuery);
1573 getSenderIdQuery.first();
1574 senderId = getSenderIdQuery.value(0).toLongLong();
1577 releaseSavePoint("sender_sp1", db);
1578 addSenderQuery.first();
1579 senderId = addSenderQuery.value(0).toLongLong();
1583 QVariantList params;
1584 // PostgreSQL handles QDateTime()'s serialized format by default, and QDateTime() serializes
1585 // to a 64-bit time compatible format by default.
1586 params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() << senderId << msg.senderPrefixes()
1588 QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db);
1590 if (!watchQuery(logMessageQuery)) {
1595 logMessageQuery.first();
1596 MsgId msgId = logMessageQuery.value(0).toLongLong();
1598 if (msgId.isValid()) {
1599 msg.setMsgId(msgId);
1607 bool PostgreSqlStorage::logMessages(MessageList& msgs)
1609 QSqlDatabase db = logDb();
1610 if (!beginTransaction(db)) {
1611 qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!";
1612 qWarning() << " -" << qPrintable(db.lastError().text());
1616 QList<int> senderIdList;
1617 QHash<SenderData, qint64> senderIds;
1618 QSqlQuery addSenderQuery;
1619 QSqlQuery selectSenderQuery;
1621 for (int i = 0; i < msgs.count(); i++) {
1622 auto& msg = msgs.at(i);
1623 SenderData sender = {msg.sender(), msg.realName(), msg.avatarUrl()};
1624 if (senderIds.contains(sender)) {
1625 senderIdList << senderIds[sender];
1629 QVariantList senderParams;
1630 senderParams << sender.sender << sender.realname << sender.avatarurl;
1632 selectSenderQuery = executePreparedQuery("select_senderid", senderParams, db);
1633 if (selectSenderQuery.first()) {
1634 senderIdList << selectSenderQuery.value(0).toLongLong();
1635 senderIds[sender] = selectSenderQuery.value(0).toLongLong();
1638 savePoint("sender_sp", db);
1639 addSenderQuery = executePreparedQuery("insert_sender", senderParams, db);
1640 if (addSenderQuery.lastError().isValid()) {
1641 // seems it was inserted meanwhile... by a different thread
1642 rollbackSavePoint("sender_sp", db);
1643 selectSenderQuery = executePreparedQuery("select_senderid", senderParams, db);
1644 watchQuery(selectSenderQuery);
1645 selectSenderQuery.first();
1646 senderIdList << selectSenderQuery.value(0).toLongLong();
1647 senderIds[sender] = selectSenderQuery.value(0).toLongLong();
1650 releaseSavePoint("sender_sp", db);
1651 addSenderQuery.first();
1652 senderIdList << addSenderQuery.value(0).toLongLong();
1653 senderIds[sender] = addSenderQuery.value(0).toLongLong();
1658 // yes we loop twice over the same list. This avoids alternating queries.
1660 for (int i = 0; i < msgs.count(); i++) {
1661 Message& msg = msgs[i];
1662 QVariantList params;
1663 // PostgreSQL handles QDateTime()'s serialized format by default, and QDateTime() serializes
1664 // to a 64-bit time compatible format by default.
1665 params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() << senderIdList.at(i)
1666 << msg.senderPrefixes() << msg.contents();
1667 QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db);
1668 if (!watchQuery(logMessageQuery)) {
1674 logMessageQuery.first();
1675 msg.setMsgId(logMessageQuery.value(0).toLongLong());
1680 // we had a rollback in the db so we need to reset all msgIds
1681 for (int i = 0; i < msgs.count(); i++) {
1682 msgs[i].setMsgId(MsgId());
1691 QList<Message> PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit)
1693 QList<Message> messagelist;
1695 QSqlDatabase db = logDb();
1696 if (!beginReadOnlyTransaction(db)) {
1697 qWarning() << "PostgreSqlStorage::requestMsgs(): cannot start read only transaction!";
1698 qWarning() << " -" << qPrintable(db.lastError().text());
1702 BufferInfo bufferInfo = getBufferInfo(user, bufferId);
1703 if (!bufferInfo.isValid()) {
1709 QVariantList params;
1710 if (last == -1 && first == -1) {
1711 queryName = "select_messagesNewestK";
1713 else if (last == -1) {
1714 queryName = "select_messagesNewerThan";
1715 params << first.toQint64();
1718 queryName = "select_messagesRange";
1719 params << first.toQint64();
1720 params << last.toQint64();
1722 params << bufferId.toInt();
1726 params << QVariant(QVariant::Int);
1728 QSqlQuery query = executePreparedQuery(queryName, params, db);
1730 if (!watchQuery(query)) {
1731 qDebug() << "select_messages failed";
1736 QDateTime timestamp;
1737 while (query.next()) {
1738 // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed
1739 // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
1740 timestamp = query.value(1).toDateTime();
1741 timestamp.setTimeSpec(Qt::UTC);
1742 Message msg(timestamp,
1744 (Message::Type)query.value(2).toInt(),
1745 query.value(8).toString(),
1746 query.value(4).toString(),
1747 query.value(5).toString(),
1748 query.value(6).toString(),
1749 query.value(7).toString(),
1750 (Message::Flags)query.value(3).toInt());
1751 msg.setMsgId(query.value(0).toLongLong());
1759 QList<Message> PostgreSqlStorage::requestMsgsFiltered(
1760 UserId user, BufferId bufferId, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags)
1762 QList<Message> messagelist;
1764 QSqlDatabase db = logDb();
1765 if (!beginReadOnlyTransaction(db)) {
1766 qWarning() << "PostgreSqlStorage::requestMsgs(): cannot start read only transaction!";
1767 qWarning() << " -" << qPrintable(db.lastError().text());
1771 BufferInfo bufferInfo = getBufferInfo(user, bufferId);
1772 if (!bufferInfo.isValid()) {
1777 QSqlQuery query(db);
1778 if (last == -1 && first == -1) {
1779 query.prepare(queryString("select_messagesNewestK_filtered"));
1781 else if (last == -1) {
1782 query.prepare(queryString("select_messagesNewerThan_filtered"));
1783 query.bindValue(":first", first.toQint64());
1786 query.prepare(queryString("select_messagesRange_filtered"));
1787 query.bindValue(":last", last.toQint64());
1788 query.bindValue(":first", first.toQint64());
1790 query.bindValue(":buffer", bufferId.toInt());
1791 query.bindValue(":limit", limit);
1793 query.bindValue(":type", typeRaw);
1794 int flagsRaw = flags;
1795 query.bindValue(":flags", flagsRaw);
1798 if (!watchQuery(query)) {
1799 qDebug() << "select_messages failed";
1804 QDateTime timestamp;
1805 while (query.next()) {
1806 // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed
1807 // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
1808 timestamp = query.value(1).toDateTime();
1809 timestamp.setTimeSpec(Qt::UTC);
1810 Message msg(timestamp,
1812 (Message::Type)query.value(2).toInt(),
1813 query.value(8).toString(),
1814 query.value(4).toString(),
1815 query.value(5).toString(),
1816 query.value(6).toString(),
1817 query.value(7).toString(),
1818 Message::Flags{query.value(3).toInt()});
1819 msg.setMsgId(query.value(0).toLongLong());
1827 QList<Message> PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit)
1829 QList<Message> messagelist;
1831 // requestBuffers uses it's own transaction.
1832 QHash<BufferId, BufferInfo> bufferInfoHash;
1833 foreach (BufferInfo bufferInfo, requestBuffers(user)) {
1834 bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
1837 QSqlDatabase db = logDb();
1838 if (!beginReadOnlyTransaction(db)) {
1839 qWarning() << "PostgreSqlStorage::requestAllMsgs(): cannot start read only transaction!";
1840 qWarning() << " -" << qPrintable(db.lastError().text());
1844 QSqlQuery query(db);
1846 query.prepare(queryString("select_messagesAllNew"));
1849 query.prepare(queryString("select_messagesAll"));
1850 query.bindValue(":lastmsg", last.toQint64());
1852 query.bindValue(":userid", user.toInt());
1853 query.bindValue(":firstmsg", first.toQint64());
1855 if (!watchQuery(query)) {
1860 QDateTime timestamp;
1861 for (int i = 0; i < limit && query.next(); i++) {
1862 // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed
1863 // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
1864 timestamp = query.value(2).toDateTime();
1865 timestamp.setTimeSpec(Qt::UTC);
1866 Message msg(timestamp,
1867 bufferInfoHash[query.value(1).toInt()],
1868 (Message::Type)query.value(3).toInt(),
1869 query.value(9).toString(),
1870 query.value(5).toString(),
1871 query.value(6).toString(),
1872 query.value(7).toString(),
1873 query.value(8).toString(),
1874 (Message::Flags)query.value(4).toInt());
1875 msg.setMsgId(query.value(0).toLongLong());
1883 QList<Message> PostgreSqlStorage::requestAllMsgsFiltered(
1884 UserId user, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags)
1886 QList<Message> messagelist;
1888 // requestBuffers uses it's own transaction.
1889 QHash<BufferId, BufferInfo> bufferInfoHash;
1890 foreach (BufferInfo bufferInfo, requestBuffers(user)) {
1891 bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
1894 QSqlDatabase db = logDb();
1895 if (!beginReadOnlyTransaction(db)) {
1896 qWarning() << "PostgreSqlStorage::requestAllMsgs(): cannot start read only transaction!";
1897 qWarning() << " -" << qPrintable(db.lastError().text());
1901 QSqlQuery query(db);
1903 query.prepare(queryString("select_messagesAllNew_filtered"));
1906 query.prepare(queryString("select_messagesAll_filtered"));
1907 query.bindValue(":lastmsg", last.toQint64());
1909 query.bindValue(":userid", user.toInt());
1910 query.bindValue(":firstmsg", first.toQint64());
1913 query.bindValue(":type", typeRaw);
1915 int flagsRaw = flags;
1916 query.bindValue(":flags", flagsRaw);
1919 if (!watchQuery(query)) {
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());
1947 QMap<UserId, QString> PostgreSqlStorage::getAllAuthUserNames()
1949 QMap<UserId, QString> authusernames;
1950 QSqlQuery query(logDb());
1951 query.prepare(queryString("select_all_authusernames"));
1955 while (query.next()) {
1956 authusernames[query.value(0).toInt()] = query.value(1).toString();
1958 return authusernames;
1961 // void PostgreSqlStorage::safeExec(QSqlQuery &query) {
1962 // qDebug() << "PostgreSqlStorage::safeExec";
1963 // qDebug() << " executing:\n" << query.executedQuery();
1964 // qDebug() << " bound Values:";
1965 // QList<QVariant> list = query.boundValues().values();
1966 // for (int i = 0; i < list.size(); ++i)
1967 // qCritical() << i << ": " << list.at(i).toString().toLatin1().data();
1971 // qDebug() << "Success:" << !query.lastError().isValid();
1974 // if(!query.lastError().isValid())
1977 // qDebug() << "==================== ERROR ====================";
1978 // watchQuery(query);
1979 // qDebug() << "===============================================";
1984 bool PostgreSqlStorage::beginTransaction(QSqlDatabase& db)
1986 bool result = db.transaction();
1989 result = db.transaction();
1994 bool PostgreSqlStorage::beginReadOnlyTransaction(QSqlDatabase& db)
1996 QSqlQuery query = db.exec("BEGIN TRANSACTION READ ONLY");
1999 query = db.exec("BEGIN TRANSACTION READ ONLY");
2001 return !query.lastError().isValid();
2004 QSqlQuery PostgreSqlStorage::prepareAndExecuteQuery(const QString& queryname, const QString& paramstring, QSqlDatabase& db)
2006 // Query preparing is done lazily. That means that instead of always checking if the query is already prepared
2007 // we just EXECUTE and catch the error
2010 db.exec("SAVEPOINT quassel_prepare_query");
2011 if (paramstring.isNull()) {
2012 query = db.exec(QString("EXECUTE quassel_%1").arg(queryname));
2015 query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring));
2018 if (!db.isOpen() || db.lastError().isValid()) {
2019 // If the query failed because the DB connection was down, reopen the connection and start a new transaction.
2022 if (!beginTransaction(db)) {
2024 << "PostgreSqlStorage::prepareAndExecuteQuery(): cannot start transaction while recovering from connection loss!";
2025 qWarning() << " -" << qPrintable(db.lastError().text());
2028 db.exec("SAVEPOINT quassel_prepare_query");
2031 db.exec("ROLLBACK TO SAVEPOINT quassel_prepare_query");
2034 // and once again: Qt leaves us without error codes so we either parse (language dependent(!)) strings
2035 // or we just guess the error. As we're only interested in unprepared queries, this will be our guess. :)
2036 QSqlQuery checkQuery = db.exec(
2037 QString("SELECT count(name) FROM pg_prepared_statements WHERE name = 'quassel_%1' AND from_sql = TRUE").arg(queryname.toLower()));
2039 if (checkQuery.value(0).toInt() == 0) {
2040 db.exec(QString("PREPARE quassel_%1 AS %2").arg(queryname).arg(queryString(queryname)));
2041 if (db.lastError().isValid()) {
2042 qWarning() << "PostgreSqlStorage::prepareQuery(): unable to prepare query:" << queryname << "AS" << queryString(queryname);
2043 qWarning() << " Error:" << db.lastError().text();
2044 return QSqlQuery(db);
2047 // we always execute the query again, even if the query was already prepared.
2048 // this ensures, that the error is properly propagated to the calling function
2049 // (otherwise the last call would be the testing select to pg_prepared_statements
2050 // which always gives a proper result and the error would be lost)
2051 if (paramstring.isNull()) {
2052 query = db.exec(QString("EXECUTE quassel_%1").arg(queryname));
2055 query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring));
2059 // only release the SAVEPOINT
2060 db.exec("RELEASE SAVEPOINT quassel_prepare_query");
2065 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString& queryname, const QVariantList& params, QSqlDatabase& db)
2067 QSqlDriver* driver = db.driver();
2069 QStringList paramStrings;
2071 for (int i = 0; i < params.count(); i++) {
2072 const QVariant& value = params.at(i);
2073 field.setType(value.type());
2077 field.setValue(value);
2079 paramStrings << driver->formatValue(field);
2082 if (params.isEmpty()) {
2083 return prepareAndExecuteQuery(queryname, db);
2086 return prepareAndExecuteQuery(queryname, paramStrings.join(", "), db);
2090 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString& queryname, const QVariant& param, QSqlDatabase& db)
2093 field.setType(param.type());
2097 field.setValue(param);
2099 QString paramString = db.driver()->formatValue(field);
2100 return prepareAndExecuteQuery(queryname, paramString, db);
2103 void PostgreSqlStorage::deallocateQuery(const QString& queryname, const QSqlDatabase& db)
2105 db.exec(QString("DEALLOCATE quassel_%1").arg(queryname));
2108 void PostgreSqlStorage::safeExec(QSqlQuery& query)
2110 // If the query fails due to the connection being gone, it seems to cause
2111 // exec() to return false but no lastError to be set
2112 if (!query.exec() && !query.lastError().isValid()) {
2113 QSqlDatabase db = logDb();
2114 QSqlQuery retryQuery(db);
2115 retryQuery.prepare(query.lastQuery());
2116 QMapIterator<QString, QVariant> i(query.boundValues());
2117 while (i.hasNext()) {
2119 retryQuery.bindValue(i.key(), i.value());
2126 // ========================================
2127 // PostgreSqlMigrationWriter
2128 // ========================================
2129 PostgreSqlMigrationWriter::PostgreSqlMigrationWriter()
2130 : PostgreSqlStorage()
2133 bool PostgreSqlMigrationWriter::prepareQuery(MigrationObject mo)
2138 query = queryString("migrate_write_quasseluser");
2141 query = queryString("migrate_write_sender");
2144 _validIdentities.clear();
2145 query = queryString("migrate_write_identity");
2148 query = queryString("migrate_write_identity_nick");
2151 query = queryString("migrate_write_network");
2154 query = queryString("migrate_write_buffer");
2157 query = queryString("migrate_write_backlog");
2160 query = queryString("migrate_write_ircserver");
2163 query = queryString("migrate_write_usersetting");
2166 query = queryString("migrate_write_corestate");
2169 newQuery(query, logDb());
2173 // bool PostgreSqlMigrationWriter::writeUser(const QuasselUserMO &user) {
2174 bool PostgreSqlMigrationWriter::writeMo(const QuasselUserMO& user)
2176 bindValue(0, user.id.toInt());
2177 bindValue(1, user.username);
2178 bindValue(2, user.password);
2179 bindValue(3, user.hashversion);
2180 bindValue(4, user.authenticator);
2184 // bool PostgreSqlMigrationWriter::writeSender(const SenderMO &sender) {
2185 bool PostgreSqlMigrationWriter::writeMo(const SenderMO& sender)
2187 bindValue(0, sender.senderId);
2188 bindValue(1, sender.sender);
2189 bindValue(2, sender.realname);
2190 bindValue(3, sender.avatarurl);
2194 // bool PostgreSqlMigrationWriter::writeIdentity(const IdentityMO &identity) {
2195 bool PostgreSqlMigrationWriter::writeMo(const IdentityMO& identity)
2197 _validIdentities << identity.id.toInt();
2198 bindValue(0, identity.id.toInt());
2199 bindValue(1, identity.userid.toInt());
2200 bindValue(2, identity.identityname);
2201 bindValue(3, identity.realname);
2202 bindValue(4, identity.awayNick);
2203 bindValue(5, identity.awayNickEnabled);
2204 bindValue(6, identity.awayReason);
2205 bindValue(7, identity.awayReasonEnabled);
2206 bindValue(8, identity.autoAwayEnabled);
2207 bindValue(9, identity.autoAwayTime);
2208 bindValue(10, identity.autoAwayReason);
2209 bindValue(11, identity.autoAwayReasonEnabled);
2210 bindValue(12, identity.detachAwayEnabled);
2211 bindValue(13, identity.detachAwayReason);
2212 bindValue(14, identity.detachAwayReasonEnabled);
2213 bindValue(15, identity.ident);
2214 bindValue(16, identity.kickReason);
2215 bindValue(17, identity.partReason);
2216 bindValue(18, identity.quitReason);
2217 bindValue(19, identity.sslCert);
2218 bindValue(20, identity.sslKey);
2222 // bool PostgreSqlMigrationWriter::writeIdentityNick(const IdentityNickMO &identityNick) {
2223 bool PostgreSqlMigrationWriter::writeMo(const IdentityNickMO& identityNick)
2225 bindValue(0, identityNick.nickid);
2226 bindValue(1, identityNick.identityId.toInt());
2227 bindValue(2, identityNick.nick);
2231 // bool PostgreSqlMigrationWriter::writeNetwork(const NetworkMO &network) {
2232 bool PostgreSqlMigrationWriter::writeMo(const NetworkMO& network)
2234 bindValue(0, network.networkid.toInt());
2235 bindValue(1, network.userid.toInt());
2236 bindValue(2, network.networkname);
2237 if (_validIdentities.contains(network.identityid.toInt()))
2238 bindValue(3, network.identityid.toInt());
2240 bindValue(3, QVariant());
2241 bindValue(4, network.encodingcodec);
2242 bindValue(5, network.decodingcodec);
2243 bindValue(6, network.servercodec);
2244 bindValue(7, network.userandomserver);
2245 bindValue(8, network.perform);
2246 bindValue(9, network.useautoidentify);
2247 bindValue(10, network.autoidentifyservice);
2248 bindValue(11, network.autoidentifypassword);
2249 bindValue(12, network.useautoreconnect);
2250 bindValue(13, network.autoreconnectinterval);
2251 bindValue(14, network.autoreconnectretries);
2252 bindValue(15, network.unlimitedconnectretries);
2253 bindValue(16, network.rejoinchannels);
2254 bindValue(17, network.connected);
2255 bindValue(18, network.usermode);
2256 bindValue(19, network.awaymessage);
2257 bindValue(20, network.attachperform);
2258 bindValue(21, network.detachperform);
2259 bindValue(22, network.usesasl);
2260 bindValue(23, network.saslaccount);
2261 bindValue(24, network.saslpassword);
2262 // Custom rate limiting
2263 bindValue(25, network.usecustommessagerate);
2264 bindValue(26, network.messagerateburstsize);
2265 bindValue(27, network.messageratedelay);
2266 bindValue(28, network.unlimitedmessagerate);
2270 // bool PostgreSqlMigrationWriter::writeBuffer(const BufferMO &buffer) {
2271 bool PostgreSqlMigrationWriter::writeMo(const BufferMO& buffer)
2273 bindValue(0, buffer.bufferid.toInt());
2274 bindValue(1, buffer.userid.toInt());
2275 bindValue(2, buffer.groupid);
2276 bindValue(3, buffer.networkid.toInt());
2277 bindValue(4, buffer.buffername);
2278 bindValue(5, buffer.buffercname);
2279 bindValue(6, (int)buffer.buffertype);
2280 bindValue(7, buffer.lastmsgid);
2281 bindValue(8, buffer.lastseenmsgid);
2282 bindValue(9, buffer.markerlinemsgid);
2283 bindValue(10, buffer.bufferactivity);
2284 bindValue(11, buffer.highlightcount);
2285 bindValue(12, buffer.key);
2286 bindValue(13, buffer.joined);
2287 bindValue(14, buffer.cipher);
2291 // bool PostgreSqlMigrationWriter::writeBacklog(const BacklogMO &backlog) {
2292 bool PostgreSqlMigrationWriter::writeMo(const BacklogMO& backlog)
2294 bindValue(0, backlog.messageid.toQint64());
2295 bindValue(1, backlog.time);
2296 bindValue(2, backlog.bufferid.toInt());
2297 bindValue(3, backlog.type);
2298 bindValue(4, (int)backlog.flags);
2299 bindValue(5, backlog.senderid);
2300 bindValue(6, backlog.senderprefixes);
2301 bindValue(7, backlog.message);
2305 // bool PostgreSqlMigrationWriter::writeIrcServer(const IrcServerMO &ircserver) {
2306 bool PostgreSqlMigrationWriter::writeMo(const IrcServerMO& ircserver)
2308 bindValue(0, ircserver.serverid);
2309 bindValue(1, ircserver.userid.toInt());
2310 bindValue(2, ircserver.networkid.toInt());
2311 bindValue(3, ircserver.hostname);
2312 bindValue(4, ircserver.port);
2313 bindValue(5, ircserver.password);
2314 bindValue(6, ircserver.ssl);
2315 bindValue(7, ircserver.sslversion);
2316 bindValue(8, ircserver.useproxy);
2317 bindValue(9, ircserver.proxytype);
2318 bindValue(10, ircserver.proxyhost);
2319 bindValue(11, ircserver.proxyport);
2320 bindValue(12, ircserver.proxyuser);
2321 bindValue(13, ircserver.proxypass);
2322 bindValue(14, ircserver.sslverify);
2326 // bool PostgreSqlMigrationWriter::writeUserSetting(const UserSettingMO &userSetting) {
2327 bool PostgreSqlMigrationWriter::writeMo(const UserSettingMO& userSetting)
2329 bindValue(0, userSetting.userid.toInt());
2330 bindValue(1, userSetting.settingname);
2331 bindValue(2, userSetting.settingvalue);
2335 bool PostgreSqlMigrationWriter::writeMo(const CoreStateMO& coreState)
2337 bindValue(0, coreState.key);
2338 bindValue(1, coreState.value);
2342 bool PostgreSqlMigrationWriter::postProcess()
2344 QSqlDatabase db = logDb();
2345 QList<Sequence> sequences;
2346 sequences << Sequence("backlog", "messageid") << Sequence("buffer", "bufferid") << Sequence("identity", "identityid")
2347 << Sequence("identity_nick", "nickid") << Sequence("ircserver", "serverid") << Sequence("network", "networkid")
2348 << Sequence("quasseluser", "userid") << Sequence("sender", "senderid");
2349 QList<Sequence>::const_iterator iter;
2350 for (iter = sequences.constBegin(); iter != sequences.constEnd(); ++iter) {
2352 newQuery(QString("SELECT setval('%1_%2_seq', max(%2)) FROM %1").arg(iter->table, iter->field), db);
2357 // Update the lastmsgid for all existing buffers.
2359 newQuery(QString("SELECT populate_lastmsgid()"), db);