Introduced workitems to Attestation IMV
[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,
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 );
55
56 DROP TABLE IF EXISTS group_members;
57 CREATE TABLE group_members (
58 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
59 group_id integer NOT NULL REFERENCES groups(id),
60 device integer NOT NULL REFERENCES devices(id),
61 UNIQUE (group_id, device)
62 );
63
64 DROP TABLE IF EXISTS default_product_groups;
65 CREATE TABLE default_product_groups (
66 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
67 group_id integer NOT NULL REFERENCES groups(id),
68 product integer NOT NULL REFERENCES products(id),
69 UNIQUE (group_id, product)
70 );
71
72 DROP TABLE IF EXISTS policies;
73 CREATE TABLE policies (
74 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
75 type integer NOT NULL,
76 name varchar(100) NOT NULL UNIQUE,
77 argument text DEFAULT '' NOT NULL,
78 rec_fail integer NOT NULL,
79 rec_noresult integer NOT NULL,
80 file integer DEFAULT 0 REFERENCES files(id),
81 dir integer DEFAULT 0 REFERENCES directories(id)
82 );
83
84 DROP TABLE IF EXISTS enforcements;
85 CREATE TABLE enforcements (
86 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
87 policy integer NOT NULL REFERENCES policies(id),
88 group_id integer NOT NULL REFERENCES groups(id),
89 max_age integer NOT NULL,
90 UNIQUE (policy, group_id)
91 );
92
93 DROP TABLE IF EXISTS sessions;
94 CREATE TABLE sessions (
95 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
96 time INTEGER NOT NULL,
97 connection INTEGER NOT NULL,
98 identity INTEGER DEFAULT 0 REFERENCES identities(id),
99 device INTEGER DEFAULT 0 REFERENCES devices(id),
100 product INTEGER DEFAULT 0 REFERENCES products(id),
101 rec INTEGER DEFAULT 3
102 );
103
104 DROP TABLE IF EXISTS workitems;
105 CREATE TABLE workitems (
106 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
107 session integer NOT NULL REFERENCES sessions(id),
108 enforcement integer NOT NULL REFERENCES enforcements(id),
109 type integer NOT NULL,
110 arg_str text,
111 arg_int INTEGER DEFAULT 0,
112 rec_fail integer NOT NULL,
113 rec_noresult integer NOT NULL,
114 rec_final integer,
115 result text
116 );
117 DROP INDEX IF EXISTS workitems_session;
118 CREATE INDEX workitems_sessions ON workitems (
119 session
120 );
121
122 DROP TABLE IF EXISTS results;
123 CREATE TABLE results (
124 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
125 session integer NOT NULL REFERENCES measurements(id),
126 policy integer NOT NULL REFERENCES policies(id),
127 rec integer NOT NULL,
128 result text NOT NULL
129 );
130 DROP INDEX IF EXISTS results_session;
131 CREATE INDEX results_session ON results (
132 session
133 );
134
135 DROP TABLE IF EXISTS components;
136 CREATE TABLE components (
137 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
138 vendor_id INTEGER NOT NULL,
139 name INTEGER NOT NULL,
140 qualifier INTEGER DEFAULT 0
141 );
142
143
144 DROP TABLE IF EXISTS key_component;
145 CREATE TABLE key_component (
146 key INTEGER NOT NULL,
147 component INTEGER NOT NULL,
148 depth INTEGER DEFAULT 0,
149 seq_no INTEGER DEFAULT 0,
150 PRIMARY KEY (key, component)
151 );
152
153
154 DROP TABLE IF EXISTS component_hashes;
155 CREATE TABLE component_hashes (
156 component INTEGER NOT NULL,
157 key INTEGER NOT NULL,
158 seq_no INTEGER NOT NULL,
159 pcr INTEGER NOT NULL,
160 algo INTEGER NOT NULL,
161 hash BLOB NOT NULL,
162 PRIMARY KEY(component, key, seq_no, algo)
163 );
164
165 DROP TABLE IF EXISTS packages;
166 CREATE TABLE packages (
167 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
168 name TEXT NOT NULL
169 );
170 DROP INDEX IF EXISTS packages_name;
171 CREATE INDEX packages_name ON packages (
172 name
173 );
174
175 DROP TABLE IF EXISTS versions;
176 CREATE TABLE versions (
177 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
178 package INTEGER NOT NULL,
179 product INTEGER NOT NULL,
180 release TEXT NOT NULL,
181 security INTEGER DEFAULT 0,
182 time INTEGER DEFAULT 0
183 );
184 DROP INDEX IF EXISTS versions_release;
185 CREATE INDEX versions_release ON versions (
186 release
187 );
188 DROP INDEX IF EXISTS versions_package_product;
189 CREATE INDEX versions_package_product ON versions (
190 package, product
191 );
192
193 DROP TABLE IF EXISTS devices;
194 CREATE TABLE devices (
195 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
196 value BLOB NOT NULL
197 );
198 DROP INDEX IF EXISTS devices_id;
199 CREATE INDEX devices_value ON devices (
200 value
201 );
202
203 DROP TABLE IF EXISTS identities;
204 CREATE TABLE identities (
205 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
206 type INTEGER NOT NULL,
207 value BLOB NOT NULL,
208 UNIQUE (type, value)
209 );
210