path: root/src/share/database/scripts/mysql/upgrade_012_to_013.sh.in

#!/bin/sh

# Copyright (C) 2022-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

# Check version.
version=$(mysql_version "${@}")
if test "${version}" != "12.0"; then
    printf 'This script upgrades 12.0 to 13.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 13.0.

-- Create a function that separates a contiguous hexadecimal string
-- into groups of two hexadecimals separated by colons.
DROP FUNCTION IF EXISTS colonSeparatedHex;
DELIMITER $$
CREATE FUNCTION colonSeparatedHex(hex VARCHAR(64))
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
    -- Declarations
    DECLARE i INT;
    DECLARE length INT;
    DECLARE output VARCHAR(64);

    -- Initializations
    SET i = 3;
    SET length = LENGTH(hex);

    -- Add a leading zero if the first octet has a single hexadecimal character.
    IF MOD(length, 2) = 1 THEN
        SET hex = CONCAT('0', hex);
        SET length = length + 1;
    END IF;

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

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

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

    RETURN output;
END $$
DELIMITER ;

-- Modify the procedure to output a memfile-ready CSV file.
DROP PROCEDURE IF EXISTS lease4DumpData;
DELIMITER $$
CREATE PROCEDURE lease4DumpData()
BEGIN
    SELECT
        INET_NTOA(address),
        IFNULL(colonSeparatedHex(HEX(hwaddr)), ''),
        IFNULL(colonSeparatedHex(HEX(client_id)), ''),
        valid_lifetime,
        UNIX_TIMESTAMP(expire),
        subnet_id,
        fqdn_fwd,
        fqdn_rev,
        REPLACE(hostname, ',', '&#x2c'),
        state,
        REPLACE(IFNULL(user_context, ''), ',', '&#x2c')
    FROM lease4
    ORDER BY address;
END $$
DELIMITER ;

-- hwtype and hwaddr_source need to be last to match memfile format.
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,state,user_context,hwtype,hwaddr_source';
END $$
DELIMITER ;

-- Modify the procedure to output a memfile-ready CSV file.
DROP PROCEDURE IF EXISTS lease6DumpData;
DELIMITER $$
CREATE PROCEDURE lease6DumpData()
BEGIN
    SELECT
        address,
        IFNULL(colonSeparatedHex(HEX(duid)), ''),
        valid_lifetime,
        UNIX_TIMESTAMP(expire),
        subnet_id,
        pref_lifetime,
        lease_type,
        iaid,
        prefix_len,
        fqdn_fwd,
        fqdn_rev,
        REPLACE(hostname, ',', '&#x2c'),
        IFNULL(colonSeparatedHex(HEX(hwaddr)), ''),
        state,
        REPLACE(IFNULL(user_context, ''), ',', '&#x2c'),
        hwtype,
        hwaddr_source
    FROM lease6
    ORDER BY address;
END $$
DELIMITER ;

-- Create a procedure that inserts a v4 lease from memfile data.
DELIMITER $$
CREATE PROCEDURE lease4Upload(
    IN address VARCHAR(15),
    IN hwaddr VARCHAR(20),
    IN client_id VARCHAR(128),
    IN valid_lifetime INT UNSIGNED,
    IN expire BIGINT UNSIGNED,
    IN subnet_id INT UNSIGNED,
    IN fqdn_fwd TINYINT,
    IN fqdn_rev TINYINT,
    IN hostname VARCHAR(255),
    IN state INT UNSIGNED,
    IN user_context TEXT
)
BEGIN
    INSERT INTO lease4 (
        address,
        hwaddr,
        client_id,
        valid_lifetime,
        expire,
        subnet_id,
        fqdn_fwd,
        fqdn_rev,
        hostname,
        state,
        user_context
    ) VALUES (
        INET_ATON(address),
        UNHEX(REPLACE(hwaddr, ':', '')),
        UNHEX(REPLACE(client_id, ':', '')),
        valid_lifetime,
        FROM_UNIXTIME(expire),
        subnet_id,
        fqdn_fwd,
        fqdn_rev,
        REPLACE(hostname, '&#x2c', ','),
        state,
        REPLACE(user_context, '&#x2c', ',')
    );
END $$
DELIMITER ;

-- Create a procedure that inserts a v6 lease from memfile data.
DELIMITER $$
CREATE PROCEDURE lease6Upload(
    IN address VARCHAR(39),
    IN duid VARCHAR(128),
    IN valid_lifetime INT UNSIGNED,
    IN expire BIGINT UNSIGNED,
    IN subnet_id INT UNSIGNED,
    IN pref_lifetime INT UNSIGNED,
    IN lease_type TINYINT,
    IN iaid INT UNSIGNED,
    IN prefix_len TINYINT UNSIGNED,
    IN fqdn_fwd TINYINT,
    IN fqdn_rev TINYINT,
    IN hostname VARCHAR(255),
    IN hwaddr VARCHAR(64),
    IN state INT UNSIGNED,
    IN user_context TEXT,
    IN hwtype SMALLINT,
    IN hwaddr_source INT UNSIGNED
)
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,
        UNHEX(REPLACE(duid, ':', '')),
        valid_lifetime,
        FROM_UNIXTIME(expire),
        subnet_id,
        pref_lifetime,
        lease_type,
        iaid,
        prefix_len,
        fqdn_fwd,
        fqdn_rev,
        REPLACE(hostname, '&#x2c', ','),
        UNHEX(REPLACE(hwaddr, ':', '')),
        state,
        REPLACE(user_context, '&#x2c', ','),
        hwtype,
        hwaddr_source
    );
END $$
DELIMITER ;

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

-- This line concludes the schema upgrade to version 13.0.

EOF