def _insert_into_db(self):
self.id = db.c.get_next_id("r4_albums", "album_id")
success = db.c.update(
"INSERT INTO r4_albums (album_id, album_name, album_name_searchable) VALUES (%s, %s, %s)",
(self.id, self.data['name'],
make_searchable_string(self.data['name'])))
return success
def _insert_into_db(self):
self.id = db.c.get_next_id("r4_groups", "group_id")
return db.c.update(
"INSERT INTO r4_groups (group_id, group_name, group_name_searchable) VALUES (%s, %s, %s)",
(self.id, self.data["name"],
make_searchable_string(self.data["name"])),
)
def _insert_into_db(self):
self.id = db.c.get_next_id("r4_albums", "album_id")
success = db.c.update(
"INSERT INTO r4_albums (album_id, album_name, album_name_searchable) VALUES (%s, %s, %s)",
(self.id, self.data["name"], make_searchable_string(self.data["name"])),
)
return success
def _update_db(self): global updated_album_ids success = db.c.update( "UPDATE r4_albums " "SET album_name = %s, album_name_searchable = %s, album_rating = %s " "WHERE album_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.data['rating'], self.id)) updated_album_ids[self.sid][self.id] = True return success
def _update_db(self):
global updated_album_ids
success = db.c.update(
"UPDATE r4_albums " "SET album_name = %s, album_name_searchable = %s " "WHERE album_id = %s",
(self.data["name"], make_searchable_string(self.data["name"]), self.id),
)
if self.sid != 0:
updated_album_ids[self.sid][self.id] = True
return success
def _update_db(self): global updated_album_ids success = db.c.update( "UPDATE r4_albums " "SET album_name = %s, album_name_searchable = %s " "WHERE album_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.id)) if self.sid != 0: updated_album_ids[self.sid][self.id] = True return success
def _update_db(self):
return db.c.update("UPDATE r4_groups SET group_name = %s, group_name_searchable = %s WHERE group_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.id))
def _insert_into_db(self):
self.id = db.c.get_next_id("r4_groups", "group_id")
return db.c.update("INSERT INTO r4_groups (group_id, group_name, group_name_searchable) VALUES (%s, %s, %s)", (self.id, self.data['name'], make_searchable_string(self.data['name'])))
def _update_db(self):
return db.c.update(
"UPDATE r4_artists SET artist_name = %s, artist_name_searchable = %s WHERE artist_id = %s",
(self.data['name'], make_searchable_string(
self.data['name']), self.id))
def _insert_into_db(self):
self.id = db.c.get_next_id("r4_artists", "artist_id")
return db.c.update(
"INSERT INTO r4_artists (artist_id, artist_name, artist_name_searchable) VALUES (%s, %s, %s)",
(self.id, self.data['name'],
make_searchable_string(self.data['name'])))
def _update_db(self):
return db.c.update(
"UPDATE r4_groups SET group_name = %s, group_name_searchable = %s WHERE group_id = %s",
(self.data["name"], make_searchable_string(
self.data["name"]), self.id),
)
def save(self, sids_override = False):
"""
Save song to the database. Does NOT associate metadata.
"""
log.debug("playlist", "saving song to database; manual sids? {}".format(sids_override))
update = False
if self.id:
update = True
else:
potential_id = None
# To check for moved/duplicate songs we try to find if it exists in the db
if self.artist_tag:
potential_id = db.c.fetch_var("SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s AND song_artist_tag = %s", (self.data['title'], self.data['length'], self.artist_tag))
else:
potential_id = db.c.fetch_var("SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s", (self.data['title'], self.data['length']))
if not config.get("allow_duplicate_song") and potential_id:
self.id = potential_id
update = True
if sids_override:
self.data['sids'] = sids_override
elif len(self.data['sids']) == 0:
raise SongHasNoSIDsException
self.data['origin_sid'] = self.data['sids'][0]
file_mtime = 0
if not self.fake:
file_mtime = os.stat(self.filename)[8]
if update:
log.debug("playlist", "updating existing song_id {}".format(self.id))
db.c.update("UPDATE r4_songs \
SET song_filename = %s, \
song_title = %s, \
song_title_searchable = %s, \
song_url = %s, \
song_link_text = %s, \
song_length = %s, \
song_track_number = %s, \
song_disc_number = %s, \
song_year = %s, \
song_scanned = TRUE, \
song_verified = TRUE, \
song_file_mtime = %s, \
song_replay_gain = %s, \
song_origin_sid = %s \
WHERE song_id = %s",
(self.filename, self.data['title'], make_searchable_string(self.data['title']), self.data['url'], self.data['link_text'], self.data['length'], self.data['track_number'], self.data['disc_number'], self.data['year'], file_mtime, self.replay_gain, self.data['origin_sid'], self.id))
if self.artist_tag:
db.c.update("UPDATE r4_songs SET song_artist_tag = %s WHERE song_id = %s", (self.artist_tag, self.id))
else:
self.id = db.c.get_next_id("r4_songs", "song_id")
log.debug("playlist", "inserting a new song with id {}".format(self.id))
db.c.update("INSERT INTO r4_songs \
(song_id, song_filename, song_title, song_title_searchable, song_url, song_link_text, song_length, song_track_number, song_disc_number, song_year, song_origin_sid, song_file_mtime, song_verified, song_scanned, song_replay_gain, song_artist_tag) \
VALUES \
(%s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s)",
(self.id, self.filename, self.data['title'], make_searchable_string(self.data['title']), self.data['url'], self.data['link_text'], self.data['length'], self.data['track_number'], self.data['disc_number'], self.data['year'], self.data['origin_sid'], file_mtime, True, True, self.replay_gain, self.artist_tag))
self.verified = True
self.data['added_on'] = int(timestamp())
current_sids = db.c.fetch_list("SELECT sid FROM r4_song_sid WHERE song_id = %s", (self.id,))
log.debug("playlist", "database sids: {}, actual sids: {}".format(current_sids, self.data['sids']))
for sid in current_sids:
if not self.data['sids'].count(sid):
db.c.update("UPDATE r4_song_sid SET song_exists = FALSE WHERE song_id = %s AND sid = %s", (self.id, sid))
for sid in self.data['sids']:
if current_sids.count(sid):
db.c.update("UPDATE r4_song_sid SET song_exists = TRUE WHERE song_id = %s AND sid = %s", (self.id, sid))
else:
db.c.update("INSERT INTO r4_song_sid (song_id, sid) VALUES (%s, %s)", (self.id, sid))
def _update_db(self):
return db.c.update("UPDATE r4_artists SET artist_name = %s, artist_name_searchable = %s WHERE artist_id = %s", (self.data['name'], make_searchable_string(self.data['name']), self.id))
def save(self, sids_override=False):
"""
Save song to the database. Does NOT associate metadata.
"""
log.debug(
"playlist",
"saving song to database; manual sids? {}".format(sids_override))
update = False
if self.id:
update = True
else:
potential_id = None
# To check for moved/duplicate songs we try to find if it exists in the db
if self.artist_tag:
potential_id = db.c.fetch_var(
"SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s AND song_artist_tag = %s",
(self.data['title'], self.data['length'], self.artist_tag))
else:
potential_id = db.c.fetch_var(
"SELECT song_id FROM r4_songs WHERE song_title = %s AND song_length = %s",
(self.data['title'], self.data['length']))
if not config.get("allow_duplicate_song") and potential_id:
self.id = potential_id
update = True
if sids_override:
self.data['sids'] = sids_override
elif len(self.data['sids']) == 0:
raise SongHasNoSIDsException
self.data['origin_sid'] = self.data['sids'][0]
file_mtime = 0
if not self.fake:
file_mtime = os.stat(self.filename)[8]
if update:
log.debug("playlist",
"updating existing song_id {}".format(self.id))
db.c.update(
"UPDATE r4_songs \
SET song_filename = %s, \
song_title = %s, \
song_title_searchable = %s, \
song_url = %s, \
song_link_text = %s, \
song_length = %s, \
song_track_number = %s, \
song_disc_number = %s, \
song_year = %s, \
song_scanned = TRUE, \
song_verified = TRUE, \
song_file_mtime = %s, \
song_replay_gain = %s, \
song_origin_sid = %s \
WHERE song_id = %s",
(self.filename, self.data['title'],
make_searchable_string(self.data['title']), self.data['url'],
self.data['link_text'], self.data['length'],
self.data['track_number'], self.data['disc_number'],
self.data['year'], file_mtime, self.replay_gain,
self.data['origin_sid'], self.id))
if self.artist_tag:
db.c.update(
"UPDATE r4_songs SET song_artist_tag = %s WHERE song_id = %s",
(self.artist_tag, self.id))
else:
self.id = db.c.get_next_id("r4_songs", "song_id")
log.debug("playlist",
"inserting a new song with id {}".format(self.id))
db.c.update(
"INSERT INTO r4_songs \
(song_id, song_filename, song_title, song_title_searchable, song_url, song_link_text, song_length, song_track_number, song_disc_number, song_year, song_origin_sid, song_file_mtime, song_verified, song_scanned, song_replay_gain, song_artist_tag) \
VALUES \
(%s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s , %s)",
(self.id, self.filename, self.data['title'],
make_searchable_string(self.data['title']), self.data['url'],
self.data['link_text'], self.data['length'],
self.data['track_number'], self.data['disc_number'],
self.data['year'], self.data['origin_sid'], file_mtime, True,
True, self.replay_gain, self.artist_tag))
self.verified = True
self.data['added_on'] = int(timestamp())
current_sids = db.c.fetch_list(
"SELECT sid FROM r4_song_sid WHERE song_id = %s", (self.id, ))
log.debug(
"playlist", "database sids: {}, actual sids: {}".format(
current_sids, self.data['sids']))
for sid in current_sids:
if not self.data['sids'].count(sid):
db.c.update(
"UPDATE r4_song_sid SET song_exists = FALSE WHERE song_id = %s AND sid = %s",
(self.id, sid))
for sid in self.data['sids']:
if current_sids.count(sid):
db.c.update(
"UPDATE r4_song_sid SET song_exists = TRUE WHERE song_id = %s AND sid = %s",
(self.id, sid))
else:
db.c.update(
"INSERT INTO r4_song_sid (song_id, sid) VALUES (%s, %s)",
(self.id, sid))
def post(self):
s = make_searchable_string(self.get_argument("search"))
if len(s) < 3:
raise APIException("search_string_too_short")
s = "%%%s%%" % s
artists = db.c.fetch_all(
"SELECT DISTINCT artist_id AS id, artist_name AS name, artist_name_searchable "
"FROM r4_song_sid "
"JOIN r4_song_artist USING (song_id) "
"JOIN r4_artists USING (artist_id) "
"WHERE sid = %s AND song_exists = TRUE AND artist_name_searchable LIKE %s "
"ORDER BY artist_name_searchable "
"LIMIT 50", (self.sid, s))
if self.user.is_anonymous():
albums = db.c.fetch_all(
"SELECT DISTINCT album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, "
"FALSE AS fave, 0 AS rating_user, FALSE as rating_complete "
"FROM r4_album_sid "
"JOIN r4_albums USING (album_id) "
"WHERE sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s "
"ORDER BY album_name_searchable "
"LIMIT 50", (self.sid, s))
else:
albums = db.c.fetch_all(
"SELECT DISTINCT r4_albums.album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, "
"COALESCE(album_fave, FALSE) AS fave, COALESCE(album_rating_user, 0) AS rating_user, COALESCE(album_rating_complete, FALSE) AS rating_complete "
"FROM r4_album_sid "
"JOIN r4_albums USING (album_id) "
"LEFT JOIN r4_album_ratings ON (r4_albums.album_id = r4_album_ratings.album_id AND r4_album_ratings.user_id = %s AND r4_album_ratings.sid = %s) "
"LEFT JOIN r4_album_faves ON (r4_albums.album_id = r4_album_faves.album_id AND r4_album_faves.user_id = %s) "
"WHERE r4_album_sid.sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s "
"ORDER BY album_name_searchable "
"LIMIT 50",
(self.user.id, self.sid, self.user.id, self.sid, s))
# use bigger query below
# songs = db.c.fetch_all("SELECT DISTINCT song_id, song_title, album_name, song_origin_sid, song_cool, song_cool_end, song_url, song_length, FROM r4_song_sid JOIN r4_songs USING (song_id) JOIN r4_albums USING (album_id) WHERE sid = %s AND song_title_searchable LIKE %s", (self.sid, s))
# base SQL here copy pasted from /rainwave/playlist_objects/album.py
if self.user.is_anonymous():
songs = db.c.fetch_all(
"SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, "
"song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, "
"song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, "
"FALSE AS requestable, song_cool AS cool, song_cool_end AS cool_end, "
"song_artist_parseable AS artist_parseable, "
"0 AS rating_user, FALSE AS fave, "
"r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable "
"FROM r4_song_sid "
"JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) "
"JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) "
"WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s "
"ORDER BY album_name_searchable, song_title_searchable "
"LIMIT 100", (s, self.sid))
else:
songs = db.c.fetch_all(
"SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, "
"song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, "
"song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, "
"TRUE AS requestable, song_cool AS cool, song_cool_end AS cool_end, "
"song_artist_parseable AS artist_parseable, "
"COALESCE(song_rating_user, 0) AS rating_user, COALESCE(song_fave, FALSE) AS fave, "
"r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable "
"FROM r4_song_sid "
"JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) "
"LEFT JOIN r4_song_ratings ON (r4_song_sid.song_id = r4_song_ratings.song_id AND user_id = %s) "
"JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) "
"WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s "
"ORDER BY album_name_searchable, song_title_searchable "
"LIMIT 100", (s, self.user.id, self.sid))
self.append("artists", artists)
self.append("albums", albums)
self.append("songs", songs)
def post(self):
s = make_searchable_string(self.get_argument("search"))
if len(s) < 3:
raise APIException("search_string_too_short")
s = "%%%s%%" % s
artists = db.c.fetch_all(
"SELECT DISTINCT artist_id AS id, artist_name AS name, artist_name_searchable "
"FROM r4_song_sid "
"JOIN r4_song_artist USING (song_id) "
"JOIN r4_artists USING (artist_id) "
"WHERE sid = %s AND song_exists = TRUE AND artist_name_searchable LIKE %s "
"ORDER BY artist_name_searchable "
"LIMIT 50",
(self.sid, s)
)
if self.user.is_anonymous():
albums = db.c.fetch_all(
"SELECT DISTINCT album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, "
"FALSE AS fave, 0 AS rating_user, FALSE as rating_complete "
"FROM r4_album_sid "
"JOIN r4_albums USING (album_id) "
"WHERE sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s "
"ORDER BY album_name_searchable "
"LIMIT 50",
(self.sid, s)
)
else:
albums = db.c.fetch_all(
"SELECT DISTINCT r4_albums.album_id AS id, album_name AS name, album_cool AS cool, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating, album_name_searchable, "
"COALESCE(album_fave, FALSE) AS fave, COALESCE(album_rating_user, 0) AS rating_user, COALESCE(album_rating_complete, FALSE) AS rating_complete "
"FROM r4_album_sid "
"JOIN r4_albums USING (album_id) "
"LEFT JOIN r4_album_ratings ON (r4_albums.album_id = r4_album_ratings.album_id AND r4_album_ratings.user_id = %s AND r4_album_ratings.sid = %s) "
"LEFT JOIN r4_album_faves ON (r4_albums.album_id = r4_album_faves.album_id AND r4_album_faves.user_id = %s) "
"WHERE r4_album_sid.sid = %s AND album_exists = TRUE AND album_name_searchable LIKE %s "
"ORDER BY album_name_searchable "
"LIMIT 50",
(self.user.id, self.sid, self.user.id, self.sid, s)
)
# use bigger query below
# songs = db.c.fetch_all("SELECT DISTINCT song_id, song_title, album_name, song_origin_sid, song_cool, song_cool_end, song_url, song_length, FROM r4_song_sid JOIN r4_songs USING (song_id) JOIN r4_albums USING (album_id) WHERE sid = %s AND song_title_searchable LIKE %s", (self.sid, s))
# base SQL here copy pasted from /rainwave/playlist_objects/album.py
if self.user.is_anonymous():
songs = db.c.fetch_all(
"SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, "
"song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, "
"song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, "
"FALSE AS requestable, song_cool AS cool, song_cool_end AS cool_end, "
"song_artist_parseable AS artist_parseable, "
"0 AS rating_user, FALSE AS fave, "
"r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable "
"FROM r4_song_sid "
"JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) "
"JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) "
"WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s "
"ORDER BY album_name_searchable, song_title_searchable "
"LIMIT 100",
(s, self.sid))
else:
songs = db.c.fetch_all(
"SELECT r4_song_sid.song_id AS id, song_length AS length, song_origin_sid AS origin_sid, song_title AS title, song_added_on AS added_on, "
"song_track_number AS track_number, song_disc_number as disc_number, song_title_searchable, "
"song_url AS url, song_link_text AS link_text, CAST(ROUND(CAST(song_rating AS NUMERIC), 1) AS REAL) AS rating, "
"TRUE AS requestable, song_cool AS cool, song_cool_end AS cool_end, "
"song_artist_parseable AS artist_parseable, "
"COALESCE(song_rating_user, 0) AS rating_user, COALESCE(song_fave, FALSE) AS fave, "
"r4_albums.album_name, r4_songs.album_id, r4_albums.album_name_searchable "
"FROM r4_song_sid "
"JOIN r4_songs ON (r4_song_sid.song_id = r4_songs.song_id AND r4_songs.song_title_searchable LIKE %s) "
"LEFT JOIN r4_song_ratings ON (r4_song_sid.song_id = r4_song_ratings.song_id AND user_id = %s) "
"JOIN r4_albums ON (r4_songs.album_id = r4_albums.album_id) "
"WHERE r4_song_sid.song_exists = TRUE AND r4_songs.song_verified = TRUE AND r4_song_sid.sid = %s "
"ORDER BY album_name_searchable, song_title_searchable "
"LIMIT 100",
(s, self.user.id, self.sid))
self.append("artists", artists)
self.append("albums", albums)
self.append("songs", songs)