dev.sql

gistfile1.txt

-- liquibase formatted sql

-- changeset lightsaber:1702472324189-1
CREATE TABLE "audit"."datei" ("datei_name" VARCHAR(100) NOT NULL, "datei_schema" TEXT, "loadtime" TIMESTAMP WITHOUT TIME ZONE, CONSTRAINT "datei_pk" PRIMARY KEY ("datei_name"));

-- changeset lightsaber:1702472324189-2
CREATE TABLE "audit"."tabelle" ("tabelle_name" VARCHAR(50) NOT NULL, "tabelle_schema" TEXT, CONSTRAINT "tabelle_pk" PRIMARY KEY ("tabelle_name"));

-- changeset lightsaber:1702472324189-3
CREATE VIEW "audit"."datei_pruefung" AS SELECT t.tabelle_name,
    t.tabelle_schema,
    d.datei_schema,
        CASE
            WHEN (t.tabelle_schema = d.datei_schema) THEN true
            ELSE false
        END AS audit_result,
    d.loadtime
   FROM (audit.tabelle t
     LEFT JOIN audit.datei d ON (((t.tabelle_name)::text = (d.datei_name)::text)));

-- changeset lightsaber:1702472324189-4
CREATE VIEW "audit"."tabellen_stats_core" AS SELECT combined_data.name,
    combined_data.anzahl_zeilen,
    combined_data.letzte_aenderung
   FROM ( SELECT 'core.ausschlusszeit_allgemein'::text AS name,
            count(*) AS anzahl_zeilen,
            max(ausschlusszeit_allgemein.load_timestamp) AS letzte_aenderung
           FROM core.ausschlusszeit_allgemein
          WHERE ((ausschlusszeit_allgemein.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.bridge_lehrgang_fachsemester'::text AS name,
            count(*) AS anzahl_zeilen,
            max(bridge_lehrgang_fachsemester.load_timestamp) AS letzte_aenderung
           FROM core.bridge_lehrgang_fachsemester
        UNION ALL
         SELECT 'core.fachsemester'::text AS name,
            count(*) AS anzahl_zeilen,
            max(fachsemester.load_timestamp) AS letzte_aenderung
           FROM core.fachsemester
        UNION ALL
         SELECT 'core.growslots'::text AS name,
            count(*) AS anzahl_zeilen,
            max(growslots.load_timestamp) AS letzte_aenderung
           FROM core.growslots
        UNION ALL
         SELECT 'core.hochschulbereich'::text AS name,
            count(*) AS anzahl_zeilen,
            max(hochschulbereich.load_timestamp) AS letzte_aenderung
           FROM core.hochschulbereich
        UNION ALL
         SELECT 'core.hochschulzentrum'::text AS name,
            count(*) AS anzahl_zeilen,
            max(hochschulzentrum.load_timestamp) AS letzte_aenderung
           FROM core.hochschulzentrum
        UNION ALL
         SELECT 'core.institut'::text AS name,
            count(*) AS anzahl_zeilen,
            max(institut.load_timestamp) AS letzte_aenderung
           FROM core.institut
        UNION ALL
         SELECT 'core.kalender'::text AS name,
            count(*) AS anzahl_zeilen,
            max(kalender.load_timestamp) AS letzte_aenderung
           FROM core.kalender
        UNION ALL
         SELECT 'core.lehrende_zeiten_temp'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrende_zeiten_temp.load_timestamp) AS letzte_aenderung
           FROM core.lehrende_zeiten_temp
        UNION ALL
         SELECT 'core.lehrender'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender.load_timestamp) AS letzte_aenderung
           FROM core.lehrender
          WHERE ((lehrender.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.lehrender_abwesenheit'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender_abwesenheit.load_timestamp) AS letzte_aenderung
           FROM core.lehrender_abwesenheit
          WHERE ((lehrender_abwesenheit.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.lehrender_ausschluss'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender_ausschluss.load_timestamp) AS letzte_aenderung
           FROM core.lehrender_ausschluss
          WHERE ((lehrender_ausschluss.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.lehrender_einsatz'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender_einsatz.load_timestamp) AS letzte_aenderung
           FROM core.lehrender_einsatz
          WHERE ((lehrender_einsatz.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.lehrender_faecher'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender_faecher.load_timestamp) AS letzte_aenderung
           FROM core.lehrender_faecher
          WHERE ((lehrender_faecher.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.lehrgaenge_zeiten_temp'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgaenge_zeiten_temp.load_timestamp) AS letzte_aenderung
           FROM core.lehrgaenge_zeiten_temp
        UNION ALL
         SELECT 'core.lehrgang'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang.load_timestamp) AS letzte_aenderung
           FROM core.lehrgang
          WHERE ((lehrgang.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.lehrgang_kooperationspartner'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_kooperationspartner.load_timestamp) AS letzte_aenderung
           FROM core.lehrgang_kooperationspartner
        UNION ALL
         SELECT 'core.lehrgang_planungsregeln'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_planungsregeln.load_timestamp) AS letzte_aenderung
           FROM core.lehrgang_planungsregeln
          WHERE ((lehrgang_planungsregeln.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.lehrgang_produktart'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_produktart.load_timestamp) AS letzte_aenderung
           FROM core.lehrgang_produktart
        UNION ALL
         SELECT 'core.lehrgang_produktgruppe'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_produktgruppe.load_timestamp) AS letzte_aenderung
           FROM core.lehrgang_produktgruppe
        UNION ALL
         SELECT 'core.lehrgang_produktkuerzel'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_produktkuerzel.load_timestamp) AS letzte_aenderung
           FROM core.lehrgang_produktkuerzel
        UNION ALL
         SELECT 'core.lehrgang_produkttyp'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_produkttyp.load_timestamp) AS letzte_aenderung
           FROM core.lehrgang_produkttyp
        UNION ALL
         SELECT 'core.modul'::text AS name,
            count(*) AS anzahl_zeilen,
            max(modul.load_timestamp) AS letzte_aenderung
           FROM core.modul
          WHERE ((modul.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.semester'::text AS name,
            count(*) AS anzahl_zeilen,
            max(semester.load_timestamp) AS letzte_aenderung
           FROM core.semester
        UNION ALL
         SELECT 'core.studiengang'::text AS name,
            count(*) AS anzahl_zeilen,
            max(studiengang.load_timestamp) AS letzte_aenderung
           FROM core.studiengang
        UNION ALL
         SELECT 'core.tageszeitinfo'::text AS name,
            count(*) AS anzahl_zeilen,
            max(tageszeitinfo.load_timestamp) AS letzte_aenderung
           FROM core.tageszeitinfo
        UNION ALL
         SELECT 'core.verlaufsplan'::text AS name,
            count(*) AS anzahl_zeilen,
            max(verlaufsplan.load_timestamp) AS letzte_aenderung
           FROM core.verlaufsplan
          WHERE ((verlaufsplan.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.zeitmodell_allgemein'::text AS name,
            count(*) AS anzahl_zeilen,
            max(zeitmodell_allgemein.load_timestamp) AS letzte_aenderung
           FROM core.zeitmodell_allgemein
        UNION ALL
         SELECT 'core.zeitmodell_blockzeiten'::text AS name,
            count(*) AS anzahl_zeilen,
            max(zeitmodell_blockzeiten.load_timestamp) AS letzte_aenderung
           FROM core.zeitmodell_blockzeiten
          WHERE ((zeitmodell_blockzeiten.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.zeitmodell_time_slots'::text AS name,
            count(*) AS anzahl_zeilen,
            max(zeitmodell_time_slots.load_timestamp) AS letzte_aenderung
           FROM core.zeitmodell_time_slots
          WHERE ((zeitmodell_time_slots.transfer_operation_name)::text <> 'Delete'::text)
        UNION ALL
         SELECT 'core.zentrale_virtuelle_produkte'::text AS name,
            count(*) AS anzahl_zeilen,
            max(zentrale_virtuelle_produkte.load_timestamp) AS letzte_aenderung
           FROM core.zentrale_virtuelle_produkte) combined_data
  ORDER BY combined_data.name;

-- changeset lightsaber:1702472324189-5
CREATE VIEW "audit"."tabellen_stats_serving" AS SELECT combined_data.name,
    combined_data.anzahl_zeilen,
    combined_data.letzte_aenderung,
    combined_data.dev_prod_identisch
   FROM ( SELECT 'serving.kurs'::text AS name,
            count(*) AS anzahl_zeilen,
            max(kurs.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.kurs
          WHERE ((kurs.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.kurs_details'::text AS name,
            count(*) AS anzahl_zeilen,
            NULL::timestamp without time zone AS letzte_aenderung,
            false AS dev_prod_identisch
           FROM serving.kurs_details
        UNION ALL
         SELECT 'serving.kurs_freie_slots'::text AS name,
            count(*) AS anzahl_zeilen,
            max(kurs_freie_slots.load_timestamp) AS letzte_aenderung,
            false AS dev_prod_identisch
           FROM serving.kurs_freie_slots
        UNION ALL
         SELECT 'serving.lehrender'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.lehrender
          WHERE ((lehrender.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.lehrender_details'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender_details.load_timestamp) AS letzte_aenderung,
            false AS dev_prod_identisch
           FROM serving.lehrender_details
        UNION ALL
         SELECT 'serving.lehrender_zeiten'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrender_zeiten.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.lehrender_zeiten
          WHERE ((lehrender_zeiten.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.lehrgang'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.lehrgang
          WHERE ((lehrgang.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.lehrgang_details'::text AS name,
            count(*) AS anzahl_zeilen,
            NULL::timestamp without time zone AS letzte_aenderung,
            false AS dev_prod_identisch
           FROM serving.lehrgang_details
        UNION ALL
         SELECT 'serving.lehrgang_zeiten'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_zeiten.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.lehrgang_zeiten
          WHERE ((lehrgang_zeiten.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.lehrgang_zeiten_aus'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_zeiten_aus.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.lehrgang_zeiten_aus
          WHERE ((lehrgang_zeiten_aus.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.lehrgang_zeitmodell'::text AS name,
            count(*) AS anzahl_zeilen,
            max(lehrgang_zeitmodell.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.lehrgang_zeitmodell
          WHERE ((lehrgang_zeitmodell.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.veranstaltung'::text AS name,
            count(*) AS anzahl_zeilen,
            max(veranstaltung.erstellt_am) AS letzte_aenderung,
            false AS dev_prod_identisch
           FROM serving.veranstaltung
          WHERE ((veranstaltung.record_status)::text <> 'deleted'::text)
        UNION ALL
         SELECT 'serving.veranstaltung_details'::text AS name,
            count(*) AS anzahl_zeilen,
            NULL::timestamp without time zone AS letzte_aenderung,
            false AS dev_prod_identisch
           FROM serving.veranstaltung_details
        UNION ALL
         SELECT 'serving.verlaufsplan'::text AS name,
            count(*) AS anzahl_zeilen,
            max(verlaufsplan.load_timestamp) AS letzte_aenderung,
            true AS dev_prod_identisch
           FROM serving.verlaufsplan
          WHERE ((verlaufsplan.record_status)::text <> 'deleted'::text)) combined_data
  ORDER BY combined_data.dev_prod_identisch, combined_data.name