| 5893 |
dpurdie |
1 |
-- Given an :RTAG_ID, :PKG_NAME and :PKG_VERSION
|
|
|
2 |
-- Determine
|
|
|
3 |
-- PKG_NAME
|
|
|
4 |
-- PKG_VERSION
|
|
|
5 |
-- PKG_ID - Package name exists
|
|
|
6 |
-- PV_ID - Package name and version exist
|
|
|
7 |
-- LPV_ID - PV_ID of package in specified release
|
|
|
8 |
-- LPKG_VERSION - Package version in specified release
|
|
|
9 |
--
|
|
|
10 |
-- q0 - static data
|
|
|
11 |
with q0 as (
|
|
|
12 |
SELECT 1 as etype, :PKG_NAME as pkg_name, :PKG_VERSION as pkg_version from dual
|
|
|
13 |
),
|
|
|
14 |
-- q1 - Package existance
|
|
|
15 |
q1 as (
|
|
|
16 |
SELECT 1 as etype, p.pkg_id
|
|
|
17 |
FROM packages p WHERE upper(p.pkg_name) = upper(:PKG_NAME)
|
|
|
18 |
),
|
|
|
19 |
-- q2 - Package Version existence
|
|
|
20 |
q2 as(
|
|
|
21 |
|
|
|
22 |
SELECT 1 as etype, pv.pv_id
|
|
|
23 |
FROM package_versions pv
|
|
|
24 |
, q1
|
|
|
25 |
WHERE pv.PKG_ID = q1.PKG_ID
|
|
|
26 |
AND upper(pv.pkg_version) = upper(:PKG_VERSION)
|
|
|
27 |
),
|
|
|
28 |
-- q3 - Package Version in specfied release
|
|
|
29 |
q3 as
|
|
|
30 |
(
|
|
|
31 |
SELECT 1 as etype, rpv.pv_id as lpv_id, rpv.pkg_version as lpkg_version
|
|
|
32 |
FROM RELEASE_CONTENT rc,
|
|
|
33 |
PACKAGE_VERSIONS rpv,
|
|
|
34 |
q1
|
|
|
35 |
WHERE rc.rtag_id = :RTAG_ID
|
|
|
36 |
AND rpv.PKG_ID = q1.PKG_ID
|
|
|
37 |
AND rc.pv_id = rpv.pv_id
|
|
|
38 |
and rpv.v_ext = REGEXP_SUBSTR(:PKG_VERSION,'\.[a-z]+$', 1,1,'i')
|
|
|
39 |
)
|
|
|
40 |
-- Join it all ap
|
|
|
41 |
SELECT q0.pkg_name, q0.pkg_version, q1.pkg_id, q2.pv_id, q3.lpv_id, q3.lpkg_version
|
|
|
42 |
FROM q0, q1, q2, q3
|
|
|
43 |
WHERE q1.etype(+) = q0.etype
|
|
|
44 |
AND q2.etype(+) = q0.etype
|
|
|
45 |
AND q3.etype(+) = q1.etype
|