Postgres tweaks

diff --git a/src/core/SQL/PostgreSQL/14/select_messages.sql b/src/core/SQL/PostgreSQL/14/select_messages.sql
index 993f89d..eae93cf 100644 (file)
--- a/src/core/SQL/PostgreSQL/14/select_messages.sql
+++ b/src/core/SQL/PostgreSQL/14/select_messages.sql
@@ -1,5 +1,6 @@
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
 JOIN sender ON backlog.senderid = sender.senderid
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
 JOIN sender ON backlog.senderid = sender.senderid

-WHERE bufferid = :bufferid
+WHERE bufferid = $1

 ORDER BY messageid DESC
 ORDER BY messageid DESC

+LIMIT $2
\ No newline at end of file

diff --git a/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql b/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql
index 865bd53..7dda189 100644 (file)
--- a/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql
+++ b/src/core/SQL/PostgreSQL/14/select_messagesNewerThan.sql
@@ -1,6 +1,6 @@
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
 JOIN sender ON backlog.senderid = sender.senderid
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
 JOIN sender ON backlog.senderid = sender.senderid

-WHERE bufferid = :bufferid
-    AND backlog.messageid >= :firstmsg
+WHERE backlog.messageid >= $1 AND bufferid = $2

 ORDER BY messageid DESC
 ORDER BY messageid DESC

+LIMIT $3
\ No newline at end of file

diff --git a/src/core/SQL/PostgreSQL/14/select_messagesRange.sql b/src/core/SQL/PostgreSQL/14/select_messagesRange.sql
index 9e596dd..e7a6a5a 100644 (file)
--- a/src/core/SQL/PostgreSQL/14/select_messagesRange.sql
+++ b/src/core/SQL/PostgreSQL/14/select_messagesRange.sql
@@ -1,7 +1,8 @@
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
 JOIN sender ON backlog.senderid = sender.senderid
 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
+WHERE backlog.messageid >= $1
+    AND backlog.messageid < $2
+    AND bufferid = $3

 ORDER BY messageid DESC
 ORDER BY messageid DESC

+LIMIT $4
\ No newline at end of file

diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp
index 8fe4cdb..0421224 100644 (file)
--- a/src/core/postgresqlstorage.cpp
+++ b/src/core/postgresqlstorage.cpp
@@ -1267,34 +1267,49 @@ QList<Message> PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, Ms
     return messagelist;
   }
 
     return messagelist;
   }
 

-  QSqlQuery query(db);
+  QString queryName;
+  QVariantList params;

   if(last == -1 && first == -1) {
   if(last == -1 && first == -1) {

-    query.prepare(queryString("select_messages"));
+    queryName = "select_messages";

   } else if(last == -1) {
   } else if(last == -1) {

-    query.prepare(queryString("select_messagesNewerThan"));
-    query.bindValue(":firstmsg", first.toInt());
+    queryName = "select_messagesNewerThan";
+    params << first.toInt();

   } else {
   } 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)) {
   if(!watchQuery(query)) {

+    qDebug() << "select_messages failed";

     db.rollback();
     return messagelist;
   }
 
   QDateTime timestamp;
     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,
     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;
   }
     msg.setMsgId(query.value(0).toInt());
     messagelist << msg;
   }


@@ -1401,7 +1416,7 @@ bool PostgreSqlStorage::prepareQuery(const QString &handle, const QString &query
 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariantList &params, 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();
 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariantList &params, 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();
   }
 
   QSqlDriver *driver = db.driver();


@@ -1431,7 +1446,7 @@ QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const Q
 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariant &param, 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();
 QSqlQuery PostgreSqlStorage::executePreparedQuery(const QString &handle, const QVariant &param, 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;
   }
 
   QSqlField field;


@@ -1457,10 +1472,6 @@ void PostgreSqlStorage::deallocateQuery(const QString &handle, const QSqlDatabas
   db.exec(QString("DEALLOCATE %1").arg(queryId));
 }
 
   db.exec(QString("DEALLOCATE %1").arg(queryId));
 }
 

-
-
-
-

 // ========================================
 //  PostgreSqlMigrationWriter
 // ========================================
 // ========================================
 //  PostgreSqlMigrationWriter
 // ========================================