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
|
-- $Id: tmdb-r11-testsets-2.pgsql $
--- @file
-- VBox Test Manager Database - Adds an idBuildCategories to TestSets.
--
--
-- Copyright (C) 2013-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
--
--
-- Drop all indexes (might already be dropped).
--
DROP INDEX TestSetsGangIdx;
DROP INDEX TestSetsBoxIdx;
DROP INDEX TestSetsBuildIdx;
DROP INDEX TestSetsTestCaseIdx;
DROP INDEX TestSetsTestVarIdx;
DROP INDEX TestSetsCreated;
DROP INDEX TestSetsDone;
--
-- Drop foreign keys on this table.
--
ALTER TABLE SchedQueues DROP CONSTRAINT SchedQueues_idTestSetGangLeader_fkey;
ALTER TABLE TestBoxStatuses DROP CONSTRAINT TestBoxStatuses_idTestSet_fkey;
ALTER TABLE TestResults DROP CONSTRAINT idTestSetFk; -- old name
ALTER TABLE TestResults DROP CONSTRAINT TestResults_idTestSet_fkey;
ALTER TABLE TestResultValues DROP CONSTRAINT TestResultValues_idTestSet_fkey;
--
-- Cleanup after failed runs.
--
DROP TABLE NewTestSets;
DROP TABLE OldTestSets;
-- Die on error from now on.
\set ON_ERROR_STOP 1
\set AUTOCOMMIT 0
\d+ TestSets;
--
-- Create the new version of the table and filling with the content of the old.
--
CREATE TABLE NewTestSets (
--- The ID of this test set.
idTestSet INTEGER DEFAULT NEXTVAL('TestSetIdSeq') NOT NULL, -- PRIMARY KEY
--- The test config timestamp, used when reading test config.
tsConfig TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
--- When this test set was scheduled.
-- idGenTestBox is valid at this point.
tsCreated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
--- When this test completed, i.e. testing stopped. This should only be set once.
tsDone TIMESTAMP WITH TIME ZONE DEFAULT NULL,
--- The current status.
enmStatus TestStatus_T DEFAULT 'running'::TestStatus_T NOT NULL,
--- The build we're testing.
-- Non-unique foreign key: Builds(idBuild)
idBuild INTEGER NOT NULL,
--- The build category of idBuild when the test started.
-- This is for speeding up graph data collection, i.e. avoid idBuild
-- the WHERE part of the selection.
idBuildCategory INTEGER , -- NOT NULL REFERENCES BuildCategories(idBuildCategory)
--- The test suite build we're using to do the testing.
-- This is NULL if the test suite zip wasn't referred or if a test suite
-- build source wasn't configured.
-- Non-unique foreign key: Builds(idBuild)
idBuildTestSuite INTEGER DEFAULT NULL,
--- The exact testbox configuration.
idGenTestBox INTEGER NOT NULL, -- REFERENCES TestBoxes(idGenTestBox)
--- The testbox ID for joining with (valid: tsStarted).
-- Non-unique foreign key: TestBoxes(idTestBox)
idTestBox INTEGER NOT NULL,
--- The testgroup (valid: tsConfig).
-- Non-unique foreign key: TestBoxes(idTestGroup)
-- Note! This also gives the member ship entry, since a testcase can only
-- have one membership per test group.
idTestGroup INTEGER NOT NULL,
--- The exact test case config we executed in this test run.
idGenTestCase INTEGER NOT NULL, -- REFERENCES TestCases(idGenTestCase)
--- The test case ID for joining with (valid: tsConfig).
-- Non-unique foreign key: TestBoxes(idTestCase)
idTestCase INTEGER NOT NULL,
--- The arguments (and requirements++) we executed this test case with.
idGenTestCaseArgs INTEGER NOT NULL, -- REFERENCES TestCaseArgs(idGenTestCaseArgs)
--- The argument variation ID (valid: tsConfig).
-- Non-unique foreign key: TestCaseArgs(idTestCaseArgs)
idTestCaseArgs INTEGER NOT NULL,
--- The root of the test result tree.
-- @note This will only be NULL early in the transaction setting up the testset.
-- @note If the test reports more than one top level test result, we'll
-- fail the whole test run and let the test developer fix it.
idTestResult INTEGER DEFAULT NULL, -- REFERENCES TestResults(idTestResult)
--- The base filename used for storing files related to this test set.
-- This is a path relative to wherever TM is dumping log files. In order
-- to not become a file system test case, we will try not to put too many
-- hundred thousand files in a directory. A simple first approach would
-- be to just use the current date (tsCreated) like this:
-- TM_FILE_DIR/year/month/day/TestSets.idTestSet
--
-- The primary log file for the test is this name suffixed by '.log'.
--
-- The files in the testresultfile table gets their full names like this:
-- TM_FILE_DIR/sBaseFilename-testresultfile.id-TestResultStrTab(testresultfile.idStrFilename)
--
-- @remarks We store this explicitly in case we change the directly layout
-- at some later point.
sBaseFilename text UNIQUE NOT NULL,
--- The gang member number number, 0 is the leader.
iGangMemberNo SMALLINT DEFAULT 0 NOT NULL, --CHECK (iGangMemberNo >= 0 AND iGangMemberNo < 1024),
--- The test set of the gang leader, NULL if no gang involved.
-- @note This is set by the gang leader as well, so that we can find all
-- gang members by WHERE idTestSetGangLeader = :id.
idTestSetGangLeader INTEGER DEFAULT NULL -- REFERENCES TestSets(idTestSet)
);
COMMIT;
\d+ NewTestSets
-- Note! Using left out join here to speed up things (no hashing).
SELECT COUNT(*) FROM TestSets a LEFT OUTER JOIN Builds b ON (a.idBuild = b.idBuild AND b.tsExpire = 'infinity'::TIMESTAMP);
SELECT COUNT(*) FROM TestSets;
INSERT INTO NewTestSets (idTestSet, tsConfig, tsCreated, tsDone, enmStatus, idBuild, idBuildCategory, idBuildTestSuite,
idGenTestBox, idTestBox, idTestGroup, idGenTestCase, idTestCase, idGenTestCaseArgs, idTestCaseArgs,
idTestResult, sBaseFilename, iGangMemberNo, idTestSetGangLeader )
SELECT a.idTestSet, a.tsConfig, a.tsCreated, tsDone, a.enmStatus, a.idBuild, b.idBuildCategory, a.idBuildTestSuite,
a.idGenTestBox, a.idTestBox, a.idTestGroup, a.idGenTestCase, a.idTestCase, a.idGenTestCaseArgs, a.idTestCaseArgs,
a.idTestResult, a.sBaseFilename, a.iGangMemberNo, a.idTestSetGangLeader
FROM TestSets a LEFT OUTER JOIN Builds b ON (a.idBuild = b.idBuild AND b.tsExpire = 'infinity'::TIMESTAMP);
COMMIT;
SELECT COUNT(*) FROM NewTestSets;
-- Note! 2-3 builds are missing from the Builds table, so fudge it.
UPDATE NewTestSets
SET idBuildCategory = 1
WHERE idBuildCategory IS NULL;
-- Switch the tables.
ALTER TABLE TestSets RENAME TO OldTestSets;
ALTER TABLE NewTestSets RENAME TO TestSets;
COMMIT;
-- Index the table.
CREATE INDEX TestSetsGangIdx ON TestSets (idTestSetGangLeader);
CREATE INDEX TestSetsBoxIdx ON TestSets (idTestBox, idTestResult);
CREATE INDEX TestSetsBuildIdx ON TestSets (idBuild, idTestResult);
CREATE INDEX TestSetsTestCaseIdx ON TestSets (idTestCase, idTestResult);
CREATE INDEX TestSetsTestVarIdx ON TestSets (idTestCaseArgs, idTestResult);
CREATE INDEX TestSetsCreated ON TestSets (tsCreated);
CREATE INDEX TestSetsDone ON TestSets (tsDone);
COMMIT;
-- Drop the old table.
DROP TABLE OldTestSets;
COMMIT;
-- Add the constraints constraint.
ALTER TABLE TestSets ADD CONSTRAINT TestSets_iGangMemberNo_Check CHECK (iGangMemberNo >= 0 AND iGangMemberNo < 1024);
ALTER TABLE TestSets ADD PRIMARY KEY (idTestSet);
ALTER TABLE TestSets ADD FOREIGN KEY (idBuildCategory) REFERENCES BuildCategories(idBuildCategory);
ALTER TABLE TestSets ADD FOREIGN KEY (idGenTestBox) REFERENCES TestBoxes(idGenTestBox);
ALTER TABLE TestSets ADD FOREIGN KEY (idGenTestCase) REFERENCES TestCases(idGenTestCase);
ALTER TABLE TestSets ADD FOREIGN KEY (idGenTestCaseArgs) REFERENCES TestCaseArgs(idGenTestCaseArgs);
ALTER TABLE TestSets ADD FOREIGN KEY (idTestResult) REFERENCES TestResults(idTestResult);
ALTER TABLE TestSets ADD FOREIGN KEY (idTestSetGangLeader) REFERENCES TestSets(idTestSet);
COMMIT;
-- Restore foreign keys.
LOCK TABLE SchedQueues, TestBoxStatuses, TestResults, TestResultValues IN EXCLUSIVE MODE;
ALTER TABLE SchedQueues ADD FOREIGN KEY (idTestSetGangLeader) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE TestBoxStatuses ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE TestResults ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE TestResultValues ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
COMMIT;
\d+ TestSets;
|
