From b9169a652a6854b3fa85aee8f833cb9e18a8d510 Mon Sep 17 00:00:00 2001 From: Janne Koschinski Date: Tue, 8 May 2018 15:50:41 -0500 Subject: [PATCH] Implement type-filtered backlog fetching Add new backlog fetching methods that only return messages of the specified types. This allows clients to only fetch the types of messages that they need, e.g. if join/part/quit is hidden. This is not yet used in Quassel itself, but third-party clients including QuasselDroid use this for better performance on limited bandwidth networks. Signed-off-by: Janne Koschinski Closes GH-339. --- src/common/backlogmanager.cpp | 11 ++ src/common/backlogmanager.h | 4 + src/common/quassel.h | 1 + .../select_messagesAllNew_filtered.sql | 9 ++ .../select_messagesAll_filtered.sql | 10 ++ .../select_messagesNewerThan_filtered.sql | 10 ++ .../select_messagesNewestK_filtered.sql | 9 ++ .../select_messagesRange_filtered.sql | 10 ++ .../SQLite/select_messagesAllNew_filtered.sql | 9 ++ .../SQLite/select_messagesAll_filtered.sql | 10 ++ .../select_messagesNewerThan_filtered.sql | 10 ++ .../select_messagesNewestK_filtered.sql | 9 ++ .../SQLite/select_messagesRange_filtered.sql | 10 ++ src/core/core.h | 31 ++++ src/core/corebacklogmanager.cpp | 86 +++++++++++ src/core/corebacklogmanager.h | 4 + src/core/postgresqlstorage.cpp | 125 ++++++++++++++++ src/core/postgresqlstorage.h | 6 + src/core/sql.qrc | 10 ++ src/core/sqlitestorage.cpp | 134 ++++++++++++++++++ src/core/sqlitestorage.h | 6 + src/core/storage.h | 23 +++ 22 files changed, 537 insertions(+) create mode 100644 src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql create mode 100644 src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql create mode 100644 src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql create mode 100644 src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql create mode 100644 src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql create mode 100644 src/core/SQL/SQLite/select_messagesAllNew_filtered.sql create mode 100644 src/core/SQL/SQLite/select_messagesAll_filtered.sql create mode 100644 src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql create mode 100644 src/core/SQL/SQLite/select_messagesNewestK_filtered.sql create mode 100644 src/core/SQL/SQLite/select_messagesRange_filtered.sql diff --git a/src/common/backlogmanager.cpp b/src/common/backlogmanager.cpp index 10717cf7..2ea49d5d 100644 --- a/src/common/backlogmanager.cpp +++ b/src/common/backlogmanager.cpp @@ -27,9 +27,20 @@ QVariantList BacklogManager::requestBacklog(BufferId bufferId, MsgId first, MsgI return QVariantList(); } +QVariantList BacklogManager::requestBacklogFiltered(BufferId bufferId, MsgId first, MsgId last, int limit, int additional, int type, int flags) +{ + REQUEST(ARG(bufferId), ARG(first), ARG(last), ARG(limit), ARG(additional), 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)) return QVariantList(); } + +QVariantList BacklogManager::requestBacklogAllFiltered(MsgId first, MsgId last, int limit, int additional, int type, int flags) +{ + REQUEST(ARG(first), ARG(last), ARG(limit), ARG(additional), ARG(type), ARG(flags)) + return QVariantList(); +} diff --git a/src/common/backlogmanager.h b/src/common/backlogmanager.h index d92180d5..1aa5d430 100644 --- a/src/common/backlogmanager.h +++ b/src/common/backlogmanager.h @@ -35,10 +35,14 @@ public: 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); inline virtual void receiveBacklog(BufferId, MsgId, MsgId, int, int, QVariantList) {}; + inline virtual void receiveBacklogFiltered(BufferId, MsgId, MsgId, int, int, int, int, QVariantList) {}; virtual QVariantList requestBacklogAll(MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0); + virtual QVariantList requestBacklogAllFiltered(MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0, int type = -1, int flags = -1); inline virtual void receiveBacklogAll(MsgId, MsgId, int, int, QVariantList) {}; + inline virtual void receiveBacklogAllFiltered(MsgId, MsgId, int, int, int, int, QVariantList) {}; signals: void backlogRequested(BufferId, MsgId, MsgId, int, int); diff --git a/src/common/quassel.h b/src/common/quassel.h index fd91f9a3..dc697fcc 100644 --- a/src/common/quassel.h +++ b/src/common/quassel.h @@ -133,6 +133,7 @@ public: ExtendedFeatures, ///< Extended features LongMessageTime, ///< Serialize message time as 64-bit RichMessages, ///< Real Name and Avatar URL in backlog + BacklogFilterType, ///< BacklogManager supports filtering backlog by MessageType #if QT_VERSION >= 0x050500 EcdsaCertfpKeys, ///< ECDSA keys for CertFP in identities #endif diff --git a/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql new file mode 100644 index 00000000..780263fc --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql @@ -0,0 +1,9 @@ +SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +FROM backlog +JOIN sender ON backlog.senderid = sender.senderid +WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) + AND backlog.messageid >= :firstmsg + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +-- Unlike SQLite, no LIMIT clause, mimicking the unfiltered version - investigate later..? diff --git a/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql new file mode 100644 index 00000000..3e1ce655 --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql @@ -0,0 +1,10 @@ +SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +FROM backlog +JOIN sender ON backlog.senderid = sender.senderid +WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) + AND backlog.messageid >= :firstmsg + AND backlog.messageid < :lastmsg + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +-- Unlike SQLite, no LIMIT clause, mimicking the unfiltered version - investigate later..? diff --git a/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql new file mode 100644 index 00000000..46b4d47b --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.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 >= :first + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :buffer) + AND bufferid = :buffer + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql new file mode 100644 index 00000000..cdb960c7 --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql @@ -0,0 +1,9 @@ +SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +FROM backlog +JOIN sender ON backlog.senderid = sender.senderid +WHERE bufferid = :buffer + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :buffer) + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql new file mode 100644 index 00000000..6030e532 --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_messagesRange_filtered.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 >= :first + AND backlog.messageid < :last + AND bufferid = :buffer + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql b/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql new file mode 100644 index 00000000..1a742b8a --- /dev/null +++ b/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql @@ -0,0 +1,9 @@ +SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +FROM backlog +JOIN sender ON backlog.senderid = sender.senderid +WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) + AND backlog.messageid >= :firstmsg + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesAll_filtered.sql b/src/core/SQL/SQLite/select_messagesAll_filtered.sql new file mode 100644 index 00000000..c09be6b3 --- /dev/null +++ b/src/core/SQL/SQLite/select_messagesAll_filtered.sql @@ -0,0 +1,10 @@ +SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +FROM backlog +JOIN sender ON backlog.senderid = sender.senderid +WHERE backlog.bufferid IN (SELECT bufferid FROM buffer WHERE userid = :userid) + AND backlog.messageid >= :firstmsg + AND backlog.messageid < :lastmsg + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql b/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql new file mode 100644 index 00000000..198df7c2 --- /dev/null +++ b/src/core/SQL/SQLite/select_messagesNewerThan_filtered.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 >= :firstmsg + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) + AND bufferid = :bufferid + AND backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql b/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql new file mode 100644 index 00000000..b4411af5 --- /dev/null +++ b/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql @@ -0,0 +1,9 @@ +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 <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) +AND backlog.type & :type != 0 +AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/SQL/SQLite/select_messagesRange_filtered.sql b/src/core/SQL/SQLite/select_messagesRange_filtered.sql new file mode 100644 index 00000000..d1e4c0f1 --- /dev/null +++ b/src/core/SQL/SQLite/select_messagesRange_filtered.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 backlog.type & :type != 0 + AND (:flags = 0 OR backlog.flags & :flags != 0) +ORDER BY messageid DESC +LIMIT :limit diff --git a/src/core/core.h b/src/core/core.h index 640ba18d..0814e7c2 100644 --- a/src/core/core.h +++ b/src/core/core.h @@ -429,6 +429,22 @@ public: } + //! Request a certain number messages stored in a given buffer, matching certain filters + /** \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 QList requestMsgsFiltered(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->requestMsgsFiltered(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 @@ -441,6 +457,21 @@ public: } + //! Request a certain number of messages across all buffers, matching certain filters + /** \param first if != -1 return only messages with a MsgId >= first + * \param last if != -1 return only messages with a MsgId < last + * \param limit Max amount of messages + * \param type The Message::Types that should be returned + * \return The requested list of messages + */ + static inline QList requestAllMsgsFiltered(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) + { + return instance()->_storage->requestAllMsgsFiltered(user, first, last, limit, type, flags); + } + + //! Request a list of all buffers known to a user. /** This method is used to get a list of all buffers we have stored a backlog from. * \note This method is threadsafe. diff --git a/src/core/corebacklogmanager.cpp b/src/core/corebacklogmanager.cpp index d0fa48d0..73e18055 100644 --- a/src/core/corebacklogmanager.cpp +++ b/src/core/corebacklogmanager.cpp @@ -78,6 +78,52 @@ QVariantList CoreBacklogManager::requestBacklog(BufferId bufferId, MsgId first, } +QVariantList CoreBacklogManager::requestBacklogFiltered(BufferId bufferId, MsgId first, MsgId last, int limit, int additional, int type, int flags) +{ + QVariantList backlog; + QList msgList; + msgList = Core::requestMsgsFiltered(coreSession()->user(), bufferId, first, last, limit, Message::Types{type}, Message::Flags{flags}); + + QList::const_iterator msgIter = msgList.constBegin(); + QList::const_iterator msgListEnd = msgList.constEnd(); + while (msgIter != msgListEnd) { + backlog << qVariantFromValue(*msgIter); + ++msgIter; + } + + if (additional && limit != 0) { + MsgId oldestMessage = first; + if (!msgList.isEmpty()) { + if (msgList.first().msgId() < msgList.last().msgId()) + oldestMessage = msgList.first().msgId(); + else + oldestMessage = msgList.last().msgId(); + } + + if (first != -1) { + last = first; + } + else { + last = oldestMessage; + } + + // only fetch additional messages if they continue seemlessly + // that is, if the list of messages is not truncated by the limit + if (last == oldestMessage) { + msgList = Core::requestMsgsFiltered(coreSession()->user(), bufferId, -1, last, additional, Message::Types{type}, Message::Flags{flags}); + msgIter = msgList.constBegin(); + msgListEnd = msgList.constEnd(); + while (msgIter != msgListEnd) { + backlog << qVariantFromValue(*msgIter); + ++msgIter; + } + } + } + + return backlog; +} + + QVariantList CoreBacklogManager::requestBacklogAll(MsgId first, MsgId last, int limit, int additional) { QVariantList backlog; @@ -115,3 +161,43 @@ QVariantList CoreBacklogManager::requestBacklogAll(MsgId first, MsgId last, int return backlog; } + + +QVariantList CoreBacklogManager::requestBacklogAllFiltered(MsgId first, MsgId last, int limit, int additional, int type, + int flags) +{ + QVariantList backlog; + QList msgList; + msgList = Core::requestAllMsgsFiltered(coreSession()->user(), first, last, limit, Message::Types{type}, Message::Flags{flags}); + + QList::const_iterator msgIter = msgList.constBegin(); + QList::const_iterator msgListEnd = msgList.constEnd(); + while (msgIter != msgListEnd) { + backlog << qVariantFromValue(*msgIter); + ++msgIter; + } + + if (additional) { + if (first != -1) { + last = first; + } + else { + last = -1; + if (!msgList.isEmpty()) { + if (msgList.first().msgId() < msgList.last().msgId()) + last = msgList.first().msgId(); + else + last = msgList.last().msgId(); + } + } + msgList = Core::requestAllMsgsFiltered(coreSession()->user(), -1, last, additional, Message::Types{type}, Message::Flags{flags}); + msgIter = msgList.constBegin(); + msgListEnd = msgList.constEnd(); + while (msgIter != msgListEnd) { + backlog << qVariantFromValue(*msgIter); + ++msgIter; + } + } + + return backlog; +} diff --git a/src/core/corebacklogmanager.h b/src/core/corebacklogmanager.h index a124016b..f3148681 100644 --- a/src/core/corebacklogmanager.h +++ b/src/core/corebacklogmanager.h @@ -37,7 +37,11 @@ public: public slots: virtual QVariantList requestBacklog(BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0); + QVariantList requestBacklogFiltered(BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1, + int additional = 0, int type = -1, int flags = -1) override; virtual QVariantList requestBacklogAll(MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0); + QVariantList requestBacklogAllFiltered(MsgId first = -1, MsgId last = -1, int limit = -1, int additional = 0, + int type = -1, int flags = -1) override; private: CoreSession *_coreSession; diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index 3c15b2c0..5b52afd6 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -1755,6 +1755,70 @@ QList PostgreSqlStorage::requestMsgs(UserId user, BufferId bufferId, Ms } +QList PostgreSqlStorage::requestMsgsFiltered(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags) +{ + 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()) { + db.rollback(); + return messagelist; + } + + QSqlQuery query(db); + if (last == -1 && first == -1) { + query.prepare(queryString("select_messagesNewestK_filtered")); + } else if (last == -1) { + query.prepare(queryString("select_messagesNewerThan_filtered")); + query.bindValue(":first", first.toInt()); + } else { + query.prepare(queryString("select_messagesRange_filtered")); + query.bindValue(":last", last.toInt()); + query.bindValue(":first", first.toInt()); + } + query.bindValue(":buffer", bufferId.toInt()); + query.bindValue(":limit", limit); + int typeRaw = type; + query.bindValue(":type", typeRaw); + int flagsRaw = flags; + query.bindValue(":flags", flagsRaw); + + safeExec(query); + if (!watchQuery(query)) { + qDebug() << "select_messages failed"; + db.rollback(); + return messagelist; + } + + QDateTime timestamp; + while (query.next()) { + timestamp = query.value(1).toDateTime(); + timestamp.setTimeSpec(Qt::UTC); + Message msg(timestamp, + bufferInfo, + (Message::Type)query.value(2).toUInt(), + 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).toInt()); + messagelist << msg; + } + + db.commit(); + return messagelist; +} + + QList PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit) { QList messagelist; @@ -1810,6 +1874,67 @@ QList PostgreSqlStorage::requestAllMsgs(UserId user, MsgId first, MsgId } +QList PostgreSqlStorage::requestAllMsgsFiltered(UserId user, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags) +{ + QList messagelist; + + // requestBuffers uses it's own transaction. + QHash bufferInfoHash; + foreach(BufferInfo bufferInfo, requestBuffers(user)) { + bufferInfoHash[bufferInfo.bufferId()] = bufferInfo; + } + + 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_filtered")); + } + else { + query.prepare(queryString("select_messagesAll_filtered")); + query.bindValue(":lastmsg", last.toInt()); + } + query.bindValue(":userid", user.toInt()); + query.bindValue(":firstmsg", first.toInt()); + + int typeRaw = type; + query.bindValue(":type", typeRaw); + + int flagsRaw = flags; + query.bindValue(":flags", flagsRaw); + + safeExec(query); + if (!watchQuery(query)) { + db.rollback(); + return messagelist; + } + + QDateTime timestamp; + for (int i = 0; i < limit && query.next(); i++) { + timestamp = query.value(2).toDateTime(); + timestamp.setTimeSpec(Qt::UTC); + Message msg(timestamp, + bufferInfoHash[query.value(1).toInt()], + (Message::Type)query.value(3).toUInt(), + query.value(9).toString(), + query.value(5).toString(), + query.value(6).toString(), + query.value(7).toString(), + query.value(8).toString(), + Message::Flags{query.value(4).toInt()}); + msg.setMsgId(query.value(0).toInt()); + messagelist << msg; + } + + db.commit(); + return messagelist; +} + QMap PostgreSqlStorage::getAllAuthUserNames() { QMap authusernames; diff --git a/src/core/postgresqlstorage.h b/src/core/postgresqlstorage.h index 5322a09a..e9fc8740 100644 --- a/src/core/postgresqlstorage.h +++ b/src/core/postgresqlstorage.h @@ -108,7 +108,13 @@ public slots: bool logMessage(Message &msg) override; bool logMessages(MessageList &msgs) override; QList requestMsgs(UserId user, BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1) override; + QList requestMsgsFiltered(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; QList requestAllMsgs(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1) override; + QList requestAllMsgsFiltered(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) override; /* Sysident handling */ QMap getAllAuthUserNames() override; diff --git a/src/core/sql.qrc b/src/core/sql.qrc index 96de7f6b..c90d472f 100644 --- a/src/core/sql.qrc +++ b/src/core/sql.qrc @@ -52,9 +52,14 @@ ./SQL/PostgreSQL/select_internaluser.sql ./SQL/PostgreSQL/select_messagesAll.sql ./SQL/PostgreSQL/select_messagesAllNew.sql + ./SQL/PostgreSQL/select_messagesAllNew_filtered.sql + ./SQL/PostgreSQL/select_messagesAll_filtered.sql ./SQL/PostgreSQL/select_messagesNewerThan.sql + ./SQL/PostgreSQL/select_messagesNewerThan_filtered.sql ./SQL/PostgreSQL/select_messagesNewestK.sql + ./SQL/PostgreSQL/select_messagesNewestK_filtered.sql ./SQL/PostgreSQL/select_messagesRange.sql + ./SQL/PostgreSQL/select_messagesRange_filtered.sql ./SQL/PostgreSQL/select_networkExists.sql ./SQL/PostgreSQL/select_network_awaymsg.sql ./SQL/PostgreSQL/select_network_usermode.sql @@ -176,9 +181,14 @@ ./SQL/SQLite/select_internaluser.sql ./SQL/SQLite/select_messagesAll.sql ./SQL/SQLite/select_messagesAllNew.sql + ./SQL/SQLite/select_messagesAllNew_filtered.sql + ./SQL/SQLite/select_messagesAll_filtered.sql ./SQL/SQLite/select_messagesNewerThan.sql + ./SQL/SQLite/select_messagesNewerThan_filtered.sql ./SQL/SQLite/select_messagesNewestK.sql + ./SQL/SQLite/select_messagesNewestK_filtered.sql ./SQL/SQLite/select_messagesRange.sql + ./SQL/SQLite/select_messagesRange_filtered.sql ./SQL/SQLite/select_networkExists.sql ./SQL/SQLite/select_network_awaymsg.sql ./SQL/SQLite/select_network_usermode.sql diff --git a/src/core/sqlitestorage.cpp b/src/core/sqlitestorage.cpp index 1e1b7982..5139d387 100644 --- a/src/core/sqlitestorage.cpp +++ b/src/core/sqlitestorage.cpp @@ -1895,6 +1895,82 @@ QList SqliteStorage::requestMsgs(UserId user, BufferId bufferId, MsgId } +QList SqliteStorage::requestMsgsFiltered(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags) +{ + QList 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); + if (last == -1 && first == -1) { + query.prepare(queryString("select_messagesNewestK_filtered")); + } + else if (last == -1) { + query.prepare(queryString("select_messagesNewerThan_filtered")); + query.bindValue(":firstmsg", first.toInt()); + } + else { + query.prepare(queryString("select_messagesRange_filtered")); + query.bindValue(":lastmsg", last.toInt()); + query.bindValue(":firstmsg", first.toInt()); + } + query.bindValue(":bufferid", bufferId.toInt()); + query.bindValue(":limit", limit); + int typeRaw = type; + query.bindValue(":type", typeRaw); + int flagsRaw = flags; + query.bindValue(":flags", flagsRaw); + + safeExec(query); + watchQuery(query); + + while (query.next()) { + Message msg(QDateTime::fromTime_t(query.value(1).toInt()), + bufferInfo, + (Message::Type)query.value(2).toUInt(), + 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).toInt()); + messagelist << msg; + } + } + db.commit(); + unlock(); + + return messagelist; +} + + QList SqliteStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit) { QList messagelist; @@ -1950,6 +2026,64 @@ QList SqliteStorage::requestAllMsgs(UserId user, MsgId first, MsgId las return messagelist; } +QList SqliteStorage::requestAllMsgsFiltered(UserId user, MsgId first, MsgId last, int limit, Message::Types type, Message::Flags flags) +{ + QList messagelist; + + QSqlDatabase db = logDb(); + db.transaction(); + + QHash bufferInfoHash; + { + QSqlQuery bufferInfoQuery(db); + bufferInfoQuery.prepare(queryString("select_buffers")); + bufferInfoQuery.bindValue(":userid", user.toInt()); + + lockForRead(); + safeExec(bufferInfoQuery); + watchQuery(bufferInfoQuery); + while (bufferInfoQuery.next()) { + BufferInfo bufferInfo = BufferInfo(bufferInfoQuery.value(0).toInt(), bufferInfoQuery.value(1).toInt(), (BufferInfo::Type)bufferInfoQuery.value(2).toInt(), bufferInfoQuery.value(3).toInt(), bufferInfoQuery.value(4).toString()); + bufferInfoHash[bufferInfo.bufferId()] = bufferInfo; + } + + QSqlQuery query(db); + if (last == -1) { + query.prepare(queryString("select_messagesAllNew_filtered")); + } + else { + query.prepare(queryString("select_messagesAll_filtered")); + query.bindValue(":lastmsg", last.toInt()); + } + query.bindValue(":userid", user.toInt()); + query.bindValue(":firstmsg", first.toInt()); + query.bindValue(":limit", limit); + int typeRaw = type; + query.bindValue(":type", typeRaw); + int flagsRaw = flags; + query.bindValue(":flags", flagsRaw); + safeExec(query); + + watchQuery(query); + + while (query.next()) { + Message msg(QDateTime::fromTime_t(query.value(2).toInt()), + bufferInfoHash[query.value(1).toInt()], + (Message::Type)query.value(3).toUInt(), + query.value(9).toString(), + query.value(5).toString(), + query.value(6).toString(), + query.value(7).toString(), + query.value(8).toString(), + Message::Flags{query.value(4).toInt()}); + msg.setMsgId(query.value(0).toInt()); + messagelist << msg; + } + } + db.commit(); + unlock(); + return messagelist; +} QMap SqliteStorage::getAllAuthUserNames() { diff --git a/src/core/sqlitestorage.h b/src/core/sqlitestorage.h index 367c644d..41312f12 100644 --- a/src/core/sqlitestorage.h +++ b/src/core/sqlitestorage.h @@ -109,7 +109,13 @@ public slots: bool logMessage(Message &msg) override; bool logMessages(MessageList &msgs) override; QList requestMsgs(UserId user, BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1) override; + QList requestMsgsFiltered(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; QList requestAllMsgs(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1) override; + QList requestAllMsgsFiltered(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) override; /* Sysident handling */ QMap getAllAuthUserNames() override; diff --git a/src/core/storage.h b/src/core/storage.h index d22372e3..7cbc3e3d 100644 --- a/src/core/storage.h +++ b/src/core/storage.h @@ -480,6 +480,18 @@ public slots: */ virtual QList requestMsgs(UserId user, BufferId bufferId, MsgId first = -1, MsgId last = -1, int limit = -1) = 0; + //! Request a certain number messages stored in a given buffer, matching certain filters + /** \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 QList requestMsgsFiltered(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 @@ -488,6 +500,17 @@ public slots: */ virtual QList requestAllMsgs(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1) = 0; + //! Request a certain number of messages across all buffers, matching certain filters + /** \param first if != -1 return only messages with a MsgId >= first + * \param last if != -1 return only messages with a MsgId < last + * \param limit Max amount of messages + * \param type The Message::Types that should be returned + * \return The requested list of messages + */ + virtual QList requestAllMsgsFiltered(UserId user, MsgId first = -1, MsgId last = -1, int limit = -1, + Message::Types type = Message::Types{-1}, + Message::Flags flags = Message::Flags{-1}) = 0; + //! Fetch all authusernames /** \return Map of all current UserIds to permitted idents */ -- 2.20.1