diff options
Diffstat (limited to 'src/share/database/scripts/mysql/upgrade_007.0_to_008.0.sh.in')
| -rw-r--r-- | src/share/database/scripts/mysql/upgrade_007.0_to_008.0.sh.in | 996 |
1 files changed, 996 insertions, 0 deletions
diff --git a/src/share/database/scripts/mysql/upgrade_007.0_to_008.0.sh.in b/src/share/database/scripts/mysql/upgrade_007.0_to_008.0.sh.in new file mode 100644 index 0000000..bba08bc --- /dev/null +++ b/src/share/database/scripts/mysql/upgrade_007.0_to_008.0.sh.in @@ -0,0 +1,996 @@ +#!/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). + +# 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" != "7.0" ]; then + printf 'This script upgrades 7.0 to 8.0. ' + printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}" + exit 0 +fi + +mysql "$@" <<EOF + + +ALTER TABLE dhcp4_options + MODIFY COLUMN modification_ts TIMESTAMP NOT NULL + DEFAULT CURRENT_TIMESTAMP; + +ALTER TABLE dhcp6_options + MODIFY COLUMN modification_ts TIMESTAMP NOT NULL + DEFAULT CURRENT_TIMESTAMP; + +ALTER TABLE dhcp4_subnet + ADD COLUMN authoritative TINYINT(1) DEFAULT NULL, + ADD COLUMN calculate_tee_times TINYINT(1) DEFAULT NULL, + ADD COLUMN t1_percent FLOAT DEFAULT NULL, + ADD COLUMN t2_percent FLOAT DEFAULT NULL; + +ALTER TABLE dhcp4_subnet + MODIFY COLUMN reservation_mode TINYINT(3) DEFAULT NULL; + +ALTER TABLE dhcp4_subnet + MODIFY COLUMN match_client_id TINYINT(1) DEFAULT NULL; + +ALTER TABLE dhcp4_shared_network + ADD COLUMN authoritative TINYINT(1) DEFAULT NULL, + ADD COLUMN calculate_tee_times TINYINT(1) DEFAULT NULL, + ADD COLUMN t1_percent FLOAT DEFAULT NULL, + ADD COLUMN t2_percent FLOAT DEFAULT NULL, + ADD COLUMN boot_file_name VARCHAR(512) DEFAULT NULL, + ADD COLUMN next_server INT(10) UNSIGNED DEFAULT NULL, + ADD COLUMN server_hostname VARCHAR(512) DEFAULT NULL; + +ALTER TABLE dhcp4_shared_network + MODIFY COLUMN reservation_mode TINYINT(3) DEFAULT NULL; + +ALTER TABLE dhcp4_shared_network + MODIFY COLUMN match_client_id TINYINT(1) DEFAULT NULL; + +ALTER TABLE dhcp6_subnet + ADD COLUMN calculate_tee_times TINYINT(1) DEFAULT NULL, + ADD COLUMN t1_percent FLOAT DEFAULT NULL, + ADD COLUMN t2_percent FLOAT DEFAULT NULL, + ADD COLUMN interface_id VARCHAR(128) DEFAULT NULL; + +ALTER TABLE dhcp6_subnet + MODIFY COLUMN reservation_mode TINYINT(3) DEFAULT NULL; + +ALTER TABLE dhcp6_subnet + MODIFY COLUMN rapid_commit TINYINT(1) DEFAULT NULL; + +ALTER TABLE dhcp6_shared_network + ADD COLUMN calculate_tee_times TINYINT(1) DEFAULT NULL, + ADD COLUMN t1_percent FLOAT DEFAULT NULL, + ADD COLUMN t2_percent FLOAT DEFAULT NULL, + ADD COLUMN interface_id VARCHAR(128) DEFAULT NULL; + +ALTER TABLE dhcp6_shared_network + MODIFY COLUMN reservation_mode TINYINT(3) DEFAULT NULL; + +ALTER TABLE dhcp6_shared_network + MODIFY COLUMN rapid_commit TINYINT(1) DEFAULT NULL; + +-- ----------------------------------------------------- +-- Make sure that constraints on the 7.0 schema tables +-- have appropriate referential actions. All tables +-- which join the configuration elements with the +-- servers should perform cascade deletion. +-- ----------------------------------------------------- + +ALTER TABLE dhcp4_global_parameter_server + DROP FOREIGN KEY fk_dhcp4_global_parameter_server_server_id; + +ALTER TABLE dhcp4_global_parameter_server + ADD CONSTRAINT fk_dhcp4_global_parameter_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp4_option_def_server + DROP FOREIGN KEY fk_dhcp4_option_def_server_server_id; + +ALTER TABLE dhcp4_option_def_server + ADD CONSTRAINT fk_dhcp4_option_def_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp4_shared_network_server + DROP FOREIGN KEY fk_dhcp4_shared_network_server_server_id; + +ALTER TABLE dhcp4_shared_network_server + ADD CONSTRAINT fk_dhcp4_shared_network_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp4_subnet_server + DROP FOREIGN KEY fk_dhcp4_subnet_server_server_id; + +ALTER TABLE dhcp4_subnet_server + ADD CONSTRAINT fk_dhcp4_subnet_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp4_options_server + DROP FOREIGN KEY fk_dhcp4_options_server_server_id; + +ALTER TABLE dhcp4_options_server + ADD CONSTRAINT fk_dhcp4_options_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp6_global_parameter_server + DROP FOREIGN KEY fk_dhcp6_global_parameter_server_server_id; + +ALTER TABLE dhcp6_global_parameter_server + ADD CONSTRAINT fk_dhcp6_global_parameter_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp6_option_def_server + DROP FOREIGN KEY fk_dhcp6_option_def_server_server_id; + +ALTER TABLE dhcp6_option_def_server + ADD CONSTRAINT fk_dhcp6_option_def_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp6_shared_network_server + DROP FOREIGN KEY fk_dhcp6_shared_network_server_server_id; + +ALTER TABLE dhcp6_shared_network_server + ADD CONSTRAINT fk_dhcp6_shared_network_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp6_subnet_server + DROP FOREIGN KEY fk_dhcp6_subnet_server_server_id; + +ALTER TABLE dhcp6_subnet_server + ADD CONSTRAINT fk_dhcp6_subnet_server_server_id + FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +ALTER TABLE dhcp6_options_server + DROP FOREIGN KEY fk_dhcp6_options_server_option_id; + +ALTER TABLE dhcp6_options_server + ADD CONSTRAINT fk_dhcp6_options_server_option_id + FOREIGN KEY (option_id) + REFERENCES dhcp6_options (option_id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +-- ----------------------------------------------------- +-- Table dhcp4_audit_revision +-- ----------------------------------------------------- +CREATE TABLE IF NOT EXISTS dhcp4_audit_revision ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + modification_ts TIMESTAMP NOT NULL, + log_message TEXT, + server_id BIGINT(10) UNSIGNED, + PRIMARY KEY (id), + KEY key_dhcp4_audit_revision_by_modification_ts (modification_ts) +) ENGINE=InnoDB; + +-- ----------------------------------------------------- +-- Drop columns from the dhcp4_audit table which now +-- belong to the dhcp4_audit_revision. +-- ----------------------------------------------------- +ALTER TABLE dhcp4_audit + DROP COLUMN modification_ts, + DROP COLUMN log_message; + +-- ----------------------------------------------------- +-- Add column revision_id and the foreign key with a +-- reference to the dhcp4_audit_revision table. +-- ----------------------------------------------------- +ALTER TABLE dhcp4_audit + ADD COLUMN revision_id BIGINT(20) UNSIGNED NOT NULL; + +ALTER TABLE dhcp4_audit + ADD CONSTRAINT fk_dhcp4_audit_revision FOREIGN KEY (revision_id) + REFERENCES dhcp4_audit_revision (id) + ON DELETE NO ACTION ON UPDATE CASCADE; + +-- ----------------------------------------------------- +-- Stored procedure which creates a new entry in the +-- dhcp4_audit_revision table and sets appropriate session +-- variables to be used while creating the audit entries +-- by triggers. This procedure should be called at the +-- beginning of a transaction which modifies configuration +-- data in the database, e.g. when new subnet is added. +-- +-- Parameters: +-- - audit_ts timestamp to be associated with the audit +-- revision. +-- - server_tag is used to retrieve the server_id which +-- associates the changes applied with the particular +-- server or all servers. +-- - audit_log_message is a log message associates with +-- the audit revision. +-- - cascade_transaction is assigned to a session +-- variable which is used in some triggers to determine +-- if the audit entry should be created for them or +-- not. Specifically, this is used when DHCP options +-- are inserted, updated or deleted. If such modification +-- is a part of the larger change (e.g. change in the +-- subnet the options belong to) the dedicated audit +-- entry for options must not be created. On the other +-- hand, if the global option is being added, the +-- audit entry for the option must be created because +-- it is the sole object modified in that case. +-- Session variable disable_audit is used to disable +-- the procedure when wiping the database during +-- unit tests. This avoids issues with revision_id +-- being null. +-- ----------------------------------------------------- +DROP PROCEDURE IF EXISTS createAuditRevisionDHCP4; +DELIMITER $$ +CREATE PROCEDURE createAuditRevisionDHCP4(IN audit_ts TIMESTAMP, + IN server_tag VARCHAR(256), + IN audit_log_message TEXT, + IN cascade_transaction TINYINT(1)) +BEGIN + DECLARE srv_id BIGINT(20); + IF @disable_audit IS NULL OR @disable_audit = 0 THEN + SELECT id INTO srv_id FROM dhcp4_server WHERE tag = server_tag; + INSERT INTO dhcp4_audit_revision (modification_ts, server_id, log_message) + VALUES (audit_ts, srv_id, audit_log_message); + SET @audit_revision_id = LAST_INSERT_ID(); + SET @cascade_transaction = cascade_transaction; + END IF; +END $$ +DELIMITER ; + +-- ----------------------------------------------------- +-- Stored procedure which creates a new entry in the +-- dhcp4_audit table. It should be called from the +-- triggers of the tables where the config modifications +-- are applied. The @audit_revision_id variable contains +-- the revision id to be placed in the audit entries. +-- +-- The following parameters are passed to this procedure: +-- - object_type_val: name of the table to be associated +-- with the applied changes. +-- - object_id_val: identifier of the modified object in +-- that table. +-- - modification_type_val: string value indicating the +-- type of the change, i.e. "create", "update" or +-- "delete". +-- Session variable disable_audit is used to disable +-- the procedure when wiping the database during +-- unit tests. This avoids issues with revision_id +-- being null. +-- ---------------------------------------------------- +DROP PROCEDURE IF EXISTS createAuditEntryDHCP4; +DELIMITER $$ +CREATE PROCEDURE createAuditEntryDHCP4(IN object_type_val VARCHAR(256), + IN object_id_val BIGINT(20) UNSIGNED, + IN modification_type_val VARCHAR(32)) +BEGIN + IF @disable_audit IS NULL OR @disable_audit = 0 THEN + INSERT INTO dhcp4_audit (object_type, object_id, modification_type, revision_id) + VALUES (object_type_val, object_id_val, \ + (SELECT id FROM modification WHERE modification_type = modification_type_val), \ + @audit_revision_id); + END IF; +END $$ +DELIMITER ; + +-- ----------------------------------------------------- +-- Triggers used to create entries in the audit +-- tables upon insertion, update or deletion of the +-- configuration entries. +-- ----------------------------------------------------- + +# Create dhcp4_global_parameter insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_global_parameter_AINS AFTER INSERT ON dhcp4_global_parameter + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_global_parameter', NEW.id, "create"); + END $$ +DELIMITER ; + +# Create dhcp4_global_parameter update trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_global_parameter_AUPD AFTER UPDATE ON dhcp4_global_parameter + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_global_parameter', NEW.id, "update"); + END $$ +DELIMITER ; + +# Create dhcp4_global_parameter delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_global_parameter_ADEL AFTER DELETE ON dhcp4_global_parameter + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_global_parameter', OLD.id, "delete"); + END $$ +DELIMITER ; + +# Create dhcp4_subnet insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_subnet_AINS AFTER INSERT ON dhcp4_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_subnet', NEW.subnet_id, "create"); + END $$ +DELIMITER ; + +# Create dhcp4_subnet update trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_subnet_AUPD AFTER UPDATE ON dhcp4_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_subnet', NEW.subnet_id, "update"); + END $$ +DELIMITER ; + +# Create dhcp4_subnet delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_subnet_ADEL AFTER DELETE ON dhcp4_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_subnet', OLD.subnet_id, "delete"); + END $$ +DELIMITER ; + +# Create dhcp4_shared_network insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_shared_network_AINS AFTER INSERT ON dhcp4_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_shared_network', NEW.id, "create"); + END $$ +DELIMITER ; + +# Create dhcp4_shared_network update trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_shared_network_AUPD AFTER UPDATE ON dhcp4_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_shared_network', NEW.id, "update"); + END $$ +DELIMITER ; + +# Create dhcp4_shared_network delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_shared_network_ADEL AFTER DELETE ON dhcp4_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_shared_network', OLD.id, "delete"); + END $$ +DELIMITER ; + +# Create dhcp4_option_def insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_option_def_AINS AFTER INSERT ON dhcp4_option_def + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_option_def', NEW.id, "create"); + END $$ +DELIMITER ; + +# Create dhcp4_option_def update trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_option_def_AUPD AFTER UPDATE ON dhcp4_option_def + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_option_def', NEW.id, "update"); + END $$ +DELIMITER ; + +# Create dhcp4_option_def delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp4_option_def_ADEL AFTER DELETE ON dhcp4_option_def + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP4('dhcp4_option_def', OLD.id, "delete"); + END $$ +DELIMITER ; + +-- ----------------------------------------------------- +-- Stored procedure which creates an audit entry for a +-- DHCPv4 option. Depending on the scope of the option +-- the audit entry can be created for various levels +-- of configuration hierarchy. If this is a global +-- option the audit entry is created for this option +-- for CREATE, UPDATE or DELETE. If the option is being +-- added for an owning option, e.g. for a subnet, the +-- audit entry is created as an UPDATE to this object. +-- From the Kea perspective such option addition will +-- be seen as a subnet update and the server will fetch +-- the whole subnet and merge it into its configuration. +-- The audit entry is not created if it was already +-- created as part of the current transaction. +-- +-- 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. +-- - 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. +-- ----------------------------------------------------- +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)) +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, create + # audit entry for the entire subnet, which indicates that + # it should be treated as the subnet update. + CALL createAuditEntryDHCP4('dhcp4_subnet', subnet_id, "update"); + ELSEIF scope_id = 4 THEN + # If shared network specific option is added or modified, + # create audit entry for the shared network which + # indicates that it should be treated as the shared + # network update. + SELECT id INTO snid FROM dhcp4_shared_network WHERE name = network_name LIMIT 1; + CALL createAuditEntryDHCP4('dhcp4_shared_network', snid, "update"); + ELSEIF scope_id = 5 THEN + # If pool specific option is added or modified, create + # audit entry for the subnet which this pool belongs to. + SELECT dhcp4_pool.subnet_id INTO sid FROM dhcp4_pool WHERE id = pool_id; + CALL createAuditEntryDHCP4('dhcp4_subnet', sid, "update"); + END IF; + END IF; +END $$ +DELIMITER ; + +# Create dhcp4_options insert trigger +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); + END $$ +DELIMITER ; + +# Create dhcp4_options update trigger +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); + END $$ +DELIMITER ; + +# Create dhcp4_options delete trigger +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); + END $$ +DELIMITER ; + +-- ----------------------------------------------------- +-- Table parameter_data_type +-- Reflects an enum used by Kea to define supported +-- data types for the simple configuration parameters, +-- e.g. global parameters used by DHCP servers. +-- ----------------------------------------------------- +CREATE TABLE IF NOT EXISTS parameter_data_type ( + id TINYINT UNSIGNED NOT NULL PRIMARY KEY, + name VARCHAR(32) NOT NULL +) ENGINE = InnoDB; + +START TRANSACTION; +INSERT INTO parameter_data_type VALUES (0, 'integer'); +INSERT INTO parameter_data_type VALUES (1, 'real'); +INSERT INTO parameter_data_type VALUES (2, 'boolean'); +INSERT INTO parameter_data_type VALUES (4, 'string'); +COMMIT; + +ALTER TABLE dhcp4_global_parameter + ADD COLUMN parameter_type TINYINT UNSIGNED NOT NULL; + +ALTER TABLE dhcp4_global_parameter + ADD CONSTRAINT fk_dhcp4_global_parameter_type FOREIGN KEY (parameter_type) + REFERENCES parameter_data_type (id); + +ALTER TABLE dhcp6_global_parameter + ADD COLUMN parameter_type TINYINT UNSIGNED NOT NULL; + +ALTER TABLE dhcp6_global_parameter + ADD CONSTRAINT fk_dhcp6_global_parameter_type FOREIGN KEY (parameter_type) + REFERENCES parameter_data_type (id); + + +-- Rename dhcp6_subnet_id column of dhcp6_pool and dhcp6_pd_pool + +ALTER TABLE dhcp6_pool + DROP FOREIGN KEY fk_dhcp6_pool_subnet_id; +DROP INDEX fk_dhcp6_pool_subnet_id + ON dhcp6_pool; + +ALTER TABLE dhcp6_pd_pool + DROP FOREIGN KEY fk_dhcp6_pd_pool_subnet_id; +DROP INDEX fk_dhcp6_pd_pool_subnet_id + ON dhcp6_pd_pool; + +ALTER TABLE dhcp6_pool + CHANGE dhcp6_subnet_id subnet_id INT(10) UNSIGNED NOT NULL; + +ALTER TABLE dhcp6_pd_pool + CHANGE dhcp6_subnet_id subnet_id INT(10) UNSIGNED NOT NULL; + +ALTER TABLE dhcp6_pool + ADD CONSTRAINT fk_dhcp6_pool_subnet_id + FOREIGN KEY (subnet_id) + REFERENCES dhcp6_subnet (subnet_id) + ON DELETE CASCADE 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 CASCADE ON UPDATE CASCADE; + +-- align dhcp6_shared_network_server indexes on dhcp4_shared_network_server + +ALTER TABLE dhcp6_shared_network_server + ADD PRIMARY KEY (shared_network_id, server_id); + +ALTER TABLE dhcp6_shared_network_server + DROP FOREIGN KEY fk_dhcp6_shared_network_server_shared_network_id; +DROP INDEX fk_dhcp6_shared_network_server_shared_network_id + ON dhcp6_shared_network_server; +ALTER TABLE dhcp6_shared_network_server + ADD CONSTRAINT fk_dhcp6_shared_network_server_shared_network_id + FOREIGN KEY (shared_network_id) + REFERENCES dhcp6_shared_network (id) + ON DELETE CASCADE ON UPDATE NO ACTION; + +-- Update dhcp4_subnet_server and dhcp6_subnet_server to allow update +-- on the prefix too by setting the CASCADE action. + +ALTER TABLE dhcp4_subnet_server + DROP FOREIGN KEY fk_dhcp4_subnet_server_subnet_id; +ALTER TABLE dhcp4_subnet_server + ADD CONSTRAINT fk_dhcp4_subnet_server_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp4_subnet (subnet_id) + ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE dhcp6_subnet_server + DROP FOREIGN KEY fk_dhcp6_subnet_server_subnet_id; +ALTER TABLE dhcp6_subnet_server + ADD CONSTRAINT fk_dhcp6_subnet_server_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp6_subnet (subnet_id) + ON DELETE CASCADE ON UPDATE CASCADE; + +-- ----------------------------------------------------- +-- Table dhcp6_audit_revision +-- ----------------------------------------------------- +CREATE TABLE IF NOT EXISTS dhcp6_audit_revision ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + modification_ts TIMESTAMP NOT NULL, + log_message TEXT, + server_id BIGINT(10) UNSIGNED, + PRIMARY KEY (id), + KEY key_dhcp6_audit_revision_by_modification_ts (modification_ts) +) ENGINE=InnoDB; + +-- ----------------------------------------------------- +-- Drop columns from the dhcp6_audit table which now +-- belong to the dhcp6_audit_revision. +-- ----------------------------------------------------- +ALTER TABLE dhcp6_audit + DROP COLUMN modification_ts, + DROP COLUMN log_message; + +-- ----------------------------------------------------- +-- Add column revision_id and the foreign key with a +-- reference to the dhcp6_audit_revision table. +-- ----------------------------------------------------- +ALTER TABLE dhcp6_audit + ADD COLUMN revision_id BIGINT(20) UNSIGNED NOT NULL; + +ALTER TABLE dhcp6_audit + ADD CONSTRAINT fk_dhcp6_audit_revision FOREIGN KEY (revision_id) + REFERENCES dhcp6_audit_revision (id) + ON DELETE NO ACTION ON UPDATE CASCADE; + +-- ----------------------------------------------------- +-- Stored procedure which creates a new entry in the +-- dhcp6_audit_revision table and sets appropriate session +-- variables to be used while creating the audit entries +-- by triggers. This procedure should be called at the +-- beginning of a transaction which modifies configuration +-- data in the database, e.g. when new subnet is added. +-- +-- Parameters: +-- - audit_ts timestamp to be associated with the audit +-- revision. +-- - server_tag is used to retrieve the server_id which +-- associates the changes applied with the particular +-- server or all servers. +-- - audit_log_message is a log message associates with +-- the audit revision. +-- - cascade_transaction is assigned to a session +-- variable which is used in some triggers to determine +-- if the audit entry should be created for them or +-- not. Specifically, this is used when DHCP options +-- are inserted, updated or deleted. If such modification +-- is a part of the larger change (e.g. change in the +-- subnet the options belong to) the dedicated audit +-- entry for options must not be created. On the other +-- hand, if the global option is being added, the +-- audit entry for the option must be created because +-- it is the sole object modified in that case. +-- Session variable disable_audit is used to disable +-- the procedure when wiping the database during +-- unit tests. This avoids issues with revision_id +-- being null. +-- ----------------------------------------------------- +DROP PROCEDURE IF EXISTS createAuditRevisionDHCP6; +DELIMITER $$ +CREATE PROCEDURE createAuditRevisionDHCP6(IN audit_ts TIMESTAMP, + IN server_tag VARCHAR(256), + IN audit_log_message TEXT, + IN cascade_transaction TINYINT(1)) +BEGIN + DECLARE srv_id BIGINT(20); + IF @disable_audit IS NULL OR @disable_audit = 0 THEN + SELECT id INTO srv_id FROM dhcp6_server WHERE tag = server_tag; + INSERT INTO dhcp6_audit_revision (modification_ts, server_id, log_message) + VALUES (audit_ts, srv_id, audit_log_message); + SET @audit_revision_id = LAST_INSERT_ID(); + SET @cascade_transaction = cascade_transaction; + END IF; +END $$ +DELIMITER ; + +-- ----------------------------------------------------- +-- Stored procedure which creates a new entry in the +-- dhcp6_audit table. It should be called from the +-- triggers of the tables where the config modifications +-- are applied. The @audit_revision_id variable contains +-- the revision id to be placed in the audit entries. +-- +-- The following parameters are passed to this procedure: +-- - object_type_val: name of the table to be associated +-- with the applied changes. +-- - object_id_val: identifier of the modified object in +-- that table. +-- - modification_type_val: string value indicating the +-- type of the change, i.e. "create", "update" or +-- "delete". +-- Session variable disable_audit is used to disable +-- the procedure when wiping the database during +-- unit tests. This avoids issues with revision_id +-- being null. +-- ---------------------------------------------------- +DROP PROCEDURE IF EXISTS createAuditEntryDHCP6; +DELIMITER $$ +CREATE PROCEDURE createAuditEntryDHCP6(IN object_type_val VARCHAR(256), + IN object_id_val BIGINT(20) UNSIGNED, + IN modification_type_val VARCHAR(32)) +BEGIN + IF @disable_audit IS NULL OR @disable_audit = 0 THEN + INSERT INTO dhcp6_audit (object_type, object_id, modification_type, revision_id) + VALUES (object_type_val, object_id_val, \ + (SELECT id FROM modification WHERE modification_type = modification_type_val), \ + @audit_revision_id); + END IF; +END $$ +DELIMITER ; + +-- ----------------------------------------------------- +-- Triggers used to create entries in the audit +-- tables upon insertion, update or deletion of the +-- configuration entries. +-- ----------------------------------------------------- + +# Create dhcp6_global_parameter insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_global_parameter_AINS AFTER INSERT ON dhcp6_global_parameter + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_global_parameter', NEW.id, "create"); + END $$ +DELIMITER ; + +# Create dhcp6_global_parameter update trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_global_parameter_AUPD AFTER UPDATE ON dhcp6_global_parameter + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_global_parameter', NEW.id, "update"); + END $$ +DELIMITER ; + +# Create dhcp6_global_parameter delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_global_parameter_ADEL AFTER DELETE ON dhcp6_global_parameter + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_global_parameter', OLD.id, "delete"); + END $$ +DELIMITER ; + +# Create dhcp6_subnet insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_subnet_AINS AFTER INSERT ON dhcp6_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_subnet', NEW.subnet_id, "create"); + END $$ +DELIMITER ; + +# Create dhcp6_subnet update trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_subnet_AUPD AFTER UPDATE ON dhcp6_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_subnet', NEW.subnet_id, "update"); + END $$ +DELIMITER ; + +# Create dhcp6_subnet delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_subnet_ADEL AFTER DELETE ON dhcp6_subnet + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_subnet', OLD.subnet_id, "delete"); + END $$ +DELIMITER ; + +# Create dhcp6_shared_network insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_shared_network_AINS AFTER INSERT ON dhcp6_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_shared_network', NEW.id, "create"); + END $$ +DELIMITER ; + +# Create dhcp6_shared_network update trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_shared_network_AUPD AFTER UPDATE ON dhcp6_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_shared_network', NEW.id, "update"); + END $$ +DELIMITER ; + +# Create dhcp6_shared_network delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_shared_network_ADEL AFTER DELETE ON dhcp6_shared_network + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_shared_network', OLD.id, "delete"); + END $$ +DELIMITER ; + +# Create dhcp6_option_def insert trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_option_def_AINS AFTER INSERT ON dhcp6_option_def + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_option_def', NEW.id, "create"); + END $$ +DELIMITER ; + +# Create dhcp6_option_def update trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_option_def_AUPD AFTER UPDATE ON dhcp6_option_def + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_option_def', NEW.id, "update"); + END $$ +DELIMITER ; + +# Create dhcp6_option_def delete trigger +DELIMITER $$ +CREATE TRIGGER dhcp6_option_def_ADEL AFTER DELETE ON dhcp6_option_def + FOR EACH ROW + BEGIN + CALL createAuditEntryDHCP6('dhcp6_option_def', OLD.id, "delete"); + END $$ +DELIMITER ; + +-- ----------------------------------------------------- +-- Stored procedure which creates an audit entry for a +-- DHCPv6 option. Depending on the scope of the option +-- the audit entry can be created for various levels +-- of configuration hierarchy. If this is a global +-- option the audit entry is created for this option +-- for CREATE, UPDATE or DELETE. If the option is being +-- added for an owning option, e.g. for a subnet, the +-- audit entry is created as an UPDATE to this object. +-- From the Kea perspective such option addition will +-- be seen as a subnet update and the server will fetch +-- the whole subnet and merge it into its configuration. +-- The audit entry is not created if it was already +-- created as part of the current transaction. +-- +-- 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. +-- - 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. +-- ----------------------------------------------------- +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)) +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, create + # audit entry for the entire subnet, which indicates that + # it should be treated as the subnet update. + CALL createAuditEntryDHCP6('dhcp6_subnet', subnet_id, "update"); + ELSEIF scope_id = 4 THEN + # If shared network specific option is added or modified, + # create audit entry for the shared network which + # indicates that it should be treated as the shared + # network update. + SELECT id INTO snid FROM dhcp6_shared_network WHERE name = network_name LIMIT 1; + CALL createAuditEntryDHCP6('dhcp6_shared_network', snid, "update"); + ELSEIF scope_id = 5 THEN + # If pool specific option is added or modified, create + # audit entry for the subnet which this pool belongs to. + SELECT dhcp6_pool.subnet_id INTO sid FROM dhcp6_pool WHERE id = pool_id; + CALL createAuditEntryDHCP6('dhcp6_subnet', sid, "update"); + ELSEIF scope_id = 6 THEN + # If pd pool specific option is added or modified, create + # audit entry for the subnet which this pd pool belongs to. + SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pd_pool_id; + CALL createAuditEntryDHCP6('dhcp6_subnet', sid, "update"); + END IF; + END IF; +END $$ +DELIMITER ; + +# Create dhcp6_options insert trigger +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); + END $$ +DELIMITER ; + +# Create dhcp6_options update trigger +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); + END $$ +DELIMITER ; + +# Create dhcp6_options delete trigger +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); + END $$ +DELIMITER ; + +# Update the schema version number +UPDATE schema_version +SET version = '8', minor = '0'; + +# This line concludes database upgrade to version 8.0. + +EOF |