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