Romber

Database tables

romber_item.instance__version_components

• Purpose
• Definition
• Constraints
• Examples

Purpose

This table provides a list of components for each item instance. This information needs a data entry page. The components are placed together in the specified order to form a displayable page. Each component specifies an include file and/or text which forms part of the final content.

An item instance component is accessed using its associated item instance ID.

Definition

Source romber_item_instance__version_components_tbl.sql

create table romber_item.instance__version_components (
  associated_meta_object_id     romber_meta.object_id           not null,
  item_component_include        romber_base.generic_include     not null,
  item_component_type_id        romber_item.component_type_id   not null,
  item_instance_id              romber_item.instance_id         not null,
  meta_transaction_id           romber_meta.transaction_id      not null );

Constraints

Source romber_item_instance__version_components_pk.sql

alter table romber_item.instance__version_components
  add constraint romber_item_instance__version_components_pk
    primary key (
      item_instance_id,
      meta_transaction_id,
      item_component_type_id );

Source romber_item_instance__version_components_fk1.sql

alter table romber_item.instance__version_components
  add constraint romber_item_instance__version_components_fk1
    foreign key (
      item_instance_id,
      meta_transaction_id )
    references romber_item.instance__versions (
      item_instance_id,
      meta_transaction_id );

Source romber_item_instance__version_components_fk2.sql

alter table romber_item.instance__version_components
  add constraint romber_item_instance__version_components_fk2
    foreign key ( item_component_type_id )
    references romber_item.component_types ( item_component_type_id );

Source romber_item_instance__version_components_fk3.sql

alter table romber_item.instance__version_components
  add constraint romber_item_instance__version_components_fk3
    foreign key ( associated_meta_object_id )
    references romber_meta.objects ( meta_object_id );

Examples

Using views:

insert into romber_item.instance__version_components (
    item_component_include,
    item_component_type_id,
    item_instance_id,
    meta_transaction_id )
  select
      '/page_begin.inc',
      ( select item_component_type_id
	  from romber_item.component_types
	  where item_component_type_code = 'BEGIN' ),
      item_instance_id,
      meta_transaction_id
    from romber_item.instance_details
    where item_instance_code = 'ROMBER';

Using only tables:

insert into romber_item.instance__version_components (
    item_component_include,
    item_component_type_id,
    item_instance_id,
    meta_transaction_id )
  select
      '/page_begin.inc',
      ( select item_component_type_id
	  from romber_item.component_types
	  where item_component_type_code = 'BEGIN' ),
      item_instance_id,
      meta_transaction_id
    from romber_item.instance__versions t1
    inner join romber_meta.object__versions t2
      on t2.meta_object_id = t1.item_instance_id
    where t1.item_instance_code = 'ROMBER'
      and t2.version_begin_timestamp <= current_timestamp at time zone 'UTC'
      and t2.version_end_timestamp > current_timestamp at time zone 'UTC';