SQL
x
SET @pat='^(?i)discord/talk.*';
SET @ns=5;
SELECT CONCAT('[[', CASE page_namespace
WHEN -2 THEN 'Media:' WHEN -1 THEN 'Special:'
WHEN 0 THEN '' WHEN 1 THEN 'Talk:'
WHEN 2 THEN 'User:' WHEN 3 THEN 'User talk:'
WHEN 4 THEN 'Wikipedia:' WHEN 5 THEN 'Wikipedia talk:'
WHEN 6 THEN ':File:' WHEN 7 THEN 'File talk:'
WHEN 8 THEN 'MediaWiki:' WHEN 9 THEN 'MediaWiki talk:'
WHEN 10 THEN 'Template:' WHEN 11 THEN 'Template talk:'
WHEN 12 THEN 'Help:' WHEN 13 THEN 'Help talk:'
WHEN 14 THEN ':Category:' WHEN 15 THEN 'Category talk:'
WHEN 100 THEN 'Portal:' WHEN 101 THEN 'Portal talk:'
WHEN 118 THEN 'Draft:' WHEN 119 THEN 'Draft talk:'
WHEN 710 THEN 'TimedText:' WHEN 711 THEN 'TimedText talk:'
WHEN 828 THEN 'Module:' WHEN 829 THEN 'Module talk:'
WHEN 2300 THEN 'Gadget:' WHEN 2301 THEN 'Gadget talk:'
WHEN 2302 THEN 'Gadget definition:' WHEN 2303 THEN 'Gadget definition talk:'
ELSE CONCAT('{{ns:', page_namespace, '}}:')
END, REPLACE(page_title, '_', ' '), ']]') AS title,
EXISTS (SELECT 1
FROM page p2
WHERE p2.page_namespace = sq.page_namespace
AND p2.page_title = sq.page_title) AS existing,
COUNT(log_namespace) AS `times deleted`,
GROUP_CONCAT(log_timestamp SEPARATOR ' // ') AS `deletion timestamps`,
GROUP_CONCAT(comment_text SEPARATOR ' // ') AS `deletion comments`,
(SELECT GROUP_CONCAT(comment_text SEPARATOR ' // ')
FROM logging_logindex
JOIN comment_logging ON comment_id = log_comment_id
WHERE log_namespace = page_namespace
AND log_title = page_title
AND log_type = 'protect'
AND log_action = 'protect'
AND log_params LIKE '%create=%') AS `salt comments`
FROM (SELECT page_namespace, page_title
FROM page
WHERE CONVERT(REPLACE(page_title, '_', ' ') USING utf8) RLIKE @pat
AND (@ns < 0 OR page_namespace = @ns)
UNION DISTINCT
SELECT ar_namespace, ar_title
FROM archive
WHERE CONVERT(REPLACE(ar_title, '_', ' ') USING utf8) RLIKE @pat
AND (@ns < 0 OR ar_namespace = @ns)) sq
LEFT JOIN logging_logindex
ON log_namespace = page_namespace
AND log_title = page_title
AND log_action = 'delete'
AND log_type = 'delete'
LEFT JOIN comment_logging ON comment_id = log_comment_id
GROUP BY page_namespace, page_title
ORDER BY `existing` DESC, page_namespace ASC, page_title ASC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.