def update_user_details(lb_id: int, musicbrainz_id: str, email: str):
""" Update the email field and MusicBrainz ID of the user specified by the lb_id
Args:
lb_id: listenbrainz row id of the user
musicbrainz_id: MusicBrainz username of a user
email: email of a user
"""
with db.engine.connect() as connection:
try:
connection.execute(
sqlalchemy.text("""
UPDATE "user"
SET email = :email
, musicbrainz_id = :musicbrainz_id
WHERE id = :lb_id
"""), {
"lb_id": lb_id,
"musicbrainz_id": musicbrainz_id,
"email": email
})
except sqlalchemy.exc.ProgrammingError as err:
logger.error(err)
raise DatabaseException("Couldn't update user's email: %s" %
str(err))
def delete_user_timeline_event(id: int, user_id: int) -> bool:
''' Deletes recommendation and notification event using id'''
try:
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text('''
DELETE FROM user_timeline_event
WHERE user_id = :user_id
AND id = :id
'''), {
'user_id': user_id,
'id': id
})
return result.rowcount == 1
except Exception as e:
raise DatabaseException(str(e))
def agree_to_gdpr(musicbrainz_id):
""" Update the gdpr_agreed column for user with specified MusicBrainz ID with current time.
Args:
musicbrainz_id (str): the MusicBrainz ID of the user
"""
with db.engine.connect() as connection:
try:
connection.execute(sqlalchemy.text("""
UPDATE "user"
SET gdpr_agreed = NOW()
WHERE LOWER(musicbrainz_id) = LOWER(:mb_id)
"""), {
'mb_id': musicbrainz_id,
})
except sqlalchemy.exc.ProgrammingError as err:
logger.error(err)
raise DatabaseException("Couldn't update gdpr agreement for user: %s" % str(err))
def update_last_login(musicbrainz_id):
""" Update the value of last_login field for user with specified MusicBrainz ID
Args:
musicbrainz_id (str): MusicBrainz username of a user
"""
with db.engine.connect() as connection:
try:
connection.execute(sqlalchemy.text("""
UPDATE "user"
SET last_login = NOW()
WHERE musicbrainz_id = :musicbrainz_id
"""), {
"musicbrainz_id": musicbrainz_id,
})
except sqlalchemy.exc.ProgrammingError as err:
logger.error(err)
raise DatabaseException("Couldn't update last_login: %s" % str(err))
def save(name, creator, members, private=False):
""" Create a new list in the database, with checks for duplicates.
Args:
name (str): the name of the list,
creator (int): the row ID of the list creator,
member ([int]): an ordered list of the row IDs of members of the list
Returns: (int): the row ID of the list
Raises: DatabaseException if list with same name for the same creator already exists
"""
with db.engine.begin() as connection:
list_id = _get_by_creator_and_name(connection, creator, name)
if list_id:
raise DatabaseException("List already exists")
list_id = _create(connection, name, creator, members, private)
return list_id
def delete(id):
""" Delete the user with specified row ID from the database.
Note: this deletes all statistics and api_compat sessions and tokens
associated with the user also.
Args:
id (int): the row ID of the listenbrainz user
"""
with db.engine.connect() as connection:
try:
connection.execute(sqlalchemy.text("""
DELETE FROM "user"
WHERE id = :id
"""), {
'id': id,
})
except sqlalchemy.exc.ProgrammingError as err:
logger.error(err)
raise DatabaseException("Couldn't delete user: %s" % str(err))
def update_musicbrainz_row_id(musicbrainz_id, musicbrainz_row_id):
""" Update the musicbrainz_row_id column for user with specified MusicBrainz username.
Args:
musicbrainz_id (str): the MusicBrainz ID (username) of the user
musicbrainz_row_id (int): the MusicBrainz row ID of the user
"""
with db.engine.connect() as connection:
try:
connection.execute(sqlalchemy.text("""
UPDATE "user"
SET musicbrainz_row_id = :musicbrainz_row_id
WHERE LOWER(musicbrainz_id) = LOWER(:mb_id)
"""), {
'musicbrainz_row_id': musicbrainz_row_id,
'mb_id': musicbrainz_id,
})
except sqlalchemy.exc.ProgrammingError as err:
logger.error(err)
raise DatabaseException("Couldn't update musicbrainz row id for user: %s" % str(err))
def update_user_email(musicbrainz_id, email):
""" Update the email field for user with specified MusicBrainz ID
Args:
musicbrainz_id (str): MusicBrainz username of a user
email (str): email of a user
"""
with db.engine.connect() as connection:
try:
connection.execute(sqlalchemy.text("""
UPDATE "user"
SET email = :email
WHERE musicbrainz_id = :musicbrainz_id
"""), {
"musicbrainz_id": musicbrainz_id,
"email": email
})
except sqlalchemy.exc.ProgrammingError as err:
logger.error(err)
raise DatabaseException(
"Couldn't update user's email: %s" % str(err))
def create_user_timeline_event(
user_id: int,
event_type: UserTimelineEventType,
metadata: UserTimelineEventMetadata,
) -> UserTimelineEvent:
""" Creates a user timeline event in the database and returns the event.
"""
try:
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
INSERT INTO user_timeline_event (user_id, event_type, metadata)
VALUES (:user_id, :event_type, :metadata)
RETURNING id, user_id, event_type, metadata, created
"""), {
'user_id': user_id,
'event_type': event_type.value,
'metadata': ujson.dumps(metadata.dict()),
})
r = dict(result.fetchone())
return UserTimelineEvent(**r)
except Exception as e:
raise DatabaseException(str(e))
