• Gem. V12
  1. 010 Functions System.sql
     + CREATE SCHEMA IF NOT EXISTS TSystem;
     + CREATE SCHEMA IF NOT EXISTS z_99_deprecated;
  2. z0 dcache + comp.sql
  3. z0 role System.sql
  4. 0 0 SQL Fielgenerator.sql
  5. Folgende SQL:
     

     CREATE TABLE recnogroup (
       reg_id                 serial PRIMARY KEY,
       reg_schema             varchar(50),                   -- Schema wo die Tabele aus reg_tablename liegt
       reg_tablename          varchar(80),                   -- (früher reg_tform) Modulname zur Identifikation der F2s
       reg_bez                varchar(100),                  -- Bezeichnung der Artikeleigenschaft Bsp: Oberfläche
       reg_gruppe             varchar(80),                   -- Gruppe zu der sie zugeordnet werden kann
       reg_ac_n               varchar(9),                    -- X TableContraints: REFERENCES artcod ON UPDATE CASCADE; -- Artikel-Code  kann zugeordent werden
       reg_pos                integer,                       -- Position des Parameters innerhalb einer Gruppe für Sortierung in GUI
       reg_pname              varchar(40) NOT NULL,          -- eindeutiger Parametername, damit in Triggern / Funktionen nicht mit ID gearbeitet werden muss.
       reg_gruppe_textno      integer,                       -- Textnr. Übersetzung für Gruppe
       reg_bez_textno         integer,                       -- Textnr. Übersetzung für Bezeichnung
       reg_paramtype          varchar(30),                   -- Parametertypen 'ptVARCHAR', 'ptINTEGER', 'ptNUMERIC', 'ptBOOLEAN', 'ptTRISTATEBOOLEAN', 'ptTIMESTAMP', 'ptDATE', 'ptENUM', 'ptTEXT'
       reg_default            varchar(100),                  -- Vorgabe bzw. Standardwert des Parameters (in varchar konvertiert)
       reg_minvalue           integer,                       -- Min-Wert für Zahlen, Mindestlänge bei varchar
       reg_maxvalue           integer,                       -- Max-Wert für Zahlen, Maximallänge bei varchar
       reg_vorgabenliste      boolean DEFAULT FALSE,         -- Vorgabenliste: Enum-Set dient als "Vorgabetabelle" für ein Modul.
       reg_visible            boolean DEFAULT TRUE,          -- Für 'normale' Nutzer in Register Eigenschaften sichtbar
       reg_exclusive          boolean,                       -- Bei Parametertyp ptENUM darf nur ein Enumerationswert angegeben sein, sonst als SET zu betrachten
       reg_autoinsert         boolean NOT NULL DEFAULT FALSE,-- Version 1: Flag in Zieltabellentrigger (z.Bsp. am QAB) abfragen, ob RecnoKeyword automatisch
                                                             -- angelegt werden soll. Version 2: In table_modified verschieben und komplett automatisieren.
       reg_field_name         varchar(50),                   -- Feldname für Vorgabetabelle-Enums, siehe wiki
       reg_field_value        varchar(100),                  -- Wert für Vorgabetabelle-Enums, siehe wiki
       reg_m_iso              varchar(10),                   -- # 7163 EAV - Datentyp Mengeneinheit
     
       -- CHECK for existing schema
       CONSTRAINT recnogroup__schema__existing CHECK (
               reg_schema IS NULL
           OR  tsystem.schema_exists( reg_schema )
       ),
     
       -- CHECK for existing table within the current searchpath
       CONSTRAINT recnogroup__table__existing CHECK (
               reg_tablename IS NULL
           OR  tsystem.table_exists( reg_tablename, reg_schema )
       ),
     
       -- CHECK for existing column
       CONSTRAINT recnogroup__column__existing CHECK (
               reg_field_name IS NULL
           OR  tsystem.column_exists( reg_tablename, reg_field_name, coalesce( reg_schema, 'public' ) )
       ),
     
       --- #18368 CHECK reg_paramtype in Parametertypen-Liste
       CONSTRAINT reg_paramtype_in_liste CHECK (
               reg_paramtype IN ( 'ptVARCHAR', 'ptINTEGER', 'ptNUMERIC', 'ptBOOLEAN', 'ptTRISTATEBOOLEAN', 'ptTIMESTAMP', 'ptDATE', 'ptENUM', 'ptTEXT' )
       )
     
     );
     
     CREATE TABLE SettingsDyn (
       sd_id           INTEGER PRIMARY KEY CHECK (sd_id > 0),  -- [SYNCRO:SyncID]
       sd_name         VARCHAR NOT NULL,                       -- TODO [SYNCRO:SyncID]
       sd_parentid     INTEGER NOT NULL,
       sd_type         VARCHAR(20),
       sd_sort         NUMERIC(7, 2),                          -- Sortierungsreihenfolge
       sd_config       TEXT,
       sd_settingsname VARCHAR(80),
       sd_settingstype VARCHAR(20),
       sd_vartxtnr     INTEGER,
       sd_deleted      BOOLEAN DEFAULT False                   -- [SYNCRO:Deleted]
       -- System (tables__generate_missing_fields)
       --modified_date TIMESTAMP(0)                            -- [SYNCRO:Modified]
     );
     
     -- for tri gram indicies for example on dbupdates
     -- CREATE EXTENSION IF NOT EXISTS pg_trgm;
     -- CREATE EXTENSION IF NOT EXISTS btree_gist;
     
     -- [SYNCRO-TABLE] DBUpdates
     CREATE TABLE dbupdates
      (
       upd_id        varchar(30) NOT NULL DEFAULT currenttime()::VARCHAR PRIMARY KEY,  -- [SYNCRO:SyncID] ID des Updates
       upd_parent    varchar(30),              -- parent (Baumstruktur)
       upd_minver    varchar(11),              -- [SYNCRO:Version] Mindest-Programmversion
       upd_kunde     varchar(20),              -- [SYNCRO:Kunde]
       upd_donedat   timestamp(0),             -- [SYNCRO:NotThisFields] update eingespielt am
       upd_bez       varchar(150) NOT NULL,    -- betreff/bezeichnung
       upd_txt       text,
       upd_sql       text,
       upd_projekt   varchar(100),
       upd_noerr     boolean NOT NULL DEFAULT false,
       upd_sperr     boolean NOT NULL DEFAULT false,
       -- System (tables__generate_missing_fields)
       dbrid         varchar(32) NOT NULL DEFAULT nextval('db_id_seq'),
       insert_date   date,
       insert_by     varchar(32),
       modified_by   varchar(32),
       modified_date timestamp(0)              -- [SYNCRO:Modified]
      );
     
      -- CREATE INDEX ON dbupdates USING gist ( dbrid gist_trgm_ops );
      -- CREATE INDEX ON dbupdates USING gist ( upd_bez gist_trgm_ops );
      -- CREATE INDEX ON dbupdates USING gist ( upd_id gist_trgm_ops );
      -- CREATE INDEX ON dbupdates USING gist ( upd_projekt gist_trgm_ops );
      CREATE INDEX ON dbupdates (upd_donedat);
     
      -- gist index for upd_sql gets to big and fails with
      -- ERROR:  index row requires 8304 bytes, maximum size is 8191
      -- CREATE INDEX ON dbupdates USING gin  ( upd_sql gin_trgm_ops );
     
      CREATE INDEX dbupdates__donedat ON dbupdates ( upd_parent );
     
     -- Triggerfunktion fürs automatisches Schließen der DB-Updates nach Version
     CREATE OR REPLACE FUNCTION dbupdates__b_i() RETURNS TRIGGER AS $$
       BEGIN
         IF EXISTS(SELECT true FROM settings WHERE s_vari = 'DBU_date_for_auto_done') THEN
             IF new.upd_id::DATE <= TSystem.Settings__Get('DBU_date_for_auto_done')::DATE THEN
                 new.upd_donedat:= currenttime();
                 new.upd_txt:= E'[DBU-AUTO-DONE]\n' || COALESCE(new.upd_txt, '');
             END IF;
         END IF;
     
         RETURN new;
       END $$ LANGUAGE plpgsql;
     
       CREATE TRIGGER dbupdates__b_i BEFORE INSERT ON dbupdates FOR EACH ROW EXECUTE PROCEDURE dbupdates__b_i();
     
       CREATE OR REPLACE FUNCTION dbupdates__filtered__nodes__upd_ids__get() RETURNS SETOF VARCHAR AS $$
           WITH _nodes AS (
                 SELECT upd_id FROM dbupdates 
                  WHERE upd_donedat >= current_timestamp - '90 days'::INTERVAL   
                     OR upd_donedat IS NULL
                     OR coalesce(modified_date, insert_date::TIMESTAMP) >= current_timestamp - '90 days'::INTERVAL
                     OR upd_sperr     
                ),
                _parents AS (
                 WITH RECURSIVE _tree AS (
                       SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, 0 AS depth FROM dbupdates a WHERE upd_id IN (SELECT upd_id FROM _nodes)
                       UNION
                       SELECT a.upd_id, a.upd_parent, a.upd_bez, a.upd_donedat, a.modified_date, a.insert_date, a.upd_sperr, depth - 1 FROM dbupdates a JOIN _tree p ON a.upd_id = p.upd_parent   
                 )
                 SELECT upd_id FROM _tree
                  WHERE depth < 0
                  ORDER BY depth
                )
                SELECT * FROM _nodes
                UNION
                SELECT * FROM _parents      
       $$ LANGUAGE SQL;
     

--
CREATE OR REPLACE FUNCTION tsystem.syncro__dbupdates(IN _dblink VARCHAR DEFAULT 'host=pg.prodat-erp.de port=5432 dbname=PRODAT-18.08 user=syncro password=syncro') RETURNS void AS $$

  BEGIN

    SET LOCAL SESSION AUTHORIZATION syncro;

    DELETE FROM dbupdates;

    INSERT INTO dbupdates ( upd_id,
                            upd_parent,
                            upd_minver,
                            upd_kunde,
                            -- upd_donedat,
                            upd_bez,
                            upd_txt,
                            upd_sql,
                            upd_projekt,
                            upd_noerr,
                            upd_sperr,
                            --,
                            dbrid,
                            insert_date,
                            insert_by,
                            modified_by,
                            modified_date
                          )

    SELECT *
      FROM dblink(_dblink,
                   'SELECT upd_id,
                           upd_parent,
                           upd_minver,
                           upd_kunde,
                           -- upd_donedat,
                           upd_bez,
                           upd_txt,
                           upd_sql,
                           upd_projekt,
                           upd_noerr,
                           upd_sperr,
                           --,
                           dbrid,
                           insert_date,
                           insert_by,
                           modified_by,
                           modified_date
                      FROM dbupdates
                     WHERE
                              upd_kunde IS null
                          AND upd_minver <= ' || TSystem.quote_literal__connstr_param( TSystem.Settings__Get( 'ProdatVersion' ) ) || '
                  ')
        AS ( upd_id        varchar(30),
             upd_parent    varchar(30),              -- parent (Baumstruktur)
             upd_minver    varchar(11),              -- [SYNCRO:Version] Mindest-Programmversion
             upd_kunde     varchar(20),              -- [SYNCRO:Kunde]
             -- upd_donedat   timestamp(0),             -- [SYNCRO:NotThisFields] update eingespielt am
             upd_bez       varchar(150),
             upd_txt       text,
             upd_sql       text,
             upd_projekt   varchar(100),
             upd_noerr     boolean,
             upd_sperr     boolean,
             -- System (tables__generate_missing_fields)
             dbrid         varchar(32),
             insert_date   date,
             insert_by     varchar(32),
             modified_by   varchar(32),
             modified_date timestamp(0)              -- [SYNCRO:Modified]
           );

  END $$ LANGUAGE plpgsql;

DROP TABLE tablefieldinfo;

-- Spaltentypen, Defaultwerte und Foreignkeys für Tabellen und Views
CREATE TABLE tablefieldinfo
 (isView                BOOLEAN DEFAULT false,  -- True für Viewspalten, False für alle Tabellenspalten
  isWritable            BOOLEAN DEFAULT false,  -- Für Tabellen immer True, bei Views nur wenn Update und Insert möglich sind
  schemaname            VARCHAR(40),            -- Schema der Tabelle / View
  tablename             VARCHAR NOT NULL,       -- View oder Tabellenname
  index                 SMALLINT NOT NULL,      -- Feldindex (Position in der Tabellendefinition)
  field                 VARCHAR NOT NULL,       -- Feldname
  type                  VARCHAR,                -- Feldtyp
  length                INTEGER,                -- Feldlänge (Nur für varchar)
  def                   TEXT,                   -- Defaultwert (Fixwert oder "nextval('name_der_id_sequenz'::regclass)" 
  foreign_schema        VARCHAR(100),           -- Die Spalte referenziert auf dieses Schema ...
  foreign_table         VARCHAR(100),           -- Die Spalte referenziert auf diese Tabelle ...
  foreign_column        VARCHAR(50),            -- ... und Spalte
  -- System (tables__generate_missing_fields)
  --   kein automatisches dbrid, insert_date, insert_by, modified_by, modified_date und table_delete-Trigger (tables__fieldInfo__fetch)
  PRIMARY KEY("schemaname","tablename", "index", "field", "isview")
 );

 CREATE INDEX tablefieldinfo_tablename ON tablefieldinfo(tablename);
 CREATE INDEX tablefieldinfo_field ON tablefieldinfo(field);

SELECT TSystem.tablefieldinfo__recreate();  

SELECT TSystem.tables__generate_missing_fields();

SELECT TSystem.Settings__Set( 'ProdatVersion', '22.06.23.00' );

CREATE OR REPLACE FUNCTION tsystem.quote_literal__connstr_param(
    IN _text varchar
  ) RETURNS varchar AS $$
  DECLARE
    _result varchar;
  BEGIN
    _result := REPLACE( REPLACE( _text, E'\\', E'\\\\' ), E'\'', E'\\\'');
    _result := concat(  '''',
                        _result,
                        ''''
                      );
    RETURN _result;
  END; $$ LANGUAGE plpgsql;

CREATE EXTENSION IF NOT EXISTS dblink;  

SELECT tsystem.syncro__dbupdates();

UPDATE dbupdates SET upd_donedat = '1899-01-01' WHERE upd_id IN ('2015-06-01 15:56:01', '2015-11-02 16:47:42', '2016-09-06 12:25:29', '2017-06-08 17:43:29', '2018-06-10 10:09:01', '2018-07-05 16:58:01')