def check_room_by_users(user_login_from, user_login_to): agruments = [user_login_from, user_login_to] sql = "SELECT room_name ,users.login FROM chat_room INNER JOIN chat_room_con ON " \ "chat_room.id_chat_room=chat_room_con.id_chat_room INNER JOIN users ON chat_room_con.id_user=users.id_user " \ "WHERE users.login=? OR users.login=? " res = db_connect(sql, agruments) return res
def create_about_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS about (
id_about INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
biography TEXT DEFAULT NULL,
location VARCHAR(255) DEFAULT NULL,
`language` VARCHAR(255) DEFAULT NULL,
phone VARCHAR(255) DEFAULT NULL,
status VARCHAR(255) DEFAULT NULL,
political VARCHAR(255) DEFAULT NULL,
fb VARCHAR(255) DEFAULT NULL,
tw VARCHAR(255) DEFAULT NULL,
inst VARCHAR(255) DEFAULT NULL,
site VARCHAR(255) DEFAULT NULL,
hobbies TEXT DEFAULT NULL,
tv_shows TEXT DEFAULT NULL,
movies TEXT DEFAULT NULL,
games TEXT DEFAULT NULL,
music TEXT DEFAULT NULL,
books TEXT DEFAULT NULL,
writers TEXT DEFAULT NULL,
others TEXT DEFAULT NULL,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("about ok")
def get_about(id_user): arguments = [id_user] sql = "SELECT * FROM about WHERE id_user=?" res = db_connect(sql, arguments) if res: return res[0] return res
def check_friends(id_requester, id_user_requested):
arguments = [id_requester, id_user_requested]
sql = "SELECT * FROM friendships WHERE id_requester=? AND id_user_requested=?"
res = db_connect(sql, arguments)
if res:
return res[0]
return res
def if_user_reported(id_from, id_to): arguments = [id_from, id_to] sql = "SELECT * FROM report WHERE id_reporter=? AND id_user=?" res = db_connect(sql, arguments) if res: return True return False
def if_user_blocked(id_from, id_to): arguments = [id_from, id_to] sql = "SELECT * FROM blocked WHERE id_who_block=? AND id_user=?" res = db_connect(sql, arguments) if res: return True return False
def get_post_by_id(id_post): arguments = [id_post] sql = "SELECT * FROM posts WHERE id_post=?" res = db_connect(sql, arguments) if res: return res[0] return res
def disliked(id_user, id_post):
arguments = [id_user, id_post]
sql = "SELECT * FROM dislikes WHERE id_user=? AND id_post=?"
res = db_connect(sql, arguments)
if res:
return True
return False
def create_users_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS users (
id_user INTEGER PRIMARY KEY AUTOINCREMENT,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
first_name VARCHAR(255),
last_name VARCHAR(255),
login VARCHAR(255) NOT NULL UNIQUE,
avatar VARCHAR(255) NOT NULL DEFAULT "/static/uploads/avatars/default.png",
cover VARCHAR(255) NOT NULL DEFAULT "/static/uploads/covers/1.jpg",
theme VARCHAR(255) NOT NULL DEFAULT "light",
date_birth DATE,
active BOOLEAN DEFAULT 0,
token VARCHAR(255),
gender INT NOT NULL DEFAULT 1,
sex_pref INTEGER NOT NULL DEFAULT 0,
rating INTEGER NOT NULL DEFAULT 0,
report INTEGER NOT NULL DEFAULT 0,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP)
''')
if res:
print(res)
else:
print("users ok")
def get_user_by_id(id_user): arguments = [id_user] sql = "SELECT * FROM users WHERE id_user = ?" res = db_connect(sql, arguments) if res: return res[0] return res
def get_by_comment_id(id_comment):
arguments = [id_comment]
sql = "SELECT * FROM comments WHERE id_comment=?"
res = db_connect(sql, arguments)
if res:
return res[0]
return res
def create_location_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS location (
id_user INTEGER NOT NULL UNIQUE,
longitude VARCHAR(255) DEFAULT NULL,
latitude VARCHAR(255) DEFAULT NULL)
''')
if res:
print(res)
else:
print("location ok")
def create_album_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS album (
id_album INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
img VARCHAR(255) NOT NULL,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("album ok")
def create_chat_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS chat_room (
id_chat_room INTEGER PRIMARY KEY AUTOINCREMENT,
room_name VARCHAR(255) NOT NULL UNIQUE)
''')
if res:
print(res)
else:
print("chat_room ok")
res = db_connect('''
CREATE TABLE IF NOT EXISTS chat_room_con (
id_chat_room INTEGER NOT NULL,
id_user INTEGER NOT NULL,
FOREIGN KEY (id_chat_room) REFERENCES chat_room(id_chat_room) ON DELETE CASCADE,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("chat_room_con ok")
def create_img_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS images (
id_img INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
img_src VARCHAR(255) NOT NULL,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("images ok")
def create_dislikes_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS dislikes (
id_user INTEGER NOT NULL,
id_post INTEGER NOT NULL,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_post) REFERENCES posts(id_post) ON DELETE CASCADE,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("dislikes ok")
def create_blocked_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS blocked (
id_blocked INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
id_who_block INTEGER NOT NULL,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE,
FOREIGN KEY (id_who_block) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("blocked ok")
def create_report_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS report (
id_report INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
id_reporter INTEGER NOT NULL,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE,
FOREIGN KEY (id_reporter) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("report ok")
def create_notifications_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS notifications (
id_notif INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
notification TEXT NOT NULL,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("notifications ok")
def create_tags_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS tags (
id_tag INTEGER PRIMARY KEY AUTOINCREMENT,
tag VARCHAR(255) NOT NULL UNIQUE,
tag_sign VARCHAR(255))
''')
if res:
print(res)
else:
print("tags ok")
res = db_connect('''
CREATE TABLE IF NOT EXISTS tags_con (
id_tag INTEGER NOT NULL,
id_user INTEGER NOT NULL,
FOREIGN KEY (id_tag) REFERENCES tags(id_tag) ON DELETE CASCADE,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("tags_con ok")
def create_comments_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS comments (
id_comment INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
id_post INTEGER NOT NULL,
text TEXT DEFAULT NULL,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_post) REFERENCES posts(id_post) ON DELETE CASCADE,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("comments ok")
def create_friends_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS friendships (
id INTEGER PRIMARY KEY AUTOINCREMENT,
id_requester INTEGER NOT NULL,
id_user_requested INTEGER NOT NULL,
status BOOLEAN DEFAULT 0 NOT NULL,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_requester) REFERENCES users(id_user) ON DELETE CASCADE,
FOREIGN KEY (id_user_requested) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("friendships ok")
def create_messages_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS messages (
id_message INTEGER PRIMARY KEY AUTOINCREMENT,
id_chat_room INTEGER NOT NULL,
id_user_from INTEGER NOT NULL,
id_user_to INTEGER NOT NULL,
message TEXT NOT NULL,
read_status BOOLEAN DEFAULT 0,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_user_from) REFERENCES users(id_user) ON DELETE CASCADE,
FOREIGN KEY (id_user_to) REFERENCES users(id_user) ON DELETE CASCADE,
FOREIGN KEY (id_chat_room) REFERENCES chat_room(id_chat_room) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("messages ok")
def create_posts_table():
res = db_connect('''
CREATE TABLE IF NOT EXISTS posts (
id_post INTEGER PRIMARY KEY AUTOINCREMENT,
id_user INTEGER NOT NULL,
id_user_from INTEGER NOT NULL,
`type` VARCHAR(255) NOT NULL,
status VARCHAR(255) NOT NULL DEFAULT "public",
content TEXT DEFAULT NULL,
img VARCHAR(255) DEFAULT NULL,
video VARCHAR(255) DEFAULT NULL,
date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (id_user) REFERENCES users(id_user) ON DELETE CASCADE,
FOREIGN KEY (id_user_from) REFERENCES users(id_user) ON DELETE CASCADE)
''')
if res:
print(res)
else:
print("posts ok")
def add_user_to_chat_room(chat_room_name, login): agruments = [chat_room_name, login] sql = "INSERT INTO chat_room_con (id_chat_room, id_user) VALUES ((SELECT id_chat_room FROM chat_room WHERE room_name=?),(SELECT id_user FROM users WHERE login=?))" res = db_connect(sql, agruments) return res
def get_chat_room_by_name(room_name): agruments = [room_name] sql = "SELECT * FROM chat_room WHERE room_name = ?" res = db_connect(sql, agruments) return res
def create_chat_room_in_db(room_name): agruments = [room_name] sql = "INSERT INTO chat_room (room_name) VALUES (?)" res = db_connect(sql, agruments) return res
def add_user_to_chat_room_by_id(id_chat_room, id_user): agruments = [id_chat_room, id_user] sql = "INSERT INTO chat_room_con (id_chat_room, id_user) VALUES (?,?)" res = db_connect(sql, agruments) return res
def dell_post(id_post): arguments = [id_post] sql = "DELETE FROM posts WHERE id_post=?" res = db_connect(sql, arguments) return res
def all_user_post(id_user): arguments = [id_user] sql = "SELECT * FROM posts WHERE id_user=? ORDER BY id_post DESC" res = db_connect(sql, arguments) return res
