Subversion Repositories DevTools

/* Release Diff */
SELECT qry.*
    FROM (

       /* Removed Packages */
       SELECT 'R' AS CHANGE_TYPE,
              v.VIEW_ID,
              v.VIEW_NAME,
              pv.PKG_ID,
              pv.PV_ID AS PV_ID_A,
              pv.COMMENTS AS COMMENTS_A,
              pkg.PKG_NAME AS PKG_NAME_A,
              pv.PKG_VERSION AS PKG_VERSION_A,
              pv.V_NMM AS MAJOR_MINOR_A,
              get_patch_version(pv.V_MM) AS PATCH_NUMBER_A,
              get_build_number(pv.V_MM) AS BUILD_NUMBER_A,
              rc.deprecated_state AS PKG_STATE_A,
              NULL AS PV_ID_B,
              NULL AS COMMENTS_B,
              NULL AS PKG_NAME_B,
              NULL AS PKG_VERSION_B,
              NULL AS MAJOR_MINOR_B,
              NULL AS PATCH_NUMBER_B,
              NULL AS BUILD_NUMBER_B,
              NULL AS PKG_STATE_B
       FROM (
             /* ReleaseContent@A - ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
             SELECT pv.PKG_ID, pv.V_EXT
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_A
             MINUS
             ((SELECT pv.PKG_ID, pv.V_EXT
                 FROM RELEASE_CONTENT rc,
                      PACKAGE_VERSIONS pv
                WHERE rc.PV_ID = pv.PV_ID
                  AND rc.RTAG_ID = :RTAG_B
               UNION
               SELECT pv.PKG_ID, pv.V_EXT
                 FROM PLANNED pl,
                      PACKAGE_VERSIONS pv
                WHERE pl.PV_ID = pv.PV_ID
                  AND pl.operation = 'A'
                  AND pl.RTAG_ID = :RTAG_B)
              MINUS
              SELECT pv.PKG_ID, pv.V_EXT
                FROM PLANNED pl,
                     PACKAGE_VERSIONS pv
               WHERE pl.PV_ID = pv.PV_ID
                 AND pl.operation = 'S'
                 AND pl.RTAG_ID = :RTAG_B)
            ) ad,
            RELEASE_CONTENT rc,
            PACKAGE_VERSIONS pv,
            PACKAGES pkg,
            VIEWS v
       WHERE rc.RTAG_ID = :RTAG_A
         AND rc.BASE_VIEW_ID = v.VIEW_ID
         AND rc.PV_ID = pv.PV_ID
         AND pv.PKG_ID = pkg.PKG_ID
         AND pv.PKG_ID = ad.PKG_ID
         AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
         AND :REMOVED IS NULL

       UNION

       /* Added Packages */
       SELECT 'A' AS CHANGE_TYPE,
              v.VIEW_ID,
              v.VIEW_NAME,
              pv.PKG_ID,
              NULL AS PV_ID_A,
              NULL AS COMMENTS_A,
              NULL AS PKG_NAME_A,
              NULL AS PKG_VERSION_A,
              NULL AS MAJOR_MINOR_A,
              NULL AS PATCH_NUMBER_A,
              NULL AS BUILD_NUMBER_A,
              NULL AS PKG_STATE_A,
              pv.PV_ID AS PV_ID_B,
              pv.COMMENTS AS COMMENTS_B,
              pkg.PKG_NAME AS PKG_NAME_B,
              pv.PKG_VERSION AS PKG_VERSION_B,
              pv.V_NMM AS MAJOR_MINOR_B,
              get_patch_version(pv.V_MM) AS PATCH_NUMBER_B,
              get_build_number(pv.V_MM) AS BUILD_NUMBER_B,
              rc.deprecated_state as PKG_STATE_B
       FROM (
             /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) - ReleaseContent@A */
             ((SELECT pv.PKG_ID, pv.V_EXT
                 FROM RELEASE_CONTENT rc,
                      PACKAGE_VERSIONS pv
                WHERE rc.PV_ID = pv.PV_ID
                  AND rc.RTAG_ID = :RTAG_B
               UNION
               SELECT pv.PKG_ID, pv.V_EXT
                 FROM PLANNED pl,
                      PACKAGE_VERSIONS pv
                WHERE pl.PV_ID = pv.PV_ID
                  AND pl.operation = 'A'
                  AND pl.RTAG_ID = :RTAG_B)
              MINUS
              SELECT pv.PKG_ID, pv.V_EXT
                FROM PLANNED pl,
                     PACKAGE_VERSIONS pv
               WHERE pl.PV_ID = pv.PV_ID
                 AND pl.operation = 'S'
                 AND pl.RTAG_ID = :RTAG_B)
             MINUS
             SELECT pv.PKG_ID, pv.V_EXT
               FROM RELEASE_CONTENT rc,
                  PACKAGE_VERSIONS pv
              WHERE rc.PV_ID = pv.PV_ID
                AND rc.RTAG_ID = :RTAG_A
            ) ad,
            RELEASE_CONTENT rc,
            PACKAGE_VERSIONS pv,
            PACKAGES pkg,
            VIEWS v
       WHERE rc.RTAG_ID = :RTAG_B
         AND v.VIEW_ID = rc.BASE_VIEW_ID
         AND rc.PV_ID = pv.PV_ID
         AND pv.PKG_ID = pkg.PKG_ID
         AND pv.PKG_ID = ad.PKG_ID
         AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
         AND :ADDED IS NULL

       UNION

       /* UPDATED Packages */
       SELECT ( CASE
                  WHEN ra.V_NMM = rb.V_NMM AND ra.patch_number = rb.patch_number AND ra.build_number != rb.build_number THEN
                    'UR'
                  WHEN ra.PV_ID < rb.PV_ID OR (ra.V_NMM = rb.V_NMM AND ra.patch_number < rb.patch_number ) THEN
                    'UW'
                  ELSE
                    'U'
                END
              ) AS CHANGE_TYPE,
              ra.VIEW_ID,
              ra.VIEW_NAME,
              ra.PKG_ID,
              ra.PV_ID AS PV_ID_A,
              ra.COMMENTS AS COMMENTS_A,
              ra.PKG_NAME AS PKG_NAME_A,
              ra.PKG_VERSION AS PKG_VERSION_A,
              ra.V_NMM AS MAJOR_MINOR_A,
              ra.patch_number AS PATCH_NUMBER_A,
              ra.build_number AS BUILD_NUMBER_A,
              ra.DEPRECATED_STATE AS PKG_STATE_A,
              rb.PV_ID AS PV_ID_B,
              rb.COMMENTS AS COMMENTS_B,
              rb.PKG_NAME AS PKG_NAME_B,
              rb.PKG_VERSION AS PKG_VERSION_B,
              rb.V_NMM AS MAJOR_MINOR_B,
              rb.patch_number AS PATCH_NUMBER_B,
              rb.build_number AS BUILD_NUMBER_B,
              rb.DEPRECATED_STATE AS PKG_STATE_B
       FROM (
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, v.VIEW_NAME, v.VIEW_ID, rc.DEPRECATED_STATE,
                     get_patch_version(pv.V_MM) AS patch_number,
                     get_build_number(pv.V_MM) AS build_number
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv,
                     PACKAGES pkg,
                     VIEWS v
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_A
                 AND pv.PKG_ID = pkg.PKG_ID
                 AND v.VIEW_ID  = rc.BASE_VIEW_ID
              ) ra,
              (
               /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
               ((SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, rc.DEPRECATED_STATE,
                        get_patch_version(pv.V_MM) AS patch_number,
                        get_build_number(pv.V_MM) AS build_number
                   FROM RELEASE_CONTENT rc,
                        PACKAGE_VERSIONS pv,
                        PACKAGES pkg
                  WHERE rc.PV_ID = pv.PV_ID
                    AND rc.RTAG_ID = :RTAG_B
                    AND pv.PKG_ID = pkg.PKG_ID
                 UNION
                 SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE,
                        get_patch_version(pv.V_MM) AS patch_number,
                        get_build_number(pv.V_MM) AS build_number
                   FROM PLANNED pl,
                        PACKAGE_VERSIONS pv,
                        PACKAGES pkg
                  WHERE pl.PV_ID = pv.PV_ID
                    AND pv.PKG_ID = pkg.PKG_ID
                    AND pl.operation = 'A'
                    AND pl.RTAG_ID = :RTAG_B)
                MINUS
                SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE,
                       get_patch_version(pv.V_MM) AS patch_number,
                       get_build_number(pv.V_MM) AS build_number
                  FROM PLANNED pl,
                       PACKAGE_VERSIONS pv,
                       PACKAGES pkg
                 WHERE pl.PV_ID = pv.PV_ID
                   AND pv.PKG_ID = pkg.PKG_ID
                   AND pl.operation = 'S'
                   AND pl.RTAG_ID = :RTAG_B)
              ) rb
       WHERE ra.PKG_ID = rb.PKG_ID
         AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
         AND ra.PV_ID != rb.PV_ID
         AND :UPDATED IS NULL

       UNION

       /* NOT CHANGED Packages - NOTE '0' as change type so these entries appear ahead of additions, in sort order */
       SELECT '0' AS CHANGE_TYPE,
              ra.VIEW_ID,
              ra.VIEW_NAME,
              ra.PKG_ID,
              ra.PV_ID AS PV_ID_A,
              ra.COMMENTS AS COMMENTS_A,
              ra.PKG_NAME AS PKG_NAME_A,
              ra.PKG_VERSION AS PKG_VERSION_A,
              ra.V_NMM AS MAJOR_MINOR_A,
              get_patch_version(ra.V_MM) AS PATCH_NUMBER_A,
              get_build_number(ra.V_MM) AS BUILD_NUMBER_A,
              ra.DEPRECATED_STATE as PKG_STATE_A,
              rb.PV_ID AS PV_ID_B,
              rb.COMMENTS AS COMMENTS_B,
              rb.PKG_NAME AS PKG_NAME_B,
              rb.PKG_VERSION AS PKG_VERSION_B,
              rb.V_NMM AS MAJOR_MINOR_B,
              get_patch_version(rb.V_MM) AS PATCH_NUMBER_B,
              get_build_number(rb.V_MM) AS BUILD_NUMBER_B,
              rb.DEPRECATED_STATE AS PKG_STATE_B
       FROM (
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, v.VIEW_NAME, v.VIEW_ID, rc.DEPRECATED_STATE
                FROM RELEASE_CONTENT rc,
                     PACKAGE_VERSIONS pv,
                     PACKAGES pkg,
                     VIEWS v
               WHERE rc.PV_ID = pv.PV_ID
                 AND rc.RTAG_ID = :RTAG_A
                 AND pv.PKG_ID = pkg.PKG_ID
                 AND v.VIEW_ID = rc.BASE_VIEW_ID
            ) ra,
            (
              /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
              (SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, rc.DEPRECATED_STATE
                 FROM RELEASE_CONTENT rc,
                      PACKAGE_VERSIONS pv,
                      PACKAGES pkg
                WHERE rc.PV_ID = pv.PV_ID
                  AND rc.RTAG_ID = :RTAG_B
                  AND pv.PKG_ID = pkg.PKG_ID
               UNION
               SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE
                 FROM PLANNED pl,
                      PACKAGE_VERSIONS pv,
                      PACKAGES pkg
                WHERE pl.PV_ID = pv.PV_ID
                  AND pl.RTAG_ID = :RTAG_B
                  AND pl.operation = 'A'
                  AND pv.PKG_ID = pkg.PKG_ID)
              MINUS
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE
                FROM PLANNED pl,
                     PACKAGE_VERSIONS pv,
                     PACKAGES pkg
               WHERE pl.PV_ID = pv.PV_ID
                 AND pl.RTAG_ID = :RTAG_B
                 AND pl.operation = 'S'
                 AND pv.PKG_ID = pkg.PKG_ID
            ) rb
       WHERE ra.PKG_ID = rb.PKG_ID
         AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
         AND ra.PV_ID = rb.PV_ID
         AND :NO_CHANGE IS NULL

    ) qry
   /* NOTE: Sorting by change type has been added to ensure diff.asp can display items correctly and filter out */
   /*       duplicates. Order is Unchanged, Additions, Removals, and finally Updates. */
ORDER BY UPPER(qry.VIEW_NAME),UPPER(qry.PKG_NAME_A),CHANGE_TYPE