User:Pathoschild/sandbox

(Previously) unprotected templates used in the MediaWiki namespace

+---------------------+-----------------------------------------+-----------------------------+-------------------+
| 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;