path: root/src/share/database/scripts/pgsql/upgrade_007_to_008.sh.in

#!/bin/sh

# Copyright (C) 2021-2022 Internet Systems Consortium, Inc. ("ISC")
#
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.

# shellcheck disable=SC1091
# SC1091: Not following: ... was not specified as input (see shellcheck -x).

# Exit with error if commands exit with non-zero and if undefined variables are
# used.
set -eu

# shellcheck disable=SC2034
# SC2034: ... appears unused. Verify use (or export if used externally).
prefix="@prefix@"

# Include utilities. Use installed version if available and
# use build version if it isn't.
if [ -e @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh ]; then
    . "@datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh"
else
    . "@abs_top_builddir@/src/bin/admin/admin-utils.sh"
fi

VERSION=$(pgsql_version "$@")

if [ "$VERSION" != "7.0" ]; then
    printf 'This script upgrades 7.0 to 8.0. '
    printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}"
    exit 0
fi

psql "$@" >/dev/null <<EOF
START TRANSACTION;

-- This starts schema update to 8.0. It adds a few missing elements for CB and
-- functions for kea-admin's lease-dump and lease-upload commands.

-- -----------------------------------------------------------------------
-- Extend the table holding DHCPv4 option definitions with a nullable
-- column matching option defintions with client classes.
-- -----------------------------------------------------------------------
ALTER TABLE dhcp4_option_def
    ADD COLUMN class_id BIGINT NULL DEFAULT NULL;

ALTER TABLE dhcp4_option_def
  ADD CONSTRAINT fk_dhcp4_option_def_client_class_id
    FOREIGN KEY (class_id)
    REFERENCES dhcp4_client_class (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- -----------------------------------------------------------------------
-- Extend the table holding DHCPv6 option definitions with a nullable
-- column matching option defintions with client classes.
-- -----------------------------------------------------------------------
ALTER TABLE dhcp6_option_def
    ADD COLUMN class_id BIGINT NULL DEFAULT NULL;

ALTER TABLE dhcp6_option_def
  ADD CONSTRAINT fk_dhcp6_option_def_client_class_id
    FOREIGN KEY (class_id)
    REFERENCES dhcp6_client_class (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- -----------------------------------------------------------------------
-- Add missing preferred_lifetime columns to dhcp6_client_class table.
-- -----------------------------------------------------------------------
ALTER TABLE dhcp6_client_class
    ADD COLUMN preferred_lifetime BIGINT DEFAULT NULL,
    ADD COLUMN min_preferred_lifetime BIGINT DEFAULT NULL,
    ADD COLUMN max_preferred_lifetime BIGINT DEFAULT NULL;

-- -----------------------------------------------------------------------
-- Add option scopes
-- -----------------------------------------------------------------------
-- Add scope for shared network specific options.
INSERT INTO dhcp_option_scope (scope_id, scope_name)
    VALUES(4, 'shared-network');

-- Add scope for pool specific options.
INSERT INTO dhcp_option_scope (scope_id, scope_name)
    VALUES(5, 'pool');

-- Add scope for PD pool specific options.
INSERT INTO dhcp_option_scope (scope_id, scope_name)
    VALUES(6, 'pd-pool');


-- Drop the existing function, createOptionAuditDHCP6 so we can replace it
-- with one that has slightly different arguments.
DROP FUNCTION IF EXISTS createOptionAuditDHCP6(modification_type VARCHAR(32),
                                               scope_id SMALLINT, option_id INT, subnet_id BIGINT,
                                               host_id INT, network_name VARCHAR(128),
                                               pool_id BIGINT, pd_pool_id BIGINT,
                                               modification_ts TIMESTAMP WITH TIME ZONE);

-- -----------------------------------------------------
--
-- New version of the createOptionAuditDHCP6 stored
-- procedure which updates modification timestamp of
-- a parent object when an option is modified.
--
-- The following parameters are passed to the procedure:
-- - modification_type: "create", "update" or "delete"
-- - scope_id: identifier of the option scope, e.g.
--   global, subnet specific etc. See dhcp_option_scope
--   for specific values.
-- - option_id: identifier of the option.
-- - subnet_id: identifier of the subnet if the option
--   belongs to the subnet.
-- - host_id: identifier of the host if the option
-- - belongs to the host.
-- - network_name: shared network name if the option
--   belongs to the shared network.
-- - pool_id: identifier of the pool if the option
--   belongs to the pool.
-- - pd_pool_id: identifier of the pool if the option
--   belongs to the pd pool.
-- - modification_ts: modification timestamp of the
--   option.
--   Some arguments are prefixed with "p_" to avoid ambiguity
--   with column names in SQL statements. PostgreSQL does not
--   allow table aliases to be used with column names in update
--   set expressions.
-- -----------------------------------------------------
CREATE OR REPLACE FUNCTION createOptionAuditDHCP6(modification_type VARCHAR(32),
                                                  scope_id SMALLINT,
                                                  option_id INT,
                                                  p_subnet_id BIGINT,
                                                  host_id INT,
                                                  network_name VARCHAR(128),
                                                  pool_id BIGINT,
                                                  pd_pool_id BIGINT,
                                                  p_modification_ts TIMESTAMP WITH TIME ZONE)
RETURNS VOID
LANGUAGE plpgsql
AS \$\$
DECLARE
    -- These variables will hold shared network id and subnet id that
    -- we will select.
    snid VARCHAR(128);
    sid BIGINT;
    cascade_transaction BOOLEAN := false;

BEGIN
    -- Cascade transaction flag is set to true to prevent creation of
    -- the audit entries for the options when the options are
    -- created as part of the parent object creation or update.
    -- For example: when the option is added as part of the subnet
    -- addition, the cascade transaction flag is equal to true. If
    -- the option is added into the existing subnet the cascade
    -- transaction is equal to false. Note that depending on the option
    -- scope the audit entry will contain the object_type value
    -- of the parent object to cause the server to replace the
    -- entire subnet. The only case when the object_type will be
    -- set to 'dhcp6_options' is when a global option is added.
    -- Global options do not have the owner.
    cascade_transaction := get_session_boolean('kea.cascade_transaction');
    IF cascade_transaction = false THEN
        -- todo: host manager hasn't been updated to use audit
        -- mechanisms so ignore host specific options for now.
        IF scope_id = 0 THEN
            -- If a global option is added or modified, create audit
            -- entry for the 'dhcp6_options' table.
            PERFORM createAuditEntryDHCP6('dhcp6_options', option_id, modification_type);
        ELSEIF scope_id = 1 THEN
            -- If subnet specific option is added or modified, update
            -- the modification timestamp of this subnet to allow the
            -- servers to refresh the subnet information. This will
            -- also result in creating an audit entry for this subnet.
            UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
                WHERE subnet_id = p_subnet_id;
        ELSEIF scope_id = 4 THEN
            -- If shared network specific option is added or modified,
            -- update the modification timestamp of this shared network
            -- to allow the servers to refresh the shared network
            -- information. This will also result in creating an
            -- audit entry for this shared network.
           SELECT id INTO snid FROM dhcp6_shared_network WHERE name = network_name LIMIT 1;
           UPDATE dhcp6_shared_network SET modification_ts = p_modification_ts
                WHERE id = snid;
        ELSEIF scope_id = 5 THEN
            -- If pool specific option is added or modified, update
            -- the modification timestamp of the owning subnet.
            SELECT dhcp6_pool.subnet_id INTO sid FROM dhcp6_pool WHERE id = pool_id;
            UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
                WHERE subnet_id = sid;
        ELSEIF scope_id = 6 THEN
            -- If pd pool specific option is added or modified, create
            -- audit entry for the subnet which this pool belongs to.
            SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pool_id;
            UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
                WHERE subnet_id = sid;
        END IF;
    END IF;
    RETURN;
END;\$\$;

-- Fix mangled constraints on dhcp4_subnet_server table.
ALTER TABLE dhcp4_subnet_server
    DROP CONSTRAINT fk_dhcp6_subnet_server_server_id,
    ADD CONSTRAINT fk_dhcp4_subnet_server_server_id
        FOREIGN KEY (server_id) REFERENCES dhcp4_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
    DROP CONSTRAINT fk_dhcp6_subnet_server_subnet_id,
    ADD CONSTRAINT fk_dhcp4_subnet_server_subnet_id
        FOREIGN KEY (subnet_id) REFERENCES dhcp4_subnet (subnet_id) ON DELETE CASCADE ON UPDATE NO ACTION;

-- Add missing foreign key indexes. PostgreSQL does not automatically create indexes for
-- foreign key constraints. These have been added using the basic guideline:
--
-- If the constraint does not reference a static table (e.g. parameter_data_type),
-- and the referencing column is not the primary key or the first
-- column in the primary key, and does not already have an index, then an index
-- should be added to the table for the referencing column.
--
-- dhcp6_global_parameter_server
CREATE INDEX  fk_dhcp6_global_parameter_server_server_id ON dhcp6_global_parameter_server (server_id);

-- dhcp6_options
-- Missing foreign key constraint and indexes
ALTER TABLE dhcp6_options ADD CONSTRAINT fk_dhcp6_options_subnet
    FOREIGN KEY (dhcp6_subnet_id)
        REFERENCES dhcp6_subnet(subnet_id)
        ON DELETE CASCADE ON UPDATE CASCADE;

CREATE INDEX fk_dhcp6_options_subnet ON dhcp6_options (dhcp6_subnet_id);
CREATE INDEX fk_dhcp6_options_pd_pool ON dhcp6_options (pd_pool_id);
CREATE INDEX fk_dhcp6_options_pool ON dhcp6_options (pool_id);
CREATE INDEX fk_dhcp6_options_shared_network ON dhcp6_options (shared_network_name);

-- dhcp6_option_def_server
-- Missing foreign key constraints and index
ALTER TABLE dhcp6_option_def_server
    ADD CONSTRAINT fk_dhcp6_option_def_server_option_def_id FOREIGN KEY (option_def_id)
        REFERENCES dhcp6_option_def (id) ON DELETE CASCADE ON UPDATE NO ACTION,
    ADD CONSTRAINT fk_dhcp6_option_def_server_server_id FOREIGN KEY (server_id)
        REFERENCES dhcp6_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX fk_dhcp6_option_def_server_server_id ON dhcp6_option_def_server (server_id);

-- dhcp6_option_def
CREATE INDEX fk_dhcp6_option_def_client_class_id ON dhcp6_option_def (class_id);

-- dhcp4_global_parameter_server
CREATE INDEX  fk_dhcp4_global_parameter_server_server_id ON dhcp4_global_parameter_server (server_id);

-- dhcp4_options
-- Missing foreign key constraint and indexes
ALTER TABLE dhcp4_options ADD CONSTRAINT fk_dhcp4_options_subnet
    FOREIGN KEY (dhcp4_subnet_id)
        REFERENCES dhcp4_subnet(subnet_id)
        ON DELETE CASCADE ON UPDATE CASCADE;

CREATE INDEX fk_dhcp4_options_subnet ON dhcp4_options (dhcp4_subnet_id);
CREATE INDEX fk_dhcp4_options_pool ON dhcp4_options (pool_id);
CREATE INDEX fk_dhcp4_options_shared_network ON dhcp4_options (shared_network_name);

-- dhcp4_option_def_server
-- Missing foreign key constraints and index
ALTER TABLE dhcp4_option_def_server
    ADD CONSTRAINT fk_dhcp4_option_def_server_option_def_id FOREIGN KEY (option_def_id)
        REFERENCES dhcp4_option_def (id) ON DELETE CASCADE ON UPDATE NO ACTION,
    ADD CONSTRAINT fk_dhcp4_option_def_server_server_id FOREIGN KEY (server_id)
        REFERENCES dhcp4_server (id) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE INDEX fk_dhcp4_option_def_server_server_id ON dhcp4_option_def_server (server_id);

-- dhcp4_option_def
CREATE INDEX fk_dhcp4_option_def_client_class_id ON dhcp4_option_def (class_id);

-- Create a function that separates groups of two hexadecimals
-- with colons.
CREATE OR REPLACE FUNCTION colonSeparatedHex(hex TEXT)
RETURNS TEXT
AS \$\$
DECLARE
    i INT := 3;
    length INT := LENGTH(hex);
    output TEXT;
BEGIN
    -- Add a leading zero if the first octet has a single hexadecimal character.
    IF MOD(length, 2) = 1 THEN
        hex := CONCAT('0', hex);
        length := length + 1;
    END IF;

    -- Start with the first octet.
    output := SUBSTR(hex, 1, 2);

    -- Add one octet at a time and a leading colon with each.
    WHILE i < length LOOP
        output := CONCAT(output, ':', SUBSTR(hex, i, 2));
        i := i + 2;
    END LOOP;

    -- Memfile uses lowercase hexadecimals.
    output := LOWER(output);

    RETURN output;
END
\$\$ LANGUAGE plpgsql;

-- Modify the function to output a memfile-ready CSV file.
-- Some columns that are SMALLINT in the lease4 table have their type promoted
-- to INT in the declaration of this function for backwards compatibility with
-- PostgreSQL versions.
DROP FUNCTION IF EXISTS lease4DumpData();
CREATE OR REPLACE FUNCTION lease4DumpData()
RETURNS TABLE (
    address INET,
    hwaddr VARCHAR,
    client_id VARCHAR,
    valid_lifetime BIGINT,
    expire BIGINT,
    subnet_id BIGINT,
    fqdn_fwd INT,
    fqdn_rev INT,
    hostname VARCHAR,
    state INT8,
    user_context VARCHAR
) AS \$\$
    SELECT
        ('0.0.0.0'::inet + address),
        colonSeparatedHex(encode(hwaddr, 'hex')),
        colonSeparatedHex(encode(client_id, 'hex')),
        valid_lifetime,
        extract(epoch from expire)::bigint,
        subnet_id,
        fqdn_fwd::int,
        fqdn_rev::int,
        replace(hostname, ',', '&#x2c'),
        state,
        replace(user_context, ',', '&#x2c')
    FROM lease4
    ORDER BY address;
\$\$ LANGUAGE SQL;

-- hwtype and hwaddr_source need to be last to match memfile format.
DROP FUNCTION IF EXISTS lease6DumpHeader();
CREATE OR REPLACE FUNCTION lease6DumpHeader()
RETURNS TEXT AS \$\$
    SELECT CAST('address,duid,valid_lifetime,expire,subnet_id,pref_lifetime,lease_type,iaid,prefix_len,fqdn_fwd,fqdn_rev,hostname,hwaddr,state,user_context,hwtype,hwaddr_source' AS TEXT) AS result;
\$\$ LANGUAGE SQL;

-- Modify the function to output a memfile-ready CSV file.
-- Some columns that are SMALLINT in the lease6 table have their type promoted
-- to INT in the declaration of this function for backwards compatibility with
-- PostgreSQL versions.
DROP FUNCTION IF EXISTS lease6DumpData();
CREATE OR REPLACE FUNCTION lease6DumpData()
RETURNS TABLE (
    address VARCHAR,
    duid VARCHAR,
    valid_lifetime BIGINT,
    expire BIGINT,
    subnet_id BIGINT,
    pref_lifetime BIGINT,
    lease_type SMALLINT,
    iaid INT,
    prefix_len SMALLINT,
    fqdn_fwd INT,
    fqdn_rev INT,
    hostname VARCHAR,
    hwaddr VARCHAR,
    state INT8,
    user_context VARCHAR,
    hwtype SMALLINT,
    hwaddr_source SMALLINT
) AS \$\$
    SELECT
        address,
        colonSeparatedHex(encode(duid, 'hex')),
        valid_lifetime,
        extract(epoch from expire)::bigint,
        subnet_id,
        pref_lifetime,
        lease_type,
        iaid,
        prefix_len,
        fqdn_fwd::int,
        fqdn_rev::int,
        replace(hostname, ',', '&#x2c'),
        colonSeparatedHex(encode(hwaddr, 'hex')),
        state,
        replace(user_context, ',', '&#x2c'),
        hwtype,
        hwaddr_source
    FROM lease6
    ORDER BY address;
\$\$ LANGUAGE SQL;

-- Create a procedure that inserts a v4 lease from memfile data.
-- Some columns that are SMALLINT in the lease4 table have their type promoted
-- to INT in the declaration of this function for backwards compatibility with
-- PostgreSQL versions.
CREATE OR REPLACE FUNCTION lease4Upload(
    IN address VARCHAR,
    IN hwaddr VARCHAR,
    IN client_id VARCHAR,
    IN valid_lifetime BIGINT,
    IN expire BIGINT,
    IN subnet_id BIGINT,
    IN fqdn_fwd INT,
    IN fqdn_rev INT,
    IN hostname VARCHAR,
    IN state INT8,
    IN user_context VARCHAR
) RETURNS VOID AS \$\$
BEGIN
    INSERT INTO lease4 (
        address,
        hwaddr,
        client_id,
        valid_lifetime,
        expire,
        subnet_id,
        fqdn_fwd,
        fqdn_rev,
        hostname,
        state,
        user_context
    ) VALUES (
        address::inet - '0.0.0.0'::inet,
        decode(replace(hwaddr, ':', ''), 'hex'),
        decode(replace(client_id, ':', ''), 'hex'),
        valid_lifetime,
        to_timestamp(expire),
        subnet_id,
        fqdn_fwd::int::boolean,
        fqdn_rev::int::boolean,
        replace(hostname, '&#x2c', ','),
        state,
        replace(user_context, '&#x2c', ',')
    );
END
\$\$ LANGUAGE plpgsql;

-- Create a procedure that inserts a v6 lease from memfile data.
-- Some columns that are SMALLINT in the lease6 table have their type promoted
-- to INT in the declaration of this function for backwards compatibility with
-- PostgreSQL versions.
CREATE OR REPLACE FUNCTION lease6Upload(
    IN address VARCHAR,
    IN duid VARCHAR,
    IN valid_lifetime BIGINT,
    IN expire BIGINT,
    IN subnet_id BIGINT,
    IN pref_lifetime BIGINT,
    IN lease_type INT,
    IN iaid INT,
    IN prefix_len INT,
    IN fqdn_fwd INT,
    IN fqdn_rev INT,
    IN hostname VARCHAR,
    IN hwaddr VARCHAR,
    IN state INT8,
    IN user_context VARCHAR,
    IN hwtype INT,
    IN hwaddr_source INT
) RETURNS VOID AS \$\$
BEGIN
    INSERT INTO lease6 (
        address,
        duid,
        valid_lifetime,
        expire,
        subnet_id,
        pref_lifetime,
        lease_type,
        iaid,
        prefix_len,
        fqdn_fwd,
        fqdn_rev,
        hostname,
        hwaddr,
        state,
        user_context,
        hwtype,
        hwaddr_source
    ) VALUES (
        address,
        decode(replace(duid, ':', ''), 'hex'),
        valid_lifetime,
        to_timestamp(expire),
        subnet_id,
        pref_lifetime,
        lease_type,
        iaid,
        prefix_len,
        fqdn_fwd::int::boolean,
        fqdn_rev::int::boolean,
        replace(hostname, '&#x2c', ','),
        decode(replace(hwaddr, ':', ''), 'hex'),
        state,
        replace(user_context, '&#x2c', ','),
        hwtype,
        hwaddr_source
    );
END
\$\$ LANGUAGE plpgsql;

-- Update the schema version number.
UPDATE schema_version
    SET version = '8', minor = '0';

-- Commit the script transaction.
COMMIT;

EOF