1 files changed, 173 insertions, 0 deletions
diff --git a/src/share/database/scripts/mysql/upgrade_003.0_to_004.0.sh.in b/src/share/database/scripts/mysql/upgrade_003.0_to_004.0.sh.in
new file mode 100644
index 0000000..3affd3a
--- /dev/null
+++ b/src/share/database/scripts/mysql/upgrade_003.0_to_004.0.sh.in
@@ -0,0 +1,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