src/libimcv/imv/tables.sql

   1 /* IMV PTS SQLite database */
   2
   3 DROP TABLE IF EXISTS directories;
   4 CREATE TABLE directories (
   5   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   6   path TEXT NOT NULL
   7 );
   8 DROP INDEX IF EXISTS directories_path;
   9 CREATE INDEX directories_path ON directories (
  10   path
  11 );
  12
  13 DROP TABLE IF EXISTS files;
  14 CREATE TABLE files (
  15   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  16   dir INTEGER DEFAULT 0 REFERENCES directories(id),
  17   name TEXT NOT NULL
  18 );
  19 DROP INDEX IF EXISTS files_name;
  20 CREATE INDEX files_name ON files (
  21   name
  22 );
  23
  24 DROP TABLE IF EXISTS products;
  25 CREATE TABLE products (
  26   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  27   name TEXT NOT NULL
  28 );
  29 DROP INDEX IF EXISTS products_name;
  30 CREATE INDEX products_name ON products (
  31   name
  32 );
  33
  34 DROP TABLE IF EXISTS algorithms;
  35 CREATE TABLE algorithms (
  36   id INTEGER PRIMARY KEY,
  37   name VARCHAR(20) not NULL
  38 );
  39
  40 DROP TABLE IF EXISTS file_hashes;
  41 CREATE TABLE file_hashes (
  42   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  43   file INTEGER NOT NULL REFERENCES files(id),
  44   product INTEGER NOT NULL REFERENCES products(id),
  45   device INTEGER DEFAULT 0,
  46   key INTEGER DEFAULT 0 REFERENCES keys(id),
  47   algo INTEGER NOT NULL REFERENCES algorithms(id),
  48   hash BLOB NOT NULL
  49 );
  50
  51 DROP TABLE IF EXISTS keys;
  52 CREATE TABLE keys (
  53   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  54   keyid BLOB NOT NULL,
  55   owner TEXT NOT NULL
  56 );
  57 DROP INDEX IF EXISTS keys_keyid;
  58 CREATE INDEX keys_keyid ON keys (
  59   keyid
  60 );
  61 DROP INDEX IF EXISTS keys_owner;
  62 CREATE INDEX keys_owner ON keys (
  63   owner
  64 );
  65
  66 DROP TABLE IF EXISTS groups;
  67 CREATE TABLE groups (
  68   id INTEGER NOT NULL PRIMARY KEY,
  69   name VARCHAR(50) NOT NULL UNIQUE,
  70   parent INTEGER
  71 );
  72
  73 DROP TABLE IF EXISTS groups_members;
  74 CREATE TABLE groups_members (
  75   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  76   group_id INTEGER NOT NULL REFERENCES groups(id),
  77   device_id INTEGER NOT NULL REFERENCES devices(id),
  78   UNIQUE (group_id, device_id)
  79 );
  80
  81 DROP TABLE IF EXISTS groups_product_defaults;
  82 CREATE TABLE groups_product_defaults (
  83   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  84   group_id INTEGER NOT NULL REFERENCES groups(id),
  85   product_id INTEGER NOT NULL REFERENCES products(id),
  86   UNIQUE (group_id, product_id)
  87 );
  88
  89 DROP TABLE IF EXISTS policies;
  90 CREATE TABLE policies (
  91   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  92   type INTEGER NOT NULL,
  93   name VARCHAR(100) NOT NULL UNIQUE,
  94   argument TEXT DEFAULT '' NOT NULL,
  95   rec_fail INTEGER NOT NULL,
  96   rec_noresult INTEGER NOT NULL,
  97   file INTEGER DEFAULT 0 REFERENCES files(id),
  98   dir INTEGER DEFAULT 0 REFERENCES directories(id)
  99 );
 100
 101 DROP TABLE IF EXISTS enforcements;
 102 CREATE TABLE enforcements (
 103   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 104   policy INTEGER NOT NULL REFERENCES policies(id),
 105   group_id INTEGER NOT NULL REFERENCES groups(id),
 106   rec_fail INTEGER,
 107   rec_noresult INTEGER,
 108   max_age INTEGER NOT NULL,
 109   UNIQUE (policy, group_id)
 110 );
 111
 112 DROP TABLE IF EXISTS sessions;
 113 CREATE TABLE sessions (
 114   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 115   time INTEGER NOT NULL,
 116   connection INTEGER NOT NULL,
 117   identity INTEGER DEFAULT 0 REFERENCES identities(id),
 118   device INTEGER DEFAULT 0 REFERENCES devices(id),
 119   product INTEGER DEFAULT 0 REFERENCES products(id),
 120   rec INTEGER DEFAULT 3
 121 );
 122
 123 DROP TABLE IF EXISTS workitems;
 124 CREATE TABLE workitems (
 125   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 126   session INTEGER NOT NULL REFERENCES sessions(id),
 127   enforcement INTEGER NOT NULL REFERENCES enforcements(id),
 128   type INTEGER NOT NULL,
 129   arg_str TEXT,
 130   arg_int INTEGER DEFAULT 0,
 131   rec_fail INTEGER NOT NULL,
 132   rec_noresult INTEGER NOT NULL,
 133   rec_final INTEGER,
 134   result TEXT
 135 );
 136 DROP INDEX IF EXISTS workitems_session;
 137 CREATE INDEX workitems_sessions ON workitems (
 138   session
 139 );
 140
 141 DROP TABLE IF EXISTS results;
 142 CREATE TABLE results (
 143   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 144   session INTEGER NOT NULL REFERENCES measurements(id),
 145   policy INTEGER NOT NULL REFERENCES policies(id),
 146   rec INTEGER NOT NULL,
 147   result TEXT NOT NULL
 148 );
 149 DROP INDEX IF EXISTS results_session;
 150 CREATE INDEX results_session ON results (
 151   session
 152 );
 153
 154 DROP TABLE IF EXISTS components;
 155 CREATE TABLE components (
 156   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 157   vendor_id INTEGER NOT NULL,
 158   name INTEGER NOT NULL,
 159   qualifier INTEGER DEFAULT 0
 160 );
 161
 162
 163 DROP TABLE IF EXISTS key_component;
 164 CREATE TABLE key_component (
 165   key INTEGER NOT NULL,
 166   component INTEGER NOT NULL,
 167   depth INTEGER DEFAULT 0,
 168   seq_no INTEGER DEFAULT 0,
 169   PRIMARY KEY (key, component)
 170 );
 171
 172
 173 DROP TABLE IF EXISTS component_hashes;
 174 CREATE TABLE component_hashes (
 175   component INTEGER NOT NULL,
 176   key INTEGER NOT NULL,
 177   seq_no INTEGER NOT NULL,
 178   pcr INTEGER NOT NULL,
 179   algo INTEGER NOT NULL,
 180   hash BLOB NOT NULL,
 181   PRIMARY KEY(component, key, seq_no, algo)
 182 );
 183
 184 DROP TABLE IF EXISTS packages;
 185 CREATE TABLE packages (
 186   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 187   name TEXT NOT NULL,
 188   blacklist INTEGER DEFAULT 0
 189 );
 190 DROP INDEX IF EXISTS packages_name;
 191 CREATE INDEX packages_name ON packages (
 192   name
 193 );
 194
 195 DROP TABLE IF EXISTS versions;
 196 CREATE TABLE versions (
 197   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 198   package INTEGER NOT NULL REFERENCES packages(id),
 199   product INTEGER NOT NULL REFERENCES products(id),
 200   release TEXT NOT NULL,
 201   security INTEGER DEFAULT 0,
 202   blacklist INTEGER DEFAULT 0,
 203   time INTEGER DEFAULT 0
 204 );
 205 DROP INDEX IF EXISTS versions_release;
 206 CREATE INDEX versions_release ON versions (
 207   release
 208 );
 209 DROP INDEX IF EXISTS versions_package_product;
 210 CREATE INDEX versions_package_product ON versions (
 211   package, product
 212 );
 213
 214 DROP TABLE IF EXISTS devices;
 215 CREATE TABLE devices (
 216   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 217   description TEXT,
 218   value BLOB NOT NULL,
 219   product INTEGER REFERENCES products(id),
 220   created INTEGER
 221 );
 222 DROP INDEX IF EXISTS devices_id;
 223 CREATE INDEX devices_value ON devices (
 224   value
 225 );
 226
 227 DROP TABLE IF EXISTS identities;
 228 CREATE TABLE identities (
 229   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 230   type INTEGER NOT NULL,
 231   value BLOB NOT NULL,
 232   UNIQUE (type, value)
 233 );
 234