source: trunk/import/alkis-functions.sql @ 421

Revision 421, 29.9 KB checked in by frank.jaeger, 5 years ago (diff)

Trigger-Function "delete_feature_kill()" angepasst für Sonderfall "Modellschwäche ausgleichen" aus ibR-ALKIS.
Bereits vorhandene Objekte werden nochmals mit "replace" ausgeliefert. Dies kann (muss) ignoriert werden.

Line 
1-- alkis-functions.sql - Trigger-Funktionen fÃŒr die FortfÃŒhrung der
2--                       alkis_beziehungen aus EintrÀgen der delete-Tabelle
3
4-- 2013-07-10: Erweiterung zur Verarbeitung der Replace-SÀtze in ALKIS-Beziehungen
5
6-- 2013-12-10:  In der Function "update_fields_beziehungen" den Fall behandeln, dass ein Objekt einer
7--             neuen Beziehung in keiner Tabelle gefunden wird.
8--             Wenn ein einzelnes Objekt fehlt, soll dies keine Auswirkungen auf andere Objekte haben.
9--             FÃŒllen von "zu_typename" auskommentiert.
10
11-- 2014-01-31: Deaktivieren von "update_fields_beziehungen",
12--             statt dessen verwenden der "import_id" um alte Relationen zu identifizieren und zu löschen.
13
14-- 2014-08-27: Angleichung des Datenbank-Schema an die NorBIT-Version.
15--             Die Trigger-Function "delete_feature_kill()" arbeitet falsch, wenn "gml_id" als "character varying" angelegt ist.
16--             Das Format war bisher charachter(16).
17--             Zugriff auf die Spalte gml_id umgestellt von "=" auf "like" um den individuellen Timestamp zu ignorieren.
18
19-- 2014-09-04  Trigger-Funktion "delete_feature_kill()" angepasst: keine Tabelle "alkis_beziehungen" mehr.
20
21-- 2014-09-11  Functions auskommentiert oder gelöscht, die "alkis_beziehungen" benötigen:
22--               "alkis_mviews()", delete_feature_kill_vers07(), alkis_beziehung_inserted()
23--             Trigger-Function "delete_feature_hist" durch aktuelle Version aus OSGeo4W ersetzt.
24
25-- 2014-09-19  FJ: Korrektur "delete_feature_hist()":
26--             Ausgehend von Version: https://raw.githubusercontent.com/norBIT/alkisimport/master/alkis-functions.sql
27--             Bei der Erstabagabe mit Vollhistorie (ibR) werden mehrere ZwischenstÀnde von Objekten eingelesen.
28--             Einige davon wurden bereits mit "endet" ausgeliefert (in replace-SÀtzen).
29--             Wenn der Trigger ausgelöst wird (in einem zweiten Durchlauf von PostNAS) kann es jeweils
30--             mehrerer VorgÀnger- und Nachfolger-Objekte mit und ohne "endet IS NULL" geben.
31
32-- 2014-09-23  FJ: Korrektur "delete_feature_kill()":
33--             Die neue Hist-Version vereinfachen (endet nicht benötigt) und zum Löschen umbauen.
34
35-- 2019-07-26  FJ: "delete_feature_kill" ÃŒberarbeitet.
36--             Der Sonderfall "ModellschwÀche ausgleichen" im ibR-ALKIS wird behandelt: replace fÃŒr bereits vorhandene Objekte ignorieren.
37--             16stellig Feature-IDs werden nicht mehr erwartet.
38
39-- Table/View/Sequence löschen, wenn vorhanden
40CREATE OR REPLACE FUNCTION alkis_dropobject(t TEXT) RETURNS varchar AS $$
41DECLARE
42        c RECORD;
43        s varchar;
44        r varchar;
45        d varchar;
46        i integer;
47        tn varchar;
48BEGIN
49        r := '';
50        d := '';
51
52        -- drop objects
53        FOR c IN SELECT relkind,relname
54                FROM pg_class
55                JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
56                WHERE pg_namespace.nspname='public' AND pg_class.relname=t
57                ORDER BY relkind
58        LOOP
59                IF c.relkind = 'v' THEN
60                        r := r || d || 'Sicht ' || c.relname || ' gelöscht.';
61                        EXECUTE 'DROP VIEW ' || c.relname || ' CASCADE';
62                ELSIF c.relkind = 'r' THEN
63                        r := r || d || 'Tabelle ' || c.relname || ' gelöscht.';
64                        EXECUTE 'DROP TABLE ' || c.relname || ' CASCADE';
65                ELSIF c.relkind = 'S' THEN
66                        r := r || d || 'Sequenz ' || c.relname || ' gelöscht.';
67                        EXECUTE 'DROP SEQUENCE ' || c.relname;
68                ELSIF c.relkind <> 'i' THEN
69                        r := r || d || 'Typ ' || c.table_type || '.' || c.table_name || ' unerwartet.';
70                END IF;
71                d := E'\n';
72        END LOOP;
73
74        FOR c IN SELECT indexname FROM pg_indexes WHERE schemaname='public' AND indexname=t
75        LOOP
76                r := r || d || 'Index ' || c.indexname || ' gelöscht.';
77                EXECUTE 'DROP INDEX ' || c.indexname;
78                d := E'\n';
79        END LOOP;
80
81        FOR c IN SELECT proname,proargtypes
82                FROM pg_proc
83                JOIN pg_namespace ON pg_proc.pronamespace=pg_namespace.oid
84                WHERE pg_namespace.nspname='public' AND pg_proc.proname=t
85        LOOP
86                r := r || d || 'Funktion ' || c.proname || ' gelöscht.';
87
88                s := 'DROP FUNCTION ' || c.proname || '(';
89                d := '';
90
91                FOR i IN array_lower(c.proargtypes,1)..array_upper(c.proargtypes,1) LOOP
92                        SELECT typname INTO tn FROM pg_type WHERE oid=c.proargtypes[i];
93                        s := s || d || tn;
94                        d := ',';
95                END LOOP;
96
97                s := s || ')';
98
99                EXECUTE s;
100
101                d := E'\n';
102        END LOOP;
103
104        FOR c IN SELECT relname,conname
105                FROM pg_constraint
106                JOIN pg_class ON pg_constraint.conrelid=pg_constraint.oid
107                JOIN pg_namespace ON pg_constraint.connamespace=pg_namespace.oid
108                WHERE pg_namespace.nspname='public' AND pg_constraint.conname=t
109        LOOP
110                r := r || d || 'Constraint ' || c.conname || ' von ' || c.relname || ' gelöscht.';
111                EXECUTE 'ALTER TABLE ' || c.relname || ' DROP CONSTRAINT ' || c.conname;
112                d := E'\n';
113        END LOOP;
114
115        RETURN r;
116END;
117$$ LANGUAGE plpgsql;
118
119-- Alle ALKIS-Tabellen löschen
120SELECT alkis_dropobject('alkis_drop');
121CREATE FUNCTION alkis_drop() RETURNS varchar AS $$
122DECLARE
123        c RECORD;
124        r VARCHAR;
125        d VARCHAR;
126BEGIN
127        r := '';
128        d := '';
129        -- drop tables & views
130        FOR c IN SELECT table_type,table_name FROM information_schema.tables WHERE table_schema='public' AND ( substr(table_name,1,3) IN ('ax_','ap_','ks_') OR table_name IN ('alkis_beziehungen','delete')) ORDER BY table_type DESC LOOP
131                IF c.table_type = 'VIEW' THEN
132                        r := r || d || 'Sicht ' || c.table_name || ' gelöscht.';
133                        EXECUTE 'DROP VIEW ' || c.table_name || ' CASCADE';
134                ELSIF c.table_type = 'BASE TABLE' THEN
135                        r := r || d || 'Tabelle ' || c.table_name || ' gelöscht.';
136                        EXECUTE 'DROP TABLE ' || c.table_name || ' CASCADE';
137                ELSE
138                        r := r || d || 'Typ ' || c.table_type || '.' || c.table_name || ' unerwartet.';
139                END IF;
140                d := E'\n';
141        END LOOP;
142
143        -- clean geometry_columns
144        DELETE FROM geometry_columns
145                WHERE f_table_schema='public'
146                AND ( substr(f_table_name,1,2) IN ('ax_','ap_','ks_')
147                 OR f_table_name IN ('alkis_beziehungen','delete') );
148
149        RETURN r;
150END;
151$$ LANGUAGE plpgsql;
152
153-- Alle ALKIS-Tabellen leeren
154SELECT alkis_dropobject('alkis_delete');
155CREATE FUNCTION alkis_delete() RETURNS varchar AS $$
156DECLARE
157        c RECORD;
158        r varchar;
159        d varchar;
160BEGIN
161        r := '';
162        d := '';
163
164        -- drop views
165        FOR c IN
166                SELECT table_name
167                FROM information_schema.tables
168                WHERE table_schema='public' AND table_type='BASE TABLE'
169                  AND ( substr(table_name,1,3) IN ('ax_','ap_','ks_')
170                        OR table_name IN ('alkis_beziehungen','delete') )
171        LOOP
172                r := r || d || c.table_name || ' wurde geleert.';
173                EXECUTE 'DELETE FROM '||c.table_name;
174                d := E'\n';
175        END LOOP;
176
177        RETURN r;
178END;
179$$ LANGUAGE plpgsql;
180
181-- Übersicht erzeugen, die alle alkis_beziehungen mit den Typen der beteiligen ALKIS-Objekte versieht
182/*
183SELECT alkis_dropobject('alkis_mviews');
184CREATE FUNCTION alkis_mviews() RETURNS varchar AS $$
185DECLARE
186        sql TEXT;
187        delim TEXT;
188        c RECORD;
189BEGIN
190        SELECT alkis_dropobject('vbeziehungen') INTO sql;
191        SELECT alkis_dropobject('vobjekte') INTO sql;
192
193        delim := '';
194        sql := 'CREATE VIEW vobjekte AS ';
195
196        FOR c IN SELECT table_name FROM information_schema.columns WHERE column_name='gml_id' AND substr(table_name,1,3) IN ('ax_','ap_','ks_') LOOP
197                sql := sql || delim || 'SELECT gml_id,beginnt,''' || c.table_name || ''' AS table_name FROM ' || c.table_name;
198                delim := ' UNION ';
199        END LOOP;
200
201        EXECUTE sql;
202
203        CREATE VIEW vbeziehungen AS
204                SELECT  beziehung_von,(SELECT table_name FROM vobjekte WHERE gml_id=beziehung_von) AS typ_von
205                        ,beziehungsart
206                        ,beziehung_zu,(SELECT table_name FROM vobjekte WHERE gml_id=beziehung_zu) AS typ_zu
207                FROM alkis_beziehungen;
208
209        RETURN 'ALKIS-Views erzeugt.';
210END;
211$$ LANGUAGE plpgsql;
212*/
213
214-- Indizes erzeugen
215SELECT alkis_dropobject('alkis_update_schema');
216CREATE FUNCTION alkis_update_schema() RETURNS varchar AS $$
217DECLARE
218        sql TEXT;
219        c RECORD;
220        i RECORD;
221        n INTEGER;
222BEGIN
223        -- Spalten in delete ergÀnzen
224        SELECT count(*) INTO n FROM information_schema.columns WHERE table_schema='public' AND table_name='delete' AND column_name='ignored';
225        IF n=0 THEN
226                ALTER TABLE "delete" ADD ignored BOOLEAN;
227        END IF;
228
229        SELECT count(*) INTO n FROM information_schema.columns WHERE table_schema='public' AND table_name='delete' AND column_name='context';
230        IF n=0 THEN
231                ALTER TABLE "delete" ADD context VARCHAR;
232        END IF;
233
234        SELECT count(*) INTO n FROM information_schema.columns WHERE table_schema='public' AND table_name='delete' AND column_name='safetoignore';
235        IF n=0 THEN
236                ALTER TABLE "delete" ADD safetoignore VARCHAR;
237        END IF;
238
239        SELECT count(*) INTO n FROM information_schema.columns WHERE table_schema='public' AND table_name='delete' AND column_name='replacedby';
240        IF n=0 THEN
241                ALTER TABLE "delete" ADD replacedBy VARCHAR;
242        END IF;
243
244        -- Spalte identifier ergÀnzen, wo sie fehlt
245        FOR c IN SELECT table_name FROM information_schema.columns a WHERE a.column_name='gml_id'
246                AND     EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='beginnt'    AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
247                AND NOT EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='identifier' AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
248        LOOP
249                EXECUTE 'ALTER TABLE ' || c.table_name || ' ADD identifier character(44)';
250        END LOOP;
251
252        -- Spalte endet ergÀnzen, wo sie fehlt
253        FOR c IN SELECT table_name FROM information_schema.columns a WHERE a.column_name='gml_id'
254                AND     EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='beginnt' AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
255                AND NOT EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='endet'   AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
256        LOOP
257                EXECUTE 'ALTER TABLE ' || c.table_name || ' ADD endet character(20) CHECK (endet>beginnt)';
258        END LOOP;
259
260        -- Lebensdauer-Constraint ergÀnzen
261        FOR c IN SELECT table_name FROM information_schema.columns a WHERE a.column_name='gml_id'
262                AND EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='beginnt' AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
263                AND EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='endet'   AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
264        LOOP
265                SELECT alkis_dropobject(c.table_name||'_lebensdauer');
266                EXECUTE 'ALTER TABLE ' || c.table_name || ' ADD CONSTRAINT ' || c.table_name || '_lebensdauer CHECK (beginnt IS NOT NULL AND endet>beginnt)';
267        END LOOP;
268
269        -- Indizes aktualisieren
270        FOR c IN SELECT table_name FROM information_schema.columns a WHERE a.column_name='gml_id'
271                AND EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='beginnt' AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
272        LOOP
273                -- Vorhandene Indizes droppen (TODO: Löscht auch die SonderfÀlle - entfernen)
274                FOR i IN EXECUTE 'SELECT indexname FROM pg_indexes WHERE NOT indexname LIKE ''%_pk'' AND schemaname=''public'' AND tablename='''||c.table_name||'''' LOOP
275                        EXECUTE 'DROP INDEX ' || i.indexname;
276                END LOOP;
277
278                -- Indizes erzeugen
279                EXECUTE 'CREATE UNIQUE INDEX ' || c.table_name || '_id ON ' || c.table_name || '(gml_id,beginnt)';
280                EXECUTE 'CREATE UNIQUE INDEX ' || c.table_name || '_ident ON ' || c.table_name || '(identifier)';
281                EXECUTE 'CREATE INDEX ' || c.table_name || '_gmlid ON ' || c.table_name || '(gml_id)';
282                EXECUTE 'CREATE INDEX ' || c.table_name || '_beginnt ON ' || c.table_name || '(beginnt)';
283                EXECUTE 'CREATE INDEX ' || c.table_name || '_endet ON ' || c.table_name || '(endet)';
284        END LOOP;
285
286        -- Geometrieindizes aktualisieren
287        FOR c IN SELECT table_name FROM information_schema.columns a WHERE a.column_name='gml_id'
288                AND EXISTS (SELECT * FROM information_schema.columns b WHERE b.column_name='wkb_geometry' AND a.table_catalog=b.table_catalog AND a.table_schema=b.table_schema AND a.table_name=b.table_name)
289        LOOP
290                EXECUTE 'CREATE INDEX ' || c.table_name || '_geom ON ' || c.table_name || ' USING GIST (wkb_geometry)';
291        END LOOP;
292
293        RETURN 'Schema aktualisiert.';
294END;
295$$ LANGUAGE plpgsql;
296
297
298-- Im Trigger 'delete_feature_trigger' muss eine dieser beiden Funktionen
299-- (delete_feature_hist oder delete_feature_kill) verlinkt werden, je nachdem ob nur
300-- aktuelle oder auch historische Objekte in der Datenbank gefÃŒhrt werden sollen.
301
302
303/*      Beschreibung und Umfeld des "delete_feature_trigger":
304        -----------------------------------------------------
305
306Der Konverter "ogr2ogr", in den PostNAS als Eingabe-Modul fÃŒr das NAS-Format eingebettet ist,
307ist vom Wesen her eigentlich ein 1:1-Konverter.
308Üblicherweise liest er ein Eingabe-GIS-Format, analysiert es und erzeugt dann die gleiche Struktur in
309einem Ausgabe-GIS-Format.
310Das ALKIS-Format "NAS" als einmaliger Datenauszug (enthÀlt nur Funktion "insert") könnte in diesem
311Rahmen vielleicht auch ohne Trigger umgesetzt werden.
312
313Viel komplexer umzusetzen sind jedoch solche NAS-Daten, die im Rahmen des NBA-Verfahrens von ALKIS abgegeben werden.
314                NBA  =  Nutzerbezogene Bestandsdaten-Aktualisierung.
315In einem NBA-Verfahren wird eine primÀre Datenquelle (ALKIS) mit einem SekundÀrbestand (PostGIS) synchronisiert.
316Es erfolgt zunÀchst eine Erstabgabe fÌr einen definierten Zeitpunkt.
317SpÀter gibt es Aktualisierungen auf weitere Zeitpunkte. Die dazu Ìbermittelten Differenzdaten enthalten
318nicht nur reine Daten (INSERT) sondern auch Anweisungen zur Änderung der zu frÃŒheren Zeitpunkten ÃŒbermittelten Daten.
319Diese Änderungs-Anweisungen werden innerhalb des Konverters ogr2ogr nicht komplett verarbeitet.
320Er verarbeitet zunÀchst nur die enthaltenen Datenfelder zum Objekt, die er in neue Zeilen in die Objekt-Tabellen einstellt.
321
322Anschließend werden Informationen zum ObjektschlÃŒssel (gml_id) und zur Lebensdauer des Objektes (beginnt) zusammen
323mit der Operation (Kontext = "delete", "update" oder "replace") in die Tabelle "delete" eingetragen.
324Dieser Eintrag in "delete" löst den Trigger aus, der sich dann darum kÌmmert, Löschungen oder
325Änderungen an VorgÀngerversionen vorzunehmen.
326
327Im NBA-Verfahren sind verschiedene "Abgabearten" möglich:
328Die Abgabeart 1000 hat zum Ziel, im SekundÀrbestand jeweils den letzten aktuellen Stand bereit zu stellen.
329Die Abgabeart 3100 hat zum Ziel, im SekundÀrbestand eine komplette Historie bereit zu stellen, die auch alle
330ZwischenstÀnde enthÀlt. Ein nicht mehr gÌltiges Objekt wird dann mit einem Eintrag in "endet" deaktiviert,
331verbleibt aber in der Datenbank. Bei der Abgabeart 3100 sind bereits in der Erstabgabe Aktualisierungs-
332Funktionen (delete, update, replace) enthalten weil mehrere historische Versionen von Objekten geliefert werden.
333
334Eine NBA-Lieferung fÃŒr ein Gebiet ist in mehrere Dateien aufgeteilt, die nacheinander abgearbeitet werden.
335Erst mit der Verarbeitung der letzten Datei einer Lieferung ist die Datenbank wieder konsistent und zeigt den
336Zustand zum neuen Abgabezeitpunkt.
337
338Jede dieser NAS-Dateien wird von PostNAS in mehreren DurchlÀufen vearbeitet.
3391. Im ersten Durchlauf wird die 1:1-Konvertierung der Daten vorgenommen.
340   Die Feldinhalte der NAS-Datei werden in neue Zeilen in die Objekttabellen der Datenbank ÃŒbertragen.
3412. Dann werden in einem weiteren Durchlauf die Operationen "delete", "update" und "replace" verarbeitet.
342   Diese werden von PostNAS in die Tabelle "delete" eingetragen, dies löst den Trigger aus.
343       
344FÃŒr die Arbeitsweise des Triggers bedeutet das:
345
346An dem Zeitpunkt, an dem der Trigger ausgelöst wird, stehen bereits alle Daten zu den Objekten in den Objekt-Tabellen.
347Darin ist aber möglicherweise das Feld "endet" noch nicht gefÌllt.
348
349WÀhrend der Konvertierung der Erstabgabe einer NBA-Abgabe der Abgabeart 3100 können aber Objekte doch schon beendet sein.
350Bei einer Erstabgabe der Abgabeart 3100 können mehrere Generation des selben Objektes vorhanden sein,
351die alle in der gleichen NAS-Datei geliefert wurden.
352Das Feld "endet" ist dann nicht geeignet zu entscheiden, welches die letzte (aktuelle) Version ist.
353
354Es kann vorkommen, dass Zwischenversionen in der Objekt-Tabelle bereits beendet sind weil sie direkt mit ihrem
355Endet-Datum geliefert wurden. Dieses wurde bereits beim ersten Durchlauf von ogr2ogr wie ein normales Datenfeld eingetragen.
356In Beispieldaten wurde analysiert, dass ein bereits beendetes Objekt in einem "insert" kein "endet" mitbringt.
357Dies muss vom Trigger beendet werden, wenn dieser einen replace fÃŒr den Nachfolger bekommt.
358
359Im gleichen Bestand wurden jedoch Nachfolger gefunden die mit einem "replace"-Satz gekommen sind
360und bereits beendet waren, weil sie ihrerseits wieder Nachfolger hatten.
361
362Das jeweils folgende "replace" kann also ein VorgÀnger-Objekt mit oder ohne "endet"-Eintrag vorfinden.
363Es können auch sowohl VorgÀnger- als auch bereits Nachfolger-Versionen eines Objektes vorhanden sein, wenn der Trigger
364ausgelöst wird.
365
366Aufgabe des Triggers ist es, zu einem verÀnderten Objekt jeweils den unmittelbaren VorgÀnger zu ermitteln
367und - falls noch nicht geschehen - den passenden endet-Eintrag nachzutragen.
368Wenn in den Daten kein "endet" mitgeliefert wird, dann wird der Beginn der Folge-Version des Objektes verwendet
369um den VorgÀnger zu beenden.
370
371Wenn ein Objekt bereits mit endet-Datum geliefert wurde, dann wird dies zwar in die Obkjekt-Tabelle eingetragen,
372der endet-Eintrag in dem replace-Satz in der delete-Tabelle, der den Trigger auslöst, ist trotzdem leer.
373Es ist Ìberlegen, ob dies im PostNAS-Programm geÀndert werden sollte.
374
375Aufgrund der KomplexitÀt dieser Mechanismen ist davon auszugehen, dass es Hersteller-spezifische Unterschiede
376gibt und auch Unterschiede zuwischen verschiedenen Versions-StÀnden des selben Herstellers.
377Die Arbeitsweise des Triggers muss daher regelmÀßig ÃŒberprÃŒft werden.
378
379*/
380
381-- Achtung: FÃŒr diese Trigger-Version mÃŒssen die SchlÃŒsselfelder "gml_id" in allen Tabellen
382--          wieder auf 16 Stellen fix gekÃŒrzt werden!
383
384-- Löschsatz verarbeiten (MIT Historie)
385-- context='delete'        => "endet" auf aktuelle Zeit setzen
386-- context='replace'       => "endet" des ersetzten auf "beginnt" des neuen Objekts setzen
387-- context='update'        => "endet" auf ÃŒbergebene Zeit setzen und "anlass" festhalten
388-- Die "gml_id" muss in der Datenbank das Format character(16) haben.
389CREATE OR REPLACE FUNCTION delete_feature_hist() RETURNS TRIGGER AS $$
390DECLARE
391        n INTEGER;
392        vbeginnt TEXT;
393        replgml TEXT;
394        featgml TEXT;
395        s TEXT;
396BEGIN
397        NEW.context := coalesce(lower(NEW.context),'delete');
398
399        IF NEW.anlass IS NULL THEN
400                NEW.anlass := '';
401        END IF;
402        featgml := substr(NEW.featureid, 1, 16); -- gml_id ohne Timestamp
403
404        IF length(NEW.featureid)=32 THEN
405                -- beginnt-Zeit der zu ersetzenden Vorgaenger-Version des Objektes
406                vbeginnt := substr(NEW.featureid, 17, 4) || '-'
407                        || substr(NEW.featureid, 21, 2) || '-'
408                        || substr(NEW.featureid, 23, 2) || 'T'
409                        || substr(NEW.featureid, 26, 2) || ':'
410                        || substr(NEW.featureid, 28, 2) || ':'
411                        || substr(NEW.featureid, 30, 2) || 'Z' ;
412        ELSIF length(NEW.featureid)=16 THEN
413                -- Ältestes nicht gelöschtes Objekt
414                EXECUTE 'SELECT min(beginnt) FROM ' || NEW.typename
415                        || ' WHERE gml_id=''' || featgml || ''''
416                        || ' AND endet IS NULL'
417                        INTO vbeginnt;
418
419                IF vbeginnt IS NULL THEN
420                        RAISE EXCEPTION '%: Keinen Kandidaten zum Löschen gefunden.', NEW.featureid;
421                END IF;
422        ELSE
423                RAISE EXCEPTION '%: Identifikator gescheitert.', NEW.featureid;
424        END IF;
425
426        IF NEW.context='delete' THEN
427                NEW.endet := to_char(CURRENT_TIMESTAMP AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"');
428
429        ELSIF NEW.context='update' THEN
430                IF NEW.endet IS NULL THEN
431                        RAISE EXCEPTION '%: Endedatum nicht gesetzt', NEW.featureid;
432                END IF;
433
434        ELSIF NEW.context='replace' THEN
435                NEW.safetoignore := lower(NEW.safetoignore);
436                replgml := substr(NEW.replacedby, 1, 16); -- ReplcedBy gml_id ohne Timestamp
437                IF NEW.safetoignore IS NULL THEN
438                        RAISE EXCEPTION '%: safeToIgnore nicht gesetzt.', NEW.featureid;
439                ELSIF NEW.safetoignore<>'true' AND NEW.safetoignore<>'false' THEN
440                        RAISE EXCEPTION '%: safeToIgnore ''%'' ungÃŒltig (''true'' oder ''false'' erwartet).', NEW.featureid, NEW.safetoignore;
441                END IF;
442
443                IF length(NEW.replacedby)=32 AND NEW.replacedby<>NEW.featureid THEN
444                        NEW.endet := substr(NEW.replacedby, 17, 4) || '-'
445                                || substr(NEW.replacedby, 21, 2) || '-'
446                                || substr(NEW.replacedby, 23, 2) || 'T'
447                                || substr(NEW.replacedby, 26, 2) || ':'
448                                || substr(NEW.replacedby, 28, 2) || ':'
449                                || substr(NEW.replacedby, 30, 2) || 'Z' ;
450                END IF;
451
452                -- Satz-Paarung VorgÀnger-Nachfolger in der Objekttabelle suchen.
453                -- Der VorgÀnger muss noch beendet werden. Der Nachfolger kann bereits beendet sein.
454                -- Das "beginn" des Nachfolgers anschließend als "endet" des Vorgaengers verwenden.
455                -- Normalfall bei NBA-Aktualisierungslaeufen. v=VorgÀnger, n=Nachfolger.
456                IF NEW.endet IS NULL THEN
457                        EXECUTE 'SELECT min(n.beginnt) FROM ' || NEW.typename || ' n'
458                                || ' JOIN ' || NEW.typename || ' v ON v.ogc_fid<n.ogc_fid'
459                                || ' WHERE v.gml_id=''' || featgml
460                                || ''' AND n.gml_id=''' || replgml
461                                || ''' AND v.endet IS NULL'
462                                INTO NEW.endet;
463                --      RAISE NOTICE 'endet setzen fuer Vorgaenger % ', NEW.endet;
464                END IF;
465
466                -- Satz-Paarung VorgÀnger-Nachfolger in der Objekttabelle suchen.
467                -- Der VorgÀnger ist bereits beendet worden weil "endet" in den Daten gefÃŒllt war.
468                -- Dieser Fall kommt bei der Erstabgabe mit Vollhistorie vor.
469                IF NEW.endet IS NULL THEN
470                        EXECUTE 'SELECT min(n.beginnt) FROM ' || NEW.typename || ' n'
471                                || ' JOIN ' || NEW.typename || ' v ON v.endet=n.beginnt '
472                                || ' WHERE v.gml_id=''' || featgml
473                                || ''' AND n.gml_id=''' || replgml
474                                || ''' AND v.beginnt=''' || vbeginnt || ''''
475                                INTO NEW.endet;
476
477                        IF NOT NEW.endet IS NULL THEN
478                        --      RAISE NOTICE '%: Vorgaenger ist schon endet', NEW.featureid;
479                                NEW.ignored=false;
480                                RETURN NEW;
481                        END IF;
482                END IF;
483
484                IF NEW.endet IS NULL THEN -- "endet" fÃŒr den VorgÀnger konnte nicht ermittelt werden
485                        IF NEW.safetoignore='false' THEN
486                                RAISE EXCEPTION '%: Beginn des ersetzenden Objekts % nicht gefunden.', NEW.featureid, NEW.replacedby;
487                        END IF;
488                        NEW.ignored=true;
489                        RETURN NEW;
490                END IF;
491        ELSE
492                RAISE EXCEPTION '%: UngÃŒltiger Kontext % (''delete'', ''replace'' oder ''update'' erwartet).', NEW.featureid, NEW.context;
493        END IF;
494
495        -- Vorgaenger ALKIS-Objekt nun beenden
496        s := 'UPDATE ' || NEW.typename
497          || ' SET endet=''' || NEW.endet || ''' ,anlass=''' || NEW.anlass || ''''
498          || ' WHERE gml_id=''' || featgml || ''' AND beginnt=''' || vbeginnt || '''' ;
499        EXECUTE s;
500        GET DIAGNOSTICS n = ROW_COUNT;
501        -- RAISE NOTICE 'SQL[%]:%', n, s;
502        IF n<>1 THEN
503                RAISE EXCEPTION '%: % schlug fehl [%]', NEW.featureid, NEW.context, n;
504        END IF;
505
506        NEW.ignored := false;
507        RETURN NEW;
508END;
509$$ LANGUAGE plpgsql;
510
511
512-- Version PostNAS 0.8 ohne "alkis_beziehungen"-Tabelle
513-- Unterschied von "delete_feature_kill" zur Version "delete_feature_hist":
514--  Historisch gewordene Objekte werden nicht auf "endet" gesetzt sondern sofort aus der Datenbank gelöscht.
515
516-- Version von 2014-09-04 bis 2014-09-23
517-- Ohne Tabelle "alkis_beziehungen".
518-- Die gml_id kann in der Datenbenk das Format "character varying" haben (angehÀngte Beginn-Zeit).
519-- Diese Version kennt den NAS-Kontext "update" nicht und kann daher nur die Abgabeart 1000 verarbeiten.
520CREATE OR REPLACE FUNCTION delete_feature_kill_abg1000() RETURNS TRIGGER AS $$
521DECLARE
522        begsql TEXT;
523        aktbeg TEXT;
524        gml_id TEXT;
525BEGIN
526        -- Alte Version, die nur die Abgabeart 1000 (ohne replace) verarbeiten kann.
527        NEW.typename := lower(NEW.typename); -- Objektart=Tabellen-Name
528        NEW.context := lower(NEW.context);   -- Operation 'delete'/'replace'/'update'
529        gml_id      := substr(NEW.featureid, 1, 16); -- ID-Teil der gml_id, ohne Timestamp
530
531        IF NEW.context IS NULL THEN
532                NEW.context := 'delete'; -- default
533        END IF;
534        IF NEW.context='delete' THEN -- Löschen des Objektes
535                EXECUTE 'DELETE FROM ' || NEW.typename || ' WHERE gml_id like ''' || gml_id || '%''';
536                --RAISE NOTICE 'Lösche gml_id % in %', gml_id, NEW.typename;
537        ELSE -- Ersetzen des Objektes (Replace). In der Objekt-Tabelle sind jetzt bereits 2 Objekte vorhanden (alt und neu).
538
539                -- beginnt-Wert des aktuellen Objektes ermitteln
540                -- besser ?   WHERE substring(gml_id,1,16) = ''' || gml_id || '''
541                begsql := 'SELECT max(beginnt) FROM ' || NEW.typename || ' WHERE gml_id like ''' || substr(NEW.replacedBy, 1, 16) || '%'' AND endet IS NULL';
542                EXECUTE begsql INTO aktbeg;
543
544                -- Alte Objekte entfernen
545                EXECUTE 'DELETE FROM ' || NEW.typename || ' WHERE gml_id like ''' || gml_id || '%'' AND beginnt < ''' || aktbeg || '''';
546        END IF;
547
548        NEW.ignored := false;
549        RETURN NEW;
550END;
551$$ LANGUAGE plpgsql;
552
553
554-- "Kill-Trigger"
555-- Historische Objekte werden sofort entfernt und nicht auf "endet" gesetzt.
556-- Dies kann auch Abgabeart 3100 verarbeiten.
557CREATE OR REPLACE FUNCTION delete_feature_kill() RETURNS TRIGGER AS $$
558DECLARE
559        n INTEGER;
560        featgml TEXT;  -- vorderer Teil von featureid: gml_id ohne Zeit
561        vbeginnt TEXT; -- hinterer Teil von featureid: beginnt-Zeit
562        s TEXT;
563BEGIN
564        -- Trigger-Version 2019-07-22 kompatibel zu "NBA-ModellschwÀche ausgleichen" (ibR)
565        NEW.context := coalesce(lower(NEW.context),'delete');
566
567        IF NEW.anlass IS NULL THEN
568                NEW.anlass := ARRAY[]::varchar[];
569        END IF;
570        IF length(NEW.featureid)<>32 THEN
571                RAISE EXCEPTION '%: Identifikator gescheitert. Falsche Laenge.', NEW.featureid;
572        END IF;
573
574        featgml := substr(NEW.featureid, 1, 16);
575
576        -- beginnt-Zeit der zu löschenden VorgÀnger-Version des Objektes
577        vbeginnt := substr(NEW.featureid, 17, 4) || '-'
578        || substr(NEW.featureid, 21, 2) || '-'
579        || substr(NEW.featureid, 23, 2) || 'T'
580        || substr(NEW.featureid, 26, 2) || ':'
581        || substr(NEW.featureid, 28, 2) || ':'
582        || substr(NEW.featureid, 30, 2) || 'Z';
583
584        IF NEW.context='replace' THEN
585                NEW.safetoignore := lower(NEW.safetoignore);
586                IF NEW.safetoignore IS NULL THEN
587                        RAISE EXCEPTION '%: safeToIgnore nicht gesetzt.', NEW.featureid;
588                ELSIF NEW.safetoignore<>'true' AND NEW.safetoignore<>'false' THEN
589                        RAISE EXCEPTION '%: safeToIgnore ''%'' ungÃŒltig (''true'' oder ''false'' erwartet).', NEW.featureid, NEW.safetoignore;
590                END IF;
591
592                IF NEW.replacedby=NEW.featureid THEN
593                --      RAISE NOTICE '%: Replace fuer identisches Objekt ausgelassen. Sonderfall Modellschwaeche.', NEW.featureid;
594                        NEW.ignored := true;
595                --      1. Fall: Die NBA-ModellschwÀche wurde erfolgreich ausgeglichen. Dies Objekt fehlte bisher, es gibt daher keinen VorgÀnger.
596                --      2. Fall: Der Insert wurde zuvor von der Datenbank abgelehnt (Unique-Constraint: gml_id, beginnt). Der Delete wÃŒrde daher die EINZIGE Version löschen, nicht den VorgÀnger.
597                --      safetoignore ist false, trotzdem ignorieren
598                ELSE
599                        -- VorgÀnger-ALKIS-Objekt löschen
600                        s := 'DELETE FROM ' || NEW.typename || ' WHERE gml_id=''' || featgml || ''' AND beginnt=''' || vbeginnt || '''' ;
601                        EXECUTE s;
602                        GET DIAGNOSTICS n = ROW_COUNT;
603                        IF n=1 THEN
604                                NEW.ignored := false;
605                        ELSE
606                                RAISE NOTICE '%: % schlug fehl ignoriert [%]', NEW.featureid, NEW.context, n;
607                                NEW.ignored := true;
608                        END IF;
609                END IF;
610
611        ELSIF NEW.context IN ('delete', 'update') THEN
612
613                -- VorgÀnger-ALKIS-Objekt löschen
614                s := 'DELETE FROM ' || NEW.typename || ' WHERE gml_id=''' || featgml || ''' AND beginnt=''' || vbeginnt || '''' ;
615                EXECUTE s;
616                GET DIAGNOSTICS n = ROW_COUNT;
617                IF n=1 THEN
618                        NEW.ignored := false;
619                ELSE
620                        RAISE NOTICE '%: % schlug fehl ignoriert [%]', NEW.featureid, NEW.context, n;
621                        NEW.ignored := true;
622                END IF;
623
624        ELSE
625                RAISE EXCEPTION '%: UngÃŒltiger Kontext % (''delete'', ''replace'' oder ''update'' erwartet).', NEW.featureid, NEW.context;
626        END IF;
627
628        RETURN NEW;
629END;
630$$ LANGUAGE plpgsql;
631
632
633-- Wenn die Datenbank MIT Historie angelegt wurde, kann nach dem Laden hiermit aufgerÀumt werden.
634CREATE OR REPLACE FUNCTION alkis_delete_all_endet() RETURNS void AS $$
635DECLARE
636        c RECORD;
637BEGIN
638        -- In allen Tabellen die Objekte löschen, die ein Ende-Datum haben
639        FOR c IN
640                SELECT table_name
641                 FROM information_schema.columns a
642                WHERE a.column_name='endet'
643            AND a.is_updatable='YES' -- keine Views, die endet-Spalte haben
644                ORDER BY table_name
645        LOOP
646                EXECUTE 'DELETE FROM ' || c.table_name || ' WHERE NOT endet IS NULL';
647                -- RAISE NOTICE 'Lösche ''endet'' in: %', c.table_name;
648        END LOOP;
649END;
650$$ LANGUAGE plpgsql;
651
652
653-- Wenn die Datenbank ohne Historie gefÃŒhrt wird, ist das Feld "identifier" verzichtbar.
654-- Diese wird nur von der Trigger-Version fÃŒr die Version MIT Historie verwendet.
655-- Es kann aus allen Tabellen entfernt werden.
656CREATE OR REPLACE FUNCTION alkis_drop_all_identifier() RETURNS void AS $$
657DECLARE
658        c RECORD;
659BEGIN
660        FOR c IN
661                SELECT table_name
662                FROM information_schema.columns a
663                WHERE a.column_name='identifier'
664                ORDER BY table_name
665        LOOP
666                EXECUTE 'ALTER TABLE ' || c.table_name || ' DROP COLUMN identifier';
667                RAISE NOTICE 'Entferne ''identifier'' aus: %', c.table_name;
668        END LOOP;
669END;
670$$ LANGUAGE plpgsql;
671
672
673-- Funktion zum Ermitteln der letzten import_id
674CREATE OR REPLACE FUNCTION get_import_id() RETURNS TRIGGER AS $$
675BEGIN
676        EXECUTE 'SELECT max(id) FROM import' INTO NEW.import_id;
677        RETURN NEW;
678END;
679$$ LANGUAGE plpgsql;
680
681
682-- 2014-09-22 Kopie aus Norbit-Github
683-- Funktioniert nicht wenn SchlÃŒsseltabellen mit "ax_" beginnen wie die Objekt-Tabellen.
684-- Darin wird dann z.B. die Splate "endet" gesucht.
685-- ToDo: besseres Namens-Schema fÃŒr Tabellen.
686--   z.B. Umbenennen "ax_buchungsstelle_buchungsart" -> "v_bs_buchungsart"
687CREATE OR REPLACE FUNCTION alkis_hist_check() RETURNS varchar AS $$
688DECLARE
689        c RECORD;
690        n INTEGER;
691        r VARCHAR;
692BEGIN
693        FOR c IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND substr(table_name,1,3) IN ('ax_','ap_','ks_') AND table_type='BASE TABLE'
694        LOOP
695                EXECUTE 'SELECT count(*) FROM ' || c.table_name || ' WHERE endet IS NULL GROUP BY gml_id HAVING count(*)>1' INTO n;
696                IF n>1 THEN
697                        r := coalesce(r||E'\n','') || c.table_name || ': ' || n || ' Objekte, die in mehreren Versionen nicht beendet sind.';
698                END IF;
699
700                EXECUTE 'SELECT count(*) FROM ' || c.table_name || ' WHERE beginnt>=endet' INTO n;
701                IF n>1 THEN
702                        r := coalesce(r||E'\n','') || c.table_name || ': ' || n || ' Objekte mit ungÃŒltiger Lebensdauer.';
703                END IF;
704
705                EXECUTE 'SELECT count(*)'
706                        || ' FROM ' || c.table_name || ' a'
707                        || ' JOIN ' || c.table_name || ' b ON a.gml_id=b.gml_id AND a.ogc_fid<>b.ogc_fid AND a.beginnt<b.endet AND a.endet>b.beginnt'
708                        INTO n;
709                IF n>0 THEN
710                        r := coalesce(r||E'\n','') || c.table_name || ': ' || n || ' LebensdauerÃŒberschneidungen.';
711                END IF;
712        END LOOP;
713
714        RETURN r;
715END;
716$$ LANGUAGE plpgsql;
717
718-- Aufruf: SELECT alkis_hist_check();
719
Note: See TracBrowser for help on using the repository browser.