Merge branch 'database-transactions'

This adds support for transactions to the database_t interface and the two
current implementations.

The pool utility is also moved to its own directory in src/.

14 files changed, 377 insertions, 121 deletions

diff --git a/src/Makefile.am b/src/Makefile.am
index 8ed45acc9..218c9434c 100644
--- a/src/Makefile.am
+++ b/src/Makefile.am
@@ -100,6 +100,10 @@ if USE_INTEGRITY_TEST
   SUBDIRS += checksum
 endif
 
+if USE_ATTR_SQL
+  SUBDIRS += pool
+endif
+
 if USE_TKM
   SUBDIRS += charon-tkm
 endif
diff --git a/src/libcharon/plugins/sql/mysql.sql b/src/libcharon/plugins/sql/mysql.sql
index e5d878689..0d1468176 100644
--- a/src/libcharon/plugins/sql/mysql.sql
+++ b/src/libcharon/plugins/sql/mysql.sql
@@ -1,5 +1,4 @@
 
-
 DROP TABLE IF EXISTS `identities`;
 CREATE TABLE `identities` (
   `id` int(10) unsigned NOT NULL auto_increment,
@@ -7,7 +6,7 @@ CREATE TABLE `identities` (
   `data` varbinary(64) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE (`type`, `data`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS `child_configs`;
@@ -27,7 +26,7 @@ CREATE TABLE `child_configs` (
   `reqid` mediumint(8) unsigned NOT NULL default '0',
   PRIMARY KEY (`id`),
   INDEX (`name`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS `child_config_traffic_selector`;
@@ -36,7 +35,7 @@ CREATE TABLE `child_config_traffic_selector` (
   `traffic_selector` int(10) unsigned NOT NULL,
   `kind` tinyint(3) unsigned NOT NULL,
   INDEX (`child_cfg`, `traffic_selector`)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS `proposals`;
@@ -44,7 +43,7 @@ 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`;
@@ -52,7 +51,7 @@ 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`;
@@ -63,7 +62,7 @@ CREATE TABLE `ike_configs` (
   `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS `ike_config_proposal`;
@@ -71,7 +70,7 @@ 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`;
@@ -101,7 +100,7 @@ CREATE TABLE `peer_configs` (
   `peer_id` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY (`id`),
   INDEX (`name`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS `peer_config_child_config`;
@@ -109,7 +108,7 @@ 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS `traffic_selectors`;
@@ -122,7 +121,7 @@ CREATE TABLE `traffic_selectors` (
   `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS certificates;
@@ -132,7 +131,7 @@ CREATE TABLE certificates (
   `keytype` tinyint(3) unsigned NOT NULL,
   `data` BLOB NOT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS certificate_identity;
@@ -140,7 +139,7 @@ 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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS private_keys;
@@ -149,7 +148,7 @@ CREATE TABLE private_keys (
   `type` tinyint(3) unsigned NOT NULL,
   `data` BLOB NOT NULL,
   PRIMARY KEY  (`id`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS private_key_identity;
@@ -157,7 +156,7 @@ 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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS shared_secrets;
@@ -166,7 +165,7 @@ CREATE TABLE shared_secrets (
   `type` tinyint(3) unsigned NOT NULL,
   `data` varbinary(256) NOT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS shared_secret_identity;
@@ -174,7 +173,7 @@ 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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS certificate_authorities;
@@ -182,7 +181,7 @@ CREATE TABLE certificate_authorities (
   `id` int(10) unsigned NOT NULL auto_increment,
   `certificate` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS certificate_distribution_points;
@@ -192,7 +191,7 @@ CREATE TABLE certificate_distribution_points (
   `type` tinyint(3) unsigned NOT NULL,
   `uri` varchar(256) NOT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS pools;
@@ -204,7 +203,7 @@ CREATE TABLE pools (
   `timeout` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE (`name`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS addresses;
@@ -219,7 +218,7 @@ CREATE TABLE addresses (
   INDEX (`pool`),
   INDEX (`identity`),
   INDEX (`address`)
-);
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 DROP TABLE IF EXISTS leases;
 CREATE TABLE leases (
@@ -229,14 +228,14 @@ CREATE TABLE leases (
   `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 (
@@ -248,7 +247,7 @@ CREATE TABLE attributes (
   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 (
@@ -265,7 +264,7 @@ CREATE TABLE ike_sas (
   `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
 DROP TABLE IF EXISTS logs;
@@ -277,6 +276,6 @@ CREATE TABLE logs (
   `msg` varchar(256) NOT NULL,
   `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
-) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
diff --git a/src/libhydra/plugins/attr_sql/Makefile.am b/src/libhydra/plugins/attr_sql/Makefile.am
index 4c369a2bd..d126bb035 100644
--- a/src/libhydra/plugins/attr_sql/Makefile.am
+++ b/src/libhydra/plugins/attr_sql/Makefile.am
@@ -1,7 +1,6 @@
 AM_CPPFLAGS = \
 	-I$(top_srcdir)/src/libstrongswan \
-	-I$(top_srcdir)/src/libhydra \
-	-DPLUGINS=\""${pool_plugins}\""
+	-I$(top_srcdir)/src/libhydra
 
 AM_CFLAGS = \
 	-rdynamic
@@ -17,10 +16,3 @@ libstrongswan_attr_sql_la_SOURCES = \
 	sql_attribute.h sql_attribute.c
 
 libstrongswan_attr_sql_la_LDFLAGS = -module -avoid-version
-
-ipsec_PROGRAMS = pool
-pool_SOURCES = pool.c pool_attributes.c pool_attributes.h \
-			   pool_usage.h pool_usage.c
-pool_LDADD = $(top_builddir)/src/libstrongswan/libstrongswan.la \
-			 $(top_builddir)/src/libhydra/libhydra.la
-pool.o :	$(top_builddir)/config.status
diff --git a/src/libhydra/plugins/attr_sql/sql_attribute.c b/src/libhydra/plugins/attr_sql/sql_attribute.c
index e91e1ed15..0a06c419f 100644
--- a/src/libhydra/plugins/attr_sql/sql_attribute.c
+++ b/src/libhydra/plugins/attr_sql/sql_attribute.c
@@ -51,15 +51,16 @@ static u_int get_identity(private_sql_attribute_t *this, identification_t *id)
 	enumerator_t *e;
 	u_int row;
 
+	this->db->transaction(this->db, TRUE);
 	/* look for peer identity in the identities table */
 	e = this->db->query(this->db,
 						"SELECT id FROM identities WHERE type = ? AND data = ?",
 						DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
 						DB_UINT);
-
 	if (e && e->enumerate(e, &row))
 	{
 		e->destroy(e);
+		this->db->commit(this->db);
 		return row;
 	}
 	DESTROY_IF(e);
@@ -68,8 +69,10 @@ static u_int get_identity(private_sql_attribute_t *this, identification_t *id)
 				  "INSERT INTO identities (type, data) VALUES (?, ?)",
 				  DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id)) == 1)
 	{
+		this->db->commit(this->db);
 		return row;
 	}
+	this->db->rollback(this->db);
 	return 0;
 }
 
@@ -346,8 +349,6 @@ METHOD(attribute_provider_t, create_attribute_enumerator, enumerator_t*,
 		u_int count;
 		char *name;
 
-		this->db->execute(this->db, NULL, "BEGIN EXCLUSIVE TRANSACTION");
-
 		/* in a first step check for attributes that match name and id */
 		if (id)
 		{
@@ -418,8 +419,6 @@ METHOD(attribute_provider_t, create_attribute_enumerator, enumerator_t*,
 			pool_enumerator->destroy(pool_enumerator);
 		}
 
-		this->db->execute(this->db, NULL, "END TRANSACTION");
-
 		/* lastly try to find global attributes */
 		if (!attr_enumerator)
 		{
@@ -474,4 +473,3 @@ sql_attribute_t *sql_attribute_create(database_t *db)
 					  DB_UINT, now);
 	return &this->public;
 }
-
diff --git a/src/libstrongswan/database/database.h b/src/libstrongswan/database/database.h
index d46fc3d34..ad5ccf95e 100644
--- a/src/libstrongswan/database/database.h
+++ b/src/libstrongswan/database/database.h
@@ -1,4 +1,5 @@
 /*
+ * Copyright (C) 2013 Tobias Brunner
  * Copyright (C) 2008 Martin Willi
  * Hochschule fuer Technik Rapperswil
  *
@@ -102,7 +103,7 @@ struct database_t {
 	enumerator_t* (*query)(database_t *this, char *sql, ...);
 
 	/**
-	 * Execute a query which dows not return rows, such as INSERT.
+	 * Execute a query which does not return rows, such as INSERT.
 	 *
 	 * @param rowid		pointer to write inserted AUTO_INCREMENT row ID, or NULL
 	 * @param sql		sql string, containing '?' placeholders
@@ -112,6 +113,41 @@ struct database_t {
 	int (*execute)(database_t *this, int *rowid, char *sql, ...);
 
 	/**
+	 * Start a transaction.
+	 *
+	 * A serializable transaction forces a strict separation between other
+	 * transactions.  Due to the performance overhead they should only be used
+	 * in certain situations (e.g. SELECT->INSERT|UPDATE).
+	 *
+	 * @note Either commit() or rollback() has to be called to end the
+	 * transaction.
+	 * @note Transactions are thread-specific. So commit()/rollbak() has to be
+	 * called from the same thread.
+	 * @note While this method can be called multiple times (commit/rollback
+	 * have to be called an equal number of times) real nested transactions are
+	 * not supported.  So if any if the "inner" transactions are rolled back
+	 * the outer most transaction is rolled back.
+	 *
+	 * @param serializable	TRUE to create a serializable transaction
+	 * @return				TRUE on success
+	 */
+	bool (*transaction)(database_t *this, bool serializable);
+
+	/**
+	 * Commit all changes made during the current transaction.
+	 *
+	 * @return			TRUE on success
+	 */
+	bool (*commit)(database_t *this);
+
+	/**
+	 * Rollback/revert all changes made during the current transaction.
+	 *
+	 * @return			TRUE on success
+	 */
+	bool (*rollback)(database_t *this);
+
+	/**
 	 * Get the database implementation type.
 	 *
 	 * To allow driver specific SQL or performance optimizations each database
diff --git a/src/libstrongswan/plugins/mysql/mysql_database.c b/src/libstrongswan/plugins/mysql/mysql_database.c
index 8bd64692c..373e9dc7c 100644
--- a/src/libstrongswan/plugins/mysql/mysql_database.c
+++ b/src/libstrongswan/plugins/mysql/mysql_database.c
@@ -1,4 +1,5 @@
 /*
+ * Copyright (C) 2013 Tobias Brunner
  * Copyright (C) 2007 Martin Willi
  * Hochschule fuer Technik Rapperswil
  *
@@ -50,6 +51,11 @@ struct private_mysql_database_t {
 	linked_list_t *pool;
 
 	/**
+	 * thread-specific transaction, as transaction_t
+	 */
+	thread_value_t *transaction;
+
+	/**
 	 * mutex to lock pool
 	 */
 	mutex_t *mutex;
@@ -99,11 +105,45 @@ struct conn_t {
 };
 
 /**
+ * database transaction
+ */
+typedef struct {
+
+	/**
+	 * Reference to the specific connection we started the transaction on
+	 */
+	conn_t *conn;
+
+	/**
+	 * Refcounter if transaction() is called multiple times
+	 */
+	refcount_t refs;
+
+	/**
+	 * TRUE if transaction was rolled back
+	 */
+	bool rollback;
+
+} transaction_t;
+
+/**
  * Release a mysql connection
  */
-static void conn_release(conn_t *conn)
+static void conn_release(private_mysql_database_t *this, conn_t *conn)
 {
+	this->mutex->lock(this->mutex);
 	conn->in_use = FALSE;
+	this->mutex->unlock(this->mutex);
+}
+
+/**
+ * Destroy a transaction and release the connection
+ */
+static void transaction_destroy(private_mysql_database_t *this,
+								transaction_t *trans)
+{
+	conn_release(this, trans->conn);
+	free(trans);
 }
 
 /**
@@ -158,13 +198,24 @@ static void conn_destroy(conn_t *this)
 /**
  * Acquire/Reuse a mysql connection
  */
-static conn_t *conn_get(private_mysql_database_t *this)
+static conn_t *conn_get(private_mysql_database_t *this, transaction_t **trans)
 {
 	conn_t *current, *found = NULL;
 	enumerator_t *enumerator;
+	transaction_t *transaction;
 
 	thread_initialize();
 
+	transaction = this->transaction->get(this->transaction);
+	if (transaction)
+	{
+		if (trans)
+		{
+			*trans = transaction;
+		}
+		return transaction->conn;
+	}
+
 	while (TRUE)
 	{
 		this->mutex->lock(this->mutex);
@@ -197,9 +248,10 @@ static conn_t *conn_get(private_mysql_database_t *this)
 	}
 	if (found == NULL)
 	{
-		found = malloc_thing(conn_t);
-		found->in_use = TRUE;
-		found->mysql = mysql_init(NULL);
+		INIT(found,
+			.in_use = TRUE,
+			.mysql = mysql_init(NULL),
+		);
 		if (!mysql_real_connect(found->mysql, this->host, this->username,
 								this->password, this->database, this->port,
 								NULL, 0))
@@ -332,6 +384,8 @@ static MYSQL_STMT* run(MYSQL *mysql, char *sql, va_list *args)
 typedef struct {
 	/** implements enumerator_t */
 	enumerator_t public;
+	/** mysql database */
+	private_mysql_database_t *db;
 	/** associated MySQL statement */
 	MYSQL_STMT *stmt;
 	/** result bindings */
@@ -373,7 +427,7 @@ static void mysql_enumerator_destroy(mysql_enumerator_t *this)
 		}
 	}
 	mysql_stmt_close(this->stmt);
-	conn_release(this->conn);
+	conn_release(this->db, this->conn);
 	free(this->bind);
 	free(this->val.p_void);
 	free(this->length);
@@ -484,7 +538,7 @@ METHOD(database_t, query, enumerator_t*,
 	mysql_enumerator_t *enumerator = NULL;
 	conn_t *conn;
 
-	conn = conn_get(this);
+	conn = conn_get(this, NULL);
 	if (!conn)
 	{
 		return NULL;
@@ -496,11 +550,16 @@ METHOD(database_t, query, enumerator_t*,
 	{
 		int columns, i;
 
-		enumerator = malloc_thing(mysql_enumerator_t);
-		enumerator->public.enumerate = (void*)mysql_enumerator_enumerate;
-		enumerator->public.destroy = (void*)mysql_enumerator_destroy;
-		enumerator->stmt = stmt;
-		enumerator->conn = conn;
+		INIT(enumerator,
+			.public = {
+				.enumerate = (void*)mysql_enumerator_enumerate,
+				.destroy = (void*)mysql_enumerator_destroy,
+
+			},
+			.db = this,
+			.stmt = stmt,
+			.conn = conn,
+		);
 		columns = mysql_stmt_field_count(stmt);
 		enumerator->bind = calloc(columns, sizeof(MYSQL_BIND));
 		enumerator->length = calloc(columns, sizeof(unsigned long));
@@ -557,7 +616,7 @@ METHOD(database_t, query, enumerator_t*,
 	}
 	else
 	{
-		conn_release(conn);
+		conn_release(this, conn);
 	}
 	va_end(args);
 	return (enumerator_t*)enumerator;
@@ -571,7 +630,7 @@ METHOD(database_t, execute, int,
 	conn_t *conn;
 	int affected = -1;
 
-	conn = conn_get(this);
+	conn = conn_get(this, NULL);
 	if (!conn)
 	{
 		return -1;
@@ -588,10 +647,101 @@ METHOD(database_t, execute, int,
 		mysql_stmt_close(stmt);
 	}
 	va_end(args);
-	conn_release(conn);
+	conn_release(this, conn);
 	return affected;
 }
 
+METHOD(database_t, transaction, bool,
+	private_mysql_database_t *this, bool serializable)
+{
+	transaction_t *trans = NULL;
+	conn_t *conn;
+
+	conn = conn_get(this, &trans);
+	if (!conn)
+	{
+		return FALSE;
+	}
+	else if (trans)
+	{
+		ref_get(&trans->refs);
+		return TRUE;
+	}
+	/* these statements are not supported in prepared statements that are used
+	 * by the execute() method */
+	if (serializable)
+	{
+		if (mysql_query(conn->mysql,
+						"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE") != 0)
+		{
+			DBG1(DBG_LIB, "starting transaction failed: %s",
+				 mysql_error(conn->mysql));
+			conn_release(this, conn);
+			return FALSE;
+		}
+	}
+	if (mysql_query(conn->mysql, "START TRANSACTION") != 0)
+	{
+		DBG1(DBG_LIB, "starting transaction failed: %s",
+			 mysql_error(conn->mysql));
+		conn_release(this, conn);
+		return FALSE;
+	}
+	INIT(trans,
+		.conn = conn,
+		.refs = 1,
+	);
+	this->transaction->set(this->transaction, trans);
+	return TRUE;
+}
+
+/**
+ * Finalize a transaction depending on the reference count and if it should be
+ * rolled back.
+ */
+static bool finalize_transaction(private_mysql_database_t *this,
+								 bool rollback)
+{
+	transaction_t *trans;
+	char *command = "COMMIT";
+	bool success;
+
+	trans = this->transaction->get(this->transaction);
+	if (!trans)
+	{
+		DBG1(DBG_LIB, "no database transaction found");
+		return FALSE;
+	}
+	/* set flag, can't be unset */
+	trans->rollback |= rollback;
+
+	if (ref_put(&trans->refs))
+	{
+		if (trans->rollback)
+		{
+			command = "ROLLBACK";
+		}
+		success = mysql_query(trans->conn->mysql, command) == 0;
+
+		this->transaction->set(this->transaction, NULL);
+		transaction_destroy(this, trans);
+		return success;
+	}
+	return TRUE;
+}
+
+METHOD(database_t, commit, bool,
+	private_mysql_database_t *this)
+{
+	return finalize_transaction(this, FALSE);
+}
+
+METHOD(database_t, rollback, bool,
+	private_mysql_database_t *this)
+{
+	return finalize_transaction(this, TRUE);
+}
+
 METHOD(database_t, get_driver,db_driver_t,
 	private_mysql_database_t *this)
 {
@@ -601,6 +751,7 @@ METHOD(database_t, get_driver,db_driver_t,
 METHOD(database_t, destroy, void,
 	private_mysql_database_t *this)
 {
+	this->transaction->destroy(this->transaction);
 	this->pool->destroy_function(this->pool, (void*)conn_destroy);
 	this->mutex->destroy(this->mutex);
 	free(this->host);
@@ -676,6 +827,9 @@ mysql_database_t *mysql_database_create(char *uri)
 			.db = {
 				.query = _query,
 				.execute = _execute,
+				.transaction = _transaction,
+				.commit = _commit,
+				.rollback = _rollback,
 				.get_driver = _get_driver,
 				.destroy = _destroy,
 			},
@@ -689,15 +843,15 @@ mysql_database_t *mysql_database_create(char *uri)
 	}
 	this->mutex = mutex_create(MUTEX_TYPE_DEFAULT);
 	this->pool = linked_list_create();
+	this->transaction = thread_value_create(NULL);
 
 	/* check connectivity */
-	conn = conn_get(this);
+	conn = conn_get(this, NULL);
 	if (!conn)
 	{
 		destroy(this);
 		return NULL;
 	}
-	conn_release(conn);
+	conn_release(this, conn);
 	return &this->public;
 }
-
diff --git a/src/libstrongswan/plugins/sqlite/sqlite_database.c b/src/libstrongswan/plugins/sqlite/sqlite_database.c
index 41d45dee7..7b4767855 100644
--- a/src/libstrongswan/plugins/sqlite/sqlite_database.c
+++ b/src/libstrongswan/plugins/sqlite/sqlite_database.c
@@ -1,4 +1,5 @@
 /*
+ * Copyright (C) 2013 Tobias Brunner
  * Copyright (C) 2007 Martin Willi
  * Hochschule fuer Technik Rapperswil
  *
@@ -20,6 +21,7 @@
 #include <library.h>
 #include <utils/debug.h>
 #include <threading/mutex.h>
+#include <threading/thread_value.h>
 
 typedef struct private_sqlite_database_t private_sqlite_database_t;
 
@@ -39,12 +41,34 @@ struct private_sqlite_database_t {
 	sqlite3 *db;
 
 	/**
-	 * mutex used to lock execute()
+	 * thread-specific transaction, as transaction_t
+	 */
+	thread_value_t *transaction;
+
+	/**
+	 * mutex used to lock execute(), if necessary
 	 */
 	mutex_t *mutex;
 };
 
 /**
+ * Database transaction
+ */
+typedef struct {
+
+	/**
+	 * Refcounter if transaction() is called multiple times
+	 */
+	refcount_t refs;
+
+	/**
+	 * TRUE if transaction was rolled back
+	 */
+	bool rollback;
+
+} transaction_t;
+
+/**
  * Create and run a sqlite stmt using a sql string and args
  */
 static sqlite3_stmt* run(private_sqlite_database_t *this, char *sql,
@@ -280,6 +304,79 @@ METHOD(database_t, execute, int,
 	return affected;
 }
 
+METHOD(database_t, transaction, bool,
+	private_sqlite_database_t *this, bool serializable)
+{
+	transaction_t *trans;
+	char *cmd = serializable ? "BEGIN EXCLUSIVE TRANSACTION"
+							 : "BEGIN TRANSACTION";
+
+	trans = this->transaction->get(this->transaction);
+	if (trans)
+	{
+		ref_get(&trans->refs);
+		return TRUE;
+	}
+	if (execute(this, NULL, cmd) == -1)
+	{
+		return FALSE;
+	}
+	INIT(trans,
+		.refs = 1,
+	);
+	this->transaction->set(this->transaction, trans);
+	return TRUE;
+}
+
+/**
+ * Finalize a transaction depending on the reference count and if it should be
+ * rolled back.
+ */
+static bool finalize_transaction(private_sqlite_database_t *this,
+								 bool rollback)
+{
+	transaction_t *trans;
+	char *command = "COMMIT TRANSACTION";
+	bool success;
+
+	trans = this->transaction->get(this->transaction);
+	if (!trans)
+	{
+		DBG1(DBG_LIB, "no database transaction found");
+		return FALSE;
+	}
+
+	if (ref_put(&trans->refs))
+	{
+		if (trans->rollback)
+		{
+			command = "ROLLBACK TRANSACTION";
+		}
+		success = execute(this, NULL, command) != -1;
+
+		this->transaction->set(this->transaction, NULL);
+		free(trans);
+		return success;
+	}
+	else
+	{	/* set flag, can't be unset */
+		trans->rollback |= rollback;
+	}
+	return TRUE;
+}
+
+METHOD(database_t, commit, bool,
+	private_sqlite_database_t *this)
+{
+	return finalize_transaction(this, FALSE);
+}
+
+METHOD(database_t, rollback, bool,
+	private_sqlite_database_t *this)
+{
+	return finalize_transaction(this, TRUE);
+}
+
 METHOD(database_t, get_driver, db_driver_t,
 	private_sqlite_database_t *this)
 {
@@ -304,6 +401,7 @@ METHOD(database_t, destroy, void,
 	{
 		DBG1(DBG_LIB, "sqlite close failed because database is busy");
 	}
+	this->transaction->destroy(this->transaction);
 	this->mutex->destroy(this->mutex);
 	free(this);
 }
@@ -330,18 +428,22 @@ sqlite_database_t *sqlite_database_create(char *uri)
 			.db = {
 				.query = _query,
 				.execute = _execute,
+				.transaction = _transaction,
+				.commit = _commit,
+				.rollback = _rollback,
 				.get_driver = _get_driver,
 				.destroy = _destroy,
 			},
 		},
 		.mutex = mutex_create(MUTEX_TYPE_RECURSIVE),
+		.transaction = thread_value_create(NULL),
 	);
 
 	if (sqlite3_open(file, &this->db) != SQLITE_OK)
 	{
 		DBG1(DBG_LIB, "opening SQLite database '%s' failed: %s",
 			 file, sqlite3_errmsg(this->db));
-		_destroy(this);
+		destroy(this);
 		return NULL;
 	}
 
@@ -349,4 +451,3 @@ sqlite_database_t *sqlite_database_create(char *uri)
 
 	return &this->public;
 }
-
diff --git a/src/libhydra/plugins/attr_sql/.gitignore b/src/pool/.gitignore
index eae4e6ad0..eae4e6ad0 100644
--- a/src/libhydra/plugins/attr_sql/.gitignore
+++ b/src/pool/.gitignore
diff --git a/src/pool/Makefile.am b/src/pool/Makefile.am
new file mode 100644
index 000000000..8b429a4ba
--- /dev/null
+++ b/src/pool/Makefile.am
@@ -0,0 +1,16 @@
+ipsec_PROGRAMS = pool
+
+pool_SOURCES = \
+	pool.c pool_attributes.c pool_attributes.h \
+	pool_usage.h pool_usage.c
+
+pool.o :	$(top_builddir)/config.status
+
+AM_CPPFLAGS = \
+	-I$(top_srcdir)/src/libstrongswan \
+	-I$(top_srcdir)/src/libhydra \
+	-DPLUGINS=\""${pool_plugins}\""
+
+pool_LDADD = \
+	$(top_builddir)/src/libstrongswan/libstrongswan.la \
+	$(top_builddir)/src/libhydra/libhydra.la
diff --git a/src/libhydra/plugins/attr_sql/pool.c b/src/pool/pool.c
index 4e7c48e23..05043cd8c 100644
--- a/src/libhydra/plugins/attr_sql/pool.c
+++ b/src/pool/pool.c
@@ -52,41 +52,6 @@ static void del(char *name);
 static void do_args(int argc, char *argv[]);
 
 /**
- * nesting counter for database transaction functions
- */
-int nested_transaction = 0;
-
-/**
- * start a database transaction
- */
-static void begin_transaction()
-{
-	if (db->get_driver(db) == DB_SQLITE)
-	{
-		if (!nested_transaction)
-		{
-			db->execute(db, NULL, "BEGIN EXCLUSIVE TRANSACTION");
-		}
-		++nested_transaction;
-	}
-}
-
-/**
- * commit a database transaction
- */
-static void commit_transaction()
-{
-	if (db->get_driver(db) == DB_SQLITE)
-	{
-		--nested_transaction;
-		if (!nested_transaction)
-		{
-			db->execute(db, NULL, "END TRANSACTION");
-		}
-	}
-}
-
-/**
  * Create or replace a pool by name
  */
 static u_int create_pool(char *name, chunk_t start, chunk_t end, int timeout)
@@ -370,8 +335,7 @@ static void add(char *name, host_t *start, host_t *end, int timeout)
 	id = create_pool(name, start_addr, end_addr, timeout);
 	printf("allocating %d addresses... ", count);
 	fflush(stdout);
-	/* run population in a transaction for sqlite */
-	begin_transaction();
+	db->transaction(db, FALSE);
 	while (TRUE)
 	{
 		db->execute(db, NULL,
@@ -384,7 +348,7 @@ static void add(char *name, host_t *start, host_t *end, int timeout)
 		}
 		chunk_increment(cur_addr);
 	}
-	commit_transaction();
+	db->commit(db);
 	printf("done.\n");
 }
 
@@ -449,8 +413,7 @@ static void add_addresses(char *pool, char *path, int timeout)
 	host_t *addr;
 	FILE *file;
 
-	/* run population in a transaction for sqlite */
-	begin_transaction();
+	db->transaction(db, FALSE);
 
 	addr = host_create_from_string("%any", 0);
 	pool_id = create_pool(pool, addr->get_address(addr),
@@ -510,7 +473,7 @@ static void add_addresses(char *pool, char *path, int timeout)
 		addr->destroy(addr);
 	}
 
-	commit_transaction();
+	db->commit(db);
 
 	printf("%d addresses done.\n", count);
 }
@@ -596,6 +559,7 @@ static void resize(char *name, host_t *end)
 	}
 	DESTROY_IF(old_end);
 
+	db->transaction(db, FALSE);
 	if (db->execute(db, NULL,
 			"UPDATE pools SET end = ? WHERE name = ?",
 			DB_BLOB, new_addr, DB_TEXT, name) <= 0)
@@ -606,8 +570,6 @@ static void resize(char *name, host_t *end)
 
 	printf("allocating %d new addresses... ", count);
 	fflush(stdout);
-	/* run population in a transaction for sqlite */
-	begin_transaction();
 	while (count-- > 0)
 	{
 		chunk_increment(cur_addr);
@@ -616,7 +578,7 @@ static void resize(char *name, host_t *end)
 			"VALUES (?, ?, ?, ?, ?)",
 			DB_UINT, id, DB_BLOB, cur_addr,	DB_UINT, 0, DB_UINT, 0, DB_UINT, 1);
 	}
-	commit_transaction();
+	db->commit(db);
 	printf("done.\n");
 
 }
@@ -900,7 +862,7 @@ static void batch(char *argv0, char *name)
 		exit(EXIT_FAILURE);
 	}
 
-	begin_transaction();
+	db->transaction(db, FALSE);
 	while (fgets(command, sizeof(command), file))
 	{
 		char *argv[ARGV_SIZE], *start;
@@ -939,7 +901,7 @@ static void batch(char *argv0, char *name)
 
 		do_args(argc, argv);
 	}
-	commit_transaction();
+	db->commit(db);
 
 	if (file != stdin)
 	{
@@ -1284,4 +1246,3 @@ int main(int argc, char *argv[])
 
 	exit(EXIT_SUCCESS);
 }
-
diff --git a/src/libhydra/plugins/attr_sql/pool_attributes.c b/src/pool/pool_attributes.c
index 1d1ba8f58..72af4f494 100644
--- a/src/libhydra/plugins/attr_sql/pool_attributes.c
+++ b/src/pool/pool_attributes.c
@@ -49,7 +49,7 @@ static const attr_info_t attr_info[] = {
 	{ "internal_ip4_netmask", VALUE_ADDR,   INTERNAL_IP4_NETMASK, 0 },
 	{ "internal_ip6_netmask", VALUE_ADDR,   INTERNAL_IP6_NETMASK, 0 },
 	{ "netmask",              VALUE_ADDR,   INTERNAL_IP4_NETMASK,
-							 				INTERNAL_IP6_NETMASK    },
+											INTERNAL_IP6_NETMASK    },
 	{ "internal_ip4_dns",     VALUE_ADDR,   INTERNAL_IP4_DNS,     0 },
 	{ "internal_ip6_dns",     VALUE_ADDR,   INTERNAL_IP6_DNS,     0 },
 	{ "dns",                  VALUE_ADDR,   INTERNAL_IP4_DNS,
@@ -57,7 +57,7 @@ static const attr_info_t attr_info[] = {
 	{ "internal_ip4_nbns",    VALUE_ADDR,   INTERNAL_IP4_NBNS,    0 },
 	{ "internal_ip6_nbns",    VALUE_ADDR,   INTERNAL_IP6_NBNS,    0 },
 	{ "nbns",                 VALUE_ADDR,   INTERNAL_IP4_NBNS,
-							 				INTERNAL_IP6_NBNS       },
+											INTERNAL_IP6_NBNS       },
 	{ "wins",                 VALUE_ADDR,   INTERNAL_IP4_NBNS,
 											INTERNAL_IP6_NBNS       },
 	{ "internal_ip4_dhcp",    VALUE_ADDR,   INTERNAL_IP4_DHCP,    0 },
@@ -214,7 +214,7 @@ static bool parse_attributes(char *name, char *value, value_type_t *value_type,
 			if (*value_type == VALUE_ADDR)
 			{
 				*type = (addr->get_family(addr) == AF_INET) ?
-						 	attr_info[i].type : attr_info[i].type_ip6;
+							attr_info[i].type : attr_info[i].type_ip6;
 				addr->destroy(addr);
 			}
 			else if (*value_type == VALUE_HEX)
@@ -493,14 +493,14 @@ void del_attr(char *name, char *pool, char *identity,
 			else if (value_type == VALUE_STRING)
 			{
 				fprintf(stderr, "deleting %s attribute (%N) with value '%.*s'%s failed.\n",
-						 		name, configuration_attribute_type_names, type,
+								name, configuration_attribute_type_names, type,
 								(int)blob_db.len, blob_db.ptr, id_pool_str);
 			}
 
 			else
 			{
 				fprintf(stderr, "deleting %s attribute (%N) with value %#B%s failed.\n",
-						 		name, configuration_attribute_type_names, type,
+								name, configuration_attribute_type_names, type,
 								&blob_db, id_pool_str);
 			}
 			query->destroy(query);
@@ -529,7 +529,7 @@ void del_attr(char *name, char *pool, char *identity,
 
 	if (!found)
 	{
-	 	if (blob.len == 0)
+		if (blob.len == 0)
 		{
 			if (type_ip6 == 0)
 			{
@@ -714,4 +714,3 @@ void show_attr(void)
 		}
 	}
 }
-
diff --git a/src/libhydra/plugins/attr_sql/pool_attributes.h b/src/pool/pool_attributes.h
index a42291f57..6a5af3349 100644
--- a/src/libhydra/plugins/attr_sql/pool_attributes.h
+++ b/src/pool/pool_attributes.h
@@ -61,5 +61,3 @@ void status_attr(bool hexout);
 void show_attr(void);
 
 #endif /* POOL_ATTRIBUTES_H_ */
-
-
diff --git a/src/libhydra/plugins/attr_sql/pool_usage.c b/src/pool/pool_usage.c
index 985bc3ae8..7622cfa86 100644
--- a/src/libhydra/plugins/attr_sql/pool_usage.c
+++ b/src/pool/pool_usage.c
@@ -124,4 +124,3 @@ Usage:\n\
                lines are ignored. The file may not contain a --batch command.\n\
   \n");
 }
-
diff --git a/src/libhydra/plugins/attr_sql/pool_usage.h b/src/pool/pool_usage.h
index a98b0d680..0082ef6f2 100644
--- a/src/libhydra/plugins/attr_sql/pool_usage.h
+++ b/src/pool/pool_usage.h
@@ -22,5 +22,4 @@
  */
 void usage(void);
 
-
 #endif /* POOL_USAGE_H_ */