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