Zusammenfassung alles Anpassungen, welche in der B2B durchgeführt werden müssen

Anpassungen in der B2B

Wenn Sie die NUI installieren, müssen einige der existierenden Tabellen der B2B aktualisiert werden. Dazu gehören:

  • Die Message/Action/Attribute Tabellen
  • Die Tabellen für UserMessages (optionales Feature)

View/Sicht (Message/Action/Attribute)

Die Suche im Nachrichtenmonitor wurde auf anderem Wege realisiert, als dies bisher der Fall ist. Die releventen Datenbanktabellen wurden als View/Sicht auf der Datenbank zusammengefasst. Dies hat den Vorteil, dass zum Beispiel bei partitionierter Datenbank nur noch auf eine Sicht (welche diese Tabellen vereint) zugegriffen werden muss.

Die folgenden Definitionen sind für eine PostgreSQL und Oracle -Datenbank. Sollten die Sicht für andere Datenbanken benötigt werden, freuen wir uns über ihre Zuarbeit. :) Eine Anfrage an uns ist ebenso möglich, wird aber nicht mit Priorität bearbeitet.

PostgreSQL

Ohne Partition

Diese View ist im Standardfall notwendig.

CREATE OR REPLACE VIEW public.b2bbp_data_message_view AS
SELECT b2bbp_data_message.messageid,
    b2bbp_data_message.referenceid,
    b2bbp_data_message.direction,
    b2bbp_data_message.started,
    b2bbp_data_message.finished,
    b2bbp_data_message.formatin,
    b2bbp_data_message.formatout,
    b2bbp_data_message.vdewtype,
    b2bbp_data_message.vdewversion,
    b2bbp_data_message.state,
    b2bbp_data_message.acknowledgement,
    b2bbp_data_message.partner,
    b2bbp_data_message.sender,
    b2bbp_data_message.correlationid,
    b2bbp_data_message.alternativeid,
    b2bbp_data_message.channelid,
    b2bbp_data_message.clearingcode,
    'A'::text AS sourcetable
  FROM b2bbp_data_message;

CREATE OR REPLACE VIEW public.b2bbp_data_action_view AS
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'A'::text AS sourcetable
FROM public.b2bbp_data_action;

CREATE OR REPLACE VIEW public.b2bbp_data_attribute_view AS
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'A'::text AS sourcetable
FROM public.b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'V'::text AS sourcetable
FROM public.b2bbp_data_attribute_archive;

CREATE OR REPLACE VIEW public.b2bbp_data_error_view AS
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'A'::text AS sourcetable
FROM public.b2bbp_data_error;

Mit Partition

Bitte verwenden sie diese View, sofern die das Application Partitions einsetzen.

CREATE OR REPLACE VIEW public.b2bbp_data_message_view AS
SELECT b2bbp_data_message.messageid,
    b2bbp_data_message.referenceid,
    b2bbp_data_message.direction,
    b2bbp_data_message.started,
    b2bbp_data_message.finished,
    b2bbp_data_message.formatin,
    b2bbp_data_message.formatout,
    b2bbp_data_message.vdewtype,
    b2bbp_data_message.vdewversion,
    b2bbp_data_message.state,
    b2bbp_data_message.acknowledgement,
    b2bbp_data_message.partner,
    b2bbp_data_message.sender,
    b2bbp_data_message.correlationid,
    b2bbp_data_message.alternativeid,
    b2bbp_data_message.channelid,
    b2bbp_data_message.clearingcode,
    'A'::text AS sourcetable
  FROM b2bbp_data_message
UNION ALL
SELECT b2bbp_data_message_clearing.messageid,
    b2bbp_data_message_clearing.referenceid,
    b2bbp_data_message_clearing.direction,
    b2bbp_data_message_clearing.started,
    b2bbp_data_message_clearing.finished,
    b2bbp_data_message_clearing.formatin,
    b2bbp_data_message_clearing.formatout,
    b2bbp_data_message_clearing.vdewtype,
    b2bbp_data_message_clearing.vdewversion,
    b2bbp_data_message_clearing.state,
    b2bbp_data_message_clearing.acknowledgement,
    b2bbp_data_message_clearing.partner,
    b2bbp_data_message_clearing.sender,
    b2bbp_data_message_clearing.correlationid,
    b2bbp_data_message_clearing.alternativeid,
    b2bbp_data_message_clearing.channelid,
    b2bbp_data_message_clearing.clearingcode,
    'C'::text AS sourcetable
  FROM b2bbp_data_message_clearing
UNION ALL
SELECT b2bbp_data_message_offline.messageid,
    b2bbp_data_message_offline.referenceid,
    b2bbp_data_message_offline.direction,
    b2bbp_data_message_offline.started,
    b2bbp_data_message_offline.finished,
    b2bbp_data_message_offline.formatin,
    b2bbp_data_message_offline.formatout,
    b2bbp_data_message_offline.vdewtype,
    b2bbp_data_message_offline.vdewversion,
    b2bbp_data_message_offline.state,
    b2bbp_data_message_offline.acknowledgement,
    b2bbp_data_message_offline.partner,
    b2bbp_data_message_offline.sender,
    b2bbp_data_message_offline.correlationid,
    b2bbp_data_message_offline.alternativeid,
    b2bbp_data_message_offline.channelid,
    b2bbp_data_message_offline.clearingcode,
    'O'::text AS sourcetable
  FROM b2bbp_data_message_offline;

CREATE OR REPLACE VIEW public.b2bbp_data_action_view AS
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'A'::text AS sourcetable
FROM public.b2bbp_data_action
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'C'::text AS sourcetable
FROM public.b2bbp_data_action_clearing
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'O'::text AS sourcetable
FROM public.b2bbp_data_action_offline

CREATE OR REPLACE VIEW public.b2bbp_data_attribute_view AS
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'A'::text AS sourcetable
FROM public.b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'V'::text AS sourcetable
FROM public.b2bbp_data_attribute_archive
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'C'::text AS sourcetable
FROM public.b2bbp_data_attribute_clearing
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'O'::text AS sourcetable
FROM public.b2bbp_data_attribute_offline;

CREATE OR REPLACE VIEW public.b2bbp_data_error_view AS
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'A'::text AS sourcetable
FROM public.b2bbp_data_error
UNION ALL
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'C'::text AS sourcetable
FROM public.b2bbp_data_error_clearing
UNION ALL
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'O'::text AS sourcetable
FROM public.b2bbp_data_error_offline;

Oracle

Ohne Partition

Diese View ist im Standardfall notwendig.

CREATE OR REPLACE VIEW b2bbp_data_message_view AS
SELECT b2bbp_data_message.messageid,
    b2bbp_data_message.referenceid,
    b2bbp_data_message.direction,
    b2bbp_data_message.started,
    b2bbp_data_message.finished,
    b2bbp_data_message.formatin,
    b2bbp_data_message.formatout,
    b2bbp_data_message.vdewtype,
    b2bbp_data_message.vdewversion,
    b2bbp_data_message.state,
    b2bbp_data_message.acknowledgement,
    b2bbp_data_message.partner,
    b2bbp_data_message.sender,
    b2bbp_data_message.correlationid,
    b2bbp_data_message.alternativeid,
    b2bbp_data_message.channelid,
    b2bbp_data_message.clearingcode,
    cast('A' as varchar(1)) AS sourcetable
  FROM b2bbp_data_message;

CREATE OR REPLACE VIEW b2bbp_data_action_view AS
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       cast('A' as varchar(1)) AS sourcetable
FROM b2bbp_data_action;

CREATE OR REPLACE VIEW b2bbp_data_attribute_view AS
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('A' as varchar(1)) AS sourcetable
FROM b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('V' as varchar(1)) AS sourcetable
FROM b2bbp_data_attribute_archive;

CREATE OR REPLACE VIEW b2bbp_data_error_view AS
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       cast('A' as varchar(1)) AS sourcetable
FROM b2bbp_data_error;

Mit Partition

Bitte verwenden sie diese View, sofern die das Application Partitions einsetzen.

CREATE OR REPLACE VIEW b2bbp_data_message_view AS
SELECT b2bbp_data_message.messageid,
    b2bbp_data_message.referenceid,
    b2bbp_data_message.direction,
    b2bbp_data_message.started,
    b2bbp_data_message.finished,
    b2bbp_data_message.formatin,
    b2bbp_data_message.formatout,
    b2bbp_data_message.vdewtype,
    b2bbp_data_message.vdewversion,
    b2bbp_data_message.state,
    b2bbp_data_message.acknowledgement,
    b2bbp_data_message.partner,
    b2bbp_data_message.sender,
    b2bbp_data_message.correlationid,
    b2bbp_data_message.alternativeid,
    b2bbp_data_message.channelid,
    b2bbp_data_message.clearingcode,
    cast('A' as varchar(1)) AS sourcetable
  FROM b2bbp_data_message
UNION ALL
SELECT b2bbp_data_messagec.messageid,
    b2bbp_data_messagec.referenceid,
    b2bbp_data_messagec.direction,
    b2bbp_data_messagec.started,
    b2bbp_data_messagec.finished,
    b2bbp_data_messagec.formatin,
    b2bbp_data_messagec.formatout,
    b2bbp_data_messagec.vdewtype,
    b2bbp_data_messagec.vdewversion,
    b2bbp_data_messagec.state,
    b2bbp_data_messagec.acknowledgement,
    b2bbp_data_messagec.partner,
    b2bbp_data_messagec.sender,
    b2bbp_data_messagec.correlationid,
    b2bbp_data_messagec.alternativeid,
    b2bbp_data_messagec.channelid,
    b2bbp_data_messagec.clearingcode,
    cast('C' as varchar(1)) AS sourcetable
  FROM b2bbp_data_messagec
UNION ALL
SELECT b2bbp_data_messageo.messageid,
    b2bbp_data_messageo.referenceid,
    b2bbp_data_messageo.direction,
    b2bbp_data_messageo.started,
    b2bbp_data_messageo.finished,
    b2bbp_data_messageo.formatin,
    b2bbp_data_messageo.formatout,
    b2bbp_data_messageo.vdewtype,
    b2bbp_data_messageo.vdewversion,
    b2bbp_data_messageo.state,
    b2bbp_data_messageo.acknowledgement,
    b2bbp_data_messageo.partner,
    b2bbp_data_messageo.sender,
    b2bbp_data_messageo.correlationid,
    b2bbp_data_messageo.alternativeid,
    b2bbp_data_messageo.channelid,
    b2bbp_data_messageo.clearingcode,
    cast('O' as varchar(1)) AS sourcetable
  FROM b2bbp_data_messageo;

CREATE OR REPLACE VIEW b2bbp_data_action_view AS
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       cast('A' as varchar(1)) AS sourcetable
FROM b2bbp_data_action
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       cast('C' as varchar(1)) AS sourcetable
FROM b2bbp_data_actionc
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       cast('O' as varchar(1)) AS sourcetable
FROM b2bbp_data_actiono;

CREATE OR REPLACE VIEW b2bbp_data_attribute_view AS
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('A' as varchar(1)) AS sourcetable
FROM b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('V' as varchar(1)) AS sourcetable
FROM b2bbp_data_attribute_archive
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('C' as varchar(1)) AS sourcetable
FROM b2bbp_data_attributec
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('O' as varchar(1)) AS sourcetable
FROM b2bbp_data_attributeo;

CREATE OR REPLACE VIEW b2bbp_data_error_view AS
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       cast('A' as varchar(1)) AS sourcetable
FROM b2bbp_data_error
UNION ALL
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       cast('C' as varchar(1)) AS sourcetable
FROM b2bbp_data_errorc
UNION ALL
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       cast('O' as varchar(1)) AS sourcetable
FROM b2bbp_data_erroro;

MS SQL

Ohne Partition

Diese View ist im Standardfall notwendig.

CREATE VIEW b2bbp_data_message_view AS
SELECT b2bbp_data_message.messageid,
    b2bbp_data_message.referenceid,
    b2bbp_data_message.direction,
    b2bbp_data_message.started,
    b2bbp_data_message.finished,
    b2bbp_data_message.formatin,
    b2bbp_data_message.formatout,
    b2bbp_data_message.vdewtype,
    b2bbp_data_message.vdewversion,
    b2bbp_data_message.state,
    b2bbp_data_message.acknowledgement,
    b2bbp_data_message.partner,
    b2bbp_data_message.sender,
    b2bbp_data_message.correlationid,
    b2bbp_data_message.alternativeid,
    b2bbp_data_message.channelid,
    b2bbp_data_message.clearingcode,
    'A' AS sourcetable
FROM b2bbp_data_message;

CREATE VIEW b2bbp_data_action_view AS
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'A' AS sourcetable
FROM b2bbp_data_action;

CREATE VIEW b2bbp_data_attribute_view AS
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'A' AS sourcetable
FROM b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'V' AS sourcetable
FROM b2bbp_data_attribute_archive;

CREATE VIEW b2bbp_data_error_view AS
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'A' AS sourcetable
FROM b2bbp_data_error;

Mit Partition

Bitte verwenden sie diese View, sofern die das Application Partitions einsetzen.

CREATE VIEW b2bbp_data_message_view AS
SELECT b2bbp_data_message.messageid,
    b2bbp_data_message.referenceid,
    b2bbp_data_message.direction,
    b2bbp_data_message.started,
    b2bbp_data_message.finished,
    b2bbp_data_message.formatin,
    b2bbp_data_message.formatout,
    b2bbp_data_message.vdewtype,
    b2bbp_data_message.vdewversion,
    b2bbp_data_message.state,
    b2bbp_data_message.acknowledgement,
    b2bbp_data_message.partner,
    b2bbp_data_message.sender,
    b2bbp_data_message.correlationid,
    b2bbp_data_message.alternativeid,
    b2bbp_data_message.channelid,
    b2bbp_data_message.clearingcode,
    'A' AS sourcetable
  FROM b2bbp_data_message
UNION ALL
SELECT b2bbp_data_messagec.messageid,
    b2bbp_data_messagec.referenceid,
    b2bbp_data_messagec.direction,
    b2bbp_data_messagec.started,
    b2bbp_data_messagec.finished,
    b2bbp_data_messagec.formatin,
    b2bbp_data_messagec.formatout,
    b2bbp_data_messagec.vdewtype,
    b2bbp_data_messagec.vdewversion,
    b2bbp_data_messagec.state,
    b2bbp_data_messagec.acknowledgement,
    b2bbp_data_messagec.partner,
    b2bbp_data_messagec.sender,
    b2bbp_data_messagec.correlationid,
    b2bbp_data_messagec.alternativeid,
    b2bbp_data_messagec.channelid,
    b2bbp_data_messagec.clearingcode,
    'C' AS sourcetable
  FROM b2bbp_data_messagec
UNION ALL
SELECT b2bbp_data_messageo.messageid,
    b2bbp_data_messageo.referenceid,
    b2bbp_data_messageo.direction,
    b2bbp_data_messageo.started,
    b2bbp_data_messageo.finished,
    b2bbp_data_messageo.formatin,
    b2bbp_data_messageo.formatout,
    b2bbp_data_messageo.vdewtype,
    b2bbp_data_messageo.vdewversion,
    b2bbp_data_messageo.state,
    b2bbp_data_messageo.acknowledgement,
    b2bbp_data_messageo.partner,
    b2bbp_data_messageo.sender,
    b2bbp_data_messageo.correlationid,
    b2bbp_data_messageo.alternativeid,
    b2bbp_data_messageo.channelid,
    b2bbp_data_messageo.clearingcode,
    'O' AS sourcetable
  FROM b2bbp_data_messageo;

CREATE VIEW b2bbp_data_action_view AS
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'A' AS sourcetable
FROM b2bbp_data_action
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'C' AS sourcetable
FROM b2bbp_data_actionc
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       'O' AS sourcetable
FROM b2bbp_data_actiono;

CREATE VIEW b2bbp_data_attribute_view AS
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'A' AS sourcetable
FROM b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'V' AS sourcetable
FROM b2bbp_data_attribute_archive
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'C' AS sourcetable
FROM b2bbp_data_attributec
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       'O' AS sourcetable
FROM b2bbp_data_attributeo;

CREATE VIEW b2bbp_data_error_view AS
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'A' AS sourcetable
FROM b2bbp_data_error
UNION ALL
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'C' AS sourcetable
FROM b2bbp_data_errorc
UNION ALL
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       'O' AS sourcetable
FROM b2bbp_data_erroro;

UserMessages

Falls Sie das Feature UserMessages nutzen, müssen die zugehörigen Tabellen angepasst werden. Dies erfolgt durch den UserMessages-Service (Backend) automatisch. Falls Sie die Anpassungen lieber manuell durchführen möchten, können Sie folgende Scripte verwenden:

Postgres

alter table b3p_adm_systemmessages add column messagesubject varchar(255);

alter table b3p_adm_systemmessages add column status varchar(50) default 'ACTIVE';

MSSQL

alter table B3P_ADM_SYSTEMMESSAGES add MESSAGESUBJECT varchar(255);

alter table B3P_ADM_SYSTEMMESSAGES add STATUS varchar(50) DEFAULT 'ACTIVE';

ORACLE

alter table B3P_ADM_SYSTEMMESSAGES add MESSAGESUBJECT varchar(255);

alter table B3P_ADM_SYSTEMMESSAGES add STATUS varchar(50) DEFAULT 'ACTIVE';
View Me   Edit Me