PRODAT PUBLIC

ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS foreign_schema varchar(100);

ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   foreign_table         VARCHAR(100);           -- Die Spalte referenziert auf diese Tabelle ...

ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   foreign_column        VARCHAR(50);            -- ... und Spalte

ALTER TABLE tablefieldinfo ADD COLUMN IF NOT EXISTS   isWritable            BOOLEAN DEFAULT false;

ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_gruppe_textno      INTEGER;

ALTER TABLE recnogroup ADD COLUMN IF NOT EXISTS reg_bez_textno         INTEGER;

ALTER TABLE SettingsDyn ADD COLUMN IF NOT EXISTS sd_deleted      BOOLEAN DEFAULT False;

ALTER TABLE adk ADD COLUMN IF NOT EXISTS ad_dokversand INTEGER;

ALTER TABLE anfart ADD COLUMN IF NOT EXISTS aArt_aknr_idx  VARCHAR(30);

--- Liefert Dokumentversandbezeuchnung von ad_dokversand

CREATE FUNCTION TAdk.DokVersandTyp(IN _ad_dokversand INTEGER, OUT ad_dokversand INTEGER,  OUT ad_DokVersandBez VARCHAR) RETURNS RECORD AS $$

 BEGIN

   SELECT null, null INTO ad_dokversand, ad_DokVersandBez;

 END $$ LANGUAGE plpgsql STABLE;

ALTER TABLE belegdokument ADD COLUMN beld_gewicht NUMERIC(12,4);

ALTER TABLE belegpos ADD COLUMN belp_gewicht NUMERIC(12,4);

ALTER TABLE belegpos ADD COLUMN IF NOT EXISTS belp_bstat VARCHAR(2) REFERENCES auftgbs ON UPDATE CASCADE ON DELETE SET NULL; -- Statusflags1

ALTER TABLE belegpos ADD COLUMN IF NOT EXISTS belp_bstat1 VARCHAR(2) REFERENCES auftgbs ON UPDATE CASCADE ON DELETE SET NULL; -- Statusflags2

ALTER TABLE belegpos ADD COLUMN IF NOT EXISTS belp_bstat2 VARCHAR(2) REFERENCES auftgbs ON UPDATE CASCADE ON DELETE SET NULL; -- Statusflags3

/*

ALTER TABLE belegposabzu ADD COLUMN IF NOT EXISTS belpaz_type          VARCHAR(1)  DEFAULT 'E';

ALTER TABLE belegposabzu ADD COLUMN IF NOT EXISTS belpaz_pos           INTEGER;

ALTER TABLE belegposabzu ADD COLUMN IF NOT EXISTS belpaz_zutxt_int     TEXT;

ALTER TABLE belegabzu ADD COLUMN IF NOT EXISTS belaz_type          VARCHAR(1) DEFAULT 'E';

ALTER TABLE belegabzu ADD COLUMN IF NOT EXISTS belaz_pos           INTEGER;

ALTER TABLE belegabzu ADD COLUMN IF NOT EXISTS belaz_zutxt_int     TEXT;

ALTER TABLE belabzu ADD COLUMN IF NOT EXISTS beaz_type          VARCHAR(1) DEFAULT 'E';

ALTER TABLE belabzu ADD COLUMN IF NOT EXISTS beaz_pos           INTEGER;

ALTER TABLE belabzu ADD COLUMN IF NOT EXISTS beaz_zutxt_int     TEXT;

ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_type          VARCHAR(1) DEFAULT 'E';

ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_pos           INTEGER;

ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_zutxt_int     TEXT;

ALTER TABLE ldsabzu ADD COLUMN IF NOT EXISTS ldaz_visible       BOOLEAN NOT NULL DEFAULT TRUE;

ALTER TABLE belzeil_grund ADD COLUMN IF NOT EXISTS bz_vkp_mce       INTEGER REFERENCES artmgc;

ALTER TABLE ldsdok ADD COLUMN IF NOT EXISTS ld_ekp_mce           INTEGER REFERENCES artmgc;

ALTER TABLE ldsdok ADD COLUMN IF NOT EXISTS ld_ekp_mce           INTEGER REFERENCES artmgc;

ALTER TABLE auftg ADD COLUMN IF NOT EXISTS ag_vkp_mce           INTEGER REFERENCES artmgc;

ALTER TABLE artpruefung ADD COLUMN IF NOT EXISTS apr_doktxt     Text;

ALTER TABLE artpruefung ADD COLUMN IF NOT EXISTS apr_aufdok     BOOL NOT NULL DEFAULT FALSE;

ALTER TABLE artpruefung ADD COLUMN IF NOT EXISTS apr_abz_id     INTEGER REFERENCES abzu ON UPDATE CASCADE;

ALTER TABLE ab2ba ADD COLUMN IF NOT EXISTS a2ba_noz_id          INTEGER REFERENCES normzert;

--*/

CREATE FUNCTION TSystem.Settings__GetBool(vari varchar, defvalue boolean DEFAULT false) RETURNS boolean AS $$

    DECLARE r boolean;

              s varchar;

    BEGIN

       IF vari = 'AdminLoginAllowed' THEN RETURN true; END IF;

       SELECT s_inha INTO s FROM public.settings WHERE s_vari = vari;

       --

       IF NOT FOUND THEN

              RETURN defvalue;

       END IF;

       --

       IF UPPER(s) IN ('-1','1', 'T', 'TRUE') THEN

              RETURN true;

       ELSE

              RETURN false;

       END IF;

    END$$LANGUAGE plpgsql PARALLEL SAFE;

	CREATE OR REPLACE FUNCTION TSystem.Settings__GetText(vari varchar)

    RETURNS text

    AS $$

        SELECT coalesce(

                    (SELECT s_inha FROM public.settings WHERE s_vari = vari)

                    , ''

               );

    $$ LANGUAGE sql PARALLEL SAFE;

-- Hinweis: Begrenzung von den 8191 Zeichen, im PgDAC, bei vom VARCHAR ohne Längenangabe.

CREATE FUNCTION TSystem.Settings__Get(vari varchar)

    RETURNS varchar

    AS $$

        SELECT TSystem.Settings__GetText(vari);

    $$LANGUAGE sql PARALLEL SAFE;

-- Setting als BOOL setzen

CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha BOOL) RETURNS VOID AS $$

  BEGIN

    IF inha IS NULL THEN

        PERFORM TSystem.Settings__Delete(vari);

        RETURN;

    END IF;

    IF inha THEN

        PERFORM TSystem.Settings__Set(vari, '-1');  -- true

    ELSE

        PERFORM TSystem.Settings__Set(vari, '0');   -- false

    END IF;

    RETURN;

  END $$ LANGUAGE plpgsql;

--

-- Setting als VARCHAR setzen

CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha VARCHAR) RETURNS VOID AS $$

  DECLARE num NUMERIC;

  BEGIN

    IF inha IS NULL THEN

        PERFORM TSystem.Settings__Delete(vari);

        RETURN;

    END IF;

    IF EXISTS(SELECT true FROM public.settings WHERE s_inha = inha AND s_vari = vari) THEN

        RETURN;

    END IF;

    BEGIN

        num:= inha::NUMERIC(20,8); -- Cast auf max. Numeric versuchen. Länge entspr. Standard für interne Werte.

    EXCEPTION

        WHEN OTHERS THEN num:= NULL;

    END;

    UPDATE settings SET s_inha= inha, s_num_inha= num WHERE s_vari = vari;

    IF NOT FOUND THEN

        INSERT INTO settings (s_vari, s_inha, s_num_inha) VALUES (vari, inha, num);

    END IF;

    RETURN;

  END $$ LANGUAGE plpgsql;

--

-- Setting als INTEGER setzen

CREATE FUNCTION TSystem.Settings__Set(vari VARCHAR, inha INTEGER) RETURNS VOID AS $$

  DECLARE num NUMERIC;

  BEGIN

    IF inha IS NULL THEN

        PERFORM TSystem.Settings__Delete(vari);

        RETURN;

    END IF;

    IF EXISTS(SELECT true FROM public.settings WHERE s_inha = inha::VARCHAR AND s_vari = vari) THEN

        RETURN;

    END IF;

    BEGIN

        num:= inha::NUMERIC(20,8); -- Cast auf max. Numeric versuchen. Länge entspr. Standard für interne Werte.

    EXCEPTION

        WHEN OTHERS THEN num:= NULL;

    END;

    UPDATE settings SET s_inha= inha::VARCHAR, s_num_inha= num WHERE s_vari = vari;

    IF NOT FOUND THEN

        INSERT INTO settings (s_vari, s_inha, s_num_inha) VALUES (vari, inha::VARCHAR, num);

    END IF;

    RETURN;

  END $$ LANGUAGE plpgsql;

  -- Value aus einem INI-TEXT auslesen -> [Section] Name=Value

  CREATE FUNCTION TSystem.INI_GetValue(ini TEXT, section VARCHAR, name VARCHAR, defvalue VARCHAR DEFAULT NULL) RETURNS VARCHAR AS $$

  DECLARE

    sectionsinhalt TEXT;

    val VARCHAR;

  BEGIN

    --Inhalt der eingegebenen 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden

    SELECT trim(substring(ini FROM E'\\[' || section || E'\\]([^\\[]*)'), E'\r\n\t ') INTO sectionsinhalt;

    IF sectionsinhalt IS NULL THEN

      RETURN defvalue;

    END IF;

    --Value mit dem 'name' aus der 'section' ablesen oder 'defvalue' nehmen, wenn nicht gefunden

    SELECT COALESCE(trim(substring(sectionsinhalt FROM E'[$\r\n]*' || name || E'[ \t]*=[ \t]*([^\r\n$]*)'), E'\r\n\t '), defvalue) INTO val;

    RETURN val;

  END $$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION tsystem.dokutypes__find__by__parentnode_id( _parentnode_id varchar )

  RETURNS varchar

  AS $$

     SELECT null

  $$ LANGUAGE sql;  

CREATE FUNCTION tsystem.database__triggers_inaktiv__get(

    IN _all_schemas   boolean = false

    )

    RETURNS TABLE (

      tgname          varchar,

      src_schemaname  varchar,

      src_tablename   varchar,

      trg_schemaname  varchar,

      trg_tablename   varchar

    )

    AS $$

          -- hinweis: cast as varchar nur in pg kleiner 13?!

          SELECT tgname::varchar,

                 src_schema.nspname::varchar  AS src_schemaname,

                 src_table.relname::varchar   AS src_tablename,

                 trg_schema.nspname::varchar  AS trg_schemaname,

                 trg_table.relname::varchar   AS trg_tablename

            FROM pg_trigger

            LEFT JOIN pg_class      AS src_table  ON src_table.oid  = pg_trigger.tgrelid

            LEFT JOIN pg_class      AS trg_table  ON trg_table.oid  = pg_trigger.tgconstrrelid

            LEFT JOIN pg_namespace  AS src_schema ON src_schema.oid = src_table.relnamespace

            LEFT JOIN pg_namespace  AS trg_schema ON trg_schema.oid = trg_table.relnamespace

           WHERE tgenabled = 'D'

             AND (

                  -- Prüfung auf deaktivierte Trigger für alle Schemata

                      _all_schemas

                  -- Prüfung auf deaktivierte Trigger gilt nicht für folg. Schemata

                    -- ignore:    trigger on drop,   fkey: public -> drop, drop -> public, drop -> drop

                    -- relevant:  trigger on public, fkey: public -> public

                  OR  (

                       -- Quelle des deaktivierten Triggers bzw. Contraint-Triggers (fKey) ist nicht in folg. Schemata

                           coalesce( src_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )

                       -- analog für Ziel des Contraint-Triggers (fKey)

                       AND coalesce( trg_schema.nspname, '' ) NOT IN ( 'z_99_drop', 'x_950_import', 'x_900_export' )

                      )

                 )

           ORDER BY tgname

    $$ LANGUAGE sql;

--

CREATE FUNCTION TSystem.triggers__all__disable() RETURNS VOID AS $$

  DECLARE r RECORD;

  BEGIN

    BEGIN

      PERFORM TSystem.Settings__Set('triggersdisabled', True);

    EXCEPTION WHEN OTHERS THEN

      PERFORM TSystem.Settings__Set('triggersdisabled', True);

    END;

    FOR r IN

      --SELECT DISTINCT schemaname, tablename

      --FROM tablefieldinfo

      --WHERE schemaname NOT IN ('z_99_drop', 'import')

      --  AND tablename NOT IN ('tablefieldinfo')  -- es gibt ein SELECT darauf, drum lässt sich kein ALTER TABLE machen

      --  AND NOT isView

      SELECT DISTINCT table_schema AS schemaname, table_name AS tablename

      FROM information_schema.tables

      WHERE table_catalog = current_database()

        AND table_type = 'BASE TABLE'

        AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')

    LOOP

      EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' DISABLE TRIGGER ALL';

    END LOOP;

  END $$ LANGUAGE plpgsql;

--

--

CREATE FUNCTION TSystem.triggers__all__enable() RETURNS VOID AS $$

  DECLARE r RECORD;

  BEGIN

    FOR r IN  -- aus TFormDBUpdates.Up_EnableTrigger -> lokales CodeSelect

      SELECT DISTINCT table_schema AS schemaname, table_name AS tablename

      FROM information_schema.tables

      WHERE table_catalog = current_database()

        AND table_type = 'BASE TABLE'

        AND table_schema NOT IN ('information_schema', 'pg_catalog', 'z_99_drop', 'import')

    LOOP

      EXECUTE 'ALTER TABLE ' || quote_ident(r.schemaname) || '.' || quote_ident(r.tablename) || ' ENABLE TRIGGER ALL';

    END LOOP;

    BEGIN

      PERFORM TSystem.Settings__Set('triggersdisabled', False);

    EXCEPTION WHEN OTHERS THEN

      PERFORM TSystem.Settings__Set('triggersdisabled', False);

    END;

  END $$ LANGUAGE plpgsql;		

    CREATE FUNCTION TSystem.LogDebug(source VARCHAR, message TEXT, category INTEGER DEFAULT 0, context TEXT DEFAULT NULL) RETURNS VOID AS $$

      BEGIN

        RETURN;

      END $$ LANGUAGE plpgsql;

-- Erstellt einen View zur Abfrage von DB-Tabellen, Views darauf, Feldern und ForeignKeys der Felder. Schließt PG_catalog und einige System-Schemata aus.

DROP VIEW IF EXISTS TSystem.tablefieldinfo_view;

CREATE VIEW TSystem.tablefieldinfo_view AS

  SELECT

    -- this filters out composite indicies and duplicated FKs on same columns

    DISTINCT ON ( n.nspname, c.relname, a.attnum )

    -- this function is used by the information schema views

    -- the bitmask works like this:

    --    b'00000100' = deleteable

    --    b'00001000' = updateable

    --    b'00010000' = insertable

    (pg_relation_is_updatable(c.oid::regclass, false)::bit(8) & b'00011100' = b'00011100' ) AS isWritable,

    c.relkind = 'v' AS isview,

    n.nspname       AS schemaname,

    c.relname       AS tablename,

    a.attnum        AS index,

    a.attname       AS field,

    -- t.typcategory,

    format_type( a.atttypid, null ) AS type,

    -- length only for string types

    CASE WHEN

        -- S = varchars und chars

        t.typcategory IN ( 'S' )

      THEN nullif(a.atttypmod,-1) - 4

      ELSE null

    END

    AS length,

    -- adsrc was removed in pg12

    pg_get_expr( d.adbin, d.adrelid ) AS "default",

    fn.nspname AS foreign_table_schema,

    ft.relname AS foreign_table_name,

    fc.attname AS foreign_column_name

  FROM

    pg_class                  AS c

    -- schemata

    LEFT JOIN pg_namespace    AS n ON

          n.oid = c.relnamespace

    -- columns

    LEFT JOIN pg_attribute    AS a ON

          c.oid = a.attrelid

    -- columntypes

    LEFT JOIN pg_type         AS t ON

          t.oid = a.atttypid

    -- default values of columns

    LEFT JOIN pg_attrdef      AS d ON

          d.adrelid = c.oid

      AND d.adnum = a.attnum

    -- FKs

    LEFT JOIN pg_constraint   AS f ON

          f.contype = 'f'             -- foreignkey

      AND f.conrelid = c.oid          -- local table

      AND array_length ( f.confkey , 1 ) < 2 -- exclude composite keys

      AND f.conkey[1] = a.attnum      -- conkey is a array over the attributes ordinal positions

    -- destination of FKs

    LEFT JOIN pg_attribute    AS fc ON

          f.confrelid  = fc.attrelid -- foreign table

      AND f.confkey[1] = fc.attnum

    LEFT JOIN pg_class        AS ft ON

          ft.oid = f.confrelid

    LEFT JOIN pg_namespace    AS fn ON

          fn.oid = ft.relnamespace

  WHERE

    -- only tables and views ( including materialized views )

        c.relkind IN ('r','v','m')

    -- p = permanent table ( default tables)

    -- u = unlogged table ( eg. tlog.auditlog )

    -- t = temporary table ( pg_temp_%, create temp table )

    AND c.relpersistence IN ('p','u')

    -- exclude certain namespaces; they are lowercase by default, except for doubleQuoted Identifier

    AND n.nspname NOT IN (

         'pg_catalog', 'information_schema', 'tcache', 'tsystem', 'sunext', 'import', 'z_99_drop', 'x_900_export'

       )

    -- hide internal fields

    -- # select attname, attnum from pg_attribute where attrelid = 'abk'::regclass::oid and attnum < 3

    -- ┌──────────────┬────────┐

    -- │   attname    │ attnum │

    -- ╞══════════════╪════════╡

    -- │ tableoid     │     -7 │

    -- │ cmax         │     -6 │

    -- │ xmax         │     -5 │

    -- │ cmin         │     -4 │

    -- │ xmin         │     -3 │

    -- │ ctid         │     -1 │

    -- │ ab_ix        │      1 │

    -- │ ab_parentabk │      2 │

    -- └──────────────┴────────┘

    AND a.attnum > 0

    -- they have been deleted anyways by the TSystem.tablefieldinfo__recreate() function

    AND a.attname NOT IN ('insert_by', 'modified_by', 'insert_date', 'modified_date')

    -- we must omit dropped columns

    AND a.attisdropped IS false

  ORDER BY schemaname, tablename, index

;

-- Ausgelagert aus Delphi. Ergänzt um Weitergabe der Constraints zugrundeliegender Tabelle an die Views.

CREATE OR REPLACE FUNCTION TSystem.tablefieldinfo__recreate(OUT fieldCountBefore INTEGER, OUT fieldCountAfter INTEGER) RETURNS RECORD AS $$

  BEGIN

    FieldCountBefore := COUNT(*) FROM tablefieldinfo;

    -- Einfach alles rauswerfen und neu aufbauen aus den Information-Schema Katalogen.

    TRUNCATE tablefieldinfo;

    INSERT INTO tablefieldinfo ( isWritable, isView, schemaname, tablename, index, field, type, length, def, foreign_schema, foreign_table, foreign_column)

    SELECT * FROM TSystem.TableFieldInfo_view ORDER BY schemaname, tablename, field;

    -- View ist Updatable und wird als ChildLink benutzt. Also brauchen wir Defaults und Foreign-Keys.

    PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_data', 'public', 'oplpm');

    PERFORM TSystem.CopyConstraintInfo('public', 'oplpm_stam', 'public', 'oplpm_data');

    -- view dbrid default wert setzen

    UPDATE tablefieldinfo SET def = 'nextval(''db_id_seq'')' WHERE isView AND isWritable AND field = 'dbrid';

    FieldCountAfter :=  COUNT(*) FROM tablefieldinfo;

    --- #18294 Auditlog-Config-FieldList Refresh

    --PERFORM tlog.AuditlogConfig_OF__all__recreate();

    RETURN;

  END $$ LANGUAGE plpgsql VOLATILE;

--

CREATE OR REPLACE FUNCTION TSystem.CopyConstraintInfo( srcSchema VARCHAR,  srcTable VARCHAR,  trgSchema VARCHAR,  trgViewOrTableName VARCHAR) RETURNS VOID AS $$

  BEGIN

    -- kopiert spalten defaultwert von tabellen in view informationen

    UPDATE tablefieldinfo AS trg SET

        def            = CASE WHEN trg.isWritable THEN src.def ELSE NULL END,

        foreign_table  = src.foreign_table,

        foreign_column = src.foreign_column

      FROM tablefieldinfo AS src

      WHERE trg.schemaname = trgSchema

        AND trg.tablename  = trgViewOrTableName

        AND src.Field      = trg.field

        AND src.schemaname = srcSchema

        AND src.tablename  = srcTable;

    RETURN;

  END $$ LANGUAGE plpgsql VOLATILE;  

SELECT TSystem.tablefieldinfo__recreate();  

-- DELETE FROM dbupdates WHERE upd_kunde IS NOT null