https://bugzilla.wikimedia.org/show_bug.cgi?id=59320

--- Comment #2 from Bugzilla Bug Importer (valhallasw) 
<wmf.bugconver...@gmail.com> ---
-------------------------------------------------------------------------------
From: Mr.Z-man <mrzmanwikim...@gmail.com>
Date: Fri, 19 Jun 2009 20:55:35
-------------------------------------------------------------------------------

The hashes are stored for deleted files, concatenated with the file extension
as fa_storage_key in the filearchive table.

#### Most common of all deleted files:

Query: 

    SELECT SUBSTRING(fa_storage_key, 1, 31) AS hash, COUNT(*), fa_name FROM
filearchive 
    WHERE fa_storage_key IS NOT NULL AND fa_storage_key != '' 
    GROUP BY hash ORDER BY COUNT(*) DESC LIMIT 100;


Results: http://toolserver.org/~alexz/DBQ-65/all.txt

#### From the last 6 months:

Query: 

    SELECT SUBSTRING(fa_storage_key, 1, 31) AS hash, COUNT(*), fa_name FROM
filearchive 
    WHERE fa_storage_key IS NOT NULL AND fa_storage_key != '' AND
fa_deleted_timestamp > 20090119000000 
    GROUP BY hash ORDER BY COUNT(*) DESC LIMIT 100;


Results: http://toolserver.org/~alexz/DBQ-65/recent.txt

#### Vandalism noted in deletion reason:

Query: 

    SELECT SUBSTRING(fa_storage_key, 1, 31) AS hash, COUNT(*), fa_name FROM
filearchive 
    WHERE fa_storage_key IS NOT NULL AND fa_storage_key != '' AND
fa_deleted_reason RLIKE "[[:<:]](vandal(ism)?|goatse|shock)[[:>:]]"
    GROUP BY hash ORDER BY COUNT(*) DESC LIMIT 100;


Resuts: http://toolserver.org/~alexz/DBQ-65/vandal.txt

#### Recent vandalism deletions:

Query: 

    SELECT SUBSTRING(fa_storage_key, 1, 31) AS hash, COUNT(*), fa_name FROM
filearchive 
    WHERE fa_storage_key IS NOT NULL AND fa_storage_key != '' AND
fa_deleted_timestamp > 20090119000000 AND fa_deleted_reason RLIKE
"[[:<:]](vandal(ism)?|goatse|shock)[[:>:]]" 
    GROUP BY hash ORDER BY COUNT(*) DESC LIMIT 100;


Results: http://toolserver.org/~alexz/DBQ-65/recentvandal.txt

-- 
You are receiving this mail because:
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l

Reply via email to