Subversion Repositories DevTools

-- Used by ALL Packages in this release
--      :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
--
With small_pd as ( SELECT 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 LEVEL as ll, pv_id 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 0 AS ll, to_number(:PV_ID) AS pv_id FROM dual
) ),
t as (
    select MIN(ll) as buildLevel, pv_id from aa group by pv_id
),
rcdata as (
    select rtag_id, pv_id, pkg_state, deprecated_state from release_content where rtag_id = :RTAG_ID 
),
baseData as (
select t.*,  p.pkg_name, pv.pkg_version, pv.MODIFIED_STAMP,pv.DLOCKED,pv.pkg_id,pv.build_time, usr.FULL_NAME, usr.USER_EMAIL
from t , packages p, package_versions pv, USERS usr
     where t.pv_id = pv.pv_id 
     and pv.pkg_id =  p.pkg_id 
     AND pv.MODIFIER_ID = usr.USER_ID
)
select bd.*, rc.PKG_STATE, rc.deprecated_state
from baseData bd , rcdata rc 
     where rc.rtag_id(+) = :RTAG_ID 
     and   rc.pv_id(+) =  bd.pv_id 
order by buildLevel, upper(pkg_name)