MFV: r361597

[FreeBSD/FreeBSD.git] / contrib / kyua / store / migrate_v1_v2.sql

-- 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);