This page contains MySQL queries for monitoring template and module protection.
/*
Examine the mainspace transclusion counts and protection levels of
all templates and modules in a category.
*/
SET @category = "Lua-based templates";
SELECT
CONCAT(
IF(page_namespace = 10, "Template:",
IF(page_namespace = 828, "Module:", "???:")),
REPLACE(page_title, "_", " ")
) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM categorylinks
INNER JOIN page ON page_id = cl_from
WHERE
cl_to = REPLACE(@category, " ", "_")
AND page_namespace IN (10, 828)
ORDER BY transclusions DESC;
/*
Examine the mainspace transclusion counts and protection levels of
all templates and modules linked from a page.
*/
SET @page = "Database reports/Unprotected templates with many transclusions/1";
SET @page_ns = 4;
SELECT
CONCAT(
IF(page_namespace = 10, "Template:",
IF(page_namespace = 828, "Module:", "???:")),
REPLACE(page_title, "_", " ")
) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM pagelinks
INNER JOIN page ON page_title = pl_title AND page_namespace = pl_namespace
WHERE
pl_from = (
SELECT
p.page_id
FROM page AS p
WHERE
p.page_title = REPLACE(@page, " ", "_")
AND p.page_namespace = @page_ns
)
AND pl_namespace IN (10, 828)
ORDER BY transclusions DESC;
/*
Examine the mainspace transclusion counts and protection levels of
all templates and modules transcluded from a page.
*/
SET @page = "Barack Obama";
SET @page_ns = 0;
SELECT
CONCAT(
IF(page_namespace = 10, "Template:",
IF(page_namespace = 828, "Module:", "???:")),
REPLACE(page_title, "_", " ")
) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM templatelinks
INNER JOIN page ON page_title = tl_title AND page_namespace = tl_namespace
WHERE
tl_from = (
SELECT
p.page_id
FROM page AS p
WHERE
p.page_title = REPLACE(@page, " ", "_")
AND p.page_namespace = @page_ns
)
AND tl_namespace IN (10, 828)
ORDER BY transclusions DESC;
/*
Examine the mainspace transclusion counts and protection levels of
all modules.
*/
SELECT
CONCAT("Module:", REPLACE(page_title, "_", " ")) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM page
WHERE
page_namespace = 828
ORDER BY transclusions DESC;
This page contains MySQL queries for monitoring template and module protection.
/*
Examine the mainspace transclusion counts and protection levels of
all templates and modules in a category.
*/
SET @category = "Lua-based templates";
SELECT
CONCAT(
IF(page_namespace = 10, "Template:",
IF(page_namespace = 828, "Module:", "???:")),
REPLACE(page_title, "_", " ")
) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM categorylinks
INNER JOIN page ON page_id = cl_from
WHERE
cl_to = REPLACE(@category, " ", "_")
AND page_namespace IN (10, 828)
ORDER BY transclusions DESC;
/*
Examine the mainspace transclusion counts and protection levels of
all templates and modules linked from a page.
*/
SET @page = "Database reports/Unprotected templates with many transclusions/1";
SET @page_ns = 4;
SELECT
CONCAT(
IF(page_namespace = 10, "Template:",
IF(page_namespace = 828, "Module:", "???:")),
REPLACE(page_title, "_", " ")
) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM pagelinks
INNER JOIN page ON page_title = pl_title AND page_namespace = pl_namespace
WHERE
pl_from = (
SELECT
p.page_id
FROM page AS p
WHERE
p.page_title = REPLACE(@page, " ", "_")
AND p.page_namespace = @page_ns
)
AND pl_namespace IN (10, 828)
ORDER BY transclusions DESC;
/*
Examine the mainspace transclusion counts and protection levels of
all templates and modules transcluded from a page.
*/
SET @page = "Barack Obama";
SET @page_ns = 0;
SELECT
CONCAT(
IF(page_namespace = 10, "Template:",
IF(page_namespace = 828, "Module:", "???:")),
REPLACE(page_title, "_", " ")
) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM templatelinks
INNER JOIN page ON page_title = tl_title AND page_namespace = tl_namespace
WHERE
tl_from = (
SELECT
p.page_id
FROM page AS p
WHERE
p.page_title = REPLACE(@page, " ", "_")
AND p.page_namespace = @page_ns
)
AND tl_namespace IN (10, 828)
ORDER BY transclusions DESC;
/*
Examine the mainspace transclusion counts and protection levels of
all modules.
*/
SELECT
CONCAT("Module:", REPLACE(page_title, "_", " ")) AS title,
(
SELECT
COUNT(*)
FROM templatelinks
WHERE
tl_title = page_title
AND tl_namespace = page_namespace
AND tl_from_namespace = 0
) AS transclusions,
(
SELECT
pr_level
FROM page_restrictions
WHERE
pr_page = page_id
AND pr_type = "edit"
AND (pr_expiry = "infinity" OR pr_expiry IS NULL)
) AS protection
FROM page
WHERE
page_namespace = 828
ORDER BY transclusions DESC;