-------------------------------------------------------- -- Package: JR_WASTEBASKET -- Author: Will Stallard -- -- This package provides the PL/SQL Wastebasket API. -- -- Deletes from the repository should be made either -- by calling the appropriate method in this package, -- by deleting from the version resolved view directly -- or by calling the .DEL CDAPI method for the element type. -- -- Much of the constraint checking for delete opertations -- is performed by triggers on the version resolved view. ----------------------------------------------------------- prompt PACKAGE BODY: Jr_Wastebasket CREATE OR REPLACE PACKAGE BODY Jr_Wastebasket IS ------------------------------- --Private package variables ------------------------------- b_purge_flag boolean:=FALSE; b_force_flag boolean:=FALSE; b_remove_fm boolean:=FALSE; b_secondary_flag boolean:=FALSE; b_constraints_enabled boolean; b_check_wa_ctxt boolean; b_purge_all_versions boolean:=FALSE; ----------------------------------------------------------------------------- -- CW 25-Jul-02 -- Fix bug 2448356 FORCE PURGE OF APPLICATION FAILS WHEN IT INCLUDES A -- NAMED PREFERENCE SET USAGE -- Global variables to record irids and ivids of PREFERENCE SETS which need -- deleting after their container has been deleted. ----------------------------------------------------------------------------- type g_typ_tab_prs_ids is table of number; g_tab_prs_irids g_typ_tab_prs_ids; g_tab_prs_ivids g_typ_tab_prs_ids; ------------------------------------------------------------ -- Private method declarations ------------------------------------------------------------ FUNCTION get_elem_type(id IN number ,prod_code OUT varchar2 ,is_irid IN boolean := FALSE) RETURN varchar2; FUNCTION get_delete_sql(i_ivid IN number ,el_type IN varchar2 := NULL ,prod_code IN varchar2 := 'CI') RETURN varchar2; PROCEDURE execute_sql(sql_stmnt IN varchar2); FUNCTION used_in_workarea(i_ivid in number ,curr_wa in number default jr_context.workarea) RETURN boolean; FUNCTION used_in_config(i_ivid IN number ,checked_in boolean := FALSE) RETURN boolean; PROCEDURE check_can_restore(i_ivid IN number); PROCEDURE check_restored_name(i_ivid number); PROCEDURE restore_history(i_ivid IN number); PROCEDURE restore_tip_node(i_ivid IN number ,real_succ IN number ,real_pred IN number ,real_succ_type IN varchar2 ,live_pred IN number ,pred_notes IN varchar2); PROCEDURE restore_branch_node(i_ivid IN number ,real_pred IN number ,real_pred_type IN varchar2); FUNCTION is_significant_node(i_ivid number) RETURN boolean; PROCEDURE check_not_sys_el(elem_id IN number ,tab_name IN varchar2 ,use_irid IN boolean DEFAULT FALSE); PROCEDURE check_remove_cfg(i_ivid IN number); PROCEDURE check_container_empty(i_ivid IN number); PROCEDURE check_remove_container_ver(i_ivid IN number); PROCEDURE delete_container_members (i_ivid IN number); PROCEDURE purge_container_members (i_ivid IN number); PROCEDURE check_parent_folder_state(i_ivid IN number ,restoring IN boolean := FALSE); PROCEDURE check_not_shared(i_irid IN number); PROCEDURE remove_folder_member(ov_irid IN number ,ov_ivid IN number); PROCEDURE update_history(i_ivid IN number); PROCEDURE remove_tip_node(i_ivid IN number ,real_pred IN number); PROCEDURE get_edge_details(i_ivid IN number ,o_irid OUT number ,o_branch OUT number ,o_seq OUT number); PROCEDURE get_audit_details(i_to_ivid IN number ,i_from_ivid IN number ,o_date_created OUT DATE ,o_created_by OUT varchar2 ,o_date_changed OUT DATE ,o_changed_by OUT varchar2); FUNCTION get_tip(i_irid IN number ,i_branch IN number ,rest_ivid IN number ,wastebasket IN boolean := FALSE) RETURN number; FUNCTION find_real_successor(i_ivid IN number ,succ_type OUT varchar2) RETURN number; FUNCTION find_real_predecessor(i_ivid IN number ,pred_type OUT varchar2) RETURN number; FUNCTION find_live_predecessor(i_ivid IN number ,pred_notes OUT varchar2) RETURN number; FUNCTION find_live_successor(i_ivid IN number ,succ_notes OUT varchar2) RETURN number; PROCEDURE check_del_ar(i_ivid IN number); PROCEDURE change_ov_wastebasket_state(i_ivid IN number ,set_deleted IN boolean DEFAULT TRUE); PROCEDURE cleanup_version_associations ( p_irid IN i$sdd_object_versions.irid%type , p_ivid IN i$sdd_object_versions.ivid%type ); PROCEDURE physical_delete (p_irid IN i$sdd_object_versions.irid%type ,p_ivid IN i$sdd_object_versions.ivid%type); ----------------------------------------------------------------------------- -- Private helper methods for bug 2448635 to record irids and ivids of -- PREFERENCE SETS during a force_purge operation, which need deleting after -- the container has been deleted. ----------------------------------------------------------------------------- -- Delete any PRS irid, ivid collection entries -- Called by force_purge procedure empty_prs_ids is begin if (g_tab_prs_irids is not null) then g_tab_prs_irids.delete; end if; if (g_tab_prs_ivids is not null) then g_tab_prs_ivids.delete; end if; end empty_prs_ids; -- Initialize PRS irid, ivid collections -- Called by force_purge procedure init_prs_ids is begin if (g_tab_prs_irids is null) then g_tab_prs_irids := g_typ_tab_prs_ids(); end if; if (g_tab_prs_ivids is null) then g_tab_prs_ivids := g_typ_tab_prs_ids(); end if; empty_prs_ids; end init_prs_ids; -- Add irid of a PRS to collection so it can be purged later -- Called by purge_container_members procedure add_prs_irid (p_prs_irid in number) is b_prs_exists boolean := false; begin if (g_tab_prs_irids is not null) then if (g_tab_prs_irids.count > 0) then for i in g_tab_prs_irids.first..g_tab_prs_irids.last loop if (g_tab_prs_irids(i) = p_prs_irid) then b_prs_exists := true; exit; end if; end loop; end if; -- count > 0 -- Only add irid if it doesn't already exist in the collection if (not b_prs_exists) then g_tab_prs_irids.extend; g_tab_prs_irids(g_tab_prs_irids.last) := p_prs_irid; end if; end if; -- table of irids defined end add_prs_irid; -- Purge all recorded PRS irids -- Called by force_purge after container deleted procedure purge_prs_irids is begin if (g_tab_prs_irids is not null and g_tab_prs_irids.count > 0) then for i in g_tab_prs_irids.first..g_tab_prs_irids.last loop purge_object(g_tab_prs_irids(i)); end loop; end if; -- table of irids defined and has entries end purge_prs_irids; -- Add ivid of a PRS to collection so it can be deleted later -- Called by delete_container_members procedure add_prs_ivid (p_prs_ivid in number) is b_prs_exists boolean := false; begin if (g_tab_prs_ivids is not null) then if (g_tab_prs_ivids.count > 0) then for i in g_tab_prs_ivids.first..g_tab_prs_ivids.last loop if (g_tab_prs_ivids(i) = p_prs_ivid) then b_prs_exists := true; exit; end if; end loop; end if; -- count > 0 -- Only add ivid if it doesn't already exist in the collection if (not b_prs_exists) then g_tab_prs_ivids.extend; g_tab_prs_ivids(g_tab_prs_ivids.last) := p_prs_ivid; end if; end if; -- table of ivids defined end add_prs_ivid; -- Delete all recorded PRS ivids -- Called by force_purge after container deleted procedure delete_prs_ivids is begin if (g_tab_prs_ivids is not null and g_tab_prs_ivids.count > 0) then for i in g_tab_prs_ivids.first..g_tab_prs_ivids.last loop execute_sql('delete sdd_prs where ivid='||TO_CHAR(g_tab_prs_ivids(i))); end loop; end if; -- table of ivids defined and has entries end delete_prs_ivids; ------------------------------------------------------------ -- Public methods ------------------------------------------------------------ ------------------------------------------------------------ -- jr_wastebasket.DISABLE_REPOS_CONSTRAINTS -- Called by delete triggers - disable the repository -- constraints during the delete, and record the current -- setting, so it can be put back at the end of the delete ------------------------------------------------------------ PROCEDURE DISABLE_REPOS_CONSTRAINTS IS BEGIN b_constraints_enabled:=Jr_Context.constraints_enabled; --We need to disable constraints during the delete operation --to prevent possible invalid reference errors whilst we nullify --foreign keys Jr_Context.disable_constraints; END; ------------------------------------------------------------ -- jr_wastebasket.RESET_REPOS_CONSTRAINTS -- Called by delete triggers - reset the repository constraint -- setting, at the end of the delete ------------------------------------------------------------ PROCEDURE RESET_REPOS_CONSTRAINTS IS BEGIN IF b_constraints_enabled THEN Jr_Context.enable_constraints; END IF; END; ------------------------------------------------------------ -- jr_wastebasket.REPOS_CONSTRAINTS_ENABLED -- Called by delete triggers - returns the state of the -- repository constraint setting prior to the start of the -- delete operation. ------------------------------------------------------------ FUNCTION REPOS_CONSTRAINTS_ENABLED RETURN boolean IS BEGIN RETURN b_constraints_enabled; END; ------------------------------------------------------------ -- jr_wastebasket.DELETE() -- -- Performs delete operation on specified element. ------------------------------------------------------------ PROCEDURE DELETE(i_ivid IN number ,el_type IN varchar2 DEFAULT NULL ,prod_code IN varchar2 DEFAULT NULL) IS sql_stmnt varchar2(300); BEGIN sql_stmnt:=get_delete_sql(i_ivid,el_type,prod_code); execute_sql(sql_stmnt); END; ------------------------------------------------------------ -- jr_wastebasket.PURGE() -- -- Performs purge operation on specified element. ------------------------------------------------------------ PROCEDURE purge(i_ivid IN number ,el_type IN varchar2 DEFAULT NULL ,prod_code IN varchar2 DEFAULT NULL) IS sql_stmnt varchar2(300); BEGIN --Set the purge flag b_purge_flag:=TRUE; --Execute the delete sql_stmnt:=get_delete_sql(i_ivid,el_type,prod_code); execute_sql(sql_stmnt); -- SDP Fix bug 1316669 - Dont put purged objects in wastebasket -- Get rid of object from the repository physical_delete(null, i_ivid); --Reset the purge flag b_purge_flag:=FALSE; EXCEPTION --If we had a problem, ensure purge flag is reset WHEN OTHERS THEN b_purge_flag:=FALSE; RAISE; END; ------------------------------------------------------------ -- jr_wastebasket.FORCE_DELETE() -- -- Performs force delete operation on specified element. ------------------------------------------------------------ PROCEDURE force_delete(i_ivid IN number ,el_type IN varchar2 DEFAULT NULL ,prod_code IN varchar2 DEFAULT NULL) IS sql_stmnt varchar2(300); BEGIN --Set the force flag b_force_flag:=TRUE; --Execute the delete sql_stmnt:=get_delete_sql(i_ivid,el_type,prod_code); execute_sql(sql_stmnt); --Reset the force flag b_force_flag:=FALSE; EXCEPTION --If we had a problem, ensure force flag is reset WHEN OTHERS THEN b_force_flag:=FALSE; RAISE; END; ------------------------------------------------------------ -- jr_wastebasket.FORCE_PURGE() -- -- Performs force purge operation on specified element. ------------------------------------------------------------ PROCEDURE force_purge(i_ivid IN number ,el_type IN varchar2 DEFAULT NULL ,prod_code IN varchar2 DEFAULT NULL) IS sql_stmnt varchar2(300); l_el_type varchar2(40); l_prod_code varchar2(240); BEGIN --Set the purge and force flags b_purge_flag:=TRUE; b_force_flag:=TRUE; -- Initialize PREFERENCE_SET irid, ivid collections init_prs_ids; Rmdbg.trace('Fix for Bug 1984967 - Force purge entities '); IF el_type IS NULL THEN --Get the element type and product code if we don't know it l_el_type:=get_elem_type(i_ivid,l_prod_code); ELSE l_el_type := el_type; l_prod_code:= prod_code; END IF; --Execute the delete sql_stmnt:=get_delete_sql(i_ivid,l_el_type,l_prod_code); execute_sql(sql_stmnt); -- SDP Fix bug 1316669 - Dont put purged objects in wastebasket -- Get rid of object from the repository IF l_el_type = 'ENT' THEN Rmdbg.trace('Fix for Bug 1984967 - Defer constraint checking '); jr_context.set_constraints(jr_context.constraints_deferred); physical_delete(null, i_ivid); jr_context.set_constraints(jr_context.constraints_immediate); ELSE physical_delete(null, i_ivid); -- CW 25-JUL-02 Fix bug 2448356 Now remove the PREFERENCE SET members after -- the container has been deleted. purge_prs_irids; delete_prs_ivids; END IF; -- Clear PREFERENCE_SET irid, ivid collections empty_prs_ids; --Reset the purge and force flags b_purge_flag:=FALSE; b_force_flag:=FALSE; EXCEPTION --If we had a problem, ensure force and purge flags are reset WHEN OTHERS THEN b_purge_flag:=FALSE; b_force_flag:=FALSE; RAISE; END force_purge; ------------------------------------------------------------ -- jr_wastebasket.PURGE_OBJECT() -- -- Purges all versions of an object from the repository. -- Operation will fail if any version is used in a workarea -- or configuration. ------------------------------------------------------------ PROCEDURE purge_object(i_irid IN number) IS dummy integer; obj_name varchar2(300); latest_ivid number; ov_ivid number; root_ivid number; tab_name varchar2(30); workarea_context number:=Jr_Context.workarea; purge_flag_set boolean:=b_purge_flag; BEGIN --Set the purge + purge all versions flags b_purge_flag:=TRUE; b_purge_all_versions:=TRUE; --Check access permissions for operation --User must have FORCE and GLOBAL_PURGE privileges IF (NOT Jr_Sys_Privs.has_privilege(USER,'GLOBAL_PURGE')) OR (NOT Jr_Sys_Privs.has_privilege(USER,'FORCE')) THEN --Insuficient privileges to purge entire version history of object. Rmmes.post('CDR',2614); RAISE PURGE_ERROR; END IF; --Get hold of an ivid for the object...get latest on MAIN BEGIN SELECT va.to_ivid INTO latest_ivid FROM sdd_version_associations va ,I$SDD_BRANCHES br WHERE br.name='MAIN' AND va.branch_id=br.branch_id AND va.edge_kind='T' AND va.irid=i_irid; EXCEPTION WHEN NO_DATA_FOUND THEN --May be 'purging' a non versioned object SELECT ov.ivid INTO latest_ivid FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.irid=i_irid; END; --Get hold of root version of object as it requires special treatment BEGIN SELECT va.to_ivid INTO root_ivid FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.irid=i_irid AND va.edge_kind='R'; EXCEPTION WHEN NO_DATA_FOUND THEN --May be 'purging' a non versioned object SELECT ov.ivid INTO root_ivid FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.irid=i_irid; END; --User must have delete privilege on object check_del_ar(latest_ivid); --Don't allow purge of object if it's a SYSTEM_ELEMENT SELECT tab.name INTO tab_name FROM rm_sql_tables tab ,I$SDD_OBJECT_VERSIONS ov WHERE tab.irid=ov.table_irid AND ov.ivid=latest_ivid; check_not_sys_el(i_irid,tab_name,TRUE); --Don't allow purge if object is used in any workarea or configuration --Check workareas... SELECT COUNT(ctxt.object_ivid) INTO dummy FROM I$SDD_WA_CONTEXT ctxt ,I$SDD_OBJECT_VERSIONS ov WHERE ov.irid=i_irid AND ctxt.object_ivid=ov.ivid AND ctxt.wastebasket='N' AND ctxt.workarea_irid IN (SELECT irid FROM I$SDD_WORKAREAS wa WHERE Jr_Context.workarea IS NULL OR wa.irid!=Jr_Context.workarea); IF dummy > 0 THEN SELECT ov.name INTO obj_name FROM I$SDD_OBJECT_VERSIONS ov ,SDD_VERSION_ASSOCIATIONS va ,I$SDD_BRANCHES br WHERE ov.ivid=va.to_ivid AND va.edge_kind='T' AND va.branch_id=br.branch_id AND br.name='MAIN' AND va.irid=i_irid; --Cannot purge %0!s. Version(s) of object currently used in workarea(s). Rmmes.post('CDR',2010,obj_name); RAISE PURGE_ERROR; END IF; --Check configs... SELECT COUNT(cm.config_ivid) INTO dummy FROM I$SDD_CONFIGURATION_MEMBERS cm ,I$SDD_CONFIGURATIONS cc WHERE cm.object_irid = i_irid AND cc.ivid = cm.config_ivid; IF dummy > 0 THEN SELECT ov.name INTO obj_name FROM I$SDD_OBJECT_VERSIONS ov ,SDD_VERSION_ASSOCIATIONS va ,I$SDD_BRANCHES br WHERE ov.ivid=va.to_ivid AND va.edge_kind='T' AND va.branch_id=br.branch_id AND br.name='MAIN' AND va.irid=i_irid; --Cannot purge %0!s. Versions of object currently used in configuration(s). Rmmes.post('CDR',2011,obj_name); RAISE PURGE_ERROR; END IF; --If we have a workarea context set, and this object is in the workarea, --then do a proper purge of this version to maintain referential integrity IF Jr_Context.workarea IS NOT NULL THEN SELECT MIN(ov.ivid) INTO ov_ivid FROM SDD_OBJECT_VERSIONS ov WHERE ov.irid=i_irid; IF ov_ivid IS NOT NULL THEN execute_sql('delete '||tab_name||' where ivid='||TO_CHAR(ov_ivid)); END IF; END IF; --OK - now just purge all other versions of the object to the wastebasket... --Null the workarea Jr_Context.set_workarea(Jr_Context.NULL_WORKAREA); --Simply update the wastebasket flag for all versions except the root UPDATE I$SDD_OBJECT_VERSIONS ov SET ov.wastebasket='Y' WHERE ov.irid=i_irid AND ov.ivid!=root_ivid; --Delete the root object through the views so the triggers can fire the --appropriatea referential integrity checks. execute_sql('delete '||tab_name||' where ivid='||TO_CHAR(root_ivid)); --Reset the workarea, and purge flag if necessary -- SDP Fix bug 1316669 - Dont put purged objects in wastebasket -- Get rid of object from the repository physical_delete(i_irid, null); Jr_Context.set_workarea(workarea_context); b_purge_all_versions := FALSE; IF NOT purge_flag_set THEN b_purge_flag:=FALSE; END IF; EXCEPTION WHEN OTHERS THEN Jr_Context.set_workarea(workarea_context); b_purge_all_versions := FALSE; IF NOT purge_flag_set THEN b_purge_flag:=FALSE; END IF; RAISE; END; -------------------------------------------------------------------------- -- jr_wastebasket.PURGE_INSIGNIFICANT_VERSIONS() -- -- Purges all 'insignificant' object versions of an object. -- If a branch id is supplied then just versions on the -- branch will be purged. -- i.e. any version: -- o not used in a configuration/workarea -- o not the source/target for a branch/merge -- o not the source of a check out -- o not a root or tip node -- -- CW 14-Mar-2001 Fix bug 1668421, this method was not considering branch -- nodes as significant. Added 'B' to the not exists edge_king list -------------------------------------------------------------------------- PROCEDURE purge_insignificant_versions(i_irid IN number ,i_branch_id IN number := NULL) IS curr_wa number := Jr_Context.workarea; null_wa number := NULL; l_logical_type rm_element_types.short_name%TYPE := NULL; is_config boolean := false; continue_purge boolean := false; CURSOR insignificant_ver IS SELECT va1.to_ivid ivid FROM SDD_VERSION_ASSOCIATIONS va1--Use view to avoid deleted versions WHERE ( i_branch_id IS NULL OR i_branch_id = va1.branch_id ) AND va1.edge_kind = 'N' AND va1.irid = i_irid AND NOT EXISTS (SELECT NULL FROM SDD_VERSION_ASSOCIATIONS va2 WHERE va2.to_ivid = va1.to_ivid AND va2.edge_kind = 'M') --Not target for merge AND NOT EXISTS (SELECT NULL FROM SDD_VERSION_ASSOCIATIONS va3 WHERE va3.from_ivid = va1.to_ivid --Not tip,source for checkout --or merge AND va3.edge_kind IN ('T','C','L','M','B')) ORDER BY va1.edge_sequence DESC; BEGIN --Check user is allowed to do this... --User must have GLOBAL_PURGE privilege IF NOT Jr_Sys_Privs.has_privilege(USER,'GLOBAL_PURGE') THEN --Insuficient privileges to purge all insignificant versions of object. Rmmes.post('CDR',2615); RAISE PURGE_ERROR; END IF; Jr_Context.set_workarea(null_wa); -- Set null context --Check if the object is a repository configuration SELECT elem.short_name INTO l_logical_type FROM I$SDD_OBJECT_VERSIONS ov ,RM_ELEMENT_TYPES elem WHERE ov.irid = i_irid AND elem.id = ov.logical_type_id AND rownum=1; IF l_logical_type='CFG' THEN is_config := true; END IF; FOR i IN insignificant_ver LOOP continue_purge := false; -- B3435964 : initialise for each version of object IF NOT used_in_workarea(i.ivid) AND NOT used_in_config(i.ivid) THEN continue_purge := true; END IF; IF is_config THEN -- B2383671 : If an configuration, check it doesn't exist in any workarea spec. declare l_dummy integer; begin SELECT 1 INTO l_dummy FROM I$SDD_WORKAREAS wa ,I$SDD_WORKAREA_SPEC_ENTRIES was WHERE wa.irid=was.workarea_irid AND was.config_ivid=i.ivid AND rownum < 2; continue_purge := false; exception when no_data_found then continue_purge := true; end; END IF; IF continue_purge THEN change_ov_wastebasket_state(i.ivid,TRUE); update_history(i.ivid); -- SDP Fix bug 1316669 - Dont put purged objects in wastebasket -- Get rid of object from the repository physical_delete(i_irid, i.ivid); END IF; END LOOP; Jr_Context.set_workarea(curr_wa); --Restore the wa context EXCEPTION WHEN OTHERS THEN --Make sure context is restored whatever... Jr_Context.set_workarea(curr_wa); RAISE; END; ------------------------------------------------------------------- -- Gets element name and product code from ivid or irid of element ------------------------------------------------------------------- FUNCTION get_elem_type(id IN number ,prod_code OUT varchar2 ,is_irid IN boolean := FALSE) RETURN varchar2 IS l_ivid number; el_name varchar2(40); BEGIN IF is_irid THEN --Pick an ivid for this element SELECT MIN(ov.ivid) INTO l_ivid FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.irid=id; ELSE l_ivid:=id; END IF; SELECT et.short_name ,et.product INTO el_name ,prod_code FROM RM_ELEMENT_TYPES et ,I$SDD_OBJECT_VERSIONS ov WHERE et.irid=ov.logical_type_id AND ov.ivid=l_ivid; RETURN el_name; END; ------------------------------------------------------------ -- Generates delete statement from the ivid and type type -- of the element. ------------------------------------------------------------ FUNCTION get_delete_sql(i_ivid IN number ,el_type IN varchar2 := NULL ,prod_code IN varchar2 := 'CI') RETURN varchar2 IS l_el_type varchar2(40); l_prod_code varchar2(240); view_name varchar2(30); sql_stmnt varchar2(300); BEGIN IF el_type IS NULL THEN --Get the element type and product code if we don't know it l_el_type:=get_elem_type(i_ivid,l_prod_code); ELSE l_el_type:=el_type; l_prod_code:=prod_code; END IF; --Get hold of the view name for the element --from the element name and product code SELECT tab.name INTO view_name FROM RM_SQL_TABLES tab ,RM_SQL_ROW_TYPES rt ,RM_ELEMENT_TYPES et WHERE tab.irid=rt.table_mapped AND rt.id=et.primary_row_type AND (et.short_name=l_el_type OR et.name=l_el_type) AND et.product=l_prod_code; --Build the statement sql_stmnt:='DELETE '||view_name||' '|| 'WHERE ivid = '||TO_CHAR(i_ivid); RETURN sql_stmnt; END; PROCEDURE execute_sql(sql_stmnt IN varchar2) IS csr integer; exec integer; BEGIN --Open a cursor csr:=DBMS_SQL.OPEN_CURSOR; --Parse and execute the sql. DBMS_SQL.PARSE(csr,sql_stmnt,DBMS_SQL.NATIVE); exec:=DBMS_SQL.EXECUTE(csr); --Close the cursor DBMS_SQL.CLOSE_CURSOR(csr); EXCEPTION WHEN OTHERS THEN --Close cursor if we had an error DBMS_SQL.CLOSE_CURSOR(csr); --Pass the exception on RAISE; END; ------------------------------------------------------------ -- jr_wastebasket.RESTORE() -- -- Restores object from the wastebasket of the current -- workarea. If no workarea context is set, restores -- object from null context wastebasket. ------------------------------------------------------------- PROCEDURE restore(i_ivid IN number) IS dummy number; wb_flag varchar2(1):=Jr_Context.wastebasket; BEGIN --Ensure that the wastebasket flag is disabled Jr_Context.unset_wastebasket; --Check we can see object in current context. SELECT COUNT(*) INTO dummy FROM SDD_WASTEBASKET WHERE ivid=i_ivid; IF dummy = 0 THEN --Object not visible in current wastebasket context Rmmes.post('CDR',2050); RAISE RESTORE_ERROR; END IF; --Check that the element can be restored --(ie - does a context folder still exist for it, is the branch still available) -- -may do additional checks in future) check_can_restore(i_ivid); --If we have a workarea context set, --can only restore into a checked out folder IF Jr_Context.workarea IS NOT NULL THEN check_parent_folder_state(i_ivid,TRUE); END IF; IF Jr_Context.workarea IS NULL THEN --In null context, can restore without needing to maintain referential integrety change_ov_wastebasket_state(i_ivid,FALSE); ELSE --In context of workarea, should also restore referencing elements --But this is tricky to work out, so for now, just restore the object change_ov_wastebasket_state(i_ivid,FALSE); END IF; --Restore the version history IF Jr_Version.is_checked_in(NULL,i_ivid) THEN restore_history(i_ivid); END IF; --Check that the name of the restored element is unique --(nature of CDAPI checking means check has to be performed AFTER restore) check_restored_name(i_ivid); --Reset the wastebasket flag if necessary IF wb_flag='Y' THEN Jr_Context.set_wastebasket; END IF; EXCEPTION WHEN OTHERS THEN --Reset the wastebasket flag if necessary IF wb_flag='Y' THEN Jr_Context.set_wastebasket; END IF; RAISE; END; ------------------------------------------------------------- -- Checks that a valid folder exists to restore the element -- into. ------------------------------------------------------------- PROCEDURE check_can_restore(i_ivid IN number) IS fol_count pls_integer:=0; cfg_count pls_integer:=0; va_irid number; va_branch_id number; clash_ivid number; br_name I$SDD_BRANCHES.NAME%TYPE; el_type RM_ELEMENT_TYPE_EXTENSIONS.NLS_NAME%TYPE; BEGIN SELECT COUNT(1) INTO fol_count FROM SDD_FOLDER_MEMBERS fm ,I$SDD_OBJECT_VERSIONS ov WHERE fm.member_object=ov.irid AND ov.ivid=i_ivid; IF fol_count = 0 THEN --Couldn't find a context folder - but we're OK if we're restoring --a configuration or root folder... SELECT COUNT(1) INTO cfg_count FROM I$SDD_CONFIGURATIONS cfg WHERE cfg.ivid=i_ivid; IF cfg_count=0 THEN SELECT COUNT(1) INTO fol_count FROM I$SDD_FOLDERS fol WHERE fol.ivid=i_ivid AND fol.root_flag='Y'; END IF; IF (fol_count = 0) AND (cfg_count = 0) THEN --Not a root folder, or config so we have an error... --No context container to restore to Rmmes.post('CDR',2054); RAISE RESTORE_ERROR; END IF; END IF; --If we're restoring the first node on a branch, then we need to --ensure that the branch hasn't been re-used for a different version --of this object BEGIN SELECT va.irid,va.branch_id INTO va_irid,va_branch_id FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.to_ivid=i_ivid AND va.edge_kind='B'; EXCEPTION WHEN NO_DATA_FOUND THEN --We're not restoring the first node on a branch RETURN; END; --Look for another 'live' version of the same object on the the same branch BEGIN SELECT va.to_ivid INTO clash_ivid FROM SDD_VERSION_ASSOCIATIONS va --Use view so we only see 'live' versions WHERE va.branch_id=va_branch_id AND va.irid=va_irid AND va.edge_kind='B'; EXCEPTION WHEN NO_DATA_FOUND THEN --There is no clash RETURN; END; --We found a clash - so post an error --Get branch name SELECT br.name INTO br_name FROM I$SDD_BRANCHES br WHERE br.branch_id=va_branch_id; --And object type SELECT nls.nls_name INTO el_type FROM rm_element_type_extensions nls ,I$SDD_OBJECT_VERSIONS ov WHERE ov.ivid=i_ivid AND nls.for_type=ov.logical_type_id; --Cannot restore %1!s %0!s as branch %2!s is now occupied by version %3!s Rmmes.post('CDR',2056,Jr_Name.get_CRN(i_ivid,'BRANCH') ,el_type ,br_name ,Jr_Name.get_version_info(clash_ivid,'VLABEL')); RAISE RESTORE_ERROR; END; ------------------------------------------------------------- -- Once an object has been restored, checked that it's -- name is unique. ------------------------------------------------------------- PROCEDURE check_restored_name(i_ivid number) IS el_irid number; el_tab_name rm_sql_tables.name%TYPE; is_root_fol varchar2(1):='N'; name_unique boolean:=TRUE; clash_irid number; clash_type number; clash_path varchar2(4000); elem_path varchar2(4000); clash_type_name rm_element_type_extensions.nls_name%TYPE; CURSOR get_folders(el_id number) IS SELECT fm.folder_reference irid FROM sdd_folder_members fm WHERE fm.member_object=el_id; BEGIN --Get irid and table name for element SELECT ov.irid,tab.name INTO el_irid,el_tab_name FROM I$SDD_OBJECT_VERSIONS ov ,RM_SQL_TABLES tab WHERE ov.ivid=i_ivid AND tab.id=ov.table_irid; --Special case for configurations IF el_tab_name='SDD_CONFIGURATIONS' THEN SELECT MAX(other_cfg.name),MAX(ov.logical_type_id) INTO clash_path,clash_type FROM I$SDD_CONFIGURATIONS other_cfg ,I$SDD_OBJECT_VERSIONS ov ,I$SDD_CONFIGURATIONS this_cfg WHERE this_cfg.ivid=i_ivid AND other_cfg.name=this_cfg.name AND ov.ivid=other_cfg.ivid AND ov.wastebasket='N' AND other_cfg.irid!=this_cfg.irid; IF clash_path IS NOT NULL THEN elem_path:=clash_path; name_unique:=FALSE; ELSE name_unique:=TRUE; END IF; ELSE --Only check uniqueness if WA context set IF Jr_Context.workarea IS NULL THEN RETURN; END IF; --Root containers are a special case IF el_tab_name='SDD_FOLDERS' THEN SELECT fol.root_flag INTO is_root_fol FROM I$SDD_FOLDERS fol WHERE fol.ivid=i_ivid; END IF; IF is_root_fol='N' THEN --Get hold of the folder(s) for the restored element, --and check uniqueness FOR fol IN get_folders(el_irid) LOOP name_unique:=Jr_Name.is_el_name_unique(fol.irid ,el_irid ,clash_irid ,clash_type); IF NOT name_unique THEN EXIT; END IF; END LOOP; ELSE --For root folders, we don't need to get hold of the folders for the element name_unique:=Jr_Name.is_el_name_unique(NULL ,el_irid ,clash_irid ,clash_type); END IF; IF NOT name_unique THEN clash_path:=Jr_Name.get_path(clash_irid,'NAME'); elem_path :=Jr_Name.get_path(el_irid,'NAME'); END IF; END IF; --If we got a clash, then post an error IF NOT name_unique THEN SELECT nls.nls_name INTO clash_type_name FROM rm_element_type_extensions nls WHERE nls.for_type=clash_type; --Cannot restore %0!s. Name uniqueness conflict with %1!s %2!s Rmmes.post('CDR',2055,elem_path,clash_type_name,clash_path); RAISE RESTORE_ERROR; END IF; END; ------------------------------------------------------------- -- Patches up the version history of an element once it -- has been restored. ------------------------------------------------------------- PROCEDURE restore_history(i_ivid IN number) IS live_pred number; live_succ number; real_pred number; real_succ number; real_pred_type varchar2(1); real_succ_type varchar2(1); rest_irid number; rest_ivid number; rest_seq number; rest_branch number; rest_type varchar2(1); branch_name varchar2(255); pred_notes varchar2(2000); succ_notes varchar2(2000); l_date_created DATE; l_created_by varchar2(30); l_date_changed DATE; l_changed_by varchar2(30); BEGIN rest_ivid:=i_ivid; --Get the immeadiate successor and predecessor for this element real_pred:=find_real_predecessor(rest_ivid,real_pred_type); IF real_pred_type IN ('B','R') THEN --Special case for restoring root/branch nodes... restore_branch_node(rest_ivid,real_pred,real_pred_type); RETURN; END IF; SELECT MIN(va.edge_kind) INTO rest_type FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.from_ivid = rest_ivid AND va.edge_kind='T'; IF rest_type = 'T' THEN --Special case for restoring tip nodes - no real --succ, so set real_succ=rest_ivid real_succ:=rest_ivid; ELSE real_succ:=find_real_successor(rest_ivid,real_succ_type); END IF; --Get the predecessor and successor for this element that are --not in the wastebasket (i.e. 'live'). live_pred:=find_live_predecessor(rest_ivid,pred_notes); live_succ:=find_live_successor(rest_ivid,succ_notes); --Have error if we couldn't locate a 'live' predecessor. IF live_pred IS NULL THEN SELECT va.branch_id INTO rest_branch FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.to_ivid=rest_ivid AND va.edge_kind NOT IN ('T','M'); branch_name:=Jr_Version.get_branch_name(rest_branch); --Cannot restore, first version on branch %0!s has been deleted. Rmmes.post('CDR',2051,branch_name); RAISE RESTORE_ERROR; END IF; --Special case if version we are restoring has no live successor --version is either a tip node, or is likely to become one on restoration. IF live_succ IS NULL THEN restore_tip_node(rest_ivid,real_succ,real_pred,real_pred_type,live_pred,pred_notes); RETURN; END IF; --Create/Update/Delete associations to make version history --consistent. IF (live_succ!=real_succ) THEN --Immeadiate successor is in wastebasket, so --update existing 'extra' link to point from --the restored node. --Preserve existing audit details for extra link get_audit_details(live_succ ,live_pred ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); UPDATE I$SDD_VERSION_ASSOCIATIONS SET from_ivid = rest_ivid --Update to point from restored node... ,notes = succ_notes WHERE to_ivid = live_succ --...to next live node... AND from_ivid = live_pred; --...rather than from previous live node. --Restore the audit details to how they were (trigger will have updated them) UPDATE I$SDD_VERSION_ASSOCIATIONS SET date_created = l_date_created ,created_by = l_created_by ,date_changed = l_date_changed ,changed_by = l_changed_by WHERE to_ivid = live_succ AND from_ivid = rest_ivid; IF (live_pred!=real_pred) THEN -- Immeadiate predecessor is ALSO in the wastebasket, so -- create a new association between the live predecessor -- and the restored node. get_edge_details(rest_ivid,rest_irid,rest_branch,rest_seq); --Ensure audit details of the new edge we are creating --match the audit details of the original edge that points --at the restored node get_audit_details(rest_ivid ,real_pred ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); INSERT INTO I$SDD_VERSION_ASSOCIATIONS (IRID ,TO_IVID ,FROM_IVID ,EDGE_KIND ,BRANCH_ID ,EDGE_SEQUENCE ,NOTES ,DATE_CREATED ,CREATED_BY ,DATE_CHANGED ,CHANGED_BY) VALUES (rest_irid ,rest_ivid ,live_pred ,'N' ,rest_branch ,rest_seq ,pred_notes ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); END IF; ELSIF (live_pred!=real_pred) THEN -- Immeadiate successor isn't in wastebasket, but predecessor is, -- so update exsisting 'extra' edge to point from the 'live' -- predecessor to the restored node. get_edge_details(rest_ivid,rest_irid,rest_branch,rest_seq); --Ensure the edge details for the 'extra' edge match up to the restored --node get_audit_details(rest_ivid ,real_pred ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); UPDATE I$SDD_VERSION_ASSOCIATIONS SET to_ivid = rest_ivid --Update edge to point to restored node... ,edge_sequence = rest_seq ,notes = pred_notes ,date_created = l_date_created ,created_by = l_created_by ,date_changed = l_date_changed ,changed_by = l_changed_by WHERE from_ivid = live_pred --...from previous live node... AND to_ivid = real_succ; --...rather than to next node. ELSE -- Immediate predecessor and successor are 'live', so -- can get just rid of 'extra' edge... DELETE I$SDD_VERSION_ASSOCIATIONS WHERE to_ivid = real_succ AND from_ivid = real_pred; END IF; END; ----------------------------------------------------- -- Handle special case of restoring the first node -- on a branch.... ----------------------------------------------------- PROCEDURE restore_branch_node(i_ivid IN number ,real_pred IN number ,real_pred_type IN varchar2) IS rest_irid number; rest_branch number; rest_seq number; branch_name varchar2(255); dummy integer; BEGIN get_edge_details(i_ivid,rest_irid,rest_branch,rest_seq); --Check that the source of the branch has not been deleted... IF real_pred_type = 'B' THEN SELECT COUNT(ov.ivid) INTO dummy FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.ivid = real_pred AND ov.wastebasket = 'N'; IF dummy = 0 THEN branch_name:=Jr_Version.get_branch_name(rest_branch); --Cannot restore - the source node for branch %0!s has been deleted. Rmmes.post('CDR',2052,branch_name); RAISE RESTORE_ERROR; END IF; END IF; --Simple matter to restore the version history - first node on a branch --must be restored before any other nodes, and may only be removed after --all other nodes, so on restore, they are always the new tip... --If object was previously tip AS WELL as the root, we don't need to --do anything! SELECT COUNT(1) INTO dummy FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.to_ivid=i_ivid AND va.edge_kind='T'; IF dummy = 0 THEN --Node wasn't previously a tip, so make it one now... INSERT INTO I$SDD_VERSION_ASSOCIATIONS (IRID ,TO_IVID ,FROM_IVID ,EDGE_KIND ,BRANCH_ID ,EDGE_SEQUENCE) VALUES (rest_irid ,i_ivid ,i_ivid ,'T' ,rest_branch ,rest_seq); END IF; END; ----------------------------------------------------- -- Handle complex case where we are restoring the -- latest _LIVE_ version on a branch (the real tip -- may be in wastebasket, or we may be restoring -- the real tip, in which case real_succ=i_ivid). ----------------------------------------------------- PROCEDURE restore_tip_node(i_ivid IN number ,real_succ IN number ,real_pred IN number ,real_succ_type IN varchar2 ,live_pred IN number ,pred_notes IN varchar2) IS rest_ivid number; rest_irid number; rest_branch number; rest_seq number; curr_tip number; old_tip number; old_tip_seq number; l_date_created DATE; l_created_by varchar2(30); l_date_changed DATE; l_changed_by varchar2(30); BEGIN rest_ivid:=i_ivid; get_edge_details(rest_ivid,rest_irid,rest_branch,rest_seq); --Get hold of the current and wastebasket tip nodes... curr_tip:=get_tip(rest_irid,rest_branch,rest_ivid,FALSE); IF (real_succ_type = 'T') OR (rest_ivid=real_succ) THEN old_tip := real_succ; ELSE old_tip:=get_tip(rest_irid,rest_branch,rest_ivid,TRUE); END IF; --If the 'live' predecessor is the current tip, then --make the restored node the new tip. IF curr_tip = live_pred THEN --Delete the current tip... DELETE I$SDD_VERSION_ASSOCIATIONS WHERE to_ivid = curr_tip AND edge_kind = 'T'; --If the node we're restoring isn't the old tip, --then make it a tip - give it the edge seq of --the old tip, so that version service can maintain --correct version seqeunce info. IF old_tip!=rest_ivid THEN SELECT va.edge_sequence INTO old_tip_seq FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.from_ivid = old_tip; INSERT INTO I$SDD_VERSION_ASSOCIATIONS (IRID ,TO_IVID ,FROM_IVID ,EDGE_KIND ,BRANCH_ID ,EDGE_SEQUENCE) VALUES (rest_irid ,rest_ivid ,rest_ivid ,'T' ,rest_branch ,old_tip_seq); END IF; ELSE --If the 'live' predecessor isn't the current tip, then update the --appropriate live edge to add the restored node into the version --history --Get hold of current audit details so we can restore them after update SELECT changed_by,date_changed INTO l_changed_by,l_date_changed FROM SDD_VERSION_ASSOCIATIONS WHERE from_ivid = live_pred AND to_ivid != rest_ivid AND edge_kind = 'N'; --Do the update UPDATE SDD_VERSION_ASSOCIATIONS SET from_ivid = rest_ivid --Edge points from the restored node... WHERE from_ivid = live_pred --...instead of the previous 'live' node AND to_ivid != rest_ivid --Don't move edge pointing to restored node AND edge_kind = 'N'; --Reset the audit properties (will have been updated by trigger) UPDATE SDD_VERSION_ASSOCIATIONS SET changed_by = l_changed_by ,date_changed = l_date_changed WHERE from_ivid = rest_ivid AND edge_kind = 'N'; --If the node we're restoring was the old tip, it's not a tip anymore, --so delete the tip edge IF i_ivid = old_tip THEN DELETE I$SDD_VERSION_ASSOCIATIONS WHERE from_ivid = old_tip AND edge_kind = 'T'; END IF; END IF; --Finally, if the immeadiate predecessor of the restored node --is not the 'live' predecessor, then create a new 'extra' edge --to link the live predecessor to the restored node. IF real_pred!=live_pred THEN --Get hold of audit details for edge we are 'replacing' get_audit_details(rest_ivid ,real_pred ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); INSERT INTO I$SDD_VERSION_ASSOCIATIONS (IRID ,TO_IVID ,FROM_IVID ,EDGE_KIND ,BRANCH_ID ,EDGE_SEQUENCE ,NOTES ,DATE_CREATED ,CREATED_BY ,DATE_CHANGED ,CHANGED_BY) VALUES (rest_irid ,rest_ivid ,live_pred ,'N' ,rest_branch ,rest_seq ,pred_notes ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); END IF; END; ----------------------------------------------------- -- Gets hold of the details for the 'live' edge with -- the specified to_ivid. Ignores 'T' and 'M' edges ----------------------------------------------------- PROCEDURE get_edge_details(i_ivid IN number ,o_irid OUT number ,o_branch OUT number ,o_seq OUT number) IS BEGIN SELECT va.irid, va.branch_id, va.edge_sequence INTO o_irid, o_branch, o_seq FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.to_ivid = i_ivid AND va.edge_kind NOT IN ('T','M'); END; ------------------------------------------------ -- Get the audit details for the edge joining -- the specified nodes ------------------------------------------------ PROCEDURE get_audit_details(i_to_ivid IN number ,i_from_ivid IN number ,o_date_created OUT DATE ,o_created_by OUT varchar2 ,o_date_changed OUT DATE ,o_changed_by OUT varchar2) IS BEGIN SELECT date_created ,created_by ,date_changed ,changed_by INTO o_date_created ,o_created_by ,o_date_changed ,o_changed_by FROM I$SDD_VERSION_ASSOCIATIONS WHERE to_ivid = i_to_ivid AND from_ivid = i_from_ivid; END; ----------------------------------------------------- -- Gets the tip node of the specified object on -- the specified branch, if wastebasket is TRUE, -- it looks for the deleted tip, otherwise, looks -- for the current, 'live' tip. -- Pass in the restored node, so we don't select -- that when looking for the live tip ----------------------------------------------------- FUNCTION get_tip(i_irid IN number ,i_branch IN number ,rest_ivid IN number ,wastebasket IN boolean := FALSE) RETURN number IS tip_ivid number; BEGIN IF wastebasket THEN --Look for tip for wastebasket/deleted object --(Nb have to account for case whereobject may -- have been deleted, but version association -- remains). SELECT va.from_ivid INTO tip_ivid FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.irid=i_irid AND va.branch_id=i_branch AND va.edge_kind='T' AND NOT EXISTS (SELECT NULL FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.ivid=va.from_ivid AND ov.wastebasket='N'); ELSE --Look for tip in the land of the living... SELECT va.from_ivid INTO tip_ivid FROM I$SDD_VERSION_ASSOCIATIONS va ,I$SDD_OBJECT_VERSIONS ov WHERE va.irid=i_irid AND va.branch_id=i_branch AND va.edge_kind='T' AND va.from_ivid!=rest_ivid AND ov.ivid=va.from_ivid AND ov.wastebasket='N'; END IF; RETURN tip_ivid; END; ----------------------------------------------------------- -- Find the immeadiate predecessor to this element, -- whether it is in the wastebasket or not ----------------------------------------------------------- FUNCTION find_real_predecessor(i_ivid IN number ,pred_type OUT varchar2) RETURN number IS real_pred number; BEGIN BEGIN --Look for predeccessor in wastebasket... SELECT va.from_ivid,va.edge_kind INTO real_pred,pred_type FROM I$SDD_VERSION_ASSOCIATIONS va ,I$SDD_OBJECT_VERSIONS ov WHERE va.to_ivid = i_ivid AND va.edge_kind NOT IN ('M','T') --Don't get merges or tips! AND ov.ivid=va.from_ivid AND ov.wastebasket='Y'; EXCEPTION WHEN NO_DATA_FOUND THEN --Couldn't find predecessor in the wastebasket, so --immeadiate predecessor is still visible... SELECT va.from_ivid,va.edge_kind INTO real_pred,pred_type FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.to_ivid = i_ivid AND va.edge_kind NOT IN ('M','T') --Don't get merges or tips! -- SDP can get multiple rows after a purge AND rownum = 1; END; RETURN real_pred; END; ----------------------------------------------------------- -- Find the immeadiate successor to this element, -- whether it is in the wastebasket or not ----------------------------------------------------------- FUNCTION find_real_successor(i_ivid IN number ,succ_type OUT varchar2) RETURN number IS real_succ number; BEGIN BEGIN --Look for successor in wastebasket... SELECT va.to_ivid,va.edge_kind INTO real_succ,succ_type FROM I$SDD_VERSION_ASSOCIATIONS va ,I$SDD_OBJECT_VERSIONS ov WHERE va.from_ivid = i_ivid AND va.edge_kind NOT IN ('M','B','R') --Don't get branches,merges or roots! AND ov.ivid=va.to_ivid AND ov.wastebasket='Y'; EXCEPTION WHEN NO_DATA_FOUND THEN --Couldn't find successor in the wastebasket, so --immeadiate successor is still visible... SELECT va.to_ivid,va.edge_kind INTO real_succ,succ_type FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.from_ivid = i_ivid AND va.edge_kind NOT IN ('M','B','R') --Don't get branches,merges or roots! -- SDP can get multiple rows after a purge AND rownum = 1; END; RETURN real_succ; END; ----------------------------------------------------- -- Finds the first predecessor to this element -- that is not in the waste basket ----------------------------------------------------- FUNCTION find_live_predecessor(i_ivid IN number ,pred_notes OUT varchar2) RETURN number IS curr_ivid number; live_pred number; edge_kind varchar2(1); curr_notes varchar2(2000); finished boolean := FALSE; in_wb varchar2(1); BEGIN curr_ivid:=i_ivid; --Get hold of the live predecessor BEGIN --Look for a 'live' edge connected to the node we are interested in SELECT va.from_ivid,va.edge_kind,va.notes INTO live_pred,edge_kind,pred_notes FROM I$SDD_OBJECT_VERSIONS ov ,I$SDD_VERSION_ASSOCIATIONS va WHERE ov.ivid=va.from_ivid AND va.to_ivid=curr_ivid AND va.edge_kind NOT IN ('M','T') --Don't follow merges or tips AND ov.wastebasket = 'N'; EXCEPTION WHEN NO_DATA_FOUND THEN --No live edge exists (node maybe itself be in wb) live_pred:= NULL; END; IF edge_kind IN ('R','B') THEN --Return null if this node is a root RETURN NULL; END IF; --Couldn't find live edge for this node, so go through entries in the --wb in turn until we find a live node IF live_pred IS NULL THEN WHILE NOT finished LOOP SELECT va.from_ivid,va.notes,va.edge_kind,ov.wastebasket INTO curr_ivid,curr_notes,edge_kind,in_wb FROM I$SDD_VERSION_ASSOCIATIONS va ,I$SDD_OBJECT_VERSIONS ov WHERE va.to_ivid=curr_ivid AND ov.ivid=va.from_ivid AND va.edge_kind NOT IN ('M','T'); --Don't follow merges or tips --We've reached the end of the branch without finding a live predecessor IF edge_kind IN ('R','B') THEN curr_ivid:=NULL; finished:=TRUE; END IF; --We've found the live predecessor, so we can finish IF in_wb = 'N' THEN finished:=TRUE; END IF; --Add notes for this version pred_notes:=SUBSTR((curr_notes||pred_notes),1,2000); END LOOP; live_pred:=curr_ivid; END IF; RETURN live_pred; END; ----------------------------------------------------- -- Finds the first successor to this element -- that is not in the waste basket, and build up -- a composite set of notes for any intervening elements -- in the wastebasket ----------------------------------------------------- FUNCTION find_live_successor(i_ivid IN number ,succ_notes OUT varchar2) RETURN number IS curr_ivid number:=0; live_succ number; edge_kind varchar2(1); in_wb varchar2(1); curr_notes varchar2(2000); finished boolean := FALSE; BEGIN curr_ivid:=i_ivid; --Get hold of the live successor BEGIN --Look for a 'live' edge connected to the node we are interested in SELECT va.to_ivid,va.edge_kind,va.notes INTO live_succ,edge_kind,succ_notes FROM I$SDD_OBJECT_VERSIONS ov ,I$SDD_VERSION_ASSOCIATIONS va WHERE ov.ivid=va.to_ivid AND va.from_ivid=curr_ivid AND va.edge_kind NOT IN ('B','M','R') --Don't follow merges, branches or roots! AND ov.wastebasket = 'N'; EXCEPTION WHEN NO_DATA_FOUND THEN --No live edge exists (node maybe itself be in wb) live_succ:= NULL; END; IF edge_kind = 'T' THEN --Return null if this node is a tip RETURN NULL; END IF; --Couldn't find live edge for this node, so go through entries in the --wb in turn until we find a live node IF live_succ IS NULL THEN WHILE NOT finished LOOP SELECT va.to_ivid,va.notes,va.edge_kind,ov.wastebasket INTO curr_ivid,curr_notes,edge_kind,in_wb FROM I$SDD_VERSION_ASSOCIATIONS va ,I$SDD_OBJECT_VERSIONS ov WHERE va.from_ivid=curr_ivid AND ov.ivid=va.to_ivid AND va.edge_kind NOT IN ('B','M','R'); --Don't follow merges, branches or roots! --We've reached the end of the branch without finding a live successor IF edge_kind = 'T' THEN curr_ivid:=NULL; finished:=TRUE; END IF; --We've found the live predecessor, so we can finish IF in_wb = 'N' THEN finished:=TRUE; END IF; --Add notes for this version succ_notes:=SUBSTR((curr_notes||succ_notes),1,2000); END LOOP; live_succ:=curr_ivid; END IF; RETURN live_succ; END; ----------------------------------------------------------------------------- -- delete_diagram -- -- CW 27-Jun-03 Fix bug 3017371 - UNABLE TO EMPTY WASTEBASKET -- -- Delete each diagram in it's own transaction in order to reduce rollback -- storage required. -- In the bug, to delete 1723 diagrams required 2.7 GB of rollback -- This is largely due to the huge volume of associated data in -- i$sdd_diagram_segments which is cascade deleted when a diagram is deleted. -- procedure delete_diagram ( p_diagram_ivid in number ) is pragma autonomous_transaction; begin cleanup_version_associations(null, p_diagram_ivid); delete i$sdd_dia d where d.ivid = p_diagram_ivid ; commit; end delete_diagram; ----------------------------------------------------------------------------- -- delete_diagrams -- -- CW 27-Jun-03 Fix bug 3017371 - UNABLE TO EMPTY WASTEBASKET -- See comments for delete_diagram above. -- procedure delete_diagrams is begin for i in ( select wb.ivid from i$rm_sql_tables tab , sdd_wastebasket wb where tab.table_name = 'SDD_DIA' and wb.table_irid = tab.irid ) loop delete_diagram(i.ivid); end loop; end delete_diagrams; ------------------------------------------------------------- -- jr_wastebasket.CLEAN_UP -- -- Empties the contents of the wastebasket (performs physical -- deletes) for the current workarea. If the current workarea -- is null, then the null_context wastebasket is emptied. -------------------------------------------------------------- PROCEDURE clean_up IS -- CW 26-Jun-03 Fix bug 3017371 - UNABLE TO EMPTY WASTEBASKET -- Remove selection of wb.ivid from sql below as this causes many more rows -- ie. delete statements, to be returned than necessary. -- The ivids of the rows to be deleted are returned within the inner select -- of stmnt. -- Only one delete statement should be returned per table -- -- Note, however that the ivids are required in the call to -- cleanup_version_associations, which is now done in its own loop below -- -- Deletion of diagrams handled in delete_diagrams -- CURSOR clean_up_sql IS SELECT DISTINCT 'delete I$'||tab.name|| ' where ivid in (select wb.ivid '|| ' from SDD_WASTEBASKET wb '|| ' where wb.table_irid = '||TO_CHAR(tab.irid)||')' stmnt ,tab.name tab_name ,tab.irid tab_irid -- ,wb.ivid ov_ivid FROM rm_sql_tables tab ,SDD_WASTEBASKET wb WHERE tab.irid = wb.table_irid and tab.table_name != 'SDD_DIA' ; BEGIN --Check user is allowed to empty wastebasket IF Jr_Context.workarea IS NULL THEN --User must have GLOBAL_PURGE privilege, when no workarea context set IF NOT Jr_Sys_Privs.has_privilege(USER,'GLOBAL_PURGE') THEN --Insuficient privileges to empty wastebasket outside a workarea context. Rmmes.post('CDR',2617); RAISE PURGE_ERROR; END IF; ELSE --User must have delete access right on workarea IF NOT Jr_Acc_Rights.has_access(Jr_Context.workarea,USER,'DEL') THEN --Insuficient privileges to empty wastebasket Rmmes.post('CDR',2616); RAISE PURGE_ERROR; END IF; END IF; -- CW 26-Jun-03 Fix bug 3017371 - UNABLE TO EMPTY WASTEBASKET -- Remove diagrams separately in their own transactions to reduce rollback -- storage requirements delete_diagrams; -- CW 26-Jun-03 Fix bug 3017371 - UNABLE TO EMPTY WASTEBASKET -- Selection of ivid removed from clean_up_sql cursor -- Ivid needed in call to cleanup version associations -- Do this in a separate loop here -- Version associations for diagrams handled in delete_diagrams for i in ( select wb.ivid from i$rm_sql_tables tab , sdd_wastebasket wb where tab.table_name != 'SDD_DIA' and wb.table_irid = tab.irid ) loop -- CW 19-Mar-2000 Fix bug 988180, cleanup branch reference (for this version only) cleanup_version_associations(null, i.ivid); end loop; --Perform actual delete of instance data FOR i IN clean_up_sql LOOP IF i.tab_name IN ('SDD_MUN','SDD_MOD') THEN --Special case for entries in I$SDD_MUN (not handled by FK constraints --or in triggers) execute_sql('delete I$SDD_MUN'|| ' where parent_ivid in (select wb.ivid '|| ' from SDD_WASTEBASKET wb '|| ' where wb.table_irid='||TO_CHAR(i.tab_irid)||')'); END IF; -- CW 19-Mar-2000 Fix bug 988180, cleanup branch reference (for this version only) -- CW 26-Jun-03 Fix bug 3017371 - UNABLE TO EMPTY WASTEBASKET -- This now done in loop above as object ivid no longer available from -- clean_up_sql cursor --cleanup_version_associations(null, i.ov_ivid); --Do actual delete of primary instances execute_sql(i.stmnt); END LOOP; --Tidy up any folder memberships left 'dangling' --Folder members are physically deleted, whether the --folder is checked in or not! DELETE I$SDD_FOLDER_MEMBERS fm WHERE NOT EXISTS (SELECT 1 FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.irid=fm.member_object); --Remove any 'dangling' access rights (access rights need to be --removed when last version of corresponding container/config is removed). DELETE SDW_ACCESS_RIGHTS acc WHERE NOT EXISTS (SELECT 1 FROM I$SDD_CONFIGURATIONS cfg WHERE cfg.irid=acc.object_reference) AND acc.object_type='CFG'; DELETE SDW_ACCESS_RIGHTS acc WHERE NOT EXISTS (SELECT 1 FROM I$SDD_FOLDERS fol WHERE fol.irid=acc.object_reference) AND acc.object_type='CEL'; --Should also roll forward the notes in the I$SDD_VERSION_ASSOCIATIONS table... END clean_up; ------------------------------------------------------------- -- jr_wastebasket.CLEAN_UP_ALL() -- -- Empties the contents of all wastebaskets ------------------------------------------------------------- PROCEDURE clean_up_all IS CURSOR clean_up_sql IS SELECT DISTINCT 'delete I$'||tab.name|| ' where ivid = (select ov.ivid '|| ' from I$SDD_OBJECT_VERSIONS ov '|| ' where ov.wastebasket=''Y'' '|| ' and ov.table_irid = '||TO_CHAR(tab.irid)||')' stmnt ,tab.name tab_name ,tab.irid tab_irid FROM rm_sql_tables tab ,I$SDD_OBJECT_VERSIONS ov WHERE tab.irid=ov.table_irid AND ov.wastebasket='Y'; BEGIN --User must have GLOBAL_PURGE privilege, when no workarea context set IF NOT Jr_Sys_Privs.has_privilege(USER,'GLOBAL_PURGE') THEN --Insuficient privileges to empty all wastebaskets. Rmmes.post('CDR',2618); RAISE PURGE_ERROR; END IF; --Generate and execute delete statements for the base tables FOR i IN clean_up_sql LOOP IF i.tab_name IN ('SDD_MUN','SDD_MOD') THEN --Special case for entries in I$SDD_MUN (not handled by FK constraints --or in triggers) execute_sql('delete I$SDD_MUN'|| ' where parent_ivid in (select ov.ivid '|| ' from I$SDD_OBJECT_VERSIONS ov '|| ' where ov.wastebasket=''Y'' '|| ' and ov.table_irid='||TO_CHAR(i.tab_irid)||')'); END IF; --Do actual delete of primary instances execute_sql(i.stmnt); END LOOP; --Tidy up any folder memberships left 'dangling' --Folder members are physically deleted, whether the --folder is checked in or not! DELETE I$SDD_FOLDER_MEMBERS fm WHERE NOT EXISTS (SELECT 1 FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.irid=fm.member_object); --Remove any 'dangling' access rights (access rights need to be --removed when last version of corresponding container/config is removed). DELETE SDW_ACCESS_RIGHTS acc WHERE NOT EXISTS (SELECT 1 FROM I$SDD_CONFIGURATIONS cfg WHERE cfg.irid=acc.object_reference) AND acc.object_type='CFG'; DELETE SDW_ACCESS_RIGHTS acc WHERE NOT EXISTS (SELECT 1 FROM I$SDD_FOLDERS fol WHERE fol.irid=acc.object_reference) AND acc.object_type='CEL'; END; ------------------------------------------------------------ -- jr_wastebasket.FORCE_FLAG -- jr_wastebasket.PURGE_FLAG -- jr_wastebasket.REMOVE_FM_FLAG -- -- Functions used by view delete triggers to determine -- if a purge or a force purge/force delete operation is -- begin performed, or if operation is to remove membership -- from current folder. ------------------------------------------------------------ FUNCTION purge_flag RETURN boolean IS BEGIN RETURN b_purge_flag; END; FUNCTION force_flag RETURN boolean IS BEGIN RETURN b_force_flag; END; FUNCTION remove_fm_flag RETURN boolean IS BEGIN RETURN b_remove_fm; END; ------------------------------------------------------------ -- jr_wastebasket.SET_SECONDARY_DELETE -- jr_wastebasket.UNSET_SECONDARY_DELETE -- jr_wastebasket.SECONDARY_DELETE -- -- Methods used by view delete triggers to determine -- whether the delete being peformed is a secondary delete, -- arising because of rereferences made to the element being -- deleted. ------------------------------------------------------------ PROCEDURE set_secondary_delete IS BEGIN b_secondary_flag:=TRUE; --Need to reset the remove_fm_flag for the secondary deletes b_remove_fm:=FALSE; --When doing secondary updates, disable wokrarea context checking. --(normally, updates to elements are not permitted with no context -- but the nullifies resulting from deletes are allowed). b_check_wa_ctxt:=jr_context.is_enabled(JR_CONTEXT.CHECK_WORKAREA_CONTEXT); jr_context.disable(JR_CONTEXT.CHECK_WORKAREA_CONTEXT); END; PROCEDURE unset_secondary_delete IS BEGIN b_secondary_flag:=FALSE; --Re-enable the workarea context checks (only if they were enabled in the --first place) IF b_check_wa_ctxt THEN jr_context.enable(JR_CONTEXT.CHECK_WORKAREA_CONTEXT); END IF; END; FUNCTION secondary_delete RETURN boolean IS BEGIN RETURN b_secondary_flag; END; ------------------------------------------------------------ -- jr_wastebasket.REMOVE() -- -- Helper method called by delete triggers. -- This performs the update on I$SDD_OBJECT_VERSIONS to -- place an object in the wastebasket, checks delete -- constraints, and updates version history. ------------------------------------------------------------ PROCEDURE remove(i_ivid IN number) IS ov_state varchar2(1); ov_name varchar2(400); ov_type varchar2(50); tab_name rm_sql_tables.name%TYPE; ov_irid number; in_config boolean; checked_in boolean; remove_fm boolean; dummy pls_integer; CURSOR configs IS SELECT cf.irid, cf.ivid FROM I$SDD_CONFIGURATIONS cf ,I$SDD_CONFIGURATION_MEMBERS cm WHERE cm.object_ivid = i_ivid AND cf.ivid = cm.config_ivid AND cm.kind IS NULL; BEGIN --Reset flag to inform triggers that delete operation removes folder members b_remove_fm:=FALSE; IF Rmdbg.enabled THEN Rmdbg.trace('---------------------------------------------------------'); Rmdbg.trace('JR_WASTEBASKET.REMOVE'); IF force_flag THEN Rmdbg.trace('FORCE'); END IF; IF purge_flag THEN Rmdbg.trace('PURGE'); ELSE Rmdbg.trace('DELETE'); END IF; Rmdbg.trace('Removing '||Jr_Name.get_path(i_ivid,'BRANCH')); Rmdbg.trace('---------------------------------------------------------'); END IF; --Cannot perform delete operations with no workarea context --B1261139 Secondary deletes are allowed with null context --(on delete of last version of an element, WA context is nulled to allow --cascades/updates accross entire repository). IF (NOT purge_flag) AND (Jr_Context.workarea IS NULL) AND (NOT secondary_delete) THEN --but can delete CONFIGURATIONS with no wa context... SELECT COUNT(1) INTO dummy FROM I$SDD_CONFIGURATIONS cfg WHERE cfg.ivid=i_ivid; IF dummy=0 THEN --not a config --Workarea context has not been set. Rmmes.post('CDR',100); RAISE DELETE_ERROR; END IF; END IF; SELECT ov.state,ov.irid,tab.name INTO ov_state,ov_irid,tab_name FROM I$SDD_OBJECT_VERSIONS ov ,rm_sql_tables tab WHERE ov.ivid = i_ivid AND tab.irid = ov.table_irid; --Check we're not attempting to remove a system element... check_not_sys_el(i_ivid,tab_name); --If we're removing a configuration, ensure it is not --used in definition of any workareas IF tab_name='SDD_CONFIGURATIONS' THEN check_remove_cfg(i_ivid); END IF; IF ov_state = 'I' THEN checked_in := TRUE; ELSE checked_in := FALSE; END IF; --If object is checked in, and the purge flag is not set, then we are --removing folder membership. if checked_in and not purge_flag then remove_fm:=TRUE; else remove_fm:=FALSE; end if; --Checked-in configurations cannot be deleted, must be purged --(DELETE on other checked in elements removes folder membership, but -- configs do not have folder membership) if remove_fm and tab_name='SDD_CONFIGURATIONS' then --Cannot delete checked-in Configuration %0!s rmmes.post('CDR',2022,jr_name.get_CRN(i_ivid,'VLABEL')); raise DELETE_ERROR; end if; --Check access rights check_del_ar(i_ivid); --If we're removing a container element, do appropriate checks --(checks are not appropriate if we are removing membership for -- a subcontainer - B1275568) IF tab_name = 'SDD_FOLDERS' and not remove_fm THEN IF (NOT force_flag) THEN IF Jr_Context.workarea IS NOT NULL THEN --In context of workarea, check cotainer is empty check_container_empty(i_ivid); ELSE --Otherwise, ensure we are not removing the last version of a container --that acts as an owner for element(s) check_remove_container_ver(i_ivid); END IF; ELSE --Force delete/purge - cascade to folder members IF (NOT purge_flag) THEN --Simple delete - delete all members delete_container_members(i_ivid); ELSE --Purge - purge all versions of members this is the last owning version for purge_container_members(i_ivid); END IF;--Purge_flag END IF;--Force_flag END IF;--SDD_FOLDERS IF (Jr_Context.workarea IS NOT NULL) AND (NOT force_flag) AND (not purge_flag) THEN --The folder for the object must be cheked out to --delete in workarea context check_parent_folder_state(i_ivid); END IF; --Cannot delete/purge elements if they are referenced (shared) --into containers in the current workarea IF (Jr_Context.workarea IS NOT NULL) AND (NOT force_flag) THEN check_not_shared(ov_irid); END IF; --Checks for purge operations --(checks not neccessary when purging all versions)... IF checked_in AND NOT b_purge_all_versions THEN --Check for remove folder member... IF remove_fm THEN --If DELETING a checked in object version, remove its --folder membership... remove_folder_member(ov_irid,i_ivid); RETURN; END IF; --Can only force purge referencing elements. IF secondary_delete AND NOT force_flag THEN ov_name:=Jr_Name.get_CRN(i_ivid,'BRANCH'); SELECT nls.nls_name INTO ov_type FROM rm_element_type_extensions nls ,I$SDD_OBJECT_VERSIONS ov WHERE nls.for_type=ov.logical_type_id AND ov.ivid=i_ivid; --Purge operation blocked by checked-in referencing %0!s %1!s. Rmmes.post('CDR',2006,ov_type,ov_name); RAISE DELETE_ERROR; END IF; --Check not purging 'significant' node. IF is_significant_node(i_ivid) THEN --Errors are posted by is_significant_node() RAISE PURGE_ERROR; END IF; END IF; --Check for use in workareas and configurations... --(Checks already performed if we're removing all versions) IF NOT b_purge_all_versions THEN --We may have nulled worakrea in order to do secondary deletes across entire --repos, in which case we need to use the saved workarea context to perform the check IF used_in_workarea(i_ivid,NVL(jr_Context.workarea,jr_wastebasket.saved_wa_context)) THEN ov_name:=Jr_Name.get_CRN(i_ivid,'BRANCH'); --Cannot delete %0!s. Object version used in other workareas. Rmmes.post('CDR',2004,ov_name); RAISE REMOVE_ERROR; END IF; --Don't remove if o.v. used in config IF (purge_flag AND force_flag) THEN --If force purging, only block if config is checked in in_config:=used_in_config(i_ivid,TRUE); ELSE in_config:=used_in_config(i_ivid,FALSE); END IF; IF in_config THEN ov_name:=Jr_Name.get_CRN(i_ivid,'BRANCH'); --Cannot delete %0!s. Object version used in configuration(s). Rmmes.post('CDR',2005,ov_name); RAISE REMOVE_ERROR; END IF; END IF; --Put the object version in question into the wastebasket change_ov_wastebasket_state(i_ivid,TRUE); --Update the version history... IF checked_in THEN update_history(i_ivid); END IF; --Ensure that the object does not exist in i$sdd_invalid_objects delete from i$sdd_invalid_objects io where io.object_ivid = i_ivid; END remove; ----------------------------------------------------------- -- Checks that the specified element does not have a -- SYSTEM_ELEMENT_FLAG set to Y. -- Will post appropriate error, and raise exception -- if element is a system element. -- Assumes that the id passed is an ivid, unless use_irid -- is set to TRUE. ----------------------------------------------------------- PROCEDURE check_not_sys_el(elem_id IN number ,tab_name IN varchar2 ,use_irid IN boolean DEFAULT FALSE) IS col_name RM_SQL_COLUMNS.NAME%TYPE; sys_flag varchar2(1); stmt varchar2(500); csr pls_integer; exec pls_integer; BEGIN --Check that table has a SYSTEM_ELEMENT_FLAG or GENERATABLE_FLAG column on it --(for LANGUAGES in Designer/OBM model, GENERATABLE_FLAG is used instead) BEGIN SELECT col.name INTO col_name FROM rm_sql_columns col ,rm_sql_tables tab WHERE col.table_irid=tab.irid AND col.name IN ('SYSTEM_ELEMENT_FLAG','GENERATABLE_FLAG') AND tab.name=tab_name; EXCEPTION WHEN NO_DATA_FOUND THEN --No SYSTEM_ELEMENT_FLAG on table, --so element passes test RETURN; END; IF NOT use_irid THEN stmt:='select '||col_name|| ' from I$'||tab_name|| ' where ivid=:el_id'|| ' and '||col_name||'=''Y'''; ELSE stmt:='select '||col_name|| ' from I$'||tab_name|| ' where irid=:el_id'|| ' and '||col_name||'=''Y'''; END IF; csr:=DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(csr,stmt,DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(csr,'el_id',elem_id); DBMS_SQL.DEFINE_COLUMN(csr,1,sys_flag,1); exec:=DBMS_SQL.EXECUTE(csr); IF DBMS_SQL.FETCH_ROWS(csr) > 0 THEN --Cannot delete system element %0!s Rmmes.post('CDR',2020,Jr_Name.get_path(elem_id,'NAME')); DBMS_SQL.CLOSE_CURSOR(csr); RAISE DELETE_ERROR; END IF; DBMS_SQL.CLOSE_CURSOR(csr); EXCEPTION WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(csr) THEN DBMS_SQL.CLOSE_CURSOR(csr); END IF; RAISE; END; ----------------------------------------------------------- -- Checks that the specified configuration is not used in -- any workarea before allowing delete/purge operations. -- Posts appropraite errors, and raises exception. ----------------------------------------------------------- PROCEDURE check_remove_cfg(i_ivid IN number) IS CURSOR get_workareas IS SELECT wa.name wa_name FROM I$SDD_WORKAREAS wa ,I$SDD_WORKAREA_SPEC_ENTRIES was WHERE wa.irid=was.workarea_irid AND was.config_ivid=i_ivid; used_in_wa boolean := FALSE; BEGIN FOR i IN get_workareas LOOP --Post error for each workarea... --Configuration %0!s is used to define Workarea %1!s Rmmes.post('CDR',2018,Jr_Name.get_CRN(i_ivid,'BRANCH') ,i.wa_name); used_in_wa:=TRUE; END LOOP; IF used_in_wa THEN RAISE DELETE_ERROR; END IF; END; ----------------------------------------------------------- -- Checks that the specified container is empty before -- allowing delete/purge operations. -- Posts appropriate error, and raises exception. ----------------------------------------------------------- PROCEDURE check_container_empty(i_ivid IN number) IS num_members pls_integer; fol_type rm_element_type_extensions.nls_name%TYPE; fol_name varchar2(400); BEGIN --Count how many members there are for this container --B908222 - only count folder members which are not in --wastebasket, and visible... SELECT COUNT(1) INTO num_members FROM I$SDD_FOLDER_MEMBERS fm ,SDD_OBJECT_VERSIONS ov WHERE fm.parent_ivid=i_ivid AND ov.irid=member_object; --If container has any members, raise error IF (num_members > 0)THEN --Get path and nls type for container fol_name:=Jr_Name.get_path(i_ivid,'BRANCH'); SELECT nls.nls_name INTO fol_type FROM rm_element_type_extensions nls ,I$SDD_OBJECT_VERSIONS ov WHERE nls.for_type=ov.logical_type_id AND ov.ivid=i_ivid; IF purge_flag THEN --Cannot purge %0!s. %1!s is not empty. Rmmes.post('CDR',2012,fol_name,fol_type); ELSE --Cannot delete %0!s. %1!s is not empty. Rmmes.post('CDR',2013,fol_name,fol_type); END IF; RAISE DELETE_ERROR; END IF; END; -------------------------------------------------------- -- When purging a container, need to ensure we are -- not removing the last version of the container -- that acts as the owning container for one or -- more of its members. -- Posts errors and raises an exception, if appropriate --------------------------------------------------------- PROCEDURE check_remove_container_ver(i_ivid IN number) IS CURSOR last_fol_members IS SELECT DISTINCT fm1.member_object ,obj_ov.name obj_name ,obj_ov.logical_type_id obj_type FROM I$SDD_FOLDER_MEMBERS fm1 ,I$SDD_OBJECT_VERSIONS obj_ov WHERE fm1.parent_ivid = i_ivid AND fm1.ownership_flag='Y' AND obj_ov.irid=fm1.member_object AND obj_ov.wastebasket='N' AND NOT EXISTS (SELECT 1 FROM I$SDD_FOLDER_MEMBERS fm2 ,I$SDD_OBJECT_VERSIONS fol_ov WHERE fm2.member_object=fm1.member_object AND fm2.folder_reference=fm1.folder_reference AND fm2.parent_ivid!=fm1.parent_ivid AND fol_ov.ivid=fm2.parent_ivid AND fol_ov.wastebasket='N'); fol_path varchar2(400); fol_type RM_ELEMENT_TYPE_EXTENSIONS.NLS_NAME%TYPE; remove_ok boolean:=TRUE; type_name RM_ELEMENT_TYPE_EXTENSIONS.NLS_NAME%TYPE; BEGIN --Cursor returns folder members for which this version of the folder --is the last version that owns the member object FOR fm IN last_fol_members LOOP IF remove_ok THEN remove_ok:=FALSE; SELECT nls.nls_name INTO fol_type FROM RM_ELEMENT_TYPE_EXTENSIONS nls ,I$SDD_OBJECT_VERSIONS ov WHERE ov.ivid=i_ivid AND nls.for_type=ov.logical_type_id; fol_path:=SUBSTR(Jr_Name.get_path(i_ivid,'BRANCH'),1,400); END IF; SELECT nls_name INTO type_name FROM RM_ELEMENT_TYPE_EXTENSIONS WHERE for_type=fm.obj_type; --Cannot purge %0!s as it is the only remaining version that owns %1!s %2!s Rmmes.post('CDR',2021,fol_type||' '||fol_path,fm.obj_name,type_name); END LOOP; IF NOT remove_ok THEN RAISE PURGE_ERROR; END IF; END; --------------------------------------------------------- -- When FORCE DELETING a container, cascade the delete -- to each of its owned members... -- -- CW 25-Jul-02 -- Fix bug 2448356 FORCE PURGE OF APPLICATION FAILS WHEN IT INCLUDES A -- NAMED PREFERENCE SET USAGE --------------------------------------------------------- PROCEDURE delete_container_members(i_ivid IN number) IS CURSOR get_folder_members1 IS SELECT ov.ivid,tab.name tab_name FROM SDD_OBJECT_VERSIONS ov ,I$SDD_FOLDER_MEMBERS fm ,RM_ELEMENT_TYPES et ,RM_SQL_ROW_TYPES rt ,RM_SQL_TABLES tab WHERE fm.parent_ivid=i_ivid AND fm.ownership_flag='Y' AND ov.irid=fm.member_object AND et.id=ov.logical_type_id AND rt.id=et.primary_row_type AND tab.id=rt.table_mapped; -- BUG 1689393 -- When called from FORCE PURGE, ensure that container contents -- used by other versions are not also purged CURSOR get_folder_members2 IS SELECT ov.ivid,tab.name tab_name FROM SDD_OBJECT_VERSIONS ov ,I$SDD_FOLDER_MEMBERS fm ,RM_ELEMENT_TYPES et ,RM_SQL_ROW_TYPES rt ,RM_SQL_TABLES tab WHERE fm.parent_ivid=i_ivid AND fm.ownership_flag='Y' AND ov.irid=fm.member_object AND et.id=ov.logical_type_id AND rt.id=et.primary_row_type AND tab.id=rt.table_mapped AND NOT EXISTS (SELECT 1 FROM I$SDD_FOLDER_MEMBERS fm2 ,I$SDD_OBJECT_VERSIONS fol_ov WHERE fm2.member_object=fm.member_object AND fm2.folder_reference=fm.folder_reference AND fm2.parent_ivid!=fm.parent_ivid AND fol_ov.ivid=fm2.parent_ivid AND fol_ov.wastebasket='N'); BEGIN --Loop over each member of the container, and issue --delete statement for it IF (NOT purge_flag) THEN FOR obj IN get_folder_members1 LOOP execute_sql('delete '||obj.tab_name||' where ivid='||TO_CHAR(obj.ivid)); END LOOP; else FOR obj IN get_folder_members2 LOOP -- BUG 1689393 if (obj.tab_name = 'SDD_PRS') then -- Remember PRS ivid for deletion after the container is deleted add_prs_ivid(obj.ivid); -- Bug 2448356 else execute_sql('delete '||obj.tab_name||' where ivid='||TO_CHAR(obj.ivid)); end if; END LOOP; END IF; END delete_container_members; ----------------------------------------------------------------------------- -- When FORCE PURGING a container, purge all versions of any folder members -- for which this is the last owning version. -- -- CW 25-Jul-02 -- Fix bug 2448356 FORCE PURGE OF APPLICATION FAILS WHEN IT INCLUDES A -- NAMED PREFERENCE SET USAGE -- -- Force purging a checked-in container can fail when it contains a -- PREFERENCE SET which is used by the checked-in container, via a PREFERENCE -- SET USAGE element, since the sdd_prs_d delete trigger (which fires when a -- 'delete sdd_prs' command is issued by the purge_object call below) -- attempts to cascade delete the usages but fails because they are -- checked-in. -- -- The irids of these PREFERENCE SETS are remembered and will be deleted in -- the force_purge method after the container instance has been deleted from -- i$sdd_folders. ----------------------------------------------------------------------------- PROCEDURE purge_container_members (i_ivid IN number) IS CURSOR last_fol_members IS SELECT DISTINCT fm1.member_object mem_irid , et.short_name l_short_type_name FROM I$SDD_FOLDER_MEMBERS fm1 , I$SDD_OBJECT_VERSIONS obj_ov , i$rm_element_types et WHERE fm1.parent_ivid = i_ivid AND fm1.ownership_flag = 'Y' AND obj_ov.irid = fm1.member_object AND obj_ov.wastebasket = 'N' and et.id = obj_ov.logical_type_id AND NOT EXISTS (SELECT 1 FROM I$SDD_FOLDER_MEMBERS fm2 , I$SDD_OBJECT_VERSIONS fol_ov WHERE fm2.member_object = fm1.member_object AND fm2.folder_reference = fm1.folder_reference AND fm2.parent_ivid != fm1.parent_ivid AND fol_ov.ivid = fm2.parent_ivid AND fol_ov.wastebasket = 'N' ) ; BEGIN FOR obj IN last_fol_members LOOP if (obj.l_short_type_name = 'PRS') then -- Remember PRS irid for deletion after the container is deleted add_prs_irid(obj.mem_irid); -- Bug 2448356 else -- Purge all versions of each object that will be 'orphaned' -- by purging this version of the container purge_object(obj.mem_irid); end if; END LOOP; --If we have a workarea context set, then we also need to --purge the current version of any remaining container --contents (to preserve referential integrity in the --context of the current workarea). IF Jr_Context.workarea IS NOT NULL THEN delete_container_members(i_ivid); END IF; END purge_container_members; --------------------------------------------------------- -- Delete/Restore operations in context of workarea -- can only be performed if objects folder is checked -- out. --------------------------------------------------------- PROCEDURE check_parent_folder_state(i_ivid IN number ,restoring IN boolean := FALSE) IS CURSOR get_folders(obj_irid number) IS SELECT ov.state, ov.ivid FROM SDD_OBJECT_VERSIONS ov ,SDD_FOLDER_MEMBERS fm WHERE ov.irid=fm.folder_reference AND fm.member_object=obj_irid; fol_state varchar2(1); ov_irid number; BEGIN --Workarea context should be set IF Jr_Context.workarea IS NULL THEN RETURN; END IF; --Get irid for object --(look on base table as we need to -- pick up objects in the wastebasket -- for restore case) SELECT MIN(ov.irid) INTO ov_irid FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.ivid=i_ivid; --Get the state of the parent folders FOR fol IN get_folders(ov_irid) LOOP IF fol.state = 'I' THEN IF NOT restoring THEN --Cannot delete %0!s from checked-in folder %1!s Rmmes.post('CDR',2017,Jr_Name.get_CRN(i_ivid,'NAME') ,Jr_Name.get_path(fol.ivid,'NAME')); ELSE --Cannot restore into checked-in folder %0!s Rmmes.post('CDR',2053,Jr_Name.get_path(fol.ivid,'NAME')); END IF; RAISE DELETE_ERROR; END IF; END LOOP; END; ----------------------------------------------------------- -- Block purge/delete if object is referenced into -- any containers in the current workarea context ----------------------------------------------------------- PROCEDURE check_not_shared(i_irid IN number) IS CURSOR get_referencing_fol IS SELECT fm.parent_ivid fol_ivid FROM sdd_folder_members fm WHERE fm.member_object=i_irid AND fm.ownership_flag='N'; is_shared boolean:=FALSE; el_type varchar2(240); container_type varchar2(240); BEGIN FOR i IN get_referencing_fol LOOP --May be multiple referencing folders, so --post a seperate error for each SELECT MIN(nls.nls_name) INTO el_type FROM rm_element_type_extensions nls ,I$SDD_OBJECT_VERSIONS ov WHERE nls.for_type=ov.logical_type_id AND ov.irid=i_irid; SELECT MIN(nls.nls_name) INTO container_type FROM rm_element_type_extensions nls ,I$SDD_OBJECT_VERSIONS ov WHERE nls.for_type=ov.logical_type_id AND ov.ivid=i.fol_ivid; --%1!s %0!s is referenced into %2!s %3! Rmmes.post('CDR',2019,Jr_Name.get_path(i_irid,'NAME') ,el_type ,container_type ,Jr_Name.get_path(i.fol_ivid,'NAME')); is_shared:=TRUE; END LOOP; --Raise exception if element is refrenced into container(s) IF is_shared THEN RAISE DELETE_ERROR; END IF; END; ----------------------------------------------------------- -- When 'deleting' a checked-in object version, we simply -- remove it's membership from the current folder. ----------------------------------------------------------- PROCEDURE remove_folder_member(ov_irid IN number ,ov_ivid IN number) IS entry_seq number; entry_rule number; entry_cfg number; root_fol varchar2(1); ov_name varchar2(400); par_fol_versioned boolean:=FALSE; CURSOR parent_folders IS SELECT ov.ivid folder_ivid ,ov.state folder_state FROM SDD_FOLDER_MEMBERS fm ,SDD_OBJECT_VERSIONS ov WHERE ov.irid=fm.folder_reference AND fm.member_object = ov_irid; BEGIN --Can't 'remove' a root folder... SELECT MIN(fol.root_flag) INTO root_fol FROM I$SDD_FOLDERS fol WHERE fol.ivid=ov_ivid; IF (root_fol IS NOT NULL) AND (root_fol='Y') THEN --Root container %0!s must be purged. Rmmes.post('CDR',2014,Jr_Name.get_crn(ov_ivid,'BRANCH')); RAISE DELETE_ERROR; END IF; --Can't remove membership if the parent folder(s) are checked in --(because of referencing of elements into multiple folder, there -- may be more than one folder for the object in a workarea) FOR par_fol IN parent_folders LOOP IF par_fol.folder_state = 'I' THEN --Cannot remove %0!s from checked-in container %1!s Rmmes.post('CDR',2015,Jr_Name.get_CRN(ov_ivid,'BRANCH') ,Jr_Name.get_path(par_fol.folder_ivid,'BRANCH')); RAISE DELETE_ERROR; ELSIF par_fol.folder_state = 'O' THEN --The parent folder is versioned... par_fol_versioned:=TRUE; END IF; END LOOP; --Remove the membership(s) --Need to set secondary delete flag, for delete of folder member (B1290537) set_secondary_delete; DELETE SDD_FOLDER_MEMBERS fm WHERE fm.member_object=ov_irid; unset_secondary_delete; --Now remove the 'orphaned' object from the workarea. IF par_fol_versioned THEN --If the parent folder is versioned (ie checked out), --we do this by setting the wastebasket flag in --I$SDD_WA_CONTEXT to 'X', rather than deleting the --row to allow the object version to be recovered if the --user performs an undo checkout on the folder. --Setting to 'X' hides the object version from wastebasket --and workarea VR views. UPDATE I$SDD_WA_CONTEXT ctxt SET ctxt.wastebasket='X' WHERE ctxt.object_ivid=ov_ivid AND ctxt.workarea_irid=Jr_Context.workarea; ELSE --Otherwise, we can simply delete the entry DELETE I$SDD_WA_CONTEXT ctxt WHERE ctxt.object_ivid=ov_ivid AND ctxt.workarea_irid=Jr_Context.workarea; END IF; --Set the flag to let the delete triggers know we are removing --folder membership b_remove_fm:=TRUE; END; ----------------------------------------------------------- -- Checks the delete/purge access rights on the -- specified object for the current user, -- according to the current settings of the purge and -- force flags. ----------------------------------------------------------- PROCEDURE check_del_ar(i_ivid IN number) IS object_irid number; folder_irid number; el_type varchar2(30); el_stype varchar2(30); el_name varchar2(256); BEGIN --Get hold of element irid and type.... BEGIN SELECT ov.irid,ov.name,et1.short_name,et2.short_name INTO object_irid,el_name,el_type,el_stype FROM I$SDD_OBJECT_VERSIONS ov ,RM_ELEMENT_TYPES et1 ,RM_ELEMENT_TYPES et2 WHERE ov.ivid=i_ivid AND et1.id = ov.logical_type_id AND et2.id = et1.supertypes; EXCEPTION WHEN NO_DATA_FOUND THEN --Trap cases where element type not defined in logical model SELECT ov.irid INTO object_irid FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.ivid=i_ivid; END; --Check for delete outside wa context. Note that triggers will perform --secondary deletes outside workarea context if we are deleting last --version, so also check saved_wa_context (This will be non null if the --triggers nulled the wa context) (B1297988) IF (Jr_Context.workarea IS NULL) AND (saved_wa_context is null) AND (el_type!='CFG') AND (NOT Jr_Sys_Privs.has_privilege(USER,'GLOBAL_PURGE')) THEN --Insufficient privileges to purge outside a workarea context Rmmes.post('CDR',2619); RAISE PURGE_ERROR; END IF; IF el_type ='CFG' OR el_stype='CEL' THEN --If deleting container or config, then check access rights --on itself folder_irid:=object_irid; ELSE --Get the owning folder for the element SELECT MIN(folder_reference) INTO folder_irid FROM I$SDD_FOLDER_MEMBERS fm WHERE member_object=object_irid AND ownership_flag='Y'; END IF; --Check delete access rights --Don't check for 'orphaned' objects IF (folder_irid IS NOT NULL) AND (NOT Jr_Acc_Rights.has_access(folder_irid,USER,'DEL')) THEN --Insufficient access rights to delete %0!s Rmmes.post('CDR',2610,el_name); RAISE DELETE_ERROR; END IF; IF purge_flag AND force_flag THEN --Check force purge IF (NOT Jr_Sys_Privs.has_privilege(USER,'PURGE')) OR (NOT Jr_Sys_Privs.has_privilege(USER,'FORCE')) THEN --Insufficient privileges to perform force purge on %0!s Rmmes.post('CDR',2611,el_name); RAISE PURGE_ERROR; END IF; ELSIF purge_flag THEN --Check purge IF (NOT Jr_Sys_Privs.has_privilege(USER,'PURGE')) THEN --Insufficient privileges to perform purge on %0!s Rmmes.post('CDR',2612,el_name); RAISE PURGE_ERROR; END IF; ELSIF force_flag THEN --Check force delete IF (NOT Jr_Sys_Privs.has_privilege(USER,'FORCE')) THEN --Insufficient privileges to perform force delete on %0!s Rmmes.post('CDR',2613,el_name); RAISE DELETE_ERROR; END IF; END IF; END; ------------------------------------------------------------- -- Checks that the specified node is not 'significant' -- post appropriate errors if it is. -- i.e. -- o Not first node on a branch (if subsequent nodes are not deleted). -- o Not the source for a branch. -- o Not the source for a checkout. -- WS What about merges???? ------------------------------------------------------------- FUNCTION is_significant_node(i_ivid number) RETURN boolean IS ov_irid number; branch_id number; dummy integer; BEGIN --Check we're not the source of a branch SELECT MIN(va.branch_id) INTO branch_id FROM SDD_VERSION_ASSOCIATIONS va--Use the view to ignore deleted stuff... WHERE va.from_ivid = i_ivid AND va.edge_kind = 'B'; IF branch_id IS NOT NULL THEN --Object Version %0!s is source node for branch %1!s Rmmes.post('CDR',2007,Jr_Name.get_CRN(i_ivid,'BRANCH') ,Jr_Version.get_branch_name(branch_id)); RETURN TRUE; END IF; --Check we're not the source of a checkout SELECT MIN(irid) INTO ov_irid FROM SDD_VERSION_ASSOCIATIONS --Use the view to ignore deleted stuff... WHERE from_ivid = i_ivid AND edge_kind IN ('C','L'); IF ov_irid IS NOT NULL THEN --Object Version %0!s is the source for checkout(s). Rmmes.post('CDR',2008,Jr_Name.get_CRN(i_ivid,'BRANCH')); RETURN TRUE; END IF; --Check we're not the first node on a branch... SELECT MIN(irid) INTO ov_irid FROM SDD_VERSION_ASSOCIATIONS --Use the view to ignore deleted stuff... WHERE to_ivid = i_ivid AND edge_kind IN ('R','B'); --If we are the first node on a branch, only allow the purge if this --is also the tip of the branch... IF ov_irid IS NOT NULL THEN SELECT COUNT(from_ivid) INTO dummy FROM SDD_VERSION_ASSOCIATIONS --Use the view to ignore deleted stuff... WHERE from_ivid = i_ivid AND edge_kind = 'T'; IF dummy = 0 THEN branch_id:=Jr_Version.get_current_branch_id(ov_irid,i_ivid); --Object Version %0!s is the root node for branch %1!s Rmmes.post('CDR',2009,Jr_Name.get_CRN(i_ivid,'BRANCH') ,Jr_Version.get_branch_name(branch_id)); RETURN TRUE; END IF; END IF; --We can do the purge... RETURN FALSE; END; ------------------------------------------------------------- -- Maintains wastebasket flage, date_changed and changed_by -- audit columns on I$SDD_OBJECT_VERSIONS ------------------------------------------------------------- PROCEDURE change_ov_wastebasket_state(i_ivid IN number ,set_deleted IN boolean DEFAULT TRUE) IS wb_state varchar2(1) := 'Y'; BEGIN IF NOT set_deleted THEN wb_state:='N'; END IF; UPDATE I$SDD_OBJECT_VERSIONS ov SET ov.wastebasket = wb_state ,ov.date_changed = SYSDATE ,ov.changed_by = USER WHERE ov.ivid=i_ivid; END; ------------------------------------------------------------- -- Updates the version history of an element once it -- has been placed in the wastebasket. ------------------------------------------------------------- PROCEDURE update_history(i_ivid IN number) IS live_pred number; live_succ number; real_pred number; real_succ number; real_pred_type varchar2(1); real_succ_type varchar2(1); rem_ivid number; succ_irid number; succ_seq number; succ_branch number; branch_name varchar2(255); pred_notes varchar2(2000); succ_notes varchar2(2000); l_date_created DATE; l_created_by varchar2(30); l_date_changed DATE; l_changed_by varchar2(30); BEGIN rem_ivid:=i_ivid; --Get the immeadiate predecessor for this element real_pred:=find_real_predecessor(rem_ivid,real_pred_type); IF real_pred_type IN ('B','R') THEN --Don't need to update history when removing root/branch nodes... RETURN; END IF; --Check if node we are removing is a tip... SELECT MIN(edge_kind) INTO real_succ_type FROM I$SDD_VERSION_ASSOCIATIONS WHERE edge_kind='T' AND to_ivid=rem_ivid; --Special case if version we are removing is the tip node IF real_succ_type = 'T' THEN remove_tip_node(rem_ivid,real_pred); RETURN; END IF; --Get the immeadiate successor and predecessor for this element real_succ:=find_real_successor(rem_ivid,real_succ_type); --Get the predecessor and successor for this element that are --not in the wastebasket (i.e. 'live'). live_pred:=find_live_predecessor(rem_ivid,pred_notes); live_succ:=find_live_successor(rem_ivid,succ_notes); --Create/Update/Delete associations to make version history --consistent. IF (live_succ!=real_succ) THEN --Immeadiate successor is already in wastebasket, so --update existing 'extra' link to point from --the previous 'live' node. --Get hold of original audit details for the edge we are 'moving' get_audit_details(live_succ ,rem_ivid ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); --Update the edge UPDATE I$SDD_VERSION_ASSOCIATIONS SET from_ivid = live_pred --Update to point from previous live node... ,notes = SUBSTR((succ_notes||pred_notes),1,2000) WHERE to_ivid = live_succ --...to next live node... AND from_ivid = rem_ivid; --...rather than from node we are removing. --Restore the original audit details UPDATE I$SDD_VERSION_ASSOCIATIONS SET created_by = l_created_by ,date_created = l_date_created ,date_changed = l_date_changed ,changed_by = l_changed_by WHERE to_ivid = live_succ AND from_ivid = live_pred; IF (live_pred!=real_pred) THEN -- Immeadiate predecessor is ALSO in the wastebasket, so -- remove the current 'extra' edge between the previous 'live' -- node and the node we are removing. DELETE I$SDD_VERSION_ASSOCIATIONS WHERE to_ivid = rem_ivid --Delete 'extra' edge between this node AND from_ivid = live_pred; --and the previous live node. END IF; ELSIF (live_pred!=real_pred) THEN -- Immeadiate successor isn't in wastebasket, but predecessor is, -- so update exsisting 'extra' edge to point from the 'live' -- predecessor to the immediate successor node, instead of the -- node we are removing. get_edge_details(live_succ,succ_irid,succ_branch,succ_seq); --Get hold of original audit details for the edge we are replacing get_audit_details(live_succ ,rem_ivid ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); UPDATE I$SDD_VERSION_ASSOCIATIONS SET to_ivid = live_succ --Update edge to point to live sucessor... ,edge_sequence = succ_seq ,notes = SUBSTR((succ_notes||pred_notes),1,2000) ,date_created = l_date_created ,created_by = l_created_by ,date_changed = l_date_changed ,changed_by = l_changed_by WHERE from_ivid = live_pred --...from previous live node... AND to_ivid = rem_ivid; --...rather than to removed node. ELSE -- Immediate predecessor and successor are 'live', so -- need to insert an 'extra' edge... get_edge_details(live_succ,succ_irid,succ_branch,succ_seq); --Get hold of original audit details for the edge we are replacing get_audit_details(live_succ ,rem_ivid ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); INSERT INTO I$SDD_VERSION_ASSOCIATIONS (IRID ,TO_IVID ,FROM_IVID ,EDGE_KIND ,BRANCH_ID ,EDGE_SEQUENCE ,NOTES ,DATE_CREATED ,CREATED_BY ,DATE_CHANGED ,CHANGED_BY) VALUES (succ_irid ,live_succ ,live_pred ,'N' ,succ_branch ,succ_seq ,SUBSTR((succ_notes||pred_notes),1,2000) ,l_date_created ,l_created_by ,l_date_changed ,l_changed_by); END IF; END; PROCEDURE remove_tip_node(i_ivid IN number ,real_pred IN number) IS live_pred number; tip_irid number; tip_seq number; tip_branch number; pred_notes varchar2(2000); BEGIN --Get hold of the predecessor of the element we are removing --that is not in the wastebasket... live_pred:=find_live_predecessor(i_ivid,pred_notes); --Get hold of details of the tip we're removing SELECT va.irid,va.branch_id,va.edge_sequence INTO tip_irid, tip_branch,tip_seq FROM I$SDD_VERSION_ASSOCIATIONS va WHERE va.to_ivid=i_ivid AND va.edge_kind='T'; --We're removing the tip node, so the 'live' predecessor becomes --the new tip - but use the edge sequence of the old tip, --so the version service can maintain correct version sequence info. INSERT INTO I$SDD_VERSION_ASSOCIATIONS (IRID ,TO_IVID ,FROM_IVID ,EDGE_KIND ,BRANCH_ID ,EDGE_SEQUENCE) VALUES (tip_irid ,live_pred ,live_pred ,'T' ,tip_branch ,tip_seq); --If immeadiate predecessor was in wastebasket, then need to --delete the 'extra' edge linking the 'live' predecessor to --the node we are removing IF live_pred!=real_pred THEN DELETE I$SDD_VERSION_ASSOCIATIONS --Delete 'extra' edge between... WHERE to_ivid = i_ivid --...node we are removing... AND from_ivid = live_pred; --...and the previous 'live' node. END IF; END; ------------------------------------------------------------ -- Returns TRUE if the specified object version -- is used in any workarea OTHER than the -- CURRENT workarea. -- If no workarea context is set, then returns false if -- object version used in ANY workarea. ------------------------------------------------------------ FUNCTION used_in_workarea(i_ivid in number ,curr_wa in number default jr_context.workarea) RETURN boolean IS dummy number; BEGIN SELECT COUNT(ctxt.object_ivid) INTO dummy FROM I$SDD_WA_CONTEXT ctxt WHERE ctxt.object_ivid=i_ivid AND ctxt.wastebasket='N' AND ctxt.workarea_irid IN (SELECT irid FROM I$SDD_WORKAREAS wa WHERE curr_wa IS NULL OR wa.irid!=curr_wa); IF dummy > 0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; ------------------------------------------------------------ -- Returns TRUE if the specified object version is used -- in any configuration. -- If checked_in is set to TRUE, then the method only -- checks checked-in configurations. ------------------------------------------------------------ FUNCTION used_in_config(i_ivid IN number ,checked_in IN boolean:=FALSE) RETURN boolean IS dummy number; BEGIN IF NOT checked_in THEN SELECT COUNT(cm.object_ivid) INTO dummy FROM I$SDD_CONFIGURATION_MEMBERS cm ,I$SDD_CONFIGURATIONS cfg WHERE cfg.ivid=cm.config_ivid AND cm.object_ivid = i_ivid; ELSE SELECT COUNT(cm.object_ivid) INTO dummy FROM I$SDD_OBJECT_VERSIONS ov ,I$SDD_CONFIGURATION_MEMBERS cm ,I$SDD_CONFIGURATIONS cfg WHERE cfg.ivid=cm.config_ivid AND ov.ivid=cfg.ivid AND ov.state='I' AND cm.object_ivid = i_ivid; END IF; IF dummy > 0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; ----------------------------------------------------------------------------- -- jr_wastebasket.cleanup_version_associations -- -- CW 19-Mar-2000 Fix bug 988180 -- Can never delete a Branch that has ever been used because there is a -- foreign key from i$sdd_version_associations.branch_id to i$sdd_branches. -- Rows in i$sdd_version_associations are never deleted (even after purge) -- so the referenced branch rows can never be deleted. -- We cannot physically delete the rows in i$sdd_version_associations during -- purge because the user may later restore the rows from the wastebasket -- and the version history could not be restored. -- However, we can do it when emptying the wastebasket or physically deleting -- rows during purge (see procedure physical_delete below) because these -- operations cannot be undone. -- Remove the rows from i$sdd_version_associations here (purge will have -- already 'patched up' the version tree) -- -- CW 26-Apr-2001 Correction to previous fix above. -- When this method is called via physical_delete, via -- purge_insignificant_versions both irid *and* ivid are passed in. Only the -- associations related to the ivid should be removed but the order of the -- original test checked irid first. Reversed order of test to fix this. ----------------------------------------------------------------------------- PROCEDURE cleanup_version_associations ( p_irid IN i$sdd_object_versions.irid%type , p_ivid IN i$sdd_object_versions.ivid%type ) IS BEGIN if p_ivid is not null then delete i$sdd_version_associations va where (va.from_ivid = p_ivid or va.to_ivid = p_ivid) ; elsif p_irid is not null then delete i$sdd_version_associations va where va.irid = p_irid ; end if; END cleanup_version_associations; -- SDP Added this procedure for bug 1316669 - ------------------------------------------------------------- -- jr_wastebasket.PHYSICAL_DELETE -- -- Performs a physical delete of an object e.g. after a purge -------------------------------------------------------------- PROCEDURE physical_delete (p_irid i$sdd_object_versions.irid%type ,p_ivid i$sdd_object_versions.ivid%type) IS l_stmt varchar2(500); l_tab_name rm_sql_tables.name%type; l_tab_irid rm_sql_tables.irid%type; l_irid i$sdd_object_versions.irid%type; BEGIN -- -- If we dont know the irid, get it from ov -- if (p_irid is null) then BEGIN select ov.irid into l_irid from i$sdd_object_versions ov where ov.ivid = p_ivid; END; else l_irid := p_irid; end if; -- -- Construct delete statement, getting table name from rm -- if (p_ivid is null) then -- Delete the entire object BEGIN SELECT 'delete I$'||tab.name|| ' where irid = ' || TO_CHAR(p_irid) stmt ,tab.name tab_name ,tab.irid tab_irid INTO l_stmt, l_tab_name, l_tab_irid FROM rm_sql_tables tab ,i$sdd_object_versions wb WHERE tab.irid=wb.table_irid AND wb.irid=p_irid AND rownum = 1; END; else BEGIN -- Just delete a specific object version SELECT 'delete I$'||tab.name|| ' where ivid = ' || TO_CHAR(p_ivid) stmt ,tab.name tab_name ,tab.irid tab_irid INTO l_stmt, l_tab_name, l_tab_irid FROM rm_sql_tables tab ,i$sdd_object_versions wb WHERE tab.irid=wb.table_irid AND wb.ivid=p_ivid; END; END IF; --Perform actual delete of instance data IF l_tab_name IN ('SDD_MUN','SDD_MOD') THEN -- Special case for entries in I$SDD_MUN (not handled by FK constraints -- or in triggers) if (p_ivid is null) then execute_sql('delete I$SDD_MUN'|| ' where parent_ivid in (select ov.ivid from i$sdd_object_versions ov where ov.irid = ' || TO_CHAR(l_irid) || ' )'); else execute_sql('delete I$SDD_MUN'|| ' where parent_ivid = ' || TO_CHAR(p_ivid)); end if; END IF; -- CW 19-Mar-2001 Fix bug 988180, cleanup branch references for rows being deleted -- NB. Must do this BEFORE deleting rows from i$... tables as triggers will remove -- rows in i$sdd_object_versions which are needed by the following method cleanup_version_associations(p_irid, p_ivid); --Do actual delete of primary instances execute_sql(l_stmt); --Tidy up any folder memberships left 'dangling' --Folder members are physically deleted, whether the --folder is checked in or not! DELETE I$SDD_FOLDER_MEMBERS fm WHERE NOT EXISTS (SELECT 1 FROM I$SDD_OBJECT_VERSIONS ov WHERE ov.irid=fm.member_object) AND fm.member_object = l_irid; --Remove any 'dangling' access rights (access rights need to be --removed when last version of corresponding container/config is removed). DELETE SDW_ACCESS_RIGHTS acc WHERE NOT EXISTS (SELECT 1 FROM I$SDD_CONFIGURATIONS cfg WHERE cfg.irid=acc.object_reference) AND acc.object_type='CFG' AND acc.object_reference=l_irid; DELETE SDW_ACCESS_RIGHTS acc WHERE NOT EXISTS (SELECT 1 FROM I$SDD_FOLDERS fol WHERE fol.irid=acc.object_reference) AND acc.object_type='CEL' AND acc.object_reference = l_irid; --Should also roll forward the notes in the I$SDD_VERSION_ASSOCIATIONS table... END; END jr_wastebasket; /