1-- Copyright 2012 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/schema_v3.sql 30-- Definition of the database schema. 31-- 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. 36 37 38BEGIN TRANSACTION; 39 40 41-- ------------------------------------------------------------------------- 42-- Metadata. 43-- ------------------------------------------------------------------------- 44 45 46-- Database-wide properties. 47-- 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. 54-- 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. 59CREATE TABLE metadata ( 60 schema_version INTEGER PRIMARY KEY CHECK (schema_version >= 1), 61 timestamp TIMESTAMP NOT NULL CHECK (timestamp >= 0) 62); 63 64 65-- ------------------------------------------------------------------------- 66-- Contexts. 67-- ------------------------------------------------------------------------- 68 69 70-- Execution contexts. 71-- 72-- A context represents the execution environment of the test run. 73-- We record such information for information and debugging purposes. 74CREATE TABLE contexts ( 75 cwd TEXT NOT NULL 76 77 -- TODO(jmmv): Record the run-time configuration. 78); 79 80 81-- Environment variables of a context. 82CREATE TABLE env_vars ( 83 var_name TEXT PRIMARY KEY, 84 var_value TEXT NOT NULL 85); 86 87 88-- ------------------------------------------------------------------------- 89-- Test suites. 90-- 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). 94-- 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-- ------------------------------------------------------------------------- 100 101 102-- Representation of the metadata objects. 103-- 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 107-- identifier. 108CREATE TABLE metadatas ( 109 metadata_id INTEGER NOT NULL, 110 111 -- The name of the property. 112 property_name TEXT NOT NULL, 113 114 -- One of the values of the property. 115 property_value TEXT, 116 117 PRIMARY KEY (metadata_id, property_name) 118); 119 120 121-- Optimize the loading of the metadata of any single entity. 122-- 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 125-- their identifier. 126-- 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. 130CREATE INDEX index_metadatas_by_id 131 ON metadatas (metadata_id); 132 133 134-- Representation of a test program. 135-- 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 139-- the future. 140CREATE TABLE test_programs ( 141 test_program_id INTEGER PRIMARY KEY AUTOINCREMENT, 142 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, 149 150 -- The path to the root of the test suite (where the Kyuafile lives). 151 root TEXT NOT NULL, 152 153 -- The path to the test program, relative to the root. 154 relative_path TEXT NOT NULL, 155 156 -- Name of the test suite the test program belongs to. 157 test_suite_name TEXT NOT NULL, 158 159 -- Reference to the various rows of metadatas. 160 metadata_id INTEGER, 161 162 -- The name of the test program interface. 163 -- 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 167); 168 169 170-- Representation of a test case. 171-- 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 175-- the future. 176CREATE TABLE test_cases ( 177 test_case_id INTEGER PRIMARY KEY AUTOINCREMENT, 178 test_program_id INTEGER REFERENCES test_programs, 179 name TEXT NOT NULL, 180 181 -- Reference to the various rows of metadatas. 182 metadata_id INTEGER 183); 184 185 186-- Optimize the loading of all test cases that are part of a test program. 187CREATE INDEX index_test_cases_by_test_programs_id 188 ON test_cases (test_program_id); 189 190 191-- Representation of test case results. 192-- 193-- Note that there is a 1:1 relation between test cases and their results. 194CREATE TABLE test_results ( 195 test_case_id INTEGER PRIMARY KEY REFERENCES test_cases, 196 result_type TEXT NOT NULL, 197 result_reason TEXT, 198 199 start_time TIMESTAMP NOT NULL, 200 end_time TIMESTAMP NOT NULL 201); 202 203 204-- Collection of output files of the test case. 205CREATE TABLE test_case_files ( 206 test_case_id INTEGER NOT NULL REFERENCES test_cases, 207 208 -- The raw name of the file. 209 -- 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, 215 216 -- Pointer to the file itself. 217 file_id INTEGER NOT NULL REFERENCES files, 218 219 PRIMARY KEY (test_case_id, file_name) 220); 221 222 223-- ------------------------------------------------------------------------- 224-- Verbatim files. 225-- ------------------------------------------------------------------------- 226 227 228-- Copies of files or logs generated during testing. 229-- 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. 232CREATE TABLE files ( 233 file_id INTEGER PRIMARY KEY, 234 235 contents BLOB NOT NULL 236); 237 238 239-- ------------------------------------------------------------------------- 240-- Initialization of values. 241-- ------------------------------------------------------------------------- 242 243 244-- Create a new metadata record. 245-- 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. 248-- 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. 251INSERT INTO metadata (timestamp, schema_version) 252 VALUES (strftime('%s', 'now'), 3); 253 254 255COMMIT TRANSACTION; 256