Добавили скрипт для добавления тестовых пользователей

This commit is contained in:
AidarKC
2026-11-11 00:51:49 +03:00
parent b220f2ac8b
commit 689321da1a
8 changed files with 7413 additions and 0 deletions
+429
View File
@@ -0,0 +1,429 @@
#!/usr/bin/env python3
"""Reproducible SQL read-model fixtures for 250 SHiNE DEMO profiles.
Stdlib only. No real user registration, Arweave, signed blocks, secrets or posts.
Never execute generated SQL against a production database.
"""
from __future__ import annotations
import csv
import hashlib
import random
from collections import Counter, defaultdict, deque
from pathlib import Path
HERE = Path(__file__).resolve().parent
COUNT = 250
SEED = 20261111
MARKER = 'SHINE_DEV_GRAPH_250_' # 20 characters, the free_data_item_id prefix
REL_HASH = hashlib.sha256(b'SHINE-TEST-GRAPH-250-RELATIONS-V1').hexdigest()
GROUPS = [
('Йога и медитация', 45),
('Разработка', 34),
('Искусство', 30),
('Музыка', 27),
('Путешествия', 25),
('Экология', 23),
('Наука', 21),
('Книги', 18),
('Фотография', 15),
('Волонтёрство', 12),
]
# Special hubs are capped to EXACT undirected friend counts, chosen *after* all normal links.
HUBS = {1: 50, 52: 55, 112: 60}
MALE = ['Алексей', 'Иван', 'Даниил', 'Максим', 'Павел', 'Роман', 'Лев', 'Игорь', 'Марк', 'Никита',
'Дмитрий', 'Олег', 'Глеб', 'Артём', 'Михаил', 'Антон', 'Егор', 'Тимур', 'Сергей', 'Владимир']
FEMALE = ['Анна', 'Мария', 'Елена', 'Вера', 'София', 'Арина', 'Ольга', 'Кира', 'Анастасия', 'Диана',
'Виктория', 'Нина', 'Ева', 'Полина', 'Дарья', 'Татьяна', 'Ирина', 'Юлия', 'Лилия', 'Алина']
SURNAMES = ['Иванов', 'Петров', 'Сидоров', 'Орлов', 'Белов', 'Новиков', 'Волков', 'Морозов',
'Фёдоров', 'Крылов', 'Лебедев', 'Соколов', 'Миронов', 'Никитин', 'Громов',
'Кузнецов', 'Гусев', 'Лазарев', 'Титов', 'Поляков']
HOBBIES = [
'Люблю делиться знаниями и находить единомышленников.',
'Общаюсь с друзьями и участвую в совместных проектах.',
'Нахожу вдохновение в творчестве и новых знакомствах.',
'Помогаю друзьям и участвую в жизни сообщества.',
'Интересуюсь новыми идеями и интересными встречами.',
]
def login(i: int) -> str:
return f'testuser{i:03d}@free'
def quote(value: object) -> str:
return "'" + str(value).replace("'", "''") + "'"
def b58(data: bytes) -> str:
abc = '123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz'
n = int.from_bytes(data, 'big')
chars = ''
while n:
n, rem = divmod(n, 58)
chars = abc[rem] + chars
return '1' * (len(data) - len(data.lstrip(b'\0'))) + (chars or '1')
def placeholder(i: int, kind: str) -> str:
# Public-looking synthetics, no corresponding private keys. Not login-capable.
return b58(hashlib.sha256(f'SHINE-DEMO-{SEED}-{i}-{kind}'.encode()).digest())
def network():
rng = random.Random(SEED)
all_ids = list(range(1, COUNT + 1))
official = set(rng.sample(all_ids, 167)) # ~2/3 official
shining = set(rng.sample(all_ids, 83)) # ~1/3 shining; independent of official
members = {}
community_for = {}
cursor = 1
for name, size in GROUPS:
members[name] = list(range(cursor, cursor + size))
for person in members[name]:
community_for[person] = name
cursor += size
assert cursor == COUNT + 1
users = []
for i in all_ids:
group_idx = next(g for g, (name, _) in enumerate(GROUPS) if community_for[i] == name)
female = ((i + group_idx) % 2 == 1)
first = (FEMALE if female else MALE)[(i * 7 + group_idx * 3) % 20]
last = SURNAMES[(i * 11 + group_idx * 2) % 20] + ('а' if female else '')
# 100 profiles also participate in a second community; membership is *text in biography*.
secondary = ''
if i % 5 in (0, 2):
secondary = GROUPS[(group_idx + 2 + i % 5) % len(GROUPS)][0]
my_groups = [community_for[i]] + ([secondary] if secondary else [])
about = f'ДЕМО SHiNE. Мои сообщества: {", ".join(my_groups)}. {HOBBIES[(i + group_idx) % len(HOBBIES)]}'
assert len(about) <= 160, (i, len(about))
users.append({
'id': i, 'login': login(i), 'first': first, 'last': last,
'gender': 'female' if female else 'male', 'shine': 'shining' if i in shining else 'unknown',
'role': 'primary' if i in official else 'non_voting',
'community': community_for[i], 'secondary': secondary, 'about': about,
})
pairs: set[tuple[int, int]] = set()
adjacency = defaultdict(set)
def add_pair(a, b):
if a == b or b in adjacency[a]:
return
pairs.add((min(a, b), max(a, b)))
adjacency[a].add(b)
adjacency[b].add(a)
# Dense local circles: rings plus enough inside-community edges for diverse degrees.
for group_idx, (name, size) in enumerate(GROUPS):
cohort = members[name]
for idx, a in enumerate(cohort):
add_pair(a, cohort[(idx + 1) % size])
add_pair(a, cohort[(idx + 2) % size])
for a in cohort:
target_degree = rng.randint(6, 13)
attempts = 0
while len(adjacency[a]) < target_degree and attempts < 140:
attempts += 1
if rng.random() < 0.84:
b = rng.choice(cohort)
else:
b = rng.choice(all_ids)
add_pair(a, b)
# Bridging links between adjacent and second-next communities.
next_cohort = members[GROUPS[(group_idx + 1) % len(GROUPS)][0]]
next2 = members[GROUPS[(group_idx + 2) % len(GROUPS)][0]]
add_pair(cohort[0], next_cohort[0])
add_pair(cohort[size // 2], next_cohort[len(next_cohort) // 2])
add_pair(cohort[1], next2[1])
# A few much larger hubs; #1 has exactly 50 distinct friends.
for hub, target in HUBS.items():
if len(adjacency[hub]) > target:
raise AssertionError(f'hub already has {len(adjacency[hub])} links')
candidates = [x for x in all_ids if x != hub and x not in adjacency[hub]]
rng.shuffle(candidates)
for peer in candidates[:target - len(adjacency[hub])]:
add_pair(hub, peer)
assert len(adjacency[hub]) == target
# Friendship is *directional* in SHiNE; most pairs are reciprocated.
edges: set[tuple[int, int, int]] = set()
friends: set[tuple[int, int]] = set()
for a, b in sorted(pairs):
directions = [(a, b), (b, a)] if (a in HUBS or b in HUBS or rng.random() < 0.92) else ([(a, b)] if rng.random() < 0.5 else [(b, a)])
close = rng.random() < 0.24
for n, (src, dst) in enumerate(directions):
tier = 10 if close else 14
if n and rng.random() < 0.025:
tier = 14 if close else 10
edges.add((src, tier, dst))
friends.add((src, dst))
# Received verifications: 500 shine / 1,000 official. People get 0/1/5/10/20.
shine_quotas = [20] * 11 + [10] * 16 + [5] * 20 + [1] * 20 + [0] * 16
official_quotas = [20] * 20 + [10] * 38 + [5] * 40 + [1] * 20 + [0] * 49
assert len(shine_quotas) == len(shining) and sum(shine_quotas) == 500
assert len(official_quotas) == len(official) and sum(official_quotas) == 1000
user_index = {u['id']: u for u in users}
def confirm(targets, quotas, eligible, kind):
issued = Counter()
targets = list(sorted(targets))
rng.shuffle(targets)
for target, desired in zip(targets, quotas):
selected = set()
for _ in range(desired):
candidates = [src for src in eligible if src != target and src not in selected and issued[src] < 40]
if not candidates:
raise AssertionError(f'no issuers left: target={target} kind={kind}')
weights = []
for src in candidates:
a = user_index[src]
b = user_index[target]
shared = bool(({a['community'], a['secondary']} - {''}) &
({b['community'], b['secondary']} - {''}))
reciprocal = (src, target) in friends and (target, src) in friends
affinity = 10 if reciprocal else 6 if (src, target) in friends else 2 if shared else 0.7
weights.append(affinity / (1 + 0.09 * issued[src]))
src = rng.choices(candidates, weights=weights, k=1)[0]
selected.add(src)
issued[src] += 1
edges.add((src, kind, target))
confirm(shining, shine_quotas, sorted(shining & official), 70)
confirm(official, official_quotas, sorted(official), 80)
# Social network: all accounts reachable via friendship ignoring direction.
seen = {1}
queue = deque([1])
while queue:
for adjacent in adjacency[queue.popleft()]:
if adjacent not in seen:
seen.add(adjacent)
queue.append(adjacent)
assert len(seen) == COUNT
friend_pairs = {tuple(sorted((a, b))) for a, b in friends}
mutual = sum((a, b) in friends and (b, a) in friends for a, b in friend_pairs)
assert mutual / len(friend_pairs) >= 0.88
assert len(set(edges)) == len(edges)
assert sum(t == 70 for _, t, _ in edges) == 500
assert sum(t == 80 for _, t, _ in edges) == 1000
assert all(a != b for a, _, b in edges)
return users, sorted(edges), {k: len(v) for k, v in adjacency.items()}
def insert_rows(out, table, columns, rows, on_conflict, batch_size=240):
for begin in range(0, len(rows), batch_size):
chunk = rows[begin:begin + batch_size]
values = ',\n'.join(' (' + ', '.join(row) + ')' for row in chunk)
out.append(f'INSERT INTO {table} ({columns}) VALUES\n{values}\n{on_conflict};\n')
def preflight():
return r'''-- AUTO-GENERATED. PostgreSQL read-model fixtures, NOT signed SHiNE users.
-- Tested against the SHiNE v40 schema in the supplied archive (static review).
-- NEVER RUN IN PRODUCTION. Requires -v expected_db=... and PGOPTIONS guard.
\set ON_ERROR_STOP on
BEGIN;
SELECT set_config('shine.expected_test_database', :'expected_db', true);
DO $preflight$
DECLARE n integer;
BEGIN
IF current_setting('shine.allow_test_graph', true) IS DISTINCT FROM 'yes' THEN
RAISE EXCEPTION 'Denied: set PGOPTIONS=-c shine.allow_test_graph=yes';
END IF;
IF current_database() IS DISTINCT FROM current_setting('shine.expected_test_database', true) THEN
RAISE EXCEPTION 'Denied: connected to %, but expected database is %', current_database(), current_setting('shine.expected_test_database', true);
END IF;
IF current_database() !~ '(^shine_t[1-4]_db$|(_dev|_test|_test_db)$)' THEN
RAISE EXCEPTION 'Denied: database % does not look like DEV/TEST', current_database();
END IF;
IF NOT EXISTS (SELECT 1 FROM db_schema_version WHERE id=1 AND schema_version >= 40) THEN
RAISE EXCEPTION 'Requires SHiNE PostgreSQL schema version >= 40';
END IF;
IF NOT EXISTS (SELECT 1 FROM shine_namespace_current WHERE namespace='free') THEN
RAISE EXCEPTION 'Missing free namespace; do not invent a live namespace';
END IF;
IF EXISTS (SELECT 1 FROM user_identity_current i
WHERE i.normalized_login ~ '^testuser[0-9]{3}@free$'
AND substring(i.normalized_login from 9 for 3)::integer BETWEEN 1 AND 250
AND i.free_data_item_id IS DISTINCT FROM
('SHINE_DEV_GRAPH_250_' || substring(i.normalized_login from 9 for 3))) THEN
RAISE EXCEPTION 'testuser001..250 login collision with non-250 fixture; seed refused';
END IF;
IF EXISTS (SELECT 1 FROM user_identity_current i
WHERE left(i.free_data_item_id,20)='SHINE_DEV_GRAPH_250_'
AND (i.normalized_login !~ '^testuser[0-9]{3}@free$'
OR substring(i.normalized_login from 9 for 3) NOT BETWEEN '001' AND '250'
OR i.free_data_item_id IS DISTINCT FROM
('SHINE_DEV_GRAPH_250_' || substring(i.normalized_login from 9 for 3)))) THEN
RAISE EXCEPTION 'Fixture marker used by an unexpected login; refused';
END IF;
SELECT count(*) INTO n FROM user_identity_current
WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_';
IF n NOT IN (0,250) THEN
RAISE EXCEPTION 'Expected 0 or 250 owned fixtures, found %', n;
END IF;
IF EXISTS (SELECT 1 FROM free_registration_certificate f JOIN user_identity_current i
ON f.login=i.login WHERE left(i.free_data_item_id,20)='SHINE_DEV_GRAPH_250_')
OR EXISTS (SELECT 1 FROM blocks_store b JOIN user_identity_current i
ON lower(b.login)=i.normalized_login WHERE left(i.free_data_item_id,20)='SHINE_DEV_GRAPH_250_')
OR EXISTS (SELECT 1 FROM blockchain_state_store b JOIN user_identity_current i
ON lower(b.login)=i.normalized_login WHERE left(i.free_data_item_id,20)='SHINE_DEV_GRAPH_250_') THEN
RAISE EXCEPTION 'Fixture has signed certificate or blockchain entries; refused';
END IF;
IF EXISTS (SELECT 1 FROM connections_state cs
WHERE (lower(cs.login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_')
OR lower(cs.to_login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_'))
AND NOT (cs.to_block_number=0
AND cs.to_block_hash=decode(''' + quote(REL_HASH) + r''','hex')
AND lower(cs.login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_')
AND lower(cs.to_login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_'))) THEN
RAISE EXCEPTION 'Non-fixture relationships found; refusing to overwrite';
END IF;
END;
$preflight$;
-- Re-seed is safe: delete only graph-owned edges, refresh all six known profile fields.
DELETE FROM connections_state cs
WHERE cs.to_block_number=0 AND cs.to_block_hash=decode(''' + quote(REL_HASH) + r''','hex')
AND lower(cs.login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_')
AND lower(cs.to_login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_');
'''
def remove_sql():
# Explicitly reject non-fixture references, including FKs from later schema versions.
return preflight().split('-- Re-seed is safe:')[0].replace('-- AUTO-GENERATED.', '-- REMOVE FIXTURES.\n-- AUTO-GENERATED.') + r'''
-- Guard other child tables with a single-column FK to user_identity_current.
-- Whitelist only the generated read models, not signed chains, sessions or messages.
DO $references$
DECLARE dep record; attached boolean;
BEGIN
IF EXISTS (SELECT 1 FROM user_access_servers_current a
JOIN user_identity_current i ON lower(a.server_login)=i.normalized_login
WHERE left(i.free_data_item_id,20)='SHINE_DEV_GRAPH_250_'
AND lower(a.user_login) NOT IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_')) THEN
RAISE EXCEPTION 'Unexpected external access-server reference';
END IF;
FOR dep IN
SELECT c.conrelid::regclass AS table_name, a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid=c.conrelid AND a.attnum=c.conkey[1]
WHERE c.contype='f' AND c.confrelid='user_identity_current'::regclass
AND array_length(c.conkey, 1)=1
LOOP
IF dep.table_name IN ('connections_state'::regclass, 'users_params'::regclass,
'user_stats_state'::regclass, 'user_access_servers_current'::regclass) THEN
CONTINUE;
END IF;
EXECUTE format('SELECT EXISTS (SELECT 1 FROM %s t JOIN user_identity_current i ON lower(t.%I)=i.normalized_login WHERE left(i.free_data_item_id,20)=%L)',
dep.table_name, dep.column_name, 'SHINE_DEV_GRAPH_250_') INTO attached;
IF attached THEN
RAISE EXCEPTION 'Refused: additional records in %.% reference demo users', dep.table_name, dep.column_name;
END IF;
END LOOP;
END;
$references$;
DELETE FROM connections_state cs
WHERE cs.to_block_number=0 AND cs.to_block_hash=decode(''' + quote(REL_HASH) + r''','hex')
AND lower(cs.login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_')
AND lower(cs.to_login) IN (SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_');
DELETE FROM user_stats_state s WHERE lower(s.login) IN
(SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_');
DELETE FROM user_profile_state p WHERE lower(p.login) IN
(SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_');
DELETE FROM users_params p WHERE lower(p.login) IN
(SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_');
DELETE FROM user_access_servers_current a WHERE lower(a.user_login) IN
(SELECT normalized_login FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_');
DELETE FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_';
COMMIT;
'''
def generate():
users, edges, degrees = network()
now = '(extract(epoch from clock_timestamp()) * 1000)::bigint'
out = [preflight()]
identity_rows = []
params_rows = []
for user in users:
i = user['id']
l = user['login']
identity_rows.append([
quote(l), quote(l), quote('free'), quote('ARWEAVE'), quote('FREE_CERT'), 'NULL',
quote(f'{MARKER}{i:03d}'), quote(placeholder(i, 'root')),
quote(placeholder(i, 'client')), quote(placeholder(i, 'blockchain')),
quote(l + '-001'), 'FALSE', now, now,
])
for key, value in [
('first_name', user['first']), ('last_name', user['last']),
('gender', user['gender']), ('shine', user['shine']),
('account_role', user['role']), ('about', user['about']),
]:
params_rows.append([quote(l), quote(key), now, quote(value), 'NULL', 'NULL'])
insert_rows(out, 'user_identity_current',
'login, normalized_login, namespace, registration_source, registration_status, '
'solana_pda_address, free_data_item_id, root_key, client_key, blockchain_key, '
'blockchain_name, is_server, registered_at_ms, updated_at_ms',
identity_rows, 'ON CONFLICT (login) DO NOTHING')
insert_rows(out, 'users_params',
'login, param, time_ms, value, client_key, signature',
params_rows, 'ON CONFLICT (login, param) DO UPDATE SET '
'time_ms=EXCLUDED.time_ms, value=EXCLUDED.value, client_key=NULL, signature=NULL')
out.append("SELECT shine_refresh_user_profile(login) FROM user_identity_current "
"WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_';\n")
relrows = [[quote(login(a)), str(kind), quote(login(b)),
'0', f"decode('{REL_HASH}','hex')"] for a, kind, b in edges]
insert_rows(out, 'connections_state',
'login, rel_type, to_login, to_block_number, to_block_hash',
relrows, 'ON CONFLICT (login, rel_type, to_login, to_block_number, to_block_hash) DO NOTHING')
out.append("SELECT shine_refresh_user_stats(login) FROM user_identity_current "
"WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_';\n")
out.append(f'''DO $assertions$
BEGIN
IF (SELECT count(*) FROM user_identity_current WHERE left(free_data_item_id,20)='SHINE_DEV_GRAPH_250_') <> 250 THEN
RAISE EXCEPTION '250 identities were expected'; END IF;
IF (SELECT count(*) FROM user_profile_state p JOIN user_identity_current i ON p.login=i.login WHERE left(i.free_data_item_id,20)='SHINE_DEV_GRAPH_250_') <> 250 THEN
RAISE EXCEPTION '250 projected user profiles were expected'; END IF;
IF (SELECT count(*) FROM user_stats_state s JOIN user_identity_current i ON s.login=i.login WHERE left(i.free_data_item_id,20)='SHINE_DEV_GRAPH_250_') <> 250 THEN
RAISE EXCEPTION '250 projected user stats were expected'; END IF;
IF (SELECT count(*) FROM connections_state WHERE rel_type=70 AND to_block_hash=decode('{REL_HASH}','hex')) <> 500 THEN
RAISE EXCEPTION '500 shining endorsements were expected'; END IF;
IF (SELECT count(*) FROM connections_state WHERE rel_type=80 AND to_block_hash=decode('{REL_HASH}','hex')) <> 1000 THEN
RAISE EXCEPTION '1000 official endorsements were expected'; END IF;
END;
$assertions$;
COMMIT;
''')
(HERE / 'seed.sql').write_text(''.join(out), encoding='utf-8')
(HERE / 'remove.sql').write_text(remove_sql(), encoding='utf-8')
with (HERE / 'users.csv').open('w', encoding='utf-8-sig', newline='') as f:
w = csv.writer(f)
w.writerow(['login', 'имя', 'фамилия', 'главное сообщество', 'дополнительное сообщество',
'о себе', 'сияние', 'роль', 'друзья', 'близкие друзья',
'получено подтверждений сияния', 'получено подтверждений официальности',
'выдано подтверждений сияния', 'выдано подтверждений официальности'])
outcounts = defaultdict(Counter)
incounts = defaultdict(Counter)
for src, kind, dst in edges:
outcounts[src][kind] += 1
incounts[dst][kind] += 1
for u in users:
i = u['id']
w.writerow([u['login'], u['first'], u['last'], u['community'], u['secondary'],
u['about'], u['shine'], u['role'], outcounts[i][14], outcounts[i][10],
incounts[i][70], incounts[i][80], outcounts[i][70], outcounts[i][80]])
types = Counter(t for _, t, _ in edges)
print('Users:', len(users), 'Groups:', dict(GROUPS))
print('Roles:', dict(Counter(u['role'] for u in users)),
'Shine:', dict(Counter(u['shine'] for u in users)))
print('Relationships:', dict(sorted(types.items())))
print('Hubs:', {login(k): degrees[k] for k in HUBS})
return users, edges, degrees
if __name__ == '__main__':
generate()