add overall recommendation to session database entry
[strongswan.git] / 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 product_file;
35 CREATE TABLE product_file (
36 product INTEGER NOT NULL REFERENCES products(id),
37 file INTEGER NOT NULL REFERENCES files(id),
38 measurement INTEGER DEFAULT 0,
39 metadata INTEGER DEFAULT 0,
40 PRIMARY KEY (product, file)
41 );
42
43 DROP TABLE IF EXISTS algorithms;
44 CREATE TABLE algorithms (
45 id INTEGER PRIMARY KEY,
46 name VARCHAR(20) not NULL
47 );
48
49 DROP TABLE IF EXISTS file_hashes;
50 CREATE TABLE file_hashes (
51 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
52 file INTEGER NOT NULL REFERENCES files(id),
53 product INTEGER NOT NULL REFERENCES products(id),
54 device INTEGER DEFAULT 0,
55 algo INTEGER NOT NULL REFERENCES algorithms(id),
56 hash BLOB NOT NULL
57 );
58
59 DROP TABLE IF EXISTS sessions;
60 CREATE TABLE sessions (
61 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
62 time INTEGER NOT NULL,
63 connection INTEGER NOT NULL,
64 identity INTEGER DEFAULT 0 REFERENCES identities(id),
65 device INTEGER DEFAULT 0 REFERENCES devices(id),
66 product INTEGER DEFAULT 0 REFERENCES products(id),
67 rec INTEGER DEFAULT 3
68 );
69
70 DROP TABLE IF EXISTS workitems;
71 CREATE TABLE workitems (
72 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
73 session integer NOT NULL REFERENCES sessions(id),
74 type integer DEFAULT 0,
75 argument text NOT NULL,
76 rec_fail integer DEFAULT 1,
77 rec_noresult integer DEFAULT 1,
78 rec_final integer DEFAULT 3,
79 result text
80 );
81 DROP INDEX IF EXISTS workitems_session;
82 CREATE INDEX workitems_sessions ON workitems (
83 session
84 );
85
86 DROP TABLE IF EXISTS components;
87 CREATE TABLE components (
88 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
89 vendor_id INTEGER NOT NULL,
90 name INTEGER NOT NULL,
91 qualifier INTEGER DEFAULT 0
92 );
93
94
95 DROP TABLE IF EXISTS key_component;
96 CREATE TABLE key_component (
97 key INTEGER NOT NULL,
98 component INTEGER NOT NULL,
99 depth INTEGER DEFAULT 0,
100 seq_no INTEGER DEFAULT 0,
101 PRIMARY KEY (key, component)
102 );
103
104
105 DROP TABLE IF EXISTS component_hashes;
106 CREATE TABLE component_hashes (
107 component INTEGER NOT NULL,
108 key INTEGER NOT NULL,
109 seq_no INTEGER NOT NULL,
110 pcr INTEGER NOT NULL,
111 algo INTEGER NOT NULL,
112 hash BLOB NOT NULL,
113 PRIMARY KEY(component, key, seq_no, algo)
114 );
115
116 DROP TABLE IF EXISTS packages;
117 CREATE TABLE packages (
118 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
119 name TEXT NOT NULL
120 );
121 DROP INDEX IF EXISTS packages_name;
122 CREATE INDEX packages_name ON packages (
123 name
124 );
125
126 DROP TABLE IF EXISTS versions;
127 CREATE TABLE versions (
128 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
129 package INTEGER NOT NULL,
130 product INTEGER NOT NULL,
131 release TEXT NOT NULL,
132 security INTEGER DEFAULT 0,
133 time INTEGER DEFAULT 0
134 );
135 DROP INDEX IF EXISTS versions_release;
136 CREATE INDEX versions_release ON versions (
137 release
138 );
139 DROP INDEX IF EXISTS versions_package_product;
140 CREATE INDEX versions_package_product ON versions (
141 package, product
142 );
143
144 DROP TABLE IF EXISTS devices;
145 CREATE TABLE devices (
146 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
147 value BLOB NOT NULL
148 );
149 DROP INDEX IF EXISTS devices_id;
150 CREATE INDEX devices_value ON devices (
151 value
152 );
153
154 DROP TABLE IF EXISTS identities;
155 CREATE TABLE identities (
156 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
157 type INTEGER NOT NULL,
158 value BLOB NOT NULL,
159 UNIQUE (type, value)
160 );