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 relevanten 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 Scripte setzen die Additional Columns voraus.

Die folgenden Definitionen sind für einige Datenbanken. 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 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,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional1' and messageid = b2bbp_data_message.messageid) as additional1,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional2' and messageid = b2bbp_data_message.messageid) as additional2,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional3' and messageid = b2bbp_data_message.messageid) as additional3,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional4' and messageid = b2bbp_data_message.messageid) as additional4
FROM b2bbp_data_message;

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

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

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

Mit Partition

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

Das Skript hierfür kann bei Bedarf bei unserem Support angefragt werden.

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 varchar2(1)) AS sourcetable,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional1' and messageid = b2bbp_data_message.messageid) as additional1,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional2' and messageid = b2bbp_data_message.messageid) as additional2,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional3' and messageid = b2bbp_data_message.messageid) as additional3,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional4' and messageid = b2bbp_data_message.messageid) as additional4
  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 varchar2(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 varchar2(1)) AS sourcetable
FROM b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('V' as varchar2(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 varchar2(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 varchar2(1)) AS sourcetable,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional1' and messageid = b2bbp_data_message.messageid) as additional1,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional2' and messageid = b2bbp_data_message.messageid) as additional2,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional3' and messageid = b2bbp_data_message.messageid) as additional3,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional4' and messageid = b2bbp_data_message.messageid) as additional4
  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 varchar2(1)) AS sourcetable,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional1' and messageid = b2bbp_data_messagec.messageid) as additional1,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional2' and messageid = b2bbp_data_messagec.messageid) as additional2,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional3' and messageid = b2bbp_data_messagec.messageid) as additional3,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional4' and messageid = b2bbp_data_messagec.messageid) as additional4
  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 varchar2(1)) AS sourcetable,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional1' and messageid = b2bbp_data_messageo.messageid) as additional1,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional2' and messageid = b2bbp_data_messageo.messageid) as additional2,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional3' and messageid = b2bbp_data_messageo.messageid) as additional3,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional4' and messageid = b2bbp_data_messageo.messageid) as additional4
  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 varchar2(1)) AS sourcetable
FROM b2bbp_data_action
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       cast('C' as varchar2(1)) AS sourcetable
FROM b2bbp_data_actionc
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       cast('O' as varchar2(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 varchar2(2)) AS sourcetable
FROM b2bbp_data_attribute
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('V' as varchar2(2)) AS sourcetable
FROM b2bbp_data_attribute_archive
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('AC' as varchar2(2)) AS sourcetable
FROM b2bbp_data_attributec
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('VC' as varchar2(2)) AS sourcetable
FROM b2bbp_data_attribute_archivec
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('AO' as varchar2(2)) AS sourcetable
FROM b2bbp_data_attributeo
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('VO' as varchar2(2)) AS sourcetable
FROM b2bbp_data_attribute_archiveo;

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

MS SQL

Ohne Partition

Diese View ist im Standardfall notwendig.

DROP VIEW IF EXISTS b2bbp_data_message_view;

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,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional1' and messageid = b2bbp_data_message.messageid) as additional1,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional2' and messageid = b2bbp_data_message.messageid) as additional2,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional3' and messageid = b2bbp_data_message.messageid) as additional3,
    (select datavalue from b2bbp_data_additional_columns where datakey = 'additional4' and messageid = b2bbp_data_message.messageid) as additional4
FROM b2bbp_data_message;

DROP VIEW IF EXISTS b2bbp_data_action_view;

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

DROP VIEW IF EXISTS b2bbp_data_attribute_view;

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;

DROP VIEW IF EXISTS b2bbp_data_error_view;

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.

Das Skript hierfür kann bei Bedarf bei unserem Support angefragt werden.

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 varchar2(255);

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

View Me Edit Me