Subversion Repositories DevTools

--  Given an :RTAG_ID, :PKG_NAME and :PKG_VERSION
--  Determine
--      PKG_NAME
--      PKG_VERSION
--      PKG_ID          - Package name exists
--      PV_ID           - Package name and version exist
--      LPV_ID          - PV_ID of package in specified release
--      LPKG_VERSION    - Package version in specified release
--          
-- q0 - static data
with q0 as (
  SELECT 1 as etype, :PKG_NAME as pkg_name, :PKG_VERSION as pkg_version from dual
),
-- q1 - Package existance
q1 as (
  SELECT 1 as etype, p.pkg_id
  FROM packages p WHERE upper(p.pkg_name) = upper(:PKG_NAME)
),
-- q2 - Package Version existence
q2 as(

  SELECT 1 as etype, pv.pv_id
    FROM package_versions pv
    , q1
    WHERE pv.PKG_ID = q1.PKG_ID
    AND upper(pv.pkg_version) = upper(:PKG_VERSION)
),
-- q3 - Package Version in specfied release
q3 as
(
  SELECT 1 as etype, rpv.pv_id as lpv_id, rpv.pkg_version as lpkg_version
     FROM RELEASE_CONTENT rc,
          PACKAGE_VERSIONS rpv, 
          q1
    WHERE rc.rtag_id = :RTAG_ID
      AND rpv.PKG_ID = q1.PKG_ID
      AND rc.pv_id = rpv.pv_id
      and rpv.v_ext = REGEXP_SUBSTR(:PKG_VERSION,'\.[a-z]+$', 1,1,'i')
)
-- Join it all ap
SELECT q0.pkg_name, q0.pkg_version, q1.pkg_id, q2.pv_id, q3.lpv_id, q3.lpkg_version
  FROM q0, q1, q2, q3
  WHERE q1.etype(+) = q0.etype
   AND  q2.etype(+)  = q0.etype
   AND  q3.etype(+)  = q1.etype