From 3b9ca94f5162134c631521db5eeabb00a8106967 Mon Sep 17 00:00:00 2001 From: schneefux Date: Thu, 2 Jun 2016 18:29:12 +0200 Subject: migrate to cleaner database schema --- app.py | 127 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 112 insertions(+), 15 deletions(-) diff --git a/app.py b/app.py index e24f619..edcd9fd 100644 --- a/app.py +++ b/app.py @@ -18,10 +18,84 @@ class dbProxy(object): self.conn.row_factory = self.dict_factory self.c = self.conn.cursor() - def create(self): + def create_v0(self): self.c.execute("CREATE TABLE IF NOT EXISTS jokes(id INTEGER PRIMARY KEY NOT NULL, text TEXT, upvotes INTEGER, downvotes INTEGER, reports INTEGER)") self.c.execute("CREATE TABLE IF NOT EXISTS votes(id INTEGER PRIMARY KEY NOT NULL, ip TEXT, jokeid INTEGER, type INTEGER)") + def create_v1(self): + self.c.execute("CREATE TABLE IF NOT EXISTS v1_jokes(id INTEGER PRIMARY KEY NOT NULL, text TEXT)") + self.c.execute("CREATE TABLE IF NOT EXISTS v1_users(id INTEGER PRIMARY KEY NOT NULL, identifier TEXT)") + self.c.execute("CREATE TABLE IF NOT EXISTS v1_votes(id INTEGER PRIMARY KEY NOT NULL, joke INTEGER, user INTEGER, type TEXT)") + + def migrate_v0to1(self): + self.create_v1() + + # create new jokes + j = self.c.execute("SELECT * FROM jokes").fetchall() + j = [(int(n['id']), n['text']) for n in j] + self.c.executemany("INSERT INTO v1_jokes(id, text) VALUES(?, ?)", j) + + votes = self.c.execute("SELECT * FROM votes").fetchall() + + # create new users + ips = set([v['ip'] for v in votes]) # uniquify + ips = [(ip,) for ip in ips] + self.c.executemany("INSERT INTO v1_users(identifier) VALUES(?)", ips) + + # create votes + for vote in votes: + user = int(self.c.execute("SELECT id FROM v1_users WHERE identifier=?", (vote['ip'],)).fetchone()['id']) + if vote['type'] == -1: + vtype = 'down' + if vote['type'] == 0: + vtype = 'report' + if vote['type'] == 1: + vtype = 'up' + self.c.execute("INSERT INTO v1_votes(joke, user, type) VALUES(?, ?, ?)", (vote['jokeid'], user, vtype)) + + # create pre-v0 votes + self.c.execute("INSERT INTO v1_users(identifier) VALUES ('anonymous')") + anonymous = self.c.lastrowid + jokes = self.c.execute("SELECT id FROM v1_jokes").fetchall() + jokes = [int(j['id']) for j in jokes] + for joke in jokes: + types = ( + ("up", "upvotes"), + ("down", "downvotes"), + ("report", "reports") + ) + for newtype, oldtype in types: + legal_votes = int(self.c.execute("SELECT COUNT(*) FROM v1_votes WHERE id=? AND type=?", (joke, newtype)).fetchone()['COUNT(*)']) + real_votes = int(self.c.execute("SELECT * FROM jokes WHERE id=?", (joke,)).fetchone()[oldtype]) + diff = real_votes - legal_votes + if diff: + self.c.executemany("INSERT INTO v1_votes(joke, user, type) VALUES(?, ?, ?)", [(joke, anonymous, newtype)]*diff) + + # drop old tables + self.c.execute("DROP TABLE jokes") + self.c.execute("DROP TABLE IF EXISTS votes") + + # write + self.conn.commit() + + def database_v(self): + versions = { + 'jokes': 0, + 'v1_jokes': 1 + } + for ver in versions: + if self.c.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?", (ver,)).fetchone()['COUNT(*)'] == 1: + return versions[ver] + return -1 + + def create(self): + if self.database_v() == 0: + print("migrating v0 to v1") + self.migrate_v0to1() + if self.database_v() == -1: + print("creating new v1 db") + self.create_v1() + def close(self): self.conn.close() @@ -38,34 +112,57 @@ class dbProxy(object): return score def getPages(self, perpage): - l = self.c.execute("SELECT COUNT(*) FROM jokes").fetchone()['COUNT(*)'] + l = self.c.execute("SELECT COUNT(*) FROM v1_jokes").fetchone()['COUNT(*)'] return int(l/perpage)+1 def getJokes(self, perpage, page): - jokes = self.c.execute("SELECT * FROM jokes").fetchall() - jokes = sorted(jokes, key=self.sort, reverse=True) - return jokes[page*perpage:(page+1)*perpage] + ret_jokes = [] + jokes = self.c.execute("SELECT * FROM v1_jokes").fetchall() + for joke in jokes: + ret_joke = { + 'id': joke['id'], + 'text': joke['text'] + } + typemap = ( + ("up", "upvotes"), + ("down", "downvotes"), + ("report", "reports") + ) + for key, tag in typemap: + ret_joke[tag] = self.c.execute("SELECT COUNT(*) FROM v1_votes WHERE joke=? AND type=?", (joke['id'], key)).fetchone()['COUNT(*)'] + + ret_jokes.append(ret_joke) + + ret_jokes = sorted(ret_jokes, key=self.sort, reverse=True) + return ret_jokes[page*perpage:(page+1)*perpage] def addJoke(self, text): - self.c.execute("INSERT INTO jokes(text, upvotes, downvotes, reports) VALUES (?, 0, 0, 0)", (text, )) + self.c.execute("INSERT INTO v1_jokes(text) VALUES(?)", (text,)) self.conn.commit() - def voteJoke(self, objectId, down, ip): - if down: - self.c.execute("UPDATE jokes SET downvotes=downvotes+1 WHERE id=?", (objectId,)) + def userByIp(self, ip): + user = self.c.execute("SELECT * FROM v1_users WHERE identifier=?", (ip,)).fetchone() + if user: + return user['id'] else: - self.c.execute("UPDATE jokes SET upvotes=upvotes+1 WHERE id=?", (objectId,)) - self.c.execute("INSERT INTO votes(ip, jokeid, type) VALUES (?, ?, ?)", (ip, objectId, -1 if down else +1)) + self.c.execute("INSERT INTO v1_users(identifier) VALUES(?)", (ip,)) + self.conn.commit() + return self.c.lastrowid + + def voteJoke(self, objectId, down, ip): + user = self.userByIp(ip) + self.c.execute("INSERT INTO v1_votes(joke, user, type) VALUES(?, ?, ?)", (objectId, user, 'down' if down else 'up')) self.conn.commit() def reportJoke(self, objectId, ip): - self.c.execute("UPDATE jokes SET reports=reports+1 WHERE id=?", (objectId,)) - self.c.execute("INSERT INTO votes(ip, jokeid, type) VALUES (?, ?, ?)", (ip, objectId, 0)) + user = self.userByIp(ip) + self.c.execute("INSERT INTO v1_votes(joke, user, type) VALUES(?, ?, 'report')", (objectId, user)) self.conn.commit() def getUserVotes(self, ip): - votes = self.c.execute("SELECT jokeid FROM votes WHERE ip=?", (ip, )).fetchall() - votes = [v['jokeid'] for v in votes] + user = self.userByIp(ip) + votes = self.c.execute("SELECT joke FROM v1_votes WHERE user=?", (user,)).fetchall() + votes = [v['joke'] for v in votes] return votes -- cgit v1.3.1