SELECT pl_from, NS, page_title, L_NS, L_titles, num_L, SB, IU, WP
FROM (
SELECT
pl_from,
-- pl_from_namespace does not appear to be consistently reliable;
-- it might be better to select from the page table and join the pagelinks table to it.
CASE
-- This fails on pages missing from the page table (presumably because they were deleted).
WHEN pl_from_namespace != page_namespace THEN CONCAT(pl_from_namespace, ' vs. ', page_namespace)
ELSE pl_from_namespace
END AS NS,
page_title,
pl_namespace AS L_NS,
GROUP_CONCAT(pl_title SEPARATOR ' ') AS L_titles,
COUNT(pl_title) AS num_L,
CASE
WHEN MAX(CASE WHEN pl_title LIKE '%/sandbox' THEN 1 END) = 1 THEN '(SB)'
ELSE ''
END AS SB,
CASE
WHEN EXISTS (
SELECT 1
FROM templatelinks
WHERE
tl_from = pl_from
AND tl_title = 'Under_construction'
) THEN '(C)'
ELSE ''
END AS C,
CASE
WHEN EXISTS (
SELECT 1
FROM categorylinks
WHERE
cl_from = pl_from
AND cl_to = 'Pages_using_Under_construction_with_the_placedby_parameter'
) THEN '(PB)'
ELSE ''
END AS C_PB,
CASE
WHEN EXISTS (
SELECT 1
FROM templatelinks
WHERE
tl_from = pl_from
AND (tl_title = 'In use' OR tl_title = 'GOCEinuse')
) THEN '(IU)'
ELSE ''
END AS IU,
CASE
WHEN EXISTS (
SELECT 1
FROM templatelinks
WHERE
tl_from = pl_from
AND tl_title = 'Copyvio-revdel'
) THEN '(RD1)'
ELSE ''
END AS RD1,
CASE
WHEN EXISTS (
SELECT 1
FROM templatelinks
WHERE
tl_from = pl_from
AND tl_title = 'Wikipedia_person_user_link'
) THEN '(WP)'
ELSE ''
END AS WP,
CASE
WHEN EXISTS (
SELECT 1
FROM categorylinks
WHERE
cl_from = pl_from
AND cl_to = 'Candidates_for_speedy_deletion'
) THEN '(CSD)'
ELSE ''
END AS CSD
FROM pagelinks
LEFT JOIN page ON page_id = pl_from
WHERE
pl_from_namespace = 0
AND pl_namespace = 2
-- In the future: AND pl_namespace != 0
GROUP BY pl_from
ORDER BY SB, page_title
) AS t1
WHERE
(
(C = '' AND RD1 = '' AND WP = '' AND CSD = '')
OR num_L != 1
)
AND (C_PB = '' OR num_L != 2)
Currently this query is run on an online database replicate of Wikipedia, so you can see this query's result.
What is this supposed to do? How does it work?
Relevant background: MediaWiki wikis separates pages into namespaces that are intended to store different types of content. On Wikipedia, the article namespace (which contains all of the actual encyclopedia) is the main namespace. Namespaces are somewhat analogous to how Stack Exchange sites separate content into the main questions site and the Meta domain for internal site discussion (however, Wikipedia sorts many things into namespaces that SE sites don't).
This query searches for internal links from articles to the user namespace. Getting all these links is easy; the complexity arises from filtering out some of the results under specific conditions.
Before going further, there's one essential piece of background about the MediaWiki database schema: the columns of each table are specifically named to be distinct, so each column starts with a prefix specific to its originating table. This query uses 4 tables: page
with prefix page_
, pagelinks
with prefix pl_
, templatelinks
with prefix tl_
, and categorylinks
with prefix cl_
.
The basic goals of the query are as follows:
- Take all link on articles (i.e.
pagelinks
rows wherepl_from_namespace = 0
) that link to user namespace (i.e. wherepl_namespace = 2
) from thepagelinks
table GROUP BY pl_from
to allow counting of links per page throughCOUNT(pl_title) AS num_L
, and to generally organize rows as page-specific.- This also uses the aggregate function
GROUP_CONCAT(pl_title SEPARATOR ' ') AS L_titles
to list each link in the final output output.
- This also uses the aggregate function
- Filter out page rows that:
- have any of the following templates:
'Under_construction'
,'Copyvio-revdel'
,'Wikipedia_person_user_link'
or has the category'Candidates_for_speedy_deletion'
- (A page "has a template" if there's a row in
templatelinks
wheretl_from = pl_from AND tl_title = 'Template_title'
. A page "has a category" if there's a row incategorylinks
wherecl_from = pl_from AND cl_to = 'Category_title'
)
- (A page "has a template" if there's a row in
- AND have one link (
numL = 1
)
- have any of the following templates:
- Additionally filter out page rows that have the category
'Pages_using_Under_construction_with_the_placedby_parameter'
AND have two links (numL = 2
) - Take note of rows that have a
page_title
ending with/sandbox
or have the template'In_use'
or'GOCEinuse'
by using what amounts to Boolean columns.
These documentation links may also be useful if one wants to understand the Mediawiki database better, but should not be required to answer the question: Database layout manual, page
table, pagelinks
table, templatelinks
table, categorylinks
table
Improvements I'm looking for
My query already runs in less than a second, so I'm not too concerned about efficiency (though I welcome any suggestions).
What I'm mainly looking for is general SQL advice. I strongly suspect there are much much better ways to handle repeated structures like:
CASE
WHEN EXISTS (
SELECT 1
FROM templatelinks
WHERE
tl_from = pl_from
AND tl_title = 'template_title'
) THEN '(col_name)'
ELSE ''
END AS col_name
If you need any clarification or have any questions, feel free to comment and I will reply.