def AddTrackToAlbum(self, idAlbum, newTrack):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_Track]
@durationSeconds = ?,
@title = ? ,
@trackNumber = ?,
@storagePath = ?,
@idGenre = ?,
@idAlbum = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
params = (newTrack.durationSeconds, newTrack.title,
newTrack.trackNumber, newTrack.storagePath, newTrack.gender,
idAlbum)
connection.cursor.execute(sql, params)
connection.cursor.nextset()
row = int(connection.cursor.fetchval())
connection.save()
print(newTrack.title, row)
return row
def AddContentCreator(self, newContentCreator):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_ContentCreator]
@name = ?,
@lastname = ?,
@stageName = ?,
@password = ?,
@email = ?,
@description = ?,
@imageStoragePath = ?,
@IdGenre = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
params = (newContentCreator.givenName, newContentCreator.lastName,
newContentCreator.stageName, newContentCreator.password,
newContentCreator.email, newContentCreator.description,
newContentCreator.imageStoragePath, None)
connection.cursor.execute(sql, params)
connection.save()
connection.close()
print(newContentCreator.stageName, newContentCreator.password)
def AddAlbum(self, newAlbum, idContentCreator):
releaseDate = datetime.datetime(newAlbum.releaseDate.year,
newAlbum.releaseDate.month,
newAlbum.releaseDate.day)
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_Album]
@title = ?,
@type = ?,
@releaseDate = ?,
@coverPath = ?,
@idContentCreator = ?,
@idGenre = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
params = (newAlbum.title, newAlbum.isSingle, releaseDate,
newAlbum.coverPath, idContentCreator, newAlbum.gender)
connection.cursor.execute(sql, params)
connection.cursor.nextset()
row = int(connection.cursor.fetchval())
connection.save()
print(newAlbum.title, row)
return row
def AddConsumer(self, newConsumer):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_Consumer]
@name = ?,
@lastname = ?,
@email = ?,
@password = ?,
@imageStoragePath = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
params = (newConsumer.givenName, newConsumer.lastName,
newConsumer.email, newConsumer.password,
newConsumer.imageStoragePath)
connection.cursor.execute(sql, params)
connection.cursor.nextset()
row = int(connection.cursor.fetchval())
connection.save()
print(newConsumer.givenName, newConsumer.lastName)
return row
def AddLibrary(self, idConsumer):
connection: SQLConnection = SQLConnection()
connection.open()
sql ="""
INSERT INTO Library (IdConsumer) VALUES (?);
"""
connection.cursor.execute(sql, idConsumer)
connection.save()
def GenerateRadioStation(self, idGender):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
SELECT * FROM Track WHERE Track.IdGenre = ?
"""
connection.cursor.execute(sql, idGender)
rows = connection.cursor.fetchall()
return rows
def DeleteAlbum(self, idAlbum: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DELETE FROM Album WHERE idAlbum = ?
"""
connection.cursor.execute(sql, idAlbum)
connection.save()
connection.close()
def DeleteAlbumTrack(self, idAlbum: int, trackNumber: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DELETE FROM Track WHERE idAlbum = ? AND trackNumber = ?
"""
params = (idAlbum, trackNumber)
connection.cursor.execute(sql, params)
connection.save()
connection.close()
def GetContentCreators(self):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
SELECT * FROM ContentCreator
"""
connection.cursor.execute(sql)
row = connection.cursor.fetchall()
print(row)
return row
def GetContentCreatorByQuery(self, query: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
EXEC [dbo].[SPC_GetContentCreatorByQuery]
@query = ?
"""
connection.cursor.execute(sql, query)
rows = connection.cursor.fetchall()
return rows
def DeleteLibraryAlbum(self, idLibrary: int, idAlbum: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DELETE FROM LibraryAlbum WHERE idLibrary = ? AND idAlbum = ?
"""
params = (idLibrary, idAlbum)
connection.cursor.execute(sql, params)
connection.save()
print("Album has been deleted")
connection.close()
def DeletePlaylistTrack(self, idPlaylist: int, idTrack: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DELETE FROM LibraryTrack WHERE idPlaylist = ? AND idTrack = ?
"""
params = (idPlaylist, idTrack)
connection.cursor.execute(sql, params)
connection.save()
print("Track has been deleted")
connection.close()
def UpdateAlbumTitle(self, idAlbum: int, newAlbumTitle: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
UPDATE Album
SET title = ?
Where idAlbum = ?
"""
params = (newAlbumTitle, idAlbum)
connection.cursor.execute(sql, params)
connection.save()
print("Album title has been updated")
connection.close()
def GetLocalTracksByIdConsumer(self, idConsumer: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPC_GetLocalTracks]
@idConsumer = ?,
@salida = @salida OUTPUT
"""
connection.cursor.execute(sql, idConsumer)
row = connection.cursor.fetchall()
return row
def UpdateAlbumCover(self, idAlbum: int, newCoverStoragePath: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
UPDATE Album
SET coverPath = ?
Where idAlbum = ?
"""
params = (newCoverStoragePath, idAlbum)
connection.cursor.execute(sql, params)
connection.save()
print("Your image has been updated")
connection.close()
def UpdateContentCreatorImage(self, email: str, newImageStoragePath: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
UPDATE ContentCreator
SET imageStoragePath = ?
Where email = ?
"""
params = (newImageStoragePath, email)
connection.cursor.execute(sql, params)
connection.save()
print("Your image has been updated")
connection.close()
def GetConsumerByEmail(self, email: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
SELECT * FROM Consumer WHERE email = ?
"""
connection.cursor.execute(sql, email)
row = connection.cursor.fetchone()
if (row != None):
print(row.email)
return row.email
connection.close()
return None
connection.close()
def UpdateConsumerImage(self, email: str, fileName: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
UPDATE Consumer
SET imageStoragePath = ?
Where email = ?
"""
params = (fileName, email)
connection.cursor.execute(sql, params)
connection.save()
print("Your image has been updated")
connection.close()
def GetContentCreatorByStageName(self, stageName: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
SELECT * FROM ContentCreator WHERE stageName = ?
"""
connection.cursor.execute(sql, stageName)
row = connection.cursor.fetchone()
if (row != None):
print(row.stageName)
return row.stageName
connection.close()
return None
connection.close()
def UpdateAlbumTrackTitle(self, idAlbum: int, trackNumber: int,
newAlbumTrackTitle: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
UPDATE Track
SET title = ?
Where idAlbum = ? AND trackNumber = ?
"""
params = (newAlbumTrackTitle, idAlbum, trackNumber)
connection.cursor.execute(sql, params)
connection.save()
print("Track title has been updated")
connection.close()
def AddTrackToPlaylist(self, idPlaylist: int, idTrack: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
EXEC [dbo].[SPI_TrackPlaylist]
@idTrack = ?,
@idPlaylist = ?
"""
params = (idTrack, idPlaylist)
connection.cursor.execute(sql, params)
connection.save()
return True
def GetConsumerByEmailPassword(self, email: str, password: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
SELECT * FROM Consumer WHERE email = ? AND password = ?
"""
params = (email, password)
connection.cursor.execute(sql, params)
row = connection.cursor.fetchone()
if (row != None):
print(row.email, row.password)
return row
connection.close()
return None
connection.close()
def GetContentCreatorByLibraryId(self, idLibrary):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPC_GetContentCreatorByIdLibrary]
@idLibrary = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
connection.cursor.execute(sql, idLibrary)
row = connection.cursor.fetchall()
return row
def GetLibraryByIdConsumer(self, idConsumer):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPC_GetLibraryConsumer]
@idConsumer = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
connection.cursor.execute(sql, idConsumer)
row = connection.cursor.fetchval()
return row
def GetTrackByPlaylistId(self, idPlaylist: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPC_GetTracksByIdPlaylist]
@idPlaylist= ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
connection.cursor.execute(sql, idPlaylist)
row = connection.cursor.fetchall()
return row
def GetTrackByTitle(self, title: str):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPC_GetTrack]
@title = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
connection.cursor.execute(sql, title)
row = connection.cursor.fetchall()
connection.save()
print(row)
connection.close()
def GetSinglesByContentCreatorId(self, idContentCreator):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPC_GetSinglesByContentCreatorId]
@idContentCreator = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
connection.cursor.execute(sql, idContentCreator)
row = connection.cursor.fetchall()
return row
connection.close()
def AddTrackToLibrary(self, idLibrary: int, idTrack: int):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_LibraryTrack]
@idLibrary = ?,
@idTrack = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
params = (idLibrary, idTrack)
connection.cursor.execute(sql, params)
connection.save()
return True
def AddFeaturingTrack(self, idNewTrack, idContentCreator):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_FeaturingTrack]
@IdTrack = ?,
@IdContentCeator = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
params = (idNewTrack, idContentCreator)
connection.cursor.execute(sql, params)
connection.cursor.nextset()
row = connection.cursor.fetchone()
connection.save()
return row
def AddLocalTrack(self, localTrack):
connection: SQLConnection = SQLConnection()
connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_LocalTrack]
@IdConsumer = ?,
@fileName = ?,
@artistName = ?,
@title = ?,
@salida = @salida OUTPUT
SELECT @salida as N'@salida'
"""
params = (localTrack.idConsumer, localTrack.fileName,
localTrack.artistName, localTrack.title)
connection.cursor.execute(sql, params)
connection.save()
return True