Subversion Repositories DevTools

CREATE PROCEDURE "RELEASE_MANAGER"."BUILD_TREE" ( nRtag_id IN NUMBER,
                                                                             retSessionNum OUT NUMBER ) IS

/* ---------------------------------------------------------------------------
    Version: 3.0.0
   --------------------------------------------------------------------------- */
    rowCnt                      NUMBER := 0;                                            -- Iterations counter
    iteration           NUMBER := 1;                  -- Maximum number of iterations allowed.
    maxIterations       NUMBER := 50;                   -- This will prevent infinite loops if cyrcular dependencies are found
        UP_THE_TREE     CONSTANT NUMBER DEFAULT 1;
        DOWN_THE_TREE   CONSTANT NUMBER DEFAULT -1;
    sessionNum          NUMBER;
        levelNum                NUMBER;
        
BEGIN
    SELECT SEQ_session_num.nextval INTO sessionNum FROM DUAL;
        
        /*
        ||         Start UP THE TREE
        */
        
        
        /* Packages with no dependencies */    
    INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
                SELECT sessionNum AS SESSION_NUM,
                           iteration AS LEVEL_NUM,
                           UP_THE_TREE AS DIRECTION,
                           rc.pv_id, pv.pkg_id, pv.v_ext
                  FROM release_content rc,
                           package_versions pv
                 WHERE rc.rtag_id = nRtag_id
                   AND rc.pv_id = pv.pv_id
                 MINUS
                SELECT sessionNum AS SESSION_NUM, 
                           iteration AS LEVEL_NUM,
                           UP_THE_TREE AS DIRECTION,
                           dep.pv_id, pv.pkg_id, pv.v_ext
                  FROM package_dependencies dep,
                           package_versions pv
                 WHERE dep.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = nRtag_id )
                   AND dep.pv_id = pv.pv_id;
                   
                   
        /* Browse UP the build tree */     
        iteration := iteration + 1;
    LOOP
        
                INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
                        SELECT DISTINCT 
                               sessionNum AS SESSION_NUM,
                               iteration AS LEVEL_NUM,
                                   UP_THE_TREE AS DIRECTION, 
                                   rdep.pv_id, rdep.pkg_id, rdep.v_ext
                          FROM (  
                                SELECT dep.pv_id, pv.pkg_id, pv.v_ext, dep.dpv_id, dpv.pkg_id AS dpkg_id, dpv.v_ext AS dv_ext
                                  FROM package_dependencies dep,
                                       release_content rc,
                                                   package_versions pv,
                                                   package_versions dpv
                                 WHERE dep.pv_id = rc.pv_id
                                   AND rc.rtag_id = nRtag_id
                                           AND dep.pv_id = pv.pv_id
                                           AND dep.dpv_id = dpv.pv_id
                                        ) rdep,
                                        temp_tree_browse ttb
                         WHERE rdep.dpkg_id  = ttb.pkg_id
                           AND NVL(rdep.dv_ext ,'|LINK_A_NULL|') = NVL(ttb.v_ext,'|LINK_A_NULL|')
                           AND ttb.SESSION_NUM = sessionNum
                           AND ttb.LEVEL_NUM = iteration - 1    
                        MINUS
                        /* Packages with all depencencies NOT matched */  
                        SELECT DISTINCT 
                               sessionNum AS SESSION_NUM,
                               iteration AS LEVEL_NUM, 
                                   UP_THE_TREE AS DIRECTION,
                                   rdep.pv_id, rdep.pkg_id, rdep.v_ext
                          FROM (  
                                SELECT dep.pv_id, pv.pkg_id, pv.v_ext, dep.dpv_id, dpv.pkg_id AS dpkg_id, dpv.v_ext AS dv_ext
                                  FROM package_dependencies dep,
                                       release_content rc,
                                                   package_versions pv,
                                                   package_versions dpv
                                 WHERE dep.pv_id = rc.pv_id
                                   AND rc.rtag_id = nRtag_id
                                           AND dep.pv_id = pv.pv_id
                                           AND dep.dpv_id = dpv.pv_id
                                        ) rdep,
                                        (
                                         SELECT tb.*
                                           FROM temp_tree_browse tb
                                          WHERE tb.SESSION_NUM = sessionNum
                                        ) ttb
                         WHERE rdep.dpkg_id  = ttb.pkg_id (+)
                           AND NVL(rdep.dv_ext ,'|LINK_A_NULL|') = NVL(ttb.v_ext (+),'|LINK_A_NULL|')
                           AND ttb.SESSION_NUM IS NULL;
                
                rowCnt := SQL%ROWCOUNT;
                IF rowCnt > 0 THEN
           iteration := iteration + 1;
                END IF;
        EXIT WHEN (rowCnt < 1) OR (iteration > maxIterations);
    END LOOP;
        
        /*---------------------------------------------------------------------------------------------------------------------*/
        
        /*     Check for unresolved dependencies
        ||  
        */
        /* UNRESOLVED */
         SELECT COUNT(*) INTO rowCnt
           FROM (
                         SELECT pv.pv_id, pv.pkg_id, pv.v_ext
                          FROM release_content rc,
                                   package_versions pv
                         WHERE rc.rtag_id = nRtag_id
                           AND rc.pv_id = pv.pv_id         
                        MINUS
                        SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
                          FROM temp_tree_browse ttb
                         WHERE ttb.session_num = sessionNum
                        );
                                
         
                          
         IF rowCnt > 0 
         THEN
                 /*     Circular dependencies detected.
                 ||     Try to resolve build order from the top now.
                 ||             Start DOWN THE TREE
                 */
                 
                iteration := 0; 
                 /* Top Level packages */       
                INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )    
                        SELECT sessionNum AS SESSION_NUM,
                               iteration AS LEVEL_NUM,
                                   DOWN_THE_TREE AS DIRECTION,
                                   pv.pv_id, pv.pkg_id, pv.v_ext
                          FROM (                   
                                        /* Packages no one depends on ( Top level packages )*/
                                        ( 
                                        /* All parents*/ 
                                        SELECT pv.pkg_id, pv.v_ext
                                  FROM package_dependencies dep,
                                       release_content rc,
                                                   package_versions pv
                                 WHERE dep.pv_id = rc.pv_id
                                   AND rc.rtag_id = nRtag_id
                                           AND dep.pv_id = pv.pv_id
                                         MINUS
                                         /* All children */
                                        SELECT dpv.pkg_id, dpv.v_ext
                                  FROM package_dependencies dep,
                                       release_content rc,
                                                   package_versions dpv
                                 WHERE dep.pv_id = rc.pv_id
                                   AND rc.rtag_id = nRtag_id
                                           AND dep.dpv_id = dpv.pv_id
                                         ) 
                                         MINUS
                                        /* Packages with resolved dependencies from UP THE TREE */ 
                                        SELECT ttb.pkg_id, ttb.v_ext
                                          FROM temp_tree_browse ttb
                                         WHERE ttb.session_num = sessionNum
                                  ) tpkg,
                                  package_versions pv,
                                  release_content rc
                        WHERE rc.rtag_id = nRtag_id
                          AND rc.pv_id = pv.pv_id
                          AND tpkg.pkg_id = pv.pkg_id
                          AND NVL(tpkg.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|');
                
                
                 /* Keep taking packages which no one depende on */                       
                 iteration := iteration - 1;  
                 LOOP     
                           INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
                                        SELECT sessionNum AS SESSION_NUM,
                                               iteration AS LEVEL_NUM,
                                               DOWN_THE_TREE AS DIRECTION,
                                               pv.pv_id, pv.pkg_id, pv.v_ext
                                          FROM (                   
                                                /* All Unresolved */
                                                (
                                                SELECT pv.pkg_id, pv.v_ext
                                                  FROM release_content rc,
                                                           package_versions pv
                                                 WHERE rc.rtag_id = nRtag_id
                                                   AND rc.pv_id = pv.pv_id         
                                                MINUS
                                                SELECT ttb.pkg_id, ttb.v_ext
                                                  FROM temp_tree_browse ttb
                                                 WHERE ttb.session_num = sessionNum
                                                )
                                                 MINUS
                                                (  
                                                 /* Children of Unresolved */  
                                                SELECT dpv.pkg_id, dpv.V_EXT
                                                  FROM (
                                                                SELECT pv.pv_id, pv.pkg_id, pv.v_ext
                                                                  FROM release_content rc,
                                                                           package_versions pv
                                                                 WHERE rc.rtag_id = nRtag_id
                                                                   AND rc.pv_id = pv.pv_id         
                                                                MINUS
                                                                SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
                                                                  FROM temp_tree_browse ttb
                                                                 WHERE ttb.session_num = sessionNum
                                                           ) unr,
                                                           package_dependencies dep,
                                                           package_versions dpv
                                                 WHERE unr.pv_id = dep.pv_id
                                                   AND dep.dpv_id = dpv.pv_id
                                                 )  
                                           ) tpkg,
                                           package_versions pv,
                                           release_content rc
                                        WHERE rc.rtag_id = nRtag_id
                                          AND rc.pv_id = pv.pv_id
                                          AND tpkg.pkg_id = pv.pkg_id
                                          AND NVL(tpkg.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|');
                                          
            rowCnt := SQL%ROWCOUNT;
                IF rowCnt > 0 THEN
                   iteration := iteration - 1;
                        END IF;
            EXIT WHEN (rowCnt < 1);
        END LOOP;
                
         END IF;
         
         
        /*---------------------------------------------------------------------------------------------------------------------*/
        
        /* 
        ||       Save results from temp table
        */      
        /* Clean up build_order table */
        DELETE FROM BUILD_ORDER WHERE rtag_id = nRtag_id;
        
        /* Save UP THE TREE */
        INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID )
                SELECT nRtag_id AS rtag_id,
                           ttb.level_num AS step_num, 
                           ttb.PV_ID
                  FROM temp_tree_browse ttb
                 WHERE ttb.session_num = sessionNum
                   AND ttb.direction = UP_THE_TREE;     
                   
        /*Get last step_num */
        SELECT MAX(ttb.level_num) + 1 INTO levelNum
        FROM temp_tree_browse ttb WHERE ttb.session_num = sessionNum AND ttb.DIRECTION = UP_THE_TREE;
        
        /* UNRESOLVED */
         SELECT 
COUNT(*) INTO rowCnt
           FROM (
                         SELECT pv.pv_id, pv.pkg_id, pv.v_ext
                          FROM release_content rc,
                                   package_versions pv
                         WHERE rc.rtag_id = nRtag_id
                           AND rc.pv_id = pv.pv_id         
                        MINUS
                        SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
                          FROM temp_tree_browse ttb
                         WHERE ttb.session_num = sessionNum
                        );
        
                        
        IF rowCnt > 0
        THEN
                /* Save unresolved packages */
                INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID, UNRESOLVED )
                    SELECT  nRtag_id AS rtag_id,
                                    levelNum AS step_num, 
                                    upv.PV_ID,
                                        'Y' AS UNRESOLVED
                           FROM (
                                         SELECT pv.pv_id, pv.pkg_id, pv.v_ext
                                          FROM release_content rc,
                                                   package_versions pv
                                         WHERE rc.rtag_id = nRtag_id
                                           AND rc.pv_id = pv.pv_id         
                                        MINUS
                                        SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
                                          FROM temp_tree_browse ttb
                                         WHERE ttb.session_num = sessionNum
                                        ) upv;
        END IF; 
                        
        /* Save DOWN THE TREE */
        levelNum := 1000;
        INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID )
                SELECT nRtag_id AS rtag_id,
                           levelNum + ttb.level_num  AS step_num, 
                           ttb.PV_ID
                  FROM temp_tree_browse ttb
                 WHERE ttb.session_num = sessionNum
                   AND ttb.direction = DOWN_THE_TREE;
        
        
        /*---------------------------------------------------------------------------------------------------------------------*/
        
        /* Clean up temp table */
        DELETE FROM TEMP_TREE_BROWSE WHERE session_num = sessionNum;
        
        retSessionNum := sessionNum;
END Build_Tree;
/