1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
|
-- $Id: testcase.pgsql $
--- @file
-- VBox Test Manager Database Stored Procedures - TestCases.
--
--
-- Copyright (C) 2012-2023 Oracle and/or its affiliates.
--
-- This file is part of VirtualBox base platform packages, as
-- available from https://www.virtualbox.org.
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation, in version 3 of the
-- License.
--
-- This program is distributed in the hope that it will be useful, but
-- WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, see <https://www.gnu.org/licenses>.
--
-- The contents of this file may alternatively be used under the terms
-- of the Common Development and Distribution License Version 1.0
-- (CDDL), a copy of it is provided in the "COPYING.CDDL" file included
-- in the VirtualBox distribution, in which case the provisions of the
-- CDDL are applicable instead of those of the GPL.
--
-- You may elect to license modified versions of this file under the
-- terms and conditions of either the GPL or the CDDL or both.
--
-- SPDX-License-Identifier: GPL-3.0-only OR CDDL-1.0
--
\set ON_ERROR_STOP 1
\connect testmanager;
DROP FUNCTION IF EXISTS add_testcase(INTEGER, TEXT, TEXT, BOOLEAN, INTEGER, TEXT, TEXT);
DROP FUNCTION IF EXISTS edit_testcase(INTEGER, INTEGER, TEXT, TEXT, BOOLEAN, INTEGER, TEXT, TEXT);
DROP FUNCTION IF EXISTS del_testcase(INTEGER);
DROP FUNCTION IF EXISTS TestCaseLogic_delEntry(INTEGER, INTEGER);
DROP FUNCTION IF EXISTS TestCaseLogic_addEntry(a_uidAuthor INTEGER, a_sName TEXT, a_sDescription TEXT,
a_fEnabled BOOL, a_cSecTimeout INTEGER, a_sTestBoxReqExpr TEXT,
a_sBuildReqExpr TEXT, a_sBaseCmd TEXT, a_sTestSuiteZips TEXT);
DROP FUNCTION IF EXISTS TestCaseLogic_editEntry(a_uidAuthor INTEGER, a_idTestCase INTEGER, a_sName TEXT, a_sDescription TEXT,
a_fEnabled BOOL, a_cSecTimeout INTEGER, a_sTestBoxReqExpr TEXT,
a_sBuildReqExpr TEXT, a_sBaseCmd TEXT, a_sTestSuiteZips TEXT);
---
-- Checks if the test case name is unique, ignoring a_idTestCaseIgnore.
-- Raises exception if duplicates are found.
--
-- @internal
--
CREATE OR REPLACE FUNCTION TestCaseLogic_checkUniqueName(a_sName TEXT, a_idTestCaseIgnore INTEGER)
RETURNS VOID AS $$
DECLARE
v_cRows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_cRows
FROM TestCases
WHERE sName = a_sName
AND tsExpire = 'infinity'::TIMESTAMP
AND idTestCase <> a_idTestCaseIgnore;
IF v_cRows <> 0 THEN
RAISE EXCEPTION 'Duplicate test case name "%" (% times)', a_sName, v_cRows;
END IF;
END;
$$ LANGUAGE plpgsql;
---
-- Check that the test case exists.
-- Raises exception if it doesn't.
--
-- @internal
--
CREATE OR REPLACE FUNCTION TestCaseLogic_checkExists(a_idTestCase INTEGER) RETURNS VOID AS $$
BEGIN
IF NOT EXISTS( SELECT *
FROM TestCases
WHERE idTestCase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP ) THEN
RAISE EXCEPTION 'Test case with ID % does not currently exist', a_idTestCase;
END IF;
END;
$$ LANGUAGE plpgsql;
---
-- Historize a row.
-- @internal
--
CREATE OR REPLACE FUNCTION TestCaseLogic_historizeEntry(a_idTestCase INTEGER, a_tsExpire TIMESTAMP WITH TIME ZONE)
RETURNS VOID AS $$
DECLARE
v_cUpdatedRows INTEGER;
BEGIN
UPDATE TestCases
SET tsExpire = a_tsExpire
WHERE idTestcase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP;
GET DIAGNOSTICS v_cUpdatedRows = ROW_COUNT;
IF v_cUpdatedRows <> 1 THEN
IF v_cUpdatedRows = 0 THEN
RAISE EXCEPTION 'Test case ID % does not currently exist', a_idTestCase;
END IF;
RAISE EXCEPTION 'Integrity error in TestCases: % current rows with idTestCase=%d', v_cUpdatedRows, a_idTestCase;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE function TestCaseLogic_addEntry(a_uidAuthor INTEGER, a_sName TEXT, a_sDescription TEXT,
a_fEnabled BOOL, a_cSecTimeout INTEGER, a_sTestBoxReqExpr TEXT,
a_sBuildReqExpr TEXT, a_sBaseCmd TEXT, a_sTestSuiteZips TEXT,
a_sComment TEXT)
RETURNS INTEGER AS $$
DECLARE
v_idTestCase INTEGER;
BEGIN
PERFORM TestCaseLogic_checkUniqueName(a_sName, -1);
INSERT INTO TestCases (uidAuthor, sName, sDescription, fEnabled, cSecTimeout,
sTestBoxReqExpr, sBuildReqExpr, sBaseCmd, sTestSuiteZips, sComment)
VALUES (a_uidAuthor, a_sName, a_sDescription, a_fEnabled, a_cSecTimeout,
a_sTestBoxReqExpr, a_sBuildReqExpr, a_sBaseCmd, a_sTestSuiteZips, a_sComment)
RETURNING idTestcase INTO v_idTestCase;
RETURN v_idTestCase;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE function TestCaseLogic_editEntry(a_uidAuthor INTEGER, a_idTestCase INTEGER, a_sName TEXT, a_sDescription TEXT,
a_fEnabled BOOL, a_cSecTimeout INTEGER, a_sTestBoxReqExpr TEXT,
a_sBuildReqExpr TEXT, a_sBaseCmd TEXT, a_sTestSuiteZips TEXT,
a_sComment TEXT)
RETURNS INTEGER AS $$
DECLARE
v_idGenTestCase INTEGER;
BEGIN
PERFORM TestCaseLogic_checkExists(a_idTestCase);
PERFORM TestCaseLogic_checkUniqueName(a_sName, a_idTestCase);
PERFORM TestCaseLogic_historizeEntry(a_idTestCase, CURRENT_TIMESTAMP);
INSERT INTO TestCases (idTestCase, uidAuthor, sName, sDescription, fEnabled, cSecTimeout,
sTestBoxReqExpr, sBuildReqExpr, sBaseCmd, sTestSuiteZips, sComment)
VALUES (a_idTestCase, a_uidAuthor, a_sName, a_sDescription, a_fEnabled, a_cSecTimeout,
a_sTestBoxReqExpr, a_sBuildReqExpr, a_sBaseCmd, a_sTestSuiteZips, a_sComment)
RETURNING idGenTestCase INTO v_idGenTestCase;
RETURN v_idGenTestCase;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION TestCaseLogic_delEntry(a_uidAuthor INTEGER, a_idTestCase INTEGER, a_fCascade BOOLEAN)
RETURNS VOID AS $$
DECLARE
v_Row TestCases%ROWTYPE;
v_tsEffective TIMESTAMP WITH TIME ZONE;
v_Rec RECORD;
v_sErrors TEXT;
BEGIN
--
-- Check preconditions.
--
IF a_fCascade <> TRUE THEN
IF EXISTS( SELECT *
FROM TestCaseDeps
WHERE idTestCasePreReq = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP ) THEN
v_sErrors := '';
FOR v_Rec IN
SELECT TestCases.idTestCase AS idTestCase,
TestCases.sName AS sName
FROM TestCaseDeps, TestCases
WHERE TestCaseDeps.idTestCasePreReq = a_idTestCase
AND TestCaseDeps.tsExpire = 'infinity'::TIMESTAMP
AND TestCases.idTestCase = TestCaseDeps.idTestCase
AND TestCases.tsExpire = 'infinity'::TIMESTAMP
LOOP
IF v_sErrors <> '' THEN
v_sErrors := v_sErrors || ', ';
END IF;
v_sErrors := v_sErrors || v_Rec.sName || ' (idTestCase=' || v_Rec.idTestCase || ')';
END LOOP;
RAISE EXCEPTION 'Other test cases depends on test case with ID %: % ', a_idTestCase, v_sErrors;
END IF;
IF EXISTS( SELECT *
FROM TestGroupMembers
WHERE idTestCase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP ) THEN
v_sErrors := '';
FOR v_Rec IN
SELECT TestGroups.idTestGroup AS idTestGroup,
TestGroups.sName AS sName
FROM TestGroupMembers, TestGroups
WHERE TestGroupMembers.idTestCase = a_idTestCase
AND TestGroupMembers.tsExpire = 'infinity'::TIMESTAMP
AND TestGroupMembers.idTestGroup = TestGroups.idTestGroup
AND TestGroups.tsExpire = 'infinity'::TIMESTAMP
LOOP
IF v_sErrors <> '' THEN
v_sErrors := v_sErrors || ', ';
END IF;
v_sErrors := v_sErrors || v_Rec.sName || ' (idTestGroup=' || v_Rec.idTestGroup || ')';
END LOOP;
RAISE EXCEPTION 'Test case with ID % is member of the following test group(s): % ', a_idTestCase, v_sErrors;
END IF;
END IF;
--
-- To preserve the information about who deleted the record, we try to
-- add a dummy record which expires immediately. I say try because of
-- the primary key, we must let the new record be valid for 1 us. :-(
--
SELECT * INTO STRICT v_Row
FROM TestCases
WHERE idTestCase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP;
v_tsEffective := CURRENT_TIMESTAMP - INTERVAL '1 microsecond';
IF v_Row.tsEffective < v_tsEffective THEN
PERFORM TestCaseLogic_historizeEntry(a_idTestCase, v_tsEffective);
v_Row.tsEffective := v_tsEffective;
v_Row.tsExpire := CURRENT_TIMESTAMP;
v_Row.uidAuthor := a_uidAuthor;
SELECT NEXTVAL('TestCaseGenIdSeq') INTO v_Row.idGenTestCase;
INSERT INTO TestCases VALUES (v_Row.*);
ELSE
PERFORM TestCaseLogic_historizeEntry(a_idTestCase, CURRENT_TIMESTAMP);
END IF;
--
-- Delete arguments, test case dependencies and resource dependencies.
-- (We don't bother recording who deleted the records here since it's
-- a lot of work and sufficiently covered in the TestCases table.)
--
UPDATE TestCaseArgs
SET tsExpire = CURRENT_TIMESTAMP
WHERE idTestCase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP;
UPDATE TestCaseDeps
SET tsExpire = CURRENT_TIMESTAMP
WHERE idTestCase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP;
UPDATE TestCaseGlobalRsrcDeps
SET tsExpire = CURRENT_TIMESTAMP
WHERE idTestCase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP;
IF a_fCascade = TRUE THEN
UPDATE TestCaseDeps
SET tsExpire = CURRENT_TIMESTAMP
WHERE idTestCasePreReq = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP;
UPDATE TestGroupMembers
SET tsExpire = CURRENT_TIMESTAMP
WHERE idTestCase = a_idTestCase
AND tsExpire = 'infinity'::TIMESTAMP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'Test case with ID % does not currently exist', a_idTestCase;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'Integrity error in TestCases: Too many current rows for %', a_idTestCase;
END;
$$ LANGUAGE plpgsql;
|
