diff options
Diffstat (limited to 'src/share/database/scripts/pgsql/upgrade_004.0_to_005.0.sh.in')
| -rw-r--r-- | src/share/database/scripts/pgsql/upgrade_004.0_to_005.0.sh.in | 172 |
1 files changed, 172 insertions, 0 deletions
diff --git a/src/share/database/scripts/pgsql/upgrade_004.0_to_005.0.sh.in b/src/share/database/scripts/pgsql/upgrade_004.0_to_005.0.sh.in new file mode 100644 index 0000000..62c861d --- /dev/null +++ b/src/share/database/scripts/pgsql/upgrade_004.0_to_005.0.sh.in @@ -0,0 +1,172 @@ +#!/bin/sh + +# Copyright (C) 2018-2022 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=$(pgsql_version "$@") + +if [ "$VERSION" != "4.0" ]; then + printf 'This script upgrades 4.0 to 5.0. ' + printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}" + exit 0 +fi + +psql "$@" >/dev/null <<EOF + +START TRANSACTION; + +-- Add a column holding leases for user context. +ALTER TABLE lease4 ADD COLUMN user_context TEXT; +ALTER TABLE lease6 ADD COLUMN user_context TEXT; + +-- +-- 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,user_context' 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, + user_context 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, + l.user_context + FROM lease4 l + left outer join lease_state s on (l.state = s.state) + ORDER BY l.address; +\$\$ 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,hwaddr,hwtype,hwaddr_source,user_context' 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, + hwaddr text, + hwtype smallint, + hwaddr_source text, + user_context 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, + encode(l.hwaddr,'hex'), + l.hwtype, + h.name, + l.user_context + ) + 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) + ORDER BY l.address; +\$\$ LANGUAGE SQL; +-- + +-- Create logs table (logs table is used by forensic logging hook library) +CREATE TABLE logs ( + timestamp TIMESTAMP WITH TIME ZONE + DEFAULT CURRENT_TIMESTAMP, -- creation timestamp + address VARCHAR(43) NULL, -- address or prefix + log TEXT NOT NULL -- the log itself + ); + +-- Create search indexes +CREATE INDEX timestamp_id ON logs (timestamp); +CREATE INDEX address_id ON logs (address); + +-- Create auth_key in hosts table for storing keys for DHCPv6 reconfigure. +ALTER TABLE hosts ADD COLUMN auth_key VARCHAR(16) DEFAULT NULL; + +-- Convert subnet-id values of 0 to NULL +UPDATE hosts SET dhcp4_subnet_id = NULL WHERE dhcp4_subnet_id = 0; +UPDATE dhcp4_options SET dhcp4_subnet_id = NULL WHERE dhcp4_subnet_id = 0; +UPDATE hosts SET dhcp6_subnet_id = NULL WHERE dhcp6_subnet_id = 0; +UPDATE dhcp6_options SET dhcp6_subnet_id = NULL WHERE dhcp6_subnet_id = 0; + +-- Set 5.0 schema version. +UPDATE schema_version + SET version = '5', minor = '0'; + +-- Schema 5.0 specification ends here. + +-- Commit the script transaction +COMMIT; + +EOF |