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
174
175
176
177
178
179
180
181
|
#!/bin/sh
# Copyright (C) 2016-2023 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 test -f "@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=$(pgsql_version "$@")
if [ "$VERSION" != "1.0" ]; then
printf 'This script upgrades 1.0 to 2.0. '
printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}"
exit 0
fi
psql "$@" >/dev/null <<EOF
-- This line starts the schema upgrade to version 2.0.
-- Add state column to the lease4 table.
ALTER TABLE lease4
ADD COLUMN state INT8 DEFAULT 0;
-- Add state column to the lease6 table.
ALTER TABLE lease6
ADD COLUMN state INT8 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 lease_state (
state INT8 PRIMARY KEY NOT NULL,
name VARCHAR(64) NOT NULL);
-- Insert currently defined state names.
START TRANSACTION;
INSERT INTO lease_state VALUES (0, 'default');
INSERT INTO lease_state VALUES (1, 'declined');
INSERT INTO lease_state VALUES (2, 'expired-reclaimed');
COMMIT;
-- 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);
--
-- FUNCTION that returns a result set containing the column names for lease4 dumps
DROP FUNCTION IF EXISTS lease4DumpHeader();
CREATE FUNCTION lease4DumpHeader() RETURNS text AS \$\$
select cast('address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state' as text) as result;
\$\$ LANGUAGE SQL;
--
--
-- FUNCTION that returns a result set containing the data for lease4 dumps
DROP FUNCTION IF EXISTS lease4DumpData();
CREATE FUNCTION lease4DumpData() RETURNS
table (address inet,
hwaddr text,
client_id text,
valid_lifetime bigint,
expire timestamp with time zone,
subnet_id bigint,
fqdn_fwd int,
fqdn_rev int,
hostname text,
state text
) as \$\$
SELECT ('0.0.0.0'::inet + l.address),
encode(l.hwaddr,'hex'),
encode(l.client_id,'hex'),
l.valid_lifetime,
l.expire,
l.subnet_id,
l.fqdn_fwd::int,
l.fqdn_rev::int,
l.hostname,
s.name
FROM lease4 l
left outer join lease_state s on (l.state = s.state);
\$\$ LANGUAGE SQL;
--
--
-- FUNCTION that returns a result set containing the column names for lease6 dumps
DROP FUNCTION IF EXISTS lease6DumpHeader();
CREATE FUNCTION lease6DumpHeader() RETURNS text AS \$\$
select cast('address,duid,valid_lifetime,expire,subnet_id,pref_lifetime,lease_type,iaid,prefix_len,fqdn_fwd,fqdn_rev,hostname,state' as text) as result;
\$\$ LANGUAGE SQL;
--
--
-- FUNCTION that returns a result set containing the data for lease6 dumps
DROP FUNCTION IF EXISTS lease6DumpData();
CREATE FUNCTION lease6DumpData() RETURNS
TABLE (
address text,
duid text,
valid_lifetime bigint,
expire timestamp with time zone,
subnet_id bigint,
pref_lifetime bigint,
name text,
iaid integer,
prefix_len smallint,
fqdn_fwd int,
fqdn_rev int,
hostname text,
state text
) AS \$\$
SELECT (l.address,
encode(l.duid,'hex'),
l.valid_lifetime,
l.expire,
l.subnet_id,
l.pref_lifetime,
t.name,
l.iaid,
l.prefix_len,
l.fqdn_fwd::int,
l.fqdn_rev::int,
l.hostname,
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);
\$\$ LANGUAGE SQL;
--
-- Update the schema version number.
START TRANSACTION;
UPDATE schema_version
SET version = '2', minor = '0';
-- This line concludes the schema upgrade to version 2.0.
-- Commit the script transaction.
COMMIT;
EOF
|
