Index: /trunk/src/VBox/ValidationKit/testmanager/core/testboxstatus.py =================================================================== --- /trunk/src/VBox/ValidationKit/testmanager/core/testboxstatus.py (revision 61506) +++ /trunk/src/VBox/ValidationKit/testmanager/core/testboxstatus.py (revision 61507) @@ -166,5 +166,5 @@ """ self._oDb.execute('SELECT TestBoxStatuses.*,\n' - ' TestBoxesWithStrings.*,\n' + ' TestBoxesWithStrings.*\n' 'FROM TestBoxStatuses,\n' ' TestBoxesWithStrings\n' @@ -174,9 +174,8 @@ ' AND TestBoxesWithStrings.uuidSystem = %s\n' ' AND TestBoxesWithStrings.ip = %s\n' - , (idTestBox, - idTestBox, - sTestBoxUuid, - sTestBoxAddr, - )); + , ( idTestBox, + idTestBox, + sTestBoxUuid, + sTestBoxAddr,) ); cRows = self._oDb.getRowCount(); if cRows != 1: @@ -202,7 +201,7 @@ ' %s,\n' ' \'idle\'::TestBoxState_T,\n' - ' NULL,\n', - ' 0)\n', - (idTestBox, idGenTestBox) ); + ' NULL,\n' + ' 0)\n' + , (idTestBox, idGenTestBox) ); self._oDb.maybeCommit(fCommit); return True; Index: /trunk/src/VBox/ValidationKit/testmanager/db/TestManagerDatabaseInit.pgsql =================================================================== --- /trunk/src/VBox/ValidationKit/testmanager/db/TestManagerDatabaseInit.pgsql (revision 61506) +++ /trunk/src/VBox/ValidationKit/testmanager/db/TestManagerDatabaseInit.pgsql (revision 61507) @@ -550,5 +550,5 @@ --- Test case scheduling priority. -- Higher number causes the test case to be run more frequently. - -- @sa SchedGroupMembers.iSchedPriority + -- @sa SchedGroupMembers.iSchedPriority, TestBoxesInSchedGroups.iSchedPriority -- @todo Not sure we want to keep this... iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL, @@ -666,7 +666,7 @@ uidAuthor INTEGER NOT NULL, - --- The scheduling priority if the test group. + --- The scheduling priority of the test group. -- Higher number causes the test case to be run more frequently. - -- @sa TestGroupMembers.iSchedPriority + -- @sa TestGroupMembers.iSchedPriority, TestBoxesInSchedGroups.iSchedPriority iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL, --- When during the week this group is allowed to start running, NULL means @@ -823,9 +823,4 @@ -- Intended for describing the box as well as making other relevant notes. idStrDescription INTEGER REFERENCES TestBoxStrTab(idStr) DEFAULT NULL, - - --- Reference to the scheduling group that this testbox is a member of. - -- Non-unique foreign key: SchedGroups(idSchedGroup) - -- A testbox is always part of a group, the default one nothing else. - idSchedGroup INTEGER DEFAULT 1 NOT NULL, --- Indicates whether this testbox is enabled. @@ -927,4 +922,37 @@ LEFT OUTER JOIN TestBoxStrTab Str8 ON idStrReport = Str8.idStr; + +--- @table TestBoxesInSchedGroups +-- N:M relationship between test boxes and scheduling groups. +-- +-- We associate a priority with this relationship. +-- +-- @remarks This table stores history. Never update or delete anything. The +-- equivalent of deleting is done by setting the 'tsExpire' field to +-- current_timestamp. To select the currently valid entries use +-- tsExpire = TIMESTAMP WITH TIME ZONE 'infinity'. +-- +CREATE TABLE TestBoxesInSchedGroups ( + --- TestBox ID. + -- Non-unique foreign key: TestBoxes(idTestBox). + idTestBox INTEGER NOT NULL, + --- Scheduling ID. + -- Non-unique foreign key: SchedGroups(idSchedGroup). + idSchedGroup INTEGER NOT NULL, + --- When this row starts taking effect (inclusive). + tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL, + --- When this row stops being tsEffective (exclusive). + tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL, + --- The user id of the one who created/modified this entry. + -- Non-unique foreign key: Users(uid) + uidAuthor INTEGER NOT NULL, + + --- The scheduling priority of the scheduling group for the test box. + -- Higher number causes the scheduling group to be serviced more frequently. + -- @sa TestGroupMembers.iSchedPriority, SchedGroups.iSchedPriority + iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL, + + PRIMARY KEY (idTestBox, idSchedGroup, tsExpire) +); @@ -1771,5 +1799,8 @@ enmState TestBoxState_T DEFAULT 'idle'::TestBoxState_T NOT NULL, --- Reference to the test set - idTestSet INTEGER REFERENCES TestSets(idTestSet) + idTestSet INTEGER REFERENCES TestSets(idTestSet), + --- Interal work item number. + -- This is used to pick and prioritize between multiple scheduling groups. + iWorkItem INTEGER DEFAULT 0 NOT NULL ); Index: /trunk/src/VBox/ValidationKit/testmanager/db/tmdb-r22-testboxes-3-teststatus-4-testboxinschedgroups-1.pgsql =================================================================== --- /trunk/src/VBox/ValidationKit/testmanager/db/tmdb-r22-testboxes-3-teststatus-4-testboxinschedgroups-1.pgsql (revision 61507) +++ /trunk/src/VBox/ValidationKit/testmanager/db/tmdb-r22-testboxes-3-teststatus-4-testboxinschedgroups-1.pgsql (revision 61507) @@ -0,0 +1,171 @@ +-- $Id$ +--- @file +-- VBox Test Manager Database - Turns idSchedGroup column in TestBoxes +-- into an N:M relationship with a priority via the new table +-- TestBoxesInSchedGroups. Adds an internal scheduling table index to +-- TestBoxStatuses to implement testboxes switching between groups. +-- + +-- +-- Copyright (C) 2013-2016 Oracle Corporation +-- +-- This file is part of VirtualBox Open Source Edition (OSE), as +-- available from http://www.virtualbox.org. This file is free software; +-- you can redistribute it and/or modify it under the terms of the GNU +-- General Public License (GPL) as published by the Free Software +-- Foundation, in version 2 as it comes in the "COPYING" file of the +-- VirtualBox OSE distribution. VirtualBox OSE is distributed in the +-- hope that it will be useful, but WITHOUT ANY WARRANTY of any kind. +-- +-- The contents of this file may alternatively be used under the terms +-- of the Common Development and Distribution License Version 1.0 +-- (CDDL) only, as it comes in the "COPYING.CDDL" file of the +-- VirtualBox OSE distribution, in which case the provisions of the +-- CDDL are applicable instead of those of the GPL. +-- +-- You may elect to license modified versions of this file under the +-- terms and conditions of either the GPL or the CDDL or both. +-- + +-- +-- Cleanup after failed runs. +-- +DROP TABLE IF EXISTS OldTestBoxes; + +-- +-- Die on error from now on. +-- +\set ON_ERROR_STOP 1 +\set AUTOCOMMIT 0 + + +-- Total grid lock. +LOCK TABLE TestBoxStatuses IN ACCESS EXCLUSIVE MODE; +LOCK TABLE TestSets IN ACCESS EXCLUSIVE MODE; +LOCK TABLE TestBoxes IN ACCESS EXCLUSIVE MODE; +LOCK TABLE SchedGroups IN ACCESS EXCLUSIVE MODE; +LOCK TABLE SchedGroupMembers IN ACCESS EXCLUSIVE MODE; + +\d+ TestBoxes; + +-- +-- We'll only be doing simple alterations so, no need to drop constraints +-- and stuff like we usually do first. +-- + +-- +-- Create the new table and populate it. +-- + +CREATE TABLE TestBoxesInSchedGroups ( + --- TestBox ID. + -- Non-unique foreign key: TestBoxes(idTestBox). + idTestBox INTEGER NOT NULL, + --- Scheduling ID. + -- Non-unique foreign key: SchedGroups(idSchedGroup). + idSchedGroup INTEGER NOT NULL, + --- When this row starts taking effect (inclusive). + tsEffective TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp NOT NULL, + --- When this row stops being tsEffective (exclusive). + tsExpire TIMESTAMP WITH TIME ZONE DEFAULT TIMESTAMP WITH TIME ZONE 'infinity' NOT NULL, + --- The user id of the one who created/modified this entry. + -- Non-unique foreign key: Users(uid) + uidAuthor INTEGER NOT NULL, + + --- The scheduling priority of the scheduling group for the test box. + -- Higher number causes the scheduling group to be serviced more frequently. + -- @sa TestGroupMembers.iSchedPriority, SchedGroups.iSchedPriority + iSchedPriority INTEGER DEFAULT 16 CHECK (iSchedPriority >= 0 AND iSchedPriority < 32) NOT NULL, + + PRIMARY KEY (idTestBox, idSchedGroup, tsExpire) +); + +GRANT ALL PRIVILEGES ON TABLE TestBoxesInSchedGroups TO testmanager; + +CREATE OR REPLACE FUNCTION TestBoxesInSchedGroups_ConvertedOneBox(a_idTestBox INTEGER) + RETURNS VOID AS $$ + DECLARE + v_Row RECORD; + v_idSchedGroup INTEGER; + v_uidAuthor INTEGER; + v_tsEffective TIMESTAMP WITH TIME ZONE; + v_tsExpire TIMESTAMP WITH TIME ZONE; + BEGIN + FOR v_Row IN + SELECT idTestBox, + idSchedGroup, + tsEffective, + tsExpire, + uidAuthor + FROM TestBoxes + WHERE idTestBox = a_idTestBox + ORDER BY tsEffective, tsExpire + LOOP + IF v_idSchedGroup IS NOT NULL THEN + IF (v_idSchedGroup != v_Row.idSchedGroup) OR (v_Row.tsEffective <> v_tsExpire) THEN + INSERT INTO TestBoxesInSchedGroups (idTestBox, idSchedGroup, tsEffective, tsExpire, uidAuthor) + VALUES (a_idTestBox, v_idSchedGroup, v_tsEffective, v_tsExpire, v_uidAuthor); + v_idSchedGroup := NULL; + END IF; + END IF; + + IF v_idSchedGroup IS NULL THEN + v_idSchedGroup := v_Row.idSchedGroup; + v_tsEffective := v_Row.tsEffective; + END IF; + IF v_Row.uidAuthor IS NOT NULL THEN + v_uidAuthor := v_Row.uidAuthor; + END IF; + v_tsExpire := v_Row.tsExpire; + END LOOP; + + IF v_idSchedGroup != -1 THEN + INSERT INTO TestBoxesInSchedGroups (idTestBox, idSchedGroup, tsEffective, tsExpire, uidAuthor) + VALUES (a_idTestBox, v_idSchedGroup, v_tsEffective, v_tsExpire, v_uidAuthor); + END IF; + END; +$$ LANGUAGE plpgsql; + +SELECT TestBoxesInSchedGroups_ConvertedOneBox(TestBoxIDs.idTestBox) +FROM ( SELECT DISTINCT idTestBox FROM TestBoxes ) AS TestBoxIDs; + +DROP FUNCTION TestBoxesInSchedGroups_ConvertedOneBox(INTEGER); + +-- +-- Do the other two modifications. +-- +ALTER TABLE TestBoxStatuses ADD COLUMN iWorkItem INTEGER DEFAULT 0 NOT NULL; + +DROP VIEW TestBoxesWithStrings; +ALTER TABLE TestBoxes DROP COLUMN idSchedGroup; +CREATE VIEW TestBoxesWithStrings AS + SELECT TestBoxes.*, + Str1.sValue AS sDescription, + Str2.sValue AS sComment, + Str3.sValue AS sOs, + Str4.sValue AS sOsVersion, + Str5.sValue AS sCpuVendor, + Str6.sValue AS sCpuArch, + Str7.sValue AS sCpuName, + Str8.sValue AS sReport + FROM TestBoxes + LEFT OUTER JOIN TestBoxStrTab Str1 ON idStrDescription = Str1.idStr + LEFT OUTER JOIN TestBoxStrTab Str2 ON idStrComment = Str2.idStr + LEFT OUTER JOIN TestBoxStrTab Str3 ON idStrOs = Str3.idStr + LEFT OUTER JOIN TestBoxStrTab Str4 ON idStrOsVersion = Str4.idStr + LEFT OUTER JOIN TestBoxStrTab Str5 ON idStrCpuVendor = Str5.idStr + LEFT OUTER JOIN TestBoxStrTab Str6 ON idStrCpuArch = Str6.idStr + LEFT OUTER JOIN TestBoxStrTab Str7 ON idStrCpuName = Str7.idStr + LEFT OUTER JOIN TestBoxStrTab Str8 ON idStrReport = Str8.idStr; + +GRANT ALL PRIVILEGES ON TABLE TestBoxesWithStrings TO testmanager; + +\prompt "Update python files while everything is locked. Hurry!" dummy + +COMMIT; + +\d TestBoxesInSchedGroups; +\d TestBoxStatuses; +\d TestBoxes; +ANALYZE VERBOSE TestBoxesInSchedGroups; +