tmdb-r26-testboxes-4.pgsql

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

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

Download in other formats:

Original Format