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
--