diff options
Diffstat (limited to 'src/share/database/scripts/mysql/upgrade_008.1_to_008.2.sh.in')
| -rw-r--r-- | src/share/database/scripts/mysql/upgrade_008.1_to_008.2.sh.in | 500 |
1 files changed, 500 insertions, 0 deletions
diff --git a/src/share/database/scripts/mysql/upgrade_008.1_to_008.2.sh.in b/src/share/database/scripts/mysql/upgrade_008.1_to_008.2.sh.in new file mode 100644 index 0000000..4ad298e --- /dev/null +++ b/src/share/database/scripts/mysql/upgrade_008.1_to_008.2.sh.in @@ -0,0 +1,500 @@ +#!/bin/sh + +# Copyright (C) 2019-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). + +# shellcheck disable=SC2039 +# SC2039: In POSIX sh, 'local' is undefined. + +# 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" != "8.1" ]; then + printf 'This script upgrades 8.1 to 8.2. ' + printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}" + exit 0 +fi + +# Get the schema name from database argument. We need this to +# query information_schema for the right database. +for arg in "${@}" +do + if ! printf '%s' "${arg}" | grep -Eq '^\-\-' + then + schema="$arg" + break + fi +done + +# Make sure we can id the schema +if [ -z "$schema" ] +then + printf "Could not find database schema name in cmd line args: %s\n" "${*}" + exit 255 +fi + +# Save the command line args, as we use these later change_column function. + +# Function to rename a column in a table. +change_column() { + local schema="${1-}"; shift + local table="${1-}"; shift + local ocolumn="${1-}"; shift + local ncolumn="${1-}"; shift + + # First let's find out if the column name in the table actually needs updating. + sql="select count(column_name) from information_schema.columns where table_schema='$schema' and table_name = '$table' and column_name = '$ocolumn'" + if ! count=$(mysql -N -B "${@}" -e "${sql}") + then + printf 'change_column: schema query failed [%s]\n' "${sql}" + exit 255 + fi + + # If we found a match record, the column needs to be renamed + if [ "$count" -eq 1 ] + then + sql="ALTER TABLE $table CHANGE COLUMN $ocolumn $ncolumn" + if ! mysql -N -B "${@}" -e "${sql}" + then + printf 'change_column: alter query failed [%s]\n' "${sql}" + exit 255 + fi + else + printf '%s column is already correct\n' "${table}" + fi +} + +mysql "$@" <<EOF + +# Drop existing trigger on the dhcp4_shared_network table. +DROP TRIGGER dhcp4_shared_network_ADEL; + +# Create new trigger which will delete options associated with the shared +# network. +DELIMITER $$ +CREATE TRIGGER dhcp4_shared_network_BDEL BEFORE DELETE ON dhcp4_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_shared_network', OLD.id, "delete"); + DELETE FROM dhcp4_options WHERE shared_network_name = OLD.name; + END $$ +DELIMITER ; + +# Drop existing trigger on the dhcp4_subnet table. +DROP TRIGGER dhcp4_subnet_ADEL; + +# Create new trigger which will delete pools associated with the subnet and +# the options associated with the subnet. +DELIMITER $$ +CREATE TRIGGER dhcp4_subnet_BDEL BEFORE DELETE ON dhcp4_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_subnet', OLD.subnet_id, "delete"); + DELETE FROM dhcp4_pool WHERE subnet_id = OLD.subnet_id; + DELETE FROM dhcp4_options WHERE dhcp4_subnet_id = OLD.subnet_id; + END $$ +DELIMITER ; + +# Do not perform cascade deletion of the data in the dhcp4_pool because +# the cascade deletion does not execute triggers associated with the table. +# Instead we are going to use triggers on the dhcp4_subnet table. +ALTER TABLE dhcp4_pool + DROP FOREIGN KEY fk_dhcp4_pool_subnet_id; + +ALTER TABLE dhcp4_pool + ADD CONSTRAINT fk_dhcp4_pool_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp4_subnet (subnet_id) + ON DELETE NO ACTION ON UPDATE CASCADE; + +# Drop existing trigger on the dhcp6_shared_network table. +DROP TRIGGER dhcp6_shared_network_ADEL; + +# Create new trigger which will delete options associated with the shared +# network. +DELIMITER $$ +CREATE TRIGGER dhcp6_shared_network_BDEL BEFORE DELETE ON dhcp6_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_shared_network', OLD.id, "delete"); + DELETE FROM dhcp6_options WHERE shared_network_name = OLD.name; + END $$ +DELIMITER ; + +# Drop existing trigger on the dhcp6_subnet table. +DROP TRIGGER dhcp6_subnet_ADEL; + +# Create new trigger which will delete pools associated with the subnet and +# the options associated with the subnet. +DELIMITER $$ +CREATE TRIGGER dhcp6_subnet_BDEL BEFORE DELETE ON dhcp6_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_subnet', OLD.subnet_id, "delete"); + DELETE FROM dhcp6_pool WHERE subnet_id = OLD.subnet_id; + DELETE FROM dhcp6_pd_pool WHERE subnet_id = OLD.subnet_id; + DELETE FROM dhcp6_options WHERE dhcp6_subnet_id = OLD.subnet_id; + END $$ +DELIMITER ; + +# Do not perform cascade deletion of the data in the dhcp6_pool and dhcp6_pd_pool +# because the cascaded deletion does not execute triggers associated with the table. +# Instead we are going to use triggers on the dhcp6_subnet table. +ALTER TABLE dhcp6_pool + DROP FOREIGN KEY fk_dhcp6_pool_subnet_id; + +ALTER TABLE dhcp6_pd_pool + DROP FOREIGN KEY fk_dhcp6_pd_pool_subnet_id; + +ALTER TABLE dhcp6_pool + ADD CONSTRAINT fk_dhcp6_pool_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp6_subnet (subnet_id) + ON DELETE NO ACTION ON UPDATE CASCADE; + +ALTER TABLE dhcp6_pd_pool + ADD CONSTRAINT fk_dhcp6_pd_pool_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp6_subnet (subnet_id) + ON DELETE NO ACTION ON UPDATE CASCADE; + +# Create trigger which removes pool specific options upon removal of +# the pool. +DELIMITER $$ +CREATE TRIGGER dhcp6_pd_pool_BDEL BEFORE DELETE ON dhcp6_pd_pool FOR EACH ROW +BEGIN +DELETE FROM dhcp6_options WHERE scope_id = 6 AND pd_pool_id = OLD.id; +END +$$ +DELIMITER ; + +# Add missing columns in pools. +ALTER TABLE dhcp4_pool + ADD COLUMN client_class VARCHAR(128) DEFAULT NULL, + ADD COLUMN require_client_classes LONGTEXT, + ADD COLUMN user_context LONGTEXT; + +ALTER TABLE dhcp6_pd_pool + ADD COLUMN excluded_prefix VARCHAR(45) DEFAULT NULL, + ADD COLUMN excluded_prefix_length TINYINT(3) NOT NULL, + ADD COLUMN client_class VARCHAR(128) DEFAULT NULL, + ADD COLUMN require_client_classes LONGTEXT, + ADD COLUMN user_context LONGTEXT; + +ALTER TABLE dhcp6_pool + ADD COLUMN client_class VARCHAR(128) DEFAULT NULL, + ADD COLUMN require_client_classes LONGTEXT, + ADD COLUMN user_context LONGTEXT; + +-- ----------------------------------------------------- +-- +-- New version of the createOptionAuditDHCP4 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. +-- - modification_ts: modification timestamp of the +-- option. +-- ----------------------------------------------------- +DROP PROCEDURE IF EXISTS createOptionAuditDHCP4; +DELIMITER $$ +CREATE PROCEDURE createOptionAuditDHCP4(IN modification_type VARCHAR(32), + IN scope_id TINYINT(3) UNSIGNED, + IN option_id BIGINT(20) UNSIGNED, + IN subnet_id INT(10) UNSIGNED, + IN host_id INT(10) UNSIGNED, + IN network_name VARCHAR(128), + IN pool_id BIGINT(20), + IN modification_ts TIMESTAMP) +BEGIN + # These variables will hold shared network id and subnet id that + # we will select. + DECLARE snid VARCHAR(128); + DECLARE sid INT(10) UNSIGNED; + + # Cascade transaction flag is set to 1 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 1. If + # the option is added into the existing subnet the cascade + # transaction is equal to 0. 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 'dhcp4_options' is when a global option is added. + # Global options do not have the owner. + IF @cascade_transaction IS NULL OR @cascade_transaction = 0 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 'dhcp4_options' table. + CALL createAuditEntryDHCP4('dhcp4_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 dhcp4_subnet AS s SET s.modification_ts = modification_ts + WHERE s.subnet_id = 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 dhcp4_shared_network WHERE name = network_name LIMIT 1; + UPDATE dhcp4_shared_network AS n SET n.modification_ts = modification_ts + WHERE n.id = snid; + ELSEIF scope_id = 5 THEN + # If pool specific option is added or modified, update + # the modification timestamp of the owning subnet. + SELECT dhcp4_pool.subnet_id INTO sid FROM dhcp4_pool WHERE id = pool_id; + UPDATE dhcp4_subnet AS s SET s.modification_ts = modification_ts + WHERE s.subnet_id = sid; + END IF; + END IF; +END $$ +DELIMITER ; + +# Recreate dhcp4_options_AINS trigger to pass timestamp to the updated +# version of the createOptionAuditDHCP4. +DROP TRIGGER IF EXISTS dhcp4_options_AINS; + +# This trigger is executed after inserting a DHCPv4 option into the +# database. It creates appropriate audit entry for this option or +# a parent object owning this option. +DELIMITER $$ +CREATE TRIGGER dhcp4_options_AINS AFTER INSERT ON dhcp4_options + FOR EACH ROW + BEGIN + CALL createOptionAuditDHCP4("create", NEW.scope_id, NEW.option_id, NEW.dhcp4_subnet_id, + NEW.host_id, NEW.shared_network_name, NEW.pool_id, + NEW.modification_ts); + END $$ +DELIMITER ; + +# Recreate dhcp4_options_AUPD trigger to pass timestamp to the updated +# version of the createOptionAuditDHCP4. +DROP TRIGGER IF EXISTS dhcp4_options_AUPD; + +# This trigger is executed after updating a DHCPv4 option in the +# database. It creates appropriate audit entry for this option or +# a parent object owning this option. +DELIMITER $$ +CREATE TRIGGER dhcp4_options_AUPD AFTER UPDATE ON dhcp4_options + FOR EACH ROW + BEGIN + CALL createOptionAuditDHCP4("update", NEW.scope_id, NEW.option_id, NEW.dhcp4_subnet_id, + NEW.host_id, NEW.shared_network_name, NEW.pool_id, + NEW.modification_ts); + END $$ +DELIMITER ; + +# Recreate dhcp4_options_ADEL trigger to pass timestamp to the updated +# version of the createOptionAuditDHCP4. +DROP TRIGGER IF EXISTS dhcp4_options_ADEL; + +# This trigger is executed after deleting a DHCPv4 option in the +# database. It creates appropriate audit entry for this option or +# a parent object owning this option. +DELIMITER $$ +CREATE TRIGGER dhcp4_options_ADEL AFTER DELETE ON dhcp4_options + FOR EACH ROW + BEGIN + CALL createOptionAuditDHCP4("delete", OLD.scope_id, OLD.option_id, OLD.dhcp4_subnet_id, + OLD.host_id, OLD.shared_network_name, OLD.pool_id, + NOW()); + END $$ +DELIMITER ; + + +-- ----------------------------------------------------- +-- +-- New version of the createOptionAuditDHCP4 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. +-- ----------------------------------------------------- +DROP PROCEDURE IF EXISTS createOptionAuditDHCP6; +DELIMITER $$ +CREATE PROCEDURE createOptionAuditDHCP6(IN modification_type VARCHAR(32), + IN scope_id TINYINT(3) UNSIGNED, + IN option_id BIGINT(20) UNSIGNED, + IN subnet_id INT(10) UNSIGNED, + IN host_id INT(10) UNSIGNED, + IN network_name VARCHAR(128), + IN pool_id BIGINT(20), + IN pd_pool_id BIGINT(20), + IN modification_ts TIMESTAMP) +BEGIN + # These variables will hold shared network id and subnet id that + # we will select. + DECLARE snid VARCHAR(128); + DECLARE sid INT(10) UNSIGNED; + + # Cascade transaction flag is set to 1 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 1. If + # the option is added into the existing subnet the cascade + # transaction is equal to 0. 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. + IF @cascade_transaction IS NULL OR @cascade_transaction = 0 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. + CALL 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 AS s SET s.modification_ts = modification_ts + WHERE s.subnet_id = 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 AS n SET n.modification_ts = modification_ts + WHERE n.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 AS s SET s.modification_ts = modification_ts + WHERE s.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 = pd_pool_id; + UPDATE dhcp6_subnet AS s SET s.modification_ts = modification_ts + WHERE s.subnet_id = sid; + END IF; + END IF; +END $$ +DELIMITER ; + +# Recreate dhcp6_options_AINS trigger to pass timestamp to the updated +# version of the createOptionAuditDHCP6. +DROP TRIGGER IF EXISTS dhcp6_options_AINS; + +# This trigger is executed after inserting a DHCPv6 option into the +# database. It creates appropriate audit entry for this option or +# a parent object owning this option. +DELIMITER $$ +CREATE TRIGGER dhcp6_options_AINS AFTER INSERT ON dhcp6_options + FOR EACH ROW + BEGIN + CALL createOptionAuditDHCP6("create", NEW.scope_id, NEW.option_id, NEW.dhcp6_subnet_id, + NEW.host_id, NEW.shared_network_name, NEW.pool_id, + NEW.pd_pool_id, NEW.modification_ts); + END $$ +DELIMITER ; + +# Recreate dhcp6_options_AUPD trigger to pass timestamp to the updated +# version of the createOptionAuditDHCP6. +DROP TRIGGER IF EXISTS dhcp6_options_AUPD; + +# This trigger is executed after updating a DHCPv6 option in the +# database. It creates appropriate audit entry for this option or +# a parent object owning this option. +DELIMITER $$ +CREATE TRIGGER dhcp6_options_AUPD AFTER UPDATE ON dhcp6_options + FOR EACH ROW + BEGIN + CALL createOptionAuditDHCP6("update", NEW.scope_id, NEW.option_id, NEW.dhcp6_subnet_id, + NEW.host_id, NEW.shared_network_name, NEW.pool_id, + NEW.pd_pool_id, NEW.modification_ts); + END $$ +DELIMITER ; + +# Recreate dhcp6_options_ADEL trigger to pass timestamp to the updated +# version of the createOptionAuditDHCP6. +DROP TRIGGER IF EXISTS dhcp6_options_ADEL; + +# This trigger is executed after deleting a DHCPv6 option in the +# database. It creates appropriate audit entry for this option or +# a parent object owning this option. +DELIMITER $$ +CREATE TRIGGER dhcp6_options_ADEL AFTER DELETE ON dhcp6_options + FOR EACH ROW + BEGIN + CALL createOptionAuditDHCP6("delete", OLD.scope_id, OLD.option_id, OLD.dhcp6_subnet_id, + OLD.host_id, OLD.shared_network_name, OLD.pool_id, + OLD.pd_pool_id, NOW()); + END $$ +DELIMITER ; + +# Update the schema version number +UPDATE schema_version +SET version = '8', minor = '2'; + +# This line concludes database upgrade to version 8.2. + +EOF + +# We need to rename the columns in the option def tables because "array" is +# a MySQL keyword as of 8.0.17 +change_column "${schema}" dhcp4_option_def array "is_array TINYINT(1) NOT NULL" "${@}" +change_column "${schema}" dhcp6_option_def array "is_array TINYINT(1) NOT NULL" "${@}" |