forked from mtusi2026/mtusi2026lab2
50 lines
2.9 KiB
SQL
50 lines
2.9 KiB
SQL
-- Трек 2: синтетические данные. 115 000 сообщений в неравномерных чатах:
|
|
-- 1 чат — 80 000, 1 чат — 20 000, 5 чатов — по 1 000, 2 000 чатов — по 5.
|
|
-- Порядок вставки перемешан (ORDER BY random()): строки одного чата лежат в куче вперемешку, как при реальной записи.
|
|
-- Вспомогательные таблицы эксперимента лежат в схеме bench и в backup не включаются (pg_dump -N bench).
|
|
\set ON_ERROR_STOP on
|
|
|
|
INSERT INTO users (id, username, display_name, password_hash, created_at)
|
|
SELECT gen_random_uuid(), 'seed_user_' || g, 'Seed', 'not-a-real-hash', now() FROM generate_series(1, 3) g;
|
|
|
|
CREATE TEMP TABLE seed_chats AS
|
|
SELECT gen_random_uuid() AS id, n,
|
|
CASE WHEN n = 1 THEN 80000 WHEN n = 2 THEN 20000 WHEN n <= 7 THEN 1000 ELSE 5 END AS msgs
|
|
FROM generate_series(1, 2007) n;
|
|
|
|
INSERT INTO chats (id, title, created_at, history_version)
|
|
SELECT id, 'seed ' || n, now() + n * interval '1 millisecond', msgs FROM seed_chats;
|
|
|
|
INSERT INTO chat_members (chat_id, user_id)
|
|
SELECT c.id, u.id FROM seed_chats c CROSS JOIN users u;
|
|
|
|
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
|
|
SELECT gen_random_uuid(), c.id, s.id, 'message ' || g || ' ' || md5(random()::text),
|
|
timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second'
|
|
FROM seed_chats c
|
|
CROSS JOIN LATERAL generate_series(1, c.msgs) g
|
|
CROSS JOIN (SELECT id FROM users WHERE username = 'seed_user_1') s
|
|
ORDER BY random();
|
|
|
|
-- Сессия с известным токеном, чтобы после restore можно было сходить в API под этим пользователем.
|
|
-- Токен учебный и одноразовый; в БД хранится только его SHA-256, как в приложении.
|
|
INSERT INTO sessions (id, token_hash, user_id, created_at, expires_at)
|
|
SELECT gen_random_uuid(), sha256(convert_to('track2-experiment-token', 'UTF8')), id, now(), now() + interval '30 days'
|
|
FROM users WHERE username = 'seed_user_1';
|
|
|
|
-- Параметры и курсоры эксперимента (каждое 51-е сообщение большого чата — случайная «глубина» страницы).
|
|
CREATE SCHEMA bench;
|
|
CREATE TABLE bench.params AS
|
|
SELECT (SELECT id FROM seed_chats WHERE n = 1) AS big,
|
|
(SELECT id FROM seed_chats WHERE n = 2) AS medium,
|
|
(SELECT id FROM seed_chats WHERE n = 8) AS small,
|
|
(SELECT id FROM users WHERE username = 'seed_user_1') AS sender;
|
|
|
|
CREATE TABLE bench.cursors AS
|
|
SELECT row_number() OVER (ORDER BY rn) AS n, created_at AS t, id
|
|
FROM (SELECT created_at, id, row_number() OVER (ORDER BY created_at, id) AS rn
|
|
FROM messages WHERE chat_id = (SELECT big FROM bench.params)) x
|
|
WHERE rn % 51 = 0;
|
|
|
|
ANALYZE;
|