1 -- Copyright 2011 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_v1.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 larger timestamp value, and never updating previous
50 -- records. When extracting data from this table, the only "valid" row is
51 -- the one with the highest timestamp. All the other rows are meaningless.
53 -- In other words, this table keeps the history of the database metadata.
54 -- The only reason for doing this is for debugging purposes. It may come
55 -- in handy to know when a particular database-wide operation happened if
56 -- it turns out that the database got corrupted.
57 CREATE TABLE metadata (
58 timestamp TIMESTAMP PRIMARY KEY CHECK (timestamp >= 0),
59 schema_version INTEGER NOT NULL CHECK (schema_version >= 1)
63 -- -------------------------------------------------------------------------
65 -- -------------------------------------------------------------------------
68 -- Execution contexts.
70 -- A context represents the execution environment of a particular action.
71 -- Because every action is invoked by the user, the context may have
72 -- changed. We record such information for information and debugging
74 CREATE TABLE contexts (
75 context_id INTEGER PRIMARY KEY AUTOINCREMENT,
78 -- TODO(jmmv): Record the run-time configuration.
82 -- Environment variables of a context.
83 CREATE TABLE env_vars (
84 context_id INTEGER REFERENCES contexts,
85 var_name TEXT NOT NULL,
86 var_value TEXT NOT NULL,
88 PRIMARY KEY (context_id, var_name)
92 -- -------------------------------------------------------------------------
94 -- -------------------------------------------------------------------------
97 -- Representation of user-initiated actions.
99 -- An action is an operation initiated by the user. At the moment, the
100 -- only operation Kyua supports is the "test" operation (in the future we
101 -- should be able to store, e.g. build logs). To keep things simple the
102 -- database schema is restricted to represent one single action.
103 CREATE TABLE actions (
104 action_id INTEGER PRIMARY KEY AUTOINCREMENT,
105 context_id INTEGER REFERENCES contexts
109 -- -------------------------------------------------------------------------
112 -- The tables in this section represent all the components that form a test
113 -- suite. This includes data about the test suite itself (test programs
114 -- and test cases), and also the data about particular runs (test results).
116 -- As you will notice, every object belongs to a particular action, has a
117 -- unique identifier and there is no attempt to deduplicate data. This
118 -- comes from the fact that a test suite is not "stable" over time: i.e. on
119 -- each execution of the test suite, test programs and test cases may have
120 -- come and gone. This has the interesting result of making the
121 -- distinction of a test case and a test result a pure syntactic
122 -- difference, because there is always a 1:1 relation.
124 -- The code that performs the processing of the actions is the component in
125 -- charge of finding correlations between test programs and test cases
126 -- across different actions.
127 -- -------------------------------------------------------------------------
130 -- Representation of a test program.
132 -- At the moment, there are no substantial differences between the
133 -- different interfaces, so we can simplify the design by with having a
134 -- single table representing all test caes. We may need to revisit this in
136 CREATE TABLE test_programs (
137 test_program_id INTEGER PRIMARY KEY AUTOINCREMENT,
138 action_id INTEGER REFERENCES actions,
140 -- The absolute path to the test program. This should not be necessary
141 -- because it is basically the concatenation of root and relative_path.
142 -- However, this allows us to very easily search for test programs
143 -- regardless of where they were executed from. (I.e. different
144 -- combinations of root + relative_path can map to the same absolute path).
145 absolute_path NOT NULL,
147 -- The path to the root of the test suite (where the Kyuafile lives).
150 -- The path to the test program, relative to the root.
151 relative_path NOT NULL,
153 -- Name of the test suite the test program belongs to.
154 test_suite_name TEXT NOT NULL,
156 -- The name of the test program interface.
158 -- Note that this indicates both the interface for the test program and
159 -- its test cases. See below for the corresponding detail tables.
160 interface TEXT NOT NULL
164 -- Representation of a test case.
166 -- At the moment, there are no substantial differences between the
167 -- different interfaces, so we can simplify the design by with having a
168 -- single table representing all test caes. We may need to revisit this in
170 CREATE TABLE test_cases (
171 test_case_id INTEGER PRIMARY KEY AUTOINCREMENT,
172 test_program_id INTEGER REFERENCES test_programs,
177 -- Representation of test case results.
179 -- Note that there is a 1:1 relation between test cases and their results.
180 -- This is a result of storing the information of a test case on every
182 CREATE TABLE test_results (
183 test_case_id INTEGER PRIMARY KEY REFERENCES test_cases,
184 result_type TEXT NOT NULL,
187 start_time TIMESTAMP NOT NULL,
188 end_time TIMESTAMP NOT NULL
192 -- Collection of output files of the test case.
193 CREATE TABLE test_case_files (
194 test_case_id INTEGER NOT NULL REFERENCES test_cases,
196 -- The raw name of the file.
198 -- The special names '__STDOUT__' and '__STDERR__' are reserved to hold
199 -- the stdout and stderr of the test case, respectively. If any of
200 -- these are empty, there will be no corresponding entry in this table
201 -- (hence why we do not allow NULLs in these fields).
202 file_name TEXT NOT NULL,
204 -- Pointer to the file itself.
205 file_id INTEGER NOT NULL REFERENCES files,
207 PRIMARY KEY (test_case_id, file_name)
211 -- -------------------------------------------------------------------------
212 -- Detail tables for the 'atf' test interface.
213 -- -------------------------------------------------------------------------
216 -- Properties specific to 'atf' test cases.
218 -- This table contains the representation of singly-valued properties such
219 -- as 'timeout'. Properties that can have more than one (textual) value
220 -- are stored in the atf_test_cases_multivalues table.
222 -- Note that all properties can be NULL because test cases are not required
224 CREATE TABLE atf_test_cases (
225 test_case_id INTEGER PRIMARY KEY REFERENCES test_cases,
227 -- Free-form description of the text case.
230 -- Either 'true' or 'false', indicating whether the test case has a
231 -- cleanup routine or not.
234 -- The timeout for the test case in microseconds.
237 -- The amount of physical memory required by the test case.
238 required_memory INTEGER,
240 -- Either 'root' or 'unprivileged', indicating the privileges required by
246 -- Representation of test case properties that have more than one value.
248 -- While we could store the flattened values of the properties as provided
249 -- by the test case itself, we choose to store the processed, split
250 -- representation. This allows us to perform queries about the test cases
251 -- directly on the database without doing text processing; for example,
252 -- "get all test cases that require /bin/ls".
253 CREATE TABLE atf_test_cases_multivalues (
254 test_case_id INTEGER REFERENCES test_cases,
256 -- The name of the property; for example, 'require.progs'.
257 property_name TEXT NOT NULL,
259 -- One of the values of the property.
260 property_value TEXT NOT NULL
264 -- -------------------------------------------------------------------------
265 -- Detail tables for the 'plain' test interface.
266 -- -------------------------------------------------------------------------
269 -- Properties specific to 'plain' test programs.
270 CREATE TABLE plain_test_programs (
271 test_program_id INTEGER PRIMARY KEY REFERENCES test_programs,
273 -- The timeout for the test cases in this test program. While this
274 -- setting has a default value for test programs, we explicitly record
275 -- the information here. The "default value" used when the test
276 -- program was run might change over time, so we want to know what it
277 -- was exactly when this was run.
278 timeout INTEGER NOT NULL
282 -- -------------------------------------------------------------------------
284 -- -------------------------------------------------------------------------
287 -- Copies of files or logs generated during testing.
289 -- TODO(jmmv): This will probably grow to unmanageable sizes. We should add a
290 -- hash to the file contents and use that as the primary key instead.
292 file_id INTEGER PRIMARY KEY,
294 contents BLOB NOT NULL
298 -- -------------------------------------------------------------------------
299 -- Initialization of values.
300 -- -------------------------------------------------------------------------
303 -- Create a new metadata record.
305 -- For every new database, we want to ensure that the metadata is valid if
306 -- the database creation (i.e. the whole transaction) succeeded.
308 -- If you modify the value of the schema version in this statement, you
309 -- will also have to modify the version encoded in the backend module.
310 INSERT INTO metadata (timestamp, schema_version)
311 VALUES (strftime('%s', 'now'), 1);