From: Marcus Eggenberger Date: Sat, 21 Feb 2009 17:02:51 +0000 (+0100) Subject: tweakinating X-Git-Tag: 0.5-rc1~334 X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=41f90ea96aad92b534a982296168baff8b8da2d7 tweakinating --- diff --git a/src/core/SQL/PostgreSQL/14/delete_buffer_for_bufferid.sql b/src/core/SQL/PostgreSQL/14/delete_buffer_for_bufferid.sql index 91dfeb97..120213b0 100644 --- a/src/core/SQL/PostgreSQL/14/delete_buffer_for_bufferid.sql +++ b/src/core/SQL/PostgreSQL/14/delete_buffer_for_bufferid.sql @@ -1,2 +1,2 @@ DELETE FROM buffer -WHERE bufferid = :bufferid +WHERE userid = :userid AND bufferid = :bufferid diff --git a/src/core/SQL/PostgreSQL/14/delete_network.sql b/src/core/SQL/PostgreSQL/14/delete_network.sql index 7200ee4d..fe17b8b8 100644 --- a/src/core/SQL/PostgreSQL/14/delete_network.sql +++ b/src/core/SQL/PostgreSQL/14/delete_network.sql @@ -1,2 +1,2 @@ DELETE FROM network -WHERE networkid = :networkid +WHERE userid = :userid AND networkid = :networkid diff --git a/src/core/SQL/PostgreSQL/14/select_messages.sql b/src/core/SQL/PostgreSQL/14/select_messages.sql index 641179f2..993f89d1 100644 --- a/src/core/SQL/PostgreSQL/14/select_messages.sql +++ b/src/core/SQL/PostgreSQL/14/select_messages.sql @@ -2,7 +2,4 @@ SELECT messageid, time, type, flags, sender, message FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = :bufferid - AND backlog.messageid >= :firstmsg - AND backlog.messageid < :lastmsg ORDER BY messageid DESC -LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/14/select_messagesAll.sql b/src/core/SQL/PostgreSQL/14/select_messagesAll.sql index 52285aff..10009c30 100644 --- a/src/core/SQL/PostgreSQL/14/select_messagesAll.sql +++ b/src/core/SQL/PostgreSQL/14/select_messagesAll.sql @@ -5,4 +5,3 @@ WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) AND backlog.messageid >= :firstmsg AND backlog.messageid < :lastmsg ORDER BY messageid DESC -LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/14/select_messagesAllNew.sql b/src/core/SQL/PostgreSQL/14/select_messagesAllNew.sql index a29f094b..b810f0d9 100644 --- a/src/core/SQL/PostgreSQL/14/select_messagesAllNew.sql +++ b/src/core/SQL/PostgreSQL/14/select_messagesAllNew.sql @@ -4,4 +4,3 @@ JOIN sender ON backlog.senderid = sender.senderid WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) AND backlog.messageid >= :firstmsg ORDER BY messageid DESC -LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql b/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql index 69d948d6..865bd534 100644 --- a/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql +++ b/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql @@ -4,4 +4,3 @@ JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = :bufferid AND backlog.messageid >= :firstmsg ORDER BY messageid DESC -LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/14/select_messagesNewestK.sql b/src/core/SQL/PostgreSQL/14/select_messagesRange.sql similarity index 68% rename from src/core/SQL/PostgreSQL/14/select_messagesNewestK.sql rename to src/core/SQL/PostgreSQL/14/select_messagesRange.sql index 6cc81cc5..9e596dda 100644 --- a/src/core/SQL/PostgreSQL/14/select_messagesNewestK.sql +++ b/src/core/SQL/PostgreSQL/14/select_messagesRange.sql @@ -2,5 +2,6 @@ SELECT messageid, time, type, flags, sender, message FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = :bufferid + AND backlog.messageid >= :firstmsg + AND backlog.messageid < :lastmsg ORDER BY messageid DESC -LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/14/update_buffer_name.sql b/src/core/SQL/PostgreSQL/14/update_buffer_name.sql index 14d65e7a..c6cc6d77 100644 --- a/src/core/SQL/PostgreSQL/14/update_buffer_name.sql +++ b/src/core/SQL/PostgreSQL/14/update_buffer_name.sql @@ -1,3 +1,3 @@ UPDATE buffer SET buffername = :buffername, buffercname = :buffercname -WHERE bufferid = :bufferid +WHERE userid = :userid AND bufferid = :bufferid diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index 56be64ef..2c9c5b44 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -194,7 +194,7 @@ void PostgreSqlStorage::setUserSetting(UserId userId, const QString &settingName out.setVersion(QDataStream::Qt_4_2); out << data; - QSqlDatabase db = logDb(); + QSqlDatabase db = logDb(); QSqlQuery query(db); query.prepare(queryString("insert_user_setting")); query.bindValue(":userid", userId.toInt()); @@ -313,7 +313,7 @@ bool PostgreSqlStorage::updateIdentity(UserId user, const CoreIdentity &identity qWarning() << " -" << qPrintable(db.lastError().text()); return false; } - + QSqlQuery checkQuery(db); checkQuery.prepare(queryString("select_checkidentity")); checkQuery.bindValue(":identityid", identity.id().toInt()); @@ -359,7 +359,7 @@ bool PostgreSqlStorage::updateIdentity(UserId user, const CoreIdentity &identity db.rollback(); return false; } - + QSqlQuery deleteNickQuery(db); deleteNickQuery.prepare(queryString("delete_nicks")); deleteNickQuery.bindValue(":identityid", identity.id().toInt()); @@ -553,9 +553,6 @@ void PostgreSqlStorage::bindServerInfo(QSqlQuery &query, const Network::Server & } bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo &info) { - if(!isValidNetwork(user, info.networkId)) - return false; - QSqlDatabase db = logDb(); if(!db.transaction()) { qWarning() << "PostgreSqlStorage::updateNetwork(): failed to begin transaction!"; @@ -565,12 +562,18 @@ bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo &info) { QSqlQuery updateQuery(db); updateQuery.prepare(queryString("update_network")); + updateQuery.bindValue(":userid", user.toInt()); bindNetworkInfo(updateQuery, info); safeExec(updateQuery); if(!watchQuery(updateQuery)) { db.rollback(); return false; } + if(updateQuery.numRowsAffected() != 1) { + // seems this is not our network... + db.rollback(); + return false; + } QSqlQuery dropServersQuery(db); dropServersQuery.prepare("DELETE FROM ircserver WHERE networkid = :networkid"); @@ -603,9 +606,6 @@ bool PostgreSqlStorage::updateNetwork(UserId user, const NetworkInfo &info) { } bool PostgreSqlStorage::removeNetwork(UserId user, const NetworkId &networkId) { - if(!isValidNetwork(user, networkId)) - return false; - QSqlDatabase db = logDb(); if(!db.transaction()) { qWarning() << "PostgreSqlStorage::removeNetwork(): cannot start transaction!"; @@ -615,6 +615,7 @@ bool PostgreSqlStorage::removeNetwork(UserId user, const NetworkId &networkId) { QSqlQuery query(db); query.prepare(queryString("delete_network")); + query.bindValue(":userid", user.toInt()); query.bindValue(":networkid", networkId.toInt()); safeExec(query); if(!watchQuery(query)) { @@ -635,7 +636,7 @@ QList PostgreSqlStorage::networks(UserId user) { qWarning() << " -" << qPrintable(db.lastError().text()); return nets; } - + QSqlQuery networksQuery(db); networksQuery.prepare(queryString("select_networks_for_user")); networksQuery.bindValue(":userid", user.toInt()); @@ -698,36 +699,6 @@ QList PostgreSqlStorage::networks(UserId user) { return nets; } -bool PostgreSqlStorage::isValidNetwork(UserId user, const NetworkId &networkId) { - QSqlQuery query(logDb()); - query.prepare(queryString("select_networkExists")); - query.bindValue(":userid", user.toInt()); - query.bindValue(":networkid", networkId.toInt()); - safeExec(query); - - watchQuery(query); - if(!query.first()) - return false; - - Q_ASSERT(!query.next()); - return true; -} - -bool PostgreSqlStorage::isValidBuffer(const UserId &user, const BufferId &bufferId) { - QSqlQuery query(logDb()); - query.prepare(queryString("select_bufferExists")); - query.bindValue(":userid", user.toInt()); - query.bindValue(":bufferid", bufferId.toInt()); - safeExec(query); - - watchQuery(query); - if(!query.first()) - return false; - - Q_ASSERT(!query.next()); - return true; -} - QList PostgreSqlStorage::connectedNetworks(UserId user) { QList connectedNets; @@ -873,7 +844,7 @@ BufferInfo PostgreSqlStorage::bufferInfo(UserId user, const NetworkId &networkId if(query.first()) { BufferInfo bufferInfo = BufferInfo(query.value(0).toInt(), networkId, (BufferInfo::Type)query.value(1).toInt(), 0, buffer); if(query.next()) { - qCritical() << "PostgreSqlStorage::getBufferInfo(): received more then one Buffer!"; + qCritical() << "PostgreSqlStorage::bufferInfo(): received more then one Buffer!"; qCritical() << " Query:" << query.lastQuery(); qCritical() << " bound Values:"; QList list = query.boundValues().values(); @@ -940,7 +911,7 @@ QList PostgreSqlStorage::requestBuffers(UserId user) { qWarning() << " -" << qPrintable(db.lastError().text()); return bufferlist; } - + QSqlQuery query(db); query.prepare(queryString("select_buffers")); query.bindValue(":userid", user.toInt()); @@ -963,7 +934,7 @@ QList PostgreSqlStorage::requestBufferIdsForNetwork(UserId user, Netwo qWarning() << " -" << qPrintable(db.lastError().text()); return bufferList; } - + QSqlQuery query(db); query.prepare(queryString("select_buffers_for_network")); query.bindValue(":networkid", networkId.toInt()); @@ -979,9 +950,6 @@ QList PostgreSqlStorage::requestBufferIdsForNetwork(UserId user, Netwo } bool PostgreSqlStorage::removeBuffer(const UserId &user, const BufferId &bufferId) { - if(!isValidBuffer(user, bufferId)) - return false; - QSqlDatabase db = logDb(); if(!db.transaction()) { qWarning() << "PostgreSqlStorage::removeBuffer(): cannot start transaction!"; @@ -990,36 +958,67 @@ bool PostgreSqlStorage::removeBuffer(const UserId &user, const BufferId &bufferI QSqlQuery query(db); query.prepare(queryString("delete_buffer_for_bufferid")); + query.bindValue(":userid", user.toInt()); query.bindValue(":bufferid", bufferId.toInt()); safeExec(query); if(!watchQuery(query)) { db.rollback(); return false; } - db.commit(); - return true; + + int numRows = query.numRowsAffected(); + switch(numRows) { + case 0: + db.commit(); + return false; + case 1: + db.commit(); + return false; + default: + // there was more then one buffer deleted... + qWarning() << "PostgreSqlStorage::removeBuffer(): Userid" << user << "BufferId" << "caused deletion of" << numRows << "Buffers! Rolling back transaction..."; + db.rollback(); + return false; + } } bool PostgreSqlStorage::renameBuffer(const UserId &user, const BufferId &bufferId, const QString &newName) { - if(!isValidBuffer(user, bufferId)) + QSqlDatabase db = logDb(); + if(!db.transaction()) { + qWarning() << "PostgreSqlStorage::renameBuffer(): cannot start transaction!"; return false; + } - QSqlQuery query(logDb()); + QSqlQuery query(db); query.prepare(queryString("update_buffer_name")); query.bindValue(":buffername", newName); query.bindValue(":buffercname", newName.toLower()); + query.bindValue(":userid", user.toInt()); query.bindValue(":bufferid", bufferId.toInt()); safeExec(query); if(query.lastError().isValid()) { + watchQuery(query); + db.rollback(); return false; } - return true; -} -bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const BufferId &bufferId1, const BufferId &bufferId2) { - if(!isValidBuffer(user, bufferId1) || !isValidBuffer(user, bufferId2)) + int numRows = query.numRowsAffected(); + switch(numRows) { + case 0: + db.commit(); + return false; + case 1: + db.commit(); + return false; + default: + // there was more then one buffer deleted... + qWarning() << "PostgreSqlStorage::renameBuffer(): Userid" << user << "BufferId" << "affected" << numRows << "Buffers! Rolling back transaction..."; + db.rollback(); return false; + } +} +bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const BufferId &bufferId1, const BufferId &bufferId2) { QSqlDatabase db = logDb(); if(!db.transaction()) { qWarning() << "PostgreSqlStorage::mergeBuffersPermanently(): cannot start transaction!"; @@ -1027,6 +1026,23 @@ bool PostgreSqlStorage::mergeBuffersPermanently(const UserId &user, const Buffer return false; } + QSqlQuery checkQuery(db); + checkQuery.prepare("SELECT count(*) FROM buffer" + "WHERE userid = :userid AND bufferid IN (:buffer1, :buffer2)"); + checkQuery.bindValue(":userid", user.toInt()); + checkQuery.bindValue(":buffer1", bufferId1.toInt()); + checkQuery.bindValue(":buffer2", bufferId2.toInt()); + safeExec(checkQuery); + if(!watchQuery(checkQuery)) { + db.rollback(); + return false; + } + checkQuery.first(); + if(checkQuery.value(0).toInt() != 2) { + db.rollback(); + return false; + } + QSqlQuery query(db); query.prepare(queryString("update_backlog_bufferid")); query.bindValue(":oldbufferid", bufferId2.toInt()); @@ -1109,7 +1125,7 @@ MsgId PostgreSqlStorage::logMessage(Message msg) { if(logMessageQuery.lastError().isValid()) { // first we need to reset the transaction db.rollback(); - db.transaction(); + db.transaction(); QSqlQuery addSenderQuery(db); addSenderQuery.prepare(queryString("insert_sender")); @@ -1134,30 +1150,38 @@ MsgId PostgreSqlStorage::logMessage(Message msg) { QList PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit) { QList messagelist; + QSqlDatabase db = logDb(); + if(!beginReadOnlyTransaction(db)) { + qWarning() << "PostgreSqlStorage::requestMsgs(): cannot start read only transaction!"; + qWarning() << " -" << qPrintable(db.lastError().text()); + return messagelist; + } + BufferInfo bufferInfo = getBufferInfo(user, bufferId); - if(!bufferInfo.isValid()) + if(!bufferInfo.isValid()) { + db.rollback(); return messagelist; + } - QSqlQuery query(logDb()); - + QSqlQuery query(db); if(last == -1 && first == -1) { - query.prepare(queryString("select_messagesNewestK")); + query.prepare(queryString("select_messages")); } else if(last == -1) { query.prepare(queryString("select_messagesNewerThan")); query.bindValue(":firstmsg", first.toInt()); } else { - query.prepare(queryString("select_messages")); + query.prepare(queryString("select_messagesRange")); query.bindValue(":lastmsg", last.toInt()); query.bindValue(":firstmsg", first.toInt()); } - query.bindValue(":bufferid", bufferId.toInt()); - query.bindValue(":limit", limit); safeExec(query); + if(!watchQuery(query)) { + db.rollback(); + return messagelist; + } - watchQuery(query); - - while(query.next()) { + 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(), @@ -1167,18 +1191,28 @@ QList PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, Ms msg.setMsgId(query.value(0).toInt()); messagelist << msg; } + + db.commit(); return messagelist; } QList PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit) { QList messagelist; + // requestBuffers uses it's own transaction. QHash bufferInfoHash; foreach(BufferInfo bufferInfo, requestBuffers(user)) { bufferInfoHash[bufferInfo.bufferId()] = bufferInfo; } - QSqlQuery query(logDb()); + QSqlDatabase db = logDb(); + if(!beginReadOnlyTransaction(db)) { + qWarning() << "PostgreSqlStorage::requestAllMsgs(): cannot start read only transaction!"; + qWarning() << " -" << qPrintable(db.lastError().text()); + return messagelist; + } + + QSqlQuery query(db); if(last == -1) { query.prepare(queryString("select_messagesAllNew")); } else { @@ -1187,12 +1221,13 @@ QList PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId } query.bindValue(":userid", user.toInt()); query.bindValue(":firstmsg", first.toInt()); - query.bindValue(":limit", limit); safeExec(query); + if(!watchQuery(query)) { + db.rollback(); + return messagelist; + } - watchQuery(query); - - while(query.next()) { + for(int i = 0; i < limit && query.next(); i++) { Message msg(QDateTime::fromTime_t(query.value(2).toInt()), bufferInfoHash[query.value(1).toInt()], (Message::Type)query.value(3).toUInt(), @@ -1203,6 +1238,7 @@ QList PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId messagelist << msg; } + db.commit(); return messagelist; } diff --git a/src/core/postgresqlstorage.h b/src/core/postgresqlstorage.h index d83b4cc5..536fa61f 100644 --- a/src/core/postgresqlstorage.h +++ b/src/core/postgresqlstorage.h @@ -111,9 +111,6 @@ protected: bool beginReadOnlyTransaction(QSqlDatabase &db); private: - bool isValidNetwork(UserId user, const NetworkId &networkId); - bool isValidBuffer(const UserId &user, const BufferId &bufferId); - void bindNetworkInfo(QSqlQuery &query, const NetworkInfo &info); void bindServerInfo(QSqlQuery &query, const Network::Server &server); diff --git a/src/core/sql.qrc b/src/core/sql.qrc index 2890a0ec..e373055f 100644 --- a/src/core/sql.qrc +++ b/src/core/sql.qrc @@ -36,7 +36,7 @@ ./SQL/PostgreSQL/14/select_messagesAll.sql ./SQL/PostgreSQL/14/select_messagesAllNew.sql ./SQL/PostgreSQL/14/select_messagesNewerThan.sql - ./SQL/PostgreSQL/14/select_messagesNewestK.sql + ./SQL/PostgreSQL/14/select_messagesRange.sql ./SQL/PostgreSQL/14/select_network_awaymsg.sql ./SQL/PostgreSQL/14/select_network_usermode.sql ./SQL/PostgreSQL/14/select_networkExists.sql