1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
-- $Id: tmdb-r22-testboxes-3-teststatus-4-testboxinschedgroups-1.pgsql $
--- @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-2019 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;
|
