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

Revision 338, 29.1 KB checked in by frank.jaeger, 10 years ago (diff)

Neuer Trigger ohne Historie. Anzahl Kontext-Operationen in import-Tabelle eintragen.

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