

prompt Creating Versioned Data Dictionary ....

rem -----------------------------------------------------+
prompt Table: i$rm_sql_schema_versions
rem -----------------------------------------------------+
rem
create table i$rm_sql_schema_versions
(  irid         number          not null
 , ivid         number          not null
 , name         varchar2(30)    not null
-- , product      varchar2(2)     not null
 , jr_type      varchar2(10)    not null
 , comments     varchar2(4000)
)
/

-- i$rm_sql_schema_versions_pk
alter table i$rm_sql_schema_versions add constraint i$rm_sql_schema_versions_pk primary key (irid, ivid)
/

-- Unique key, note only single version of schemas currently supported
-- rm_sql_schemas_uk_name
alter table i$rm_sql_schema_versions add constraint rm_sql_schemas_uk_name unique (name)
/

-- rm_sql_schemas_chk_type
alter table i$rm_sql_schema_versions add constraint rm_sql_schemas_chk_type
check (jr_type in ('SYSTEM', 'DESIGNER', 'USER'))
/

rem -----------------------------------------------------+
prompt Table: i$rm_sql_users
rem -----------------------------------------------------+
rem
create table i$rm_sql_users (
  name            varchar2(256) not null
 ,active_workarea number
)
/

-- i$rm_sql_users_pk
alter table i$rm_sql_users add constraint i$rm_sql_users_pk primary key (name)
/

rem -----------------------------------------------------+
prompt Table : i$rm_sql_schema_members
rem -----------------------------------------------------+
rem
create table i$rm_sql_schema_members
(  schema_irid  number        not null
 , schema_ivid  number        not null
 , object_type  varchar2(30)  not null
 , object_irid  number        not null
 , object_ivid  number        not null
)
/

-- i$rm_sql_schema_members_pk
alter table i$rm_sql_schema_members add constraint i$rm_sql_schema_members_pk
            primary key (schema_irid, schema_ivid, object_irid, object_ivid)
/

-- i$rm_sql_schema_members_sch_fk
alter table i$rm_sql_schema_members add constraint i$rm_sql_schema_members_sch_fk
            foreign key (schema_irid, schema_ivid) references i$rm_sql_schema_versions on delete cascade
/

create index ind_sm_obj_irid_ivid on i$rm_sql_schema_members (object_irid, object_ivid)
/

rem -----------------------------------------------------+
prompt Table : i$rm_sql_tables
rem
rem Notes : name_column - use ref instead ?
rem         owned_by    - set to 'TRUE' if secondary, else null
rem TODO  : Move column pragma information to rm_sql_columns ?
rem         eg. name_column, logical_type_column etc
rem -----------------------------------------------------+
rem
create table i$rm_sql_tables (
  irid                           number                       not null
 ,ivid                           number                       not null
 ,table_name                     varchar2(30)                 not null
 ,populate_logical_model         varchar2(1)      default 'Y'
 ,gen_api                        varchar2(1)      default 'Y'
 ,gen_api_callout                varchar2(1)
 ,gen_api_callout_stub           varchar2(1)
 ,maintain_dependent_objects     varchar2(1)
 ,is_system                      varchar2(1)
 ,jr_type                        varchar2(10)
 ,pct_free                       number
 ,pct_used                       number
 ,ini_trans                      number
 ,max_trans                      number
 ,initial_extent                 number
 ,next_extent                    number
 ,min_extents                    number
 ,max_extents                    number
 ,pct_increase                   number
 ,comments                       varchar2(4000)
 ,name_column                    varchar2(35)
 ,logical_type_column            varchar2(35)
 ,date_created_column            varchar2(35)
 ,date_changed_column            varchar2(35)
 ,created_by_column              varchar2(35)
 ,changed_by_column              varchar2(35)
 ,notm_column                    varchar2(35)
 ,extension                      varchar2(35)
 ,owned_by                       varchar2(35)
 ,nls                            varchar2(1)
 ,id                             number(38)
 ,types                          number(38)
 ,number_of_times_modified       number(38)         default 0       not null
 ,date_created                   date               default sysdate not null
 ,created_by                     varchar2(240)      default user    not null
 ,date_changed                   date
 ,changed_by                     varchar2(240)
 ,install_rows                   varchar2(240)
 ,plural_name                    varchar2(40)
 ,product                        varchar2(240)
 ,short_name                     varchar2(20)
 ,source_ref                     number(38)
)
/

comment on column i$rm_sql_tables.owned_by is 'TRUE if secondary object, else null';

-- pk_rm_sql_tables
alter table i$rm_sql_tables add constraint pk_rm_sql_tables
primary key (irid, ivid)
/

-- uk_rm_sql_tables_id
alter table i$rm_sql_tables add constraint uk_rm_sql_tables_id
unique (id)
/

-- uk_rm_sql_tables_name
alter table i$rm_sql_tables add constraint uk_rm_sql_tables_name
unique (table_name)
/

-- rm_sql_tables_chk_type
alter table i$rm_sql_tables add constraint rm_sql_tables_chk_type
check (jr_type in ('SYSTEM', 'DESIGNER', 'USER'))
/

create or replace trigger i$rm_sql_tables_irid
 before insert on i$rm_sql_tables
 for each row
 begin
  if :new.irid is null then
    :new.irid := jr_util.get_new_irid;
  end if;
  :new.ivid := 1;
 end i$rm_sql_tables_irid;
/

rem ----------------------------------------------------------+
prompt Table : i$rm_sql_table_properties
rem This table is for storing "customized" properties of tables,
rem such as Display_Title
rem ----------------------------------------------------------+
create table i$rm_sql_table_properties (
  table_irid   number        not null
 ,table_ivid   number        not null
 ,property     varchar2(32)  not null
 ,value        varchar2(256)
)
/

-- i$rm_sql_table_properties_pk
alter table i$rm_sql_table_properties add constraint i$rm_sql_table_properties_pk
primary key (table_irid, table_ivid, property)
/

-- i$rm_sql_table_props_to_tables
alter table i$rm_sql_table_properties add constraint i$rm_sql_table_props_to_tables
foreign key (table_irid, table_ivid) references i$rm_sql_tables on delete cascade
/

rem ---------------------------------------------------------------------------+
prompt Table:  i$sdd_version_registry
rem    Notes:  Table of functions to call for type-specific
rem            version and copy operations
rem    Notes1: This table must be defined after i$rm_sql_tables because it has a
rem            foreign key which references it
rem    Todo:   name change to i$rm_sql_version_plugins
rem ---------------------------------------------------------------------------+
rem 

create table i$sdd_version_registry (
   table_irid           number        not null
  ,table_ivid           number        not null
  ,copy_function        varchar2(64)
  ,new_version_function varchar(64)
)
/

-- i$sdd_version_registry_pk
alter table i$sdd_version_registry add constraint i$sdd_version_registry_pk
primary key(table_irid, table_ivid)
/

-- i$sdd_version_registry_fk
alter table i$sdd_version_registry add constraint i$sdd_version_registry_fk
foreign key (table_irid, table_ivid) references i$rm_sql_tables on delete cascade deferrable
/

rem -----------------------------------------------------+
prompt Table : i$rm_sql_columns
rem
rem Notes : lob_index_name - use ref instead ?
rem -----------------------------------------------------+
rem
create table i$rm_sql_columns (
  table_irid                     number            not null
 ,table_ivid                     number            not null
 ,column_id                      number            not null
 ,column_name                    varchar2(30)      not null
 ,data_type                      varchar2(30)      not null
 ,data_length                    number
 ,data_precision                 number
 ,data_scale                     number
 ,nullable                       varchar2(1)
 ,default_length                 number
 ,data_default                   long
 ,num_distinct                   number
 ,low_value                      raw(32)
 ,high_value                     raw(32)
 ,comments                       varchar2(4000)
 ,lob_index_name                 varchar2(30)
 ,lob_chunk                      number
 ,lob_pctversion                 number
 ,lob_cache                      varchar2(3)
 ,lob_logging                    varchar2(3)
 ,lob_in_row                     varchar2(3)
 ,nls                            varchar2(1)
 ,id                             number(38)
 ,types                          number(38)
 ,in_table                       number(38)
 ,number_of_times_modified       number(38)        default 0        not null
 ,date_created                   date              default sysdate  not null
 ,created_by                     varchar2(240)     default user     not null
 ,date_changed                   date
 ,changed_by                     varchar2(240)
 ,map_constraint                 varchar2(240)
 ,plural_name                    varchar2(40)
 ,short_name                     varchar2(20)
 ,source_ref                     number(38)
 ,system_use                     number(38)
 ,temp                           number(38)
)
/

-- pk_rm_sql_columns
alter table i$rm_sql_columns add constraint pk_rm_sql_columns
primary key (table_irid, table_ivid, column_id)
/

-- uk_rm_sql_columns_id
alter table i$rm_sql_columns add constraint uk_rm_sql_columns_id
unique (id)
/

-- fk_rm_sql_columns_tab
alter table i$rm_sql_columns add constraint fk_rm_sql_columns_tab
foreign key (table_irid, table_ivid) references i$rm_sql_tables on delete cascade
/

create index ind_rm_sql_columns_tab_fk   on i$rm_sql_columns (table_irid)
/
create index ind_rm_sql_columns_name     on i$rm_sql_columns (column_name)
/
create index ind_rm_sql_columns_in_table on i$rm_sql_columns (in_table)
/

rem ----------------------------------------------------------+
prompt Table : i$rm_sql_column_properties
rem This table is for storing "customized" properties of columns
rem such as Display_Width
rem ----------------------------------------------------------+

create table i$rm_sql_column_properties (
  col_table_irid   number        not null
 ,col_table_ivid   number        not null
 ,col_column_id    number        not null
 ,property         varchar2(32)  not null
 ,value            varchar2(256)
)
/

-- i$rm_sql_column_properties_pk
alter table i$rm_sql_column_properties add constraint i$rm_sql_column_properties_pk
primary key (col_table_irid, col_table_ivid, col_column_id, property)
/

-- i$rm_sql_column_props_to_cols
alter table i$rm_sql_column_properties add constraint i$rm_sql_column_props_to_cols
foreign key (col_table_irid, col_table_ivid, col_column_id) references i$rm_sql_columns on delete cascade
/

rem --------------------------------------------------------------------------------------------------+
prompt Table : i$rm_sql_constraints
rem
rem Notes : r_constraint_name - use ref instead ?
rem CW 23-Sep-1999 Fix bug 734145, identify non-updateable PKs and UKs so registration does not need
rem                to generate trigger code to check child objects if they are updated
rem CW 30-Nov-1999 Fix bug 704362, support deferred constraint checking
rem                Add column oracle_constraint which records, for non-check constraints, whether or
rem                not the constraint is implemented as a real Oracle constraint (Y) or as a plsql
rem                check (N)
rem --------------------------------------------------------------------------------------------------+
rem
create table i$rm_sql_constraints (
  irid                           number                   not null
, ivid                           number                   not null
, table_irid                     number                   not null
, table_ivid                     number                   not null
, is_owning_fk                   varchar2(1)  default 'N' not null
, updateable                     varchar2(1)  default 'Y' not null
, oracle_constraint              varchar2(1)  default 'N' not null
, constraint_name                varchar2(30)
, constraint_type                varchar2(1)
, search_condition               long
, r_constraint_name              varchar2(30)
, delete_rule                    varchar2(9)
, deferrable                     varchar2(14)
)
/

comment on column i$rm_sql_constraints.is_owning_fk is 'Defines whether or not this fk describes an owning relationship';

-- i$rm_sql_constraints_pk
alter table i$rm_sql_constraints add constraint i$rm_sql_constraints_pk
primary key (irid, ivid)
/

-- i$rm_sql_constraints_tab_fk
alter table i$rm_sql_constraints add constraint i$rm_sql_constraints_tab_fk
foreign key (table_irid, table_ivid) references i$rm_sql_tables on delete cascade
/

create index ind_rm_sql_constraints_tab_fk on i$rm_sql_constraints (table_irid, table_ivid)
/

create unique index ind_rm_sql_con_name on i$rm_sql_constraints (constraint_name)
/

create index ind_rm_sql_con_r_name on i$rm_sql_constraints (r_constraint_name)
/

rem -----------------------------------------------------+
prompt Table : i$rm_sql_cons_columns
rem
rem Notes : column_name - use ref instead ?
rem -----------------------------------------------------+
rem
create table i$rm_sql_cons_columns (
  constraint_irid                number            not null
 ,constraint_ivid                number            not null
 ,column_name                    varchar2(30)      not null
 ,position                       number
)
/

-- i$rm_sql_cons_columns_pk
alter table i$rm_sql_cons_columns add constraint i$rm_sql_cons_columns_pk
primary key (constraint_irid, constraint_ivid, column_name)
/

-- i$rm_sql_cons_columns_cons_fk
alter table i$rm_sql_cons_columns add constraint i$rm_sql_cons_columns_cons_fk
foreign key (constraint_irid, constraint_ivid) references i$rm_sql_constraints on delete cascade
/

rem -----------------------------------------------------+
prompt Table : i$rm_sql_triggers
rem -----------------------------------------------------+
rem
create table i$rm_sql_triggers (
  irid                           number            not null
 ,ivid                           number            not null
 ,table_irid                     number            not null
 ,table_ivid                     number            not null
 ,trigger_name                   varchar2(30)      not null
 ,trigger_type                   varchar2(16)
 ,triggering_event               varchar2(26)
 ,referencing_names              varchar2(87)
 ,when_clause                    varchar2(4000)
 ,status                         varchar2(8)
 ,description                    varchar2(4000)
 ,trigger_body                   long
)
/

-- i$rm_sql_triggers_pk
alter table i$rm_sql_triggers add constraint i$rm_sql_triggers_pk
primary key (irid, ivid)
/

-- i$rm_sql_triggers_tab_fk
alter table i$rm_sql_triggers add constraint i$rm_sql_triggers_tab_fk
foreign key (table_irid, table_ivid) references i$rm_sql_tables on delete cascade
/

create index ind_rm_sql_triggers_tab_fk on i$rm_sql_triggers (table_irid, table_ivid)
/

rem -----------------------------------------------------+
prompt Table :i$rm_sql_indexes
rem
rem Notes : index_type      - use code ?
rem         uniqueness      - use flag ?
rem         include_column  - use ref ?
rem
rem Following columns removed from inside table definition, as they cause
rem problems for the RAU
-- ,logging                        varchar2(3)
-- ,degree                         varchar2(40)
-- ,instances                      varchar2(40)
-- ,partitioned                    varchar2(3)
rem -----------------------------------------------------+
rem
create table i$rm_sql_indexes (
  irid                           number            not null
 ,ivid                           number            not null
 ,table_irid                     number            not null
 ,table_ivid                     number            not null
 ,index_name                     varchar2(30)      not null
 ,index_type                     varchar2(12)      not null
 ,uniqueness                     varchar2(9)
 ,ini_trans                      number
 ,max_trans                      number
 ,initial_extent                 number
 ,next_extent                    number
 ,min_extents                    number
 ,max_extents                    number
 ,pct_increase                   number
 ,pct_threshold                  number
 ,include_column                 number
 ,freelists                      number
 ,freelist_groups                number
 ,pct_free                       number
)
/

-- i$rm_sql_indexes_pk
alter table i$rm_sql_indexes add constraint i$rm_sql_indexes_pk
primary key (irid, ivid)
/

-- i$rm_sql_indexes_tab_fk
alter table i$rm_sql_indexes add constraint i$rm_sql_indexes_tab_fk
foreign key (table_irid, table_ivid) references i$rm_sql_tables on delete cascade
/

create index ind_rm_sql_indexes_tab_fk on i$rm_sql_indexes (table_irid, table_ivid)
/

rem -----------------------------------------------------+
prompt Table :i$rm_sql_ind_columns
rem -----------------------------------------------------+
rem
create table i$rm_sql_ind_columns (
  index_irid                     number            not null
 ,index_ivid                     number            not null
 ,column_position                number            not null
 ,column_name                    varchar2(4000)
 ,column_length                  number            not null
)
/

-- i$rm_sql_ind_columns_pk
alter table i$rm_sql_ind_columns add constraint i$rm_sql_ind_columns_pk
primary key (index_irid, index_ivid, column_position)
/

-- i$rm_sql_ind_columns_index_fk
alter table i$rm_sql_ind_columns add constraint i$rm_sql_ind_columns_index_fk
foreign key (index_irid, index_ivid) references i$rm_sql_indexes on delete cascade
/

rem -----------------------------------------------------+
prompt Table :i$rm_sql_sequences
rem -----------------------------------------------------+
rem
create table i$rm_sql_sequences (
  irid                           number            not null
 ,ivid                           number            not null
 ,sequence_name                  varchar2(30)      not null 
 ,min_value                      number
 ,max_value                      number
 ,increment_by                   number            not null 
 ,cycle_flag                     varchar2(1)
 ,order_flag                     varchar2(1)
 ,cache_size                     number            not null 
 ,start_with                     number            not null
)
/

-- i$rm_sql_sequences_pk
alter table i$rm_sql_sequences add constraint i$rm_sql_sequences_pk
primary key (irid, ivid)
/

rem -----------------------------------------------------+
prompt Table :i$rm_sql_views
rem -----------------------------------------------------+
rem
create table i$rm_sql_views (
  irid                           number            not null
 ,ivid                           number            not null
 ,view_name                      varchar2(30)      not null
 ,text_length                    number
 ,text                           long
 ,type_text_length               number
 ,type_text                      varchar2(4000)
 ,oid_text_length                number
 ,oid_text                       varchar2(4000)
 ,view_type_owner                varchar2(30)
 ,view_type                      varchar2(30)
)
/

-- i$rm_sql_views_pk
alter table i$rm_sql_views add constraint i$rm_sql_views_pk
primary key (irid, ivid)
/

create unique index ind_rm_sql_view_name on i$rm_sql_views (view_name)
/

rem -----------------------------------------------------+
prompt Table :i$rm_sql_synonyms
rem -----------------------------------------------------+
rem
create table i$rm_sql_synonyms (
  irid                           number            not null
 ,ivid                           number            not null
 ,synonym_name                   varchar2(30)      not null
 ,object_name                    varchar2(30)      not null
)
/

-- i$rm_sql_synonyms_pk
alter table i$rm_sql_synonyms add constraint i$rm_sql_synonyms_pk
primary key (irid, ivid)
/

create unique index ind_rm_sql_syn_name on i$rm_sql_synonyms (synonym_name)
/

rem -----------------------------------------------------+
prompt Table :i$rm_sql_stored_objects
rem -----------------------------------------------------+
rem
create table i$rm_sql_stored_objects (
  irid                           number            not null
 ,ivid                           number            not null
 ,name                           varchar2(30)      not null
 ,type                           varchar2(12)      not null
)
/

-- i$rm_sql_stored_objects_pk
alter table i$rm_sql_stored_objects add constraint i$rm_sql_stored_objects_pk
primary key (irid, ivid)
/

rem -----------------------------------------------------+
prompt Table :i$rm_sql_source
rem -----------------------------------------------------+
rem
create table i$rm_sql_source (
  stored_object_irid             number            not null
 ,stored_object_ivid             number            not null
 ,line                           number            not null
 ,text                           varchar2(4000)
)
/

-- i$rm_sql_source_pk
alter table i$rm_sql_source add constraint i$rm_sql_source_pk
primary key (stored_object_irid, stored_object_ivid, line)
/

-- i$rm_sql_source_obj_fk
alter table i$rm_sql_source add constraint i$rm_sql_source_obj_fk
foreign key (stored_object_irid, stored_object_ivid) references i$rm_sql_stored_objects on delete cascade
/


rem -----------------------------------------------------+
prompt Table : rm_sql_tables_old
rem
rem Table to preserve guids from previous registration
rem -----------------------------------------------------+
create table rm_sql_tables_old
( irid                     number                       not null
, name                     varchar2(30)                 not null
)
/



