src/libpts/plugins/imv_attestation/tables.sql

   1 /* PTS SQLite database */
   2
   3 DROP TABLE IF EXISTS files;
   4 CREATE TABLE files (
   5   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   6   type INTEGER NOT NULL,
   7   path TEXT NOT NULL
   8 );
   9 DROP INDEX IF EXISTS files_path;
  10 CREATE INDEX files_path ON files (
  11   path
  12 );
  13
  14 DROP TABLE IF EXISTS products;
  15 CREATE TABLE products (
  16   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  17   name TEXT NOT NULL
  18 );
  19 DROP INDEX IF EXISTS products_name;
  20 CREATE INDEX products_name ON products (
  21   name
  22 );
  23
  24 DROP TABLE IF EXISTS product_file;
  25 CREATE TABLE product_file (
  26   product INTEGER NOT NULL,
  27   file INTEGER NOT NULL,
  28   measurement INTEGER DEFAULT 0,
  29   metadata INTEGER DEFAULT 0,
  30   PRIMARY KEY (product, file)
  31 );
  32
  33 DROP TABLE IF EXISTS file_hashes;
  34 CREATE TABLE file_hashes (
  35   file INTEGER NOT NULL,
  36   directory INTEGER DEFAULT 0,
  37   product INTEGER NOT NULL,
  38   key INTEGER DEFAULT 0,
  39   algo INTEGER NOT NULL,
  40   hash BLOB NOT NULL,
  41   PRIMARY KEY(file, directory, product, algo)
  42 );
  43
  44 DROP TABLE IF EXISTS keys;
  45 CREATE TABLE keys (
  46   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  47   keyid BLOB NOT NULL,
  48   owner TEXT NOT NULL
  49 );
  50 DROP INDEX IF EXISTS keys_keyid;
  51 CREATE INDEX keys_keyid ON keys (
  52   keyid
  53 );
  54 DROP INDEX IF EXISTS keys_owner;
  55 CREATE INDEX keys_owner ON keys (
  56   owner
  57 );
  58
  59 DROP TABLE IF EXISTS components;
  60 CREATE TABLE components (
  61   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  62   vendor_id INTEGER NOT NULL,
  63   name INTEGER NOT NULL,
  64   qualifier INTEGER DEFAULT 0
  65 );
  66
  67
  68 DROP TABLE IF EXISTS key_component;
  69 CREATE TABLE key_component (
  70   key INTEGER NOT NULL,
  71   component INTEGER NOT NULL,
  72   depth INTEGER DEFAULT 0,
  73   seq_no INTEGER DEFAULT 0,
  74   PRIMARY KEY (key, component)
  75 );
  76
  77
  78 DROP TABLE IF EXISTS component_hashes;
  79 CREATE TABLE component_hashes (
  80   component INTEGER NOT NULL,
  81   key INTEGER NOT NULL,
  82   seq_no INTEGER NOT NULL,
  83   pcr INTEGER NOT NULL,
  84   algo INTEGER NOT NULL,
  85   hash BLOB NOT NULL,
  86   PRIMARY KEY(component, key, seq_no, algo)
  87 );
  88
  89 DROP TABLE IF EXISTS packages;
  90 CREATE TABLE packages (
  91   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  92   name TEXT NOT NULL
  93 );
  94 DROP INDEX IF EXISTS packages_name;
  95 CREATE INDEX packages_name ON packages (
  96   name
  97 );
  98
  99 DROP TABLE IF EXISTS versions;
 100 CREATE TABLE versions (
 101   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 102   package INTEGER NOT NULL,
 103   product INTEGER NOT NULL,
 104   release TEXT NOT NULL,
 105   security INTEGER DEFAULT 0,
 106   time INTEGER DEFAULT 0
 107 );
 108 DROP INDEX IF EXISTS versions_release;
 109 CREATE INDEX versions_release ON versions (
 110   release
 111 );
 112 DROP INDEX IF EXISTS versions_package_product;
 113 CREATE INDEX versions_package_product ON versions (
 114   package, product
 115 );
 116
 117 DROP TABLE IF EXISTS devices;
 118 CREATE TABLE devices (
 119   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 120   value BLOB NOT NULL
 121 );
 122 DROP INDEX IF EXISTS devices_id;
 123 CREATE INDEX devices_value ON devices (
 124   value
 125 );
 126
 127 DROP TABLE IF EXISTS device_infos;
 128 CREATE TABLE device_infos (
 129   device INTEGER NOT NULL,
 130   time INTEGER NOT NULL,
 131   product INTEGER DEFAULT 0,
 132   count INTEGER DEFAULT 0,
 133   count_update INTEGER DEFAULT 0,
 134   count_blacklist INTEGER DEFAULT 0,
 135   flags INTEGER DEFAULT 0,
 136   PRIMARY KEY (device, time)
 137 );
 138