src/pool/sqlite.sql

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