[#3663] Add ddns ttl columns to mysql schema

src/share/database/scripts/mysql/upgrade_027_to_028.sh.in
    new file

src/bin/admin/tests/mysql_tests.sh.in
    mysql_upgrade_27_to_28_test() - new test

src/lib/mysql/mysql_constants.h
   schema version bump

configure.ac
src/share/database/scripts/mysql/.gitignore
src/share/database/scripts/mysql/Makefile.am
    added new upgrade file

src/share/database/scripts/mysql/dhcpdb_create.mysql
    Add ddns-ttl* columns to network and subnet tables

7 files changed, 157 insertions, 2 deletions

diff --git a/configure.ac b/configure.ac
index 5c7542b8ac..8b19d20ac6 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1826,6 +1826,8 @@ AC_CONFIG_FILES([src/share/database/scripts/mysql/upgrade_025_to_026.sh],
                 [chmod +x src/share/database/scripts/mysql/upgrade_025_to_026.sh])
 AC_CONFIG_FILES([src/share/database/scripts/mysql/upgrade_026_to_027.sh],
                 [chmod +x src/share/database/scripts/mysql/upgrade_026_to_027.sh])
+AC_CONFIG_FILES([src/share/database/scripts/mysql/upgrade_027_to_028.sh],
+                [chmod +x src/share/database/scripts/mysql/upgrade_027_to_028.sh])
 AC_CONFIG_FILES([src/share/database/scripts/mysql/wipe_data.sh],
                 [chmod +x src/share/database/scripts/mysql/wipe_data.sh])
 AC_CONFIG_FILES([src/share/database/scripts/pgsql/Makefile])
diff --git a/src/bin/admin/tests/mysql_tests.sh.in b/src/bin/admin/tests/mysql_tests.sh.in
index 386f94b530..1768ba085a 100644
--- a/src/bin/admin/tests/mysql_tests.sh.in
+++ b/src/bin/admin/tests/mysql_tests.sh.in
@@ -992,6 +992,33 @@ mysql_upgrade_26_to_27_test() {
     check_table_column client_classes dhcp6_pd_pool
 }
 
+mysql_upgrade_27_to_28_test() {
+
+    # check ddns ttl fields were added to dhcp4_shared_network
+    check_table_column ddns_ttl_percent dhcp4_shared_network
+    check_table_column ddns_ttl dhcp4_shared_network
+    check_table_column ddns_ttl_min dhcp4_shared_network
+    check_table_column ddns_ttl_max dhcp4_shared_network
+
+    # check ddns ttl fields were added to dhcp4_subnet
+    check_table_column ddns_ttl_percent dhcp4_subnet
+    check_table_column ddns_ttl dhcp4_subnet
+    check_table_column ddns_ttl_min dhcp4_subnet
+    check_table_column ddns_ttl_max dhcp4_subnet
+
+    # check ddns ttl fields were added to dhcp6_shared_network
+    check_table_column ddns_ttl_percent dhcp6_shared_network
+    check_table_column ddns_ttl dhcp6_shared_network
+    check_table_column ddns_ttl_min dhcp6_shared_network
+    check_table_column ddns_ttl_max dhcp6_shared_network
+
+    # check ddns ttl fields were added to dhcp6_subnet
+    check_table_column ddns_ttl_percent dhcp6_subnet
+    check_table_column ddns_ttl dhcp6_subnet
+    check_table_column ddns_ttl_min dhcp6_subnet
+    check_table_column ddns_ttl_max dhcp6_subnet
+}
+
 mysql_upgrade_test() {
 
     test_start "mysql.upgrade"
@@ -1013,7 +1040,7 @@ mysql_upgrade_test() {
 
     # Verify that the upgraded schema reports the latest version.
     version=$("${kea_admin}" db-version mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}")
-    assert_str_eq "27.0" "${version}" "Expected kea-admin to return %s, returned value was %s"
+    assert_str_eq "28.0" "${version}" "Expected kea-admin to return %s, returned value was %s"
 
     # Let's check that the new tables are indeed there.
 
@@ -1691,6 +1718,9 @@ SET @disable_audit = 0"
     # Check upgrade from 26.0 to 27.0.
     mysql_upgrade_26_to_27_test
 
+    # Check upgrade from 27.0 to 28.0.
+    mysql_upgrade_27_to_28_test
+
     # Let's wipe the whole database
     mysql_wipe
 
diff --git a/src/lib/mysql/mysql_constants.h b/src/lib/mysql/mysql_constants.h
index 7d2d640730..a5e2787499 100644
--- a/src/lib/mysql/mysql_constants.h
+++ b/src/lib/mysql/mysql_constants.h
@@ -52,7 +52,7 @@ const int MLM_MYSQL_FETCH_FAILURE = 0;
 
 /// @name Current database schema version values.
 //@{
-const uint32_t MYSQL_SCHEMA_VERSION_MAJOR = 27;
+const uint32_t MYSQL_SCHEMA_VERSION_MAJOR = 28;
 const uint32_t MYSQL_SCHEMA_VERSION_MINOR = 0;
 
 //@}
diff --git a/src/share/database/scripts/mysql/.gitignore b/src/share/database/scripts/mysql/.gitignore
index e67addfeb8..892cfe3190 100644
--- a/src/share/database/scripts/mysql/.gitignore
+++ b/src/share/database/scripts/mysql/.gitignore
@@ -35,4 +35,5 @@
 /upgrade_024_to_025.sh
 /upgrade_025_to_026.sh
 /upgrade_026_to_027.sh
+/upgrade_027_to_028.sh
 /wipe_data.sh
diff --git a/src/share/database/scripts/mysql/Makefile.am b/src/share/database/scripts/mysql/Makefile.am
index bedb59088b..ff312fb6d4 100644
--- a/src/share/database/scripts/mysql/Makefile.am
+++ b/src/share/database/scripts/mysql/Makefile.am
@@ -46,6 +46,7 @@ mysql_SCRIPTS += upgrade_023_to_024.sh
 mysql_SCRIPTS += upgrade_024_to_025.sh
 mysql_SCRIPTS += upgrade_025_to_026.sh
 mysql_SCRIPTS += upgrade_026_to_027.sh
+mysql_SCRIPTS += upgrade_027_to_028.sh
 mysql_SCRIPTS += wipe_data.sh
 
 DISTCLEANFILES = ${mysql_SCRIPTS}
diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql
index c363b52a84..87f1d2da38 100644
--- a/src/share/database/scripts/mysql/dhcpdb_create.mysql
+++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql
@@ -6131,6 +6131,38 @@ UPDATE schema_version
 
 -- This line concludes the schema upgrade to version 27.0.
 
+-- This line starts the schema upgrade to version 28.0.
+
+ALTER TABLE dhcp4_shared_network
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+ALTER TABLE dhcp4_subnet
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+ALTER TABLE dhcp6_shared_network
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+ALTER TABLE dhcp6_subnet
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+-- Update the schema version number.
+UPDATE schema_version
+    SET version = '28', minor = '0';
+
+-- This line concludes the schema upgrade to version 28.0.
+
 # Notes:
 #
 # Indexes
diff --git a/src/share/database/scripts/mysql/upgrade_027_to_028.sh.in b/src/share/database/scripts/mysql/upgrade_027_to_028.sh.in
new file mode 100644
index 0000000000..afa9f2eddb
--- /dev/null
+++ b/src/share/database/scripts/mysql/upgrade_027_to_028.sh.in
@@ -0,0 +1,89 @@
+#!/bin/sh
+
+# Copyright (C) 2024 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/.
+
+# 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 based on location of this script. Check for sources first,
+# so that the unexpected situations with weird paths fall on the default
+# case of installed.
+script_path=$(cd "$(dirname "${0}")" && pwd)
+if test "${script_path}" = "@abs_top_builddir@/src/share/database/scripts/mysql"; then
+    # shellcheck source=./src/bin/admin/admin-utils.sh.in
+    . "@abs_top_builddir@/src/bin/admin/admin-utils.sh"
+else
+    # shellcheck source=./src/bin/admin/admin-utils.sh.in
+    . "@datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh"
+fi
+
+# Check version.
+version=$(mysql_version "${@}")
+if test "${version}" != "27.0"; then
+    printf 'This script upgrades 27.0 to 28.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 28.0.
+
+ALTER TABLE dhcp4_shared_network
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+ALTER TABLE dhcp4_subnet
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+ALTER TABLE dhcp6_shared_network
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+ALTER TABLE dhcp6_subnet
+    ADD COLUMN ddns_ttl_percent FLOAT DEFAULT NULL,
+    ADD COLUMN ddns_ttl         INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_min     INT(10) DEFAULT NULL,
+    ADD COLUMN ddns_ttl_max     INT(10) DEFAULT NULL;
+
+-- Update the schema version number.
+UPDATE schema_version
+    SET version = '28', minor = '0';
+
+-- This line concludes the schema upgrade to version 28.0.
+
+EOF