Subversion Repositories DevTools

CREATE PACKAGE BODY "RELEASE_MANAGER"."RM_ISSUES" AS

-- Private Implementation -----------------------------------------------------


        FUNCTION VerIsGrtrThanOrEqlToStart( XXstart IN NUMBER, XX IN NUMBER, YYstart IN NUMBER, YY IN NUMBER, ZZstart IN NUMBER, ZZ IN NUMBER ) RETURN NUMBER
        AS

        BEGIN
                -- start boundary case
                IF ( XXstart < XX ) THEN
                        RETURN 1;

                ELSIF ( XXstart = XX ) THEN

                        -- need to consider YY
                        IF ( YYstart < YY ) THEN
                                RETURN 1;

                        ELSIF ( YYstart = YY ) THEN

                                        -- need to consider ZZ
                                        IF ( ZZstart <= ZZ ) THEN
                                                RETURN 1;

                                        ELSE
                                                RETURN 0;
                                        END IF;

                        ELSE
                                RETURN 0;
                        END IF;
                ELSE
                        RETURN 0;
                END IF;
        EXCEPTION
                WHEN OTHERS THEN
                        RETURN -1;
        END;


        FUNCTION VerIsLessThanOrEqlToEnd( XXend IN NUMBER, XX IN NUMBER, YYend IN NUMBER, YY IN NUMBER, ZZend IN NUMBER, ZZ IN NUMBER ) RETURN NUMBER
        AS

        BEGIN
                -- end boundary case
                IF ( XX < XXend ) THEN
                        RETURN 1;

                ELSIF ( XX = XXend ) THEN

                        -- need to consider YY
                        IF ( YY < YYend ) THEN
                                RETURN 1;

                        ELSIF ( YY = YYend ) THEN

                                        -- need to consider ZZ
                                        IF ( ZZ <= ZZend ) THEN
                                                RETURN 1;

                                        ELSE
                                                RETURN 0;
                                        END IF;
                        ELSE

                                RETURN 0;
                        END IF;
                ELSE
                        RETURN 0;
                END IF;
        EXCEPTION
                WHEN OTHERS THEN
                        RETURN -1;
        END;

        /*
        -       version format:         XX.YY.ZZ.abc
        */
        FUNCTION VersionIsBetween( version IN VARCHAR2, version_start IN VARCHAR2, version_end IN VARCHAR ) RETURN NUMBER
        AS
                XXstart         NUMBER  :=      0;
                XXend           NUMBER  :=      0;
                XX                      NUMBER  :=      0;
                YYstart         NUMBER  :=      0;
                YYend           NUMBER  :=      0;
                YY                      NUMBER  :=      0;
                ZZstart         NUMBER  :=      0;
                ZZend           NUMBER  :=      0;
                ZZ                      NUMBER  :=      0;
                first_dot       NUMBER  :=      0;
                second_dot      NUMBER  :=      0;
                third_dot       NUMBER  :=      0;

                ProjExtstart    VARCHAR2(10);
                ProjExtend              VARCHAR2(10);
                ProjExt                 VARCHAR2(10);

        BEGIN
                -- strip the version number
                first_dot :=  INSTR(version_start, '.', 1, 1);
                second_dot :=  INSTR(version_start, '.', 1, 2);
                third_dot :=  INSTR(version_start, '.', 1, 3);

                XXstart := TO_NUMBER( SUBSTR( version_start, 1, first_dot - 1 ) );
                YYstart := TO_NUMBER( SUBSTR( version_start, first_dot + 1, second_dot - ( first_dot + 1 ) ) );
                ZZstart := TO_NUMBER( SUBSTR( version_start, second_dot + 1, third_dot - ( second_dot + 1 ) ) );
                ProjExtstart := SUBSTR( version_start, third_dot + 1, LENGTH(version_start) - third_dot );

                first_dot :=  INSTR(version_end, '.', 1, 1);
                second_dot :=  INSTR(version_end, '.', 1, 2);
                third_dot :=  INSTR(version_end, '.', 1, 3);

                XXend := TO_NUMBER( SUBSTR( version_end, 1, first_dot - 1 ) );
                YYend := TO_NUMBER( SUBSTR( version_end, first_dot + 1, second_dot - ( first_dot + 1 ) ) );
                ZZend := TO_NUMBER( SUBSTR( version_end, second_dot + 1, third_dot - ( second_dot + 1 ) ) );
                ProjExtend := SUBSTR( version_end, third_dot + 1, LENGTH(version_end) - third_dot );

                first_dot :=  INSTR(version, '.', 1, 1);
                second_dot :=  INSTR(version, '.', 1, 2);
                third_dot :=  INSTR(version, '.', 1, 3);

                XX := TO_NUMBER( SUBSTR( version, 1, first_dot - 1 ) );
                YY := TO_NUMBER( SUBSTR( version, first_dot + 1, second_dot - ( first_dot + 1 ) ) );
                ZZ := TO_NUMBER( SUBSTR( version, second_dot + 1, third_dot - ( second_dot + 1 ) ) );
                ProjExt := SUBSTR( version, third_dot + 1, LENGTH(version) - third_dot );

                -- only include versions if all project extensions are the same
                IF ( ProjExtstart = ProjExt AND ProjExt = ProjExtend ) THEN

                        IF ( VerIsGrtrThanOrEqlToStart( XXstart, XX, YYstart, YY, ZZstart, ZZ ) = 1 AND VerIsLessThanOrEqlToEnd( XXend, XX, YYend, YY, ZZend, ZZ ) = 1 ) THEN
                                RETURN 1;
                        ELSE
                                RETURN 0;
                        END IF;
                ELSE
                        RETURN 0;
                END IF;

        EXCEPTION
                WHEN OTHERS THEN
                        RETURN -1;
        END;

        FUNCTION GetPkgId( pkgName IN VARCHAR2 ) RETURN NUMBER
        AS
            pkg_id NUMBER;
        BEGIN
                SELECT
                        p.PKG_ID
                INTO
                        pkg_id
                FROM
                        PACKAGES p
                WHERE
                        p.PKG_NAME = pkgName;

                RETURN pkg_id;
        EXCEPTION
            WHEN OTHERS THEN
                        dbms_output.put_line('GetPkgId exception: ' || SQLERRM );
                        RETURN -1;
        END;


-- Public Implementation ------------------------------------------------------

        /*
        -- Proc: AllIssues - Gets all issues for a package from dependent packages one level deep
        --
        -- INPUT PARAMETERS:
        --
        --              pkg_name        -       The name of the top level package to get issues for
        --              version_start   -       The start version for the comparison
        --              version_end     -       The end version for the comparison
        */
        PROCEDURE AllIssues( vCursor OUT T_Cur, pkg_name IN VARCHAR2, version_start IN VARCHAR2, version_end IN VARCHAR2 )
        AS
                pkgId   NUMBER;
        BEGIN
                -- get pkg_id of the input package:
                pkgId := GetPkgId( pkg_name );

                OPEN vCursor FOR
                SELECT
                          pv.PKG_ID,
                          pv.PKG_VERSION,
                          pv.PV_ID,
                          i_pkg.ISS_ID,
                          ( SELECT pkg_name FROM PACKAGES WHERE pkg_id = pd.DPKG_ID ) AS DPV_NAME,
                          pd.DPKG_ID,
                          ( SELECT pkg_version FROM PACKAGE_VERSIONS WHERE pv_id = pd.DPV_ID ) AS DPV_VER,
                          pd.DPV_ID,
                          i_dpkg.ISS_ID AS ISSUE_ID
                FROM
                         PACKAGE_VERSIONS pv
                                                          LEFT OUTER JOIN CQ_ISSUES i_pkg ON pv.PV_ID = i_pkg.PV_ID
                                                          LEFT OUTER JOIN PACKAGE_DEPENDENCIES pd ON pv.PV_ID = pd.PV_ID
                                                          LEFT OUTER JOIN CQ_ISSUES i_dpkg ON pd.DPV_ID = i_dpkg.PV_ID
                WHERE
                         pv.PKG_ID = pkgId
                AND
                        VersionIsBetween( pv.PKG_VERSION, version_start, version_end ) = 1
                AND
                         ( i_dpkg.ISS_ID IS NOT NULL OR i_pkg.ISS_ID IS NOT NULL )
                ORDER BY
                         pv.PKG_ID,
                         pv.PV_ID,
                         pd.DPKG_ID,
                         pd.DPV_ID;
        EXCEPTION
                WHEN OTHERS THEN
                        dbms_output.put_line('AllIssues exception: ' || SQLERRM );
        END;


        -- NOTE: make RM_PACKAGE_ISSUES a temporary table when tested ok

        /*
        -- Proc: LoadIssuesTable
        --
        -- Populates Package_Issues table with details of issues from all dependent packages.
        -- This will be for all package versions of the input pkg_name between the
        -- version_start and version_end.
        --
        -- INPUT PARAMETERS:
        --
        --              pkg_name        -       The name of the top level package to get issues for
        --              version_start   -       The start version for the comparison
        --              version_end     -       The end version for the comparison
        */
        PROCEDURE LoadIssuesTable( vCursor OUT T_Cur, pkg_name IN VARCHAR2, version_start IN VARCHAR2, version_end IN VARCHAR2 )
        AS
                pkgId   NUMBER;

                CURSOR pack_vers_cur IS
                        SELECT
                                pv.PV_ID,
                                pv.PKG_VERSION,
                                pv.PKG_ID,
                                p.PKG_NAME
                        FROM
                                PACKAGE_VERSIONS pv
                                        JOIN PACKAGES p ON pv.PKG_ID = p.PKG_ID
                        WHERE
                                 pv.PKG_ID = pkgId
                        AND
                                VersionIsBetween( pv.PKG_VERSION, version_start, version_end ) = 1;

        BEGIN
                DELETE FROM RELEASE_MANAGER.RM_PKG_ISSUES; /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/

                -- get the pkg_id we are finding issues for
                pkgId := GetPkgId( pkg_name );

                -- find all the top level packages we are dealing with and recursively process their issues and their dependent package issues.
                FOR pack_ver_rec IN  pack_vers_cur
                LOOP
                        InsertIssuesForDepends( pack_ver_rec.PV_ID, pack_ver_rec.PKG_ID, pack_ver_rec.PKG_NAME, pack_ver_rec.PV_ID, pack_ver_rec.PKG_VERSION );
                        COMMIT;
                END LOOP;

                -- The output cursor - shows the individual versions of the top level package then were reported on
                OPEN vCursor FOR
                SELECT DISTINCT ISS_ID, PKG_VERSION FROM RM_PKG_ISSUES WHERE ISS_ID IS NOT NULL; /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/
/*removed
                SELECT
                        PKG_VERSION
                FROM
                        RELEASE_MANAGER.RM_PACKAGE_ISSUES;
*/


        EXCEPTION
                WHEN OTHERS THEN
                        dbms_output.put_line('LoadIssuesTable exception: ' || SQLERRM );
        END;


        /*
        -- Proc: InsertIssuesForDepends
        --
        -- Used by LoadIssuesTable to recursively find issues for all dependent packages
        --
        -- INPUT PARAMETERS:
        --
        --              pvID            -       The package version id of the package to get issues for
        --              top_pkgID       -       The package id of the top level package to get issues for - should be the same as pvID when called from LoadIssuesTable (different when called recursively)
        --              top_pkgName -   The package name of the top level package
        --              top_pvID        -       The package version id of the top level package
        --              top_pkgVer  -   The package version description of the top level package
        */
        PROCEDURE InsertIssuesForDepends( pvID IN NUMBER, top_pkgID IN NUMBER, top_pkgName IN VARCHAR2, top_pvID IN NUMBER, 
top_pkgVer IN VARCHAR2 )
        AS
                pkgId           NUMBER;
                issCnt          NUMBER := 0;
                pkgCheck        NUMBER := 0;
                depCheck        NUMBER := 0;

                dpkgName        VARCHAR(50);
                dpkgVersion     VARCHAR(50);

                CURSOR dep_packs_cur IS
                        SELECT
                                DPV_ID
                        FROM
                                PACKAGE_DEPENDENCIES
                        WHERE
                                PV_ID = pvID;

        BEGIN

                -- check to see if the package has been processed previously
                SELECT
                        COUNT(*)
                INTO
                        pkgCheck
                FROM
                        RM_PKG_ISSUES /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/
                WHERE
                        DPV_ID = pvID;

                dbms_output.put_line('pkgCheck: ' || pkgCheck );

                -- Package not already processed (no cyclic dependency) - process it
                IF ( pkgCheck = 0 ) THEN

                        -- check to see if this package version has any issues assigned to it
                        SELECT
                                COUNT(*)
                        INTO
                                issCnt
                        FROM
                                CQ_ISSUES i
                        WHERE
                                i.PV_ID = pvID;

                        dbms_output.put_line('issCnt: ' || issCnt );

                        -- Always enter a marker row into the table even if there are no issues for the package.
                        -- This allows us to pick up any cyclic dependencies.
                        IF ( issCnt > 0 ) THEN
                                -- get issues and insert into RM_PACKAGE_ISSUES
                                /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/

                                INSERT INTO RM_PKG_ISSUES ( PKG_ID, PKG_NAME, PV_ID, PKG_VERSION, DPV_ID, DPKG_NAME, DPKG_VERSION, ISS_DB, ISS_ID )
                                        SELECT DISTINCT
                                                top_pkgID               AS PKG_ID,
                                                top_pkgName             AS PKG_NAME,
                                                top_pvID                AS PV_ID,
                                                top_pkgVer              AS PKG_VERSION,
                                                pv.PV_ID                AS DPV_ID,
                                                p.PKG_NAME              AS DPKG_NAME,
                                                pv.PKG_VERSION  AS DPKG_VERSION,
                                                ci.ISS_DB,
                                                ci.ISS_ID
                                        FROM
                                                PACKAGE_VERSIONS pv
                                                        JOIN PACKAGES p ON pv.PKG_ID = p.PKG_ID
                                                        JOIN CQ_ISSUES ci ON pv.PV_ID = ci.PV_ID
                                        WHERE
                                                pv.PV_ID = pvID;

                        ELSE
                                -- get the dpkg details - there will always be a row returned here
                                SELECT
                                        p.PKG_NAME
                                INTO
                                        dpkgName
                                FROM
                                        PACKAGE_VERSIONS pv
                                                JOIN PACKAGES p ON pv.PKG_ID = p.PKG_ID
                                WHERE
                                         pv.PV_ID = pvID;

                                SELECT
                                        pv.PKG_VERSION
                                INTO
                                        dpkgVersion
                                FROM
                                        PACKAGE_VERSIONS pv
                                WHERE
                                         pv.PV_ID = pvID;

                                -- enter a marker row
                                /*RM_PACKAGE_ISSUES replaced by RM_PKG_ISSUES*/
                                INSERT INTO RM_PKG_ISSUES ( PKG_ID, PKG_NAME, PV_ID, PKG_VERSION, DPV_ID, DPKG_NAME, DPKG_VERSION, ISS_DB, ISS_ID )
                                VALUES (
                                        top_pkgID,
                                        top_pkgName,
                                        top_pvID,
                                        top_pkgVer,
                                        pvID,
                                        dpkgName,
                                        dpkgVersion,
                                        NULL,
                                        NULL );

                        END IF;

                        -- If this package version has dependencies then recurse
                        SELECT
                                COUNT(*)
                        INTO
                                depCheck
                        FROM
                                PACKAGE_DEPENDENCIES
                        WHERE
                                PV_ID = pvID;

                        IF ( depCheck > 0 ) THEN
                                -- get dependencies and call this function recursively for each one
                                FOR dep_rec IN  dep_packs_cur
                                LOOP
                                        InsertIssuesForDepends( dep_rec.DPV_ID, top_pkgID, top_pkgName, top_pvID, top_pkgVer );
                                END LOOP;

                        END IF;

                END IF;

        /*EXCEPTION
                WHEN OTHERS THEN
                        no exception handling required
        */
        END;

END Rm_Issues;
/
ALTER PACKAGE "RELEASE_MANAGER"."RM_ISSUES" 
  COMPILE BODY 
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE
 REUSE SETTINGS TIMESTAMP '2008-05-26 11:58:46'
/