def create_comments_table():
query = """CREATE TABLE IF NOT EXISTS Comment(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
comment_text TEXT,
user_id INTEGER,
advertisement_id INTEGER,
FOREIGN KEY (user_id) REFERENCES User(id),
FOREIGN KEY (advertisement_id) REFERENCES Advertisement(id))"""
CUD_function(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,
phone_number TEXT)"""
CUD_function(query)
def create_advertisement_table():
query = """CREATE TABLE IF NOT EXISTS Advertisement(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
title TEXT,
date DATE,
description TEXT,
user_id INTEGER,
category_id INTEGER,
FOREIGN KEY (user_id) REFERENCES User(id),
FOREIGN KEY (category_id) REFERENCES Category(id))"""
CUD_function(query)
def update_comment(comment_text, id, editing_user):
get_comment_user_id_query = """SELECT user_id FROM Comment
WHERE id = %s"""
get_comment_user_id_params = [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 Comment
SET comment_text = %s
WHERE id = %s"""
edit_comment_params = [comment_text, id]
CUD_function(edit_comment_query, edit_comment_params)
else:
print("You can't edit comments that are not yours")
def update_advertisement(description, id, editing_user):
get_advertisement_user_id_query = """SELECT user_id FROM Advertisement
WHERE id = %s"""
get_advertisement_user_id_params = [id]
advertisement_user_id = select_function(get_advertisement_user_id_query,
get_advertisement_user_id_params)
if editing_user == advertisement_user_id[0]:
edit_advertisement_query = """UPDATE Advertisement
SET description = %s
WHERE id = %s"""
edit_advertisement_params = [description, id]
CUD_function(edit_advertisement_query, edit_advertisement_params)
else:
print("You can't edit advertisement that are not yours")
def delete_comment(id):
query = """DELETE FROM Comment
WHERE id = %s"""
params = [id]
CUD_function(query, params)
def create_comment(comment_text, user_id, advertisement_id):
query = """INSERT INTO Comment(comment_text,user_id,advertisement_id) VALUES (%s,%s,%s)"""
params = [comment_text, user_id, advertisement_id]
CUD_function(query, params)
def delete_category(id):
query = """DELETE FROM Advertisement
WHERE id = %s"""
params = [id]
CUD_function(query, params)
def create_advertisement(title, date, description, user_id, category_id):
query = """INSERT INTO Advertisement(title,date,description,user_id,category_id) VALUES (%s,%s,%s,%s,%s)"""
params = [title, date, description, user_id, category_id]
CUD_function(query, params)
def delete_user(id):
query = """DELETE FROM User
WHERE id = %s"""
params = [id]
CUD_function(query, params)
def update_user(first_name,id):
query = """UPDATE User
SET first_name = %s
WHERE id = %s"""
params = [first_name,id]
CUD_function(query, params)
def create_user(username,password,first_name,last_name):
query = """INSERT INTO User(username,password,first_name,last_name) VALUES (%s,%s,%s,%s)"""
params = [username,password,first_name,last_name]
CUD_function(query,params)
def create_category(name):
query = """INSERT INTO Category(name) VALUES (%s)"""
params = [name]
CUD_function(query, params)
def update_category(name, id):
query = """UPDATE Category
SET name = %s
WHERE id = %s"""
params = [name, id]
CUD_function(query, params)
def create_category_table():
query = """CREATE TABLE IF NOT EXISTS Category(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name TEXT)"""
CUD_function(query)