diff options
Diffstat (limited to 'src/share/database/scripts/pgsql/upgrade_007_to_008.sh.in')
| -rw-r--r-- | src/share/database/scripts/pgsql/upgrade_007_to_008.sh.in | 514 |
1 files changed, 514 insertions, 0 deletions
diff --git a/src/share/database/scripts/pgsql/upgrade_007_to_008.sh.in b/src/share/database/scripts/pgsql/upgrade_007_to_008.sh.in new file mode 100644 index 0000000..fe18652 --- /dev/null +++ b/src/share/database/scripts/pgsql/upgrade_007_to_008.sh.in @@ -0,0 +1,514 @@ +#!/bin/sh + +# Copyright (C) 2021-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" != "7.0" ]; then + printf 'This script upgrades 7.0 to 8.0. ' + printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}" + exit 0 +fi + +psql "$@" >/dev/null <<EOF +START TRANSACTION; + +-- This starts schema update to 8.0. It adds a few missing elements for CB and +-- functions for kea-admin's lease-dump and lease-upload commands. + +-- ----------------------------------------------------------------------- +-- Extend the table holding DHCPv4 option definitions with a nullable +-- column matching option defintions with client classes. +-- ----------------------------------------------------------------------- +ALTER TABLE dhcp4_option_def + ADD COLUMN class_id BIGINT NULL DEFAULT NULL; + +ALTER TABLE dhcp4_option_def + ADD CONSTRAINT fk_dhcp4_option_def_client_class_id + FOREIGN KEY (class_id) + REFERENCES dhcp4_client_class (id) + ON DELETE CASCADE + ON UPDATE CASCADE; + +-- ----------------------------------------------------------------------- +-- Extend the table holding DHCPv6 option definitions with a nullable +-- column matching option defintions with client classes. +-- ----------------------------------------------------------------------- +ALTER TABLE dhcp6_option_def + ADD COLUMN class_id BIGINT NULL DEFAULT NULL; + +ALTER TABLE dhcp6_option_def + ADD CONSTRAINT fk_dhcp6_option_def_client_class_id + FOREIGN KEY (class_id) + REFERENCES dhcp6_client_class (id) + ON DELETE CASCADE + ON UPDATE CASCADE; + +-- ----------------------------------------------------------------------- +-- Add missing preferred_lifetime columns to dhcp6_client_class table. +-- ----------------------------------------------------------------------- +ALTER TABLE dhcp6_client_class + ADD COLUMN preferred_lifetime BIGINT DEFAULT NULL, + ADD COLUMN min_preferred_lifetime BIGINT DEFAULT NULL, + ADD COLUMN max_preferred_lifetime BIGINT DEFAULT NULL; + +-- ----------------------------------------------------------------------- +-- Add option scopes +-- ----------------------------------------------------------------------- +-- Add scope for shared network specific options. +INSERT INTO dhcp_option_scope (scope_id, scope_name) + VALUES(4, 'shared-network'); + +-- Add scope for pool specific options. +INSERT INTO dhcp_option_scope (scope_id, scope_name) + VALUES(5, 'pool'); + +-- Add scope for PD pool specific options. +INSERT INTO dhcp_option_scope (scope_id, scope_name) + VALUES(6, 'pd-pool'); + + +-- Drop the existing function, createOptionAuditDHCP6 so we can replace it +-- with one that has slightly different arguments. +DROP FUNCTION IF EXISTS createOptionAuditDHCP6(modification_type VARCHAR(32), + scope_id SMALLINT, option_id INT, subnet_id BIGINT, + host_id INT, network_name VARCHAR(128), + pool_id BIGINT, pd_pool_id BIGINT, + modification_ts TIMESTAMP WITH TIME ZONE); + +-- ----------------------------------------------------- +-- +-- New version of the createOptionAuditDHCP6 stored +-- procedure which updates modification timestamp of +-- a parent object when an option is modified. +-- +-- The following parameters are passed to the procedure: +-- - modification_type: "create", "update" or "delete" +-- - scope_id: identifier of the option scope, e.g. +-- global, subnet specific etc. See dhcp_option_scope +-- for specific values. +-- - option_id: identifier of the option. +-- - subnet_id: identifier of the subnet if the option +-- belongs to the subnet. +-- - host_id: identifier of the host if the option +-- - belongs to the host. +-- - network_name: shared network name if the option +-- belongs to the shared network. +-- - pool_id: identifier of the pool if the option +-- belongs to the pool. +-- - pd_pool_id: identifier of the pool if the option +-- belongs to the pd pool. +-- - modification_ts: modification timestamp of the +-- option. +-- Some arguments are prefixed with "p_" to avoid ambiguity +-- with column names in SQL statements. PostgreSQL does not +-- allow table aliases to be used with column names in update +-- set expressions. +-- ----------------------------------------------------- +CREATE OR REPLACE FUNCTION createOptionAuditDHCP6(modification_type VARCHAR(32), + scope_id SMALLINT, + option_id INT, + p_subnet_id BIGINT, + host_id INT, + network_name VARCHAR(128), + pool_id BIGINT, + pd_pool_id BIGINT, + p_modification_ts TIMESTAMP WITH TIME ZONE) +RETURNS VOID +LANGUAGE plpgsql +AS \$\$ +DECLARE + -- These variables will hold shared network id and subnet id that + -- we will select. + snid VARCHAR(128); + sid BIGINT; + cascade_transaction BOOLEAN := false; + +BEGIN + -- Cascade transaction flag is set to true to prevent creation of + -- the audit entries for the options when the options are + -- created as part of the parent object creation or update. + -- For example: when the option is added as part of the subnet + -- addition, the cascade transaction flag is equal to true. If + -- the option is added into the existing subnet the cascade + -- transaction is equal to false. Note that depending on the option + -- scope the audit entry will contain the object_type value + -- of the parent object to cause the server to replace the + -- entire subnet. The only case when the object_type will be + -- set to 'dhcp6_options' is when a global option is added. + -- Global options do not have the owner. + cascade_transaction := get_session_boolean('kea.cascade_transaction'); + IF cascade_transaction = false THEN + -- todo: host manager hasn't been updated to use audit + -- mechanisms so ignore host specific options for now. + IF scope_id = 0 THEN + -- If a global option is added or modified, create audit + -- entry for the 'dhcp6_options' table. + PERFORM createAuditEntryDHCP6('dhcp6_options', option_id, modification_type); + ELSEIF scope_id = 1 THEN + -- If subnet specific option is added or modified, update + -- the modification timestamp of this subnet to allow the + -- servers to refresh the subnet information. This will + -- also result in creating an audit entry for this subnet. + UPDATE dhcp6_subnet SET modification_ts = p_modification_ts + WHERE subnet_id = p_subnet_id; + ELSEIF scope_id = 4 THEN + -- If shared network specific option is added or modified, + -- update the modification timestamp of this shared network + -- to allow the servers to refresh the shared network + -- information. This will also result in creating an + -- audit entry for this shared network. + SELECT id INTO snid FROM dhcp6_shared_network WHERE name = network_name LIMIT 1; + UPDATE dhcp6_shared_network SET modification_ts = p_modification_ts + WHERE id = snid; + ELSEIF scope_id = 5 THEN + -- If pool specific option is added or modified, update + -- the modification timestamp of the owning subnet. + SELECT dhcp6_pool.subnet_id INTO sid FROM dhcp6_pool WHERE id = pool_id; + UPDATE dhcp6_subnet SET modification_ts = p_modification_ts + WHERE subnet_id = sid; + ELSEIF scope_id = 6 THEN + -- If pd pool specific option is added or modified, create + -- audit entry for the subnet which this pool belongs to. + SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pool_id; + UPDATE dhcp6_subnet SET modification_ts = p_modification_ts + WHERE subnet_id = sid; + END IF; + END IF; + RETURN; +END;\$\$; + +-- Fix mangled constraints on dhcp4_subnet_server table. +ALTER TABLE dhcp4_subnet_server + DROP CONSTRAINT fk_dhcp6_subnet_server_server_id, + ADD CONSTRAINT fk_dhcp4_subnet_server_server_id + FOREIGN KEY (server_id) REFERENCES dhcp4_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION, + DROP CONSTRAINT fk_dhcp6_subnet_server_subnet_id, + ADD CONSTRAINT fk_dhcp4_subnet_server_subnet_id + FOREIGN KEY (subnet_id) REFERENCES dhcp4_subnet (subnet_id) ON DELETE CASCADE ON UPDATE NO ACTION; + +-- Add missing foreign key indexes. PostgreSQL does not automatically create indexes for +-- foreign key constraints. These have been added using the basic guideline: +-- +-- If the constraint does not reference a static table (e.g. parameter_data_type), +-- and the referencing column is not the primary key or the first +-- column in the primary key, and does not already have an index, then an index +-- should be added to the table for the referencing column. +-- +-- dhcp6_global_parameter_server +CREATE INDEX fk_dhcp6_global_parameter_server_server_id ON dhcp6_global_parameter_server (server_id); + +-- dhcp6_options +-- Missing foreign key constraint and indexes +ALTER TABLE dhcp6_options ADD CONSTRAINT fk_dhcp6_options_subnet + FOREIGN KEY (dhcp6_subnet_id) + REFERENCES dhcp6_subnet(subnet_id) + ON DELETE CASCADE ON UPDATE CASCADE; + +CREATE INDEX fk_dhcp6_options_subnet ON dhcp6_options (dhcp6_subnet_id); +CREATE INDEX fk_dhcp6_options_pd_pool ON dhcp6_options (pd_pool_id); +CREATE INDEX fk_dhcp6_options_pool ON dhcp6_options (pool_id); +CREATE INDEX fk_dhcp6_options_shared_network ON dhcp6_options (shared_network_name); + +-- dhcp6_option_def_server +-- Missing foreign key constraints and index +ALTER TABLE dhcp6_option_def_server + ADD CONSTRAINT fk_dhcp6_option_def_server_option_def_id FOREIGN KEY (option_def_id) + REFERENCES dhcp6_option_def (id) ON DELETE CASCADE ON UPDATE NO ACTION, + ADD CONSTRAINT fk_dhcp6_option_def_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION; + +CREATE INDEX fk_dhcp6_option_def_server_server_id ON dhcp6_option_def_server (server_id); + +-- dhcp6_option_def +CREATE INDEX fk_dhcp6_option_def_client_class_id ON dhcp6_option_def (class_id); + +-- dhcp4_global_parameter_server +CREATE INDEX fk_dhcp4_global_parameter_server_server_id ON dhcp4_global_parameter_server (server_id); + +-- dhcp4_options +-- Missing foreign key constraint and indexes +ALTER TABLE dhcp4_options ADD CONSTRAINT fk_dhcp4_options_subnet + FOREIGN KEY (dhcp4_subnet_id) + REFERENCES dhcp4_subnet(subnet_id) + ON DELETE CASCADE ON UPDATE CASCADE; + +CREATE INDEX fk_dhcp4_options_subnet ON dhcp4_options (dhcp4_subnet_id); +CREATE INDEX fk_dhcp4_options_pool ON dhcp4_options (pool_id); +CREATE INDEX fk_dhcp4_options_shared_network ON dhcp4_options (shared_network_name); + +-- dhcp4_option_def_server +-- Missing foreign key constraints and index +ALTER TABLE dhcp4_option_def_server + ADD CONSTRAINT fk_dhcp4_option_def_server_option_def_id FOREIGN KEY (option_def_id) + REFERENCES dhcp4_option_def (id) ON DELETE CASCADE ON UPDATE NO ACTION, + ADD CONSTRAINT fk_dhcp4_option_def_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION; + +CREATE INDEX fk_dhcp4_option_def_server_server_id ON dhcp4_option_def_server (server_id); + +-- dhcp4_option_def +CREATE INDEX fk_dhcp4_option_def_client_class_id ON dhcp4_option_def (class_id); + +-- Create a function that separates groups of two hexadecimals +-- with colons. +CREATE OR REPLACE FUNCTION colonSeparatedHex(hex TEXT) +RETURNS TEXT +AS \$\$ +DECLARE + i INT := 3; + length INT := LENGTH(hex); + output TEXT; +BEGIN + -- Add a leading zero if the first octet has a single hexadecimal character. + IF MOD(length, 2) = 1 THEN + hex := CONCAT('0', hex); + length := length + 1; + END IF; + + -- Start with the first octet. + output := SUBSTR(hex, 1, 2); + + -- Add one octet at a time and a leading colon with each. + WHILE i < length LOOP + output := CONCAT(output, ':', SUBSTR(hex, i, 2)); + i := i + 2; + END LOOP; + + -- Memfile uses lowercase hexadecimals. + output := LOWER(output); + + RETURN output; +END +\$\$ LANGUAGE plpgsql; + +-- Modify the function to output a memfile-ready CSV file. +-- Some columns that are SMALLINT in the lease4 table have their type promoted +-- to INT in the declaration of this function for backwards compatibility with +-- PostgreSQL versions. +DROP FUNCTION IF EXISTS lease4DumpData(); +CREATE OR REPLACE FUNCTION lease4DumpData() +RETURNS TABLE ( + address INET, + hwaddr VARCHAR, + client_id VARCHAR, + valid_lifetime BIGINT, + expire BIGINT, + subnet_id BIGINT, + fqdn_fwd INT, + fqdn_rev INT, + hostname VARCHAR, + state INT8, + user_context VARCHAR +) AS \$\$ + SELECT + ('0.0.0.0'::inet + address), + colonSeparatedHex(encode(hwaddr, 'hex')), + colonSeparatedHex(encode(client_id, 'hex')), + valid_lifetime, + extract(epoch from expire)::bigint, + subnet_id, + fqdn_fwd::int, + fqdn_rev::int, + replace(hostname, ',', ','), + state, + replace(user_context, ',', ',') + FROM lease4 + ORDER BY address; +\$\$ LANGUAGE SQL; + +-- hwtype and hwaddr_source need to be last to match memfile format. +DROP FUNCTION IF EXISTS lease6DumpHeader(); +CREATE OR REPLACE 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,hwaddr,state,user_context,hwtype,hwaddr_source' AS TEXT) AS result; +\$\$ LANGUAGE SQL; + +-- Modify the function to output a memfile-ready CSV file. +-- Some columns that are SMALLINT in the lease6 table have their type promoted +-- to INT in the declaration of this function for backwards compatibility with +-- PostgreSQL versions. +DROP FUNCTION IF EXISTS lease6DumpData(); +CREATE OR REPLACE FUNCTION lease6DumpData() +RETURNS TABLE ( + address VARCHAR, + duid VARCHAR, + valid_lifetime BIGINT, + expire BIGINT, + subnet_id BIGINT, + pref_lifetime BIGINT, + lease_type SMALLINT, + iaid INT, + prefix_len SMALLINT, + fqdn_fwd INT, + fqdn_rev INT, + hostname VARCHAR, + hwaddr VARCHAR, + state INT8, + user_context VARCHAR, + hwtype SMALLINT, + hwaddr_source SMALLINT +) AS \$\$ + SELECT + address, + colonSeparatedHex(encode(duid, 'hex')), + valid_lifetime, + extract(epoch from expire)::bigint, + subnet_id, + pref_lifetime, + lease_type, + iaid, + prefix_len, + fqdn_fwd::int, + fqdn_rev::int, + replace(hostname, ',', ','), + colonSeparatedHex(encode(hwaddr, 'hex')), + state, + replace(user_context, ',', ','), + hwtype, + hwaddr_source + FROM lease6 + ORDER BY address; +\$\$ LANGUAGE SQL; + +-- Create a procedure that inserts a v4 lease from memfile data. +-- Some columns that are SMALLINT in the lease4 table have their type promoted +-- to INT in the declaration of this function for backwards compatibility with +-- PostgreSQL versions. +CREATE OR REPLACE FUNCTION lease4Upload( + IN address VARCHAR, + IN hwaddr VARCHAR, + IN client_id VARCHAR, + IN valid_lifetime BIGINT, + IN expire BIGINT, + IN subnet_id BIGINT, + IN fqdn_fwd INT, + IN fqdn_rev INT, + IN hostname VARCHAR, + IN state INT8, + IN user_context VARCHAR +) RETURNS VOID AS \$\$ +BEGIN + INSERT INTO lease4 ( + address, + hwaddr, + client_id, + valid_lifetime, + expire, + subnet_id, + fqdn_fwd, + fqdn_rev, + hostname, + state, + user_context + ) VALUES ( + address::inet - '0.0.0.0'::inet, + decode(replace(hwaddr, ':', ''), 'hex'), + decode(replace(client_id, ':', ''), 'hex'), + valid_lifetime, + to_timestamp(expire), + subnet_id, + fqdn_fwd::int::boolean, + fqdn_rev::int::boolean, + replace(hostname, ',', ','), + state, + replace(user_context, ',', ',') + ); +END +\$\$ LANGUAGE plpgsql; + +-- Create a procedure that inserts a v6 lease from memfile data. +-- Some columns that are SMALLINT in the lease6 table have their type promoted +-- to INT in the declaration of this function for backwards compatibility with +-- PostgreSQL versions. +CREATE OR REPLACE FUNCTION lease6Upload( + IN address VARCHAR, + IN duid VARCHAR, + IN valid_lifetime BIGINT, + IN expire BIGINT, + IN subnet_id BIGINT, + IN pref_lifetime BIGINT, + IN lease_type INT, + IN iaid INT, + IN prefix_len INT, + IN fqdn_fwd INT, + IN fqdn_rev INT, + IN hostname VARCHAR, + IN hwaddr VARCHAR, + IN state INT8, + IN user_context VARCHAR, + IN hwtype INT, + IN hwaddr_source INT +) RETURNS VOID AS \$\$ +BEGIN + INSERT INTO lease6 ( + address, + duid, + valid_lifetime, + expire, + subnet_id, + pref_lifetime, + lease_type, + iaid, + prefix_len, + fqdn_fwd, + fqdn_rev, + hostname, + hwaddr, + state, + user_context, + hwtype, + hwaddr_source + ) VALUES ( + address, + decode(replace(duid, ':', ''), 'hex'), + valid_lifetime, + to_timestamp(expire), + subnet_id, + pref_lifetime, + lease_type, + iaid, + prefix_len, + fqdn_fwd::int::boolean, + fqdn_rev::int::boolean, + replace(hostname, ',', ','), + decode(replace(hwaddr, ':', ''), 'hex'), + state, + replace(user_context, ',', ','), + hwtype, + hwaddr_source + ); +END +\$\$ LANGUAGE plpgsql; + +-- Update the schema version number. +UPDATE schema_version + SET version = '8', minor = '0'; + +-- Commit the script transaction. +COMMIT; + +EOF |