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