prompt Package Body: jr_object

create or replace package body jr_object is

  -- Private data

  type table_record is record ( irid          number
                              , ivid          number
                              , table_name    varchar2(35)
                              );
  type table_list is table of table_record;
  all_owned_tables table_list := table_list();
  all_owned_tables_idx pls_integer := 0;


  -- check_pragmas
  --
  -- Owning objects of a table are specified by a repos pragma defined against the table.
  -- The owner is specified by the foreign key name to the owner from the table.
  -- The pragma is of the form owning_object_via_fk=<foreign key name> (or keyword abbreviation ofk)
  -- Registration used to use the existence of (a single) cascade delete foreign key to indicate
  -- ownership. It still assumes that owning foreign keys are also cascade delete.
  --
  -- This procedure reads all foreign key names specified as owning foreign keys and lists all
  -- those which are not cascade delete
  --
  -- NB. In sqlplus, to see the output from this procedure, type 'set serveroutput on'
  --
  procedure check_pragmas (schema_name varchar2) is

    cursor tab_cur is
      select 
        t.table_name
       ,c.comments
      from   all_tables t, all_tab_comments c
      where  t.owner      = upper(check_pragmas.schema_name)
      and    t.owner      = c.owner(+)
      and    t.table_name = c.table_name(+)
      and not exists (select null
                      from   i$rm_sql_tables rt
                      where  rt.table_name = t.table_name);


    cursor fk_con_cur (table_name varchar2) is
      select
        decode(generated, 'USER NAME', constraint_name) constraint_name
       ,constraint_type
       ,search_condition
       ,r_constraint_name
       ,delete_rule
       ,deferrable
      from   all_constraints
      where  owner           = upper(check_pragmas.schema_name)
      and    table_name      = fk_con_cur.table_name
      and    constraint_type = 'R'
      and    status          = 'ENABLED';

    tab_keywords           jr_comment.tvp_list;
    owned_by               varchar2(35);
    is_owning_fk           varchar2(1);

    procedure init_variables is
    begin
      owned_by             := null;
      is_owning_fk         := 'N';
    end init_variables;


  begin
    
    dbms_output.put_line('Starting to check that ofks are also cascade delete...');

    for tab_rec in tab_cur loop

      init_variables;

      tab_keywords := jr_comment.get_keywords(tab_rec.comments);

      if (jr_comment.lookup(tab_keywords, 'OWNING_OBJECT_VIA_FK', owned_by) or
          jr_comment.lookup(tab_keywords, 'OFK', owned_by) or
          jr_comment.lookup(tab_keywords, 'OPTIONAL_OWNING_OBJECT_VIA_FK', owned_by) or
          jr_comment.lookup(tab_keywords, 'OPTOFK', owned_by)
         )
      then
        owned_by := 'TRUE';
      else
        owned_by := null;
      end if;

      for fk_con_rec in fk_con_cur (tab_rec.table_name) loop

        -- If this constraint is a foreign key constraint, is it an owning foreign key constraint ?
        -- Loop through the ofk pragmas looking for this foreign key

        is_owning_fk := 'N';
        if fk_con_rec.constraint_type = 'R' and tab_keywords is not null then
          for i in 1..tab_keywords.count loop
            if tab_keywords(i).tag is not null and
               (upper(tab_keywords(i).tag) in ( 'OWNING_OBJECT_VIA_FK'         , 'OFK') or
                upper(tab_keywords(i).tag) in ( 'OPTIONAL_OWNING_OBJECT_VIA_FK', 'OPTOFK')) and
               upper(tab_keywords(i).value) = fk_con_rec.constraint_name
            then
              if fk_con_rec.delete_rule = 'CASCADE' then
                is_owning_fk := 'Y';
              else
                is_owning_fk := 'N';
                dbms_output.put_line('Owning foreign key '||tab_keywords(i).value||' must be specified as cascade delete');
              end if;
            end if;
          end loop;
        end if;

      end loop;

    end loop;

    dbms_output.put_line('Finished checking that ofks are also cascade delete');

  end check_pragmas;

  procedure tab_init is
  begin
    all_owned_tables.delete;
    all_owned_tables_idx := 0;
  end tab_init;

  --
  -- Procedure : get_all_owned_tables
  --
  procedure get_all_owned_tables (table_irid number, table_ivid number, indent number) is
    cursor ownee_cur is
      select t.irid, t.ivid, t.table_name
      from   i$rm_sql_constraints fk, i$rm_sql_constraints pk, i$rm_sql_tables t
      where  pk.table_irid         = get_all_owned_tables.table_irid
      and    pk.table_ivid         = get_all_owned_tables.table_ivid
      and    fk.r_constraint_name  = pk.constraint_name
      and    fk.is_owning_fk      != 'N'
      and    fk.table_irid         = t.irid
      and    fk.table_ivid         = t.ivid
      order by t.table_name
      ;

    function in_owned_table_list (a_table_list table_list, a_name varchar2) return boolean is
    begin
      for i in 1..a_table_list.count loop
        if a_table_list(i).table_name = a_name then
          return TRUE;
        end if;
      end loop;

      return FALSE;
    end in_owned_table_list;

  begin

    for ownee_rec in ownee_cur loop

      if not in_owned_table_list(all_owned_tables, ownee_rec.table_name) then
        dbms_output.put_line(lpad('-', indent, '-') || ownee_rec.table_name);

        all_owned_tables_idx := all_owned_tables_idx + 1;
        all_owned_tables.extend();
        all_owned_tables(all_owned_tables_idx).irid          := ownee_rec.irid;
        all_owned_tables(all_owned_tables_idx).ivid          := ownee_rec.ivid;
        all_owned_tables(all_owned_tables_idx).table_name    := ownee_rec.table_name;

        get_all_owned_tables (ownee_rec.irid, ownee_rec.ivid, indent + 1); 
      end if;

    end loop;
  end get_all_owned_tables;


  --
  -- Procedure : report
  --
  -- report on object definitions (ie. an object and everything it owns)
  -- split into 2 procedures due to limitations of dbms_output buffer
  --
  procedure report_upto_m is

    cursor top_tab_cur is
      select tab.irid
           , tab.ivid
           , tab.table_name
      from   rm_sql_tables tab
      where  tab.table_name like 'SDD%'
      and    tab.table_name <= 'SDD_M%'
      and    not exists
              (select null
               from   rm_sql_constraints fk
               where  fk.table_irid      = tab.irid
               and    fk.constraint_type = 'R'
               and    fk.is_owning_fk   != 'N'
              )
      order by tab.table_name
      ;

  begin
    for top_tab_rec in top_tab_cur loop
      tab_init;
      dbms_output.put_line(top_tab_rec.table_name);
      get_all_owned_tables(top_tab_rec.irid, top_tab_rec.ivid, 1);
    end loop;
  end report_upto_m;

  procedure report_from_m is

    cursor top_tab_cur is
      select tab.irid
           , tab.ivid
           , tab.table_name
      from   rm_sql_tables tab
      where  tab.table_name like 'SDD%'
      and    tab.table_name > 'SDD_M%'
      and    not exists
              (select null
               from   rm_sql_constraints fk
               where  fk.table_irid      = tab.irid
               and    fk.constraint_type = 'R'
               and    fk.is_owning_fk   != 'N'
              )
      order by tab.table_name
      ;

  begin
    for top_tab_rec in top_tab_cur loop
      tab_init;
      dbms_output.put_line(top_tab_rec.table_name);
      get_all_owned_tables(top_tab_rec.irid, top_tab_rec.ivid, 1);
    end loop;
  end report_from_m;

  procedure report (table_name varchar2) is

    cursor top_tab_cur is
      select tab.irid
           , tab.ivid
           , tab.table_name
      from   rm_sql_tables tab
      where  tab.table_name = upper(report.table_name)
      ;

  begin
    for top_tab_rec in top_tab_cur loop
      tab_init;
      dbms_output.put_line(top_tab_rec.table_name);
      get_all_owned_tables(top_tab_rec.irid, top_tab_rec.ivid, 1);
    end loop;
  end report;

end jr_object;
/