From 68b12b0b035c460c7bab93959004bd83c0b160f2 Mon Sep 17 00:00:00 2001 From: Shane Synan Date: Fri, 10 Jul 2020 17:50:59 -0400 Subject: [PATCH] core: Fix SQL defaults for filtered backlog fetch Modify the SQL queries for *_filtered.sql backlog fetching to allow specifying an invalid type of "0" or "-1" to match any message type. This allows filtering backlog fetching by flag without restricting by type. Also modify the "= 0" check for :flag to "<= 0". This matches the function definitions and fixes specifying "-1" instead of "0". Without this change, specifying "-1" will result in missing messages! Committed separately from the new forward backlog fetching in case issues are found, making reverting easier. --- src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql | 6 +++--- src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql | 6 +++--- .../SQL/PostgreSQL/select_messagesNewerThan_filtered.sql | 6 +++--- src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql | 6 +++--- src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql | 6 +++--- src/core/SQL/SQLite/select_messagesAllNew_filtered.sql | 6 +++--- src/core/SQL/SQLite/select_messagesAll_filtered.sql | 6 +++--- src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql | 6 +++--- src/core/SQL/SQLite/select_messagesNewestK_filtered.sql | 6 +++--- src/core/SQL/SQLite/select_messagesRange_filtered.sql | 6 +++--- 10 files changed, 30 insertions(+), 30 deletions(-) diff --git a/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql b/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql index 780263fc..7ba9e681 100644 --- a/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesAllNew_filtered.sql @@ -1,9 +1,9 @@ -SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index 3e1ce655..2c6e0fca 100644 --- a/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesAll_filtered.sql @@ -1,10 +1,10 @@ -SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index 46b4d47b..9ef356c1 100644 --- a/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesNewerThan_filtered.sql @@ -1,10 +1,10 @@ -SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index cdb960c7..39f107d2 100644 --- a/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesNewestK_filtered.sql @@ -1,9 +1,9 @@ -SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index 6030e532..087f6112 100644 --- a/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql +++ b/src/core/SQL/PostgreSQL/select_messagesRange_filtered.sql @@ -1,10 +1,10 @@ -SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index 1a742b8a..92388636 100644 --- a/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesAllNew_filtered.sql @@ -1,9 +1,9 @@ -SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index c09be6b3..6fceb2a1 100644 --- a/src/core/SQL/SQLite/select_messagesAll_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesAll_filtered.sql @@ -1,10 +1,10 @@ -SELECT messageid, bufferid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index 198df7c2..53f26b14 100644 --- a/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesNewerThan_filtered.sql @@ -1,10 +1,10 @@ -SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR 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 index b4411af5..d290c580 100644 --- a/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesNewestK_filtered.sql @@ -1,9 +1,9 @@ -SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) +AND (:type <= 0 OR 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 index d1e4c0f1..223a92c2 100644 --- a/src/core/SQL/SQLite/select_messagesRange_filtered.sql +++ b/src/core/SQL/SQLite/select_messagesRange_filtered.sql @@ -1,10 +1,10 @@ -SELECT messageid, time, type, flags, sender, senderprefixes, realname, avatarurl, message +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) + AND (:type <= 0 OR backlog.type & :type != 0) + AND (:flags <= 0 OR backlog.flags & :flags != 0) ORDER BY messageid DESC LIMIT :limit -- 2.20.1