path: root/src/share/database/scripts/mysql/upgrade_013_to_014.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

# Check version.
version=$(mysql_version "${@}")
if test "${version}" != "13.0"; then
    printf 'This script upgrades 13.0 to 14.0. '
    printf 'Reported version is %s. Skipping upgrade.\n' "${version}"
    exit 0
fi

# Get the schema name from database argument. We need this to
# query information_schema for the right database.
for arg in "${@}"
do
    if ! printf '%s' "${arg}" | grep -Eq '^\-\-'
    then
        schema="$arg"
        break
    fi
done

# Make sure we have the schema.
if [ -z "$schema" ]
then
    printf "Could not find database schema name in cmd line args: %s\n" "${*}"
    exit 255
fi

mysql "$@" <<EOF
-- This line starts the schema upgrade to version 14.

-- Modify shared-network-name foreign key constraint on dhcp4_subnet to not perform
-- the update when the network is deleted the cascaded update will not execute
-- dhcp4_subnet update trigger leaving the updated subnets without audit_entries.
ALTER TABLE dhcp4_subnet
    DROP FOREIGN KEY fk_dhcp4_subnet_shared_network;

ALTER TABLE dhcp4_subnet
    ADD CONSTRAINT fk_dhcp4_subnet_shared_network FOREIGN KEY (shared_network_name)
        REFERENCES dhcp4_shared_network (name)
        ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Modify BEFORE delete trigger on dhcp4_shared_network to explicitly
-- update dhcp4_subnets. This ensures there are audit entries for updated
-- subnets.
DROP TRIGGER dhcp4_shared_network_BDEL;

DELIMITER $$
CREATE TRIGGER dhcp4_shared_network_BDEL BEFORE DELETE ON dhcp4_shared_network
    FOR EACH ROW
    BEGIN
        CALL createAuditEntryDHCP4('dhcp4_shared_network', OLD.id, "delete");
        -- In MySQL Foreign key constraint triggered updates will not cascade, so we explicitly
        -- update subnets first which should ensure they get audit entries.
        UPDATE dhcp4_subnet SET shared_network_name = NULL WHERE shared_network_name = OLD.name;
        DELETE FROM dhcp4_options WHERE shared_network_name = OLD.name;
    END $$
DELIMITER ;

-- Modify shared-network-name foreign key constraint on dhcp6_subnet to not perform
-- the update when the network is deleted the cascaded update will not execute
-- dhcp6_subnet update trigger leaving the updated subnets without audit_entries.
ALTER TABLE dhcp6_subnet
    DROP FOREIGN KEY fk_dhcp6_subnet_shared_network;

ALTER TABLE dhcp6_subnet
    ADD CONSTRAINT fk_dhcp6_subnet_shared_network FOREIGN KEY (shared_network_name)
        REFERENCES dhcp6_shared_network (name)
        ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Modify BEFORE delete trigger on dhcp6_shared_network to explicitly
-- update dhcp6_subnets. This ensures there are audit entries for updated
-- subnets.
DROP TRIGGER dhcp6_shared_network_BDEL;

DELIMITER $$
CREATE TRIGGER dhcp6_shared_network_BDEL BEFORE DELETE ON dhcp6_shared_network
    FOR EACH ROW
    BEGIN
        CALL createAuditEntryDHCP6('dhcp6_shared_network', OLD.id, "delete");
        -- In MySQL Foreign key constraint triggered updates will not cascade, so we explicitly
        -- update subnets first which should ensure they get audit entries.
        UPDATE dhcp6_subnet SET shared_network_name = NULL WHERE shared_network_name = OLD.name;
        DELETE FROM dhcp6_options WHERE shared_network_name = OLD.name;
    END $$
DELIMITER ;

-- Add user_context column to client class tables.
ALTER TABLE dhcp4_client_class ADD COLUMN user_context LONGTEXT NULL;
ALTER TABLE dhcp6_client_class ADD COLUMN user_context LONGTEXT NULL;

-- Schema changes related to lease limiting start here. --

-- Recreate the triggers that update the leaseX_stat tables as stored procedures. --

DROP PROCEDURE IF EXISTS lease4_AINS_lease4_stat;
DELIMITER $$
CREATE PROCEDURE lease4_AINS_lease4_stat(IN new_state TINYINT,
                                         IN new_subnet_id INT UNSIGNED)
BEGIN
    IF new_state = 0 OR new_state = 1 THEN
        -- Update the state count if it exists.
        UPDATE lease4_stat SET leases = leases + 1
            WHERE subnet_id = new_subnet_id AND state = new_state;

        -- Insert the state count record if it does not exist.
        IF ROW_COUNT() <= 0 THEN
            INSERT INTO lease4_stat VALUES (new_subnet_id, new_state, 1);
        END IF;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease4_AUPD_lease4_stat;
DELIMITER $$
CREATE PROCEDURE lease4_AUPD_lease4_stat(IN old_state TINYINT,
                                         IN old_subnet_id INT UNSIGNED,
                                         IN new_state TINYINT,
                                         IN new_subnet_id INT UNSIGNED)
BEGIN
    IF old_subnet_id != new_subnet_id OR old_state != new_state THEN
        IF old_state = 0 OR old_state = 1 THEN
            -- Decrement the old state count if record exists.
            UPDATE lease4_stat
                SET leases = IF(leases > 0, leases - 1, 0)
                WHERE subnet_id = old_subnet_id AND state = old_state;
        END IF;

        IF new_state = 0 OR new_state = 1 THEN
            -- Increment the new state count if record exists.
            UPDATE lease4_stat SET leases = leases + 1
                WHERE subnet_id = new_subnet_id AND state = new_state;

            -- Insert new state record if it does not exist.
            IF ROW_COUNT() <= 0 THEN
                INSERT INTO lease4_stat VALUES (new_subnet_id, new_state, 1);
            END IF;
        END IF;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease4_ADEL_lease4_stat;
DELIMITER $$
CREATE PROCEDURE lease4_ADEL_lease4_stat(IN old_state TINYINT,
                                         IN old_subnet_id INT UNSIGNED)
BEGIN
    IF old_state = 0 OR old_state = 1 THEN
        -- Decrement the state count if record exists.
        UPDATE lease4_stat
            SET leases = IF(leases > 0, leases - 1, 0)
            WHERE subnet_id = old_subnet_id AND old_state = state;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease6_AINS_lease6_stat;
DELIMITER $$
CREATE PROCEDURE lease6_AINS_lease6_stat(IN new_state TINYINT,
                                         IN new_subnet_id INT UNSIGNED,
                                         IN new_lease_type TINYINT)
BEGIN
    IF new_state = 0 OR new_state = 1 THEN
        -- Update the state count if it exists.
        UPDATE lease6_stat SET leases = leases + 1
            WHERE subnet_id = new_subnet_id AND lease_type = new_lease_type
            AND state = new_state;

        -- Insert the state count record if it does not exist.
        IF ROW_COUNT() <= 0 THEN
            INSERT INTO lease6_stat VALUES (new_subnet_id, new_lease_type, new_state, 1);
        END IF;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease6_AUPD_lease6_stat;
DELIMITER $$
CREATE PROCEDURE lease6_AUPD_lease6_stat(IN old_state TINYINT,
                                         IN old_subnet_id INT UNSIGNED,
                                         IN old_lease_type TINYINT,
                                         IN new_state TINYINT,
                                         IN new_subnet_id INT UNSIGNED,
                                         IN new_lease_type TINYINT)
BEGIN
    IF old_subnet_id != new_subnet_id OR
       old_lease_type != new_lease_type OR
       old_state != new_state THEN
        IF old_state = 0 OR old_state = 1 THEN
            -- Decrement the old state count if record exists.
            UPDATE lease6_stat
                SET leases = IF(leases > 0, leases - 1, 0)
                WHERE subnet_id = old_subnet_id AND lease_type = old_lease_type
                AND state = old_state;
        END IF;

        IF new_state = 0 OR new_state = 1 THEN
            -- Increment the new state count if record exists
            UPDATE lease6_stat SET leases = leases + 1
                WHERE subnet_id = new_subnet_id AND lease_type = new_lease_type
                AND state = new_state;

            -- Insert new state record if it does not exist
            IF ROW_COUNT() <= 0 THEN
                INSERT INTO lease6_stat
                VALUES (new_subnet_id, new_lease_type, new_state, 1);
            END IF;
        END IF;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease6_ADEL_lease6_stat;
DELIMITER $$
CREATE PROCEDURE lease6_ADEL_lease6_stat(IN old_state TINYINT,
                                         IN old_subnet_id INT UNSIGNED,
                                         IN old_lease_type TINYINT)
BEGIN
    IF old_state = 0 OR old_state = 1 THEN
        -- Decrement the state count if record exists
        UPDATE lease6_stat
            SET leases = IF(leases > 0, leases - 1, 0)
            WHERE subnet_id = old_subnet_id AND lease_type = old_lease_type
            AND state = old_state;
    END IF;
END $$
DELIMITER ;

-- Create tables that contain the number of active leases. --

DROP TABLE IF EXISTS lease4_stat_by_client_class;
CREATE TABLE lease4_stat_by_client_class (
    client_class VARCHAR(128) NOT NULL PRIMARY KEY,
    leases BIGINT UNSIGNED NOT NULL
) ENGINE = InnoDB;

DROP TABLE IF EXISTS lease6_stat_by_client_class;
CREATE TABLE lease6_stat_by_client_class (
    client_class VARCHAR(128) NOT NULL,
    lease_type TINYINT NOT NULL,
    leases BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (client_class, lease_type),
    CONSTRAINT fk_lease6_stat_by_client_class_lease_type FOREIGN KEY (lease_type)
        REFERENCES lease6_types (lease_type)
) ENGINE = InnoDB;

-- Create procedures to be called for each row in after-event triggers for
-- INSERT, UPDATE and DELETE on lease tables.

DROP PROCEDURE IF EXISTS lease4_AINS_lease4_stat_by_client_class;
DELIMITER $$
CREATE PROCEDURE lease4_AINS_lease4_stat_by_client_class(IN new_state TINYINT,
                                                         IN new_user_context TEXT)
BEGIN
    -- Declarations
    DECLARE client_classes TEXT;
    DECLARE class VARCHAR(255);
    DECLARE length INT;
    DECLARE i INT;

    -- Ignore ERROR 3141 (22032) at line 1: Invalid JSON text in argument 1 to
    --      function json_extract: "The document is empty." at position 0.
    -- Ignore ERROR 4037 (HY000): Unexpected end of JSON text in argument 1 to function 'json_extract'
    -- These situations are handled with a propagating NULL result from JSON_EXTRACT.
    DECLARE CONTINUE HANDLER FOR 3141 BEGIN END;
    DECLARE CONTINUE HANDLER FOR 4037 BEGIN END;

    -- Only state 0 is needed for lease limiting.
    IF new_state = 0 THEN
        -- Dive into client classes.
        SET client_classes = JSON_EXTRACT(new_user_context, '$."ISC"."client-classes"');
        SET length = JSON_LENGTH(client_classes);

        -- Iterate through all the client classes and increment the lease count for each.
        SET i = 0;
        label: WHILE i < length DO
            SET class = JSON_UNQUOTE(JSON_EXTRACT(client_classes, CONCAT('\$[', i, ']')));

            -- Upsert to increment the lease count.
            UPDATE lease4_stat_by_client_class SET leases = leases + 1
                WHERE client_class = class;
            IF ROW_COUNT() = 0 THEN
                INSERT INTO lease4_stat_by_client_class VALUES (class, 1);
            END IF;

            SET i = i + 1;
        END WHILE label;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease4_AUPD_lease4_stat_by_client_class;
DELIMITER $$
CREATE PROCEDURE lease4_AUPD_lease4_stat_by_client_class(IN old_state TINYINT,
                                                         IN old_user_context TEXT,
                                                         IN new_state TINYINT,
                                                         IN new_user_context TEXT)
BEGIN
    -- Declarations
    DECLARE old_client_classes TEXT;
    DECLARE new_client_classes TEXT;
    DECLARE class VARCHAR(255);
    DECLARE length INT;
    DECLARE i INT;

    SET old_client_classes = JSON_EXTRACT(old_user_context, '$."ISC"."client-classes"');
    SET new_client_classes = JSON_EXTRACT(new_user_context, '$."ISC"."client-classes"');

    IF old_state != new_state OR old_client_classes != new_client_classes THEN
        -- Check if it's moving away from a counted state.
        IF old_state = 0 THEN
            -- Dive into client classes.
            SET length = JSON_LENGTH(old_client_classes);
            SET i = 0;
            label: WHILE i < length DO
                SET class = JSON_UNQUOTE(JSON_EXTRACT(old_client_classes, CONCAT('\$[', i, ']')));

                -- Decrement the lease count if the record exists.
                UPDATE lease4_stat_by_client_class SET leases = IF(leases > 0, leases - 1, 0)
                    WHERE client_class = class;

                SET i = i + 1;
            END WHILE label;
        END IF;

        -- Check if it's moving into a counted state.
        IF new_state = 0 THEN
            -- Dive into client classes.
            SET length = JSON_LENGTH(new_client_classes);
            SET i = 0;
            label: WHILE i < length DO
                SET class = JSON_UNQUOTE(JSON_EXTRACT(new_client_classes, CONCAT('\$[', i, ']')));

                -- Upsert to increment the lease count.
                UPDATE lease4_stat_by_client_class SET leases = leases + 1
                    WHERE client_class = class;
                IF ROW_COUNT() <= 0 THEN
                    INSERT INTO lease4_stat_by_client_class VALUES (class, 1);
                END IF;

                SET i = i + 1;
            END WHILE label;
        END IF;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease4_ADEL_lease4_stat_by_client_class;
DELIMITER $$
CREATE PROCEDURE lease4_ADEL_lease4_stat_by_client_class(IN old_state TINYINT,
                                                         IN old_user_context TEXT)
BEGIN
    -- Declarations
    DECLARE client_classes TEXT;
    DECLARE class VARCHAR(255);
    DECLARE length INT;
    DECLARE i INT;

    -- Ignore ERROR 3141 (22032) at line 1: Invalid JSON text in argument 1 to
    --      function json_extract: "The document is empty." at position 0.
    -- Ignore ERROR 4037 (HY000): Unexpected end of JSON text in argument 1 to function 'json_extract'
    -- These situations are handled with a propagating NULL result from JSON_EXTRACT.
    DECLARE CONTINUE HANDLER FOR 3141 BEGIN END;
    DECLARE CONTINUE HANDLER FOR 4037 BEGIN END;

    -- Only state 0 is accounted for in lease limiting.
    IF old_state = 0 THEN
        -- Dive into client classes.
        SET client_classes = JSON_EXTRACT(old_user_context, '$."ISC"."client-classes"');
        SET length = JSON_LENGTH(client_classes);

        SET i = 0;
        label: WHILE i < length DO
            SET class = JSON_UNQUOTE(JSON_EXTRACT(client_classes, CONCAT('\$[', i, ']')));

            -- Decrement the lease count if the record exists.
            UPDATE lease4_stat_by_client_class SET leases = IF(leases > 0, leases - 1, 0)
                WHERE client_class = class;

            SET i = i + 1;
        END WHILE label;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease6_AINS_lease6_stat_by_client_class;
DELIMITER $$
CREATE PROCEDURE lease6_AINS_lease6_stat_by_client_class(IN new_state TINYINT,
                                                         IN new_user_context TEXT,
                                                         IN new_lease_type TINYINT)
BEGIN
    -- Declarations
    DECLARE client_classes TEXT;
    DECLARE class VARCHAR(255);
    DECLARE length INT;
    DECLARE i INT;

    -- Ignore ERROR 3141 (22032) at line 1: Invalid JSON text in argument 1 to
    --      function json_extract: "The document is empty." at position 0.
    -- Ignore ERROR 4037 (HY000): Unexpected end of JSON text in argument 1 to function 'json_extract'
    -- These situations are handled with a propagating NULL result from JSON_EXTRACT.
    DECLARE CONTINUE HANDLER FOR 3141 BEGIN END;
    DECLARE CONTINUE HANDLER FOR 4037 BEGIN END;

    -- Only state 0 is needed for lease limiting.
    IF new_state = 0 THEN
        -- Dive into client classes.
        SET client_classes = JSON_EXTRACT(new_user_context, '$."ISC"."client-classes"');
        SET length = JSON_LENGTH(client_classes);

        SET i = 0;
        label: WHILE i < length DO
            SET class = JSON_UNQUOTE(JSON_EXTRACT(client_classes, CONCAT('\$[', i, ']')));

            -- Upsert to increment the lease count.
            UPDATE lease6_stat_by_client_class SET leases = leases + 1
                WHERE client_class = class AND lease_type = new_lease_type;
            IF ROW_COUNT() <= 0 THEN
                INSERT INTO lease6_stat_by_client_class VALUES (class, new_lease_type, 1);
            END IF;

            SET i = i + 1;
        END WHILE label;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease6_AUPD_lease6_stat_by_client_class;
DELIMITER $$
CREATE PROCEDURE lease6_AUPD_lease6_stat_by_client_class(IN old_state TINYINT,
                                                         IN old_user_context TEXT,
                                                         IN old_lease_type TINYINT,
                                                         IN new_state TINYINT,
                                                         IN new_user_context TEXT,
                                                         IN new_lease_type TINYINT)
BEGIN
    -- Declarations
    DECLARE old_client_classes TEXT;
    DECLARE new_client_classes TEXT;
    DECLARE class VARCHAR(255);
    DECLARE length INT;
    DECLARE i INT;

    SET old_client_classes = JSON_EXTRACT(old_user_context, '$."ISC"."client-classes"');
    SET new_client_classes = JSON_EXTRACT(new_user_context, '$."ISC"."client-classes"');

    IF old_state != new_state OR old_client_classes != new_client_classes OR old_lease_type != new_lease_type THEN
        -- Check if it's moving away from a counted state.
        IF old_state = 0 THEN
            -- Dive into client classes.
            SET length = JSON_LENGTH(old_client_classes);
            SET i = 0;
            label: WHILE i < length DO
                SET class = JSON_UNQUOTE(JSON_EXTRACT(old_client_classes, CONCAT('\$[', i, ']')));

                -- Decrement the lease count if the record exists.
                UPDATE lease6_stat_by_client_class SET leases = IF(leases > 0, leases - 1, 0)
                    WHERE client_class = class AND lease_type = old_lease_type;

                SET i = i + 1;
            END WHILE label;
        END IF;

        -- Check if it's moving into a counted state.
        IF new_state = 0 THEN
            -- Dive into client classes.
            SET length = JSON_LENGTH(new_client_classes);
            SET i = 0;
            label: WHILE i < length DO
                SET class = JSON_UNQUOTE(JSON_EXTRACT(new_client_classes, CONCAT('\$[', i, ']')));

                -- Upsert to increment the lease count.
                UPDATE lease6_stat_by_client_class SET leases = leases + 1
                    WHERE client_class = class AND lease_type = new_lease_type;
                IF ROW_COUNT() <= 0 THEN
                    INSERT INTO lease6_stat_by_client_class VALUES (class, new_lease_type, 1);
                END IF;

                SET i = i + 1;
            END WHILE label;
        END IF;
    END IF;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS lease6_ADEL_lease6_stat_by_client_class;
DELIMITER $$
CREATE PROCEDURE lease6_ADEL_lease6_stat_by_client_class(IN old_state TINYINT,
                                                         IN old_user_context TEXT,
                                                         IN old_lease_type TINYINT)
BEGIN
    -- Declarations
    DECLARE client_classes TEXT;
    DECLARE class VARCHAR(255);
    DECLARE length INT;
    DECLARE i INT;

    -- Ignore ERROR 3141 (22032) at line 1: Invalid JSON text in argument 1 to
    --      function json_extract: "The document is empty." at position 0.
    -- Ignore ERROR 4037 (HY000): Unexpected end of JSON text in argument 1 to function 'json_extract'
    -- These situations are handled with a propagating NULL result from JSON_EXTRACT.
    DECLARE CONTINUE HANDLER FOR 3141 BEGIN END;
    DECLARE CONTINUE HANDLER FOR 4037 BEGIN END;

    -- Only state 0 is accounted for in lease limiting. But check both states to be consistent with lease6_stat.
    IF old_state = 0 THEN
        -- Dive into client classes.
        SET client_classes = JSON_EXTRACT(old_user_context, '$."ISC"."client-classes"');
        SET length = JSON_LENGTH(client_classes);

        SET i = 0;
        label: WHILE i < length DO
            SET class = JSON_UNQUOTE(JSON_EXTRACT(client_classes, CONCAT('\$[', i, ']')));

            -- Decrement the lease count if the record exists.
            UPDATE lease6_stat_by_client_class SET leases = IF(leases > 0, leases - 1, 0)
                WHERE client_class = class AND lease_type = old_lease_type;

            SET i = i + 1;
        END WHILE label;
    END IF;
END $$
DELIMITER ;

-- Recreate the after-event triggers for INSERT, UPDATE and DELETE on lease tables to call the --
-- stored procedures above in pairs of two: for client classes and for subnets. --

-- Function that establishes whether JSON functions are supported.
-- They should be provided with MySQL>= 5.7, MariaDB >= 10.2.3.
DROP FUNCTION IF EXISTS isJsonSupported;
DELIMITER $$
CREATE FUNCTION isJsonSupported()
RETURNS BOOL
DETERMINISTIC
BEGIN
    DECLARE dummy BOOL;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        RETURN false;

    SELECT JSON_EXTRACT('{ "foo": 1 }', '$.foo') INTO dummy;
    RETURN true;
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS stat_lease4_insert;
DROP TRIGGER IF EXISTS lease4_AINS;
DELIMITER $$
CREATE TRIGGER lease4_AINS AFTER INSERT ON lease4 FOR EACH ROW
BEGIN
    CALL lease4_AINS_lease4_stat(NEW.state, NEW.subnet_id);
    IF @json_supported IS NULL THEN
        SELECT isJsonSupported() INTO @json_supported;
    END IF;
    IF @json_supported = 1 THEN
        CALL lease4_AINS_lease4_stat_by_client_class(NEW.state, NEW.user_context);
    END IF;
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS stat_lease4_update;
DROP TRIGGER IF EXISTS lease4_AUPD;
DELIMITER $$
CREATE TRIGGER lease4_AUPD AFTER UPDATE ON lease4 FOR EACH ROW
BEGIN
    CALL lease4_AUPD_lease4_stat(OLD.state, OLD.subnet_id, NEW.state, NEW.subnet_id);
    IF @json_supported IS NULL THEN
        SELECT isJsonSupported() INTO @json_supported;
    END IF;
    IF @json_supported = 1 THEN
        CALL lease4_AUPD_lease4_stat_by_client_class(OLD.state, OLD.user_context, NEW.state, NEW.user_context);
    END IF;
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS stat_lease4_delete;
DROP TRIGGER IF EXISTS lease4_ADEL;
DELIMITER $$
CREATE TRIGGER lease4_ADEL AFTER DELETE ON lease4 FOR EACH ROW
BEGIN
    CALL lease4_ADEL_lease4_stat(OLD.state, OLD.subnet_id);
    IF @json_supported IS NULL THEN
        SELECT isJsonSupported() INTO @json_supported;
    END IF;
    IF @json_supported = 1 THEN
        CALL lease4_ADEL_lease4_stat_by_client_class(OLD.state, OLD.user_context);
    END IF;
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS stat_lease6_insert;
DROP TRIGGER IF EXISTS lease6_AINS;
DELIMITER $$
CREATE TRIGGER lease6_AINS AFTER INSERT ON lease6 FOR EACH ROW
BEGIN
    CALL lease6_AINS_lease6_stat(NEW.state, NEW.subnet_id, NEW.lease_type);
    IF @json_supported IS NULL THEN
        SELECT isJsonSupported() INTO @json_supported;
    END IF;
    IF @json_supported = 1 THEN
        CALL lease6_AINS_lease6_stat_by_client_class(NEW.state, NEW.user_context, NEW.lease_type);
    END IF;
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS stat_lease6_update;
DROP TRIGGER IF EXISTS lease6_AUPD;
DELIMITER $$
CREATE TRIGGER lease6_AUPD AFTER UPDATE ON lease6 FOR EACH ROW
BEGIN
    CALL lease6_AUPD_lease6_stat(OLD.state, OLD.subnet_id, OLD.lease_type, NEW.state, NEW.subnet_id, NEW.lease_type);
    IF @json_supported IS NULL THEN
        SELECT isJsonSupported() INTO @json_supported;
    END IF;
    IF @json_supported = 1 THEN
        CALL lease6_AUPD_lease6_stat_by_client_class(OLD.state, OLD.user_context, OLD.lease_type, NEW.state, NEW.user_context, NEW.lease_type);
    END IF;
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS stat_lease6_delete;
DROP TRIGGER IF EXISTS lease6_ADEL;
DELIMITER $$
CREATE TRIGGER lease6_ADEL AFTER DELETE ON lease6 FOR EACH ROW
BEGIN
    CALL lease6_ADEL_lease6_stat(OLD.state, OLD.subnet_id, OLD.lease_type);
    IF @json_supported IS NULL THEN
        SELECT isJsonSupported() INTO @json_supported;
    END IF;
    IF @json_supported = 1 THEN
        CALL lease6_ADEL_lease6_stat_by_client_class(OLD.state, OLD.user_context, OLD.lease_type);
    END IF;
END $$
DELIMITER ;

-- Create functions that check if the lease limits set in the given user context are exceeded.
-- They return a string describing a limit that is being exceeded, or an empty
-- string if no limits are exceeded. The following format for is assumed for user_context
-- (not all nodes are mandatory and values are given only as examples):
-- { "ISC": { "limits": { "client-classes": [ { "name": "foo", "address-limit": 2, "prefix-limit": 1 } ],
--                        "subnet": { "id": 1, "address-limit": 2, "prefix-limit": 1 } } } }

DROP FUNCTION IF EXISTS checkLease4Limits;
DELIMITER $$
CREATE FUNCTION checkLease4Limits(user_context TEXT)
RETURNS TEXT
READS SQL DATA
BEGIN
    -- Declarations
    DECLARE json_element TEXT;
    DECLARE length INT;
    DECLARE class TEXT;
    DECLARE name VARCHAR(255);
    DECLARE i INT;
    DECLARE lease_limit INT;
    DECLARE lease_count INT;

    -- Dive into client class limits.
    SET json_element = JSON_EXTRACT(user_context, '$."ISC"."limits"."client-classes"');
    SET length = JSON_LENGTH(json_element);

    SET i = 0;
    label: WHILE i < length DO
        -- Get the lease limit for this client class.
        SET class = JSON_EXTRACT(json_element, CONCAT('\$[', i, ']'));
        SET name = JSON_UNQUOTE(JSON_EXTRACT(class, '$.name'));
        SET lease_limit = JSON_EXTRACT(class, '$."address-limit"');

        IF lease_limit IS NOT NULL THEN
            -- Get the lease count for this client class.
            SET lease_count = (SELECT leases FROM lease4_stat_by_client_class WHERE client_class = name);
            IF lease_count IS NULL THEN
                SET lease_count = 0;
            END IF;

            -- Compare. Return immediately if the limit is exceeded.
            IF lease_limit <= lease_count THEN
                RETURN CONCAT('address limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count);
            END IF;
        END IF;

        SET i = i + 1;
    END WHILE label;

    -- Dive into subnet limits. Reuse i as subnet ID.
    SET json_element = JSON_EXTRACT(user_context, '$.ISC.limits.subnet');
    SET i = JSON_EXTRACT(json_element, '$.id');
    SET lease_limit = JSON_EXTRACT(json_element, '$."address-limit"');

    IF lease_limit IS NOT NULL THEN
        -- Get the lease count for this client class.
        SET lease_count = (SELECT leases FROM lease4_stat WHERE subnet_id = i AND state = 0);
        IF lease_count IS NULL THEN
            SET lease_count = 0;
        END IF;

        -- Compare. Return immediately if the limit is exceeded.
        IF lease_limit <= lease_count THEN
                RETURN CONCAT('address limit ', lease_limit, ' for subnet ID ', i, ', current lease count ', lease_count);
        END IF;
    END IF;

    RETURN '';
END $$
DELIMITER ;

DROP FUNCTION IF EXISTS checkLease6Limits;
DELIMITER $$
CREATE FUNCTION checkLease6Limits(user_context TEXT)
RETURNS TEXT
READS SQL DATA
BEGIN
    -- Declarations
    DECLARE json_element TEXT;
    DECLARE length INT;
    DECLARE class TEXT;
    DECLARE name VARCHAR(255);
    DECLARE i INT;
    DECLARE lease_limit INT;
    DECLARE lease_count INT;

    -- Dive into client class limits.
    SET json_element = JSON_EXTRACT(user_context, '$."ISC"."limits"."client-classes"');
    SET length = JSON_LENGTH(json_element);

    SET i = 0;
    label: WHILE i < length DO
        -- Get the lease limit for this client class.
        SET class = JSON_EXTRACT(json_element, CONCAT('\$[', i, ']'));
        SET name = JSON_UNQUOTE(JSON_EXTRACT(class, '$.name'));

        SET lease_limit = JSON_EXTRACT(class, '$."address-limit"');
        IF lease_limit IS NOT NULL THEN
            -- Get the address count for this client class.
            SET lease_count = (SELECT leases FROM lease6_stat_by_client_class WHERE client_class = name AND lease_type = 0);
            IF lease_count IS NULL THEN
                SET lease_count = 0;
            END IF;

            -- Compare. Return immediately if the limit is exceeded.
            IF lease_limit <= lease_count THEN
                RETURN CONCAT('address limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count);
            END IF;
        END IF;

        SET lease_limit = JSON_EXTRACT(class, '$."prefix-limit"');
        IF lease_limit IS NOT NULL THEN
            -- Get the prefix count for this client class.
            SET lease_count = (SELECT leases FROM lease6_stat_by_client_class WHERE client_class = name AND lease_type = 2);
            IF lease_count IS NULL THEN
                SET lease_count = 0;
            END IF;

            -- Compare. Return immediately if the limit is exceeded.
            IF lease_limit <= lease_count THEN
                RETURN CONCAT('prefix limit ', lease_limit, ' for client class "', name, '", current lease count ', lease_count);
            END IF;
        END IF;

        SET i = i + 1;
    END WHILE label;

    -- Dive into subnet limits. Reuse i as subnet ID.
    SET json_element = JSON_EXTRACT(user_context, '$.ISC.limits.subnet');
    SET i = JSON_EXTRACT(json_element, '$.id');
    SET lease_limit = JSON_EXTRACT(json_element, '$."address-limit"');
    IF lease_limit IS NOT NULL THEN
        -- Get the lease count for this client class.
        SET lease_count = (SELECT leases FROM lease6_stat WHERE subnet_id = i AND lease_type = 0 AND state = 0);
        IF lease_count IS NULL THEN
            SET lease_count = 0;
        END IF;

        -- Compare. Return immediately if the limit is exceeded.
        IF lease_limit <= lease_count THEN
                RETURN CONCAT('address limit ', lease_limit, ' for subnet ID ', i, ', current lease count ', lease_count);
        END IF;
    END IF;
    SET lease_limit = JSON_EXTRACT(json_element, '$."prefix-limit"');
    IF lease_limit IS NOT NULL THEN
        -- Get the lease count for this client class.
        SET lease_count = (SELECT leases FROM lease6_stat WHERE subnet_id = i AND lease_type = 2 AND state = 0);
        IF lease_count IS NULL THEN
            SET lease_count = 0;
        END IF;

        -- Compare. Return immediately if the limit is exceeded.
        IF lease_limit <= lease_count THEN
            RETURN CONCAT('prefix limit ', lease_limit, ' for subnet ID ', i, ', current lease count ', lease_count);
        END IF;
    END IF;

    RETURN '';
END $$
DELIMITER ;

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

-- This line concludes the schema upgrade to version 14.
EOF