Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
Wider EFS Facilities Services blacklist candidate
by
Cryptic
This query is marked as a draft
This query has been published
by
Cryptic
.
This shows pages, existing and deleted, that would be caught by a titleblacklist entry of .*trinit.*optim.* For [[Wikipedia:Sockpuppet investigations/Fajar Mardiansyah]] circa 12 August 2024.
Toggle Highlighting
SQL
SET @pat='(?i)trinit.*optim'; 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 REPLACE(page_title, '_', ' ') RLIKE @pat UNION DISTINCT SELECT ar_namespace, ar_title FROM archive WHERE REPLACE(ar_title, '_', ' ') RLIKE @pat) 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
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...