def create_user_table():
query = """CREATE TABLE IF NOT EXISTS User(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
username TEXT,
password TEXT,
first_name TEXT,
last_name TEXT)"""
CUD_query(query)
def create_comments_table():
query = """CREATE TABLE IF NOT EXISTS Comments(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
firm_id INTEGER,
user_id INTEGER,
comment TEXT,
answer TEXT NULL,
FOREIGN KEY (user_id) REFERENCES User(id),
FOREIGN KEY (firm_id) REFERENCES Firm(id))"""
CUD_query(query)
def update_comment_text(comment_id, editing_user, comment_text):
get_comment_user_id_query = """Select user_id FROM Comments WHERE id = %s"""
get_comment_user_id_params = [comment_id]
comment_user_id = select_function(get_comment_user_id_query,
get_comment_user_id_params)
if editing_user == comment_user_id[0]:
edit_comment_query = """ UPDATE Comments
SET comment = %s
WHERE id = %s"""
edit_comment_params = [comment_text, comment_id]
CUD_query(edit_comment_query, edit_comment_params)
else:
print("You cant edit this comment!!")
def delete_user_by_id(user_id):
query = """DELETE FROM User WHERE id = %s"""
params = [user_id]
CUD_query(query, params)
def update_user_first_name(user_id, new_first_name):
query = """UPDATE User SET first_name = %s WHERE id = %s"""
params = [new_first_name, user_id]
CUD_query(query, params)
def create_user(username, password, first_name, last_name, residence):
query = """INSERT INTO User(username,password,first_name, last_name, residence) VALUES (%s, %s, %s, %s, %s)"""
params = [username, password, first_name, last_name, residence]
CUD_query(query, params)
def delete_comment_by_id(comment_id):
query = """DELETE FROM Comments WHERE id = %s"""
params = [comment_id]
CUD_query(query, params)
def update_answer(comment_id, answer):
query = """UPDATE Comments SET answer = %s WHERE id = %s"""
params = [answer, comment_id]
CUD_query(query, params)
def create_comment(firm_id, user_id, comment):
query = """INSERT INTO Comments(firm_id, user_id, comment) VALUES (%s, %s, %s)"""
params = [firm_id, user_id, comment]
CUD_query(query, params)