X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=blobdiff_plain;f=src%2Fcore%2Fpostgresqlstorage.cpp;h=ba998ea50b67e28426f09c99a954666ef238e218;hp=c37bab432dc459701dee296edaf3e2bd7edbe4d0;hb=46a76ffbf0e9e7af3d4ceb074c0b8dff461b4a86;hpb=aca3263f8f94e0d6640bdf857e71115151b85037 diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index c37bab43..ba998ea5 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -35,7 +35,20 @@ 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 { + qDebug() << QSqlDatabase::drivers(); if(!QSqlDatabase::isDriverAvailable("QPSQL")) return false; return true; } @@ -49,16 +62,30 @@ 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; } +void PostgreSqlStorage::initDbSession(QSqlDatabase &db) { + // this blows... but unfortunately Qt's PG driver forces us to this... + db.exec("set standard_conforming_strings = off"); + db.exec("set escape_string_warning = off"); +} + void PostgreSqlStorage::setConnectionProperties(const QVariantMap &properties) { _userName = properties["Username"].toString(); _password = properties["Password"].toString(); @@ -123,12 +150,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) { @@ -154,6 +182,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")); @@ -297,7 +338,7 @@ IdentityId PostgreSqlStorage::createIdentity(UserId user, CoreIdentity &identity } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::createIdentity(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::createIdentity(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return IdentityId(); } @@ -380,7 +421,7 @@ bool PostgreSqlStorage::updateIdentity(UserId user, const CoreIdentity &identity } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::updateIdentity(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::updateIdentity(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return false; } @@ -509,7 +550,7 @@ NetworkId PostgreSqlStorage::createNetwork(UserId user, const NetworkInfo &info) } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::updateNetwork(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::createNetwork(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return NetworkId(); } @@ -518,7 +559,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)); @@ -527,6 +568,9 @@ void PostgreSqlStorage::bindNetworkInfo(QSqlQuery &query, const NetworkInfo &inf query.bindValue(":useautoidentify", info.useAutoIdentify); query.bindValue(":autoidentifyservice", info.autoIdentifyService); query.bindValue(":autoidentifypassword", info.autoIdentifyPassword); + query.bindValue(":usesasl", info.useSasl); + query.bindValue(":saslaccount", info.saslAccount); + query.bindValue(":saslpassword", info.saslPassword); query.bindValue(":useautoreconnect", info.useAutoReconnect); query.bindValue(":autoreconnectinterval", info.autoReconnectInterval); query.bindValue(":autoreconnectretries", info.autoReconnectRetries); @@ -596,7 +640,7 @@ bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo &info) { } if(!db.commit()) { - qWarning() << "PostgreSqlStorage::updateNetwork(): commiting data failed!"; + qWarning() << "PostgreSqlStorage::updateNetwork(): committing data failed!"; qWarning() << " -" << qPrintable(db.lastError().text()); return false; } @@ -666,6 +710,9 @@ QList PostgreSqlStorage::networks(UserId user) { net.autoReconnectRetries = networksQuery.value(13).toInt(); net.unlimitedReconnectRetries = networksQuery.value(14).toBool(); net.rejoinChannels = networksQuery.value(15).toBool(); + net.useSasl = networksQuery.value(16).toBool(); + net.saslAccount = networksQuery.value(17).toString(); + net.saslPassword = networksQuery.value(18).toString(); serversQuery.bindValue(":networkid", net.networkId.toInt()); safeExec(serversQuery); @@ -866,6 +913,8 @@ BufferInfo PostgreSqlStorage::bufferInfo(UserId user, const NetworkId &networkId createQuery.bindValue(":buffertype", (int)type); createQuery.bindValue(":buffername", buffer); createQuery.bindValue(":buffercname", buffer.toLower()); + createQuery.bindValue(":joined", type & BufferInfo::ChannelBuffer ? true : false); + safeExec(createQuery); if(createQuery.lastError().isValid()) { @@ -971,7 +1020,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..."; @@ -1007,7 +1056,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..."; @@ -1025,7 +1074,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()); @@ -1053,6 +1102,7 @@ bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const Buffer QSqlQuery delBufferQuery(logDb()); delBufferQuery.prepare(queryString("delete_buffer_for_bufferid")); + delBufferQuery.bindValue(":userid", user.toInt()); delBufferQuery.bindValue(":bufferid", bufferId2.toInt()); safeExec(delBufferQuery); if(!watchQuery(delBufferQuery)) { @@ -1102,6 +1152,44 @@ QHash PostgreSqlStorage::bufferLastSeenMsgIds(UserId user) { return lastSeenHash; } +void PostgreSqlStorage::setBufferMarkerLineMsg(UserId user, const BufferId &bufferId, const MsgId &msgId) { + QSqlQuery query(logDb()); + query.prepare(queryString("update_buffer_markerlinemsgid")); + + query.bindValue(":userid", user.toInt()); + query.bindValue(":bufferid", bufferId.toInt()); + query.bindValue(":markerlinemsgid", msgId.toInt()); + safeExec(query); + watchQuery(query); +} + +QHash PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user) { + QHash markerLineHash; + + QSqlDatabase db = logDb(); + if(!beginReadOnlyTransaction(db)) { + qWarning() << "PostgreSqlStorage::bufferMarkerLineMsgIds(): cannot start read only transaction!"; + qWarning() << " -" << qPrintable(db.lastError().text()); + return markerLineHash; + } + + QSqlQuery query(db); + query.prepare(queryString("select_buffer_markerlinemsgids")); + query.bindValue(":userid", user.toInt()); + safeExec(query); + if(!watchQuery(query)) { + db.rollback(); + return markerLineHash; + } + + while(query.next()) { + markerLineHash[query.value(0).toInt()] = query.value(1).toInt(); + } + + db.commit(); + return markerLineHash; +} + bool PostgreSqlStorage::logMessage(Message &msg) { QSqlDatabase db = logDb(); if(!db.transaction()) { @@ -1247,34 +1335,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; } QDateTime timestamp; - for(int i = 0; i < limit && query.next(); i++) { + 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()); + 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; } @@ -1381,7 +1484,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(); @@ -1411,7 +1514,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; @@ -1437,10 +1540,6 @@ void PostgreSqlStorage::deallocateQuery(const QString &handle, const QSqlDatabas db.exec(QString("DEALLOCATE %1").arg(queryId)); } - - - - // ======================================== // PostgreSqlMigrationWriter // ======================================== @@ -1459,6 +1558,7 @@ bool PostgreSqlMigrationWriter::prepareQuery(MigrationObject mo) { query = queryString("migrate_write_sender"); break; case Identity: + _validIdentities.clear(); query = queryString("migrate_write_identity"); break; case IdentityNick: @@ -1501,6 +1601,7 @@ bool PostgreSqlMigrationWriter::writeMo(const SenderMO &sender) { //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); @@ -1538,7 +1639,10 @@ bool PostgreSqlMigrationWriter::writeMo(const NetworkMO &network) { bindValue(0, network.networkid.toInt()); bindValue(1, network.userid.toInt()); bindValue(2, network.networkname); - bindValue(3, network.identityid.toInt()); + 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); @@ -1557,6 +1661,9 @@ bool PostgreSqlMigrationWriter::writeMo(const NetworkMO &network) { bindValue(19, network.awaymessage); bindValue(20, network.attachperform); bindValue(21, network.detachperform); + bindValue(22, network.usesasl); + bindValue(23, network.saslaccount); + bindValue(24, network.saslpassword); return exec(); } @@ -1570,8 +1677,9 @@ bool PostgreSqlMigrationWriter::writeMo(const BufferMO &buffer) { bindValue(5, buffer.buffercname); bindValue(6, (int)buffer.buffertype); bindValue(7, buffer.lastseenmsgid); - bindValue(8, buffer.key); - bindValue(9, buffer.joined); + bindValue(8, buffer.markerlinemsgid); + bindValue(9, buffer.key); + bindValue(10, buffer.joined); return exec(); } @@ -1613,3 +1721,24 @@ bool PostgreSqlMigrationWriter::writeMo(const UserSettingMO &userSetting) { 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; +}