
prompt Installing Versioned Instance Schema ....
rem TODO: put table and column comments against all these

rem ---------------------------------------------------------------------------+
rem The Repository Instance Object Schema - General Notes
rem
rem Identifiers: Most tables have a surrogate primary key: (irid,ivid)
rem instance repository identifier, instance version identifier
rem ---------------------------------------------------------------------------+

rem ---------------------------------------------------------------------------+
rem TODOS:
rem   - do all constraints need to be deferrable
rem   - need GUID in front of every object
rem ---------------------------------------------------------------------------+


rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_object_versions
rem ---------------------------------------------------------------------------+
rem Moved to jrov.sql and jrovtrg.sql


rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_invalid_objects
rem    Notes: Stores objects in need of constraint evaluation
rem
rem           CW 15-sep-1999 Fix bug 964423 remove use of config member triggers
rem           Primary objects now validated in the same way as secondary objects
rem           using _inv and _val triggers. No need for config information in
rem           this table
rem
rem TODO: Define fks to objver, tables and cfg ?
rem ---------------------------------------------------------------------------+
rem
create table i$sdd_invalid_objects (
  object_irid     number
, object_ivid     number
, table_irid      number
, type_irid       number
, config_irid     number
, config_ivid     number
)
/

-- CW 23-Sep-1999 Fix bug 923601 remove unique key causing locking contention
-- sdd_invobj_uk
--alter table i$sdd_invalid_objects add constraint sdd_invobj_uk
--unique (table_irid, object_irid, object_ivid)
--/

-- Index no longer required as indexed columns are same as in the unique key
-- create index i$invobj_ind1 on i$sdd_invalid_objects (table_irid, object_irid, object_ivid)
-- /

rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_deleted_objects
rem    Notes: Stores deleted objects in need of constraint evaluation
rem
rem TODO: Define uk (cf. invalid objects)
rem TODO: Define fks to objver, tables and cfg ?
rem ---------------------------------------------------------------------------+
rem

create table i$sdd_deleted_objects (
  object_irid     number
 ,object_ivid     number
 ,table_irid      number
 ,config_irid     number
 ,config_ivid     number
)
/

rem ---------------------------------------------------------------------------+
prompt Table: ck_chk_ref_set
rem    Notes: Temporary table used by JR_CHECK_REFS package when checking
rem           references in an arbitary set of objects.
rem
rem ---------------------------------------------------------------------------+
rem

create table CK_CHK_REF_SET(
   set_irid number not null
  ,el_ivid  number not null
)
/


rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_config_spec
rem
rem A rule based specification for the contents of a configuration. Rules may
rem select from the whole repository or from another configuration. A
rem specification is not versionable. Several versions of a configuration may
rem use the same specification.
rem ---------------------------------------------------------------------------+
rem
create table i$sdd_config_spec (
  irid                  number                        not null
, name                  varchar2(256)                 not null
, notm                  number        default 0       not null
, date_created          date          default sysdate not null
, created_by            varchar2(32)  default user    not null
, date_changed          date
, changed_by            varchar2(32)
, selection_config_ivid number
)
/

-- pk_sdd_cfgspec
alter table i$sdd_config_spec add constraint pk_sdd_cfgspec primary key (irid)
/

-- uk_sdd_cfgspec
alter table i$sdd_config_spec add constraint uk_sdd_cfgspec unique (name)
/

create or replace trigger sdd_cfgspec_audit
  before update on i$sdd_config_spec
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_config_spec_entry
rem
rem The entries of a configuration specification. Each entry identifies a
rem selection rule with values for any parameters of the rule. The sequence of
rem the entries is important, determining which rule takes precedence in cases
rem of conflict.
rem ---------------------------------------------------------------------------+
rem
create table i$sdd_config_spec_entry (
  config_spec_irid      number                        not null
, rule_irid             number                        not null
, entry_sequence        number                        not null
, exclude               varchar2(1)   default 'N'     not null
, notm                  number        default 0       not null
, date_created          date          default sysdate not null
, created_by            varchar2(32)  default user    not null
, date_changed          date
, changed_by            varchar2(32)
, param1_value          varchar2(300)
, param2_value          varchar2(300)
, param3_value          varchar2(300)
)
/

-- pk_sdd_cfg_specentry
alter table i$sdd_config_spec_entry add constraint pk_sdd_cfg_specentry
primary key (config_spec_irid, rule_irid)
/

-- uk_sdd_cfg_specentry
alter table i$sdd_config_spec_entry add constraint uk_sdd_cfg_specentry
unique (config_spec_irid, entry_sequence)
/

-- fk_cfg_specentry_to_cfgspec
alter table i$sdd_config_spec_entry add constraint fk_cfg_specentry_to_cfgspec
foreign key (config_spec_irid) references i$sdd_config_spec
/

-- fk_cfg_specentry_to_rule
alter table i$sdd_config_spec_entry add constraint fk_cfg_specentry_to_rule
foreign key (rule_irid) references i$sdd_rules
/

-- cfg_spc_entry_check_exlude
alter table i$sdd_config_spec_entry add constraint cfg_spc_entry_check_exlude
check (exclude in ('Y', 'N'))
/

-- Not creating an index for the foreign key fk_specentry_to_cfgspec as this is the
-- leading part of the indexes for the primary and unique key constraints

create index ind_fk_cfg_specentry_to_rule on i$sdd_config_spec_entry (rule_irid)
/

create or replace trigger sdd_cfgspec_entry_audit
  before update on i$sdd_config_spec_entry
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_configurations
rem    Notes: configurations group together sets of objects
rem ---------------------------------------------------------------------------+
rem

create table i$sdd_configurations (
  irid              number                        not null
, ivid              number                        not null
, name              varchar2(256)                 not null
, notm              number        default 0       not null
, date_created      date          default sysdate not null
, created_by        varchar2(32)  default user    not null
, date_changed      date
, changed_by        varchar2(32)
, config_spec_irid  number
, kind              varchar2(32)
, tracking          varchar2(1)
, description       varchar2(2000)
)
/

comment on table i$sdd_configurations is 'Configurations group together specific object versions';

-- pk_sdd_cfg
alter table i$sdd_configurations add constraint pk_sdd_cfg
primary key (ivid)
/

-- fk_cfg_to_cfgspec
alter table i$sdd_configurations add constraint fk_cfg_to_cfgspec
foreign key (config_spec_irid) references i$sdd_config_spec
/

-- Can now create the foreign key from config_spec now config table created
-- fk_cfgspec_to_cfg
alter table i$sdd_config_spec add constraint fk_cfgspec_to_cfg
foreign key (selection_config_ivid) references i$sdd_configurations
/

create index ind_cfg_name on i$sdd_configurations (name)
/

create index ind_fk_cfg_to_cfgspec on i$sdd_configurations (config_spec_irid)
/

create index ind_fk_cfgspec_to_cfg on i$sdd_config_spec (selection_config_ivid)
/

-- CW 16-Jul-1999
-- Configuration triggers moved to jrcfgtrg.sql

rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_configuration_members
rem    Notes: maintains the (nested) collections of i$sdd_object_versions that
rem           are intended to work together.
rem           A constraint on this table prevents same objects with
rem           different versions from being in the same configuration
rem ---------------------------------------------------------------------------+
rem

create table i$sdd_configuration_members (
  config_irid    number                        not null
 ,config_ivid    number                        not null
 ,object_irid    number                        not null
 ,object_ivid    number                        not null
, notm           number        default 0       not null
, date_created   date          default sysdate not null
, created_by     varchar2(32)  default user    not null
, date_changed   date
, changed_by     varchar2(32)
 ,kind           varchar2(1)
)
/

comment on table  i$sdd_configuration_members      is 'Grouping for configurations';
comment on column i$sdd_configuration_members.kind is 'Reason for insertion: (R)esolved (C)hecked out';

-- cfgmbr_kind_range
alter table i$sdd_configuration_members add constraint cfgmbr_kind_range
check (kind in (null, 'R', 'C'))
/

-- pk_sdd_cfgmbr
alter table i$sdd_configuration_members add constraint pk_sdd_cfgmbr
primary key (config_ivid, object_ivid)
/

-- fk_cfgmbr_to_cfg
alter table i$sdd_configuration_members add constraint fk_cfgmbr_to_cfg
foreign key (config_ivid) references i$sdd_configurations on delete cascade
/

rem NOTE: This must not be ON DELETE CASCADE! due to mutating table errors
rem       CW 5-Oct-98
rem       This may have been true if/when configurations were configuration members
rem       in which case we may have then deleted rows in this table - mutating error
rem       However, configurations cannot be configuration members (although I don't
rem       think there is any code to stop this (TODO), so I think the fk can be
rem       changed to cascade delete

-- fk_cfgmbr_to_objver
alter table i$sdd_configuration_members add constraint fk_cfgmbr_to_objver
foreign key (object_ivid) references i$sdd_object_versions
/

rem This ensures that there cannot be similar objects with different versions
rem directly in the same configuration
rem TODO: this does NOT prevent the nasty nested case

-- uk_sdd_cfgmbr_no_dup_vers
alter table i$sdd_configuration_members add constraint uk_sdd_cfgmbr_no_dup_vers
unique (config_ivid, object_irid)
/

create unique index ind_cfgmbr_ov_cfg on i$sdd_configuration_members (object_ivid, config_ivid)
/

-- Note, not creating an index on fk_cfgmbr_to_cfg (config_ivid) as this column is indexed by
-- virtue of being the leading part of the pk and uk

create index ind_fk_cfgmbr_to_objver on i$sdd_configuration_members (object_ivid)
/


--B1280348 - Audit trigger removed from config memebers table to improve performance

rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_branches
rem    Notes: Stores globally allocated branches
rem ---------------------------------------------------------------------------+
rem
create table i$sdd_branches (
  branch_id     number                          not null
 ,name          varchar2(128)                   not null
 ,notm          number         default 0        not null
 ,date_created  date           default sysdate  not null
 ,created_by    varchar2(32)   default user     not null
 ,date_changed  date
 ,changed_by    varchar2(32)
 ,notes         varchar2(2000)
)
/

-- pk_sdd_br
alter table i$sdd_branches add constraint pk_sdd_br
primary key (branch_id)
/

-- uk_sdd_br_name
alter table i$sdd_branches add constraint uk_sdd_br_name
unique (name)
/

-- CW 16-Jul-1999
-- Branch triggers moved to jrbrtrg.sql (except for audit trigger below)

create or replace trigger sdd_branch_audit
  before update on i$sdd_branches
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

-- CW 23-Sep-1999 Fix bug 1000526 preserve guid values of seed data
insert into i$sdd_branches (branch_id, name) values (415838727997810237600499570000283705, 'MAIN');

rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_version_associations
rem    Notes: Stores the version graph for a particular object
rem ---------------------------------------------------------------------------+
rem
create table i$sdd_version_associations (
  irid          number                          not null
 ,from_ivid     number                          not null
 ,to_ivid       number                          not null
 ,edge_kind     varchar2(1)     default 'N'     not null
 ,branch_id     number          default 0       not null
 ,notm          number          default 0       not null
 ,date_created  date            default sysdate not null
 ,created_by    varchar2(30)    default user    not null
 ,date_changed  date
 ,changed_by    varchar2(32)
 ,edge_sequence number
 ,notes         varchar2(2000)
)
/

-- version_graph_edge_kind
alter table i$sdd_version_associations add constraint version_graph_edge_kind
check (edge_kind in ('B','N','M','T','R','C','L'))
/

-- TODO: this constraint is violated by jr_version!
--       should be fixed
-- version_graph_pk
-- alter table i$sdd_version_associations add constraint version_graph_pk
-- primary key (irid, from_ivid, to_ivid)
-- /

-- fk_va_to_br
alter table i$sdd_version_associations add constraint fk_va_to_br
foreign key (branch_id) references i$sdd_branches
/

comment on column i$sdd_version_associations.edge_kind is
  'Valid edge kinds are : Branch, Normal, Merge, Tip, Root, Checkout, Locked checkout'
/

create index ind_va_from_edge_kind on
i$sdd_version_associations (from_ivid, irid, edge_kind)
/

create index ind_va_branch_edge_kind on
i$sdd_version_associations (branch_id, irid, edge_kind, to_ivid)
/

create index ind_va_to_irid on
i$sdd_version_associations (to_ivid, irid)
/

create index ind_va_irid on
i$sdd_version_associations (irid)
/

create or replace trigger sdd_verassoc_audit
before update on i$sdd_version_associations
for each row
declare
  adt_notm         number;
  adt_date_changed date;
  adt_changed_by   varchar2(30);
begin
  adt_notm         := :new.notm;
  adt_date_changed := :new.date_changed;
  adt_changed_by   := :new.changed_by;

  jr_audit.pre_update( adt_notm, adt_date_changed, adt_changed_by);
  if ((:new.notm is null) or (:old.notm=:new.notm)) then
    :new.notm:=adt_notm;
  end if;
  if ((:new.date_changed is null) or (:old.date_changed=:new.date_changed)) then
    :new.date_changed:=adt_date_changed;
  end if;
  if ((:new.changed_by is null) or (:old.changed_by=:new.changed_by)) then
    :new.changed_by:=adt_changed_by;
  end if;
end;
/

rem ---------------------------------------------------------------------------+
prompt Table: i$sdd_repos_policies
rem    Notes: Stores the Repository policies. These are repository wide preferences
rem    such as always lock on check out and are held as NVP.
rem    Access to this table should only use the package jr_policy
rem ---------------------------------------------------------------------------+
rem
create table i$sdd_repos_policies (
  name          varchar2(128)                   not null
 ,value         varchar2(128)                   not null
 ,notm          number          default 0       not null
 ,date_created  date            default sysdate not null
 ,created_by    varchar2(30)    default user    not null
 ,date_changed  date
 ,changed_by    varchar2(30)
)
/

-- pk_sdd_repos_policies
alter table i$sdd_repos_policies add constraint pk_sdd_repos_policies
primary key (name)
/

create or replace trigger sdd_repos_policy_audit
  before update on i$sdd_repos_policies
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

insert into i$sdd_repos_policies (name, value) values ('COPY_DEPENDENCIES_ON_VERSIONING' , 'Y');
insert into i$sdd_repos_policies (name, value) values ('COPY_DEPENDENCIES_ON_COPYING'    , 'Y');

rem ---------------------------------------------------------------------------+
rem Workarea Specification Tables
rem TODO: These definitiuons are generated from the DE -
rem need to add comments and constraints etc.
rem ---------------------------------------------------------------------------+

rem ---------------------------------------------------------------------------+
prompt Creating Table 'i$sdd_workareas'
rem
rem CW 14-Sep-98 Fixed bug 715601, removed column fs_cache_mode
rem
rem ---------------------------------------------------------------------------+
create table i$sdd_workareas
 (irid                    number                            not null
 ,owner                   varchar2(256)                     not null
 ,name                    varchar2(256)                     not null
 ,kind                    varchar2(30)    default 'PRIVATE' not null
 ,notm                    number          default 0         not null
 ,date_created            date            default sysdate   not null
 ,created_by              varchar2(32)    default user      not null
 ,notc                    number          default 0         not null
 ,date_changed            date
 ,changed_by              varchar2(32)
 ,is_compiled             char(1)
 ,default_checkin_branch  number
 ,description             varchar2(2000)
)
/

comment on column i$sdd_workareas.notc is 'Number of times workarea has been compiled';

-- pk_sdd_wa
alter table i$sdd_workareas add constraint pk_sdd_wa
primary key (irid)
/

-- Each workarea change may be checked in on a default branch
-- fk_wa_to_br
alter table i$sdd_workareas add constraint fk_wa_to_br
foreign key (default_checkin_branch) references i$sdd_branches
/

create index ind_wa_owner_name on i$sdd_workareas (owner, name)
/

create index ind_wa_name on i$sdd_workareas (name)
/

create index ind_fk_wa_to_br  on i$sdd_workareas (default_checkin_branch)
/

-- CW 16-Jul-1999
-- Wokarea triggers moved to jrwatrg.sql (except for audit trigger below)

create or replace trigger sdd_workarea_audit
  before update on i$sdd_workareas
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

rem ---------------------------------------------------------------------------+
PROMPT Creating Table 'i$sdd_wa_context'
rem
rem Note : Table is index organized table containing all neccessary info for
rem        version resolution queries.
rem 
rem WGS 8th Sept 1999, Part of performance fix (Bug 986706)
rem ---------------------------------------------------------------------------+
create table i$sdd_wa_context
(
  workarea_irid  number                   not null
, object_ivid    number                   not null
, wastebasket    varchar2(1)  default 'N' not null 
, spec_entry     number       
, constraint pk_wa_ctxt
   primary key (workarea_irid, object_ivid, wastebasket)
)
organization index
/

-- Each workarea context entry must be for a workarea
-- fk_wa_ctxt_to_wa
alter table i$sdd_wa_context add constraint fk_wa_ctxt_to_wa
foreign key (workarea_irid) references i$sdd_workareas on delete cascade
/

-- Removed foreign key fk_wa_ctxt_to_objver on object_ivid, to i$sdd_object_versions
-- as this caused mutating table errors

rem ---------------------------------------------------------------------------+
PROMPT Creating Table 'i$sdd_workarea_spec_entries'
rem
rem Note : There is no workarea spec entry for the private configuration in a
rem        workarea
rem CW 15-Sep-98
rem Fix bug 727783, modify definition of table
rem ---------------------------------------------------------------------------+
create table i$sdd_workarea_spec_entries
( seq           number                        not null
, workarea_irid number                        not null
, exclude       varchar2(1)   default 'N'     not null
, notm          number        default 0       not null
, date_created  date          default sysdate not null
, created_by    varchar2(30)  default user    not null
, config_ivid   number                        
, date_changed  date
, changed_by    varchar2(30)
, rule_irid     number
, predecessor   number
)
/

-- check_exlude
alter table i$sdd_workarea_spec_entries add constraint check_exlude
check (exclude in ('Y', 'N'))
/

-- TODO, why don't we use irid like everywhere else ?
-- pk_sdd_waspec
alter table i$sdd_workarea_spec_entries add constraint pk_sdd_waspec
primary key (seq)
/

-- Each workarea spec entry may succeed another workarea spec entry
-- fk_waspec_to_waspec
alter table i$sdd_workarea_spec_entries add constraint fk_waspec_to_waspec
foreign key (predecessor) references i$sdd_workarea_spec_entries
/

-- Each workarea spec entry must be defined for a workarea
-- fk_waspec_to_wa
alter table i$sdd_workarea_spec_entries add constraint fk_waspec_to_wa
foreign key (workarea_irid) references i$sdd_workareas on delete cascade
/

-- Each workarea spec entry must identify a configuration
-- fk_waspec_to_cfg
alter table i$sdd_workarea_spec_entries add constraint fk_waspec_to_cfg
foreign key (config_ivid) references i$sdd_configurations on delete cascade
/

-- Each workarea spec entry may be defined by a rule
-- fk_waspec_to_rule
alter table i$sdd_workarea_spec_entries add constraint fk_waspec_to_rule
foreign key (rule_irid) references i$sdd_rules
/

create index ind_fk_waspec_to_waspec on i$sdd_workarea_spec_entries (predecessor)
/

create index ind_fk_waspec_to_wa on i$sdd_workarea_spec_entries (workarea_irid)
/

create index ind_fk_waspec_to_cfg on i$sdd_workarea_spec_entries (config_ivid)
/

create index ind_fk_waspec_to_rule on i$sdd_workarea_spec_entries (rule_irid)
/

create or replace trigger sdd_waspec_entry_audit
  before update on i$sdd_workarea_spec_entries
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

rem ---------------------------------------------------------------------------+
PROMPT Creating Table 'i$sdd_compilation_changes'
rem
rem ---------------------------------------------------------------------------+

create table i$sdd_compilation_changes
( workarea_irid  number       not null
, object_ivid    number       not null
, change_type    varchar2(1)
)
/

-- pk_sdd_compilation_changes
alter table i$sdd_compilation_changes add constraint pk_sdd_compilation_changes
primary key (workarea_irid, object_ivid)
/

-- fk_comp_changes_to_wa
alter table i$sdd_compilation_changes add constraint fk_comp_changes_to_wa
foreign key (workarea_irid) references i$sdd_workareas on delete cascade
/

-- fk_comp_changes_to_objver
alter table i$sdd_compilation_changes add constraint fk_comp_changes_to_objver
foreign key (object_ivid) references i$sdd_object_versions on delete cascade
/

create index ind_fk_comp_changes_to_wa on i$sdd_compilation_changes (workarea_irid)
/

create index ind_fk_comp_changes_to_objver on i$sdd_compilation_changes (object_ivid)
/

rem ---------------------------------------------------------------------------+
PROMPT Creating Table 'i$sdd_rule_param_values'
rem
rem Table used to store values used for rule parameters
rem against the configurations and rules these values
rem are used for.
rem WS 11th Jan 1999
rem ---------------------------------------------------------------------------+

create table i$sdd_rule_param_values
( entry_seq     number                        not null
, rule_irid     number                        not null
, param_seq     number                        not null
, value         varchar2(300)                 not null
, notm          number        default 0       not null
, date_created  date          default sysdate not null
, created_by    varchar2(30)  default user    not null
, date_changed  date
, changed_by    varchar2(30)
)
/

-- pk_sdd_rulprmval
alter table i$sdd_rule_param_values add constraint pk_sdd_rulprmval
primary key (entry_seq, param_seq)
/

-- fk_prmval_to_waspec
alter table i$sdd_rule_param_values add constraint fk_prmval_to_waspec
foreign key (entry_seq) references i$sdd_workarea_spec_entries on delete cascade
/

-- fk_prmval_to_rulprm
alter table i$sdd_rule_param_values add constraint fk_prmval_to_rulprm
foreign key (rule_irid, param_seq) references i$sdd_rule_params on delete cascade
/

-- Note, not creating an index on fk_prmval_to_waspec (entry_seq) as column is
-- indexed due to being leading column of pk

create index ind_fk_prmval_to_rulprm on i$sdd_rule_param_values (rule_irid, param_seq)
/

create or replace trigger sdd_rulprmval_audit
  before update on i$sdd_rule_param_values
  for each row
  begin
    jr_audit.pre_update( :new.notm, :new.date_changed, :new.changed_by);
  end;
/

