diff options
Diffstat (limited to 'store/migrate_v1_v2.sql')
| -rw-r--r-- | store/migrate_v1_v2.sql | 357 |
1 files changed, 357 insertions, 0 deletions
diff --git a/store/migrate_v1_v2.sql b/store/migrate_v1_v2.sql new file mode 100644 index 000000000000..52d2f6a8e00c --- /dev/null +++ b/store/migrate_v1_v2.sql @@ -0,0 +1,357 @@ +-- Copyright 2013 The Kyua Authors. +-- All rights reserved. +-- +-- Redistribution and use in source and binary forms, with or without +-- modification, are permitted provided that the following conditions are +-- met: +-- +-- * Redistributions of source code must retain the above copyright +-- notice, this list of conditions and the following disclaimer. +-- * Redistributions in binary form must reproduce the above copyright +-- notice, this list of conditions and the following disclaimer in the +-- documentation and/or other materials provided with the distribution. +-- * Neither the name of Google Inc. nor the names of its contributors +-- may be used to endorse or promote products derived from this software +-- without specific prior written permission. +-- +-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS +-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT +-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR +-- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT +-- OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, +-- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT +-- LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, +-- DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY +-- THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT +-- (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE +-- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + +-- \file store/v1-to-v2.sql +-- Migration of a database with version 1 of the schema to version 2. +-- +-- Version 2 appeared in revision 9a73561a1e3975bba4cbfd19aee6b2365a39519e +-- and its changes were: +-- +-- * Changed the primary key of the metadata table to be the +-- schema_version, not the timestamp. Because timestamps only have +-- second resolution, the old schema made testing of schema migrations +-- difficult. +-- +-- * Introduced the metadatas table, which holds the metadata of all test +-- programs and test cases in an abstract manner regardless of their +-- interface. +-- +-- * Added the metadata_id field to the test_programs and test_cases +-- tables, referencing the new metadatas table. +-- +-- * Changed the precision of the timeout metadata field to be in seconds +-- rather than in microseconds. There is no data loss, and the code that +-- writes the metadata is simplified. +-- +-- * Removed the atf_* and plain_* tables. +-- +-- * Added missing indexes to improve the performance of reports. +-- +-- * Added missing column affinities to the absolute_path and relative_path +-- columns of the test_programs table. + + +-- TODO(jmmv): Implement addition of missing affinities. + + +-- +-- Change primary key of the metadata table. +-- + + +CREATE TABLE new_metadata ( + schema_version INTEGER PRIMARY KEY CHECK (schema_version >= 1), + timestamp TIMESTAMP NOT NULL CHECK (timestamp >= 0) +); + +INSERT INTO new_metadata (schema_version, timestamp) + SELECT schema_version, timestamp FROM metadata; + +DROP TABLE metadata; +ALTER TABLE new_metadata RENAME TO metadata; + + +-- +-- Add the new tables, columns and indexes. +-- + + +CREATE TABLE metadatas ( + metadata_id INTEGER NOT NULL, + property_name TEXT NOT NULL, + property_value TEXT, + + PRIMARY KEY (metadata_id, property_name) +); + + +-- Upgrade the test_programs table by adding missing column affinities and +-- the new metadata_id column. +CREATE TABLE new_test_programs ( + test_program_id INTEGER PRIMARY KEY AUTOINCREMENT, + action_id INTEGER REFERENCES actions, + + absolute_path TEXT NOT NULL, + root TEXT NOT NULL, + relative_path TEXT NOT NULL, + test_suite_name TEXT NOT NULL, + metadata_id INTEGER, + interface TEXT NOT NULL +); +PRAGMA foreign_keys = OFF; +INSERT INTO new_test_programs (test_program_id, action_id, absolute_path, + root, relative_path, test_suite_name, + interface) + SELECT test_program_id, action_id, absolute_path, root, relative_path, + test_suite_name, interface FROM test_programs; +DROP TABLE test_programs; +ALTER TABLE new_test_programs RENAME TO test_programs; +PRAGMA foreign_keys = ON; + + +ALTER TABLE test_cases ADD COLUMN metadata_id INTEGER; + + +CREATE INDEX index_metadatas_by_id + ON metadatas (metadata_id); +CREATE INDEX index_test_programs_by_action_id + ON test_programs (action_id); +CREATE INDEX index_test_cases_by_test_programs_id + ON test_cases (test_program_id); + + +-- +-- Data migration +-- +-- This is, by far, the trickiest part of the migration. +-- TODO(jmmv): Describe the trickiness in here. +-- + + +-- Auxiliary table to construct the final contents of the metadatas table. +-- +-- We construct the contents by writing a row for every metadata property of +-- every test program and test case. Entries corresponding to a test program +-- will have the test_program_id field set to not NULL and entries corresponding +-- to test cases will have the test_case_id set to not NULL. +-- +-- The tricky part, however, is to create the individual identifiers for every +-- metadata entry. We do this by picking the minimum ROWID of a particular set +-- of properties that map to a single test_program_id or test_case_id. +CREATE TABLE tmp_metadatas ( + test_program_id INTEGER DEFAULT NULL, + test_case_id INTEGER DEFAULT NULL, + interface TEXT NOT NULL, + property_name TEXT NOT NULL, + property_value TEXT NOT NULL, + + UNIQUE (test_program_id, test_case_id, property_name) +); +CREATE INDEX index_tmp_metadatas_by_test_case_id + ON tmp_metadatas (test_case_id); +CREATE INDEX index_tmp_metadatas_by_test_program_id + ON tmp_metadatas (test_program_id); + + +-- Populate default metadata values for all test programs and test cases. +-- +-- We do this first to ensure that all test programs and test cases have +-- explicit values for their metadata. Because we want to keep historical data +-- for the tests, we must record these values unconditionally instead of relying +-- on the built-in values in the code. +-- +-- Once this is done, we override any values explicity set by the tests. +CREATE TABLE tmp_default_metadata ( + default_name TEXT PRIMARY KEY, + default_value TEXT NOT NULL +); +INSERT INTO tmp_default_metadata VALUES ('allowed_architectures', ''); +INSERT INTO tmp_default_metadata VALUES ('allowed_platforms', ''); +INSERT INTO tmp_default_metadata VALUES ('description', ''); +INSERT INTO tmp_default_metadata VALUES ('has_cleanup', 'false'); +INSERT INTO tmp_default_metadata VALUES ('required_configs', ''); +INSERT INTO tmp_default_metadata VALUES ('required_files', ''); +INSERT INTO tmp_default_metadata VALUES ('required_memory', '0'); +INSERT INTO tmp_default_metadata VALUES ('required_programs', ''); +INSERT INTO tmp_default_metadata VALUES ('required_user', ''); +INSERT INTO tmp_default_metadata VALUES ('timeout', '300'); +INSERT INTO tmp_metadatas + SELECT test_program_id, NULL, interface, default_name, default_value + FROM test_programs JOIN tmp_default_metadata; +INSERT INTO tmp_metadatas + SELECT NULL, test_case_id, interface, default_name, default_value + FROM test_programs JOIN test_cases + ON test_cases.test_program_id = test_programs.test_program_id + JOIN tmp_default_metadata; +DROP TABLE tmp_default_metadata; + + +-- Populate metadata overrides from plain test programs. +UPDATE tmp_metadatas + SET property_value = ( + SELECT CAST(timeout / 1000000 AS TEXT) FROM plain_test_programs AS aux + WHERE aux.test_program_id = tmp_metadatas.test_program_id) + WHERE test_program_id IS NOT NULL AND property_name = 'timeout' + AND interface = 'plain'; +UPDATE tmp_metadatas + SET property_value = ( + SELECT DISTINCT CAST(timeout / 1000000 AS TEXT) + FROM test_cases AS aux JOIN plain_test_programs + ON aux.test_program_id == plain_test_programs.test_program_id + WHERE aux.test_case_id = tmp_metadatas.test_case_id) + WHERE test_case_id IS NOT NULL AND property_name = 'timeout' + AND interface = 'plain'; + + +CREATE INDEX index_tmp_atf_test_cases_multivalues_by_test_case_id + ON atf_test_cases_multivalues (test_case_id); + + +-- Populate metadata overrides from ATF test cases. +UPDATE atf_test_cases SET description = '' WHERE description IS NULL; +UPDATE atf_test_cases SET required_user = '' WHERE required_user IS NULL; + +UPDATE tmp_metadatas + SET property_value = ( + SELECT description FROM atf_test_cases AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id) + WHERE test_case_id IS NOT NULL AND property_name = 'description' + AND interface = 'atf'; +UPDATE tmp_metadatas + SET property_value = ( + SELECT has_cleanup FROM atf_test_cases AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id) + WHERE test_case_id IS NOT NULL AND property_name = 'has_cleanup' + AND interface = 'atf'; +UPDATE tmp_metadatas + SET property_value = ( + SELECT CAST(timeout / 1000000 AS TEXT) FROM atf_test_cases AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id) + WHERE test_case_id IS NOT NULL AND property_name = 'timeout' + AND interface = 'atf'; +UPDATE tmp_metadatas + SET property_value = ( + SELECT CAST(required_memory AS TEXT) FROM atf_test_cases AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id) + WHERE test_case_id IS NOT NULL AND property_name = 'required_memory' + AND interface = 'atf'; +UPDATE tmp_metadatas + SET property_value = ( + SELECT required_user FROM atf_test_cases AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id) + WHERE test_case_id IS NOT NULL AND property_name = 'required_user' + AND interface = 'atf'; +UPDATE tmp_metadatas + SET property_value = ( + SELECT GROUP_CONCAT(aux.property_value, ' ') + FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id AND + aux.property_name = 'require.arch') + WHERE test_case_id IS NOT NULL AND property_name = 'allowed_architectures' + AND interface = 'atf' + AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id + AND property_name = 'require.arch'); +UPDATE tmp_metadatas + SET property_value = ( + SELECT GROUP_CONCAT(aux.property_value, ' ') + FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id AND + aux.property_name = 'require.machine') + WHERE test_case_id IS NOT NULL AND property_name = 'allowed_platforms' + AND interface = 'atf' + AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id + AND property_name = 'require.machine'); +UPDATE tmp_metadatas + SET property_value = ( + SELECT GROUP_CONCAT(aux.property_value, ' ') + FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id AND + aux.property_name = 'require.config') + WHERE test_case_id IS NOT NULL AND property_name = 'required_configs' + AND interface = 'atf' + AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id + AND property_name = 'require.config'); +UPDATE tmp_metadatas + SET property_value = ( + SELECT GROUP_CONCAT(aux.property_value, ' ') + FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id AND + aux.property_name = 'require.files') + WHERE test_case_id IS NOT NULL AND property_name = 'required_files' + AND interface = 'atf' + AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id + AND property_name = 'require.files'); +UPDATE tmp_metadatas + SET property_value = ( + SELECT GROUP_CONCAT(aux.property_value, ' ') + FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id AND + aux.property_name = 'require.progs') + WHERE test_case_id IS NOT NULL AND property_name = 'required_programs' + AND interface = 'atf' + AND EXISTS(SELECT 1 FROM atf_test_cases_multivalues AS aux + WHERE aux.test_case_id = tmp_metadatas.test_case_id + AND property_name = 'require.progs'); + + +-- Fill metadata_id pointers in the test_programs and test_cases tables. +UPDATE test_programs + SET metadata_id = ( + SELECT MIN(ROWID) FROM tmp_metadatas + WHERE tmp_metadatas.test_program_id = test_programs.test_program_id + ); +UPDATE test_cases + SET metadata_id = ( + SELECT MIN(ROWID) FROM tmp_metadatas + WHERE tmp_metadatas.test_case_id = test_cases.test_case_id + ); + + +-- Populate the metadatas table based on tmp_metadatas. +INSERT INTO metadatas (metadata_id, property_name, property_value) + SELECT ( + SELECT MIN(ROWID) FROM tmp_metadatas AS s + WHERE s.test_program_id = tmp_metadatas.test_program_id + ), property_name, property_value + FROM tmp_metadatas WHERE test_program_id IS NOT NULL; +INSERT INTO metadatas (metadata_id, property_name, property_value) + SELECT ( + SELECT MIN(ROWID) FROM tmp_metadatas AS s + WHERE s.test_case_id = tmp_metadatas.test_case_id + ), property_name, property_value + FROM tmp_metadatas WHERE test_case_id IS NOT NULL; + + +-- Drop temporary entities used during the migration. +DROP INDEX index_tmp_atf_test_cases_multivalues_by_test_case_id; +DROP INDEX index_tmp_metadatas_by_test_program_id; +DROP INDEX index_tmp_metadatas_by_test_case_id; +DROP TABLE tmp_metadatas; + + +-- +-- Drop obsolete tables. +-- + + +DROP TABLE atf_test_cases; +DROP TABLE atf_test_cases_multivalues; +DROP TABLE plain_test_programs; + + +-- +-- Update the metadata version. +-- + + +INSERT INTO metadata (timestamp, schema_version) + VALUES (strftime('%s', 'now'), 2); |