Separated IMV session management from IMV policy database
[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 );
145
146 DROP TABLE IF EXISTS component_hashes;
147 CREATE TABLE component_hashes (
148 component INTEGER NOT NULL,
149 key INTEGER NOT NULL,
150 seq_no INTEGER NOT NULL,
151 pcr INTEGER NOT NULL,
152 algo INTEGER NOT NULL,
153 hash BLOB NOT NULL,
154 PRIMARY KEY(component, key, seq_no, algo)
155 );
156
157 DROP TABLE IF EXISTS packages;
158 CREATE TABLE packages (
159 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
160 name TEXT NOT NULL,
161 blacklist INTEGER DEFAULT 0
162 );
163 DROP INDEX IF EXISTS packages_name;
164 CREATE INDEX packages_name ON packages (
165 name
166 );
167
168 DROP TABLE IF EXISTS versions;
169 CREATE TABLE versions (
170 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
171 package INTEGER NOT NULL REFERENCES packages(id),
172 product INTEGER NOT NULL REFERENCES products(id),
173 release TEXT NOT NULL,
174 security INTEGER DEFAULT 0,
175 blacklist INTEGER DEFAULT 0,
176 time INTEGER DEFAULT 0
177 );
178 DROP INDEX IF EXISTS versions_release;
179 CREATE INDEX versions_release ON versions (
180 release
181 );
182 DROP INDEX IF EXISTS versions_package_product;
183 CREATE INDEX versions_package_product ON versions (
184 package, product
185 );
186
187 DROP TABLE IF EXISTS devices;
188 CREATE TABLE devices (
189 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
190 description TEXT DEFAULT '',
191 value TEXT NOT NULL,
192 product INTEGER REFERENCES products(id),
193 trusted INTEGER DEFAULT 0,
194 created INTEGER
195 );
196 DROP INDEX IF EXISTS devices_id;
197 CREATE INDEX devices_value ON devices (
198 value
199 );
200
201 DROP TABLE IF EXISTS identities;
202 CREATE TABLE identities (
203 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
204 type INTEGER NOT NULL,
205 value BLOB NOT NULL,
206 UNIQUE (type, value)
207 );
208
209 DROP TABLE IF EXISTS regids;
210 CREATE TABLE regids (
211 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
212 name TEXT NOT NULL
213 );
214 DROP INDEX IF EXISTS regids_name;
215 CREATE INDEX regids_name ON regids (
216 name
217 );
218
219 DROP TABLE IF EXISTS tags;
220 CREATE TABLE tags (
221 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
222 regid INTEGER NOT NULL REFERENCES regids(id),
223 unique_sw_id TEXT NOT NULL,
224 value TEXT
225 );
226 DROP INDEX IF EXISTS tags_name;
227 CREATE INDEX tags_unique_sw_id ON tags (
228 unique_sw_id
229 );
230