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
--          Chnages to improve the performance of that query could be used here too
--
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 ),
aa as (SELECT * from (
    SELECT pv_id, build_time 
        FROM small_pd pd 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
) ), 
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 ),
pbt as (SELECT build_time as BuildTime, 1 as item from package_versions where pv_id = :PV_ID )
select total.buildtime as Total_BuildTime, pbt.BuildTime  from total, pbt where total.item = pbt.item