Tables added for CAs and CDPs.
[strongswan.git] / src / libcharon / plugins / sql / sqlite.sql
1
2
3 DROP TABLE IF EXISTS identities;
4 CREATE TABLE identities (
5 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
6 type INTEGER NOT NULL,
7 data BLOB NOT NULL,
8 UNIQUE (type, data)
9 );
10
11
12 DROP TABLE IF EXISTS child_configs;
13 CREATE TABLE child_configs (
14 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
15 name TEXT NOT NULL,
16 lifetime INTEGER NOT NULL DEFAULT '1500',
17 rekeytime INTEGER NOT NULL DEFAULT '1200',
18 jitter INTEGER NOT NULL DEFAULT '60',
19 updown TEXT DEFAULT NULL,
20 hostaccess INTEGER NOT NULL DEFAULT '0',
21 mode INTEGER NOT NULL DEFAULT '2',
22 start_action INTEGER NOT NULL DEFAULT '0',
23 dpd_action INTEGER NOT NULL DEFAULT '0',
24 close_action INTEGER NOT NULL DEFAULT '0',
25 ipcomp INTEGER NOT NULL DEFAULT '0'
26 );
27 DROP INDEX IF EXISTS child_configs_name;
28 CREATE INDEX child_configs_name ON child_configs (
29 name
30 );
31
32
33 DROP TABLE IF EXISTS child_config_traffic_selector;
34 CREATE TABLE child_config_traffic_selector (
35 child_cfg INTEGER NOT NULL,
36 traffic_selector INTEGER NOT NULL,
37 kind INTEGER NOT NULL
38 );
39 DROP INDEX IF EXISTS child_config_traffic_selector;
40 CREATE INDEX child_config_traffic_selector_all ON child_config_traffic_selector (
41 child_cfg, traffic_selector
42 );
43
44 DROP TABLE IF EXISTS proposals;
45 CREATE TABLE proposals (
46 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
47 proposal TEXT NOT NULL
48 );
49
50
51 DROP TABLE IF EXISTS child_config_proposal;
52 CREATE TABLE child_config_proposal (
53 child_cfg INTEGER NOT NULL,
54 prio INTEGER NOT NULL,
55 prop INTEGER NOT NULL
56 );
57
58
59 DROP TABLE IF EXISTS ike_configs;
60 CREATE TABLE ike_configs (
61 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
62 certreq INTEGER NOT NULL DEFAULT '1',
63 force_encap INTEGER NOT NULL DEFAULT '0',
64 local TEXT NOT NULL,
65 remote TEXT NOT NULL
66 );
67
68
69 DROP TABLE IF EXISTS ike_config_proposal;
70 CREATE TABLE ike_config_proposal (
71 ike_cfg INTEGER NOT NULL,
72 prio INTEGER NOT NULL,
73 prop INTEGER NOT NULL
74 );
75
76
77 DROP TABLE IF EXISTS peer_configs;
78 CREATE TABLE peer_configs (
79 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
80 name TEXT NOT NULL,
81 ike_version INTEGER NOT NULL DEFAULT '2',
82 ike_cfg INTEGER NOT NULL,
83 local_id TEXT NOT NULL,
84 remote_id TEXT NOT NULL,
85 cert_policy INTEGER NOT NULL DEFAULT '1',
86 uniqueid INTEGER NOT NULL DEFAULT '0',
87 auth_method INTEGER NOT NULL DEFAULT '1',
88 eap_type INTEGER NOT NULL DEFAULT '0',
89 eap_vendor INTEGER NOT NULL DEFAULT '0',
90 keyingtries INTEGER NOT NULL DEFAULT '3',
91 rekeytime INTEGER NOT NULL DEFAULT '7200',
92 reauthtime INTEGER NOT NULL DEFAULT '0',
93 jitter INTEGER NOT NULL DEFAULT '180',
94 overtime INTEGER NOT NULL DEFAULT '300',
95 mobike INTEGER NOT NULL DEFAULT '1',
96 dpd_delay INTEGER NOT NULL DEFAULT '120',
97 virtual TEXT DEFAULT NULL,
98 pool TEXT DEFAULT NULL,
99 mediation INTEGER NOT NULL DEFAULT '0',
100 mediated_by INTEGER NOT NULL DEFAULT '0',
101 peer_id INTEGER NOT NULL DEFAULT '0'
102 );
103 DROP INDEX IF EXISTS peer_configs_name;
104 CREATE INDEX peer_configs_name ON peer_configs (
105 name
106 );
107
108
109 DROP TABLE IF EXISTS peer_config_child_config;
110 CREATE TABLE peer_config_child_config (
111 peer_cfg INTEGER NOT NULL,
112 child_cfg INTEGER NOT NULL,
113 PRIMARY KEY (peer_cfg, child_cfg)
114 );
115
116
117 DROP TABLE IF EXISTS traffic_selectors;
118 CREATE TABLE traffic_selectors (
119 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
120 type INTEGER NOT NULL DEFAULT '7',
121 protocol INTEGER NOT NULL DEFAULT '0',
122 start_addr BLOB DEFAULT NULL,
123 end_addr BLOB DEFAULT NULL,
124 start_port INTEGER NOT NULL DEFAULT '0',
125 end_port INTEGER NOT NULL DEFAULT '65535'
126 );
127
128
129 DROP TABLE IF EXISTS certificates;
130 CREATE TABLE certificates (
131 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
132 type INTEGER NOT NULL,
133 keytype INTEGER NOT NULL,
134 data BLOB NOT NULL
135 );
136
137
138 DROP TABLE IF EXISTS certificate_identity;
139 CREATE TABLE certificate_identity (
140 certificate INTEGER NOT NULL,
141 identity INTEGER NOT NULL,
142 PRIMARY KEY (certificate, identity)
143 );
144
145
146 DROP TABLE IF EXISTS private_keys;
147 CREATE TABLE private_keys (
148 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
149 type INTEGER NOT NULL,
150 data BLOB NOT NULL
151 );
152
153
154 DROP TABLE IF EXISTS private_key_identity;
155 CREATE TABLE private_key_identity (
156 private_key INTEGER NOT NULL,
157 identity INTEGER NOT NULL,
158 PRIMARY KEY (private_key, identity)
159 );
160
161
162 DROP TABLE IF EXISTS shared_secrets;
163 CREATE TABLE shared_secrets (
164 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
165 type INTEGER NOT NULL,
166 data BLOB NOT NULL
167 );
168
169
170 DROP TABLE IF EXISTS shared_secret_identity;
171 CREATE TABLE shared_secret_identity (
172 shared_secret INTEGER NOT NULL,
173 identity INTEGER NOT NULL,
174 PRIMARY KEY (shared_secret, identity)
175 );
176
177
178 DROP TABLE IF EXISTS certificate_authorities;
179 CREATE TABLE certificate_authorities (
180 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
181 certificate INTEGER NOT NULL
182 );
183
184
185 DROP TABLE IF EXISTS certificate_distribution_points;
186 CREATE TABLE certificate_distribution_points (
187 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
188 ca INTEGER NOT NULL,
189 type INTEGER NOT NULL,
190 uri TEXT NOT NULL
191 );
192
193
194 DROP TABLE IF EXISTS pools;
195 CREATE TABLE pools (
196 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
197 name TEXT NOT NULL UNIQUE,
198 start BLOB NOT NULL,
199 end BLOB NOT NULL,
200 timeout INTEGER NOT NULL
201 );
202
203 DROP TABLE IF EXISTS addresses;
204 CREATE TABLE addresses (
205 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
206 pool INTEGER NOT NULL,
207 address BLOB NOT NULL,
208 identity INTEGER NOT NULL,
209 acquired INTEGER NOT NULL,
210 released INTEGER NOT NULL
211 );
212 DROP INDEX IF EXISTS addresses_pool;
213 CREATE INDEX addresses_pool ON addresses (
214 pool
215 );
216 DROP INDEX IF EXISTS addresses_address;
217 CREATE INDEX addresses_address ON addresses (
218 address
219 );
220 DROP INDEX IF EXISTS addresses_identity;
221 CREATE INDEX addresses_identity ON addresses (
222 identity
223 );
224
225 DROP TABLE IF EXISTS leases;
226 CREATE TABLE leases (
227 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
228 address INTEGER NOT NULL,
229 identity INTEGER NOT NULL,
230 acquired INTEGER NOT NULL,
231 released INTEGER NOT NULL
232 );
233
234 DROP TABLE IF EXISTS attribute_pools;
235 CREATE TABLE attribute_pools (
236 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
237 name TEXT NOT NULL
238 );
239
240 DROP TABLE IF EXISTS attributes;
241 CREATE TABLE attributes (
242 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
243 identity INTEGER NOT NULL DEFAULT 0,
244 pool INTEGER NOT NULL DEFAULT 0,
245 type INTEGER NOT NULL,
246 value BLOB NOT NULL
247 );
248 DROP INDEX IF EXISTS attributes_identity;
249 CREATE INDEX attributes_identity ON attributes (
250 identity
251 );
252 DROP INDEX IF EXISTS attributes_pool;
253 CREATE INDEX attributes_pool ON attributes (
254 pool
255 );
256
257 DROP TABLE IF EXISTS ike_sas;
258 CREATE TABLE ike_sas (
259 local_spi BLOB NOT NULL PRIMARY KEY,
260 remote_spi BLOB NOT NULL,
261 id INTEGER NOT NULL,
262 initiator INTEGER NOT NULL,
263 local_id_type INTEGER NOT NULL,
264 local_id_data BLOB DEFAULT NULL,
265 remote_id_type INTEGER NOT NULL,
266 remote_id_data BLOB DEFAULT NULL,
267 host_family INTEGER NOT NULL,
268 local_host_data BLOB NOT NULL,
269 remote_host_data BLOB NOT NULL,
270 created INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
271 );
272
273 DROP TABLE IF EXISTS logs;
274 CREATE TABLE logs (
275 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
276 local_spi BLOB NOT NULL,
277 signal INTEGER NOT NULL,
278 level INTEGER NOT NULL,
279 msg TEXT NOT NULL,
280 time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
281 );
282