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 certificate_authorities;
 179 CREATE TABLE certificate_authorities (
 180   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 181   certificate INTEGER NOT NULL
 182 );
 183
 184
 185 DROP TABLE IF EXISTS certificate_distribution_points;
 186 CREATE TABLE certificate_distribution_points (
 187   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 188   ca INTEGER NOT NULL,
 189   type INTEGER NOT NULL,
 190   uri TEXT NOT NULL
 191 );
 192
 193
 194 DROP TABLE IF EXISTS pools;
 195 CREATE TABLE pools (
 196   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 197   name TEXT NOT NULL UNIQUE,
 198   start BLOB NOT NULL,
 199   end BLOB NOT NULL,
 200   timeout INTEGER NOT NULL
 201 );
 202
 203 DROP TABLE IF EXISTS addresses;
 204 CREATE TABLE addresses (
 205   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 206   pool INTEGER NOT NULL,
 207   address BLOB NOT NULL,
 208   identity INTEGER NOT NULL,
 209   acquired INTEGER NOT NULL,
 210   released INTEGER NOT NULL
 211 );
 212 DROP INDEX IF EXISTS addresses_pool;
 213 CREATE INDEX addresses_pool ON addresses (
 214   pool
 215 );
 216 DROP INDEX IF EXISTS addresses_address;
 217 CREATE INDEX addresses_address ON addresses (
 218   address
 219 );
 220 DROP INDEX IF EXISTS addresses_identity;
 221 CREATE INDEX addresses_identity ON addresses (
 222   identity
 223 );
 224
 225 DROP TABLE IF EXISTS leases;
 226 CREATE TABLE leases (
 227   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 228   address INTEGER NOT NULL,
 229   identity INTEGER NOT NULL,
 230   acquired INTEGER NOT NULL,
 231   released INTEGER NOT NULL
 232 );
 233
 234 DROP TABLE IF EXISTS attribute_pools;
 235 CREATE TABLE attribute_pools (
 236   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 237   name TEXT NOT NULL
 238 );
 239
 240 DROP TABLE IF EXISTS attributes;
 241 CREATE TABLE attributes (
 242   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 243   identity INTEGER NOT NULL DEFAULT 0,
 244   pool INTEGER NOT NULL DEFAULT 0,
 245   type INTEGER NOT NULL,
 246   value BLOB NOT NULL
 247 );
 248 DROP INDEX IF EXISTS attributes_identity;
 249 CREATE INDEX attributes_identity ON attributes (
 250   identity
 251 );
 252 DROP INDEX IF EXISTS attributes_pool;
 253 CREATE INDEX attributes_pool ON attributes (
 254   pool
 255 );
 256
 257 DROP TABLE IF EXISTS ike_sas;
 258 CREATE TABLE ike_sas (
 259   local_spi BLOB NOT NULL PRIMARY KEY,
 260   remote_spi BLOB NOT NULL,
 261   id INTEGER NOT NULL,
 262   initiator INTEGER NOT NULL,
 263   local_id_type INTEGER NOT NULL,
 264   local_id_data BLOB DEFAULT NULL,
 265   remote_id_type INTEGER NOT NULL,
 266   remote_id_data BLOB DEFAULT NULL,
 267   host_family INTEGER NOT NULL,
 268   local_host_data BLOB NOT NULL,
 269   remote_host_data BLOB NOT NULL,
 270   created INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
 271 );
 272
 273 DROP TABLE IF EXISTS logs;
 274 CREATE TABLE logs (
 275   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 276   local_spi BLOB NOT NULL,
 277   signal INTEGER NOT NULL,
 278   level INTEGER NOT NULL,
 279   msg TEXT NOT NULL,
 280   time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
 281 );
 282