The mediawiki tecentchanges special page

The list of changes in mediawiki is presented in a SpecialPage called Recentchanges, quite extensive and useful:

The problem is, when including this page on another, it is showed like this:

Which looks awful, with all wiki codes like == or {{, or html codes like
. Isn’t there a better solution than modifying a special page (which would be overwritten with an upgrade)?

The wikihow model

Wikihow created a very nice approach to this:

As far as I can see, it lists these types of changes:

• a new user joined the wiki;
• a new file uploaded;
• a category added to an article;
• an edition to an article;
• a user left a message to another;
• and of course, when a new article is created.

Except for the user messaging system, which is probably based on a specific extension, all the others can be taken from the database. Also, the category addition is not that different from an article edition, so I will leave that one apart too.

recent changes sql, the core

There are four key data items to get for each news:

• what it is about (joining, creating, editing, uploading);
• when it occurred (here it comes handy the date semantics function);
• who did it (the user);
• and where happened (the link with further info).

What, when, who and where sounds like a good plan. Let’s see the queries:

a new user joined the wiki

The query is quite easy, new users are those who registered last:

SELECT user_id, user_name, user_registration, "joined", CONCAT("User:", user_name)
FROM user
ORDER BY user_registration DESC

an article was created

This query is probably the most complicated, as it comes from the most important couple of tables in mediawiki, revisions and pages.

Basically, it takes all changes if they had some content (length > 0), if they are new pages (‘Created…’), and only from proper content pages (i.e., not talk pages or special content, that is, namespace = 0):

SELECT r.rev_user, r.rev_user_text, r.rev_timestamp, "created", r.rev_page, p.page_title
FROM `revision` r, page p
WHERE r.rev_page = p.page_id
  AND r.rev_len > 0
  AND r.rev_comment LIKE 'Created%'
  AND p.page_namespace = 0
ORDER BY rev_timestamp DESC

an article was edited

Same as above, changing the comment; since there can be several changes for the same page, we take the latest only:

SELECT r.rev_user, r.rev_user_text, max( r.rev_timestamp ), "edited",  r.rev_page, p.page_title
FROM `revision` r, page p
WHERE r.rev_page = p.page_id
  AND r.rev_len > 0
  AND  r.rev_comment NOT LIKE 'Created%'
  AND p.page_namespace = 0
GROUP BY r.rev_user, r.rev_user_text, r.rev_page, p.page_title
ORDER BY 3 DESC

This query can be improved, depending on each use: not listing sysops editions, or those on the home page. May you have a special need for these, please leave a comment below.

a file was uploaded

Quite easy, after the previous queries:

SELECT i.img_user, i.img_user_text, i.img_timestamp, "uploaded", p.page_id, i.img_name
FROM image i, page p
WHERE p.page_title = i.img_name
ORDER BY img_timestamp DESC

The final query, putting it all together

Every query has different columns, different column names and maybe even types, so they must be unified, and joined through a UNION clause. Also, the ORDER BY must be the same for all.

(SELECT user_id, user_name, user_registration AS d_when, "joined" AS what, 0 AS page_id, CONCAT("User:", user_name) AS page_title FROM user)
UNION
(SELECT r.rev_user AS user_id, r.rev_user_text AS user_name, max( r.rev_timestamp ) AS d_when, "edited" AS what,  r.rev_page AS page_id, p.page_title FROM `revision` r, page p WHERE r.rev_page = p.page_id AND r.rev_len > 0 AND r.rev_comment = "" AND p.page_namespace = 0 GROUP BY r.rev_user, r.rev_user_text, r.rev_page, p.page_title )
UNION
(SELECT r.rev_user AS user_id, r.rev_user_text AS user_name, r.rev_timestamp AS d_when, "created" AS what, r.rev_page AS page_id, p.page_title FROM `revision` r, page p WHERE r.rev_page = p.page_id AND r.rev_len > 0 AND r.rev_comment LIKE 'Created%' AND p.page_namespace = 0 )
UNION
(SELECT i.img_user AS user_id, i.img_user_text AS user_name, i.img_timestamp AS d_when, "uploaded" AS what, p.page_id, i.img_name AS page_title FROM image i, page p WHERE p.page_title = i.img_name)
ORDER BY d_when DESC

Including that in a new special page, and including that special page, that will need another post.

The mediawiki news, aka recent changes by geometrus, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.