1*b0d29bc4SBrooks Davis-- Copyright 2014 The Kyua Authors. 2*b0d29bc4SBrooks Davis-- All rights reserved. 3*b0d29bc4SBrooks Davis-- 4*b0d29bc4SBrooks Davis-- Redistribution and use in source and binary forms, with or without 5*b0d29bc4SBrooks Davis-- modification, are permitted provided that the following conditions are 6*b0d29bc4SBrooks Davis-- met: 7*b0d29bc4SBrooks Davis-- 8*b0d29bc4SBrooks Davis-- * Redistributions of source code must retain the above copyright 9*b0d29bc4SBrooks Davis-- notice, this list of conditions and the following disclaimer. 10*b0d29bc4SBrooks Davis-- * Redistributions in binary form must reproduce the above copyright 11*b0d29bc4SBrooks Davis-- notice, this list of conditions and the following disclaimer in the 12*b0d29bc4SBrooks Davis-- documentation and/or other materials provided with the distribution. 13*b0d29bc4SBrooks Davis-- * Neither the name of Google Inc. nor the names of its contributors 14*b0d29bc4SBrooks Davis-- may be used to endorse or promote products derived from this software 15*b0d29bc4SBrooks Davis-- without specific prior written permission. 16*b0d29bc4SBrooks Davis-- 17*b0d29bc4SBrooks Davis-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 18*b0d29bc4SBrooks Davis-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 19*b0d29bc4SBrooks Davis-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 20*b0d29bc4SBrooks Davis-- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 21*b0d29bc4SBrooks Davis-- OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 22*b0d29bc4SBrooks Davis-- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 23*b0d29bc4SBrooks Davis-- LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 24*b0d29bc4SBrooks Davis-- DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 25*b0d29bc4SBrooks Davis-- THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 26*b0d29bc4SBrooks Davis-- (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 27*b0d29bc4SBrooks Davis-- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 28*b0d29bc4SBrooks Davis 29*b0d29bc4SBrooks Davis-- \file store/v2-to-v3.sql 30*b0d29bc4SBrooks Davis-- Migration of a database with version 2 of the schema to version 3. 31*b0d29bc4SBrooks Davis-- 32*b0d29bc4SBrooks Davis-- Version 3 appeared in revision 084d740b1da635946d153475156e335ddfc4aed6 33*b0d29bc4SBrooks Davis-- and its changes were: 34*b0d29bc4SBrooks Davis-- 35*b0d29bc4SBrooks Davis-- * Removal of historical data. 36*b0d29bc4SBrooks Davis-- 37*b0d29bc4SBrooks Davis-- Because from v2 to v3 we went from a unified database to many separate 38*b0d29bc4SBrooks Davis-- databases, this file is parameterized on @ACTION_ID@. The file has to 39*b0d29bc4SBrooks Davis-- be executed once per action with this string replaced. 40*b0d29bc4SBrooks Davis 41*b0d29bc4SBrooks Davis 42*b0d29bc4SBrooks DavisATTACH DATABASE "@OLD_DATABASE@" AS old_store; 43*b0d29bc4SBrooks Davis 44*b0d29bc4SBrooks Davis 45*b0d29bc4SBrooks Davis-- New database already contains a record for v3. Just import older entries. 46*b0d29bc4SBrooks DavisINSERT INTO metadata SELECT * FROM old_store.metadata; 47*b0d29bc4SBrooks Davis 48*b0d29bc4SBrooks DavisINSERT INTO contexts 49*b0d29bc4SBrooks Davis SELECT cwd 50*b0d29bc4SBrooks Davis FROM old_store.actions 51*b0d29bc4SBrooks Davis NATURAL JOIN old_store.contexts 52*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@; 53*b0d29bc4SBrooks Davis 54*b0d29bc4SBrooks DavisINSERT INTO env_vars 55*b0d29bc4SBrooks Davis SELECT var_name, var_value 56*b0d29bc4SBrooks Davis FROM old_store.actions 57*b0d29bc4SBrooks Davis NATURAL JOIN old_store.contexts 58*b0d29bc4SBrooks Davis NATURAL JOIN old_store.env_vars 59*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@; 60*b0d29bc4SBrooks Davis 61*b0d29bc4SBrooks DavisINSERT INTO metadatas 62*b0d29bc4SBrooks Davis SELECT metadata_id, property_name, property_value 63*b0d29bc4SBrooks Davis FROM old_store.metadatas 64*b0d29bc4SBrooks Davis WHERE metadata_id IN ( 65*b0d29bc4SBrooks Davis SELECT test_programs.metadata_id 66*b0d29bc4SBrooks Davis FROM old_store.test_programs 67*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@ 68*b0d29bc4SBrooks Davis ) OR metadata_id IN ( 69*b0d29bc4SBrooks Davis SELECT test_cases.metadata_id 70*b0d29bc4SBrooks Davis FROM old_store.test_programs JOIN old_store.test_cases 71*b0d29bc4SBrooks Davis ON test_programs.test_program_id == test_cases.test_program_id 72*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@ 73*b0d29bc4SBrooks Davis ); 74*b0d29bc4SBrooks Davis 75*b0d29bc4SBrooks DavisINSERT INTO test_programs 76*b0d29bc4SBrooks Davis SELECT test_program_id, absolute_path, root, relative_path, 77*b0d29bc4SBrooks Davis test_suite_name, metadata_id, interface 78*b0d29bc4SBrooks Davis FROM old_store.test_programs 79*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@; 80*b0d29bc4SBrooks Davis 81*b0d29bc4SBrooks DavisINSERT INTO test_cases 82*b0d29bc4SBrooks Davis SELECT test_cases.test_case_id, test_cases.test_program_id, 83*b0d29bc4SBrooks Davis test_cases.name, test_cases.metadata_id 84*b0d29bc4SBrooks Davis FROM old_store.test_cases JOIN old_store.test_programs 85*b0d29bc4SBrooks Davis ON test_cases.test_program_id == test_programs.test_program_id 86*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@; 87*b0d29bc4SBrooks Davis 88*b0d29bc4SBrooks DavisINSERT INTO test_results 89*b0d29bc4SBrooks Davis SELECT test_results.test_case_id, test_results.result_type, 90*b0d29bc4SBrooks Davis test_results.result_reason, test_results.start_time, test_results.end_time 91*b0d29bc4SBrooks Davis FROM old_store.test_results 92*b0d29bc4SBrooks Davis JOIN old_store.test_cases 93*b0d29bc4SBrooks Davis ON test_results.test_case_id == test_cases.test_case_id 94*b0d29bc4SBrooks Davis JOIN old_store.test_programs 95*b0d29bc4SBrooks Davis ON test_cases.test_program_id == test_programs.test_program_id 96*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@; 97*b0d29bc4SBrooks Davis 98*b0d29bc4SBrooks DavisINSERT INTO files 99*b0d29bc4SBrooks Davis SELECT files.file_id, files.contents 100*b0d29bc4SBrooks Davis FROM old_store.files 101*b0d29bc4SBrooks Davis JOIN old_store.test_case_files 102*b0d29bc4SBrooks Davis ON files.file_id == test_case_files.file_id 103*b0d29bc4SBrooks Davis JOIN old_store.test_cases 104*b0d29bc4SBrooks Davis ON test_case_files.test_case_id == test_cases.test_case_id 105*b0d29bc4SBrooks Davis JOIN old_store.test_programs 106*b0d29bc4SBrooks Davis ON test_cases.test_program_id == test_programs.test_program_id 107*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@; 108*b0d29bc4SBrooks Davis 109*b0d29bc4SBrooks DavisINSERT INTO test_case_files 110*b0d29bc4SBrooks Davis SELECT test_case_files.test_case_id, test_case_files.file_name, 111*b0d29bc4SBrooks Davis test_case_files.file_id 112*b0d29bc4SBrooks Davis FROM old_store.test_case_files 113*b0d29bc4SBrooks Davis JOIN old_store.test_cases 114*b0d29bc4SBrooks Davis ON test_case_files.test_case_id == test_cases.test_case_id 115*b0d29bc4SBrooks Davis JOIN old_store.test_programs 116*b0d29bc4SBrooks Davis ON test_cases.test_program_id == test_programs.test_program_id 117*b0d29bc4SBrooks Davis WHERE action_id == @ACTION_ID@; 118*b0d29bc4SBrooks Davis 119*b0d29bc4SBrooks Davis 120*b0d29bc4SBrooks DavisDETACH DATABASE old_store; 121