Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
221 vnguyen 1 
CREATE PACKAGE BODY "RELEASE_MANAGER"."PK_RMAPI_TEST"
2 
IS
3 
/*
4 
------------------------------
5 
||  Last Modified:  Rupesh Solanki
6 
||  Modified Date:  11 September 2006
7 
||  Body Version:   2.0
8 
------------------------------
9 
*/
10 
 
11 
   /*-------------------------------------------------------------------------------------------------------*/
12 
   FUNCTION package_dependencies (pkgname VARCHAR2, pkgversion VARCHAR2)
13 
      RETURN typecur
14 
   IS
15 
      npvid     NUMBER  := 0;
16 
      RECORDS   typecur;
17 
   BEGIN
18 
      BEGIN
19 
         -- Get PV_ID --
20 
         SELECT pv.pv_id
21 
           INTO npvid
22 
           FROM PACKAGES pkg, package_versions pv
23 
          WHERE pv.pkg_id = pkg.pkg_id
24 
            AND pkg.pkg_name = pkgname
25 
            AND pv.pkg_version = pkgversion;
26 
      EXCEPTION
27 
         WHEN NO_DATA_FOUND
28 
         THEN
29 
            raise_application_error (-20000, 'Package Not Found!');
30 
            --WHEN OTHERS THEN
31 
                  -- Consider logging the error and then re-raise
32 
            RAISE;
33 
      END;
34 
 
35 
      -- Finally get package dependencies --
36 
      OPEN RECORDS FOR
37 
         SELECT dpv.pv_id, dpkg.pkg_name, dpv.pkg_version
38 
           FROM package_dependencies dep, PACKAGES dpkg, package_versions dpv
39 
          WHERE dep.pv_id = npvid
40 
            AND dpv.pkg_id = dpkg.pkg_id
41 
            AND dpv.pv_id = dep.dpv_id;
42 
 
43 
      RETURN RECORDS;
44 
   END;
45 
 
46 
/*-------------------------------------------------------------------------------------------------------*/
47 
   FUNCTION wip_iteration_package (
48 
      projname        VARCHAR2,
49 
      iterationname   VARCHAR2,
50 
      pkgname         VARCHAR2
51 
   )
52 
      RETURN typecur
53 
   IS
54 
      nrtagid   NUMBER  := 0;
55 
      RECORDS   typecur;
56 
   BEGIN
57 
      BEGIN
58 
         -- Get latest rtag_id --
59 
         SELECT rt.rtag_id
60 
           INTO nrtagid
61 
           FROM projects proj, release_tags rt
62 
          WHERE rt.proj_id = proj.proj_id
63 
            AND UPPER (proj.proj_name) = UPPER (projname)
64 
            AND UPPER (rt.rtag_name) = UPPER (iterationname);
65 
 
66 
         IF (nrtagid IS NULL)
67 
         THEN
68 
            raise_application_error (-20000,
69 
                                     'Work In Progress is Not Found!');
70 
         END IF;
71 
      EXCEPTION
72 
         WHEN NO_DATA_FOUND
73 
         THEN
74 
            raise_application_error (-20000,
75 
                                     'Work In Progress is Not Found!');
76 
            RAISE;
77 
      END;
78 
 
79 
      -- Finally get package dependencies --
80 
      OPEN RECORDS FOR
81 
         SELECT pv.pkg_version, pv.dlocked AS is_official, pv.pkg_label,
82 
                pv.src_path
83 
           FROM PACKAGES pkg, package_versions pv, work_in_progress wip
84 
          WHERE pv.pkg_id = pkg.pkg_id
85 
            AND wip.pv_id = pv.pv_id
86 
            AND wip.rtag_id = nrtagid
87 
            AND pkg.pkg_name = pkgname;
88 
 
89 
      RETURN RECORDS;
90 
   END;
91 
 
92 
/*-------------------------------------------------------------------------------------------------------*/
93 
   FUNCTION auto_make_release (
94 
      rtagid                   IN   NUMBER,
95 
      pkgname                  IN   VARCHAR2,
96 
      vext                     IN   VARCHAR2,
97 
      newpkgversion            IN   VARCHAR2,
98 
      label                    IN   VARCHAR2,
99 
      dependenciesimportlist   IN   VARCHAR2,
100 
      isrippled                IN   NUMBER,
101 
      username                 IN   VARCHAR2
102 
   )
103 
      RETURN NUMBER
104 
   IS
105 
      pvid                            NUMBER                        := 0;
106 
      userid                          NUMBER;
107 
      dlocked                         VARCHAR2 (20)                 := NULL;
108 
      clonefrompvid                   NUMBER;
109 
      ssv_mm                          package_versions.v_mm%TYPE;
110 
      ssv_nmm                         package_versions.v_nmm%TYPE;
111 
      ssv_ext                         package_versions.v_ext%TYPE;
112 
      return_package_not_found        NUMBER                        := -1;
113 
      return_package_already_exists   NUMBER                        := -2;
114 
      return_not_approved             NUMBER                        := -3;
115 
   BEGIN
116 
      /*--------------- Business Rules Here -------------------*/
117 
      IF (rtagid IS NULL)
118 
      THEN
119 
         raise_application_error (-20000, 'RtagId must be supplied.');
120 
      END IF;
121 
 
122 
      IF (pkgname IS NULL)
123 
      THEN
124 
         raise_application_error (-20000, 'PkgName must be supplied.');
125 
      END IF;
126 
 
127 
      IF (newpkgversion IS NULL)
128 
      THEN
129 
         raise_application_error (-20000, 'PkgVersion must be supplied.');
130 
      END IF;
131 
 
132 
      IF (label IS NULL)
133 
      THEN
134 
         raise_application_error (-20000, 'Label must be supplied.');
135 
      END IF;
136 
 
137 
      IF (isrippled IS NULL) OR (isrippled < 0) OR (isrippled > 1)
138 
      THEN
139 
         raise_application_error
140 
            (-20000,
141 
             'IsRippled must be set to 1 (Is rippled build) or 0 (Is planned build).'
142 
            );
143 
      END IF;
144 
 
145 
      IF (username IS NULL)
146 
      THEN
147 
         raise_application_error (-20000, 'UserName must be supplied.');
148 
      END IF;
149 
 
150 
      -- Get user_id
151 
      BEGIN
152 
         SELECT usr.user_id
153 
           INTO userid
154 
           FROM users usr
155 
          WHERE UPPER (usr.user_name) = UPPER (username)
156 
            AND usr.is_disabled IS NULL;
157 
      EXCEPTION
158 
         WHEN NO_DATA_FOUND
159 
         THEN
160 
            raise_application_error (-20000,
161 
                                        'UserName '
162 
                                     || username
163 
                                     || ' is not valid or disabled.'
164 
                                    );
165 
      END;
166 
 
167 
/*-------------------------------------------------------*/
168 
 
169 
      -- Create package if necessary
170 
      IF isrippled = 1
171 
      THEN
172 
         /* Ripple Build */
173 
         BEGIN
174 
            -- Make sure that package does not exist
175 
            SELECT pv.pv_id
176 
              INTO pvid
177 
              FROM package_versions pv, PACKAGES pkg
178 
             WHERE pv.pkg_id = pkg.pkg_id
179 
               AND pkg.pkg_name = pkgname
180 
               AND pv.pkg_version = newpkgversion;
181 
         EXCEPTION
182 
            WHEN NO_DATA_FOUND
183 
            THEN
184 
               pvid := 0;
185 
         END;
186 
 
187 
         IF (pvid = 0)
188 
         THEN
189 
            -- Split current version in parts
190 
            split_version (newpkgversion, ssv_mm, ssv_nmm, ssv_ext);
191 
 
192 
            BEGIN
193 
               -- Find package to be replaced with thie ripple package
194 
               IF vext = ssv_ext
195 
               THEN
196 
                  SELECT pv.pv_id
197 
                    INTO clonefrompvid
198 
                    FROM PACKAGES pkg, package_versions pv,
199 
                         release_content rc
200 
                   WHERE rc.pv_id = pv.pv_id
201 
                     AND pv.pkg_id = pkg.pkg_id
202 
                     AND rc.rtag_id = rtagid
203 
                     AND pkg.pkg_name = pkgname
204 
                     AND pv.v_ext = ssv_ext;
205 
               ELSE
206 
                  SELECT pv.pv_id
207 
                    INTO clonefrompvid
208 
                    FROM PACKAGES pkg, package_versions pv,
209 
                         release_content rc
210 
                   WHERE rc.pv_id = pv.pv_id
211 
                     AND pv.pkg_id = pkg.pkg_id
212 
                     AND rc.rtag_id = rtagid
213 
                     AND pkg.pkg_name = pkgname
214 
                     AND pv.v_ext = vext;
215 
               END IF;
216 
            EXCEPTION
217 
               WHEN NO_DATA_FOUND
218 
               THEN
219 
                  raise_application_error
220 
                                        (-20000,
221 
                                            'Cannot get CloneFromPvId. VExt='
222 
                                         || vext
223 
                                         || ', RtagId='
224 
                                         || rtagid
225 
                                         || ', PkgName='
226 
                                         || pkgname
227 
                                         || ', SSV_EXT='
228 
                                         || ssv_ext
229 
                                        );
230 
            END;
231 
 
232 
            IF vext = ssv_ext
233 
            THEN
234 
               -- Create package
235 
               seed_package_names_versions (pkgname,
236 
                                            newpkgversion,
237 
                                            userid,
238 
 
239 
     pvid,
240 
                                            clonefrompvid
241 
                                           );
242 
            ELSE
243 
               -- Create package
244 
               seed_package_names_versions2 (pkgname,
245 
                                             newpkgversion,
246 
                                             userid,
247 
                                             pvid,
248 
                                             clonefrompvid
249 
                                            );
250 
            END IF;
251 
 
252 
            -- Update Package reason for release
253 
            UPDATE package_versions pv
254 
               SET pv.comments = 'Rippled Build.',
255 
                   pv.build_type = 'Y'
256 
             WHERE pv.pv_id = pvid;
257 
         ELSE
258 
            -- Package already exists, hence cannot be used for ripple build
259 
            RETURN return_package_already_exists;
260 
         END IF;
261 
      ELSE
262 
         /* Auto build from Pending area */
263 
 
264 
         -- Find package in pending area
265 
         BEGIN
266 
            SELECT pv.pv_id, pv.dlocked
267 
              INTO pvid, dlocked
268 
              FROM planned pl, package_versions pv, PACKAGES pkg
269 
             WHERE pl.pv_id = pv.pv_id
270 
               AND pv.pkg_id = pkg.pkg_id
271 
               AND pl.rtag_id = rtagid
272 
               AND pkg.pkg_name = pkgname
273 
               AND pv.dlocked = 'A'
274 
               AND NVL (pv.v_ext, '|LINK_A_NULL|') =
275 
                                                   NVL (vext, '|LINK_A_NULL|');
276 
         EXCEPTION
277 
            WHEN NO_DATA_FOUND
278 
            THEN
279 
               pvid := 0;
280 
         END;
281 
 
282 
         IF (pvid = 0)
283 
         THEN
284 
            -- Package does not exist in pending area, hence report it
285 
            RETURN return_package_not_found;
286 
         ELSIF (dlocked != 'A')
287 
         THEN
288 
            -- Package is not approved for autobuild
289 
            RETURN return_not_approved;
290 
         END IF;
291 
      END IF;
292 
 
293 
      BEGIN
294 
         -- Import Dependencies
295 
         import_dependencies (pvid, dependenciesimportlist, userid);
296 
      END;
297 
 
298 
      BEGIN
299 
         -- Split current version in parts
300 
         split_version (newpkgversion, ssv_mm, ssv_nmm, ssv_ext);
301 
 
302 
         -- Update Package Details
303 
         UPDATE package_versions pv
304 
            SET pv.pkg_version = newpkgversion,
305 
                pv.v_ext = ssv_ext,
306 
                pv.v_mm = ssv_mm,
307 
                pv.v_nmm = ssv_nmm,
308 
                pv.pkg_label = label
309 
          WHERE pv.pv_id = pvid;
310 
      EXCEPTION
311 
         WHEN DUP_VAL_ON_INDEX
312 
         THEN
313 
            -- Package already exists, hence cannot be used for ripple build
314 
            RETURN return_package_already_exists;
315 
      END;
316 
 
317 
	  -- Update the is_autobuildable
318 
	  update package_versions
319 
	  set is_autobuildable = 'Y'
320 
	  where pv_id = pvid;
321 
 
322 
      -- Now release package
323 
      pk_environment.auto_make_release (pvid,
324 
                                        rtagid,
325 
                                        userid,
326 
                                        vext,
327 
                                        ssv_ext,
328 
                                        clonefrompvid
329 
                                       );
330 
 
331 
 
332 
      RETURN pvid;
333 
   END;
334 
 
335 
/*-------------------------------------------------------------------------------------------------------*/
336 
   PROCEDURE import_dependencies (
337 
      pvid                     IN   NUMBER,
338 
      dependenciesimportlist   IN   VARCHAR2,
339 
      userid                   IN   NUMBER
340 
   )
341 
   IS
342 
/*
343 
|| DependenciesImportList Format:
344 
|| "pkgA","1.0.0";"pkgB","2.0.0";
345 
||  OR 'pkgA','1.0.0';'pkgB','2.0.0';
346 
*/
347 
      TYPE tdictionary IS TABLE OF VARCHAR2 (4000)
348 
         INDEX BY VARCHAR2 (4000);
349 
 
350 
      seperator         VARCHAR2 (2)           := '||';
351 
      pkgname           VARCHAR2 (4000);
352 
      pkgversion        VARCHAR2 (4000);
353 
      buildtype         VARCHAR2 (50);
354 
      pkgid             NUMBER;
355 
      vext              VARCHAR2 (4000);
356 
      dpvid             NUMBER;
357 
      slist             VARCHAR2 (4000);
358 
      cbuildtypes       tdictionary;
359 
      dependencyrow     NUMBER;
360 
      sdependency       VARCHAR2 (4000);
361 
      first_pos         VARCHAR2 (4000);
362 
      second_pos        VARCHAR2 (4000);
363 
      third_pos         VARCHAR2 (4000);
364 
      forth_pos         VARCHAR2 (4000);
365 
      citemcollection   relmgr_varchar2_tab_t  := relmgr_varchar2_tab_t ();
366 
 
367 
      CURSOR curbuildtype
368 
      IS
369 
         SELECT dpv.pkg_id || seperator || dpv.v_ext AS pkgid_ext,
370 
                dep.build_type
371 
           FROM package_dependencies dep, package_versions dpv
372 
          WHERE dep.pv_id = pvid AND dep.dpv_id = dpv.pv_id;
373 
 
374 
      recbuildtype      curbuildtype%ROWTYPE;
375 
   BEGIN
376 
      slist := dependenciesimportlist;
377 
 
378 
      -- Preformat String
379 
      IF NOT slist IS NULL
380 
      THEN
381 
         slist := REPLACE (slist, ' ');                      -- Remove spaces
382 
         slist := REPLACE (slist, UTL_TCP.crlf);
383 
                            -- Remove new line and carriage-return characters
384 
         slist := REPLACE (slist, '''', '"');             -- Replace ' with "
385 
      END IF;
386 
 
387 
      -- Get Current Dependencies
388 
      OPEN curbuildtype;
389 
 
390 
      FETCH curbuildtype
391 
       INTO recbuildtype;
392 
 
393 
      WHILE curbuildtype%FOUND
394 
      LOOP
395 
         cbuildtypes (recbuildtype.pkgid_ext) := recbuildtype.build_type;
396 
 
397 
         FETCH curbuildtype
398 
          INTO recbuildtype;
399 
      END LOOP;
400 
 
401 
      CLOSE curbuildtype;
402 
 
403 
      -- Separate dependencies with ; separator
404 
      citemcollection := in_list_varchar2 (slist, ';');
405 
 
406 
      BEGIN
407 
         -- Remove old dependencies
408 
         DELETE FROM package_dependencies dep
409 
               WHERE dep.pv_id = pvid;
410 
 
411 
         -- Loop through dependencies
412 
         FOR dependencyrow IN 1 .. citemcollection.COUNT
413 
         LOOP
414 
            -- Extract pkg_name and pkg_version
415 
            sdependency := citemcollection (dependencyrow);
416 
            first_pos := INSTR (sdependency, '"', 1, 1);
417 
            second_pos := INSTR (sdependency, '"', 1, 2);
418 
            third_pos := INSTR (sdependency, '"', 1, 3);
419 
            forth_pos := INSTR (sdependency, '"', 1, 4);
420 
            pkgname :=
421 
               SUBSTR (sdependency,
422 
                       (first_pos + 1),
423 
                       (second_pos - first_pos - 1)
424 
                      );
425 
            pkgversion :=
426 
               SUBSTR (sdependency,
427 
                       (third_pos + 1),
428 
                       (forth_pos - third_pos - 1)
429 
                      );
430 
 
431 
            -- Dependency must exits to be linked against
432 
            BEGIN
433 
               SELECT pv.pv_id, pv.pkg_id, pv.v_ext
434 
                 INTO dpvid, pkgid, vext
435 
                 FROM package_versions pv, PACKAGES pkg
436 
                WHERE pv.pkg_id = pkg.pkg_id
437 
                  AND pkg.pkg_name = pkgname
438 
                  AND pv.pkg_version = pkgversion;
439 
            EXCEPTION
440 
               WHEN NO_DATA_FOUND
441 
               THEN
442 
                  raise_application_error
443 
                                  (-20000,
444 
                                      'Dependency ['
445 
                                   || pkgname
446 
                                   || ' '
447 
                                   || pkgversion
448 
                                   || '] does not exist yet and cannot be used!'
449 
                                  );
450 
            END;
451 
 
452 
            -- Get Build Type (i.e. BuildPackageArchive or LinkPackageArchive)
453 
            BEGIN
454 
               buildtype := cbuildtypes (pkgid || seperator || vext);
455 
 
456 
               IF buildtype IS NULL
457 
               THEN
458 
                  -- Set build type to LinkPackageArchive by default
459 
                  buildtype := 'L';
460 
               END IF;
461 
            EXCEPTION
462 
               WHEN NO_DATA_FOUND
463 
               THEN
464 
                  buildtype := 'L';
465 
            END;
466 
 
467 
            -- Insert Dependencies
468 
            update_package_dependency (pvid,
469 
                                       pkgname,
470 
                                       pkgversion,
471 
                                       buildtype,
472 
                                       userid,
473 
 
474 
                                      );
475 
         END LOOP;
476 
 
477 
END;
478 
   END;
479 
 
480 
/*-------------------------------------------------------------------------------------------------------*/
481 
   FUNCTION return_package_version (pkgname IN VARCHAR2, rtagid IN NUMBER)
482 
      RETURN VARCHAR2
483 
   IS
484 
      pkgversion   VARCHAR2 (4000);
485 
   BEGIN
486 
      BEGIN
487 
         SELECT pv.pkg_version
488 
           INTO pkgversion
489 
           FROM PACKAGES pkg, release_content rc, package_versions pv
490 
          WHERE pv.pv_id = rc.pv_id
491 
            AND pkg.pkg_id = pv.pkg_id
492 
            AND pkg.pkg_name = pkgname
493 
            AND rc.rtag_id = rtagid;
494 
 
495 
         RETURN pkgversion;
496 
      END;
497 
   END;
498 
 
499 
/*-------------------------------------------------------------------------------------------------------*/
500 
PROCEDURE CQ_TEST IS
501 
 
502 
  INPUT NUMBER(8,2);
503 
  RESULT VARCHAR2(50);
504 
BEGIN
505 
  INPUT := 33558440;
506 
  RESULT := dbo.sp_RM_getIssueDetails@DEVI(INPUT);
507 
  INSERT INTO CQ_TEST (ID) VALUES (RESULT);
508 
 
509 
 
510 
END;
511 
/*-------------------------------------------------------------------------------------------------------*/
512 
   PROCEDURE UPDATE_DASH_BOARD ( RtagId IN NUMBER ) IS
513 
 
514 
   projId NUMBER;
515 
   projIdDB NUMBER;
516 
   TOTAL NUMBER;
517 
   AUTO_TOTAL NUMBER;
518 
   rtagIdDB NUMBER;
519 
 
520 
   BEGIN
521 
   		SELECT count(*) INTO TOTAL
522 
		FROM RELEASE_CONTENT rc, PACKAGE_VERSIONS pv
523 
		WHERE pv.pv_id = rc.pv_id and rc.rtag_id = RtagId;
524 
 
525 
		SELECT count(*) INTO AUTO_TOTAL
526 
		FROM RELEASE_CONTENT rc, PACKAGE_VERSIONS pv
527 
		WHERE pv.pv_id = rc.pv_id and pv.is_autobuildable = 'Y'
528 
		and rc.rtag_id = RtagId;
529 
 
530 
		BEGIN
531 
			SELECT RTAG_ID INTO rtagIdDB
532 
			FROM DASH_BOARD
533 
			WHERE RTAG_ID = RtagId;
534 
 
535 
			SELECT PROJ_ID INTO projIdDB
536 
			FROM DASH_BOARD
537 
			WHERE RTAG_ID = RtagId;
538 
            EXCEPTION
539 
               WHEN NO_DATA_FOUND
540 
			   THEN	rtagIdDB := '';
541 
 
542 
		END;
543 
 
544 
		IF rtagIdDB IS NULL THEN
545 
			SELECT PROJ_ID INTO projId
546 
			FROM RELEASE_TAGS
547 
			WHERE RTAG_ID = RtagId;
548 
 
549 
			INSERT INTO DASH_BOARD (PROJ_ID, RTAG_ID, LAST_BUILD_TIME, AUTOMATED_PACKAGES, TOTAL_PACKAGES)
550 
			VALUES ( projId, RtagId, ORA_SYSDATETIME, AUTO_TOTAL, TOTAL);
551 
		ELSE
552 
			UPDATE DASH_BOARD
553 
			SET LAST_BUILD_TIME = ORA_SYSDATETIME,
554 
				AUTOMATED_PACKAGES = AUTO_TOTAL,
555 
				TOTAL_PACKAGES = TOTAL
556 
			WHERE PROJ_ID = projIdDB
557 
			AND RTAG_ID = rtagIdDB;
558 
		END IF;
559 
 
560 
 
561 
 
562 
 
563 
 
564 
 
565 
   END;
566 
 
567 
 
568 
/*-------------------------------------------------------------------------------------------------------*/
569 
END pk_rmapi_test;
570 
/
571 
ALTER PACKAGE "RELEASE_MANAGER"."PK_RMAPI_TEST"
572 
  COMPILE BODY
573 
    PLSQL_OPTIMIZE_LEVEL=  2
574 
    PLSQL_CODE_TYPE=  INTERPRETED
575 
    PLSQL_DEBUG=  FALSE
576 
 REUSE SETTINGS TIMESTAMP '2007-02-06 11:21:54'
577 
/