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