1*b0d29bc4SBrooks Davis-- Copyright 2011 The Kyua Authors. 2*b0d29bc4SBrooks Davis-- All rights reserved. 3*b0d29bc4SBrooks Davis-- 4*b0d29bc4SBrooks Davis-- Redistribution and use in source and binary forms, with or without 5*b0d29bc4SBrooks Davis-- modification, are permitted provided that the following conditions are 6*b0d29bc4SBrooks Davis-- met: 7*b0d29bc4SBrooks Davis-- 8*b0d29bc4SBrooks Davis-- * Redistributions of source code must retain the above copyright 9*b0d29bc4SBrooks Davis-- notice, this list of conditions and the following disclaimer. 10*b0d29bc4SBrooks Davis-- * Redistributions in binary form must reproduce the above copyright 11*b0d29bc4SBrooks Davis-- notice, this list of conditions and the following disclaimer in the 12*b0d29bc4SBrooks Davis-- documentation and/or other materials provided with the distribution. 13*b0d29bc4SBrooks Davis-- * Neither the name of Google Inc. nor the names of its contributors 14*b0d29bc4SBrooks Davis-- may be used to endorse or promote products derived from this software 15*b0d29bc4SBrooks Davis-- without specific prior written permission. 16*b0d29bc4SBrooks Davis-- 17*b0d29bc4SBrooks Davis-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 18*b0d29bc4SBrooks Davis-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 19*b0d29bc4SBrooks Davis-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 20*b0d29bc4SBrooks Davis-- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 21*b0d29bc4SBrooks Davis-- OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 22*b0d29bc4SBrooks Davis-- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 23*b0d29bc4SBrooks Davis-- LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 24*b0d29bc4SBrooks Davis-- DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 25*b0d29bc4SBrooks Davis-- THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 26*b0d29bc4SBrooks Davis-- (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 27*b0d29bc4SBrooks Davis-- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 28*b0d29bc4SBrooks Davis 29*b0d29bc4SBrooks Davis-- \file store/schema_v1.sql 30*b0d29bc4SBrooks Davis-- Definition of the database schema. 31*b0d29bc4SBrooks Davis-- 32*b0d29bc4SBrooks Davis-- The whole contents of this file are wrapped in a transaction. We want 33*b0d29bc4SBrooks Davis-- to ensure that the initial contents of the database (the table layout as 34*b0d29bc4SBrooks Davis-- well as any predefined values) are written atomically to simplify error 35*b0d29bc4SBrooks Davis-- handling in our code. 36*b0d29bc4SBrooks Davis 37*b0d29bc4SBrooks Davis 38*b0d29bc4SBrooks DavisBEGIN TRANSACTION; 39*b0d29bc4SBrooks Davis 40*b0d29bc4SBrooks Davis 41*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 42*b0d29bc4SBrooks Davis-- Metadata. 43*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 44*b0d29bc4SBrooks Davis 45*b0d29bc4SBrooks Davis 46*b0d29bc4SBrooks Davis-- Database-wide properties. 47*b0d29bc4SBrooks Davis-- 48*b0d29bc4SBrooks Davis-- Rows in this table are immutable: modifying the metadata implies writing 49*b0d29bc4SBrooks Davis-- a new record with a larger timestamp value, and never updating previous 50*b0d29bc4SBrooks Davis-- records. When extracting data from this table, the only "valid" row is 51*b0d29bc4SBrooks Davis-- the one with the highest timestamp. All the other rows are meaningless. 52*b0d29bc4SBrooks Davis-- 53*b0d29bc4SBrooks Davis-- In other words, this table keeps the history of the database metadata. 54*b0d29bc4SBrooks Davis-- The only reason for doing this is for debugging purposes. It may come 55*b0d29bc4SBrooks Davis-- in handy to know when a particular database-wide operation happened if 56*b0d29bc4SBrooks Davis-- it turns out that the database got corrupted. 57*b0d29bc4SBrooks DavisCREATE TABLE metadata ( 58*b0d29bc4SBrooks Davis timestamp TIMESTAMP PRIMARY KEY CHECK (timestamp >= 0), 59*b0d29bc4SBrooks Davis schema_version INTEGER NOT NULL CHECK (schema_version >= 1) 60*b0d29bc4SBrooks Davis); 61*b0d29bc4SBrooks Davis 62*b0d29bc4SBrooks Davis 63*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 64*b0d29bc4SBrooks Davis-- Contexts. 65*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 66*b0d29bc4SBrooks Davis 67*b0d29bc4SBrooks Davis 68*b0d29bc4SBrooks Davis-- Execution contexts. 69*b0d29bc4SBrooks Davis-- 70*b0d29bc4SBrooks Davis-- A context represents the execution environment of a particular action. 71*b0d29bc4SBrooks Davis-- Because every action is invoked by the user, the context may have 72*b0d29bc4SBrooks Davis-- changed. We record such information for information and debugging 73*b0d29bc4SBrooks Davis-- purposes. 74*b0d29bc4SBrooks DavisCREATE TABLE contexts ( 75*b0d29bc4SBrooks Davis context_id INTEGER PRIMARY KEY AUTOINCREMENT, 76*b0d29bc4SBrooks Davis cwd TEXT NOT NULL 77*b0d29bc4SBrooks Davis 78*b0d29bc4SBrooks Davis -- TODO(jmmv): Record the run-time configuration. 79*b0d29bc4SBrooks Davis); 80*b0d29bc4SBrooks Davis 81*b0d29bc4SBrooks Davis 82*b0d29bc4SBrooks Davis-- Environment variables of a context. 83*b0d29bc4SBrooks DavisCREATE TABLE env_vars ( 84*b0d29bc4SBrooks Davis context_id INTEGER REFERENCES contexts, 85*b0d29bc4SBrooks Davis var_name TEXT NOT NULL, 86*b0d29bc4SBrooks Davis var_value TEXT NOT NULL, 87*b0d29bc4SBrooks Davis 88*b0d29bc4SBrooks Davis PRIMARY KEY (context_id, var_name) 89*b0d29bc4SBrooks Davis); 90*b0d29bc4SBrooks Davis 91*b0d29bc4SBrooks Davis 92*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 93*b0d29bc4SBrooks Davis-- Actions. 94*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 95*b0d29bc4SBrooks Davis 96*b0d29bc4SBrooks Davis 97*b0d29bc4SBrooks Davis-- Representation of user-initiated actions. 98*b0d29bc4SBrooks Davis-- 99*b0d29bc4SBrooks Davis-- An action is an operation initiated by the user. At the moment, the 100*b0d29bc4SBrooks Davis-- only operation Kyua supports is the "test" operation (in the future we 101*b0d29bc4SBrooks Davis-- should be able to store, e.g. build logs). To keep things simple the 102*b0d29bc4SBrooks Davis-- database schema is restricted to represent one single action. 103*b0d29bc4SBrooks DavisCREATE TABLE actions ( 104*b0d29bc4SBrooks Davis action_id INTEGER PRIMARY KEY AUTOINCREMENT, 105*b0d29bc4SBrooks Davis context_id INTEGER REFERENCES contexts 106*b0d29bc4SBrooks Davis); 107*b0d29bc4SBrooks Davis 108*b0d29bc4SBrooks Davis 109*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 110*b0d29bc4SBrooks Davis-- Test suites. 111*b0d29bc4SBrooks Davis-- 112*b0d29bc4SBrooks Davis-- The tables in this section represent all the components that form a test 113*b0d29bc4SBrooks Davis-- suite. This includes data about the test suite itself (test programs 114*b0d29bc4SBrooks Davis-- and test cases), and also the data about particular runs (test results). 115*b0d29bc4SBrooks Davis-- 116*b0d29bc4SBrooks Davis-- As you will notice, every object belongs to a particular action, has a 117*b0d29bc4SBrooks Davis-- unique identifier and there is no attempt to deduplicate data. This 118*b0d29bc4SBrooks Davis-- comes from the fact that a test suite is not "stable" over time: i.e. on 119*b0d29bc4SBrooks Davis-- each execution of the test suite, test programs and test cases may have 120*b0d29bc4SBrooks Davis-- come and gone. This has the interesting result of making the 121*b0d29bc4SBrooks Davis-- distinction of a test case and a test result a pure syntactic 122*b0d29bc4SBrooks Davis-- difference, because there is always a 1:1 relation. 123*b0d29bc4SBrooks Davis-- 124*b0d29bc4SBrooks Davis-- The code that performs the processing of the actions is the component in 125*b0d29bc4SBrooks Davis-- charge of finding correlations between test programs and test cases 126*b0d29bc4SBrooks Davis-- across different actions. 127*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 128*b0d29bc4SBrooks Davis 129*b0d29bc4SBrooks Davis 130*b0d29bc4SBrooks Davis-- Representation of a test program. 131*b0d29bc4SBrooks Davis-- 132*b0d29bc4SBrooks Davis-- At the moment, there are no substantial differences between the 133*b0d29bc4SBrooks Davis-- different interfaces, so we can simplify the design by with having a 134*b0d29bc4SBrooks Davis-- single table representing all test caes. We may need to revisit this in 135*b0d29bc4SBrooks Davis-- the future. 136*b0d29bc4SBrooks DavisCREATE TABLE test_programs ( 137*b0d29bc4SBrooks Davis test_program_id INTEGER PRIMARY KEY AUTOINCREMENT, 138*b0d29bc4SBrooks Davis action_id INTEGER REFERENCES actions, 139*b0d29bc4SBrooks Davis 140*b0d29bc4SBrooks Davis -- The absolute path to the test program. This should not be necessary 141*b0d29bc4SBrooks Davis -- because it is basically the concatenation of root and relative_path. 142*b0d29bc4SBrooks Davis -- However, this allows us to very easily search for test programs 143*b0d29bc4SBrooks Davis -- regardless of where they were executed from. (I.e. different 144*b0d29bc4SBrooks Davis -- combinations of root + relative_path can map to the same absolute path). 145*b0d29bc4SBrooks Davis absolute_path NOT NULL, 146*b0d29bc4SBrooks Davis 147*b0d29bc4SBrooks Davis -- The path to the root of the test suite (where the Kyuafile lives). 148*b0d29bc4SBrooks Davis root TEXT NOT NULL, 149*b0d29bc4SBrooks Davis 150*b0d29bc4SBrooks Davis -- The path to the test program, relative to the root. 151*b0d29bc4SBrooks Davis relative_path NOT NULL, 152*b0d29bc4SBrooks Davis 153*b0d29bc4SBrooks Davis -- Name of the test suite the test program belongs to. 154*b0d29bc4SBrooks Davis test_suite_name TEXT NOT NULL, 155*b0d29bc4SBrooks Davis 156*b0d29bc4SBrooks Davis -- The name of the test program interface. 157*b0d29bc4SBrooks Davis -- 158*b0d29bc4SBrooks Davis -- Note that this indicates both the interface for the test program and 159*b0d29bc4SBrooks Davis -- its test cases. See below for the corresponding detail tables. 160*b0d29bc4SBrooks Davis interface TEXT NOT NULL 161*b0d29bc4SBrooks Davis); 162*b0d29bc4SBrooks Davis 163*b0d29bc4SBrooks Davis 164*b0d29bc4SBrooks Davis-- Representation of a test case. 165*b0d29bc4SBrooks Davis-- 166*b0d29bc4SBrooks Davis-- At the moment, there are no substantial differences between the 167*b0d29bc4SBrooks Davis-- different interfaces, so we can simplify the design by with having a 168*b0d29bc4SBrooks Davis-- single table representing all test caes. We may need to revisit this in 169*b0d29bc4SBrooks Davis-- the future. 170*b0d29bc4SBrooks DavisCREATE TABLE test_cases ( 171*b0d29bc4SBrooks Davis test_case_id INTEGER PRIMARY KEY AUTOINCREMENT, 172*b0d29bc4SBrooks Davis test_program_id INTEGER REFERENCES test_programs, 173*b0d29bc4SBrooks Davis name TEXT NOT NULL 174*b0d29bc4SBrooks Davis); 175*b0d29bc4SBrooks Davis 176*b0d29bc4SBrooks Davis 177*b0d29bc4SBrooks Davis-- Representation of test case results. 178*b0d29bc4SBrooks Davis-- 179*b0d29bc4SBrooks Davis-- Note that there is a 1:1 relation between test cases and their results. 180*b0d29bc4SBrooks Davis-- This is a result of storing the information of a test case on every 181*b0d29bc4SBrooks Davis-- single action. 182*b0d29bc4SBrooks DavisCREATE TABLE test_results ( 183*b0d29bc4SBrooks Davis test_case_id INTEGER PRIMARY KEY REFERENCES test_cases, 184*b0d29bc4SBrooks Davis result_type TEXT NOT NULL, 185*b0d29bc4SBrooks Davis result_reason TEXT, 186*b0d29bc4SBrooks Davis 187*b0d29bc4SBrooks Davis start_time TIMESTAMP NOT NULL, 188*b0d29bc4SBrooks Davis end_time TIMESTAMP NOT NULL 189*b0d29bc4SBrooks Davis); 190*b0d29bc4SBrooks Davis 191*b0d29bc4SBrooks Davis 192*b0d29bc4SBrooks Davis-- Collection of output files of the test case. 193*b0d29bc4SBrooks DavisCREATE TABLE test_case_files ( 194*b0d29bc4SBrooks Davis test_case_id INTEGER NOT NULL REFERENCES test_cases, 195*b0d29bc4SBrooks Davis 196*b0d29bc4SBrooks Davis -- The raw name of the file. 197*b0d29bc4SBrooks Davis -- 198*b0d29bc4SBrooks Davis -- The special names '__STDOUT__' and '__STDERR__' are reserved to hold 199*b0d29bc4SBrooks Davis -- the stdout and stderr of the test case, respectively. If any of 200*b0d29bc4SBrooks Davis -- these are empty, there will be no corresponding entry in this table 201*b0d29bc4SBrooks Davis -- (hence why we do not allow NULLs in these fields). 202*b0d29bc4SBrooks Davis file_name TEXT NOT NULL, 203*b0d29bc4SBrooks Davis 204*b0d29bc4SBrooks Davis -- Pointer to the file itself. 205*b0d29bc4SBrooks Davis file_id INTEGER NOT NULL REFERENCES files, 206*b0d29bc4SBrooks Davis 207*b0d29bc4SBrooks Davis PRIMARY KEY (test_case_id, file_name) 208*b0d29bc4SBrooks Davis); 209*b0d29bc4SBrooks Davis 210*b0d29bc4SBrooks Davis 211*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 212*b0d29bc4SBrooks Davis-- Detail tables for the 'atf' test interface. 213*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 214*b0d29bc4SBrooks Davis 215*b0d29bc4SBrooks Davis 216*b0d29bc4SBrooks Davis-- Properties specific to 'atf' test cases. 217*b0d29bc4SBrooks Davis-- 218*b0d29bc4SBrooks Davis-- This table contains the representation of singly-valued properties such 219*b0d29bc4SBrooks Davis-- as 'timeout'. Properties that can have more than one (textual) value 220*b0d29bc4SBrooks Davis-- are stored in the atf_test_cases_multivalues table. 221*b0d29bc4SBrooks Davis-- 222*b0d29bc4SBrooks Davis-- Note that all properties can be NULL because test cases are not required 223*b0d29bc4SBrooks Davis-- to define them. 224*b0d29bc4SBrooks DavisCREATE TABLE atf_test_cases ( 225*b0d29bc4SBrooks Davis test_case_id INTEGER PRIMARY KEY REFERENCES test_cases, 226*b0d29bc4SBrooks Davis 227*b0d29bc4SBrooks Davis -- Free-form description of the text case. 228*b0d29bc4SBrooks Davis description TEXT, 229*b0d29bc4SBrooks Davis 230*b0d29bc4SBrooks Davis -- Either 'true' or 'false', indicating whether the test case has a 231*b0d29bc4SBrooks Davis -- cleanup routine or not. 232*b0d29bc4SBrooks Davis has_cleanup TEXT, 233*b0d29bc4SBrooks Davis 234*b0d29bc4SBrooks Davis -- The timeout for the test case in microseconds. 235*b0d29bc4SBrooks Davis timeout INTEGER, 236*b0d29bc4SBrooks Davis 237*b0d29bc4SBrooks Davis -- The amount of physical memory required by the test case. 238*b0d29bc4SBrooks Davis required_memory INTEGER, 239*b0d29bc4SBrooks Davis 240*b0d29bc4SBrooks Davis -- Either 'root' or 'unprivileged', indicating the privileges required by 241*b0d29bc4SBrooks Davis -- the test case. 242*b0d29bc4SBrooks Davis required_user TEXT 243*b0d29bc4SBrooks Davis); 244*b0d29bc4SBrooks Davis 245*b0d29bc4SBrooks Davis 246*b0d29bc4SBrooks Davis-- Representation of test case properties that have more than one value. 247*b0d29bc4SBrooks Davis-- 248*b0d29bc4SBrooks Davis-- While we could store the flattened values of the properties as provided 249*b0d29bc4SBrooks Davis-- by the test case itself, we choose to store the processed, split 250*b0d29bc4SBrooks Davis-- representation. This allows us to perform queries about the test cases 251*b0d29bc4SBrooks Davis-- directly on the database without doing text processing; for example, 252*b0d29bc4SBrooks Davis-- "get all test cases that require /bin/ls". 253*b0d29bc4SBrooks DavisCREATE TABLE atf_test_cases_multivalues ( 254*b0d29bc4SBrooks Davis test_case_id INTEGER REFERENCES test_cases, 255*b0d29bc4SBrooks Davis 256*b0d29bc4SBrooks Davis -- The name of the property; for example, 'require.progs'. 257*b0d29bc4SBrooks Davis property_name TEXT NOT NULL, 258*b0d29bc4SBrooks Davis 259*b0d29bc4SBrooks Davis -- One of the values of the property. 260*b0d29bc4SBrooks Davis property_value TEXT NOT NULL 261*b0d29bc4SBrooks Davis); 262*b0d29bc4SBrooks Davis 263*b0d29bc4SBrooks Davis 264*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 265*b0d29bc4SBrooks Davis-- Detail tables for the 'plain' test interface. 266*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 267*b0d29bc4SBrooks Davis 268*b0d29bc4SBrooks Davis 269*b0d29bc4SBrooks Davis-- Properties specific to 'plain' test programs. 270*b0d29bc4SBrooks DavisCREATE TABLE plain_test_programs ( 271*b0d29bc4SBrooks Davis test_program_id INTEGER PRIMARY KEY REFERENCES test_programs, 272*b0d29bc4SBrooks Davis 273*b0d29bc4SBrooks Davis -- The timeout for the test cases in this test program. While this 274*b0d29bc4SBrooks Davis -- setting has a default value for test programs, we explicitly record 275*b0d29bc4SBrooks Davis -- the information here. The "default value" used when the test 276*b0d29bc4SBrooks Davis -- program was run might change over time, so we want to know what it 277*b0d29bc4SBrooks Davis -- was exactly when this was run. 278*b0d29bc4SBrooks Davis timeout INTEGER NOT NULL 279*b0d29bc4SBrooks Davis); 280*b0d29bc4SBrooks Davis 281*b0d29bc4SBrooks Davis 282*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 283*b0d29bc4SBrooks Davis-- Verbatim files. 284*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 285*b0d29bc4SBrooks Davis 286*b0d29bc4SBrooks Davis 287*b0d29bc4SBrooks Davis-- Copies of files or logs generated during testing. 288*b0d29bc4SBrooks Davis-- 289*b0d29bc4SBrooks Davis-- TODO(jmmv): This will probably grow to unmanageable sizes. We should add a 290*b0d29bc4SBrooks Davis-- hash to the file contents and use that as the primary key instead. 291*b0d29bc4SBrooks DavisCREATE TABLE files ( 292*b0d29bc4SBrooks Davis file_id INTEGER PRIMARY KEY, 293*b0d29bc4SBrooks Davis 294*b0d29bc4SBrooks Davis contents BLOB NOT NULL 295*b0d29bc4SBrooks Davis); 296*b0d29bc4SBrooks Davis 297*b0d29bc4SBrooks Davis 298*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 299*b0d29bc4SBrooks Davis-- Initialization of values. 300*b0d29bc4SBrooks Davis-- ------------------------------------------------------------------------- 301*b0d29bc4SBrooks Davis 302*b0d29bc4SBrooks Davis 303*b0d29bc4SBrooks Davis-- Create a new metadata record. 304*b0d29bc4SBrooks Davis-- 305*b0d29bc4SBrooks Davis-- For every new database, we want to ensure that the metadata is valid if 306*b0d29bc4SBrooks Davis-- the database creation (i.e. the whole transaction) succeeded. 307*b0d29bc4SBrooks Davis-- 308*b0d29bc4SBrooks Davis-- If you modify the value of the schema version in this statement, you 309*b0d29bc4SBrooks Davis-- will also have to modify the version encoded in the backend module. 310*b0d29bc4SBrooks DavisINSERT INTO metadata (timestamp, schema_version) 311*b0d29bc4SBrooks Davis VALUES (strftime('%s', 'now'), 1); 312*b0d29bc4SBrooks Davis 313*b0d29bc4SBrooks Davis 314*b0d29bc4SBrooks DavisCOMMIT TRANSACTION; 315