pool: Install SQL schemas from src/pool

This allows us to install the schemas if either the attr-sql or sql
plugin is enabled, since both use the same schema (at least in parts).

diff --git a/src/Makefile.am b/src/Makefile.am
index 264a3cf..f290d9c 100644 (file)
--- a/src/Makefile.am
+++ b/src/Makefile.am
@@ -98,6 +98,10 @@ endif
 
 if USE_ATTR_SQL
   SUBDIRS += pool
+else
+if USE_SQL
+  SUBDIRS += pool
+endif
 endif
 
 if USE_TKM

diff --git a/src/libcharon/plugins/sql/Makefile.am b/src/libcharon/plugins/sql/Makefile.am
index 50b6dc3..fd56931 100644 (file)
--- a/src/libcharon/plugins/sql/Makefile.am
+++ b/src/libcharon/plugins/sql/Makefile.am
@@ -17,6 +17,3 @@ libstrongswan_sql_la_SOURCES = \
        sql_cred.h sql_cred.c sql_logger.h sql_logger.c
 
 libstrongswan_sql_la_LDFLAGS = -module -avoid-version
-
-templatesdir = $(pkgdatadir)/templates/database/sql
-dist_templates_DATA = mysql.sql sqlite.sql

diff --git a/src/libcharon/plugins/sql/mysql.sql b/src/libcharon/plugins/sql/mysql.sql
deleted file mode 100644 (file)
index 1b43759..0000000
--- a/src/libcharon/plugins/sql/mysql.sql
+++ /dev/null
@@ -1,281 +0,0 @@
-
-DROP TABLE IF EXISTS `identities`;
-CREATE TABLE `identities` (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `type` tinyint(4) unsigned NOT NULL,
-  `data` varbinary(64) NOT NULL,
-  PRIMARY KEY (`id`),
-  UNIQUE (`type`, `data`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `child_configs`;
-CREATE TABLE `child_configs` (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `name` varchar(32) collate utf8_unicode_ci NOT NULL,
-  `lifetime` mediumint(8) unsigned NOT NULL default '1500',
-  `rekeytime` mediumint(8) unsigned NOT NULL default '1200',
-  `jitter` mediumint(8) unsigned NOT NULL default '60',
-  `updown` varchar(128) collate utf8_unicode_ci default NULL,
-  `hostaccess` tinyint(1) unsigned NOT NULL default '0',
-  `mode` tinyint(4) unsigned NOT NULL default '2',
-  `start_action` tinyint(4) unsigned NOT NULL default '0',
-  `dpd_action` tinyint(4) unsigned NOT NULL default '0',
-  `close_action` tinyint(4) unsigned NOT NULL default '0',
-  `ipcomp` tinyint(4) unsigned NOT NULL default '0',
-  `reqid` mediumint(8) unsigned NOT NULL default '0',
-  PRIMARY KEY (`id`),
-  INDEX (`name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `child_config_traffic_selector`;
-CREATE TABLE `child_config_traffic_selector` (
-  `child_cfg` int(10) unsigned NOT NULL,
-  `traffic_selector` int(10) unsigned NOT NULL,
-  `kind` tinyint(3) unsigned NOT NULL,
-  INDEX (`child_cfg`, `traffic_selector`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `proposals`;
-CREATE TABLE `proposals` (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `proposal` varchar(128) NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `child_config_proposal`;
-CREATE TABLE `child_config_proposal` (
-  `child_cfg` int(10) unsigned NOT NULL,
-  `prio` smallint(5) unsigned NOT NULL,
-  `prop` int(10) unsigned NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `ike_configs`;
-CREATE TABLE `ike_configs` (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `certreq` tinyint(3) unsigned NOT NULL default '1',
-  `force_encap` tinyint(1) NOT NULL default '0',
-  `local` varchar(128) collate utf8_unicode_ci NOT NULL,
-  `remote` varchar(128) collate utf8_unicode_ci NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `ike_config_proposal`;
-CREATE TABLE `ike_config_proposal` (
-  `ike_cfg` int(10) unsigned NOT NULL,
-  `prio` smallint(5) unsigned NOT NULL,
-  `prop` int(10) unsigned NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `peer_configs`;
-CREATE TABLE `peer_configs` (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `name` varchar(32) collate utf8_unicode_ci NOT NULL,
-  `ike_version` tinyint(3) unsigned NOT NULL default '2',
-  `ike_cfg` int(10) unsigned NOT NULL,
-  `local_id` varchar(64) collate utf8_unicode_ci NOT NULL,
-  `remote_id` varchar(64) collate utf8_unicode_ci NOT NULL,
-  `cert_policy` tinyint(3) unsigned NOT NULL default '1',
-  `uniqueid` tinyint(3) unsigned NOT NULL default '0',
-  `auth_method` tinyint(3) unsigned NOT NULL default '1',
-  `eap_type` tinyint(3) unsigned NOT NULL default '0',
-  `eap_vendor` smallint(5) unsigned NOT NULL default '0',
-  `keyingtries` tinyint(3) unsigned NOT NULL default '3',
-  `rekeytime` mediumint(8) unsigned NOT NULL default '7200',
-  `reauthtime` mediumint(8) unsigned NOT NULL default '0',
-  `jitter` mediumint(8) unsigned NOT NULL default '180',
-  `overtime` mediumint(8) unsigned NOT NULL default '300',
-  `mobike` tinyint(1) NOT NULL default '1',
-  `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
-  `virtual` varchar(40) default NULL,
-  `pool` varchar(32) default NULL,
-  `mediation` tinyint(1) NOT NULL default '0',
-  `mediated_by` int(10) unsigned NOT NULL default '0',
-  `peer_id` int(10) unsigned NOT NULL default '0',
-  PRIMARY KEY (`id`),
-  INDEX (`name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `peer_config_child_config`;
-CREATE TABLE `peer_config_child_config` (
-  `peer_cfg` int(10) unsigned NOT NULL,
-  `child_cfg` int(10) unsigned NOT NULL,
-  PRIMARY KEY (`peer_cfg`, `child_cfg`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS `traffic_selectors`;
-CREATE TABLE `traffic_selectors` (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `type` tinyint(3) unsigned NOT NULL default '7',
-  `protocol` smallint(5) unsigned NOT NULL default '0',
-  `start_addr` varbinary(16) default NULL,
-  `end_addr` varbinary(16) default NULL,
-  `start_port` smallint(5) unsigned NOT NULL default '0',
-  `end_port` smallint(5) unsigned NOT NULL default '65535',
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS certificates;
-CREATE TABLE certificates (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `type` tinyint(3) unsigned NOT NULL,
-  `keytype` tinyint(3) unsigned NOT NULL,
-  `data` BLOB NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS certificate_identity;
-CREATE TABLE certificate_identity (
-  `certificate` int(10) unsigned NOT NULL,
-  `identity` int(10) unsigned NOT NULL,
-  PRIMARY KEY (`certificate`, `identity`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS private_keys;
-CREATE TABLE private_keys (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `type` tinyint(3) unsigned NOT NULL,
-  `data` BLOB NOT NULL,
-  PRIMARY KEY  (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS private_key_identity;
-CREATE TABLE private_key_identity (
-  `private_key` int(10) unsigned NOT NULL,
-  `identity` int(10) unsigned NOT NULL,
-  PRIMARY KEY (`private_key`, `identity`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS shared_secrets;
-CREATE TABLE shared_secrets (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `type` tinyint(3) unsigned NOT NULL,
-  `data` varbinary(256) NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS shared_secret_identity;
-CREATE TABLE shared_secret_identity (
-  `shared_secret` int(10) unsigned NOT NULL,
-  `identity` int(10) unsigned NOT NULL,
-  PRIMARY KEY (`shared_secret`, `identity`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS certificate_authorities;
-CREATE TABLE certificate_authorities (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `certificate` int(10) unsigned NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS certificate_distribution_points;
-CREATE TABLE certificate_distribution_points (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `ca` int(10) unsigned NOT NULL,
-  `type` tinyint(3) unsigned NOT NULL,
-  `uri` varchar(256) NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS pools;
-CREATE TABLE pools (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `name` varchar(32) NOT NULL,
-  `start` varbinary(16) NOT NULL,
-  `end` varbinary(16) NOT NULL,
-  `timeout` int(10) unsigned NOT NULL,
-  PRIMARY KEY (`id`),
-  UNIQUE (`name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS addresses;
-CREATE TABLE addresses (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `pool` int(10) unsigned NOT NULL,
-  `address` varbinary(16) NOT NULL,
-  `identity` int(10) unsigned NOT NULL DEFAULT 0,
-  `acquired` int(10) unsigned NOT NULL DEFAULT 0,
-  `released` int(10) unsigned NOT NULL DEFAULT 1,
-  PRIMARY KEY (`id`),
-  INDEX (`pool`),
-  INDEX (`identity`),
-  INDEX (`address`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-DROP TABLE IF EXISTS leases;
-CREATE TABLE leases (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `address` int(10) unsigned NOT NULL,
-  `identity` int(10) unsigned NOT NULL,
-  `acquired` int(10) unsigned NOT NULL,
-  `released` int(10) unsigned DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-DROP TABLE IF EXISTS attribute_pools;
-CREATE TABLE attribute_pools (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `name` varchar(32) NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-DROP TABLE IF EXISTS attributes;
-CREATE TABLE attributes (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `identity` int(10) unsigned NOT NULL default '0',
-  `pool` int(10) unsigned NOT NULL default '0',
-  `type` int(10) unsigned NOT NULL,
-  `value` varbinary(16) NOT NULL,
-  PRIMARY KEY (`id`),
-  INDEX (`identity`),
-  INDEX (`pool`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-DROP TABLE IF EXISTS ike_sas;
-CREATE TABLE ike_sas (
-  `local_spi` varbinary(8) NOT NULL,
-  `remote_spi` varbinary(8) NOT NULL,
-  `id` int(10) unsigned NOT NULL,
-  `initiator` tinyint(1) NOT NULL,
-  `local_id_type` tinyint(3) NOT NULL,
-  `local_id_data` varbinary(64) DEFAULT NULL,
-  `remote_id_type` tinyint(3) NOT NULL,
-  `remote_id_data` varbinary(64) DEFAULT NULL,
-  `host_family` tinyint(3) NOT NULL,
-  `local_host_data` varbinary(16) NOT NULL,
-  `remote_host_data` varbinary(16) NOT NULL,
-  `lastuse` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-  PRIMARY KEY (`local_spi`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-
-DROP TABLE IF EXISTS logs;
-CREATE TABLE logs (
-  `id` int(10) unsigned NOT NULL auto_increment,
-  `local_spi` varbinary(8) NOT NULL,
-  `signal` tinyint(3) NOT NULL,
-  `level` tinyint(3) NOT NULL,
-  `msg` varchar(256) NOT NULL,
-  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
-

diff --git a/src/libcharon/plugins/sql/sqlite.sql b/src/libcharon/plugins/sql/sqlite.sql
deleted file mode 100644 (file)
index 7801263..0000000
--- a/src/libcharon/plugins/sql/sqlite.sql
+++ /dev/null
@@ -1,283 +0,0 @@
-
-
-DROP TABLE IF EXISTS identities;
-CREATE TABLE identities (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  type INTEGER NOT NULL,
-  data BLOB NOT NULL,
-  UNIQUE (type, data)
-);
-
-
-DROP TABLE IF EXISTS child_configs;
-CREATE TABLE child_configs (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  name TEXT NOT NULL,
-  lifetime INTEGER NOT NULL DEFAULT '1500',
-  rekeytime INTEGER NOT NULL DEFAULT '1200',
-  jitter INTEGER NOT NULL DEFAULT '60',
-  updown TEXT DEFAULT NULL,
-  hostaccess INTEGER NOT NULL DEFAULT '0',
-  mode INTEGER NOT NULL DEFAULT '2',
-  start_action INTEGER NOT NULL DEFAULT '0',
-  dpd_action INTEGER NOT NULL DEFAULT '0',
-  close_action INTEGER NOT NULL DEFAULT '0',
-  ipcomp INTEGER NOT NULL DEFAULT '0',
-  reqid INTEGER NOT NULL DEFAULT '0'
-);
-DROP INDEX IF EXISTS child_configs_name;
-CREATE INDEX child_configs_name ON child_configs (
-  name
-);
-
-
-DROP TABLE IF EXISTS child_config_traffic_selector;
-CREATE TABLE child_config_traffic_selector (
-  child_cfg INTEGER NOT NULL,
-  traffic_selector INTEGER NOT NULL,
-  kind INTEGER NOT NULL
-);
-DROP INDEX IF EXISTS child_config_traffic_selector;
-CREATE INDEX child_config_traffic_selector_all ON child_config_traffic_selector (
-  child_cfg, traffic_selector
-);
-
-DROP TABLE IF EXISTS proposals;
-CREATE TABLE proposals (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  proposal TEXT NOT NULL
-);
-
-
-DROP TABLE IF EXISTS child_config_proposal;
-CREATE TABLE child_config_proposal (
-  child_cfg INTEGER NOT NULL,
-  prio INTEGER NOT NULL,
-  prop INTEGER NOT NULL
-);
-
-
-DROP TABLE IF EXISTS ike_configs;
-CREATE TABLE ike_configs (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  certreq INTEGER NOT NULL DEFAULT '1',
-  force_encap INTEGER NOT NULL DEFAULT '0',
-  local TEXT NOT NULL,
-  remote TEXT NOT NULL
-);
-
-
-DROP TABLE IF EXISTS ike_config_proposal;
-CREATE TABLE ike_config_proposal (
-  ike_cfg INTEGER NOT NULL,
-  prio INTEGER NOT NULL,
-  prop INTEGER NOT NULL
-);
-
-
-DROP TABLE IF EXISTS peer_configs;
-CREATE TABLE peer_configs (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  name TEXT NOT NULL,
-  ike_version INTEGER NOT NULL DEFAULT '2',
-  ike_cfg INTEGER NOT NULL,
-  local_id TEXT NOT NULL,
-  remote_id TEXT NOT NULL,
-  cert_policy INTEGER NOT NULL DEFAULT '1',
-  uniqueid INTEGER NOT NULL DEFAULT '0',
-  auth_method INTEGER NOT NULL DEFAULT '1',
-  eap_type INTEGER NOT NULL DEFAULT '0',
-  eap_vendor INTEGER NOT NULL DEFAULT '0',
-  keyingtries INTEGER NOT NULL DEFAULT '3',
-  rekeytime INTEGER NOT NULL DEFAULT '7200',
-  reauthtime INTEGER NOT NULL DEFAULT '0',
-  jitter INTEGER NOT NULL DEFAULT '180',
-  overtime INTEGER NOT NULL DEFAULT '300',
-  mobike INTEGER NOT NULL DEFAULT '1',
-  dpd_delay INTEGER NOT NULL DEFAULT '120',
-  virtual TEXT DEFAULT NULL,
-  pool TEXT DEFAULT NULL,
-  mediation INTEGER NOT NULL DEFAULT '0',
-  mediated_by INTEGER NOT NULL DEFAULT '0',
-  peer_id INTEGER NOT NULL DEFAULT '0'
-);
-DROP INDEX IF EXISTS peer_configs_name;
-CREATE INDEX peer_configs_name ON peer_configs (
-  name
-);
-
-
-DROP TABLE IF EXISTS peer_config_child_config;
-CREATE TABLE peer_config_child_config (
-  peer_cfg INTEGER NOT NULL,
-  child_cfg INTEGER NOT NULL,
-  PRIMARY KEY (peer_cfg, child_cfg)
-);
-
-
-DROP TABLE IF EXISTS traffic_selectors;
-CREATE TABLE traffic_selectors (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  type INTEGER NOT NULL DEFAULT '7',
-  protocol INTEGER NOT NULL DEFAULT '0',
-  start_addr BLOB DEFAULT NULL,
-  end_addr BLOB DEFAULT NULL,
-  start_port INTEGER NOT NULL DEFAULT '0',
-  end_port INTEGER NOT NULL DEFAULT '65535'
-);
-
-
-DROP TABLE IF EXISTS certificates;
-CREATE TABLE certificates (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  type INTEGER NOT NULL,
-  keytype INTEGER NOT NULL,
-  data BLOB NOT NULL
-);
-
-
-DROP TABLE IF EXISTS certificate_identity;
-CREATE TABLE certificate_identity (
-  certificate INTEGER NOT NULL,
-  identity INTEGER NOT NULL,
-  PRIMARY KEY (certificate, identity)
-);
-
-
-DROP TABLE IF EXISTS private_keys;
-CREATE TABLE private_keys (
-  id INTEGER NOT NULL  PRIMARY KEY AUTOINCREMENT,
-  type INTEGER NOT NULL,
-  data BLOB NOT NULL
-);
-
-
-DROP TABLE IF EXISTS private_key_identity;
-CREATE TABLE private_key_identity (
-  private_key INTEGER NOT NULL,
-  identity INTEGER NOT NULL,
-  PRIMARY KEY (private_key, identity)
-);
-
-
-DROP TABLE IF EXISTS shared_secrets;
-CREATE TABLE shared_secrets (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  type INTEGER NOT NULL,
-  data BLOB NOT NULL
-);
-
-
-DROP TABLE IF EXISTS shared_secret_identity;
-CREATE TABLE shared_secret_identity (
-  shared_secret INTEGER NOT NULL,
-  identity INTEGER NOT NULL,
-  PRIMARY KEY (shared_secret, identity)
-);
-
-
-DROP TABLE IF EXISTS certificate_authorities;
-CREATE TABLE certificate_authorities (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  certificate INTEGER NOT NULL
-);
-
-
-DROP TABLE IF EXISTS certificate_distribution_points;
-CREATE TABLE certificate_distribution_points (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  ca INTEGER NOT NULL,
-  type INTEGER NOT NULL,
-  uri TEXT NOT NULL
-);
-
-
-DROP TABLE IF EXISTS pools;
-CREATE TABLE pools (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  name TEXT NOT NULL UNIQUE,
-  start BLOB NOT NULL,
-  end BLOB NOT NULL,
-  timeout INTEGER NOT NULL
-);
-
-DROP TABLE IF EXISTS addresses;
-CREATE TABLE addresses (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  pool INTEGER NOT NULL,
-  address BLOB NOT NULL,
-  identity INTEGER NOT NULL DEFAULT 0,
-  acquired INTEGER NOT NULL DEFAULT 0,
-  released INTEGER NOT NULL DEFAULT 1
-);
-DROP INDEX IF EXISTS addresses_pool;
-CREATE INDEX addresses_pool ON addresses (
-  pool
-);
-DROP INDEX IF EXISTS addresses_address;
-CREATE INDEX addresses_address ON addresses (
-  address
-);
-DROP INDEX IF EXISTS addresses_identity;
-CREATE INDEX addresses_identity ON addresses (
-  identity
-);
-
-DROP TABLE IF EXISTS leases;
-CREATE TABLE leases (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  address INTEGER NOT NULL,
-  identity INTEGER NOT NULL,
-  acquired INTEGER NOT NULL,
-  released INTEGER NOT NULL
-);
-
-DROP TABLE IF EXISTS attribute_pools;
-CREATE TABLE attribute_pools (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  name TEXT NOT NULL
-);
-
-DROP TABLE IF EXISTS attributes;
-CREATE TABLE attributes (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  identity INTEGER NOT NULL DEFAULT 0,
-  pool INTEGER NOT NULL DEFAULT 0,
-  type INTEGER NOT NULL,
-  value BLOB NOT NULL
-);
-DROP INDEX IF EXISTS attributes_identity;
-CREATE INDEX attributes_identity ON attributes (
-  identity
-);
-DROP INDEX IF EXISTS attributes_pool;
-CREATE INDEX attributes_pool ON attributes (
-  pool
-);
-
-DROP TABLE IF EXISTS ike_sas;
-CREATE TABLE ike_sas (
-  local_spi BLOB NOT NULL PRIMARY KEY,
-  remote_spi BLOB NOT NULL,
-  id INTEGER NOT NULL,
-  initiator INTEGER NOT NULL,
-  local_id_type INTEGER NOT NULL,
-  local_id_data BLOB DEFAULT NULL,
-  remote_id_type INTEGER NOT NULL,
-  remote_id_data BLOB DEFAULT NULL,
-  host_family INTEGER NOT NULL,
-  local_host_data BLOB NOT NULL,
-  remote_host_data BLOB NOT NULL,
-  created INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
-);
-
-DROP TABLE IF EXISTS logs;
-CREATE TABLE logs (
-  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-  local_spi BLOB NOT NULL,
-  signal INTEGER NOT NULL,
-  level INTEGER NOT NULL,
-  msg TEXT NOT NULL,
-  time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
-);
-

diff --git a/src/pool/Makefile.am b/src/pool/Makefile.am
index 8b429a4..2756f93 100644 (file)
--- a/src/pool/Makefile.am
+++ b/src/pool/Makefile.am
@@ -1,3 +1,5 @@
+if USE_ATTR_SQL
+
 ipsec_PROGRAMS = pool
 
 pool_SOURCES = \
@@ -14,3 +16,8 @@ AM_CPPFLAGS = \
 pool_LDADD = \
        $(top_builddir)/src/libstrongswan/libstrongswan.la \
        $(top_builddir)/src/libhydra/libhydra.la
+
+endif !USE_ATTR_SQL
+
+templatesdir = $(pkgdatadir)/templates/database/sql
+dist_templates_DATA = mysql.sql sqlite.sql

diff --git a/src/pool/mysql.sql b/src/pool/mysql.sql
new file mode 100644 (file)
index 0000000..1b43759
--- /dev/null
+++ b/src/pool/mysql.sql
@@ -0,0 +1,281 @@
+
+DROP TABLE IF EXISTS `identities`;
+CREATE TABLE `identities` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `type` tinyint(4) unsigned NOT NULL,
+  `data` varbinary(64) NOT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE (`type`, `data`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `child_configs`;
+CREATE TABLE `child_configs` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `name` varchar(32) collate utf8_unicode_ci NOT NULL,
+  `lifetime` mediumint(8) unsigned NOT NULL default '1500',
+  `rekeytime` mediumint(8) unsigned NOT NULL default '1200',
+  `jitter` mediumint(8) unsigned NOT NULL default '60',
+  `updown` varchar(128) collate utf8_unicode_ci default NULL,
+  `hostaccess` tinyint(1) unsigned NOT NULL default '0',
+  `mode` tinyint(4) unsigned NOT NULL default '2',
+  `start_action` tinyint(4) unsigned NOT NULL default '0',
+  `dpd_action` tinyint(4) unsigned NOT NULL default '0',
+  `close_action` tinyint(4) unsigned NOT NULL default '0',
+  `ipcomp` tinyint(4) unsigned NOT NULL default '0',
+  `reqid` mediumint(8) unsigned NOT NULL default '0',
+  PRIMARY KEY (`id`),
+  INDEX (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `child_config_traffic_selector`;
+CREATE TABLE `child_config_traffic_selector` (
+  `child_cfg` int(10) unsigned NOT NULL,
+  `traffic_selector` int(10) unsigned NOT NULL,
+  `kind` tinyint(3) unsigned NOT NULL,
+  INDEX (`child_cfg`, `traffic_selector`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `proposals`;
+CREATE TABLE `proposals` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `proposal` varchar(128) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `child_config_proposal`;
+CREATE TABLE `child_config_proposal` (
+  `child_cfg` int(10) unsigned NOT NULL,
+  `prio` smallint(5) unsigned NOT NULL,
+  `prop` int(10) unsigned NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `ike_configs`;
+CREATE TABLE `ike_configs` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `certreq` tinyint(3) unsigned NOT NULL default '1',
+  `force_encap` tinyint(1) NOT NULL default '0',
+  `local` varchar(128) collate utf8_unicode_ci NOT NULL,
+  `remote` varchar(128) collate utf8_unicode_ci NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `ike_config_proposal`;
+CREATE TABLE `ike_config_proposal` (
+  `ike_cfg` int(10) unsigned NOT NULL,
+  `prio` smallint(5) unsigned NOT NULL,
+  `prop` int(10) unsigned NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `peer_configs`;
+CREATE TABLE `peer_configs` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `name` varchar(32) collate utf8_unicode_ci NOT NULL,
+  `ike_version` tinyint(3) unsigned NOT NULL default '2',
+  `ike_cfg` int(10) unsigned NOT NULL,
+  `local_id` varchar(64) collate utf8_unicode_ci NOT NULL,
+  `remote_id` varchar(64) collate utf8_unicode_ci NOT NULL,
+  `cert_policy` tinyint(3) unsigned NOT NULL default '1',
+  `uniqueid` tinyint(3) unsigned NOT NULL default '0',
+  `auth_method` tinyint(3) unsigned NOT NULL default '1',
+  `eap_type` tinyint(3) unsigned NOT NULL default '0',
+  `eap_vendor` smallint(5) unsigned NOT NULL default '0',
+  `keyingtries` tinyint(3) unsigned NOT NULL default '3',
+  `rekeytime` mediumint(8) unsigned NOT NULL default '7200',
+  `reauthtime` mediumint(8) unsigned NOT NULL default '0',
+  `jitter` mediumint(8) unsigned NOT NULL default '180',
+  `overtime` mediumint(8) unsigned NOT NULL default '300',
+  `mobike` tinyint(1) NOT NULL default '1',
+  `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
+  `virtual` varchar(40) default NULL,
+  `pool` varchar(32) default NULL,
+  `mediation` tinyint(1) NOT NULL default '0',
+  `mediated_by` int(10) unsigned NOT NULL default '0',
+  `peer_id` int(10) unsigned NOT NULL default '0',
+  PRIMARY KEY (`id`),
+  INDEX (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `peer_config_child_config`;
+CREATE TABLE `peer_config_child_config` (
+  `peer_cfg` int(10) unsigned NOT NULL,
+  `child_cfg` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`peer_cfg`, `child_cfg`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `traffic_selectors`;
+CREATE TABLE `traffic_selectors` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `type` tinyint(3) unsigned NOT NULL default '7',
+  `protocol` smallint(5) unsigned NOT NULL default '0',
+  `start_addr` varbinary(16) default NULL,
+  `end_addr` varbinary(16) default NULL,
+  `start_port` smallint(5) unsigned NOT NULL default '0',
+  `end_port` smallint(5) unsigned NOT NULL default '65535',
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS certificates;
+CREATE TABLE certificates (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `type` tinyint(3) unsigned NOT NULL,
+  `keytype` tinyint(3) unsigned NOT NULL,
+  `data` BLOB NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS certificate_identity;
+CREATE TABLE certificate_identity (
+  `certificate` int(10) unsigned NOT NULL,
+  `identity` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`certificate`, `identity`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS private_keys;
+CREATE TABLE private_keys (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `type` tinyint(3) unsigned NOT NULL,
+  `data` BLOB NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS private_key_identity;
+CREATE TABLE private_key_identity (
+  `private_key` int(10) unsigned NOT NULL,
+  `identity` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`private_key`, `identity`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS shared_secrets;
+CREATE TABLE shared_secrets (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `type` tinyint(3) unsigned NOT NULL,
+  `data` varbinary(256) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS shared_secret_identity;
+CREATE TABLE shared_secret_identity (
+  `shared_secret` int(10) unsigned NOT NULL,
+  `identity` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`shared_secret`, `identity`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS certificate_authorities;
+CREATE TABLE certificate_authorities (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `certificate` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS certificate_distribution_points;
+CREATE TABLE certificate_distribution_points (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `ca` int(10) unsigned NOT NULL,
+  `type` tinyint(3) unsigned NOT NULL,
+  `uri` varchar(256) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS pools;
+CREATE TABLE pools (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `name` varchar(32) NOT NULL,
+  `start` varbinary(16) NOT NULL,
+  `end` varbinary(16) NOT NULL,
+  `timeout` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS addresses;
+CREATE TABLE addresses (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `pool` int(10) unsigned NOT NULL,
+  `address` varbinary(16) NOT NULL,
+  `identity` int(10) unsigned NOT NULL DEFAULT 0,
+  `acquired` int(10) unsigned NOT NULL DEFAULT 0,
+  `released` int(10) unsigned NOT NULL DEFAULT 1,
+  PRIMARY KEY (`id`),
+  INDEX (`pool`),
+  INDEX (`identity`),
+  INDEX (`address`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+DROP TABLE IF EXISTS leases;
+CREATE TABLE leases (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `address` int(10) unsigned NOT NULL,
+  `identity` int(10) unsigned NOT NULL,
+  `acquired` int(10) unsigned NOT NULL,
+  `released` int(10) unsigned DEFAULT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+DROP TABLE IF EXISTS attribute_pools;
+CREATE TABLE attribute_pools (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `name` varchar(32) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+DROP TABLE IF EXISTS attributes;
+CREATE TABLE attributes (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `identity` int(10) unsigned NOT NULL default '0',
+  `pool` int(10) unsigned NOT NULL default '0',
+  `type` int(10) unsigned NOT NULL,
+  `value` varbinary(16) NOT NULL,
+  PRIMARY KEY (`id`),
+  INDEX (`identity`),
+  INDEX (`pool`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+DROP TABLE IF EXISTS ike_sas;
+CREATE TABLE ike_sas (
+  `local_spi` varbinary(8) NOT NULL,
+  `remote_spi` varbinary(8) NOT NULL,
+  `id` int(10) unsigned NOT NULL,
+  `initiator` tinyint(1) NOT NULL,
+  `local_id_type` tinyint(3) NOT NULL,
+  `local_id_data` varbinary(64) DEFAULT NULL,
+  `remote_id_type` tinyint(3) NOT NULL,
+  `remote_id_data` varbinary(64) DEFAULT NULL,
+  `host_family` tinyint(3) NOT NULL,
+  `local_host_data` varbinary(16) NOT NULL,
+  `remote_host_data` varbinary(16) NOT NULL,
+  `lastuse` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (`local_spi`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS logs;
+CREATE TABLE logs (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `local_spi` varbinary(8) NOT NULL,
+  `signal` tinyint(3) NOT NULL,
+  `level` tinyint(3) NOT NULL,
+  `msg` varchar(256) NOT NULL,
+  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+

diff --git a/src/pool/sqlite.sql b/src/pool/sqlite.sql
new file mode 100644 (file)
index 0000000..7801263
--- /dev/null
+++ b/src/pool/sqlite.sql
@@ -0,0 +1,283 @@
+
+
+DROP TABLE IF EXISTS identities;
+CREATE TABLE identities (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  type INTEGER NOT NULL,
+  data BLOB NOT NULL,
+  UNIQUE (type, data)
+);
+
+
+DROP TABLE IF EXISTS child_configs;
+CREATE TABLE child_configs (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  name TEXT NOT NULL,
+  lifetime INTEGER NOT NULL DEFAULT '1500',
+  rekeytime INTEGER NOT NULL DEFAULT '1200',
+  jitter INTEGER NOT NULL DEFAULT '60',
+  updown TEXT DEFAULT NULL,
+  hostaccess INTEGER NOT NULL DEFAULT '0',
+  mode INTEGER NOT NULL DEFAULT '2',
+  start_action INTEGER NOT NULL DEFAULT '0',
+  dpd_action INTEGER NOT NULL DEFAULT '0',
+  close_action INTEGER NOT NULL DEFAULT '0',
+  ipcomp INTEGER NOT NULL DEFAULT '0',
+  reqid INTEGER NOT NULL DEFAULT '0'
+);
+DROP INDEX IF EXISTS child_configs_name;
+CREATE INDEX child_configs_name ON child_configs (
+  name
+);
+
+
+DROP TABLE IF EXISTS child_config_traffic_selector;
+CREATE TABLE child_config_traffic_selector (
+  child_cfg INTEGER NOT NULL,
+  traffic_selector INTEGER NOT NULL,
+  kind INTEGER NOT NULL
+);
+DROP INDEX IF EXISTS child_config_traffic_selector;
+CREATE INDEX child_config_traffic_selector_all ON child_config_traffic_selector (
+  child_cfg, traffic_selector
+);
+
+DROP TABLE IF EXISTS proposals;
+CREATE TABLE proposals (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  proposal TEXT NOT NULL
+);
+
+
+DROP TABLE IF EXISTS child_config_proposal;
+CREATE TABLE child_config_proposal (
+  child_cfg INTEGER NOT NULL,
+  prio INTEGER NOT NULL,
+  prop INTEGER NOT NULL
+);
+
+
+DROP TABLE IF EXISTS ike_configs;
+CREATE TABLE ike_configs (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  certreq INTEGER NOT NULL DEFAULT '1',
+  force_encap INTEGER NOT NULL DEFAULT '0',
+  local TEXT NOT NULL,
+  remote TEXT NOT NULL
+);
+
+
+DROP TABLE IF EXISTS ike_config_proposal;
+CREATE TABLE ike_config_proposal (
+  ike_cfg INTEGER NOT NULL,
+  prio INTEGER NOT NULL,
+  prop INTEGER NOT NULL
+);
+
+
+DROP TABLE IF EXISTS peer_configs;
+CREATE TABLE peer_configs (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  name TEXT NOT NULL,
+  ike_version INTEGER NOT NULL DEFAULT '2',
+  ike_cfg INTEGER NOT NULL,
+  local_id TEXT NOT NULL,
+  remote_id TEXT NOT NULL,
+  cert_policy INTEGER NOT NULL DEFAULT '1',
+  uniqueid INTEGER NOT NULL DEFAULT '0',
+  auth_method INTEGER NOT NULL DEFAULT '1',
+  eap_type INTEGER NOT NULL DEFAULT '0',
+  eap_vendor INTEGER NOT NULL DEFAULT '0',
+  keyingtries INTEGER NOT NULL DEFAULT '3',
+  rekeytime INTEGER NOT NULL DEFAULT '7200',
+  reauthtime INTEGER NOT NULL DEFAULT '0',
+  jitter INTEGER NOT NULL DEFAULT '180',
+  overtime INTEGER NOT NULL DEFAULT '300',
+  mobike INTEGER NOT NULL DEFAULT '1',
+  dpd_delay INTEGER NOT NULL DEFAULT '120',
+  virtual TEXT DEFAULT NULL,
+  pool TEXT DEFAULT NULL,
+  mediation INTEGER NOT NULL DEFAULT '0',
+  mediated_by INTEGER NOT NULL DEFAULT '0',
+  peer_id INTEGER NOT NULL DEFAULT '0'
+);
+DROP INDEX IF EXISTS peer_configs_name;
+CREATE INDEX peer_configs_name ON peer_configs (
+  name
+);
+
+
+DROP TABLE IF EXISTS peer_config_child_config;
+CREATE TABLE peer_config_child_config (
+  peer_cfg INTEGER NOT NULL,
+  child_cfg INTEGER NOT NULL,
+  PRIMARY KEY (peer_cfg, child_cfg)
+);
+
+
+DROP TABLE IF EXISTS traffic_selectors;
+CREATE TABLE traffic_selectors (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  type INTEGER NOT NULL DEFAULT '7',
+  protocol INTEGER NOT NULL DEFAULT '0',
+  start_addr BLOB DEFAULT NULL,
+  end_addr BLOB DEFAULT NULL,
+  start_port INTEGER NOT NULL DEFAULT '0',
+  end_port INTEGER NOT NULL DEFAULT '65535'
+);
+
+
+DROP TABLE IF EXISTS certificates;
+CREATE TABLE certificates (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  type INTEGER NOT NULL,
+  keytype INTEGER NOT NULL,
+  data BLOB NOT NULL
+);
+
+
+DROP TABLE IF EXISTS certificate_identity;
+CREATE TABLE certificate_identity (
+  certificate INTEGER NOT NULL,
+  identity INTEGER NOT NULL,
+  PRIMARY KEY (certificate, identity)
+);
+
+
+DROP TABLE IF EXISTS private_keys;
+CREATE TABLE private_keys (
+  id INTEGER NOT NULL  PRIMARY KEY AUTOINCREMENT,
+  type INTEGER NOT NULL,
+  data BLOB NOT NULL
+);
+
+
+DROP TABLE IF EXISTS private_key_identity;
+CREATE TABLE private_key_identity (
+  private_key INTEGER NOT NULL,
+  identity INTEGER NOT NULL,
+  PRIMARY KEY (private_key, identity)
+);
+
+
+DROP TABLE IF EXISTS shared_secrets;
+CREATE TABLE shared_secrets (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  type INTEGER NOT NULL,
+  data BLOB NOT NULL
+);
+
+
+DROP TABLE IF EXISTS shared_secret_identity;
+CREATE TABLE shared_secret_identity (
+  shared_secret INTEGER NOT NULL,
+  identity INTEGER NOT NULL,
+  PRIMARY KEY (shared_secret, identity)
+);
+
+
+DROP TABLE IF EXISTS certificate_authorities;
+CREATE TABLE certificate_authorities (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  certificate INTEGER NOT NULL
+);
+
+
+DROP TABLE IF EXISTS certificate_distribution_points;
+CREATE TABLE certificate_distribution_points (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  ca INTEGER NOT NULL,
+  type INTEGER NOT NULL,
+  uri TEXT NOT NULL
+);
+
+
+DROP TABLE IF EXISTS pools;
+CREATE TABLE pools (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  name TEXT NOT NULL UNIQUE,
+  start BLOB NOT NULL,
+  end BLOB NOT NULL,
+  timeout INTEGER NOT NULL
+);
+
+DROP TABLE IF EXISTS addresses;
+CREATE TABLE addresses (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  pool INTEGER NOT NULL,
+  address BLOB NOT NULL,
+  identity INTEGER NOT NULL DEFAULT 0,
+  acquired INTEGER NOT NULL DEFAULT 0,
+  released INTEGER NOT NULL DEFAULT 1
+);
+DROP INDEX IF EXISTS addresses_pool;
+CREATE INDEX addresses_pool ON addresses (
+  pool
+);
+DROP INDEX IF EXISTS addresses_address;
+CREATE INDEX addresses_address ON addresses (
+  address
+);
+DROP INDEX IF EXISTS addresses_identity;
+CREATE INDEX addresses_identity ON addresses (
+  identity
+);
+
+DROP TABLE IF EXISTS leases;
+CREATE TABLE leases (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  address INTEGER NOT NULL,
+  identity INTEGER NOT NULL,
+  acquired INTEGER NOT NULL,
+  released INTEGER NOT NULL
+);
+
+DROP TABLE IF EXISTS attribute_pools;
+CREATE TABLE attribute_pools (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  name TEXT NOT NULL
+);
+
+DROP TABLE IF EXISTS attributes;
+CREATE TABLE attributes (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  identity INTEGER NOT NULL DEFAULT 0,
+  pool INTEGER NOT NULL DEFAULT 0,
+  type INTEGER NOT NULL,
+  value BLOB NOT NULL
+);
+DROP INDEX IF EXISTS attributes_identity;
+CREATE INDEX attributes_identity ON attributes (
+  identity
+);
+DROP INDEX IF EXISTS attributes_pool;
+CREATE INDEX attributes_pool ON attributes (
+  pool
+);
+
+DROP TABLE IF EXISTS ike_sas;
+CREATE TABLE ike_sas (
+  local_spi BLOB NOT NULL PRIMARY KEY,
+  remote_spi BLOB NOT NULL,
+  id INTEGER NOT NULL,
+  initiator INTEGER NOT NULL,
+  local_id_type INTEGER NOT NULL,
+  local_id_data BLOB DEFAULT NULL,
+  remote_id_type INTEGER NOT NULL,
+  remote_id_data BLOB DEFAULT NULL,
+  host_family INTEGER NOT NULL,
+  local_host_data BLOB NOT NULL,
+  remote_host_data BLOB NOT NULL,
+  created INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+DROP TABLE IF EXISTS logs;
+CREATE TABLE logs (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  local_spi BLOB NOT NULL,
+  signal INTEGER NOT NULL,
+  level INTEGER NOT NULL,
+  msg TEXT NOT NULL,
+  time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+