Files
2026-10-30 12:32:42 +03:00

51 lines
2.3 KiB
SQL

\set ON_ERROR_STOP on
\set epoch '''2026-01-01T00:00:00Z'''
DO $$ BEGIN
IF current_database() <> 'track' THEN
RAISE EXCEPTION 'track-seed.sql выполняется только в базе track';
END IF;
END $$;
TRUNCATE messages, chat_members, chats, sessions, users;
DROP INDEX IF EXISTS messages_history_idx, track_chat_idx, track_time_idx;
INSERT INTO users (id, username, display_name, password_hash, created_at)
SELECT track_user(u), 'track_' || u, 'Участник ' || u, '$argon2id$v=19$m=19456,t=2,p=1$dHJhY2s$dHJhY2s', :epoch
FROM generate_series(1, 2000) AS u;
INSERT INTO chats (id, title, created_at)
SELECT track_chat(k), 'Чат ' || k, :epoch FROM generate_series(1, 1000) AS k;
INSERT INTO chat_members (chat_id, user_id, position, chat_created_at)
SELECT track_chat(k), track_user(2 * k - 1 + p), p + 1, :epoch
FROM generate_series(1, 1000) AS k, generate_series(0, 1) AS p;
CREATE TEMP TABLE track_sizes AS
SELECT k, greatest(1, round(:messages * w / sum(w) OVER ()))::int AS n
FROM (SELECT k, power(k, -1.3) AS w FROM generate_series(1, 1000) AS k) AS weights;
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT track_message(k, i), track_chat(k), track_user(2 * k - 1 + i % 2),
'Сообщение ' || i || ' в чате ' || k || ' ' || repeat('·', 10 + (i * 7) % 60),
:epoch::timestamptz + (i * (2592000.0 / n)) * interval '1 second'
FROM track_sizes, generate_series(1, n) AS i;
UPDATE chats SET last_message_at = latest.created_at
FROM (SELECT chat_id, max(created_at) AS created_at FROM messages GROUP BY chat_id) AS latest
WHERE chats.id = latest.chat_id;
CREATE INDEX messages_history_idx ON messages (chat_id, created_at, id);
VACUUM ANALYZE;
SELECT json_build_object(
'messages', (SELECT sum(n) FROM track_sizes),
'chats', 1000,
'zipf_s', 1.3,
'largest', (SELECT n FROM track_sizes WHERE k = 1),
'chat_50', (SELECT n FROM track_sizes WHERE k = 50),
'smallest', (SELECT n FROM track_sizes WHERE k = 1000),
'largest_share', round((SELECT n FROM track_sizes WHERE k = 1)::numeric / (SELECT sum(n) FROM track_sizes), 4),
'top10_share', round((SELECT sum(n) FROM track_sizes WHERE k <= 10)::numeric / (SELECT sum(n) FROM track_sizes), 4),
'table_bytes', pg_table_size('messages')
);