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 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 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 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 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, ) ) ))
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}