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