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