tools: Add migrate DB script

2 files changed, 225 insertions, 0 deletions

diff --git a/tools/db.py b/tools/db.py
new file mode 100644
index 0000000..7a3f31a
--- /dev/null
+++ b/tools/db.py
@@ -0,0 +1,165 @@
+import MySQLdb
+import time
+
+ROOM_VIDEO_LOG_SIZE = 50
+ROOM_CHAT_LOG_SIZE = 50
+ROOM_TOP_LOG_SIZE = 50
+
+IMAGE_EXTENSIONS = ('gif','jpg','peg','png')
+TIME_ZONE = 7
+
+def now ():
+        return int(time.mktime(time.localtime()))
+def is_image (url):
+        return url[-3:].lower() in IMAGE_EXTENSIONS
+
+class db(object):
+        def __init__ (self):
+                self.conn = None
+                self.cursor = None
+                self.connect()
+
+        def connect (self):
+                self.conn = MySQLdb.connect (host = "localhost",
+                                        #read_default_file = "~/.my.cnf",
+					user = "scannerjammer",
+					passwd = "bs~fZY71dk",
+                                        db = "scannerjammer")
+                self.cursor = self.conn.cursor ()
+
+        def execute (self,sql,args=()):
+                try:
+                        self.cursor.execute(sql,args)
+                except MySQLdb.Error, e:
+                        print "Error %d: %s" % (e.args[0], e.args[1])
+                        # sys.exit (1)
+                        self.connect()
+                        self.cursor.execute(sql,args)
+
+        def lastinsertid (self):
+                return DB.conn.insert_id()
+
+	def getRooms (self):
+		self.execute ("SELECT * FROM sj_room")
+		return self.cursor.fetchall ()
+	def getNewVideoCounts (self):
+		self.execute ("SELECT roomid,count(*) FROM sj_video WHERE date > %s GROUP BY roomid", (now() - 86400))
+		rows = self.cursor.fetchall ()
+		try:
+			lookup = {}
+			for pair in rows:
+				lookup[int(pair[0])] = pair[1]
+			return lookup
+		except: 
+			return {}
+	def getRoomByName (self, name):
+		self.execute ("SELECT * FROM sj_room WHERE name=%s LIMIT 1", (name))
+		rows = self.cursor.fetchall ()
+		try:
+			return rows[0]
+		except:
+			return None
+
+	def getTokboxSessionForRoom (self, roomname):
+		self.execute ("SELECT tokbox FROM sj_room WHERE name=%s", (roomname))
+		rows = self.cursor.fetchall ()
+		try:
+			return rows[0][0]
+		except:
+			return None
+	def saveTokboxSessionForRoom (self, roomname, sessionid):
+		self.execute ("UPDATE sj_room SET tokbox=%s WHERE name=%s", (sessionid, roomname))
+
+	def getUrlsForUser (self,userid):
+		self.execute ("SELECT * FROM sj_url WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_CHAT_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		urlz = []
+		for row in rows:
+			url = (row[0],row[1],row[4],row[5])
+			urlz.append(url)
+		return urlz
+
+	def getImagesForUser (self,userid):
+		self.execute ("SELECT * FROM sj_url WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_CHAT_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		urlz = []
+		for row in rows:
+			if not is_image(row[5]):
+				continue
+			url = (row[0],row[1],row[4],row[5])
+			urlz.append(url)
+		return urlz
+
+	def getTopVideos (self):
+		self.execute ("SELECT * FROM sj_video WHERE removed != 1 ORDER BY likes DESC LIMIT %s", (ROOM_TOP_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		vidz = []
+		for row in rows:
+			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
+			vidz.append(vid)
+		return vidz
+
+	def getTopVideosToday (self):
+		n = int ( now() ) - 3600 * TIME_ZONE
+		today = n - (n % 86400)
+		self.execute ("SELECT * FROM sj_video WHERE date > %s AND removed != 1 ORDER BY likes DESC LIMIT %s", (today, ROOM_TOP_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		vidz = []
+		for row in rows:
+			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
+			vidz.append(vid)
+		return vidz
+
+	def getTopVideosYesterday (self):
+		n = int ( now() ) - 3600 * TIME_ZONE
+		today = n - (n % 86400)
+		yesterday = today - 86400
+		self.execute ("SELECT * FROM sj_video WHERE date > %s AND date < %s AND removed != 1 ORDER BY likes DESC LIMIT %s", (yesterday, today, ROOM_TOP_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		vidz = []
+		for row in reversed(rows):
+			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
+			vidz.append(vid)
+		return vidz
+
+	def getTopVideosForUser (self, userid):
+		self.execute ("SELECT * FROM sj_video WHERE userid=%s ORDER BY likes DESC LIMIT %s", (userid,ROOM_TOP_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		vidz = []
+		for row in rows:
+			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
+			vidz.append(vid)
+		return vidz
+
+	def getLikedVideosForUser (self,userid):
+		self.execute ("SELECT videoid FROM sj_likes WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_VIDEO_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		if not len(rows):
+			return
+		# LIKES:  0 userid  1 videoid  2 date
+		videoids = ",".join([ str(x[0]) for x in rows ])
+		self.execute ("SELECT * FROM sj_video WHERE id IN (%s)" % videoids)
+		rows = self.cursor.fetchall ()
+		vidz = []
+		for row in reversed(rows):
+			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
+			vidz.append(vid)
+		return vidz
+
+	def getVideosForUser (self,userid):
+		self.execute ("SELECT * FROM sj_video WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_VIDEO_LOG_SIZE))
+		rows = self.cursor.fetchall ()
+		vidz = []
+		# 0 id  1 date  2 userid  3 user  4 url  5 title
+		for row in reversed(rows):
+			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
+			vidz.append(vid)
+		return vidz
+
+	def getAllLikes (self):
+		self.execute ("SELECT * FROM sj_likes", ())
+		return self.cursor.fetchall ()
+	def getAllVideos (self):
+		self.execute ("SELECT * FROM sj_video", ())
+		return self.cursor.fetchall ()
+
diff --git a/tools/migrate_db.py b/tools/migrate_db.py
new file mode 100755
index 0000000..16bf30c
--- /dev/null
+++ b/tools/migrate_db.py
@@ -0,0 +1,60 @@
+#!/usr/bin/env python
+
+from db import db as DB
+from pprint import pprint
+
+class MigrateDB(DB):
+
+    def __init__(self, *args, **kwargs):
+        super(MigrateDB, self).__init__(*args, **kwargs)
+
+    def get_table(self, name):
+        self.execute('SELECT * FROM %s' % name)
+        fields = [d[0] for d in self.cursor.description]
+        rows = self.cursor.fetchall()
+        for row in rows:
+            yield dict(zip(fields, row))
+
+    def get_radio_chat_table(self):
+        return self.get_table('radio_chat')
+
+    def get_search_log_table(self):
+        return self.get_table('search_log')
+
+    def get_sj_chat_table(self):
+        return self.get_table('sj_chat')
+
+    def get_sj_likes_table(self):
+        return self.get_table('sj_likes')
+
+    def get_sj_likes_tmp_table(self):
+        return self.get_table('sj_likes_tmp')
+
+    def get_sj_room_table(self):
+        return self.get_table('sj_room')
+
+    def get_sj_search_log_table(self):
+        return self.get_table('sj_search_log')
+
+    def get_sj_session_table(self):
+        return self.get_table('sj_sesson')
+
+    def get_sj_url_table(self):
+        return self.get_table('sj_url')
+
+    def get_sj_url_tmp_table(self):
+        return self.get_table('sj_url_tmp')
+
+    def get_sj_user_table(self):
+        return self.get_table('sj_user')
+
+    def get_sj_video_table(self):
+        return self.get_table('sj_video')
+
+if __name__ == '__main__':
+    db = MigrateDB()
+    db.connect()
+    for row in db.get_sj_user_table():
+        for key, value in row.iteritems():
+             print key, ', ', value, ', ', type(value)
+        print