path: root/src/share/database/scripts/mysql/upgrade_006.0_to_007.0.sh.in

#!/bin/sh

# Copyright (C) 2018-2023 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 test -f "@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
-- This line starts the schema upgrade to version 7.0.

# 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 the schema upgrade to version 7.0.

EOF