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

Anpassungen in der B2B

View/Sicht

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_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,
    cast('C' as varchar(1)) 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,
    cast('O' as varchar(1)) AS sourcetable
  FROM b2bbp_data_message_offline;

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_action_clearing
UNION ALL
SELECT actionId,
       messageId,
       started,
       finished,
       type,
       name,
       className,
       state,
       cast('O' as varchar(1)) AS sourcetable
FROM b2bbp_data_action_offline

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_attribute_clearing
UNION ALL
SELECT attributeId,
       actionId,
       messageId,
       name,
       val,
       len,
       type,
       preview,
       cast('O' as varchar(1)) AS sourcetable
FROM b2bbp_data_attribute_offline;

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_error_clearing
UNION ALL
SELECT errorId,
       actionId,
       messageId,
       name,
       message,
       stacktrace,
       cast('O' as varchar(1)) AS sourcetable
FROM b2bbp_data_error_offline;
View Me   Edit Me