first commit in a while

1 files changed, 159 insertions, 0 deletions

diff --git a/feeder/db.py b/feeder/db.py
new file mode 100755
index 0000000..f5f13d3
--- /dev/null
+++ b/feeder/db.py
@@ -0,0 +1,159 @@
+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:
+        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
+
+