From 097f625c4bd147f4127573ad8ab48555f9dc62d3 Mon Sep 17 00:00:00 2001 From: Janne Koschinski Date: Sun, 28 Jun 2020 11:29:37 +0200 Subject: [PATCH] [PR-540] Allow loading backlog in reverse direction --- src/common/backlogmanager.cpp | 6 ++ src/common/backlogmanager.h | 3 + .../SQL/PostgreSQL/select_messagesForward.sql | 10 +++ .../SQL/SQLite/select_messagesForward.sql | 10 +++ src/core/core.h | 19 ++++ src/core/corebacklogmanager.cpp | 12 +++ src/core/corebacklogmanager.h | 2 + src/core/postgresqlstorage.cpp | 81 ++++++++++++++++- src/core/postgresqlstorage.h | 7 ++ src/core/sqlitestorage.cpp | 88 +++++++++++++++++++ src/core/sqlitestorage.h | 7 ++ src/core/storage.h | 16 ++++ 12 files changed, 259 insertions(+), 2 deletions(-) create mode 100644 src/core/SQL/PostgreSQL/select_messagesForward.sql create mode 100644 src/core/SQL/SQLite/select_messagesForward.sql diff --git a/src/common/backlogmanager.cpp b/src/common/backlogmanager.cpp index 1980a3c8..45d2818f 100644 --- a/src/common/backlogmanager.cpp +++ b/src/common/backlogmanager.cpp @@ -32,6 +32,12 @@ QVariantList BacklogManager::requestBacklogFiltered(BufferId bufferId, MsgId fir return QVariantList(); } +QVariantList BacklogManager::requestBacklogForward(BufferId bufferId, MsgId first, MsgId last, int limit, int type, int flags) +{ + REQUEST(ARG(bufferId), ARG(first), ARG(last), ARG(limit), ARG(type), ARG(flags)) + return QVariantList(); +} + QVariantList BacklogManager::requestBacklogAll(MsgId first, MsgId last, int limit, int additional) { REQUEST(ARG(first), ARG(last), ARG(limit), ARG(additional)) diff --git a/src/common/backlogmanager.h b/src/common/backlogmanager.h index f800b50b..a8eda9e8 100644 --- a/src/common/backlogmanager.h +++ b/src/common/backlogmanager.h @@ -39,8 +39,11 @@ public slots: virtual QVariantList requestBacklog(BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0); virtual QVariantList requestBacklogFiltered( BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0, int type = -1, int flags = -1); + virtual QVariantList requestBacklogForward( + BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, int type = -1, int flags = -1); inline virtual void receiveBacklog(BufferId, MsgId, MsgId, int, int, QVariantList){}; inline virtual void receiveBacklogFiltered(BufferId, MsgId, MsgId, int, int, int, int, QVariantList){}; + inline virtual void receiveBacklogForward(BufferId, MsgId, MsgId, int, int, int, QVariantList){}; virtual QVariantList requestBacklogAll(MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0); virtual QVariantList requestBacklogAllFiltered( diff --git a/src/core/SQL/PostgreSQL/select_messagesForward.sql b/src/core/SQL/PostgreSQL/select_messagesForward.sql new file mode 100644 index 00000000..e645e96b --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_messagesForward.sql @@ -0,0 +1,10 @@ +SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +FROM backlog +JOIN sender ON backlog.senderid = sender.senderid +WHERE backlog.messageid >= $1 + AND backlog.messageid < $2 + AND bufferid = $3 + AND backlog.type & $4 != 0 + AND ($5 = 0 OR backlog.flags & $5 != 0) +ORDER BY messageid ASC +LIMIT $6 diff --git a/src/core/SQL/SQLite/select_messagesForward.sql b/src/core/SQL/SQLite/select_messagesForward.sql new file mode 100644 index 00000000..881bb9f6 --- /dev/null +++ b/src/core/SQL/SQLite/select_messagesForward.sql @@ -0,0 +1,10 @@ +SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +FROM backlog +JOIN sender ON backlog.senderid = sender.senderid +WHERE bufferid = :bufferid + AND backlog.messageid >= :firstmsg + AND backlog.messageid < :lastmsg + AND (:type = 0 OR backlog.type & :type != 0) + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid ASC +LIMIT :limit diff --git a/src/core/core.h b/src/core/core.h index 4428f023..f55553c1 100644 --- a/src/core/core.h +++ b/src/core/core.h @@ -424,6 +424,25 @@ public: return instance()->_storage->requestMsgsFiltered(user, bufferId, first, last, limit, type, flags); } + //! Request a certain number messages stored in a given buffer, matching certain filters, ascending + /** \param buffer The buffer we request messages from + * \param first if != -1 return only messages with a MsgId >= first + * \param last if != -1 return only messages with a MsgId < last + * \param limit if != -1 limit the returned list to a max of \limit entries + * \param type The Message::Types that should be returned + * \return The requested list of messages + */ + static inline std::vector requestMsgsForward(UserId user, + BufferId bufferId, + MsgId first = -1, + MsgId last = -1, + int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) + { + return instance()->_storage->requestMsgsForward(user, bufferId, first, last, limit, type, flags); + } + //! Request a certain number of messages across all buffers /** \param first if != -1 return only messages with a MsgId >= first * \param last if != -1 return only messages with a MsgId < last diff --git a/src/core/corebacklogmanager.cpp b/src/core/corebacklogmanager.cpp index f973adcd..6a61ba66 100644 --- a/src/core/corebacklogmanager.cpp +++ b/src/core/corebacklogmanager.cpp @@ -109,6 +109,18 @@ QVariantList CoreBacklogManager::requestBacklogFiltered(BufferId bufferId, MsgId return backlog; } +QVariantList CoreBacklogManager::requestBacklogForward(BufferId bufferId, MsgId first, MsgId last, int limit, int type, int flags) +{ + QVariantList backlog; + auto msgList = Core::requestMsgsForward(coreSession()->user(), bufferId, first, last, limit, Message::Types{type}, Message::Flags{flags}); + + std::transform(msgList.cbegin(), msgList.cend(), std::back_inserter(backlog), [](auto&& msg) { + return QVariant::fromValue(msg); + }); + + return backlog; +} + QVariantList CoreBacklogManager::requestBacklogAll(MsgId first, MsgId last, int limit, int additional) { QVariantList backlog; diff --git a/src/core/corebacklogmanager.h b/src/core/corebacklogmanager.h index 195190e2..7f25df3e 100644 --- a/src/core/corebacklogmanager.h +++ b/src/core/corebacklogmanager.h @@ -37,6 +37,8 @@ public slots: QVariantList requestBacklog(BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0) override; QVariantList requestBacklogFiltered( BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0, int type = -1, int flags = -1) override; + QVariantList requestBacklogForward( + BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, int type = -1, int flags = -1) override; QVariantList requestBacklogAll(MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0) override; QVariantList requestBacklogAllFiltered( MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0, int type = -1, int flags = -1) override; diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index 88bc86ce..36301c6b 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -49,7 +49,7 @@ bool PostgreSqlStorage::isAvailable() const { if (!QSqlDatabase::isDriverAvailable("QPSQL")) { qWarning() << qPrintable(tr("PostgreSQL driver plugin not available for Qt. Installed drivers:")) - << qPrintable(QSqlDatabase::drivers().join(", ")); + << qPrintable(QSqlDatabase::drivers().join(", ")); return false; } return true; @@ -115,7 +115,8 @@ bool PostgreSqlStorage::initDbSession(QSqlDatabase& db) break; default: // The slash got replaced with 0 or more than 2 slashes! o_O - qCritical() << "Your version of Qt does something _VERY_ strange to slashes in QSqlQueries! You should consult your trusted doctor!"; + qCritical() + << "Your version of Qt does something _VERY_ strange to slashes in QSqlQueries! You should consult your trusted doctor!"; return false; break; } @@ -1907,6 +1908,82 @@ std::vector PostgreSqlStorage::requestMsgsFiltered( return messagelist; } +std::vector PostgreSqlStorage::requestMsgsForward( + UserId user, BufferId bufferId, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags) +{ + std::vector messagelist; + + QSqlDatabase db = logDb(); + if (!beginReadOnlyTransaction(db)) { + qWarning() << "PostgreSqlStorage::requestMsgsForward(): cannot start read only transaction!"; + qWarning() << " -" << qPrintable(db.lastError().text()); + return messagelist; + } + + BufferInfo bufferInfo = getBufferInfo(user, bufferId); + if (!bufferInfo.isValid()) { + db.rollback(); + return messagelist; + } + + QString queryName; + QVariantList params; + + if (first == -1) { + params << std::numeric_limits::min(); + } else { + params << first.toQint64(); + } + + if (last == -1) { + params << std::numeric_limits::max(); + } else { + params << last.toQint64(); + } + + params << bufferId.toInt(); + + int typeRaw = type; + int flagsRaw = flags; + params << typeRaw; + params << flagsRaw; + + if (limit != -1) + params << limit; + else + params << QVariant(QVariant::Int); + + QSqlQuery query = executePreparedQuery("select_messagesForward", params, db); + + if (!watchQuery(query)) { + qDebug() << "select_messages failed"; + db.rollback(); + return messagelist; + } + + QDateTime timestamp; + while (query.next()) { + // PostgreSQL returns date/time in ISO 8601 format, no 64-bit handling needed + // See https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT + timestamp = query.value(1).toDateTime(); + timestamp.setTimeSpec(Qt::UTC); + Message msg(timestamp, + bufferInfo, + (Message::Type)query.value(2).toInt(), + query.value(8).toString(), + query.value(4).toString(), + query.value(5).toString(), + query.value(6).toString(), + query.value(7).toString(), + (Message::Flags)query.value(3).toInt()); + msg.setMsgId(query.value(0).toLongLong()); + messagelist.push_back(std::move(msg)); + } + + db.commit(); + return messagelist; +} + std::vector PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit) { std::vector messagelist; diff --git a/src/core/postgresqlstorage.h b/src/core/postgresqlstorage.h index 2821fe65..beac18d0 100644 --- a/src/core/postgresqlstorage.h +++ b/src/core/postgresqlstorage.h @@ -116,6 +116,13 @@ public: int limit = -1, Message::Types type = Message::Types{-1}, Message::Flags flags = Message::Flags{-1}) override; + std::vector requestMsgsForward(UserId user, + BufferId bufferId, + MsgId first = -1, + MsgId last = -1, + int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) override; std::vector requestAllMsgs(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1) override; std::vector requestAllMsgsFiltered(UserId user, MsgId first = -1, diff --git a/src/core/sqlitestorage.cpp b/src/core/sqlitestorage.cpp index 9455a61c..df5033a7 100644 --- a/src/core/sqlitestorage.cpp +++ b/src/core/sqlitestorage.cpp @@ -2075,6 +2075,94 @@ std::vector SqliteStorage::requestMsgsFiltered( return messagelist; } +std::vector SqliteStorage::requestMsgsForward( + UserId user, BufferId bufferId, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags) +{ + std::vector messagelist; + + QSqlDatabase db = logDb(); + db.transaction(); + + bool error = false; + BufferInfo bufferInfo; + { + // code dupication from getBufferInfo: + // this is due to the impossibility of nesting transactions and recursive locking + QSqlQuery bufferInfoQuery(db); + bufferInfoQuery.prepare(queryString("select_buffer_by_id")); + bufferInfoQuery.bindValue(":userid", user.toInt()); + bufferInfoQuery.bindValue(":bufferid", bufferId.toInt()); + + lockForRead(); + safeExec(bufferInfoQuery); + error = !watchQuery(bufferInfoQuery) || !bufferInfoQuery.first(); + if (!error) { + bufferInfo = BufferInfo(bufferInfoQuery.value(0).toInt(), + bufferInfoQuery.value(1).toInt(), + (BufferInfo::Type)bufferInfoQuery.value(2).toInt(), + 0, + bufferInfoQuery.value(4).toString()); + error = !bufferInfo.isValid(); + } + } + if (error) { + db.rollback(); + unlock(); + return messagelist; + } + + { + QSqlQuery query(db); + query.prepare(queryString("select_messagesForward")); + + if (first == -1) { + query.bindValue(":firstmsg", std::numeric_limits::min()); + } else { + query.bindValue(":firstmsg", first.toQint64()); + } + + if (last == -1) { + query.bindValue(":lastmsg", std::numeric_limits::max()); + } else { + query.bindValue(":lastmsg", last.toQint64()); + } + + query.bindValue(":bufferid", bufferId.toInt()); + + int typeRaw = type; + int flagsRaw = flags; + query.bindValue(":type", typeRaw); + query.bindValue(":flags", flagsRaw); + + query.bindValue(":limit", limit); + + safeExec(query); + watchQuery(query); + + while (query.next()) { + Message msg( + // As of SQLite schema version 31, timestamps are stored in milliseconds + // instead of seconds. This nets us more precision as well as simplifying + // 64-bit time. + QDateTime::fromMSecsSinceEpoch(query.value(1).toLongLong()), + bufferInfo, + (Message::Type)query.value(2).toInt(), + query.value(8).toString(), + query.value(4).toString(), + query.value(5).toString(), + query.value(6).toString(), + query.value(7).toString(), + Message::Flags{query.value(3).toInt()}); + msg.setMsgId(query.value(0).toLongLong()); + messagelist.push_back(std::move(msg)); + } + } + db.commit(); + unlock(); + + return messagelist; +} + std::vector SqliteStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit) { std::vector messagelist; diff --git a/src/core/sqlitestorage.h b/src/core/sqlitestorage.h index 4d6f0b07..7b0a3de6 100644 --- a/src/core/sqlitestorage.h +++ b/src/core/sqlitestorage.h @@ -120,6 +120,13 @@ public: int limit = -1, Message::Types type = Message::Types{-1}, Message::Flags flags = Message::Flags{-1}) override; + std::vector requestMsgsForward(UserId user, + BufferId bufferId, + MsgId first = -1, + MsgId last = -1, + int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) override; std::vector requestAllMsgs(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1) override; std::vector requestAllMsgsFiltered(UserId user, MsgId first = -1, diff --git a/src/core/storage.h b/src/core/storage.h index d5ece50f..2e2cac49 100644 --- a/src/core/storage.h +++ b/src/core/storage.h @@ -527,6 +527,22 @@ public: Message::Types type = Message::Types{-1}, Message::Flags flags = Message::Flags{-1}) = 0; + //! Request a certain number messages stored in a given buffer, matching certain filters, ascending + /** \param buffer The buffer we request messages from + * \param first if != -1 return only messages with a MsgId >= first + * \param last if != -1 return only messages with a MsgId < last + * \param limit if != -1 limit the returned list to a max of \limit entries + * \param type The Message::Types that should be returned + * \return The requested list of messages + */ + virtual std::vector requestMsgsForward(UserId user, + BufferId bufferId, + MsgId first = -1, + MsgId last = -1, + int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) = 0; + //! Request a certain number of messages across all buffers /** \param first if != -1 return only messages with a MsgId >= first * \param last if != -1 return only messages with a MsgId < last -- 2.20.1