From 533df5c796f22a3fe652920f21d4abc24718b0ea Mon Sep 17 00:00:00 2001 From: Marcus Eggenberger Date: Tue, 1 Feb 2011 23:18:23 +0100 Subject: [PATCH] despammificating the pglog This is done by switching from EAFP to LBYL (although I don't like this...) --- src/core/SQL/PostgreSQL/16/insert_message.sql | 2 +- src/core/SQL/PostgreSQL/16/insert_sender.sql | 3 +- .../SQL/PostgreSQL/16/select_senderid.sql | 3 + src/core/postgresqlstorage.cpp | 90 ++++++++++------ src/core/sql.qrc | 101 +++++++++--------- 5 files changed, 113 insertions(+), 86 deletions(-) create mode 100644 src/core/SQL/PostgreSQL/16/select_senderid.sql diff --git a/src/core/SQL/PostgreSQL/16/insert_message.sql b/src/core/SQL/PostgreSQL/16/insert_message.sql index d35022cd..2013acb8 100644 --- a/src/core/SQL/PostgreSQL/16/insert_message.sql +++ b/src/core/SQL/PostgreSQL/16/insert_message.sql @@ -1,3 +1,3 @@ INSERT INTO backlog (time, bufferid, type, flags, senderid, message) -VALUES ($1, $2, $3, $4, (SELECT senderid FROM sender WHERE sender = $5), $6) +VALUES ($1, $2, $3, $4, $5, $6) RETURNING messageid diff --git a/src/core/SQL/PostgreSQL/16/insert_sender.sql b/src/core/SQL/PostgreSQL/16/insert_sender.sql index 215e6037..f7d89ad2 100644 --- a/src/core/SQL/PostgreSQL/16/insert_sender.sql +++ b/src/core/SQL/PostgreSQL/16/insert_sender.sql @@ -1,2 +1,3 @@ INSERT INTO sender (sender) -VALUES ($1) \ No newline at end of file +VALUES ($1) +RETURNING senderid diff --git a/src/core/SQL/PostgreSQL/16/select_senderid.sql b/src/core/SQL/PostgreSQL/16/select_senderid.sql new file mode 100644 index 00000000..b0b3ed3c --- /dev/null +++ b/src/core/SQL/PostgreSQL/16/select_senderid.sql @@ -0,0 +1,3 @@ +SELECT senderid +FROM sender +WHERE sender = $1 diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index 84e6a05f..2de29604 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -1198,37 +1198,40 @@ bool PostgreSqlStorage::logMessage(Message &msg) { return false; } + QSqlQuery getSenderIdQuery = executePreparedQuery("select_senderid", msg.sender(), db); + int senderId; + if(getSenderIdQuery.first()) { + senderId = getSenderIdQuery.value(0).toInt(); + } else { + // it's possible that the sender was already added by another thread + // since the insert might fail we're setting a savepoint + savePoint("sender_sp1", db); + QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", msg.sender(), db); + + if(addSenderQuery.lastError().isValid()) { + rollbackSavePoint("sender_sp1", db); + getSenderIdQuery = db.exec(getSenderIdQuery.lastQuery()); + getSenderIdQuery.first(); + senderId = getSenderIdQuery.value(0).toInt(); + } else { + releaseSavePoint("sender_sp1", db); + addSenderQuery.first(); + senderId = addSenderQuery.value(0).toInt(); + } + } + QVariantList params; params << msg.timestamp() << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() - << msg.sender() + << senderId << msg.contents(); QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db); - if(logMessageQuery.lastError().isValid()) { - // first we need to reset the transaction + if(!watchQuery(logMessageQuery)) { db.rollback(); - db.transaction(); - - // it's possible that the sender was already added by another thread - // since the insert might fail we're setting a savepoint - savePoint("sender_sp1", db); - QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", msg.sender(), db); - if(addSenderQuery.lastError().isValid()) - rollbackSavePoint("sender_sp1", db); - else - releaseSavePoint("sender_sp1", db); - - logMessageQuery = db.exec(logMessageQuery.lastQuery()); - if(!watchQuery(logMessageQuery)) { - qDebug() << "==================== Sender Query:"; - watchQuery(addSenderQuery); - qDebug() << "==================== /Sender Query"; - db.rollback(); - return false; - } + return false; } logMessageQuery.first(); @@ -1250,19 +1253,38 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { return false; } - QSet senders; + QList senderIdList; + QHash senderIds; + QSqlQuery addSenderQuery; + QSqlQuery selectSenderQuery;; for(int i = 0; i < msgs.count(); i++) { const QString &sender = msgs.at(i).sender(); - if(senders.contains(sender)) + if(senderIds.contains(sender)) { + senderIdList << senderIds[sender]; continue; - senders << sender; + } - savePoint("sender_sp", db); - QSqlQuery addSenderQuery = executePreparedQuery("insert_sender", sender, db); - if(addSenderQuery.lastError().isValid()) - rollbackSavePoint("sender_sp", db); - else - releaseSavePoint("sender_sp", db); + selectSenderQuery = executePreparedQuery("select_senderid", sender, db); + if(selectSenderQuery.first()) { + senderIdList << selectSenderQuery.value(0).toInt(); + senderIds[sender] = selectSenderQuery.value(0).toInt(); + } else { + savePoint("sender_sp", db); + addSenderQuery= executePreparedQuery("insert_sender", sender, db); + if(addSenderQuery.lastError().isValid()) { + // seems it was inserted meanwhile... by a different thread + rollbackSavePoint("sender_sp", db); + selectSenderQuery = db.exec(selectSenderQuery.lastQuery()); + selectSenderQuery.first(); + senderIdList << selectSenderQuery.value(0).toInt(); + senderIds[sender] = selectSenderQuery.value(0).toInt(); + } else { + releaseSavePoint("sender_sp", db); + addSenderQuery.first(); + senderIdList << addSenderQuery.value(0).toInt(); + senderIds[sender] = addSenderQuery.value(0).toInt(); + } + } } // yes we loop twice over the same list. This avoids alternating queries. @@ -1274,7 +1296,7 @@ bool PostgreSqlStorage::logMessages(MessageList &msgs) { << msg.bufferInfo().bufferId().toInt() << msg.type() << (int)msg.flags() - << msg.sender() + << senderIdList.at(i) << msg.contents(); QSqlQuery logMessageQuery = executePreparedQuery("insert_message", params, db); if(!watchQuery(logMessageQuery)) { @@ -1464,8 +1486,8 @@ QSqlQuery PostgreSqlStorage::prepareAndExecuteQuery(const QString &queryname, co } // we alwas execute the query again, even if the query was already prepared. // this ensures, that the error is properly propagated to the calling function - // (otherwise the lasst call would be the test select to pg_prepared_statements - // which always gives a proper result) + // (otherwise the last call would be the testing select to pg_prepared_statements + // which always gives a proper result and the error would be lost) if(paramstring.isNull()) { query = db.exec(QString("EXECUTE quassel_%1").arg(queryname)); } else { diff --git a/src/core/sql.qrc b/src/core/sql.qrc index f713861b..fce06925 100644 --- a/src/core/sql.qrc +++ b/src/core/sql.qrc @@ -32,11 +32,11 @@ ./SQL/PostgreSQL/16/migrate_write_sender.sql ./SQL/PostgreSQL/16/migrate_write_usersetting.sql ./SQL/PostgreSQL/16/select_authuser.sql - ./SQL/PostgreSQL/16/select_bufferByName.sql - ./SQL/PostgreSQL/16/select_bufferExists.sql ./SQL/PostgreSQL/16/select_buffer_by_id.sql ./SQL/PostgreSQL/16/select_buffer_lastseen_messages.sql ./SQL/PostgreSQL/16/select_buffer_markerlinemsgids.sql + ./SQL/PostgreSQL/16/select_bufferByName.sql + ./SQL/PostgreSQL/16/select_bufferExists.sql ./SQL/PostgreSQL/16/select_buffers.sql ./SQL/PostgreSQL/16/select_buffers_for_network.sql ./SQL/PostgreSQL/16/select_checkidentity.sql @@ -48,12 +48,13 @@ ./SQL/PostgreSQL/16/select_messagesAllNew.sql ./SQL/PostgreSQL/16/select_messagesNewerThan.sql ./SQL/PostgreSQL/16/select_messagesRange.sql - ./SQL/PostgreSQL/16/select_networkExists.sql ./SQL/PostgreSQL/16/select_network_awaymsg.sql ./SQL/PostgreSQL/16/select_network_usermode.sql + ./SQL/PostgreSQL/16/select_networkExists.sql ./SQL/PostgreSQL/16/select_networks_for_user.sql ./SQL/PostgreSQL/16/select_nicks.sql ./SQL/PostgreSQL/16/select_persistent_channels.sql + ./SQL/PostgreSQL/16/select_senderid.sql ./SQL/PostgreSQL/16/select_servers_for_network.sql ./SQL/PostgreSQL/16/select_user_setting.sql ./SQL/PostgreSQL/16/select_userid.sql @@ -110,50 +111,6 @@ ./SQL/SQLite/15/upgrade_000_fix_ircservers.sql ./SQL/SQLite/15/upgrade_000_fix_network.sql ./SQL/SQLite/16/upgrade_000_alter_buffer_add_markerlinemsgid.sql - ./SQL/SQLite/2/upgrade_000_drop_buffergroup.sql - ./SQL/SQLite/2/upgrade_010_update_schemaversion.sql - ./SQL/SQLite/3/upgrade_000_update_backlog_flags.sql - ./SQL/SQLite/3/upgrade_010_update_schemaversion.sql - ./SQL/SQLite/4/upgrade_000_rename_buffertable.sql - ./SQL/SQLite/4/upgrade_010_create_buffertable.sql - ./SQL/SQLite/4/upgrade_020_copy_buffertable.sql - ./SQL/SQLite/4/upgrade_030_drop_oldbuffertable.sql - ./SQL/SQLite/4/upgrade_040_create_buffer_idx.sql - ./SQL/SQLite/4/upgrade_050_create_buffer_cname_idx.sql - ./SQL/SQLite/5/upgrade_000_rename_networktable.sql - ./SQL/SQLite/5/upgrade_010_create_newnetworktable.sql - ./SQL/SQLite/5/upgrade_020_copy_networktable.sql - ./SQL/SQLite/5/upgrade_030_drop_oldnetworktable.sql - ./SQL/SQLite/5/upgrade_180_create_ircservers.sql - ./SQL/SQLite/6/upgrade_000_alter_buffertable.sql - ./SQL/SQLite/6/upgrade_010_set_statusbuffertype.sql - ./SQL/SQLite/6/upgrade_020_set_channelbuffertype.sql - ./SQL/SQLite/6/upgrade_030_set_querybuffertype.sql - ./SQL/SQLite/6/upgrade_040_update_msgtype.sql - ./SQL/SQLite/6/upgrade_050_update_msgtype.sql - ./SQL/SQLite/6/upgrade_060_update_msgtype.sql - ./SQL/SQLite/6/upgrade_070_update_msgtype.sql - ./SQL/SQLite/6/upgrade_080_update_msgtype.sql - ./SQL/SQLite/6/upgrade_090_update_msgtype.sql - ./SQL/SQLite/6/upgrade_100_update_msgtype.sql - ./SQL/SQLite/6/upgrade_110_update_msgtype.sql - ./SQL/SQLite/6/upgrade_120_update_msgtype.sql - ./SQL/SQLite/6/upgrade_130_update_msgtype.sql - ./SQL/SQLite/6/upgrade_140_update_msgtype.sql - ./SQL/SQLite/6/upgrade_150_update_msgtype.sql - ./SQL/SQLite/6/upgrade_160_update_msgtype.sql - ./SQL/SQLite/7/upgrade_000_rename_networktable.sql - ./SQL/SQLite/7/upgrade_010_create_newnetworktable.sql - ./SQL/SQLite/7/upgrade_020_copy_networktable.sql - ./SQL/SQLite/7/upgrade_030_drop_oldnetworktable.sql - ./SQL/SQLite/7/upgrade_040_alter_buffer_add_lastseen.sql - ./SQL/SQLite/8/upgrade_000_alter_network_add_connected.sql - ./SQL/SQLite/8/upgrade_010_alter_buffer_add_key.sql - ./SQL/SQLite/8/upgrade_020_alter_buffer_add_joined.sql - ./SQL/SQLite/8/upgrade_030_update_buffer_set_joined_for_channels.sql - ./SQL/SQLite/9/upgrade_000_create_backlog_idx.sql - ./SQL/SQLite/9/upgrade_010_create_backlog_idx2.sql - ./SQL/SQLite/9/upgrade_020_create_buffer_idx.sql ./SQL/SQLite/17/delete_backlog_by_uid.sql ./SQL/SQLite/17/delete_backlog_for_buffer.sql ./SQL/SQLite/17/delete_backlog_for_network.sql @@ -185,11 +142,11 @@ ./SQL/SQLite/17/migrate_read_sender.sql ./SQL/SQLite/17/migrate_read_usersetting.sql ./SQL/SQLite/17/select_authuser.sql - ./SQL/SQLite/17/select_bufferByName.sql - ./SQL/SQLite/17/select_bufferExists.sql ./SQL/SQLite/17/select_buffer_by_id.sql ./SQL/SQLite/17/select_buffer_lastseen_messages.sql ./SQL/SQLite/17/select_buffer_markerlinemsgids.sql + ./SQL/SQLite/17/select_bufferByName.sql + ./SQL/SQLite/17/select_bufferExists.sql ./SQL/SQLite/17/select_buffers.sql ./SQL/SQLite/17/select_buffers_for_merge.sql ./SQL/SQLite/17/select_buffers_for_network.sql @@ -202,9 +159,9 @@ ./SQL/SQLite/17/select_messagesAllNew.sql ./SQL/SQLite/17/select_messagesNewerThan.sql ./SQL/SQLite/17/select_messagesNewestK.sql - ./SQL/SQLite/17/select_networkExists.sql ./SQL/SQLite/17/select_network_awaymsg.sql ./SQL/SQLite/17/select_network_usermode.sql + ./SQL/SQLite/17/select_networkExists.sql ./SQL/SQLite/17/select_networks_for_user.sql ./SQL/SQLite/17/select_nicks.sql ./SQL/SQLite/17/select_persistent_channels.sql @@ -243,5 +200,49 @@ ./SQL/SQLite/17/upgrade_000_alter_network_add_sasl.sql ./SQL/SQLite/17/upgrade_001_alter_network_add_sasl.sql ./SQL/SQLite/17/upgrade_002_alter_network_add_sasl.sql + ./SQL/SQLite/2/upgrade_000_drop_buffergroup.sql + ./SQL/SQLite/2/upgrade_010_update_schemaversion.sql + ./SQL/SQLite/3/upgrade_000_update_backlog_flags.sql + ./SQL/SQLite/3/upgrade_010_update_schemaversion.sql + ./SQL/SQLite/4/upgrade_000_rename_buffertable.sql + ./SQL/SQLite/4/upgrade_010_create_buffertable.sql + ./SQL/SQLite/4/upgrade_020_copy_buffertable.sql + ./SQL/SQLite/4/upgrade_030_drop_oldbuffertable.sql + ./SQL/SQLite/4/upgrade_040_create_buffer_idx.sql + ./SQL/SQLite/4/upgrade_050_create_buffer_cname_idx.sql + ./SQL/SQLite/5/upgrade_000_rename_networktable.sql + ./SQL/SQLite/5/upgrade_010_create_newnetworktable.sql + ./SQL/SQLite/5/upgrade_020_copy_networktable.sql + ./SQL/SQLite/5/upgrade_030_drop_oldnetworktable.sql + ./SQL/SQLite/5/upgrade_180_create_ircservers.sql + ./SQL/SQLite/6/upgrade_000_alter_buffertable.sql + ./SQL/SQLite/6/upgrade_010_set_statusbuffertype.sql + ./SQL/SQLite/6/upgrade_020_set_channelbuffertype.sql + ./SQL/SQLite/6/upgrade_030_set_querybuffertype.sql + ./SQL/SQLite/6/upgrade_040_update_msgtype.sql + ./SQL/SQLite/6/upgrade_050_update_msgtype.sql + ./SQL/SQLite/6/upgrade_060_update_msgtype.sql + ./SQL/SQLite/6/upgrade_070_update_msgtype.sql + ./SQL/SQLite/6/upgrade_080_update_msgtype.sql + ./SQL/SQLite/6/upgrade_090_update_msgtype.sql + ./SQL/SQLite/6/upgrade_100_update_msgtype.sql + ./SQL/SQLite/6/upgrade_110_update_msgtype.sql + ./SQL/SQLite/6/upgrade_120_update_msgtype.sql + ./SQL/SQLite/6/upgrade_130_update_msgtype.sql + ./SQL/SQLite/6/upgrade_140_update_msgtype.sql + ./SQL/SQLite/6/upgrade_150_update_msgtype.sql + ./SQL/SQLite/6/upgrade_160_update_msgtype.sql + ./SQL/SQLite/7/upgrade_000_rename_networktable.sql + ./SQL/SQLite/7/upgrade_010_create_newnetworktable.sql + ./SQL/SQLite/7/upgrade_020_copy_networktable.sql + ./SQL/SQLite/7/upgrade_030_drop_oldnetworktable.sql + ./SQL/SQLite/7/upgrade_040_alter_buffer_add_lastseen.sql + ./SQL/SQLite/8/upgrade_000_alter_network_add_connected.sql + ./SQL/SQLite/8/upgrade_010_alter_buffer_add_key.sql + ./SQL/SQLite/8/upgrade_020_alter_buffer_add_joined.sql + ./SQL/SQLite/8/upgrade_030_update_buffer_set_joined_for_channels.sql + ./SQL/SQLite/9/upgrade_000_create_backlog_idx.sql + ./SQL/SQLite/9/upgrade_010_create_backlog_idx2.sql + ./SQL/SQLite/9/upgrade_020_create_buffer_idx.sql -- 2.20.1