1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
#!/usr/bin/env python
# Copyright 2005-2006 Canonical Ltd. All rights reserved.
import _pythonpath
import sys
from optparse import OptionParser
from zope.component import getUtility
from canonical.config import config
from canonical.lp import initZopeless, AUTOCOMMIT_ISOLATION
from canonical.launchpad.scripts import (
execute_zcml_for_scripts, logger_options, logger
)
from canonical.launchpad.scripts.lockfile import LockFile
from canonical.launchpad.interfaces import IPersonSet
from canonical.database.sqlbase import connect
_default_lock_file = '/var/lock/launchpad-karma-update.lock'
def update_karma_cache():
"""Update the KarmaCache table for all valid Launchpad users.
For each Launchpad user with a preferred email address, calculate his
karmavalue for each category of actions we have and update his entry in
the KarmaCache table. If a user doesn't have an entry for that category in
KarmaCache a new one will be created.
"""
# We use the autocommit transaction isolation level to minimize
# contention, and also allows us to not bother explicitly calling
# COMMIT all the time. This script in no way relies on transactions,
# so it is safe.
ztm = initZopeless(
dbuser=config.karmacacheupdater.dbuser,
implicitBegin=False, isolation=AUTOCOMMIT_ISOLATION
)
con = connect(config.karmacacheupdater.dbuser)
con.set_isolation_level(AUTOCOMMIT_ISOLATION)
cur = con.cursor()
karma_expires_after = '1 year'
# Calculate everyones karma. Karma degrades each day, becoming
# worthless after karma_expires_after. This query produces odd results
# when datecreated is in the future, but there is really no point adding
# the extra WHEN clause.
log.info("Calculating everyones karma")
cur.execute("""
SELECT person, category, ROUND(SUM(
CASE WHEN datecreated + %(karma_expires_after)s::interval
<= CURRENT_TIMESTAMP AT TIME ZONE 'UTC' THEN 0
ELSE points * (1 - extract(
EPOCH FROM CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - datecreated
) / extract(EPOCH FROM %(karma_expires_after)s::interval))
END
))
FROM Karma, KarmaAction
WHERE action = KarmaAction.id
GROUP BY person, category
""", vars())
# Suck into RAM to avoid tieing up resources on the DB.
results = list(cur.fetchall())
log.info("Got %d (person, category) scores", len(results))
# Note that we don't need to commit each iteration because we are running
# in autocommit mode.
for person, category, points in results:
log.debug(
"Setting person=%(person)d, category=%(category)d, "
"points=%(points)d", vars()
)
if points <= 0:
# Don't allow our table to bloat with inactive users
cur.execute("""
DELETE FROM KarmaCache WHERE
person=%(person)s AND category=%(category)s
""", vars())
else:
# Attempt to UPDATE. If no rows modified, perform an INSERT
cur.execute("""
UPDATE KarmaCache SET karmavalue=%(points)s
WHERE person=%(person)s AND category=%(category)s
""", vars())
assert cur.rowcount in (0, 1), \
'Bad rowcount %r returned from DML' % (cur.rowcount,)
if cur.rowcount == 0:
cur.execute("""
INSERT INTO KarmaCache (person, category, karmavalue)
VALUES (%(person)s, %(category)s, %(points)s)
""", vars())
# VACUUM KarmaCache since we have just touched every record in it
cur.execute("""VACUUM KarmaCache""")
# Update the KarmaTotalCache table
log.info("Rebuilding KarmaTotalCache")
# Trash old records
cur.execute("""
DELETE FROM KarmaTotalCache
WHERE person NOT IN (SELECT person FROM KarmaCache)
""")
# Update existing records
cur.execute("""
UPDATE KarmaTotalCache SET karma_total=sum_karmavalue
FROM (
SELECT person AS sum_person, SUM(karmavalue) AS sum_karmavalue
FROM KarmaCache GROUP BY person
) AS sums
WHERE KarmaTotalCache.person = sum_person
""")
# VACUUM KarmaTotalCache since we have just touched every row in it.
cur.execute("""VACUUM KarmaTotalCache""")
# Insert new records into the KarmaTotalCache table. If deadlocks
# become a problem, first LOCK the corresponding rows in the Person table
# so the bulk insert cannot fail. We don't bother at the moment as this
# would involve granting UPDATE rights on the Person table to the
# karmacacheupdater user.
## cur.execute("BEGIN")
## cur.execute("""
## SELECT * FROM Person
## WHERE id NOT IN (SELECT person FROM KarmaTotalCache)
## FOR UPDATE
## """)
cur.execute("""
INSERT INTO KarmaTotalCache (person, karma_total)
SELECT person, SUM(karmavalue) FROM KarmaCache
WHERE person NOT IN (SELECT person FROM KarmaTotalCache)
GROUP BY person
""")
## cur.execute("COMMIT")
if __name__ == '__main__':
parser = OptionParser()
logger_options(parser)
(options, arguments) = parser.parse_args()
if arguments:
parser.error("Unhandled arguments %s" % repr(arguments))
execute_zcml_for_scripts()
log = logger(options, 'karmacache')
log.info("Updating the karma cache of Launchpad users.")
lockfile = LockFile(_default_lock_file, logger=log)
try:
lockfile.acquire()
except OSError:
log.info("lockfile %s already exists, exiting", _default_lock_file)
sys.exit(1)
try:
update_karma_cache()
finally:
lockfile.release()
log.info("Finished updating the karma cache of Launchpad users.")
|