Files
2026-10-23 19:30:08 +03:00

98 lines
4.2 KiB
Python

"""Доступ к данным."""
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):
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):
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):
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