prompt Package Header : jrm_pop

create or replace package jrm_pop is

  -- Types
  t_role  varchar2(20);
  c_role  varchar2(20);

  subtype table_role  is t_role%type;
  subtype column_role is c_role%type;

  -- Constants

  -- Table Roles
  PRIMARY   constant table_role := 'PRIMARY';
  SECONDARY constant table_role := 'SECONDARY';
  OPTIONAL  constant table_role := 'OPTIONAL';

  -- Column Roles
  NAME_COLUMN           constant column_role := 'NAME_COLUMN';
  LOGICAL_TYPE_COLUMN   constant column_role := 'LOGICAL_TYPE_COLUMN';
  NOTM_COLUMN           constant column_role := 'NOTM_COLUMN';
  DATE_CREATED_COLUMN   constant column_role := 'DATE_CREATED_COLUMN';
  DATE_MODIFIED_COLUMN  constant column_role := 'DATE_MODIFIED_COLUMN';
  CREATED_BY_COLUMN     constant column_role := 'CREATED_BY_COLUMN';
  MODIFIED_BY_COLUMN    constant column_role := 'MODIFIED_BY_COLUMN';
  NO_OFK                constant column_role := 'NO OFK';
  
  -- Cursor to select the tables for a particular type
  cursor cTablesForType(el_id IN NUMBER) is
  SELECT DISTINCT t.id id, t.name name
  FROM rm_sql_tables t, rm_sql_row_types r
  WHERE t.id = r.table_mapped
  AND r.id IN
  ( SELECT primary_row_type
    FROM rm_element_types 
    WHERE primary_row_type IS NOT NULL
    START WITH id = el_id
    CONNECT BY PRIOR id = supertypes
    UNION ALL
    SELECT e.primary_row_type
    FROM rm_interface_implementations i, rm_element_types e
    WHERE i.interface = el_id
    AND i.class_implementation = e.id
    AND e.primary_row_type IS NOT NULL
  )
  ORDER BY t.name;

  -- Select the instantiable sub types of this interface or class in a particular table
  cursor cInstantiableSubTypesInTable(el_id IN NUMBER, tab_id IN NUMBER) is
  SELECT e.id id, e.name name
  FROM   rm_element_types e, rm_sql_row_types r
  WHERE  e.primary_row_type = r.id
  AND    r.table_mapped = tab_id
  AND    e.id IN
  ( SELECT id
    FROM   rm_element_types
    WHERE  nvl(abstract, 'N') = 'N'
    START WITH id = el_id
    CONNECT BY PRIOR id = supertypes
    UNION ALL
    SELECT e.id
    FROM rm_element_types e, rm_interface_implementations i
    WHERE i.interface = el_id
    AND i.class_implementation = e.id
    AND nvl(e.abstract, 'N') = 'N'
  )
  ORDER BY 1;

  -- Select the instantiable sub types of this interface or class
  cursor cInstantiableSubTypes(el_id IN NUMBER) is
  SELECT e.id id, e.name name
  FROM rm_element_types e
  WHERE e.id IN
  ( SELECT id
    FROM rm_element_types
    WHERE nvl(abstract, 'N') = 'N'
    START WITH id = el_id
    CONNECT BY PRIOR id = supertypes
    UNION ALL
    SELECT e.id
    FROM rm_element_types e, rm_interface_implementations i
    WHERE i.interface = el_id
    AND i.class_implementation = e.id
    AND nvl(e.abstract, 'N') = 'N'
  )
  ORDER BY 1;

  -- Select any parent associations from an element
  cursor cOtherAssociations(el_id IN NUMBER) is
  SELECT 
      pThis.name this_name, lThis.min_cardinality this_min_card, lThis.max_cardinality this_max_card
    , lThis.aggregation this_aggregation, lThis.transferable this_transferable
    , pOther.name other_name, lOther.min_cardinality other_min_card, eOther.name other_type
    , lOther.max_cardinality other_max_card, lOther.aggregation other_aggregation
    , lOther.transferable other_transferable, cOther.name other_col, lOther.defined_against other_id, lThis.defined_against this_id
  FROM 
      rm_properties pThis, rm_properties pOther
    , rm_link_properties lThis, rm_link_properties lOther
    , rm_property_maps mThis, rm_property_maps mOther
    , rm_sql_columns cOther, rm_element_types eOther
  WHERE mThis.context = el_id
    AND mThis.property = pThis.id AND pThis.id = lThis.id 
    AND lThis.link_type = lOther.link_type  AND lThis.id <> lOther.id
    AND lOther.id = pOther.id  AND pOther.id = mOther.property 
    AND pOther.defined_against = mOther.context AND pOther.defined_against = eOther.id
    AND mOther.in_column = cOther.id
    AND pOther.is_stored = 'Y'
  ORDER BY pThis.defined_against, pThis.id;
 
  -- Select any child associations to an element
  cursor cChildAssociations(el_id IN NUMBER) is
  SELECT 
      pThis.name this_name, lThis.min_cardinality this_min_card 
    , lThis.max_cardinality this_max_card, lThis.aggregation this_aggregation
    , pOther.name other_name, lOther.min_cardinality other_min_card
    , lOther.max_cardinality other_max_card, lOther.aggregation other_aggregation
    , cThis.name col_name, lThis.defined_against defined_against, lOther.defined_against other_defined_against
  FROM 
      rm_properties pThis, rm_properties pOther
    , rm_link_properties lThis, rm_link_properties lOther
    , rm_property_maps mThis, rm_property_maps mOther
    , rm_sql_columns cThis
  WHERE mThis.context = el_id
    AND mThis.property = pThis.id AND pThis.id = lThis.id 
    AND lThis.link_type = lOther.link_type  AND lThis.id <> lOther.id
    AND mOther.property = pOther.id AND pOther.id = lOther.id
    AND pOther.defined_against = mOther.context
    AND mThis.in_column = cThis.id
    AND pThis.is_stored = 'Y'
  ORDER BY pThis.id;   


  -- Initialise rm population run
  procedure init(product VARCHAR2, dump_output BOOLEAN);
  procedure finish;
  function  get_product return varchar2;
  pragma restrict_references(get_product, wnps, wnds);

  -- Diagnostics support
  function get_metadata_error return BOOLEAN;
  procedure show_errors;

  -- Generation methods
  procedure drop_all;
  procedure drop_schema;
  procedure drop_existing_metadata;

  procedure create_table   ( table_name       varchar2
                           , tab_role         table_role  default PRIMARY
                           );

  ------------------------------------------------------------------
  -- Possible column roles are identified under 'constants' above
  -- If columns fulfilling these roles are not provided then
  -- default repos columns will be created.
  -- If a name column is not provided but a column called 'name' is
  -- created, this will be used as the name column
  ------------------------------------------------------------------
  procedure create_column  ( name           varchar2
                           , datatype       varchar2
                           , length         number
                           , precision      number
                           , scale          number
                           , nullable       varchar2
                           , default_value  varchar2
                           , col_role       column_role default null
                           );

  procedure end_create_table (table_name  varchar2);

  procedure create_constraint ( name              varchar2
                              , constraint_type   varchar2
                              , search_condition  long
                              , r_constraint_name varchar2
                              , delete_rule       varchar2
                              , deferrable        varchar2
                              , updateable        varchar2  default 'Y'
                              , is_owning         varchar2  default 'N'
                              );

  procedure create_check_constraint ( name              varchar2
                                    , search_condition  long
                                    );

  procedure create_key_column (name varchar2);

  procedure create_type_id (type_name  varchar2, type_id number default null);

  procedure create_data_type ( data_type_name   varchar2
                             , length           number
                             , precision        number
                             , scale            number
                             , sys_data_type    varchar2
                             , super_type_name  varchar2
                             , super_length     number
                             , super_precision  number
                             , super_scale      number
                             );
  procedure create_data_type_value ( value_identifier varchar2
                                   , value            varchar2
                                   , value_seq        number
                                   );

  procedure create_property_range(lovalue number, hivalue number);

  procedure create_type ( type_name       varchar2
                        , super_type_name varchar2
                        ,	table_name      varchar2
                        , short_name      varchar2
                        ,	java_full_name  varchar2
                        , instantiable    varchar2
                        ,	interface       varchar2
                        , display_name    varchar2
                        , is_primary      varchar2
                        , owned_by        varchar2  default null
                        );

  procedure add_interface_implementation(interface_name VARCHAR2);

  procedure create_property ( property_name varchar2
                            , attr_id       number
                            , column_name   varchar2
                            , default_value varchar2
                            , java_datatype varchar2
                            , datatype      varchar2
                            , length        number
                            , precision     number
                            , scale         number
                            , is_stored     varchar2
                            , mandatory     varchar2
                            , maintained    varchar2
                            , update_mode   varchar2
                            , display_name  varchar2
                            , desc_seq      number default null
                            );

  procedure create_association ( property_name varchar2
                               , column_name varchar2
                               , this_id      number
                               , other_id     number
                               , min_card     number
                               , max_card     number
                               , is_stored    varchar2
                               , mandatory    varchar2
                               , navigable    varchar2
                               , transferable varchar2
                               , ordered_set  varchar2
                               , aggregation  varchar2
                               , maintained   varchar2
                               , update_mode  varchar2
                               , display_name varchar2
                               , is_owning_assoc varchar2
                               );

  procedure create_type_constraint ( constraint_name VARCHAR2, 
                                     enabled VARCHAR2);
                                   
  procedure create_type_constraint_comp ( property_name VARCHAR2 );                                   

  function find_property ( name VARCHAR2, 
                           el_id NUMBER  ) return NUMBER;
  function find_message_usage ( nls_utility VARCHAR2, 
                                nls_code NUMBER ) return NUMBER;


  procedure add_default_order_attribute(attr_id NUMBER);
  procedure add_default_order_sequence(seq_col_name VARCHAR2);
  procedure populate_super_maps;

  procedure create_views;
  procedure create_packages;
  procedure create_triggers;

  -- Helper methods (these are made public for their potential re-use elswhere)
  procedure add_line(stmt in out dbms_sql.varchar2s, line in out BINARY_INTEGER, str in varchar2);
  procedure dump_stmt(stmt IN dbms_sql.varchar2s);
  
  procedure delete_data(prod varchar2);

end jrm_pop;
/