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   `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
 191 DROP TABLE IF EXISTS ike_sas;
 192 CREATE TABLE ike_sas (
 193   `local_spi` varbinary(8) NOT NULL,
 194   `remote_spi` varbinary(8) NOT NULL,
 195   `id` int(10) unsigned NOT NULL,
 196   `initiator` tinyint(1) NOT NULL,
 197   `local_id_type` tinyint(3) NOT NULL,
 198   `local_id_data` varbinary(64) NOT NULL,
 199   `remote_id_type` tinyint(3) NOT NULL,
 200   `remote_id_data` varbinary(64) NOT NULL,
 201   `host_family` tinyint(3) NOT NULL,
 202   `local_host_data` varbinary(16) NOT NULL,
 203   `remote_host_data` varbinary(16) NOT NULL,
 204   `lastuse` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 205   PRIMARY KEY (`local_spi`)
 206 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 207
 208
 209 DROP TABLE IF EXISTS logs;
 210 CREATE TABLE logs (
 211   `id` int(10) unsigned NOT NULL auto_increment,
 212   `local_spi` varbinary(8) NOT NULL,
 213   `signal` tinyint(3) NOT NULL,
 214   `level` tinyint(3) NOT NULL,
 215   `msg` varchar(256) NOT NULL,
 216   `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 217   PRIMARY KEY (`id`)
 218 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 219
 220