668d9d0b1c1360062a0e9b663cd2c37906ae4a33
[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 PRIMARY KEY (`id`),
28 INDEX (`name`)
29 ) ENGINE=MyISAM 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=MyISAM 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 );
46
47
48 DROP TABLE IF EXISTS `child_config_proposal`;
49 CREATE TABLE `child_config_proposal` (
50 `child_cfg` int(10) unsigned NOT NULL,
51 `prio` smallint(5) unsigned NOT NULL,
52 `prop` int(10) unsigned NOT NULL
53 );
54
55
56 DROP TABLE IF EXISTS `ike_configs`;
57 CREATE TABLE `ike_configs` (
58 `id` int(10) unsigned NOT NULL auto_increment,
59 `certreq` tinyint(3) unsigned NOT NULL default '1',
60 `force_encap` tinyint(1) NOT NULL default '0',
61 `local` varchar(128) collate utf8_unicode_ci NOT NULL,
62 `remote` varchar(128) collate utf8_unicode_ci NOT NULL,
63 PRIMARY KEY (`id`)
64 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
65
66
67 DROP TABLE IF EXISTS `ike_config_proposal`;
68 CREATE TABLE `ike_config_proposal` (
69 `ike_cfg` int(10) unsigned NOT NULL,
70 `prio` smallint(5) unsigned NOT NULL,
71 `prop` int(10) unsigned NOT NULL
72 );
73
74
75 DROP TABLE IF EXISTS `peer_configs`;
76 CREATE TABLE `peer_configs` (
77 `id` int(10) unsigned NOT NULL auto_increment,
78 `name` varchar(32) collate utf8_unicode_ci NOT NULL,
79 `ike_version` tinyint(3) unsigned NOT NULL default '2',
80 `ike_cfg` int(10) unsigned NOT NULL,
81 `local_id` varchar(64) collate utf8_unicode_ci NOT NULL,
82 `remote_id` varchar(64) collate utf8_unicode_ci NOT NULL,
83 `cert_policy` tinyint(3) unsigned NOT NULL default '1',
84 `uniqueid` tinyint(3) unsigned NOT NULL default '0',
85 `auth_method` tinyint(3) unsigned NOT NULL default '1',
86 `eap_type` tinyint(3) unsigned NOT NULL default '0',
87 `eap_vendor` smallint(5) unsigned NOT NULL default '0',
88 `keyingtries` tinyint(3) unsigned NOT NULL default '3',
89 `rekeytime` mediumint(8) unsigned NOT NULL default '7200',
90 `reauthtime` mediumint(8) unsigned NOT NULL default '0',
91 `jitter` mediumint(8) unsigned NOT NULL default '180',
92 `overtime` mediumint(8) unsigned NOT NULL default '300',
93 `mobike` tinyint(1) NOT NULL default '1',
94 `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
95 `virtual` varchar(40) default NULL,
96 `pool` varchar(32) default NULL,
97 `mediation` tinyint(1) NOT NULL default '0',
98 `mediated_by` int(10) unsigned NOT NULL default '0',
99 `peer_id` int(10) unsigned NOT NULL default '0',
100 PRIMARY KEY (`id`),
101 INDEX (`name`)
102 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
103
104
105 DROP TABLE IF EXISTS `peer_config_child_config`;
106 CREATE TABLE `peer_config_child_config` (
107 `peer_cfg` int(10) unsigned NOT NULL,
108 `child_cfg` int(10) unsigned NOT NULL,
109 PRIMARY KEY (`peer_cfg`, `child_cfg`)
110 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
111
112
113 DROP TABLE IF EXISTS `traffic_selectors`;
114 CREATE TABLE `traffic_selectors` (
115 `id` int(10) unsigned NOT NULL auto_increment,
116 `type` tinyint(3) unsigned NOT NULL default '7',
117 `protocol` smallint(5) unsigned NOT NULL default '0',
118 `start_addr` varbinary(16) default NULL,
119 `end_addr` varbinary(16) default NULL,
120 `start_port` smallint(5) unsigned NOT NULL default '0',
121 `end_port` smallint(5) unsigned NOT NULL default '65535',
122 PRIMARY KEY (`id`)
123 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
124
125
126 DROP TABLE IF EXISTS certificates;
127 CREATE TABLE certificates (
128 `id` int(10) unsigned NOT NULL auto_increment,
129 `type` tinyint(3) unsigned NOT NULL,
130 `keytype` tinyint(3) unsigned NOT NULL,
131 `data` BLOB NOT NULL,
132 PRIMARY KEY (`id`)
133 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
134
135
136 DROP TABLE IF EXISTS certificate_identity;
137 CREATE TABLE certificate_identity (
138 `certificate` int(10) unsigned NOT NULL,
139 `identity` int(10) unsigned NOT NULL,
140 PRIMARY KEY (`certificate`, `identity`)
141 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
142
143
144 DROP TABLE IF EXISTS private_keys;
145 CREATE TABLE private_keys (
146 `id` int(10) unsigned NOT NULL auto_increment,
147 `type` tinyint(3) unsigned NOT NULL,
148 `data` BLOB NOT NULL,
149 PRIMARY KEY (`id`)
150 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
151
152
153 DROP TABLE IF EXISTS private_key_identity;
154 CREATE TABLE private_key_identity (
155 `private_key` int(10) unsigned NOT NULL,
156 `identity` int(10) unsigned NOT NULL,
157 PRIMARY KEY (`private_key`, `identity`)
158 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
159
160
161 DROP TABLE IF EXISTS shared_secrets;
162 CREATE TABLE shared_secrets (
163 `id` int(10) unsigned NOT NULL auto_increment,
164 `type` tinyint(3) unsigned NOT NULL,
165 `data` varbinary(256) NOT NULL,
166 PRIMARY KEY (`id`)
167 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
168
169
170 DROP TABLE IF EXISTS shared_secret_identity;
171 CREATE TABLE shared_secret_identity (
172 `shared_secret` int(10) unsigned NOT NULL,
173 `identity` int(10) unsigned NOT NULL,
174 PRIMARY KEY (`shared_secret`, `identity`)
175 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
176
177
178 DROP TABLE IF EXISTS pools;
179 CREATE TABLE pools (
180 `id` int(10) unsigned NOT NULL auto_increment,
181 `name` varchar(32) NOT NULL,
182 `start` varbinary(16) NOT NULL,
183 `end` varbinary(16) NOT NULL,
184 `timeout` int(10) unsigned NOT NULL,
185 PRIMARY KEY (`id`),
186 UNIQUE (`name`)
187 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
188
189
190 DROP TABLE IF EXISTS addresses;
191 CREATE TABLE addresses (
192 `id` int(10) unsigned NOT NULL auto_increment,
193 `pool` int(10) unsigned NOT NULL,
194 `address` varbinary(16) NOT NULL,
195 `identity` int(10) unsigned NOT NULL,
196 `acquired` int(10) unsigned NOT NULL,
197 `released` int(10) unsigned DEFAULT NULL,
198 PRIMARY KEY (`id`),
199 INDEX (`pool`),
200 INDEX (`identity`),
201 INDEX (`address`)
202 );
203
204 DROP TABLE IF EXISTS leases;
205 CREATE TABLE leases (
206 `id` int(10) unsigned NOT NULL auto_increment,
207 `address` int(10) unsigned NOT NULL,
208 `identity` int(10) unsigned NOT NULL,
209 `acquired` int(10) unsigned NOT NULL,
210 `released` int(10) unsigned DEFAULT NULL,
211 PRIMARY KEY (`id`)
212 );
213
214 DROP TABLE IF EXISTS attribute_pools;
215 CREATE TABLE attribute_pools (
216 `id` int(10) unsigned NOT NULL auto_increment,
217 `name` varchar(32) NOT NULL,
218 PRIMARY KEY (`id`)
219 );
220
221 DROP TABLE IF EXISTS attributes;
222 CREATE TABLE attributes (
223 `id` int(10) unsigned NOT NULL auto_increment,
224 `identity` int(10) unsigned NOT NULL default '0',
225 `pool` int(10) unsigned NOT NULL default '0',
226 `type` int(10) unsigned NOT NULL,
227 `value` varbinary(16) NOT NULL,
228 PRIMARY KEY (`id`),
229 INDEX (`identity`),
230 INDEX (`pool`)
231 );
232
233 DROP TABLE IF EXISTS ike_sas;
234 CREATE TABLE ike_sas (
235 `local_spi` varbinary(8) NOT NULL,
236 `remote_spi` varbinary(8) NOT NULL,
237 `id` int(10) unsigned NOT NULL,
238 `initiator` tinyint(1) NOT NULL,
239 `local_id_type` tinyint(3) NOT NULL,
240 `local_id_data` varbinary(64) DEFAULT NULL,
241 `remote_id_type` tinyint(3) NOT NULL,
242 `remote_id_data` varbinary(64) DEFAULT NULL,
243 `host_family` tinyint(3) NOT NULL,
244 `local_host_data` varbinary(16) NOT NULL,
245 `remote_host_data` varbinary(16) NOT NULL,
246 `lastuse` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
247 PRIMARY KEY (`local_spi`)
248 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
249
250
251 DROP TABLE IF EXISTS logs;
252 CREATE TABLE logs (
253 `id` int(10) unsigned NOT NULL auto_increment,
254 `local_spi` varbinary(8) NOT NULL,
255 `signal` tinyint(3) NOT NULL,
256 `level` tinyint(3) NOT NULL,
257 `msg` varchar(256) NOT NULL,
258 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
259 PRIMARY KEY (`id`)
260 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
261
262