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 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 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 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 delete_from_albums(album_id):
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('DELETE FROM albums where id = %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 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 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 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 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_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 _reset_albums():
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute('DELETE FROM albums')
conn.commit()
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_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 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_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 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_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 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 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 get_albums():
sql = """
SELECT id, name, artist, url, img, available, channel, added
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 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 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 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 get_album_details_with_users(album_id):
sql = """
SELECT id, name, artist, url, img, available, channel, added, tags_json, users_json
FROM albums
WHERE id = %s;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql, (album_id, ))
return Album.from_values(cur.fetchone())
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 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 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 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 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 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 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 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 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 add_user_to_album(album_id, user):
with closing(get_connection()) as conn:
try:
sql = """
UPDATE albums
SET users_json = users_json || %s
WHERE id = %s AND NOT users_json ? %s;
"""
cur = conn.cursor()
cur.execute(sql, (json.dumps([user]), album_id, user))
conn.commit()
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 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 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 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 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 get_albums_by_tag(tag):
sql = """
SELECT id, name, artist, url, img, available, channel, added, tags_json
FROM albums
WHERE tags_json ? %s
AND available = true;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql, (tag, ))
return Album.albums_from_values(cur.fetchall())
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 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 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 find_album_artist_duplicates():
sql = """
SELECT a1.* from (SELECT id, LOWER(name) as name, LOWER(artist) as artist, url, img, available, channel, added 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 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 get_random_album():
sql = """
SELECT id, name, artist, url, img, available, channel, added, tags_json
FROM albums
WHERE available = true
ORDER BY RANDOM()
LIMIT 1;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
cur.execute(sql)
return Album.from_values(cur.fetchone())
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)
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)
def search_albums(query):
sql = """
SELECT id, name, artist, url, img, available, channel, added, tags_json
FROM albums
WHERE LOWER(name) LIKE %s
OR LOWER(artist) LIKE %s
OR tags_json ? %s
AND available = true;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
term = f'%{query}%'
cur.execute(sql, (term, term, query))
return Album.albums_from_values(cur.fetchall())
except (psycopg2.ProgrammingError, psycopg2.InternalError) as e:
raise DatabaseError(e)
def search_albums(query):
sql = """
SELECT id, name, artist, url, img, available, channel, added, tags_json
FROM albums
WHERE LOWER(name) LIKE %s
OR LOWER(artist) LIKE %s
OR tags_json ? %s
AND available = true;
"""
with closing(get_connection()) as conn:
try:
cur = conn.cursor()
term = f'%{query}%'
cur.execute(sql, (term, term, query))
return Album.albums_from_values(cur.fetchall())
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 create_albums_table():
sql = """
CREATE TABLE IF NOT EXISTS albums (
id varchar PRIMARY KEY,
artist varchar DEFAULT '',
name varchar DEFAULT '',
url varchar DEFAULT '',
img varchar DEFAULT '',
channel varchar DEFAULT '',
available boolean DEFAULT true,
added timestamp DEFAULT now(),
tags_json jsonb DEFAULT '[]',
users_json jsonb DEFAULT '[]'
);"""
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)
