forked from mtusi2026/mtusi2026lab2
106 lines
5.0 KiB
Python
106 lines
5.0 KiB
Python
"""Доступ к данным. Только параметризованные запросы (psycopg передаёт параметры отдельно от SQL)."""
|
|
import uuid
|
|
|
|
from . import cursor, security
|
|
from .errors import bad_request
|
|
from .sessions import fmt_ts
|
|
|
|
|
|
def username_exists(conn, username):
|
|
return conn.execute("SELECT 1 FROM users WHERE username = %s", (username,)).fetchone() is not None
|
|
|
|
|
|
def insert_user(conn, username, display_name, password_hash):
|
|
return conn.execute(
|
|
"""INSERT INTO users (id, username, display_name, password_hash) VALUES (%s, %s, %s, %s)
|
|
RETURNING id, username, display_name, created_at""",
|
|
(uuid.uuid4(), username, display_name, password_hash)).fetchone()
|
|
|
|
|
|
def user_for_login(conn, username):
|
|
return conn.execute("SELECT id, username, display_name, password_hash, created_at FROM users WHERE username = %s",
|
|
(username,)).fetchone()
|
|
|
|
|
|
def update_password_hash(conn, user_id, new_hash):
|
|
conn.execute("UPDATE users SET password_hash = %s WHERE id = %s", (new_hash, user_id))
|
|
|
|
|
|
def chat_json(row):
|
|
return {"id": str(row["id"]), "title": row["title"], "member_ids": [str(x) for x in row["member_ids"]],
|
|
"created_at": fmt_ts(row["created_at"])}
|
|
|
|
|
|
def create_chat(conn, creator_id, title, member_ids, check_members=True):
|
|
"""Составная операция (users -> chats -> chat_members) в транзакции вызывающего.
|
|
|
|
Любая ошибка после вставки chats откатывает ВСЁ: чат без участников не появится.
|
|
check_members=False нужен только тесту отката: FK на chat_members сработает уже после вставки чата.
|
|
"""
|
|
if check_members:
|
|
found = conn.execute("SELECT count(*) AS n FROM users WHERE id = ANY(%s)", (member_ids,)).fetchone()["n"]
|
|
if found != len(member_ids):
|
|
raise bad_request("Неизвестный участник чата")
|
|
chat = conn.execute(
|
|
"INSERT INTO chats (id, title, created_by) VALUES (%s, %s, %s) RETURNING id, title, created_at",
|
|
(uuid.uuid4(), title, creator_id)).fetchone()
|
|
conn.execute("INSERT INTO chat_members (chat_id, user_id) SELECT %s, unnest(%s::uuid[])",
|
|
(chat["id"], member_ids))
|
|
chat["member_ids"] = sorted(member_ids)
|
|
return chat
|
|
|
|
|
|
def get_chat(conn, chat_id, user_id):
|
|
"""Чат + список участников, только если user_id — участник (иначе None => 404)."""
|
|
return conn.execute(
|
|
"""SELECT c.id, c.title, c.created_at,
|
|
ARRAY(SELECT m.user_id FROM chat_members m WHERE m.chat_id = c.id ORDER BY m.user_id) AS member_ids
|
|
FROM chats c
|
|
WHERE c.id = %s AND EXISTS (SELECT 1 FROM chat_members me WHERE me.chat_id = c.id AND me.user_id = %s)""",
|
|
(chat_id, user_id)).fetchone()
|
|
|
|
|
|
def is_member(conn, chat_id, user_id):
|
|
return conn.execute("SELECT 1 FROM chat_members WHERE chat_id = %s AND user_id = %s",
|
|
(chat_id, user_id)).fetchone() is not None
|
|
|
|
|
|
def list_chats(conn, user_id, after, limit):
|
|
base = """SELECT c.id, c.title, c.created_at,
|
|
ARRAY(SELECT m.user_id FROM chat_members m WHERE m.chat_id = c.id ORDER BY m.user_id) AS member_ids
|
|
FROM chat_members me JOIN chats c ON c.id = me.chat_id
|
|
WHERE me.user_id = %s"""
|
|
params = [user_id]
|
|
if after:
|
|
base += " AND (c.created_at, c.id) > (%s, %s)"
|
|
params += list(after)
|
|
rows = conn.execute(base + " ORDER BY c.created_at, c.id LIMIT %s", params + [limit + 1]).fetchall()
|
|
page, more = rows[:limit], len(rows) > limit
|
|
nxt = cursor.encode("chats", user_id, page[-1]["created_at"], page[-1]["id"]) if more else None
|
|
return [chat_json(r) for r in page], nxt
|
|
|
|
|
|
def message_json(row):
|
|
return {"id": str(row["id"]), "chat_id": str(row["chat_id"]), "sender_id": str(row["sender_id"]),
|
|
"text": row["text"], "created_at": fmt_ts(row["created_at"])}
|
|
|
|
|
|
def insert_message(conn, chat_id, sender_id, text):
|
|
return conn.execute(
|
|
"""INSERT INTO messages (id, chat_id, sender_id, text) VALUES (%s, %s, %s, %s)
|
|
RETURNING id, chat_id, sender_id, text, created_at""",
|
|
(uuid.uuid4(), chat_id, sender_id, text)).fetchone()
|
|
|
|
|
|
def list_messages(conn, chat_id, after, limit):
|
|
"""Keyset: WHERE (created_at, id) > курсор ORDER BY created_at, id LIMIT n+1 — идёт по индексу, без OFFSET."""
|
|
sql = "SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = %s"
|
|
params = [chat_id]
|
|
if after:
|
|
sql += " AND (created_at, id) > (%s, %s)"
|
|
params += list(after)
|
|
rows = conn.execute(sql + " ORDER BY created_at, id LIMIT %s", params + [limit + 1]).fetchall()
|
|
page, more = rows[:limit], len(rows) > limit
|
|
nxt = cursor.encode("messages", chat_id, page[-1]["created_at"], page[-1]["id"]) if more else None
|
|
return [message_json(r) for r in page], nxt
|