1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
# Copyright (C) 2012-2021 Internet Systems Consortium, Inc. ("ISC")
#
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
# This is the Kea schema 1.0 specification for MySQL.
# Note: this is outdated version on purpose and it used to test upgrade
# process. Do not update this file to 2.0 or any later.
#
# 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 <user> -p <password> <database> < 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 12 of RFC 8415
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
) ENGINE = INNODB;
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/share/database/scripts/mysql/dhcpdb_create.mysql,
# which defines the schema for the unit tests.
CREATE TABLE schema_version (
version INT PRIMARY KEY NOT NULL, # Major version number
minor INT # Minor version number
) ENGINE = INNODB;
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 portable 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.
|
