Exemple #1
0
	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
Exemple #2
0
	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
Exemple #3
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
Exemple #4
0
	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
Exemple #5
0
	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'
Exemple #6
0
	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;
Exemple #7
0
	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
Exemple #8
0
	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
Exemple #9
0
	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
Exemple #10
0
	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'
Exemple #11
0
	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'])
Exemple #12
0
	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
Exemple #13
0
	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
Exemple #14
0
	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'
Exemple #15
0
	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
Exemple #16
0
	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
Exemple #17
0
	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;
Exemple #18
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
Exemple #19
0
	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;
Exemple #20
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
Exemple #21
0
	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
Exemple #22
0
	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
Exemple #23
0
	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'])
Exemple #24
0
	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
Exemple #25
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
Exemple #26
0
	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
Exemple #27
0
	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;
Exemple #28
0
	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;
Exemple #29
0
	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
Exemple #30
0
	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