-- $Id$

--- @file

-- VBox Test Manager Database - Adds an idBuildCategories to TestSets.

--

--

-- Copyright (C) 2013-2014 Oracle Corporation

--

-- This file is part of VirtualBox Open Source Edition (OSE), as

-- available from http://www.virtualbox.org. This file is free software;

-- you can redistribute it and/or modify it under the terms of the GNU

-- General Public License (GPL) as published by the Free Software

-- Foundation, in version 2 as it comes in the "COPYING" file of the

-- VirtualBox OSE distribution. VirtualBox OSE is distributed in the

-- hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.

--

-- The contents of this file may alternatively be used under the terms

-- of the Common Development and Distribution License Version 1.0

-- (CDDL) only, as it comes in the "COPYING.CDDL" file of the

-- VirtualBox OSE 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.

--

--

-- 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;