1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
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 ()
|
