1 -- Copyright 2014 The Kyua Authors.
2 -- All rights reserved.
4 -- Redistribution and use in source and binary forms, with or without
5 -- modification, are permitted provided that the following conditions are
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.
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.
29 -- \file store/v2-to-v3.sql
30 -- Migration of a database with version 2 of the schema to version 3.
32 -- Version 3 appeared in revision 084d740b1da635946d153475156e335ddfc4aed6
33 -- and its changes were:
35 -- * Removal of historical data.
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.
42 ATTACH DATABASE "@OLD_DATABASE@" AS old_store;
45 -- New database already contains a record for v3. Just import older entries.
46 INSERT INTO metadata SELECT * FROM old_store.metadata;
50 FROM old_store.actions
51 NATURAL JOIN old_store.contexts
52 WHERE action_id == @ACTION_ID@;
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@;
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@
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@
75 INSERT 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@;
81 INSERT 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@;
88 INSERT 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@;
99 SELECT files.file_id, files.contents
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@;
109 INSERT 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@;
120 DETACH DATABASE old_store;