def create_comments_table():
query = """CREATE TABLE IF NOT EXISTS Comments(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
advert_id INTEGER,
user_id INTEGER,
comment TEXT,
FOREIGN KEY (user_id) REFERENCES User(id),
FOREIGN KEY (advert_id) REFERENCES Advert(id))"""
CUD_query(query)
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,
residence TEXT)"""
CUD_query(query)
def create_advert_table():
query = """CREATE TABLE IF NOT EXISTS Advert(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER,
category_id INTEGER,
item TEXT,
description TEXT,
date DATETIME,
FOREIGN KEY (admin_id) REFERENCES User(id),
FOREIGN KEY (category_id) REFERENCES Categories(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 update_advert_description(description, advert_id, editing_user):
get_advert_user_id_query = """Select user_id FROM Advert WHERE id = %s"""
get_advert_user_id_params = [advert_id]
advert_user_id = select_function(get_advert_user_id_query,
get_advert_user_id_params)
if editing_user == advert_user_id[0]:
edit_advert_query = """ UPDATE Advert
SET description = %s
WHERE id = %s"""
edit_advert_params = [description, advert_id]
CUD_query(edit_advert_query, edit_advert_params)
else:
print("You cant edit this advert!!")
def create_categories_table():
query = """CREATE TABLE IF NOT EXISTS Categories(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name TEXT)"""
CUD_query(query)
def delete_category_by_id(category_id):
query = """DELETE FROM Categories WHERE id = %s"""
params = [category_id]
CUD_query(query, params)
def update_category_name(category_id, new_category_name):
query = """UPDATE Categories SET name = %s WHERE id = %s"""
params = [new_category_name, category_id]
CUD_query(query, params)
def create_category(name):
query = """INSERT INTO Categories(name) VALUES (%s)"""
params = [name]
CUD_query(query, params)
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 create_comment(advert_id, user_id, comment):
query = """INSERT INTO Comments(advert_id, user_id, comment) VALUES (%s, %s, %s)"""
params = [advert_id, user_id, comment]
CUD_query(query, params)
def delete_advert_by_id(advert_id):
query = """DELETE FROM Advert WHERE id = %s"""
params = [advert_id]
CUD_query(query, params)
def create_advert(admin_id, category_id, item, number):
date = datetime.date.today()
query = """INSERT INTO Advert(admin_id, category_id, item, number, date) VALUES (%s, %s, %s, %s, %s)"""
params = [admin_id, category_id, number, item, date]
CUD_query(query, params)