VirtualBox

source: vbox/trunk/src/VBox/ValidationKit/testmanager/core/testbox.pgsql@ 92892

Last change on this file since 92892 was 82968, checked in by vboxsync, 5 years ago

Copyright year updates by scm.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
File size: 27.7 KB
Line 
1-- $Id: testbox.pgsql 82968 2020-02-04 10:35:17Z vboxsync $
2--- @file
3-- VBox Test Manager Database Stored Procedures - TestBoxes.
4--
5
6--
7-- Copyright (C) 2012-2020 Oracle Corporation
8--
9-- This file is part of VirtualBox Open Source Edition (OSE), as
10-- available from http://www.virtualbox.org. This file is free software;
11-- you can redistribute it and/or modify it under the terms of the GNU
12-- General Public License (GPL) as published by the Free Software
13-- Foundation, in version 2 as it comes in the "COPYING" file of the
14-- VirtualBox OSE distribution. VirtualBox OSE is distributed in the
15-- hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
16--
17-- The contents of this file may alternatively be used under the terms
18-- of the Common Development and Distribution License Version 1.0
19-- (CDDL) only, as it comes in the "COPYING.CDDL" file of the
20-- VirtualBox OSE distribution, in which case the provisions of the
21-- CDDL are applicable instead of those of the GPL.
22--
23-- You may elect to license modified versions of this file under the
24-- terms and conditions of either the GPL or the CDDL or both.
25--
26
27
28--
29-- Old type signatures.
30--
31DROP FUNCTION IF EXISTS TestBoxLogic_addEntry(a_uidAuthor INTEGER,
32 a_ip inet,
33 a_uuidSystem uuid,
34 a_sName TEXT,
35 a_sDescription TEXT,
36 a_idSchedGroup INTEGER,
37 a_fEnabled BOOLEAN,
38 a_enmLomKind LomKind_T,
39 a_ipLom inet,
40 a_pctScaleTimeout INTEGER, -- Actually smallint, but default typing fun.
41 a_sComment TEXT,
42 a_enmPendingCmd TestBoxCmd_T,
43 OUT r_idTestBox INTEGER,
44 OUT r_idGenTestBox INTEGER,
45 OUT r_tsEffective TIMESTAMP WITH TIME ZONE);
46DROP FUNCTION IF EXISTS TestBoxLogic_editEntry(a_uidAuthor INTEGER,
47 a_idTestBox INTEGER,
48 a_ip inet,
49 a_uuidSystem uuid,
50 a_sName TEXT,
51 a_sDescription TEXT,
52 a_idSchedGroup INTEGER,
53 a_fEnabled BOOLEAN,
54 a_enmLomKind LomKind_T,
55 a_ipLom inet,
56 a_pctScaleTimeout INTEGER, -- Actually smallint, but default typing fun.
57 a_sComment TEXT,
58 a_enmPendingCmd TestBoxCmd_T,
59 OUT r_idGenTestBox INTEGER,
60 OUT r_tsEffective TIMESTAMP WITH TIME ZONE);
61DROP FUNCTION IF EXISTS TestBoxLogic_removeEntry(INTEGER, INTEGER, BOOLEAN);
62DROP FUNCTION IF EXISTS TestBoxLogic_addGroupEntry(a_uidAuthor INTEGER,
63 a_idTestBox INTEGER,
64 a_idSchedGroup INTEGER,
65 a_iSchedPriority INTEGER,
66 OUT r_tsEffective TIMESTAMP WITH TIME ZONE);
67DROP FUNCTION IF EXISTS TestBoxLogic_editGroupEntry(a_uidAuthor INTEGER,
68 a_idTestBox INTEGER,
69 a_idSchedGroup INTEGER,
70 a_iSchedPriority INTEGER,
71 OUT r_tsEffective INTEGER);
72
73
74---
75-- Checks if the test box name is unique, ignoring a_idTestCaseIgnore.
76-- Raises exception if duplicates are found.
77--
78-- @internal
79--
80CREATE OR REPLACE FUNCTION TestBoxLogic_checkUniqueName(a_sName TEXT, a_idTestBoxIgnore INTEGER)
81 RETURNS VOID AS $$
82 DECLARE
83 v_cRows INTEGER;
84 BEGIN
85 SELECT COUNT(*) INTO v_cRows
86 FROM TestBoxes
87 WHERE sName = a_sName
88 AND tsExpire = 'infinity'::TIMESTAMP
89 AND idTestBox <> a_idTestBoxIgnore;
90 IF v_cRows <> 0 THEN
91 RAISE EXCEPTION 'Duplicate test box name "%" (% times)', a_sName, v_cRows;
92 END IF;
93 END;
94$$ LANGUAGE plpgsql;
95
96
97---
98-- Checks that the given scheduling group exists.
99-- Raises exception if it doesn't.
100--
101-- @internal
102--
103CREATE OR REPLACE FUNCTION TestBoxLogic_checkSchedGroupExists(a_idSchedGroup INTEGER)
104 RETURNS VOID AS $$
105 DECLARE
106 v_cRows INTEGER;
107 BEGIN
108 SELECT COUNT(*) INTO v_cRows
109 FROM SchedGroups
110 WHERE idSchedGroup = a_idSchedGroup
111 AND tsExpire = 'infinity'::TIMESTAMP;
112 IF v_cRows <> 1 THEN
113 IF v_cRows = 0 THEN
114 RAISE EXCEPTION 'Scheduling group with ID % was not found', a_idSchedGroup;
115 END IF;
116 RAISE EXCEPTION 'Integrity error in SchedGroups: % current rows with idSchedGroup=%', v_cRows, a_idSchedGroup;
117 END IF;
118 END;
119$$ LANGUAGE plpgsql;
120
121
122---
123-- Checks that the given testbxo + scheduling group pair does not currently exists.
124-- Raises exception if it does.
125--
126-- @internal
127--
128CREATE OR REPLACE FUNCTION TestBoxLogic_checkTestBoxNotInSchedGroup(a_idTestBox INTEGER, a_idSchedGroup INTEGER)
129 RETURNS VOID AS $$
130 DECLARE
131 v_cRows INTEGER;
132 BEGIN
133 SELECT COUNT(*) INTO v_cRows
134 FROM TestBoxesInSchedGroups
135 WHERE idTestBox = a_idTestBox
136 AND idSchedGroup = a_idSchedGroup
137 AND tsExpire = 'infinity'::TIMESTAMP;
138 IF v_cRows <> 0 THEN
139 RAISE EXCEPTION 'TestBox % is already a member of scheduling group %', a_idTestBox, a_idSchedGroup;
140 END IF;
141 END;
142$$ LANGUAGE plpgsql;
143
144
145---
146-- Historize a row.
147-- @internal
148--
149CREATE OR REPLACE FUNCTION TestBoxLogic_historizeEntry(a_idGenTestBox INTEGER, a_tsExpire TIMESTAMP WITH TIME ZONE)
150 RETURNS VOID AS $$
151 DECLARE
152 v_cUpdatedRows INTEGER;
153 BEGIN
154 UPDATE TestBoxes
155 SET tsExpire = a_tsExpire
156 WHERE idGenTestBox = a_idGenTestBox
157 AND tsExpire = 'infinity'::TIMESTAMP;
158 GET DIAGNOSTICS v_cUpdatedRows = ROW_COUNT;
159 IF v_cUpdatedRows <> 1 THEN
160 IF v_cUpdatedRows = 0 THEN
161 RAISE EXCEPTION 'Test box generation ID % is no longer valid', a_idGenTestBox;
162 END IF;
163 RAISE EXCEPTION 'Integrity error in TestBoxes: % current rows with idGenTestBox=%', v_cUpdatedRows, a_idGenTestBox;
164 END IF;
165 END;
166$$ LANGUAGE plpgsql;
167
168
169---
170-- Historize a in-scheduling-group row.
171-- @internal
172--
173CREATE OR REPLACE FUNCTION TestBoxLogic_historizeGroupEntry(a_idTestBox INTEGER,
174 a_idSchedGroup INTEGER,
175 a_tsExpire TIMESTAMP WITH TIME ZONE)
176 RETURNS VOID AS $$
177 DECLARE
178 v_cUpdatedRows INTEGER;
179 BEGIN
180 UPDATE TestBoxesInSchedGroups
181 SET tsExpire = a_tsExpire
182 WHERE idTestBox = a_idTestBox
183 AND idSchedGroup = a_idSchedGroup
184 AND tsExpire = 'infinity'::TIMESTAMP;
185 GET DIAGNOSTICS v_cUpdatedRows = ROW_COUNT;
186 IF v_cUpdatedRows <> 1 THEN
187 IF v_cUpdatedRows = 0 THEN
188 RAISE EXCEPTION 'TestBox ID % / SchedGroup ID % is no longer a valid combination', a_idTestBox, a_idSchedGroup;
189 END IF;
190 RAISE EXCEPTION 'Integrity error in TestBoxesInSchedGroups: % current rows for % / %',
191 v_cUpdatedRows, a_idTestBox, a_idSchedGroup;
192 END IF;
193 END;
194$$ LANGUAGE plpgsql;
195
196
197---
198-- Translate string via the string table.
199--
200-- @returns NULL if a_sValue is NULL, otherwise a string ID.
201--
202CREATE OR REPLACE FUNCTION TestBoxLogic_lookupOrFindString(a_sValue TEXT)
203 RETURNS INTEGER AS $$
204 DECLARE
205 v_idStr INTEGER;
206 v_cRows INTEGER;
207 BEGIN
208 IF a_sValue IS NULL THEN
209 RETURN NULL;
210 END IF;
211
212 SELECT idStr
213 INTO v_idStr
214 FROM TestBoxStrTab
215 WHERE sValue = a_sValue;
216 GET DIAGNOSTICS v_cRows = ROW_COUNT;
217 IF v_cRows = 0 THEN
218 INSERT INTO TestBoxStrTab (sValue)
219 VALUES (a_sValue)
220 RETURNING idStr INTO v_idStr;
221 END IF;
222 RETURN v_idStr;
223 END;
224$$ LANGUAGE plpgsql;
225
226
227---
228-- Only adds the user settable parts of the row, i.e. not what TestBoxLogic_updateOnSignOn touches.
229--
230CREATE OR REPLACE function TestBoxLogic_addEntry(a_uidAuthor INTEGER,
231 a_ip inet,
232 a_uuidSystem uuid,
233 a_sName TEXT,
234 a_sDescription TEXT,
235 a_fEnabled BOOLEAN,
236 a_enmLomKind LomKind_T,
237 a_ipLom inet,
238 a_pctScaleTimeout INTEGER, -- Actually smallint, but default typing fun.
239 a_sComment TEXT,
240 a_enmPendingCmd TestBoxCmd_T,
241 OUT r_idTestBox INTEGER,
242 OUT r_idGenTestBox INTEGER,
243 OUT r_tsEffective TIMESTAMP WITH TIME ZONE
244 ) AS $$
245 DECLARE
246 v_idStrDescription INTEGER;
247 v_idStrComment INTEGER;
248 BEGIN
249 PERFORM TestBoxLogic_checkUniqueName(a_sName, -1);
250
251 SELECT TestBoxLogic_lookupOrFindString(a_sDescription) INTO v_idStrDescription;
252 SELECT TestBoxLogic_lookupOrFindString(a_sComment) INTO v_idStrComment;
253
254 INSERT INTO TestBoxes (
255 tsEffective, -- 1
256 uidAuthor, -- 2
257 ip, -- 3
258 uuidSystem, -- 4
259 sName, -- 5
260 idStrDescription, -- 6
261 fEnabled, -- 7
262 enmLomKind, -- 8
263 ipLom, -- 9
264 pctScaleTimeout, -- 10
265 idStrComment, -- 11
266 enmPendingCmd ) -- 12
267 VALUES (CURRENT_TIMESTAMP, -- 1
268 a_uidAuthor, -- 2
269 a_ip, -- 3
270 a_uuidSystem, -- 4
271 a_sName, -- 5
272 v_idStrDescription, -- 6
273 a_fEnabled, -- 7
274 a_enmLomKind, -- 8
275 a_ipLom, -- 9
276 a_pctScaleTimeout, -- 10
277 v_idStrComment, -- 11
278 a_enmPendingCmd ) -- 12
279 RETURNING idTestBox, idGenTestBox, tsEffective INTO r_idTestBox, r_idGenTestBox, r_tsEffective;
280 END;
281$$ LANGUAGE plpgsql;
282
283
284CREATE OR REPLACE function TestBoxLogic_addGroupEntry(a_uidAuthor INTEGER,
285 a_idTestBox INTEGER,
286 a_idSchedGroup INTEGER,
287 a_iSchedPriority INTEGER,
288 OUT r_tsEffective TIMESTAMP WITH TIME ZONE
289 ) AS $$
290 BEGIN
291 PERFORM TestBoxLogic_checkSchedGroupExists(a_idSchedGroup);
292 PERFORM TestBoxLogic_checkTestBoxNotInSchedGroup(a_idTestBox, a_idSchedGroup);
293
294 INSERT INTO TestBoxesInSchedGroups (
295 idTestBox,
296 idSchedGroup,
297 tsEffective,
298 tsExpire,
299 uidAuthor,
300 iSchedPriority)
301 VALUES (a_idTestBox,
302 a_idSchedGroup,
303 CURRENT_TIMESTAMP,
304 'infinity'::TIMESTAMP,
305 a_uidAuthor,
306 a_iSchedPriority)
307 RETURNING tsEffective INTO r_tsEffective;
308 END;
309$$ LANGUAGE plpgsql;
310
311
312---
313-- Only adds the user settable parts of the row, i.e. not what TestBoxLogic_updateOnSignOn touches.
314--
315CREATE OR REPLACE function TestBoxLogic_editEntry(a_uidAuthor INTEGER,
316 a_idTestBox INTEGER,
317 a_ip inet,
318 a_uuidSystem uuid,
319 a_sName TEXT,
320 a_sDescription TEXT,
321 a_fEnabled BOOLEAN,
322 a_enmLomKind LomKind_T,
323 a_ipLom inet,
324 a_pctScaleTimeout INTEGER, -- Actually smallint, but default typing fun.
325 a_sComment TEXT,
326 a_enmPendingCmd TestBoxCmd_T,
327 OUT r_idGenTestBox INTEGER,
328 OUT r_tsEffective TIMESTAMP WITH TIME ZONE
329 ) AS $$
330 DECLARE
331 v_Row TestBoxes%ROWTYPE;
332 v_idStrDescription INTEGER;
333 v_idStrComment INTEGER;
334 BEGIN
335 PERFORM TestBoxLogic_checkUniqueName(a_sName, a_idTestBox);
336
337 SELECT TestBoxLogic_lookupOrFindString(a_sDescription) INTO v_idStrDescription;
338 SELECT TestBoxLogic_lookupOrFindString(a_sComment) INTO v_idStrComment;
339
340 -- Fetch and historize the current row - there must be one.
341 UPDATE TestBoxes
342 SET tsExpire = CURRENT_TIMESTAMP
343 WHERE idTestBox = a_idTestBox
344 AND tsExpire = 'infinity'::TIMESTAMP
345 RETURNING * INTO STRICT v_Row;
346
347 -- Modify the row with the new data.
348 v_Row.uidAuthor := a_uidAuthor;
349 v_Row.ip := a_ip;
350 v_Row.uuidSystem := a_uuidSystem;
351 v_Row.sName := a_sName;
352 v_Row.idStrDescription := v_idStrDescription;
353 v_Row.fEnabled := a_fEnabled;
354 v_Row.enmLomKind := a_enmLomKind;
355 v_Row.ipLom := a_ipLom;
356 v_Row.pctScaleTimeout := a_pctScaleTimeout;
357 v_Row.idStrComment := v_idStrComment;
358 v_Row.enmPendingCmd := a_enmPendingCmd;
359 v_Row.tsEffective := v_Row.tsExpire;
360 r_tsEffective := v_Row.tsExpire;
361 v_Row.tsExpire := 'infinity'::TIMESTAMP;
362
363 -- Get a new generation ID.
364 SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
365 r_idGenTestBox := v_Row.idGenTestBox;
366
367 -- Insert the modified row.
368 INSERT INTO TestBoxes VALUES (v_Row.*);
369 END;
370$$ LANGUAGE plpgsql;
371
372
373CREATE OR REPLACE function TestBoxLogic_editGroupEntry(a_uidAuthor INTEGER,
374 a_idTestBox INTEGER,
375 a_idSchedGroup INTEGER,
376 a_iSchedPriority INTEGER,
377 OUT r_tsEffective TIMESTAMP WITH TIME ZONE
378 ) AS $$
379 DECLARE
380 v_Row TestBoxesInSchedGroups%ROWTYPE;
381 v_idStrDescription INTEGER;
382 v_idStrComment INTEGER;
383 BEGIN
384 PERFORM TestBoxLogic_checkSchedGroupExists(a_idSchedGroup);
385
386 -- Fetch and historize the current row - there must be one.
387 UPDATE TestBoxesInSchedGroups
388 SET tsExpire = CURRENT_TIMESTAMP
389 WHERE idTestBox = a_idTestBox
390 AND idSchedGroup = a_idSchedGroup
391 AND tsExpire = 'infinity'::TIMESTAMP
392 RETURNING * INTO STRICT v_Row;
393
394 -- Modify the row with the new data.
395 v_Row.uidAuthor := a_uidAuthor;
396 v_Row.iSchedPriority := a_iSchedPriority;
397 v_Row.tsEffective := v_Row.tsExpire;
398 r_tsEffective := v_Row.tsExpire;
399 v_Row.tsExpire := 'infinity'::TIMESTAMP;
400
401 -- Insert the modified row.
402 INSERT INTO TestBoxesInSchedGroups VALUES (v_Row.*);
403 END;
404$$ LANGUAGE plpgsql;
405
406
407CREATE OR REPLACE FUNCTION TestBoxLogic_removeEntry(a_uidAuthor INTEGER, a_idTestBox INTEGER, a_fCascade BOOLEAN)
408 RETURNS VOID AS $$
409 DECLARE
410 v_Row TestBoxes%ROWTYPE;
411 v_tsEffective TIMESTAMP WITH TIME ZONE;
412 v_Rec RECORD;
413 v_sErrors TEXT;
414 BEGIN
415 --
416 -- Check preconditions.
417 --
418 IF a_fCascade <> TRUE THEN
419 -- @todo implement checks which throws useful exceptions.
420 ELSE
421 RAISE EXCEPTION 'CASCADE test box deletion is not implemented';
422 END IF;
423
424 --
425 -- Delete all current groups, skipping history since we're also deleting the testbox.
426 --
427 UPDATE TestBoxesInSchedGroups
428 SET tsExpire = CURRENT_TIMESTAMP
429 WHERE idTestBox = a_idTestBox
430 AND tsExpire = 'infinity'::TIMESTAMP;
431
432 --
433 -- To preserve the information about who deleted the record, we try to
434 -- add a dummy record which expires immediately. I say try because of
435 -- the primary key, we must let the new record be valid for 1 us. :-(
436 --
437 SELECT * INTO STRICT v_Row
438 FROM TestBoxes
439 WHERE idTestBox = a_idTestBox
440 AND tsExpire = 'infinity'::TIMESTAMP;
441
442 v_tsEffective := CURRENT_TIMESTAMP - INTERVAL '1 microsecond';
443 IF v_Row.tsEffective < v_tsEffective THEN
444 PERFORM TestBoxLogic_historizeEntry(v_Row.idGenTestBox, v_tsEffective);
445
446 v_Row.tsEffective := v_tsEffective;
447 v_Row.tsExpire := CURRENT_TIMESTAMP;
448 v_Row.uidAuthor := a_uidAuthor;
449 SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
450 INSERT INTO TestBoxes VALUES (v_Row.*);
451 ELSE
452 PERFORM TestBoxLogic_historizeEntry(v_Row.idGenTestBox, CURRENT_TIMESTAMP);
453 END IF;
454
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 RAISE EXCEPTION 'Test box with ID % does not currently exist', a_idTestBox;
458 WHEN TOO_MANY_ROWS THEN
459 RAISE EXCEPTION 'Integrity error in TestBoxes: Too many current rows for %', a_idTestBox;
460 END;
461$$ LANGUAGE plpgsql;
462
463
464CREATE OR REPLACE FUNCTION TestBoxLogic_removeGroupEntry(a_uidAuthor INTEGER, a_idTestBox INTEGER, a_idSchedGroup INTEGER)
465 RETURNS VOID AS $$
466 DECLARE
467 v_Row TestBoxesInSchedGroups%ROWTYPE;
468 v_tsEffective TIMESTAMP WITH TIME ZONE;
469 BEGIN
470 --
471 -- To preserve the information about who deleted the record, we try to
472 -- add a dummy record which expires immediately. I say try because of
473 -- the primary key, we must let the new record be valid for 1 us. :-(
474 --
475 SELECT * INTO STRICT v_Row
476 FROM TestBoxesInSchedGroups
477 WHERE idTestBox = a_idTestBox
478 AND idSchedGroup = a_idSchedGroup
479 AND tsExpire = 'infinity'::TIMESTAMP;
480
481 v_tsEffective := CURRENT_TIMESTAMP - INTERVAL '1 microsecond';
482 IF v_Row.tsEffective < v_tsEffective THEN
483 PERFORM TestBoxLogic_historizeGroupEntry(a_idTestBox, a_idSchedGroup, v_tsEffective);
484
485 v_Row.tsEffective := v_tsEffective;
486 v_Row.tsExpire := CURRENT_TIMESTAMP;
487 v_Row.uidAuthor := a_uidAuthor;
488 INSERT INTO TestBoxesInSchedGroups VALUES (v_Row.*);
489 ELSE
490 PERFORM TestBoxLogic_historizeGroupEntry(a_idTestBox, a_idSchedGroup, CURRENT_TIMESTAMP);
491 END IF;
492
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 RAISE EXCEPTION 'TestBox #% does is not currently a member of scheduling group #%', a_idTestBox, a_idSchedGroup;
496 WHEN TOO_MANY_ROWS THEN
497 RAISE EXCEPTION 'Integrity error in TestBoxesInSchedGroups: Too many current rows for % / %',
498 a_idTestBox, a_idSchedGroup;
499 END;
500$$ LANGUAGE plpgsql;
501
502
503---
504-- Sign on update
505--
506CREATE OR REPLACE function TestBoxLogic_updateOnSignOn(a_idTestBox INTEGER,
507 a_ip inet,
508 a_sOs TEXT,
509 a_sOsVersion TEXT,
510 a_sCpuVendor TEXT,
511 a_sCpuArch TEXT,
512 a_sCpuName TEXT,
513 a_lCpuRevision bigint,
514 a_cCpus INTEGER, -- Actually smallint, but default typing fun.
515 a_fCpuHwVirt boolean,
516 a_fCpuNestedPaging boolean,
517 a_fCpu64BitGuest boolean,
518 a_fChipsetIoMmu boolean,
519 a_fRawMode boolean,
520 a_cMbMemory bigint,
521 a_cMbScratch bigint,
522 a_sReport TEXT,
523 a_iTestBoxScriptRev INTEGER,
524 a_iPythonHexVersion INTEGER,
525 OUT r_idGenTestBox INTEGER
526 ) AS $$
527 DECLARE
528 v_Row TestBoxes%ROWTYPE;
529 v_idStrOs INTEGER;
530 v_idStrOsVersion INTEGER;
531 v_idStrCpuVendor INTEGER;
532 v_idStrCpuArch INTEGER;
533 v_idStrCpuName INTEGER;
534 v_idStrReport INTEGER;
535 BEGIN
536 SELECT TestBoxLogic_lookupOrFindString(a_sOs) INTO v_idStrOs;
537 SELECT TestBoxLogic_lookupOrFindString(a_sOsVersion) INTO v_idStrOsVersion;
538 SELECT TestBoxLogic_lookupOrFindString(a_sCpuVendor) INTO v_idStrCpuVendor;
539 SELECT TestBoxLogic_lookupOrFindString(a_sCpuArch) INTO v_idStrCpuArch;
540 SELECT TestBoxLogic_lookupOrFindString(a_sCpuName) INTO v_idStrCpuName;
541 SELECT TestBoxLogic_lookupOrFindString(a_sReport) INTO v_idStrReport;
542
543 -- Fetch and historize the current row - there must be one.
544 UPDATE TestBoxes
545 SET tsExpire = CURRENT_TIMESTAMP
546 WHERE idTestBox = a_idTestBox
547 AND tsExpire = 'infinity'::TIMESTAMP
548 RETURNING * INTO STRICT v_Row;
549
550 -- Modify the row with the new data.
551 v_Row.uidAuthor := NULL;
552 v_Row.ip := a_ip;
553 v_Row.idStrOs := v_idStrOs;
554 v_Row.idStrOsVersion := v_idStrOsVersion;
555 v_Row.idStrCpuVendor := v_idStrCpuVendor;
556 v_Row.idStrCpuArch := v_idStrCpuArch;
557 v_Row.idStrCpuName := v_idStrCpuName;
558 v_Row.lCpuRevision := a_lCpuRevision;
559 v_Row.cCpus := a_cCpus;
560 v_Row.fCpuHwVirt := a_fCpuHwVirt;
561 v_Row.fCpuNestedPaging := a_fCpuNestedPaging;
562 v_Row.fCpu64BitGuest := a_fCpu64BitGuest;
563 v_Row.fChipsetIoMmu := a_fChipsetIoMmu;
564 v_Row.fRawMode := a_fRawMode;
565 v_Row.cMbMemory := a_cMbMemory;
566 v_Row.cMbScratch := a_cMbScratch;
567 v_Row.idStrReport := v_idStrReport;
568 v_Row.iTestBoxScriptRev := a_iTestBoxScriptRev;
569 v_Row.iPythonHexVersion := a_iPythonHexVersion;
570 v_Row.tsEffective := v_Row.tsExpire;
571 v_Row.tsExpire := 'infinity'::TIMESTAMP;
572
573 -- Get a new generation ID.
574 SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
575 r_idGenTestBox := v_Row.idGenTestBox;
576
577 -- Insert the modified row.
578 INSERT INTO TestBoxes VALUES (v_Row.*);
579 END;
580$$ LANGUAGE plpgsql;
581
582
583---
584-- Set new command.
585--
586CREATE OR REPLACE function TestBoxLogic_setCommand(a_uidAuthor INTEGER,
587 a_idTestBox INTEGER,
588 a_enmOldCmd TestBoxCmd_T,
589 a_enmNewCmd TestBoxCmd_T,
590 a_sComment TEXT,
591 OUT r_idGenTestBox INTEGER,
592 OUT r_tsEffective TIMESTAMP WITH TIME ZONE
593 ) AS $$
594 DECLARE
595 v_Row TestBoxes%ROWTYPE;
596 v_idStrComment INTEGER;
597 BEGIN
598 SELECT TestBoxLogic_lookupOrFindString(a_sComment) INTO v_idStrComment;
599
600 -- Fetch and historize the current row - there must be one.
601 UPDATE TestBoxes
602 SET tsExpire = CURRENT_TIMESTAMP
603 WHERE idTestBox = a_idTestBox
604 AND tsExpire = 'infinity'::TIMESTAMP
605 AND enmPendingCmd = a_enmOldCmd
606 RETURNING * INTO STRICT v_Row;
607
608 -- Modify the row with the new data.
609 v_Row.enmPendingCmd := a_enmNewCmd;
610 IF v_idStrComment IS NOT NULL THEN
611 v_Row.idStrComment := v_idStrComment;
612 END IF;
613 v_Row.tsEffective := v_Row.tsExpire;
614 r_tsEffective := v_Row.tsExpire;
615 v_Row.tsExpire := 'infinity'::TIMESTAMP;
616
617 -- Get a new generation ID.
618 SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
619 r_idGenTestBox := v_Row.idGenTestBox;
620
621 -- Insert the modified row.
622 INSERT INTO TestBoxes VALUES (v_Row.*);
623 END;
624$$ LANGUAGE plpgsql;
625
Note: See TracBrowser for help on using the repository browser.

© 2024 Oracle Support Privacy / Do Not Sell My Info Terms of Use Trademark Policy Automated Access Etiquette