Subversion Repositories DevTools

/* Added Components */
SELECT rc.NAME,
           rc.BYTE_SIZE,
           rc.CRC_CKSUM,
           3 AS DIFF_STATE
  FROM (
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
                           rc.BYTE_SIZE, 
                           rc.CRC_CKSUM
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :PV_ID
                   AND rc.OS_ID = :OS_ID
           ) rc,
           (
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :PV_ID
                   AND rc.OS_ID = :OS_ID
                MINUS 
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :COMPARE_PV_ID 
                   AND rc.OS_ID = :OS_ID
           ) crc
 WHERE rc.NAME = crc.NAME   
 
UNION 
 
/* Removed Components */
SELECT rc.NAME,
           rc.BYTE_SIZE,
           rc.CRC_CKSUM,
           2 AS DIFF_STATE
  FROM (
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
                           rc.BYTE_SIZE, 
                           rc.CRC_CKSUM
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :COMPARE_PV_ID 
                   AND rc.OS_ID = :OS_ID
           ) rc,
           (
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :COMPARE_PV_ID 
                MINUS 
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :PV_ID 
                   AND rc.OS_ID = :OS_ID
           ) crc
 WHERE rc.NAME = crc.NAME          
 
UNION 
 
SELECT pv.NAME,
       pv.BYTE_SIZE, 
           pv.CRC_CKSUM,
           DECODE ( pv.CRC_CKSUM, cpv.CRC_CKSUM, 0, 1 ) AS DIFF_STATE
  FROM (
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
                           rc.BYTE_SIZE, 
                           rc.CRC_CKSUM
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :PV_ID 
                   AND rc.OS_ID = :OS_ID
           ) pv,
           (
                SELECT rc.FILE_PATH || rc.FILE_NAME AS NAME,
                           rc.BYTE_SIZE, 
                           rc.CRC_CKSUM
                  FROM PRODUCT_COMPONENTS rc
                 WHERE rc.PV_ID = :COMPARE_PV_ID
                   AND rc.OS_ID = :OS_ID
           ) cpv
 WHERE pv.NAME = cpv.NAME