ported IP pool to mysql
authorMartin Willi <martin@strongswan.org>
Fri, 9 May 2008 15:01:22 +0000 (15:01 -0000)
committerMartin Willi <martin@strongswan.org>
Fri, 9 May 2008 15:01:22 +0000 (15:01 -0000)
src/charon/plugins/sql/mysql.sql
src/charon/plugins/sql/pool.c
src/charon/plugins/sql/sql_attribute.c
src/charon/plugins/sql/sqlite.sql

index 6c04aef..3624abd 100644 (file)
@@ -151,6 +151,34 @@ CREATE TABLE shared_secret_identity (
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
+DROP TABLE IF EXISTS pools;
+CREATE TABLE pools (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `name` varchar(32) NOT NULL,
+  `start` varbinary(16) NOT NULL,
+  `end` varbinary(16) NOT NULL,
+  `next` varbinary(16) NOT NULL,
+  `timeout` int(10) unsigned DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE (`name`)
+) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS leases;
+CREATE TABLE leases (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `pool` int(10) unsigned NOT NULL,
+  `address` varbinary(16) NOT NULL,
+  `identity` int(10) unsigned NOT NULL,
+  `acquired` int(10) unsigned NOT NULL,
+  `released` int(10) unsigned DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  INDEX (`pool`),
+  INDEX (`identity`),
+  INDEX (`released`)
+);
+
+
 DROP TABLE IF EXISTS ike_sas;
 CREATE TABLE ike_sas (
   `local_spi` varbinary(8) NOT NULL,
@@ -164,7 +192,7 @@ CREATE TABLE ike_sas (
   `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,
+  `lastuse` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`local_spi`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
@@ -176,7 +204,7 @@ CREATE TABLE logs (
   `signal` tinyint(3) NOT NULL,
   `level` tinyint(3) NOT NULL,
   `msg` varchar(256) NOT NULL,
-  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
+  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
index 5d9509d..136c7e1 100644 (file)
@@ -123,7 +123,7 @@ static void status()
                        }
                        
                        lease = db->query(db, "SELECT COUNT(*) FROM leases "
-                                                         "WHERE pool = ? AND release ISNULL",
+                                                         "WHERE pool = ? AND released IS NULL",
                                                          DB_UINT, id, DB_INT);
                        if (lease)
                        {
@@ -224,17 +224,15 @@ static void leases(char *name, char *filter)
        enumerator_t *query;
        chunk_t address_chunk, identity_chunk;
        int identity_type;
-       u_int acquire, release, timeout;
+       u_int acquired, released, timeout;
        host_t *address;
        identification_t *identity;
        bool found = FALSE;
        
        query = db->query(db, "SELECT name, address, identities.type, "
-                                         "identities.data, acquire, release, timeout "
+                                         "identities.data, acquired, released, timeout "
                                          "FROM leases JOIN pools ON leases.pool = pools.id "
-                                         "JOIN identities ON leases.identity = identities.id "
-                                         "WHERE (? or name = ?)",
-                                         DB_INT, name == NULL, DB_TEXT, name,
+                                         "JOIN identities ON leases.identity = identities.id ",
                                          DB_TEXT, DB_BLOB, DB_INT,
                                          DB_BLOB, DB_UINT, DB_UINT, DB_UINT);
        if (!query)
@@ -243,7 +241,7 @@ static void leases(char *name, char *filter)
                exit(-1);
        }
        while (query->enumerate(query, &name, &address_chunk, &identity_type,
-                                                       &identity_chunk, &acquire, &release, &timeout))
+                                                       &identity_chunk, &acquired, &released, &timeout))
        {
                if (!found)
                {
@@ -254,16 +252,16 @@ static void leases(char *name, char *filter)
                address = host_create_from_blob(address_chunk);
                identity = identification_create_from_encoding(identity_type, identity_chunk);
                
-               printf("%-8s %15H  %-32D  %T  ", name, address, identity, &acquire);
-               if (release)
+               printf("%-8s %15H  %-32D  %T  ", name, address, identity, &acquired);
+               if (released)
                {
-                       printf("%T  ", &release);
+                       printf("%T  ", &released);
                }
                else
                {
                        printf("                          ");
                }
-               if (release == 0)
+               if (released == 0)
                {
                        printf("%-7s\n", "online");
                }
@@ -271,7 +269,7 @@ static void leases(char *name, char *filter)
                {
                        printf("%-7s\n", "static");
                }
-               else if (release >= time(NULL) - timeout)
+               else if (released >= time(NULL) - timeout)
                {
                        printf("%-7s\n", "valid");
                }
@@ -310,7 +308,7 @@ static void purge(char *name)
        {
                purged = db->execute(db, NULL,
                                        "DELETE FROM leases WHERE pool = ? "
-                                       "AND release NOTNULL AND release < ?",
+                                       "AND released NOTNULL AND released < ?",
                                        DB_UINT, id, DB_UINT, time(NULL) - timeout);
        }
        query->destroy(query);
@@ -361,7 +359,7 @@ int main(int argc, char *argv[])
        library_init(STRONGSWAN_CONF);
        atexit(library_deinit);
        
-       lib->plugins->load(lib->plugins, IPSEC_PLUGINDIR, "libstrongswan-sqlite");
+       lib->plugins->load(lib->plugins, IPSEC_PLUGINDIR, "libstrongswan-");
        
        uri = lib->settings->get_str(lib->settings, "charon.plugins.sql.database", NULL);
        if (!uri)
index 717d8fe..7c26d12 100644 (file)
@@ -95,20 +95,20 @@ static host_t* get_lease(private_sql_attribute_t *this,
                                                "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.release ISNULL OR p.timeout ISNULL "
-                                               " OR (l.release >= (? - p.timeout))) "
-                                               "ORDER BY l.acquire LIMIT 1", DB_TEXT, name,
+                                               "AND (l.released IS NULL OR p.timeout IS NULL "
+                                               " 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_INT, DB_BLOB);
+                                               DB_UINT, DB_BLOB);
        if (e)
        {
                if (e->enumerate(e, &lease, &address))
                {
                        /* found one, set the lease to active */
                        if (this->db->execute(this->db, NULL,
-                                                 "UPDATE leases SET release = NULL WHERE id = ?",
-                                                 DB_INT, lease) > 0)
+                                                 "UPDATE leases SET released = NULL WHERE id = ?",
+                                                 DB_UINT, lease) > 0)
                        {
                                ip = ip_from_chunk(address);
                                DBG1(DBG_CFG, "reassigning address from valid lease "
@@ -130,7 +130,7 @@ static host_t* create_lease(private_sql_attribute_t *this,
        enumerator_t *e;
        chunk_t address;
        host_t *ip = NULL;
-       int pool, identity = 0;
+       u_int pool, identity = 0, released, timeout;
        bool new = FALSE;
        
        /* we currently do not use database transactions. While this would be 
@@ -143,22 +143,22 @@ static host_t* create_lease(private_sql_attribute_t *this,
        
        /* find an address which has outdated leases only */
        e = this->db->query(this->db,
-                                               "SELECT pool, address FROM leases "
+                                               "SELECT pool, address, released, timeout FROM leases "
                                                "JOIN pools ON leases.pool = pools.id "
                                                "WHERE name = ? "
-                                               "GROUP BY address HAVING release NOTNULL "
-                                               "AND MAX(release) < ? + pools.timeout LIMIT 1",
+                                               "GROUP BY address HAVING released IS NOT NULL "
+                                               "AND MAX(released) < (? + timeout) LIMIT 1",
                                                DB_TEXT, name, DB_UINT, time(NULL),
-                                               DB_INT, DB_BLOB);
+                                               DB_UINT, DB_BLOB, DB_UINT, DB_UINT);
        
-       if (!e || !e->enumerate(e, &pool, &address))
+       if (!e || !e->enumerate(e, &pool, &address, &released, &timeout))
        {
                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_INT, DB_BLOB);
+                               DB_UINT, DB_BLOB);
                if (!e || !e->enumerate(e, &pool, &address))
                {
                        /* pool seems full */
@@ -175,7 +175,7 @@ static host_t* create_lease(private_sql_attribute_t *this,
        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_INT);
+                                               DB_UINT);
        if (!e || !e->enumerate(e, &identity))
        {
                DESTROY_IF(e);
@@ -192,18 +192,18 @@ static host_t* create_lease(private_sql_attribute_t *this,
        if (identity)
        {
                if (this->db->execute(this->db, NULL,
-                                "INSERT INTO leases (pool, address, identity, acquire) "
-                                "VALUES (?, ?, ?, ?)",
-                                DB_INT, pool, DB_BLOB, address, DB_INT, identity,
-                                DB_UINT, time(NULL)) > 0)
+                               "INSERT INTO leases (pool, address, identity, acquired) "
+                               "VALUES (?, ?, ?, ?)",
+                               DB_UINT, pool, DB_BLOB, address, DB_UINT, identity,
+                               DB_UINT, time(NULL)) > 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_INT, pool);
+                                                                 "UPDATE pools SET next = ? WHERE id = ?",
+                                                                 DB_BLOB, address, DB_UINT, pool);
                                DBG1(DBG_CFG, "assigning lease with new address "
                                         "from pool %s", name);
                        }
@@ -242,9 +242,9 @@ static bool release_address(private_sql_attribute_t *this,
                                                        char *name, host_t *address)
 {
        if (this->db->execute(this->db, NULL,
-                       "UPDATE leases SET release = ? WHERE "
+                       "UPDATE leases SET released = ? WHERE "
                        "pool IN (SELECT id FROM pools WHERE name = ?) AND "
-                       "address = ? AND release ISNULL",
+                       "address = ? AND released IS NULL",
                        DB_UINT, time(NULL),
                        DB_TEXT, name, DB_BLOB, address->get_address(address)) > 0)
        {
index 97c304a..760cf1b 100644 (file)
@@ -151,6 +151,7 @@ CREATE TABLE shared_secret_identity (
   PRIMARY KEY (shared_secret, identity)
 );
 
+
 DROP TABLE IF EXISTS pools;
 CREATE TABLE pools (
   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
@@ -158,21 +159,23 @@ CREATE TABLE pools (
   start BLOB NOT NULL,
   end BLOB NOT NULL,
   next BLOB NOT NULL,
-  timeout INTEGER DEFAULT NULL
+  timeout INTEGER DEFAULT NULL,
+  UNIQUE (name)
 );
 DROP INDEX IF EXISTS pools_name;
 CREATE INDEX pools_name ON pools (
   name
 );
 
+
 DROP TABLE IF EXISTS leases;
 CREATE TABLE leases (
   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   pool INTEGER NOT NULL,
   address BLOB NOT NULL,
   identity INTEGER NOT NULL,
-  acquire INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  release INTEGER DEFAULT NULL
+  acquired INTEGER NOT NULL,
+  released INTEGER DEFAULT NULL
 );
 DROP INDEX IF EXISTS leases_pool;
 CREATE INDEX leases_pool ON leases (
@@ -182,11 +185,12 @@ DROP INDEX IF EXISTS leases_identity;
 CREATE INDEX leases_identity ON leases (
   identity
 );
-DROP INDEX IF EXISTS leases_release;
-CREATE INDEX leases_release ON leases (
-  release
+DROP INDEX IF EXISTS leases_released;
+CREATE INDEX leases_released ON leases (
+  released
 );
 
+
 DROP TABLE IF EXISTS ike_sas;
 CREATE TABLE ike_sas (
   local_spi BLOB NOT NULL PRIMARY KEY,