3bb1c28831
Adds pg_trgm/unaccent, search_normalize, generated title_search/name_search columns, game_alt_titles (read-only for API roles), manual_fields key alt_titles and the RPC search_catalog_games. pgTAP 09_catalog_search covers rights, normalization, relevance, filters, paging and the runbook snippets. Refs #3 Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
605 lines
48 KiB
PL/PgSQL
605 lines
48 KiB
PL/PgSQL
-- KAT-05 (PR 2a): Katalog-Fundament. Quellen, Verlage, Ausblenden, manual_fields, View catalog_games,
|
|
-- Spaltenrechte auf games, Startbestand-Werkzeug (Fixtures) und die SQL-Bausteine aus docs/katalog-pflege.md.
|
|
-- Läuft auch mit eingespieltem Startbestand: eigene Fixtures mit festen IDs, gezählt wird nur darüber.
|
|
-- Die Include-Dateien in ../support/ (\ir) erzeugt supabase/catalog/build_startbestand.ts aus catalog/testdata/.
|
|
begin;
|
|
select plan(148);
|
|
|
|
insert into auth.users (id, email, raw_user_meta_data) values
|
|
('11111111-1111-1111-1111-111111111111', 'anna@example.com', '{"age_confirmed":"true","terms_accepted":"true","terms_version":"test","display_name":"Anna"}'),
|
|
('22222222-2222-2222-2222-222222222222', 'ben@example.com', '{"age_confirmed":"true","terms_accepted":"true","terms_version":"test","display_name":"Ben"}');
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- Struktur (KAT-05 Kriterium 2)
|
|
-- ---------------------------------------------------------------------------------------------
|
|
select col_is_pk('public', 'games', 'id', 'KAT-05 Kriterium 2: games.id ist der Primärschlüssel');
|
|
select col_type_is('public', 'games', 'id', 'uuid', 'KAT-05 Kriterium 2: games.id ist eine uuid');
|
|
select col_has_default('public', 'games', 'id', 'KAT-05 Kriterium 2: games.id hat einen Standardwert (eigene Shuffle-ID)');
|
|
select is((select count(*) from information_schema.columns
|
|
where table_schema = 'public' and table_name = 'games'
|
|
and column_name in ('description_locale', 'hidden_at', 'merged_into_id', 'manual_fields',
|
|
'catalog_source_id', 'source_ref', 'source_url', 'source_synced_at')),
|
|
8::bigint, 'KAT-05 Kriterium 2: games hat die neuen Spalten');
|
|
select results_eq($$select key, name, images_allowed, texts_allowed from public.catalog_sources where key = 'bgg'$$,
|
|
$$values ('bgg'::text, 'BoardGameGeek'::text, false, false)$$,
|
|
'KAT-05 Kriterium 6: Quelle bgg ist angelegt, ohne Anzeige-Erlaubnis für Bilder und Texte');
|
|
|
|
insert into public.games (id, title, visibility) values
|
|
('f6000000-0000-0000-0000-000000000001', 'Fixture Eins', 'catalog'),
|
|
('f6000000-0000-0000-0000-000000000002', 'Fixture Zwei', 'catalog');
|
|
|
|
select lives_ok($$update public.games set id = 'f6000000-0000-0000-0000-0000000000ff', title = 'Fixture Eins'
|
|
where id = 'f6000000-0000-0000-0000-000000000001'$$, 'Update mit neuer id läuft ohne Fehler');
|
|
select is((select count(*) from public.games where id = 'f6000000-0000-0000-0000-000000000001'), 1::bigint,
|
|
'KAT-05 Kriterium 2: die ID bleibt bei einem Update unverändert');
|
|
|
|
select throws_ok($$update public.games set catalog_source_id = (select id from public.catalog_sources where key = 'bgg')
|
|
where id = 'f6000000-0000-0000-0000-000000000001'$$, '23514', null,
|
|
'KAT-05 Kriterium 2: Quelle ohne source_ref wird abgelehnt');
|
|
select throws_ok($$update public.games set source_ref = '13' where id = 'f6000000-0000-0000-0000-000000000001'$$,
|
|
'23514', null, 'KAT-05 Kriterium 2: source_ref ohne Quelle wird abgelehnt');
|
|
select throws_ok($$update public.games set source_url = 'https://example.org/x' where id = 'f6000000-0000-0000-0000-000000000001'$$,
|
|
'23514', null, 'source_url ohne Quelle wird abgelehnt');
|
|
update public.games set catalog_source_id = (select id from public.catalog_sources where key = 'bgg'), source_ref = '13'
|
|
where id = 'f6000000-0000-0000-0000-000000000001';
|
|
select throws_ok($$update public.games set catalog_source_id = (select id from public.catalog_sources where key = 'bgg'), source_ref = '13'
|
|
where id = 'f6000000-0000-0000-0000-000000000002'$$, '23505', null,
|
|
'KAT-05 Kriterium 2: (Quelle, source_ref) ist im Katalog eindeutig');
|
|
select throws_ok($$update public.games set source_url = 'http://example.org/x' where id = 'f6000000-0000-0000-0000-000000000001'$$,
|
|
'23514', null, 'source_url muss mit https:// beginnen');
|
|
select throws_ok($$update public.catalog_sources set terms_url = 'http://example.org' where key = 'bgg'$$,
|
|
'23514', null, 'KAT-05 Kriterium 2: terms_url muss mit https:// beginnen');
|
|
select throws_ok($$update public.catalog_sources set attribution_url = 'ftp://example.org' where key = 'bgg'$$,
|
|
'23514', null, 'attribution_url muss mit https:// beginnen');
|
|
select throws_ok($$insert into public.catalog_sources (key, name) values ('BGG!', 'Falsch')$$, '23514', null,
|
|
'Schlüssel einer Quelle: nur a-z, 0-9, Unterstrich');
|
|
select throws_ok($$delete from public.catalog_sources where key = 'bgg'$$, '23503', null,
|
|
'Eine Quelle mit Spielen wird nicht gelöscht (on delete restrict)');
|
|
|
|
select throws_ok($$update public.games set description_locale = 'fr', description = 'x' where id = 'f6000000-0000-0000-0000-000000000002'$$,
|
|
'23514', null, 'description_locale nur de oder en');
|
|
select throws_ok($$update public.games set description_locale = 'de' where id = 'f6000000-0000-0000-0000-000000000002'$$,
|
|
'23514', null, 'description_locale nur zusammen mit description');
|
|
select throws_ok($$update public.games set manual_fields = array['title', 'unbekannt'] where id = 'f6000000-0000-0000-0000-000000000002'$$,
|
|
'23514', null, 'KAT-05 Kriterium 1: unbekannter Schlüssel in manual_fields wird abgelehnt');
|
|
select throws_ok($$update public.games set merged_into_id = id, hidden_at = now() where id = 'f6000000-0000-0000-0000-000000000002'$$,
|
|
'23514', null, 'Ein Spiel kann nicht in sich selbst aufgehen');
|
|
select throws_ok($$update public.games set merged_into_id = 'f6000000-0000-0000-0000-000000000001' where id = 'f6000000-0000-0000-0000-000000000002'$$,
|
|
'23514', null, 'Zusammengeführte Spiele müssen ausgeblendet sein');
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- Trigger games_merged_into_catalog und on delete restrict (KAT-05 Kriterium 4)
|
|
-- ---------------------------------------------------------------------------------------------
|
|
insert into public.games (id, title, visibility, owner_id) values
|
|
('f6000000-0000-0000-0000-0000000000a1', 'Annas Prototyp', 'private', '11111111-1111-1111-1111-111111111111'),
|
|
('f6000000-0000-0000-0000-0000000000b1', 'Bens Prototyp', 'private', '22222222-2222-2222-2222-222222222222');
|
|
select throws_ok($$update public.games set merged_into_id = 'f6000000-0000-0000-0000-0000000000a1', hidden_at = now()
|
|
where id = 'f6000000-0000-0000-0000-000000000002'$$, 'P0001', 'merged_into_not_catalog',
|
|
'KAT-05 Kriterium 4: merged_into_id auf ein privates Spiel wird abgelehnt');
|
|
select throws_ok($$insert into public.games (title, visibility, hidden_at, merged_into_id)
|
|
values ('Fixture Drei', 'catalog', now(), 'f6000000-0000-0000-0000-0000000000a1')$$, 'P0001', 'merged_into_not_catalog',
|
|
'merged_into_id auf ein privates Spiel wird auch beim Anlegen abgelehnt');
|
|
select lives_ok($$update public.games set merged_into_id = 'f6000000-0000-0000-0000-000000000001', hidden_at = now()
|
|
where id = 'f6000000-0000-0000-0000-000000000002'$$, 'merged_into_id auf ein Katalogspiel ist erlaubt');
|
|
select throws_ok($$delete from public.games where id = 'f6000000-0000-0000-0000-000000000001'$$, '23503', null,
|
|
'KAT-05 Kriterium 4: Ein Zieleintrag mit Verweis wird nicht gelöscht (on delete restrict)');
|
|
update public.games set merged_into_id = null, hidden_at = null where id = 'f6000000-0000-0000-0000-000000000002';
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- Verlage
|
|
-- ---------------------------------------------------------------------------------------------
|
|
insert into public.publishers (id, name) values ('f6000000-0000-0000-0000-0000000000c1', 'Fixture Verlag');
|
|
select throws_ok($$insert into public.publishers (name) values ('FIXTURE VERLAG')$$, '23505', null,
|
|
'Verlagsnamen sind unabhängig von der Schreibweise eindeutig');
|
|
select throws_ok($$insert into public.publishers (name) values (repeat('x', 201))$$, '23514', null,
|
|
'Verlagsname höchstens 200 Zeichen');
|
|
select lives_ok($$insert into public.game_publishers (game_id, publisher_id)
|
|
values ('f6000000-0000-0000-0000-000000000001', 'f6000000-0000-0000-0000-0000000000c1')$$,
|
|
'Ein Katalogspiel bekommt einen Verlag');
|
|
select throws_ok($$insert into public.game_publishers (game_id, publisher_id)
|
|
values ('f6000000-0000-0000-0000-000000000001', 'f6000000-0000-0000-0000-0000000000c1')$$, '23505', null,
|
|
'Das Paar Spiel/Verlag ist eindeutig');
|
|
select lives_ok($$insert into public.game_publishers (game_id, publisher_id)
|
|
values ('f6000000-0000-0000-0000-000000000002', 'f6000000-0000-0000-0000-0000000000c1')$$,
|
|
'Ein Verlag hat mehrere Spiele');
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- manual_fields (KAT-05 Kriterium 1, nur Datenmodell; Kriterium 4)
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- Zeile 1: nur Titel gesetzt. Zeile 2: Fakten gesetzt (Spec-F4: alle gesetzten Felder gelten als manuell).
|
|
insert into public.games (id, title, year, min_players, description, description_locale, visibility) values
|
|
('f6000000-0000-0000-0000-0000000000d1', 'Fixture Manuell', 1999, 2, 'Eigener Text.', 'de', 'catalog');
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-0000000000d1'),
|
|
array['description', 'description_locale', 'min_players', 'title', 'year'],
|
|
'KAT-05 Kriterium 1: Insert markiert alle gesetzten Felder, sortiert und ohne Duplikate');
|
|
insert into public.games (id, title, visibility) values ('f6000000-0000-0000-0000-0000000000d2', 'Fixture Nur Titel', 'catalog');
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-0000000000d2'),
|
|
array['title'], 'KAT-05 Kriterium 1: Insert mit nur einem gesetzten Feld markiert nur dieses');
|
|
|
|
update public.games set min_age = 8 where id = 'f6000000-0000-0000-0000-0000000000d1';
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-0000000000d1'),
|
|
array['description', 'description_locale', 'min_age', 'min_players', 'title', 'year'],
|
|
'KAT-05 Kriterium 1: Update als postgres ergänzt das geänderte Feld');
|
|
update public.games set manual_fields = array_remove(manual_fields, 'min_age') where id = 'f6000000-0000-0000-0000-0000000000d1';
|
|
select is((select 'min_age' = any(manual_fields) from public.games where id = 'f6000000-0000-0000-0000-0000000000d1'),
|
|
false, 'KAT-05 Kriterium 4: Eine bewusst entfernte Markierung bleibt entfernt');
|
|
update public.games set min_age = min_age where id = 'f6000000-0000-0000-0000-0000000000d1';
|
|
select is((select 'min_age' = any(manual_fields) from public.games where id = 'f6000000-0000-0000-0000-0000000000d1'),
|
|
false, 'Ein Update ohne Wertänderung markiert das Feld nicht wieder');
|
|
update public.games set min_age = 10, manual_fields = array_remove(manual_fields, 'min_age') where id = 'f6000000-0000-0000-0000-0000000000d1';
|
|
select is((select 'min_age' = any(manual_fields) from public.games where id = 'f6000000-0000-0000-0000-0000000000d1'),
|
|
true, 'Wer das Feld ändert, markiert es wieder, auch wenn die Markierung im selben Update entfernt wird');
|
|
|
|
-- set local als postgres schaltet die Kennzeichnung NICHT ab (W7)
|
|
set local shuffle.catalog_import = 'on';
|
|
update public.games set min_age = 12 where id = 'f6000000-0000-0000-0000-0000000000d1';
|
|
update public.games set manual_fields = array_remove(manual_fields, 'min_age') where id = 'f6000000-0000-0000-0000-0000000000d1';
|
|
update public.games set min_age = 13 where id = 'f6000000-0000-0000-0000-0000000000d1';
|
|
select is((select 'min_age' = any(manual_fields) from public.games where id = 'f6000000-0000-0000-0000-0000000000d1'),
|
|
true, 'KAT-05 Kriterium 1: set local shuffle.catalog_import als postgres schaltet die Kennzeichnung nicht ab');
|
|
reset shuffle.catalog_import;
|
|
|
|
-- Privates Spiel: keine Kennzeichnung
|
|
insert into public.games (id, title, year, visibility, owner_id) values
|
|
('f6000000-0000-0000-0000-0000000000a2', 'Annas Zweites', 2001, 'private', '11111111-1111-1111-1111-111111111111');
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-0000000000a2'),
|
|
'{}'::text[], 'Private Spiele werden nicht gekennzeichnet');
|
|
|
|
-- Import-Ausnahme: Rolle catalog_importer UND Einstellung on (der Import setzt sie per set local)
|
|
create role catalog_importer nologin;
|
|
grant catalog_importer to postgres with set true;
|
|
grant usage on schema public to catalog_importer;
|
|
-- Seit KAT-01 berechnet jeder Schreibende title_search selbst: auch der Import braucht EXECUTE auf
|
|
-- search_normalize und USAGE auf das Schema extensions (unaccent). Hinweis für SAM-08.
|
|
grant execute on function public.search_normalize(text) to catalog_importer;
|
|
grant usage on schema extensions to catalog_importer;
|
|
grant select, insert, update on public.games to catalog_importer;
|
|
grant select, insert, update, delete on public.game_genres to catalog_importer;
|
|
create policy t_importer_games on public.games for all to catalog_importer using (true) with check (true);
|
|
create policy t_importer_links on public.game_genres for all to catalog_importer using (true) with check (true);
|
|
insert into public.genres (id, name_de, name_en) values
|
|
('f6000000-0000-0000-0000-0000000000e1', 'Fixture Genre Eins', 'Fixture genre one'),
|
|
('f6000000-0000-0000-0000-0000000000e2', 'Fixture Genre Zwei', 'Fixture genre two');
|
|
|
|
set local role catalog_importer;
|
|
set local shuffle.catalog_import = 'on';
|
|
insert into public.games (id, title, year, visibility) values
|
|
('f6000000-0000-0000-0000-0000000000f1', 'Fixture Import', 2010, 'catalog');
|
|
insert into public.game_genres (game_id, genre_id) values
|
|
('f6000000-0000-0000-0000-0000000000f1', 'f6000000-0000-0000-0000-0000000000e1');
|
|
reset shuffle.catalog_import;
|
|
reset role;
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-0000000000f1'),
|
|
'{}'::text[], 'KAT-05 Kriterium 1: Rolle catalog_importer mit Einstellung on (der Import setzt sie per set local) markiert nichts (Insert und Zuordnung)');
|
|
|
|
set local role catalog_importer;
|
|
update public.games set year = 2011 where id = 'f6000000-0000-0000-0000-0000000000f1';
|
|
reset role;
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-0000000000f1'),
|
|
array['year'], 'KAT-05 Kriterium 1: Rolle catalog_importer ohne die Einstellung wird gekennzeichnet');
|
|
|
|
-- Zuordnungen als Pseudo-Felder
|
|
update public.games set manual_fields = '{}' where id = 'f6000000-0000-0000-0000-0000000000f1';
|
|
insert into public.mechanics (id, name_de, name_en) values ('f6000000-0000-0000-0000-0000000000e3', 'Fixture Mechanik', 'Fixture mechanic');
|
|
insert into public.categories (id, name_de, name_en) values ('f6000000-0000-0000-0000-0000000000e4', 'Fixture Kategorie', 'Fixture category');
|
|
insert into public.game_genres (game_id, genre_id) values ('f6000000-0000-0000-0000-000000000002', 'f6000000-0000-0000-0000-0000000000e1');
|
|
insert into public.game_mechanics (game_id, mechanic_id) values ('f6000000-0000-0000-0000-000000000002', 'f6000000-0000-0000-0000-0000000000e3');
|
|
insert into public.game_categories (game_id, category_id) values ('f6000000-0000-0000-0000-000000000002', 'f6000000-0000-0000-0000-0000000000e4');
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-000000000002'),
|
|
array['categories', 'genres', 'mechanics', 'publishers', 'title'],
|
|
'KAT-05 Kriterium 1: Zuordnungen anlegen markiert genres, mechanics, categories und publishers');
|
|
|
|
update public.games set manual_fields = array['title'] where id = 'f6000000-0000-0000-0000-000000000002';
|
|
update public.game_genres set genre_id = 'f6000000-0000-0000-0000-0000000000e2'
|
|
where game_id = 'f6000000-0000-0000-0000-000000000002';
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-000000000002'),
|
|
array['genres', 'title'], 'KAT-05 Kriterium 1: Zuordnung ändern markiert genres');
|
|
update public.games set manual_fields = array['title'] where id = 'f6000000-0000-0000-0000-000000000002';
|
|
delete from public.game_genres where game_id = 'f6000000-0000-0000-0000-000000000002';
|
|
delete from public.game_publishers where game_id = 'f6000000-0000-0000-0000-000000000002';
|
|
select is((select manual_fields from public.games where id = 'f6000000-0000-0000-0000-000000000002'),
|
|
array['genres', 'publishers', 'title'], 'KAT-05 Kriterium 1: Zuordnung löschen markiert genres und publishers');
|
|
|
|
-- Stabilität: ist 'genres' schon markiert, schreibt eine neue oder gelöschte Zuordnung die Spielzeile nicht noch einmal.
|
|
-- (ctid statt updated_at, weil now() in der Transaktion konstant ist; jedes UPDATE erzeugt eine neue ctid.)
|
|
create temp table snap_game on commit drop as
|
|
select ctid::text as c from public.games where id = 'f6000000-0000-0000-0000-000000000002';
|
|
insert into public.game_genres (game_id, genre_id) values ('f6000000-0000-0000-0000-000000000002', 'f6000000-0000-0000-0000-0000000000e1');
|
|
select is((select count(*) from public.game_genres where game_id = 'f6000000-0000-0000-0000-000000000002'), 1::bigint,
|
|
'Neue Zuordnung ist angelegt (Trigger hat gefeuert)');
|
|
select is((select 'genres' = any(manual_fields) from public.games where id = 'f6000000-0000-0000-0000-000000000002'), true,
|
|
'genres ist bereits markiert');
|
|
select is((select g.ctid::text from public.games g where g.id = 'f6000000-0000-0000-0000-000000000002'),
|
|
(select c from snap_game), 'Eine neue Zuordnung schreibt die Spielzeile nicht erneut, wenn die Markierung schon da ist');
|
|
delete from public.game_genres where game_id = 'f6000000-0000-0000-0000-000000000002';
|
|
select is((select count(*) from public.game_genres where game_id = 'f6000000-0000-0000-0000-000000000002'), 0::bigint,
|
|
'Die Zuordnung ist gelöscht (Trigger hat gefeuert)');
|
|
select is((select g.ctid::text from public.games g where g.id = 'f6000000-0000-0000-0000-000000000002'),
|
|
(select c from snap_game), 'Eine gelöschte Zuordnung schreibt die Spielzeile nicht erneut, wenn die Markierung schon da ist');
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- View catalog_games: Ausblenden, Zusammenführen, Anzeige-Erlaubnis (Kriterien 4 und 6)
|
|
-- ---------------------------------------------------------------------------------------------
|
|
select is((select count(*) from public.catalog_games where id in
|
|
('f6000000-0000-0000-0000-000000000001', 'f6000000-0000-0000-0000-000000000002', 'f6000000-0000-0000-0000-0000000000a1')),
|
|
2::bigint, 'catalog_games enthält Katalogspiele, keine privaten');
|
|
|
|
insert into public.collection_items (owner_id, game_id)
|
|
values ('11111111-1111-1111-1111-111111111111', 'f6000000-0000-0000-0000-000000000002');
|
|
|
|
-- docs/katalog-pflege.md, Abschnitt 4: ausblenden
|
|
update public.games set hidden_at = now()
|
|
where title = 'Fixture Zwei' and visibility = 'catalog' and hidden_at is null;
|
|
|
|
select is((select count(*) from public.catalog_games where id = 'f6000000-0000-0000-0000-000000000002'), 0::bigint,
|
|
'KAT-05 Kriterium 4: Ein ausgeblendetes Spiel fehlt in catalog_games');
|
|
set local role anon;
|
|
select is((select count(*) from public.catalog_games where id = 'f6000000-0000-0000-0000-000000000002'), 0::bigint,
|
|
'KAT-05 Kriterium 4: Besucher finden das ausgeblendete Spiel nicht in catalog_games');
|
|
select is((select count(*) from public.games where id = 'f6000000-0000-0000-0000-000000000002'), 1::bigint,
|
|
'KAT-05 Kriterium 4: Das ausgeblendete Spiel bleibt per ID lesbar (Spec-F2)');
|
|
reset role;
|
|
select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}', true);
|
|
set local role authenticated;
|
|
select is((select count(*) from public.catalog_games where id = 'f6000000-0000-0000-0000-000000000002'), 0::bigint,
|
|
'KAT-05 Kriterium 4: Angemeldete finden das ausgeblendete Spiel nicht in catalog_games');
|
|
select is((select count(*) from public.collection_items where game_id = 'f6000000-0000-0000-0000-000000000002'), 1::bigint,
|
|
'KAT-05 Kriterium 4: Ein Sammlungseintrag zu einem ausgeblendeten Spiel bleibt lesbar');
|
|
reset role;
|
|
|
|
-- docs/katalog-pflege.md, Abschnitt 4: wieder einblenden
|
|
update public.games set hidden_at = null
|
|
where title = 'Fixture Zwei' and visibility = 'catalog';
|
|
select is((select count(*) from public.catalog_games where id = 'f6000000-0000-0000-0000-000000000002'), 1::bigint,
|
|
'KAT-05 Kriterium 4: Wieder eingeblendet ist das Spiel in catalog_games');
|
|
|
|
-- zusammengeführt
|
|
update public.games set merged_into_id = 'f6000000-0000-0000-0000-000000000001', hidden_at = now()
|
|
where id = 'f6000000-0000-0000-0000-000000000002';
|
|
select is((select count(*) from public.catalog_games where id = 'f6000000-0000-0000-0000-000000000002'), 0::bigint,
|
|
'KAT-05 Kriterium 4: Ein zusammengeführtes Spiel fehlt in catalog_games');
|
|
update public.games set merged_into_id = null, hidden_at = null where id = 'f6000000-0000-0000-0000-000000000002';
|
|
|
|
-- Anzeige-Erlaubnis je Quelle (zweite Sicherung, E6). Ein Import würde manual_fields leer lassen.
|
|
insert into public.games (id, title, description, description_locale, image_path, visibility,
|
|
catalog_source_id, source_ref, source_url)
|
|
values ('f6000000-0000-0000-0000-0000000000f2', 'Fixture Quelle', 'Fremder Text', 'en', 'covers/fremd.png', 'catalog',
|
|
(select id from public.catalog_sources where key = 'bgg'), '99', 'https://boardgamegeek.com/boardgame/99');
|
|
update public.games set manual_fields = '{}' where id = 'f6000000-0000-0000-0000-0000000000f2';
|
|
|
|
select results_eq($$select description, image_path from public.catalog_games where id = 'f6000000-0000-0000-0000-0000000000f2'$$,
|
|
$$values (null::text, null::text)$$,
|
|
'KAT-05 Kriterium 6: Text und Bild einer Quelle ohne Erlaubnis sind in catalog_games leer');
|
|
select results_eq($$select source_key, source_name, source_attribution_text, source_attribution_url, source_url
|
|
from public.catalog_games where id = 'f6000000-0000-0000-0000-0000000000f2'$$,
|
|
$$values ('bgg'::text, 'BoardGameGeek'::text, 'Powered by BGG'::text, 'https://boardgamegeek.com'::text,
|
|
'https://boardgamegeek.com/boardgame/99'::text)$$,
|
|
'catalog_games liefert die Quellenangabe');
|
|
update public.catalog_sources set texts_allowed = true where key = 'bgg';
|
|
select results_eq($$select description, image_path from public.catalog_games where id = 'f6000000-0000-0000-0000-0000000000f2'$$,
|
|
$$values ('Fremder Text'::text, null::text)$$,
|
|
'KAT-05 Kriterium 6: Mit Text-Erlaubnis erscheint der Text, das Bild weiter nicht');
|
|
update public.catalog_sources set texts_allowed = false, images_allowed = true where key = 'bgg';
|
|
select results_eq($$select description, image_path from public.catalog_games where id = 'f6000000-0000-0000-0000-0000000000f2'$$,
|
|
$$values (null::text, 'covers/fremd.png'::text)$$,
|
|
'KAT-05 Kriterium 6: Mit Bild-Erlaubnis erscheint das Bild, der Text nicht');
|
|
update public.catalog_sources set images_allowed = false where key = 'bgg';
|
|
update public.games set manual_fields = array['description', 'image_path'] where id = 'f6000000-0000-0000-0000-0000000000f2';
|
|
select results_eq($$select description, image_path from public.catalog_games where id = 'f6000000-0000-0000-0000-0000000000f2'$$,
|
|
$$values ('Fremder Text'::text, 'covers/fremd.png'::text)$$,
|
|
'KAT-05 Kriterium 6: Manuell gepflegte Felder erscheinen auch ohne Erlaubnis der Quelle');
|
|
select is((select description from public.catalog_games where id = 'f6000000-0000-0000-0000-0000000000d1'), 'Eigener Text.',
|
|
'KAT-05 Kriterium 6: Ein Spiel ohne Quelle zeigt seinen Text');
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- Rechte (KAT-05 Kriterium 9, Sicherheit der Daten Kriterium 2)
|
|
-- ---------------------------------------------------------------------------------------------
|
|
select is_empty($$select col from unnest(array['id', 'hidden_at', 'merged_into_id', 'manual_fields', 'catalog_source_id',
|
|
'source_ref', 'source_url', 'source_synced_at', 'created_at', 'updated_at']) as col
|
|
where has_column_privilege('authenticated', 'public.games', col, 'update')
|
|
or has_column_privilege('authenticated', 'public.games', col, 'insert')$$,
|
|
'KAT-05 Kriterium 9: Nutzer haben kein Schreibrecht auf id, hidden_at, merged_into_id, manual_fields, catalog_source_id, source_*');
|
|
select is_empty($$select col from unnest(array['owner_id', 'title', 'description', 'description_locale', 'year', 'min_players',
|
|
'max_players', 'min_minutes', 'max_minutes', 'min_age', 'image_path', 'bgg_id', 'visibility']) as col
|
|
where not has_column_privilege('authenticated', 'public.games', col, 'insert')
|
|
or (col <> 'owner_id' and not has_column_privilege('authenticated', 'public.games', col, 'update'))$$,
|
|
'Nutzer dürfen die Spalten laut Spec 3.2 anlegen und ändern');
|
|
select is_empty($$select p.proname from pg_proc p join pg_namespace n on n.oid = p.pronamespace
|
|
where n.nspname = 'public' and p.proname in ('games_mark_manual_fields', 'game_links_mark_manual', 'games_merged_into_catalog')
|
|
and (has_function_privilege('anon', p.oid, 'execute') or has_function_privilege('authenticated', p.oid, 'execute')
|
|
or has_function_privilege('service_role', p.oid, 'execute'))$$,
|
|
'Die neuen Funktionen sind für anon, authenticated und service_role nicht ausführbar');
|
|
select is_empty($$select t from unnest(array['catalog_sources', 'publishers', 'game_publishers']) as t
|
|
where not (select relrowsecurity from pg_class where oid = ('public.' || t)::regclass)$$,
|
|
'Die neuen Tabellen haben Row Level Security');
|
|
select is_empty($$select t, p from unnest(array['catalog_sources', 'publishers', 'game_publishers']) as t,
|
|
unnest(array['insert', 'update', 'delete', 'truncate', 'references', 'trigger']) as p
|
|
where has_table_privilege('anon', 'public.' || t, p)
|
|
or (p in ('truncate', 'references', 'trigger') and has_table_privilege('authenticated', 'public.' || t, p))$$,
|
|
'Grant-Matrix: anon hat auf den neuen Tabellen nur select, niemand hat truncate, references oder trigger');
|
|
select is_empty($$select t, p from unnest(array['catalog_sources', 'publishers', 'game_publishers']) as t,
|
|
unnest(array['select', 'insert', 'update', 'delete']) as p
|
|
where not has_table_privilege('authenticated', 'public.' || t, p)
|
|
or (p = 'select' and not has_table_privilege('anon', 'public.' || t, p))$$,
|
|
'Grant-Matrix: authenticated hat select, insert, update, delete (Schutz durch Policies), anon select');
|
|
|
|
-- Besucher
|
|
set local role anon;
|
|
select ok((select count(*) from public.catalog_sources) >= 1, 'Sicherheit Kriterium 2: Besucher lesen catalog_sources');
|
|
select is((select count(*) from public.publishers where id = 'f6000000-0000-0000-0000-0000000000c1'), 1::bigint,
|
|
'Sicherheit Kriterium 2: Besucher lesen publishers');
|
|
select is((select count(*) from public.game_publishers where game_id = 'f6000000-0000-0000-0000-000000000001'), 1::bigint,
|
|
'Sicherheit Kriterium 2: Besucher lesen game_publishers von Katalogspielen');
|
|
select is((select count(*) from public.catalog_games where id = 'f6000000-0000-0000-0000-000000000001'), 1::bigint,
|
|
'Sicherheit Kriterium 2: Besucher lesen catalog_games');
|
|
select throws_ok($$insert into public.publishers (name) values ('Besucher-Verlag')$$, '42501', null, 'Besucher legen keine Verlage an');
|
|
select throws_ok($$insert into public.catalog_sources (key, name) values ('besucher', 'Besucher')$$, '42501', null,
|
|
'Besucher legen keine Quellen an');
|
|
select throws_ok($$update public.games set title = 'Gehackt' where id = 'f6000000-0000-0000-0000-000000000001'$$, '42501', null,
|
|
'KAT-05 Kriterium 9: Besucher ändern keine Katalogspiele');
|
|
select throws_ok($$delete from public.game_publishers where game_id = 'f6000000-0000-0000-0000-000000000001'$$, '42501', null,
|
|
'Besucher löschen keine Verlagszuordnungen');
|
|
select throws_ok($$update public.catalog_sources set texts_allowed = true$$, '42501', null, 'Besucher ändern keine Quellen');
|
|
select throws_ok($$delete from public.catalog_sources$$, '42501', null, 'Besucher löschen keine Quellen');
|
|
select throws_ok($$update public.publishers set name = 'Gehackt'$$, '42501', null, 'Besucher ändern keine Verlage');
|
|
select throws_ok($$delete from public.publishers$$, '42501', null, 'Besucher löschen keine Verlage');
|
|
select throws_ok($$update public.catalog_games set title = 'Gehackt'$$, '55000', null, 'Die View catalog_games ist für Besucher nicht schreibbar (nicht aktualisierbar)');
|
|
select is_empty($$select p from unnest(array['insert', 'update', 'delete']) as p
|
|
where has_table_privilege('anon', 'public.catalog_games', p) or has_table_privilege('authenticated', 'public.catalog_games', p)$$,
|
|
'Kriterium 9: anon und authenticated haben auf catalog_games nur select');
|
|
reset role;
|
|
|
|
-- Anna
|
|
select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}', true);
|
|
set local role authenticated;
|
|
select lives_ok($$update public.games set title = 'Annas Prototyp 2' where id = 'f6000000-0000-0000-0000-0000000000a1'$$,
|
|
'Anna ändert den Titel ihres privaten Spiels');
|
|
select throws_ok($$update public.games set hidden_at = now() where id = 'f6000000-0000-0000-0000-0000000000a1'$$, '42501', null,
|
|
'KAT-05 Kriterium 9: Anna setzt hidden_at nicht, auch nicht an ihrem privaten Spiel');
|
|
select throws_ok($$update public.games set merged_into_id = 'f6000000-0000-0000-0000-000000000001' where id = 'f6000000-0000-0000-0000-0000000000a1'$$,
|
|
'42501', null, 'KAT-05 Kriterium 9: Anna setzt merged_into_id nicht');
|
|
select throws_ok($$update public.games set manual_fields = array['title'] where id = 'f6000000-0000-0000-0000-0000000000a1'$$,
|
|
'42501', null, 'KAT-05 Kriterium 9: Anna setzt manual_fields nicht');
|
|
select throws_ok($$update public.games set catalog_source_id = (select id from public.catalog_sources limit 1), source_ref = 'x'
|
|
where id = 'f6000000-0000-0000-0000-0000000000a1'$$, '42501', null, 'KAT-05 Kriterium 9: Anna setzt catalog_source_id nicht');
|
|
select throws_ok($$update public.games set source_url = 'https://example.org' where id = 'f6000000-0000-0000-0000-0000000000a1'$$,
|
|
'42501', null, 'KAT-05 Kriterium 9: Anna setzt source_url nicht');
|
|
select throws_ok($$update public.games set id = gen_random_uuid() where id = 'f6000000-0000-0000-0000-0000000000a1'$$,
|
|
'42501', null, 'KAT-05 Kriterium 9: Anna ändert die id nicht');
|
|
select throws_ok($$insert into public.games (id, title, visibility, owner_id)
|
|
values (gen_random_uuid(), 'Mit eigener ID', 'private', '11111111-1111-1111-1111-111111111111')$$, '42501', null,
|
|
'KAT-05 Kriterium 9: Anna wählt beim Anlegen keine eigene id');
|
|
select throws_ok($$insert into public.games (title, visibility, owner_id, hidden_at)
|
|
values ('Versteckt', 'private', '11111111-1111-1111-1111-111111111111', now())$$, '42501', null,
|
|
'Anna legt kein Spiel mit hidden_at an');
|
|
select lives_ok($$insert into public.games (title, description, year, visibility, owner_id)
|
|
values ('Annas Neues', 'Text', 2022, 'private', '11111111-1111-1111-1111-111111111111')$$, 'Anna legt ein privates Spiel an');
|
|
select is((select manual_fields from public.games where title = 'Annas Neues'), '{}'::text[],
|
|
'Annas privates Spiel wird nicht als manuell gepflegt gekennzeichnet');
|
|
select is_empty($$update public.games set title = 'Gehackt' where id = 'f6000000-0000-0000-0000-000000000001' returning 1$$,
|
|
'KAT-05 Kriterium 9: Anna ändert keine Katalogspiele');
|
|
select lives_ok($$insert into public.game_publishers (game_id, publisher_id)
|
|
values ('f6000000-0000-0000-0000-0000000000a1', 'f6000000-0000-0000-0000-0000000000c1')$$,
|
|
'Anna ordnet ihrem privaten Spiel einen Verlag zu (Trigger stört nicht)');
|
|
select lives_ok($$insert into public.game_genres (game_id, genre_id)
|
|
values ('f6000000-0000-0000-0000-0000000000a1', 'f6000000-0000-0000-0000-0000000000e1')$$,
|
|
'Anna ordnet ihrem privaten Spiel ein Genre zu (Trigger stört nicht)');
|
|
select lives_ok($$delete from public.game_genres where game_id = 'f6000000-0000-0000-0000-0000000000a1'$$,
|
|
'Anna löscht eine Zuordnung ihres privaten Spiels');
|
|
select throws_ok($$insert into public.game_publishers (game_id, publisher_id)
|
|
values ('f6000000-0000-0000-0000-000000000001', 'f6000000-0000-0000-0000-0000000000c1')$$, '42501', null,
|
|
'KAT-05 Kriterium 9: Anna ordnet einem Katalogspiel keinen Verlag zu');
|
|
select is_empty($$delete from public.game_publishers where game_id = 'f6000000-0000-0000-0000-000000000001' returning 1$$,
|
|
'KAT-05 Kriterium 9: Anna löscht keine Verlagszuordnung eines Katalogspiels');
|
|
select throws_ok($$insert into public.publishers (name) values ('Annas Verlag')$$, '42501', null, 'Anna legt keine Verlage an');
|
|
select throws_ok($$insert into public.catalog_sources (key, name) values ('anna', 'Anna')$$, '42501', null, 'Anna legt keine Quellen an');
|
|
select is_empty($$update public.catalog_sources set texts_allowed = true returning 1$$, 'Anna ändert keine Quellen');
|
|
select is_empty($$delete from public.catalog_sources returning 1$$, 'Anna löscht keine Quellen');
|
|
select is_empty($$update public.publishers set name = 'Gehackt' returning 1$$, 'Anna ändert keine Verlage');
|
|
select throws_ok($$insert into public.catalog_games (id, title) values (gen_random_uuid(), 'Gehackt')$$, '55000', null,
|
|
'Die View catalog_games ist für Nutzer nicht schreibbar (nicht aktualisierbar)');
|
|
select is((select count(*) from public.catalog_games where id = 'f6000000-0000-0000-0000-0000000000a1'), 0::bigint,
|
|
'catalog_games zeigt auch Annas eigenes privates Spiel nicht');
|
|
reset role;
|
|
|
|
-- Gefälschte Admin-Rolle in user_metadata zählt nicht
|
|
select set_config('request.jwt.claims',
|
|
'{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated","user_metadata":{"role":"admin"}}', true);
|
|
set local role authenticated;
|
|
select throws_ok($$insert into public.publishers (name) values ('Admin-Verlag')$$, '42501', null,
|
|
'KAT-05 Kriterium 9: user_metadata.role = admin gibt keine Rechte');
|
|
select is_empty($$update public.games set title = 'Gehackt' where id = 'f6000000-0000-0000-0000-000000000001' returning 1$$,
|
|
'KAT-05 Kriterium 9: user_metadata.role = admin ändert keine Katalogspiele');
|
|
select throws_ok($$insert into public.catalog_sources (key, name) values ('admin', 'Admin')$$, '42501', null,
|
|
'KAT-05 Kriterium 9: user_metadata.role = admin legt keine Quellen an');
|
|
select is_empty($$update public.catalog_sources set texts_allowed = true returning 1$$,
|
|
'KAT-05 Kriterium 9: user_metadata.role = admin ändert keine Quellen');
|
|
select throws_ok($$insert into public.game_publishers (game_id, publisher_id)
|
|
values ('f6000000-0000-0000-0000-000000000002', 'f6000000-0000-0000-0000-0000000000c1')$$, '42501', null,
|
|
'KAT-05 Kriterium 9: user_metadata.role = admin ordnet Katalogspielen keinen Verlag zu');
|
|
reset role;
|
|
|
|
-- Ben
|
|
select set_config('request.jwt.claims', '{"sub":"22222222-2222-2222-2222-222222222222","role":"authenticated"}', true);
|
|
set local role authenticated;
|
|
select is((select count(*) from public.game_publishers where game_id = 'f6000000-0000-0000-0000-0000000000a1'), 0::bigint,
|
|
'Ben sieht keine Verlagszuordnungen von Annas privatem Spiel');
|
|
select throws_ok($$insert into public.game_publishers (game_id, publisher_id)
|
|
values ('f6000000-0000-0000-0000-0000000000a1', 'f6000000-0000-0000-0000-0000000000c1')$$, '42501', null,
|
|
'Ben ordnet Annas privatem Spiel keinen Verlag zu');
|
|
reset role;
|
|
|
|
-- Konto löschen erfasst die privaten Spiele samt Zuordnungen (Kaskaden)
|
|
select lives_ok($$delete from auth.users where id = '11111111-1111-1111-1111-111111111111'$$,
|
|
'Das Konto von Anna lässt sich löschen');
|
|
select is((select count(*) from public.games where owner_id = '11111111-1111-1111-1111-111111111111'), 0::bigint,
|
|
'Annas private Spiele sind mit dem Konto gelöscht');
|
|
select is((select count(*) from public.game_publishers where game_id = 'f6000000-0000-0000-0000-0000000000a1'), 0::bigint,
|
|
'Annas Zuordnungen sind mit dem Konto gelöscht');
|
|
select is((select count(*) from public.games where id = 'f6000000-0000-0000-0000-000000000001'), 1::bigint,
|
|
'Katalogspiele bleiben beim Löschen eines Kontos erhalten');
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- Runbook docs/katalog-pflege.md, Abschnitt 3 und 4 (SQL-Bausteine unverändert, ohne begin/commit)
|
|
-- ---------------------------------------------------------------------------------------------
|
|
insert into public.genres (name_de, name_en) values ('Strategiespiel', 'Strategy game') on conflict do nothing;
|
|
|
|
-- Abschnitt 3: Spiel finden (vorab nachschlagen; das Spiel gibt es hier noch nicht)
|
|
select is((select count(*) from (
|
|
select id, title, year, hidden_at from public.games
|
|
where visibility = 'catalog' and lower(title) = lower('Beispielspiel Runbook')) f), 0::bigint, 'Runbook 3: Suche vor dem Anlegen findet nichts');
|
|
|
|
-- Abschnitt 3: Neues Katalogspiel mit Verlag und Genre
|
|
insert into public.publishers (name) values ('Beispielverlag')
|
|
on conflict ((lower(name))) do nothing;
|
|
|
|
with neu as (
|
|
insert into public.games (id, title, year, min_players, max_players, min_minutes, max_minutes, min_age,
|
|
description, description_locale, visibility)
|
|
values ('7a1b2c3d-0000-4000-8000-000000000001', 'Beispielspiel Runbook', 2020, 2, 4, 30, 60, 8, 'Eigener Kurztext.', 'de', 'catalog')
|
|
returning id
|
|
), verlag as (
|
|
insert into public.game_publishers (game_id, publisher_id)
|
|
select neu.id, p.id from neu, public.publishers p where lower(p.name) = lower('Beispielverlag')
|
|
)
|
|
insert into public.game_genres (game_id, genre_id)
|
|
select neu.id, g.id from neu, public.genres g where g.name_de = 'Strategiespiel';
|
|
|
|
select is((select count(*) from public.games where id = '7a1b2c3d-0000-4000-8000-000000000001' and visibility = 'catalog' and owner_id is null), 1::bigint,
|
|
'KAT-05 Kriterium 8: Runbook 3, Spiel anlegen');
|
|
select is((select manual_fields from public.games where title = 'Beispielspiel Runbook'),
|
|
array['description', 'description_locale', 'genres', 'max_minutes', 'max_players', 'min_age', 'min_minutes', 'min_players',
|
|
'publishers', 'title', 'year'],
|
|
'KAT-05 Kriterium 8: Runbook 3, das neue Spiel ist als manuell gepflegt gekennzeichnet');
|
|
select is((select count(*) from public.game_publishers gp join public.games g on g.id = gp.game_id
|
|
where g.title = 'Beispielspiel Runbook'), 1::bigint, 'Runbook 3: Verlag zugeordnet');
|
|
select is((select count(*) from public.game_genres gg join public.games g on g.id = gg.game_id
|
|
where g.title = 'Beispielspiel Runbook'), 1::bigint, 'Runbook 3: Genre zugeordnet');
|
|
|
|
select is((select count(*) from (
|
|
select id, title, year, hidden_at from public.games
|
|
where visibility = 'catalog' and lower(title) = lower('Beispielspiel Runbook')) f), 1::bigint, 'Runbook 3: Suche findet das Spiel');
|
|
|
|
-- Abschnitt 3: Fakten ändern
|
|
update public.games set min_age = 10, max_minutes = 90
|
|
where id = '7a1b2c3d-0000-4000-8000-000000000001' and visibility = 'catalog';
|
|
select is((select min_age from public.games where title = 'Beispielspiel Runbook'), 10::smallint, 'Runbook 3: Fakten ändern');
|
|
|
|
-- Abschnitt 3: Markierung bewusst entfernen
|
|
update public.games set manual_fields = array_remove(manual_fields, 'min_age')
|
|
where id = '7a1b2c3d-0000-4000-8000-000000000001' and visibility = 'catalog';
|
|
select is((select 'min_age' = any(manual_fields) from public.games where title = 'Beispielspiel Runbook'), false,
|
|
'Runbook 3: Markierung entfernen');
|
|
|
|
-- Abschnitt 4: ausblenden
|
|
update public.games set hidden_at = now()
|
|
where id = '7a1b2c3d-0000-4000-8000-000000000001' and visibility = 'catalog' and hidden_at is null;
|
|
select is((select count(*) from public.catalog_games where title = 'Beispielspiel Runbook'), 0::bigint,
|
|
'KAT-05 Kriterium 8: Runbook 4, ausgeblendet');
|
|
-- Abschnitt 4: wieder einblenden
|
|
update public.games set hidden_at = null
|
|
where id = '7a1b2c3d-0000-4000-8000-000000000001' and visibility = 'catalog';
|
|
select is((select count(*) from public.catalog_games where title = 'Beispielspiel Runbook'), 1::bigint,
|
|
'KAT-05 Kriterium 8: Runbook 4, wieder eingeblendet');
|
|
|
|
-- ---------------------------------------------------------------------------------------------
|
|
-- Startbestand-Werkzeug mit fiktiven Testdaten (KAT-05 Kriterium 7 und 10)
|
|
-- Vorab: ein im Studio angelegter Begriff und Verlag mit gleichem Namen, aber anderer ID bzw. Schreibweise.
|
|
-- ---------------------------------------------------------------------------------------------
|
|
insert into public.genres (id, name_de, name_en) values
|
|
('f6000000-0000-0000-0000-0000000000e5', 'Testgenre A', 'Test genre A (Studio)');
|
|
insert into public.publishers (id, name) values ('f6000000-0000-0000-0000-0000000000c2', 'TestVerlag');
|
|
|
|
\ir ../support/vocabulary_fixture.inc
|
|
\ir ../support/startbestand_fixture.inc
|
|
|
|
select is((select count(*) from public.genres where name_de = 'Testgenre A'), 1::bigint,
|
|
'Vokabular: Ein im Studio angelegter Begriff mit gleichem Namen löst keinen Fehler aus');
|
|
select is((select id from public.genres where name_de = 'Testgenre A'), 'f6000000-0000-0000-0000-0000000000e5'::uuid,
|
|
'Vokabular: Der vorhandene Begriff gewinnt');
|
|
select is((select count(*) from public.publishers where lower(name) = 'testverlag'), 1::bigint,
|
|
'Startbestand: Ein Verlag in anderer Schreibweise wird wiederverwendet');
|
|
select is((select count(*) from public.games where title like 'Testspiel %'), 3::bigint, 'KAT-05 Kriterium 7: drei Testspiele eingespielt');
|
|
select is((select id from public.games where title = 'Testspiel A'),
|
|
extensions.uuid_generate_v5('6f1d9a52-3c4e-4b8a-9f27-5d0e8c71a3b4', 'game:testspiel-a'),
|
|
'KAT-05 Kriterium 7: gleicher key gibt die gleiche UUID wie uuid_generate_v5 in PostgreSQL');
|
|
select is((select id from public.games where title like 'Testspiel C%'),
|
|
extensions.uuid_generate_v5('6f1d9a52-3c4e-4b8a-9f27-5d0e8c71a3b4', 'game:testspiel-c'),
|
|
'Die ID ist unabhängig von Sonderzeichen im Titel');
|
|
select results_eq($$select title, year, min_players, max_players, min_minutes, max_minutes, min_age, description_locale, visibility
|
|
from public.games where title = 'Testspiel A'$$,
|
|
$$values ('Testspiel A'::text, 2020::smallint, 2::smallint, 4::smallint, 30::smallint, 60::smallint, 8::smallint, 'de'::text, 'catalog'::text)$$,
|
|
'KAT-05 Kriterium 10: Fakten des Testspiels A');
|
|
select is_empty($$select 1 from public.games where title like 'Testspiel %'
|
|
and (image_path is not null or catalog_source_id is not null or bgg_id is not null or source_ref is not null or owner_id is not null)$$,
|
|
'KAT-05 Kriterium 10: kein Cover, keine Quelle, keine BGG-Kennung (SB6)');
|
|
select is((select manual_fields from public.games where title = 'Testspiel A'),
|
|
array['description', 'description_locale', 'genres', 'max_minutes', 'max_players', 'mechanics', 'min_age', 'min_minutes',
|
|
'min_players', 'publishers', 'title', 'year'],
|
|
'KAT-05 Kriterium 1: Der Startbestand gilt als manuell gepflegt (SB8)');
|
|
select is((select hidden_at is not null from public.games where title like 'Testspiel C%'), true, 'hidden = ja blendet das Spiel aus');
|
|
select is((select hidden_at is null from public.games where title = 'Testspiel A'), true, 'hidden leer lässt hidden_at unberührt');
|
|
select is((select count(*) from public.game_publishers gp join public.games g on g.id = gp.game_id where g.title = 'Testspiel B'), 2::bigint,
|
|
'Testspiel B hat zwei Verlage');
|
|
select is((select count(*) from public.game_genres gg join public.games g on g.id = gg.game_id where g.title = 'Testspiel A'), 2::bigint,
|
|
'Testspiel A hat zwei Genres');
|
|
|
|
-- Abgleich der Zuordnungen und "die Datei gewinnt" vor dem Go-live
|
|
update public.games set title = 'Geändert' where title = 'Testspiel A';
|
|
insert into public.game_genres (game_id, genre_id)
|
|
select g.id, t.id from public.games g, public.genres t where g.title = 'Testspiel B' and t.name_de = 'Testgenre B';
|
|
delete from public.game_genres where game_id = (select id from public.games where title = 'Geändert')
|
|
and genre_id = (select id from public.genres where name_de = 'Testgenre B');
|
|
\ir ../support/startbestand_fixture.inc
|
|
select is((select count(*) from public.games where title = 'Testspiel A'), 1::bigint, 'Zweites Einspielen stellt geänderte Fakten wieder her');
|
|
select results_eq($$select t.name_de from public.game_genres gg join public.games g on g.id = gg.game_id
|
|
join public.genres t on t.id = gg.genre_id where g.title = 'Testspiel A' order by 1$$,
|
|
$$values ('Testgenre A'::text), ('Testgenre B'::text)$$, 'Zweites Einspielen ergänzt fehlende Zuordnungen');
|
|
select results_eq($$select t.name_de from public.game_genres gg join public.games g on g.id = gg.game_id
|
|
join public.genres t on t.id = gg.genre_id where g.title = 'Testspiel B' order by 1$$,
|
|
$$values ('Testgenre A'::text)$$, 'Zweites Einspielen entfernt nicht mehr gewünschte Zuordnungen');
|
|
|
|
-- Idempotenz: ein weiteres Einspielen ändert nichts (ctid statt updated_at, weil now() in der Transaktion konstant ist)
|
|
create temp table snap_rows on commit drop as
|
|
select 'g' as t, id::text as a, null::text as b, ctid::text as c, updated_at as u from public.games where title like 'Testspiel %'
|
|
union all select 'gg', game_id::text, genre_id::text, ctid::text, updated_at from public.game_genres
|
|
where game_id in (select id from public.games where title like 'Testspiel %')
|
|
union all select 'gm', game_id::text, mechanic_id::text, ctid::text, updated_at from public.game_mechanics
|
|
where game_id in (select id from public.games where title like 'Testspiel %')
|
|
union all select 'gp', game_id::text, publisher_id::text, ctid::text, updated_at from public.game_publishers
|
|
where game_id in (select id from public.games where title like 'Testspiel %')
|
|
union all select 'p', id::text, null, ctid::text, updated_at from public.publishers where lower(name) in ('testverlag', 'zweiter testverlag')
|
|
union all select 'v', id::text, null, ctid::text, updated_at from public.genres where name_de like 'Testgenre %'
|
|
union all select 'm', id::text, null, ctid::text, updated_at from public.mechanics where name_de like 'Testmechanik %';
|
|
\ir ../support/vocabulary_fixture.inc
|
|
\ir ../support/startbestand_fixture.inc
|
|
select is((select count(*) from snap_rows), 21::bigint, 'Schnappschuss enthält die erwarteten Zeilen (3 Spiele, 12 Zuordnungen, 2 Verlage, 2 Genres, 2 Mechaniken)');
|
|
select is_empty($$select * from snap_rows s where not exists (select 1 from (
|
|
select 'g' as t, id::text as a, null::text as b, ctid::text as c, updated_at as u from public.games
|
|
union all select 'gg', game_id::text, genre_id::text, ctid::text, updated_at from public.game_genres
|
|
union all select 'gm', game_id::text, mechanic_id::text, ctid::text, updated_at from public.game_mechanics
|
|
union all select 'gp', game_id::text, publisher_id::text, ctid::text, updated_at from public.game_publishers
|
|
union all select 'p', id::text, null, ctid::text, updated_at from public.publishers
|
|
union all select 'v', id::text, null, ctid::text, updated_at from public.genres
|
|
union all select 'm', id::text, null, ctid::text, updated_at from public.mechanics) n
|
|
where n.t = s.t and n.a = s.a and n.b is not distinct from s.b and n.c = s.c and n.u = s.u)$$,
|
|
'KAT-05 Kriterium 7: Erneutes Einspielen ändert keine Zeile (Spiele, Zuordnungen, Verlage, Begriffe)');
|
|
|
|
-- Prüfabfrage checks.sql (Fixture-Variante derselben Abfrage)
|
|
\ir ../support/checks_fixture.inc
|
|
select results_eq($$select rule, ist::int, status from checks_result order by rule$$,
|
|
$$values ('Info'::text, 1, 'Information'::text), ('Info-Alt', 1, 'Information'), ('SB0', 3, 'Information'), ('SB1', 2, 'Verstoß'),
|
|
('SB2', 0, 'ok'), ('SB4', 0, 'ok'), ('SB6', 0, 'ok')$$,
|
|
'KAT-05 Kriterium 10: checks.sql meldet für die Testspiele keinen Verstoß bei SB2, SB4, SB6 (SB1 liegt lokal erwartbar unter 180)');
|
|
update public.games set min_minutes = null where title = 'Testspiel A';
|
|
update public.games set bgg_id = 4711 where title = 'Testspiel B';
|
|
update public.games set description = null, description_locale = null where title like 'Testspiel C%';
|
|
select results_eq($$select rule, ist::int, status from checks_result where rule in ('SB2', 'SB4', 'SB6') order by rule$$,
|
|
$$values ('SB2'::text, 1, 'Verstoß'::text), ('SB4', 1, 'Verstoß'), ('SB6', 1, 'Verstoß')$$,
|
|
'KAT-05 Kriterium 10: checks.sql meldet fehlende Dauer, gesetzte BGG-Kennung und fehlenden Kurztext');
|
|
insert into public.games (title, visibility) values ('Kein Startbestand', 'catalog');
|
|
select is((select ist::int from checks_result where rule = 'SB2'), 1,
|
|
'KAT-05 Kriterium 10: Ein Spiel außerhalb des Startbestands wird von checks.sql ignoriert');
|
|
|
|
select * from finish();
|
|
rollback;
|