From: Shane Synan Date: Fri, 10 Jul 2020 21:50:59 +0000 (-0400) Subject: core: Fix SQL defaults for filtered backlog fetch X-Git-Tag: 0.14-rc1~38 X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=68b12b0b035c460c7bab93959004bd83c0b160f2 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. --- 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