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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
#!/bin/sh
# Copyright (C) 2016-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/.
# shellcheck disable=SC1091
# SC1091: Not following: ... was not specified as input (see shellcheck -x).
# Exit with error if commands exit with non-zero and if undefined variables are
# used.
set -eu
# shellcheck disable=SC2034
# SC2034: ... appears unused. Verify use (or export if used externally).
prefix="@prefix@"
# Include utilities. Use installed version if available and
# use build version if it isn't.
if [ -e @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh ]; then
. "@datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh"
else
. "@abs_top_builddir@/src/bin/admin/admin-utils.sh"
fi
VERSION=$(mysql_version "$@")
if [ "$VERSION" != "3.0" ]; then
printf 'This script upgrades 3.0 to 4.0. '
printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}"
exit 0
fi
mysql "$@" <<EOF
# Add state column to the lease4 table.
ALTER TABLE lease4
ADD COLUMN state INT UNSIGNED DEFAULT 0;
# Add state column to the lease6 table.
ALTER TABLE lease6
ADD COLUMN state INT UNSIGNED DEFAULT 0;
# Create indexes for querying leases in a given state and segregated
# by the expiration time. One of the applications is to retrieve all
# expired leases. However, these indexes can be also used to retrieve
# leases in a given state regardless of the expiration time.
CREATE INDEX lease4_by_state_expire ON lease4 (state ASC, expire ASC);
CREATE INDEX lease6_by_state_expire ON lease6 (state ASC, expire ASC);
# Create table holding mapping of the lease states to their names.
# This is not used in queries from the DHCP server but rather in
# direct queries from the lease database management tools.
CREATE TABLE IF NOT EXISTS lease_state (
state INT UNSIGNED PRIMARY KEY NOT NULL,
name VARCHAR(64) NOT NULL
) ENGINE=INNODB;
# Insert currently defined state names.
INSERT INTO lease_state VALUES (0, 'default');
INSERT INTO lease_state VALUES (1, 'declined');
INSERT INTO lease_state VALUES (2, 'expired-reclaimed');
# Add a constraint that any state value added to the lease4 must
# map to a value in the lease_state table.
ALTER TABLE lease4
ADD CONSTRAINT fk_lease4_state FOREIGN KEY (state)
REFERENCES lease_state (state);
# Add a constraint that any state value added to the lease6 must
# map to a value in the lease_state table.
ALTER TABLE lease6
ADD CONSTRAINT fk_lease6_state FOREIGN KEY (state)
REFERENCES lease_state (state);
# Add a constraint that lease type in the lease6 table must map
# to a lease type defined in the lease6_types table.
ALTER TABLE lease6
ADD CONSTRAINT fk_lease6_type FOREIGN KEY (lease_type)
REFERENCES lease6_types (lease_type);
# Modify the name of one of the HW address sources, and add a new one.
UPDATE lease_hwaddr_source
SET name = 'HWADDR_SOURCE_DOCSIS_CMTS'
WHERE hwaddr_source = 64;
INSERT INTO lease_hwaddr_source VALUES (128, 'HWADDR_SOURCE_DOCSIS_MODEM');
# Add UNSIGNED to match with the lease6.
ALTER TABLE lease_hwaddr_source
MODIFY COLUMN hwaddr_source INT UNSIGNED NOT NULL DEFAULT 0;
# Add a constraint that non-null hwaddr_source in the lease6 table
# must map to an entry in the lease_hwaddr_source.
ALTER TABLE lease6
ADD CONSTRAINT fk_lease6_hwaddr_source FOREIGN KEY (hwaddr_source)
REFERENCES lease_hwaddr_source (hwaddr_source);
# FUNCTION that returns a result set containing the column names for lease4 dumps
DROP PROCEDURE IF EXISTS lease4DumpHeader;
DELIMITER $$
CREATE PROCEDURE lease4DumpHeader()
BEGIN
SELECT 'address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state';
END $$
DELIMITER ;
# FUNCTION that returns a result set containing the data for lease4 dumps
DROP PROCEDURE IF EXISTS lease4DumpData;
DELIMITER $$
CREATE PROCEDURE lease4DumpData()
BEGIN
SELECT
INET_NTOA(l.address),
IFNULL(HEX(l.hwaddr), ''),
IFNULL(HEX(l.client_id), ''),
l.valid_lifetime,
l.expire,
l.subnet_id,
l.fqdn_fwd,
l.fqdn_rev,
l.hostname,
s.name
from
lease4 l
LEFT OUTER JOIN lease_state s on (l.state = s.state);
END $$
DELIMITER ;
# FUNCTION that returns a result set containing the column names for lease6 dumps
DROP PROCEDURE IF EXISTS lease6DumpHeader;
DELIMITER $$
CREATE PROCEDURE lease6DumpHeader()
BEGIN
SELECT 'address,duid,valid_lifetime,expire,subnet_id,pref_lifetime,lease_type,iaid,prefix_len,fqdn_fwd,fqdn_rev,hostname,hwaddr,hwtype,hwaddr_source,state';
END $$
DELIMITER ;
# FUNCTION that returns a result set containing the data for lease6 dumps
DROP PROCEDURE IF EXISTS lease6DumpData;
DELIMITER $$
CREATE PROCEDURE lease6DumpData()
BEGIN
SELECT
l.address,
IFNULL(HEX(l.duid), ''),
l.valid_lifetime,
l.expire,
l.subnet_id,
l.pref_lifetime,
IFNULL(t.name, ''),
l.iaid,
l.prefix_len,
l.fqdn_fwd,
l.fqdn_rev,
l.hostname,
IFNULL(HEX(l.hwaddr), ''),
IFNULL(l.hwtype, ''),
IFNULL(h.name, ''),
IFNULL(s.name, '')
FROM lease6 l
left outer join lease6_types t on (l.lease_type = t.lease_type)
left outer join lease_state s on (l.state = s.state)
left outer join lease_hwaddr_source h on (l.hwaddr_source = h.hwaddr_source);
END $$
DELIMITER ;
# Update the schema version number
UPDATE schema_version
SET version = '4', minor = '0';
# This line concludes database upgrade to version 4.0.
EOF
|
