X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=blobdiff_plain;f=src%2Fcore%2Fpostgresqlstorage.cpp;h=13aa8c94ff34f72f53d7134edc7b7f50ddbe357e;hp=96805baf5b1f635e2d105705ca013f4120e33cb2;hb=7723c1357b48dd30842d40d4e4237e15dbffad70;hpb=b9828e0dd235964b8e2e97f844f4bed3476d3bd4 diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index 96805baf..13aa8c94 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -26,8 +26,6 @@ #include "network.h" #include "quassel.h" -int PostgreSqlStorage::_maxRetryCount = 150; // yes this is a large number... only other way to "handle" this is bailing out... - PostgreSqlStorage::PostgreSqlStorage(QObject *parent) : AbstractSqlStorage(parent), _port(-1) @@ -37,6 +35,18 @@ PostgreSqlStorage::PostgreSqlStorage(QObject *parent) PostgreSqlStorage::~PostgreSqlStorage() { } +AbstractSqlMigrationWriter *PostgreSqlStorage::createMigrationWriter() { + PostgreSqlMigrationWriter *writer = new PostgreSqlMigrationWriter(); + QVariantMap properties; + properties["Username"] = _userName; + properties["Password"] = _password; + properties["Hostname"] = _hostName; + properties["Port"] = _port; + properties["Database"] = _databaseName; + writer->setConnectionProperties(properties); + return writer; +} + bool PostgreSqlStorage::isAvailable() const { if(!QSqlDatabase::isDriverAvailable("QPSQL")) return false; return true; @@ -51,16 +61,32 @@ QString PostgreSqlStorage::description() const { return tr("PostgreSQL Turbo Bomber HD!"); } -QVariantMap PostgreSqlStorage::setupKeys() const { +QStringList PostgreSqlStorage::setupKeys() const { + QStringList keys; + keys << "Username" + << "Password" + << "Hostname" + << "Port" + << "Database"; + return keys; +} +QVariantMap PostgreSqlStorage::setupDefaults() const { QVariantMap map; map["Username"] = QVariant(QString("quassel")); - map["Password"] = QVariant(QString()); map["Hostname"] = QVariant(QString("localhost")); map["Port"] = QVariant(5432); map["Database"] = QVariant(QString("quassel")); return map; } +bool PostgreSqlStorage::setup(const QVariantMap &settings) { + bool success = AbstractSqlStorage::setup(settings); + if(success) { + logDb().exec(QString("ALTER USER %1 SET standard_conforming_strings TO on").arg(userName())); + } + return success; +} + void PostgreSqlStorage::setConnectionProperties(const QVariantMap &properties) { _userName = properties["Username"].toString(); _password = properties["Password"].toString(); @@ -125,12 +151,13 @@ UserId PostgreSqlStorage::addUser(const QString &user, const QString &password) return uid; } -void PostgreSqlStorage::updateUser(UserId user, const QString &password) { +bool PostgreSqlStorage::updateUser(UserId user, const QString &password) { QSqlQuery query(logDb()); query.prepare(queryString("update_userpassword")); query.bindValue(":userid", user.toInt()); query.bindValue(":password", cryptedPassword(password)); safeExec(query); + return query.numRowsAffected() != 0; } void PostgreSqlStorage::renameUser(UserId user, const QString &newName) { @@ -156,6 +183,19 @@ UserId PostgreSqlStorage::validateUser(const QString &user, const QString &passw } } +UserId PostgreSqlStorage::getUserId(const QString &user) { + QSqlQuery query(logDb()); + query.prepare(queryString("select_userid")); + query.bindValue(":username", user); + safeExec(query); + + if(query.first()) { + return query.value(0).toInt(); + } else { + return 0; + } +} + UserId PostgreSqlStorage::internalUser() { QSqlQuery query(logDb()); query.prepare(queryString("select_internaluser")); @@ -276,7 +316,7 @@ IdentityId PostgreSqlStorage::createIdentity(UserId user, CoreIdentity &identity return IdentityId(); } - qDebug() << "creatId" << query.first() << query.value(0).toInt(); + query.first(); identityId = query.value(0).toInt(); identity.setId(identityId); @@ -488,7 +528,7 @@ NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info) return NetworkId(); } - qDebug() << "createNet:" << query.first() << query.value(0).toInt(); + query.first(); networkId = query.value(0).toInt(); if(!networkId.isValid()) { @@ -511,7 +551,7 @@ NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info) } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::updateNetwork(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::createNetwork(): commiting data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return NetworkId(); } @@ -520,7 +560,7 @@ NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info) void PostgreSqlStorage::bindNetworkInfo(QSqlQuery &query, const NetworkInfo &info) { query.bindValue(":networkname", info.networkName); - query.bindValue(":identityid", info.identity.toInt()); + query.bindValue(":identityid", info.identity.isValid() ? info.identity.toInt() : QVariant()); query.bindValue(":encodingcodec", QString(info.codecForEncoding)); query.bindValue(":decodingcodec", QString(info.codecForDecoding)); query.bindValue(":servercodec", QString(info.codecForServer)); @@ -973,7 +1013,7 @@ bool PostgreSqlStorage::removeBuffer(const UserId &user, const BufferId &bufferI return false; case 1: db.commit(); - return false; + return true; default: // there was more then one buffer deleted... qWarning() << "PostgreSqlStorage::removeBuffer(): Userid" << user << "BufferId" << "caused deletion of" << numRows << "Buffers! Rolling back transaction..."; @@ -1009,7 +1049,7 @@ bool PostgreSqlStorage::renameBuffer(const UserId &user, const BufferId &bufferI return false; case 1: db.commit(); - return false; + return true; default: // there was more then one buffer deleted... qWarning() << "PostgreSqlStorage::renameBuffer(): Userid" << user << "BufferId" << "affected" << numRows << "Buffers! Rolling back transaction..."; @@ -1027,7 +1067,7 @@ bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const Buffer } QSqlQuery checkQuery(db); - checkQuery.prepare("SELECT count(*) FROM buffer" + checkQuery.prepare("SELECT count(*) FROM buffer " "WHERE userid = :userid AND bufferid IN (:buffer1, :buffer2)"); checkQuery.bindValue(":userid", user.toInt()); checkQuery.bindValue(":buffer1", bufferId1.toInt()); @@ -1120,7 +1160,7 @@ bool PostgreSqlStorage::logMessage(Message &msg) { } QVariantList params; - params << msg.timestamp().toTime_t() + params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() @@ -1204,7 +1244,7 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { for(int i = 0; i < msgs.count(); i++) { Message &msg = msgs[i]; QVariantList params; - params << msg.timestamp().toTime_t() + params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() @@ -1249,31 +1289,49 @@ QList PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, Ms return messagelist; } - QSqlQuery query(db); + QString queryName; + QVariantList params; if(last == -1 && first == -1) { - query.prepare(queryString("select_messages")); + queryName = "select_messages"; } else if(last == -1) { - query.prepare(queryString("select_messagesNewerThan")); - query.bindValue(":firstmsg", first.toInt()); + queryName = "select_messagesNewerThan"; + params << first.toInt(); } else { - query.prepare(queryString("select_messagesRange")); - query.bindValue(":lastmsg", last.toInt()); - query.bindValue(":firstmsg", first.toInt()); + queryName = "select_messagesRange"; + params << first.toInt(); + params << last.toInt(); } - query.bindValue(":bufferid", bufferId.toInt()); - safeExec(query); + params << bufferId.toInt(); + if(limit != -1) + params << limit; + else + params << "ALL"; + + if(!prepareQuery(queryName, queryString(queryName), db)) { + qWarning() << "PostgreSqlStorage::logMessages(): unable to prepare query:" << queryString(queryName); + qWarning() << " Error:" << db.lastError().text(); + db.rollback(); + return messagelist; + } + + QSqlQuery query = executePreparedQuery(queryName, params, db); + if(!watchQuery(query)) { + qDebug() << "select_messages failed"; db.rollback(); return messagelist; } - for(int i = 0; i < limit && query.next(); i++) { - Message msg(QDateTime::fromTime_t(query.value(1).toInt()), - bufferInfo, - (Message::Type)query.value(2).toUInt(), - query.value(5).toString(), - query.value(4).toString(), - (Message::Flags)query.value(3).toUInt()); + QDateTime timestamp; + while(query.next()) { + timestamp = query.value(1).toDateTime(); + timestamp.setTimeSpec(Qt::UTC); + Message msg(timestamp, + bufferInfo, + (Message::Type)query.value(2).toUInt(), + query.value(5).toString(), + query.value(4).toString(), + (Message::Flags)query.value(3).toUInt()); msg.setMsgId(query.value(0).toInt()); messagelist << msg; } @@ -1313,8 +1371,11 @@ QList PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId return messagelist; } + QDateTime timestamp; for(int i = 0; i < limit && query.next(); i++) { - Message msg(QDateTime::fromTime_t(query.value(2).toInt()), + timestamp = query.value(1).toDateTime(); + timestamp.setTimeSpec(Qt::UTC); + Message msg(timestamp, bufferInfoHash[query.value(1).toInt()], (Message::Type)query.value(3).toUInt(), query.value(6).toString(), @@ -1377,7 +1438,7 @@ bool PostgreSqlStorage::prepareQuery(const QString &handle, const QString &query QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariantList ¶ms, const QSqlDatabase &db) { if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { qWarning() << "PostgreSqlStorage::executePreparedQuery() no prepared Query with handle" << handle << "on Database" << db.connectionName(); - return QSqlQuery(); + return QSqlQuery(db); } QSqlDriver *driver = db.driver(); @@ -1407,7 +1468,7 @@ QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const Q QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariant ¶m, const QSqlDatabase &db) { if(!_preparedQueries.contains(db.connectionName()) || !_preparedQueries[db.connectionName()].contains(handle)) { qWarning() << "PostgreSqlStorage::executePreparedQuery() no prepared Query with handle" << handle << "on Database" << db.connectionName(); - return QSqlQuery(); + return QSqlQuery(db); } QSqlField field; @@ -1433,3 +1494,201 @@ void PostgreSqlStorage::deallocateQuery(const QString &handle, const QSqlDatabas db.exec(QString("DEALLOCATE %1").arg(queryId)); } +// ======================================== +// PostgreSqlMigrationWriter +// ======================================== +PostgreSqlMigrationWriter::PostgreSqlMigrationWriter() + : PostgreSqlStorage() +{ +} + +bool PostgreSqlMigrationWriter::prepareQuery(MigrationObject mo) { + QString query; + switch(mo) { + case QuasselUser: + query = queryString("migrate_write_quasseluser"); + break; + case Sender: + query = queryString("migrate_write_sender"); + break; + case Identity: + _validIdentities.clear(); + query = queryString("migrate_write_identity"); + break; + case IdentityNick: + query = queryString("migrate_write_identity_nick"); + break; + case Network: + query = queryString("migrate_write_network"); + break; + case Buffer: + query = queryString("migrate_write_buffer"); + break; + case Backlog: + query = queryString("migrate_write_backlog"); + break; + case IrcServer: + query = queryString("migrate_write_ircserver"); + break; + case UserSetting: + query = queryString("migrate_write_usersetting"); + break; + } + newQuery(query, logDb()); + return true; +} + +//bool PostgreSqlMigrationWriter::writeUser(const QuasselUserMO &user) { +bool PostgreSqlMigrationWriter::writeMo(const QuasselUserMO &user) { + bindValue(0, user.id.toInt()); + bindValue(1, user.username); + bindValue(2, user.password); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeSender(const SenderMO &sender) { +bool PostgreSqlMigrationWriter::writeMo(const SenderMO &sender) { + bindValue(0, sender.senderId); + bindValue(1, sender.sender); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeIdentity(const IdentityMO &identity) { +bool PostgreSqlMigrationWriter::writeMo(const IdentityMO &identity) { + _validIdentities << identity.id.toInt(); + bindValue(0, identity.id.toInt()); + bindValue(1, identity.userid.toInt()); + bindValue(2, identity.identityname); + bindValue(3, identity.realname); + bindValue(4, identity.awayNick); + bindValue(5, identity.awayNickEnabled); + bindValue(6, identity.awayReason); + bindValue(7, identity.awayReasonEnabled); + bindValue(8, identity.autoAwayEnabled); + bindValue(9, identity.autoAwayTime); + bindValue(10, identity.autoAwayReason); + bindValue(11, identity.autoAwayReasonEnabled); + bindValue(12, identity.detachAwayEnabled); + bindValue(13, identity.detachAwayReason); + bindValue(14, identity.detchAwayReasonEnabled); + bindValue(15, identity.ident); + bindValue(16, identity.kickReason); + bindValue(17, identity.partReason); + bindValue(18, identity.quitReason); + bindValue(19, identity.sslCert); + bindValue(20, identity.sslKey); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeIdentityNick(const IdentityNickMO &identityNick) { +bool PostgreSqlMigrationWriter::writeMo(const IdentityNickMO &identityNick) { + bindValue(0, identityNick.nickid); + bindValue(1, identityNick.identityId.toInt()); + bindValue(2, identityNick.nick); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeNetwork(const NetworkMO &network) { +bool PostgreSqlMigrationWriter::writeMo(const NetworkMO &network) { + bindValue(0, network.networkid.toInt()); + bindValue(1, network.userid.toInt()); + bindValue(2, network.networkname); + if(_validIdentities.contains(network.identityid.toInt())) + bindValue(3, network.identityid.toInt()); + else + bindValue(3, QVariant()); + bindValue(4, network.encodingcodec); + bindValue(5, network.decodingcodec); + bindValue(6, network.servercodec); + bindValue(7, network.userandomserver); + bindValue(8, network.perform); + bindValue(9, network.useautoidentify); + bindValue(10, network.autoidentifyservice); + bindValue(11, network.autoidentifypassword); + bindValue(12, network.useautoreconnect); + bindValue(13, network.autoreconnectinterval); + bindValue(14, network.autoreconnectretries); + bindValue(15, network.unlimitedconnectretries); + bindValue(16, network.rejoinchannels); + bindValue(17, network.connected); + bindValue(18, network.usermode); + bindValue(19, network.awaymessage); + bindValue(20, network.attachperform); + bindValue(21, network.detachperform); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeBuffer(const BufferMO &buffer) { +bool PostgreSqlMigrationWriter::writeMo(const BufferMO &buffer) { + bindValue(0, buffer.bufferid.toInt()); + bindValue(1, buffer.userid.toInt()); + bindValue(2, buffer.groupid); + bindValue(3, buffer.networkid.toInt()); + bindValue(4, buffer.buffername); + bindValue(5, buffer.buffercname); + bindValue(6, (int)buffer.buffertype); + bindValue(7, buffer.lastseenmsgid); + bindValue(8, buffer.key); + bindValue(9, buffer.joined); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeBacklog(const BacklogMO &backlog) { +bool PostgreSqlMigrationWriter::writeMo(const BacklogMO &backlog) { + bindValue(0, backlog.messageid.toInt()); + bindValue(1, backlog.time); + bindValue(2, backlog.bufferid.toInt()); + bindValue(3, backlog.type); + bindValue(4, (int)backlog.flags); + bindValue(5, backlog.senderid); + bindValue(6, backlog.message); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeIrcServer(const IrcServerMO &ircserver) { +bool PostgreSqlMigrationWriter::writeMo(const IrcServerMO &ircserver) { + bindValue(0, ircserver.serverid); + bindValue(1, ircserver.userid.toInt()); + bindValue(2, ircserver.networkid.toInt()); + bindValue(3, ircserver.hostname); + bindValue(4, ircserver.port); + bindValue(5, ircserver.password); + bindValue(6, ircserver.ssl); + bindValue(7, ircserver.sslversion); + bindValue(8, ircserver.useproxy); + bindValue(9, ircserver.proxytype); + bindValue(10, ircserver.proxyhost); + bindValue(11, ircserver.proxyport); + bindValue(12, ircserver.proxyuser); + bindValue(13, ircserver.proxypass); + return exec(); +} + +//bool PostgreSqlMigrationWriter::writeUserSetting(const UserSettingMO &userSetting) { +bool PostgreSqlMigrationWriter::writeMo(const UserSettingMO &userSetting) { + bindValue(0, userSetting.userid.toInt()); + bindValue(1, userSetting.settingname); + bindValue(2, userSetting.settingvalue); + return exec(); +} + +bool PostgreSqlMigrationWriter::postProcess() { + QSqlDatabase db = logDb(); + QList sequences; + sequences << Sequence("backlog", "messageid") + << Sequence("buffer", "bufferid") + << Sequence("identity", "identityid") + << Sequence("identity_nick", "nickid") + << Sequence("ircserver", "serverid") + << Sequence("network", "networkid") + << Sequence("quasseluser", "userid") + << Sequence("sender", "senderid"); + QList::const_iterator iter; + for(iter = sequences.constBegin(); iter != sequences.constEnd(); iter++) { + resetQuery(); + newQuery(QString("SELECT setval('%1_%2_seq', max(%2)) FROM %1").arg(iter->table, iter->field), db); + if(!exec()) + return false; + } + return true; +}