Files
SHiNE-server/scripts/dev/test-graph-250/remove.sql
T

110 lines
6.6 KiB
PL/PgSQL

-- REMOVE FIXTURES.
-- 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('1e6d5c945427d0891c741f1eef93be0f4df0fa6611f61c343f3b2f68f9b6da35','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$;
-- 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('1e6d5c945427d0891c741f1eef93be0f4df0fa6611f61c343f3b2f68f9b6da35','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;