Subversion Repositories DevTools

Rev

Blame | Last modification | View Log | RSS feed 

CREATE PACKAGE BODY "RELEASE_MANAGER"."PK_ARCHIVE" 
IS
/*
------------------------------
||  Author:  Rupesh Solanki
||  Date:    26 October 2006
||  Version:   1.0
------------------------------
*/
/*---------------------------*/
PROCEDURE populate_packages_table IS

BEGIN
                  INSERT INTO archive_manager.PACKAGES
                  (
                  select * from packages where pkg_id not in 
                                  (select pkg_id from archive_manager.packages)
                  );

          INSERT INTO archive_manager.PROCESSES
          (
          select * from processes where proc_id not in 
            (select proc_id from archive_manager.processes)
          );
            


END;
/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE populate_archive_data_table (
      nrtagid   IN   release_tags.rtag_id%TYPE
   )
   IS
/*
Reason: To populate the archive_data table with information regarding the
         number of package versions that exist in other releases.
*/
      numcount      NUMBER;
      numusedby     NUMBER;
      numruntime    NUMBER;
      
      /* Get all the package versions in the release */
      CURSOR archive_cur
      IS
         SELECT pv_id
           FROM release_content
          WHERE rtag_id = nrtagid;

      archive_rec   archive_cur%ROWTYPE;
   BEGIN
/*--------------- Business Rules Here -------------------*/
/*-------------------------------------------------------*/
      OPEN archive_cur;

      FETCH archive_cur
       INTO archive_rec;

      WHILE archive_cur%FOUND
      LOOP
        /* How many packages depend on this package version? */
         SELECT COUNT (*)
           INTO numusedby
           FROM package_dependencies
          WHERE dpv_id = archive_rec.pv_id;
        
        /* How many project releases use this package version?  */
         SELECT COUNT (*)
           INTO numcount
           FROM release_content
          WHERE pv_id = archive_rec.pv_id;

        /* How many packages have this package version as runtime
        dependency? */
         SELECT COUNT (*)
           INTO numruntime
           FROM runtime_dependencies
          WHERE rtd_id = archive_rec.pv_id;

        /* Insert into the archive_data table if they are not runtime dependant
        and package dependant and they exist in that particular release only*/
         IF numusedby = 0 AND numcount = 1 AND numruntime = 0
         THEN
            INSERT INTO archive_data
                        (rtag_id, pv_id
                        )
                 VALUES (nrtagid, archive_rec.pv_id
                        );
         END IF;

         FETCH archive_cur
          INTO archive_rec;
      END LOOP;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE migrate_pv_to_archive_schema (
      nrtagid   IN   release_tags.rtag_id%TYPE
   )
   IS
   BEGIN

      /* MIGRATION - PACKAGE_VERSIONS */
      INSERT INTO archive_manager.package_versions
         SELECT *
           FROM package_versions
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* MIGRATION - PACKAGE_BUILD_ENV */
      INSERT INTO archive_manager.package_build_env
         SELECT *
           FROM package_build_env
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM PACKAGE_BUILD_ENV */
      DELETE FROM package_build_env
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - PACKAGE_BUILD_INFO */
      INSERT INTO archive_manager.package_build_info
         SELECT *
           FROM package_build_info
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM PACKAGE_BUILD_info */
      DELETE FROM package_build_info
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);                                                         

      /* MIGRATION - UNIT_TESTS  */
      INSERT INTO archive_manager.unit_tests
         SELECT *
           FROM unit_tests
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM UNIT_TESTS*/
      DELETE FROM unit_tests
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - PACKAGE_PROCESSES */
      INSERT INTO archive_manager.package_processes
         SELECT *
           FROM package_processes
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM PACKAGE_PROCESSES*/
      DELETE FROM package_processes
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - PACKAGE_DEPENDENCIES */
      INSERT INTO archive_manager.package_dependencies
         SELECT *
           FROM package_dependencies
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM PACKAGE_DEPENDENCIES*/
      DELETE FROM package_dependencies
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - CODE_REVIEWS */
      INSERT INTO archive_manager.code_reviews
         SELECT *
           FROM code_reviews
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM CODE_REVIEWS*/
      DELETE FROM code_reviews
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - RUNTIME_DEPENDENCIES*/
      INSERT INTO archive_manager.runtime_dependencies
         SELECT *
           FROM runtime_dependencies
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM RUNTIME_DEPENDENCIES*/
      DELETE FROM runtime_dependencies
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - PACKAGE_DOCUMENTS */
      INSERT INTO archive_manager.package_documents
         SELECT *
           FROM package_documents
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM PACKAGE_DOCUMENTS*/
      DELETE FROM package_documents
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - PACKAGE_PATCHES */
      INSERT INTO archive_manager.package_patches
         SELECT *
           FROM package_patches
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM PACKAGE_PATCHES*/
      DELETE FROM package_patches
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - CQ_ISSUES */
      INSERT INTO archive_manager.cq_issues
         SELECT *
           FROM cq_issues
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM CQ_ISSUES*/
      DELETE FROM cq_issues
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - ADDITIONAL_NOTES */
      INSERT INTO archive_manager.additional_notes
         SELECT *
           FROM additional_notes
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM ADDITIONAL_NOTES*/
      DELETE FROM additional_notes
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - RELEASE_COMPONENTS */
      INSERT INTO archive_manager.release_components
         SELECT *
           FROM release_components
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM RELEASE_COMPONENTS*/
      DELETE FROM release_components
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - IGNORE_WARNINGS */
      INSERT INTO archive_manager.ignore_warnings
         SELECT *
           FROM ignore_warnings
          WHERE pv_id IN (SELECT pv_id
                            FROM 
archive_data);

      /* PURGE DATA FROM IGNORE_WARNINGS*/
      DELETE FROM ignore_warnings
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* PURGE DATA FROM WORK_IN_PROGRESS */
      DELETE FROM work_in_progress
            WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM PLANNED */
      DELETE FROM planned
            WHERE rtag_id = nrtagid;

      /* MIGRATION - JIRA_ISSUES */
      INSERT INTO archive_manager.jira_issues
         SELECT *
           FROM jira_issues
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM JIRA_ISSUES*/
      DELETE FROM jira_issues
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - PRODUCT_COMPONENTS */
      INSERT INTO archive_manager.product_components
         SELECT *
           FROM product_components
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM PRODUCT_COMPONENTS*/
      DELETE FROM product_components
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);

      /* MIGRATION - ACTION_LOG */
      INSERT INTO archive_manager.action_log
         SELECT *
           FROM action_log
          WHERE pv_id IN (SELECT pv_id
                            FROM archive_data);

      /* PURGE DATA FROM ACTION_LOG*/
      DELETE FROM action_log
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);


   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE migrate_rtag_to_archive_schema (
      nrtagid   IN   release_tags.rtag_id%TYPE
   )
   IS
   BEGIN
      /* MIGRATION - DO_NOT_RIPPLE */
      INSERT INTO archive_manager.do_not_ripple
         SELECT *
           FROM do_not_ripple dnp
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM DO_NOT_RIPPLE */
      DELETE FROM do_not_ripple
            WHERE rtag_id = nrtagid;
                        
      /* MIGRATION - ADVISORY_RIPPLES*/
      INSERT INTO archive_manager.advisory_ripples
         SELECT *
           FROM advisory_ripple dnp
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM ADVISORY_RIPPLES*/
      DELETE FROM advisory_ripple
            WHERE rtag_id = nrtagid;                    

      /* MIGRATION - RELEASE_CONTENT */
      INSERT INTO archive_manager.release_content
         SELECT *
           FROM release_content
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM RELEASE_CONTENT*/
      DELETE FROM release_content
            WHERE rtag_id = nrtagid;

      /* MIGRATION - NOTIFICATION_HISTORY */
      INSERT INTO archive_manager.notification_history
         SELECT *
           FROM notification_history
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM NOTIFICATION_HISTORY*/
      DELETE FROM notification_history
            WHERE rtag_id = nrtagid;

      /* MIGRATION - BUILD_ORDER   */
      INSERT INTO archive_manager.build_order
         SELECT *
           FROM build_order
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM BUILD_ORDER*/
      DELETE FROM build_order
            WHERE rtag_id = nrtagid;

      /* MIGRATION - PROJECT_ACTION_LOG */
      INSERT INTO archive_manager.project_action_log
         SELECT *
           FROM project_action_log
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM PROJECT_ACTION_LOG */
      DELETE FROM project_action_log
            WHERE rtag_id = nrtagid;

      /* MIGRATION - DEPRECATED_PACKAGES */
      INSERT INTO archive_manager.deprecated_packages
         SELECT *
           FROM deprecated_packages
          WHERE rtag_id = nrtagid;

      /* PURGE DATA FROM DEPRECATED_PACKAGES */
      DELETE FROM deprecated_packages
            WHERE rtag_id = nrtagid;
                        
      /* MIGRATION - RELEASE_TAGS */
      INSERT INTO archive_manager.release_tags
         SELECT *
           FROM release_tags
          WHERE rtag_id = nrtagid;
                        
                        
      /* PURGE DATA FROM PACKAGE_VERSIONS*/
      DELETE FROM package_versions
            WHERE pv_id IN (SELECT pv_id
                              FROM archive_data);
                                                          
                                                                                
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE clean_up_archive_data_table (
      nrtagid   IN   release_tags.rtag_id%TYPE
   )
   IS
   BEGIN
      /* Cleaning Up The Archive_Data Table */
      DELETE FROM archive_data
            WHERE rtag_id = nrtagid;
   END;

/*-------------------------------------------------------------------------------------------------------*/
   PROCEDURE write_action_log (
      nuserid   IN   NUMBER,
      nrtagid   IN   release_tags.rtag_id%TYPE
   )
   IS
   BEGIN
      /* Write Into Archive_Action_Log Table */
      INSERT INTO archive_action_log
                  (user_id, date_time_stamp, rtag_id,
                   description
                  )
           VALUES (nuserid, ora_sysdatetime, nrtagid,
                   'Release has been archived to the ARCHIVE_MANAGER schema'
                  );
   END;
/*-------------------------------------------------------------------------------------------------------*/
END pk_archive; 
/
ALTER PACKAGE "RELEASE_MANAGER"."PK_ARCHIVE" 
  COMPILE BODY 
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  TRUE
 REUSE SETTINGS TIMESTAMP '2008-02-18 13:19:10'
/