Obsolete:Tool:Query service/1

This page contains historical information. It may be outdated or unreliable.

Begriffsklärungsseiten

Description	Top linked disambiguation pages on the German Wikipedia and the Dutch Wikipedia.
Assignee	Bryan
JIRA bug	DBQ-1
Source	dewiki_dab.sh / nlwiki_dab.sh
Results	Wikipedia:WikiProjekt Begriffsklärungsseiten/Top-BKS / Wikipedia:Links naar doorverwijspagina's/data
Approximate run time	40 minutes
Interval	weekly

Code

/usr/bin/mysql -hsql-s2 --skip-column-names u_bryan >/home/bryan/public_html/stats/dbquery/dewiki_dab.txt <<EOF
CREATE TABLE IF NOT EXISTS dewiki_disambiguations (page_title VARBINARY(255), page_id INT, page_latest INT, rd_title VARBINARY(255), date DATE, PRIMARY KEY(page_title), INDEX(rd_title), INDEX (date));
CREATE TABLE IF NOT EXISTS dewiki_dablinks (page_title VARBINARY(255), linkcount INT, date DATE, PRIMARY KEY(page_title), INDEX(date));

DELETE FROM dewiki_disambiguations WHERE date = CURDATE();

-- All pages in the main namespace that are in the category "Begriffsklärung"
INSERT INTO dewiki_disambiguations SELECT page_title, page_id, page_latest, NULL as rd_title, CURDATE() AS date FROM dewiki_p.page, dewiki_p.categorylinks WHERE page_namespace = 0 AND page_id = cl_from AND cl_to = "Begriffsklärung";

-- All pages in the main namespace that redirect to a disambiguation page
REPLACE INTO dewiki_disambiguations SELECT p.page_title, p.page_id, p.page_latest, r.rd_title, CURDATE() AS date FROM dewiki_p.page AS p, dewiki_p.redirect AS r, dewiki_disambiguations AS d WHERE p.page_namespace = 0 AND p.page_id = r.rd_from AND r.rd_namespace = 0 AND r.rd_title = d.page_title AND d.date = CURDATE() AND d.rd_title IS NULL;

-- Links to dismabiguation pages
DELETE FROM dewiki_dablinks WHERE date = CURDATE();

-- Links to disambiguations where the link source is in the main namespace
INSERT INTO dewiki_dablinks SELECT d.page_title, COUNT(p.page_id) AS linkcount, CURDATE() as date FROM dewiki_p.page AS p, dewiki_p.pagelinks, dewiki_disambiguations AS d WHERE p.page_namespace = 0 AND p.page_id = pl_from AND (pl_namespace, pl_title) = (0, d.page_title) AND d.date = CURDATE() GROUP BY d.page_title;

-- Get the diffs in linkcount
CREATE TEMPORARY TABLE dewiki_dablinks_diff (page_title VARBINARY(255), cur_linkcount INT, prev_linkcount INT, PRIMARY KEY(page_title));
INSERT INTO dewiki_dablinks_diff (page_title, cur_linkcount, prev_linkcount) SELECT page_title, linkcount, linkcount FROM dewiki_dablinks WHERE date = CURDATE() AND linkcount > 100 ORDER BY linkcount DESC;
SELECT DISTINCT @prev_date := date FROM dewiki_dablinks WHERE date < CURDATE() ORDER BY date DESC LIMIT 1;
UPDATE dewiki_dablinks_diff, dewiki_dablinks SET prev_linkcount = linkcount WHERE dewiki_dablinks_diff.page_title = dewiki_dablinks.page_title AND date = @prev_date;

-- Output in wiki format
SELECT CONCAT('# [[', page_title, ']]: ', cur_linkcount, ' (', (cur_linkcount - prev_linkcount), ') [[Special:Whatlinkshere/', page_title, '|Links]]') FROM dewiki_dablinks_diff ORDER BY cur_linkcount DESC;
EOF

/home/bryan/local/bin/python update.py -w de:wikipedia -p Wikipedia:WikiProjekt_Begriffsklärungsseiten/Top-BKS -t list -s "Updating disambiguations" `mysql -hsql-s2 --skip-column-names -e "SELECT CONCAT('count:', COUNT(*)) FROM dewiki_disambiguations WHERE date = CURDATE(); SELECT CONCAT('linkcount:', SUM(linkcount)) FROM dewiki_dablinks WHERE date = CURDATE(); SELECT CONCAT('today:', CURDATE()); SELECT CONCAT('last:', date) FROM dewiki_dablinks WHERE date < CURDATE() ORDER BY date DESC LIMIT 1;" u_bryan` </home/bryan/public_html/stats/dbquery/dewiki_dab.txt

This page was moved from toolserver:en:Query service/1. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/1/edithistory