# Copyright (C) 2012-2013 Internet Systems Consortium. # # Permission to use, copy, modify, and distribute this software for any # purpose with or without fee is hereby granted, provided that the above # copyright notice and this permission notice appear in all copies. # # THE SOFTWARE IS PROVIDED "AS IS" AND INTERNET SYSTEMS CONSORTIUM # DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL # IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL # INTERNET SYSTEMS CONSORTIUM BE LIABLE FOR ANY SPECIAL, DIRECT, # INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING # FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, # NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION # WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. # This is the BIND 10 DHCP schema specification for MySQL. # # The schema is reasonably portable (with the exception of the engine # specification, which is MySQL-specific). Minor changes might be needed for # other databases. # To create the schema, either type the command: # # mysql -u -p < dhcpdb_create.mysql # # ... at the command prompt, or log in to the MySQL database and at the "mysql>" # prompt, issue the command: # # source dhcpdb_create.mysql # Holds the IPv4 leases. CREATE TABLE lease4 ( address INT UNSIGNED PRIMARY KEY NOT NULL, # IPv4 address hwaddr VARBINARY(20), # Hardware address client_id VARBINARY(128), # Client ID valid_lifetime INT UNSIGNED, # Length of the lease (seconds) expire TIMESTAMP, # Expiration time of the lease subnet_id INT UNSIGNED, # Subnet identification fqdn_fwd BOOL, # Has forward DNS update been performed by a server fqdn_rev BOOL, # Has reverse DNS update been performed by a server hostname VARCHAR(255) # The FQDN of the client ) ENGINE = INNODB; # Create search indexes for lease4 table # index by hwaddr and subnet_id CREATE INDEX lease4_by_hwaddr_subnet_id ON lease4 (hwaddr, subnet_id); # index by client_id and subnet_id CREATE INDEX lease4_by_client_id_subnet_id ON lease4 (client_id, subnet_id); # Holds the IPv6 leases. # N.B. The use of a VARCHAR for the address is temporary for development: # it will eventually be replaced by BINARY(16). CREATE TABLE lease6 ( address VARCHAR(39) PRIMARY KEY NOT NULL, # IPv6 address duid VARBINARY(128), # DUID valid_lifetime INT UNSIGNED, # Length of the lease (seconds) expire TIMESTAMP, # Expiration time of the lease subnet_id INT UNSIGNED, # Subnet identification pref_lifetime INT UNSIGNED, # Preferred lifetime lease_type TINYINT, # Lease type (see lease6_types # table for possible values) iaid INT UNSIGNED, # See Section 10 of RFC 3315 prefix_len TINYINT UNSIGNED, # For IA_PD only fqdn_fwd BOOL, # Has forward DNS update been performed by a server fqdn_rev BOOL, # Has reverse DNS update been performed by a server hostname VARCHAR(255) # The FQDN of the client ) ENGINE = INNODB; # Create search indexes for lease4 table # index by iaid, subnet_id, and duid CREATE INDEX lease6_by_iaid_subnet_id_duid ON lease6 (iaid, subnet_id, duid); # ... and a definition of lease6 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. # Make sure those values match Lease6::LeaseType enum (see src/bin/dhcpsrv/ # lease_mgr.h) CREATE TABLE lease6_types ( lease_type TINYINT PRIMARY KEY NOT NULL, # Lease type code. name VARCHAR(5) # Name of the lease type ); START TRANSACTION; 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 COMMIT; # 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". # # NOTE: this MUST be kept in step with src/lib/dhcpsrv/tests/schema_copy.h, # which defines the schema for the unit tests. If you are updating # the version number, the schema has changed: please ensure that # schema_copy.h has been updated as well. CREATE TABLE schema_version ( version INT PRIMARY KEY NOT NULL, # Major version number minor INT # Minor version number ); START TRANSACTION; INSERT INTO schema_version VALUES (1, 0); COMMIT; # Notes: # # Indexes # ======= # 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. # # Field Sizes # =========== # If any of the VARxxx field sizes are altered, the lengths in the MySQL # backend source file (mysql_lease_mgr.cc) must be correspondingly changed. # # Portability # =========== # The "ENGINE = INNODB" on some tables is not portablea to another database # and will need to be removed. # # Some columns contain binary data so are stored as VARBINARY instead of # VARCHAR. This may be non-portable between databases: in this case, the # definition should be changed to VARCHAR.