source: vbox/trunk/src/VBox/ValidationKit/testmanager/db/tmdb-r21-testsets-4.pgsql@ 86647

-- $Id: tmdb-r21-testsets-4.pgsql 82968 2020-02-04 10:35:17Z vboxsync $
--- @file
-- VBox Test Manager Database - Adds an idSchedGroup to TestSets in
-- preparation for testboxes belonging to multiple scheduling queues.
--

--
-- Copyright (C) 2013-2020 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.
--

--
-- Cleanup after failed runs.
--
DROP TABLE IF EXISTS OldTestSets;

--
-- Die on error from now on.
--
\set ON_ERROR_STOP 1
\set AUTOCOMMIT 0


-- Total grid lock (don't want to deadlock below).
LOCK TABLE TestBoxStatuses      IN ACCESS EXCLUSIVE MODE;
LOCK TABLE TestSets             IN ACCESS EXCLUSIVE MODE;
LOCK TABLE TestBoxes            IN ACCESS EXCLUSIVE MODE;
LOCK TABLE TestResults          IN ACCESS EXCLUSIVE MODE;
LOCK TABLE TestResultFailures   IN ACCESS EXCLUSIVE MODE;
LOCK TABLE TestResultFiles      IN ACCESS EXCLUSIVE MODE;
LOCK TABLE TestResultMsgs       IN ACCESS EXCLUSIVE MODE;
LOCK TABLE TestResultValues     IN ACCESS EXCLUSIVE MODE;
LOCK TABLE SchedGroups          IN ACCESS EXCLUSIVE MODE;
LOCK TABLE SchedQueues          IN ACCESS EXCLUSIVE MODE;
LOCK TABLE SchedGroupMembers    IN ACCESS EXCLUSIVE MODE;

\d+ TestSets;

--
-- Rename the table, drop foreign keys refering to it, and drop constrains
-- within the table itself.  The latter is mostly for naming and we do it
-- up front in case the database we're running against has different names
-- due to previous conversions.
--
ALTER TABLE TestSets RENAME TO OldTestSets;

ALTER TABLE TestResultFailures  DROP CONSTRAINT IF EXISTS idtestsetfk;
ALTER TABLE TestResultFailures  DROP CONSTRAINT IF EXISTS TestResultFailures_idTestSet_fkey;
ALTER TABLE SchedQueues         DROP CONSTRAINT IF EXISTS SchedQueues_idTestSetGangLeader_fkey;
ALTER TABLE TestBoxStatuses     DROP CONSTRAINT IF EXISTS TestBoxStatuses_idTestSet_fkey;
ALTER TABLE TestResultFiles     DROP CONSTRAINT IF EXISTS TestResultFiles_idTestSet_fkey;
ALTER TABLE TestResultMsgs      DROP CONSTRAINT IF EXISTS TestResultMsgs_idTestSet_fkey;
ALTER TABLE TestResults         DROP CONSTRAINT IF EXISTS TestResults_idTestSet_fkey;
ALTER TABLE TestResultValues    DROP CONSTRAINT IF EXISTS TestResultValues_idTestSet_fkey;
ALTER TABLE TestResultValues    DROP CONSTRAINT IF EXISTS TestResultValues_idTestSet_fkey1;

ALTER TABLE OldTestSets     DROP CONSTRAINT testsets_igangmemberno_check;

ALTER TABLE OldTestSets     DROP CONSTRAINT TestSets_idBuildCategory_fkey;
ALTER TABLE OldTestSets     DROP CONSTRAINT TestSets_idGenTestBox_fkey;
ALTER TABLE OldTestSets     DROP CONSTRAINT TestSets_idGenTestCase_fkey;
ALTER TABLE OldTestSets     DROP CONSTRAINT TestSets_idGenTestCaseArgs_fkey;
ALTER TABLE OldTestSets     DROP CONSTRAINT TestSets_idTestResult_fkey;
ALTER TABLE OldTestSets     DROP CONSTRAINT TestSets_idTestSetGangLeader_fkey;

ALTER TABLE OldTestSets     DROP CONSTRAINT IF EXISTS TestSets_sBaseFilename_key;
ALTER TABLE OldTestSets     DROP CONSTRAINT IF EXISTS NewTestSets_sBaseFilename_key;
ALTER TABLE OldTestSets     DROP CONSTRAINT TestSets_pkey;

DROP INDEX IF EXISTS TestSetsGangIdx;
DROP INDEX IF EXISTS TestSetsBoxIdx;
DROP INDEX IF EXISTS TestSetsBuildIdx;
DROP INDEX IF EXISTS TestSetsTestCaseIdx;
DROP INDEX IF EXISTS TestSetsTestVarIdx;
DROP INDEX IF EXISTS TestSetsDoneCreatedBuildCatIdx;
DROP INDEX IF EXISTS TestSetsGraphBoxIdx;


-- This output should be free of indexes, constraints and references from other tables.
\d+ OldTestSets;

\prompt "Is the above table completely free of indexes, constraints and references? Ctrl-C if not."  dummy

--
-- Create the new table (no foreign keys).
--
CREATE TABLE TestSets (
    --- The ID of this test set.
    idTestSet           INTEGER   DEFAULT NEXTVAL('TestSetIdSeq')  NOT NULL,

    --- 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,
    --- 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,
    --- The testbox ID for joining with (valid: tsStarted).
    -- Non-unique foreign key: TestBoxes(idTestBox)
    idTestBox           INTEGER     NOT NULL,
    --- The scheduling group ID the test was scheduled thru (valid: tsStarted).
    -- Non-unique foreign key: SchedGroups(idSchedGroup)
    idSchedGroup        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,
    --- 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,
    --- 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,

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

);

-- Convert the data.
INSERT INTO TestSets (
            idTestSet,
            tsConfig,
            tsCreated,
            tsDone,
            enmStatus,
            idBuild,
            idBuildCategory,
            idBuildTestSuite,
            idGenTestBox,
            idTestBox,
            idSchedGroup,
            idTestGroup,
            idGenTestCase,
            idTestCase,
            idGenTestCaseArgs,
            idTestCaseArgs,
            idTestResult,
            sBaseFilename,
            iGangMemberNo,
            idTestSetGangLeader
            )
SELECT      OldTestSets.idTestSet,
            OldTestSets.tsConfig,
            OldTestSets.tsCreated,
            OldTestSets.tsDone,
            OldTestSets.enmStatus,
            OldTestSets.idBuild,
            OldTestSets.idBuildCategory,
            OldTestSets.idBuildTestSuite,
            OldTestSets.idGenTestBox,
            OldTestSets.idTestBox,
            TestBoxes.idSchedGroup,
            OldTestSets.idTestGroup,
            OldTestSets.idGenTestCase,
            OldTestSets.idTestCase,
            OldTestSets.idGenTestCaseArgs,
            OldTestSets.idTestCaseArgs,
            OldTestSets.idTestResult,
            OldTestSets.sBaseFilename,
            OldTestSets.iGangMemberNo,
            OldTestSets.idTestSetGangLeader
FROM        OldTestSets
            INNER JOIN TestBoxes
                    ON OldTestSets.idGenTestBox = TestBoxes.idGenTestBox;

-- Restore the primary key and unique constraints.
ALTER TABLE TestSets ADD PRIMARY KEY (idTestSet);
ALTER TABLE TestSets ADD UNIQUE (sBaseFilename);

-- Restore check constraints.
ALTER TABLE TestSets ADD CONSTRAINT TestSets_iGangMemberNo_Check CHECK (iGangMemberNo >= 0 AND iGangMemberNo < 1024);

-- Restore foreign keys in the table.
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);

-- Restore indexes.
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 TestSetsDoneCreatedBuildCatIdx ON TestSets (tsDone DESC NULLS FIRST, tsCreated ASC, idBuildCategory);
CREATE INDEX TestSetsGraphBoxIdx    ON TestSets (idTestBox, tsCreated DESC, tsDone ASC NULLS LAST, idBuildCategory, idTestCase);

-- Restore foreign key references to the table.
ALTER TABLE TestResults         ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE TestResultValues    ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE TestResultFiles     ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE TestResultMsgs      ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE TestResultFailures  ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;

ALTER TABLE TestBoxStatuses     ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
ALTER TABLE SchedQueues         ADD FOREIGN KEY (idTestSetGangLeader) REFERENCES TestSets(idTestSet) MATCH FULL;

-- Drop the old table.
DROP TABLE OldTestSets;

\prompt "Update python files while everything is locked. Hurry!"  dummy

-- Grant access to the new table.
GRANT ALL PRIVILEGES ON TABLE TestSets TO testmanager;

COMMIT;

\d TestSets;