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