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