def add_tags(playlistID, tag):
# check if the tag exists already with the playlist
result = db.session.execute(
"SELECT * FROM Tags WHERE PlaylistID = :playlistID",
{"playlistID": playlistID})
for existing_tag in result:
print(tag, existing_tag)
if existing_tag.TagName.lower() == tag.lower():
return send_response(
status=409,
message="You already added this tag to this playlist!")
try:
result = db.session.execute(
'''INSERT INTO Tags (TagName, PlaylistID)
VALUES (:tagName, :playlistID)''', {
"tagName": tag,
"playlistID": playlistID
})
db.session.commit()
except Exception as e:
return send_response(status=500, message="Oops, somethin went wrong")
return send_response(status=200, message="Tag added successfully!")
def login():
data = request.get_json()
username = data.get("username")
password = data.get("password")
if not username or not password:
return send_response(status=400,
message="Username and Password is required")
result = db.session.execute(
"SELECT * FROM User_Account WHERE userID = :username",
{"username": username})
user = result.fetchone()
if not user:
return send_response(status=400, message="Username not found")
elif user.Password != password:
return send_response(status=401,
message="Password incorrect. Try again")
else:
return send_response(status=200,
data={
"username": user.UserID,
"FirstName": user.FirstName,
"LastName": user.LastName,
"FollowingCount": user.FollowingCount,
"FollowerCount": user.FollowerCount
})
def delete_song(playlistID, songID):
try:
# create the playlist
result = db.session.execute(
'''DELETE FROM PlaylistEntry WHERE PlaylistID = :playlistID
AND SongID = :songID''', {
"playlistID": playlistID,
"songID": songID
})
db.session.commit()
return send_response(status=200, message="Song deleted from playlist!")
except Exception as e:
print(e)
return send_response(status=500,
message="Oops, something went wrong. Try again")
def top_tags():
result = db.session.execute(
'''SELECT TagName, COUNT(PlaylistID) AS Count FROM Tags GROUP BY TagName
ORDER BY COUNT(PlaylistID) DESC LIMIT 3''')
tags = [dict(row) for row in result.fetchall()]
return send_response(status=200, data={"tags": tags})
def get_specific_playlist(playlistID):
result = db.session.execute(
'''SELECT * FROM Playlist WHERE PlaylistID = :playlistID''',
{"playlistID": playlistID})
items = [dict(row) for row in result.fetchall()]
# items = [dict(row) for row in result.fetchall()]
return send_response(status=200, data={'PlaylistDetails': items})
def get_user_info(userID):
result = db.session.execute(
"SELECT * FROM User_Account WHERE UserID =:username",
{'username': userID})
userInfo = dict(result.fetchone())
return send_response(status=200, data={"UserInfo": userInfo})
def signup():
data = request.get_json()
firstName = data.get('first_name')
lastName = data.get('last_name')
username = data.get('username')
password = data.get('password')
if not username or not password:
# 400 error, need username or password
return send_response(status=400,
message="Username and Password is required")
# first check if the user exists
result = db.session.execute(
"SELECT userID FROM User_Account WHERE userID = :username",
{"username": username})
result = result.fetchone()
if result is None:
# we can now add this userzz
try:
result = db.session.execute(
'''INSERT INTO User_Account (UserID, Password, FirstName, LastName, FollowingCount, FollowerCount)
VALUES (:username, :password, :firstName, :lastName, :following, :follower)''',
{
"username": username,
"password": password,
"firstName": firstName,
"lastName": lastName,
"following": 0,
"follower": 0
})
db.session.commit()
except Exception as e:
return send_response(
status=500, message="Oops, something went wrong. Try again")
else:
return send_response(
status=409, message="Username already exists! Pick another one")
result = db.session.execute(
"SELECT UserID FROM User_Account WHERE UserID = :username",
{"username": username})
new_user = result.fetchone()
return send_response(status=200, data={"UserID": new_user.UserID})
def top_songs():
result = db.session.execute(
'''SELECT SongTitle, COUNT(SongURL) AS Count FROM PlaylistEntry
GROUP BY SongTitle, SongURL
ORDER BY COUNT(SongURL) DESC
LIMIT 5''')
songs = [dict(row) for row in result.fetchall()]
return send_response(status=200, data={"TopSongs": songs})
def delete_tags(playlistID, tag):
# print(request)
# # data = request.get_json()
# tag = data.get('tag')
try:
result = db.session.execute(
'''DELETE FROM Tags WHERE PlaylistID = :playlistID AND TagName = :tagName''',
{
"playlistID": playlistID,
"tagName": tag
})
db.session.commit()
return send_response(status=200, message="Playlist tag deleted!")
except Exception as e:
print(e)
return send_response(status=500, message="oops, something went wrong.")
def update_playlist_count(playlistID):
# data = request.get_json()
# playlistID = data.get('playlistID')
# print(playlistID)
# up counter by 1
try:
result = db.session.execute(
'''UPDATE Playlist SET PlaylistCount = PlaylistCount + 1 WHERE PlaylistID = :playlistID''',
{"playlistID": playlistID})
db.session.commit()
except Exception as e:
print(e)
return send_response(status=500,
message="Oops, something went wrong. Try again")
return send_response(status=200, message="ok!")
def get_tags(playlistID):
result = db.session.execute(
"SELECT * FROM Tags WHERE PlaylistID = :playlistID",
{"playlistID": playlistID})
tag_list = []
for tags in result:
tag_list.append({"TagName": tags.TagName})
return send_response(status=200, data={"TagList": tag_list})
def filter_user_playlist(tag, userID):
result = db.session.execute(
'''SELECT PlaylistID, Title, Description, DateCreated FROM Playlist NATURAL JOIN Tags WHERE TagName =:tag AND UserID = :userID''',
{
"tag": tag,
"userID": userID
})
items = [dict(row) for row in result.fetchall()]
return send_response(status=200, data={"Playlists: ": items})
def update_playlist(playlistID):
data = request.get_json()
description = data.get('description')
if not description:
return send_response(
status=400, message="Title or Description should be filled out.")
if description:
result = db.session.execute(
'''UPDATE Playlist SET Description = :description WHERE PlaylistID = :playlistID''',
{
"description": description,
"playlistID": playlistID
})
db.session.commit()
return send_response(status=200, message="Updated Playlist!")
def all_tags():
result = db.session.execute(
"SELECT DISTINCT TagName FROM Tags ORDER BY TagName ASC")
tags = []
items = [dict(row) for row in result.fetchall()]
for tag in items:
tags.append(tag["TagName"])
return send_response(status=200, data={"Tags": tags})
def unfollow_user():
# {"curr": str, "follow": str}
data = request.get_json()
current_user: str = data['curr']
follow: str = data['follow']
with graph_db.session() as session:
session.run(f"""
MATCH (u:User {{id: {current_user}}})-[r:FOLLOWS]->(f:User {{id: {follow}}})
DELETE r
""")
return send_response(status=200)
def delete_playlist(userID):
data = request.get_json()
title = data.get('title')
# create unique playlistID identifier (userID-title)
id_title = title.replace(" ", "-")
playlistID = userID + '-' + id_title
try:
# create the playlist
result = db.session.execute(
'''DELETE FROM Playlist WHERE PlaylistID = :playlistID''',
{"playlistID": playlistID})
db.session.commit()
return send_response(status=200, message="Playlist deleted!")
except Exception as e:
print(e)
return send_response(status=500,
message="Oops, something went wrong. Try again")
def follow_user():
# {"curr": int, "follow": int}
data = request.get_json()
current_user: int = data['curr']
follow: int = data['follow']
with graph_db.session() as session:
# lazily add users to graph and then add relationship
session.run(f"""
MERGE (u:User {{id: {current_user}}})
MERGE (f:User {{id: {follow}}})
MERGE (u)-[r:FOLLOWS]->(f)
""")
return send_response(status=200)
def get_playlists(userID):
result = db.session.execute(
"SELECT * From Playlist WHERE userID = :userID", {"userID": userID})
playlists_list = []
for playlist in result:
playlists_list.append({
"PlaylistID": playlist.PlaylistID,
"Title": playlist.Title,
"Description": playlist.Description,
"DateCreated": playlist.DateCreated,
})
return send_response(status=200, data={"Playlists": playlists_list})
def get_mutual_recommendations():
# {"curr": str}
data = request.get_json()
current_user: str = data.get('curr')
users = []
with graph_db.session() as session:
result = session.run(f"""
MATCH (a:User {{ id:{current_user} }})-[:FOLLOWS]->(:User)<-[:FOLLOWS]-(b:User)
WHERE b.id <> a.id
RETURN b.id AS id
""")
for record in result:
users.append(record['id'])
return send_response(status=200, data={'users': users})
def add_song(playlistID):
data = request.get_json()
#songID = data.get('songID')
songTitle = data.get('songTitle')
# For spotify this will be the URI (format = spotify:track:spotifyID)
songURL = data.get('songURL')
# MAKE SURE SOURCE IS LOWERCASE
source = data.get('source')
# Creating SongID here in the form of playlistID-songURL
songID = playlistID + '-' + songURL
# check if song already exists
if not get_songs_helper(playlistID, songURL):
return send_response(status=409, message="Song already added!")
try:
result = db.session.execute(
'''INSERT INTO PlaylistEntry (SongID, PlaylistID, SongTitle, Source,
SongURL)
VALUES (:songID, :playlistID, :songTitle, :source,
:songURL)''', {
"songID": songID,
"playlistID": playlistID,
"songTitle": songTitle,
"source": source,
"songURL": songURL
})
db.session.commit()
return send_response(status=200, message="Added song successfully!")
except Exception as e:
print(e)
return send_response(status=500,
message="Oops, something went wrong. Try again")
def search_playlist(query):
result = db.session.execute(
"SELECT DISTINCT Playlist.Title, Playlist.PlaylistID, Playlist.Description, Playlist.DateCreated, Playlist.UserID FROM Playlist LEFT OUTER JOIN Tags on Playlist.PlaylistID = Tags.PlaylistID WHERE Tags.TagName LIKE '%{0}%' OR Playlist.Title LIKE '%{0}%' OR Playlist.Description LIKE '%{0}%'"
.format(query))
playlists_list = []
items = [dict(row) for row in result.fetchall()]
for playlist in items:
playlists_list.append({
"Title": playlist["Title"],
"PlaylistID": playlist["PlaylistID"],
"Description": playlist["Description"],
"DateCreated": playlist["DateCreated"],
"UserID": playlist["UserID"]
})
return send_response(status=200, data={"SearchResults": playlists_list})
def top_songs_tags():
result = db.session.execute(
'''SELECT DISTINCT SongTitle, Source, SongURL, COUNT(SongURL)
FROM PlaylistEntry NATURAL JOIN Tags t
INNER JOIN
(SELECT TagName
FROM Tags
GROUP BY TagName
ORDER BY COUNT(PlaylistID) DESC
LIMIT 3) as t2
ON t.TagName = t2.TagName
GROUP BY SongTitle, Source, SongURL
ORDER BY COUNT(SongURL) DESC
LIMIT 3''')
songs = [dict(row) for row in result.fetchall()]
return send_response(status=200, data={"TopSongs": songs})
def get_song(playlistID):
result = db.session.execute(
"SELECT * FROM PlaylistEntry WHERE PlaylistID = :playlistID",
{"playlistID": playlistID})
# print(result.fetchone().SongID)
# if result.fetchone().SongID == '':
# print(result)
songs = []
for song in result:
if song.SongID == '':
break
songs.append({
"SongID": song.SongID,
"SongTitle": song.SongTitle,
"Source": song.Source,
"SongURL": song.SongURL
})
return send_response(status=200, data={"Songs": songs})
def create_random_playlist(tag, userID):
# create the playlist
data = request.get_json()
# print('request: ', data)
title = data.get('title')
description = data.get('description')
# create unique playlistID identifier
# important! format = (userID-title), where spaces in original title are
id_title = title.replace(" ", "-")
playlistID = userID + '-' + id_title
# get current date
# format = Month-Day-YYYY
today = date.today()
dateCreated = today.strftime("%b-%d-%Y")
if not title:
return send_response(status=400,
message="Title for your playlist is required")
songs = []
result = db.session.execute(
"SELECT PlaylistID From Playlist WHERE PlaylistID = :playlistID",
{"playlistID": playlistID})
result = result.fetchone()
if result is None:
try:
# create the playlist
result = db.session.execute(
'''INSERT INTO Playlist (PlaylistID, UserID, Title, Description, DateCreated)
VALUES (:playlistID, :userID, :title, :description, :dateCreated)''',
{
"playlistID": playlistID,
"userID": userID,
"title": title,
"description": description,
"dateCreated": dateCreated
})
db.session.commit()
except Exception as e:
print(e)
return send_response(
status=500, message="Oops, something went wrong. Try again")
else:
return send_response(
status=409,
message=
"You already have a playlist with this title, choose another name!"
)
engine = create_engine(url)
connection = engine.raw_connection()
cursor = connection.cursor()
cursor.callproc('Generate_Playlist', [tag, playlistID])
cursor.close()
connection.commit()
connection.close()
# get songs from the top 3 most popular playlists attributed to the tag
engine = create_engine(url)
connection = engine.raw_connection()
cursor = connection.cursor()
cursor.callproc('Popular_Playlist_Songs', [tag, playlistID])
cursor.close()
connection.commit()
connection.close()
result = db.session.execute(
"SELECT * FROM PlaylistEntry WHERE PlaylistID = :playlistID",
{"playlistID": playlistID})
for song in result:
songs.append({
"SongID": song.SongID,
"SongTitle": song.SongTitle,
"Source": song.Source,
"SongURL": song.SongURL
})
return send_response(status=200,
message="Your new playlist has been generated!",
data={
"PlaylistID": playlistID,
"Songs": songs
})
def get_all_users():
result = db.session.execute("SELECT UserID FROM User_Account")
users = [row[0] for row in result.fetchall()]
return send_response(status=200, data={"usernames": users})
def new_playlist(userID):
data = request.get_json()
# userID = data.get('userID')
title = data.get('title')
description = data.get('description')
# create unique playlistID identifier
# important! format = (userID-title), where spaces in original title are
# replaced with '-'
id_title = title.replace(" ", "-")
playlistID = userID + '-' + id_title
# get current date
# format = Month-Day-YYYY
today = date.today()
dateCreated = today.strftime("%b-%d-%Y")
if not title:
return send_response(status=400,
message="Title for your playlist is required")
# check to see if user has a playlist with the same title already
result = db.session.execute(
"SELECT PlaylistID From Playlist WHERE PlaylistID = :playlistID",
{"playlistID": playlistID})
result = result.fetchone()
if result is None:
try:
# create the playlist
result = db.session.execute(
'''INSERT INTO Playlist (PlaylistID, UserID, Title, Description, DateCreated)
VALUES (:playlistID, :userID, :title, :description, :dateCreated)''',
{
"playlistID": playlistID,
"userID": userID,
"title": title,
"description": description,
"dateCreated": dateCreated
})
db.session.commit()
except Exception as e:
print(e)
return send_response(
status=500, message="Oops, something went wrong. Try again")
else:
return send_response(
status=409,
message=
"You already have a playlist with this title, choose another name!"
)
result = db.session.execute(
'''SELECT * FROM Playlist WHERE UserID = :userID AND
Title = :title''', {
"userID": userID,
"title": title
})
playlist = result.fetchone()
return send_response(status=200,
data={
"Playlist": {
"PlaylistID": playlist.PlaylistID,
"Title": playlist.Title,
"Description": playlist.Description,
"DateCreated": playlist.DateCreated,
}
})
