
rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_object_versions
rem    Notes: this table contains an entry for every instance object in the
rem           runtime repository.
rem    Note2: A row in this table "knows" which general type it is for,
rem           but not which version of the type (row in i$rm_sql_tables) - this
rem           is because a single row may morph and be different things
rem           to different configurations.
rem    Note3: the name column on this table is denormalised from the specific
rem           type instance table - it simplifies the naming code to have
rem           the name stored in this one place
rem
rem CW 25-Jan-99
rem Fix bug 790901, add delta compression support
rem ---------------------------------------------------------------------------+
rem

create table i$sdd_object_versions
( irid                    number                              not null
, ivid                    number                              not null
, table_irid              number                              not null
, logical_type_id         number                              not null
, notm                    number            default 0         not null
, obj_notm                number            default 0         not null
, state                   varchar2(1)       default 'N'       not null
, wastebasket             varchar2(1)       default 'N'       not null
, date_created            date              default sysdate   not null
, created_by              varchar2(30)      default user      not null
, name                    varchar2(256)
, date_changed            date
, changed_by              varchar2(30)
, lock_flag               varchar2(1)
, vlabel                  varchar2(32)
, branch_id               number
, sequence_in_branch      number
, obj_notm_when_analyzed  number
, compression_base_ivid   number
, compression_method      varchar2(10)
, irid_guid               raw(16)
, comments                varchar2(2000)
)
/

comment on table  i$sdd_object_versions       is 'All instance objects in the runtime repository';
comment on column i$sdd_object_versions.state is 'State of object is  New, checked-In, or checked-Out';

-- pk_sdd_objver
alter table i$sdd_object_versions add constraint pk_sdd_objver
primary key (ivid)
/

-- fk_objver_to_tab
alter table i$sdd_object_versions add constraint fk_objver_to_tab
foreign key (table_irid) references i$rm_sql_tables (id)
/

-- fk_objver_to_elem
alter table i$sdd_object_versions add constraint fk_objver_to_elem
foreign key (logical_type_id) references i$rm_element_types
/

-- fk_objver_to_objver
--alter table i$sdd_object_versions add constraint fk_objver_to_objver
--foreign key (compression_base_ivid) references i$sdd_object_versions
--/

-- check_state
alter table i$sdd_object_versions add constraint check_state check (state in ('N', 'I', 'O'))
/

-- check_wastebasket
alter table i$sdd_object_versions add constraint check_wastebasket check (wastebasket in ('Y','N'))
/

create unique index ind_ov_ivid_waste on i$sdd_object_versions (ivid, wastebasket)
/

--create index ind_ov_compression_base_ivid on i$sdd_object_versions (compression_base_ivid)
--/

create unique index ind_ov_meta_data on i$sdd_object_versions (irid, ivid, table_irid, logical_type_id)
/

create index ind_ov_state_irid on i$sdd_object_versions (state, irid)
/

create unique index ind_ov_state_ivid on i$sdd_object_versions (state, ivid, irid)
/

create index ind_ov_vlabel on i$sdd_object_versions (irid, vlabel)
/

create index ind_ov_name on i$sdd_object_versions (name)
/

create index ind_ov_table_irid on i$sdd_object_versions (table_irid)
/

create index ind_ov_logical_type_id on i$sdd_object_versions (logical_type_id)
/

