store IKE and ESP proposals in SQL database
[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 algorithms;
45 CREATE TABLE algorithms (
46 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
47 algorithm TEXT NOT NULL
48 );
49
50
51 DROP TABLE IF EXISTS child_config_algorithm;
52 CREATE TABLE child_config_algorithm (
53 child_cfg INTEGER NOT NULL,
54 prio INTEGER NOT NULL,
55 alg 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_algorithm;
70 CREATE TABLE ike_config_algorithm (
71 ike_cfg INTEGER NOT NULL,
72 prio INTEGER NOT NULL,
73 alg 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 pools;
179 CREATE TABLE pools (
180 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
181 name TEXT NOT NULL UNIQUE,
182 start BLOB NOT NULL,
183 end BLOB NOT NULL,
184 timeout INTEGER NOT NULL
185 );
186
187 DROP TABLE IF EXISTS addresses;
188 CREATE TABLE addresses (
189 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
190 pool INTEGER NOT NULL,
191 address BLOB NOT NULL,
192 identity INTEGER NOT NULL,
193 acquired INTEGER NOT NULL,
194 released INTEGER NOT NULL
195 );
196 DROP INDEX IF EXISTS addresses_pool;
197 CREATE INDEX addresses_pool ON addresses (
198 pool
199 );
200 DROP INDEX IF EXISTS addresses_address;
201 CREATE INDEX addresses_address ON addresses (
202 address
203 );
204 DROP INDEX IF EXISTS addresses_identity;
205 CREATE INDEX addresses_identity ON addresses (
206 identity
207 );
208
209 DROP TABLE IF EXISTS leases;
210 CREATE TABLE leases (
211 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
212 address INTEGER NOT NULL,
213 identity INTEGER NOT NULL,
214 acquired INTEGER NOT NULL,
215 released INTEGER NOT NULL
216 );
217
218 DROP TABLE IF EXISTS attribute_pools;
219 CREATE TABLE attribute_pools (
220 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
221 name TEXT NOT NULL
222 );
223
224 DROP TABLE IF EXISTS attributes;
225 CREATE TABLE attributes (
226 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
227 identity INTEGER NOT NULL DEFAULT 0,
228 pool INTEGER NOT NULL DEFAULT 0,
229 type INTEGER NOT NULL,
230 value BLOB NOT NULL
231 );
232 DROP INDEX IF EXISTS attributes_identity;
233 CREATE INDEX attributes_identity ON attributes (
234 identity
235 );
236 DROP INDEX IF EXISTS attributes_pool;
237 CREATE INDEX attributes_pool ON attributes (
238 pool
239 );
240
241 DROP TABLE IF EXISTS ike_sas;
242 CREATE TABLE ike_sas (
243 local_spi BLOB NOT NULL PRIMARY KEY,
244 remote_spi BLOB NOT NULL,
245 id INTEGER NOT NULL,
246 initiator INTEGER NOT NULL,
247 local_id_type INTEGER NOT NULL,
248 local_id_data BLOB DEFAULT NULL,
249 remote_id_type INTEGER NOT NULL,
250 remote_id_data BLOB DEFAULT NULL,
251 host_family INTEGER NOT NULL,
252 local_host_data BLOB NOT NULL,
253 remote_host_data BLOB NOT NULL,
254 created INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
255 );
256
257 DROP TABLE IF EXISTS logs;
258 CREATE TABLE logs (
259 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
260 local_spi BLOB NOT NULL,
261 signal INTEGER NOT NULL,
262 level INTEGER NOT NULL,
263 msg TEXT NOT NULL,
264 time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
265 );
266