-- 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;