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