def delete_collection(collection_id):
# SQL
conn = connect_sys_db()
query = "DELETE FROM collections WHERE (id = \'{id}\')".format(
id=collection_id)
with mysql(conn) as cursor:
cursor.execute(query)
def update_email(self, new_email):
# SQL
conn = connect_sys_db()
query = 'UPDATE users SET email = \'{new_email}\' WHERE id = \'{id}\''.format(
id=self._id, new_email=new_email)
with mysql(conn) as cursor:
cursor.execute(query)
def update_username(self, new_username):
# SQL
conn = connect_sys_db()
query = 'UPDATE users SET username = \'{new_username}\' WHERE id = \'{id}\''.format(
id=self._id, new_username=new_username)
with mysql(conn) as cursor:
cursor.execute(query)
def register_account(username, password, admin, email):
# If username already existed
if User.is_user_exists_by_username(username):
return False, 'This username has already been registered'
# SQL
conn = connect_sys_db()
query = 'SELECT email FROM users WHERE email = \'{email}\''.format(
email=email)
db_result = read_sql(sql=query, con=conn)
# If email address already been registered
if not db_result.empty:
return False, 'This email has already been registered'
# SQL
query = 'INSERT INTO users VALUES(0, \'{username}\',' \
'HEX(AES_ENCRYPT(\'{password}\', \'{key}\')), \'{admin}\', \'{email}\')' \
.format(
username=username,
password=password,
admin=admin,
email=email,
key=SECRET_KEY
)
with mysql(conn) as cursor:
cursor.execute(query)
return True, ''
def update_goal(user_id, year, month, goal):
# SQL
conn = connect_sys_db()
query = "UPDATE monthly_goal SET goal = \'{goal}\' WHERE (user_id = \'{user_id}\' AND year = \'{year}\' AND month = \'{month}\')".format(
user_id=user_id, year=year, month=month, goal=goal)
with mysql(conn) as cursor:
cursor.execute(query)
def set_goal(user_id, year, month, goal):
# SQL
conn = connect_sys_db()
query = "INSERT INTO monthly_goal VALUES(\'{user_id}\', \'{year}\', \'{month}\', \'{goal}\')".format(
user_id=user_id, year=year, month=month, goal=goal)
with mysql(conn) as cursor:
cursor.execute(query)
def move_book_to_another_collection(self, new_collection_id, book_id):
# SQL
conn = connect_sys_db()
query = "UPDATE collects SET collection_id = \'{new_collection_id}\' WHERE (book_id = \'{book_id}\' AND collection_id = \'{old_collection_id}\')".format(
new_collection_id=new_collection_id,
old_collection_id=self._id,
book_id=book_id)
with mysql(conn) as cursor:
cursor.execute(query)
def delete_user(username):
if not User.is_user_exists_by_username(username):
return False
conn = connect_sys_db()
# SQL
query = 'DELETE FROM users WHERE username = \'{username}\' AND admin = \'{admin}\'' \
.format(username=username, admin=0)
with mysql(conn) as cursor:
cursor.execute(query)
return True
def mark_as_unread(user_id, book_id):
read_collection_id = Collection.get_readcollection_id(user_id)
# SQL
conn = connect_sys_db()
query = "DELETE FROM collects WHERE (collection_id = \'{collection_id}\' AND book_id = \'{book_id}\')".format(
book_id=book_id,
collection_id=read_collection_id,
)
with mysql(conn) as cursor:
cursor.execute(query)
def delete_review(user_id, book_id):
# SQL
conn = connect_sys_db()
query = 'DELETE FROM review_rate WHERE book_id = \'{book_id}\' AND user_id = \'{user_id}\'' \
.format(
book_id=book_id,
user_id=user_id
)
with mysql(conn) as cursor:
cursor.execute(query)
def mark_as_read(user_id, book_id, date):
read_collection_id = Collection.get_readcollection_id(user_id)
date = date + "-10 10:00:00"
print(date)
# SQL
conn = connect_sys_db()
query = "INSERT INTO collects VALUES(\'{book_id}\', \'{collection_id}\', \'{collect_time}\')".format(
book_id=book_id,
collection_id=read_collection_id,
# collect_time="2020-06-29 06:06:18.423409"
collect_time=date)
with mysql(conn) as cursor:
cursor.execute(query)
def create_view_tables():
# SQL
conn = connect_sys_db()
query1 = "create or replace view view4 as select books.id, count(*) as collect_time from books join collects " \
"on books.id = collects.book_id group by books.id "
query2 = "create or replace view view2 as select books.id, count(*) as read_time from books join collects on " \
"books.id = collects.book_id join collections on collects.collection_id = collections.id where " \
"collections.name = 'read' group by books.id "
query3 = "create or replace view view3 as select books.id, avg(review_rate.rating) as avg_rating from books " \
"join review_rate on books.id = review_rate.book_id group by books.id "
with mysql(conn) as cursor:
cursor.execute(query1)
cursor.execute(query2)
cursor.execute(query3)
def post_new_collection(user_id, name):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM collections WHERE (user_id = \'{user_id}\' AND name = \'{name}\')".format(
user_id=user_id, name=name)
db_result = read_sql(sql=query, con=conn)
# If collection's name already existed
if db_result.empty:
query = "INSERT INTO collections VALUES(0,\'{user_id}\',\'{name}\',\'{time}\')".format(
user_id=user_id, name=name, time=datetime.datetime.utcnow())
with mysql(conn) as cursor:
cursor.execute(query)
return True
else:
return False
def update_collection_name(self, user_id, new_name):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM collections WHERE (user_id = \'{user_id}\' AND name = \'{name}\')".format(
user_id=user_id, name=new_name)
db_result = read_sql(sql=query, con=conn)
# Is new collection name already exist
if not db_result.empty:
return False, 'This collection already existed'
# SQL
query = "UPDATE collections SET name = \'{name}\' WHERE (user_id = \'{user_id}\' AND id = \'{id}\')".format(
name=new_name, user_id=user_id, id=self._id)
with mysql(conn) as cursor:
cursor.execute(query)
return True, 'Collection update successfully'
def update_user_password(username, new_password):
if not User.is_user_exists_by_username(username):
return False
conn = connect_sys_db()
# SQL
query = 'UPDATE users SET password = HEX(AES_ENCRYPT(\'{new_password}\', \'{key}\'))' \
' WHERE username = \'{username}\' AND' \
' admin = \'{admin}\'' \
.format(
username=username,
new_password=new_password,
key=SECRET_KEY,
admin=0
)
with mysql(conn) as cursor:
cursor.execute(query)
return True
def delete_book_in_collection(self, book_id):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM collects WHERE (collection_id = \'{collection_id}\' and book_id = \'{book_id}\')".format(
collection_id=self._id,
book_id=book_id,
)
db_result = read_sql(sql=query, con=conn)
# Is book exist in this collection
if db_result.empty:
return False
# SQL
query = "DELETE FROM collects WHERE (collection_id = \'{collection_id}\' AND book_id = \'{book_id}\')".format(
book_id=book_id, collection_id=self._id)
with mysql(conn) as cursor:
cursor.execute(query)
return True
def add_book_to_collection(self, book_id):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM collects WHERE (collection_id = \'{collection_id}\' and book_id = \'{book_id}\')".format(
collection_id=self._id,
book_id=book_id,
)
db_result = read_sql(sql=query, con=conn)
# Is book already existed in collection
if not db_result.empty:
return 201, "This book already existed in this collection"
# SQL
query = "INSERT INTO collects VALUES(\'{book_id}\', \'{collection_id}\', \'{collect_time}\')".format(
book_id=book_id,
collection_id=self._id,
collect_time=datetime.datetime.utcnow())
with mysql(conn) as cursor:
cursor.execute(query)
return 200, "Add book to collection successfully"
def edit_review(user_id, book_id, rating, content):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM review_rate WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\')".format(
user_id=user_id, book_id=book_id)
db_result = read_sql(sql=query, con=conn)
# If review is existed
if not db_result.empty:
query = 'UPDATE review_rate SET rating = \'{rating}\', review_content = \'{review_content}\', review_time = \'{review_time}\' WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\')'.format(
rating=rating,
review_content=content,
review_time=datetime.datetime.utcnow(),
user_id=user_id,
book_id=book_id)
with mysql(conn) as cursor:
cursor.execute(query)
return True
else:
return False
def update_password(self, old_password, new_password):
# SQL
# Check old password
conn = connect_sys_db()
query = "SELECT * FROM users WHERE (password = HEX(AES_ENCRYPT(\'{old_password}\', \'{key}\')) AND id = \'{id}\')".format(
old_password=old_password, id=self._id, key=SECRET_KEY)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
# SQL
# Update to new password
conn = connect_sys_db()
query = 'UPDATE users SET password = HEX(AES_ENCRYPT(\'{new_password}\', \'{key}\')) WHERE id = \'{id}\'' \
.format(
id=self._id,
new_password=new_password,
key=SECRET_KEY
)
with mysql(conn) as cursor:
cursor.execute(query)
return True
def new_review(user_id, book_id, rating, content):
user = User(user_id)
username = user.get_username()
# SQL
conn = connect_sys_db()
query = "SELECT * FROM review_rate WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\')".format(
user_id=user_id, book_id=book_id)
db_result = read_sql(sql=query, con=conn)
# If review does not exist
if db_result.empty:
query = "INSERT INTO review_rate VALUES(\'{book_id}\',\'{user_id}\',\'{username}\',\'{rating}\',\'{content}\',\'{time}\')".format(
user_id=user_id,
book_id=book_id,
username=username,
rating=rating,
content=content,
time=datetime.datetime.utcnow())
with mysql(conn) as cursor:
cursor.execute(query)
return True
else:
return False