testing: Use installed SQL schema instead of local copy
[strongswan.git] / testing / hosts / default / etc / pts / 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 key INTEGER DEFAULT 0 REFERENCES keys(id),
47 algo INTEGER NOT NULL REFERENCES algorithms(id),
48 hash BLOB NOT NULL
49 );
50
51 DROP TABLE IF EXISTS keys;
52 CREATE TABLE keys (
53 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
54 keyid BLOB NOT NULL,
55 owner TEXT NOT NULL
56 );
57 DROP INDEX IF EXISTS keys_keyid;
58 CREATE INDEX keys_keyid ON keys (
59 keyid
60 );
61 DROP INDEX IF EXISTS keys_owner;
62 CREATE INDEX keys_owner ON keys (
63 owner
64 );
65
66 DROP TABLE IF EXISTS groups;
67 CREATE TABLE groups (
68 id INTEGER NOT NULL PRIMARY KEY,
69 name VARCHAR(50) NOT NULL UNIQUE,
70 parent INTEGER
71 );
72
73 DROP TABLE IF EXISTS groups_members;
74 CREATE TABLE groups_members (
75 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
76 group_id INTEGER NOT NULL REFERENCES groups(id),
77 device_id INTEGER NOT NULL REFERENCES devices(id),
78 UNIQUE (group_id, device_id)
79 );
80
81 DROP TABLE IF EXISTS groups_product_defaults;
82 CREATE TABLE groups_product_defaults (
83 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
84 group_id INTEGER NOT NULL REFERENCES groups(id),
85 product_id INTEGER NOT NULL REFERENCES products(id),
86 UNIQUE (group_id, product_id)
87 );
88
89 DROP TABLE IF EXISTS policies;
90 CREATE TABLE policies (
91 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
92 type INTEGER NOT NULL,
93 name VARCHAR(100) NOT NULL UNIQUE,
94 argument TEXT DEFAULT '' NOT NULL,
95 rec_fail INTEGER NOT NULL,
96 rec_noresult INTEGER NOT NULL,
97 file INTEGER DEFAULT 0 REFERENCES files(id),
98 dir INTEGER DEFAULT 0 REFERENCES directories(id)
99 );
100
101 DROP TABLE IF EXISTS enforcements;
102 CREATE TABLE enforcements (
103 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
104 policy INTEGER NOT NULL REFERENCES policies(id),
105 group_id INTEGER NOT NULL REFERENCES groups(id),
106 rec_fail INTEGER,
107 rec_noresult INTEGER,
108 max_age INTEGER NOT NULL,
109 UNIQUE (policy, group_id)
110 );
111
112 DROP TABLE IF EXISTS sessions;
113 CREATE TABLE sessions (
114 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
115 time INTEGER NOT NULL,
116 connection INTEGER NOT NULL,
117 identity INTEGER DEFAULT 0 REFERENCES identities(id),
118 device INTEGER DEFAULT 0 REFERENCES devices(id),
119 product INTEGER DEFAULT 0 REFERENCES products(id),
120 rec INTEGER DEFAULT 3
121 );
122
123 DROP TABLE IF EXISTS workitems;
124 CREATE TABLE workitems (
125 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
126 session INTEGER NOT NULL REFERENCES sessions(id),
127 enforcement INTEGER NOT NULL REFERENCES enforcements(id),
128 type INTEGER NOT NULL,
129 arg_str TEXT,
130 arg_int INTEGER DEFAULT 0,
131 rec_fail INTEGER NOT NULL,
132 rec_noresult INTEGER NOT NULL,
133 rec_final INTEGER,
134 result TEXT
135 );
136 DROP INDEX IF EXISTS workitems_session;
137 CREATE INDEX workitems_sessions ON workitems (
138 session
139 );
140
141 DROP TABLE IF EXISTS results;
142 CREATE TABLE results (
143 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
144 session INTEGER NOT NULL REFERENCES measurements(id),
145 policy INTEGER NOT NULL REFERENCES policies(id),
146 rec INTEGER NOT NULL,
147 result TEXT NOT NULL
148 );
149 DROP INDEX IF EXISTS results_session;
150 CREATE INDEX results_session ON results (
151 session
152 );
153
154 DROP TABLE IF EXISTS components;
155 CREATE TABLE components (
156 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
157 vendor_id INTEGER NOT NULL,
158 name INTEGER NOT NULL,
159 qualifier INTEGER DEFAULT 0
160 );
161
162
163 DROP TABLE IF EXISTS key_component;
164 CREATE TABLE key_component (
165 key INTEGER NOT NULL,
166 component INTEGER NOT NULL,
167 depth INTEGER DEFAULT 0,
168 seq_no INTEGER DEFAULT 0,
169 PRIMARY KEY (key, component)
170 );
171
172
173 DROP TABLE IF EXISTS component_hashes;
174 CREATE TABLE component_hashes (
175 component INTEGER NOT NULL,
176 key INTEGER NOT NULL,
177 seq_no INTEGER NOT NULL,
178 pcr INTEGER NOT NULL,
179 algo INTEGER NOT NULL,
180 hash BLOB NOT NULL,
181 PRIMARY KEY(component, key, seq_no, algo)
182 );
183
184 DROP TABLE IF EXISTS packages;
185 CREATE TABLE packages (
186 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
187 name TEXT NOT NULL,
188 blacklist INTEGER DEFAULT 0
189 );
190 DROP INDEX IF EXISTS packages_name;
191 CREATE INDEX packages_name ON packages (
192 name
193 );
194
195 DROP TABLE IF EXISTS versions;
196 CREATE TABLE versions (
197 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
198 package INTEGER NOT NULL REFERENCES packages(id),
199 product INTEGER NOT NULL REFERENCES products(id),
200 release TEXT NOT NULL,
201 security INTEGER DEFAULT 0,
202 blacklist INTEGER DEFAULT 0,
203 time INTEGER DEFAULT 0
204 );
205 DROP INDEX IF EXISTS versions_release;
206 CREATE INDEX versions_release ON versions (
207 release
208 );
209 DROP INDEX IF EXISTS versions_package_product;
210 CREATE INDEX versions_package_product ON versions (
211 package, product
212 );
213
214 DROP TABLE IF EXISTS devices;
215 CREATE TABLE devices (
216 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
217 description TEXT DEFAULT '',
218 value TEXT NOT NULL,
219 product INTEGER REFERENCES products(id),
220 created INTEGER
221 );
222 DROP INDEX IF EXISTS devices_id;
223 CREATE INDEX devices_value ON devices (
224 value
225 );
226
227 DROP TABLE IF EXISTS identities;
228 CREATE TABLE identities (
229 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
230 type INTEGER NOT NULL,
231 value BLOB NOT NULL,
232 UNIQUE (type, value)
233 );
234
235 DROP TABLE IF EXISTS regids;
236 CREATE TABLE regids (
237 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
238 name TEXT NOT NULL
239 );
240 DROP INDEX IF EXISTS regids_name;
241 CREATE INDEX regids_name ON regids (
242 name
243 );
244
245 DROP TABLE IF EXISTS tags;
246 CREATE TABLE tags (
247 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
248 regid INTEGER NOT NULL REFERENCES regids(id),
249 unique_sw_id TEXT NOT NULL,
250 value TEXT
251 );
252 DROP INDEX IF EXISTS tags_name;
253 CREATE INDEX tags_unique_sw_id ON tags (
254 unique_sw_id
255 );
256