Topic on User talk:Brooke Vibber/Compacting the revision table round 2

Some people (cc @Halfak (WMF)) have been talking for a while about storing various bits of data relevant to edit review and vandalism fighting. There's an ancient bug asking for the "patrolled" and "bot" flags to be stored in the revision table rather than the recentchanges table so that this data doesn't go away after 30 days.

In addition to the patrol flag (or instead of it, on wikis where it's not enabled for historical reasons), the other bit of information that's important for coordinating patrol/review work is whether a revision has been reverted already. We don't currently have any standardized way of even determining this (researchers tend to DIY their revert detection AFAIK), and we also don't have a place to store the result for others to reuse. Currently you can kind of do something with rev_sha1, but with that moving to the content table you wouldn't be able to get a hash for an entire revision easily. If this were built into MediaWiki, we might be able to take the user's action (e.g. using the rollback button or an undo link) into account as well.

So this would mean adding something like rev_reverted as a boolean or rev_reverted_by as an integer (pointing to the rev_id that reverts it), as well as rev_bot and rev_patrolled (both booleans). I'm sensitive to not wanting to make the revision table wider than it needs to be, so if people feel like this is too much, perhaps we could move these things (along with rev_minor_edit) out to a revision_flags table or something?

I think I replied to this briefly in passing on a task, but my inclination is yes, a revision_flags or more specific revert_tracking table sounds most appropriate for this sort of thing, where we're sorta tracking meta-information about the actions.

Cool, thanks. A separate table keyed by rev_id would also not be blocked on this big schema change, which is another advantage. We could start building it quite soon.

@Daniel Kinzler (WMDE) points out that change tags could be used for revert tracking and possibly other things, but that we'd probably want to optimize the schema for tags first (which he said @Ladsgroup is looking into)