Delete spam users from ckan

[kindly]

Spam users where added to thedatahub and we need to clean them.

[kindly]

This was run to delete the users and their mistaken revisions that where created.

BEGIN;
delete from revision r where r.id in (
    select r.id from "user" join revision r on r.author = "user".name 
    left join resource_revision rr on rr.revision_id = r.id 
    left join package_revision pr on pr.revision_id = r.id 
    left join group_revision gr on gr.revision_id = r.id  
    where "user".created between '2011-08-15' and '2011-09-06'
    and gr.id is null and rr.id is null and pr.id is null 
    and ("user".name similar to '%[0-9]' or "user".fullname similar to '[A-Z][a-z]*[A-Z]%') 
    and "user".name not like 'http%'
);
delete from "user" u where u.id in (
    select "user".id from "user" 
    left join revision r on r.author = "user".name 
    where r.id is null and "user".created between '2011-08-15' and '2011-09-06' 
    and ("user".name similar to '%[0-9]' or "user".fullname similar to '[A-Z][a-z]*[A-Z]%') 
    and "user".name not like 'http%'
);
COMMIT;

[dread]

The majority of our 6000 users are still spammers. Can we simply elimenate those who've not made any revisions? Even if we take a few genuine users with it, these are pretty dead accounts anyway.

[dread]

Reopening - this is still a serious problem. We can't link to the user page because it is so embarrassing: ​http://thedatahub.org/user