SQL
AخA
USE wikidatawiki_p;
SELECT CONCAT('* [[User:',rev1.rev_user_text,'|]] [[Special:Diff/',rev1.rev_id,'|removed]] a sitelink from [[Q',epp_entity_id,']] at ',rev1.rev_timestamp,
" with comment: ''",REPLACE(REPLACE(REPLACE(rev1.rev_comment,' */','}}'),'/* wbsetsitelink-remove:','{{int:wikibase-entity-summary-wbsetsitelink-remove|'),'}} ','}}: '),"''") AS log
FROM (SELECT * FROM wb_entity_per_page WHERE epp_redirect_target IS NULL) AS epp
JOIN (SELECT * FROM revision WHERE rev_comment LIKE '/* wbsetsitelink-remove:%') AS rev1
ON rev1.rev_page = epp_page_id
LEFT JOIN (SELECT DISTINCT ips_item_id FROM wb_items_per_site) AS ips
ON ips_item_id = epp_entity_id
LEFT JOIN (SELECT * FROM pagelinks WHERE pl_namespace = 0 AND pl_from_namespace = 0) AS links
ON links.pl_title = CONCAT('Q', epp_entity_id)
LEFT JOIN (SELECT DISTINCT ug_user FROM user_groups WHERE ug_group = 'bot') AS bots
ON ug_user = rev_user
LEFT JOIN (SELECT * FROM revision WHERE rev_comment LIKE '/* wbsetsitelink-remove:%') AS rev2
ON rev2.rev_page = rev1.rev_page AND rev2.rev_id > rev1.rev_id
WHERE ips_item_id IS NULL
AND rev2.rev_timestamp IS NULL
AND ug_user IS NULL
GROUP BY epp_entity_id HAVING COUNT(links.pl_from) = 0
ORDER BY rev1.rev_id
LIMIT 2000;
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.