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'

/