diff options
Diffstat (limited to 'src/VBox/ValidationKit/testmanager/db/tmdb-r11-testsets-2.pgsql')
| -rw-r--r-- | src/VBox/ValidationKit/testmanager/db/tmdb-r11-testsets-2.pgsql | 214 |
1 files changed, 214 insertions, 0 deletions
diff --git a/src/VBox/ValidationKit/testmanager/db/tmdb-r11-testsets-2.pgsql b/src/VBox/ValidationKit/testmanager/db/tmdb-r11-testsets-2.pgsql new file mode 100644 index 00000000..1df2807f --- /dev/null +++ b/src/VBox/ValidationKit/testmanager/db/tmdb-r11-testsets-2.pgsql @@ -0,0 +1,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; + |