despammificating the pglog

This is done by switching from EAFP to LBYL (although I don't like this...)

diff --git a/src/core/SQL/PostgreSQL/16/insert_message.sql b/src/core/SQL/PostgreSQL/16/insert_message.sql
index d35022c..2013acb 100644 (file)
--- 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 215e603..f7d89ad 100644 (file)
--- 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 (file)
index 0000000..b0b3ed3
--- /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 84e6a05..2de2960 100644 (file)
--- 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<QString> senders;
+  QList<int> senderIdList;
+  QHash<QString, int> 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 f713861..fce0692 100644 (file)
--- a/src/core/sql.qrc
+++ b/src/core/sql.qrc
@@ -32,11 +32,11 @@
     <file>./SQL/PostgreSQL/16/migrate_write_sender.sql</file>
     <file>./SQL/PostgreSQL/16/migrate_write_usersetting.sql</file>
     <file>./SQL/PostgreSQL/16/select_authuser.sql</file>
-    <file>./SQL/PostgreSQL/16/select_bufferByName.sql</file>
-    <file>./SQL/PostgreSQL/16/select_bufferExists.sql</file>
     <file>./SQL/PostgreSQL/16/select_buffer_by_id.sql</file>
     <file>./SQL/PostgreSQL/16/select_buffer_lastseen_messages.sql</file>
     <file>./SQL/PostgreSQL/16/select_buffer_markerlinemsgids.sql</file>
+    <file>./SQL/PostgreSQL/16/select_bufferByName.sql</file>
+    <file>./SQL/PostgreSQL/16/select_bufferExists.sql</file>
     <file>./SQL/PostgreSQL/16/select_buffers.sql</file>
     <file>./SQL/PostgreSQL/16/select_buffers_for_network.sql</file>
     <file>./SQL/PostgreSQL/16/select_checkidentity.sql</file>
@@ -48,12 +48,13 @@
     <file>./SQL/PostgreSQL/16/select_messagesAllNew.sql</file>
     <file>./SQL/PostgreSQL/16/select_messagesNewerThan.sql</file>
     <file>./SQL/PostgreSQL/16/select_messagesRange.sql</file>
-    <file>./SQL/PostgreSQL/16/select_networkExists.sql</file>
     <file>./SQL/PostgreSQL/16/select_network_awaymsg.sql</file>
     <file>./SQL/PostgreSQL/16/select_network_usermode.sql</file>
+    <file>./SQL/PostgreSQL/16/select_networkExists.sql</file>
     <file>./SQL/PostgreSQL/16/select_networks_for_user.sql</file>
     <file>./SQL/PostgreSQL/16/select_nicks.sql</file>
     <file>./SQL/PostgreSQL/16/select_persistent_channels.sql</file>
+    <file>./SQL/PostgreSQL/16/select_senderid.sql</file>
     <file>./SQL/PostgreSQL/16/select_servers_for_network.sql</file>
     <file>./SQL/PostgreSQL/16/select_user_setting.sql</file>
     <file>./SQL/PostgreSQL/16/select_userid.sql</file>
@@ -110,50 +111,6 @@
     <file>./SQL/SQLite/15/upgrade_000_fix_ircservers.sql</file>
     <file>./SQL/SQLite/15/upgrade_000_fix_network.sql</file>
     <file>./SQL/SQLite/16/upgrade_000_alter_buffer_add_markerlinemsgid.sql</file>
-    <file>./SQL/SQLite/2/upgrade_000_drop_buffergroup.sql</file>
-    <file>./SQL/SQLite/2/upgrade_010_update_schemaversion.sql</file>
-    <file>./SQL/SQLite/3/upgrade_000_update_backlog_flags.sql</file>
-    <file>./SQL/SQLite/3/upgrade_010_update_schemaversion.sql</file>
-    <file>./SQL/SQLite/4/upgrade_000_rename_buffertable.sql</file>
-    <file>./SQL/SQLite/4/upgrade_010_create_buffertable.sql</file>
-    <file>./SQL/SQLite/4/upgrade_020_copy_buffertable.sql</file>
-    <file>./SQL/SQLite/4/upgrade_030_drop_oldbuffertable.sql</file>
-    <file>./SQL/SQLite/4/upgrade_040_create_buffer_idx.sql</file>
-    <file>./SQL/SQLite/4/upgrade_050_create_buffer_cname_idx.sql</file>
-    <file>./SQL/SQLite/5/upgrade_000_rename_networktable.sql</file>
-    <file>./SQL/SQLite/5/upgrade_010_create_newnetworktable.sql</file>
-    <file>./SQL/SQLite/5/upgrade_020_copy_networktable.sql</file>
-    <file>./SQL/SQLite/5/upgrade_030_drop_oldnetworktable.sql</file>
-    <file>./SQL/SQLite/5/upgrade_180_create_ircservers.sql</file>
-    <file>./SQL/SQLite/6/upgrade_000_alter_buffertable.sql</file>
-    <file>./SQL/SQLite/6/upgrade_010_set_statusbuffertype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_020_set_channelbuffertype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_030_set_querybuffertype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_040_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_050_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_060_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_070_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_080_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_090_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_100_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_110_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_120_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_130_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_140_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_150_update_msgtype.sql</file>
-    <file>./SQL/SQLite/6/upgrade_160_update_msgtype.sql</file>
-    <file>./SQL/SQLite/7/upgrade_000_rename_networktable.sql</file>
-    <file>./SQL/SQLite/7/upgrade_010_create_newnetworktable.sql</file>
-    <file>./SQL/SQLite/7/upgrade_020_copy_networktable.sql</file>
-    <file>./SQL/SQLite/7/upgrade_030_drop_oldnetworktable.sql</file>
-    <file>./SQL/SQLite/7/upgrade_040_alter_buffer_add_lastseen.sql</file>
-    <file>./SQL/SQLite/8/upgrade_000_alter_network_add_connected.sql</file>
-    <file>./SQL/SQLite/8/upgrade_010_alter_buffer_add_key.sql</file>
-    <file>./SQL/SQLite/8/upgrade_020_alter_buffer_add_joined.sql</file>
-    <file>./SQL/SQLite/8/upgrade_030_update_buffer_set_joined_for_channels.sql</file>
-    <file>./SQL/SQLite/9/upgrade_000_create_backlog_idx.sql</file>
-    <file>./SQL/SQLite/9/upgrade_010_create_backlog_idx2.sql</file>
-    <file>./SQL/SQLite/9/upgrade_020_create_buffer_idx.sql</file>
     <file>./SQL/SQLite/17/delete_backlog_by_uid.sql</file>
     <file>./SQL/SQLite/17/delete_backlog_for_buffer.sql</file>
     <file>./SQL/SQLite/17/delete_backlog_for_network.sql</file>
@@ -185,11 +142,11 @@
     <file>./SQL/SQLite/17/migrate_read_sender.sql</file>
     <file>./SQL/SQLite/17/migrate_read_usersetting.sql</file>
     <file>./SQL/SQLite/17/select_authuser.sql</file>
-    <file>./SQL/SQLite/17/select_bufferByName.sql</file>
-    <file>./SQL/SQLite/17/select_bufferExists.sql</file>
     <file>./SQL/SQLite/17/select_buffer_by_id.sql</file>
     <file>./SQL/SQLite/17/select_buffer_lastseen_messages.sql</file>
     <file>./SQL/SQLite/17/select_buffer_markerlinemsgids.sql</file>
+    <file>./SQL/SQLite/17/select_bufferByName.sql</file>
+    <file>./SQL/SQLite/17/select_bufferExists.sql</file>
     <file>./SQL/SQLite/17/select_buffers.sql</file>
     <file>./SQL/SQLite/17/select_buffers_for_merge.sql</file>
     <file>./SQL/SQLite/17/select_buffers_for_network.sql</file>
@@ -202,9 +159,9 @@
     <file>./SQL/SQLite/17/select_messagesAllNew.sql</file>
     <file>./SQL/SQLite/17/select_messagesNewerThan.sql</file>
     <file>./SQL/SQLite/17/select_messagesNewestK.sql</file>
-    <file>./SQL/SQLite/17/select_networkExists.sql</file>
     <file>./SQL/SQLite/17/select_network_awaymsg.sql</file>
     <file>./SQL/SQLite/17/select_network_usermode.sql</file>
+    <file>./SQL/SQLite/17/select_networkExists.sql</file>
     <file>./SQL/SQLite/17/select_networks_for_user.sql</file>
     <file>./SQL/SQLite/17/select_nicks.sql</file>
     <file>./SQL/SQLite/17/select_persistent_channels.sql</file>
@@ -243,5 +200,49 @@
     <file>./SQL/SQLite/17/upgrade_000_alter_network_add_sasl.sql</file>
     <file>./SQL/SQLite/17/upgrade_001_alter_network_add_sasl.sql</file>
     <file>./SQL/SQLite/17/upgrade_002_alter_network_add_sasl.sql</file>
+    <file>./SQL/SQLite/2/upgrade_000_drop_buffergroup.sql</file>
+    <file>./SQL/SQLite/2/upgrade_010_update_schemaversion.sql</file>
+    <file>./SQL/SQLite/3/upgrade_000_update_backlog_flags.sql</file>
+    <file>./SQL/SQLite/3/upgrade_010_update_schemaversion.sql</file>
+    <file>./SQL/SQLite/4/upgrade_000_rename_buffertable.sql</file>
+    <file>./SQL/SQLite/4/upgrade_010_create_buffertable.sql</file>
+    <file>./SQL/SQLite/4/upgrade_020_copy_buffertable.sql</file>
+    <file>./SQL/SQLite/4/upgrade_030_drop_oldbuffertable.sql</file>
+    <file>./SQL/SQLite/4/upgrade_040_create_buffer_idx.sql</file>
+    <file>./SQL/SQLite/4/upgrade_050_create_buffer_cname_idx.sql</file>
+    <file>./SQL/SQLite/5/upgrade_000_rename_networktable.sql</file>
+    <file>./SQL/SQLite/5/upgrade_010_create_newnetworktable.sql</file>
+    <file>./SQL/SQLite/5/upgrade_020_copy_networktable.sql</file>
+    <file>./SQL/SQLite/5/upgrade_030_drop_oldnetworktable.sql</file>
+    <file>./SQL/SQLite/5/upgrade_180_create_ircservers.sql</file>
+    <file>./SQL/SQLite/6/upgrade_000_alter_buffertable.sql</file>
+    <file>./SQL/SQLite/6/upgrade_010_set_statusbuffertype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_020_set_channelbuffertype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_030_set_querybuffertype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_040_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_050_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_060_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_070_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_080_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_090_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_100_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_110_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_120_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_130_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_140_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_150_update_msgtype.sql</file>
+    <file>./SQL/SQLite/6/upgrade_160_update_msgtype.sql</file>
+    <file>./SQL/SQLite/7/upgrade_000_rename_networktable.sql</file>
+    <file>./SQL/SQLite/7/upgrade_010_create_newnetworktable.sql</file>
+    <file>./SQL/SQLite/7/upgrade_020_copy_networktable.sql</file>
+    <file>./SQL/SQLite/7/upgrade_030_drop_oldnetworktable.sql</file>
+    <file>./SQL/SQLite/7/upgrade_040_alter_buffer_add_lastseen.sql</file>
+    <file>./SQL/SQLite/8/upgrade_000_alter_network_add_connected.sql</file>
+    <file>./SQL/SQLite/8/upgrade_010_alter_buffer_add_key.sql</file>
+    <file>./SQL/SQLite/8/upgrade_020_alter_buffer_add_joined.sql</file>
+    <file>./SQL/SQLite/8/upgrade_030_update_buffer_set_joined_for_channels.sql</file>
+    <file>./SQL/SQLite/9/upgrade_000_create_backlog_idx.sql</file>
+    <file>./SQL/SQLite/9/upgrade_010_create_backlog_idx2.sql</file>
+    <file>./SQL/SQLite/9/upgrade_020_create_buffer_idx.sql</file>
 </qresource>
 </RCC>