Annotation of embedaddon/strongswan/src/libimcv/imv/tables.sql, revision 1.1.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