better normalized tables for SQL plugin (IDs)

6 files changed, 453 insertions, 286 deletions

diff --git a/src/charon/plugins/sql/mysql.sql b/src/charon/plugins/sql/mysql.sql
index 72a775090..a480601a3 100644
--- a/src/charon/plugins/sql/mysql.sql
+++ b/src/charon/plugins/sql/mysql.sql
@@ -1,5 +1,15 @@
 
 
+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=MyISAM  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,
@@ -10,7 +20,8 @@ CREATE TABLE `child_configs` (
   `updown` varchar(128) collate utf8_unicode_ci default NULL,
   `hostaccess` tinyint(1) unsigned NOT NULL default '1',
   `mode` tinyint(4) unsigned NOT NULL default '1',
-  PRIMARY KEY  (`id`)
+  PRIMARY KEY (`id`),
+  INDEX (`name`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
@@ -18,7 +29,8 @@ 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
+  `kind` tinyint(3) unsigned NOT NULL,
+  INDEX (`child_cfg`, `traffic_selector`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
@@ -27,15 +39,15 @@ 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(64) collate utf8_unicode_ci NOT NULL,
-  `remote` varchar(64) collate utf8_unicode_ci NOT NULL,
-  PRIMARY KEY  (`id`)
+  `local` varchar(128) collate utf8_unicode_ci NOT NULL,
+  `remote` varchar(128) collate utf8_unicode_ci NOT NULL,
+  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS `peer_configs`;
 CREATE TABLE `peer_configs` (
-  `id` int(11) NOT NULL auto_increment,
+  `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,
@@ -53,19 +65,19 @@ CREATE TABLE `peer_configs` (
   `mobike` tinyint(1) NOT NULL default '1',
   `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
   `dpd_action` tinyint(3) unsigned NOT NULL default '1',
-  `local_vip` varchar(128) collate utf8_unicode_ci default NULL,
-  `remote_vip` varchar(128) collate utf8_unicode_ci default NULL,
   `mediation` tinyint(1) NOT NULL default '0',
-  `mediated_by` int(11) NOT NULL default '0',
-  `peer_id` varchar(64) collate utf8_unicode_ci default NULL,
-  PRIMARY KEY  (`id`)
+  `mediated_by` int(10) unsigned NOT NULL default '0',
+  `peer_id` int(10) unsigned NOT NULL default '0',
+  PRIMARY KEY (`id`),
+  INDEX (`name`)
 ) ENGINE=MyISAM  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
+  `child_cfg` int(10) unsigned NOT NULL,
+  PRIMARY KEY (`peer_cfg`, `child_cfg`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
@@ -74,22 +86,11 @@ 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` varchar(40) collate utf8_unicode_ci default NULL,
-  `end_addr` varchar(40) collate utf8_unicode_ci default NULL,
+  `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=MyISAM  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,
-  `local` varchar(64) default NULL,
-  `remote` varchar(64) default NULL,
-  `data` BLOB NOT NULL,
-  PRIMARY KEY  (`id`)
+  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
@@ -98,10 +99,16 @@ CREATE TABLE certificates (
   `id` int(10) unsigned NOT NULL auto_increment,
   `type` tinyint(3) unsigned NOT NULL,
   `keytype` tinyint(3) unsigned NOT NULL,
-  `keyid` BLOB NOT NULL,
-  `subject` varchar(64) default NULL,
   `data` BLOB NOT NULL,
-  PRIMARY KEY  (`id`)
+  PRIMARY KEY (`id`)
+) ENGINE=MyISAM  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=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
@@ -109,33 +116,63 @@ DROP TABLE IF EXISTS private_keys;
 CREATE TABLE private_keys (
   `id` int(10) unsigned NOT NULL auto_increment,
   `type` tinyint(3) unsigned NOT NULL,
-  `keyid` tinyblob NOT NULL,
   `data` BLOB NOT NULL,
   PRIMARY KEY  (`id`)
 ) ENGINE=MyISAM  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=MyISAM  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=MyISAM  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=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
 DROP TABLE IF EXISTS ike_sas;
 CREATE TABLE ike_sas (
-  `local_spi` BLOB(8) NOT NULL,
-  `remote_spi` BLOB(8) NOT NULL,
+  `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` varchar(64) collate utf8_unicode_ci default NULL,
-  `remote_id` varchar(64) collate utf8_unicode_ci default NULL,
-  `local` varchar(64) collate utf8_unicode_ci NOT NULL,
-  `remote` varchar(64) collate utf8_unicode_ci NOT NULL,
-  PRIMARY KEY  (local_spi(8))
+  `local_id_type` tinyint(3) NOT NULL,
+  `local_id_data` varbinary(64) NOT NULL,
+  `remote_id_type` tinyint(3) NOT NULL,
+  `remote_id_data` varbinary(64) NOT 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=MyISAM  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` BLOB(8) NOT NULL,
+  `local_spi` varbinary(8) NOT NULL,
   `signal` tinyint(3) NOT NULL,
   `level` tinyint(3) NOT NULL,
-  `msg`varchar(256) NOT NULL,
-  PRIMARY KEY  (`id`)
+  `msg` varchar(256) NOT NULL,
+  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
+  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
diff --git a/src/charon/plugins/sql/sql_config.c b/src/charon/plugins/sql/sql_config.c
index eaa9da5ef..1cc52ac8b 100644
--- a/src/charon/plugins/sql/sql_config.c
+++ b/src/charon/plugins/sql/sql_config.c
@@ -52,7 +52,7 @@ static traffic_selector_t *build_traffic_selector(private_sql_config_t *this,
 												  enumerator_t *e, bool *local)
 {
 	int type, protocol, start_port, end_port;
-	char *start_addr, *end_addr;
+	chunk_t start_addr, end_addr;
 	traffic_selector_t *ts;
 	enum {
 		TS_LOCAL = 0,
@@ -71,8 +71,8 @@ static traffic_selector_t *build_traffic_selector(private_sql_config_t *this,
 				*local = TRUE;
 				/* FALL */
 			case TS_REMOTE:
-				ts = traffic_selector_create_from_string(protocol, type, 
-								start_addr, start_port, end_addr, end_port);
+				ts = traffic_selector_create_from_bytes(protocol, type,
+								start_addr, start_port,	end_addr, end_port);
 				break;
 			case TS_LOCAL_DYNAMIC:
 				*local = TRUE;
@@ -109,7 +109,7 @@ static void add_traffic_selectors(private_sql_config_t *this,
 			"ON id = traffic_selector WHERE child_cfg = ?",
 			DB_INT, id,
 			DB_INT, DB_INT, DB_INT,
-			DB_TEXT, DB_TEXT, DB_INT, DB_INT);
+			DB_BLOB, DB_BLOB, DB_INT, DB_INT);
 	if (e)
 	{
 		while ((ts = build_traffic_selector(this, e, &local)))
@@ -244,18 +244,20 @@ static peer_cfg_t *get_peer_cfg_by_id(private_sql_config_t *this, int id)
 	peer_cfg_t *peer_cfg = NULL;
 	
 	e = this->db->query(this->db,
-			"SELECT id, name, ike_cfg, local_id, remote_id, cert_policy, "
-			"auth_method, eap_type, eap_vendor, keyingtries, "
-			"rekeytime, reauthtime, jitter, overtime, mobike, "
-			"dpd_delay, dpd_action, local_vip, remote_vip, "
-			"mediation, mediated_by, peer_id "
-			"FROM peer_configs WHERE id = ?",
+			"SELECT c.id, name, ike_cfg, l.type, l.data, r.type, r.data, "
+			"cert_policy, auth_method, eap_type, eap_vendor, keyingtries, "
+			"rekeytime, reauthtime, jitter, overtime, mobike, dpd_delay, "
+			"dpd_action, mediation, mediated_by, COALESCE(p.type, 0), p.data "
+			"FROM peer_configs AS c "
+			"JOIN identities AS l ON local_id = l.id "
+			"JOIN identities AS r ON remote_id = r.id "
+			"LEFT JOIN identities AS p ON peer_id = p.id "
+			"WHERE id = ?",
 			DB_INT, id,
-			DB_INT, DB_INT, DB_TEXT, DB_TEXT, DB_INT,
-			DB_INT, DB_INT, DB_INT, DB_INT,
+			DB_INT, DB_TEXT, DB_INT, DB_INT, DB_BLOB, DB_INT, DB_BLOB,
 			DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
-			DB_INT, DB_INT, DB_TEXT, DB_TEXT,
-			DB_INT, DB_INT, DB_TEXT);
+			DB_INT, DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+			DB_INT, DB_INT, DB_INT, DB_INT, DB_BLOB);
 	if (e)
 	{
 		peer_cfg = build_peer_cfg(this, e, NULL, NULL);
@@ -270,68 +272,55 @@ static peer_cfg_t *get_peer_cfg_by_id(private_sql_config_t *this, int id)
 static peer_cfg_t *build_peer_cfg(private_sql_config_t *this, enumerator_t *e,
 								  identification_t *me, identification_t *other)
 {
-	int id, ike_cfg, cert_policy, auth_method, eap_type, eap_vendor,
-		keyingtries, rekeytime, reauthtime, jitter, overtime, mobike,
-		dpd_delay, dpd_action, mediation, mediated_by;
-	char *local_id, *remote_id, *local_vip, *remote_vip, *peer_id, *name;
+	int id, ike_cfg, l_type, r_type,
+		cert_policy, auth_method, eap_type, eap_vendor, keyingtries,
+		rekeytime, reauthtime, jitter, overtime, mobike, dpd_delay,
+		dpd_action, mediation, mediated_by, p_type;
+	chunk_t l_data, r_data, p_data;
+	char *name;
 	
-	while (e->enumerate(e, &id, &name, &ike_cfg, &local_id, &remote_id, &cert_policy, 
-						&auth_method, &eap_type, &eap_vendor, &keyingtries, 
-						&rekeytime, &reauthtime, &jitter, &overtime, &mobike, 
-						&dpd_delay, &dpd_action, &local_vip, &remote_vip, 
-						&mediation, &mediated_by, &peer_id))
+	while (e->enumerate(e,
+			&id, &name, &ike_cfg, &l_type, &l_data, &r_type, &r_data,
+			&cert_policy, &auth_method, &eap_type, &eap_vendor, &keyingtries,
+			&rekeytime, &reauthtime, &jitter, &overtime, &mobike, &dpd_delay,
+			&dpd_action, &mediation, &mediated_by, &p_type, &p_data))
 	{
-		ike_cfg_t *ike;
+		identification_t *local_id, *remote_id, *peer_id = NULL;
 		peer_cfg_t *peer_cfg, *mediated_cfg;
-		identification_t *my_id, *other_id, *peer;
-		host_t *my_vip, *other_vip;
+		ike_cfg_t *ike;
 		
-		my_id = identification_create_from_string(local_id);
-		if (!my_id)
+		local_id = identification_create_from_encoding(l_type, l_data);
+		remote_id = identification_create_from_encoding(r_type, r_data);
+		if ((me && !me->matches(me, local_id)) ||
+			(other && !other->matches(other, remote_id)))
 		{
-			continue;
-		}
-		if (me && !me->matches(me, my_id))
-		{
-			my_id->destroy(my_id);
-			continue;
-		}
-		other_id = identification_create_from_string(remote_id);
-		if (!other_id)
-		{
-			my_id->destroy(my_id);
-			continue;
-		}
-		if (other && !other->matches(other, other_id))
-		{
-			other_id->destroy(other_id);
-			my_id->destroy(my_id);
+			local_id->destroy(local_id);
+			remote_id->destroy(remote_id);
 			continue;
 		}
 		ike = get_ike_cfg_by_id(this, ike_cfg);
 		mediated_cfg = mediated_by ? get_peer_cfg_by_id(this, mediated_by) : NULL;
-		peer = peer_id ? identification_create_from_string(peer_id) : NULL;
-		my_vip = local_vip ? host_create_from_string(local_vip, 0) : NULL;
-		other_vip = remote_vip ? host_create_from_string(remote_vip, 0) : NULL;
+		if (p_type)
+		{
+			peer_id = identification_create_from_encoding(p_type, p_data);
+		}
 		
 		if (ike)
 		{
 			peer_cfg = peer_cfg_create(
-								name, 2, ike, my_id, other_id, cert_policy,
-								auth_method, eap_type, eap_vendor, keyingtries, 
-								rekeytime, reauthtime, jitter, overtime, mobike,
-								dpd_delay, dpd_action, my_vip, other_vip,
-								mediation, mediated_cfg, peer);
+							name, 2, ike, local_id, remote_id, cert_policy,
+							auth_method, eap_type, eap_vendor, keyingtries, 
+							rekeytime, reauthtime, jitter, overtime, mobike,
+							dpd_delay, dpd_action, NULL, NULL,
+							mediation, mediated_cfg, peer_id);
 			add_child_cfgs(this, peer_cfg, id);
 			return peer_cfg;
 		}
 		DESTROY_IF(ike);
 		DESTROY_IF(mediated_cfg);
-		DESTROY_IF(peer);
-		DESTROY_IF(my_vip);
-		DESTROY_IF(other_vip);
-		DESTROY_IF(my_id);
-		DESTROY_IF(other_id);
+		DESTROY_IF(peer_id);
+		DESTROY_IF(local_id);
+		DESTROY_IF(remote_id);
 	}
 	return NULL;
 }
@@ -345,18 +334,20 @@ static peer_cfg_t *get_peer_cfg_by_name(private_sql_config_t *this, char *name)
 	peer_cfg_t *peer_cfg = NULL;
 	
 	e = this->db->query(this->db,
-			"SELECT id, name, ike_cfg, local_id, remote_id, cert_policy, "
-			"auth_method, eap_type, eap_vendor, keyingtries, "
-			"rekeytime, reauthtime, jitter, overtime, mobike, "
-			"dpd_delay, dpd_action, local_vip, remote_vip, "
-			"mediation, mediated_by, peer_id "
-			"FROM peer_configs WHERE ike_version = ? AND name = ?",
+			"SELECT c.id, name, ike_cfg, l.type, l.data, r.type, r.data, "
+			"cert_policy, auth_method, eap_type, eap_vendor, keyingtries, "
+			"rekeytime, reauthtime, jitter, overtime, mobike, dpd_delay, "
+			"dpd_action, mediation, mediated_by, COALESCE(p.type, 0), p.data "
+			"FROM peer_configs AS c "
+			"JOIN identities AS l ON local_id = l.id "
+			"JOIN identities AS r ON remote_id = r.id "
+			"LEFT JOIN identities AS p ON peer_id = p.id "
+			"WHERE ike_version = ? AND name = ?",
 			DB_INT, 2, DB_TEXT, name,
-			DB_INT, DB_TEXT, DB_INT, DB_TEXT, DB_TEXT, DB_INT,
-			DB_INT, DB_INT, DB_INT, DB_INT,
+			DB_INT, DB_TEXT, DB_INT, DB_INT, DB_BLOB, DB_INT, DB_BLOB,
 			DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
-			DB_INT, DB_INT, DB_TEXT, DB_TEXT,
-			DB_INT, DB_INT, DB_TEXT);
+			DB_INT, DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+			DB_INT, DB_INT, DB_INT, DB_INT, DB_BLOB);
 	if (e)
 	{
 		peer_cfg = build_peer_cfg(this, e, NULL, NULL);
@@ -491,18 +482,20 @@ static enumerator_t* create_peer_cfg_enumerator(private_sql_config_t *this,
 
 	/* TODO: only get configs whose IDs match exactly or contain wildcards */
 	e->inner = this->db->query(this->db,
-			"SELECT id, name, ike_cfg, local_id, remote_id, cert_policy, "
-			"auth_method, eap_type, eap_vendor, keyingtries, "
-			"rekeytime, reauthtime, jitter, overtime, mobike, "
-			"dpd_delay, dpd_action, local_vip, remote_vip, "
-			"mediation, mediated_by, peer_id "
-			"FROM peer_configs WHERE ike_version = ? ",
+			"SELECT c.id, name, ike_cfg, l.type, l.data, r.type, r.data, "
+			"cert_policy, auth_method, eap_type, eap_vendor, keyingtries, "
+			"rekeytime, reauthtime, jitter, overtime, mobike, dpd_delay, "
+			"dpd_action, mediation, mediated_by, COALESCE(p.type, 0), p.data "
+			"FROM peer_configs AS c "
+			"JOIN identities AS l ON local_id = l.id "
+			"JOIN identities AS r ON remote_id = r.id "
+			"LEFT JOIN identities AS p ON peer_id = p.id "
+			"WHERE ike_version = ?",
 			DB_INT, 2,
-			DB_INT, DB_TEXT, DB_INT, DB_TEXT, DB_TEXT, DB_INT,
-			DB_INT, DB_INT, DB_INT, DB_INT,
+			DB_INT, DB_TEXT, DB_INT, DB_INT, DB_BLOB, DB_INT, DB_BLOB,
 			DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
-			DB_INT, DB_INT, DB_TEXT, DB_TEXT,
-			DB_INT, DB_INT, DB_TEXT);
+			DB_INT, DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+			DB_INT, DB_INT, DB_INT, DB_INT, DB_BLOB);
 	if (!e->inner)
 	{
 		free(e);
diff --git a/src/charon/plugins/sql/sql_cred.c b/src/charon/plugins/sql/sql_cred.c
index 91185ee98..73a6a9c66 100644
--- a/src/charon/plugins/sql/sql_cred.c
+++ b/src/charon/plugins/sql/sql_cred.c
@@ -94,29 +94,29 @@ static enumerator_t* create_private_enumerator(private_sql_cred_t *this,
 											   identification_t *id)
 {
 	private_enumerator_t *e;
-	chunk_t keyid = chunk_empty;
 	
-	if (id)
-	{
-		if (id->get_type(id) != ID_PUBKEY_INFO_SHA1)
-		{
-			DBG1(DBG_CFG, "looking for %N private key", id_type_names, id->get_type(id));
-			return NULL;
-		}
-		keyid = id->get_encoding(id);
-		DBG1(DBG_CFG, "looking for %#B", &keyid);
-	}
-	DBG1(DBG_CFG, "looking for a private key");
 	e = malloc_thing(private_enumerator_t);
 	e->current = NULL;
 	e->public.enumerate = (void*)private_enumerator_enumerate;
 	e->public.destroy = (void*)private_enumerator_destroy;
-	e->inner = this->db->query(this->db,
-			"SELECT type, data FROM private_keys "
-			"WHERE (? OR keyid = ?) AND (? OR type = ?)",
-			DB_INT, id == NULL, DB_BLOB, keyid,
-			DB_INT, type == KEY_ANY, DB_INT, type,
-			DB_INT, DB_BLOB);
+	if (id && id->get_type(id) != ID_ANY)
+	{
+		e->inner = this->db->query(this->db,
+				"SELECT p.type, p.data FROM private_keys AS p "
+				"JOIN private_key_identity AS pi ON p.id = pi.private_key "
+				"JOIN identities AS i ON pi.identity = i.id "
+				"WHERE i.type = ? AND i.data = ? AND (? OR p.type = ?)",
+				DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
+				DB_INT, type == KEY_ANY, DB_INT, type,
+				DB_INT, DB_BLOB);
+	}
+	else
+	{
+		e->inner = this->db->query(this->db,
+				"SELECT type, data FROM private_keys WHERE (? OR type = ?)",
+				DB_INT, type == KEY_ANY, DB_INT, type,
+				DB_INT, DB_BLOB);
+	}
 	if (!e->inner)
 	{
 		free(e);
@@ -180,29 +180,33 @@ static enumerator_t* create_cert_enumerator(private_sql_cred_t *this,
 										identification_t *id, bool trusted)
 {
 	cert_enumerator_t *e;
-	chunk_t enc = chunk_empty;
-	id_type_t type = ID_ANY;
-	
-	if (id)
-	{
-		type = id->get_type(id);
-		enc = id->get_encoding(id);
-	}
 	
 	e = malloc_thing(cert_enumerator_t);
 	e->current = NULL;
 	e->public.enumerate = (void*)cert_enumerator_enumerate;
 	e->public.destroy = (void*)cert_enumerator_destroy;
-	e->inner = this->db->query(this->db,
-			"SELECT type, data FROM certificates "
-			"WHERE (? OR type = ?) AND (? OR keytype = ?) AND "
-			"(? OR (? AND subject = ?) OR (? AND keyid = ?))",
-			DB_INT, cert == CERT_ANY, DB_INT, cert,
-			DB_INT, key == KEY_ANY, DB_INT, key,
-			DB_INT, id == NULL,
-			DB_INT, type == ID_DER_ASN1_DN, DB_BLOB, enc,
-			DB_INT, type == ID_PUBKEY_INFO_SHA1, DB_BLOB, enc,
-			DB_INT, DB_BLOB);
+	if (id && id->get_type(id) != ID_ANY)
+	{
+		e->inner = this->db->query(this->db,
+				"SELECT c.type, c.data FROM certificates AS c "
+				"JOIN certificate_identity AS ci ON c.id = ci.certificate "
+				"JOIN identities AS i ON ci.identity = i.id "
+				"WHERE i.type = ? AND i.data = ? AND "
+				"(? OR c.type = ?) AND (? OR c.keytype = ?)",
+				DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
+				DB_INT, cert == CERT_ANY, DB_INT, cert,
+				DB_INT, key == KEY_ANY, DB_INT, key,
+				DB_INT, DB_BLOB);
+	}
+	else
+	{
+		e->inner = this->db->query(this->db,
+				"SELECT type, data FROM certificates WHERE "
+				"(? OR type = ?) AND (? OR keytype = ?)",
+				DB_INT, cert == CERT_ANY, DB_INT, cert,
+				DB_INT, key == KEY_ANY, DB_INT, key,
+				DB_INT, DB_BLOB);
+	}
 	if (!e->inner)
 	{
 		free(e);
@@ -219,10 +223,10 @@ typedef struct {
 	enumerator_t public;
 	/** inner SQL enumerator */
 	enumerator_t *inner;
-	/** match of me */
-	id_match_t me;
-	/** match of other */
-	id_match_t other;
+	/** own identity */
+	identification_t *me;
+	/** remote identity */
+	identification_t *other;
 	/** currently enumerated private key */
 	shared_key_t *current;
 } shared_enumerator_t;
@@ -246,11 +250,11 @@ static bool shared_enumerator_enumerate(shared_enumerator_t *this,
 			*shared = this->current;
 			if (me)
 			{
-				*me = this->me;
+				*me = this->me ? ID_MATCH_PERFECT : ID_MATCH_ANY;
 			}
 			if (other)
 			{
-				*other = this->other;
+				*other = this->other ? ID_MATCH_PERFECT : ID_MATCH_ANY;
 			}
 			return TRUE;
 		}
@@ -277,31 +281,48 @@ static enumerator_t* create_shared_enumerator(private_sql_cred_t *this,
 								  identification_t *me, identification_t *other)
 {
 	shared_enumerator_t *e;
-	chunk_t my_chunk = chunk_empty, other_chunk = chunk_empty;
 	
 	e = malloc_thing(shared_enumerator_t);
-	e->me = ID_MATCH_ANY;
-	e->other = ID_MATCH_ANY;
-	if (me)
+	e->me = me;
+	e->other = other;
+	e->current = NULL;
+	e->public.enumerate = (void*)shared_enumerator_enumerate;
+	e->public.destroy = (void*)shared_enumerator_destroy;
+	if (!me && !other)
 	{
-		e->me = ID_MATCH_PERFECT;
-		my_chunk = me->get_encoding(me);
+		e->inner = this->db->query(this->db,
+				"SELECT type, data FROM shared_secrets WHERE  (? OR type = ?)",
+				DB_INT, type == SHARED_ANY, DB_INT, type,
+				DB_INT, DB_BLOB);
 	}
-	if (other)
+	else if (me && other)
 	{
-		e->other = ID_MATCH_PERFECT;
-		other_chunk = other->get_encoding(other);
+		e->inner = this->db->query(this->db,
+				"SELECT s.type, s.data FROM shared_secrets AS s "
+				"JOIN shared_secret_identity AS sm ON s.id = sm.shared_secret "
+				"JOIN identities AS m ON sm.identity = m.id "
+				"JOIN shared_secret_identity AS so ON s.id = so.shared_secret "
+				"JOIN identities AS o ON so.identity = o.id "
+				"WHERE m.type = ? AND m.data = ? AND o.type = ? AND o.data = ? "
+				"AND (? OR s.type = ?)",
+				DB_INT, me->get_type(me), DB_BLOB, me->get_encoding(me),
+				DB_INT, other->get_type(other), DB_BLOB, other->get_encoding(other),
+				DB_INT, type == SHARED_ANY, DB_INT, type,
+				DB_INT, DB_BLOB);				
+	}
+	else
+	{
+		identification_t *id = me ? me : other;
+		
+		e->inner = this->db->query(this->db,
+				"SELECT s.type, s.data FROM shared_secrets AS s "
+				"JOIN shared_secret_identity AS si ON s.id = si.shared_secret "
+				"JOIN identities AS i ON si.identity = i.id "
+				"WHERE i.type = ? AND i.data = ? AND (? OR s.type = ?)",
+				DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
+				DB_INT, type == SHARED_ANY, DB_INT, type,
+				DB_INT, DB_BLOB);
 	}
-	e->current = NULL;
-	e->public.enumerate = (void*)shared_enumerator_enumerate;
-	e->public.destroy = (void*)shared_enumerator_destroy;
-	e->inner = this->db->query(this->db,
-			"SELECT type, data FROM certificates "
-			"WHERE (? OR local = ?) AND (? OR remote = ?) AND (? OR type = ?)",
-			DB_INT, me == NULL, DB_BLOB, my_chunk,
-			DB_INT, other == NULL, DB_BLOB, other_chunk,
-			DB_INT, type == SHARED_ANY, DB_INT, type,
-			DB_INT, DB_BLOB);
 	if (!e->inner)
 	{
 		free(e);
diff --git a/src/charon/plugins/sql/sql_logger.c b/src/charon/plugins/sql/sql_logger.c
index a55777e3f..901720c28 100644
--- a/src/charon/plugins/sql/sql_logger.c
+++ b/src/charon/plugins/sql/sql_logger.c
@@ -42,6 +42,11 @@ struct private_sql_logger_t {
 	 * logging level
 	 */
 	int level;
+	
+	/**
+	 * avoid recursive logging
+	 */
+	bool recursive;
 };
 
 
@@ -51,20 +56,25 @@ struct private_sql_logger_t {
 static bool signal_(private_sql_logger_t *this, signal_t signal, level_t level,
 					int thread, ike_sa_t* ike_sa, char *format, va_list args)
 {
+	if (this->recursive)
+	{
+		return TRUE;
+	}
+	this->recursive = TRUE;
+
 	if (ike_sa && level <= this->level)
 	{
-		char buffer[8192], local_id[64], remote_id[64], local[40], remote[40];
-		char *current = buffer, *next;
+		char buffer[8192];
 		chunk_t local_spi, remote_spi;
+		host_t *local_host, *remote_host;
+		identification_t *local_id, *remote_id;
 		u_int64_t ispi, rspi;
-		bool initiator;
 		ike_sa_id_t *id;
 	
 		id = ike_sa->get_id(ike_sa);
-		initiator = id->is_initiator(id);
 		ispi = id->get_initiator_spi(id);
 		rspi = id->get_responder_spi(id);
-		if (initiator)
+		if (id->is_initiator(id))
 		{
 			local_spi.ptr = (char*)&ispi;
 			remote_spi.ptr = (char*)&rspi;
@@ -75,39 +85,35 @@ static bool signal_(private_sql_logger_t *this, signal_t signal, level_t level,
 			remote_spi.ptr = (char*)&ispi;
 		}
 		local_spi.len = remote_spi.len = sizeof(ispi);
-		snprintf(local_id, sizeof(local_id), "%D", ike_sa->get_my_id(ike_sa));
-		snprintf(remote_id, sizeof(remote_id), "%D", ike_sa->get_other_id(ike_sa));
-		snprintf(local, sizeof(local), "%H", ike_sa->get_my_host(ike_sa));
-		snprintf(remote, sizeof(remote), "%H", ike_sa->get_other_host(ike_sa));
+		local_id = ike_sa->get_my_id(ike_sa);
+		remote_id = ike_sa->get_other_id(ike_sa);
+		local_host = ike_sa->get_my_host(ike_sa);
+		remote_host = ike_sa->get_other_host(ike_sa);
 		
-		/* write in memory buffer first */
 		vsnprintf(buffer, sizeof(buffer), format, args);
-	
+		
 		this->db->execute(this->db, NULL, "REPLACE INTO ike_sas ("
 						  "local_spi, remote_spi, id, initiator, "
-						  "local_id, remote_id, local, remote) "
-						  "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
+						  "local_id_type, local_id_data, "
+						  "remote_id_type, remote_id_data, "
+						  "host_family, local_host_data, remote_host_data) "
+						  "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
 						  DB_BLOB, local_spi, DB_BLOB, remote_spi,
 						  DB_INT, ike_sa->get_unique_id(ike_sa),
-						  DB_INT, initiator,
-						  DB_TEXT, local_id, DB_TEXT, remote_id, 
-						  DB_TEXT, local, DB_TEXT, remote);
-		/* do a log with every line */
-		while (current)
-		{
-			next = strchr(current, '\n');
-			if (next)
-			{
-				*(next++) = '\0';
-			}
-			this->db->execute(this->db, NULL,
-							  "INSERT INTO logs (local_spi, signal, level, msg) "
-							  "VALUES (?, ?, ?, ?)",
-							  DB_BLOB, local_spi, DB_INT, signal, DB_INT, level,
-							  DB_TEXT, current);
-			current = next;
-		}
+						  DB_INT, id->is_initiator(id),
+						  DB_INT, local_id->get_type(local_id),
+						  DB_BLOB, local_id->get_encoding(local_id),
+						  DB_INT, remote_id->get_type(remote_id),
+						  DB_BLOB, remote_id->get_encoding(remote_id),
+						  DB_INT, local_host->get_family(local_host),
+						  DB_BLOB, local_host->get_address(local_host),
+						  DB_BLOB, remote_host->get_address(remote_host));
+		this->db->execute(this->db, NULL, "INSERT INTO logs ("
+						  "local_spi, signal, level, msg) VALUES (?, ?, ?, ?)",
+						  DB_BLOB, local_spi, DB_INT, signal, DB_INT, level,
+						  DB_TEXT, buffer);
 	}
+	this->recursive = FALSE;
 	/* always stay registered */
 	return TRUE;
 }
@@ -131,6 +137,7 @@ sql_logger_t *sql_logger_create(database_t *db)
 	this->public.destroy = (void(*)(sql_logger_t*))destroy;
 	
 	this->db = db;
+	this->recursive = FALSE;
 	
 	this->level = lib->settings->get_int(lib->settings,
 										 "charon.plugins.sql.loglevel", 1);
diff --git a/src/charon/plugins/sql/sqlite.sql b/src/charon/plugins/sql/sqlite.sql
index 8ea1d9041..f072a0618 100644
--- a/src/charon/plugins/sql/sqlite.sql
+++ b/src/charon/plugins/sql/sqlite.sql
@@ -1,14 +1,28 @@
 
+
+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 '1',
-  mode INTEGER NOT NULL default '1'
+  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 '1',
+  mode INTEGER NOT NULL DEFAULT '1'
+);
+DROP INDEX IF EXISTS child_configs_name;
+CREATE INDEX child_configs_name ON child_configs (
+  name
 );
 
 
@@ -18,108 +32,145 @@ CREATE TABLE child_config_traffic_selector (
   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 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',
+  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 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_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',
-  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',
-  dpd_action INTEGER NOT NULL default '1',
-  local_vip TEXT default NULL,
-  remote_vip TEXT default NULL,
-  mediation INTEGER NOT NULL default '0',
-  mediated_by INTEGER NOT NULL default '0',
-  peer_id TEXT default NULL
+  cert_policy INTEGER NOT NULL DEFAULT '1',
+  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',
+  dpd_action INTEGER NOT NULL DEFAULT '1',
+  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
+  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 TEXT default NULL,
-  end_addr TEXT default NULL,
-  start_port INTEGER NOT NULL default '0',
-  end_port INTEGER NOT NULL default '65535'
+  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 shared_secrets;
-CREATE TABLE shared_secrets (
-	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	type INTEGER NOT NULL,
-	local TEXT default NULL,
-	remote TEXT default NULL,
-	data BLOB NOT NULL
-);
 
 DROP TABLE IF EXISTS certificates;
 CREATE TABLE certificates (
-	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	type INTEGER NOT NULL,
-	keytype INTEGER NOT NULL,
-	keyid BLOB NOT NULL,
-	subject TEXT default NULL,
-	data BLOB NOT NULL
+  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,
-	keyid BLOB NOT NULL,
-	data BLOB NOT NULL
+  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 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 TEXT NOT NULL,
-  remote_id TEXT NOT NULL,
-  local TEXT NOT NULL,
-  remote TEXT NOT NULL
+  local_id_type INTEGER NOT NULL,
+  local_id_data BLOB NOT NULL,
+  remote_id_type INTEGER NOT NULL,
+  remote_id_data BLOB NOT 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
+  msg TEXT NOT NULL,
+  time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
 
-
-
diff --git a/src/charon/plugins/sql/test.sql b/src/charon/plugins/sql/test.sql
index be6d96fd3..f599eb234 100644
--- a/src/charon/plugins/sql/test.sql
+++ b/src/charon/plugins/sql/test.sql
@@ -1,10 +1,42 @@
 
+INSERT INTO identities (
+	type, data
+) VALUES (
+	9, 'C=CH, O=Linux strongSwan, CN=martin'
+);
+INSERT INTO identities (
+	type, data
+) VALUES (
+	2, 'sidv0150.hsr.ch'
+);
+INSERT INTO identities (
+	type, data
+) VALUES (
+	202, X'5d735be540d27e858bbc56d7b73766d859bac953'
+);
+INSERT INTO identities (
+	type, data
+) VALUES (
+	202, X'65c7bb4351a284794e4bf3bf60f4df70dc822b21'
+);
+INSERT INTO identities (
+	type, data
+) VALUES (
+	9, 'C=CH, O=HSR, OU=IntSec, CN=sidv0150.hsr.ch'
+);
+
 INSERT INTO ike_configs (
 	local, remote
 ) VALUES (
 	'0.0.0.0', '152.96.52.150'
 );
 
+INSERT INTO peer_configs (
+	name, ike_cfg, local_id, remote_id
+) VALUES (
+	'sqltest', 1, 1, 2
+);
+
 INSERT INTO child_configs (
 	name
 ) VALUES (
@@ -17,43 +49,69 @@ INSERT INTO peer_config_child_config (
 	1, 1
 );
 
-INSERT INTO traffic_selectors (type) VALUES (7);
+INSERT INTO traffic_selectors (
+	type
+) VALUES (
+	7
+);
 
 INSERT INTO child_config_traffic_selector (
 	child_cfg, traffic_selector, kind
 ) VALUES (
 	1, 1, 2
 );
-
 INSERT INTO child_config_traffic_selector (
 	child_cfg, traffic_selector, kind
 ) VALUES (
 	1, 1, 3
 );
 
-INSERT INTO peer_configs (
-	name, ike_cfg, local_id, remote_id
+INSERT INTO shared_secrets (
+	type, data
 ) VALUES (
-	'sqltest', 1, 'C=CH, O=Linux strongSwan, CN=martin', 'sidv0150.hsr.ch'
+	1, 'martintestpsk'
 );
 
-INSERT INTO certificates (
-	type, keytype, keyid, subject, data
+INSERT INTO shared_secret_identity (
+	shared_secret, identity
 ) VALUES (
-	1, 1, 
-	X'5d735be540d27e858bbc56d7b73766d859bac953',
-	'C=CH, O=Linux strongSwan, CN=martin',
-	X'308202fa308201e2a00302010202105af265ae78ff23def7a6a3948c3fa0c1300d06092a864886f70d01010505003039310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e310f300d060355040313066d617274696e301e170d3037303432373037313432365a170d3132303432353037313432365a3039310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e310f300d060355040313066d617274696e30820122300d06092a864886f70d01010105000382010f003082010a0282010100d7b9ba4de23b3d357a3f886795e7fd9fe90a0d793a9e218fcbe46724ae0cdab3ccec36b4a84df13dade48c639254b7b202a200628b04aca017ad179a050dd7b30802c526cfdd0542fc136d9fb1f34f821def01c991ea371b7928fabf9fb3eb824f10c64ba408f78ef200ea0497809f6586de6bc7da83fcad4aaf528b4d33ee49872f3b6045668fe689ccb19202172b7b8e90478484599581d8e0f387e00409fdcc3a2134faecbef59ccf55807be3759d3668ab83e3ad01530d8a9aa6b015c9c5f89b5132cf976cfe4a563cc88f4a70234ff6f7e69f09cd8fea207d34c0c5c034066f8beb04543f0ecde285ab943e916c186f965df28b10e99043b06152accf750203010001300d06092a864886f70d01010505000382010100096342ade5a3f6c95d08f2787beb8aef5000c8ebe92694cb84107e426b863857a602985a2c8f44321b978c7e4bd8e8e80f4ab9319ff69f0e6726052a99143541479afa12940be9277c7120d78d3b97192d15ffa4f3898d295ff63f93af7861e4e12e75c12cc4769519f837dcd8007a3c0f492e880916b39233df77834fb59e308c481dd884fbf1b9a0be25ff4cebef2bcdfa0b94663b28083f3ada41d06bab5ebb8a9fdc983e593748be69de8582f2538be444e4719114850e1e79dd62f5dc2589ab505baaaee3646a2334d730e22ac8810cecd231c61eb6c057d9e114069bf8516947f09ccd69ef8e5f62da10f73c6d0f33ec6ffd940716413206a4e1083187'
+	1, 1
 );
 
-INSERT INTO certificates (
-	type, keytype, keyid, subject, data
+INSERT INTO shared_secret_identity (
+	shared_secret, identity
 ) VALUES (
-	1, 1,
-	X'65c7bb4351a284794e4bf3bf60f4df70dc822b21',
-	'C=CH, O=HSR, OU=IntSec, CN=sidv0150.hsr.ch',
-	X'30820503308202eba003020102020149300d06092a864886f70d01010505003045310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311730150603550403130e496e745365632032303037204341301e170d3037303131363135323634385a170d3131303131353135323634385a3046310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311830160603550403130f73696476303135302e6873722e636830820122300d06092a864886f70d01010105000382010f003082010a0282010100cd946c229f7d52b21da1cb5384e7dcaf6529f760534a56355efd49e87a9c6f1ddd5ff303bd7eb49c23de03adc487456f41eb5f92947bdc8ff8dbe443f8d112e0da2c98145e7c4d1cd15cddd08577f4d4f3d0a2e1da3c08c94cd819758751931e7a9724cc43d73a11b8e176a268b4cdbbf3995cb09723abc9bfc477c25e714a4661a84c078be7404d8986be55f20437e3a6b278a3cc89aec085941f1a1aafaf4b22ae146fe4684d5567dc30658a32087d01b98515070cb1653311cb6102f82a83c638c2a79985dbb9600752e9cbc272014a5c547b4ab59130c3a948658bff794b6f202cf95939ffa73b10521f05c060cecb15f8597ed95d72b9e405ee31f1b5d90203010001a381fc3081f930090603551d1304023000300b0603551d0f0404030203a8301d0603551d0e04160414ed90e64feca21f4b6897992422e0de21b9d6262930750603551d23046e306c80140ab8ed865c4ded8c83a7c681fafb49292d451f43a149a4473045310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311730150603550403130e496e745365632032303037204341820900df8d6b00b2efccd8301a0603551d1104133011820f73696476303135302e6873722e6368302d0603551d1f042630243022a020a01e861c687474703a2f2f696e747365632e6873722e63682f6873722e63726c300d06092a864886f70d01010505000382020100a4d7ca2d8e49943102104f0b4aec0c80ab18b2499bc3ac582f1df13e34a11a664ec5ac92df47de5c6ecad1f931f7226a038e65764cc97eb01f6ff022e0745f134824316ca5912e6a648924e1932301b71e95ee00390a1de90ca8f0eb91c5cc781e22828ec1e9a4b3040c193cd5ece18acdf97d0bc0c99c04433301cfe06c29bbd15b18133266c76056cf0c184aaafd25cc4fde60cb02d12bc9f96de41521d4cd1a57d28cb4da80493e39b3f2fe2a1f52c5e787f63837f406999b40fa77928fdd9d97bdb68828a096bc98275c7d6b40c4be020a3787ac9078a8471283a7e7fa2001b65e4558eb9c13e3883d0858f6a56f1260f35edff5953a4a28e785c2f853ab29b8ccee245e7799ab372c2a6e05b75de593be238df7a6c95792d9c67bf33bbe0edbdcda48373ed1097a76324950d1dd3f5f3046b04dc150ee38c69bbd1ceb8421db05bb39f16ec309f5b97aed81b45b04da2603bb0cc8ecff96a627cb8a73d700309fd4dde266196fb51799ca46c7d885da6eee1e300b7d6a4f2c701f8ae3b061f576e8e3028070e8662c120198d8df5993c8c41f3465217ebbcdfcad0706f608c6492ffcac5a69c1e9b610e1a41534ca3f10631ddc045edeba46e35804eb43629241e036acdd701eaf8a634a05250443648bb619ae5192ea281b0ae40ac94e9e38afd14b212b0c09e4a92ca64b61ea520e828273c39a529169d09d555ccf21'
+	1, 2
 );
 
+INSERT INTO certificates (
+	type, keytype, data
+) VALUES (
+	1, 1, X'308202fa308201e2a00302010202105af265ae78ff23def7a6a3948c3fa0c1300d06092a864886f70d01010505003039310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e310f300d060355040313066d617274696e301e170d3037303432373037313432365a170d3132303432353037313432365a3039310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e310f300d060355040313066d617274696e30820122300d06092a864886f70d01010105000382010f003082010a0282010100d7b9ba4de23b3d357a3f886795e7fd9fe90a0d793a9e218fcbe46724ae0cdab3ccec36b4a84df13dade48c639254b7b202a200628b04aca017ad179a050dd7b30802c526cfdd0542fc136d9fb1f34f821def01c991ea371b7928fabf9fb3eb824f10c64ba408f78ef200ea0497809f6586de6bc7da83fcad4aaf528b4d33ee49872f3b6045668fe689ccb19202172b7b8e90478484599581d8e0f387e00409fdcc3a2134faecbef59ccf55807be3759d3668ab83e3ad01530d8a9aa6b015c9c5f89b5132cf976cfe4a563cc88f4a70234ff6f7e69f09cd8fea207d34c0c5c034066f8beb04543f0ecde285ab943e916c186f965df28b10e99043b06152accf750203010001300d06092a864886f70d01010505000382010100096342ade5a3f6c95d08f2787beb8aef5000c8ebe92694cb84107e426b863857a602985a2c8f44321b978c7e4bd8e8e80f4ab9319ff69f0e6726052a99143541479afa12940be9277c7120d78d3b97192d15ffa4f3898d295ff63f93af7861e4e12e75c12cc4769519f837dcd8007a3c0f492e880916b39233df77834fb59e308c481dd884fbf1b9a0be25ff4cebef2bcdfa0b94663b28083f3ada41d06bab5ebb8a9fdc983e593748be69de8582f2538be444e4719114850e1e79dd62f5dc2589ab505baaaee3646a2334d730e22ac8810cecd231c61eb6c057d9e114069bf8516947f09ccd69ef8e5f62da10f73c6d0f33ec6ffd940716413206a4e1083187'
+);
+INSERT INTO certificates (
+	type, keytype, data
+) VALUES (
+	1, 1, X'30820503308202eba003020102020149300d06092a864886f70d01010505003045310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311730150603550403130e496e745365632032303037204341301e170d3037303131363135323634385a170d3131303131353135323634385a3046310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311830160603550403130f73696476303135302e6873722e636830820122300d06092a864886f70d01010105000382010f003082010a0282010100cd946c229f7d52b21da1cb5384e7dcaf6529f760534a56355efd49e87a9c6f1ddd5ff303bd7eb49c23de03adc487456f41eb5f92947bdc8ff8dbe443f8d112e0da2c98145e7c4d1cd15cddd08577f4d4f3d0a2e1da3c08c94cd819758751931e7a9724cc43d73a11b8e176a268b4cdbbf3995cb09723abc9bfc477c25e714a4661a84c078be7404d8986be55f20437e3a6b278a3cc89aec085941f1a1aafaf4b22ae146fe4684d5567dc30658a32087d01b98515070cb1653311cb6102f82a83c638c2a79985dbb9600752e9cbc272014a5c547b4ab59130c3a948658bff794b6f202cf95939ffa73b10521f05c060cecb15f8597ed95d72b9e405ee31f1b5d90203010001a381fc3081f930090603551d1304023000300b0603551d0f0404030203a8301d0603551d0e04160414ed90e64feca21f4b6897992422e0de21b9d6262930750603551d23046e306c80140ab8ed865c4ded8c83a7c681fafb49292d451f43a149a4473045310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311730150603550403130e496e745365632032303037204341820900df8d6b00b2efccd8301a0603551d1104133011820f73696476303135302e6873722e6368302d0603551d1f042630243022a020a01e861c687474703a2f2f696e747365632e6873722e63682f6873722e63726c300d06092a864886f70d01010505000382020100a4d7ca2d8e49943102104f0b4aec0c80ab18b2499bc3ac582f1df13e34a11a664ec5ac92df47de5c6ecad1f931f7226a038e65764cc97eb01f6ff022e0745f134824316ca5912e6a648924e1932301b71e95ee00390a1de90ca8f0eb91c5cc781e22828ec1e9a4b3040c193cd5ece18acdf97d0bc0c99c04433301cfe06c29bbd15b18133266c76056cf0c184aaafd25cc4fde60cb02d12bc9f96de41521d4cd1a57d28cb4da80493e39b3f2fe2a1f52c5e787f63837f406999b40fa77928fdd9d97bdb68828a096bc98275c7d6b40c4be020a3787ac9078a8471283a7e7fa2001b65e4558eb9c13e3883d0858f6a56f1260f35edff5953a4a28e785c2f853ab29b8ccee245e7799ab372c2a6e05b75de593be238df7a6c95792d9c67bf33bbe0edbdcda48373ed1097a76324950d1dd3f5f3046b04dc150ee38c69bbd1ceb8421db05bb39f16ec309f5b97aed81b45b04da2603bb0cc8ecff96a627cb8a73d700309fd4dde266196fb51799ca46c7d885da6eee1e300b7d6a4f2c701f8ae3b061f576e8e3028070e8662c120198d8df5993c8c41f3465217ebbcdfcad0706f608c6492ffcac5a69c1e9b610e1a41534ca3f10631ddc045edeba46e35804eb43629241e036acdd701eaf8a634a05250443648bb619ae5192ea281b0ae40ac94e9e38afd14b212b0c09e4a92ca64b61ea520e828273c39a529169d09d555ccf21'
+);
 
-
+INSERT INTO certificate_identity (
+	certificate, identity
+) VALUES (
+	1, 3
+);
+INSERT INTO certificate_identity (
+	certificate, identity
+) VALUES (
+	1, 1
+);
+INSERT INTO certificate_identity (
+	certificate, identity
+) VALUES (
+	2, 4
+);
+INSERT INTO certificate_identity (
+	certificate, identity
+) VALUES (
+	2, 5
+);