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