-- $Id: TestManagerDatabaseInit.pgsql 69111 2017-10-17 14:26:02Z vboxsync $ --- @file -- VBox Test Manager Database Creation script. -- -- -- Copyright (C) 2012-2017 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. :-) -- -- -- D R O P D A T A B A S E t e s t m a n a g e r - - you do this now. \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 abandoned testcase (scenario #9), log which -- testbox abandoned 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, --- Read access only. fReadOnly BOOLEAN NOT NULL DEFAULT FALSE, 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, -- Comment regarding a change or something. sComment 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, -- Comment regarding a change or something. sComment TEXT DEFAULT NULL, 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, TestBoxesInSchedGroups.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, -- Comment regarding a change or something. sComment TEXT 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 of the test group. -- Higher number causes the test case to be run more frequently. -- @sa TestGroupMembers.iSchedPriority, TestBoxesInSchedGroups.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) ); --- @table TestBoxStrTab -- String table for the test boxes. -- -- This is a string cache for all string members in TestBoxes except the name. -- The rational is to avoid duplicating large strings like sReport when the -- testbox reports a new cMbScratch value or the box when the test sheriff -- sends a reboot command or similar. -- -- At the time this table was introduced, we had 400558 TestBoxes rows, where -- the SUM(LENGTH(sReport)) was 993MB. There were really just 1066 distinct -- sReport values, with a total length of 0x3 MB. -- -- Nothing is ever deleted from this table. -- -- @note Should use a stored procedure to query/insert a string. -- -- -- TestBox stats prior to conversion: -- SELECT COUNT(*) FROM TestBoxes: 400558 rows -- SELECT pg_total_relation_size('TestBoxes'): 740794368 bytes (706 MB) -- Average row cost: 740794368 / 400558 = 1849 bytes/row -- -- After conversion: -- SELECT COUNT(*) FROM TestBoxes: 400558 rows -- SELECT pg_total_relation_size('TestBoxes'): 144375808 bytes (138 MB) -- SELECT COUNT(idStr) FROM TestBoxStrTab: 1292 rows -- SELECT pg_total_relation_size('TestBoxStrTab'): 5709824 bytes (5.5 MB) -- (144375808 + 5709824) / 740794368 = 20 % -- Average row cost boxes: 144375808 / 400558 = 360 bytes/row -- Average row cost strings: 5709824 / 1292 = 4420 bytes/row -- CREATE SEQUENCE TestBoxStrTabIdSeq START 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; CREATE TABLE TestBoxStrTab ( --- The ID of this string. idStr INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestBoxStrTabIdSeq'), --- The string value. sValue text NOT NULL, --- Creation time stamp. tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL ); -- Note! Must use hash index as the sReport strings are too long for regular indexing. CREATE INDEX TestBoxStrTabNameIdx ON TestBoxStrTab USING hash (sValue); --- Empty string with ID 0. INSERT INTO TestBoxStrTab (idStr, sValue) VALUES (0, ''); --- @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. idStrDescription INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT 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), --- Change comment or similar. idStrComment INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL, --- @name Scheduling properties (reported by testbox script). -- @{ --- Same abbrieviations as kBuild, see KBUILD_OSES. idStrOs INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL, --- Informational, no fixed format. idStrOsVersion INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL, --- Same as CPUID reports (GenuineIntel, AuthenticAMD, CentaurHauls, ...). idStrCpuVendor INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL, --- Same as kBuild - x86, amd64, ... See KBUILD_ARCHES. idStrCpuArch INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL, --- The CPU name if available. idStrCpuName INTEGER REFERENCES TestBoxStrTab(idStr) 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, --- Set if the test box does raw-mode tests. fRawMode 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. idStrReport INTEGER REFERENCES TestBoxStrTab(idStr) 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); -- -- Create a view for TestBoxes where the strings are resolved. -- CREATE VIEW TestBoxesWithStrings AS SELECT TestBoxes.*, Str1.sValue AS sDescription, Str2.sValue AS sComment, Str3.sValue AS sOs, Str4.sValue AS sOsVersion, Str5.sValue AS sCpuVendor, Str6.sValue AS sCpuArch, Str7.sValue AS sCpuName, Str8.sValue AS sReport FROM TestBoxes LEFT OUTER JOIN TestBoxStrTab Str1 ON idStrDescription = Str1.idStr LEFT OUTER JOIN TestBoxStrTab Str2 ON idStrComment = Str2.idStr LEFT OUTER JOIN TestBoxStrTab Str3 ON idStrOs = Str3.idStr LEFT OUTER JOIN TestBoxStrTab Str4 ON idStrOsVersion = Str4.idStr LEFT OUTER JOIN TestBoxStrTab Str5 ON idStrCpuVendor = Str5.idStr LEFT OUTER JOIN TestBoxStrTab Str6 ON idStrCpuArch = Str6.idStr LEFT OUTER JOIN TestBoxStrTab Str7 ON idStrCpuName = Str7.idStr LEFT OUTER JOIN TestBoxStrTab Str8 ON idStrReport = Str8.idStr; --- @table TestBoxesInSchedGroups -- N:M relationship between test boxes and scheduling groups. -- -- We associate a priority with this relationship. -- -- @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 TestBoxesInSchedGroups ( --- TestBox ID. -- Non-unique foreign key: TestBoxes(idTestBox). idTestBox INTEGER NOT NULL, --- Scheduling ID. -- Non-unique foreign key: SchedGroups(idSchedGroup). idSchedGroup 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 of the scheduling group for the test box. -- Higher number causes the scheduling group to be serviced more frequently. -- @sa TestGroupMembers.iSchedPriority, SchedGroups.iSchedPriority iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL, PRIMARY KEY (idTestBox, idSchedGroup, 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 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 test set 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 set this value is a part of (for avoiding joining thru TestResults). -- 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); -- The TestResultValuesLogIdx is for speeding up the log viewer. CREATE INDEX TestResultValuesLogIdx ON TestResultValues(idTestSet, 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, --- The test set this file is a part of (for avoiding joining thru TestResults). -- 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 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); CREATE INDEX TestResultFilesIdx2 ON TestResultFiles(idTestSet, tsCreated DESC); --- @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, --- The test set this file is a part of (for avoiding joining thru TestResults). -- 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 message string. idStrMsg INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL, --- The message level. enmLevel TestResultMsgLevel_T NOT NULL ); CREATE INDEX TestResultMsgsIdx ON TestResultMsgs(idTestResult); CREATE INDEX TestResultMsgsIdx2 ON TestResultMsgs(idTestSet, tsCreated DESC); --- @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 scheduling group ID the test was scheduled thru (valid: tsStarted). -- Non-unique foreign key: SchedGroups(idSchedGroup) idSchedGroup INTEGER NOT NULL, --- The testgroup (valid: tsConfig). -- Non-unique foreign key: TestBoxes(idTestGroup) -- Note! This also gives the member ship entry, since a testcase can only -- have one membership per test group. idTestGroup INTEGER NOT NULL, --- The exact test case config we executed in this test run. idGenTestCase INTEGER 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 TestResultFiles ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL; ALTER TABLE TestResultMsgs ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL; ALTER TABLE TestResultFailures ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 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), --- Interal work item number. -- This is used to pick and prioritize between multiple scheduling groups. iWorkItem INTEGER DEFAULT 0 NOT NULL ); --- @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);