def create_dislikes():
database.db_query('''CREATE TABLE IF NOT EXISTS dislikes (
id_photo INTEGER NOT NULL,
id_user INTEGER NOT NULL,
data_of_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_photo) REFERENCES photos(id_photo) ON DELETE CASCADE,
FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE)''')
def create_token(id, login):
date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
token = hashlib.md5((login + date).encode('utf-8')).hexdigest()
array = [token, id, login]
sql = 'UPDATE users SET token=? WHERE id=? AND login=?'
database.db_query(sql, array)
return token
def create_photos():
database.db_query('''CREATE TABLE IF NOT EXISTS photos (
id_photo INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
photo TEXT NOT NULL,
date_added DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE)''')
def create_blocked_users():
database.db_query('''CREATE TABLE IF NOT EXISTS blocked_users(
id_block INTEGER PRIMARY KEY AUTOINCREMENT,
blocked_user_id INTEGER NOT NULL,
who_block_id INTEGER NOT NULL,
FOREIGN KEY (blocked_user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (who_block_id) REFERENCES users(id) ON DELETE CASCADE)''')
def create_dialogues():
database.db_query('''CREATE TABLE IF NOT EXISTS dialogues(
id_dialogue INTEGER PRIMARY KEY AUTOINCREMENT,
dialogue_name TEXT NOT NULL,
id_user1 INTEGER NOT NULL,
id_user2 INTEGER NOT NULL,
FOREIGN KEY (id_user1) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (id_user2) REFERENCES users(id) ON DELETE CASCADE)''')
def create_about():
database.db_query('''CREATE TABLE IF NOT EXISTS about(
id INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
information TEXT,
education TEXT,
work_exp TEXT,
languages TEXT)''')
def create_requests():
database.db_query('''CREATE TABLE IF NOT EXISTS requests(
id_request INTEGER PRIMARY KEY AUTOINCREMENT,
id_sender INTEGER NOT NULL,
id_taker INTEGER NOT NULL,
status INTEGER NOT NULL,
FOREIGN KEY (id_sender) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (id_taker) REFERENCES users(id) ON DELETE CASCADE)''')
def create_comments():
database.db_query('''CREATE TABLE IF NOT EXISTS comments(
id_comment INTEGER PRIMARY KEY AUTOINCREMENT,
id_photo INTEGER NOT NULL,
id_user INTEGER NOT NULL,
text TEXT,
date_of_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_photo) REFERENCES photos(id_photo) ON DELETE CASCADE,
FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE)''')
def create_education():
database.db_query('''CREATE TABLE IF NOT EXISTS education(
id_education INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
university TEXT NOT NULL,
from_year TEXT NOT NULL,
to_year TEXT NOT NULL,
description TEXT,
FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE)''')
def create_notifications():
database.db_query('''CREATE TABLE IF NOT EXISTS notifications (
id_notification INTEGER PRIMARY KEY AUTOINCREMENT,
to_whom_id INTEGER NOT NULL,
notification TEXT,
notif_type TEXT,
image TEXT,
status INT NOT NULL DEFAULT 0,
date_of_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (to_whom_id) REFERENCES users(id) ON DELETE CASCADE)''')
def create_work():
database.db_query('''CREATE TABLE IF NOT EXISTS job(
id_job INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
company TEXT NOT NULL,
designation TEXT NOT NULL,
from_year TEXT NOT NULL,
to_year TEXT NOT NULL,
city TEXT NOT NULL,
description TEXT,
FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE)''')
def create_messages():
database.db_query('''CREATE TABLE IF NOT EXISTS messages (
id_message INTEGER PRIMARY KEY AUTOINCREMENT,
id_dialogue INTEGER NOT NULL,
from_whom_id INTEGER NOT NULL,
to_whom_id INTEGER NOT NULL,
message TEXT NOT NULL,
status TEXT NOT NULL DEFAULT unread,
date_of_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_dialogue) REFERENCES dialogues(id_dialogue) ON DELETE CASCADE,
FOREIGN KEY (from_whom_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (to_whom_id) REFERENCES users(id) ON DELETE CASCADE)
''')
def check_block(id_user, my_id):
array = [id_user, my_id]
sql = 'SELECT * FROM blocked_users WHERE blocked_user_id=? AND who_block_id=?'
res = database.db_query(sql, array)
if not res:
array = [my_id, id_user]
sql = 'SELECT * FROM blocked_users WHERE blocked_user_id=? AND who_block_id=?'
res = database.db_query(sql, array)
if res:
return 'in_block'
else:
return None
else:
return 'block'
def check_request(id1, id2):
array = [id1, id2]
sql = 'SELECT * FROM requests WHERE id_sender=? AND id_taker=?'
res = database.db_query(sql, array)
if not res:
array = [id2, id1]
sql = 'SELECT * FROM requests WHERE id_sender=? AND id_taker=?'
res = database.db_query(sql, array)
if res:
return 'taker'
else:
return None
else:
return 'sender'
def delete_interest(id_interest, id_user):
array = [id_interest, id_user]
sql = 'DELETE FROM interests_users WHERE id_interest=? AND id_user=?'
res = database.db_query(sql, array)
if not res:
return True
return res
def get_last_message_by_dialogue_id(id_dialogue):
array = [id_dialogue]
sql = 'SELECT * FROM messages WHERE id_dialogue=? GROUP BY id_message ORDER BY id_message DESC LIMIT 1;'
res = database.db_query(sql, array)
if res:
return res[0]['message']
else:
return 'There are no messages in this dialog yet'
def get_information(id):
array = [id]
sql = 'SELECT information FROM about WHERE id_user=?'
res = database.db_query(sql, array)
if res:
return res[0]['information']
else:
return False
def get_geolocation_by_user_id(id_user):
array = [id_user]
sql = 'SELECT * FROM geolocation WHERE id_user=?'
res = database.db_query(sql, array)
if res:
return res[0]
else:
return False
def check_token(email, token):
array = [email]
sql = 'SELECT id, token, activation FROM users WHERE email=?'
res = database.db_query(sql, array)
if not res:
return False
if res[0]['token'] == token:
return res
else:
return False
def create_users():
database.db_query('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
login TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
avatar TEXT,
background TEXT,
birth_date TEXT NOT NULL,
city TEXT NOT NULL,
country TEXT NOT NULL,
activation INTEGER DEFAULT 0,
token TEXT,
gender TEXT,
sex_pref TEXT,
date_of_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
fame_rating INTEGER DEFAULT 0,
report INTEGER DEFAULT 0)
''')
def photo_dislikes(id_photo):
array = [id_photo]
sql = 'SELECT * FROM dislikes WHERE id_photo=?'
res = database.db_query(sql, array)
n = len(res)
return n
def check_dislike(id_photo, id_user):
array = [id_photo, id_user]
sql = 'SELECT * FROM dislikes WHERE id_photo=? AND id_user=?'
res = database.db_query(sql, array)
return res
def get_comments_by_photo_id(id_photo):
array = [id_photo]
sql = 'SELECT * FROM comments WHERE id_photo=?'
res = database.db_query(sql, array)
return res
def create_sympathys():
database.db_query('''CREATE TABLE IF NOT EXISTS sympathys(
id_user1 INTEGER NOT NULL,
id_user2 INTEGER NOT NULL,
FOREIGN KEY (id_user1) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (id_user2) REFERENCES users(id) ON DELETE CASCADE)''')
def create_geolocation():
database.db_query('''CREATE TABLE IF NOT EXISTS geolocation(
id_user INTEGER NOT NULL UNIQUE,
latitude TEXT,
longitude TEXT,
FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE)''')
def create_interests():
database.db_query('''CREATE TABLE IF NOT EXISTS interests(
id_interest INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL UNIQUE,
icon TEXT)''')
def activate_user(id):
array = [id]
sql = 'UPDATE users SET activation = 1, token = NULL WHERE id=?'
res = database.db_query(sql, array)
return res
def create_interests_users():
database.db_query('''CREATE TABLE IF NOT EXISTS interests_users(
id_interest INTEGER NOT NULL,
id_user INTEGER NOT NULL,
FOREIGN KEY (id_interest) REFERENCES interests(id_interest) ON DELETE CASCADE,
FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE)''')
def change_geolocation(id_user, latitude, longitude):
array = [latitude, longitude, id_user]
sql = 'UPDATE geolocation SET latitude=?, longitude=? WHERE id_user=?'
res = database.db_query(sql, array)
return res
def get_avatar(id):
array = [id]
sql = 'SELECT avatar FROM users WHERE id=?'
res = database.db_query(sql, array)
return res[0]['avatar']
