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