"""Проверки на уровне базы данных. Запуск при запущенном стенде: python student-tests/test_db.py SQL выполняется в контейнере postgres через `docker compose exec postgres psql`. 1. Откат составной операции: чат с несуществующим участником -> 400, и в базе не остаётся ни чата, ни участников, хотя строка чата успела вставиться. 2. База сама не даёт записать сообщение от не-участника (внешний ключ). 3. Пароль хранится как хеш Argon2id, а не как текст или быстрый хеш. 4. Миграции: после перезапуска API ничего не применяется повторно. """ import time import uuid from common import call, check, compose, compose_output, new_user, psql, run_tests, wait_ready def sql_value(sql): code, output, error = psql(sql) check(code == 0, f"psql failed: {error}") return output def test_create_chat_rolls_back(): alice = new_user("rollback") title = "rollback-" + uuid.uuid4().hex[:8] inserted_before = int(sql_value("SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname = 'chats'")) # Второй участник не существует: INSERT чата пройдёт, INSERT участника упадёт на внешнем ключе. call("POST", "/api/v1/chats", 400, token=alice["token"], body={"title": title, "member_ids": [alice["id"], str(uuid.uuid4())]}) left = sql_value(f"SELECT count(*) FROM chats WHERE title = '{title}'") orphans = sql_value("SELECT count(*) FROM chats c WHERE NOT EXISTS " "(SELECT 1 FROM chat_members m WHERE m.chat_id = c.id)") # Статистика PostgreSQL считает и отменённые вставки; она обновляется с задержкой до ~1 с. inserted_after = inserted_before deadline = time.monotonic() + 15 while inserted_after == inserted_before and time.monotonic() < deadline: time.sleep(0.5) inserted_after = int(sql_value("SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname = 'chats'")) print(f"chat with unknown member -> 400; rows with this title: {left}; chats without members: {orphans}; " f"chats insert counter: {inserted_before} -> {inserted_after}") check(left == "0" and orphans == "0", "chat row survived the failed transaction") check(inserted_after > inserted_before, "the chat row was never inserted, rollback is not shown") print("PASS: chat row was inserted and then rolled back together with members") def test_message_needs_membership_in_db(): code, _, error = psql("INSERT INTO messages (id, chat_id, sender_id, text) " f"VALUES ('{uuid.uuid4()}', '{uuid.uuid4()}', '{uuid.uuid4()}', 'direct insert')") print(f"direct INSERT of a message from a non-member -> exit code {code}: {error.splitlines()[0] if error else ''}") check(code != 0 and "foreign key" in error, "database accepted a message from a non-member") print("PASS: the database itself rejects a message from a non-member") def test_password_is_argon2id(): user = new_user("hash") stored = sql_value(f"SELECT password_hash FROM users WHERE username = '{user['username']}'") print(f"stored password: {stored[:31]}... (length {len(stored)})") check(stored.startswith("$argon2id$") and user["password"] not in stored, "password is not an Argon2id hash") print("PASS: password is stored as Argon2id hash") def test_unique_username_constraint(): constraint = sql_value("SELECT pg_get_constraintdef(oid) FROM pg_constraint " "WHERE conrelid = 'users'::regclass AND contype = 'u'") print(f"unique constraint on users: {constraint}") check(constraint == "UNIQUE (username)", "no unique constraint on username") print("PASS: username is unique in the database") def test_migrations_are_not_repeated(): versions = sql_value("SELECT string_agg(version, ',' ORDER BY version) FROM schema_migrations") print(f"applied migrations: {versions}; restarting api") compose("restart", "api") wait_ready() again = sql_value("SELECT string_agg(version, ',' ORDER BY version) FROM schema_migrations") last_log = [line for line in compose_output("logs", "api").splitlines() if "Migrations applied" in line][-1] print(f"after restart: {again}; log: {last_log.split('messenger.db: ')[-1]}") check(again == versions and "none" in last_log, "migrations were applied again") print("PASS: migrations are applied once, restart does nothing") if __name__ == "__main__": run_tests([ test_create_chat_rolls_back, test_message_needs_membership_in_db, test_password_is_argon2id, test_unique_username_constraint, test_migrations_are_not_repeated, ])