experimental and untested reimplementation of sql based IP pool

uses address preallocation and separate address/lease tables for linear lookup time

4 files changed, 300 insertions, 232 deletions

diff --git a/src/charon/plugins/sql/mysql.sql b/src/charon/plugins/sql/mysql.sql
index f98fe71e2..412abc715 100644
--- a/src/charon/plugins/sql/mysql.sql
+++ b/src/charon/plugins/sql/mysql.sql
@@ -157,15 +157,14 @@ CREATE TABLE pools (
   `name` varchar(32) NOT NULL,
   `start` varbinary(16) NOT NULL,
   `end` varbinary(16) NOT NULL,
-  `next` varbinary(16) NOT NULL,
   `timeout` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE (`name`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
-DROP TABLE IF EXISTS leases;
-CREATE TABLE leases (
+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,
@@ -175,7 +174,17 @@ CREATE TABLE leases (
   PRIMARY KEY (`id`),
   INDEX (`pool`),
   INDEX (`identity`),
-  INDEX (`released`)
+  INDEX (`address`)
+);
+
+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`)
 );
 
 
diff --git a/src/charon/plugins/sql/pool.c b/src/charon/plugins/sql/pool.c
index 86ceddeee..288ef06b4 100644
--- a/src/charon/plugins/sql/pool.c
+++ b/src/charon/plugins/sql/pool.c
@@ -100,7 +100,7 @@ Usage:\n\
       utc:    Show times in UTC instead of local time\n\
   \n\
   ipsec pool --purge <name>\n\
-    Delete expired leases of a pool:\n\
+    Delete lease history of a pool:\n\
       name:   Name of the pool to purge\n\
   \n");
 	exit(0);
@@ -147,8 +147,8 @@ static void status(void)
 			}
 			printf("%6d ", size);
 			/* get number of online hosts */
-			lease = db->query(db, "SELECT COUNT(*) FROM leases "
-							  "WHERE pool = ? AND released IS NULL",
+			lease = db->query(db, "SELECT COUNT(*) FROM addresses "
+							  "WHERE pool = ? AND acquired != 0 AND released = 0",
 							  DB_UINT, id, DB_INT);
 			if (lease)
 			{
@@ -157,10 +157,10 @@ static void status(void)
 			}
 			printf("%5d (%2d%%) ", online, online*100/size);
 			/* get number of online or valid lieases */
-			lease = db->query(db, "SELECT COUNT(*) FROM leases JOIN pools "
-							  "ON leases.pool = pools.id "
-							  "WHERE pools.id = ? "
-							  "AND (released IS NULL OR released > ? - timeout) ",
+			lease = db->query(db, "SELECT COUNT(*) FROM addresses JOIN pools "
+							  "ON addresses.pool = pools.id "
+							  "WHERE pools.id = ? AND acquired != 0 "
+							  "AND (released = 0 OR released > ? - timeout) ",
 							  DB_UINT, id, DB_UINT, time(NULL), DB_UINT);
 			if (lease)
 			{
@@ -183,14 +183,33 @@ static void status(void)
 }
 
 /**
+ * increment a chunk, as it would reprensent a network order integer
+ */
+static void increment_chunk(chunk_t chunk)
+{
+	int i;
+	
+	for (i = chunk.len - 1; i >= 0; i--)
+	{
+		if (++chunk.ptr[i] != 0)
+		{
+			return;
+		}
+	}
+}
+
+/**
  * ipsec pool --add - add a new pool
  */
 static void add(char *name, host_t *start, host_t *end, int timeout)
 {
-	chunk_t start_addr, end_addr;
+	chunk_t start_addr, end_addr, cur_addr;
+	u_int id, count;
 	
 	start_addr = start->get_address(start);
 	end_addr = end->get_address(end);
+	cur_addr = chunk_clonea(start_addr);
+	count = get_pool_size(start_addr, end_addr);
 
 	if (start_addr.len != end_addr.len ||
 		memcmp(start_addr.ptr, end_addr.ptr, start_addr.len) > 0)
@@ -198,16 +217,36 @@ static void add(char *name, host_t *start, host_t *end, int timeout)
 		fprintf(stderr, "invalid start/end pair specified.\n");
 		exit(-1);
 	}
-	if (db->execute(db, NULL,
-			"INSERT INTO pools (name, start, end, next, timeout) "
-			"VALUES (?, ?, ?, ?, ?)",
+	if (db->execute(db, &id,
+			"INSERT INTO pools (name, start, end, timeout) "
+			"VALUES (?, ?, ?, ?)",
 			DB_TEXT, name, DB_BLOB, start_addr,
-			DB_BLOB, end_addr, DB_BLOB, start_addr,
-			DB_INT, timeout*3600) != 1)
+			DB_BLOB, end_addr, DB_INT, timeout*3600) != 1)
 	{
 		fprintf(stderr, "creating pool failed.\n");
 		exit(-1);
 	}
+	printf("allocating %d addresses... ", count);
+	fflush(stdout);
+	if (db->get_driver(db) == DB_SQLITE)
+	{	/* run population in a transaction for sqlite */
+		db->execute(db, NULL, "BEGIN TRANSACTION");
+	}
+	do
+	{
+		db->execute(db, NULL,
+			"INSERT INTO addresses (pool, address, identity, acquired, released) "
+			"VALUES (?, ?, ?, ?, ?)",
+			DB_UINT, id, DB_BLOB, cur_addr,	DB_UINT, 0, DB_UINT, 0, DB_UINT, 1);
+		increment_chunk(cur_addr);
+	}
+	while (!chunk_equals(cur_addr, end_addr));
+	if (db->get_driver(db) == DB_SQLITE)
+	{
+		db->execute(db, NULL, "END TRANSACTION");
+	}
+	printf("done.\n", count);
+	
 	exit(0);
 }
 
@@ -231,9 +270,12 @@ static void del(char *name)
 	{
 		found = TRUE;
 		if (db->execute(db, NULL,
-				"DELETE FROM pools WHERE id = ?", DB_UINT, id) != 1 ||
+				"DELETE FROM leases WHERE address IN ("
+				" SELECT id FROM addresses WHERE pool = ?)", DB_UINT, id) < 0 ||
+			db->execute(db, NULL,
+				"DELETE FROM addresses WHERE pool = ?", DB_UINT, id) < 0 ||
 			db->execute(db, NULL,
-				"DELETE FROM leases WHERE pool = ?", DB_UINT, id) < 0)
+				"DELETE FROM pools WHERE id = ?", DB_UINT, id) < 0)
 		{
 			fprintf(stderr, "deleting pool failed.\n");
 			query->destroy(query);
@@ -255,36 +297,58 @@ static void del(char *name)
 static void resize(char *name, host_t *end)
 {
 	enumerator_t *query;
-	chunk_t next_addr, end_addr;
+	chunk_t old_addr, new_addr, cur_addr;
+	u_int id, count;
 	
-	end_addr = end->get_address(end);
+	new_addr = end->get_address(end);
 	
-	query = db->query(db, "SELECT next FROM pools WHERE name = ?",
-					  DB_TEXT, name, DB_BLOB);
-	if (!query || !query->enumerate(query, &next_addr))
+	query = db->query(db, "SELECT id, end FROM pools WHERE name = ?",
+					  DB_TEXT, name, DB_UINT, DB_BLOB);
+	if (!query || !query->enumerate(query, &id, &old_addr))
 	{
 		DESTROY_IF(query);
 		fprintf(stderr, "resizing pool failed.\n");
 		exit(-1);
 	}
-	if (next_addr.len != end_addr.len ||
-		memcmp(end_addr.ptr, next_addr.ptr, end_addr.len) < 0)
+	if (old_addr.len != new_addr.len ||
+		memcmp(new_addr.ptr, old_addr.ptr, old_addr.len) < 0)
 	{
-		end = host_create_from_blob(next_addr);
-		fprintf(stderr, "pool addresses up to %H in use, resizing failed.\n", end);
-		end->destroy(end);
+		fprintf(stderr, "shrinking of pools not supported.\n");
 		query->destroy(query);
 		exit(-1);
 	}
+	cur_addr = chunk_clonea(old_addr);
+	count = get_pool_size(old_addr, new_addr) - 1;
 	query->destroy(query);
 
 	if (db->execute(db, NULL,
 			"UPDATE pools SET end = ? WHERE name = ?",
-			DB_BLOB, end_addr, DB_TEXT, name) <= 0)
+			DB_BLOB, new_addr, DB_TEXT, name) <= 0)
 	{
 		fprintf(stderr, "pool '%s' not found.\n", name);
 		exit(-1);
 	}
+	
+	printf("allocating %d new addresses... ", count);
+	fflush(stdout);
+	if (db->get_driver(db) == DB_SQLITE)
+	{	/* run population in a transaction for sqlite */
+		db->execute(db, NULL, "BEGIN TRANSACTION");
+	}
+	while (count-- > 0)
+	{
+		increment_chunk(cur_addr);
+		db->execute(db, NULL,
+			"INSERT INTO addresses (pool, address, identity, acquired, released) "
+			"VALUES (?, ?, ?, ?, ?)",
+			DB_UINT, id, DB_BLOB, cur_addr,	DB_UINT, 0, DB_UINT, 0, DB_UINT, 1);
+	}
+	if (db->get_driver(db) == DB_SQLITE)
+	{
+		db->execute(db, NULL, "END TRANSACTION");
+	}
+	printf("done.\n", count);
+	
 	exit(0);
 }
 
@@ -398,17 +462,27 @@ static enumerator_t *create_lease_query(char *filter)
 		}
 	}
 	query = db->query(db,
-				"SELECT name, address, identities.type, "
-				"identities.data, acquired, released, timeout "
-				"FROM leases JOIN pools ON leases.pool = pools.id "
+				"SELECT name, addresses.address, identities.type, "
+				"identities.data, leases.acquired, leases.released, timeout "
+				"FROM leases JOIN addresses ON leases.address = addresses.id "
+				"JOIN pools ON addresses.pool = pools.id "
 				"JOIN identities ON leases.identity = identities.id "
 				"WHERE (? OR name = ?) "
 				"AND (? OR (identities.type = ? AND identities.data = ?)) "
-				"AND (? OR address = ?) "
-				"AND (? OR (? >= acquired AND (? <= released OR released IS NULL))) "
-				"AND (? OR released IS NULL) "
-				"AND (? OR released > ? - timeout) "
-				"AND (? OR released < ? - timeout)",
+				"AND (? OR addresses.address = ?) "
+				"AND (? OR (? >= leases.acquired AND (? <= leases.released))) "
+				"AND (? OR leases.released > ? - timeout) "
+				"AND (? OR leases.released < ? - timeout) "
+				"AND ? "
+				"UNION "
+				"SELECT name, address, identities.type, identities.data, "
+				"acquired, released, timeout FROM addresses "
+				"JOIN pools ON addresses.pool = pools.id "
+				"JOIN identities ON addresses.identity = identities.id "
+				"WHERE ? AND released = 0 "
+				"AND (? OR name = ?) "
+				"AND (? OR (identities.type = ? AND identities.data = ?)) "
+				"AND (? OR address = ?)",
 				DB_INT, pool == NULL, DB_TEXT, pool,
 				DB_INT, id == NULL,
 					DB_INT, id ? id->get_type(id) : 0,
@@ -416,9 +490,18 @@ static enumerator_t *create_lease_query(char *filter)
 				DB_INT, addr == NULL,
 					DB_BLOB, addr ? addr->get_address(addr) : chunk_empty,
 				DB_INT, tstamp == 0, DB_UINT, tstamp, DB_UINT, tstamp,
-				DB_INT, !online,
 				DB_INT, !valid, DB_INT, time(NULL),
 				DB_INT, !expired, DB_INT, time(NULL),
+				DB_INT, !online,
+				/* union */
+				DB_INT, !(valid || expired),
+				DB_INT, pool == NULL, DB_TEXT, pool,
+				DB_INT, id == NULL,
+					DB_INT, id ? id->get_type(id) : 0,
+					DB_BLOB, id ? id->get_encoding(id) : chunk_empty,
+				DB_INT, addr == NULL,
+					DB_BLOB, addr ? addr->get_address(addr) : chunk_empty,
+				/* res */
 				DB_TEXT, DB_BLOB, DB_INT, DB_BLOB, DB_UINT, DB_UINT, DB_UINT);
 	/* id and addr leak but we can't destroy them until query is destroyed. */
 	return query;
@@ -507,30 +590,18 @@ static void leases(char *filter, bool utc)
  */
 static void purge(char *name)
 {
-	enumerator_t *query;
-	u_int id, timeout, purged = 0;
+	int purged = 0;
 	
-	query = db->query(db, "SELECT id, timeout FROM pools WHERE name = ?",
-					  DB_TEXT, name, DB_UINT, DB_UINT);
-	if (!query)
+	purged = db->execute(db, NULL,
+				"DELETE FROM leases WHERE address IN ("
+				" SELECT id FROM addresses WHERE pool IN ("
+				"  SELECT id FROM pools WHERE name = ?))",
+				DB_TEXT, name);
+	if (purged < 0)
 	{
-		fprintf(stderr, "purging pool failed.\n");
+		fprintf(stderr, "purging pool '%s' failed.\n", name);
 		exit(-1);
 	}
-	/* we have to keep one lease if we purge. It wouldn't be reallocateable
-	 * as we move on the "next" address for speedy allocation */
-	if (query->enumerate(query, &id, &timeout))
-	{
-		timeout = time(NULL) - timeout;
-		purged = db->execute(db, NULL,
-					"DELETE FROM leases WHERE pool = ? "
-					"AND released IS NOT NULL AND released < ? AND id NOT IN ("
-					" SELECT id FROM leases "
-					" WHERE released IS NOT NULL and released < ? "
-					" GROUP BY address)",
-					DB_UINT, id, DB_UINT, timeout, DB_UINT, timeout);
-	}
-	query->destroy(query);
 	fprintf(stderr, "purged %d leases in pool '%s'.\n", purged, name);
 	exit(0);
 }
diff --git a/src/charon/plugins/sql/sql_attribute.c b/src/charon/plugins/sql/sql_attribute.c
index 847042792..e3cf60fec 100644
--- a/src/charon/plugins/sql/sql_attribute.c
+++ b/src/charon/plugins/sql/sql_attribute.c
@@ -18,7 +18,6 @@
 #include "sql_attribute.h"
 
 #include <daemon.h>
-#include <utils/mutex.h>
 
 typedef struct private_sql_attribute_t private_sql_attribute_t;
 
@@ -36,187 +35,152 @@ struct private_sql_attribute_t {
 	 * database connection
 	 */
 	database_t *db;
-	
-	/**
-	 * mutex to simulate transactions
-	 */
-	mutex_t *mutex;
 };
 
-/** 
- * convert a address blob to an ip of the correct family
+/**
+ * read a host_t address from the addresses table
  */
-static host_t *ip_from_chunk(chunk_t address)
+static host_t *host_from_chunk(chunk_t chunk)
 {
-	switch (address.len)
+	switch (chunk.len)
 	{
 		case 4:
-			return host_create_from_chunk(AF_INET, address, 0);
+			return host_create_from_chunk(AF_INET, chunk, 0);
 		case 16:
-			return host_create_from_chunk(AF_INET6, address, 0);
+			return host_create_from_chunk(AF_INET6, chunk, 0);
 		default:
 			return NULL;
-	}		
+	}
 }
 
 /**
- * increment a chunk, as it would reprensent a network order integer
+ * lookup/insert an identity
  */
-static void increment_chunk(chunk_t chunk)
+static u_int get_identity(private_sql_attribute_t *this, identification_t *id)
 {
-	int i;
+	enumerator_t *e;
+	u_int row;
 	
-	for (i = chunk.len - 1; i >= 0; i--)
+	/* 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))
 	{
-		if (++chunk.ptr[i] != 0)
-		{
-			return;
-		}
+		e->destroy(e);
+		return row;
+	}
+	DESTROY_IF(e);
+	/* not found, insert new one */
+	if (this->db->execute(this->db, &row,
+				  "INSERT INTO identities (type, data) VALUES (?, ?)",
+				  DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id)) == 1)
+	{
+		return row;
 	}
+	return 0;
 }
 
 /**
- * Lookup if we have an existing lease
+ * Lookup pool by name
  */
-static host_t* get_lease(private_sql_attribute_t *this,
-						 char *name, identification_t *id)
+static u_int get_pool(private_sql_attribute_t *this, char *name, u_int *timeout)
 {
 	enumerator_t *e;
-	chunk_t address;
-	host_t *ip = NULL;
-	int lease;
-	
-	/* transaction simulation, see create_lease() */
-	this->mutex->lock(this->mutex);
-	
-	/* select a lease for "id" which still valid */
-	e = this->db->query(this->db,
-						"SELECT l.id, l.address FROM leases AS l "
-						"JOIN pools AS p ON l.pool = p.id "
-						"JOIN identities AS i ON l.identity = i.id "
-						"WHERE p.name = ? AND i.type = ? AND i.data = ? "
-						"AND (l.released IS NULL OR p.timeout = 0 "
-						" OR (l.released >= (? - p.timeout))) "
-						"ORDER BY l.acquired LIMIT 1", DB_TEXT, name,
-						DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
-						DB_UINT, time(NULL),
-						DB_UINT, DB_BLOB);
-	if (e)
+	u_int pool;
+
+	e = this->db->query(this->db, "SELECT id, timeout FROM pools WHERE name = ?",
+						DB_TEXT, name, DB_UINT, DB_UINT);
+	if (e && e->enumerate(e, &pool, timeout))
 	{
-		if (e->enumerate(e, &lease, &address))
-		{
-			/* found one, set the lease to active */
-			if (this->db->execute(this->db, NULL,
-						  "UPDATE leases SET released = NULL WHERE id = ?",
-						  DB_UINT, lease) > 0)
-			{
-				ip = ip_from_chunk(address);
-				DBG1(DBG_CFG, "reassigning address from valid lease "
-					 "from pool '%s'", name);
-			}
-		}
 		e->destroy(e);
+		return pool;
 	}
-	this->mutex->unlock(this->mutex);
-	return ip;
+	DBG1(DBG_CFG, "ip pool '%s' not found");
+	return 0;
 }
 
 /**
- * Create a new lease entry for client
+ * Lookup a lease
  */
-static host_t* create_lease(private_sql_attribute_t *this,
-							char *name, identification_t *id)
+static host_t *get_address(private_sql_attribute_t *this, char *name,
+						   u_int pool, u_int timeout, u_int identity)
 {
 	enumerator_t *e;
+	u_int id;
 	chunk_t address;
-	host_t *ip = NULL;
-	u_int pool, identity = 0, released, timeout;
-	bool new = FALSE;
-	
-	/* we currently do not use database transactions. While this would be 
-	 * the clean way, there is no real advantage, but some disadvantages:
-	 * - we would require InnoDB for mysql, as MyISAM does not support trans.
-	 * - the mysql plugin uses connection pooling, and we would need a 
-	 *   mechanism to lock transactions to a single connection.
-	 */
-	this->mutex->lock(this->mutex);
+	host_t *host;
+	time_t now = time(NULL);
 	
-	/* find an address which has outdated leases only. The HAVING clause filters
-	 * out leases which are active (released = NULL) or not expired */
-	e = this->db->query(this->db,
-						"SELECT pool, address, released, timeout FROM leases "
-						"JOIN pools ON leases.pool = pools.id "
-						"WHERE name = ? and timeout > 0 "
-						"GROUP BY address HAVING COUNT(released) = COUNT(*) "
-						"AND MAX(released) < (? - timeout) LIMIT 1",
-						DB_TEXT, name, DB_UINT, time(NULL),
-						DB_UINT, DB_BLOB, DB_UINT, DB_UINT);
+	/* We check for leases for that identity first and for other expired
+	 * leases afterwards. We select an address as a candidate, but double
+	 * check if it is still valid in the update. This allows us to work
+	 * without locking. */
 	
-	if (!e || !e->enumerate(e, &pool, &address, &released, &timeout))
+	/* check for an existing lease for that identity  */
+	while (TRUE)
 	{
-		DESTROY_IF(e);
-		/* no outdated lease found, acquire new address */
 		e = this->db->query(this->db,
-				"SELECT id, next FROM pools WHERE name = ? AND next <= end",
-				DB_TEXT, name,
-				DB_UINT, DB_BLOB);
-		if (!e || !e->enumerate(e, &pool, &address))
+				"SELECT id, address FROM addresses "
+				"WHERE pool = ? AND identity = ? AND released != 0 LIMIT 1",
+				DB_UINT, pool, DB_UINT, identity, DB_UINT, DB_BLOB);
+		if (!e || !e->enumerate(e, &id, &address))
 		{
-			/* pool seems full */
 			DESTROY_IF(e);
-			this->mutex->unlock(this->mutex);
-			return NULL;
+			break;	
+		}
+		address = chunk_clonea(address);
+		e->destroy(e);
+		if (this->db->execute(this->db, NULL,
+				"UPDATE addresses SET acquired = ?, released = 0 "
+				"WHERE id = ? AND identity = ? AND released != 0",
+				DB_UINT, now, DB_UINT, id, DB_UINT, identity) > 0)
+		{
+			host = host_from_chunk(address);
+			if (host)
+			{
+				DBG1(DBG_CFG, "acquired existing lease "
+					 "for address %H in pool '%s'", host, name);
+				return host;
+			}
 		}
-		new = TRUE;
 	}
-	address = chunk_clonea(address);
-	e->destroy(e);
 	
-	/* 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, &identity))
-	{
-		DESTROY_IF(e);
-		/* not found, insert new one */
-		this->db->execute(this->db, &identity,
-					  "INSERT INTO identities (type, data) VALUES (?, ?)",
-					  DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id));
-	}
-	else
+	/* check for an expired lease */
+	while (TRUE)
 	{
+		e = this->db->query(this->db,
+				"SELECT id, address FROM addresses "
+				"WHERE pool = ? AND released != 0 AND released < ? LIMIT 1",
+				DB_UINT, pool, DB_UINT, now - timeout, DB_UINT, DB_BLOB);
+		if (!e || !e->enumerate(e, &id, &address))
+		{
+			DESTROY_IF(e);
+			break;	
+		}
+		address = chunk_clonea(address);
 		e->destroy(e);
-	}
-	/* if we have an identity, insert a new lease */
-	if (identity)
-	{
+			
 		if (this->db->execute(this->db, NULL,
-				"INSERT INTO leases (pool, address, identity, acquired) "
-				"VALUES (?, ?, ?, ?)",
-				DB_UINT, pool, DB_BLOB, address, DB_UINT, identity,
-				DB_UINT, time(NULL)) > 0)
+				"UPDATE addresses SET "
+				"acquired = ?, released = 0, identity = ? "
+				"WHERE id = ? AND released != 0 AND released < ?",
+				DB_UINT, now, DB_UINT, identity,
+				DB_UINT, id, DB_UINT, now - timeout) > 0)
 		{
-			ip = ip_from_chunk(address);
-			if (new)
-			{	/* update next address, as we have consumed one */
-				increment_chunk(address);
-				this->db->execute(this->db, NULL,
-								  "UPDATE pools SET next = ? WHERE id = ?",
-								  DB_BLOB, address, DB_UINT, pool);
-				DBG1(DBG_CFG, "assigning lease with new address "
-					 "from pool '%s'", name);
-			}
-			else
+			host = host_from_chunk(address);
+			if (host)
 			{
-				DBG1(DBG_CFG, "reassigning address from expired lease "
-					 "from pool '%s'", name);
+				DBG1(DBG_CFG, "acquired new lease "
+					 "for address %H in pool '%s'", host, name);
+				return host;
 			}
 		}
 	}
-	this->mutex->unlock(this->mutex);
-	return ip;
+	DBG1(DBG_CFG, "no available address found in pool '%s'", name);
+	return 0;
 }
 
 /**
@@ -227,24 +191,28 @@ static host_t* acquire_address(private_sql_attribute_t *this,
 							   auth_info_t *auth, host_t *requested)
 {
 	enumerator_t *enumerator;
-	host_t *ip = NULL;
+	u_int pool, timeout, identity;
+	host_t *address = NULL;
 	
-	enumerator = enumerator_create_token(name, ",", " ");
-	while (enumerator->enumerate(enumerator, &name))
+	identity = get_identity(this, id);
+	if (identity)
 	{
-		ip = get_lease(this, name, id);
-		if (ip)
+		enumerator = enumerator_create_token(name, ",", " ");
+		while (enumerator->enumerate(enumerator, &name))
 		{
-			break;
-		}
-		ip = create_lease(this, name, id);
-		if (ip)
-		{
-			break;
+			pool = get_pool(this, name, &timeout);
+			if (pool)
+			{
+				address = get_address(this, name, pool, timeout, identity);
+				if (address)
+				{
+					break;
+				}
+			}
 		}
+		enumerator->destroy(enumerator);
 	}
-	enumerator->destroy(enumerator);
-	return ip;
+	return address;
 }
 
 /**
@@ -254,23 +222,35 @@ static bool release_address(private_sql_attribute_t *this,
 							char *name, host_t *address)
 {
 	enumerator_t *enumerator;
+	bool found = FALSE;
+	time_t now = time(NULL);
 	
 	enumerator = enumerator_create_token(name, ",", " ");
 	while (enumerator->enumerate(enumerator, &name))
 	{
-		if (this->db->execute(this->db, NULL,
-				"UPDATE leases SET released = ? WHERE "
-				"pool IN (SELECT id FROM pools WHERE name = ?) AND "
-				"address = ? AND released IS NULL",
-				DB_UINT, time(NULL),
-				DB_TEXT, name, DB_BLOB, address->get_address(address)) > 0)
+		u_int pool, timeout;
+		
+		pool = get_pool(this, name, &timeout);
+		if (pool)
 		{
-			enumerator->destroy(enumerator);
-			return TRUE;
+			if (this->db->execute(this->db, NULL,
+					"INSERT INTO leases (address, identity, acquired, released)"
+					" SELECT id, identity, acquired, ? FROM addresses "
+					" WHERE pool = ? AND address = ?",
+					DB_UINT, now, DB_UINT, pool,
+					DB_BLOB, address->get_address(address)) > 0 &&
+				this->db->execute(this->db, NULL,
+					"UPDATE addresses SET released = ? WHERE "
+					"pool = ? AND address = ?", DB_UINT, time(NULL),
+					DB_UINT, pool, DB_BLOB, address->get_address(address)) > 0)
+			{
+				found = TRUE;
+				break;
+			}
 		}
 	}
 	enumerator->destroy(enumerator);
-	return FALSE;
+	return found;
 }
 
 /**
@@ -278,7 +258,6 @@ static bool release_address(private_sql_attribute_t *this,
  */
 static void destroy(private_sql_attribute_t *this)
 {
-	this->mutex->destroy(this->mutex);
 	free(this);
 }
 
@@ -288,19 +267,22 @@ static void destroy(private_sql_attribute_t *this)
 sql_attribute_t *sql_attribute_create(database_t *db)
 {
 	private_sql_attribute_t *this = malloc_thing(private_sql_attribute_t);
+	time_t now = time(NULL);
 	
 	this->public.provider.acquire_address = (host_t*(*)(attribute_provider_t *this, char*, identification_t *,auth_info_t *, host_t *))acquire_address;
 	this->public.provider.release_address = (bool(*)(attribute_provider_t *this, char*,host_t *))release_address;
 	this->public.destroy = (void(*)(sql_attribute_t*))destroy;
 	
 	this->db = db;
-	this->mutex = mutex_create(MUTEX_DEFAULT);
 	
 	/* close any "online" leases in the case we crashed */
 	this->db->execute(this->db, NULL,
-					  "UPDATE leases SET released = ? WHERE released IS NULL",
-					  DB_UINT, time(NULL));
-	
+					"INSERT INTO leases (address, identity, acquired, released)"
+					" SELECT id, identity, acquired, ? FROM addresses "
+					" WHERE released = 0", DB_UINT, now);
+	this->db->execute(this->db, NULL,
+					  "UPDATE addresses SET released = ? WHERE released = 0",
+					  DB_UINT, now);
 	return &this->public;
 }
 
diff --git a/src/charon/plugins/sql/sqlite.sql b/src/charon/plugins/sql/sqlite.sql
index 49d350129..e1002d458 100644
--- a/src/charon/plugins/sql/sqlite.sql
+++ b/src/charon/plugins/sql/sqlite.sql
@@ -158,36 +158,42 @@ CREATE TABLE pools (
   name TEXT NOT NULL,
   start BLOB NOT NULL,
   end BLOB NOT NULL,
-  next BLOB NOT NULL,
-  timeout INTEGER NOT NULL,
-  UNIQUE (name)
+  timeout INTEGER NOT NULL
 );
 DROP INDEX IF EXISTS pools_name;
 CREATE INDEX pools_name ON pools (
   name
 );
 
-
-DROP TABLE IF EXISTS leases;
-CREATE TABLE leases (
+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,
   acquired INTEGER NOT NULL,
-  released INTEGER DEFAULT NULL
+  released INTEGER NOT NULL
 );
-DROP INDEX IF EXISTS leases_pool;
-CREATE INDEX leases_pool ON leases (
+DROP INDEX IF EXISTS addresses_pool;
+CREATE INDEX addresses_pool ON addresses (
   pool
 );
-DROP INDEX IF EXISTS leases_identity;
-CREATE INDEX leases_identity ON leases (
+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 INDEX IF EXISTS leases_released;
-CREATE INDEX leases_released ON leases (
-  released
+
+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
 );