tmdb-r26-testboxes-4.pgsql@ 103197

Last change on this file since 103197 was 103197, checked in by vboxsync, 15 months ago
ValidationKit/testmanager: Distinguish between testboxes which can do hw-virt/np and the ones using the host native API for virtualization, bugref:10592
Property svn:eol-style set to `native` Property svn:keywords set to `Author Date Id Revision`
File size: 17.1 KB

Line
1	-- $Id: tmdb-r26-testboxes-4.pgsql 103197 2024-02-05 10:00:40Z vboxsync $
2	--- @file
3	-- VBox Test Manager Database - Adds fNativeApi to TestBoxes.
4	--
5
6	--
7	-- Copyright (C) 2024 Oracle and/or its affiliates.
8	--
9	-- This file is part of VirtualBox base platform packages, as
10	-- available from https://www.virtualbox.org.
11	--
12	-- This program is free software; you can redistribute it and/or
13	-- modify it under the terms of the GNU General Public License
14	-- as published by the Free Software Foundation, in version 3 of the
15	-- License.
16	--
17	-- This program is distributed in the hope that it will be useful, but
18	-- WITHOUT ANY WARRANTY; without even the implied warranty of
19	-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
20	-- General Public License for more details.
21	--
22	-- You should have received a copy of the GNU General Public License
23	-- along with this program; if not, see <https://www.gnu.org/licenses>.
24	--
25	-- The contents of this file may alternatively be used under the terms
26	-- of the Common Development and Distribution License Version 1.0
27	-- (CDDL), a copy of it is provided in the "COPYING.CDDL" file included
28	-- in the VirtualBox distribution, in which case the provisions of the
29	-- CDDL are applicable instead of those of the GPL.
30	--
31	-- You may elect to license modified versions of this file under the
32	-- terms and conditions of either the GPL or the CDDL or both.
33	--
34	-- SPDX-License-Identifier: GPL-3.0-only OR CDDL-1.0
35	--
36
37	--
38	-- Cleanup after failed runs.
39	--
40	DROP TABLE IF EXISTS OldTestBoxes;
41
42	--
43	-- Die on error from now on.
44	--
45	\set ON_ERROR_STOP 1
46	\set AUTOCOMMIT 0
47
48	-- Total grid lock.
49	LOCK TABLE TestBoxStatuses IN ACCESS EXCLUSIVE MODE;
50	LOCK TABLE TestSets IN ACCESS EXCLUSIVE MODE;
51	LOCK TABLE SchedGroupMembers IN ACCESS EXCLUSIVE MODE;
52	LOCK TABLE TestBoxes IN ACCESS EXCLUSIVE MODE;
53
54	--
55	-- Rename the original table, drop constrains and foreign key references so we
56	-- get the right name automatic when creating the new one.
57	--
58	ALTER TABLE TestBoxes RENAME TO OldTestBoxes;
59
60	ALTER TABLE OldTestBoxes DROP CONSTRAINT testboxes_ccpus_check;
61	ALTER TABLE OldTestBoxes DROP CONSTRAINT testboxes_check;
62	ALTER TABLE OldTestBoxes DROP CONSTRAINT testboxes_cmbmemory_check;
63	ALTER TABLE OldTestBoxes DROP CONSTRAINT testboxes_cmbscratch_check;
64	ALTER TABLE OldTestBoxes DROP CONSTRAINT testboxes_pctscaletimeout_check;
65
66	ALTER TABLE TestBoxStatuses DROP CONSTRAINT TestBoxStatuses_idGenTestBox_fkey;
67	ALTER TABLE TestSets DROP CONSTRAINT TestSets_idGenTestBox_fkey;
68
69	ALTER TABLE OldTestBoxes DROP CONSTRAINT testboxes_pkey;
70	ALTER TABLE OldTestBoxes DROP CONSTRAINT testboxes_idgentestbox_key;
71
72	DROP INDEX IF EXISTS TestBoxesUuidIdx;
73	DROP INDEX IF EXISTS TestBoxesExpireEffectiveIdx;
74
75	-- This output should be free of index, constraints and references from other tables.
76	\d+ OldTestBoxes;
77
78
79	CREATE TABLE TestBoxes (
80	--- The fixed testbox ID.
81	-- This is assigned when the testbox is created and will never change.
82	idTestBox INTEGER DEFAULT NEXTVAL('TestBoxIdSeq') NOT NULL,
83	--- When this row starts taking effect (inclusive).
84	tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL,
85	--- When this row stops being tsEffective (exclusive).
86	tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL,
87	--- The user id of the one who created/modified this entry.
88	-- When modified automatically by the testbox, NULL is used.
89	-- Non-unique foreign key: Users(uid)
90	uidAuthor INTEGER DEFAULT NULL,
91	--- Generation ID for this row.
92	-- This is primarily for referencing by TestSets.
93	idGenTestBox INTEGER UNIQUE DEFAULT NEXTVAL('TestBoxGenIdSeq') NOT NULL,
94
95	--- The testbox IP.
96	-- This is from the webserver point of view and automatically updated on
97	-- SIGNON. The test setup doesn't permit for IP addresses to change while
98	-- the testbox is operational, because this will break gang tests.
99	ip inet NOT NULL,
100	--- The system or firmware UUID.
101	-- This uniquely identifies the testbox when talking to the server. After
102	-- SIGNON though, the testbox will also provide idTestBox and ip to
103	-- establish its identity beyond doubt.
104	uuidSystem uuid NOT NULL,
105	--- The testbox name.
106	-- Usually similar to the DNS name.
107	sName text NOT NULL,
108	--- Optional testbox description.
109	-- Intended for describing the box as well as making other relevant notes.
110	idStrDescription INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
111
112	--- Indicates whether this testbox is enabled.
113	-- A testbox gets disabled when we're doing maintenance, debugging a issue
114	-- that happens only on that testbox, or some similar stuff. This is an
115	-- alternative to deleting the testbox.
116	fEnabled BOOLEAN DEFAULT NULL,
117
118	--- The kind of lights-out-management.
119	enmLomKind LomKind_T DEFAULT 'none'::LomKind_T NOT NULL,
120	--- The IP adress of the lights-out-management.
121	-- This can be NULL if enmLomKind is 'none', otherwise it must contain a valid address.
122	ipLom inet DEFAULT NULL,
123
124	--- Timeout scale factor, given as a percent.
125	-- This is a crude adjustment of the test case timeout for slower hardware.
126	pctScaleTimeout smallint DEFAULT 100 NOT NULL CHECK (pctScaleTimeout > 10 AND pctScaleTimeout < 20000),
127
128	--- Change comment or similar.
129	idStrComment INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
130
131	--- @name Scheduling properties (reported by testbox script).
132	-- @{
133	--- Same abbrieviations as kBuild, see KBUILD_OSES.
134	idStrOs INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
135	--- Informational, no fixed format.
136	idStrOsVersion INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
137	--- Same as CPUID reports (GenuineIntel, AuthenticAMD, CentaurHauls, ...).
138	idStrCpuVendor INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
139	--- Same as kBuild - x86, amd64, ... See KBUILD_ARCHES.
140	idStrCpuArch INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
141	--- The CPU name if available.
142	idStrCpuName INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
143	--- Number identifying the CPU family/model/stepping/whatever.
144	-- For x86 and AMD64 type CPUs, this will on the following format:
145	-- (EffFamily << 24) \| (EffModel << 8) \| Stepping.
146	lCpuRevision bigint DEFAULT NULL,
147	--- Number of CPUs, CPU cores and CPU threads.
148	cCpus smallint DEFAULT NULL CHECK (cCpus IS NULL OR cCpus > 0),
149	--- Set if capable of hardware virtualization.
150	fCpuHwVirt boolean DEFAULT NULL,
151	--- Set if capable of nested paging.
152	fCpuNestedPaging boolean DEFAULT NULL,
153	--- Set if CPU capable of 64-bit (VBox) guests.
154	fCpu64BitGuest boolean DEFAULT NULL,
155	--- Set if chipset with usable IOMMU (VT-d / AMD-Vi).
156	fChipsetIoMmu boolean DEFAULT NULL,
157	--- Set if the test box does raw-mode tests.
158	fRawMode boolean DEFAULT NULL,
159	-- Set if the test box does native API (NEM) tests.
160	fNativeApi boolean DEFAULT NULL,
161	--- The (approximate) memory size in megabytes (rounded down to nearest 4 MB).
162	cMbMemory bigint DEFAULT NULL CHECK (cMbMemory IS NULL OR cMbMemory > 0),
163	--- The amount of scratch space in megabytes (rounded down to nearest 64 MB).
164	cMbScratch bigint DEFAULT NULL CHECK (cMbScratch IS NULL OR cMbScratch >= 0),
165	--- Free form hardware and software report field.
166	idStrReport INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL,
167	--- @}
168
169	--- The testbox script revision number, serves the purpose of a version number.
170	-- Probably good to have when scheduling upgrades as well for status purposes.
171	iTestBoxScriptRev INTEGER DEFAULT 0 NOT NULL,
172	--- The python sys.hexversion (layed out as of 2.7).
173	-- Good to know which python versions we need to support.
174	iPythonHexVersion INTEGER DEFAULT NULL,
175
176	--- Pending command.
177	-- @note We put it here instead of in TestBoxStatuses to get history.
178	enmPendingCmd TestBoxCmd_T DEFAULT 'none'::TestBoxCmd_T NOT NULL,
179
180	PRIMARY KEY (idTestBox, tsExpire),
181
182	--- Nested paging requires hardware virtualization.
183	CHECK (fCpuNestedPaging IS NULL OR (fCpuNestedPaging <> TRUE OR fCpuHwVirt = TRUE))
184	);
185
186
187	--
188	-- Populate the test box table.
189	--
190
191	INSERT INTO TestBoxes (
192	idTestBox, -- 0
193	tsEffective, -- 1
194	tsExpire, -- 2
195	uidAuthor, -- 3
196	idGenTestBox, -- 4
197	ip, -- 5
198	uuidSystem, -- 6
199	sName, -- 7
200	idStrDescription, -- 8
201	fEnabled, -- 9
202	enmLomKind, -- 10
203	ipLom, -- 11
204	pctScaleTimeout, -- 12
205	idStrComment, -- 13
206	idStrOs, -- 14
207	idStrOsVersion, -- 15
208	idStrCpuVendor, -- 16
209	idStrCpuArch, -- 17
210	idStrCpuName, -- 18
211	lCpuRevision, -- 19
212	cCpus, -- 20
213	fCpuHwVirt, -- 21
214	fCpuNestedPaging, -- 22
215	fCpu64BitGuest, -- 23
216	fChipsetIoMmu, -- 24
217	fRawMode, -- 25
218	fNativeApi, -- 26
219	cMbMemory, -- 27
220	cMbScratch, -- 28
221	idStrReport, -- 29
222	iTestBoxScriptRev, -- 30
223	iPythonHexVersion, -- 31
224	enmPendingCmd -- 32
225	)
226	SELECT idTestBox, -- 0
227	tsEffective, -- 1
228	tsExpire, -- 2
229	uidAuthor, -- 3
230	idGenTestBox, -- 4
231	ip, -- 5
232	uuidSystem, -- 6
233	sName, -- 7
234	idStrDescription, -- 8
235	fEnabled, -- 9
236	enmLomKind, -- 10
237	ipLom, -- 11
238	pctScaleTimeout, -- 12
239	idStrComment, -- 13
240	idStrOs, -- 14
241	idStrOsVersion, -- 15
242	idStrCpuVendor, -- 16
243	idStrCpuArch, -- 17
244	idStrCpuName, -- 18
245	lCpuRevision, -- 19
246	cCpus, -- 20
247	fCpuHwVirt, -- 21
248	fCpuNestedPaging, -- 22
249	fCpu64BitGuest, -- 23
250	fChipsetIoMmu, -- 24
251	fRawMode, -- 25
252	NULL, -- 26
253	cMbMemory, -- 27
254	cMbScratch, -- 28
255	idStrReport, -- 29
256	iTestBoxScriptRev, -- 30
257	iPythonHexVersion, -- 31
258	enmPendingCmd -- 32
259	FROM OldTestBoxes;
260
261	-- Restore indexes.
262	CREATE UNIQUE INDEX TestBoxesUuidIdx ON TestBoxes (uuidSystem, tsExpire DESC);
263	CREATE INDEX TestBoxesExpireEffectiveIdx ON TestBoxes (tsExpire DESC, tsEffective ASC);
264
265	-- Restore foreign key references to the table.
266	ALTER TABLE TestBoxStatuses ADD CONSTRAINT TestBoxStatuses_idGenTestBox_fkey
267	FOREIGN KEY (idGenTestBox) REFERENCES TestBoxes(idGenTestBox);
268	ALTER TABLE TestSets ADD CONSTRAINT TestSets_idGenTestBox_fkey
269	FOREIGN KEY (idGenTestBox) REFERENCES TestBoxes(idGenTestBox);
270
271	DROP VIEW TestBoxesWithStrings;
272	CREATE VIEW TestBoxesWithStrings AS
273	SELECT TestBoxes.*,
274	Str1.sValue AS sDescription,
275	Str2.sValue AS sComment,
276	Str3.sValue AS sOs,
277	Str4.sValue AS sOsVersion,
278	Str5.sValue AS sCpuVendor,
279	Str6.sValue AS sCpuArch,
280	Str7.sValue AS sCpuName,
281	Str8.sValue AS sReport
282	FROM TestBoxes
283	LEFT OUTER JOIN TestBoxStrTab Str1 ON idStrDescription = Str1.idStr
284	LEFT OUTER JOIN TestBoxStrTab Str2 ON idStrComment = Str2.idStr
285	LEFT OUTER JOIN TestBoxStrTab Str3 ON idStrOs = Str3.idStr
286	LEFT OUTER JOIN TestBoxStrTab Str4 ON idStrOsVersion = Str4.idStr
287	LEFT OUTER JOIN TestBoxStrTab Str5 ON idStrCpuVendor = Str5.idStr
288	LEFT OUTER JOIN TestBoxStrTab Str6 ON idStrCpuArch = Str6.idStr
289	LEFT OUTER JOIN TestBoxStrTab Str7 ON idStrCpuName = Str7.idStr
290	LEFT OUTER JOIN TestBoxStrTab Str8 ON idStrReport = Str8.idStr;
291
292	-- Drop the old table.
293	DROP TABLE OldTestBoxes;
294
295	COMMIT;
296
297	\d TestBoxes;
298
299
300	--
301	-- Update the TestBoxLogic_updateOnSignOn function from core/testbox.pgsql.
302	-- Note sure if we usually do it this way or not...
303	--
304	CREATE OR REPLACE function TestBoxLogic_updateOnSignOn(a_idTestBox INTEGER,
305	a_ip inet,
306	a_sOs TEXT,
307	a_sOsVersion TEXT,
308	a_sCpuVendor TEXT,
309	a_sCpuArch TEXT,
310	a_sCpuName TEXT,
311	a_lCpuRevision bigint,
312	a_cCpus INTEGER, -- Actually smallint, but default typing fun.
313	a_fCpuHwVirt boolean,
314	a_fCpuNestedPaging boolean,
315	a_fCpu64BitGuest boolean,
316	a_fChipsetIoMmu boolean,
317	a_fRawMode boolean,
318	a_fNativeApi boolean,
319	a_cMbMemory bigint,
320	a_cMbScratch bigint,
321	a_sReport TEXT,
322	a_iTestBoxScriptRev INTEGER,
323	a_iPythonHexVersion INTEGER,
324	OUT r_idGenTestBox INTEGER
325	) AS $$
326	DECLARE
327	v_Row TestBoxes%ROWTYPE;
328	v_idStrOs INTEGER;
329	v_idStrOsVersion INTEGER;
330	v_idStrCpuVendor INTEGER;
331	v_idStrCpuArch INTEGER;
332	v_idStrCpuName INTEGER;
333	v_idStrReport INTEGER;
334	BEGIN
335	SELECT TestBoxLogic_lookupOrFindString(a_sOs) INTO v_idStrOs;
336	SELECT TestBoxLogic_lookupOrFindString(a_sOsVersion) INTO v_idStrOsVersion;
337	SELECT TestBoxLogic_lookupOrFindString(a_sCpuVendor) INTO v_idStrCpuVendor;
338	SELECT TestBoxLogic_lookupOrFindString(a_sCpuArch) INTO v_idStrCpuArch;
339	SELECT TestBoxLogic_lookupOrFindString(a_sCpuName) INTO v_idStrCpuName;
340	SELECT TestBoxLogic_lookupOrFindString(a_sReport) INTO v_idStrReport;
341
342	-- Fetch and historize the current row - there must be one.
343	UPDATE TestBoxes
344	SET tsExpire = CURRENT_TIMESTAMP
345	WHERE idTestBox = a_idTestBox
346	AND tsExpire = 'infinity'::TIMESTAMP
347	RETURNING * INTO STRICT v_Row;
348
349	-- Modify the row with the new data.
350	v_Row.uidAuthor := NULL;
351	v_Row.ip := a_ip;
352	v_Row.idStrOs := v_idStrOs;
353	v_Row.idStrOsVersion := v_idStrOsVersion;
354	v_Row.idStrCpuVendor := v_idStrCpuVendor;
355	v_Row.idStrCpuArch := v_idStrCpuArch;
356	v_Row.idStrCpuName := v_idStrCpuName;
357	v_Row.lCpuRevision := a_lCpuRevision;
358	v_Row.cCpus := a_cCpus;
359	v_Row.fCpuHwVirt := a_fCpuHwVirt;
360	v_Row.fCpuNestedPaging := a_fCpuNestedPaging;
361	v_Row.fCpu64BitGuest := a_fCpu64BitGuest;
362	v_Row.fChipsetIoMmu := a_fChipsetIoMmu;
363	v_Row.fRawMode := a_fRawMode;
364	v_Row.fNativeApi := a_fNativeApi;
365	v_Row.cMbMemory := a_cMbMemory;
366	v_Row.cMbScratch := a_cMbScratch;
367	v_Row.idStrReport := v_idStrReport;
368	v_Row.iTestBoxScriptRev := a_iTestBoxScriptRev;
369	v_Row.iPythonHexVersion := a_iPythonHexVersion;
370	v_Row.tsEffective := v_Row.tsExpire;
371	v_Row.tsExpire := 'infinity'::TIMESTAMP;
372
373	-- Get a new generation ID.
374	SELECT NEXTVAL('TestBoxGenIdSeq') INTO v_Row.idGenTestBox;
375	r_idGenTestBox := v_Row.idGenTestBox;
376
377	-- Insert the modified row.
378	INSERT INTO TestBoxes VALUES (v_Row.*);
379	END;
380	$$ LANGUAGE plpgsql;
381
382	COMMIT;
383

Note: See TracBrowser for help on using the repository browser.

Download in other formats:

Original Format