User:Pathoschild/sandbox
Jump to navigation
Jump to search
(Previously) unprotected templates used in the MediaWiki namespace
[edit]+---------------------+-----------------------------------------+-----------------------------+-------------------+ | template | page | protection_level | protection_expiry | +---------------------+-----------------------------------------+-----------------------------+-------------------+ | Template:Clear | MediaWiki:Loginend | autoconfirmed|autoconfirmed | infinity|infinity | | Template:Green | MediaWiki:Checkuser-summary | autoconfirmed|autoconfirmed | infinity|infinity | | Template:Header | MediaWiki:Proofreadpage_header_template | sysop|sysop | infinity|infinity | | Template:Messagebox | MediaWiki:Uploadtext | autoconfirmed|autoconfirmed | infinity|infinity | | Template:Smaller | MediaWiki:Gadget-altindex | autoconfirmed|autoconfirmed | infinity|infinity | +---------------------+-----------------------------------------+-----------------------------+-------------------+ 5 rows in set (0.56 sec)
---get templates
SELECT
CONCAT(dtn.ns_name, ':', tl_title) AS template,
CONCAT(dpn.ns_name, ':', dpp.page_title) AS page,
protection_level,
protection_expiry
FROM templatelinks
JOIN page
AS dtp
ON page_title = tl_title
AND page_namespace = tl_namespace
AND page_namespace != 8
JOIN toolserver.namespace
AS dtn
ON dtn.dbname = 'enwikisource_p'
AND dtn.ns_id = tl_namespace
---get pages
JOIN page
AS dpp
ON dpp.page_id = tl_from
AND dpp.page_namespace = 8
JOIN toolserver.namespace
AS dpn
ON dpn.dbname = 'enwikisource_p'
AND dpn.ns_id = dpp.page_namespace
---get template protection
LEFT JOIN (
SELECT
pr_page,
GROUP_CONCAT(COALESCE(pr_level, '') SEPARATOR '|') AS protection_level,
GROUP_CONCAT(COALESCE(pr_expiry, '') SEPARATOR '|') AS protection_expiry
FROM page_restrictions
GROUP BY pr_page
)
AS dtr
ON pr_page = dtp.page_id
ORDER BY template;