updated sql plugin to respect config changes
[strongswan.git] / src / charon / 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 PRIMARY KEY (`id`),
26 INDEX (`name`)
27 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
28
29
30 DROP TABLE IF EXISTS `child_config_traffic_selector`;
31 CREATE TABLE `child_config_traffic_selector` (
32 `child_cfg` int(10) unsigned NOT NULL,
33 `traffic_selector` int(10) unsigned NOT NULL,
34 `kind` tinyint(3) unsigned NOT NULL,
35 INDEX (`child_cfg`, `traffic_selector`)
36 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
37
38
39 DROP TABLE IF EXISTS `ike_configs`;
40 CREATE TABLE `ike_configs` (
41 `id` int(10) unsigned NOT NULL auto_increment,
42 `certreq` tinyint(3) unsigned NOT NULL default '1',
43 `force_encap` tinyint(1) NOT NULL default '0',
44 `local` varchar(128) collate utf8_unicode_ci NOT NULL,
45 `remote` varchar(128) collate utf8_unicode_ci NOT NULL,
46 PRIMARY KEY (`id`)
47 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
48
49
50 DROP TABLE IF EXISTS `peer_configs`;
51 CREATE TABLE `peer_configs` (
52 `id` int(10) unsigned NOT NULL auto_increment,
53 `name` varchar(32) collate utf8_unicode_ci NOT NULL,
54 `ike_version` tinyint(3) unsigned NOT NULL default '2',
55 `ike_cfg` int(10) unsigned NOT NULL,
56 `local_id` varchar(64) collate utf8_unicode_ci NOT NULL,
57 `remote_id` varchar(64) collate utf8_unicode_ci NOT NULL,
58 `cert_policy` tinyint(3) unsigned NOT NULL default '1',
59 `uniqueid` tinyint(3) unsigned NOT NULL default '0',
60 `auth_method` tinyint(3) unsigned NOT NULL default '1',
61 `eap_type` tinyint(3) unsigned NOT NULL default '0',
62 `eap_vendor` smallint(5) unsigned NOT NULL default '0',
63 `keyingtries` tinyint(3) unsigned NOT NULL default '3',
64 `rekeytime` mediumint(8) unsigned NOT NULL default '7200',
65 `reauthtime` mediumint(8) unsigned NOT NULL default '0',
66 `jitter` mediumint(8) unsigned NOT NULL default '180',
67 `overtime` mediumint(8) unsigned NOT NULL default '300',
68 `mobike` tinyint(1) NOT NULL default '1',
69 `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
70 `virtual` varchar(40) default NULL,
71 `pool` varchar(32) default NULL,
72 `mediation` tinyint(1) NOT NULL default '0',
73 `mediated_by` int(10) unsigned NOT NULL default '0',
74 `peer_id` int(10) unsigned NOT NULL default '0',
75 PRIMARY KEY (`id`),
76 INDEX (`name`)
77 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
78
79
80 DROP TABLE IF EXISTS `peer_config_child_config`;
81 CREATE TABLE `peer_config_child_config` (
82 `peer_cfg` int(10) unsigned NOT NULL,
83 `child_cfg` int(10) unsigned NOT NULL,
84 PRIMARY KEY (`peer_cfg`, `child_cfg`)
85 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
86
87
88 DROP TABLE IF EXISTS `traffic_selectors`;
89 CREATE TABLE `traffic_selectors` (
90 `id` int(10) unsigned NOT NULL auto_increment,
91 `type` tinyint(3) unsigned NOT NULL default '7',
92 `protocol` smallint(5) unsigned NOT NULL default '0',
93 `start_addr` varbinary(16) default NULL,
94 `end_addr` varbinary(16) default NULL,
95 `start_port` smallint(5) unsigned NOT NULL default '0',
96 `end_port` smallint(5) unsigned NOT NULL default '65535',
97 PRIMARY KEY (`id`)
98 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
99
100
101 DROP TABLE IF EXISTS certificates;
102 CREATE TABLE certificates (
103 `id` int(10) unsigned NOT NULL auto_increment,
104 `type` tinyint(3) unsigned NOT NULL,
105 `keytype` tinyint(3) unsigned NOT NULL,
106 `data` BLOB NOT NULL,
107 PRIMARY KEY (`id`)
108 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
109
110
111 DROP TABLE IF EXISTS certificate_identity;
112 CREATE TABLE certificate_identity (
113 `certificate` int(10) unsigned NOT NULL,
114 `identity` int(10) unsigned NOT NULL,
115 PRIMARY KEY (`certificate`, `identity`)
116 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
117
118
119 DROP TABLE IF EXISTS private_keys;
120 CREATE TABLE private_keys (
121 `id` int(10) unsigned NOT NULL auto_increment,
122 `type` tinyint(3) unsigned NOT NULL,
123 `data` BLOB NOT NULL,
124 PRIMARY KEY (`id`)
125 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
126
127
128 DROP TABLE IF EXISTS private_key_identity;
129 CREATE TABLE private_key_identity (
130 `private_key` int(10) unsigned NOT NULL,
131 `identity` int(10) unsigned NOT NULL,
132 PRIMARY KEY (`private_key`, `identity`)
133 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
134
135
136 DROP TABLE IF EXISTS shared_secrets;
137 CREATE TABLE shared_secrets (
138 `id` int(10) unsigned NOT NULL auto_increment,
139 `type` tinyint(3) unsigned NOT NULL,
140 `data` varbinary(256) NOT NULL,
141 PRIMARY KEY (`id`)
142 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
143
144
145 DROP TABLE IF EXISTS shared_secret_identity;
146 CREATE TABLE shared_secret_identity (
147 `shared_secret` int(10) unsigned NOT NULL,
148 `identity` int(10) unsigned NOT NULL,
149 PRIMARY KEY (`shared_secret`, `identity`)
150 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
151
152
153 DROP TABLE IF EXISTS ike_sas;
154 CREATE TABLE ike_sas (
155 `local_spi` varbinary(8) NOT NULL,
156 `remote_spi` varbinary(8) NOT NULL,
157 `id` int(10) unsigned NOT NULL,
158 `initiator` tinyint(1) NOT NULL,
159 `local_id_type` tinyint(3) NOT NULL,
160 `local_id_data` varbinary(64) NOT NULL,
161 `remote_id_type` tinyint(3) NOT NULL,
162 `remote_id_data` varbinary(64) NOT NULL,
163 `host_family` tinyint(3) NOT NULL,
164 `local_host_data` varbinary(16) NOT NULL,
165 `remote_host_data` varbinary(16) NOT NULL,
166 `lastuse` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
167 PRIMARY KEY (`local_spi`)
168 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
169
170
171 DROP TABLE IF EXISTS logs;
172 CREATE TABLE logs (
173 `id` int(10) unsigned NOT NULL auto_increment,
174 `local_spi` varbinary(8) NOT NULL,
175 `signal` tinyint(3) NOT NULL,
176 `level` tinyint(3) NOT NULL,
177 `msg` varchar(256) NOT NULL,
178 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
179 PRIMARY KEY (`id`)
180 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
181
182