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

#!/bin/sh

# Copyright (C) 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" != "8.0" ]; then
    printf 'This script upgrades 8.0 to 9.0. '
    printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}"
    exit 0
fi

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

-- This starts schema update to 9.0.

-- Add missing cascade to constraint on dhcp4/6_subnet_server tables.
ALTER TABLE dhcp4_subnet_server
    DROP CONSTRAINT fk_dhcp4_subnet_server_server_id,
    ADD CONSTRAINT fk_dhcp4_subnet_server_server_id
        FOREIGN KEY (server_id) REFERENCES dhcp4_server (id) ON DELETE CASCADE ON UPDATE CASCADE,
    DROP CONSTRAINT fk_dhcp4_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 CASCADE;

ALTER TABLE dhcp6_subnet_server
    DROP CONSTRAINT fk_dhcp6_subnet_server_server_id,
    ADD CONSTRAINT fk_dhcp6_subnet_server_server_id
        FOREIGN KEY (server_id) REFERENCES dhcp6_server (id) ON DELETE CASCADE ON UPDATE CASCADE,
    DROP CONSTRAINT fk_dhcp6_subnet_server_subnet_id,
    ADD CONSTRAINT fk_dhcp6_subnet_server_subnet_id
        FOREIGN KEY (subnet_id) REFERENCES dhcp6_subnet (subnet_id) ON DELETE CASCADE ON UPDATE CASCADE;

-- Fix constraint typo on dhcp4_option_def_server
ALTER TABLE dhcp4_option_def_server
    DROP CONSTRAINT dhcp4_option_def_server_option_def_id_fkey,
    ADD CONSTRAINT dhcp4_option_def_server_option_def_id_fkey
        FOREIGN KEY (option_def_id) REFERENCES dhcp4_option_def(id) ON DELETE CASCADE;

-- DROP shared-network ADEL triggers that should not exist.
DROP TRIGGER IF EXISTS dhcp4_shared_network_ADEL on dhcp4_shared_network CASCADE;
DROP TRIGGER IF EXISTS dhcp6_shared_network_ADEL on dhcp6_shared_network CASCADE;

-- Replace createOptionAuditDHCP4() with a version that has local variable
-- snid correctly declared as a BIGINT.
--
-- -----------------------------------------------------
--
-- 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.
-- - p_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.
-- - p_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 createOptionAuditDHCP4(modification_type VARCHAR,
                                                  scope_id SMALLINT,
                                                  option_id INT,
                                                  p_subnet_id BIGINT,
                                                  host_id INT,
                                                  network_name VARCHAR,
                                                  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 BIGINT;
    sid BIGINT;
    cascade_transaction BOOLEAN;
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 'dhcp4_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 'dhcp4_options' table.
            PERFORM createAuditEntryDHCP4('dhcp4_options', option_id, modification_type);
        ELSEIF scope_id = 1 THEN
            -- If subnet specific option is added or modified, update
            -- the modification timestamp of this subnet to allow the
            -- servers to refresh the subnet information. This will
            -- also result in creating an audit entry for this subnet.
            UPDATE dhcp4_subnet 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 dhcp4_shared_network WHERE name = network_name LIMIT 1;
           UPDATE dhcp4_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 dhcp4_pool.subnet_id INTO sid FROM dhcp4_pool WHERE id = pool_id;
            UPDATE dhcp4_subnet SET modification_ts = p_modification_ts
                WHERE subnet_id = sid;
        END IF;
    END IF;
    RETURN;
END;\$\$;

-- Replace createOptionAuditDHCP6() with a version that has local variable
-- snid correctly declared as a BIGINT.
--
-- -----------------------------------------------------
--
-- 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.
-- - p_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.
-- - p_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,
                                                  scope_id SMALLINT,
                                                  option_id INT,
                                                  p_subnet_id BIGINT,
                                                  host_id INT,
                                                  network_name VARCHAR,
                                                  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 BIGINT;
    sid BIGINT;
    cascade_transaction BOOLEAN;
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;\$\$;

-- Returns the epoch GMT time in second from a timestamp with time zone
--
-- param input_ts timestamp value to convert
-- return a BIGINT containing the number of seconds since the epoch in GMT.
CREATE OR REPLACE FUNCTION gmt_epoch(input_ts TIMESTAMP WITH TIME ZONE)
RETURNS BIGINT
AS \$\$
DECLARE
    gmt_epoch BIGINT;
BEGIN
    SELECT (extract(epoch from input_ts) + extract(timezone from input_ts))::BIGINT INTO gmt_epoch;
    RETURN gmt_epoch;
    EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'gmt_epoch(%) : failed, sqlstate: %', input_ts, sqlstate;
END;\$\$
LANGUAGE plpgsql;

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

-- Commit the script transaction.
COMMIT;

EOF