129 lines
5.4 KiB
PL/PgSQL
129 lines
5.4 KiB
PL/PgSQL
-- Migration p2-001: codings fuer die fuenf Phase-2-Schichten oeffnen
|
|
--
|
|
-- psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f migrations/p2-001-codings-mehrschichtig.sql
|
|
--
|
|
-- Setzt das Ingest-Schema voraus (swp-01-ingest/schema.sql). Aendert an
|
|
-- raw_items nichts und loescht keine Zeile.
|
|
--
|
|
-- Zwei Dinge im Bestand verhindern Phase 2 vollstaendig:
|
|
--
|
|
-- unique (raw_item_id, coder_version) - laesst genau eine Zeile je Item
|
|
-- und Version zu, Phase 2 braucht fuenf
|
|
-- check ebene in ('keine','quad',...) - kennt nur die CAMEO-Ebenen aus
|
|
-- Phase 3, kein 'dublette'
|
|
--
|
|
-- Beides wird ersetzt, nicht ergaenzt. `ebene` traegt damit zwei Bedeutungen:
|
|
-- fuer Phase 3 die erreichte CAMEO-Ebene, fuer Phase 2 den Modulnamen.
|
|
-- Auseinander zu halten sind sie ueber coder_version (Phase 2: 'p2-...').
|
|
-- Eine eigene Spalte waere sauberer, kostet aber einen Eingriff in Phase 3,
|
|
-- die es noch nicht gibt - wenn, dann dort und dann.
|
|
|
|
begin;
|
|
|
|
alter table codings drop constraint if exists codings_raw_item_id_coder_version_key;
|
|
alter table codings drop constraint if exists codings_ebene_check;
|
|
|
|
alter table codings
|
|
add constraint codings_uniq unique (raw_item_id, coder_version, ebene);
|
|
|
|
alter table codings
|
|
add constraint codings_ebene_check check (ebene in (
|
|
-- Phase 3, CAMEO
|
|
'keine', 'quad', 'root', 'base', 'event',
|
|
-- Phase 2, Anreicherung
|
|
'dublette', 'akteure', 'geo', 'tonalitaet', 'embedding'
|
|
));
|
|
|
|
comment on column codings.ebene is
|
|
'Phase 3: erreichte CAMEO-Ebene. Phase 2: Modulname. Trennung ueber coder_version';
|
|
|
|
create index if not exists codings_ebene_idx on codings (ebene, kodiert_am);
|
|
create index if not exists codings_nutzlast_idx on codings using gin (nutzlast);
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- Keine Werkausschnitte in der Nutzlast.
|
|
--
|
|
-- codings ist Kandidat fuer den OpenData-Dump. Offsets und Codes duerfen
|
|
-- hinaus, Text nicht - ausser Eigennamen (Akteurs- und Ortsformen), die kurz
|
|
-- sind. 100 Zeichen ist die Grenze, unter der kein Teaser wiederherstellbar
|
|
-- ist und ueber der kein Organisationsname liegen muss.
|
|
--
|
|
-- Als Constraint, nicht als Vorsatz: Abnahmekriterium aus Abschnitt 10.
|
|
-- --------------------------------------------------------------------------
|
|
create or replace function p2_nutzlast_ohne_text(nutzlast jsonb, grenze integer default 100)
|
|
returns boolean language sql immutable parallel safe as $$
|
|
select not exists (
|
|
select 1
|
|
from jsonb_path_query(nutzlast, '$.**') as wert
|
|
where jsonb_typeof(wert) = 'string'
|
|
and length(wert #>> '{}') > grenze
|
|
)
|
|
$$;
|
|
|
|
comment on function p2_nutzlast_ohne_text is
|
|
'Wahr, wenn keine Zeichenkette in der Nutzlast laenger als grenze ist';
|
|
|
|
alter table codings
|
|
add constraint codings_kein_volltext
|
|
check (p2_nutzlast_ohne_text(nutzlast));
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- Was hinter einem coder_version-Hash steht. Ohne diese Tabelle ist die
|
|
-- Versionierung wertlos: der Hash sagt dann nur, dass sich etwas geaendert
|
|
-- hat, nicht was.
|
|
-- --------------------------------------------------------------------------
|
|
create table if not exists coder_versionen (
|
|
version text primary key, -- 'p2-2026-09-07-a3f1b2c4'
|
|
komponenten jsonb not null, -- Regelwerk, Lexika, Modelle, Quantisierung
|
|
erstellt_am timestamptz not null default now(),
|
|
notiz text
|
|
);
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- Betriebsprotokoll, analog poll_laeufe. Anders als dort ist die Einheit
|
|
-- nicht die Quelle, sondern Slot und Modul: Phase 2 arbeitet itemweise ueber
|
|
-- alle Quellen hinweg.
|
|
-- --------------------------------------------------------------------------
|
|
create table if not exists p2_laeufe (
|
|
id bigserial primary key,
|
|
slot timestamptz, -- null im Backfill ueber den Gesamtbestand
|
|
modul text not null,
|
|
coder_version text not null,
|
|
begonnen_am timestamptz not null default now(),
|
|
dauer_ms integer,
|
|
items_gesehen integer not null default 0,
|
|
items_kodiert integer not null default 0,
|
|
items_fehler integer not null default 0,
|
|
fehler text
|
|
);
|
|
|
|
create index if not exists p2_laeufe_slot_idx on p2_laeufe (slot desc nulls last);
|
|
create index if not exists p2_laeufe_modul_idx on p2_laeufe (modul, begonnen_am desc);
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- Arbeitsvorrat je Schicht. `unkodiert(v)` aus dem Ingest-Schema fragt nur,
|
|
-- ob es *irgendeine* Zeile gibt, und liefert nach dem ersten Modul nichts
|
|
-- mehr zurueck.
|
|
--
|
|
-- select * from unkodiert('p2-2026-09-07-a3f1b2c4', 'akteure') limit 500;
|
|
-- --------------------------------------------------------------------------
|
|
create or replace function unkodiert(v text, schicht text)
|
|
returns setof raw_items language sql stable as $$
|
|
select r.* from raw_items r
|
|
where not exists (
|
|
select 1 from codings c
|
|
where c.raw_item_id = r.id
|
|
and c.coder_version = v
|
|
and c.ebene = schicht
|
|
)
|
|
order by r.id
|
|
$$;
|
|
|
|
-- Verwerfbarkeit (Invariante 4): der gesamte Phase-2-Bestand geht so weg.
|
|
-- delete from codings where coder_version like 'p2-%';
|
|
|
|
insert into schema_migrationen (name) values ('p2-001-codings-mehrschichtig')
|
|
on conflict (name) do nothing;
|
|
|
|
commit;
|