Files
SHiNE-server/scripts/dev/test-graph-250/generate.py
T

430 lines
22 KiB
Python
Raw Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
#!/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()