On 02/07/12 18:45, Gregor Martynus wrote:
Hey there,
I really appreciate the help of you and the others in the mailing list,
thanks to your help I was able to extract the data my friend needs for
her wikipedia study.
While it worked as a proof of concept, that the data she needs is
available, it took to much time to process all needed data and she needs
it another 3 to 4 times.
I contact you because you offered me to send an SQL dump back then, does
that mean you've access to the Wikipedia database?
I'm not an SQL expert, but I think based on the MediaWiki database
schema, I could build the SQL queries for the 3 things she needs
1. user_accounts(name, created_at, blocked_at) created manually in 2012
2. pages (namespace_id, title, is_redirect)
3. revisions (page_id, user_id, created_at) created by users from 1.
bytes_diff would be nice, if available
The goal is to find users that manually created an account, have not
been blocked and that made their first contribution in June 2012.
Any chance you could run the queries and send me the results as sql
dumps? Or if not, do you know somebody I could ask?
Thanks again for your help!
--
Gregor Martynus
Given that your requisites here are more specific than those of the bug,
I have made the following query for you, taking advantage that the
recentchanges talbe keeps the data from last month:
select rc_timestamp, rc_user_text, user_registration, rc_namespace,
rc_title, rc_old_len, rc_new_len, rc_new, rc_minor, rc_type,
page_is_redirect from recentchanges JOIN user on (user_id=rc_user) LEFT
JOIN page ON (page_id=rc_cur_id) where rc_timestamp > '201206000000'
and user_registration LIKE '2012%' order by rc_timestamp asc
Some notes:
- This will contain from June 2 to July 2.
- No bytes_diff, but you have old and new byte len :)
- It may have some non-edit log entries.
File lives at
http://toolserver.org/~platonides/sandbox/martynus.txt
I also made a list of the accounts created in 2012 which were made manually:
SELECT user_id, user_name, log_timestamp AS signed_up_at FROM
user LEFT JOIN logging ON user_id = log_user WHERE log_type =
'newusers' AND log_action = 'create' AND log_timestamp >
'20120000000000
File lives at
http://toolserver.org/~platonides/sandbox/martynus2.txt
And of block actions done to users registered in 2012:
SELECT log_action, log_timestamp, log_title, user_registration FROM
logging JOIN user on (log_title=user_name) WHERE log_type = 'block'AND
log_timestamp > '20120000000000' and user_registration >
'20120000000000'
File lives at
http://toolserver.org/~platonides/sandbox/martynus3.txt
I guess that will work for your needs.
Regards
PS: Rows per file:
73426 martynus.txt
54429 martynus2.txt
2893 martynus3.txt