1-- Copyright 2014 The Kyua Authors. 2-- All rights reserved. 3-- 4-- Redistribution and use in source and binary forms, with or without 5-- modification, are permitted provided that the following conditions are 6-- met: 7-- 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. 16-- 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. 28 29-- \file store/v2-to-v3.sql 30-- Migration of a database with version 2 of the schema to version 3. 31-- 32-- Version 3 appeared in revision 084d740b1da635946d153475156e335ddfc4aed6 33-- and its changes were: 34-- 35-- * Removal of historical data. 36-- 37-- Because from v2 to v3 we went from a unified database to many separate 38-- databases, this file is parameterized on @ACTION_ID@. The file has to 39-- be executed once per action with this string replaced. 40 41 42ATTACH DATABASE "@OLD_DATABASE@" AS old_store; 43 44 45-- New database already contains a record for v3. Just import older entries. 46INSERT INTO metadata SELECT * FROM old_store.metadata; 47 48INSERT INTO contexts 49 SELECT cwd 50 FROM old_store.actions 51 NATURAL JOIN old_store.contexts 52 WHERE action_id == @ACTION_ID@; 53 54INSERT INTO env_vars 55 SELECT var_name, var_value 56 FROM old_store.actions 57 NATURAL JOIN old_store.contexts 58 NATURAL JOIN old_store.env_vars 59 WHERE action_id == @ACTION_ID@; 60 61INSERT INTO metadatas 62 SELECT metadata_id, property_name, property_value 63 FROM old_store.metadatas 64 WHERE metadata_id IN ( 65 SELECT test_programs.metadata_id 66 FROM old_store.test_programs 67 WHERE action_id == @ACTION_ID@ 68 ) OR metadata_id IN ( 69 SELECT test_cases.metadata_id 70 FROM old_store.test_programs JOIN old_store.test_cases 71 ON test_programs.test_program_id == test_cases.test_program_id 72 WHERE action_id == @ACTION_ID@ 73 ); 74 75INSERT INTO test_programs 76 SELECT test_program_id, absolute_path, root, relative_path, 77 test_suite_name, metadata_id, interface 78 FROM old_store.test_programs 79 WHERE action_id == @ACTION_ID@; 80 81INSERT INTO test_cases 82 SELECT test_cases.test_case_id, test_cases.test_program_id, 83 test_cases.name, test_cases.metadata_id 84 FROM old_store.test_cases JOIN old_store.test_programs 85 ON test_cases.test_program_id == test_programs.test_program_id 86 WHERE action_id == @ACTION_ID@; 87 88INSERT INTO test_results 89 SELECT test_results.test_case_id, test_results.result_type, 90 test_results.result_reason, test_results.start_time, test_results.end_time 91 FROM old_store.test_results 92 JOIN old_store.test_cases 93 ON test_results.test_case_id == test_cases.test_case_id 94 JOIN old_store.test_programs 95 ON test_cases.test_program_id == test_programs.test_program_id 96 WHERE action_id == @ACTION_ID@; 97 98INSERT INTO files 99 SELECT files.file_id, files.contents 100 FROM old_store.files 101 JOIN old_store.test_case_files 102 ON files.file_id == test_case_files.file_id 103 JOIN old_store.test_cases 104 ON test_case_files.test_case_id == test_cases.test_case_id 105 JOIN old_store.test_programs 106 ON test_cases.test_program_id == test_programs.test_program_id 107 WHERE action_id == @ACTION_ID@; 108 109INSERT INTO test_case_files 110 SELECT test_case_files.test_case_id, test_case_files.file_name, 111 test_case_files.file_id 112 FROM old_store.test_case_files 113 JOIN old_store.test_cases 114 ON test_case_files.test_case_id == test_cases.test_case_id 115 JOIN old_store.test_programs 116 ON test_cases.test_program_id == test_programs.test_program_id 117 WHERE action_id == @ACTION_ID@; 118 119 120DETACH DATABASE old_store; 121