VirtualBox

source: vbox/trunk/src/VBox/ValidationKit/testmanager/db/TestManagerDatabaseInit.pgsql@ 86996

Last change on this file since 86996 was 84884, checked in by vboxsync, 4 years ago

testmanager/db: Add an index on VcsRevisions(tsCreated). bugref:9778

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
File size: 83.1 KB
Line 
1-- $Id: TestManagerDatabaseInit.pgsql 84884 2020-06-20 21:53:12Z vboxsync $
2--- @file
3-- VBox Test Manager Database Creation script.
4--
5
6--
7-- Copyright (C) 2012-2020 Oracle Corporation
8--
9-- This file is part of VirtualBox Open Source Edition (OSE), as
10-- available from http://www.virtualbox.org. This file is free software;
11-- you can redistribute it and/or modify it under the terms of the GNU
12-- General Public License (GPL) as published by the Free Software
13-- Foundation, in version 2 as it comes in the "COPYING" file of the
14-- VirtualBox OSE distribution. VirtualBox OSE is distributed in the
15-- hope that it will be useful, but WITHOUT ANY WARRANTY of any kind.
16--
17-- The contents of this file may alternatively be used under the terms
18-- of the Common Development and Distribution License Version 1.0
19-- (CDDL) only, as it comes in the "COPYING.CDDL" file of the
20-- VirtualBox OSE distribution, in which case the provisions of the
21-- CDDL are applicable instead of those of the GPL.
22--
23-- You may elect to license modified versions of this file under the
24-- terms and conditions of either the GPL or the CDDL or both.
25--
26
27--
28-- Declaimer:
29--
30-- The guys working on this design are not database experts, web
31-- programming experts or similar, rather we are low level guys
32-- who's main job is x86 & AMD64 virtualization. So, please don't
33-- be too hard on us. :-)
34--
35--
36
37
38-- 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.
39\set ON_ERROR_STOP 1
40CREATE DATABASE testmanager;
41\connect testmanager;
42
43
44-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
45--
46-- S y s t e m
47--
48-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
49
50---
51-- Log table for a few important events.
52--
53-- Currently, two events are planned to be logged:
54-- - Sign on of an unknown testbox, including the IP and System UUID.
55-- This will be restricted to one entry per 24h or something like that:
56-- SELECT COUNT(*)
57-- FROM SystemLog
58-- WHERE tsCreated >= (current_timestamp - interval '24 hours')
59-- AND sEvent = 'TBoxUnkn'
60-- AND sLogText = :sNewLogText;
61-- - When cleaning up an abandoned testcase (scenario #9), log which
62-- testbox abandoned which testset.
63--
64-- The Web UI will have some way of displaying the log.
65--
66-- A batch job should regularly clean out old log messages, like for instance
67-- > 64 days.
68--
69CREATE TABLE SystemLog (
70 --- When this was logged.
71 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
72 --- The event type.
73 -- This is a 8 character string identifier so that we don't need to change
74 -- some enum type everytime we introduce a new event type.
75 sEvent CHAR(8) NOT NULL,
76 --- The log text.
77 sLogText text NOT NULL,
78
79 PRIMARY KEY (tsCreated, sEvent)
80);
81
82
83-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
84--
85-- C o n f i g u r a t i o n
86--
87-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
88
89--- @table Users
90-- Test manager users.
91--
92-- This is mainly for doing simple access checks before permitting access to
93-- the test manager. This needs to be coordinated with
94-- apache/ldap/Oracle-Single-Sign-On.
95--
96-- The main purpose, though, is for tracing who changed the test config and
97-- analysis data.
98--
99-- @remarks This table stores history. Never update or delete anything. The
100-- equivalent of deleting is done by setting the 'tsExpire' field to
101-- current_timestamp.
102--
103CREATE SEQUENCE UserIdSeq
104 START 1
105 INCREMENT BY 1
106 NO MAXVALUE
107 NO MINVALUE
108 CACHE 1;
109CREATE TABLE Users (
110 --- The user id.
111 uid INTEGER DEFAULT NEXTVAL('UserIdSeq') NOT NULL,
112 --- When this row starts taking effect (inclusive).
113 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
114 --- When this row stops being tsEffective (exclusive).
115 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
116 --- The user id of the one who created/modified this entry.
117 -- Non-unique foreign key: Users(uid)
118 uidAuthor INTEGER DEFAULT NULL,
119 --- User name.
120 sUsername text NOT NULL,
121 --- The email address of the user.
122 sEmail text NOT NULL,
123 --- The full name.
124 sFullName text NOT NULL,
125 --- The login name used by apache.
126 sLoginName text NOT NULL,
127 --- Read access only.
128 fReadOnly BOOLEAN NOT NULL DEFAULT FALSE,
129
130 PRIMARY KEY (uid, tsExpire)
131);
132CREATE INDEX UsersLoginNameIdx ON Users (sLoginName, tsExpire DESC);
133
134
135--- @table GlobalResources
136-- Global resource configuration.
137--
138-- For example an iSCSI target.
139--
140-- @remarks This table stores history. Never update or delete anything. The
141-- equivalent of deleting is done by setting the 'tsExpire' field to
142-- current_timestamp.
143--
144CREATE SEQUENCE GlobalResourceIdSeq
145 START 1
146 INCREMENT BY 1
147 NO MAXVALUE
148 NO MINVALUE
149 CACHE 1;
150CREATE TABLE GlobalResources (
151 --- The global resource ID.
152 -- This stays the same thru updates.
153 idGlobalRsrc INTEGER DEFAULT NEXTVAL('GlobalResourceIdSeq') NOT NULL,
154 --- When this row starts taking effect (inclusive).
155 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
156 --- When this row stops being tsEffective (exclusive).
157 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
158 --- The user id of the one who created/modified this entry.
159 -- Non-unique foreign key: Users(uid)
160 uidAuthor INTEGER NOT NULL,
161 --- The name of the resource.
162 sName text NOT NULL,
163 --- Optional resource description.
164 sDescription text,
165 --- Indicates whether this resource is currently enabled (online).
166 fEnabled boolean DEFAULT FALSE NOT NULL,
167
168 PRIMARY KEY (idGlobalRsrc, tsExpire)
169);
170
171
172--- @table BuildSources
173-- Build sources.
174--
175-- This is used by a scheduling group to select builds and the default
176-- Validation Kit from the Builds table.
177--
178-- @remarks This table stores history. Never update or delete anything. The
179-- equivalent of deleting is done by setting the 'tsExpire' field to
180-- current_timestamp.
181--
182-- @todo Any better way of representing this so we could more easily
183-- join/whatever when searching for builds?
184--
185CREATE SEQUENCE BuildSourceIdSeq
186 START 1
187 INCREMENT BY 1
188 NO MAXVALUE
189 NO MINVALUE
190 CACHE 1;
191CREATE TABLE BuildSources (
192 --- The build source identifier.
193 -- This stays constant over time.
194 idBuildSrc INTEGER DEFAULT NEXTVAL('BuildSourceIdSeq') NOT NULL,
195 --- When this row starts taking effect (inclusive).
196 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
197 --- When this row stops being tsEffective (exclusive).
198 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
199 --- The user id of the one who created/modified this entry.
200 -- Non-unique foreign key: Users(uid)
201 uidAuthor INTEGER NOT NULL,
202
203 --- The name of the build source.
204 sName TEXT NOT NULL,
205 --- Description.
206 sDescription TEXT DEFAULT NULL,
207
208 --- Which product.
209 -- ASSUME that it is okay to limit a build source to a single product.
210 sProduct text NOT NULL,
211 --- Which branch.
212 -- ASSUME that it is okay to limit a build source to a branch.
213 sBranch text NOT NULL,
214
215 --- Build types to include, all matches if NULL.
216 -- @todo Weighting the types would be nice in a later version.
217 asTypes text ARRAY DEFAULT NULL,
218 --- Array of the 'sOs.sCpuArch' to match, all matches if NULL.
219 -- See KBUILD_OSES in kBuild for a list of standard target OSes, and
220 -- KBUILD_ARCHES for a list of standard architectures.
221 --
222 -- @remarks See marks on 'os-agnostic' and 'noarch' in BuildCategories.
223 asOsArches text ARRAY DEFAULT NULL,
224
225 --- The first subversion tree revision to match, no lower limit if NULL.
226 iFirstRevision INTEGER DEFAULT NULL,
227 --- The last subversion tree revision to match, no upper limit if NULL.
228 iLastRevision INTEGER DEFAULT NULL,
229
230 --- The maximum age of the builds in seconds, unlimited if NULL.
231 cSecMaxAge INTEGER DEFAULT NULL,
232
233 PRIMARY KEY (idBuildSrc, tsExpire)
234);
235
236
237--- @table TestCases
238-- Test case configuration.
239--
240-- @remarks This table stores history. Never update or delete anything. The
241-- equivalent of deleting is done by setting the 'tsExpire' field to
242-- current_timestamp.
243--
244CREATE SEQUENCE TestCaseIdSeq
245 START 1
246 INCREMENT BY 1
247 NO MAXVALUE
248 NO MINVALUE
249 CACHE 1;
250CREATE SEQUENCE TestCaseGenIdSeq
251 START 1
252 INCREMENT BY 1
253 NO MAXVALUE
254 NO MINVALUE
255 CACHE 1;
256CREATE TABLE TestCases (
257 --- The fixed test case ID.
258 -- This is assigned when the test case is created and will never change.
259 idTestCase INTEGER DEFAULT NEXTVAL('TestCaseIdSeq') NOT NULL,
260 --- When this row starts taking effect (inclusive).
261 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
262 --- When this row stops being tsEffective (exclusive).
263 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
264 --- The user id of the one who created/modified this entry.
265 -- Non-unique foreign key: Users(uid)
266 uidAuthor INTEGER NOT NULL,
267 --- Generation ID for this row, a truly unique identifier.
268 -- This is primarily for referencing by TestSets.
269 idGenTestCase INTEGER UNIQUE DEFAULT NEXTVAL('TestCaseGenIdSeq') NOT NULL,
270
271 --- The name of the test case.
272 sName TEXT NOT NULL,
273 --- Optional test case description.
274 sDescription TEXT DEFAULT NULL,
275 --- Indicates whether this test case is currently enabled.
276 fEnabled BOOLEAN DEFAULT FALSE NOT NULL,
277 --- Default test case timeout given in seconds.
278 cSecTimeout INTEGER NOT NULL CHECK (cSecTimeout > 0),
279 --- Default TestBox requirement expression (python boolean expression).
280 -- All the scheduler properties are available for use with the same names
281 -- as in that table.
282 -- If NULL everything matches.
283 sTestBoxReqExpr TEXT DEFAULT NULL,
284 --- Default build requirement expression (python boolean expression).
285 -- The following build properties are available: sProduct, sBranch,
286 -- sType, asOsArches, sVersion, iRevision, uidAuthor and idBuild.
287 -- If NULL everything matches.
288 sBuildReqExpr TEXT DEFAULT NULL,
289
290 --- The base command.
291 -- String suitable for executing in bourne shell with space as separator
292 -- (IFS). References to @BUILD_BINARIES@ will be replaced WITH the content
293 -- of the Builds(sBinaries) field.
294 sBaseCmd TEXT NOT NULL,
295
296 --- Comma separated list of test suite zips (or tars) that the testbox will
297 -- need to download and expand prior to testing.
298 -- If NULL the current test suite of the scheduling group will be used (the
299 -- scheduling group will have an optional test suite build queue associated
300 -- with it). The current test suite can also be referenced by
301 -- @VALIDATIONKIT_ZIP@ in case more downloads are required. Files may also be
302 -- uploaded to the test manager download area, in which case the
303 -- @DOWNLOAD_BASE_URL@ prefix can be used to refer to this area.
304 sTestSuiteZips TEXT DEFAULT NULL,
305
306 -- Comment regarding a change or something.
307 sComment TEXT DEFAULT NULL,
308
309 PRIMARY KEY (idTestCase, tsExpire)
310);
311
312
313--- @table TestCaseArgs
314-- Test case argument list variations.
315--
316-- For example, we have a test case that does a set of tests on a virtual
317-- machine. To get better code/feature coverage of this testcase we wish to
318-- run it with different guest hardware configuration. The test case may do
319-- the same stuff, but the guest OS as well as the VMM may react differently to
320-- the hardware configurations and uncover issues in the VMM, device emulation
321-- or other places.
322--
323-- Typical hardware variations are:
324-- - guest memory size (RAM),
325-- - guest video memory size (VRAM),
326-- - virtual CPUs / cores / threads,
327-- - virtual chipset
328-- - virtual network interface card (NIC)
329-- - USB 1.1, USB 2.0, no USB
330--
331-- The TM web UI will help the user create a reasonable set of permutations
332-- of these parameters, the user specifies a maximum and the TM uses certain
333-- rules together with random selection to generate the desired number. The
334-- UI will also help suggest fitting testbox requirements according to the
335-- RAM/VRAM sizes and the virtual CPU counts. The user may then make
336-- adjustments to the suggestions before commit them.
337--
338-- Alternatively, the user may also enter all the permutations without any
339-- help from the UI.
340--
341-- Note! All test cases has at least one entry in this table, even if it is
342-- empty, because testbox requirements are specified thru this.
343--
344-- Querying the valid parameter lists for a testase this way:
345-- SELECT * ... WHERE idTestCase = TestCases.idTestCase
346-- AND tsExpire > <when>
347-- AND tsEffective <= <when>;
348--
349-- Querying the valid parameter list for the latest generation can be
350-- simplified by just checking tsExpire date:
351-- SELECT * ... WHERE idTestCase = TestCases.idTestCase
352-- AND tsExpire == TIMESTAMP WITH TIME ZONE 'infinity';
353--
354-- @remarks This table stores history. Never update or delete anything. The
355-- equivalent of deleting is done by setting the 'tsExpire' field to
356-- current_timestamp.
357--
358CREATE SEQUENCE TestCaseArgsIdSeq
359 START 1
360 INCREMENT BY 1
361 NO MAXVALUE
362 NO MINVALUE
363 CACHE 1;
364CREATE SEQUENCE TestCaseArgsGenIdSeq
365 START 1
366 INCREMENT BY 1
367 NO MAXVALUE
368 NO MINVALUE
369 CACHE 1;
370CREATE TABLE TestCaseArgs (
371 --- The test case ID.
372 -- Non-unique foreign key: TestCases(idTestCase).
373 idTestCase INTEGER NOT NULL,
374 --- The testcase argument variation ID (fixed).
375 -- This is primarily for TestGroupMembers.aidTestCaseArgs.
376 idTestCaseArgs INTEGER DEFAULT NEXTVAL('TestCaseArgsIdSeq') NOT NULL,
377 --- When this row starts taking effect (inclusive).
378 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
379 --- When this row stops being tsEffective (exclusive).
380 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
381 --- The user id of the one who created/modified this entry.
382 -- Non-unique foreign key: Users(uid)
383 uidAuthor INTEGER NOT NULL,
384 --- Generation ID for this row.
385 -- This is primarily for efficient referencing by TestSets and SchedQueues.
386 idGenTestCaseArgs INTEGER UNIQUE DEFAULT NEXTVAL('TestCaseArgsGenIdSeq') NOT NULL,
387
388 --- The additional arguments.
389 -- String suitable for bourne shell style argument parsing with space as
390 -- separator (IFS). References to @BUILD_BINARIES@ will be replaced with
391 -- the content of the Builds(sBinaries) field.
392 sArgs TEXT NOT NULL,
393 --- Optional test case timeout given in seconds.
394 -- If NULL, the TestCases.cSecTimeout field is used instead.
395 cSecTimeout INTEGER DEFAULT NULL CHECK (cSecTimeout IS NULL OR cSecTimeout > 0),
396 --- Additional TestBox requirement expression (python boolean expression).
397 -- All the scheduler properties are available for use with the same names
398 -- as in that table. This is checked after first checking the requirements
399 -- in the TestCases.sTestBoxReqExpr field.
400 sTestBoxReqExpr TEXT DEFAULT NULL,
401 --- Additional build requirement expression (python boolean expression).
402 -- The following build properties are available: sProduct, sBranch,
403 -- sType, asOsArches, sVersion, iRevision, uidAuthor and idBuild. This is
404 -- checked after first checking the requirements in the
405 -- TestCases.sBuildReqExpr field.
406 sBuildReqExpr TEXT DEFAULT NULL,
407 --- Number of testboxes required (gang scheduling).
408 cGangMembers SMALLINT DEFAULT 1 NOT NULL CHECK (cGangMembers > 0 AND cGangMembers < 1024),
409 --- Optional variation sub-name.
410 sSubName TEXT DEFAULT NULL,
411
412 --- The arguments are part of the primary key for several reasons.
413 -- No duplicate argument lists (makes no sense - if you want to prioritize
414 -- argument lists, we add that explicitly). This may hopefully enable us
415 -- to more easily check coverage later on, even when the test case is
416 -- reconfigured with more/less permutations.
417 PRIMARY KEY (idTestCase, tsExpire, sArgs)
418);
419CREATE INDEX TestCaseArgsLookupIdx ON TestCaseArgs (idTestCase, tsExpire DESC, tsEffective ASC);
420
421
422--- @table TestCaseDeps
423-- Test case dependencies (N:M)
424--
425-- This effect build selection. The build must have passed all runs of the
426-- given prerequisite testcase (idTestCasePreReq) and executed at a minimum one
427-- argument list variation.
428--
429-- This should also affect scheduling order, if possible at least one
430-- prerequisite testcase variation should be place before the specific testcase
431-- in the scheduling queue.
432--
433-- @remarks This table stores history. Never update or delete anything. The
434-- equivalent of deleting is done by setting the 'tsExpire' field to
435-- current_timestamp. To select the currently valid entries use
436-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
437--
438CREATE TABLE TestCaseDeps (
439 --- The test case that depends on someone.
440 -- Non-unique foreign key: TestCases(idTestCase).
441 idTestCase INTEGER NOT NULL,
442 --- The prerequisite test case ID.
443 -- Non-unique foreign key: TestCases(idTestCase).
444 idTestCasePreReq INTEGER NOT NULL,
445 --- When this row starts taking effect (inclusive).
446 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
447 --- When this row stops being tsEffective (exclusive).
448 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
449 --- The user id of the one who created/modified this entry.
450 -- Non-unique foreign key: Users(uid)
451 uidAuthor INTEGER NOT NULL,
452
453 PRIMARY KEY (idTestCase, idTestCasePreReq, tsExpire)
454);
455
456
457--- @table TestCaseGlobalRsrcDeps
458-- Test case dependencies on global resources (N:M)
459--
460-- @remarks This table stores history. Never update or delete anything. The
461-- equivalent of deleting is done by setting the 'tsExpire' field to
462-- current_timestamp. To select the currently valid entries use
463-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
464--
465CREATE TABLE TestCaseGlobalRsrcDeps (
466 --- The test case that depends on someone.
467 -- Non-unique foreign key: TestCases(idTestCase).
468 idTestCase INTEGER NOT NULL,
469 --- The prerequisite resource ID.
470 -- Non-unique foreign key: GlobalResources(idGlobalRsrc).
471 idGlobalRsrc INTEGER NOT NULL,
472 --- When this row starts taking effect (inclusive).
473 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
474 --- When this row stops being tsEffective (exclusive).
475 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
476 --- The user id of the one who created/modified this entry.
477 -- Non-unique foreign key: Users(uid)
478 uidAuthor INTEGER NOT NULL,
479
480 PRIMARY KEY (idTestCase, idGlobalRsrc, tsExpire)
481);
482
483
484--- @table TestGroups
485-- Test Group - A collection of test cases.
486--
487-- This is for simplifying test configuration by working with a few groups
488-- instead of a herd of individual testcases. It may also be used for creating
489-- test suites for certain areas (like guest additions) or tasks (like
490-- performance measurements).
491--
492-- A test case can be member of any number of test groups.
493--
494-- @remarks This table stores history. Never update or delete anything. The
495-- equivalent of deleting is done by setting the 'tsExpire' field to
496-- current_timestamp. To select the currently valid entries use
497-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
498--
499CREATE SEQUENCE TestGroupIdSeq
500 START 1
501 INCREMENT BY 1
502 NO MAXVALUE
503 NO MINVALUE
504 CACHE 1;
505CREATE TABLE TestGroups (
506 --- The fixed scheduling group ID.
507 -- This is assigned when the group is created and will never change.
508 idTestGroup INTEGER DEFAULT NEXTVAL('TestGroupIdSeq') NOT NULL,
509 --- When this row starts taking effect (inclusive).
510 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
511 --- When this row stops being tsEffective (exclusive).
512 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
513 --- The user id of the one who created/modified this entry.
514 -- Non-unique foreign key: Users(uid)
515 uidAuthor INTEGER NOT NULL,
516
517 --- The name of the scheduling group.
518 sName TEXT NOT NULL,
519 --- Optional group description.
520 sDescription TEXT,
521 -- Comment regarding a change or something.
522 sComment TEXT DEFAULT NULL,
523
524 PRIMARY KEY (idTestGroup, tsExpire)
525);
526CREATE INDEX TestGroups_id_index ON TestGroups (idTestGroup, tsExpire DESC, tsEffective ASC);
527
528
529--- @table TestGroupMembers
530-- The N:M relationship between test case configurations and test groups.
531--
532-- @remarks This table stores history. Never update or delete anything. The
533-- equivalent of deleting is done by setting the 'tsExpire' field to
534-- current_timestamp. To select the currently valid entries use
535-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
536--
537CREATE TABLE TestGroupMembers (
538 --- The group ID.
539 -- Non-unique foreign key: TestGroups(idTestGroup).
540 idTestGroup INTEGER NOT NULL,
541 --- The test case ID.
542 -- Non-unique foreign key: TestCases(idTestCase).
543 idTestCase INTEGER NOT NULL,
544 --- When this row starts taking effect (inclusive).
545 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
546 --- When this row stops being tsEffective (exclusive).
547 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
548 --- The user id of the one who created/modified this entry.
549 -- Non-unique foreign key: Users(uid)
550 uidAuthor INTEGER NOT NULL,
551
552 --- Test case scheduling priority.
553 -- Higher number causes the test case to be run more frequently.
554 -- @sa SchedGroupMembers.iSchedPriority, TestBoxesInSchedGroups.iSchedPriority
555 -- @todo Not sure we want to keep this...
556 iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL,
557
558 --- Limit the memberships to the given argument variations.
559 -- Non-unique foreign key: TestCaseArgs(idTestCase, idTestCaseArgs).
560 aidTestCaseArgs INTEGER ARRAY DEFAULT NULL,
561
562 PRIMARY KEY (idTestGroup, idTestCase, tsExpire)
563);
564
565
566--- @table SchedGroups
567-- Scheduling group (aka. testbox partitioning) configuration.
568--
569-- A testbox is associated with exactly one scheduling group. This association
570-- can be changed, of course. If we (want to) retire a group which still has
571-- testboxes associated with it, these will be moved to the 'default' group.
572--
573-- The TM web UI will make sure that a testbox is always in a group and that
574-- the default group cannot be deleted.
575--
576-- A scheduling group combines several things:
577-- - A selection of builds to test (via idBuildSrc).
578-- - A collection of test groups to test with (via SchedGroupMembers).
579-- - A set of testboxes to test on (via TestBoxes.idSchedGroup).
580--
581-- In additions there is an optional source of fresh test suite builds (think
582-- VBoxTestSuite) as well as scheduling options.
583--
584-- @remarks This table stores history. Never update or delete anything. The
585-- equivalent of deleting is done by setting the 'tsExpire' field to
586-- current_timestamp. To select the currently valid entries use
587-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
588--
589CREATE TYPE Scheduler_T AS ENUM (
590 'bestEffortContinousItegration',
591 'reserved'
592);
593CREATE SEQUENCE SchedGroupIdSeq
594 START 2
595 INCREMENT BY 1
596 NO MAXVALUE
597 NO MINVALUE
598 CACHE 1;
599CREATE TABLE SchedGroups (
600 --- The fixed scheduling group ID.
601 -- This is assigned when the group is created and will never change.
602 idSchedGroup INTEGER DEFAULT NEXTVAL('SchedGroupIdSeq') NOT NULL,
603 --- When this row starts taking effect (inclusive).
604 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
605 --- When this row stops being tsEffective (exclusive).
606 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
607 --- The user id of the one who created/modified this entry.
608 -- Non-unique foreign key: Users(uid)
609 -- @note This is NULL for the default group.
610 uidAuthor INTEGER DEFAULT NULL,
611
612 --- The name of the scheduling group.
613 sName TEXT NOT NULL,
614 --- Optional group description.
615 sDescription TEXT,
616 --- Indicates whether this group is currently enabled.
617 fEnabled boolean NOT NULL,
618 --- The scheduler to use.
619 -- This is for when we later desire different scheduling that the best
620 -- effort stuff provided by the initial implementation.
621 enmScheduler Scheduler_T DEFAULT 'bestEffortContinousItegration'::Scheduler_T NOT NULL,
622 --- The build source.
623 -- Non-unique foreign key: BuildSources(idBuildSrc)
624 idBuildSrc INTEGER DEFAULT NULL,
625 --- The Validation Kit build source (@VALIDATIONKIT_ZIP@).
626 -- Non-unique foreign key: BuildSources(idBuildSrc)
627 idBuildSrcTestSuite INTEGER DEFAULT NULL,
628 -- Comment regarding a change or something.
629 sComment TEXT DEFAULT NULL,
630
631 PRIMARY KEY (idSchedGroup, tsExpire)
632);
633
634-- Special default group.
635INSERT INTO SchedGroups (idSchedGroup, tsEffective, tsExpire, sName, sDescription, fEnabled)
636 VALUES (1, TIMESTAMP WITH TIME ZONE 'epoch', TIMESTAMP WITH TIME ZONE 'infinity', 'default', 'default group', FALSE);
637
638
639--- @table SchedGroupMembers
640-- N:M relationship between scheduling groups and test groups.
641--
642-- Several scheduling parameters are associated with this relationship.
643--
644-- The test group dependency (idTestGroupPreReq) can be used in the same way as
645-- TestCaseDeps.idTestCasePreReq, only here on test group level. This means it
646-- affects the build selection. The builds needs to have passed all test runs
647-- the prerequisite test group and done at least one argument variation of each
648-- test case in it.
649--
650-- @remarks This table stores history. Never update or delete anything. The
651-- equivalent of deleting is done by setting the 'tsExpire' field to
652-- current_timestamp. To select the currently valid entries use
653-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
654--
655CREATE TABLE SchedGroupMembers (
656 --- Scheduling ID.
657 -- Non-unique foreign key: SchedGroups(idSchedGroup).
658 idSchedGroup INTEGER NOT NULL,
659 --- Testgroup ID.
660 -- Non-unique foreign key: TestGroups(idTestGroup).
661 idTestGroup INTEGER NOT NULL,
662 --- When this row starts taking effect (inclusive).
663 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
664 --- When this row stops being tsEffective (exclusive).
665 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
666 --- The user id of the one who created/modified this entry.
667 -- Non-unique foreign key: Users(uid)
668 uidAuthor INTEGER NOT NULL,
669
670 --- The scheduling priority of the test group.
671 -- Higher number causes the test case to be run more frequently.
672 -- @sa TestGroupMembers.iSchedPriority, TestBoxesInSchedGroups.iSchedPriority
673 iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL,
674 --- When during the week this group is allowed to start running, NULL means
675 -- there are no constraints.
676 -- Each bit in the bitstring represents one hour, with bit 0 indicating the
677 -- midnight hour on a monday.
678 bmHourlySchedule bit(168) DEFAULT NULL,
679 --- Optional test group dependency.
680 -- Non-unique foreign key: TestGroups(idTestGroup).
681 -- This is for requiring that a build has been subject to smoke tests
682 -- before bothering to subject it to longer tests.
683 -- @todo Not entirely sure this should be here, but I'm not so keen on yet
684 -- another table as the only use case is smoketests.
685 idTestGroupPreReq INTEGER DEFAULT NULL,
686
687 PRIMARY KEY (idSchedGroup, idTestGroup, tsExpire)
688);
689
690
691--- @table TestBoxStrTab
692-- String table for the test boxes.
693--
694-- This is a string cache for all string members in TestBoxes except the name.
695-- The rational is to avoid duplicating large strings like sReport when the
696-- testbox reports a new cMbScratch value or the box when the test sheriff
697-- sends a reboot command or similar.
698--
699-- At the time this table was introduced, we had 400558 TestBoxes rows, where
700-- the SUM(LENGTH(sReport)) was 993MB. There were really just 1066 distinct
701-- sReport values, with a total length of 0x3 MB.
702--
703-- Nothing is ever deleted from this table.
704--
705-- @note Should use a stored procedure to query/insert a string.
706--
707--
708-- TestBox stats prior to conversion:
709-- SELECT COUNT(*) FROM TestBoxes: 400558 rows
710-- SELECT pg_total_relation_size('TestBoxes'): 740794368 bytes (706 MB)
711-- Average row cost: 740794368 / 400558 = 1849 bytes/row
712--
713-- After conversion:
714-- SELECT COUNT(*) FROM TestBoxes: 400558 rows
715-- SELECT pg_total_relation_size('TestBoxes'): 144375808 bytes (138 MB)
716-- SELECT COUNT(idStr) FROM TestBoxStrTab: 1292 rows
717-- SELECT pg_total_relation_size('TestBoxStrTab'): 5709824 bytes (5.5 MB)
718-- (144375808 + 5709824) / 740794368 = 20 %
719-- Average row cost boxes: 144375808 / 400558 = 360 bytes/row
720-- Average row cost strings: 5709824 / 1292 = 4420 bytes/row
721--
722CREATE SEQUENCE TestBoxStrTabIdSeq
723 START 1
724 INCREMENT BY 1
725 NO MAXVALUE
726 NO MINVALUE
727 CACHE 1;
728CREATE TABLE TestBoxStrTab (
729 --- The ID of this string.
730 idStr INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestBoxStrTabIdSeq'),
731 --- The string value.
732 sValue text NOT NULL,
733 --- Creation time stamp.
734 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL
735);
736-- Note! Must use hash index as the sReport strings are too long for regular indexing.
737CREATE INDEX TestBoxStrTabNameIdx ON TestBoxStrTab USING hash (sValue);
738
739--- Empty string with ID 0.
740INSERT INTO TestBoxStrTab (idStr, sValue) VALUES (0, '');
741
742
743--- @type TestBoxCmd_T
744-- Testbox commands.
745CREATE TYPE TestBoxCmd_T AS ENUM (
746 'none',
747 'abort',
748 'reboot', --< This implies abort. Status changes when reaching 'idle'.
749 'upgrade', --< This is only handled when asking for work.
750 'upgrade-and-reboot', --< Ditto.
751 'special' --< Similar to upgrade, reserved for the future.
752);
753
754
755--- @type LomKind_T
756-- The kind of lights out management on a testbox.
757CREATE TYPE LomKind_T AS ENUM (
758 'none',
759 'ilom',
760 'elom',
761 'apple-xserve-lom'
762);
763
764
765--- @table TestBoxes
766-- Testbox configurations.
767--
768-- The testboxes are identified by IP and the system UUID if available. Should
769-- the IP change, the testbox will be refused at sign on and the testbox
770-- sheriff will have to update it's IP.
771--
772-- @todo Implement the UUID stuff. Get it from DMI, UEFI or whereever.
773-- Mismatching needs to be logged somewhere...
774--
775-- To query the currently valid configuration:
776-- SELECT ... WHERE id = idTestBox AND tsExpire = TIMESTAMP WITH TIME ZONE 'infinity';
777--
778-- @remarks This table stores history. Never update or delete anything. The
779-- equivalent of deleting is done by setting the 'tsExpire' field to
780-- current_timestamp. To select the currently valid entries use
781-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
782--
783CREATE SEQUENCE TestBoxIdSeq
784 START 1
785 INCREMENT BY 1
786 NO MAXVALUE
787 NO MINVALUE
788 CACHE 1;
789CREATE SEQUENCE TestBoxGenIdSeq
790 START 1
791 INCREMENT BY 1
792 NO MAXVALUE
793 NO MINVALUE
794 CACHE 1;
795CREATE TABLE TestBoxes (
796 --- The fixed testbox ID.
797 -- This is assigned when the testbox is created and will never change.
798 idTestBox INTEGER DEFAULT NEXTVAL('TestBoxIdSeq') NOT NULL,
799 --- When this row starts taking effect (inclusive).
800 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
801 --- When this row stops being tsEffective (exclusive).
802 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
803 --- The user id of the one who created/modified this entry.
804 -- When modified automatically by the testbox, NULL is used.
805 -- Non-unique foreign key: Users(uid)
806 uidAuthor INTEGER DEFAULT NULL,
807 --- Generation ID for this row.
808 -- This is primarily for referencing by TestSets.
809 idGenTestBox INTEGER UNIQUE DEFAULT NEXTVAL('TestBoxGenIdSeq') NOT NULL,
810
811 --- The testbox IP.
812 -- This is from the webserver point of view and automatically updated on
813 -- SIGNON. The test setup doesn't permit for IP addresses to change while
814 -- the testbox is operational, because this will break gang tests.
815 ip inet NOT NULL,
816 --- The system or firmware UUID.
817 -- This uniquely identifies the testbox when talking to the server. After
818 -- SIGNON though, the testbox will also provide idTestBox and ip to
819 -- establish its identity beyond doubt.
820 uuidSystem uuid NOT NULL,
821 --- The testbox name.
822 -- Usually similar to the DNS name.
823 sName text NOT NULL,
824 --- Optional testbox description.
825 -- Intended for describing the box as well as making other relevant notes.
826 idStrDescription INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
827
828 --- Indicates whether this testbox is enabled.
829 -- A testbox gets disabled when we're doing maintenance, debugging a issue
830 -- that happens only on that testbox, or some similar stuff. This is an
831 -- alternative to deleting the testbox.
832 fEnabled BOOLEAN DEFAULT NULL,
833
834 --- The kind of lights-out-management.
835 enmLomKind LomKind_T DEFAULT 'none'::LomKind_T NOT NULL,
836 --- The IP adress of the lights-out-management.
837 -- This can be NULL if enmLomKind is 'none', otherwise it must contain a valid address.
838 ipLom inet DEFAULT NULL,
839
840 --- Timeout scale factor, given as a percent.
841 -- This is a crude adjustment of the test case timeout for slower hardware.
842 pctScaleTimeout smallint DEFAULT 100 NOT NULL CHECK (pctScaleTimeout > 10 AND pctScaleTimeout < 20000),
843
844 --- Change comment or similar.
845 idStrComment INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
846
847 --- @name Scheduling properties (reported by testbox script).
848 -- @{
849 --- Same abbrieviations as kBuild, see KBUILD_OSES.
850 idStrOs INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
851 --- Informational, no fixed format.
852 idStrOsVersion INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
853 --- Same as CPUID reports (GenuineIntel, AuthenticAMD, CentaurHauls, ...).
854 idStrCpuVendor INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
855 --- Same as kBuild - x86, amd64, ... See KBUILD_ARCHES.
856 idStrCpuArch INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
857 --- The CPU name if available.
858 idStrCpuName INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
859 --- Number identifying the CPU family/model/stepping/whatever.
860 -- For x86 and AMD64 type CPUs, this will on the following format:
861 -- (EffFamily << 24) | (EffModel << 8) | Stepping.
862 lCpuRevision bigint DEFAULT NULL,
863 --- Number of CPUs, CPU cores and CPU threads.
864 cCpus smallint DEFAULT NULL CHECK (cCpus IS NULL OR cCpus > 0),
865 --- Set if capable of hardware virtualization.
866 fCpuHwVirt boolean DEFAULT NULL,
867 --- Set if capable of nested paging.
868 fCpuNestedPaging boolean DEFAULT NULL,
869 --- Set if CPU capable of 64-bit (VBox) guests.
870 fCpu64BitGuest boolean DEFAULT NULL,
871 --- Set if chipset with usable IOMMU (VT-d / AMD-Vi).
872 fChipsetIoMmu boolean DEFAULT NULL,
873 --- Set if the test box does raw-mode tests.
874 fRawMode boolean DEFAULT NULL,
875 --- The (approximate) memory size in megabytes (rounded down to nearest 4 MB).
876 cMbMemory bigint DEFAULT NULL CHECK (cMbMemory IS NULL OR cMbMemory > 0),
877 --- The amount of scratch space in megabytes (rounded down to nearest 64 MB).
878 cMbScratch bigint DEFAULT NULL CHECK (cMbScratch IS NULL OR cMbScratch >= 0),
879 --- Free form hardware and software report field.
880 idStrReport INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
881 --- @}
882
883 --- The testbox script revision number, serves the purpose of a version number.
884 -- Probably good to have when scheduling upgrades as well for status purposes.
885 iTestBoxScriptRev INTEGER DEFAULT 0 NOT NULL,
886 --- The python sys.hexversion (layed out as of 2.7).
887 -- Good to know which python versions we need to support.
888 iPythonHexVersion INTEGER DEFAULT NULL,
889
890 --- Pending command.
891 -- @note We put it here instead of in TestBoxStatuses to get history.
892 enmPendingCmd TestBoxCmd_T DEFAULT 'none'::TestBoxCmd_T NOT NULL,
893
894 PRIMARY KEY (idTestBox, tsExpire),
895
896 --- Nested paging requires hardware virtualization.
897 CHECK (fCpuNestedPaging IS NULL OR (fCpuNestedPaging <> TRUE OR fCpuHwVirt = TRUE))
898);
899CREATE UNIQUE INDEX TestBoxesUuidIdx ON TestBoxes (uuidSystem, tsExpire DESC);
900CREATE INDEX TestBoxesExpireEffectiveIdx ON TestBoxes (tsExpire DESC, tsEffective ASC);
901
902
903--
904-- Create a view for TestBoxes where the strings are resolved.
905--
906CREATE VIEW TestBoxesWithStrings AS
907 SELECT TestBoxes.*,
908 Str1.sValue AS sDescription,
909 Str2.sValue AS sComment,
910 Str3.sValue AS sOs,
911 Str4.sValue AS sOsVersion,
912 Str5.sValue AS sCpuVendor,
913 Str6.sValue AS sCpuArch,
914 Str7.sValue AS sCpuName,
915 Str8.sValue AS sReport
916 FROM TestBoxes
917 LEFT OUTER JOIN TestBoxStrTab Str1 ON idStrDescription = Str1.idStr
918 LEFT OUTER JOIN TestBoxStrTab Str2 ON idStrComment = Str2.idStr
919 LEFT OUTER JOIN TestBoxStrTab Str3 ON idStrOs = Str3.idStr
920 LEFT OUTER JOIN TestBoxStrTab Str4 ON idStrOsVersion = Str4.idStr
921 LEFT OUTER JOIN TestBoxStrTab Str5 ON idStrCpuVendor = Str5.idStr
922 LEFT OUTER JOIN TestBoxStrTab Str6 ON idStrCpuArch = Str6.idStr
923 LEFT OUTER JOIN TestBoxStrTab Str7 ON idStrCpuName = Str7.idStr
924 LEFT OUTER JOIN TestBoxStrTab Str8 ON idStrReport = Str8.idStr;
925
926
927--- @table TestBoxesInSchedGroups
928-- N:M relationship between test boxes and scheduling groups.
929--
930-- We associate a priority with this relationship.
931--
932-- @remarks This table stores history. Never update or delete anything. The
933-- equivalent of deleting is done by setting the 'tsExpire' field to
934-- current_timestamp. To select the currently valid entries use
935-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
936--
937CREATE TABLE TestBoxesInSchedGroups (
938 --- TestBox ID.
939 -- Non-unique foreign key: TestBoxes(idTestBox).
940 idTestBox INTEGER NOT NULL,
941 --- Scheduling ID.
942 -- Non-unique foreign key: SchedGroups(idSchedGroup).
943 idSchedGroup INTEGER NOT NULL,
944 --- When this row starts taking effect (inclusive).
945 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
946 --- When this row stops being tsEffective (exclusive).
947 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
948 --- The user id of the one who created/modified this entry.
949 -- Non-unique foreign key: Users(uid)
950 uidAuthor INTEGER NOT NULL,
951
952 --- The scheduling priority of the scheduling group for the test box.
953 -- Higher number causes the scheduling group to be serviced more frequently.
954 -- @sa TestGroupMembers.iSchedPriority, SchedGroups.iSchedPriority
955 iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL,
956
957 PRIMARY KEY (idTestBox, idSchedGroup, tsExpire)
958);
959
960
961-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
962--
963-- F a i l u r e T r a c k i n g
964--
965-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
966
967
968--- @table FailureCategories
969-- Failure categories.
970--
971-- This is for organizing the failure reasons.
972--
973-- @remarks This table stores history. Never update or delete anything. The
974-- equivalent of deleting is done by setting the 'tsExpire' field to
975-- current_timestamp. To select the currently valid entries use
976-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
977--
978CREATE SEQUENCE FailureCategoryIdSeq
979 START 1
980 INCREMENT BY 1
981 NO MAXVALUE
982 NO MINVALUE
983 CACHE 1;
984CREATE TABLE FailureCategories (
985 --- The identifier of this failure category (once assigned, it will never change).
986 idFailureCategory INTEGER DEFAULT NEXTVAL('FailureCategoryIdSeq') NOT NULL,
987 --- When this row starts taking effect (inclusive).
988 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
989 --- When this row stops being tsEffective (exclusive).
990 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
991 --- The user id of the one who created/modified this entry.
992 -- Non-unique foreign key: Users(uid)
993 uidAuthor INTEGER NOT NULL,
994 --- The short category description.
995 -- For combo boxes and other selection lists.
996 sShort text NOT NULL,
997 --- Full description
998 -- For cursor-over-poppups for instance.
999 sFull text NOT NULL,
1000
1001 PRIMARY KEY (idFailureCategory, tsExpire)
1002);
1003
1004
1005--- @table FailureReasons
1006-- Failure reasons.
1007--
1008-- When analysing a test failure, the testbox sheriff will try assign a fitting
1009-- reason for the failure. This table is here to help the sheriff in his/hers
1010-- job as well as developers looking checking if their changes affected the
1011-- test results in any way.
1012--
1013-- @remarks This table stores history. Never update or delete anything. The
1014-- equivalent of deleting is done by setting the 'tsExpire' field to
1015-- current_timestamp. To select the currently valid entries use
1016-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
1017--
1018CREATE SEQUENCE FailureReasonIdSeq
1019 START 1
1020 INCREMENT BY 1
1021 NO MAXVALUE
1022 NO MINVALUE
1023 CACHE 1;
1024CREATE TABLE FailureReasons (
1025 --- The identifier of this failure reason (once assigned, it will never change).
1026 idFailureReason INTEGER DEFAULT NEXTVAL('FailureReasonIdSeq') NOT NULL,
1027 --- When this row starts taking effect (inclusive).
1028 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1029 --- When this row stops being tsEffective (exclusive).
1030 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
1031 --- The user id of the one who created/modified this entry.
1032 -- Non-unique foreign key: Users(uid)
1033 uidAuthor INTEGER NOT NULL,
1034
1035 --- The failure category this reason belongs to.
1036 -- Non-unique foreign key: FailureCategories(idFailureCategory)
1037 idFailureCategory INTEGER NOT NULL,
1038 --- The short failure description.
1039 -- For combo boxes and other selection lists.
1040 sShort text NOT NULL,
1041 --- Full failure description.
1042 sFull text NOT NULL,
1043 --- Ticket number in the primary bugtracker.
1044 iTicket INTEGER DEFAULT NULL,
1045 --- Other URLs to reports or discussions of the observed symptoms.
1046 asUrls text ARRAY DEFAULT NULL,
1047
1048 PRIMARY KEY (idFailureReason, tsExpire)
1049);
1050CREATE INDEX FailureReasonsCategoryIdx ON FailureReasons (idFailureCategory, idFailureReason);
1051
1052
1053
1054--- @table TestResultFailures
1055-- This is for tracking/discussing test result failures.
1056--
1057-- The rational for putting this is a separate table is that we need history on
1058-- this while TestResults does not.
1059--
1060-- @remarks This table stores history. Never update or delete anything. The
1061-- equivalent of deleting is done by setting the 'tsExpire' field to
1062-- current_timestamp. To select the currently valid entries use
1063-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
1064--
1065CREATE TABLE TestResultFailures (
1066 --- The test result we're disucssing.
1067 -- @note The foreign key is declared after TestResults (further down).
1068 idTestResult INTEGER NOT NULL,
1069 --- When this row starts taking effect (inclusive).
1070 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1071 --- When this row stops being tsEffective (exclusive).
1072 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
1073 --- The user id of the one who created/modified this entry.
1074 -- Non-unique foreign key: Users(uid)
1075 uidAuthor INTEGER NOT NULL,
1076 --- The testsest this result is a part of.
1077 -- This is mainly an aid for bypassing the enormous TestResults table.
1078 -- Note! This is a foreign key, but we have to add it after TestSets has
1079 -- been created, see further down.
1080 idTestSet INTEGER NOT NULL,
1081
1082 --- The suggested failure reason.
1083 -- Non-unique foreign key: FailureReasons(idFailureReason)
1084 idFailureReason INTEGER NOT NULL,
1085 --- Optional comment.
1086 sComment text DEFAULT NULL,
1087
1088 PRIMARY KEY (idTestResult, tsExpire)
1089);
1090CREATE INDEX TestResultFailureIdx ON TestResultFailures (idTestSet, tsExpire DESC, tsEffective ASC);
1091CREATE INDEX TestResultFailureIdx2 ON TestResultFailures (idTestResult, tsExpire DESC, tsEffective ASC);
1092CREATE INDEX TestResultFailureIdx3 ON TestResultFailures (idFailureReason, idTestResult, tsExpire DESC, tsEffective ASC);
1093
1094
1095
1096
1097-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1098--
1099-- T e s t I n p u t
1100--
1101-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1102
1103
1104--- @table BuildBlacklist
1105-- Table used to blacklist sets of builds.
1106--
1107-- The best usage example is a VMM developer realizing that a change causes the
1108-- host to panic, hang, or otherwise misbehave. To prevent the testbox sheriff
1109-- from repeatedly having to reboot testboxes, the builds gets blacklisted
1110-- until there is a working build again. This may mean adding an open ended
1111-- blacklist spec and then updating it with the final revision number once the
1112-- fix has been committed.
1113--
1114-- @remarks This table stores history. Never update or delete anything. The
1115-- equivalent of deleting is done by setting the 'tsExpire' field to
1116-- current_timestamp. To select the currently valid entries use
1117-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
1118--
1119-- @todo Would be nice if we could replace the text strings below with a set of
1120-- BuildCategories, or sore it in any other way which would enable us to
1121-- do a negative join with build category... The way it is specified
1122-- now, it looks like we have to open a cursor of prospecitve builds and
1123-- filter then thru this table one by one.
1124--
1125-- Any better representation is welcome, but this is low prioirty for
1126-- now, as it's relatively easy to change this later one.
1127--
1128CREATE SEQUENCE BuildBlacklistIdSeq
1129 START 1
1130 INCREMENT BY 1
1131 NO MAXVALUE
1132 NO MINVALUE
1133 CACHE 1;
1134CREATE TABLE BuildBlacklist (
1135 --- The blacklist entry id.
1136 -- This stays constant over time.
1137 idBlacklisting INTEGER DEFAULT NEXTVAL('BuildBlacklistIdSeq') NOT NULL,
1138 --- When this row starts taking effect (inclusive).
1139 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1140 --- When this row stops being tsEffective (exclusive).
1141 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
1142 --- The user id of the one who created/modified this entry.
1143 -- Non-unique foreign key: Users(uid)
1144 uidAuthor INTEGER NOT NULL,
1145
1146 --- The reason for the blacklisting.
1147 -- Non-unique foreign key: FailureReasons(idFailureReason)
1148 idFailureReason INTEGER NOT NULL,
1149
1150 --- Which product.
1151 -- ASSUME that it is okay to limit a blacklisting to a single product.
1152 sProduct text NOT NULL,
1153 --- Which branch.
1154 -- ASSUME that it is okay to limit a blacklisting to a branch.
1155 sBranch text NOT NULL,
1156
1157 --- Build types to include, all matches if NULL.
1158 asTypes text ARRAY DEFAULT NULL,
1159 --- Array of the 'sOs.sCpuArch' to match, all matches if NULL.
1160 -- See KBUILD_OSES in kBuild for a list of standard target OSes, and
1161 -- KBUILD_ARCHES for a list of standard architectures.
1162 --
1163 -- @remarks See marks on 'os-agnostic' and 'noarch' in BuildCategories.
1164 asOsArches text ARRAY DEFAULT NULL,
1165
1166 --- The first subversion tree revision to blacklist.
1167 iFirstRevision INTEGER NOT NULL,
1168 --- The last subversion tree revision to blacklist, no upper limit if NULL.
1169 iLastRevision INTEGER NOT NULL,
1170
1171 PRIMARY KEY (idBlacklisting, tsExpire)
1172);
1173CREATE INDEX BuildBlacklistIdx ON BuildBlacklist (iLastRevision DESC, iFirstRevision ASC, sProduct, sBranch,
1174 tsExpire DESC, tsEffective ASC);
1175
1176--- @table BuildCategories
1177-- Build categories.
1178--
1179-- The purpose of this table is saving space in the Builds table and hopefully
1180-- speed things up when selecting builds as well (compared to selecting on 4
1181-- text fields in the much larger Builds table).
1182--
1183-- Insert only table, no update, no delete. History is not needed.
1184--
1185CREATE SEQUENCE BuildCategoryIdSeq
1186 START 1
1187 INCREMENT BY 1
1188 NO MAXVALUE
1189 NO MINVALUE
1190 CACHE 1;
1191CREATE TABLE BuildCategories (
1192 --- The build type identifier.
1193 idBuildCategory INTEGER PRIMARY KEY DEFAULT NEXTVAL('BuildCategoryIdSeq') NOT NULL,
1194 --- Product.
1195 -- The product name. For instance 'VBox' or 'VBoxTestSuite'.
1196 sProduct TEXT NOT NULL,
1197 --- The version control repository name.
1198 sRepository TEXT NOT NULL,
1199 --- The branch name (in the version control system).
1200 sBranch TEXT NOT NULL,
1201 --- The build type.
1202 -- See KBUILD_BLD_TYPES in kBuild for a list of standard build types.
1203 sType TEXT NOT NULL,
1204 --- Array of the 'sOs.sCpuArch' supported by the build.
1205 -- See KBUILD_OSES in kBuild for a list of standard target OSes, and
1206 -- KBUILD_ARCHES for a list of standard architectures.
1207 --
1208 -- @remarks 'os-agnostic' is used if the build doesn't really target any
1209 -- specific OS or if it targets all applicable OSes.
1210 -- 'noarch' is used if the build is architecture independent or if
1211 -- all applicable architectures are handled.
1212 -- Thus, 'os-agnostic.noarch' will run on all build boxes.
1213 --
1214 -- @note The array shall be sorted ascendingly to prevent unnecessary duplicates!
1215 --
1216 asOsArches TEXT ARRAY NOT NULL,
1217
1218 UNIQUE (sProduct, sRepository, sBranch, sType, asOsArches)
1219);
1220
1221
1222--- @table Builds
1223-- The builds table contains builds from the tinderboxes and oaccasionally from
1224-- developers.
1225--
1226-- The tinderbox side could be fed by a batch job enumerating the build output
1227-- directories every so often, looking for new builds. Or we could query them
1228-- from the tinderbox database. Yet another alternative is making the
1229-- tinderbox server or client side software inform us about all new builds.
1230--
1231-- The developer builds are entered manually thru the TM web UI. They are used
1232-- for subjecting new code to some larger scale testing before commiting,
1233-- enabling, or merging a private branch.
1234--
1235-- The builds are being selected from this table by the via the build source
1236-- specification that SchedGroups.idBuildSrc and
1237-- SchedGroups.idBuildSrcTestSuite links to.
1238--
1239-- @remarks This table stores history. Never update or delete anything. The
1240-- equivalent of deleting is done by setting the 'tsExpire' field to
1241-- current_timestamp. To select the currently valid entries use
1242-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'.
1243--
1244CREATE SEQUENCE BuildIdSeq
1245 START 1
1246 INCREMENT BY 1
1247 NO MAXVALUE
1248 NO MINVALUE
1249 CACHE 1;
1250CREATE TABLE Builds (
1251 --- The build identifier.
1252 -- This remains unchanged
1253 idBuild INTEGER DEFAULT NEXTVAL('BuildIdSeq') NOT NULL,
1254 --- When this build was created or entered into the database.
1255 -- This remains unchanged
1256 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1257 --- When this row starts taking effect (inclusive).
1258 tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1259 --- When this row stops being tsEffective (exclusive).
1260 tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
1261 --- The user id of the one who created/modified this entry.
1262 -- Non-unique foreign key: Users(uid)
1263 -- @note This is NULL if added by a batch job / tinderbox.
1264 uidAuthor INTEGER DEFAULT NULL,
1265 --- The build category.
1266 idBuildCategory INTEGER REFERENCES BuildCategories(idBuildCategory) NOT NULL,
1267 --- The subversion tree revision of the build.
1268 iRevision INTEGER NOT NULL,
1269 --- The product version number (suitable for RTStrVersionCompare).
1270 sVersion TEXT NOT NULL,
1271 --- The link to the tinderbox log of this build.
1272 sLogUrl TEXT,
1273 --- Comma separated list of binaries.
1274 -- The binaries have paths relative to the TESTBOX_PATH_BUILDS or full URLs.
1275 sBinaries TEXT NOT NULL,
1276 --- Set when the binaries gets deleted by the build quota script.
1277 fBinariesDeleted BOOLEAN DEFAULT FALSE NOT NULL,
1278
1279 UNIQUE (idBuild, tsExpire)
1280);
1281CREATE INDEX BuildsLookupIdx ON Builds (idBuildCategory, iRevision);
1282
1283
1284--- @table VcsRevisions
1285-- This table is for translating build revisions into commit details.
1286--
1287-- For graphs and test results, it would be useful to translate revisions into
1288-- dates and maybe provide commit message and the committer.
1289--
1290-- Data is entered exclusively thru one or more batch jobs, so no internal
1291-- authorship needed. Also, since we're mirroring data from external sources
1292-- here, the batch job is allowed to update/replace existing records.
1293--
1294-- @todo We we could collect more info from the version control systems, if we
1295-- believe it's useful and can be presented in a reasonable manner.
1296-- Getting a list of affected files would be simple (requires
1297-- a separate table with a M:1 relationship to this table), or try
1298-- associate a commit to a branch.
1299--
1300CREATE TABLE VcsRevisions (
1301 --- The version control tree name.
1302 sRepository TEXT NOT NULL,
1303 --- The version control tree revision number.
1304 iRevision INTEGER NOT NULL,
1305 --- When the revision was created (committed).
1306 tsCreated TIMESTAMP WITH TIME ZONE NOT NULL,
1307 --- The name of the committer.
1308 -- @note Not to be confused with uidAuthor and test manager users.
1309 sAuthor TEXT,
1310 --- The commit message.
1311 sMessage TEXT,
1312
1313 UNIQUE (sRepository, iRevision)
1314);
1315CREATE INDEX VcsRevisionsByDate ON VcsRevisions (tsCreated DESC);
1316
1317
1318--- @table VcsBugReferences
1319-- This is for relating commits to a bug and vice versa.
1320--
1321-- This feature isn't so much for the test manager as a cheap way of extending
1322-- bug trackers without VCS integration. We just need to parse the commit
1323-- messages when inserting them into the VcsRevisions table.
1324--
1325-- Same input, updating and history considerations as VcsRevisions.
1326--
1327CREATE TABLE VcsBugReferences (
1328 --- The version control tree name.
1329 sRepository TEXT NOT NULL,
1330 --- The version control tree revision number.
1331 iRevision INTEGER NOT NULL,
1332 --- The bug tracker identifier - see g_kaBugTrackers in config.py.
1333 sBugTracker CHAR(4) NOT NULL,
1334 --- The bug number in the bug tracker.
1335 lBugNo BIGINT NOT NULL,
1336
1337 UNIQUE (sRepository, iRevision, sBugTracker, lBugNo)
1338);
1339CREATE INDEX VcsBugReferencesLookupIdx ON VcsBugReferences (sBugTracker, lBugNo);
1340
1341
1342
1343
1344-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1345--
1346-- T e s t R e s u l t s
1347--
1348-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1349
1350
1351--- @table TestResultStrTab
1352-- String table for the test results.
1353--
1354-- This is a string cache for value names, test names and possible more, that
1355-- is frequently repated in the test results record for each test run. The
1356-- purpose is not only to save space, but to make datamining queries faster by
1357-- giving them integer fields to work on instead of text fields. There may
1358-- possibly be some benefits on INSERT as well as there are only integer
1359-- indexes.
1360--
1361-- Nothing is ever deleted from this table.
1362--
1363-- @note Should use a stored procedure to query/insert a string.
1364--
1365CREATE SEQUENCE TestResultStrTabIdSeq
1366 START 1
1367 INCREMENT BY 1
1368 NO MAXVALUE
1369 NO MINVALUE
1370 CACHE 1;
1371CREATE TABLE TestResultStrTab (
1372 --- The ID of this string.
1373 idStr INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultStrTabIdSeq'),
1374 --- The string value.
1375 sValue text NOT NULL,
1376 --- Creation time stamp.
1377 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL
1378);
1379CREATE UNIQUE INDEX TestResultStrTabNameIdx ON TestResultStrTab (sValue);
1380
1381--- Empty string with ID 0.
1382INSERT INTO TestResultStrTab (idStr, sValue) VALUES (0, '');
1383
1384
1385--- @type TestStatus_T
1386-- The status of a test (set / result).
1387--
1388CREATE TYPE TestStatus_T AS ENUM (
1389 -- Initial status:
1390 'running',
1391 -- Final statuses:
1392 'success',
1393 -- Final status: Test didn't fail as such, it was something else.
1394 'skipped',
1395 'bad-testbox',
1396 'aborted',
1397 -- Final status: Test failed.
1398 'failure',
1399 'timed-out',
1400 'rebooted'
1401);
1402
1403
1404--- @table TestResults
1405-- Test results - a recursive bundle of joy!
1406--
1407-- A test case will be created when the testdriver calls reporter.testStart and
1408-- concluded with reporter.testDone. The testdriver (or it subordinates) can
1409-- use these methods to create nested test results. For IPRT based test cases,
1410-- RTTestCreate, RTTestInitAndCreate and RTTestSub will both create new test
1411-- result records, where as RTTestSubDone, RTTestSummaryAndDestroy and
1412-- RTTestDestroy will conclude records.
1413--
1414-- By concluding is meant updating the status. When the test driver reports
1415-- success, we check it against reported results. (paranoia strikes again!)
1416--
1417-- Nothing is ever deleted from this table.
1418--
1419-- @note As seen below, several other tables associate data with a
1420-- test result, and the top most test result is referenced by the
1421-- test set.
1422--
1423CREATE SEQUENCE TestResultIdSeq
1424 START 1
1425 INCREMENT BY 1
1426 NO MAXVALUE
1427 NO MINVALUE
1428 CACHE 1;
1429CREATE TABLE TestResults (
1430 --- The ID of this test result.
1431 idTestResult INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultIdSeq'),
1432 --- The parent test result.
1433 -- This is NULL for the top test result.
1434 idTestResultParent INTEGER REFERENCES TestResults(idTestResult),
1435 --- The test set this result is a part of.
1436 -- Note! This is a foreign key, but we have to add it after TestSets has
1437 -- been created, see further down.
1438 idTestSet INTEGER NOT NULL,
1439 --- Creation time stamp. This may also be the timestamp of when the test started.
1440 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1441 --- The elapsed time for this test.
1442 -- This is either reported by the directly (with some sanity checking) or
1443 -- calculated (current_timestamp - created_ts).
1444 -- @todo maybe use a nanosecond field here, check with what
1445 tsElapsed interval DEFAULT NULL,
1446 --- The test name.
1447 idStrName INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,
1448 --- The error count.
1449 cErrors INTEGER DEFAULT 0 NOT NULL,
1450 --- The test status.
1451 enmStatus TestStatus_T DEFAULT 'running'::TestStatus_T NOT NULL,
1452 --- Nesting depth.
1453 iNestingDepth smallint NOT NULL CHECK (iNestingDepth >= 0 AND iNestingDepth < 16),
1454 -- Make sure errors and status match up.
1455 CONSTRAINT CheckStatusMatchesErrors
1456 CHECK ( (cErrors > 0 AND enmStatus IN ('running'::TestStatus_T,
1457 'failure'::TestStatus_T, 'timed-out'::TestStatus_T, 'rebooted'::TestStatus_T ))
1458 OR (cErrors = 0 AND enmStatus IN ('running'::TestStatus_T, 'success'::TestStatus_T,
1459 'skipped'::TestStatus_T, 'aborted'::TestStatus_T, 'bad-testbox'::TestStatus_T))
1460 ),
1461 -- The following is for the TestResultFailures foreign key.
1462 -- Note! This was added with the name TestResults_idTestResult_idTestSet_key in the tmdb-r16 update script.
1463 UNIQUE (idTestResult, idTestSet)
1464);
1465
1466CREATE INDEX TestResultsSetIdx ON TestResults (idTestSet, idStrName, idTestResult);
1467CREATE INDEX TestResultsParentIdx ON TestResults (idTestResultParent);
1468-- The TestResultsNameIdx and TestResultsNameIdx2 are for speeding up the result graph & reporting code.
1469CREATE INDEX TestResultsNameIdx ON TestResults (idStrName, tsCreated DESC);
1470CREATE INDEX TestResultsNameIdx2 ON TestResults (idTestResult, idStrName);
1471
1472ALTER TABLE TestResultFailures ADD CONSTRAINT TestResultFailures_idTestResult_idTestSet_fkey
1473 FOREIGN KEY (idTestResult, idTestSet) REFERENCES TestResults(idTestResult, idTestSet) MATCH FULL;
1474
1475
1476--- @table TestResultValues
1477-- Test result values.
1478--
1479-- A testdriver or subordinate may report a test value via
1480-- reporter.testValue(), while IPRT based test will use RTTestValue and
1481-- associates.
1482--
1483-- This is an insert only table, no deletes, no updates.
1484--
1485CREATE SEQUENCE TestResultValueIdSeq
1486 START 1
1487 INCREMENT BY 1
1488 NO MAXVALUE
1489 NO MINVALUE
1490 CACHE 1;
1491CREATE TABLE TestResultValues (
1492 --- The ID of this value.
1493 idTestResultValue INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultValueIdSeq'),
1494 --- The test result it was reported within.
1495 idTestResult INTEGER REFERENCES TestResults(idTestResult) NOT NULL,
1496 --- The test set this value is a part of (for avoiding joining thru TestResults).
1497 -- Note! This is a foreign key, but we have to add it after TestSets has
1498 -- been created, see further down.
1499 idTestSet INTEGER NOT NULL,
1500 --- Creation time stamp.
1501 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1502 --- The name.
1503 idStrName INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,
1504 --- The value.
1505 lValue bigint NOT NULL,
1506 --- The unit.
1507 -- @todo This is currently not defined properly. Will fix/correlate this
1508 -- with the other places we use unit (IPRT/testdriver/VMMDev).
1509 iUnit smallint NOT NULL CHECK (iUnit >= 0 AND iUnit < 1024)
1510);
1511
1512CREATE INDEX TestResultValuesIdx ON TestResultValues(idTestResult);
1513-- The TestResultValuesGraphIdx is for speeding up the result graph & reporting code.
1514CREATE INDEX TestResultValuesGraphIdx ON TestResultValues(idStrName, tsCreated);
1515-- The TestResultValuesLogIdx is for speeding up the log viewer.
1516CREATE INDEX TestResultValuesLogIdx ON TestResultValues(idTestSet, tsCreated);
1517
1518
1519--- @table TestResultFiles
1520-- Test result files.
1521--
1522-- A testdriver or subordinate may report a file by using
1523-- reporter.addFile() or reporter.addLogFile().
1524--
1525-- The files stored here as well as the primary log file will be processed by a
1526-- batch job and compressed if considered compressable. Thus, TM will look for
1527-- files with a .gz/.bz2 suffix first and then without a suffix.
1528--
1529-- This is an insert only table, no deletes, no updates.
1530--
1531CREATE SEQUENCE TestResultFileId
1532 START 1
1533 INCREMENT BY 1
1534 NO MAXVALUE
1535 NO MINVALUE
1536 CACHE 1;
1537CREATE TABLE TestResultFiles (
1538 --- The ID of this file.
1539 idTestResultFile INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultFileId'),
1540 --- The test result it was reported within.
1541 idTestResult INTEGER REFERENCES TestResults(idTestResult) NOT NULL,
1542 --- The test set this file is a part of (for avoiding joining thru TestResults).
1543 -- Note! This is a foreign key, but we have to add it after TestSets has
1544 -- been created, see further down.
1545 idTestSet INTEGER NOT NULL,
1546 --- Creation time stamp.
1547 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1548 --- The filename relative to TestSets(sBaseFilename) + '-'.
1549 -- The set of valid filename characters should be very limited so that no
1550 -- file system issues can occure either on the TM side or the user when
1551 -- loading the files. Tests trying to use other characters will fail.
1552 -- Valid character regular expession: '^[a-zA-Z0-9_-(){}#@+,.=]*$'
1553 idStrFile INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,
1554 --- The description.
1555 idStrDescription INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,
1556 --- The kind of file.
1557 -- For instance: 'log/release/vm',
1558 -- 'screenshot/failure',
1559 -- 'screencapture/failure',
1560 -- 'xmllog/somestuff'
1561 idStrKind INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,
1562 --- The mime type for the file.
1563 -- For instance: 'text/plain',
1564 -- 'image/png',
1565 -- 'video/webm',
1566 -- 'text/xml'
1567 idStrMime INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL
1568);
1569
1570CREATE INDEX TestResultFilesIdx ON TestResultFiles(idTestResult);
1571CREATE INDEX TestResultFilesIdx2 ON TestResultFiles(idTestSet, tsCreated DESC);
1572
1573
1574--- @table TestResultMsgs
1575-- Test result message.
1576--
1577-- A testdriver or subordinate may report a message via the sDetails parameter
1578-- of the reporter.testFailure() method, while IPRT test cases will use
1579-- RTTestFailed, RTTestPrintf and their friends. For RTTestPrintf, we will
1580-- ignore the more verbose message levels since these can also be found in one
1581-- of the logs.
1582--
1583-- This is an insert only table, no deletes, no updates.
1584--
1585CREATE TYPE TestResultMsgLevel_T AS ENUM (
1586 'failure',
1587 'info'
1588);
1589CREATE SEQUENCE TestResultMsgIdSeq
1590 START 1
1591 INCREMENT BY 1
1592 NO MAXVALUE
1593 NO MINVALUE
1594 CACHE 1;
1595CREATE TABLE TestResultMsgs (
1596 --- The ID of this file.
1597 idTestResultMsg INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestResultMsgIdSeq'),
1598 --- The test result it was reported within.
1599 idTestResult INTEGER REFERENCES TestResults(idTestResult) NOT NULL,
1600 --- The test set this file is a part of (for avoiding joining thru TestResults).
1601 -- Note! This is a foreign key, but we have to add it after TestSets has
1602 -- been created, see further down.
1603 idTestSet INTEGER NOT NULL,
1604 --- Creation time stamp.
1605 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1606 --- The message string.
1607 idStrMsg INTEGER REFERENCES TestResultStrTab(idStr) NOT NULL,
1608 --- The message level.
1609 enmLevel TestResultMsgLevel_T NOT NULL
1610);
1611
1612CREATE INDEX TestResultMsgsIdx ON TestResultMsgs(idTestResult);
1613CREATE INDEX TestResultMsgsIdx2 ON TestResultMsgs(idTestSet, tsCreated DESC);
1614
1615
1616--- @table TestSets
1617-- Test sets / Test case runs.
1618--
1619-- This is where we collect data about test runs.
1620--
1621-- @todo Not entirely sure where the 'test set' term came from. Consider
1622-- finding something more appropriate.
1623--
1624CREATE SEQUENCE TestSetIdSeq
1625 START 1
1626 INCREMENT BY 1
1627 NO MAXVALUE
1628 NO MINVALUE
1629 CACHE 1;
1630CREATE TABLE TestSets (
1631 --- The ID of this test set.
1632 idTestSet INTEGER PRIMARY KEY DEFAULT NEXTVAL('TestSetIdSeq') NOT NULL,
1633
1634 --- The test config timestamp, used when reading test config.
1635 tsConfig TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
1636 --- When this test set was scheduled.
1637 -- idGenTestBox is valid at this point.
1638 tsCreated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
1639 --- When this test completed, i.e. testing stopped. This should only be set once.
1640 tsDone TIMESTAMP WITH TIME ZONE DEFAULT NULL,
1641 --- The current status.
1642 enmStatus TestStatus_T DEFAULT 'running'::TestStatus_T NOT NULL,
1643
1644 --- The build we're testing.
1645 -- Non-unique foreign key: Builds(idBuild)
1646 idBuild INTEGER NOT NULL,
1647 --- The build category of idBuild when the test started.
1648 -- This is for speeding up graph data collection, i.e. avoid idBuild
1649 -- the WHERE part of the selection.
1650 idBuildCategory INTEGER REFERENCES BuildCategories(idBuildCategory) NOT NULL,
1651 --- The test suite build we're using to do the testing.
1652 -- This is NULL if the test suite zip wasn't referred or if a test suite
1653 -- build source wasn't configured.
1654 -- Non-unique foreign key: Builds(idBuild)
1655 idBuildTestSuite INTEGER DEFAULT NULL,
1656
1657 --- The exact testbox configuration.
1658 idGenTestBox INTEGER REFERENCES TestBoxes(idGenTestBox) NOT NULL,
1659 --- The testbox ID for joining with (valid: tsStarted).
1660 -- Non-unique foreign key: TestBoxes(idTestBox)
1661 idTestBox INTEGER NOT NULL,
1662 --- The scheduling group ID the test was scheduled thru (valid: tsStarted).
1663 -- Non-unique foreign key: SchedGroups(idSchedGroup)
1664 idSchedGroup INTEGER NOT NULL,
1665
1666 --- The testgroup (valid: tsConfig).
1667 -- Non-unique foreign key: TestBoxes(idTestGroup)
1668 -- Note! This also gives the member ship entry, since a testcase can only
1669 -- have one membership per test group.
1670 idTestGroup INTEGER NOT NULL,
1671
1672 --- The exact test case config we executed in this test run.
1673 idGenTestCase INTEGER REFERENCES TestCases(idGenTestCase) NOT NULL,
1674 --- The test case ID for joining with (valid: tsConfig).
1675 -- Non-unique foreign key: TestBoxes(idTestCase)
1676 idTestCase INTEGER NOT NULL,
1677
1678 --- The arguments (and requirements++) we executed this test case with.
1679 idGenTestCaseArgs INTEGER REFERENCES TestCaseArgs(idGenTestCaseArgs) NOT NULL,
1680 --- The argument variation ID (valid: tsConfig).
1681 -- Non-unique foreign key: TestCaseArgs(idTestCaseArgs)
1682 idTestCaseArgs INTEGER NOT NULL,
1683
1684 --- The root of the test result tree.
1685 -- @note This will only be NULL early in the transaction setting up the testset.
1686 -- @note If the test reports more than one top level test result, we'll
1687 -- fail the whole test run and let the test developer fix it.
1688 idTestResult INTEGER REFERENCES TestResults(idTestResult) DEFAULT NULL,
1689
1690 --- The base filename used for storing files related to this test set.
1691 -- This is a path relative to wherever TM is dumping log files. In order
1692 -- to not become a file system test case, we will try not to put too many
1693 -- hundred thousand files in a directory. A simple first approach would
1694 -- be to just use the current date (tsCreated) like this:
1695 -- TM_FILE_DIR/year/month/day/TestSets.idTestSet
1696 --
1697 -- The primary log file for the test is this name suffixed by '.log'.
1698 --
1699 -- The files in the testresultfile table gets their full names like this:
1700 -- TM_FILE_DIR/sBaseFilename-testresultfile.id-TestResultStrTab(testresultfile.idStrFilename)
1701 --
1702 -- @remarks We store this explicitly in case we change the directly layout
1703 -- at some later point.
1704 sBaseFilename text UNIQUE NOT NULL,
1705
1706 --- The gang member number number, 0 is the leader.
1707 iGangMemberNo SMALLINT DEFAULT 0 NOT NULL CHECK (iGangMemberNo >= 0 AND iGangMemberNo < 1024),
1708 --- The test set of the gang leader, NULL if no gang involved.
1709 -- @note This is set by the gang leader as well, so that we can find all
1710 -- gang members by WHERE idTestSetGangLeader = :id.
1711 idTestSetGangLeader INTEGER REFERENCES TestSets(idTestSet) DEFAULT NULL
1712
1713);
1714CREATE INDEX TestSetsGangIdx ON TestSets (idTestSetGangLeader);
1715CREATE INDEX TestSetsBoxIdx ON TestSets (idTestBox, idTestResult);
1716CREATE INDEX TestSetsBuildIdx ON TestSets (idBuild, idTestResult);
1717CREATE INDEX TestSetsTestCaseIdx ON TestSets (idTestCase, idTestResult);
1718CREATE INDEX TestSetsTestVarIdx ON TestSets (idTestCaseArgs, idTestResult);
1719--- The TestSetsDoneCreatedBuildCatIdx is for testbox results, graph options and such.
1720CREATE INDEX TestSetsDoneCreatedBuildCatIdx ON TestSets (tsDone DESC NULLS FIRST, tsCreated ASC, idBuildCategory);
1721--- For graphs.
1722CREATE INDEX TestSetsGraphBoxIdx ON TestSets (idTestBox, tsCreated DESC, tsDone ASC NULLS LAST, idBuildCategory, idTestCase);
1723
1724ALTER TABLE TestResults ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
1725ALTER TABLE TestResultValues ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
1726ALTER TABLE TestResultFiles ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
1727ALTER TABLE TestResultMsgs ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
1728ALTER TABLE TestResultFailures ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
1729
1730
1731
1732
1733-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1734--
1735-- 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
1736--
1737-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1738
1739--- @type TestBoxState_T
1740-- TestBox state.
1741--
1742-- @todo Consider drawing a state diagram for this.
1743--
1744CREATE TYPE TestBoxState_T AS ENUM (
1745 --- Nothing to do.
1746 -- Prev: testing, gang-cleanup, rebooting, upgrading,
1747 -- upgrading-and-rebooting, doing-special-cmd.
1748 -- Next: testing, gang-gathering, rebooting, upgrading,
1749 -- upgrading-and-rebooting, doing-special-cmd.
1750 'idle',
1751 --- Executing a test.
1752 -- Prev: idle
1753 -- Next: idle
1754 'testing',
1755
1756 -- Gang scheduling statuses:
1757 --- The gathering of a gang.
1758 -- Prev: idle
1759 -- Next: gang-gathering-timedout, gang-testing
1760 'gang-gathering',
1761 --- The gathering timed out, the testbox needs to cleanup and move on.
1762 -- Prev: gang-gathering
1763 -- Next: idle
1764 -- This is set on all gathered members by the testbox who triggers the
1765 -- timeout.
1766 'gang-gathering-timedout',
1767 --- The gang scheduling equivalent of 'testing'.
1768 -- Prev: gang-gathering
1769 -- Next: gang-cleanup
1770 'gang-testing',
1771 --- Waiting for the other gang members to stop testing so that cleanups
1772 -- can be performed and members safely rescheduled.
1773 -- Prev: gang-testing
1774 -- Next: idle
1775 --
1776 -- There are two resource clean up issues being targeted here:
1777 -- 1. Global resources will be allocated by the leader when he enters the
1778 -- 'gang-gathering' state. If the leader quits and frees the resource
1779 -- while someone is still using it, bad things will happen. Imagine a
1780 -- global resource without any access checks and relies exclusivly on
1781 -- the TM doing its job.
1782 -- 2. TestBox resource accessed by other gang members may also be used in
1783 -- other tests. Should a gang member leave early and embark on a
1784 -- testcase using the same resources, bad things will happen. Example:
1785 -- Live migration. One partner leaves early because it detected some
1786 -- fatal failure, the other one is still trying to connect to him.
1787 -- The testbox is scheduled again on the same live migration testcase,
1788 -- only with different arguments (VM config), it will try migrate using
1789 -- the same TCP ports. Confusion ensues.
1790 --
1791 -- To figure out whether to remain in this status because someone is
1792 -- still testing:
1793 -- SELECT COUNT(*) FROM TestBoxStatuses, TestSets
1794 -- WHERE TestSets.idTestSetGangLeader = :idGangLeader
1795 -- AND TestSets.idTestBox = TestBoxStatuses.idTestBox
1796 -- AND TestSets.idTestSet = TestBoxStatuses.idTestSet
1797 -- AND TestBoxStatuses.enmState = 'gang-testing'::TestBoxState_T;
1798 'gang-cleanup',
1799
1800 -- Command related statuses (all command status changes comes from 'idle'
1801 -- and goes back to 'idle'):
1802 'rebooting',
1803 'upgrading',
1804 'upgrading-and-rebooting',
1805 'doing-special-cmd'
1806);
1807
1808--- @table TestBoxStatuses
1809-- Testbox status table.
1810--
1811-- History is not planned on this table.
1812--
1813CREATE TABLE TestBoxStatuses (
1814 --- The testbox.
1815 idTestBox INTEGER PRIMARY KEY NOT NULL,
1816 --- The testbox generation ID.
1817 idGenTestBox INTEGER REFERENCES TestBoxes(idGenTestBox) NOT NULL,
1818 --- When this status was last updated.
1819 -- This is updated everytime the testbox talks to the test manager, thus it
1820 -- can easily be used to find testboxes which has stopped responding.
1821 --
1822 -- This is used for timeout calculation during gang-gathering, so in that
1823 -- scenario it won't be updated until the gang is gathered or we time out.
1824 tsUpdated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
1825 --- The current state.
1826 enmState TestBoxState_T DEFAULT 'idle'::TestBoxState_T NOT NULL,
1827 --- Reference to the test set
1828 idTestSet INTEGER REFERENCES TestSets(idTestSet),
1829 --- Interal work item number.
1830 -- This is used to pick and prioritize between multiple scheduling groups.
1831 iWorkItem INTEGER DEFAULT 0 NOT NULL
1832);
1833
1834
1835--- @table GlobalResourceStatuses
1836-- Global resource status, tracks which test set resources are allocated by.
1837--
1838-- History is not planned on this table.
1839--
1840CREATE TABLE GlobalResourceStatuses (
1841 --- The resource ID.
1842 -- Non-unique foreign key: GlobalResources(idGlobalRsrc).
1843 idGlobalRsrc INTEGER PRIMARY KEY NOT NULL,
1844 --- The resource owner.
1845 -- @note This is going thru testboxstatus to be able to use the testbox ID
1846 -- as a foreign key.
1847 idTestBox INTEGER REFERENCES TestBoxStatuses(idTestBox) NOT NULL,
1848 --- When the allocation took place.
1849 tsAllocated TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL
1850);
1851
1852
1853--- @table SchedQueues
1854-- Scheduler queue.
1855--
1856-- The queues are currently associated with a scheduling group, it could
1857-- alternative be changed to hook on to a testbox instead. It depends on what
1858-- kind of scheduling method we prefer. The former method aims at test case
1859-- thruput, making sacrifices in the hardware distribution area. The latter is
1860-- more like the old buildbox style testing, making sure that each test case is
1861-- executed on each testbox.
1862--
1863-- When there are configuration changes, TM will regenerate the scheduling
1864-- queue for the affected scheduling groups. We do not concern ourselves with
1865-- trying to continue at the approximately same queue position, we simply take
1866-- it from the top.
1867--
1868-- When a testbox ask for work, we will open a cursor on the queue and take the
1869-- first test in the queue that can be executed on that testbox. The test will
1870-- be moved to the end of the queue (getting a new item_id).
1871--
1872-- If a test is manually changed to the head of the queue, the item will get a
1873-- item_id which is 1 lower than the head of the queue. Unless someone does
1874-- this a couple of billion times, we shouldn't have any trouble running out of
1875-- number space. :-)
1876--
1877-- Manually moving a test to the end of the queue is easy, just get a new
1878-- 'item_id'.
1879--
1880-- History is not planned on this table.
1881--
1882CREATE SEQUENCE SchedQueueItemIdSeq
1883 START 1
1884 INCREMENT BY 1
1885 NO MAXVALUE
1886 NO MINVALUE
1887 CACHE 1;
1888CREATE TABLE SchedQueues (
1889 --- The scheduling queue (one queue per scheduling group).
1890 -- Non-unique foreign key: SchedGroups(idSchedGroup)
1891 idSchedGroup INTEGER NOT NULL,
1892 --- The scheduler queue entry ID.
1893 -- Lower numbers means early queue position.
1894 idItem INTEGER DEFAULT NEXTVAL('SchedQueueItemIdSeq') NOT NULL,
1895 --- The queue offset.
1896 -- This is used for repositining the queue when recreating it. It can also
1897 -- be used to figure out how jumbled the queue gets after real life has had
1898 -- it's effect on it.
1899 offQueue INTEGER NOT NULL,
1900 --- The test case argument variation to execute.
1901 idGenTestCaseArgs INTEGER REFERENCES TestCaseArgs(idGenTestCaseArgs) NOT NULL,
1902 --- The relevant testgroup.
1903 -- Non-unique foreign key: TestGroups(idTestGroup).
1904 idTestGroup INTEGER NOT NULL,
1905 --- Aggregated test group dependencies (NULL if none).
1906 -- Non-unique foreign key: TestGroups(idTestGroup).
1907 -- See also comments on SchedGroupMembers.idTestGroupPreReq.
1908 aidTestGroupPreReqs INTEGER ARRAY DEFAULT NULL,
1909 --- The scheduling time constraints (see SchedGroupMembers.bmHourlySchedule).
1910 bmHourlySchedule bit(168) DEFAULT NULL,
1911 --- When the queue entry was created and for which config is valid.
1912 -- This is the timestamp that should be used when reading config info.
1913 tsConfig TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
1914 --- When this status was last scheduled.
1915 -- This is set to current_timestamp when moving the entry to the end of the
1916 -- queue. It's initial value is unix-epoch. Not entirely sure if it's
1917 -- useful beyond introspection and non-unique foreign key hacking.
1918 tsLastScheduled TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'epoch' NOT NULL,
1919
1920 --- This is used in gang scheduling.
1921 idTestSetGangLeader INTEGER REFERENCES TestSets(idTestSet) DEFAULT NULL UNIQUE,
1922 --- The number of gang members still missing.
1923 --
1924 -- This saves calculating the number of missing members via selects like:
1925 -- SELECT COUNT(*) FROM TestSets WHERE idTestSetGangLeader = :idGang;
1926 -- and
1927 -- SELECT cGangMembers FROM TestCaseArgs WHERE idGenTestCaseArgs = :idTest;
1928 -- to figure out whether to remain in 'gather-gang'::TestBoxState_T.
1929 --
1930 cMissingGangMembers smallint DEFAULT 1 NOT NULL,
1931
1932 --- @todo
1933 --- The number of times this has been considered for scheduling.
1934 -- cConsidered SMALLINT DEFAULT 0 NOT NULL,
1935
1936 PRIMARY KEY (idSchedGroup, idItem)
1937);
1938CREATE INDEX SchedQueuesItemIdx ON SchedQueues(idItem);
1939CREATE INDEX SchedQueuesSchedGroupIdx ON SchedQueues(idSchedGroup);
1940
Note: See TracBrowser for help on using the repository browser.

© 2024 Oracle Support Privacy / Do Not Sell My Info Terms of Use Trademark Policy Automated Access Etiquette