User:Kotepho/reports/queries, scripts, and scratch

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