def recommendedbByCateg(self, id, categ):
par = {
'categ' : categ,
'id' : id
}
db = Db.con()
cur = db.cursor()
qry = """
SELECT *
FROM videos AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM videos)) AS id)
AS r2
WHERE r1.id >= r2.id
AND uniq <> %(id)s
AND category = %(categ)s
ORDER BY r1.id ASC
LIMIT 5
"""
cur.execute(qry, par)
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
db.close()
tags = False
def countRecords(self, dbtable, type, isPublic):
db = Db.con()
cur = db.cursor()
par = {
'type1' : '%"type": "' + type + '"%',
'type2' : '%"type":"' + type + '"%',
'type3' : '%"type" : "' + type + '"%',
'type4' : '%"type" :"' + type + '"%',
'isPublic': isPublic
}
if (type):
if dbtable == 'videos':
qry = "SELECT id FROM videos WHERE is_public = %(isPublic)s AND (data like %(type1)s OR data like %(type2)s OR data like %(type3)s OR data like %(type4)s)"
else:
if dbtable == 'videos':
qry = "SELECT id FROM videos WHERE is_public = %(isPublic)s"
cur.execute(qry, par)
if cur.rowcount > 0:
rec = cur.rowcount
db.close()
return rec
else:
db.close()
return 0
def allUserDataCount(self, param):
username = param['username']
if username == False:
return False;
limit = param['limit'] # 20 per page
curPage = param['curPage'] # 1 is the current page
endAt = int(curPage) * int(limit) # 20 is the record number to end at
startAt = endAt - int(limit) # 20 - 20 = 0 is the record number to start at
newPar = {
'username': username,
'startAt' : startAt,
'limit' : int(limit)
}
db = Db.con()
cur = db.cursor()
qry = "SELECT uniq,category,tags,data,is_public,id,date_added FROM videos WHERE username = %(username)s ORDER BY id DESC LIMIT %(startAt)s, %(limit)s"
cur.execute(qry, newPar)
if cur.rowcount > 0:
records = cur.rowcount
else:
records = 0
db.close()
return records
def deleteEmbed(self, param):
db = Db.con()
cur = db.cursor()
qry = "SELECT uniq,username,category,tags,data FROM videos WHERE id = %(id)s LIMIT 0, 1"
cur.execute(qry, param)
if cur.rowcount > 0:
rows = cur.fetchall()
data = {
'uniq': rows[0][0],
'username': rows[0][1],
'category': rows[0][2],
'tags': rows[0][3],
'data': rows[0][4]
}
# now insert it
q = """
INSERT INTO deleted (uniq, data, username, is_public, category, is_approved, tags)
VALUES
(%(uniq)s, %(data)s, %(username)s, '1', %(category)s, '1', %(tags)s)
"""
cur.execute(q, data)
db.commit()
cur.execute("""DELETE FROM videos WHERE id = %(id)s""", param)
try:
db.commit()
resp = 'success'
except:
resp = False
db.close()
return resp
def add_to_playlist(self, param):
db = Db.con()
cur = db.cursor()
# sample list_ids - ['23,34']
for item in param['list_ids']:
# becomes - 23,34
if item:
# becomes ['23','34']
splt = string.split(item, ",")
for y in splt:
newPar = {
'pl_id': y,
'atpl_id': param['atpl_id']
}
q = """
INSERT INTO playlist_contents (pl_id, vid_id)
VALUES
(%(pl_id)s, %(atpl_id)s)
"""
cur.execute(q, newPar)
db.commit()
db.close()
return 'success'
def allPublicByType(self,param):
type = param['type']
limit = param['limit'] # 20 per page
curPage = param['curPage'] # 1
endAt = int(curPage) * int(limit) # 20
startAt = endAt - int(limit) # 20 - 20 = 0
obj = {
'type1' : '%"type": "' + type + '"%',
'type2' : '%"type":"' + type + '"%',
'type3' : '%"type" : "' + type + '"%',
'type4' : '%"type" :"' + type + '"%',
'startAt' : startAt,
'limit' : int(limit)
}
db = Db.con()
cur = db.cursor()
qry = "SELECT uniq,category,tags,data,date_added FROM videos WHERE is_public = '1' AND (data like %(type1)s OR data like %(type2)s OR data like %(type3)s OR data like %(type4)s) ORDER BY id DESC LIMIT %(startAt)s, %(limit)s"
cur.execute(qry, obj)
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
db.close()
return False;
def allUserData(self, param):
username = param['username']
if username == False:
return False;
# pagination does not work at this time
#limit = param['limit'] # 20 per page
#curPage = param['curPage'] # 1 is the current page
#endAt = int(curPage) * int(limit) # 20 is the record number to end at
#startAt = endAt - int(limit) # 20 - 20 = 0 is the record number to start at
newPar = {
'username': username,
#'startAt' : startAt,
#'limit' : int(limit)
}
db = Db.con()
cur = db.cursor()
qry = "SELECT uniq,category,tags,data,is_public,id,date_added FROM videos WHERE username = %(username)s ORDER BY id DESC"
cur.execute(qry, newPar)
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
db.close()
return False
def allCategories(self):
db = Db.con()
cur = db.cursor()
cur.execute("SELECT * FROM category")
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
return False
def allPublicById(self,id): db = Db.con() cur = db.cursor() qry = "SELECT uniq,category,tags,data,id, date_added FROM videos WHERE uniq = %s" cur.execute(qry, id) if cur.rowcount > 0: row = cur.fetchall() db.close() return row else: db.close() return False
def _insertUniq(self, id, username):
db = Db.con()
cur = db.cursor()
myUniq = base64.b64encode(str(username) + str(id))
par = {
'uniq': myUniq
}
cur.execute("""UPDATE videos SET uniq = %(uniq)s WHERE uniq = 'xdemx'""", par)
db.commit()
db.close()
return 'success'
def add_playlist(self, param):
db = Db.con()
cur = db.cursor()
q = """
INSERT INTO playlist (uniq_id, playlist_name, owner, is_public, description, tags)
VALUES
('xdemx', %(playlist)s, %(username)s, 1, %(description)s, %(tags)s)
"""
cur.execute(q, param)
db.commit()
lastId = cur.lastrowid
db.close()
return self._insertUniq(lastId, param['key'])
def removeEmbed(self, param):
db = Db.con()
cur = db.cursor()
cur.execute("""DELETE FROM playlist_contents WHERE pl_id = %(pl_id)s AND vid_id = %(vid_id)s""", param)
try:
db.commit()
resp = 'success'
except:
resp = False
db.close()
return resp
def user_query(self, param): db = Db.con() cur = db.cursor() q = """ SELECT * FROM users WHERE user_email = %(username)s """ cur.execute(q, param) if cur.rowcount > 0: rows = cur.fetchall() db.close() return rows else: return False
def _insertUniq(self, id, key):
db = Db.con()
cur = db.cursor()
myUniq = base64.b64encode(str(key) + str(id))
par = {
'uniq': myUniq
}
cur.execute("""UPDATE playlist SET uniq_id = %(uniq)s WHERE uniq_id = 'xdemx'""", par)
db.commit()
db.close()
return 'success'
def userRegister(self, param): db = Db.con() cur = db.cursor() q = """ INSERT INTO users (user_email, user_pass, user_active) VALUES (%(username)s, %(password)s, 1) """ cur.execute(q, param) try: db.commit() return True except: return False
def embedToPublic(self, param):
db = Db.con()
cur = db.cursor()
cur.execute("""UPDATE videos SET is_public = %(is_public)s WHERE id = %(id)s""", param)
try:
db.commit()
resp = 'success'
except:
resp = False
db.close()
return resp
def allPublicBySearchCount(self,param):
query = param['qry'];
db = Db.con()
cur = db.cursor()
obj = {
'qry' : query
}
sqry = "SELECT uniq,category,tags,data,date_added FROM videos WHERE is_public = 1 AND (tags REGEXP %(qry)s OR category REGEXP %(qry)s OR data REGEXP %(qry)s)"
cur.execute(sqry, obj)
if cur.rowcount > 0:
db.close()
return cur.rowcount
else:
db.close()
return 0;
def playlistToPublic(self, param):
db = Db.con()
cur = db.cursor()
print param
cur.execute("""UPDATE playlist SET is_public = %(is_public)s WHERE id = %(pl_id)s""", param)
try:
db.commit()
resp = 'success'
except:
resp = False
db.close()
return resp
def allPublicByTagCount(self,param):
tag = param['tag'];
db = Db.con()
cur = db.cursor()
obj = {
'tag' : '%' + str(tag) + '%'
}
qry = "SELECT uniq FROM videos WHERE is_public = 1 AND tags like %(tag)s"
cur.execute(qry, obj)
if cur.rowcount > 0:
db.close()
return cur.rowcount
else:
db.close()
return 0;
def getAllTags(self):
db = Db.con()
cur = db.cursor()
i = 0
data = {}
cur.execute("SELECT tags FROM videos WHERE tags <> '' AND is_public = 1 GROUP BY id DESC")
if cur.rowcount > 0:
rows = cur.fetchall()
for row in rows:
tags = row[0]
rArr = tags.split(',')
for item in rArr:
data[i] = item.strip()
i += 1
db.close()
return data
else:
return False
def getPublicPlaylistsCount(self, param): #param may contain username db = Db.con() cur = db.cursor() if param['username']: qry = "SELECT playlist.* FROM playlist, playlist_contents WHERE playlist_contents.pl_id = playlist.id and (playlist.owner = %(username)s OR playlist.is_public = 1) GROUP BY playlist.id" else: qry = "SELECT playlist.* FROM playlist, playlist_contents WHERE playlist_contents.pl_id = playlist.id and playlist.is_public = 1 GROUP BY playlist.id" cur.execute(qry, param) # playlists exist if cur.rowcount > 0: records = cur.rowcount else: records = 0 return records
def deletePlaylist(self, param):
db = Db.con()
cur = db.cursor()
cur.execute("""DELETE FROM playlist WHERE id = %(pl_id)s""", param)
try:
db.commit()
cur.execute("""DELETE FROM playlist_contents WHERE pl_id = %(pl_id)s""", param)
try:
db.commit()
resp = 'success'
except:
resp = False
except:
resp = False
db.close()
return resp
def embed(self, param):
# param is an object
db = Db.con()
cur = db.cursor()
# all new videos are not approved by default (is_approved = 0)
# not approved means that the video is not yet verified by admin
# but by default is visible from the user -- until junked by admin
q = """
INSERT INTO videos (uniq, data, username, is_public, category, is_approved, tags)
VALUES
('xdemx', %(data)s, %(username)s, %(is_public)s, %(category)s, %(is_approved)s, %(tags)s)
"""
cur.execute(q, param)
db.commit()
lastId = cur.lastrowid
db.close()
return self._insertUniq(lastId, param['username'])
def getPublicPlaylistsByTagCount(self, param): param['username'] = '' if param['tag'] == '': return False db = Db.con() cur = db.cursor() tag = param['tag'] tag = '%' + str(tag) + '%' param['tag'] = tag qry = "SELECT playlist.* FROM playlist, playlist_contents WHERE playlist_contents.pl_id = playlist.id and playlist.is_public = 1 AND playlist.tags like %(tag)s GROUP BY playlist.id" cur.execute(qry, param) # playlists exist if cur.rowcount > 0: db.close() return cur.rowcount else: return 0
def recommendedByTags(self, id, tags, categ):
db = Db.con()
cur = db.cursor()
tags = False
if tags:
isWhere = ''
i = 0
spTags = tags.split(', ')
for item in spTags:
if i == 0:
isWhere = "tags like '%" + item + "%'"
else:
isWhere += " OR tags like '%" + item + "%'"
i += 1
qry = """
SELECT *
FROM videos AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM videos)) AS id)
AS r2
WHERE r1.id >= r2.id
AND uniq <> '%s'
AND (%s)
ORDER BY r1.id ASC
LIMIT 5
""" %(id, isWhere)
cur.execute(qry)
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
db.close()
tags = False
if categ:
return self.recommendedbByCateg(id, categ)
else:
return False
def allPublic(self, param):
db = Db.con()
cur = db.cursor()
limit = param['limit'] # 20 per page
curPage = param['curPage'] # 1
endAt = int(curPage) * int(limit) # 20
startAt = endAt - int(limit) # 20 - 20 = 0
newPar = {
'startAt' : startAt,
'limit' : int(limit)
}
qry = "SELECT uniq,category,tags,data,date_added FROM videos WHERE is_public = '1' ORDER BY id DESC LIMIT %(startAt)s, %(limit)s"
cur.execute(qry, newPar)
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
db.close()
return rows
def allPublicBySearch(self,param):
query = param['qry'];
db = Db.con()
cur = db.cursor()
limit = param['limit'] # 20 per page
curPage = param['curPage'] # 1 is the current page
endAt = int(curPage) * int(limit) # 20 is the record number to end at
startAt = endAt - int(limit) # 20 - 20 = 0 is the record number to start at
obj = {
'qry' : query,
'startAt' : startAt,
'limit' : int(limit)
}
sqry = "SELECT uniq,category,tags,data,date_added FROM videos WHERE is_public = 1 AND (tags REGEXP %(qry)s OR category REGEXP %(qry)s OR data REGEXP %(qry)s) ORDER BY id DESC LIMIT %(startAt)s, %(limit)s"
cur.execute(sqry, obj)
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
db.close()
return False;
def allPublicByTag(self,param):
tag = param['tag'];
db = Db.con()
cur = db.cursor()
limit = param['limit'] # 20 per page
curPage = param['curPage'] # 1 is the current page
endAt = int(curPage) * int(limit) # 20 is the record number to end at
startAt = endAt - int(limit) # 20 - 20 = 0 is the record number to start at
obj = {
'tag' : '%' + str(tag) + '%',
'startAt' : startAt,
'limit' : int(limit)
}
qry = "SELECT uniq,category,tags,data,date_added FROM videos WHERE is_public = 1 AND tags like %(tag)s LIMIT %(startAt)s, %(limit)s"
cur.execute(qry, obj)
if cur.rowcount > 0:
rows = cur.fetchall()
db.close()
return rows
else:
db.close()
return False;
def getPlaylists(self, param):
# param object contains the username
db = Db.con()
cur = db.cursor()
robj = {}
i = 0
# get all playlists from this user
qry = "SELECT * FROM playlist WHERE owner = %(username)s"
cur.execute(qry, param)
# playlists exist
if cur.rowcount > 0:
rows = cur.fetchall()
# check each playlist as item
for item in rows:
# make sure yobj is inialized inside the for loop where you are
# assigning the value to it. Otherwise, unexpected and bizzare
# things happen when it is not properly re-initiazed upon loop
# in this case:
### obj[0].yobj is blank, obj[1].yobj is not blank.
### if yobj is not re-initialized below, obj[0].yobj inherits
### the value of obj[1].yobj.... totally strange!!!!
yobj = {}
plid = item[0] # get the playlist id
# get the video ids related to this playlist
qry = "SELECT vid_id FROM playlist_contents WHERE pl_id = %s"
cur.execute(qry, plid)
k = 0
# if video ids exist
if cur.rowcount > 0:
xrows = cur.fetchall()
# loop each one
for xitem in xrows:
vid_id = xitem[0]
# now get the videos. even the private ones since
# this is going to be seen only by the owner of the playlist
qry = "SELECT uniq,category,tags,data,id FROM videos WHERE id = %s"
cur.execute(qry, vid_id)
if cur.rowcount > 0:
yrows = cur.fetchall()
for yitem in yrows:
yobj[k] = {
'uniq': yitem[0],
'category': yitem[1],
'tags': yitem[2],
'data': yitem[3],
'id':yitem[4]
}
k = k + 1
try:
test = yobj[0]
except:
yobj = False
robj[i] = {
'id' : item[0],
'uniq': item[1],
'playlist_name' : item[2],
'owner': item[3],
'is_public': item[4],
'description': item[5],
'tags' : item[6],
'embeds': yobj
}
else:
try:
test = yobj[0]
except:
yobj = False
robj[i] = {
'id' : item[0],
'uniq': item[1],
'playlist_name' : item[2],
'owner': item[3],
'is_public': item[4],
'description': item[5],
'tags' : item[6],
'embeds': yobj
}
i = i + 1
try:
test = robj[0]
except:
robj = False
db.close()
return robj
def getPublicPlaylistsByTag(self, param):
if param['tag'] == '':
return False
db = Db.con()
cur = db.cursor()
robj = {}
tag = param['tag']
tag = '%' + str(tag) + '%'
param['tag'] = tag
limit = param['limit'] # 20 per page
curPage = param['curPage'] # 1
endAt = int(curPage) * int(limit) # 20
startAt = endAt - int(limit) # 20 - 20 = 0
param['startAt'] = startAt
param['limit'] = int(limit)
i = 0
qry = "SELECT playlist.* FROM playlist, playlist_contents WHERE playlist_contents.pl_id = playlist.id and playlist.is_public = 1 AND playlist.tags like %(tag)s GROUP BY playlist.id LIMIT %(startAt)s, %(limit)s"
cur.execute(qry, param)
# playlists exist
if cur.rowcount > 0:
rows = cur.fetchall()
# check each playlist as item
for item in rows:
# make sure yobj is inialized inside the for loop where you are
# assigning the value to it. Otherwise, unexpected and bizzare
# things happen when it is not properly re-initiazed upon loop
# in this case:
### obj[0].yobj is blank, obj[1].yobj is not blank.
### if yobj is not re-initialized below, obj[0].yobj inherits
### the value of obj[1].yobj.... totally strange!!!!
yobj = {}
plid = item[0] # get the playlist id
# get the video ids related to this playlist
qry = "SELECT vid_id FROM playlist_contents WHERE pl_id = %s"
cur.execute(qry, plid)
k = 0
# if video ids exist
if cur.rowcount > 0:
xrows = cur.fetchall()
# loop each one
for xitem in xrows:
vid_id = xitem[0]
param['vid_id'] = vid_id
if param['username']:
qry = "SELECT uniq,category,tags,data,id FROM videos WHERE id = %(vid_id)s AND (username = %(username)s OR is_public = 1)"
else:
# just get the public ones
qry = "SELECT uniq,category,tags,data,id FROM videos WHERE id = %(vid_id)s AND is_public = 1"
cur.execute(qry, param)
if cur.rowcount > 0:
yrows = cur.fetchall()
for yitem in yrows:
yobj[k] = {
'uniq': yitem[0],
'category': yitem[1],
'tags': yitem[2],
'data': yitem[3],
'id':yitem[4]
}
k = k + 1
try:
test = yobj[0]
except:
yobj = False
robj[i] = {
'id' : item[0],
'uniq': item[1],
'playlist_name' : item[2],
'owner': item[3],
'is_public': item[4],
'description': item[5],
'tags' : item[6],
'embeds': yobj
}
else:
try:
test = yobj[0]
except:
yobj = False
robj[i] = {
'id' : item[0],
'uniq': item[1],
'playlist_name' : item[2],
'owner': item[3],
'is_public': item[4],
'description': item[5],
'tags' : item[6],
'embeds': yobj
}
i = i + 1
try:
test = robj[0]
except:
robj = False
db.close()
return robj