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   version INTEGER REFERENCES versions(id),
  45   device INTEGER REFERENCES devices(id),
  46   size INTEGER,
  47   algo INTEGER NOT NULL REFERENCES algorithms(id),
  48   hash VARCHAR(64) NOT NULL,
  49   mutable INTEGER DEFAULT 0
  50 );
  51
  52 DROP TABLE IF EXISTS groups;
  53 CREATE TABLE groups (
  54   id INTEGER NOT NULL PRIMARY KEY,
  55   name VARCHAR(50) NOT NULL UNIQUE,
  56   parent INTEGER
  57 );
  58
  59 DROP TABLE IF EXISTS groups_members;
  60 CREATE TABLE groups_members (
  61   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  62   group_id INTEGER NOT NULL REFERENCES groups(id),
  63   device_id INTEGER NOT NULL REFERENCES devices(id),
  64   UNIQUE (group_id, device_id)
  65 );
  66
  67 DROP TABLE IF EXISTS groups_product_defaults;
  68 CREATE TABLE groups_product_defaults (
  69   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  70   group_id INTEGER NOT NULL REFERENCES groups(id),
  71   product_id INTEGER NOT NULL REFERENCES products(id),
  72   UNIQUE (group_id, product_id)
  73 );
  74
  75 DROP TABLE IF EXISTS policies;
  76 CREATE TABLE policies (
  77   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  78   type INTEGER NOT NULL,
  79   name VARCHAR(100) NOT NULL UNIQUE,
  80   argument TEXT DEFAULT '' NOT NULL,
  81   rec_fail INTEGER NOT NULL,
  82   rec_noresult INTEGER NOT NULL,
  83   file INTEGER DEFAULT 0 REFERENCES files(id),
  84   dir INTEGER DEFAULT 0 REFERENCES directories(id)
  85 );
  86
  87 DROP TABLE IF EXISTS enforcements;
  88 CREATE TABLE enforcements (
  89   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  90   policy INTEGER NOT NULL REFERENCES policies(id),
  91   group_id INTEGER NOT NULL REFERENCES groups(id),
  92   rec_fail INTEGER,
  93   rec_noresult INTEGER,
  94   max_age INTEGER NOT NULL,
  95   UNIQUE (policy, group_id)
  96 );
  97
  98 DROP TABLE IF EXISTS sessions;
  99 CREATE TABLE sessions (
 100   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 101   time INTEGER NOT NULL,
 102   connection INTEGER NOT NULL,
 103   identity INTEGER DEFAULT 0 REFERENCES identities(id),
 104   device INTEGER DEFAULT 0 REFERENCES devices(id),
 105   product INTEGER DEFAULT 0 REFERENCES products(id),
 106   rec INTEGER DEFAULT 3
 107 );
 108
 109 DROP TABLE IF EXISTS sessions_identities;
 110 CREATE TABLE sessions_identities (
 111   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 112   session_id INTEGER NOT NULL REFERENCES sessions(id),
 113   identity_id INTEGER NOT NULL REFERENCES identities(id),
 114   UNIQUE (session_id, identity_id)
 115 );
 116
 117 DROP TABLE IF EXISTS workitems;
 118 CREATE TABLE workitems (
 119   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 120   session INTEGER NOT NULL REFERENCES sessions(id),
 121   enforcement INTEGER NOT NULL REFERENCES enforcements(id),
 122   type INTEGER NOT NULL,
 123   arg_str TEXT,
 124   arg_int INTEGER DEFAULT 0,
 125   rec_fail INTEGER NOT NULL,
 126   rec_noresult INTEGER NOT NULL,
 127   rec_final INTEGER,
 128   result TEXT
 129 );
 130 DROP INDEX IF EXISTS workitems_session;
 131 CREATE INDEX workitems_sessions ON workitems (
 132   session
 133 );
 134
 135 DROP TABLE IF EXISTS results;
 136 CREATE TABLE results (
 137   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 138   session INTEGER NOT NULL REFERENCES measurements(id),
 139   policy INTEGER NOT NULL REFERENCES policies(id),
 140   rec INTEGER NOT NULL,
 141   result TEXT NOT NULL
 142 );
 143 DROP INDEX IF EXISTS results_session;
 144 CREATE INDEX results_session ON results (
 145   session
 146 );
 147
 148 DROP TABLE IF EXISTS components;
 149 CREATE TABLE components (
 150   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 151   vendor_id INTEGER NOT NULL,
 152   name INTEGER NOT NULL,
 153   qualifier INTEGER DEFAULT 0,
 154   label TEXT NOT NULL
 155 );
 156
 157 DROP TABLE IF EXISTS component_hashes;
 158 CREATE TABLE component_hashes (
 159   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 160   component INTEGER NOT NULL REFERENCES components(id),
 161   key INTEGER NOT NULL REFERENCES devices(id),
 162   seq_no INTEGER NOT NULL,
 163   pcr INTEGER NOT NULL,
 164   algo INTEGER NOT NULL REFERENCES algorithms(id),
 165   hash BLOB NOT NULL
 166 );
 167
 168 DROP TABLE IF EXISTS packages;
 169 CREATE TABLE packages (
 170   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 171   name TEXT NOT NULL,
 172   blacklist INTEGER DEFAULT 0
 173 );
 174 DROP INDEX IF EXISTS packages_name;
 175 CREATE INDEX packages_name ON packages (
 176   name
 177 );
 178
 179 DROP TABLE IF EXISTS versions;
 180 CREATE TABLE versions (
 181   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 182   product INTEGER NOT NULL REFERENCES products(id),
 183   package INTEGER NOT NULL REFERENCES packages(id),
 184   release TEXT,
 185   security INTEGER DEFAULT 0,
 186   blacklist INTEGER DEFAULT 0,
 187   time INTEGER DEFAULT 0
 188 );
 189 DROP INDEX IF EXISTS versions_release;
 190 CREATE INDEX versions_release ON versions (
 191   release
 192 );
 193 DROP INDEX IF EXISTS versions_package_product;
 194 CREATE INDEX versions_package_product ON versions (
 195   package, product
 196 );
 197
 198 DROP TABLE IF EXISTS devices;
 199 CREATE TABLE devices (
 200   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 201   description TEXT DEFAULT '',
 202   value TEXT NOT NULL,
 203   product INTEGER REFERENCES products(id),
 204   trusted INTEGER DEFAULT 0,
 205   created INTEGER
 206 );
 207 DROP INDEX IF EXISTS devices_id;
 208 CREATE INDEX devices_value ON devices (
 209   value
 210 );
 211
 212 DROP TABLE IF EXISTS identities;
 213 CREATE TABLE identities (
 214   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 215   type INTEGER NOT NULL,
 216   value BLOB NOT NULL,
 217   UNIQUE (type, value)
 218 );
 219
 220 DROP TABLE IF EXISTS "swid_entities";
 221 CREATE TABLE "swid_entities" (
 222   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 223   "name" VARCHAR(255) NOT NULL,
 224   "regid" VARCHAR(255) NOT NULL
 225 );
 226 DROP INDEX IF EXISTS "swid_entities_name";
 227 DROP INDEX IF EXISTS "swid_entities_regid";
 228 CREATE INDEX "swid_entities_name" ON "swid_entities" (
 229   "name"
 230 );
 231 CREATE INDEX "swid_entities_regid" ON "swid_entities" (
 232   "regid"
 233 );
 234
 235 DROP TABLE IF EXISTS "swid_entityroles";
 236 CREATE TABLE "swid_entityroles" (
 237   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 238   "tag_id" INTEGER NOT NULL REFERENCES "swid_tags" ("id"),
 239   "entity_id" INTEGER NOT NULL,
 240   "role" SMALLINT UNSIGNED NOT NULL
 241 );
 242 DROP INDEX if EXISTS "swid_entityroles_tag_id";
 243 DROP INDEX IF EXISTS "swid_entityroles_tag_entity_id";
 244 CREATE INDEX "swid_entityroles_tag_id" ON "swid_entityroles" (
 245   "tag_id"
 246 );
 247 CREATE INDEX "swid_entityroles_entity_id" ON "swid_entityroles" (
 248   "entity_id"
 249 );
 250
 251 DROP TABLE IF EXISTS "swid_tags";
 252 CREATE TABLE "swid_tags" (
 253   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 254   "package_name" VARCHAR(255) NOT NULL,
 255   "version" VARCHAR(255) NOT NULL,
 256   "unique_id" VARCHAR(255) NOT NULL,
 257   "swid_xml" TEXT NOT NULL,
 258   "software_id" VARCHAR(255) NOT NULL
 259 );
 260 DROP INDEX if EXISTS "swid_tags_unique_id";
 261 DROP INDEX IF EXISTS "swid_tags_package_name";
 262 DROP INDEX IF EXISTS "swid_tags_software_id";
 263 CREATE INDEX "swid_tags_unique_id" ON "swid_tags" (
 264   "unique_id"
 265 );
 266 CREATE INDEX "swid_tags_package_name" ON "swid_tags" (
 267   "package_name"
 268 );
 269 CREATE INDEX "swid_tags_software_id" ON "swid_tags" (
 270   "software_id"
 271 );
 272
 273 DROP TABLE IF EXISTS "swid_tags_files";
 274 CREATE TABLE "swid_tags_files" (
 275   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 276   "tag_id" INTEGER NOT NULL,
 277   "file_id" INTEGER NOT NULL REFERENCES "files" ("id"),
 278   UNIQUE ("tag_id", "file_id")
 279 );
 280 DROP INDEX IF EXISTS "swid_tags_files_file_id";
 281 DROP INDEX IF EXISTS "swid_tags_files_tag_id";
 282 CREATE INDEX "swid_tags_files_file_id" ON "swid_tags_files" (
 283   "file_id"
 284 );
 285 CREATE INDEX "swid_tags_files_tag_id" ON "swid_tags_files" (
 286   "tag_id"
 287 );
 288
 289 DROP TABLE IF EXISTS "swid_tags_sessions";
 290 CREATE TABLE "swid_tags_sessions" (
 291   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 292   "tag_id" INTEGER NOT NULL,
 293   "session_id" INTEGER NOT NULL REFERENCES "sessions" ("id"),
 294   UNIQUE ("tag_id", "session_id")
 295 );
 296 DROP INDEX IF EXISTS "swid_tags_sessions_tag_id";
 297 DROP INDEX IF EXISTS "swid_tags_sessions_session_id";
 298 CREATE INDEX "swid_tags_sessions_tag_id" ON "swid_tags_sessions" (
 299   "tag_id"
 300 );
 301 CREATE INDEX "swid_tags_sessions_session_id" ON "swid_tags_sessions" (
 302 "session_id"
 303 );
 304
 305 DROP TABLE IF EXISTS "swid_tagstats";
 306 CREATE TABLE "swid_tagstats" (
 307   "id" INTEGER NOT NULL PRIMARY KEY,
 308   "tag_id" INTEGER NOT NULL REFERENCES "swid_tags" ("id"),
 309   "device_id" INTEGER NOT NULL REFERENCES "devices" ("id"),
 310   "first_seen_id" INTEGER NOT NULL REFERENCES "sessions" ("id"),
 311   "last_seen_id" INTEGER NOT NULL REFERENCES "sessions" ("id"),
 312   "first_installed_id" INTEGER REFERENCES "swid_events" ("id"),
 313   "last_deleted_id" INTEGER REFERENCES "swid_events" ("id"),
 314   UNIQUE ("tag_id", "device_id")
 315 );
 316 CREATE INDEX "swid_tagstats_tag_id" ON "swid_tagstats" ("tag_id");
 317 CREATE INDEX "swid_tagstats_device_id" ON "swid_tagstats" ("device_id");
 318 CREATE INDEX "swid_tagstats_first_seen_id" ON "swid_tagstats" ("first_seen_id");
 319 CREATE INDEX "swid_tagstats_last_seen_id" ON "swid_tagstats" ("last_seen_id");
 320
 321 DROP TABLE IF EXISTS "swid_events";
 322 CREATE TABLE "swid_events" (
 323   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 324   "device" INTEGER REFERENCES "devices" ("id"),
 325   "epoch" INTEGER NOT NULL,
 326   "eid" INTEGER NOT NULL,
 327   "timestamp" CHAR(20) NOT NULL
 328 );
 329 DROP INDEX IF EXISTS "swid_events_device";
 330 CREATE INDEX "swid_events_device" ON "swid_events" (
 331   "device"
 332 );
 333
 334 DROP TABLE IF EXISTS "swid_tags_events";
 335 CREATE TABLE "swid_tags_events" (
 336   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 337   "tag_id" INTEGER NOT NULL REFERENCES "swid_tags" ("id"),
 338   "event_id" INTEGER NOT NULL REFERENCES "swid_events" ("id"),
 339   "action" INTEGER NOT NULL,
 340   "record_id" INTEGER DEFAULT 0,
 341   "source_id" INTEGER DEFAULT 0
 342 );
 343 DROP INDEX IF EXISTS "swid_tags_events_event_id";
 344 DROP INDEX IF EXISTS "swid_tags_events_tag_id";
 345 CREATE INDEX "swid_tags_events_event_id" ON "swid_tags_events" (
 346   "event_id"
 347 );
 348 CREATE INDEX "swid_tags_events_tag_id" ON "swid_tags_events" (
 349   "tag_id"
 350 );