-- $Id: TestManagerDatabaseInit.pgsql 61255 2016-05-28 03:52:35Z vboxsync $ --- @file -- VBox Test Manager Database Creation script. -- -- -- Copyright (C) 2012-2015 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 (sLoginName, tsExpire DESC); --- @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 > -- AND tsEffective <= ; -- -- 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), --- Optional variation sub-name. sSubName TEXT DEFAULT NULL, --- 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 DESC, tsEffective ASC); --- @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 DESC, tsEffective ASC); --- @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 DESC); CREATE INDEX TestBoxesExpireEffectiveIdx ON TestBoxes (tsExpire DESC, tsEffective ASC); -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 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 testsest this result is a part of. -- This is mainly an aid for bypassing the enormous TestResults table. -- Note! This is a foreign key, but we have to add it after TestSets has -- been created, see further down. idTestSet 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) ); CREATE INDEX TestResultFailureIdx ON TestResultFailures (idTestSet, tsExpire DESC, tsEffective ASC); CREATE INDEX TestResultFailureIdx2 ON TestResultFailures (idTestResult, tsExpire DESC, tsEffective ASC); CREATE INDEX TestResultFailureIdx3 ON TestResultFailures (idFailureReason, idTestResult, tsExpire DESC, tsEffective ASC); -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 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) ); CREATE INDEX BuildBlacklistIdx ON BuildBlacklist (iLastRevision DESC, iFirstRevision ASC, sProduct, sBranch, tsExpire DESC, tsEffective ASC); --- @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)) ), -- The following is for the TestResultFailures foreign key. -- Note! This was added with the name TestResults_idTestResult_idTestSet_key in the tmdb-r16 update script. UNIQUE (idTestResult, idTestSet) ); CREATE INDEX TestResultsSetIdx ON TestResults (idTestSet, idStrName, idTestResult); CREATE INDEX TestResultsParentIdx ON TestResults (idTestResultParent); -- The TestResultsNameIdx and TestResultsNameIdx2 are for speeding up the result graph & reporting code. CREATE INDEX TestResultsNameIdx ON TestResults (idStrName, tsCreated DESC); CREATE INDEX TestResultsNameIdx2 ON TestResults (idTestResult, idStrName); ALTER TABLE TestResultFailures ADD CONSTRAINT TestResultFailures_idTestResult_idTestSet_fkey FOREIGN KEY (idTestResult, idTestSet) REFERENCES TestResults(idTestResult, idTestSet) 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 TestSetsDoneCreatedBuildCatIdx is for testbox results, graph options and such. CREATE INDEX TestSetsDoneCreatedBuildCatIdx ON TestSets (tsDone DESC NULLS FIRST, tsCreated ASC, idBuildCategory); --- For graphs. CREATE INDEX TestSetsGraphBoxIdx ON TestSets (idTestBox, tsCreated DESC, tsDone ASC NULLS LAST, 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; ALTER TABLE TestResultFailures ADD CONSTRAINT idTestSetFk 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) ); CREATE INDEX SchedQueuesItemIdx ON SchedQueues(idItem); CREATE INDEX SchedQueuesSchedGroupIdx ON SchedQueues(idSchedGroup);