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