| 221 |
vnguyen |
1 |
CREATE PROCEDURE "RELEASE_MANAGER"."BUILD_TREE" ( nRtag_id IN NUMBER,
|
|
|
2 |
retSessionNum OUT NUMBER ) IS
|
|
|
3 |
|
|
|
4 |
/* ---------------------------------------------------------------------------
|
|
|
5 |
Version: 3.0.0
|
|
|
6 |
--------------------------------------------------------------------------- */
|
|
|
7 |
rowCnt NUMBER := 0; -- Iterations counter
|
|
|
8 |
iteration NUMBER := 1; -- Maximum number of iterations allowed.
|
|
|
9 |
maxIterations NUMBER := 50; -- This will prevent infinite loops if cyrcular dependencies are found
|
|
|
10 |
UP_THE_TREE CONSTANT NUMBER DEFAULT 1;
|
|
|
11 |
DOWN_THE_TREE CONSTANT NUMBER DEFAULT -1;
|
|
|
12 |
sessionNum NUMBER;
|
|
|
13 |
levelNum NUMBER;
|
|
|
14 |
|
|
|
15 |
BEGIN
|
|
|
16 |
SELECT SEQ_session_num.nextval INTO sessionNum FROM DUAL;
|
|
|
17 |
|
|
|
18 |
/*
|
|
|
19 |
|| Start UP THE TREE
|
|
|
20 |
*/
|
|
|
21 |
|
|
|
22 |
|
|
|
23 |
/* Packages with no dependencies */
|
|
|
24 |
INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
|
|
|
25 |
SELECT sessionNum AS SESSION_NUM,
|
|
|
26 |
iteration AS LEVEL_NUM,
|
|
|
27 |
UP_THE_TREE AS DIRECTION,
|
|
|
28 |
rc.pv_id, pv.pkg_id, pv.v_ext
|
|
|
29 |
FROM release_content rc,
|
|
|
30 |
package_versions pv
|
|
|
31 |
WHERE rc.rtag_id = nRtag_id
|
|
|
32 |
AND rc.pv_id = pv.pv_id
|
|
|
33 |
MINUS
|
|
|
34 |
SELECT sessionNum AS SESSION_NUM,
|
|
|
35 |
iteration AS LEVEL_NUM,
|
|
|
36 |
UP_THE_TREE AS DIRECTION,
|
|
|
37 |
dep.pv_id, pv.pkg_id, pv.v_ext
|
|
|
38 |
FROM package_dependencies dep,
|
|
|
39 |
package_versions pv
|
|
|
40 |
WHERE dep.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = nRtag_id )
|
|
|
41 |
AND dep.pv_id = pv.pv_id;
|
|
|
42 |
|
|
|
43 |
|
|
|
44 |
/* Browse UP the build tree */
|
|
|
45 |
iteration := iteration + 1;
|
|
|
46 |
LOOP
|
|
|
47 |
|
|
|
48 |
INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
|
|
|
49 |
SELECT DISTINCT
|
|
|
50 |
sessionNum AS SESSION_NUM,
|
|
|
51 |
iteration AS LEVEL_NUM,
|
|
|
52 |
UP_THE_TREE AS DIRECTION,
|
|
|
53 |
rdep.pv_id, rdep.pkg_id, rdep.v_ext
|
|
|
54 |
FROM (
|
|
|
55 |
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
|
|
|
56 |
FROM package_dependencies dep,
|
|
|
57 |
release_content rc,
|
|
|
58 |
package_versions pv,
|
|
|
59 |
package_versions dpv
|
|
|
60 |
WHERE dep.pv_id = rc.pv_id
|
|
|
61 |
AND rc.rtag_id = nRtag_id
|
|
|
62 |
AND dep.pv_id = pv.pv_id
|
|
|
63 |
AND dep.dpv_id = dpv.pv_id
|
|
|
64 |
) rdep,
|
|
|
65 |
temp_tree_browse ttb
|
|
|
66 |
WHERE rdep.dpkg_id = ttb.pkg_id
|
|
|
67 |
AND NVL(rdep.dv_ext ,'|LINK_A_NULL|') = NVL(ttb.v_ext,'|LINK_A_NULL|')
|
|
|
68 |
AND ttb.SESSION_NUM = sessionNum
|
|
|
69 |
AND ttb.LEVEL_NUM = iteration - 1
|
|
|
70 |
MINUS
|
|
|
71 |
/* Packages with all depencencies NOT matched */
|
|
|
72 |
SELECT DISTINCT
|
|
|
73 |
sessionNum AS SESSION_NUM,
|
|
|
74 |
iteration AS LEVEL_NUM,
|
|
|
75 |
UP_THE_TREE AS DIRECTION,
|
|
|
76 |
rdep.pv_id, rdep.pkg_id, rdep.v_ext
|
|
|
77 |
FROM (
|
|
|
78 |
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
|
|
|
79 |
FROM package_dependencies dep,
|
|
|
80 |
release_content rc,
|
|
|
81 |
package_versions pv,
|
|
|
82 |
package_versions dpv
|
|
|
83 |
WHERE dep.pv_id = rc.pv_id
|
|
|
84 |
AND rc.rtag_id = nRtag_id
|
|
|
85 |
AND dep.pv_id = pv.pv_id
|
|
|
86 |
AND dep.dpv_id = dpv.pv_id
|
|
|
87 |
) rdep,
|
|
|
88 |
(
|
|
|
89 |
SELECT tb.*
|
|
|
90 |
FROM temp_tree_browse tb
|
|
|
91 |
WHERE tb.SESSION_NUM = sessionNum
|
|
|
92 |
) ttb
|
|
|
93 |
WHERE rdep.dpkg_id = ttb.pkg_id (+)
|
|
|
94 |
AND NVL(rdep.dv_ext ,'|LINK_A_NULL|') = NVL(ttb.v_ext (+),'|LINK_A_NULL|')
|
|
|
95 |
AND ttb.SESSION_NUM IS NULL;
|
|
|
96 |
|
|
|
97 |
rowCnt := SQL%ROWCOUNT;
|
|
|
98 |
IF rowCnt > 0 THEN
|
|
|
99 |
iteration := iteration + 1;
|
|
|
100 |
END IF;
|
|
|
101 |
EXIT WHEN (rowCnt < 1) OR (iteration > maxIterations);
|
|
|
102 |
END LOOP;
|
|
|
103 |
|
|
|
104 |
/*---------------------------------------------------------------------------------------------------------------------*/
|
|
|
105 |
|
|
|
106 |
/* Check for unresolved dependencies
|
|
|
107 |
||
|
|
|
108 |
*/
|
|
|
109 |
/* UNRESOLVED */
|
|
|
110 |
SELECT COUNT(*) INTO rowCnt
|
|
|
111 |
FROM (
|
|
|
112 |
SELECT pv.pv_id, pv.pkg_id, pv.v_ext
|
|
|
113 |
FROM release_content rc,
|
|
|
114 |
package_versions pv
|
|
|
115 |
WHERE rc.rtag_id = nRtag_id
|
|
|
116 |
AND rc.pv_id = pv.pv_id
|
|
|
117 |
MINUS
|
|
|
118 |
SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
|
|
|
119 |
FROM temp_tree_browse ttb
|
|
|
120 |
WHERE ttb.session_num = sessionNum
|
|
|
121 |
);
|
|
|
122 |
|
|
|
123 |
|
|
|
124 |
|
|
|
125 |
IF rowCnt > 0
|
|
|
126 |
THEN
|
|
|
127 |
/* Circular dependencies detected.
|
|
|
128 |
|| Try to resolve build order from the top now.
|
|
|
129 |
|| Start DOWN THE TREE
|
|
|
130 |
*/
|
|
|
131 |
|
|
|
132 |
iteration := 0;
|
|
|
133 |
/* Top Level packages */
|
|
|
134 |
INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
|
|
|
135 |
SELECT sessionNum AS SESSION_NUM,
|
|
|
136 |
iteration AS LEVEL_NUM,
|
|
|
137 |
DOWN_THE_TREE AS DIRECTION,
|
|
|
138 |
pv.pv_id, pv.pkg_id, pv.v_ext
|
|
|
139 |
FROM (
|
|
|
140 |
/* Packages no one depends on ( Top level packages )*/
|
|
|
141 |
(
|
|
|
142 |
/* All parents*/
|
|
|
143 |
SELECT pv.pkg_id, pv.v_ext
|
|
|
144 |
FROM package_dependencies dep,
|
|
|
145 |
release_content rc,
|
|
|
146 |
package_versions pv
|
|
|
147 |
WHERE dep.pv_id = rc.pv_id
|
|
|
148 |
AND rc.rtag_id = nRtag_id
|
|
|
149 |
AND dep.pv_id = pv.pv_id
|
|
|
150 |
MINUS
|
|
|
151 |
/* All children */
|
|
|
152 |
SELECT dpv.pkg_id, dpv.v_ext
|
|
|
153 |
FROM package_dependencies dep,
|
|
|
154 |
release_content rc,
|
|
|
155 |
package_versions dpv
|
|
|
156 |
WHERE dep.pv_id = rc.pv_id
|
|
|
157 |
AND rc.rtag_id = nRtag_id
|
|
|
158 |
AND dep.dpv_id = dpv.pv_id
|
|
|
159 |
)
|
|
|
160 |
MINUS
|
|
|
161 |
/* Packages with resolved dependencies from UP THE TREE */
|
|
|
162 |
SELECT ttb.pkg_id, ttb.v_ext
|
|
|
163 |
FROM temp_tree_browse ttb
|
|
|
164 |
WHERE ttb.session_num = sessionNum
|
|
|
165 |
) tpkg,
|
|
|
166 |
package_versions pv,
|
|
|
167 |
release_content rc
|
|
|
168 |
WHERE rc.rtag_id = nRtag_id
|
|
|
169 |
AND rc.pv_id = pv.pv_id
|
|
|
170 |
AND tpkg.pkg_id = pv.pkg_id
|
|
|
171 |
AND NVL(tpkg.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|');
|
|
|
172 |
|
|
|
173 |
|
|
|
174 |
/* Keep taking packages which no one depende on */
|
|
|
175 |
iteration := iteration - 1;
|
|
|
176 |
LOOP
|
|
|
177 |
INSERT INTO TEMP_TREE_BROWSE (SESSION_NUM, LEVEL_NUM, DIRECTION, PV_ID, PKG_ID, V_EXT )
|
|
|
178 |
SELECT sessionNum AS SESSION_NUM,
|
|
|
179 |
iteration AS LEVEL_NUM,
|
|
|
180 |
DOWN_THE_TREE AS DIRECTION,
|
|
|
181 |
pv.pv_id, pv.pkg_id, pv.v_ext
|
|
|
182 |
FROM (
|
|
|
183 |
/* All Unresolved */
|
|
|
184 |
(
|
|
|
185 |
SELECT pv.pkg_id, pv.v_ext
|
|
|
186 |
FROM release_content rc,
|
|
|
187 |
package_versions pv
|
|
|
188 |
WHERE rc.rtag_id = nRtag_id
|
|
|
189 |
AND rc.pv_id = pv.pv_id
|
|
|
190 |
MINUS
|
|
|
191 |
SELECT ttb.pkg_id, ttb.v_ext
|
|
|
192 |
FROM temp_tree_browse ttb
|
|
|
193 |
WHERE ttb.session_num = sessionNum
|
|
|
194 |
)
|
|
|
195 |
MINUS
|
|
|
196 |
(
|
|
|
197 |
/* Children of Unresolved */
|
|
|
198 |
SELECT dpv.pkg_id, dpv.V_EXT
|
|
|
199 |
FROM (
|
|
|
200 |
SELECT pv.pv_id, pv.pkg_id, pv.v_ext
|
|
|
201 |
FROM release_content rc,
|
|
|
202 |
package_versions pv
|
|
|
203 |
WHERE rc.rtag_id = nRtag_id
|
|
|
204 |
AND rc.pv_id = pv.pv_id
|
|
|
205 |
MINUS
|
|
|
206 |
SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
|
|
|
207 |
FROM temp_tree_browse ttb
|
|
|
208 |
WHERE ttb.session_num = sessionNum
|
|
|
209 |
) unr,
|
|
|
210 |
package_dependencies dep,
|
|
|
211 |
package_versions dpv
|
|
|
212 |
WHERE unr.pv_id = dep.pv_id
|
|
|
213 |
AND dep.dpv_id = dpv.pv_id
|
|
|
214 |
)
|
|
|
215 |
) tpkg,
|
|
|
216 |
package_versions pv,
|
|
|
217 |
release_content rc
|
|
|
218 |
WHERE rc.rtag_id = nRtag_id
|
|
|
219 |
AND rc.pv_id = pv.pv_id
|
|
|
220 |
AND tpkg.pkg_id = pv.pkg_id
|
|
|
221 |
AND NVL(tpkg.v_ext,'|LINK_A_NULL|') = NVL(pv.v_ext,'|LINK_A_NULL|');
|
|
|
222 |
|
|
|
223 |
rowCnt := SQL%ROWCOUNT;
|
|
|
224 |
IF rowCnt > 0 THEN
|
|
|
225 |
iteration := iteration - 1;
|
|
|
226 |
END IF;
|
|
|
227 |
EXIT WHEN (rowCnt < 1);
|
|
|
228 |
END LOOP;
|
|
|
229 |
|
|
|
230 |
END IF;
|
|
|
231 |
|
|
|
232 |
|
|
|
233 |
/*---------------------------------------------------------------------------------------------------------------------*/
|
|
|
234 |
|
|
|
235 |
/*
|
|
|
236 |
|| Save results from temp table
|
|
|
237 |
*/
|
|
|
238 |
/* Clean up build_order table */
|
|
|
239 |
DELETE FROM BUILD_ORDER WHERE rtag_id = nRtag_id;
|
|
|
240 |
|
|
|
241 |
/* Save UP THE TREE */
|
|
|
242 |
INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID )
|
|
|
243 |
SELECT nRtag_id AS rtag_id,
|
|
|
244 |
ttb.level_num AS step_num,
|
|
|
245 |
ttb.PV_ID
|
|
|
246 |
FROM temp_tree_browse ttb
|
|
|
247 |
WHERE ttb.session_num = sessionNum
|
|
|
248 |
AND ttb.direction = UP_THE_TREE;
|
|
|
249 |
|
|
|
250 |
/*Get last step_num */
|
|
|
251 |
SELECT MAX(ttb.level_num) + 1 INTO levelNum
|
|
|
252 |
FROM temp_tree_browse ttb WHERE ttb.session_num = sessionNum AND ttb.DIRECTION = UP_THE_TREE;
|
|
|
253 |
|
|
|
254 |
/* UNRESOLVED */
|
|
|
255 |
SELECT
|
|
|
256 |
COUNT(*) INTO rowCnt
|
|
|
257 |
FROM (
|
|
|
258 |
SELECT pv.pv_id, pv.pkg_id, pv.v_ext
|
|
|
259 |
FROM release_content rc,
|
|
|
260 |
package_versions pv
|
|
|
261 |
WHERE rc.rtag_id = nRtag_id
|
|
|
262 |
AND rc.pv_id = pv.pv_id
|
|
|
263 |
MINUS
|
|
|
264 |
SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
|
|
|
265 |
FROM temp_tree_browse ttb
|
|
|
266 |
WHERE ttb.session_num = sessionNum
|
|
|
267 |
);
|
|
|
268 |
|
|
|
269 |
|
|
|
270 |
IF rowCnt > 0
|
|
|
271 |
THEN
|
|
|
272 |
/* Save unresolved packages */
|
|
|
273 |
INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID, UNRESOLVED )
|
|
|
274 |
SELECT nRtag_id AS rtag_id,
|
|
|
275 |
levelNum AS step_num,
|
|
|
276 |
upv.PV_ID,
|
|
|
277 |
'Y' AS UNRESOLVED
|
|
|
278 |
FROM (
|
|
|
279 |
SELECT pv.pv_id, pv.pkg_id, pv.v_ext
|
|
|
280 |
FROM release_content rc,
|
|
|
281 |
package_versions pv
|
|
|
282 |
WHERE rc.rtag_id = nRtag_id
|
|
|
283 |
AND rc.pv_id = pv.pv_id
|
|
|
284 |
MINUS
|
|
|
285 |
SELECT ttb.pv_id, ttb.pkg_id, ttb.v_ext
|
|
|
286 |
FROM temp_tree_browse ttb
|
|
|
287 |
WHERE ttb.session_num = sessionNum
|
|
|
288 |
) upv;
|
|
|
289 |
END IF;
|
|
|
290 |
|
|
|
291 |
/* Save DOWN THE TREE */
|
|
|
292 |
levelNum := 1000;
|
|
|
293 |
INSERT INTO BUILD_ORDER ( RTAG_ID, STEP_NUM, PV_ID )
|
|
|
294 |
SELECT nRtag_id AS rtag_id,
|
|
|
295 |
levelNum + ttb.level_num AS step_num,
|
|
|
296 |
ttb.PV_ID
|
|
|
297 |
FROM temp_tree_browse ttb
|
|
|
298 |
WHERE ttb.session_num = sessionNum
|
|
|
299 |
AND ttb.direction = DOWN_THE_TREE;
|
|
|
300 |
|
|
|
301 |
|
|
|
302 |
/*---------------------------------------------------------------------------------------------------------------------*/
|
|
|
303 |
|
|
|
304 |
/* Clean up temp table */
|
|
|
305 |
DELETE FROM TEMP_TREE_BROWSE WHERE session_num = sessionNum;
|
|
|
306 |
|
|
|
307 |
retSessionNum := sessionNum;
|
|
|
308 |
END Build_Tree;
|
|
|
309 |
/
|