1 -- Copyright 2012 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/schema_v3.sql
30 -- Definition of the database schema.
32 -- The whole contents of this file are wrapped in a transaction. We want
33 -- to ensure that the initial contents of the database (the table layout as
34 -- well as any predefined values) are written atomically to simplify error
35 -- handling in our code.
41 -- -------------------------------------------------------------------------
43 -- -------------------------------------------------------------------------
46 -- Database-wide properties.
48 -- Rows in this table are immutable: modifying the metadata implies writing
49 -- a new record with a new schema_version greater than all existing
50 -- records, and never updating previous records. When extracting data from
51 -- this table, the only "valid" row is the one with the highest
52 -- scheam_version. All the other rows are meaningless and only exist for
53 -- historical purposes.
55 -- In other words, this table keeps the history of the database metadata.
56 -- The only reason for doing this is for debugging purposes. It may come
57 -- in handy to know when a particular database-wide operation happened if
58 -- it turns out that the database got corrupted.
59 CREATE TABLE metadata (
60 schema_version INTEGER PRIMARY KEY CHECK (schema_version >= 1),
61 timestamp TIMESTAMP NOT NULL CHECK (timestamp >= 0)
65 -- -------------------------------------------------------------------------
67 -- -------------------------------------------------------------------------
70 -- Execution contexts.
72 -- A context represents the execution environment of the test run.
73 -- We record such information for information and debugging purposes.
74 CREATE TABLE contexts (
77 -- TODO(jmmv): Record the run-time configuration.
81 -- Environment variables of a context.
82 CREATE TABLE env_vars (
83 var_name TEXT PRIMARY KEY,
84 var_value TEXT NOT NULL
88 -- -------------------------------------------------------------------------
91 -- The tables in this section represent all the components that form a test
92 -- suite. This includes data about the test suite itself (test programs
93 -- and test cases), and also the data about particular runs (test results).
95 -- As you will notice, every object has a unique identifier and there is no
96 -- attempt to deduplicate data. This has the interesting result of making
97 -- the distinction of a test case and a test result a pure syntactic
98 -- difference, because there is always a 1:1 relation.
99 -- -------------------------------------------------------------------------
102 -- Representation of the metadata objects.
104 -- The way this table works is like this: every time we record a metadata
105 -- object, we calculate what its identifier should be as the last rowid of
106 -- the table. All properties of that metadata object thus receive the same
108 CREATE TABLE metadatas (
109 metadata_id INTEGER NOT NULL,
111 -- The name of the property.
112 property_name TEXT NOT NULL,
114 -- One of the values of the property.
117 PRIMARY KEY (metadata_id, property_name)
121 -- Optimize the loading of the metadata of any single entity.
123 -- The metadata_id column of the metadatas table is not enough to act as a
124 -- primary key, yet we need to locate entries in the metadatas table solely by
127 -- TODO(jmmv): I think this index is useless given that the primary key in the
128 -- metadatas table includes the metadata_id as the first component. Need to
129 -- verify this and drop the index or this comment appropriately.
130 CREATE INDEX index_metadatas_by_id
131 ON metadatas (metadata_id);
134 -- Representation of a test program.
136 -- At the moment, there are no substantial differences between the
137 -- different interfaces, so we can simplify the design by with having a
138 -- single table representing all test caes. We may need to revisit this in
140 CREATE TABLE test_programs (
141 test_program_id INTEGER PRIMARY KEY AUTOINCREMENT,
143 -- The absolute path to the test program. This should not be necessary
144 -- because it is basically the concatenation of root and relative_path.
145 -- However, this allows us to very easily search for test programs
146 -- regardless of where they were executed from. (I.e. different
147 -- combinations of root + relative_path can map to the same absolute path).
148 absolute_path TEXT NOT NULL,
150 -- The path to the root of the test suite (where the Kyuafile lives).
153 -- The path to the test program, relative to the root.
154 relative_path TEXT NOT NULL,
156 -- Name of the test suite the test program belongs to.
157 test_suite_name TEXT NOT NULL,
159 -- Reference to the various rows of metadatas.
162 -- The name of the test program interface.
164 -- Note that this indicates both the interface for the test program and
165 -- its test cases. See below for the corresponding detail tables.
166 interface TEXT NOT NULL
170 -- Representation of a test case.
172 -- At the moment, there are no substantial differences between the
173 -- different interfaces, so we can simplify the design by with having a
174 -- single table representing all test caes. We may need to revisit this in
176 CREATE TABLE test_cases (
177 test_case_id INTEGER PRIMARY KEY AUTOINCREMENT,
178 test_program_id INTEGER REFERENCES test_programs,
181 -- Reference to the various rows of metadatas.
186 -- Optimize the loading of all test cases that are part of a test program.
187 CREATE INDEX index_test_cases_by_test_programs_id
188 ON test_cases (test_program_id);
191 -- Representation of test case results.
193 -- Note that there is a 1:1 relation between test cases and their results.
194 CREATE TABLE test_results (
195 test_case_id INTEGER PRIMARY KEY REFERENCES test_cases,
196 result_type TEXT NOT NULL,
199 start_time TIMESTAMP NOT NULL,
200 end_time TIMESTAMP NOT NULL
204 -- Collection of output files of the test case.
205 CREATE TABLE test_case_files (
206 test_case_id INTEGER NOT NULL REFERENCES test_cases,
208 -- The raw name of the file.
210 -- The special names '__STDOUT__' and '__STDERR__' are reserved to hold
211 -- the stdout and stderr of the test case, respectively. If any of
212 -- these are empty, there will be no corresponding entry in this table
213 -- (hence why we do not allow NULLs in these fields).
214 file_name TEXT NOT NULL,
216 -- Pointer to the file itself.
217 file_id INTEGER NOT NULL REFERENCES files,
219 PRIMARY KEY (test_case_id, file_name)
223 -- -------------------------------------------------------------------------
225 -- -------------------------------------------------------------------------
228 -- Copies of files or logs generated during testing.
230 -- TODO(jmmv): This will probably grow to unmanageable sizes. We should add a
231 -- hash to the file contents and use that as the primary key instead.
233 file_id INTEGER PRIMARY KEY,
235 contents BLOB NOT NULL
239 -- -------------------------------------------------------------------------
240 -- Initialization of values.
241 -- -------------------------------------------------------------------------
244 -- Create a new metadata record.
246 -- For every new database, we want to ensure that the metadata is valid if
247 -- the database creation (i.e. the whole transaction) succeeded.
249 -- If you modify the value of the schema version in this statement, you
250 -- will also have to modify the version encoded in the backend module.
251 INSERT INTO metadata (timestamp, schema_version)
252 VALUES (strftime('%s', 'now'), 3);