1 /***************************************************************************
2 * Copyright (C) 2005-2014 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"
29 PostgreSqlStorage::PostgreSqlStorage(QObject *parent)
30 : AbstractSqlStorage(parent),
36 PostgreSqlStorage::~PostgreSqlStorage()
41 AbstractSqlMigrationWriter *PostgreSqlStorage::createMigrationWriter()
43 PostgreSqlMigrationWriter *writer = new PostgreSqlMigrationWriter();
44 QVariantMap properties;
45 properties["Username"] = _userName;
46 properties["Password"] = _password;
47 properties["Hostname"] = _hostName;
48 properties["Port"] = _port;
49 properties["Database"] = _databaseName;
50 writer->setConnectionProperties(properties);
55 bool PostgreSqlStorage::isAvailable() const
57 qDebug() << QSqlDatabase::drivers();
58 if (!QSqlDatabase::isDriverAvailable("QPSQL")) return false;
63 QString PostgreSqlStorage::displayName() const
65 return QString("PostgreSQL");
69 QString PostgreSqlStorage::description() const
71 // FIXME: proper description
72 return tr("PostgreSQL Turbo Bomber HD!");
76 QStringList PostgreSqlStorage::setupKeys() const
88 QVariantMap PostgreSqlStorage::setupDefaults() const
91 map["Username"] = QVariant(QString("quassel"));
92 map["Hostname"] = QVariant(QString("localhost"));
93 map["Port"] = QVariant(5432);
94 map["Database"] = QVariant(QString("quassel"));
99 bool PostgreSqlStorage::initDbSession(QSqlDatabase &db)
101 // check whether the Qt driver performs string escaping or not.
102 // i.e. test if it doubles slashes.
104 testField.setType(QVariant::String);
105 testField.setValue("\\");
106 QString formattedString = db.driver()->formatValue(testField);
107 switch(formattedString.count('\\')) {
109 // yes it does... and we cannot do anything to change the behavior of Qt.
110 // If this is a legacy DB (Postgres < 8.2), then everything is already ok,
111 // as this is the expected behavior.
112 // If it is a newer version, switch to legacy mode.
114 quWarning() << "Switching Postgres to legacy mode. (set standard conforming strings to off)";
115 // If the following calls fail, it is a legacy DB anyways, so it doesn't matter
116 // and no need to check the outcome.
117 db.exec("set standard_conforming_strings = off");
118 db.exec("set escape_string_warning = off");
121 // ok, so Qt does not escape...
122 // That means we have to ensure that postgres uses standard conforming strings...
124 QSqlQuery query = db.exec("set standard_conforming_strings = on");
125 if (query.lastError().isValid()) {
126 // We cannot enable standard conforming strings...
127 // since Quassel does no escaping by itself, this would yield a major vulnerability.
128 quError() << "Failed to enable standard_conforming_strings for the Postgres db!";
134 // The slash got replaced with 0 or more than 2 slashes! o_O
135 quError() << "Your version of Qt does something _VERY_ strange to slashes in QSqlQueries! You should consult your trusted doctor!";
140 // Set the PostgreSQL session timezone to UTC, since we want timestamps stored in UTC
141 QSqlQuery tzQuery = db.exec("SET timezone = 'UTC'");
142 if (tzQuery.lastError().isValid()) {
143 quError() << "Failed to set timezone to UTC!";
151 void PostgreSqlStorage::setConnectionProperties(const QVariantMap &properties)
153 _userName = properties["Username"].toString();
154 _password = properties["Password"].toString();
155 _hostName = properties["Hostname"].toString();
156 _port = properties["Port"].toInt();
157 _databaseName = properties["Database"].toString();
161 int PostgreSqlStorage::installedSchemaVersion()
163 QSqlQuery query = logDb().exec("SELECT value FROM coreinfo WHERE key = 'schemaversion'");
165 return query.value(0).toInt();
167 // maybe it's really old... (schema version 0)
168 query = logDb().exec("SELECT MAX(version) FROM coreinfo");
170 return query.value(0).toInt();
172 return AbstractSqlStorage::installedSchemaVersion();
176 bool PostgreSqlStorage::updateSchemaVersion(int newVersion)
178 QSqlQuery query(logDb());
179 query.prepare("UPDATE coreinfo SET value = :version WHERE key = 'schemaversion'");
180 query.bindValue(":version", newVersion);
184 if (query.lastError().isValid()) {
185 qCritical() << "PostgreSqlStorage::updateSchemaVersion(int): Updating schema version failed!";
192 bool PostgreSqlStorage::setupSchemaVersion(int version)
194 QSqlQuery query(logDb());
195 query.prepare("INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)");
196 query.bindValue(":version", version);
200 if (query.lastError().isValid()) {
201 qCritical() << "PostgreSqlStorage::setupSchemaVersion(int): Updating schema version failed!";
208 UserId PostgreSqlStorage::addUser(const QString &user, const QString &password)
210 QSqlQuery query(logDb());
211 query.prepare(queryString("insert_quasseluser"));
212 query.bindValue(":username", user);
213 query.bindValue(":password", cryptedPassword(password));
215 if (!watchQuery(query))
219 UserId uid = query.value(0).toInt();
220 emit userAdded(uid, user);
225 bool PostgreSqlStorage::updateUser(UserId user, const QString &password)
227 QSqlQuery query(logDb());
228 query.prepare(queryString("update_userpassword"));
229 query.bindValue(":userid", user.toInt());
230 query.bindValue(":password", cryptedPassword(password));
232 return query.numRowsAffected() != 0;
236 void PostgreSqlStorage::renameUser(UserId user, const QString &newName)
238 QSqlQuery query(logDb());
239 query.prepare(queryString("update_username"));
240 query.bindValue(":userid", user.toInt());
241 query.bindValue(":username", newName);
243 emit userRenamed(user, newName);
247 UserId PostgreSqlStorage::validateUser(const QString &user, const QString &password)
249 QSqlQuery query(logDb());
250 query.prepare(queryString("select_authuser"));
251 query.bindValue(":username", user);
252 query.bindValue(":password", cryptedPassword(password));
256 return query.value(0).toInt();
264 UserId PostgreSqlStorage::getUserId(const QString &user)
266 QSqlQuery query(logDb());
267 query.prepare(queryString("select_userid"));
268 query.bindValue(":username", user);
272 return query.value(0).toInt();
280 UserId PostgreSqlStorage::internalUser()
282 QSqlQuery query(logDb());
283 query.prepare(queryString("select_internaluser"));
287 return query.value(0).toInt();
295 void PostgreSqlStorage::delUser(UserId user)
297 QSqlDatabase db = logDb();
298 if (!db.transaction()) {
299 qWarning() << "PostgreSqlStorage::delUser(): cannot start transaction!";
304 query.prepare(queryString("delete_quasseluser"));
305 query.bindValue(":userid", user.toInt());
307 if (!watchQuery(query)) {
313 emit userRemoved(user);
318 void PostgreSqlStorage::setUserSetting(UserId userId, const QString &settingName, const QVariant &data)
321 QDataStream out(&rawData, QIODevice::WriteOnly);
322 out.setVersion(QDataStream::Qt_4_2);
325 QSqlDatabase db = logDb();
326 QSqlQuery selectQuery(db);
327 selectQuery.prepare(queryString("select_user_setting"));
328 selectQuery.bindValue(":userid", userId.toInt());
329 selectQuery.bindValue(":settingname", settingName);
330 safeExec(selectQuery);
332 QString setQueryString;
333 if (!selectQuery.first()) {
334 setQueryString = queryString("insert_user_setting");
337 setQueryString = queryString("update_user_setting");
340 QSqlQuery setQuery(db);
341 setQuery.prepare(setQueryString);
342 setQuery.bindValue(":userid", userId.toInt());
343 setQuery.bindValue(":settingname", settingName);
344 setQuery.bindValue(":settingvalue", rawData);
349 QVariant PostgreSqlStorage::getUserSetting(UserId userId, const QString &settingName, const QVariant &defaultData)
351 QSqlQuery query(logDb());
352 query.prepare(queryString("select_user_setting"));
353 query.bindValue(":userid", userId.toInt());
354 query.bindValue(":settingname", settingName);
359 QByteArray rawData = query.value(0).toByteArray();
360 QDataStream in(&rawData, QIODevice::ReadOnly);
361 in.setVersion(QDataStream::Qt_4_2);
371 IdentityId PostgreSqlStorage::createIdentity(UserId user, CoreIdentity &identity)
373 IdentityId identityId;
375 QSqlDatabase db = logDb();
376 if (!db.transaction()) {
377 qWarning() << "PostgreSqlStorage::createIdentity(): Unable to start Transaction!";
378 qWarning() << " -" << qPrintable(db.lastError().text());
383 query.prepare(queryString("insert_identity"));
384 query.bindValue(":userid", user.toInt());
385 query.bindValue(":identityname", identity.identityName());
386 query.bindValue(":realname", identity.realName());
387 query.bindValue(":awaynick", identity.awayNick());
388 query.bindValue(":awaynickenabled", identity.awayNickEnabled());
389 query.bindValue(":awayreason", identity.awayReason());
390 query.bindValue(":awayreasonenabled", identity.awayReasonEnabled());
391 query.bindValue(":autoawayenabled", identity.awayReasonEnabled());
392 query.bindValue(":autoawaytime", identity.autoAwayTime());
393 query.bindValue(":autoawayreason", identity.autoAwayReason());
394 query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled());
395 query.bindValue(":detachawayenabled", identity.detachAwayEnabled());
396 query.bindValue(":detachawayreason", identity.detachAwayReason());
397 query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled());
398 query.bindValue(":ident", identity.ident());
399 query.bindValue(":kickreason", identity.kickReason());
400 query.bindValue(":partreason", identity.partReason());
401 query.bindValue(":quitreason", identity.quitReason());
403 query.bindValue(":sslcert", identity.sslCert().toPem());
404 query.bindValue(":sslkey", identity.sslKey().toPem());
406 query.bindValue(":sslcert", QByteArray());
407 query.bindValue(":sslkey", QByteArray());
410 if (query.lastError().isValid()) {
417 identityId = query.value(0).toInt();
418 identity.setId(identityId);
420 if (!identityId.isValid()) {
426 QSqlQuery insertNickQuery(db);
427 insertNickQuery.prepare(queryString("insert_nick"));
428 foreach(QString nick, identity.nicks()) {
429 insertNickQuery.bindValue(":identityid", identityId.toInt());
430 insertNickQuery.bindValue(":nick", nick);
431 safeExec(insertNickQuery);
432 if (!watchQuery(insertNickQuery)) {
439 qWarning() << "PostgreSqlStorage::createIdentity(): committing data failed!";
440 qWarning() << " -" << qPrintable(db.lastError().text());
447 bool PostgreSqlStorage::updateIdentity(UserId user, const CoreIdentity &identity)
449 QSqlDatabase db = logDb();
450 if (!db.transaction()) {
451 qWarning() << "PostgreSqlStorage::updateIdentity(): Unable to start Transaction!";
452 qWarning() << " -" << qPrintable(db.lastError().text());
456 QSqlQuery checkQuery(db);
457 checkQuery.prepare(queryString("select_checkidentity"));
458 checkQuery.bindValue(":identityid", identity.id().toInt());
459 checkQuery.bindValue(":userid", user.toInt());
460 safeExec(checkQuery);
462 // there should be exactly one identity for the given id and user
463 if (!checkQuery.first() || checkQuery.value(0).toInt() != 1) {
469 query.prepare(queryString("update_identity"));
470 query.bindValue(":identityname", identity.identityName());
471 query.bindValue(":realname", identity.realName());
472 query.bindValue(":awaynick", identity.awayNick());
473 query.bindValue(":awaynickenabled", identity.awayNickEnabled());
474 query.bindValue(":awayreason", identity.awayReason());
475 query.bindValue(":awayreasonenabled", identity.awayReasonEnabled());
476 query.bindValue(":autoawayenabled", identity.awayReasonEnabled());
477 query.bindValue(":autoawaytime", identity.autoAwayTime());
478 query.bindValue(":autoawayreason", identity.autoAwayReason());
479 query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled());
480 query.bindValue(":detachawayenabled", identity.detachAwayEnabled());
481 query.bindValue(":detachawayreason", identity.detachAwayReason());
482 query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled());
483 query.bindValue(":ident", identity.ident());
484 query.bindValue(":kickreason", identity.kickReason());
485 query.bindValue(":partreason", identity.partReason());
486 query.bindValue(":quitreason", identity.quitReason());
488 query.bindValue(":sslcert", identity.sslCert().toPem());
489 query.bindValue(":sslkey", identity.sslKey().toPem());
491 query.bindValue(":sslcert", QByteArray());
492 query.bindValue(":sslkey", QByteArray());
494 query.bindValue(":identityid", identity.id().toInt());
497 if (!watchQuery(query)) {
502 QSqlQuery deleteNickQuery(db);
503 deleteNickQuery.prepare(queryString("delete_nicks"));
504 deleteNickQuery.bindValue(":identityid", identity.id().toInt());
505 safeExec(deleteNickQuery);
506 if (!watchQuery(deleteNickQuery)) {
511 QSqlQuery insertNickQuery(db);
512 insertNickQuery.prepare(queryString("insert_nick"));
513 foreach(QString nick, identity.nicks()) {
514 insertNickQuery.bindValue(":identityid", identity.id().toInt());
515 insertNickQuery.bindValue(":nick", nick);
516 safeExec(insertNickQuery);
517 if (!watchQuery(insertNickQuery)) {
524 qWarning() << "PostgreSqlStorage::updateIdentity(): committing data failed!";
525 qWarning() << " -" << qPrintable(db.lastError().text());
532 void PostgreSqlStorage::removeIdentity(UserId user, IdentityId identityId)
534 QSqlDatabase db = logDb();
535 if (!db.transaction()) {
536 qWarning() << "PostgreSqlStorage::removeIdentity(): Unable to start Transaction!";
537 qWarning() << " -" << qPrintable(db.lastError().text());
542 query.prepare(queryString("delete_identity"));
543 query.bindValue(":identityid", identityId.toInt());
544 query.bindValue(":userid", user.toInt());
546 if (!watchQuery(query)) {
555 QList<CoreIdentity> PostgreSqlStorage::identities(UserId user)
557 QList<CoreIdentity> identities;
559 QSqlDatabase db = logDb();
560 if (!beginReadOnlyTransaction(db)) {
561 qWarning() << "PostgreSqlStorage::identites(): cannot start read only transaction!";
562 qWarning() << " -" << qPrintable(db.lastError().text());
567 query.prepare(queryString("select_identities"));
568 query.bindValue(":userid", user.toInt());
570 QSqlQuery nickQuery(db);
571 nickQuery.prepare(queryString("select_nicks"));
575 while (query.next()) {
576 CoreIdentity identity(IdentityId(query.value(0).toInt()));
578 identity.setIdentityName(query.value(1).toString());
579 identity.setRealName(query.value(2).toString());
580 identity.setAwayNick(query.value(3).toString());
581 identity.setAwayNickEnabled(!!query.value(4).toInt());
582 identity.setAwayReason(query.value(5).toString());
583 identity.setAwayReasonEnabled(!!query.value(6).toInt());
584 identity.setAutoAwayEnabled(!!query.value(7).toInt());
585 identity.setAutoAwayTime(query.value(8).toInt());
586 identity.setAutoAwayReason(query.value(9).toString());
587 identity.setAutoAwayReasonEnabled(!!query.value(10).toInt());
588 identity.setDetachAwayEnabled(!!query.value(11).toInt());
589 identity.setDetachAwayReason(query.value(12).toString());
590 identity.setDetachAwayReasonEnabled(!!query.value(13).toInt());
591 identity.setIdent(query.value(14).toString());
592 identity.setKickReason(query.value(15).toString());
593 identity.setPartReason(query.value(16).toString());
594 identity.setQuitReason(query.value(17).toString());
596 identity.setSslCert(query.value(18).toByteArray());
597 identity.setSslKey(query.value(19).toByteArray());
600 nickQuery.bindValue(":identityid", identity.id().toInt());
601 QList<QString> nicks;
603 watchQuery(nickQuery);
604 while (nickQuery.next()) {
605 nicks << nickQuery.value(0).toString();
607 identity.setNicks(nicks);
608 identities << identity;
615 NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info)
619 QSqlDatabase db = logDb();
620 if (!db.transaction()) {
621 qWarning() << "PostgreSqlStorage::createNetwork(): failed to begin transaction!";
622 qWarning() << " -" << qPrintable(db.lastError().text());
627 query.prepare(queryString("insert_network"));
628 query.bindValue(":userid", user.toInt());
629 bindNetworkInfo(query, info);
631 if (query.lastError().isValid()) {
638 networkId = query.value(0).toInt();
640 if (!networkId.isValid()) {
646 QSqlQuery insertServersQuery(db);
647 insertServersQuery.prepare(queryString("insert_server"));
648 foreach(Network::Server server, info.serverList) {
649 insertServersQuery.bindValue(":userid", user.toInt());
650 insertServersQuery.bindValue(":networkid", networkId.toInt());
651 bindServerInfo(insertServersQuery, server);
652 safeExec(insertServersQuery);
653 if (!watchQuery(insertServersQuery)) {
660 qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!";
661 qWarning() << " -" << qPrintable(db.lastError().text());
668 void PostgreSqlStorage::bindNetworkInfo(QSqlQuery &query, const NetworkInfo &info)
670 query.bindValue(":networkname", info.networkName);
671 query.bindValue(":identityid", info.identity.isValid() ? info.identity.toInt() : QVariant());
672 query.bindValue(":encodingcodec", QString(info.codecForEncoding));
673 query.bindValue(":decodingcodec", QString(info.codecForDecoding));
674 query.bindValue(":servercodec", QString(info.codecForServer));
675 query.bindValue(":userandomserver", info.useRandomServer);
676 query.bindValue(":perform", info.perform.join("\n"));
677 query.bindValue(":useautoidentify", info.useAutoIdentify);
678 query.bindValue(":autoidentifyservice", info.autoIdentifyService);
679 query.bindValue(":autoidentifypassword", info.autoIdentifyPassword);
680 query.bindValue(":usesasl", info.useSasl);
681 query.bindValue(":saslaccount", info.saslAccount);
682 query.bindValue(":saslpassword", info.saslPassword);
683 query.bindValue(":useautoreconnect", info.useAutoReconnect);
684 query.bindValue(":autoreconnectinterval", info.autoReconnectInterval);
685 query.bindValue(":autoreconnectretries", info.autoReconnectRetries);
686 query.bindValue(":unlimitedconnectretries", info.unlimitedReconnectRetries);
687 query.bindValue(":rejoinchannels", info.rejoinChannels);
688 if (info.networkId.isValid())
689 query.bindValue(":networkid", info.networkId.toInt());
693 void PostgreSqlStorage::bindServerInfo(QSqlQuery &query, const Network::Server &server)
695 query.bindValue(":hostname", server.host);
696 query.bindValue(":port", server.port);
697 query.bindValue(":password", server.password);
698 query.bindValue(":ssl", server.useSsl);
699 query.bindValue(":sslversion", server.sslVersion);
700 query.bindValue(":useproxy", server.useProxy);
701 query.bindValue(":proxytype", server.proxyType);
702 query.bindValue(":proxyhost", server.proxyHost);
703 query.bindValue(":proxyport", server.proxyPort);
704 query.bindValue(":proxyuser", server.proxyUser);
705 query.bindValue(":proxypass", server.proxyPass);
709 bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo &info)
711 QSqlDatabase db = logDb();
712 if (!db.transaction()) {
713 qWarning() << "PostgreSqlStorage::updateNetwork(): failed to begin transaction!";
714 qWarning() << " -" << qPrintable(db.lastError().text());
718 QSqlQuery updateQuery(db);
719 updateQuery.prepare(queryString("update_network"));
720 updateQuery.bindValue(":userid", user.toInt());
721 bindNetworkInfo(updateQuery, info);
722 safeExec(updateQuery);
723 if (!watchQuery(updateQuery)) {
727 if (updateQuery.numRowsAffected() != 1) {
728 // seems this is not our network...
733 QSqlQuery dropServersQuery(db);
734 dropServersQuery.prepare("DELETE FROM ircserver WHERE networkid = :networkid");
735 dropServersQuery.bindValue(":networkid", info.networkId.toInt());
736 safeExec(dropServersQuery);
737 if (!watchQuery(dropServersQuery)) {
742 QSqlQuery insertServersQuery(db);
743 insertServersQuery.prepare(queryString("insert_server"));
744 foreach(Network::Server server, info.serverList) {
745 insertServersQuery.bindValue(":userid", user.toInt());
746 insertServersQuery.bindValue(":networkid", info.networkId.toInt());
747 bindServerInfo(insertServersQuery, server);
748 safeExec(insertServersQuery);
749 if (!watchQuery(insertServersQuery)) {
756 qWarning() << "PostgreSqlStorage::updateNetwork(): committing data failed!";
757 qWarning() << " -" << qPrintable(db.lastError().text());
764 bool PostgreSqlStorage::removeNetwork(UserId user, const NetworkId &networkId)
766 QSqlDatabase db = logDb();
767 if (!db.transaction()) {
768 qWarning() << "PostgreSqlStorage::removeNetwork(): cannot start transaction!";
769 qWarning() << " -" << qPrintable(db.lastError().text());
774 query.prepare(queryString("delete_network"));
775 query.bindValue(":userid", user.toInt());
776 query.bindValue(":networkid", networkId.toInt());
778 if (!watchQuery(query)) {
788 QList<NetworkInfo> PostgreSqlStorage::networks(UserId user)
790 QList<NetworkInfo> nets;
792 QSqlDatabase db = logDb();
793 if (!beginReadOnlyTransaction(db)) {
794 qWarning() << "PostgreSqlStorage::networks(): cannot start read only transaction!";
795 qWarning() << " -" << qPrintable(db.lastError().text());
799 QSqlQuery networksQuery(db);
800 networksQuery.prepare(queryString("select_networks_for_user"));
801 networksQuery.bindValue(":userid", user.toInt());
803 QSqlQuery serversQuery(db);
804 serversQuery.prepare(queryString("select_servers_for_network"));
806 safeExec(networksQuery);
807 if (!watchQuery(networksQuery)) {
812 while (networksQuery.next()) {
814 net.networkId = networksQuery.value(0).toInt();
815 net.networkName = networksQuery.value(1).toString();
816 net.identity = networksQuery.value(2).toInt();
817 net.codecForServer = networksQuery.value(3).toString().toLatin1();
818 net.codecForEncoding = networksQuery.value(4).toString().toLatin1();
819 net.codecForDecoding = networksQuery.value(5).toString().toLatin1();
820 net.useRandomServer = networksQuery.value(6).toBool();
821 net.perform = networksQuery.value(7).toString().split("\n");
822 net.useAutoIdentify = networksQuery.value(8).toBool();
823 net.autoIdentifyService = networksQuery.value(9).toString();
824 net.autoIdentifyPassword = networksQuery.value(10).toString();
825 net.useAutoReconnect = networksQuery.value(11).toBool();
826 net.autoReconnectInterval = networksQuery.value(12).toUInt();
827 net.autoReconnectRetries = networksQuery.value(13).toInt();
828 net.unlimitedReconnectRetries = networksQuery.value(14).toBool();
829 net.rejoinChannels = networksQuery.value(15).toBool();
830 net.useSasl = networksQuery.value(16).toBool();
831 net.saslAccount = networksQuery.value(17).toString();
832 net.saslPassword = networksQuery.value(18).toString();
834 serversQuery.bindValue(":networkid", net.networkId.toInt());
835 safeExec(serversQuery);
836 if (!watchQuery(serversQuery)) {
841 Network::ServerList servers;
842 while (serversQuery.next()) {
843 Network::Server server;
844 server.host = serversQuery.value(0).toString();
845 server.port = serversQuery.value(1).toUInt();
846 server.password = serversQuery.value(2).toString();
847 server.useSsl = serversQuery.value(3).toBool();
848 server.sslVersion = serversQuery.value(4).toInt();
849 server.useProxy = serversQuery.value(5).toBool();
850 server.proxyType = serversQuery.value(6).toInt();
851 server.proxyHost = serversQuery.value(7).toString();
852 server.proxyPort = serversQuery.value(8).toUInt();
853 server.proxyUser = serversQuery.value(9).toString();
854 server.proxyPass = serversQuery.value(10).toString();
857 net.serverList = servers;
865 QList<NetworkId> PostgreSqlStorage::connectedNetworks(UserId user)
867 QList<NetworkId> connectedNets;
869 QSqlDatabase db = logDb();
870 if (!beginReadOnlyTransaction(db)) {
871 qWarning() << "PostgreSqlStorage::connectedNetworks(): cannot start read only transaction!";
872 qWarning() << " -" << qPrintable(db.lastError().text());
873 return connectedNets;
877 query.prepare(queryString("select_connected_networks"));
878 query.bindValue(":userid", user.toInt());
882 while (query.next()) {
883 connectedNets << query.value(0).toInt();
887 return connectedNets;
891 void PostgreSqlStorage::setNetworkConnected(UserId user, const NetworkId &networkId, bool isConnected)
893 QSqlQuery query(logDb());
894 query.prepare(queryString("update_network_connected"));
895 query.bindValue(":userid", user.toInt());
896 query.bindValue(":networkid", networkId.toInt());
897 query.bindValue(":connected", isConnected);
903 QHash<QString, QString> PostgreSqlStorage::persistentChannels(UserId user, const NetworkId &networkId)
905 QHash<QString, QString> persistentChans;
907 QSqlDatabase db = logDb();
908 if (!beginReadOnlyTransaction(db)) {
909 qWarning() << "PostgreSqlStorage::persistentChannels(): cannot start read only transaction!";
910 qWarning() << " -" << qPrintable(db.lastError().text());
911 return persistentChans;
915 query.prepare(queryString("select_persistent_channels"));
916 query.bindValue(":userid", user.toInt());
917 query.bindValue(":networkid", networkId.toInt());
921 while (query.next()) {
922 persistentChans[query.value(0).toString()] = query.value(1).toString();
926 return persistentChans;
930 void PostgreSqlStorage::setChannelPersistent(UserId user, const NetworkId &networkId, const QString &channel, bool isJoined)
932 QSqlQuery query(logDb());
933 query.prepare(queryString("update_buffer_persistent_channel"));
934 query.bindValue(":userid", user.toInt());
935 query.bindValue(":networkId", networkId.toInt());
936 query.bindValue(":buffercname", channel.toLower());
937 query.bindValue(":joined", isJoined);
943 void PostgreSqlStorage::setPersistentChannelKey(UserId user, const NetworkId &networkId, const QString &channel, const QString &key)
945 QSqlQuery query(logDb());
946 query.prepare(queryString("update_buffer_set_channel_key"));
947 query.bindValue(":userid", user.toInt());
948 query.bindValue(":networkId", networkId.toInt());
949 query.bindValue(":buffercname", channel.toLower());
950 query.bindValue(":key", key);
956 QString PostgreSqlStorage::awayMessage(UserId user, NetworkId networkId)
958 QSqlQuery query(logDb());
959 query.prepare(queryString("select_network_awaymsg"));
960 query.bindValue(":userid", user.toInt());
961 query.bindValue(":networkid", networkId.toInt());
966 awayMsg = query.value(0).toString();
971 void PostgreSqlStorage::setAwayMessage(UserId user, NetworkId networkId, const QString &awayMsg)
973 QSqlQuery query(logDb());
974 query.prepare(queryString("update_network_set_awaymsg"));
975 query.bindValue(":userid", user.toInt());
976 query.bindValue(":networkid", networkId.toInt());
977 query.bindValue(":awaymsg", awayMsg);
983 QString PostgreSqlStorage::userModes(UserId user, NetworkId networkId)
985 QSqlQuery query(logDb());
986 query.prepare(queryString("select_network_usermode"));
987 query.bindValue(":userid", user.toInt());
988 query.bindValue(":networkid", networkId.toInt());
993 modes = query.value(0).toString();
998 void PostgreSqlStorage::setUserModes(UserId user, NetworkId networkId, const QString &userModes)
1000 QSqlQuery query(logDb());
1001 query.prepare(queryString("update_network_set_usermode"));
1002 query.bindValue(":userid", user.toInt());
1003 query.bindValue(":networkid", networkId.toInt());
1004 query.bindValue(":usermode", userModes);
1010 BufferInfo PostgreSqlStorage::bufferInfo(UserId user, const NetworkId &networkId, BufferInfo::Type type, const QString &buffer, bool create)
1012 QSqlDatabase db = logDb();
1013 if (!db.transaction()) {
1014 qWarning() << "PostgreSqlStorage::bufferInfo(): cannot start read only transaction!";
1015 qWarning() << " -" << qPrintable(db.lastError().text());
1016 return BufferInfo();
1019 QSqlQuery query(db);
1020 query.prepare(queryString("select_bufferByName"));
1021 query.bindValue(":networkid", networkId.toInt());
1022 query.bindValue(":userid", user.toInt());
1023 query.bindValue(":buffercname", buffer.toLower());
1026 if (query.first()) {
1027 BufferInfo bufferInfo = BufferInfo(query.value(0).toInt(), networkId, (BufferInfo::Type)query.value(1).toInt(), 0, buffer);
1029 qCritical() << "PostgreSqlStorage::bufferInfo(): received more then one Buffer!";
1030 qCritical() << " Query:" << query.lastQuery();
1031 qCritical() << " bound Values:";
1032 QList<QVariant> list = query.boundValues().values();
1033 for (int i = 0; i < list.size(); ++i)
1034 qCritical() << i << ":" << list.at(i).toString().toLatin1().data();
1043 return BufferInfo();
1046 QSqlQuery createQuery(db);
1047 createQuery.prepare(queryString("insert_buffer"));
1048 createQuery.bindValue(":userid", user.toInt());
1049 createQuery.bindValue(":networkid", networkId.toInt());
1050 createQuery.bindValue(":buffertype", (int)type);
1051 createQuery.bindValue(":buffername", buffer);
1052 createQuery.bindValue(":buffercname", buffer.toLower());
1053 createQuery.bindValue(":joined", type & BufferInfo::ChannelBuffer ? true : false);
1055 safeExec(createQuery);
1057 if (createQuery.lastError().isValid()) {
1058 qWarning() << "PostgreSqlStorage::bufferInfo(): unable to create buffer";
1059 watchQuery(createQuery);
1061 return BufferInfo();
1064 createQuery.first();
1066 BufferInfo bufferInfo = BufferInfo(createQuery.value(0).toInt(), networkId, type, 0, buffer);
1072 BufferInfo PostgreSqlStorage::getBufferInfo(UserId user, const BufferId &bufferId)
1074 QSqlQuery query(logDb());
1075 query.prepare(queryString("select_buffer_by_id"));
1076 query.bindValue(":userid", user.toInt());
1077 query.bindValue(":bufferid", bufferId.toInt());
1079 if (!watchQuery(query))
1080 return BufferInfo();
1083 return BufferInfo();
1085 BufferInfo bufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), 0, query.value(4).toString());
1086 Q_ASSERT(!query.next());
1092 QList<BufferInfo> PostgreSqlStorage::requestBuffers(UserId user)
1094 QList<BufferInfo> bufferlist;
1096 QSqlDatabase db = logDb();
1097 if (!beginReadOnlyTransaction(db)) {
1098 qWarning() << "PostgreSqlStorage::requestBuffers(): cannot start read only transaction!";
1099 qWarning() << " -" << qPrintable(db.lastError().text());
1103 QSqlQuery query(db);
1104 query.prepare(queryString("select_buffers"));
1105 query.bindValue(":userid", user.toInt());
1109 while (query.next()) {
1110 bufferlist << BufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), query.value(3).toInt(), query.value(4).toString());
1117 QList<BufferId> PostgreSqlStorage::requestBufferIdsForNetwork(UserId user, NetworkId networkId)
1119 QList<BufferId> bufferList;
1121 QSqlDatabase db = logDb();
1122 if (!beginReadOnlyTransaction(db)) {
1123 qWarning() << "PostgreSqlStorage::requestBufferIdsForNetwork(): cannot start read only transaction!";
1124 qWarning() << " -" << qPrintable(db.lastError().text());
1128 QSqlQuery query(db);
1129 query.prepare(queryString("select_buffers_for_network"));
1130 query.bindValue(":networkid", networkId.toInt());
1131 query.bindValue(":userid", user.toInt());
1135 while (query.next()) {
1136 bufferList << BufferId(query.value(0).toInt());
1143 bool PostgreSqlStorage::removeBuffer(const UserId &user, const BufferId &bufferId)
1145 QSqlDatabase db = logDb();
1146 if (!db.transaction()) {
1147 qWarning() << "PostgreSqlStorage::removeBuffer(): cannot start transaction!";
1151 QSqlQuery query(db);
1152 query.prepare(queryString("delete_buffer_for_bufferid"));
1153 query.bindValue(":userid", user.toInt());
1154 query.bindValue(":bufferid", bufferId.toInt());
1156 if (!watchQuery(query)) {
1161 int numRows = query.numRowsAffected();
1170 // there was more then one buffer deleted...
1171 qWarning() << "PostgreSqlStorage::removeBuffer(): Userid" << user << "BufferId" << "caused deletion of" << numRows << "Buffers! Rolling back transaction...";
1178 bool PostgreSqlStorage::renameBuffer(const UserId &user, const BufferId &bufferId, const QString &newName)
1180 QSqlDatabase db = logDb();
1181 if (!db.transaction()) {
1182 qWarning() << "PostgreSqlStorage::renameBuffer(): cannot start transaction!";
1186 QSqlQuery query(db);
1187 query.prepare(queryString("update_buffer_name"));
1188 query.bindValue(":buffername", newName);
1189 query.bindValue(":buffercname", newName.toLower());
1190 query.bindValue(":userid", user.toInt());
1191 query.bindValue(":bufferid", bufferId.toInt());
1193 if (query.lastError().isValid()) {
1199 int numRows = query.numRowsAffected();
1208 // there was more then one buffer deleted...
1209 qWarning() << "PostgreSqlStorage::renameBuffer(): Userid" << user << "BufferId" << "affected" << numRows << "Buffers! Rolling back transaction...";
1216 bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const BufferId &bufferId1, const BufferId &bufferId2)
1218 QSqlDatabase db = logDb();
1219 if (!db.transaction()) {
1220 qWarning() << "PostgreSqlStorage::mergeBuffersPermanently(): cannot start transaction!";
1221 qWarning() << " -" << qPrintable(db.lastError().text());
1225 QSqlQuery checkQuery(db);
1226 checkQuery.prepare("SELECT count(*) FROM buffer "
1227 "WHERE userid = :userid AND bufferid IN (:buffer1, :buffer2)");
1228 checkQuery.bindValue(":userid", user.toInt());
1229 checkQuery.bindValue(":buffer1", bufferId1.toInt());
1230 checkQuery.bindValue(":buffer2", bufferId2.toInt());
1231 safeExec(checkQuery);
1232 if (!watchQuery(checkQuery)) {
1237 if (checkQuery.value(0).toInt() != 2) {
1242 QSqlQuery query(db);
1243 query.prepare(queryString("update_backlog_bufferid"));
1244 query.bindValue(":oldbufferid", bufferId2.toInt());
1245 query.bindValue(":newbufferid", bufferId1.toInt());
1247 if (!watchQuery(query)) {
1252 QSqlQuery delBufferQuery(logDb());
1253 delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
1254 delBufferQuery.bindValue(":userid", user.toInt());
1255 delBufferQuery.bindValue(":bufferid", bufferId2.toInt());
1256 safeExec(delBufferQuery);
1257 if (!watchQuery(delBufferQuery)) {
1267 void PostgreSqlStorage::setBufferLastSeenMsg(UserId user, const BufferId &bufferId, const MsgId &msgId)
1269 QSqlQuery query(logDb());
1270 query.prepare(queryString("update_buffer_lastseen"));
1272 query.bindValue(":userid", user.toInt());
1273 query.bindValue(":bufferid", bufferId.toInt());
1274 query.bindValue(":lastseenmsgid", msgId.toInt());
1280 QHash<BufferId, MsgId> PostgreSqlStorage::bufferLastSeenMsgIds(UserId user)
1282 QHash<BufferId, MsgId> lastSeenHash;
1284 QSqlDatabase db = logDb();
1285 if (!beginReadOnlyTransaction(db)) {
1286 qWarning() << "PostgreSqlStorage::bufferLastSeenMsgIds(): cannot start read only transaction!";
1287 qWarning() << " -" << qPrintable(db.lastError().text());
1288 return lastSeenHash;
1291 QSqlQuery query(db);
1292 query.prepare(queryString("select_buffer_lastseen_messages"));
1293 query.bindValue(":userid", user.toInt());
1295 if (!watchQuery(query)) {
1297 return lastSeenHash;
1300 while (query.next()) {
1301 lastSeenHash[query.value(0).toInt()] = query.value(1).toInt();
1305 return lastSeenHash;
1309 void PostgreSqlStorage::setBufferMarkerLineMsg(UserId user, const BufferId &bufferId, const MsgId &msgId)
1311 QSqlQuery query(logDb());
1312 query.prepare(queryString("update_buffer_markerlinemsgid"));
1314 query.bindValue(":userid", user.toInt());
1315 query.bindValue(":bufferid", bufferId.toInt());
1316 query.bindValue(":markerlinemsgid", msgId.toInt());
1322 QHash<BufferId, MsgId> PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user)
1324 QHash<BufferId, MsgId> markerLineHash;
1326 QSqlDatabase db = logDb();
1327 if (!beginReadOnlyTransaction(db)) {
1328 qWarning() << "PostgreSqlStorage::bufferMarkerLineMsgIds(): cannot start read only transaction!";
1329 qWarning() << " -" << qPrintable(db.lastError().text());
1330 return markerLineHash;
1333 QSqlQuery query(db);
1334 query.prepare(queryString("select_buffer_markerlinemsgids"));
1335 query.bindValue(":userid", user.toInt());
1337 if (!watchQuery(query)) {
1339 return markerLineHash;
1342 while (query.next()) {
1343 markerLineHash[query.value(0).toInt()] = query.value(1).toInt();
1347 return markerLineHash;
1351 bool PostgreSqlStorage::logMessage(Message &msg)
1353 QSqlDatabase db = logDb();
1354 if (!db.transaction()) {
1355 qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!";
1356 qWarning() << " -" << qPrintable(db.lastError().text());
1360 QSqlQuery getSenderIdQuery = executePreparedQuery("select_senderid", msg.sender(), db);
1362 if (getSenderIdQuery.first()) {
1363 senderId = getSenderIdQuery.value(0).toInt();
1366 // it's possible that the sender was already added by another thread
1367 // since the insert might fail we're setting a savepoint
1368 savePoint("sender_sp1", db);
1369 QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", msg.sender(), db);
1371 if (addSenderQuery.lastError().isValid()) {
1372 rollbackSavePoint("sender_sp1", db);
1373 getSenderIdQuery = db.exec(getSenderIdQuery.lastQuery());
1374 getSenderIdQuery.first();
1375 senderId = getSenderIdQuery.value(0).toInt();
1378 releaseSavePoint("sender_sp1", db);
1379 addSenderQuery.first();
1380 senderId = addSenderQuery.value(0).toInt();
1384 QVariantList params;
1385 params << msg.timestamp()
1386 << msg.bufferInfo().bufferId().toInt()
1391 QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db);
1393 if (!watchQuery(logMessageQuery)) {
1398 logMessageQuery.first();
1399 MsgId msgId = logMessageQuery.value(0).toInt();
1401 if (msgId.isValid()) {
1402 msg.setMsgId(msgId);
1411 bool PostgreSqlStorage::logMessages(MessageList &msgs)
1413 QSqlDatabase db = logDb();
1414 if (!db.transaction()) {
1415 qWarning() << "PostgreSqlStorage::logMessage(): cannot start transaction!";
1416 qWarning() << " -" << qPrintable(db.lastError().text());
1420 QList<int> senderIdList;
1421 QHash<QString, int> senderIds;
1422 QSqlQuery addSenderQuery;
1423 QSqlQuery selectSenderQuery;;
1424 for (int i = 0; i < msgs.count(); i++) {
1425 const QString &sender = msgs.at(i).sender();
1426 if (senderIds.contains(sender)) {
1427 senderIdList << senderIds[sender];
1431 selectSenderQuery = executePreparedQuery("select_senderid", sender, db);
1432 if (selectSenderQuery.first()) {
1433 senderIdList << selectSenderQuery.value(0).toInt();
1434 senderIds[sender] = selectSenderQuery.value(0).toInt();
1437 savePoint("sender_sp", db);
1438 addSenderQuery = executePreparedQuery("insert_sender", sender, db);
1439 if (addSenderQuery.lastError().isValid()) {
1440 // seems it was inserted meanwhile... by a different thread
1441 rollbackSavePoint("sender_sp", db);
1442 selectSenderQuery = db.exec(selectSenderQuery.lastQuery());
1443 selectSenderQuery.first();
1444 senderIdList << selectSenderQuery.value(0).toInt();
1445 senderIds[sender] = selectSenderQuery.value(0).toInt();
1448 releaseSavePoint("sender_sp", db);
1449 addSenderQuery.first();
1450 senderIdList << addSenderQuery.value(0).toInt();
1451 senderIds[sender] = addSenderQuery.value(0).toInt();
1456 // yes we loop twice over the same list. This avoids alternating queries.
1458 for (int i = 0; i < msgs.count(); i++) {
1459 Message &msg = msgs[i];
1460 QVariantList params;
1461 params << msg.timestamp()
1462 << msg.bufferInfo().bufferId().toInt()
1465 << senderIdList.at(i)
1467 QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db);
1468 if (!watchQuery(logMessageQuery)) {
1474 logMessageQuery.first();
1475 msg.setMsgId(logMessageQuery.value(0).toInt());
1480 // we had a rollback in the db so we need to reset all msgIds
1481 for (int i = 0; i < msgs.count(); i++) {
1482 msgs[i].setMsgId(MsgId());
1492 QList<Message> PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit)
1494 QList<Message> messagelist;
1496 QSqlDatabase db = logDb();
1497 if (!beginReadOnlyTransaction(db)) {
1498 qWarning() << "PostgreSqlStorage::requestMsgs(): cannot start read only transaction!";
1499 qWarning() << " -" << qPrintable(db.lastError().text());
1503 BufferInfo bufferInfo = getBufferInfo(user, bufferId);
1504 if (!bufferInfo.isValid()) {
1510 QVariantList params;
1511 if (last == -1 && first == -1) {
1512 queryName = "select_messages";
1514 else if (last == -1) {
1515 queryName = "select_messagesNewerThan";
1516 params << first.toInt();
1519 queryName = "select_messagesRange";
1520 params << first.toInt();
1521 params << last.toInt();
1523 params << bufferId.toInt();
1527 params << QVariant(QVariant::Int);
1529 QSqlQuery query = executePreparedQuery(queryName, params, db);
1531 if (!watchQuery(query)) {
1532 qDebug() << "select_messages failed";
1537 QDateTime timestamp;
1538 while (query.next()) {
1539 timestamp = query.value(1).toDateTime();
1540 timestamp.setTimeSpec(Qt::UTC);
1541 Message msg(timestamp,
1543 (Message::Type)query.value(2).toUInt(),
1544 query.value(5).toString(),
1545 query.value(4).toString(),
1546 (Message::Flags)query.value(3).toUInt());
1547 msg.setMsgId(query.value(0).toInt());
1556 QList<Message> PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit)
1558 QList<Message> messagelist;
1560 // requestBuffers uses it's own transaction.
1561 QHash<BufferId, BufferInfo> bufferInfoHash;
1562 foreach(BufferInfo bufferInfo, requestBuffers(user)) {
1563 bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
1566 QSqlDatabase db = logDb();
1567 if (!beginReadOnlyTransaction(db)) {
1568 qWarning() << "PostgreSqlStorage::requestAllMsgs(): cannot start read only transaction!";
1569 qWarning() << " -" << qPrintable(db.lastError().text());
1573 QSqlQuery query(db);
1575 query.prepare(queryString("select_messagesAllNew"));
1578 query.prepare(queryString("select_messagesAll"));
1579 query.bindValue(":lastmsg", last.toInt());
1581 query.bindValue(":userid", user.toInt());
1582 query.bindValue(":firstmsg", first.toInt());
1584 if (!watchQuery(query)) {
1589 QDateTime timestamp;
1590 for (int i = 0; i < limit && query.next(); i++) {
1591 timestamp = query.value(1).toDateTime();
1592 timestamp.setTimeSpec(Qt::UTC);
1593 Message msg(timestamp,
1594 bufferInfoHash[query.value(1).toInt()],
1595 (Message::Type)query.value(3).toUInt(),
1596 query.value(6).toString(),
1597 query.value(5).toString(),
1598 (Message::Flags)query.value(4).toUInt());
1599 msg.setMsgId(query.value(0).toInt());
1608 // void PostgreSqlStorage::safeExec(QSqlQuery &query) {
1609 // qDebug() << "PostgreSqlStorage::safeExec";
1610 // qDebug() << " executing:\n" << query.executedQuery();
1611 // qDebug() << " bound Values:";
1612 // QList<QVariant> list = query.boundValues().values();
1613 // for (int i = 0; i < list.size(); ++i)
1614 // qCritical() << i << ": " << list.at(i).toString().toLatin1().data();
1618 // qDebug() << "Success:" << !query.lastError().isValid();
1621 // if(!query.lastError().isValid())
1624 // qDebug() << "==================== ERROR ====================";
1625 // watchQuery(query);
1626 // qDebug() << "===============================================";
1631 bool PostgreSqlStorage::beginReadOnlyTransaction(QSqlDatabase &db)
1633 QSqlQuery query = db.exec("BEGIN TRANSACTION READ ONLY");
1634 return !query.lastError().isValid();
1638 QSqlQuery PostgreSqlStorage::prepareAndExecuteQuery(const QString &queryname, const QString ¶mstring, const QSqlDatabase &db)
1640 // Query preparing is done lazily. That means that instead of always checking if the query is already prepared
1641 // we just EXECUTE and catch the error
1644 db.exec("SAVEPOINT quassel_prepare_query");
1645 if (paramstring.isNull()) {
1646 query = db.exec(QString("EXECUTE quassel_%1").arg(queryname));
1649 query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring));
1652 if (db.lastError().isValid()) {
1653 // and once again: Qt leaves us without error codes so we either parse (language dependant(!)) strings
1654 // or we just guess the error. As we're only interested in unprepared queries, this will be our guess. :)
1655 db.exec("ROLLBACK TO SAVEPOINT quassel_prepare_query");
1656 QSqlQuery checkQuery = db.exec(QString("SELECT count(name) FROM pg_prepared_statements WHERE name = 'quassel_%1' AND from_sql = TRUE").arg(queryname.toLower()));
1658 if (checkQuery.value(0).toInt() == 0) {
1659 db.exec(QString("PREPARE quassel_%1 AS %2").arg(queryname).arg(queryString(queryname)));
1660 if (db.lastError().isValid()) {
1661 qWarning() << "PostgreSqlStorage::prepareQuery(): unable to prepare query:" << queryname << "AS" << queryString(queryname);
1662 qWarning() << " Error:" << db.lastError().text();
1663 return QSqlQuery(db);
1666 // we alwas execute the query again, even if the query was already prepared.
1667 // this ensures, that the error is properly propagated to the calling function
1668 // (otherwise the last call would be the testing select to pg_prepared_statements
1669 // which always gives a proper result and the error would be lost)
1670 if (paramstring.isNull()) {
1671 query = db.exec(QString("EXECUTE quassel_%1").arg(queryname));
1674 query = db.exec(QString("EXECUTE quassel_%1 (%2)").arg(queryname).arg(paramstring));
1678 // only release the SAVEPOINT
1679 db.exec("RELEASE SAVEPOINT quassel_prepare_query");
1685 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &queryname, const QVariantList ¶ms, const QSqlDatabase &db)
1687 QSqlDriver *driver = db.driver();
1689 QStringList paramStrings;
1691 for (int i = 0; i < params.count(); i++) {
1692 const QVariant &value = params.at(i);
1693 field.setType(value.type());
1697 field.setValue(value);
1699 paramStrings << driver->formatValue(field);
1702 if (params.isEmpty()) {
1703 return prepareAndExecuteQuery(queryname, db);
1706 return prepareAndExecuteQuery(queryname, paramStrings.join(", "), db);
1711 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &queryname, const QVariant ¶m, const QSqlDatabase &db)
1714 field.setType(param.type());
1718 field.setValue(param);
1720 QString paramString = db.driver()->formatValue(field);
1721 return prepareAndExecuteQuery(queryname, paramString, db);
1725 void PostgreSqlStorage::deallocateQuery(const QString &queryname, const QSqlDatabase &db)
1727 db.exec(QString("DEALLOCATE quassel_%1").arg(queryname));
1731 // ========================================
1732 // PostgreSqlMigrationWriter
1733 // ========================================
1734 PostgreSqlMigrationWriter::PostgreSqlMigrationWriter()
1735 : PostgreSqlStorage()
1740 bool PostgreSqlMigrationWriter::prepareQuery(MigrationObject mo)
1745 query = queryString("migrate_write_quasseluser");
1748 query = queryString("migrate_write_sender");
1751 _validIdentities.clear();
1752 query = queryString("migrate_write_identity");
1755 query = queryString("migrate_write_identity_nick");
1758 query = queryString("migrate_write_network");
1761 query = queryString("migrate_write_buffer");
1764 query = queryString("migrate_write_backlog");
1767 query = queryString("migrate_write_ircserver");
1770 query = queryString("migrate_write_usersetting");
1773 newQuery(query, logDb());
1778 //bool PostgreSqlMigrationWriter::writeUser(const QuasselUserMO &user) {
1779 bool PostgreSqlMigrationWriter::writeMo(const QuasselUserMO &user)
1781 bindValue(0, user.id.toInt());
1782 bindValue(1, user.username);
1783 bindValue(2, user.password);
1788 //bool PostgreSqlMigrationWriter::writeSender(const SenderMO &sender) {
1789 bool PostgreSqlMigrationWriter::writeMo(const SenderMO &sender)
1791 bindValue(0, sender.senderId);
1792 bindValue(1, sender.sender);
1797 //bool PostgreSqlMigrationWriter::writeIdentity(const IdentityMO &identity) {
1798 bool PostgreSqlMigrationWriter::writeMo(const IdentityMO &identity)
1800 _validIdentities << identity.id.toInt();
1801 bindValue(0, identity.id.toInt());
1802 bindValue(1, identity.userid.toInt());
1803 bindValue(2, identity.identityname);
1804 bindValue(3, identity.realname);
1805 bindValue(4, identity.awayNick);
1806 bindValue(5, identity.awayNickEnabled);
1807 bindValue(6, identity.awayReason);
1808 bindValue(7, identity.awayReasonEnabled);
1809 bindValue(8, identity.autoAwayEnabled);
1810 bindValue(9, identity.autoAwayTime);
1811 bindValue(10, identity.autoAwayReason);
1812 bindValue(11, identity.autoAwayReasonEnabled);
1813 bindValue(12, identity.detachAwayEnabled);
1814 bindValue(13, identity.detachAwayReason);
1815 bindValue(14, identity.detchAwayReasonEnabled);
1816 bindValue(15, identity.ident);
1817 bindValue(16, identity.kickReason);
1818 bindValue(17, identity.partReason);
1819 bindValue(18, identity.quitReason);
1820 bindValue(19, identity.sslCert);
1821 bindValue(20, identity.sslKey);
1826 //bool PostgreSqlMigrationWriter::writeIdentityNick(const IdentityNickMO &identityNick) {
1827 bool PostgreSqlMigrationWriter::writeMo(const IdentityNickMO &identityNick)
1829 bindValue(0, identityNick.nickid);
1830 bindValue(1, identityNick.identityId.toInt());
1831 bindValue(2, identityNick.nick);
1836 //bool PostgreSqlMigrationWriter::writeNetwork(const NetworkMO &network) {
1837 bool PostgreSqlMigrationWriter::writeMo(const NetworkMO &network)
1839 bindValue(0, network.networkid.toInt());
1840 bindValue(1, network.userid.toInt());
1841 bindValue(2, network.networkname);
1842 if (_validIdentities.contains(network.identityid.toInt()))
1843 bindValue(3, network.identityid.toInt());
1845 bindValue(3, QVariant());
1846 bindValue(4, network.encodingcodec);
1847 bindValue(5, network.decodingcodec);
1848 bindValue(6, network.servercodec);
1849 bindValue(7, network.userandomserver);
1850 bindValue(8, network.perform);
1851 bindValue(9, network.useautoidentify);
1852 bindValue(10, network.autoidentifyservice);
1853 bindValue(11, network.autoidentifypassword);
1854 bindValue(12, network.useautoreconnect);
1855 bindValue(13, network.autoreconnectinterval);
1856 bindValue(14, network.autoreconnectretries);
1857 bindValue(15, network.unlimitedconnectretries);
1858 bindValue(16, network.rejoinchannels);
1859 bindValue(17, network.connected);
1860 bindValue(18, network.usermode);
1861 bindValue(19, network.awaymessage);
1862 bindValue(20, network.attachperform);
1863 bindValue(21, network.detachperform);
1864 bindValue(22, network.usesasl);
1865 bindValue(23, network.saslaccount);
1866 bindValue(24, network.saslpassword);
1871 //bool PostgreSqlMigrationWriter::writeBuffer(const BufferMO &buffer) {
1872 bool PostgreSqlMigrationWriter::writeMo(const BufferMO &buffer)
1874 bindValue(0, buffer.bufferid.toInt());
1875 bindValue(1, buffer.userid.toInt());
1876 bindValue(2, buffer.groupid);
1877 bindValue(3, buffer.networkid.toInt());
1878 bindValue(4, buffer.buffername);
1879 bindValue(5, buffer.buffercname);
1880 bindValue(6, (int)buffer.buffertype);
1881 bindValue(7, buffer.lastseenmsgid);
1882 bindValue(8, buffer.markerlinemsgid);
1883 bindValue(9, buffer.key);
1884 bindValue(10, buffer.joined);
1889 //bool PostgreSqlMigrationWriter::writeBacklog(const BacklogMO &backlog) {
1890 bool PostgreSqlMigrationWriter::writeMo(const BacklogMO &backlog)
1892 bindValue(0, backlog.messageid.toInt());
1893 bindValue(1, backlog.time);
1894 bindValue(2, backlog.bufferid.toInt());
1895 bindValue(3, backlog.type);
1896 bindValue(4, (int)backlog.flags);
1897 bindValue(5, backlog.senderid);
1898 bindValue(6, backlog.message);
1903 //bool PostgreSqlMigrationWriter::writeIrcServer(const IrcServerMO &ircserver) {
1904 bool PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver)
1906 bindValue(0, ircserver.serverid);
1907 bindValue(1, ircserver.userid.toInt());
1908 bindValue(2, ircserver.networkid.toInt());
1909 bindValue(3, ircserver.hostname);
1910 bindValue(4, ircserver.port);
1911 bindValue(5, ircserver.password);
1912 bindValue(6, ircserver.ssl);
1913 bindValue(7, ircserver.sslversion);
1914 bindValue(8, ircserver.useproxy);
1915 bindValue(9, ircserver.proxytype);
1916 bindValue(10, ircserver.proxyhost);
1917 bindValue(11, ircserver.proxyport);
1918 bindValue(12, ircserver.proxyuser);
1919 bindValue(13, ircserver.proxypass);
1924 //bool PostgreSqlMigrationWriter::writeUserSetting(const UserSettingMO &userSetting) {
1925 bool PostgreSqlMigrationWriter::writeMo(const UserSettingMO &userSetting)
1927 bindValue(0, userSetting.userid.toInt());
1928 bindValue(1, userSetting.settingname);
1929 bindValue(2, userSetting.settingvalue);
1934 bool PostgreSqlMigrationWriter::postProcess()
1936 QSqlDatabase db = logDb();
1937 QList<Sequence> sequences;
1938 sequences << Sequence("backlog", "messageid")
1939 << Sequence("buffer", "bufferid")
1940 << Sequence("identity", "identityid")
1941 << Sequence("identity_nick", "nickid")
1942 << Sequence("ircserver", "serverid")
1943 << Sequence("network", "networkid")
1944 << Sequence("quasseluser", "userid")
1945 << Sequence("sender", "senderid");
1946 QList<Sequence>::const_iterator iter;
1947 for (iter = sequences.constBegin(); iter != sequences.constEnd(); iter++) {
1949 newQuery(QString("SELECT setval('%1_%2_seq', max(%2)) FROM %1").arg(iter->table, iter->field), db);