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_v2.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 a particular action.
73 -- Because every action is invoked by the user, the context may have
74 -- changed. We record such information for information and debugging
76 CREATE TABLE contexts (
77 context_id INTEGER PRIMARY KEY AUTOINCREMENT,
80 -- TODO(jmmv): Record the run-time configuration.
84 -- Environment variables of a context.
85 CREATE TABLE env_vars (
86 context_id INTEGER REFERENCES contexts,
87 var_name TEXT NOT NULL,
88 var_value TEXT NOT NULL,
90 PRIMARY KEY (context_id, var_name)
94 -- -------------------------------------------------------------------------
96 -- -------------------------------------------------------------------------
99 -- Representation of user-initiated actions.
101 -- An action is an operation initiated by the user. At the moment, the
102 -- only operation Kyua supports is the "test" operation (in the future we
103 -- should be able to store, e.g. build logs). To keep things simple the
104 -- database schema is restricted to represent one single action.
105 CREATE TABLE actions (
106 action_id INTEGER PRIMARY KEY AUTOINCREMENT,
107 context_id INTEGER REFERENCES contexts
111 -- -------------------------------------------------------------------------
114 -- The tables in this section represent all the components that form a test
115 -- suite. This includes data about the test suite itself (test programs
116 -- and test cases), and also the data about particular runs (test results).
118 -- As you will notice, every object belongs to a particular action, has a
119 -- unique identifier and there is no attempt to deduplicate data. This
120 -- comes from the fact that a test suite is not "stable" over time: i.e. on
121 -- each execution of the test suite, test programs and test cases may have
122 -- come and gone. This has the interesting result of making the
123 -- distinction of a test case and a test result a pure syntactic
124 -- difference, because there is always a 1:1 relation.
126 -- The code that performs the processing of the actions is the component in
127 -- charge of finding correlations between test programs and test cases
128 -- across different actions.
129 -- -------------------------------------------------------------------------
132 -- Representation of the metadata objects.
134 -- The way this table works is like this: every time we record a metadata
135 -- object, we calculate what its identifier should be as the last rowid of
136 -- the table. All properties of that metadata object thus receive the same
138 CREATE TABLE metadatas (
139 metadata_id INTEGER NOT NULL,
141 -- The name of the property.
142 property_name TEXT NOT NULL,
144 -- One of the values of the property.
147 PRIMARY KEY (metadata_id, property_name)
151 -- Optimize the loading of the metadata of any single entity.
153 -- The metadata_id column of the metadatas table is not enough to act as a
154 -- primary key, yet we need to locate entries in the metadatas table solely by
157 -- TODO(jmmv): I think this index is useless given that the primary key in the
158 -- metadatas table includes the metadata_id as the first component. Need to
159 -- verify this and drop the index or this comment appropriately.
160 CREATE INDEX index_metadatas_by_id
161 ON metadatas (metadata_id);
164 -- Representation of a test program.
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_programs (
171 test_program_id INTEGER PRIMARY KEY AUTOINCREMENT,
172 action_id INTEGER REFERENCES actions,
174 -- The absolute path to the test program. This should not be necessary
175 -- because it is basically the concatenation of root and relative_path.
176 -- However, this allows us to very easily search for test programs
177 -- regardless of where they were executed from. (I.e. different
178 -- combinations of root + relative_path can map to the same absolute path).
179 absolute_path TEXT NOT NULL,
181 -- The path to the root of the test suite (where the Kyuafile lives).
184 -- The path to the test program, relative to the root.
185 relative_path TEXT NOT NULL,
187 -- Name of the test suite the test program belongs to.
188 test_suite_name TEXT NOT NULL,
190 -- Reference to the various rows of metadatas.
193 -- The name of the test program interface.
195 -- Note that this indicates both the interface for the test program and
196 -- its test cases. See below for the corresponding detail tables.
197 interface TEXT NOT NULL
201 -- Optimize the lookup of test programs by the action they belong to.
202 CREATE INDEX index_test_programs_by_action_id
203 ON test_programs (action_id);
206 -- Representation of a test case.
208 -- At the moment, there are no substantial differences between the
209 -- different interfaces, so we can simplify the design by with having a
210 -- single table representing all test caes. We may need to revisit this in
212 CREATE TABLE test_cases (
213 test_case_id INTEGER PRIMARY KEY AUTOINCREMENT,
214 test_program_id INTEGER REFERENCES test_programs,
217 -- Reference to the various rows of metadatas.
222 -- Optimize the loading of all test cases that are part of a test program.
223 CREATE INDEX index_test_cases_by_test_programs_id
224 ON test_cases (test_program_id);
227 -- Representation of test case results.
229 -- Note that there is a 1:1 relation between test cases and their results.
230 -- This is a result of storing the information of a test case on every
232 CREATE TABLE test_results (
233 test_case_id INTEGER PRIMARY KEY REFERENCES test_cases,
234 result_type TEXT NOT NULL,
237 start_time TIMESTAMP NOT NULL,
238 end_time TIMESTAMP NOT NULL
242 -- Collection of output files of the test case.
243 CREATE TABLE test_case_files (
244 test_case_id INTEGER NOT NULL REFERENCES test_cases,
246 -- The raw name of the file.
248 -- The special names '__STDOUT__' and '__STDERR__' are reserved to hold
249 -- the stdout and stderr of the test case, respectively. If any of
250 -- these are empty, there will be no corresponding entry in this table
251 -- (hence why we do not allow NULLs in these fields).
252 file_name TEXT NOT NULL,
254 -- Pointer to the file itself.
255 file_id INTEGER NOT NULL REFERENCES files,
257 PRIMARY KEY (test_case_id, file_name)
261 -- -------------------------------------------------------------------------
263 -- -------------------------------------------------------------------------
266 -- Copies of files or logs generated during testing.
268 -- TODO(jmmv): This will probably grow to unmanageable sizes. We should add a
269 -- hash to the file contents and use that as the primary key instead.
271 file_id INTEGER PRIMARY KEY,
273 contents BLOB NOT NULL
277 -- -------------------------------------------------------------------------
278 -- Initialization of values.
279 -- -------------------------------------------------------------------------
282 -- Create a new metadata record.
284 -- For every new database, we want to ensure that the metadata is valid if
285 -- the database creation (i.e. the whole transaction) succeeded.
287 -- If you modify the value of the schema version in this statement, you
288 -- will also have to modify the version encoded in the backend module.
289 INSERT INTO metadata (timestamp, schema_version)
290 VALUES (strftime('%s', 'now'), 2);