def searchMusic():
error = None
name = str(request.args["musicName"])
if not name:
error = "Music name is required."
if error is not None:
flash(error)
else:
page_index = 1
page_max_size = 20
page_size = 20
if "page" in request.args:
page_index = int(request.args["page"])
if "size" in request.args:
page_max_size = int(request.args["size"])
cur = get_db().cursor().execute(
"select count(*) as total_items from music where music_name like \'%"
+ name + "%\'").fetchone()
total_items = int(cur["total_items"])
total_pages = int(total_items / page_max_size)
if total_items % page_max_size > 0:
total_pages += 1
if page_index == total_pages:
page_size = total_items % page_max_size
if total_items == 0:
page_index = 0
page_size = 0
cur = (get_db().cursor().execute(
"select id,created,music_name,artist_id,(case when id in (select music_id from practice where player_id="
+ str(session['user_id']) +
") then 1 else 0 end) as played from music where music_name like \'%"
+ name + "%\' limit ? offset ?",
(page_size, (page_index - 1) * page_max_size)))
my_query = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
res = {
'pageIndex': page_index,
'pageSize': page_size,
'totalItems': total_items,
'totalPages': total_pages,
'items': my_query
}
return json.dumps(res, cls=encoder, ensure_ascii=False)
def search_artist():
error = None
name = request.args["artistName"]
if not id:
error = "Artist name is required."
if error is not None:
flash(error)
else:
page_index = 1
page_max_size = 20
page_size = 20
if "page" in request.args:
page_index = int(request.args["page"])
if "size" in request.args:
page_max_size = int(request.args["size"])
cur = get_db().cursor().execute(
"select count(*) as total_items from artist where artist_name like \'%"
+ name + "%\'").fetchone()
total_items = int(cur["total_items"])
total_pages = int(total_items / page_max_size)
if total_items % page_max_size > 0:
total_pages += 1
if page_index == total_pages:
page_size = total_items % page_max_size
if total_items == 0:
page_index = 0
page_size = 0
cur = (get_db().cursor().execute(
"select id, artist_name from artist where artist_name like \'%" +
name + "%\' limit ? offset ?",
(page_size, (page_index - 1) * page_max_size)))
my_query = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
res = {
'pageIndex': page_index,
'pageSize': page_size,
'totalItems': total_items,
'totalPages': total_pages,
'items': my_query
}
return json.dumps(res, ensure_ascii=False, cls=encoder)
def add_favourite():
error = None
if request.method == "POST":
jsonData = request.get_json()
music_id = jsonData["music_id"]
user_id = session['user_id']
else: # for test
music_id = 6
user_id = 1
if not music_id:
error = "Music ID is required."
if not user_id:
error = "User ID is required."
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
"INSERT INTO favourite (user_id, music_id) VALUES (? ,?)",
(user_id, music_id),
)
db.commit()
return "Add success!"
def add_practice():
"""Mark a music piece to have been played"""
error = None
if request.method == "POST":
jsonData = request.get_json()
music_id = jsonData["music_id"]
player_id = session['user_id']
score = jsonData["score"]
content = jsonData["content"]
else: # for test
music_id = 6
player_id = 1
score = 0
content = "miao"
if not music_id:
error = "Music ID is required."
if error is not None:
flash(error)
else:
if score is None:
score = -1
if content is None:
content = ""
db = get_db()
db.execute(
"INSERT INTO practice (music_id, player_id, score, content) VALUES (?, ?, ? ,?)",
(music_id, player_id, score, content),
)
db.commit()
return "Add success!"
def show():
page_index = 1
page_max_size = 20
page_size = 20
if "page" in request.args:
page_index = int(request.args["page"])
if "size" in request.args:
page_max_size = int(request.args["size"])
cur = get_db().cursor().execute(
"select count(*) as total_items from music,practice\
where music.id=practice.music_id \
and practice.player_id=" + str(session['user_id'])).fetchone()
total_items = int(cur["total_items"])
total_pages = int(total_items / page_max_size)
if total_items % page_max_size > 0:
total_pages += 1
if page_index == total_pages:
page_size = total_items % page_max_size
if total_items == 0:
page_index = 0
page_size = 0
cur = (get_db().cursor().execute(
"select music.id,music.created,music.music_name,music.artist_id,practice.content,practice.score\
from music,practice\
where music.id=practice.music_id \
and practice.player_id=" + str(session['user_id']) +
" limit ? offset ? ", (page_size, (page_index - 1) * page_max_size)))
my_query = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
res = {
'pageIndex': page_index,
'pageSize': page_size,
'totalItems': total_items,
'totalPages': total_pages,
'items': my_query
}
return json.dumps(res, cls=encoder, ensure_ascii=False)
def delete_artist():
if request.method == "POST":
jsonData = request.get_json()
artist_name = jsonData["artist_name"]
else: # just for test
artist_name = 'Mozart'
db = get_db()
db.execute("DELETE FROM artist WHERE artist_name = ?", (artist_name, ))
db.commit()
return redirect(url_for("hello")) # TODO change the url
def status():
db = get_db()
error = None
if session['user_id'] is None:
error = "Not log in"
if error is None:
user = db.execute('SELECT * FROM user WHERE id = ?',
(session['user_id'], )).fetchone()
res = {'username': user['username'], 'nickname': user['nickname']}
return json.dumps(res, ensure_ascii=False, cls=encoder)
flash(error)
return error # TODO change the url
def show_artist():
page_index = 1
page_max_size = 20
page_size = 20
if "page" in request.args:
page_index = int(request.args["page"])
if "size" in request.args:
page_max_size = int(request.args["size"])
cur = get_db().cursor().execute(
"select count(*) as total_items from artist").fetchone()
total_items = int(cur["total_items"])
total_pages = int(total_items / page_max_size)
if total_items % page_max_size > 0:
total_pages += 1
if page_index == total_pages:
page_size = total_items % page_max_size
if total_items == 0:
page_index = 0
page_size = 0
cur = (get_db().cursor().execute(
"select id, artist_name from artist limit ? offset ?",
(page_size, (page_index - 1) * page_max_size)))
my_query = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
res = {
'pageIndex': page_index,
'pageSize': page_size,
'totalItems': total_items,
'totalPages': total_pages,
'items': my_query
}
return json.dumps(res, ensure_ascii=False, cls=encoder)
def delete():
"""Delete a music.
"""
if request.method == "POST":
jsonData = request.get_json()
music_name = jsonData["music_name"]
else: # just for test
music_name = 'See you again'
db = get_db()
db.execute("DELETE FROM music WHERE music_name = ?", (music_name, ))
db.commit()
return redirect(url_for("hello")) # TODO change the url
def delete_favourite():
if request.method == "POST":
jsonData = request.get_json()
music_id = jsonData["music_id"]
user_id = session['user_id']
else: # just for test
music_id = 6
user_id = 1
db = get_db()
db.execute("DELETE FROM favourite WHERE music_id = ? and user_id = ? ",
(music_id, user_id))
db.commit()
return "Delete success!"
def delete_practice():
"""Marked a piece of music as not played"""
if request.method == "POST":
jsonData = request.get_json()
music_id = jsonData["music_id"]
player_id = session['user_id']
else: # just for test
music_id = 6
player_id = 1
db = get_db()
db.execute("DELETE FROM practice WHERE music_id = ? and player_id=? ",
(music_id, player_id))
db.commit()
return "Delete success!"
def show():
error = None
id = request.args.get("id")
if not id:
error = "Artist ID is required."
if error is not None:
flash(error)
else:
cur = (get_db().cursor().execute("select artist_name,introduction\
from artist\
where id=" + str(id)))
my_query = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
return json.dumps(my_query, cls=encoder, ensure_ascii=False)
def getAverageScore():
error = None
id = request.args.get("musicId")
if not id:
error = "Music ID is required."
if error is not None:
flash(error)
else:
cur = (get_db().cursor().execute("select avg(score) as avg_score\
from practice \
where music_id=" + str(id)))
my_query = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
return json.dumps(my_query, cls=encoder, ensure_ascii=False)
def getCommentAndScore():
error = None
id = request.args.get("musicId")
if not id:
error = "Music ID is required."
if error is not None:
flash(error)
else:
cur = (get_db().cursor().execute(
"select music_id,player_id,nickname,score,content \
from practice,user \
where user.id=practice.player_id and practice.music_id=" +
str(id)))
my_query = [
dict((cur.description[i][0], value) for i, value in enumerate(row))
for row in cur.fetchall()
]
return json.dumps(my_query, cls=encoder, ensure_ascii=False)
def login():
db = get_db()
if request.method == 'POST':
jsonData = request.get_json()
username = jsonData['username']
password = jsonData['password']
error = None
user = db.execute('SELECT * FROM user WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not user['password'] == password:
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('hello'))
flash(error)
else: # just for test
username = '******'
password = '******'
error = None
user = db.execute('SELECT * FROM user WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not user['password'] == password:
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('index')) # TODO change the url
flash(error)
return error # TODO change the url
def register():
db = get_db()
if request.method == 'POST':
jsonData = request.get_json()
username = jsonData['username']
nickname = jsonData['nickname']
password = jsonData['password']
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif not nickname:
error = 'Nickname is required.'
elif db.execute('SELECT id FROM user WHERE username = ?',
(username, )).fetchone() is not None:
error = 'User {} is already registered.'.format(username)
if error is not None:
flash(error)
else:
db.execute(
'INSERT INTO user (username, nickname, password) VALUES (?, ?, ?)',
(username, nickname, password))
db.commit()
return redirect(url_for('hello')) # TODO change the url
else: # just for test
username = '******'
nickname = 'test'
password = '******'
db.execute(
'INSERT INTO user (username, nickname, password) VALUES (?, ?, ?)',
(username, nickname, password))
db.commit()
return "Add success!"
def add():
"""Create a new post for the current user."""
if request.method == "POST":
jsonData = request.get_json()
music_name = jsonData["music_name"]
artist_id = jsonData["artist_id"]
error = None
if not music_name:
error = "Music name is required."
if error is not None:
flash(error)
else:
cur = (get_db().cursor().execute(
"SELECT * FROM music where music_name=?", (music_name, )))
if len(cur.fetchall()) != 0:
return "Music named " + music_name + " already exists"
else:
db = get_db()
db.execute(
"INSERT INTO music (music_name, artist_id) VALUES (?, ?)",
(music_name, artist_id),
)
db.commit()
return redirect(url_for("hello")) # TODO change the url
else:
cur = (get_db().cursor().execute(
"SELECT * FROM music where music_name='See you again'"))
if len(cur.fetchall()) != 0:
return "Music named 'See you again' already exists"
else:
get_db().execute(
"INSERT INTO music (music_name) VALUES ('See you again')")
get_db().commit()
return 'Add a music successfully'
def add_artist():
if request.method == "POST":
jsonData = request.get_json()
artist_name = jsonData["name"]
introduction = jsonData["introduction"]
error = None
if not artist_name:
error = "Artist name is required."
if error is not None:
flash(error)
else:
cur = (get_db().cursor().execute(
"SELECT * FROM artist where artist_name=?", (artist_name, )))
if len(cur.fetchall()) != 0:
return "Artist named " + artist_name + " already exists"
else:
db = get_db()
db.execute(
"INSERT INTO artist (artist_name, introduction) VALUES (?, ?)",
(artist_name, introduction),
)
db.commit()
return redirect(url_for("hello")) # TODO change the url
else:
cur = (get_db().cursor().execute(
"SELECT * FROM artist where artist_name='Mozart'"))
if len(cur.fetchall()) != 0:
return "Artist named 'Mozart' already exists"
else:
get_db().execute(
"INSERT INTO artist (artist_name) VALUES ('Mozart')")
get_db().commit()
return 'Add a artist successfully'
return redirect(url_for("hello")) # TODO change the url