Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100755 52 lines (46 sloc) 2.007 kb
0061115 tools for dealing with spammers
Justine Tunney authored
1 #!/bin/bash
2 #
3 # i recalculate counter fields in database to ensure their
4 # correctness. sometimes stuff like comment karma can get out of sync
5 # if users are purged from the database or a software bug occurs.
6 # this script should be run periodically to fix that.
7 #
8 # to use me run "crontab -e" and add:
9 #
10 # @hourly nice recalculate.sh ows
11 #
12
13 DB=$1
14 [[ $DB ]] || exit 1
15
16 cat <<EOF | psql -q $DB
100d6c4 calculate karma field
Justine Tunney authored
17
0061115 tools for dealing with spammers
Justine Tunney authored
18 update occupywallst_comment as C
19 set ups = coalesce((select count(*)
c4b0012 karma chameleon
Justine Tunney authored
20 from occupywallst_commentvote as CV
21 inner join occupywallst_userinfo as UI
22 on CV.user_id = UI.user_id
23 where comment_id = C.id
24 and is_shadow_banned = false
25 and vote = 1), 0),
0061115 tools for dealing with spammers
Justine Tunney authored
26 downs = coalesce((select count(*)
c4b0012 karma chameleon
Justine Tunney authored
27 from occupywallst_commentvote as CV
28 inner join occupywallst_userinfo as UI
29 on CV.user_id = UI.user_id
30 where comment_id = C.id
31 and is_shadow_banned = false
32 and vote = -1), 0),
0061115 tools for dealing with spammers
Justine Tunney authored
33 karma = coalesce((select sum(vote)
c4b0012 karma chameleon
Justine Tunney authored
34 from occupywallst_commentvote as CV
35 inner join occupywallst_userinfo as UI
36 on CV.user_id = UI.user_id
37 where comment_id = C.id
38 and is_shadow_banned = false), 0)
39 where published > now() - interval '30 day';
100d6c4 calculate karma field
Justine Tunney authored
40
41 update occupywallst_userinfo as U
42 set karma = coalesce((select sum(karma)
43 from occupywallst_comment as C
0274104 removed comments don't contribute to krama
Justine Tunney authored
44 where C.user_id = U.user_id
c4b0012 karma chameleon
Justine Tunney authored
45 and not is_removed
46 and not is_deleted), 0)
47 where U.user_id in (select distinct(user_id)
48 from occupywallst_comment
49 where published > now() - interval '30 day');
100d6c4 calculate karma field
Justine Tunney authored
50
0061115 tools for dealing with spammers
Justine Tunney authored
51 EOF
Something went wrong with that request. Please try again.