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, } })