User:Kotepho/reports/queries, scripts, and scratch
From Wikipedia, the free encyclopedia
create table nsnames (id int NOT NULL, name varchar(35) NOT NULL default , PRIMARY KEY id(id)) ENGINE=InnoDB; insert into nsnames VALUES (0,),(1,'Talk'), (2,'User'), (3,'User_talk;'), (4, 'Wikipedia'), (5,'Wikipedia_talk'), (6, 'Image'), (7, 'Image_talk'), (8, 'MediaWiki'), (9, 'MediaWiki_talk'), (10, 'Template'), (11, 'Template_talk'), (12, 'Help'), (13, 'Help_talk'), (14, 'Category'), (15, 'Category_talk'), (100, 'Portal'), ('101', 'Portal_talk'), (-2, 'Media'), (-1, 'Special');
Table for joining on when you want to get real page titles
Images by copyright status statistics: Complicated and with hacky perl code that probably needs to be thrown out and re-written.... I'll get around to it eventually I guess
/Images in pages in Cat: User templates:
/Media used in namespaces other than 0 that contain a template on the image description page that is in category:Non-free_image_copyright_tags:
- both listed on their pages
/deleted page by time:
select page_title, page_touched, page_restrictions from categorylinks INNER JOIN page ON cl_from = page_id WHERE cl_to = 'Protected_deleted_pages' AND page_namespace = 0 ORDER BY page_touched ASC; I think
/deleted page by page id:
select page_title, page_id, page_touched, page_restrictions from categorylinks INNER JOIN page ON cl_from = page_id WHERE cl_to = 'Protected_deleted_pages' AND page_namespace = 0 ORDER BY page_id ASC;
/deleted page by cl_timestamp:
select page_title, cl_timestamp, page_restrictions from categorylinks INNER JOIN page ON cl_from = page_id WHERE cl_to = 'Protected_deleted_pages' AND page_namespace = 0 ORDER BY cl_timestamp ASC;
+ some one off seds that I don't recall atm (I probably also used into outfile to make it easier on my self, will troll .bash_history or redo it later)
/admin/total/ graphs:
select left(log_timestamp,8) as day, COUNT(log_action) as acount into outfile '/tmp/graph-unprotect.txt' from logging where log_action = 'unprotect' group by day with rollup;
comment out the total line with a # (or just don't use rollup, but I wanted it)
gnuplot> set xdata time
gnuplot> set timefmt "%y%m%d"
gnuplot> set title "Unprotects"
gnuplot> set xlabel "Day"
gnuplot> set ylabel "Count"
gnuplot> set terminal png giant size 1024,768
gnuplot> set output 'graph-unprotect.png'
gnuplot> plot '/tmp/graph-unprotect.txt" 1:2
/admin/total/by_user:
/admin/total/blocks:
/admin/month/blocks:
select left(log_timestamp,6) as month, user_name, COUNT(log_action) as acount into outfile '/tmp/name-blocks.txt' from logging INNER JOIN uid ON log_user = user_id where log_action = 'block' group by month, user_name having COUNT(log_action) > 0 order by acount desc; Terribly slow--table scan, temporary table, filesort
