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