--------------------------------------------------------
--  DDL for Package Body UPDATE_CASCADE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "UPDATE_CASCADE" 
as

type cnameArray is table of user_cons_columns.column_name%type
                   index by binary_integer;

sql_stmt           varchar2(32767);
use_dbms_output    boolean default FALSE;
preserve_rowid     boolean default TRUE;

function q( s in varchar2 ) return varchar2
is
begin
    return '"'||s||'"';
end q;

function pkg_name( s in varchar2 ) return varchar2
is
begin
    return q( 'uc_' || s );
end pkg_name;


function view_name( s in varchar2 ) return varchar2
is
begin
    return q( 'u' || s || 'v' );
end view_name;

function trigger_name( s in varchar2, s2 in varchar2 ) return varchar2
is
begin
    return q( 'u' || s || s2 );
end trigger_name;


function strip( s in varchar2 ) return varchar2
is
begin
    return ltrim(rtrim(s));
end strip;

procedure add( s in varchar2 )
is
begin
    if ( use_dbms_output ) then
        dbms_output.put_line( '    ' || s );
    else
        dbms_output.put_line( '    ' || s );
        sql_stmt := sql_stmt || chr(10) || s;
    end if;
end add;


procedure execute_immediate
as
    exec_cursor     integer default dbms_sql.open_cursor;
    rows_processed  number  default 0;
begin
    if ( use_dbms_output ) then
        dbms_output.put_line( '/' );
    else
        dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
        rows_processed := dbms_sql.execute(exec_cursor);
        dbms_output.put_line( '/' );
    end if;
    dbms_sql.close_cursor( exec_cursor );
    sql_stmt := NULL;
exception
    when others then
      dbms_output.put_line('Error while executing or parsing this statement:' || chr(10));
      dbms_output.put_line(sql_stmt);
      dbms_output.put_line(SQLERRM(SQLCODE())||chr(10));
      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      sql_stmt := NULL;
      raise;
end;


procedure get_pkey_names
( p_table_name      in out user_constraints.table_name%type,
  p_pkey_names         out cnameArray,
  p_constraint_name in out user_constraints.constraint_name%type )
is
begin
  select table_name, constraint_name
    into p_table_name, p_constraint_name
    from user_constraints
   where ( table_name = p_table_name
           or table_name = upper(p_table_name) )
          and constraint_type = 'P' ;
  begin
    for x in ( select column_name , position
                 from user_cons_columns
                where constraint_name = p_constraint_name
                order by position ) loop
        p_pkey_names( x.position ) := x.column_name;
    end loop;

  end;

exception
    when no_data_found then
      Null;
end get_pkey_names;


procedure write_spec( p_table_name in user_constraints.table_name%type,
                      p_pkey_names in cnameArray )
is
    l_comma     char(1) default ' ';
begin
    add( 'create or replace package' || pkg_name(p_table_name) );
    add( 'as' );
    add( '--' );
    add( '    stackLevel number default 0;' );

    add( '    type rowCnt_type is table of number index by binary_integer;' );

    add( '    rowCnt_start rowCnt_type;' );
    add( '    rowCnt_end rowCnt_type;' );
    add( '--' );
    add( '    inRowidKeeper boolean default FALSE;' );
    add( '--' );

    for i in 1 .. 16 loop
        begin
            add( '    type C' || strip(i) || '_type is table of ' ||
                    q(p_table_name) || '.' || q(p_pkey_names(i)) ||
                 '%type index by binary_integer;' );
            add( '--' );
            add( '    empty_C' || strip(i) || ' C' || strip(i) || '_type;' );
            add( '    old_C' || strip(i) || '   C' || strip(i) || '_type;' );
            add( '    new_C' || strip(i) || '   C' || strip(i) || '_type;' );
            add( '--' );
        exception
            when no_data_found then exit;
        end;
    end loop;

    add( '--' );
    add( '    procedure stack_push;' );
    add( '--' );
    add( '    procedure do_cascade;' );
    add( '--' );

    add( '    procedure add_entry' );
    add( '    ( ' );
    for i in 1 .. 16 loop
        begin
            add( '        ' || l_comma || 'p_old_C' || strip(i) || ' in ' ||
                  q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
            l_comma := ',';
            add( '        ,p_new_C' || strip(i) || ' in out ' ||
                  q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
        exception
            when no_data_found then exit;
        end;
    end loop;
    add( '     );' );
    add( '--' );
    add( 'end ' || pkg_name(p_table_name) || ';' );

end write_spec;


procedure write_body
( p_table_name         in user_constraints.table_name%type,
  p_pkey_names         in cnameArray,
  p_constraint_name    in user_constraints.constraint_name%type )
is
    l_col_cnt         number default 0;
    l_comma         char(1) default ' ';
    l_pkey_str      varchar2(32000);
    l_pkey_name_str varchar2(32000);
    l_other_col_str varchar2(32000);
begin
    add( 'create or replace package body ' || pkg_name(p_table_name) );
    add( 'as' );
    add( '--' );
    add( '    procedure stack_push' );
    add( '    is' );
    add( '    begin' );
    add( '--' );
    add( '        stackLevel := stackLevel + 1;' );
    add( '--' );
    add( '        if (stackLevel = 1) then' );
    add( '          rowCnt_start(stackLevel) := 0;' );
    add( '          rowCnt_end(stackLevel) := 0;' );
    add( '        else' );
    add( '          rowCnt_start(stackLevel) := rowCnt_end(stackLevel-1);' );
    add( '          rowCnt_end(stackLevel) := rowCnt_end(stackLevel-1);' );
    add( '        end if;' );
    add( '--' );
    add( '    end stack_push;' );
    add( '--' );
    add( '    procedure stack_pop' );
    add( '    is' );
    add( '    begin' );
    add( '--' );
    add( '        if ( inRowidKeeper ) then return; end if;' );
    add( '--' );
    add( '        if ( stackLevel = 0 ) then return; end if;' );
    add( '--' );
    add( '        rowCnt_start(stackLevel) := 0;' );
    add( '        rowCnt_end(stackLevel) := 0;' );
    add( '        stackLevel := stackLevel - 1;' );
    add( '--' );
    add( '        if (stackLevel = 0) then' );
    for i in 1 .. 16 loop
        begin
           if (p_pkey_names(i) = p_pkey_names(i)) then
              l_col_cnt := l_col_cnt+1;
           end if;
           add( '            old_C' || strip(i) || ' := empty_C' || strip(i) || ';' );
           add( '            new_C' || strip(i) || ' := empty_C' || strip(i) || ';' );
        exception
           when no_data_found then exit;
        end;
    end loop;
    add( '        end if;' );
    add( '    end stack_pop;' );
    add( '--' );

    add( '    procedure add_entry' );
    add( '    ( ' );
    for i in 1 .. 16 loop
        begin
            add( '            ' || l_comma || 'p_old_C' || strip(i) || ' in ' ||
                q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type' );
            l_comma := ',';
            add( '            ,p_new_C' || strip(i) || ' in out ' ||
                  q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type' );
        exception
            when no_data_found then exit;
        end;
    end loop;
    add( '     )' );
    add( '    is' );
    add( '    begin' );
    add( '--' );
    add( '        if ( inRowidKeeper ) then return; end if;' );
    add( '--' );
    add( '        if ( stackLevel = 0 ) then return; end if;' );
    add( '--' );
    add( '        if ( ' );
    for i in 1 .. l_col_cnt loop
        if ( i <> 1 ) then
            add( '            or' );
        end if;
        add( '             p_old_C' || strip(i) || ' <> ' ||
             'p_new_C' || strip(i) );
    end loop;
    add( '         ) then' );
    add( '        rowCnt_end(stackLevel) := rowCnt_end(stackLevel) + 1;' );

    for i in 1 .. l_col_cnt loop
        add( '        old_C' || strip(i) ||
             '( rowCnt_end(stackLevel) ) := p_old_C' || strip(i) || ';' );
        add( '        new_C' || strip(i) ||
             '( rowCnt_end(stackLevel) ) := p_new_C' || strip(i) || ';' );
        add( '        p_new_C' || strip(i) ||
             ' := p_old_C' || strip(i) || ';' );
    end loop;

    add( '        end if;' );
    add( '    end add_entry;' );

    add( '--' );

    l_comma := ' ';
    for i in 1 .. l_col_cnt loop
        l_pkey_str      := l_pkey_str||l_comma||'$$_C' || strip(i) || '(i)';
        l_pkey_name_str := l_pkey_name_str || l_comma || q(p_pkey_names(i));
        l_comma := ',';
    end loop;

    for x in ( select column_name
                 from user_tab_columns
                where table_name = p_table_name
                  and column_name not in
                    ( select column_name
                        from user_cons_columns
                       where constraint_name = p_constraint_name )
                order by column_id )
    loop
        l_other_col_str := l_other_col_str || ',' || q(x.column_name);
    end loop;

    add( '    procedure do_cascade' );
    add( '    is' );
    add( '    begin' );
    add( '--' );
    add( '        if ( inRowidKeeper ) then return; end if;' );
    add( '--' );
    add( '        if ( stackLevel = 0 ) then return; end if;' );
    add( '--' );
    add( '        for i in 1+rowCnt_start(stackLevel) .. rowCnt_end(stackLevel) loop' );
    add( '            insert into ' || q(p_table_name) || ' (' );
    add( '            ' || l_pkey_name_str );
    add( '            ' || l_other_col_str || ') select');
    add( '            ' || replace( l_pkey_str, '$$', 'new' ) );
    add( '            ' || l_other_col_str );

    add( '            from ' || q(p_table_name) || ' a' );
    add( '            where (' || l_pkey_name_str || ' ) =' );
    add( '                  ( select ' || replace(l_pkey_str,'$$','old') );
    add( '                      from dual );' );
    add( '--' );
    if ( preserve_rowid ) then
       add( '            inRowidKeeper := TRUE;' );
       add( '            update ' || q(p_table_name) || ' set' );
       add( '            ( ' || l_pkey_name_str || ' ) =' );
       add( '            ( select' );
       for i in 1 .. l_col_cnt loop
          if ( i <> 1 ) then add( '                  ,' ); end if;
          add( '                 decode( ' || q(p_pkey_names(i)) ||
          replace(', old_c$$(i), new_c$$(i), old_c$$(i) )', '$$',strip(i)) );
       end loop;
       add( '              from dual )' );
       add( '            where ( ' || l_pkey_name_str || ' ) =' );
       add( '                  ( select ' || replace(l_pkey_str,'$$','new') );
       add( '                      from dual )' );
       add( '               or ( ' || l_pkey_name_str || ' ) =' );
       add( '                  ( select ' || replace(l_pkey_str,'$$','old') );
       add( '                      from dual );' );
       add( '            inRowidKeeper := FALSE;' );
    end if;

    for x in ( select table_name, constraint_name
                 from user_constraints
                where r_constraint_name = p_constraint_name
                  and constraint_type = 'R' ) loop

        l_comma := ' ';
        l_other_col_str := '';
        for y in ( select column_name
                     from user_cons_columns
                    where constraint_name = x.constraint_name
                    order by position ) loop
            l_other_col_str := l_other_col_str || l_comma || q(y.column_name);
            l_comma := ',';
        end loop;

        add( '--' );
        add( '            update ' || q( x.table_name ) || ' set ');
        add( '            ( ' || l_other_col_str || ' ) = ' );
        add( '            ( select  ' || replace( l_pkey_str, '$$', 'new' ) );
        add( '                from dual )' );
        add( '            where ( ' || l_other_col_str || ' ) = ' );
        add( '                  ( select' ||
                                replace( l_pkey_str, '$$', 'old' ) );
        add( '                    from dual );' );

    end loop;

    add( '--' );
    add( '            delete from ' || q(p_table_name)  );
    add( '             where ( ' || l_pkey_name_str || ' ) = ' );
    add( '                   ( select ' ||
                                replace( l_pkey_str, '$$', 'old' ) );
    add( '                       from dual);' );
    add( '        end loop;' );

    add( '--' );
    add( '        stack_pop;' );
    add( '   exception' );
    add( '       when others then' );
    add( '          inRowidKeeper := FALSE;' );
    add( '          stack_pop;' );
    add( '          raise;' );
    add( '    end do_cascade;' );
    add( '--' );
    add( 'end ' || pkg_name( p_table_name ) || ';' );

end write_body;


procedure write_bu_trigger( p_table_name in user_constraints.table_name%type,
                            p_pkey_names in cnameArray )
is
    l_comma char(1) default ' ';
begin
    add( 'create or replace trigger ' || trigger_name( p_table_name, '1' ) );
    add( 'before update of ' );
    for i in 1 .. 16 loop
        begin
            add( '   ' || l_comma || q(p_pkey_names(i)) );
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;
    add( 'on ' || q(p_table_name) );
    add( 'begin ' || pkg_name(p_table_name) || '.stack_push; end;' );
end write_bu_trigger;


procedure write_bufer_trigger
( p_table_name in user_constraints.table_name%type,
  p_pkey_names in cnameArray )
is
    l_comma   char(1) default ' ';
begin
    add( 'create or replace trigger '||trigger_name( p_table_name, '2' ) );
    add( 'before update of ' );

    for i in 1 .. 16 loop
        begin
            add( '   ' || l_comma || q(p_pkey_names(i)) );
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;
    add( 'on ' || q(p_table_name) );
    add( 'for each row' );
    add( 'begin ' );
    add( '   ' || pkg_name(p_table_name) || '.add_entry(' );

    l_comma := ' ';
    for i in 1 .. 16 loop
        begin
            add( '      ' || l_comma || ':old.' || q(p_pkey_names(i)) );
            add( '      ,:new.' || q(p_pkey_names(i)) );
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;
    add( '      );' );
    add( 'end;' );
end write_bufer_trigger;

procedure write_au_trigger( p_table_name in user_constraints.table_name%type,
                            p_pkey_names in cnameArray )
is
    l_comma  char(1) default ' ';
begin
    add( 'create or replace trigger ' || trigger_name( p_table_name, '3' ) );
    add( 'after update of ' );
    for i in 1 .. 16 loop
        begin
            add( '   ' || l_comma || q(p_pkey_names(i)) );
            l_comma := ',';
        exception
            when no_data_found then exit;
        end;
    end loop;
    add( 'on ' || q(p_table_name) );
    add( 'begin ' || pkg_name(p_table_name) || '.do_cascade; end;' );
end write_au_trigger;


procedure on_table
  ( p_table_name          in     dt415.di000216%type,
    p_table_id            in     dt415.di000265%type )
is
    l_table_name                 user_constraints.table_name%type default p_table_name;
    l_constraint_name            user_constraints.constraint_name%type;
    l_pkey_names                 cnameArray;
begin
    dbms_output.put_line(chr(10)||chr(10)||'-- adding UC packages for DT #'||p_table_id||' a.k.a. '||p_table_name||chr(10));

    get_pkey_names(l_table_name, l_pkey_names, l_constraint_name);

    sql_stmt := NULL;
    write_spec( l_table_name, l_pkey_names );
    execute_immediate;
    write_body( l_table_name, l_pkey_names, l_constraint_name );
    execute_immediate;
    write_bu_trigger( l_table_name, l_pkey_names );
    execute_immediate;
    write_bufer_trigger( l_table_name, l_pkey_names );
    execute_immediate;
    write_au_trigger( l_table_name, l_pkey_names );
    execute_immediate;

end on_table;


procedure on_all_tables
  ( p_preserve_rowid      in     boolean default TRUE,
    p_use_dbms_output     in     boolean default FALSE )
is
    p_table_name                 VARCHAR(28); -- we need space for the padding

begin
    use_dbms_output := p_use_dbms_output;
    preserve_rowid  := p_preserve_rowid;

    dbms_output.enable(1000000);

    planta_data_dictionary.load_enabled;

    for t in (
        SELECT
            id,
            name
        FROM planta_tables
        ORDER BY
            id asc
    ) loop
        dbms_output.enable(1000000);
        on_table( t.name, t.id );
    end loop;

    planta_data_dictionary.clear;

end on_all_tables;


-- helpers for removal
procedure drop_package( p_table_name in user_constraints.table_name%type )
is
begin
    add( 'drop package ' || pkg_name(p_table_name) );
end drop_package;

procedure drop_bu_trigger( p_table_name in user_constraints.table_name%type )
is
begin
    add( 'drop trigger ' || trigger_name( p_table_name, '1' ) );
end drop_bu_trigger;

procedure drop_bufer_trigger( p_table_name in user_constraints.table_name%type )
is
begin
    add( 'drop trigger ' || trigger_name( p_table_name, '2' ) );
end drop_bufer_trigger;

procedure drop_au_trigger( p_table_name in user_constraints.table_name%type )
is
begin
    add( 'drop trigger ' || trigger_name( p_table_name, '3' ) );
end drop_au_trigger;

procedure off_table
  ( p_table_name          in     dt415.di000216%type,
    p_table_id            in     dt415.di000265%type )
is
    l_table_name                 user_constraints.table_name%type default p_table_name;
begin
    dbms_output.put_line(chr(10)||chr(10)||'-- removing UC packages for DT #'||p_table_id||' a.k.a. '||p_table_name||chr(10));

    sql_stmt := NULL;
    drop_bu_trigger( l_table_name );
    execute_immediate;
    drop_bufer_trigger( l_table_name );
    execute_immediate;
    drop_au_trigger( l_table_name );
    execute_immediate;

    drop_package( l_table_name );
    execute_immediate;
end off_table;

procedure off_all_tables
is
begin
    dbms_output.enable(1000000);

    planta_data_dictionary.load_enabled;

    for t in (
        SELECT
            id,
            name
        FROM planta_tables
        ORDER BY
            id asc
    ) loop
        dbms_output.enable(1000000);
        off_table( t.name, t.id );
    end loop;

    planta_data_dictionary.clear;

end off_all_tables;

end update_cascade;
