[drupal-devel] [bug] Ignore unpublished comments when determining last_post

[Morbus Iff]

 Project:      Drupal
 Version:      4.5.0
 Component:    tracker.module
 Category:     bug reports
 Priority:     normal
 Assigned to:  Morbus Iff
 Reported by:  Morbus Iff
 Updated by:   Morbus Iff
 Status:       patch
 Attachment:   http://drupal.org/files/issues/tracker_0.patch (2.59 KB)

Patch attached for 4.5.0. Haven't tried applying it to CVS, but it needs
to go there too.

Morbus Iff



Previous comments:
------------------------------------------------------------------------

January 12, 2005 - 11:02 : Morbus Iff

The automated unpublishing is great for stopping spam from being
displayed, but it doesn't stop spam from corrupting another feature: my
"recent posts" (tracker.module). I'll regularly get bursts of 200
auto-unpublished spams which make the "recent posts" page useless - an
unpublished spam still affects the modification date of a node, which
makes "recent posts" display craploads of updates, even though there
really isn't any. Thus, the feature request would be:

 - treat an unpublished spam as a deleted spam
 - when a spam is automatically unpublished, revert the node
modification date to its previous value.

I'm not entirely sure how easily possible this is, as you'd have to
make sure the date is properly set when someone says "nah, this
unpublished spam was actual ham", and then republishes it.

------------------------------------------------------------------------

January 12, 2005 - 14:41 : Jeremy at kerneltrap.org

This is a known and annoying bug.  As it affects me too, I assure you
I'll get to fixing this eventually... 

Any ideas on how to cleanly implement this are welcome.

------------------------------------------------------------------------

January 13, 2005 - 16:27 : Morbus Iff

Would it seem to you that this behavior should be part of core? Ignoring
spam.module entirely, if a node is updated at 11:00, and then updated
with a comment at 12:00, the 12:00 is saved in there as the node's
modification time. But if a comment is unpublished, shouldn't the node
also be reverted back to its previous state (being 11:00)? How is the
deletion of data (permanently hiding it) different from merely
temporarily hiding it (ie., unpublishing it)?

Perhaps I should move request to Drupal.

------------------------------------------------------------------------

January 13, 2005 - 16:32 : Jeremy at kerneltrap.org

Feel free to move it.  If I come up with a clean solution (at least as
far as spam is concerned), I'll update the issue wherever it is.

------------------------------------------------------------------------

January 13, 2005 - 16:39 : Morbus Iff

Was talking about this with killes (who "frankly [doesn't] care too much
about comments"), and I'm now wondering if I'm going nutty with all this
talk about node and comment timestamps. We should investigate to see if
tracker.module looks at /unpublished/ comment timestamps instead of
just published ones. If it doesn't make a distinction, then it would
theoretically be a quickie patch to a SQL statement to only check
against published comment timestamps, not unpublished.

------------------------------------------------------------------------

January 13, 2005 - 16:42 : Morbus Iff

With a quick look, it looks like tracker_page() only checks node.status,
and not comment.status. We should be able to put a patch in for
comment.status = 1 (unpublished being 2), and that should solve our
problem, right?

------------------------------------------------------------------------

January 13, 2005 - 16:55 : Morbus Iff

Apologies... "AND c.status = 0". Modifying the two queries in
tracker_page() made things, seemingly, work as I desire. Can you
confirm?

------------------------------------------------------------------------

January 13, 2005 - 17:02 : Jeremy at kerneltrap.org

It didn't appear to help with regards to the spam module.  I had a
recent spam flood, and with or without the 'AND c.status = 0' I saw the
same posts, including those with recently unpublished spam comments.

------------------------------------------------------------------------

January 13, 2005 - 17:08 : Morbus Iff

Odd - I had one unpublished piece of spam that I tested against (for
4.5.0, not CVS), and modifying tracker.module seemed to work as I
intended.  So, the following bit of SQL still shows you erroneous nodes
with unpublished comments?

SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name,
MAX(GREATEST(n.changed, c.timestamp)) AS last_post FROM node n LEFT
JOIN comments c ON n.nid = c.nid INNER JOIN users u ON n.uid = u.uid
WHERE n.status = 1 AND c.status = 0 AND '1' GROUP BY n.nid, n.title,
n.type, n.changed, n.uid, u.name ORDER BY last_post DESC LIMIT 10;

The above is what is actually run against my database with the proposed
tracker.module change. (This code is currently active on gamegrene.com,
so we can test live if need be. Submit a comment as an anonymous user
with the words "poker" or "texas hold'em", and it should be a)
unpublished automatically via your module, and b) not shown in the
tracker.

------------------------------------------------------------------------

January 14, 2005 - 07:52 : Morbus Iff

For what it's worth, I just woke up to another flood of 150 spams, and
no corruption of my recent posts.

------------------------------------------------------------------------

January 14, 2005 - 08:05 : Jeremy at kerneltrap.org

Then it sounds like a patch is in order...  For the tracker module.

------------------------------------------------------------------------

January 14, 2005 - 08:59 : Morbus Iff

Have you been able to confirm this though?

-- 
View: http://drupal.org/node/15500
Edit: http://drupal.org/project/comments/add/15500