def vote(self, upvote, id, remote_addr):
"""+1 a given comment. Returns the new like count (may not change because
the creater can't vote on his/her own comment and multiple votes from the
same ip address are ignored as well)."""
rv = self.db.execute(
'SELECT likes, dislikes, voters FROM comments WHERE id=?', (id, )) \
.fetchone()
if rv is None:
return None
likes, dislikes, voters = rv
if likes + dislikes >= 142:
return {'likes': likes, 'dislikes': dislikes}
bf = Bloomfilter(bytearray(voters), likes + dislikes)
if remote_addr in bf:
return {'likes': likes, 'dislikes': dislikes}
bf.add(remote_addr)
self.db.execute([
'UPDATE comments SET',
' likes = likes + 1,' if upvote else 'dislikes = dislikes + 1,',
' voters = ?'
'WHERE id=?;'
], (buffer(bf.array), id))
if upvote:
return {'likes': likes + 1, 'dislikes': dislikes}
return {'likes': likes, 'dislikes': dislikes + 1}
def vote(self, upvote, id, remote_addr):
"""+1 a given comment. Returns the new like count (may not change because
the creater can't vote on his/her own comment and multiple votes from the
same ip address are ignored as well)."""
rv = self.db.execute("SELECT likes, dislikes, voters FROM comments WHERE id=?", (id,)).fetchone()
if rv is None:
return None
likes, dislikes, voters = rv
if likes + dislikes >= 142:
return {"likes": likes, "dislikes": dislikes}
bf = Bloomfilter(bytearray(voters), likes + dislikes)
if remote_addr in bf:
return {"likes": likes, "dislikes": dislikes}
bf.add(remote_addr)
self.db.execute(
[
"UPDATE comments SET",
" likes = likes + 1," if upvote else "dislikes = dislikes + 1,",
" voters = ?" "WHERE id=?;",
],
(buffer(bf.array), id),
)
if upvote:
return {"likes": likes + 1, "dislikes": dislikes}
return {"likes": likes, "dislikes": dislikes + 1}
def vote(self, id, remote_addr):
"""+1 a given comment. Returns the new like count (may not change because
the creater can't vote on his/her own comment and multiple votes from the
same ip address are ignored as well)."""
rv = self.db.execute(
'SELECT likes, voters FROM comments WHERE id=?', (id, )) \
.fetchone()
if rv is None:
return None
likes, voters = rv
if likes >= 142:
return {'likes': likes}
bf = Bloomfilter(bytearray(voters), likes)
if remote_addr in bf:
return {'likes': likes}
bf.add(remote_addr)
self.db.execute([
'UPDATE comments SET',
' likes = likes + 1,',
' voters = ?'
'WHERE id=?;'], (buffer(bf.array), id))
return {'likes': likes + 1}
def vote(self, upvote, id, remote_addr):
"""+1 a given comment. Returns the new like count (may not change because
the creater can't vote on his/her own comment and multiple votes from the
same ip address are ignored as well)."""
rv = self.db.fetchone(
'SELECT likes, dislikes, voters FROM comments WHERE id=%s', (id, ))
if rv is None:
return None
likes, dislikes, votersPickle = rv
voters = pickle.loads(votersPickle)
if likes + dislikes >= 142:
return {'likes': likes, 'dislikes': dislikes}
bf = Bloomfilter(voters.array, likes + dislikes)
if remote_addr in bf:
return {'likes': likes, 'dislikes': dislikes}
bf.add(remote_addr)
self.db.commit([
'UPDATE comments SET',
' likes = likes + 1,' if upvote else 'dislikes = dislikes + 1,',
' voters = %s'
'WHERE id=%s;'], (pickle.dumps(bf.array), id))
if upvote:
return {'likes': likes + 1, 'dislikes': dislikes}
return {'likes': likes, 'dislikes': dislikes + 1}
def add(self, uri, c):
"""
Add new comment to DB and return a mapping of :attribute:`fields` and
database values.
"""
if c.get("parent") is not None:
ref = self.get(c["parent"])
if ref.get("parent") is not None:
c["parent"] = ref["parent"]
self.db.execute([
'INSERT INTO comments (', ' tid, parent,'
' created, modified, mode, remote_addr,',
' text, author, email, website, voters, notification)',
'SELECT', ' threads.id, ?,', ' ?, ?, ?, ?,',
' ?, ?, ?, ?, ?, ?', 'FROM threads WHERE threads.uri = ?;'
], (c.get('parent'), c.get('created')
or time.time(), None, c["mode"], c['remote_addr'], c['text'],
c.get('author'), c.get('email'), c.get('website'),
buffer(Bloomfilter(iterable=[c['remote_addr']]).array),
c.get('notification'), uri))
return dict(
zip(
Comments.fields,
self.db.execute(
'SELECT *, MAX(c.id) FROM comments AS c INNER JOIN threads ON threads.uri = ?',
(uri, )).fetchone()))
def add(self, uri, c):
"""
Add a new comment to the database and return public fields as dict.
Initializes voter bloom array with provided :param:`remote_addr` and
adds a new thread to the `main.threads` table.
"""
self.db.execute([
'INSERT INTO comments (',
' tid, parent,'
' created, modified, mode, remote_addr,',
' text, author, email, website, voters )',
'SELECT',
' threads.id, ?,',
' ?, ?, ?, ?,',
' ?, ?, ?, ?, ?',
'FROM threads WHERE threads.uri = ?;'], (
c.get('parent'),
c.get('created') or time.time(), None, c["mode"], c['remote_addr'],
c['text'], c.get('author'), c.get('email'), c.get('website'), buffer(
Bloomfilter(iterable=[c['remote_addr']]).array),
uri)
)
return dict(zip(Comments.fields, self.db.execute(
'SELECT *, MAX(c.id) FROM comments AS c INNER JOIN threads ON threads.uri = ?',
(uri, )).fetchone()))
def migrate(self, to):
if self.version >= to:
return
logger.info("migrate database from version %i to %i", self.version, to)
# re-initialize voters blob due a bug in the bloomfilter signature
# which added older commenter's ip addresses to the current voters blob
if self.version == 0:
from isso.utils import Bloomfilter
bf = buffer(Bloomfilter(iterable=["127.0.0.0"]).array)
with sqlite3.connect(self.path) as con:
con.execute('UPDATE comments SET voters=?', (bf, ))
con.execute('PRAGMA user_version = 1')
logger.info("%i rows changed", con.total_changes)
# move [general] session-key to database
if self.version == 1:
with sqlite3.connect(self.path) as con:
if self.conf.has_option("general", "session-key"):
con.execute('UPDATE preferences SET value=? WHERE key=?', (
self.conf.get("general", "session-key"), "session-key"))
con.execute('PRAGMA user_version = 2')
logger.info("%i rows changed", con.total_changes)
# limit max. nesting level to 1
if self.version == 2:
def first(rv):
return list(map(operator.itemgetter(0), rv))
with sqlite3.connect(self.path) as con:
top = first(con.execute(
"SELECT id FROM comments WHERE parent IS NULL").fetchall())
flattened = defaultdict(set)
for id in top:
ids = [id, ]
while ids:
rv = first(con.execute(
"SELECT id FROM comments WHERE parent=?", (ids.pop(), )))
ids.extend(rv)
flattened[id].update(set(rv))
for id in flattened.keys():
for n in flattened[id]:
con.execute(
"UPDATE comments SET parent=? WHERE id=?", (id, n))
con.execute('PRAGMA user_version = 3')
logger.info("%i rows changed", con.total_changes)
def vote(self, upvote, id, remote_addr):
"""+1 a given comment. Returns the new like count (may not change because
the creater can't vote on his/her own comment and multiple votes from the
same ip address are ignored as well)."""
rv = self.db.execute(
'SELECT likes, dislikes, voters FROM comments WHERE id=?', (id, )) \
.fetchone()
if rv is None:
return None
operation_name = 'Upvote' if upvote else 'Downvote'
likes, dislikes, voters = rv
if likes + dislikes >= MAX_LIKES_AND_DISLIKES:
message = '{} denied due to a "likes + dislikes" total too high ({} >= {})'.format(
operation_name, likes + dislikes, MAX_LIKES_AND_DISLIKES)
logger.debug('Comments.vote(id=%s): %s', id, message)
return {'likes': likes, 'dislikes': dislikes, 'message': message}
bf = Bloomfilter(bytearray(voters), likes + dislikes)
if remote_addr in bf:
message = '{} denied because a vote has already been registered for this remote address: {}'.format(
operation_name, remote_addr)
logger.debug('Comments.vote(id=%s): %s', id, message)
return {'likes': likes, 'dislikes': dislikes, 'message': message}
bf.add(remote_addr)
self.db.execute([
'UPDATE comments SET',
' likes = likes + 1,' if upvote else 'dislikes = dislikes + 1,',
' voters = ?'
'WHERE id=?;'
], (buffer(bf.array), id))
if upvote:
return {'likes': likes + 1, 'dislikes': dislikes}
return {'likes': likes, 'dislikes': dislikes + 1}
def add(self, uri, c):
"""
Add new comment to DB and return a mapping of :attribute:`fields` and
database values.
"""
if c.get("parent") is not None:
ref = self.get(c["parent"])
if ref.get("parent") is not None:
c["parent"] = ref["parent"]
self.db.commit("""
INSERT INTO comments (
tid, parent,
created, modified, mode, remote_addr,
text, author, email, website,
voters,
notification
)
SELECT
threads.id, %s,
%s, %s, %s, %s,
%s, %s, %s, %s,
%s,
%s
FROM threads WHERE threads.uri = %s;
""", (
c.get('parent'),
c.get('created') or time.time(), None, c["mode"], c['remote_addr'],
c['text'], c.get('author'), c.get('email'), c.get('website'),
pickle.dumps(Bloomfilter(iterable=[c['remote_addr']])),
c.get('notification'),
uri
)
)
logger.info("Added comment for uri %s", uri)
return dict(zip(Comments.fields, self.db.fetchone("""
SELECT * FROM comments AS c
INNER JOIN threads
ON threads.uri = %s
ORDER BY c.id DESC
LIMIT 1
""",
(uri, )
)
))
