Subversion Repositories DevTools

-- Used to determine the possible build time of a package, including

-- the ripple effects. Used to provide the user with an indication

--  CalcRippleTime.sql

--

--      :RTAG_ID

--      :PV_ID

--

-- All the package-versions that use a specified package-version in a specified release

--

-- Note: The outer join of release_content was causing a slow query

--       This sql is a bit faster

--

-- Based on UsedByAllPackagesInThisRelease.asp

--          Changes to improve the performance of that query could be used here too

--

--

-- Create a small set of packages with dependency and built time information

With small_pd as ( SELECT pv1.build_time, pd.*, pd.pkg_id || pv1.v_ext as pkg_sig, pd.dpkg_id || pv2.v_ext as dpkg_sig 

                    FROM package_dependencies pd, package_versions pv1, package_versions pv2 

                    WHERE pv1.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = :RTAG_ID ) 

                    AND pd.pv_id = pv1.pv_id AND pd.dpv_id = pv2.pv_id ),

-- Remove those packages that are Advisory Ripple, Pegged or from an SDK

bb as ( select * from small_pd 

        where dpkg_sig not in (

            select pkg_id || v_ext as pkg_sig from package_versions 

            where pv_id in (

                select pv_id from advisory_ripple WHERE rtag_id = :RTAG_ID

                union

                select pv_id from pegged_versions WHERE rtag_id = :RTAG_ID

                union

                select pv_id from release_content WHERE rtag_id = :RTAG_ID and sdktag_id is not null

            )

           )

),

-- Generate a tree of packages starting at the package of interest

aa as (SELECT * from (

    SELECT pv_id, build_time 

        FROM bb connect by 

                           prior pkg_sig = dpkg_sig

                           start with dpkg_sig in (SELECT pkg_id || v_ext FROM package_versions where pv_id = :PV_ID )

    UNION

     SELECT pv_id, build_time from package_versions where pv_id = :PV_ID

) ),

-- Sum the build times

total as (SELECT sum(bd.build_time) as BuildTime, 1 as item

            FROM aa bd , release_content rc 

            WHERE rc.rtag_id(+) = :RTAG_ID 

             AND  rc.pv_id(+) =  bd.pv_id ),

-- Package Build Time

pbt as (SELECT build_time as BuildTime, 1 as item from package_versions where pv_id = :PV_ID )

-- Return Total and package times in a single row

select total.buildtime as Total_BuildTime, pbt.BuildTime  from total, pbt where total.item = pbt.item

--