def get_pin_history_for_user(user_id: int, count: int,
offset: int) -> List[PinnedRecording]:
""" Get a list of pinned recordings for the user in descending order of their created date
Args:
user_id: the row ID of the user in the DB
count: number of pinned recordings to be returned
offset: number of pinned recordings to skip from the beginning
Returns:
A list of PinnedRecording objects sorted by newest to oldest creation date.
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT {columns}
FROM pinned_recording as pin
WHERE user_id = :user_id
ORDER BY created DESC
LIMIT :count
OFFSET :offset
""".format(columns=','.join(PINNED_REC_GET_COLUMNS))), {
'user_id': user_id,
'count': count,
'offset': offset
})
return [PinnedRecording(**dict(row)) for row in result.fetchall()]
def get_pins_for_feed(user_ids: List[int], min_ts: int, max_ts: int,
count: int) -> List[PinnedRecording]:
""" Gets a list of PinnedRecordings for specified users in descending order of their created date.
Args:
user_ids: a list of user row IDs
min_ts: History before this timestamp will not be returned
max_ts: History after this timestamp will not be returned
count: Maximum amount of objects to be returned
Returns:
A list of PinnedRecording objects.
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT {columns}
FROM pinned_recording as pin
WHERE pin.user_id IN :user_ids
AND pin.created > :min_ts
AND pin.created < :max_ts
ORDER BY pin.created DESC
LIMIT :count
""".format(columns=','.join(PINNED_REC_GET_COLUMNS))), {
"user_ids": tuple(user_ids),
"min_ts": datetime.utcfromtimestamp(min_ts),
"max_ts": datetime.utcfromtimestamp(max_ts),
"count": count,
})
return [PinnedRecording(**dict(row)) for row in result.fetchall()]
def _pinned_recording_to_api(pinnedRecording: PinnedRecording) -> dict:
pin = pinnedRecording.dict()
pin["created"] = int(pin["created"].timestamp())
pin["pinned_until"] = int(pin["pinned_until"].timestamp())
del pin["user_id"]
if pin["user_name"] is None:
del pin["user_name"]
return pin
def get_current_pin_for_user(user_id: int) -> PinnedRecording:
""" Get the currently active pinned recording for the user if they have one.
Args:
user_id: the row ID of the user in the DB
Returns:
A PinnedRecording object.
"""
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT {columns}
FROM pinned_recording as pin
WHERE (user_id = :user_id
AND pinned_until >= NOW())
""".format(columns=','.join(PINNED_REC_GET_COLUMNS))),
{'user_id': user_id})
row = result.fetchone()
return PinnedRecording(**dict(row)) if row else None
def get_pins_for_user_following(user_id: int, count: int,
offset: int) -> List[PinnedRecording]:
""" Get a list of active pinned recordings for all the users that a user follows sorted
in descending order of their created date.
Args:
user_id: the row ID of the main user in the DB
count: number of pinned recordings to be returned
offset: number of pinned recordings to skip from the beginning
Returns:
A list of PinnedRecording objects sorted by newest to oldest creation date.
"""
COLUMNS_WITH_USERNAME = PINNED_REC_GET_COLUMNS + [
'"user".musicbrainz_id as user_name'
]
with db.engine.connect() as connection:
result = connection.execute(
sqlalchemy.text("""
SELECT {columns}
FROM user_relationship
JOIN "user"
ON user_1 = "user".id
JOIN pinned_recording as pin
ON user_1 = pin.user_id
WHERE user_0 = :user_id
AND relationship_type = 'follow'
AND pinned_until >= NOW()
ORDER BY created DESC
LIMIT :count
OFFSET :offset
""".format(columns=','.join(COLUMNS_WITH_USERNAME))), {
'user_id': user_id,
'count': count,
'offset': offset
})
return [PinnedRecording(**dict(row)) for row in result.fetchall()]
def pin(pinned_recording: WritablePinnedRecording):
""" Inserts a pinned recording record into the database for the user.
If the user already has an active pinned recording, it will be unpinned before the new one is pinned.
Args:
pinned_recording: An object of class WritablePinnedRecording
Returns:
a PinnedRecording based on the input argument with the addition of a user_id field
"""
args = {
'user_id': pinned_recording.user_id,
'recording_msid': pinned_recording.recording_msid,
'recording_mbid': pinned_recording.recording_mbid,
'blurb_content': pinned_recording.blurb_content,
'pinned_until': pinned_recording.pinned_until,
'created': pinned_recording.created
}
with db.engine.connect() as connection:
connection.execute(
sqlalchemy.text("""
UPDATE pinned_recording
SET pinned_until = NOW()
WHERE (user_id = :user_id AND pinned_until >= NOW())
"""), {"user_id": pinned_recording.user_id})
result = connection.execute(
sqlalchemy.text("""
INSERT INTO pinned_recording (user_id, recording_msid, recording_mbid, blurb_content, pinned_until, created)
VALUES (:user_id, :recording_msid, :recording_mbid, :blurb_content, :pinned_until, :created)
RETURNING (id)
"""), args)
row_id = result.fetchone()["id"]
pinned_recording.row_id = row_id
return PinnedRecording.parse_obj(pinned_recording.dict())