diff options
Diffstat (limited to 'src/share/database/scripts/mysql/upgrade_006.0_to_007.0.sh.in')
| -rw-r--r-- | src/share/database/scripts/mysql/upgrade_006.0_to_007.0.sh.in | 664 |
1 files changed, 664 insertions, 0 deletions
diff --git a/src/share/database/scripts/mysql/upgrade_006.0_to_007.0.sh.in b/src/share/database/scripts/mysql/upgrade_006.0_to_007.0.sh.in new file mode 100644 index 0000000..5d83da5 --- /dev/null +++ b/src/share/database/scripts/mysql/upgrade_006.0_to_007.0.sh.in @@ -0,0 +1,664 @@ +#!/bin/sh + +# Copyright (C) 2018-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" != "6.0" ]; then + printf 'This script upgrades 6.0 to 7.0. ' + printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}" + exit 0 +fi + +mysql "$@" <<EOF + +# Add user context into tables holding leases +ALTER TABLE lease4 ADD COLUMN user_context TEXT NULL; +ALTER TABLE lease6 ADD COLUMN user_context TEXT NULL; + +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,user_context'; +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, + IFNULL(l.user_context, '') +FROM + lease4 l + LEFT OUTER JOIN lease_state s on (l.state = s.state) +ORDER BY l.address; +END $$ +DELIMITER ; + +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,user_context'; +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, ''), + IFNULL(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; +END $$ +DELIMITER ; + +# Create logs table (logs table is used by forensic logging hook library) +CREATE TABLE logs ( + timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, # creation timestamp + address VARCHAR(43) NULL, # address or prefix + log TEXT NOT NULL # the log itself + ) ENGINE = INNODB; + +# Create search index +CREATE INDEX timestamp_index ON logs (timestamp); + +#add auth key for reconfiguration +ALTER TABLE hosts + ADD COLUMN auth_key VARCHAR(16) 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; + +# 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"); + +# Create table modification +CREATE TABLE IF NOT EXISTS modification ( + id TINYINT(3) NOT NULL, + modification_type VARCHAR(32) NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO modification(id, modification_type) + VALUES(0, "create"); + +INSERT INTO modification(id, modification_type) + VALUES(1, "update"); + +INSERT INTO modification(id, modification_type) + VALUES(2, "delete"); + +# Create table dhcp4_server +# +CREATE TABLE IF NOT EXISTS dhcp4_server ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + tag VARCHAR(64) NOT NULL, + description TEXT, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (id), + UNIQUE KEY dhcp4_server_tag_UNIQUE (tag), + KEY key_dhcp4_server_modification_ts (modification_ts) +) ENGINE=InnoDB; + +# Special server entry meaning "all servers". This refers to +# the configuration entries owned by all servers. +INSERT INTO dhcp4_server(id, tag, description, modification_ts) + VALUES(1, "all", "special type: all servers", NOW()); + +# Create table dhcp4_audit +# +CREATE TABLE IF NOT EXISTS dhcp4_audit ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + object_type VARCHAR(256) NOT NULL, + object_id BIGINT(2) UNSIGNED NOT NULL, + modification_type TINYINT(1) NOT NULL, + modification_ts TIMESTAMP NOT NULL, + log_message TEXT, + PRIMARY KEY (id), + KEY key_dhcp4_audit_by_modification_ts (modification_ts), + KEY fk_dhcp4_audit_modification_type (modification_type), + CONSTRAINT fk_dhcp4_audit_modification_type FOREIGN KEY (modification_type) + REFERENCES modification (id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp4_global_parameter +# +CREATE TABLE IF NOT EXISTS dhcp4_global_parameter ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + name VARCHAR(128) NOT NULL, + value LONGTEXT NOT NULL, + modification_ts timestamp NOT NULL, + PRIMARY KEY (id), + KEY key_dhcp4_global_parameter_modification_ts (modification_ts), + KEY key_dhcp4_global_parameter_name (name) +) ENGINE=InnoDB; + +# Create table dhcp4_global_parameter_server +# M-to-M cross-reference between global parameters and servers +# +CREATE TABLE IF NOT EXISTS dhcp4_global_parameter_server ( + parameter_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (parameter_id, server_id), + KEY fk_dhcp4_global_parameter_server_server_id (server_id), + KEY key_dhcp4_global_parameter_server (modification_ts), + CONSTRAINT fk_dhcp4_global_parameter_server_parameter_id FOREIGN KEY (parameter_id) + REFERENCES dhcp4_global_parameter (id) + ON DELETE CASCADE ON UPDATE NO ACTION, + CONSTRAINT fk_dhcp4_global_parameter_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp4_option_def +# +CREATE TABLE IF NOT EXISTS dhcp4_option_def ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + code SMALLINT UNSIGNED NOT NULL, + name VARCHAR(128) NOT NULL, + space VARCHAR(128) NOT NULL, + type TINYINT UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + is_array TINYINT(1) NOT NULL, + encapsulate VARCHAR(128) NOT NULL, + record_types VARCHAR(512) DEFAULT NULL, + user_context LONGTEXT, + PRIMARY KEY (id), + KEY key_dhcp4_option_def_modification_ts (modification_ts), + KEY key_dhcp4_option_def_code_space (code, space) +) ENGINE=InnoDB; + +# Create table dhcp4_option_def_server +# M-to-M cross-reference between option definitions and servers +# +CREATE TABLE IF NOT EXISTS dhcp4_option_def_server ( + option_def_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (option_def_id, server_id), + KEY fk_dhcp4_option_def_server_server_id_idx (server_id), + KEY key_dhcp4_option_def_server_modification_ts (modification_ts), + 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, + CONSTRAINT fk_dhcp4_option_def_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp4_shared_network +# +CREATE TABLE IF NOT EXISTS dhcp4_shared_network ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + name VARCHAR(128) NOT NULL, + client_class VARCHAR(128) DEFAULT NULL, + interface VARCHAR(128) DEFAULT NULL, + match_client_id TINYINT(1) NOT NULL DEFAULT '1', + modification_ts TIMESTAMP NOT NULL, + rebind_timer INT(10) DEFAULT NULL, + relay LONGTEXT, + renew_timer INT(10) DEFAULT NULL, + require_client_classes LONGTEXT DEFAULT NULL, + reservation_mode TINYINT(3) NOT NULL DEFAULT '3', + user_context LONGTEXT, + valid_lifetime INT(10) DEFAULT NULL, + PRIMARY KEY (id), + UNIQUE KEY name_UNIQUE (name), + KEY key_dhcp4_shared_network_modification_ts (modification_ts) +) ENGINE=InnoDB; + +# Create table dhcp4_shared_network_server +# M-to-M cross-reference between shared networks and servers +# +CREATE TABLE IF NOT EXISTS dhcp4_shared_network_server ( + shared_network_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (shared_network_id, server_id), + KEY key_dhcp4_shared_network_server_modification_ts (modification_ts), + KEY fk_dhcp4_shared_network_server_server_id (server_id), + CONSTRAINT fk_dhcp4_shared_network_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT fk_dhcp4_shared_network_server_shared_network_id FOREIGN KEY (shared_network_id) + REFERENCES dhcp4_shared_network (id) ON DELETE CASCADE ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp4_subnet +# +CREATE TABLE IF NOT EXISTS dhcp4_subnet ( + subnet_id INT(10) UNSIGNED NOT NULL, + subnet_prefix VARCHAR(32) NOT NULL, + 4o6_interface VARCHAR(128) DEFAULT NULL, + 4o6_interface_id VARCHAR(128) DEFAULT NULL, + 4o6_subnet VARCHAR(64) DEFAULT NULL, + boot_file_name VARCHAR(512) DEFAULT NULL, + client_class VARCHAR(128) DEFAULT NULL, + interface VARCHAR(128) DEFAULT NULL, + match_client_id TINYINT(1) NOT NULL DEFAULT '1', + modification_ts TIMESTAMP NOT NULL, + next_server INT(10) UNSIGNED DEFAULT NULL, + rebind_timer INT(10) DEFAULT NULL, + relay LONGTEXT, + renew_timer INT(10) DEFAULT NULL, + require_client_classes LONGTEXT DEFAULT NULL, + reservation_mode TINYINT(3) NOT NULL DEFAULT '3', + server_hostname VARCHAR(512) DEFAULT NULL, + shared_network_name VARCHAR(128) DEFAULT NULL, + user_context LONGTEXT, + valid_lifetime INT(10) DEFAULT NULL, + PRIMARY KEY (subnet_id), + UNIQUE KEY subnet4_subnet_prefix (subnet_prefix), + KEY fk_dhcp4_subnet_shared_network (shared_network_name), + KEY key_dhcp4_subnet_modification_ts (modification_ts), + CONSTRAINT fk_dhcp4_subnet_shared_network FOREIGN KEY (shared_network_name) + REFERENCES dhcp4_shared_network (name) + ON DELETE SET NULL ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp4_pool +# +CREATE TABLE IF NOT EXISTS dhcp4_pool ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + start_address INT(10) UNSIGNED NOT NULL, + end_address INT(10) UNSIGNED NOT NULL, + subnet_id INT(10) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (id), + KEY key_dhcp4_pool_modification_ts (modification_ts), + KEY fk_dhcp4_pool_subnet_id (subnet_id), + CONSTRAINT fk_dhcp4_pool_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp4_subnet (subnet_id) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +# Create table dhcp4_subnet_server +# M-to-M cross-reference between subnets and servers +# +CREATE TABLE IF NOT EXISTS dhcp4_subnet_server ( + subnet_id INT(10) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (subnet_id,server_id), + KEY fk_dhcp4_subnet_server_server_id_idx (server_id), + KEY key_dhcp4_subnet_server_modification_ts (modification_ts), + CONSTRAINT fk_dhcp4_subnet_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT fk_dhcp4_subnet_server_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp4_subnet (subnet_id) + ON DELETE CASCADE ON UPDATE NO ACTION +) ENGINE=InnoDB; + + +# Modify the primary key to BINGINT as other tables have. +# +ALTER TABLE dhcp4_options MODIFY option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; + +# Add configuration backend specific columns. +ALTER TABLE dhcp4_options + ADD COLUMN shared_network_name VARCHAR(128) DEFAULT NULL, + ADD COLUMN pool_id BIGINT(20) UNSIGNED DEFAULT NULL, + ADD COLUMN modification_ts TIMESTAMP NOT NULL; + +# Create table dhcp4_options_server +# M-to-M cross-reference between options and servers +# +CREATE TABLE IF NOT EXISTS dhcp4_options_server ( + option_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (option_id, server_id), + KEY fk_dhcp4_options_server_server_id (server_id), + KEY key_dhcp4_options_server_modification_ts (modification_ts), + CONSTRAINT fk_dhcp4_options_server_option_id FOREIGN KEY (option_id) + REFERENCES dhcp4_options (option_id) + ON DELETE CASCADE ON UPDATE NO ACTION, + CONSTRAINT fk_dhcp4_options_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp4_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create trigger which removes pool specific options upon removal of +# the pool. +DELIMITER $$ +CREATE TRIGGER dhcp4_pool_BDEL BEFORE DELETE ON dhcp4_pool FOR EACH ROW +-- Edit trigger body code below this line. Do not edit lines above this one +BEGIN +DELETE FROM dhcp4_options WHERE scope_id = 5 AND pool_id = OLD.id; +END +$$ +DELIMITER ; + +# Create table dhcp6_server +# +CREATE TABLE IF NOT EXISTS dhcp6_server ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + tag VARCHAR(64) NOT NULL, + description TEXT, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (id), + UNIQUE KEY dhcp6_server_tag_UNIQUE (tag), + KEY key_dhcp6_server_modification_ts (modification_ts) +) ENGINE=InnoDB; + +# Special server entry meaning "all servers". This refers to +# the configuration entries owned by all servers. +INSERT INTO dhcp6_server(id, tag, description, modification_ts) + VALUES(1, "all", "special type: all servers", NOW()); + +# Create table dhcp6_audit +# +CREATE TABLE IF NOT EXISTS dhcp6_audit ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + object_type VARCHAR(256) NOT NULL, + object_id BIGINT(20) UNSIGNED NOT NULL, + modification_type TINYINT(1) NOT NULL, + modification_ts TIMESTAMP NOT NULL, + log_message TEXT, + PRIMARY KEY (id), + KEY key_dhcp6_audit_modification_ts (modification_ts), + KEY fk_dhcp6_audit_modification_type (modification_type), + CONSTRAINT fk_dhcp6_audit_modification_type FOREIGN KEY (modification_type) + REFERENCES modification (id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp6_global_parameter +# +CREATE TABLE IF NOT EXISTS dhcp6_global_parameter ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + name VARCHAR(128) NOT NULL, + value LONGTEXT NOT NULL, + modification_ts timestamp NOT NULL, + PRIMARY KEY (id), + KEY key_dhcp6_global_parameter_modification_ts (modification_ts), + KEY key_dhcp6_global_parameter_name (name) +) ENGINE=InnoDB; + +# Create table dhcp6_global_parameter_server +# M-to-M cross-reference between global parameters and servers +# +CREATE TABLE IF NOT EXISTS dhcp6_global_parameter_server ( + parameter_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (parameter_id, server_id), + KEY fk_dhcp6_global_parameter_server_server_id (server_id), + KEY key_dhcp6_global_parameter_server (modification_ts), + CONSTRAINT fk_dhcp6_global_parameter_server_parameter_id FOREIGN KEY (parameter_id) + REFERENCES dhcp6_global_parameter (id) + ON DELETE CASCADE ON UPDATE NO ACTION, + CONSTRAINT fk_dhcp6_global_parameter_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp6_option_def +# +CREATE TABLE IF NOT EXISTS dhcp6_option_def ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + code SMALLINT UNSIGNED NOT NULL, + name VARCHAR(128) NOT NULL, + space VARCHAR(128) NOT NULL, + type TINYINT UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + is_array TINYINT(1) NOT NULL, + encapsulate VARCHAR(128) NOT NULL, + record_types VARCHAR(512) DEFAULT NULL, + user_context LONGTEXT, + PRIMARY KEY (id), + KEY key_dhcp6_option_def_modification_ts (modification_ts), + KEY key_dhcp6_option_def_code_space (code, space) +) ENGINE=InnoDB; + +# Create table dhcp6_option_def_server +# M-to-M cross-reference between option definitions and servers +# +CREATE TABLE IF NOT EXISTS dhcp6_option_def_server ( + option_def_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (option_def_id, server_id), + KEY fk_dhcp6_option_def_server_server_id_idx (server_id), + KEY key_dhcp6_option_def_server_modification_ts (modification_ts), + 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, + CONSTRAINT fk_dhcp6_option_def_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp6_shared_network +# +CREATE TABLE dhcp6_shared_network ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + name VARCHAR(128) NOT NULL, + client_class VARCHAR(128) DEFAULT NULL, + interface VARCHAR(128) DEFAULT NULL, + modification_ts TIMESTAMP NOT NULL, + preferred_lifetime INT(10) DEFAULT NULL, + rapid_commit TINYINT(1) NOT NULL DEFAULT '1', + rebind_timer INT(10) DEFAULT NULL, + relay LONGTEXT DEFAULT NULL, + renew_timer INT(10) DEFAULT NULL, + require_client_classes LONGTEXT, + reservation_mode TINYINT(3) NOT NULL DEFAULT '3', + user_context LONGTEXT, + valid_lifetime INT(10) DEFAULT NULL, + PRIMARY KEY (id), + UNIQUE KEY name_UNIQUE (name), + KEY key_dhcp6_shared_network_modification_ts (modification_ts) +) ENGINE=InnoDB; + +# Create table dhcp6_shared_network_server +# M-to-M cross-reference between shared networks and servers +# +CREATE TABLE IF NOT EXISTS dhcp6_shared_network_server ( + shared_network_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + KEY key_dhcp6_shared_network_server_modification_ts (modification_ts), + KEY fk_dhcp6_shared_network_server_server_id_idx (server_id), + KEY fk_dhcp6_shared_network_server_shared_network_id (shared_network_id), + CONSTRAINT fk_dhcp6_shared_network_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION, + 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 +) ENGINE=InnoDB; + +# Create table dhcp6_subnet +# +CREATE TABLE dhcp6_subnet ( + subnet_id int(10) UNSIGNED NOT NULL, + subnet_prefix VARCHAR(64) NOT NULL, + client_class VARCHAR(128) DEFAULT NULL, + interface VARCHAR(128) DEFAULT NULL, + modification_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + preferred_lifetime INT(10) DEFAULT NULL, + rapid_commit TINYINT(1) NOT NULL DEFAULT '1', + rebind_timer INT(10) DEFAULT NULL, + relay LONGTEXT DEFAULT NULL, + renew_timer INT(10) DEFAULT NULL, + require_client_classes LONGTEXT, + reservation_mode TINYINT(3) NOT NULL DEFAULT '3', + shared_network_name VARCHAR(128) DEFAULT NULL, + user_context LONGTEXT, + valid_lifetime INT(10) DEFAULT NULL, + PRIMARY KEY (subnet_id), + UNIQUE KEY subnet6_subnet_prefix (subnet_prefix), + KEY fk_dhcp6_subnet_shared_network (shared_network_name), + KEY key_dhcp6_subnet_modification_ts (modification_ts), + CONSTRAINT fk_dhcp6_subnet_shared_network FOREIGN KEY (shared_network_name) + REFERENCES dhcp6_shared_network (name) + ON DELETE SET NULL ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp6_subnet_server +# M-to-M cross-reference between subnets and servers +# +CREATE TABLE dhcp6_subnet_server ( + subnet_id INT(10) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (subnet_id, server_id), + KEY fk_dhcp6_subnet_server_server_id (server_id), + KEY key_dhcp6_subnet_server_modification_ts (modification_ts), + CONSTRAINT fk_dhcp6_subnet_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT fk_dhcp6_subnet_server_subnet_id FOREIGN KEY (subnet_id) + REFERENCES dhcp6_subnet (subnet_id) + ON DELETE CASCADE ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create table dhcp6_pd_pool +# +CREATE TABLE IF NOT EXISTS dhcp6_pd_pool ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + prefix VARCHAR(45) NOT NULL, + prefix_length TINYINT(3) NOT NULL, + delegated_prefix_length TINYINT(3) NOT NULL, + dhcp6_subnet_id INT(10) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (id), + KEY fk_dhcp6_pd_pool_subnet_id (dhcp6_subnet_id), + KEY key_dhcp6_pd_pool_modification_ts (modification_ts), + CONSTRAINT fk_dhcp6_pd_pool_subnet_id FOREIGN KEY (dhcp6_subnet_id) + REFERENCES dhcp6_subnet (subnet_id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +# Create table dhcp6_pool +# +CREATE TABLE IF NOT EXISTS dhcp6_pool ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + start_address VARCHAR(45) NOT NULL, + end_address VARCHAR(45) NOT NULL, + dhcp6_subnet_id INT(10) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (id), + KEY fk_dhcp6_pool_subnet_id (dhcp6_subnet_id), + KEY key_dhcp6_pool_modification_ts (modification_ts), + CONSTRAINT fk_dhcp6_pool_subnet_id FOREIGN KEY (dhcp6_subnet_id) + REFERENCES dhcp6_subnet (subnet_id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +# Modify the primary key to BINGINT as other tables have. +ALTER TABLE dhcp6_options MODIFY option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT; + +# Add configuration backend specific columns. +ALTER TABLE dhcp6_options + ADD COLUMN shared_network_name VARCHAR(128) DEFAULT NULL, + ADD COLUMN pool_id BIGINT(20) UNSIGNED DEFAULT NULL, + ADD COLUMN pd_pool_id BIGINT(20) UNSIGNED DEFAULT NULL, + ADD COLUMN modification_ts TIMESTAMP NOT NULL; + +# Create table dhcp6_options_server +# M-to-M cross-reference between options and servers +# +CREATE TABLE IF NOT EXISTS dhcp6_options_server ( + option_id BIGINT(20) UNSIGNED NOT NULL, + server_id BIGINT(20) UNSIGNED NOT NULL, + modification_ts TIMESTAMP NOT NULL, + PRIMARY KEY (option_id, server_id), + KEY fk_dhcp6_options_server_server_id_idx (server_id), + KEY key_dhcp6_options_server_modification_ts (modification_ts), + CONSTRAINT fk_dhcp6_options_server_option_id FOREIGN KEY (option_id) + REFERENCES dhcp6_options (option_id) + ON DELETE CASCADE ON UPDATE NO ACTION, + CONSTRAINT fk_dhcp6_options_server_server_id FOREIGN KEY (server_id) + REFERENCES dhcp6_server (id) + ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB; + +# Create trigger which removes pool specific options upon removal of +# the pool. +DELIMITER $$ +CREATE TRIGGER dhcp6_pool_BDEL BEFORE DELETE ON dhcp6_pool FOR EACH ROW +-- Edit trigger body code below this line. Do not edit lines above this one +BEGIN +DELETE FROM dhcp6_options WHERE scope_id = 5 AND pool_id = OLD.id; +END +$$ +DELIMITER ; + +# Update the schema version number +UPDATE schema_version +SET version = '7', minor = '0'; + +# This line concludes database upgrade to version 7.0. + +EOF |