
rem -----------------------------------------------------+
prompt Creating version-resolved views
rem -----------------------------------------------------+

rem -----------------------------------------------------+
prompt rm_sql_users view
rem -----------------------------------------------------+
rem
create or replace view rm_sql_users (
  name
 ,active_workarea
) as
select
  name
 ,active_workarea
from i$rm_sql_users
/

rem --------------------------------------------------------------------------------+
prompt rm_sql_tables view
rem
rem Notes : CW 16-Jul-98
rem         Fix bug 701641
rem         Apparently diff/merge code has a requirement to be able to identify which
rem         column in a table is the object identifying column and which column is the
rem         reference column to an object (see Daniel Shimshoni for details).
rem         The following columns can be used for the following purpose :
rem repos_id_column                : The irid column of the table; used to be irid for
rem                                  primary objects and parent_irid for secondary objects.
rem                                  However, all objects now get an irid column (although
rem                                  they can still only be versioned if they are primary
rem                                  objects).
rem                                  Now some tables have an irid column and a parent_irid
rem                                  column we cannot use this single column to identify both,
rem                                  have therefore added extra metadata columns, see below.
rem                                  The value of this column is nearly always 'irid' except
rem                                  for some 'repos2' tables (see decode below).
rem repos_version_column           : Nearly always 'ivid' except some 'repos2' tables
rem repos_parent_id_column         : Secondary objects only, nearly always 'parent_irid'
rem                                  (null for primary objects)
rem repos_parent_version_column    : Secondary objects only, nearly always 'parent_ivid'
rem                                  (null for primary objects)
rem repos_reference_id_column      : The name of the id column used for referencing an object
rem                                  in object versions.
rem repos_reference_version_column : The name of the version column used for referencing an
rem                                  object in object versions.
rem
rem Note, primary objects are identified where 'owned_by' is null, else secondary object
rem Cannot determine by whether repos_id_column is irid because we now add irid columns
rem to all objects.
rem
rem Example :
rem  Table  repos_id  repos_version repos_parent repos_parent_  repos_id      repos_version
rem         _column   _column       _id_column   version_column _reference    _reference
rem                                                             _column       _column
rem
rem  sdd_folders (a typical primary object) :
rem         irid      ivid          null         null           null          null
rem
rem  sdd_folder_members (a secondary object with a reference to object_versions)
rem         irid      ivid          parent_irid  parent_ivid    member_object null
rem
rem CW 9-Oct-98, Fix bug 699269, parent_irid column being removed so meta data column
rem repos_parent_id_column is no longer needed
rem
rem Note, following columns added for backward compatibility with Repos 1
rem name
rem init_trans
rem storage_initial
rem storage_next
rem storage_minextents
rem storage_maxextents
rem storage_pctincrease
rem --------------------------------------------------------------------------------+
rem
create or replace view rm_sql_tables (
  irid           
 ,ivid           
 ,table_name
 ,name
 ,populate_logical_model
 ,gen_api
 ,gen_api_callout
 ,gen_api_callout_stub
 ,maintain_dependent_objects
 ,is_system      
 ,jr_type
 ,pct_free
 ,pct_used
 ,ini_trans
 ,init_trans
 ,max_trans
 ,initial_extent 
 ,next_extent    
 ,min_extents    
 ,max_extents    
 ,pct_increase   
 ,storage_initial
 ,storage_next
 ,storage_minextents
 ,storage_maxextents
 ,storage_pctincrease
 ,comments       
 ,name_column
 ,logical_type_column
 ,date_created_column
 ,date_changed_column
 ,created_by_column
 ,changed_by_column
 ,notm_column
 ,extension      
 ,owned_by       
 ,nls            
 ,repos_table_name
 ,repos_id_column
 ,repos_version_column
 ,repos_parent_version_column
 ,repos_reference_id_column
 ,repos_reference_version_column
 ,repos_nls_column
 ,id
 ,types
 ,number_of_times_modified
 ,date_created
 ,created_by
 ,date_changed
 ,changed_by
 ,install_rows
 ,plural_name
 ,product
 ,short_name
 ,source_ref
) as
select
  irid           
 ,ivid           
 ,table_name     
 ,table_name     
 ,populate_logical_model
 ,gen_api
 ,gen_api_callout
 ,gen_api_callout_stub
 ,maintain_dependent_objects
 ,is_system   
 ,jr_type
 ,pct_free
 ,pct_used
 ,ini_trans
 ,ini_trans
 ,max_trans
 ,initial_extent 
 ,next_extent    
 ,min_extents    
 ,max_extents    
 ,pct_increase   
 ,initial_extent 
 ,next_extent
 ,min_extents
 ,max_extents
 ,pct_increase
 ,comments
 ,name_column
 ,logical_type_column
 ,date_created_column
 ,date_changed_column
 ,created_by_column
 ,changed_by_column
 ,notm_column
 ,extension      
 ,owned_by       
 ,nls
 ,decode( nls
        , 'Y', 'L$'
        , 'I$') || table_name
 ,decode( table_name
        , 'RM_SQL_TABLE_PROPERTIES',   null
        , 'RM_SQL_COLUMNS',            null
        , 'RM_SQL_COLUMN_PROPERTIES',  null
        , 'RM_SQL_CONS_COLUMNS',       null
        , 'RM_SQL_IND_COLUMNS',        null
        , 'RM_SQL_SOURCE',             null
        , 'RM_SQL_USERS',              null
        , 'SDD_VERSION_REGISTRY',      null
        , 'SDD_CONFIGURATION_MEMBERS', null
        , 'IRID')
 ,decode( table_name
        , 'RM_SQL_TABLE_PROPERTIES',   null
        , 'RM_SQL_COLUMNS',            null
        , 'RM_SQL_COLUMN_PROPERTIES',  null
        , 'RM_SQL_CONS_COLUMNS',       null
        , 'RM_SQL_IND_COLUMNS',        null
        , 'RM_SQL_SOURCE',             null
        , 'RM_SQL_USERS',              null
        , 'SDD_VERSION_REGISTRY',      null
        , 'SDD_CONFIGURATION_MEMBERS', null
        , 'IVID')
 ,decode( table_name
        , 'RM_SQL_TABLE_PROPERTIES',   'TABLE_IVID'
        , 'RM_SQL_COLUMNS',            'TABLE_IVID'
        , 'RM_SQL_COLUMN_PROPERTIES',  'COL_TABLE_IVID'
        , 'RM_SQL_CONSTRAINTS',        'TABLE_IVID'
        , 'RM_SQL_CONS_COLUMNS',       'CONSTRAINT_IVID'
        , 'RM_SQL_TRIGGERS',           'TABLE_IVID'
        , 'RM_SQL_INDEXES',            'TABLE_IVID'
        , 'RM_SQL_IND_COLUMNS',        'INDEX_IVID'
        , 'RM_SQL_SOURCE',             'STORED_OBJECT_IVID'
        , 'SDD_VERSION_REGISTRY',      'TABLE_IVID'
        , 'SDD_CONFIGURATION_MEMBERS', 'CONFIG_IVID'
        , decode( owned_by
                , null, null
                , 'PARENT_IVID'))
 ,decode( table_name
        , 'SDD_CONFIGURATION_MEMBERS', 'OBJECT_IRID'
        , 'SDD_FOLDER_MEMBERS',        'MEMBER_OBJECT'
        , null)
 ,decode( table_name
        , 'SDD_CONFIGURATION_MEMBERS', 'OBJECT_IVID'
        , null)
 ,decode( nls
        , 'Y', 'LID'
        , null)
 ,id
 ,types
 ,number_of_times_modified
 ,date_created
 ,created_by
 ,date_changed
 ,changed_by
 ,install_rows
 ,plural_name
 ,product
 ,short_name
 ,source_ref
from  i$rm_sql_tables
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_table_properties view
rem -----------------------------------------------------+
rem

create or replace view rm_sql_table_properties (
   table_irid
  ,table_ivid
  ,property
  ,value
   ) as
select
   table_irid
  ,table_ivid
  ,property
  ,value
from  i$rm_sql_table_properties
where table_ivid=1
/

rem -----------------------------------------------------+
prompt sdd_version_registry view
rem -----------------------------------------------------+
rem

create or replace view sdd_version_registry (
   table_irid
  ,table_ivid
  ,copy_function
  ,new_version_function
) as
select
   table_irid
  ,table_ivid
  ,copy_function
  ,new_version_function
from  i$sdd_version_registry
where table_ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_columns view
rem
rem Note, following columns added for backward compatibility with Repos 1
rem seq
rem name
rem -----------------------------------------------------+
rem

create or replace view rm_sql_columns (
  table_irid      
 ,table_ivid      
 ,column_id       
 ,seq
 ,column_name     
 ,name
 ,data_type       
 ,data_length     
 ,data_precision  
 ,data_scale      
 ,nullable        
 ,default_length  
 ,data_default    
 ,num_distinct    
 ,low_value       
 ,high_value      
 ,comments        
 ,lob_index_name  
 ,lob_chunk       
 ,lob_pctversion  
 ,lob_cache       
 ,lob_logging     
 ,lob_in_row      
 ,nls
 ,id
 ,types
 ,in_table
 ,number_of_times_modified
 ,date_created
 ,created_by
 ,date_changed
 ,changed_by
 ,map_constraint
 ,plural_name
 ,short_name
 ,source_ref
 ,system_use
 ,temp
) as
select
  table_irid      
 ,table_ivid      
 ,column_id       
 ,column_id       
 ,column_name     
 ,column_name     
 ,data_type       
 ,data_length     
 ,data_precision  
 ,data_scale      
 ,nullable        
 ,default_length  
 ,data_default    
 ,num_distinct    
 ,low_value       
 ,high_value      
 ,comments        
 ,lob_index_name  
 ,lob_chunk       
 ,lob_pctversion  
 ,lob_cache       
 ,lob_logging     
 ,lob_in_row      
 ,nls
 ,id
 ,types
 ,in_table
 ,number_of_times_modified
 ,date_created
 ,created_by
 ,date_changed
 ,changed_by
 ,map_constraint
 ,plural_name
 ,short_name
 ,source_ref
 ,system_use
 ,temp
from i$rm_sql_columns
where table_ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_column_properties view
rem -----------------------------------------------------+
rem

create or replace view rm_sql_column_properties (
   col_table_irid
  ,col_table_ivid
  ,col_column_id
  ,property
  ,value
   ) as
select
   col_table_irid
  ,col_table_ivid
  ,col_column_id
  ,property
  ,value
from  i$rm_sql_column_properties
where col_table_ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_constraints view 
rem -----------------------------------------------------+
rem
create or replace view rm_sql_constraints (
  irid             
, ivid             
, table_irid       
, table_ivid       
, constraint_name  
, constraint_type  
, search_condition 
, r_constraint_name
, delete_rule      
, deferrable
, is_owning_fk
, updateable
, oracle_constraint
) as
select
  irid             
, ivid             
, table_irid       
, table_ivid       
, constraint_name  
, constraint_type  
, search_condition 
, r_constraint_name
, delete_rule      
, deferrable
, is_owning_fk
, updateable
, oracle_constraint
from i$rm_sql_constraints
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_cons_columns view 
rem -----------------------------------------------------+
rem
create or replace view rm_sql_cons_columns (
  constraint_irid
 ,constraint_ivid
 ,column_name    
 ,position       
) as
select
  constraint_irid
 ,constraint_ivid
 ,column_name    
 ,position       
from i$rm_sql_cons_columns
where constraint_ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_triggers view 
rem -----------------------------------------------------+
rem
create or replace view rm_sql_triggers (
  irid             
 ,ivid             
 ,table_irid       
 ,table_ivid       
 ,trigger_name     
 ,trigger_type     
 ,triggering_event 
 ,referencing_names
 ,when_clause      
 ,status           
 ,description      
 ,trigger_body     
) as
select
  irid             
 ,ivid             
 ,table_irid       
 ,table_ivid       
 ,trigger_name     
 ,trigger_type     
 ,triggering_event 
 ,referencing_names
 ,when_clause      
 ,status           
 ,description      
 ,trigger_body     
from i$rm_sql_triggers
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_indexes view 
rem -----------------------------------------------------+
rem
create or replace view rm_sql_indexes (
  irid           
 ,ivid           
 ,table_irid     
 ,table_ivid     
 ,index_name     
 ,index_type     
 ,uniqueness     
 ,ini_trans      
 ,max_trans      
 ,initial_extent 
 ,next_extent    
 ,min_extents    
 ,max_extents    
 ,pct_increase   
 ,pct_threshold  
 ,include_column 
 ,freelists      
 ,freelist_groups
 ,pct_free       
) as
select
  irid           
 ,ivid           
 ,table_irid     
 ,table_ivid     
 ,index_name     
 ,index_type     
 ,uniqueness     
 ,ini_trans      
 ,max_trans      
 ,initial_extent 
 ,next_extent    
 ,min_extents    
 ,max_extents    
 ,pct_increase   
 ,pct_threshold  
 ,include_column 
 ,freelists      
 ,freelist_groups
 ,pct_free       
from i$rm_sql_indexes
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_ind_columns view 
rem -----------------------------------------------------+
rem
create or replace view rm_sql_ind_columns (
  index_irid     
 ,index_ivid     
 ,column_position
 ,column_name    
 ,column_length  
) as
select
  index_irid     
 ,index_ivid     
 ,column_position
 ,column_name    
 ,column_length  
from i$rm_sql_ind_columns
where index_ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_sequences view
rem -----------------------------------------------------+
rem
create or replace view rm_sql_sequences (
  irid         
 ,ivid         
 ,sequence_name
 ,min_value    
 ,max_value    
 ,increment_by 
 ,cycle_flag   
 ,order_flag   
 ,cache_size   
 ,start_with   
) as
select
  irid         
 ,ivid         
 ,sequence_name
 ,min_value    
 ,max_value    
 ,increment_by 
 ,cycle_flag   
 ,order_flag   
 ,cache_size   
 ,start_with   
from i$rm_sql_sequences
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_views view
rem -----------------------------------------------------+
rem
create or replace view rm_sql_views (
  irid            
 ,ivid            
 ,view_name       
 ,text_length     
 ,text            
 ,type_text_length
 ,type_text       
 ,oid_text_length 
 ,oid_text        
 ,view_type_owner 
 ,view_type       
) as
select
  irid            
 ,ivid            
 ,view_name       
 ,text_length     
 ,text            
 ,type_text_length
 ,type_text       
 ,oid_text_length 
 ,oid_text        
 ,view_type_owner 
 ,view_type       
from i$rm_sql_views
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_synonyms view
rem -----------------------------------------------------+
rem
create or replace view rm_sql_synonyms (
  irid        
 ,ivid        
 ,synonym_name
 ,object_name 
) as
select
  irid        
 ,ivid        
 ,synonym_name
 ,object_name 
from i$rm_sql_synonyms
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_stored_objects view
rem -----------------------------------------------------+
rem
create or replace view rm_sql_stored_objects (
  irid
 ,ivid
 ,name
 ,type
) as
select
  irid
 ,ivid
 ,name
 ,type
from i$rm_sql_stored_objects
where ivid=1
/

rem -----------------------------------------------------+
prompt rm_sql_source view
rem -----------------------------------------------------+
rem
create or replace view rm_sql_source (
  stored_object_irid
 ,stored_object_ivid
 ,line              
 ,text              
) as
select
  stored_object_irid
 ,stored_object_ivid
 ,line              
 ,text              
from i$rm_sql_source
where stored_object_ivid=1
/


