Subversion Repositories DevTools

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