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