User:Dispenser/Mistyped namespaces
Jump to navigation
Jump to search
-- 2> /dev/null; date; echo '
/* Mistyped namespaces from File: pages
*
* Author: Dispenser
* License: Public domain
* Run time: 1 min
*/
SELECT
CONCAT("[",
"{{fullurl:Special:Search|search=insource:/\[\[",
SUBSTRING_INDEX(pl_title, ":", 1), ":/}} ",
SUBSTRING_INDEX(pl_title, ":", 1),"]",
" <small>([http://toolserver.org/~dispenser/cgi-bin/redlinks.py/",
SUBSTRING_INDEX(pl_title, ":", 1), ":?dbname=commonswiki index])</small>"
) AS "Prefix",
COUNT(*) AS "Count",
COUNT(DISTINCT pl_title) AS "Targets",
/* TODO report [[CategoryWithoutPrefix]] */
/*page_title=REGEXP_REPLACE(pl_title, "^[A-Za-z]{1,16}[:;.,]+_*", "") */
SUM(EXISTS (SELECT 1 FROM page WHERE page_namespace=14
AND page_title=SUBSTRING_INDEX(pl_title, ":", -1))) AS "CatFix",
CONCAT("[[", pl_title, "]] ([[Special:WhatLinksHere/", pl_title, "|← links]])") AS "Example"
FROM pagelinks
LEFT JOIN page ON page_namespace=0 AND page_title=pl_title
WHERE pl_namespace=0
AND pl_title REGEXP "^[^#*;:&-()<=>{|}]+:" /* Avoid autolinking junk */
AND pl_from_namespace=6 /* File descriptions only */
AND page_id IS NULL /* Target is a red link */
GROUP BY 1
ORDER BY COUNT(*) DESC
LIMIT 140
;-- ' | mysql -ch commonswiki.labsdb commonswiki_p --html > ~/public_html/logs/mistyped_ns.html; date;
_327.jpg&action=edit&redlink=1){kind=link}
_327.jpg){kind=link}
