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 | --
|
---|
31 | DROP 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);
|
---|
46 | DROP 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);
|
---|
61 | DROP FUNCTION IF EXISTS TestBoxLogic_removeEntry(INTEGER, INTEGER, BOOLEAN);
|
---|
62 | DROP 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);
|
---|
67 | DROP 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 | --
|
---|
80 | CREATE 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 | --
|
---|
103 | CREATE 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 | --
|
---|
128 | CREATE 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 | --
|
---|
149 | CREATE 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 | --
|
---|
173 | CREATE 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 | --
|
---|
202 | CREATE 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 | --
|
---|
230 | CREATE 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 |
|
---|
284 | CREATE 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 | --
|
---|
315 | CREATE 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 |
|
---|
373 | CREATE 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 |
|
---|
407 | CREATE 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 |
|
---|
464 | CREATE 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 | --
|
---|
506 | CREATE 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 | --
|
---|
586 | CREATE 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 |
|
---|