def get_random_song_timed(sid, target_seconds = None, target_delta = None):
"""
Fetch a random song abiding by all election block, request block, and
availability rules, but giving priority to the target song length
provided. Falls back to get_random_song on failure.
"""
if not target_seconds:
return get_random_song(sid)
if not target_delta:
target_delta = config.get_station(sid, "song_lookup_length_delta")
sql_query = ("FROM r4_song_sid "
"JOIN r4_songs USING (song_id) "
"JOIN r4_album_sid ON (r4_album_sid.album_id = r4_songs.album_id AND r4_album_sid.sid = r4_song_sid.sid) "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_elec_blocked = FALSE "
"AND album_requests_pending IS NULL "
"AND song_request_only = FALSE "
"AND song_length >= %s AND song_length <= %s")
lower_target_bound = target_seconds - (target_delta / 2)
upper_target_bound = target_seconds + (target_delta / 2)
num_available = db.c.fetch_var("SELECT COUNT(r4_song_sid.song_id) " + sql_query, (sid, lower_target_bound, upper_target_bound))
log.info("song_select", "Song pool size (cooldown, blocks, requests, timed) [target %s delta %s]: %s" % (target_seconds, target_delta, num_available))
if num_available == 0:
log.warn("song_select", "No songs available with target_seconds %s and target_delta %s." % (target_seconds, target_delta))
log.debug("song_select", "Song select query: SELECT COUNT(r4_song_sid.song_id) " + sql_query % (sid, lower_target_bound, upper_target_bound))
return get_random_song(sid)
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var("SELECT r4_song_sid.song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, lower_target_bound, upper_target_bound, offset))
return Song.load_from_id(song_id, sid)
def get_random_song(sid):
"""
Fetch a random song, abiding by all election block, request block, and
availability rules. Falls back to get_random_ignore_requests on failure.
"""
sql_query = ("FROM r4_song_sid "
"JOIN r4_songs USING (song_id) "
"JOIN r4_album_sid ON (r4_album_sid.album_id = r4_songs.album_id AND r4_album_sid.sid = r4_song_sid.sid) "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_request_only = FALSE "
"AND song_elec_blocked = FALSE "
"AND album_requests_pending IS NULL")
num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query, (sid,))
log.info("song_select", "Song pool size (cooldown, blocks, requests): %s" % num_available)
offset = 0
if num_available == 0:
log.warn("song_select", "No songs available despite no timing rules.")
log.debug("song_select", "Song select query: SELECT COUNT(song_id) " + (sql_query % (sid,)))
return get_random_song_ignore_requests(sid)
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, offset))
return Song.load_from_id(song_id, sid)
def get_random_song_timed(sid, target_seconds = None, target_delta = None):
"""
Fetch a random song abiding by all election block, request block, and
availability rules, but giving priority to the target song length
provided. Falls back to get_random_song on failure.
"""
if not target_seconds:
return get_random_song(sid)
if not target_delta:
target_delta = config.get_station(sid, "song_lookup_length_delta")
sql_query = ("FROM r4_song_sid "
"JOIN r4_songs USING (song_id) "
"JOIN r4_album_sid ON (r4_album_sid.album_id = r4_songs.album_id AND r4_album_sid.sid = r4_song_sid.sid) "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_elec_blocked = FALSE "
"AND album_requests_pending IS NULL "
"AND song_request_only = FALSE "
"AND song_length >= %s AND song_length <= %s")
lower_target_bound = target_seconds - (target_delta / 2)
upper_target_bound = target_seconds + (target_delta / 2)
num_available = db.c.fetch_var("SELECT COUNT(r4_song_sid.song_id) " + sql_query, (sid, lower_target_bound, upper_target_bound))
log.info("song_select", "Song pool size (cooldown, blocks, requests, timed) [target %s delta %s]: %s" % (target_seconds, target_delta, num_available))
if num_available == 0:
log.warn("song_select", "No songs available with target_seconds %s and target_delta %s." % (target_seconds, target_delta))
log.debug("song_select", "Song select query: SELECT COUNT(r4_song_sid.song_id) " + sql_query % (sid, lower_target_bound, upper_target_bound))
return get_random_song(sid)
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var("SELECT r4_song_sid.song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, lower_target_bound, upper_target_bound, offset))
return Song.load_from_id(song_id, sid)
def get_random_song(sid):
"""
Fetch a random song, abiding by all election block, request block, and
availability rules. Falls back to get_random_ignore_requests on failure.
"""
sql_query = ("FROM r4_song_sid "
"JOIN r4_songs USING (song_id) "
"JOIN r4_album_sid ON (r4_album_sid.album_id = r4_songs.album_id AND r4_album_sid.sid = r4_song_sid.sid) "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_request_only = FALSE "
"AND song_elec_blocked = FALSE "
"AND album_requests_pending IS NULL")
num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query, (sid,))
log.info("song_select", "Song pool size (cooldown, blocks, requests): %s" % num_available)
offset = 0
if num_available == 0:
log.warn("song_select", "No songs available despite no timing rules.")
log.debug("song_select", "Song select query: SELECT COUNT(song_id) " + (sql_query % (sid,)))
return get_random_song_ignore_requests(sid)
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, offset))
return Song.load_from_id(song_id, sid)
def get_random_song_ignore_requests(sid):
"""
Fetch a random song abiding by election block and availability rules,
but ignoring request blocking rules.
"""
sql_query = ("FROM r4_song_sid "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_request_only = FALSE "
"AND song_elec_blocked = FALSE ")
num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query,
(sid, ))
log.debug("song_select",
"Song pool size (cooldown, blocks): %s" % num_available)
offset = 0
if num_available == 0:
log.warn("song_select",
"No songs available while ignoring pending requests.")
log.debug(
"song_select",
"Song select query: SELECT COUNT(song_id) " + (sql_query %
(sid, )),
)
return get_random_song_ignore_all(sid)
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var(
"SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s",
(sid, offset))
return Song.load_from_id(song_id, sid)
def get_shortest_song(sid):
"""
Fetch the shortest song available abiding by election block and availability rules.
"""
sql_query = ("FROM r4_song_sid "
"JOIN r4_songs USING (song_id) "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_request_only = FALSE "
"AND song_elec_blocked = FALSE "
"ORDER BY song_length")
song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1", (sid,))
return Song.load_from_id(song_id, sid)
def get_shortest_song(sid):
"""
Fetch the shortest song available abiding by election block and availability rules.
"""
sql_query = ("FROM r4_song_sid "
"JOIN r4_songs USING (song_id) "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_request_only = FALSE "
"AND song_elec_blocked = FALSE "
"ORDER BY song_length")
song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1", (sid,))
return Song.load_from_id(song_id, sid)
def get_random_song_ignore_all(sid):
"""
Fetches the most stale song (longest time since it's been played) in the db,
ignoring all availability and election block rules.
"""
sql_query = "FROM r4_song_sid WHERE r4_song_sid.sid = %s AND song_exists = TRUE "
num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query, (sid,))
offset = 0
if num_available == 0:
log.critical("song_select", "No songs exist.")
log.debug("song_select", "Song select query: SELECT COUNT(song_id) " + (sql_query % (sid,)))
raise Exception("Could not find any songs to play.")
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, offset))
return Song.load_from_id(song_id, sid)
def get_random_song_ignore_all(sid):
"""
Fetches the most stale song (longest time since it's been played) in the db,
ignoring all availability and election block rules.
"""
sql_query = "FROM r4_song_sid WHERE r4_song_sid.sid = %s AND song_exists = TRUE "
num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query, (sid,))
offset = 0
if num_available == 0:
log.critical("song_select", "No songs exist.")
log.debug("song_select", "Song select query: SELECT COUNT(song_id) " + (sql_query % (sid,)))
raise Exception("Could not find any songs to play.")
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, offset))
return Song.load_from_id(song_id, sid)
def get_random_song_ignore_requests(sid):
"""
Fetch a random song abiding by election block and availability rules,
but ignoring request blocking rules.
"""
sql_query = ("FROM r4_song_sid "
"WHERE r4_song_sid.sid = %s "
"AND song_exists = TRUE "
"AND song_cool = FALSE "
"AND song_request_only = FALSE "
"AND song_elec_blocked = FALSE ")
num_available = db.c.fetch_var("SELECT COUNT(song_id) " + sql_query, (sid,))
log.debug("song_select", "Song pool size (cooldown, blocks): %s" % num_available)
offset = 0
if num_available == 0:
log.warn("song_select", "No songs available while ignoring pending requests.")
log.debug("song_select", "Song select query: SELECT COUNT(song_id) " + (sql_query % (sid,)))
return get_random_song_ignore_all(sid)
else:
offset = random.randint(1, num_available) - 1
song_id = db.c.fetch_var("SELECT song_id " + sql_query + " LIMIT 1 OFFSET %s", (sid, offset))
return Song.load_from_id(song_id, sid)
REQONLY_STATION = 2
remove_songs = db.c.fetch_all(
"SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating <= %s AND song_origin_sid != %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s",
(REMOVE_THRESHOLD, REQONLY_STATION, REQUIRED_RATING_COUNT))
reqonly_songs = db.c.fetch_all(
"SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating > %s AND song_rating <= %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s",
(REMOVE_THRESHOLD, REQONLY_THRESHOLD, REQUIRED_RATING_COUNT))
if REQONLY_STATION:
reqonly_songs += db.c.fetch_all(
"SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating <= %s AND song_origin_sid = %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s",
(REMOVE_THRESHOLD, REQONLY_STATION, REQUIRED_RATING_COUNT))
for row in remove_songs:
song = Song.load_from_id(row['song_id'])
fn = row['song_filename'].split(os.sep)[-1]
dn = "%s%s%s%s%s" % (args.moveto, os.sep, row['song_origin_sid'],
os.sep, song.albums[0].data['name'])
if args.execute:
mkdir_p(dn)
shutil.move(row['song_filename'], "%s%s%s" % (dn, os.sep, fn))
song.disable()
print "Disabled: %s" % row['song_filename']
for row in reqonly_songs:
if args.execute:
db.c.update(
db.connect()
cache.connect()
REMOVE_THRESHOLD = 3.0
REQUIRED_RATING_COUNT = 20
REQONLY_THRESHOLD = 3.3
REQONLY_STATION = 2
remove_songs = db.c.fetch_all("SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating <= %s AND song_origin_sid != %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s", (REMOVE_THRESHOLD, REQONLY_STATION, REQUIRED_RATING_COUNT))
reqonly_songs = db.c.fetch_all("SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating > %s AND song_rating <= %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s", (REMOVE_THRESHOLD, REQONLY_THRESHOLD, REQUIRED_RATING_COUNT))
if REQONLY_STATION:
reqonly_songs += db.c.fetch_all("SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating <= %s AND song_origin_sid = %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s", (REMOVE_THRESHOLD, REQONLY_STATION, REQUIRED_RATING_COUNT))
for row in remove_songs:
song = Song.load_from_id(row['song_id'])
fn = row['song_filename'].split(os.sep)[-1]
dn = "%s%s%s%s%s" % (args.moveto, os.sep, row['song_origin_sid'], os.sep, song.albums[0].data['name'])
if args.execute:
mkdir_p(dn)
shutil.move(row['song_filename'], "%s%s%s" % (dn, os.sep, fn))
song.disable()
print "Disabled: %s" % row['song_filename']
for row in reqonly_songs:
if args.execute:
db.c.update("UPDATE r4_song_sid SET song_request_only = TRUE, song_request_only_end = NULL WHERE song_id = %s", (row['song_id'],))
db.c.update("UPDATE r4_songs SET song_request_count = 0 WHERE song_id = %s", (row['song_id'],))
"SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating <= %s AND song_origin_sid != %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s",
(REMOVE_THRESHOLD, REQONLY_STATION, REQUIRED_RATING_COUNT),
)
reqonly_songs = db.c.fetch_all(
"SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating > %s AND song_rating <= %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s",
(REMOVE_THRESHOLD, REQONLY_THRESHOLD, REQUIRED_RATING_COUNT),
)
if REQONLY_STATION:
reqonly_songs += db.c.fetch_all(
"SELECT song_id, song_origin_sid, song_filename FROM r4_songs WHERE song_rating <= %s AND song_origin_sid = %s AND song_origin_sid != 0 AND song_verified = TRUE AND song_rating_count >= %s",
(REMOVE_THRESHOLD, REQONLY_STATION, REQUIRED_RATING_COUNT),
)
for row in remove_songs:
song = Song.load_from_id(row["song_id"])
fn = row["song_filename"].split(os.sep)[-1]
dn = "%s%s%s%s%s" % (
args.moveto,
os.sep,
row["song_origin_sid"],
os.sep,
song.albums[0].data["name"],
)
if args.execute:
mkdir_p(dn)
shutil.move(row["song_filename"], "%s%s%s" % (dn, os.sep, fn))
song.disable()
