def add_to_list(album_id):
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('INSERT INTO list (album) VALUES (%s)', (album_id, ))
conn.commit()
except psycopg2.IntegrityError:
raise DatabaseError(f'list item {album_id} already exists')
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def _reset_list():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('DELETE FROM list')
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def reset_users():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute("UPDATE albums SET users_json = '[]';")
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_album_ids():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('SELECT id FROM albums;')
return [c[0] for c in cur.fetchall()]
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def delete_from_list(album_id):
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('DELETE FROM list where album = %s;', (album_id, ))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def add_many_to_list(album_ids):
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.executemany('INSERT INTO list (album) VALUES (%s)', album_ids)
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_albums_unavailable_count():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('SELECT id FROM albums WHERE available = false;')
return cur.rowcount
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_list():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('SELECT album FROM list;')
return [item[0] for item in cur.fetchall()]
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_list_count():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('SELECT album FROM list;')
return cur.rowcount
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def create_list_table():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(
'CREATE TABLE IF NOT EXISTS list (id serial PRIMARY KEY, album varchar);'
)
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def add_to_albums(album_id, artist, name, url, img='', channel=''):
sql = """
INSERT INTO albums (
id,
artist,
name,
url,
img,
channel,
available
) VALUES (%s, %s, %s, %s, %s, %s, %s);"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql, (album_id, artist, name, url, img, channel, True))
conn.commit()
except psycopg2.IntegrityError:
raise DatabaseError(f'album {album_id} already exists')
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_albums_with_users():
sql = """
SELECT id, name, artist, url, img, available, channel, added, released, tags_json, users_json
FROM albums
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql)
return Album.albums_from_values(cur.fetchall())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def update_album_availability(album_id, status):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET available = %s
WHERE id = %s;
"""
cur = conn.cursor()
cur.execute(sql, (bool(status), album_id))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_album_details_from_ids(album_ids):
sql = """
SELECT id, artist, name, url, img, available, channel, added, released
FROM albums
WHERE id IN %s;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql, (album_ids, ))
return Album.albums_from_values(cur.fetchall())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def create_albums_index():
sql = """
CREATE INDEX IF NOT EXISTS alb_lo_name
ON albums (
LOWER(name)
);"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql)
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def remove_user_from_all_albums(user):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET users_json = users_json - %s
WHERE users_json ? %s;
"""
cur = conn.cursor()
cur.execute(sql, (user, user))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def set_album_users(album_id, users):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET users_json = %s
WHERE id = %s;
"""
cur = conn.cursor()
cur.execute(sql, (json.dumps(users), album_id))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def update_album_added(album_id, added):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET added = %s
WHERE id = %s;
"""
cur = conn.cursor()
cur.execute(sql, (added, album_id))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def remove_tag_from_album(album_id, tag):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET tags_json = tags_json - %s
WHERE id = %s;
"""
cur = conn.cursor()
cur.execute(sql, (json.dumps(tag), album_id))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_albums_by_channel(channel):
sql = """
SELECT id, name, artist, url, img, available, channel, added
FROM albums
WHERE channel = %s
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql, (channel, ))
return Album.albums_from_values(cur.fetchall())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def add_released_to_album(album_id, date):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET released = %s
WHERE id = %s;
"""
cur = conn.cursor()
cur.execute(sql, (date, album_id))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def add_user_review_to_album(album_id, user, review):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET reviews_json = reviews_json || %s
WHERE id = %s;
"""
cur = conn.cursor()
cur.execute(sql, (json.dumps([{user: review}]), album_id))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def remove_user_review_from_album(album_id, array_element):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET reviews_json = reviews_json - %s
WHERE id = %s;
"""
cur = conn.cursor()
cur.execute(sql, (array_element, album_id))
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_album_details_with_reviews(album_id):
sql = """
SELECT id, name, artist, url, img, available, channel, added, released, reviews_json
FROM albums
WHERE id = %s;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor(cursor_factory=NamedTupleCursor)
cur.execute(sql, (album_id, ))
return Album.from_values(cur.fetchone())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_album_details_by_url(album_url):
sql = """
SELECT id, name, artist, url, img, available, channel, added, tags_json
FROM albums
WHERE url = %s;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql, (album_url, ))
return Album.from_values(cur.fetchone())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def de_dup():
duplicates = [
(album_id, )
for album_id, count in collections.Counter(get_list()).items()
if count > 1
]
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.executemany('DELETE FROM list where album = %s;', duplicates)
cur.executemany('INSERT INTO list (album) VALUES (%s)', duplicates)
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def find_album_artist_duplicates():
sql = """
SELECT a1.* from (SELECT id, LOWER(name) as name, LOWER(artist) as artist, url, img, available, channel, added, released from albums) a1
LEFT JOIN (SELECT LOWER(artist) as artist, LOWER(name) as name, COUNT(*) as duplicates from albums GROUP BY LOWER(artist), LOWER(name)) a2
ON a1.artist = a2.artist AND a1.name = a2.name
WHERE a2.duplicates > 1
ORDER BY a1.artist, a1.name
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql)
return Album.albums_from_values(cur.fetchall())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def get_random_album():
sql = """
SELECT id, name, artist, url, img, available, channel, added, released, tags_json, reviews_json
FROM albums
WHERE available = true
ORDER BY RANDOM()
LIMIT 1;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor(cursor_factory=NamedTupleCursor)
cur.execute(sql)
return Album.from_values(cur.fetchone())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def search_albums_by_tag(query):
sql = """
SELECT id, name, artist, url, img, available, channel, added, released, tags_json, reviews_json
FROM albums
WHERE tags_json ? %s
AND available = true;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor(cursor_factory=NamedTupleCursor)
# term = f'%{query}%' TODO
cur.execute(sql, (query, ))
return Album.albums_from_values(cur.fetchall())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def add_many_to_albums(albums):
sql = """
INSERT INTO albums (
id,
artist,
name,
url,
img
) VALUES (%s, %s, %s, %s, %s);"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.executemany(sql, albums)
conn.commit()
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
