PRODAT PUBLIC

{{toc}}

h1. Update Postgres 13/15 (ab Prodat 2104)

* Postgres 13/15 Installieren

* pg_hba.conf übernehmen

* ALTE SICHERUNGEN ENTFERNEN, sonst wird alles mitgenommen und man muss überall die implicit-casts einspielen!

* ggf eigene cluster für mandanten wenn diese getrennt sein sollen. > https://redmine.prodat-sql.de/issues/17033#note-15

* MoveDB Script ausführen. Dazu das Script direkt anpassen (Server und Port). Anweisungen beachten und unbedingt einhalten!

+ ..\Administration\DB_Admin_BackUp_TestDB\DB_Move_DB_Cluster__too.bat

h2. Update Version 12 (Postgres 9) auf Version 2206 (Postgres 13/15)

* Postgres 13/15 installieren

* gem. oberer Beschreibung den Datenbankcluster in Postgres 13/15 umziehen. Somit wird am Bestandssystem NICHTS geändert.

* Nachdem der Cluster umgezogen ist, müssen die *2 Files im Anhang* ausgeführt werden

+ Durch diese Files werden Dummy Funktionen eingespielt, die Kompatiblilität mit dem neuen Client herstellen. Teilweise werden nuf Funktionsrümpfe eingespielt, damit die Oberflächen starten. Durch die DBupdates werden dann die richtigen Funktionen eingespielt

+ Wenn es beim Einspielen der Files zu fehlern kommt, muss man checken ob die Funktionen doch bereits vorhanden sind und dann auskommentieren

h2. Update V11

{{collapse(SQL)

* Gem. V12

*# 010 Functions System.sql

+ CREATE SCHEMA IF NOT EXISTS TSystem;

+ CREATE SCHEMA IF NOT EXISTS z_99_deprecated;

*# z0 dcache + comp.sql

*# z0 role System.sql

*# 0 0 SQL Fielgenerator.sql

*# Folgende SQL:

<pre><code class="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;

</code></pre>

<pre><code class="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;

</code></pre>

<pre><code class="sql">

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')

</code></pre>

}}