SHA256
430 lines
22 KiB
Python
430 lines
22 KiB
Python
#!/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()
|