BIND 10 trac2142, updated. 5ad130df69219a00a4ab28d5028e9fc07282b9c2 [2142] Removed redundant tables and columns. Added more comments.

[BIND 10 source code commits]

The branch, trac2142 has been updated
       via  5ad130df69219a00a4ab28d5028e9fc07282b9c2 (commit)
      from  5d2d668266686751c4a56470458c0c02970e2dfe (commit)

Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.

- Log -----------------------------------------------------------------
commit 5ad130df69219a00a4ab28d5028e9fc07282b9c2
Author: Stephen Morris <stephen at isc.org>
Date:   Fri Oct 12 15:25:55 2012 +0100

    [2142] Removed redundant tables and columns.  Added more comments.

-----------------------------------------------------------------------

Summary of changes:
 src/lib/dhcp/dhcpdb_create.mysql |   64 ++++++++++++++++++++++++--------------
 1 file changed, 41 insertions(+), 23 deletions(-)

-----------------------------------------------------------------------
diff --git a/src/lib/dhcp/dhcpdb_create.mysql b/src/lib/dhcp/dhcpdb_create.mysql
index 4ee40ae..6f95f94 100644
--- a/src/lib/dhcp/dhcpdb_create.mysql
+++ b/src/lib/dhcp/dhcpdb_create.mysql
@@ -13,15 +13,14 @@
 # NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION
 # WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
 
+# Holds the IPv4 leases.
 CREATE TABLE lease4 (
     address INT UNSIGNED UNIQUE NOT NULL,   # IPv4 address
     hwaddr VARCHAR(20),                     # Hardware address
     client_id VARCHAR(128),                 # Client ID
-    valid_lft INT,                          # Valid lifetime (seconds)
-    recycle_time INT DEFAULT 0,             # Recycle time (seconds)
-    cllt TIMESTAMP,                         # Client last comm time
-    pool_id INT,                            # Pool identification
-    options TEXT                            # List of options
+    lease_time INT UNSIGNED,                # Length of the lease (seconds)
+    expire TIMESTAMP,                       # Expiration time of the lease
+    subnet_id INT UNSIGNED                  # Subnet identification
     ) ENGINE = INNODB;
 
 # Holds the IPv6 leases
@@ -29,28 +28,47 @@ CREATE TABLE lease6 (
     address BINARY(16) UNIQUE NOT NULL,     # IPv6 address
     hwaddr VARCHAR(20),                     # Hardware address
     client_id VARCHAR(128),                 # Client ID
-    fixed BOOL DEFAULT FALSE,               # Is this lease fixed?
-    valid_lft INT,                          # Valid lifetime (seconds)
-    recycle_time INT DEFAULT 0,             # Recycle time (seconds)
-    cllt TIMESTAMP,                         # Client last comm time
-    pool_id INT,                            # Pool identification
+    lifetime INT UNSIGNED,                  # Length of the lease (seconds)
+    expire TIMESTAMP,                       # Expiration time of the lease
+    subnet_id INT UNSIGNED,                 # Subnet identification
+    lease_type TINYINT,                     # Lease type
     iaid INT UNSIGNED,                      # IA ID
-    prefix_len TINYINT UNSIGNED,            # For IA_PD only
-    options TEXT                            # Textual form of options
+    prefix_len TINYINT UNSIGNED             # For IA_PD only
     ) ENGINE = INNODB;
 
-# The host table
-CREATE TABLE host (
-    hostname VARCHAR(255) UNIQUE,           # Name of the host
-    address4 INT UNSIGNED DEFAULT NULL,     # IPv4 address
-    address6 BINARY(16) DEFAULT NULL,       # IPv6 address
-    options TEXT                            # Textual form of the options
-    ) ENGINE = INNODB;
+# ... and a definition of leasei6 types.  This table is a convenience for
+# users of the database - if they want to view the lease table and use the
+# type names, they can join this table with the lease6 table
+CREATE TABLE lease6_types (
+    lease_type TINYINT UNIQUE NOT NULL,     # Lease type code
+    name VARCHAR(5)                         # Name of the lease type
+    );
+INSERT INTO lease6_types VALUES (0, "IA_NA");   # Non-temporary v6 addresses
+INSERT INTO lease6_types VALUES (1, "IA_TA");   # Temporary v6 addresses
+INSERT INTO lease6_types VALUES (2, "IA_PD");   # Prefix delegations
 
-# Finally, the version of the schema.  We start at 0.0 during development.
-# This table is only modified during schema upgrades.
+# Finally, the version of the schema.  We start at 0.1 during development.
+# This table is only modified during schema upgrades.  For historical reasons
+# (related to the names of the columns in the BIND 10 DNS database file), the
+# first column is called "version" and not "major".
 CREATE TABLE schema_version (
-    major INT NOT NULL,                     # Major version number
+    version INT NOT NULL,                   # Major version number
     minor INT NOT NULL                      # Minor version number
     );
-INSERT INTO schema_version VALUES (0, 0);
+INSERT INTO schema_version VALUES (0, 1);
+
+# It is likely that additional indexes will be needed.  However, the
+# increase in lookup performance from these will come at the expense
+# of a decrease in performance during insert operations due to the need
+# to update the indexes.  For this reason, the need for additional indexes
+# will be determined by experiment during performance tests.
+#
+# The most likely additional indexes will cover the following columns:
+#
+# expire
+# To speed up the deletion of expired leases from the database.
+#
+# hwaddr and client_id
+# For lease stability: if a client requests a new lease, try to find an
+# existing or recently expired lease for it so that it can keep using the
+# same IP address.