do not process workitems with NULL result
[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 argument text NOT NULL,
111 rec_fail integer NOT NULL,
112 rec_noresult integer NOT NULL,
113 rec_final integer,
114 result text
115 );
116 DROP INDEX IF EXISTS workitems_session;
117 CREATE INDEX workitems_sessions ON workitems (
118 session
119 );
120
121 DROP TABLE IF EXISTS results;
122 CREATE TABLE results (
123 id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
124 session integer NOT NULL REFERENCES measurements(id),
125 policy integer NOT NULL REFERENCES policies(id),
126 rec integer NOT NULL,
127 result text NOT NULL
128 );
129 DROP INDEX IF EXISTS results_session;
130 CREATE INDEX results_session ON results (
131 session
132 );
133
134 DROP TABLE IF EXISTS components;
135 CREATE TABLE components (
136 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
137 vendor_id INTEGER NOT NULL,
138 name INTEGER NOT NULL,
139 qualifier INTEGER DEFAULT 0
140 );
141
142
143 DROP TABLE IF EXISTS key_component;
144 CREATE TABLE key_component (
145 key INTEGER NOT NULL,
146 component INTEGER NOT NULL,
147 depth INTEGER DEFAULT 0,
148 seq_no INTEGER DEFAULT 0,
149 PRIMARY KEY (key, component)
150 );
151
152
153 DROP TABLE IF EXISTS component_hashes;
154 CREATE TABLE component_hashes (
155 component INTEGER NOT NULL,
156 key INTEGER NOT NULL,
157 seq_no INTEGER NOT NULL,
158 pcr INTEGER NOT NULL,
159 algo INTEGER NOT NULL,
160 hash BLOB NOT NULL,
161 PRIMARY KEY(component, key, seq_no, algo)
162 );
163
164 DROP TABLE IF EXISTS packages;
165 CREATE TABLE packages (
166 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
167 name TEXT NOT NULL
168 );
169 DROP INDEX IF EXISTS packages_name;
170 CREATE INDEX packages_name ON packages (
171 name
172 );
173
174 DROP TABLE IF EXISTS versions;
175 CREATE TABLE versions (
176 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
177 package INTEGER NOT NULL,
178 product INTEGER NOT NULL,
179 release TEXT NOT NULL,
180 security INTEGER DEFAULT 0,
181 time INTEGER DEFAULT 0
182 );
183 DROP INDEX IF EXISTS versions_release;
184 CREATE INDEX versions_release ON versions (
185 release
186 );
187 DROP INDEX IF EXISTS versions_package_product;
188 CREATE INDEX versions_package_product ON versions (
189 package, product
190 );
191
192 DROP TABLE IF EXISTS devices;
193 CREATE TABLE devices (
194 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
195 value BLOB NOT NULL
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