-- $Id$

--- @file

-- VBox Test Manager Database Creation script.

--

--

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

--

--

-- Declaimer:

--

-- The guys working on this design are not database experts, web

-- programming experts or similar, rather we are low level guys

-- who's main job is x86 & AMD64 virtualization. So, please don't

-- be too hard on us. :-)

--

--

DROP DATABASE testmanager; -- WARNING!! WE CURRENTLY DROP THE DATABASE!

\set ON_ERROR_STOP 1

CREATE DATABASE testmanager;

\connect testmanager;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--

-- S y s t e m

--

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

---

-- Log table for a few important events.

--

-- Currently, two events are planned to be logged:

-- - Sign on of an unknown testbox, including the IP and System UUID.

-- This will be restricted to one entry per 24h or something like that:

-- SELECT COUNT(*)

-- FROM SystemLog

-- WHERE tsCreated >= (current_timestamp - interval '24 hours')

-- AND sEvent = 'TBoxUnkn'

-- AND sLogText = :sNewLogText;

-- - When cleaning up an abandond testcase (scenario #9), log which

-- testbox abandond which testset.

--

-- The Web UI will have some way of displaying the log.

--

-- A batch job should regularly clean out old log messages, like for instance

-- > 64 days.

--

CREATE TABLE SystemLog (

--- When this was logged.

tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- The event type.

-- This is a 8 character string identifier so that we don't need to change

-- some enum type everytime we introduce a new event type.

sEvent CHAR(8) NOT NULL,

--- The log text.

sLogText text NOT NULL,

PRIMARY KEY (tsCreated, sEvent)

);

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--

-- C o n f i g u r a t i o n

--

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--- @table Users

-- Test manager users.

--

-- This is mainly for doing simple access checks before permitting access to

-- the test manager. This needs to be coordinated with

-- apache/ldap/Oracle-Single-Sign-On.

--

-- The main purpose, though, is for tracing who changed the test config and

-- analysis data.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp.

--

CREATE SEQUENCE UserIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE Users (

--- The user id.

uid INTEGER DEFAULT NEXTVAL('UserIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER DEFAULT NULL,

--- User name.

sUsername text NOT NULL,

--- The email address of the user.

sEmail text NOT NULL,

--- The full name.

sFullName text NOT NULL,

--- The login name used by apache.

sLoginName text NOT NULL,

PRIMARY KEY (uid, tsExpire)

);

CREATE INDEX UsersLoginNameIdx ON Users (tsExpire, sLoginName);

--- @table GlobalResources

-- Global resource configuration.

--

-- For example an iSCSI target.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp.

--

CREATE SEQUENCE GlobalResourceIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE GlobalResources (

--- The global resource ID.

-- This stays the same thru updates.

idGlobalRsrc INTEGER DEFAULT NEXTVAL('GlobalResourceIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The name of the resource.

sName text NOT NULL,

--- Optional resource description.

sDescription text,

--- Indicates whether this resource is currently enabled (online).

fEnabled boolean DEFAULT FALSE NOT NULL,

PRIMARY KEY (idGlobalRsrc, tsExpire)

);

--- @table BuildSources

-- Build sources.

--

-- This is used by a scheduling group to select builds and the default

-- Validation Kit from the Builds table.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp.

--

-- @todo Any better way of representing this so we could more easily

-- join/whatever when searching for builds?

--

CREATE SEQUENCE BuildSourceIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE BuildSources (

--- The build source identifier.

-- This stays constant over time.

idBuildSrc INTEGER DEFAULT NEXTVAL('BuildSourceIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The name of the build source.

sName TEXT NOT NULL,

--- Description.

sDescription TEXT DEFAULT NULL,

--- Which product.

-- ASSUME that it is okay to limit a build source to a single product.

sProduct text NOT NULL,

--- Which branch.

-- ASSUME that it is okay to limit a build source to a branch.

sBranch text NOT NULL,

--- Build types to include, all matches if NULL.

-- @todo Weighting the types would be nice in a later version.

asTypes text ARRAY DEFAULT NULL,

--- Array of the 'sOs.sCpuArch' to match, all matches if NULL.

-- See KBUILD_OSES in kBuild for a list of standard target OSes, and

-- KBUILD_ARCHES for a list of standard architectures.

--

-- @remarks See marks on 'os-agnostic' and 'noarch' in BuildCategories.

asOsArches text ARRAY DEFAULT NULL,

--- The first subversion tree revision to match, no lower limit if NULL.

iFirstRevision INTEGER DEFAULT NULL,

--- The last subversion tree revision to match, no upper limit if NULL.

iLastRevision INTEGER DEFAULT NULL,

--- The maximum age of the builds in seconds, unlimited if NULL.

cSecMaxAge INTEGER DEFAULT NULL,

PRIMARY KEY (idBuildSrc, tsExpire)

);

--- @table TestCases

-- Test case configuration.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp.

--

CREATE SEQUENCE TestCaseIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE SEQUENCE TestCaseGenIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestCases (

--- The fixed test case ID.

-- This is assigned when the test case is created and will never change.

idTestCase INTEGER DEFAULT NEXTVAL('TestCaseIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- Generation ID for this row, a truly unique identifier.

-- This is primarily for referencing by TestSets.

idGenTestCase INTEGER UNIQUE DEFAULT NEXTVAL('TestCaseGenIdSeq') NOT NULL,

--- The name of the test case.

sName TEXT NOT NULL,

--- Optional test case description.

sDescription TEXT DEFAULT NULL,

--- Indicates whether this test case is currently enabled.

fEnabled BOOLEAN DEFAULT FALSE NOT NULL,

--- Default test case timeout given in seconds.

cSecTimeout INTEGER NOT NULL CHECK (cSecTimeout > 0),

--- Default TestBox requirement expression (python boolean expression).

-- All the scheduler properties are available for use with the same names

-- as in that table.

-- If NULL everything matches.

sTestBoxReqExpr TEXT DEFAULT NULL,

--- Default build requirement expression (python boolean expression).

-- The following build properties are available: sProduct, sBranch,

-- sType, asOsArches, sVersion, iRevision, uidAuthor and idBuild.

-- If NULL everything matches.

sBuildReqExpr TEXT DEFAULT NULL,

--- The base command.

-- String suitable for executing in bourne shell with space as separator

-- (IFS). References to @BUILD_BINARIES@ will be replaced WITH the content

-- of the Builds(sBinaries) field.

sBaseCmd TEXT NOT NULL,

--- Comma separated list of test suite zips (or tars) that the testbox will

-- need to download and expand prior to testing.

-- If NULL the current test suite of the scheduling group will be used (the

-- scheduling group will have an optional test suite build queue associated

-- with it). The current test suite can also be referenced by

-- @VALIDATIONKIT_ZIP@ in case more downloads are required. Files may also be

-- uploaded to the test manager download area, in which case the

-- @DOWNLOAD_BASE_URL@ prefix can be used to refer to this area.

sTestSuiteZips TEXT DEFAULT NULL,

PRIMARY KEY (idTestCase, tsExpire)

);

--- @table TestCaseArgs

-- Test case argument list variations.

--

-- For example, we have a test case that does a set of tests on a virtual

-- machine. To get better code/feature coverage of this testcase we wish to

-- run it with different guest hardware configuration. The test case may do

-- the same stuff, but the guest OS as well as the VMM may react differently to

-- the hardware configurations and uncover issues in the VMM, device emulation

-- or other places.

--

-- Typical hardware variations are:

-- - guest memory size (RAM),

-- - guest video memory size (VRAM),

-- - virtual CPUs / cores / threads,

-- - virtual chipset

-- - virtual network interface card (NIC)

-- - USB 1.1, USB 2.0, no USB

--

-- The TM web UI will help the user create a reasonable set of permutations

-- of these parameters, the user specifies a maximum and the TM uses certain

-- rules together with random selection to generate the desired number. The

-- UI will also help suggest fitting testbox requirements according to the

-- RAM/VRAM sizes and the virtual CPU counts. The user may then make

-- adjustments to the suggestions before commit them.

--

-- Alternatively, the user may also enter all the permutations without any

-- help from the UI.

--

-- Note! All test cases has at least one entry in this table, even if it is

-- empty, because testbox requirements are specified thru this.

--

-- Querying the valid parameter lists for a testase this way:

-- SELECT * ... WHERE idTestCase = TestCases.idTestCase

-- AND tsExpire > <when>

-- AND tsEffective <= <when>;

--

-- Querying the valid parameter list for the latest generation can be

-- simplified by just checking tsExpire date:

-- SELECT * ... WHERE idTestCase = TestCases.idTestCase

-- AND tsExpire == TIMESTAMP WITH TIME ZONE 'infinity';

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp.

--

CREATE SEQUENCE TestCaseArgsIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE SEQUENCE TestCaseArgsGenIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestCaseArgs (

--- The test case ID.

-- Non-unique foreign key: TestCases(idTestCase).

idTestCase INTEGER NOT NULL,

--- The testcase argument variation ID (fixed).

-- This is primarily for TestGroupMembers.aidTestCaseArgs.

idTestCaseArgs INTEGER DEFAULT NEXTVAL('TestCaseArgsIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- Generation ID for this row.

-- This is primarily for efficient referencing by TestSets and SchedQueues.

idGenTestCaseArgs INTEGER UNIQUE DEFAULT NEXTVAL('TestCaseArgsGenIdSeq') NOT NULL,

--- The additional arguments.

-- String suitable for bourne shell style argument parsing with space as

-- separator (IFS). References to @BUILD_BINARIES@ will be replaced with

-- the content of the Builds(sBinaries) field.

sArgs TEXT NOT NULL,

--- Optional test case timeout given in seconds.

-- If NULL, the TestCases.cSecTimeout field is used instead.

cSecTimeout INTEGER DEFAULT NULL CHECK (cSecTimeout IS NULL OR cSecTimeout > 0),

--- Additional TestBox requirement expression (python boolean expression).

-- All the scheduler properties are available for use with the same names

-- as in that table. This is checked after first checking the requirements

-- in the TestCases.sTestBoxReqExpr field.

sTestBoxReqExpr TEXT DEFAULT NULL,

--- Additional build requirement expression (python boolean expression).

-- The following build properties are available: sProduct, sBranch,

-- sType, asOsArches, sVersion, iRevision, uidAuthor and idBuild. This is

-- checked after first checking the requirements in the

-- TestCases.sBuildReqExpr field.

sBuildReqExpr TEXT DEFAULT NULL,

--- Number of testboxes required (gang scheduling).

cGangMembers SMALLINT DEFAULT 1 NOT NULL CHECK (cGangMembers > 0 AND cGangMembers < 1024),

--- The arguments are part of the primary key for several reasons.

-- No duplicate argument lists (makes no sense - if you want to prioritize

-- argument lists, we add that explicitly). This may hopefully enable us

-- to more easily check coverage later on, even when the test case is

-- reconfigured with more/less permutations.

PRIMARY KEY (idTestCase, tsExpire, sArgs)

);

CREATE INDEX TestCaseArgsLookupIdx ON TestCaseArgs (idTestCase, tsExpire, tsEffective);

--- @table TestCaseDeps

-- Test case dependencies (N:M)

--

-- This effect build selection. The build must have passed all runs of the

-- given prerequisite testcase (idTestCasePreReq) and executed at a minimum one

-- argument list variation.

--

-- This should also affect scheduling order, if possible at least one

-- prerequisite testcase variation should be place before the specific testcase

-- in the scheduling queue.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE TABLE TestCaseDeps (

--- The test case that depends on someone.

-- Non-unique foreign key: TestCases(idTestCase).

idTestCase INTEGER NOT NULL,

--- The prerequisite test case ID.

-- Non-unique foreign key: TestCases(idTestCase).

idTestCasePreReq INTEGER NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

PRIMARY KEY (idTestCase, idTestCasePreReq, tsExpire)

);

--- @table TestCaseGlobalRsrcDeps

-- Test case dependencies on global resources (N:M)

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE TABLE TestCaseGlobalRsrcDeps (

--- The test case that depends on someone.

-- Non-unique foreign key: TestCases(idTestCase).

idTestCase INTEGER NOT NULL,

--- The prerequisite resource ID.

-- Non-unique foreign key: GlobalResources(idGlobalRsrc).

idGlobalRsrc INTEGER NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

PRIMARY KEY (idTestCase, idGlobalRsrc, tsExpire)

);

--- @table TestGroups

-- Test Group - A collection of test cases.

--

-- This is for simplifying test configuration by working with a few groups

-- instead of a herd of individual testcases. It may also be used for creating

-- test suites for certain areas (like guest additions) or tasks (like

-- performance measurements).

--

-- A test case can be member of any number of test groups.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE SEQUENCE TestGroupIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestGroups (

--- The fixed scheduling group ID.

-- This is assigned when the group is created and will never change.

idTestGroup INTEGER DEFAULT NEXTVAL('TestGroupIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The name of the scheduling group.

sName text NOT NULL,

--- Optional group description.

sDescription text,

PRIMARY KEY (idTestGroup, tsExpire)

);

CREATE INDEX TestGroups_id_index ON TestGroups (idTestGroup, tsExpire, tsEffective);

--- @table TestGroupMembers

-- The N:M relation ship between test case configurations and test groups.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE TABLE TestGroupMembers (

--- The group ID.

-- Non-unique foreign key: TestGroups(idTestGroup).

idTestGroup INTEGER NOT NULL,

--- The test case ID.

-- Non-unique foreign key: TestCases(idTestCase).

idTestCase INTEGER NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- Test case scheduling priority.

-- Higher number causes the test case to be run more frequently.

-- @sa SchedGroupMembers.iSchedPriority

-- @todo Not sure we want to keep this...

iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL,

--- Limit the memberships to the given argument variations.

-- Non-unique foreign key: TestCaseArgs(idTestCase, idTestCaseArgs).

aidTestCaseArgs INTEGER ARRAY DEFAULT NULL,

PRIMARY KEY (idTestGroup, idTestCase, tsExpire)

);

--- @table SchedGroups

-- Scheduling group (aka. testbox partitioning) configuration.

--

-- A testbox is associated with exactly one scheduling group. This association

-- can be changed, of course. If we (want to) retire a group which still has

-- testboxes associated with it, these will be moved to the 'default' group.

--

-- The TM web UI will make sure that a testbox is always in a group and that

-- the default group cannot be deleted.

--

-- A scheduling group combines several things:

-- - A selection of builds to test (via idBuildSrc).

-- - A collection of test groups to test with (via SchedGroupMembers).

-- - A set of testboxes to test on (via TestBoxes.idSchedGroup).

--

-- In additions there is an optional source of fresh test suite builds (think

-- VBoxTestSuite) as well as scheduling options.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE TYPE Scheduler_T AS ENUM (

'bestEffortContinousItegration',

'reserved'

);

CREATE SEQUENCE SchedGroupIdSeq

START 2

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE SchedGroups (

--- The fixed scheduling group ID.

-- This is assigned when the group is created and will never change.

idSchedGroup INTEGER DEFAULT NEXTVAL('SchedGroupIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

-- @note This is NULL for the default group.

uidAuthor INTEGER DEFAULT NULL,

--- The name of the scheduling group.

sName text NOT NULL,

--- Optional group description.

sDescription text,

--- Indicates whether this group is currently enabled.

fEnabled boolean NOT NULL,

--- The scheduler to use.

-- This is for when we later desire different scheduling that the best

-- effort stuff provided by the initial implementation.

enmScheduler Scheduler_T DEFAULT 'bestEffortContinousItegration'::Scheduler_T NOT NULL,

--- The build source.

-- Non-unique foreign key: BuildSources(idBuildSrc)

idBuildSrc INTEGER DEFAULT NULL,

--- The Validation Kit build source (@VALIDATIONKIT_ZIP@).

-- Non-unique foreign key: BuildSources(idBuildSrc)

idBuildSrcTestSuite INTEGER DEFAULT NULL,

PRIMARY KEY (idSchedGroup, tsExpire)

);

-- Special default group.

INSERT INTO SchedGroups (idSchedGroup, tsEffective, tsExpire, sName, sDescription, fEnabled)

VALUES (1, TIMESTAMP WITH TIME ZONE 'epoch', TIMESTAMP WITH TIME ZONE 'infinity', 'default', 'default group', FALSE);

--- @table SchedGroupMembers

-- N:M relationship between scheduling groups and test groups.

--

-- Several scheduling parameters are associated with this relationship.

--

-- The test group dependency (idTestGroupPreReq) can be used in the same way as

-- TestCaseDeps.idTestCasePreReq, only here on test group level. This means it

-- affects the build selection. The builds needs to have passed all test runs

-- the prerequisite test group and done at least one argument variation of each

-- test case in it.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE TABLE SchedGroupMembers (

--- Scheduling ID.

-- Non-unique foreign key: SchedGroups(idSchedGroup).

idSchedGroup INTEGER NOT NULL,

--- Testgroup ID.

-- Non-unique foreign key: TestGroups(idTestGroup).

idTestGroup INTEGER NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The scheduling priority if the test group.

-- Higher number causes the test case to be run more frequently.

-- @sa TestGroupMembers.iSchedPriority

iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL,

--- When during the week this group is allowed to start running, NULL means

-- there are no constraints.

-- Each bit in the bitstring represents one hour, with bit 0 indicating the

-- midnight hour on a monday.

bmHourlySchedule bit(168) DEFAULT NULL,

--- Optional test group dependency.

-- Non-unique foreign key: TestGroups(idTestGroup).

-- This is for requiring that a build has been subject to smoke tests

-- before bothering to subject it to longer tests.

-- @todo Not entirely sure this should be here, but I'm not so keen on yet

-- another table as the only use case is smoketests.

idTestGroupPreReq INTEGER DEFAULT NULL,

PRIMARY KEY (idSchedGroup, idTestGroup, tsExpire)

);

--- @type TestBoxCmd_T

-- Testbox commands.

CREATE TYPE TestBoxCmd_T AS ENUM (

'none',

'abort',

'reboot', --< This implies abort. Status changes when reaching 'idle'.

'upgrade', --< This is only handled when asking for work.

'upgrade-and-reboot', --< Ditto.

'special' --< Similar to upgrade, reserved for the future.

);

--- @type LomKind_T

-- The kind of lights out management on a testbox.

CREATE TYPE LomKind_T AS ENUM (

'none',

'ilom',

'elom',

'apple-xserve-lom'

);

--- @table TestBoxes

-- Testbox configurations.

--

-- The testboxes are identified by IP and the system UUID if available. Should

-- the IP change, the testbox will be refused at sign on and the testbox

-- sheriff will have to update it's IP.

--

-- @todo Implement the UUID stuff. Get it from DMI, UEFI or whereever.

-- Mismatching needs to be logged somewhere...

--

-- To query the currently valid configuration:

-- SELECT ... WHERE id = idTestBox AND tsExpire = TIMESTAMP WITH TIME ZONE 'infinity';

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE SEQUENCE TestBoxIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE SEQUENCE TestBoxGenIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestBoxes (

--- The fixed testbox ID.

-- This is assigned when the testbox is created and will never change.

idTestBox INTEGER DEFAULT NEXTVAL('TestBoxIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- When modified automatically by the testbox, NULL is used.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER DEFAULT NULL,

--- Generation ID for this row.

-- This is primarily for referencing by TestSets.

idGenTestBox INTEGER UNIQUE DEFAULT NEXTVAL('TestBoxGenIdSeq') NOT NULL,

--- The testbox IP.

-- This is from the webserver point of view and automatically updated on

-- SIGNON. The test setup doesn't permit for IP addresses to change while

-- the testbox is operational, because this will break gang tests.

ip inet NOT NULL,

--- The system or firmware UUID.

-- This uniquely identifies the testbox when talking to the server. After

-- SIGNON though, the testbox will also provide idTestBox and ip to

-- establish its identity beyond doubt.

uuidSystem uuid NOT NULL,

--- The testbox name.

-- Usually similar to the DNS name.

sName text NOT NULL,

--- Optional testbox description.

-- Intended for describing the box as well as making other relevant notes.

sDescription text DEFAULT NULL,

--- Reference to the scheduling group that this testbox is a member of.

-- Non-unique foreign key: SchedGroups(idSchedGroup)

-- A testbox is always part of a group, the default one nothing else.

idSchedGroup INTEGER DEFAULT 1 NOT NULL,

--- Indicates whether this testbox is enabled.

-- A testbox gets disabled when we're doing maintenance, debugging a issue

-- that happens only on that testbox, or some similar stuff. This is an

-- alternative to deleting the testbox.

fEnabled BOOLEAN DEFAULT NULL,

--- The kind of lights-out-management.

enmLomKind LomKind_T DEFAULT 'none'::LomKind_T NOT NULL,

--- The IP adress of the lights-out-management.

-- This can be NULL if enmLomKind is 'none', otherwise it must contain a valid address.

ipLom inet DEFAULT NULL,

--- Timeout scale factor, given as a percent.

-- This is a crude adjustment of the test case timeout for slower hardware.

pctScaleTimeout smallint DEFAULT 100 NOT NULL CHECK (pctScaleTimeout > 10 AND pctScaleTimeout < 20000),

--- @name Scheduling properties (reported by testbox script).

-- @{

--- Same abbrieviations as kBuild, see KBUILD_OSES.

sOs text DEFAULT NULL,

--- Informational, no fixed format.

sOsVersion text DEFAULT NULL,

--- Same as CPUID reports (GenuineIntel, AuthenticAMD, CentaurHauls, ...).

sCpuVendor text DEFAULT NULL,

--- Same as kBuild - x86, amd64, ... See KBUILD_ARCHES.

sCpuArch text DEFAULT NULL,

--- The CPU name if available.

sCpuName text DEFAULT NULL,

--- Number identifying the CPU family/model/stepping/whatever.

-- For x86 and AMD64 type CPUs, this will on the following format:

-- (EffFamily << 24) | (EffModel << 8) | Stepping.

lCpuRevision bigint DEFAULT NULL,

--- Number of CPUs, CPU cores and CPU threads.

cCpus smallint DEFAULT NULL CHECK (cCpus IS NULL OR cCpus > 0),

--- Set if capable of hardware virtualization.

fCpuHwVirt boolean DEFAULT NULL,

--- Set if capable of nested paging.

fCpuNestedPaging boolean DEFAULT NULL,

--- Set if CPU capable of 64-bit (VBox) guests.

fCpu64BitGuest boolean DEFAULT NULL,

--- Set if chipset with usable IOMMU (VT-d / AMD-Vi).

fChipsetIoMmu boolean DEFAULT NULL,

--- The (approximate) memory size in megabytes (rounded down to nearest 4 MB).

cMbMemory bigint DEFAULT NULL CHECK (cMbMemory IS NULL OR cMbMemory > 0),

--- The amount of scratch space in megabytes (rounded down to nearest 64 MB).

cMbScratch bigint DEFAULT NULL CHECK (cMbScratch IS NULL OR cMbScratch >= 0),

--- Free form hardware and software report field.

sReport text DEFAULT NULL,

--- @}

--- The testbox script revision number, serves the purpose of a version number.

-- Probably good to have when scheduling upgrades as well for status purposes.

iTestBoxScriptRev INTEGER DEFAULT 0 NOT NULL,

--- The python sys.hexversion (layed out as of 2.7).

-- Good to know which python versions we need to support.

iPythonHexVersion INTEGER DEFAULT NULL,

--- Pending command.

-- @note We put it here instead of in TestBoxStatuses to get history.

enmPendingCmd TestBoxCmd_T DEFAULT 'none'::TestBoxCmd_T NOT NULL,

PRIMARY KEY (idTestBox, tsExpire),

--- Nested paging requires hardware virtualization.

CHECK (fCpuNestedPaging IS NULL OR (fCpuNestedPaging <> TRUE OR fCpuHwVirt = TRUE))

);

CREATE UNIQUE INDEX TestBoxesUuidIdx ON TestBoxes (uuidSystem, tsExpire);

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--

-- F a i l u r e T r a c k i n g

--

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--- @table FailureCategories

-- Failure categories.

--

-- This is for organizing the failure reasons.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE SEQUENCE FailureCategoryIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE FailureCategories (

--- The identifier of this failure category (once assigned, it will never change).

idFailureCategory INTEGER DEFAULT NEXTVAL('FailureCategoryIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The short category description.

-- For combo boxes and other selection lists.

sShort text NOT NULL,

--- Full description

-- For cursor-over-poppups for instance.

sFull text NOT NULL,

PRIMARY KEY (idFailureCategory, tsExpire)

);

--- @table FailureReasons

-- Failure reasons.

--

-- When analysing a test failure, the testbox sheriff will try assign a fitting

-- reason for the failure. This table is here to help the sheriff in his/hers

-- job as well as developers looking checking if their changes affected the

-- test results in any way.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE SEQUENCE FailureReasonIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE FailureReasons (

--- The identifier of this failure reason (once assigned, it will never change).

idFailureReason INTEGER DEFAULT NEXTVAL('FailureReasonIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The failure category this reason belongs to.

-- Non-unique foreign key: FailureCategories(idFailureCategory)

idFailureCategory INTEGER NOT NULL,

--- The short failure description.

-- For combo boxes and other selection lists.

sShort text NOT NULL,

--- Full failure description.

sFull text NOT NULL,

--- Ticket number in the primary bugtracker.

iTicket INTEGER DEFAULT NULL,

--- Other URLs to reports or discussions of the observed symptoms.

asUrls text ARRAY DEFAULT NULL,

PRIMARY KEY (idFailureReason, tsExpire)

);

CREATE INDEX FailureReasonsCategoryIdx ON FailureReasons (idFailureCategory, idFailureReason);

--- @table TestResultFailures

-- This is for tracking/discussing test result failures.

--

-- The rational for putting this is a separate table is that we need history on

-- this while TestResults does not.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE TABLE TestResultFailures (

--- The test result we're disucssing.

-- @note The foreign key is declared after TestResults (further down).

idTestResult INTEGER NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The suggested failure reason.

-- Non-unique foreign key: FailureReasons(idFailureReason)

idFailureReason INTEGER NOT NULL,

--- Optional comment.

sComment text DEFAULT NULL,

PRIMARY KEY (idTestResult, tsExpire)

);

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--

-- T e s t I n p u t

--

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--- @table BuildBlacklist

-- Table used to blacklist sets of builds.

--

-- The best usage example is a VMM developer realizing that a change causes the

-- host to panic, hang, or otherwise misbehave. To prevent the testbox sheriff

-- from repeatedly having to reboot testboxes, the builds gets blacklisted

-- until there is a working build again. This may mean adding an open ended

-- blacklist spec and then updating it with the final revision number once the

-- fix has been committed.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

-- @todo Would be nice if we could replace the text strings below with a set of

-- BuildCategories, or sore it in any other way which would enable us to

-- do a negative join with build category... The way it is specified

-- now, it looks like we have to open a cursor of prospecitve builds and

-- filter then thru this table one by one.

--

-- Any better representation is welcome, but this is low prioirty for

-- now, as it's relatively easy to change this later one.

--

CREATE SEQUENCE BuildBlacklistIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE BuildBlacklist (

--- The blacklist entry id.

-- This stays constant over time.

idBlacklisting INTEGER DEFAULT NEXTVAL('BuildBlacklistIdSeq') NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

uidAuthor INTEGER NOT NULL,

--- The reason for the blacklisting.

-- Non-unique foreign key: FailureReasons(idFailureReason)

idFailureReason INTEGER NOT NULL,

--- Which product.

-- ASSUME that it is okay to limit a blacklisting to a single product.

sProduct text NOT NULL,

--- Which branch.

-- ASSUME that it is okay to limit a blacklisting to a branch.

sBranch text NOT NULL,

--- Build types to include, all matches if NULL.

asTypes text ARRAY DEFAULT NULL,

--- Array of the 'sOs.sCpuArch' to match, all matches if NULL.

-- See KBUILD_OSES in kBuild for a list of standard target OSes, and

-- KBUILD_ARCHES for a list of standard architectures.

--

-- @remarks See marks on 'os-agnostic' and 'noarch' in BuildCategories.

asOsArches text ARRAY DEFAULT NULL,

--- The first subversion tree revision to blacklist.

iFirstRevision INTEGER NOT NULL,

--- The last subversion tree revision to blacklist, no upper limit if NULL.

iLastRevision INTEGER NOT NULL,

PRIMARY KEY (idBlacklisting, tsExpire)

);

--- @table BuildCategories

-- Build categories.

--

-- The purpose of this table is saving space in the Builds table and hopefully

-- speed things up when selecting builds as well (compared to selecting on 4

-- text fields in the much larger Builds table).

--

-- Insert only table, no update, no delete. History is not needed.

--

CREATE SEQUENCE BuildCategoryIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE BuildCategories (

--- The build type identifier.

idBuildCategory INTEGER PRIMARY KEY DEFAULT NEXTVAL('BuildCategoryIdSeq') NOT NULL,

--- Product.

-- The product name. For instance 'VBox' or 'VBoxTestSuite'.

sProduct TEXT NOT NULL,

--- The version control repository name.

sRepository TEXT NOT NULL,

--- The branch name (in the version control system).

sBranch TEXT NOT NULL,

--- The build type.

-- See KBUILD_BLD_TYPES in kBuild for a list of standard build types.

sType TEXT NOT NULL,

--- Array of the 'sOs.sCpuArch' supported by the build.

-- See KBUILD_OSES in kBuild for a list of standard target OSes, and

-- KBUILD_ARCHES for a list of standard architectures.

--

-- @remarks 'os-agnostic' is used if the build doesn't really target any

-- specific OS or if it targets all applicable OSes.

-- 'noarch' is used if the build is architecture independent or if

-- all applicable architectures are handled.

-- Thus, 'os-agnostic.noarch' will run on all build boxes.

--

-- @note The array shall be sorted ascendingly to prevent unnecessary duplicates!

--

asOsArches TEXT ARRAY NOT NULL,

UNIQUE (sProduct, sRepository, sBranch, sType, asOsArches)

);

--- @table Builds

-- The builds table contains builds from the tinderboxes and oaccasionally from

-- developers.

--

-- The tinderbox side could be fed by a batch job enumerating the build output

-- directories every so often, looking for new builds. Or we could query them

-- from the tinderbox database. Yet another alternative is making the

-- tinderbox server or client side software inform us about all new builds.

--

-- The developer builds are entered manually thru the TM web UI. They are used

-- for subjecting new code to some larger scale testing before commiting,

-- enabling, or merging a private branch.

--

-- The builds are being selected from this table by the via the build source

-- specification that SchedGroups.idBuildSrc and

-- SchedGroups.idBuildSrcTestSuite links to.

--

-- @remarks This table stores history. Never update or delete anything. The

-- equivalent of deleting is done by setting the 'tsExpire' field to

-- current_timestamp. To select the currently valid entries use

-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.

--

CREATE SEQUENCE BuildIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE Builds (

--- The build identifier.

-- This remains unchanged

idBuild INTEGER DEFAULT NEXTVAL('BuildIdSeq') NOT NULL,

--- When this build was created or entered into the database.

-- This remains unchanged

tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row starts taking effect (inclusive).

tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- When this row stops being tsEffective (exclusive).

tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,

--- The user id of the one who created/modified this entry.

-- Non-unique foreign key: Users(uid)

-- @note This is NULL if added by a batch job / tinderbox.

uidAuthor INTEGER DEFAULT NULL,

--- The build category.

idBuildCategory INTEGER REFERENCES BuildCategories(idBuildCategory) NOT NULL,

--- The subversion tree revision of the build.

iRevision INTEGER NOT NULL,

--- The product version number (suitable for RTStrVersionCompare).

sVersion TEXT NOT NULL,

--- The link to the tinderbox log of this build.

sLogUrl TEXT,

--- Comma separated list of binaries.

-- The binaries have paths relative to the TESTBOX_PATH_BUILDS or full URLs.

sBinaries TEXT NOT NULL,

--- Set when the binaries gets deleted by the build quota script.

fBinariesDeleted BOOLEAN DEFAULT FALSE NOT NULL,

UNIQUE (idBuild, tsExpire)

);

CREATE INDEX BuildsLookupIdx ON Builds (idBuildCategory, iRevision);

--- @table VcsRevisions

-- This table is for translating build revisions into commit details.

--

-- For graphs and test results, it would be useful to translate revisions into

-- dates and maybe provide commit message and the committer.

--

-- Data is entered exclusively thru one or more batch jobs, so no internal

-- authorship needed. Also, since we're mirroring data from external sources

-- here, the batch job is allowed to update/replace existing records.

--

-- @todo We we could collect more info from the version control systems, if we

-- believe it's useful and can be presented in a reasonable manner.

-- Getting a list of affected files would be simple (requires

-- a separate table with a M:1 relationship to this table), or try

-- associate a commit to a branch.

--

CREATE TABLE VcsRevisions (

--- The version control tree name.

sRepository TEXT NOT NULL,

--- The version control tree revision number.

iRevision INTEGER NOT NULL,

--- When the revision was created (committed).

tsCreated TIMESTAMP WITH TIME ZONE NOT NULL,

--- The name of the committer.

-- @note Not to be confused with uidAuthor and test manager users.

sAuthor TEXT,

--- The commit message.

sMessage TEXT,

UNIQUE (sRepository, iRevision)

);

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--

-- T e s t R e s u l t s

--

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--- @table TestResultStrTab

-- String table for the test results.

--

-- This is a string cache for value names, test names and possible more, that

-- is frequently repated in the test results record for each test run. The

-- purpose is not only to save space, but to make datamining queries faster by

-- giving them integer fields to work on instead of text fields. There may

-- possibly be some benefits on INSERT as well as there are only integer

-- indexes.

--

-- Nothing is ever deleted from this table.

--

-- @note Should use a stored procedure to query/insert a string.

--

CREATE SEQUENCE TestResultStrTabIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestResultStrTab (

--- The ID of this string.

idStr INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultStrTabIdSeq'),

--- The string value.

sValue text NOT NULL,

--- Creation time stamp.

tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL

);

CREATE UNIQUE INDEX TestResultStrTabNameIdx ON TestResultStrTab (sValue);

--- Empty string with ID 0.

INSERT INTO TestResultStrTab (idStr, sValue) VALUES (0, '');

--- @type TestStatus_T

-- The status of a test (set / result).

--

CREATE TYPE TestStatus_T AS ENUM (

-- Initial status:

'running',

-- Final statuses:

'success',

-- Final status: Test didn't fail as such, it was something else.

'skipped',

'bad-testbox',

'aborted',

-- Final status: Test failed.

'failure',

'timed-out',

'rebooted'

);

--- @table TestResults

-- Test results - a recursive bundle of joy!

--

-- A test case will be created when the testdriver calls reporter.testStart and

-- concluded with reporter.testDone. The testdriver (or it subordinates) can

-- use these methods to create nested test results. For IPRT based test cases,

-- RTTestCreate, RTTestInitAndCreate and RTTestSub will both create new test

-- result records, where as RTTestSubDone, RTTestSummaryAndDestroy and

-- RTTestDestroy will conclude records.

--

-- By concluding is meant updating the status. When the test driver reports

-- success, we check it against reported results. (paranoia strikes again!)

--

-- Nothing is ever deleted from this table.

--

-- @note As seen below, several other tables associate data with a

-- test result, and the top most test result is referenced by the

-- test set.

--

CREATE SEQUENCE TestResultIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestResults (

--- The ID of this test result.

idTestResult INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultIdSeq'),

--- The parent test result.

-- This is NULL for the top test result.

idTestResultParent INTEGER REFERENCES TestResults(idTestResult),

--- The testsest this result is a part of.

-- Note! This is a foreign key, but we have to add it after TestSets has

-- been created, see further down.

idTestSet INTEGER NOT NULL,

--- Creation time stamp. This may also be the timestamp of when the test started.

tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- The elapsed time for this test.

-- This is either reported by the directly (with some sanity checking) or

-- calculated (current_timestamp - created_ts).

-- @todo maybe use a nanosecond field here, check with what

tsElapsed interval DEFAULT NULL,

--- The test name.

idStrName INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,

--- The error count.

cErrors INTEGER DEFAULT 0 NOT NULL,

--- The test status.

enmStatus TestStatus_T DEFAULT 'running'::TestStatus_T NOT NULL,

--- Nesting depth.

iNestingDepth smallint NOT NULL CHECK (iNestingDepth >= 0 AND iNestingDepth < 16),

-- Make sure errors and status match up.

CONSTRAINT CheckStatusMatchesErrors

CHECK ( (cErrors > 0 AND enmStatus IN ('running'::TestStatus_T,

'failure'::TestStatus_T, 'timed-out'::TestStatus_T, 'rebooted'::TestStatus_T ))

OR (cErrors = 0 AND enmStatus IN ('running'::TestStatus_T, 'success'::TestStatus_T,

'skipped'::TestStatus_T, 'aborted'::TestStatus_T, 'bad-testbox'::TestStatus_T))

)

);

CREATE INDEX TestResultsSetIdx ON TestResults (idTestSet, idStrName, idTestResult);

CREATE INDEX TestResultsParentIdx ON TestResults (idTestResultParent);

-- The TestResultsNameIdx is for speeding up the result graph & reporting code.

CREATE INDEX TestResultsNameIdx ON TestResults (idStrName, idTestResult, tsCreated);

ALTER TABLE TestResultFailures

ADD CONSTRAINT idTestResultFk FOREIGN KEY (idTestResult) REFERENCES TestResults(idTestResult) MATCH FULL;

--- @table TestResultValues

-- Test result values.

--

-- A testdriver or subordinate may report a test value via

-- reporter.testValue(), while IPRT based test will use RTTestValue and

-- associates.

--

-- This is an insert only table, no deletes, no updates.

--

CREATE SEQUENCE TestResultValueIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestResultValues (

--- The ID of this value.

idTestResultValue INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultValueIdSeq'),

--- The test result it was reported within.

idTestResult INTEGER REFERENCES TestResults(idTestResult) NOT NULL,

--- The test result it was reported within.

-- Note! This is a foreign key, but we have to add it after TestSets has

-- been created, see further down.

idTestSet INTEGER NOT NULL,

--- Creation time stamp.

tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- The name.

idStrName INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,

--- The value.

lValue bigint NOT NULL,

--- The unit.

-- @todo This is currently not defined properly. Will fix/correlate this

-- with the other places we use unit (IPRT/testdriver/VMMDev).

iUnit smallint NOT NULL CHECK (iUnit >= 0 AND iUnit < 1024)

);

CREATE INDEX TestResultValuesIdx ON TestResultValues(idTestResult);

-- The TestResultValuesGraphIdx is for speeding up the result graph & reporting code.

CREATE INDEX TestResultValuesGraphIdx ON TestResultValues(idStrName, tsCreated);

--- @table TestResultFiles

-- Test result files.

--

-- A testdriver or subordinate may report a file by using

-- reporter.addFile() or reporter.addLogFile().

--

-- The files stored here as well as the primary log file will be processed by a

-- batch job and compressed if considered compressable. Thus, TM will look for

-- files with a .gz/.bz2 suffix first and then without a suffix.

--

-- This is an insert only table, no deletes, no updates.

--

CREATE SEQUENCE TestResultFileId

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestResultFiles (

--- The ID of this file.

idTestResultFile INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultFileId'),

--- The test result it was reported within.

idTestResult INTEGER REFERENCES TestResults(idTestResult) NOT NULL,

--- Creation time stamp.

tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- The filename relative to TestSets(sBaseFilename) + '-'.

-- The set of valid filename characters should be very limited so that no

-- file system issues can occure either on the TM side or the user when

-- loading the files. Tests trying to use other characters will fail.

-- Valid character regular expession: '^[a-zA-Z0-9_-(){}#@+,.=]*$'

idStrFile INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,

--- The description.

idStrDescription INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,

--- The kind of file.

-- For instance: 'log/release/vm',

-- 'screenshot/failure',

-- 'screencapture/failure',

-- 'xmllog/somestuff'

idStrKind INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,

--- The mime type for the file.

-- For instance: 'text/plain',

-- 'image/png',

-- 'video/webm',

-- 'text/xml'

idStrMime INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL

);

CREATE INDEX TestResultFilesIdx ON TestResultFiles(idTestResult);

--- @table TestResultMsgs

-- Test result message.

--

-- A testdriver or subordinate may report a message via the sDetails parameter

-- of the reporter.testFailure() method, while IPRT test cases will use

-- RTTestFailed, RTTestPrintf and their friends. For RTTestPrintf, we will

-- ignore the more verbose message levels since these can also be found in one

-- of the logs.

--

-- This is an insert only table, no deletes, no updates.

--

CREATE TYPE TestResultMsgLevel_T AS ENUM (

'failure',

'info'

);

CREATE SEQUENCE TestResultMsgIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestResultMsgs (

--- The ID of this file.

idTestResultMsg INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultMsgIdSeq'),

--- The test result it was reported within.

idTestResult INTEGER REFERENCES TestResults(idTestResult) NOT NULL,

--- Creation time stamp.

tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- The message string.

idStrMsg INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,

--- The message level.

enmLevel TestResultMsgLevel_T NOT NULL

);

CREATE INDEX TestResultMsgsIdx ON TestResultMsgs(idTestResult);

--- @table TestSets

-- Test sets / Test case runs.

--

-- This is where we collect data about test runs.

--

-- @todo Not entirely sure where the 'test set' term came from. Consider

-- finding something more appropriate.

--

CREATE SEQUENCE TestSetIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE TestSets (

--- The ID of this test set.

idTestSet INTEGER PRIMARY KEY 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 REFERENCES BuildCategories(idBuildCategory) 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 REFERENCES TestBoxes(idGenTestBox) NOT NULL,

--- 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 REFERENCES TestCases(idGenTestCase) 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 REFERENCES TestCaseArgs(idGenTestCaseArgs) 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 REFERENCES TestResults(idTestResult) 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 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 REFERENCES TestSets(idTestSet) DEFAULT NULL

);

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

--- The TestSetCreatedDone is for testbox results, graph options and such.

CREATE INDEX TestSetsCreatedDoneIdx ON TestSets (tsCreated, tsDone);

--- For graphs.

CREATE INDEX TestSetsGraphBoxIdx ON TestSets (idTestBox, tsCreated, tsDone, idBuildCategory, idTestCase);

ALTER TABLE TestResults ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;

ALTER TABLE TestResultValues ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--

-- T e s t M a n g e r P e r s i s t e n t S t o r a g e

--

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

--- @type TestBoxState_T

-- TestBox state.

--

-- @todo Consider drawing a state diagram for this.

--

CREATE TYPE TestBoxState_T AS ENUM (

--- Nothing to do.

-- Prev: testing, gang-cleanup, rebooting, upgrading,

-- upgrading-and-rebooting, doing-special-cmd.

-- Next: testing, gang-gathering, rebooting, upgrading,

-- upgrading-and-rebooting, doing-special-cmd.

'idle',

--- Executing a test.

-- Prev: idle

-- Next: idle

'testing',

-- Gang scheduling statuses:

--- The gathering of a gang.

-- Prev: idle

-- Next: gang-gathering-timedout, gang-testing

'gang-gathering',

--- The gathering timed out, the testbox needs to cleanup and move on.

-- Prev: gang-gathering

-- Next: idle

-- This is set on all gathered members by the testbox who triggers the

-- timeout.

'gang-gathering-timedout',

--- The gang scheduling equivalent of 'testing'.

-- Prev: gang-gathering

-- Next: gang-cleanup

'gang-testing',

--- Waiting for the other gang members to stop testing so that cleanups

-- can be performed and members safely rescheduled.

-- Prev: gang-testing

-- Next: idle

--

-- There are two resource clean up issues being targeted here:

-- 1. Global resources will be allocated by the leader when he enters the

-- 'gang-gathering' state. If the leader quits and frees the resource

-- while someone is still using it, bad things will happen. Imagine a

-- global resource without any access checks and relies exclusivly on

-- the TM doing its job.

-- 2. TestBox resource accessed by other gang members may also be used in

-- other tests. Should a gang member leave early and embark on a

-- testcase using the same resources, bad things will happen. Example:

-- Live migration. One partner leaves early because it detected some

-- fatal failure, the other one is still trying to connect to him.

-- The testbox is scheduled again on the same live migration testcase,

-- only with different arguments (VM config), it will try migrate using

-- the same TCP ports. Confusion ensues.

--

-- To figure out whether to remain in this status because someone is

-- still testing:

-- SELECT COUNT(*) FROM TestBoxStatuses, TestSets

-- WHERE TestSets.idTestSetGangLeader = :idGangLeader

-- AND TestSets.idTestBox = TestBoxStatuses.idTestBox

-- AND TestSets.idTestSet = TestBoxStatuses.idTestSet

-- AND TestBoxStatuses.enmState = 'gang-testing'::TestBoxState_T;

'gang-cleanup',

-- Command related statuses (all command status changes comes from 'idle'

-- and goes back to 'idle'):

'rebooting',

'upgrading',

'upgrading-and-rebooting',

'doing-special-cmd'

);

--- @table TestBoxStatuses

-- Testbox status table.

--

-- History is not planned on this table.

--

CREATE TABLE TestBoxStatuses (

--- The testbox.

idTestBox INTEGER PRIMARY KEY NOT NULL,

--- The testbox generation ID.

idGenTestBox INTEGER REFERENCES TestBoxes(idGenTestBox) NOT NULL,

--- When this status was last updated.

-- This is updated everytime the testbox talks to the test manager, thus it

-- can easily be used to find testboxes which has stopped responding.

--

-- This is used for timeout calculation during gang-gathering, so in that

-- scenario it won't be updated until the gang is gathered or we time out.

tsUpdated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,

--- The current state.

enmState TestBoxState_T DEFAULT 'idle'::TestBoxState_T NOT NULL,

--- Reference to the test set

idTestSet INTEGER REFERENCES TestSets(idTestSet)

);

--- @table GlobalResourceStatuses

-- Global resource status, tracks which test set resources are allocated by.

--

-- History is not planned on this table.

--

CREATE TABLE GlobalResourceStatuses (

--- The resource ID.

-- Non-unique foreign key: GlobalResources(idGlobalRsrc).

idGlobalRsrc INTEGER PRIMARY KEY NOT NULL,

--- The resource owner.

-- @note This is going thru testboxstatus to be able to use the testbox ID

-- as a foreign key.

idTestBox INTEGER REFERENCES TestBoxStatuses(idTestBox) NOT NULL,

--- When the allocation took place.

tsAllocated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL

);

--- @table SchedQueues

-- Scheduler queue.

--

-- The queues are currently associated with a scheduling group, it could

-- alternative be changed to hook on to a testbox instead. It depends on what

-- kind of scheduling method we prefer. The former method aims at test case

-- thruput, making sacrifices in the hardware distribution area. The latter is

-- more like the old buildbox style testing, making sure that each test case is

-- executed on each testbox.

--

-- When there are configuration changes, TM will regenerate the scheduling

-- queue for the affected scheduling groups. We do not concern ourselves with

-- trying to continue at the approximately same queue position, we simply take

-- it from the top.

--

-- When a testbox ask for work, we will open a cursor on the queue and take the

-- first test in the queue that can be executed on that testbox. The test will

-- be moved to the end of the queue (getting a new item_id).

--

-- If a test is manually changed to the head of the queue, the item will get a

-- item_id which is 1 lower than the head of the queue. Unless someone does

-- this a couple of billion times, we shouldn't have any trouble running out of

-- number space. :-)

--

-- Manually moving a test to the end of the queue is easy, just get a new

-- 'item_id'.

--

-- History is not planned on this table.

--

CREATE SEQUENCE SchedQueueItemIdSeq

START 1

INCREMENT BY 1

NO MAXVALUE

NO MINVALUE

CACHE 1;

CREATE TABLE SchedQueues (

--- The scheduling queue (one queue per scheduling group).

-- Non-unique foreign key: SchedGroups(idSchedGroup)

idSchedGroup INTEGER NOT NULL,

--- The scheduler queue entry ID.

-- Lower numbers means early queue position.

idItem INTEGER DEFAULT NEXTVAL('SchedQueueItemIdSeq') NOT NULL,

--- The queue offset.

-- This is used for repositining the queue when recreating it. It can also

-- be used to figure out how jumbled the queue gets after real life has had

-- it's effect on it.

offQueue INTEGER NOT NULL,

--- The test case argument variation to execute.

idGenTestCaseArgs INTEGER REFERENCES TestCaseArgs(idGenTestCaseArgs) NOT NULL,

--- The relevant testgroup.

-- Non-unique foreign key: TestGroups(idTestGroup).

idTestGroup INTEGER NOT NULL,

--- Aggregated test group dependencies (NULL if none).

-- Non-unique foreign key: TestGroups(idTestGroup).

-- See also comments on SchedGroupMembers.idTestGroupPreReq.

aidTestGroupPreReqs INTEGER ARRAY DEFAULT NULL,

--- The scheduling time constraints (see SchedGroupMembers.bmHourlySchedule).

bmHourlySchedule bit(168) DEFAULT NULL,

--- When the queue entry was created and for which config is valid.

-- This is the timestamp that should be used when reading config info.

tsConfig TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,

--- When this status was last scheduled.

-- This is set to current_timestamp when moving the entry to the end of the

-- queue. It's initial value is unix-epoch. Not entirely sure if it's

-- useful beyond introspection and non-unique foreign key hacking.

tsLastScheduled TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'epoch' NOT NULL,

--- This is used in gang scheduling.

idTestSetGangLeader INTEGER REFERENCES TestSets(idTestSet) DEFAULT NULL UNIQUE,

--- The number of gang members still missing.

--

-- This saves calculating the number of missing members via selects like:

-- SELECT COUNT(*) FROM TestSets WHERE idTestSetGangLeader = :idGang;

-- and

-- SELECT cGangMembers FROM TestCaseArgs WHERE idGenTestCaseArgs = :idTest;

-- to figure out whether to remain in 'gather-gang'::TestBoxState_T.

--

cMissingGangMembers smallint DEFAULT 1 NOT NULL,

PRIMARY KEY (idSchedGroup, idItem)

);