1 -- Copyright 2013 The Kyua Authors.
2 -- All rights reserved.
4 -- Redistribution and use in source and binary forms, with or without
5 -- modification, are permitted provided that the following conditions are
8 -- * Redistributions of source code must retain the above copyright
9 -- notice, this list of conditions and the following disclaimer.
10 -- * Redistributions in binary form must reproduce the above copyright
11 -- notice, this list of conditions and the following disclaimer in the
12 -- documentation and/or other materials provided with the distribution.
13 -- * Neither the name of Google Inc. nor the names of its contributors
14 -- may be used to endorse or promote products derived from this software
15 -- without specific prior written permission.
17 -- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
18 -- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
19 -- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
20 -- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
21 -- OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
22 -- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
23 -- LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
24 -- DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
25 -- THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26 -- (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
27 -- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29 -- \file store/v1-to-v2.sql
30 -- Migration of a database with version 1 of the schema to version 2.
32 -- Version 2 appeared in revision 9a73561a1e3975bba4cbfd19aee6b2365a39519e
33 -- and its changes were:
35 -- * Changed the primary key of the metadata table to be the
36 -- schema_version, not the timestamp. Because timestamps only have
37 -- second resolution, the old schema made testing of schema migrations
40 -- * Introduced the metadatas table, which holds the metadata of all test
41 -- programs and test cases in an abstract manner regardless of their
44 -- * Added the metadata_id field to the test_programs and test_cases
45 -- tables, referencing the new metadatas table.
47 -- * Changed the precision of the timeout metadata field to be in seconds
48 -- rather than in microseconds. There is no data loss, and the code that
49 -- writes the metadata is simplified.
51 -- * Removed the atf_* and plain_* tables.
53 -- * Added missing indexes to improve the performance of reports.
55 -- * Added missing column affinities to the absolute_path and relative_path
56 -- columns of the test_programs table.
59 -- TODO(jmmv): Implement addition of missing affinities.
63 -- Change primary key of the metadata table.
67 CREATE TABLE new_metadata (
68 schema_version INTEGER PRIMARY KEY CHECK (schema_version >= 1),
69 timestamp TIMESTAMP NOT NULL CHECK (timestamp >= 0)
72 INSERT INTO new_metadata (schema_version, timestamp)
73 SELECT schema_version, timestamp FROM metadata;
76 ALTER TABLE new_metadata RENAME TO metadata;
80 -- Add the new tables, columns and indexes.
84 CREATE TABLE metadatas (
85 metadata_id INTEGER NOT NULL,
86 property_name TEXT NOT NULL,
89 PRIMARY KEY (metadata_id, property_name)
93 -- Upgrade the test_programs table by adding missing column affinities and
94 -- the new metadata_id column.
95 CREATE TABLE new_test_programs (
96 test_program_id INTEGER PRIMARY KEY AUTOINCREMENT,
97 action_id INTEGER REFERENCES actions,
99 absolute_path TEXT NOT NULL,
101 relative_path TEXT NOT NULL,
102 test_suite_name TEXT NOT NULL,
104 interface TEXT NOT NULL
106 PRAGMA foreign_keys = OFF;
107 INSERT INTO new_test_programs (test_program_id, action_id, absolute_path,
108 root, relative_path, test_suite_name,
110 SELECT test_program_id, action_id, absolute_path, root, relative_path,
111 test_suite_name, interface FROM test_programs;
112 DROP TABLE test_programs;
113 ALTER TABLE new_test_programs RENAME TO test_programs;
114 PRAGMA foreign_keys = ON;
117 ALTER TABLE test_cases ADD COLUMN metadata_id INTEGER;
120 CREATE INDEX index_metadatas_by_id
121 ON metadatas (metadata_id);
122 CREATE INDEX index_test_programs_by_action_id
123 ON test_programs (action_id);
124 CREATE INDEX index_test_cases_by_test_programs_id
125 ON test_cases (test_program_id);
131 -- This is, by far, the trickiest part of the migration.
132 -- TODO(jmmv): Describe the trickiness in here.
136 -- Auxiliary table to construct the final contents of the metadatas table.
138 -- We construct the contents by writing a row for every metadata property of
139 -- every test program and test case. Entries corresponding to a test program
140 -- will have the test_program_id field set to not NULL and entries corresponding
141 -- to test cases will have the test_case_id set to not NULL.
143 -- The tricky part, however, is to create the individual identifiers for every
144 -- metadata entry. We do this by picking the minimum ROWID of a particular set
145 -- of properties that map to a single test_program_id or test_case_id.
146 CREATE TABLE tmp_metadatas (
147 test_program_id INTEGER DEFAULT NULL,
148 test_case_id INTEGER DEFAULT NULL,
149 interface TEXT NOT NULL,
150 property_name TEXT NOT NULL,
151 property_value TEXT NOT NULL,
153 UNIQUE (test_program_id, test_case_id, property_name)
155 CREATE INDEX index_tmp_metadatas_by_test_case_id
156 ON tmp_metadatas (test_case_id);
157 CREATE INDEX index_tmp_metadatas_by_test_program_id
158 ON tmp_metadatas (test_program_id);
161 -- Populate default metadata values for all test programs and test cases.
163 -- We do this first to ensure that all test programs and test cases have
164 -- explicit values for their metadata. Because we want to keep historical data
165 -- for the tests, we must record these values unconditionally instead of relying
166 -- on the built-in values in the code.
168 -- Once this is done, we override any values explicity set by the tests.
169 CREATE TABLE tmp_default_metadata (
170 default_name TEXT PRIMARY KEY,
171 default_value TEXT NOT NULL
173 INSERT INTO tmp_default_metadata VALUES ('allowed_architectures', '');
174 INSERT INTO tmp_default_metadata VALUES ('allowed_platforms', '');
175 INSERT INTO tmp_default_metadata VALUES ('description', '');
176 INSERT INTO tmp_default_metadata VALUES ('has_cleanup', 'false');
177 INSERT INTO tmp_default_metadata VALUES ('required_configs', '');
178 INSERT INTO tmp_default_metadata VALUES ('required_files', '');
179 INSERT INTO tmp_default_metadata VALUES ('required_memory', '0');
180 INSERT INTO tmp_default_metadata VALUES ('required_programs', '');
181 INSERT INTO tmp_default_metadata VALUES ('required_user', '');
182 INSERT INTO tmp_default_metadata VALUES ('timeout', '300');
183 INSERT INTO tmp_metadatas
184 SELECT test_program_id, NULL, interface, default_name, default_value
185 FROM test_programs JOIN tmp_default_metadata;
186 INSERT INTO tmp_metadatas
187 SELECT NULL, test_case_id, interface, default_name, default_value
188 FROM test_programs JOIN test_cases
189 ON test_cases.test_program_id = test_programs.test_program_id
190 JOIN tmp_default_metadata;
191 DROP TABLE tmp_default_metadata;
194 -- Populate metadata overrides from plain test programs.
196 SET property_value = (
197 SELECT CAST(timeout / 1000000 AS TEXT) FROM plain_test_programs AS aux
198 WHERE aux.test_program_id = tmp_metadatas.test_program_id)
199 WHERE test_program_id IS NOT NULL AND property_name = 'timeout'
200 AND interface = 'plain';
202 SET property_value = (
203 SELECT DISTINCT CAST(timeout / 1000000 AS TEXT)
204 FROM test_cases AS aux JOIN plain_test_programs
205 ON aux.test_program_id == plain_test_programs.test_program_id
206 WHERE aux.test_case_id = tmp_metadatas.test_case_id)
207 WHERE test_case_id IS NOT NULL AND property_name = 'timeout'
208 AND interface = 'plain';
211 CREATE INDEX index_tmp_atf_test_cases_multivalues_by_test_case_id
212 ON atf_test_cases_multivalues (test_case_id);
215 -- Populate metadata overrides from ATF test cases.
216 UPDATE atf_test_cases SET description = '' WHERE description IS NULL;
217 UPDATE atf_test_cases SET required_user = '' WHERE required_user IS NULL;
220 SET property_value = (
221 SELECT description FROM atf_test_cases AS aux
222 WHERE aux.test_case_id = tmp_metadatas.test_case_id)
223 WHERE test_case_id IS NOT NULL AND property_name = 'description'
224 AND interface = 'atf';
226 SET property_value = (
227 SELECT has_cleanup FROM atf_test_cases AS aux
228 WHERE aux.test_case_id = tmp_metadatas.test_case_id)
229 WHERE test_case_id IS NOT NULL AND property_name = 'has_cleanup'
230 AND interface = 'atf';
232 SET property_value = (
233 SELECT CAST(timeout / 1000000 AS TEXT) FROM atf_test_cases AS aux
234 WHERE aux.test_case_id = tmp_metadatas.test_case_id)
235 WHERE test_case_id IS NOT NULL AND property_name = 'timeout'
236 AND interface = 'atf';
238 SET property_value = (
239 SELECT CAST(required_memory AS TEXT) FROM atf_test_cases AS aux
240 WHERE aux.test_case_id = tmp_metadatas.test_case_id)
241 WHERE test_case_id IS NOT NULL AND property_name = 'required_memory'
242 AND interface = 'atf';
244 SET property_value = (
245 SELECT required_user FROM atf_test_cases AS aux
246 WHERE aux.test_case_id = tmp_metadatas.test_case_id)
247 WHERE test_case_id IS NOT NULL AND property_name = 'required_user'
248 AND interface = 'atf';
250 SET property_value = (
251 SELECT GROUP_CONCAT(aux.property_value, ' ')
252 FROM atf_test_cases_multivalues AS aux
253 WHERE aux.test_case_id = tmp_metadatas.test_case_id AND
254 aux.property_name = 'require.arch')
255 WHERE test_case_id IS NOT NULL AND property_name = 'allowed_architectures'
256 AND interface = 'atf'
257 AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux
258 WHERE aux.test_case_id = tmp_metadatas.test_case_id
259 AND property_name = 'require.arch');
261 SET property_value = (
262 SELECT GROUP_CONCAT(aux.property_value, ' ')
263 FROM atf_test_cases_multivalues AS aux
264 WHERE aux.test_case_id = tmp_metadatas.test_case_id AND
265 aux.property_name = 'require.machine')
266 WHERE test_case_id IS NOT NULL AND property_name = 'allowed_platforms'
267 AND interface = 'atf'
268 AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux
269 WHERE aux.test_case_id = tmp_metadatas.test_case_id
270 AND property_name = 'require.machine');
272 SET property_value = (
273 SELECT GROUP_CONCAT(aux.property_value, ' ')
274 FROM atf_test_cases_multivalues AS aux
275 WHERE aux.test_case_id = tmp_metadatas.test_case_id AND
276 aux.property_name = 'require.config')
277 WHERE test_case_id IS NOT NULL AND property_name = 'required_configs'
278 AND interface = 'atf'
279 AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux
280 WHERE aux.test_case_id = tmp_metadatas.test_case_id
281 AND property_name = 'require.config');
283 SET property_value = (
284 SELECT GROUP_CONCAT(aux.property_value, ' ')
285 FROM atf_test_cases_multivalues AS aux
286 WHERE aux.test_case_id = tmp_metadatas.test_case_id AND
287 aux.property_name = 'require.files')
288 WHERE test_case_id IS NOT NULL AND property_name = 'required_files'
289 AND interface = 'atf'
290 AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux
291 WHERE aux.test_case_id = tmp_metadatas.test_case_id
292 AND property_name = 'require.files');
294 SET property_value = (
295 SELECT GROUP_CONCAT(aux.property_value, ' ')
296 FROM atf_test_cases_multivalues AS aux
297 WHERE aux.test_case_id = tmp_metadatas.test_case_id AND
298 aux.property_name = 'require.progs')
299 WHERE test_case_id IS NOT NULL AND property_name = 'required_programs'
300 AND interface = 'atf'
301 AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux
302 WHERE aux.test_case_id = tmp_metadatas.test_case_id
303 AND property_name = 'require.progs');
306 -- Fill metadata_id pointers in the test_programs and test_cases tables.
309 SELECT MIN(ROWID) FROM tmp_metadatas
310 WHERE tmp_metadatas.test_program_id = test_programs.test_program_id
314 SELECT MIN(ROWID) FROM tmp_metadatas
315 WHERE tmp_metadatas.test_case_id = test_cases.test_case_id
319 -- Populate the metadatas table based on tmp_metadatas.
320 INSERT INTO metadatas (metadata_id, property_name, property_value)
322 SELECT MIN(ROWID) FROM tmp_metadatas AS s
323 WHERE s.test_program_id = tmp_metadatas.test_program_id
324 ), property_name, property_value
325 FROM tmp_metadatas WHERE test_program_id IS NOT NULL;
326 INSERT INTO metadatas (metadata_id, property_name, property_value)
328 SELECT MIN(ROWID) FROM tmp_metadatas AS s
329 WHERE s.test_case_id = tmp_metadatas.test_case_id
330 ), property_name, property_value
331 FROM tmp_metadatas WHERE test_case_id IS NOT NULL;
334 -- Drop temporary entities used during the migration.
335 DROP INDEX index_tmp_atf_test_cases_multivalues_by_test_case_id;
336 DROP INDEX index_tmp_metadatas_by_test_program_id;
337 DROP INDEX index_tmp_metadatas_by_test_case_id;
338 DROP TABLE tmp_metadatas;
342 -- Drop obsolete tables.
346 DROP TABLE atf_test_cases;
347 DROP TABLE atf_test_cases_multivalues;
348 DROP TABLE plain_test_programs;
352 -- Update the metadata version.
356 INSERT INTO metadata (timestamp, schema_version)
357 VALUES (strftime('%s', 'now'), 2);