| Line 1... |
Line 1... |
| 1 |
-- Used by ALL Packages in this release
|
1 |
-- Used by ALL Packages in this release
|
| 2 |
-- :RTAG_ID
|
2 |
-- :RTAG_ID
|
| 3 |
-- :PV_ID
|
3 |
-- :PV_ID
|
| 4 |
--
|
4 |
--
|
| 5 |
-- All the package-vesrions that use a specified package-version in a specified release
|
5 |
-- All the package-versions that use a specified package-version in a specified release
|
| - |
|
6 |
--
|
| - |
|
7 |
-- Note: The outer join of release_content was causing a slow query
|
| - |
|
8 |
-- This sql is a bit faster
|
| 6 |
--
|
9 |
--
|
| 7 |
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 (
|
10 |
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 (
|
| 8 |
SELECT pv_id FROM release_content WHERE rtag_id = :RTAG_ID
|
11 |
SELECT pv_id FROM release_content WHERE rtag_id = :RTAG_ID
|
| 9 |
) AND pd.pv_id = pv1.pv_id AND pd.dpv_id = pv2.pv_id ),
|
12 |
) AND pd.pv_id = pv1.pv_id AND pd.dpv_id = pv2.pv_id ),
|
| 10 |
aa as (select * from (
|
13 |
aa as (select * from (
|
| 11 |
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 )
|
14 |
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 )
|
| 12 |
UNION
|
15 |
UNION
|
| 13 |
SELECT 0 AS ll, to_number(:PV_ID) AS pv_id FROM dual
|
16 |
SELECT 0 AS ll, to_number(:PV_ID) AS pv_id FROM dual
|
| 14 |
) )
|
17 |
) ),
|
| - |
|
18 |
t as (
|
| - |
|
19 |
select MIN(ll) as buildLevel, pv_id from aa group by pv_id
|
| - |
|
20 |
),
|
| - |
|
21 |
rcdata as (
|
| - |
|
22 |
select rtag_id, pv_id, pkg_state, deprecated_state from release_content where rtag_id = :RTAG_ID
|
| - |
|
23 |
),
|
| - |
|
24 |
baseData as (
|
| 15 |
select t.*, p.pkg_name, pv.pkg_version, pv.MODIFIED_STAMP,pv.DLOCKED,pv.pkg_id, rc.deprecated_state, pv.build_time, rc.PKG_STATE, usr.FULL_NAME, usr.USER_EMAIL
|
25 |
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
|
| 16 |
from (select MIN(ll) as buildLevel, pv_id
|
26 |
from t , packages p, package_versions pv, USERS usr
|
| 17 |
from aa group by pv_id
|
27 |
where t.pv_id = pv.pv_id
|
| - |
|
28 |
and pv.pkg_id = p.pkg_id
|
| - |
|
29 |
AND pv.MODIFIER_ID = usr.USER_ID
|
| - |
|
30 |
)
|
| 18 |
) t, packages p, package_versions pv, release_content rc, USERS usr
|
31 |
select bd.*, rc.PKG_STATE, rc.deprecated_state
|
| - |
|
32 |
from baseData bd , rcdata rc
|
| 19 |
where t.pv_id = pv.pv_id and pv.pkg_id = p.pkg_id AND rc.rtag_id(+) = :RTAG_ID and rc.pv_id(+) = pv.pv_id AND pv.MODIFIER_ID = usr.USER_ID
|
33 |
where rc.rtag_id(+) = :RTAG_ID
|
| - |
|
34 |
and rc.pv_id(+) = bd.pv_id
|
| 20 |
order by buildLevel, upper(pkg_name)
|
35 |
order by buildLevel, upper(pkg_name)
|
| 21 |
|
36 |
|
| - |
|
37 |
|