def __init__(self, drop_courses_table=False):
self.db_manager = DatabaseManager()
if drop_courses_table:
conn = self.db_manager.db_conn
conn.execute("DROP TABLE courses")
conn.close()
self.create_tables()
def execute(self):
rows = DatabaseManager.execute_prepared(
'SELECT ID, file_name FROM song WHERE id = %s', (self.__id, ))
if not rows:
raise ValueError(f'{self.command_text} : Not a valid ID')
DatabaseManager.execute_prepared('DELETE FROM song WHERE id=%s',
(self.__id, ))
os.remove('./storage/' + rows[0][1])
def execute(self) -> int:
rows = DatabaseManager.execute_prepared(
'SELECT ID FROM song WHERE file_name = %s', (self.file_name, ))
if rows:
return rows[0][0]
shutil.copy2(self.__file_path, './storage')
query = 'INSERT INTO song (file_name'
values = '(%s'
prep_values = [self.file_name]
for p in self.__modified_params:
if p[0] == '--title':
query += ', song_name'
values += ', %s'
prep_values.append(p[2])
if p[0] == '--artist':
query += ', artist_id'
values += ', %s'
prep_values.append(DatabaseManager.get_artist_id(p[2]))
if p[0] == '--album':
query += ', album_id'
values += ', %s'
prep_values.append(DatabaseManager.get_album_id(p[2]))
if p[0] == '--release-year':
query += ', release_year'
values += ', %s'
prep_values.append(p[2])
if p[0] == '--duration':
query += ', duration_sec'
values += ', %s'
prep_values.append(to_s(p[2]))
query += ') VALUES ' + values + ')'
DatabaseManager.execute_prepared(query, tuple(prep_values))
song_id = self.execute()
for p in self.__modified_params:
if p[0] == '--tag':
DatabaseManager.execute_prepared(
'INSERT INTO song_tag (song_id, tag_id, value) VALUES (%s, %s, %s)',
(song_id, DatabaseManager.get_tag_id(p[2]), p[3]))
return song_id
def execute(self):
rows = DatabaseManager.execute_prepared(
'SELECT ID, file_name, song_name, artist_id,'
'duration_sec FROM song WHERE id = %s', (self.__id, ))
if not rows:
raise ValueError(f'{self.command_text} : Not a valid ID')
print(
f'Playing {rows[0][2]} by {DatabaseManager.get_artist_by_id(rows[0][3])}. '
f'Duration : {to_m_s(rows[0][4]) if rows[0][4] else "Unknown. Playing first 10 seconds"}...'
)
mixer.init()
mixer.music.load('./storage/' + rows[0][1])
mixer.music.play()
sleep(rows[0][4] if rows[0][4] else 10)
def execute(self):
rows = DatabaseManager.execute(
'SELECT song.id, file_name, song_name, artist_id, album_id, song.release_year,'
'duration_sec FROM song')
for p in self.__modified_params:
if p[0] == '--artist':
artist_id = DatabaseManager.get_artist_id(p[2])
rows = list(filter(lambda x: x[3] == artist_id, rows))
if p[0] == '--album':
album_id = DatabaseManager.get_album_id(p[2])
rows = list(filter(lambda x: x[4] == album_id, rows))
if p[0] == '--title':
rows = list(filter(lambda x: x[2] == p[2], rows))
if p[0] == '--release-year':
rows = list(filter(lambda x: x[5] == int(p[2]), rows))
for p in self.__modified_params:
if p[0] == '--tag':
tag_id = DatabaseManager.get_tag_id(p[2])
tag_rows = list(
filter(
lambda x: x[2] == p[3],
DatabaseManager.execute_prepared(
'SELECT song_id, tag_id, value FROM song_tag WHERE tag_id = %s',
(tag_id, ))))
rows = list(
filter(
lambda x: x[0] in [song_id[0] for song_id in tag_rows],
rows))
return [{
"ID": row[0],
"File Name": row[1],
"Title": row[2] if row[2] else "Unknown",
"Artist": DatabaseManager.get_artist_by_id(row[3]),
"Album": DatabaseManager.get_album_by_id(row[4]),
"Release Year": row[5] if row[5] else "Unknown",
"Duration": to_m_s(row[6]) if row[6] else "Unknown",
"Tags": DatabaseManager.get_tags_for_song_id(row[0])
} for row in rows]
def execute(self) -> None:
rows = DatabaseManager.execute_prepared(
'SELECT ID FROM song WHERE id = %s', (self.__id, ))
if not rows:
raise ValueError(f'{self.command_text} : ID not valid')
query = 'UPDATE song set '
values = '('
prep_values = []
for p in self.__modified_params:
if p[0] == '--title':
query += 'song_name = %s, '
prep_values.append(p[2])
if p[0] == '--artist':
query += 'artist_id = %s, '
prep_values.append(DatabaseManager.get_artist_id(p[2]))
if p[0] == '--album':
query += 'album_id = %s, '
prep_values.append(DatabaseManager.get_album_id(p[2]))
if p[0] == '--release-year':
query += 'release_year = %s, '
prep_values.append(p[2])
if p[0] == '--duration':
query += 'duration_sec = %s, '
prep_values.append(to_s(p[2]))
query = query.removesuffix(', ')
query += " WHERE ID = %s"
prep_values.append(self.__id)
DatabaseManager.execute_prepared(query, tuple(prep_values))
for p in self.__modified_params:
if p[0] == '--atag':
DatabaseManager.execute_prepared(
'INSERT INTO song_tag (song_id, tag_id, value) VALUES (%s, %s, %s)',
(self.__id, DatabaseManager.get_tag_id(p[2]), p[3]))
class _CourseHubDatabaseInitializer:
""" A set of functions to help setup the database. Use cautiously. """
def __init__(self, drop_courses_table=False):
self.db_manager = DatabaseManager()
if drop_courses_table:
conn = self.db_manager.db_conn
conn.execute("DROP TABLE courses")
conn.close()
self.create_tables()
def set_course_ratings(self, data):
"""Initally sets the course rating using the information gathered from the course evals.
:param data: dictionary that contains course code as key to a tuple with ratings
:return:
"""
# set the database values using info in the tuple
cur = self.db_manager.db_conn.cursor()
for key in data.keys():
tuple_of_info = data[key]
workload = tuple_of_info[0]
recommend = tuple_of_info[1]
total = tuple_of_info[2]
cur.execute(
'UPDATE courses SET workload_rating = ? WHERE course_code = ?',
[workload, key])
cur.execute(
'UPDATE courses SET recommendation_rating = ? WHERE course_code = ?',
[recommend, key])
cur.execute(
'UPDATE courses SET num_ratings = ? WHERE course_code = ?',
[total, key])
combined_rating = (workload + recommend) / 2
cur.execute(
'UPDATE courses SET overall_rating = ? WHERE course_code = ?',
[combined_rating, key])
cur.execute(
'UPDATE courses SET ratings_last_updated = ? WHERE course_code = ?',
[int(time()), key])
self.db_manager.db_conn.commit()
cur.close()
def insert_course(self, data):
""" Insert data into the table
:param dict data: Keys: [courseId, org_name, course_title, code, course_description]
:return:
"""
course_id = data["course_id"]
org_name = data["org_name"]
course_title = data["course_title"]
code = data["course_code"]
course_description = data["course_description"]
prerequisite = data["prerequisite"]
corequisite = data["corequisite"]
exclusion = data["exclusion"]
breadth = data["breadth"]
input_data = [
course_id, code, course_description, course_title, org_name, 0, 0,
0, 0, prerequisite, corequisite, exclusion, breadth, 0
]
c = self.db_manager.db_conn.cursor()
course_exists = c.execute('SELECT * FROM courses WHERE id = ?',
[str(course_id)])
# NOTE: this will makesure the same course in two different sections don't both get added.
if course_exists.fetchall() == []:
c.execute(
'insert into courses (id, course_code, course_description, course_title, org_name,'
'workload_rating, recommendation_rating, overall_rating, num_ratings,'
'prerequisite, corequisite, exclusion, breadth, ratings_last_updated) values (?,?,?,?,?,?,?,?,?,?,?,?,?, ?)',
input_data)
self.db_manager.db_conn.commit()
c.close()
def create_tables(self):
""" Creates the comment and course tables in the db """
comment_table = """ CREATE TABLE IF NOT EXISTS comments(
id text PRIMARY KEY,
course_id text,
comment text,
timestamp integer,
votes integer,
root integer,
children text,
user_id text);
"""
course_table = """
CREATE TABLE IF NOT EXISTS courses(
id text PRIMARY KEY,
course_code text,
course_description text,
course_title text,
org_name text,
workload_rating float,
recommendation_rating float,
overall_rating float,
num_ratings integer,
prerequisite text,
corequisite text,
exclusion text,
breadth text,
ratings_last_updated integer);
"""
user_table = """
CREATE TABLE IF NOT EXISTS users(
id text PRIMARY KEY,
picture text,
username text,
email text);
"""
user_to_comment_table = """
CREATE TABLE IF NOT EXISTS user_to_comment(
user_id text,
comment_id text,
upvote_or_downvote text DEFAULT "None");
"""
user_to_course_table = """
CREATE TABLE IF NOT EXISTS user_to_course(
user_id text,
course_id text,
workload_rating float,
recommendation_rating float);
"""
self.db_manager.create_table(comment_table)
self.db_manager.create_table(course_table)
self.db_manager.create_table(user_table)
self.db_manager.create_table(user_to_comment_table)
self.db_manager.create_table(user_to_course_table)
def __init__(self):
DatabaseManager.__init__(self)
raise ValueError(f'{self.command_text} : Not a valid ID')
print(
f'Playing {rows[0][2]} by {DatabaseManager.get_artist_by_id(rows[0][3])}. '
f'Duration : {to_m_s(rows[0][4]) if rows[0][4] else "Unknown. Playing first 10 seconds"}...'
)
mixer.init()
mixer.music.load('./storage/' + rows[0][1])
mixer.music.play()
sleep(rows[0][4] if rows[0][4] else 10)
if __name__ == '__main__':
DatabaseManager.init_database(force_clear=True)
# print(AddSong().usage)
# print(DeleteSong().usage)
# print(ModifySong().usage)
# print(Search().usage)
# print(CreateSaveList().usage)
# print(Play().usage)
try:
print(AddSong().decode(
'Add_song ./temp/Rust_In_Peace_Polaris.mp3 --title = Rust In Peace Polaris --album = Rust In Peace '
'--release-year = 1990 '
'--artist = Megadeth '
' --tag = codec:mp3 --tag = sample rate:44100Hz').execute())
print(AddSong().decode(
'Add_song ./temp/02-megadeth-dystopia.flac --title = Dystopia --album = Dystopia '