Utilizador:RobinHood70/Useful Queries

All queries on this page assume an appropriate USING command has already been issued, or a database has been selected in the GUI.

Detailed Job Queue

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT job_cmd, COUNT(*) AS Cnt FROM job GROUP BY job_cmd;

Find Parser Function Usage

Uses full-text search index (which ignores hash character) to narrow down the list, then re-uses the same table without a full-text search to detect the leading hash. Formats output as wikilinks for easy copy/paste. Does not detect full usage, only #pfunction. (Could be made to do so, but unless there are a lot of results, it's probably safest and easiest on our servers to let the user figure out what's really a parser function and what's not.)

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SET @parfunc = 'icon'; SELECT CONCAT(':[[{{ns:', CAST(page_namespace AS char), '}}:', page_title, ']]') AS PageName FROM page WHERE page_id IN ( SELECT si_page FROM searchindex WHERE MATCH (si_text) AGAINST (@parfunc) AND (si_text LIKE CONCAT('%#', @parfunc, '%')) );

First-Person Search

Based on the query above, this query uses a whole-word regex to find only the words listed. It limits its search to gamespace only.

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT `page`.page_namespace, `page`.page_title, text.old_text FROM `page` INNER JOIN revision ON `page`.page_latest = revision.rev_id INNER JOIN `text` ON revision.rev_text_id = `text`.old_id WHERE `page`.page_namespace BETWEEN 100 AND 200 AND (`page`.page_namespace & 1) = 0 AND old_text REGEXP '[[:<:]](i|i\'m|i\'ll|i\'ve|me|my|mine|myself)[[:>:]]' ORDER BY `page`.page_namespace, `page`.page_title;

IPs Creating Lots of Accounts

Change HAVING clause to desired cutoff before running.

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT cuc_ip, COUNT(*) AS Aliases FROM (SELECT DISTINCT cuc_user_text, cuc_ip FROM cu_changes WHERE cuc_actiontext = 'was created') AS derived1 GROUP BY cuc_ip HAVING COUNT(*) >= 5 ORDER BY Aliases DESC

Somewhat clunky ranged version:

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT CAST(LEFT(cuc_ip, LOCATE('.', cuc_ip, LOCATE('.', cuc_ip) + 1) - 1) AS CHAR) Address16, COUNT(*) Cnt FROM cu_changes WHERE cuc_actiontext = 'was created' GROUP BY Address16 HAVING Cnt > 1 ORDER BY Cnt DESC

Pages Without Trails

A(n ever so slightly insane) query to pull up a list of pages that don't have trails. This could also have been done by bot, but would have required a lot of data retrieval. First stab, may need to be modified to exclude additional templates, depending what Silencer tells me. :)

The general idea is to list any page which doesn't use any of the templates from Category:Bread Crumb Trail Templates, or one of the indirect trail templates like {{Creature Summary}}. It only looks at custom namespaces, excluding their talk pages, as well as excluding subpages (for now...would need to remove that to check TR and Stirk properly).

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT CONCAT(':[[{{NS:', CAST(page_namespace AS char), '}}:', page_title, ']]') AS PageName FROM `page` WHERE page_namespace >= 100 AND page_namespace & 1 = 0 AND page_is_redirect = 0 AND page_title NOT LIKE '%/%' AND page_id NOT IN (SELECT templatelinks.tl_from FROM (SELECT cl_from excludeTitle FROM categorylinks WHERE cl_to = 'Bread_Crumb_Trail_Templates' UNION SELECT page_id FROM `page` WHERE page_namespace = 10 AND page_title IN ('Book_Summary' , 'City_Summary', 'Creature_Summary', 'Dagerfall_Services_Summary', 'Effect_Summary', 'Ingredient_Summary', 'Morrowind_Town_Table', 'Mod_Summary', 'NPC_Summary', 'Oblivion_World_Summary', 'Place_Summary', 'Shadowkey_NPC_Summary', 'Spell_Summary')) exclusions INNER JOIN `page` ON exclusions.excludeTitle = `page`.page_id INNER JOIN templatelinks ON `page`.page_title = templatelinks.tl_title) ORDER BY page_namespace , page_title LIMIT 0 , 1000

Pages Without Visible Categories

This query will display all pages that have no visible categories. Note that red-linked categories are treated the same as hidden categories. It's moderately long-running, at about 30 seconds.

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT CONCAT(':[[{{NS:', CAST(page_namespace AS char), '}}:', page_title, ']]') AS PageName FROM `page` LEFT JOIN (SELECT DISTINCT categorylinks.cl_from FROM (categorylinks INNER JOIN `page` ON categorylinks.cl_to = `page`.page_title) LEFT JOIN (SELECT `page`.page_id FROM page_props INNER JOIN `page` ON `page`.page_id = page_props.pp_page WHERE page_props.pp_propname = 'hiddencat' AND `page`.page_namespace = 14) hiddenCats ON `page`.page_id = hiddenCats.page_id WHERE hiddenCats.page_id IS NULL) visCats ON `page`.page_id = visCats.cl_from WHERE page_namespace >= 100 AND page_namespace & 1 = 0 AND page_is_redirect = 0 AND visCats.cl_from IS NULL

Slow Search

This search is database intensive and should be avoided except in cases where the MySQL/Lucene full text searches can't do the job. To minimize impact, searching has been limited to article space only, excluding User space. Because namespaces are only translated to names via PHP, only the numeric namespace is available. Use PHP or an API query to get the correct namespace text.

<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT `page`.page_namespace, `page`.page_title, text.old_text FROM `page` INNER JOIN revision ON `page`.page_latest = revision.rev_id INNER JOIN `text` ON revision.rev_text_id = `text`.old_id WHERE (`page`.page_namespace & 1) = 0 AND (`page`.page_namespace != 2) AND (old_text LIKE '% the the %') ORDER BY `page`.page_namespace, `page`.page_title;