diff options
Diffstat (limited to 'src/share/database/scripts/mysql/upgrade_012_to_013.sh.in')
| -rw-r--r-- | src/share/database/scripts/mysql/upgrade_012_to_013.sh.in | 265 |
1 files changed, 265 insertions, 0 deletions
diff --git a/src/share/database/scripts/mysql/upgrade_012_to_013.sh.in b/src/share/database/scripts/mysql/upgrade_012_to_013.sh.in new file mode 100644 index 0000000..ee133d5 --- /dev/null +++ b/src/share/database/scripts/mysql/upgrade_012_to_013.sh.in @@ -0,0 +1,265 @@ +#!/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}" != "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 + +-- 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, ',', ','), + state, + REPLACE(IFNULL(user_context, ''), ',', ',') + 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, ',', ','), + IFNULL(colonSeparatedHex(HEX(hwaddr)), ''), + state, + REPLACE(IFNULL(user_context, ''), ',', ','), + 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, ',', ','), + state, + REPLACE(user_context, ',', ',') + ); +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, ',', ','), + UNHEX(REPLACE(hwaddr, ':', '')), + state, + REPLACE(user_context, ',', ','), + hwtype, + hwaddr_source + ); +END $$ +DELIMITER ; + +-- Update the schema version number. +UPDATE schema_version + SET version = '13', minor = '0'; + +-- This line concludes database upgrade to version 13. +EOF |