From d1f89626fbf7773b8be7fb930615295505443fa5 Mon Sep 17 00:00:00 2001 From: Thomas Markwalder Date: Mon, 15 Jul 2024 14:18:56 -0400 Subject: [PATCH] [#3484] Updated db version, db scripts and tests modified: configure.ac src/bin/admin/tests/mysql_tests.sh.in src/bin/admin/tests/pgsql_tests.sh.in src/lib/mysql/mysql_constants.h src/lib/pgsql/pgsql_connection.h src/share/database/scripts/mysql/.gitignore src/share/database/scripts/mysql/dhcpdb_create.mysql src/share/database/scripts/mysql/dhcpdb_drop.mysql src/share/database/scripts/pgsql/.gitignore src/share/database/scripts/pgsql/dhcpdb_create.pgsql src/share/database/scripts/pgsql/dhcpdb_drop.pgsql new: src/share/database/scripts/mysql/upgrade_022_to_022.1.sh.in src/share/database/scripts/pgsql/upgrade_022_to_022.1.sh.in --- configure.ac | 4 + src/bin/admin/tests/mysql_tests.sh.in | 84 +++++++++++++- src/bin/admin/tests/pgsql_tests.sh.in | 87 ++++++++++++++- src/lib/mysql/mysql_constants.h | 2 +- src/lib/pgsql/pgsql_connection.h | 2 +- src/share/database/scripts/mysql/.gitignore | 1 + .../scripts/mysql/dhcpdb_create.mysql | 47 ++++++++ .../database/scripts/mysql/dhcpdb_drop.mysql | 1 + .../scripts/mysql/upgrade_022_to_022.1.sh.in | 103 ++++++++++++++++++ src/share/database/scripts/pgsql/.gitignore | 1 + .../scripts/pgsql/dhcpdb_create.pgsql | 48 ++++++++ .../database/scripts/pgsql/dhcpdb_drop.pgsql | 1 + .../scripts/pgsql/upgrade_022_to_022.1.sh.in | 91 ++++++++++++++++ 13 files changed, 467 insertions(+), 5 deletions(-) create mode 100644 src/share/database/scripts/mysql/upgrade_022_to_022.1.sh.in create mode 100644 src/share/database/scripts/pgsql/upgrade_022_to_022.1.sh.in diff --git a/configure.ac b/configure.ac index 69622edc0d..bdabddbb6f 100644 --- a/configure.ac +++ b/configure.ac @@ -1764,6 +1764,8 @@ AC_CONFIG_FILES([src/share/database/scripts/mysql/upgrade_020_to_021.sh], [chmod +x src/share/database/scripts/mysql/upgrade_020_to_021.sh]) AC_CONFIG_FILES([src/share/database/scripts/mysql/upgrade_021_to_022.sh], [chmod +x src/share/database/scripts/mysql/upgrade_021_to_022.sh]) +AC_CONFIG_FILES([src/share/database/scripts/mysql/upgrade_022_to_022.1.sh], + [chmod +x src/share/database/scripts/mysql/upgrade_022_to_022.1.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]) @@ -1821,6 +1823,8 @@ AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_020_to_021.sh], [chmod +x src/share/database/scripts/pgsql/upgrade_020_to_021.sh]) AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_021_to_022.sh], [chmod +x src/share/database/scripts/pgsql/upgrade_021_to_022.sh]) +AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_022_to_022.1.sh], + [chmod +x src/share/database/scripts/pgsql/upgrade_022_to_022.1.sh]) AC_CONFIG_FILES([src/share/database/scripts/pgsql/wipe_data.sh], [chmod +x src/share/database/scripts/pgsql/wipe_data.sh]) AC_CONFIG_FILES([src/share/yang/Makefile]) diff --git a/src/bin/admin/tests/mysql_tests.sh.in b/src/bin/admin/tests/mysql_tests.sh.in index 3bf7b513de..514b5d17d9 100644 --- a/src/bin/admin/tests/mysql_tests.sh.in +++ b/src/bin/admin/tests/mysql_tests.sh.in @@ -813,6 +813,14 @@ mysql_upgrade_18_to_19_test() { run_statement "ipv6_reservations_insert" "$qry" "3001::99" } +mysql_upgrade_22_0_to_22_1_test() { + query="SELECT count(id) from option_def_data_type" + run_command \ + mysql_execute "${query}" + assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" + assert_str_eq '18' "${OUTPUT}" "${query}: expected output %s, returned %s" +} + mysql_upgrade_test() { test_start "mysql.upgrade" @@ -834,7 +842,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 "22.0" "${version}" "Expected kea-admin to return %s, returned value was %s" + assert_str_eq "22.1" "${version}" "Expected kea-admin to return %s, returned value was %s" # Let's check that the new tables are indeed there. @@ -1497,6 +1505,8 @@ SET @disable_audit = 0" # Check upgrade from 18.0 to 19.0. mysql_upgrade_18_to_19_test + # Check upgrade from 22.0 to 22.1. + mysql_upgrade_22_0_to_22_1_test # Let's wipe the whole database mysql_wipe @@ -3293,6 +3303,77 @@ mysql_reservation_mode_out_of_pool_parameters_test() { test_finish 0 } +# Verifies that OPT_RECORD_TYPE values are updated +mysql_migrate_opt_record_type() { + test_start "mysql.migrate_opt_record_type" + + # Let's wipe the whole database + mysql_wipe + + # We need to create an older database with lease data so we can + # verify the upgrade mechanisms which prepopulate the lease stat + # tables. + # + # Initialize database to schema 1.0. + mysql_execute_script "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.mysql" + assert_eq 0 "${EXIT_CODE}" "cannot initialize 1.0 database, expected exit code: %d, actual: %d" + + # Now upgrade to schema 22.0 + mysql_upgrade_schema_to_version 22.0 + + # Now insert option definitions. + sql=\ +"set @disable_audit = 1; \ + insert into dhcp4_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('222','foo','dhcp4',17,current_timestamp,NULL, false, false);\ + insert into dhcp4_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('223','bar','dhcp4',17,current_timestamp,'10, 7, 2, 14', false, false);\ + insert into dhcp4_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('224','bar2','dhcp4',18,current_timestamp,'10, 7, 2, 14', false, false); + insert into dhcp6_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('222','foo','dhcp6',17,current_timestamp,NULL, false, false);\ + insert into dhcp6_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('223','bar','dhcp6',17,current_timestamp,'10, 7, 2, 14', false, false);\ + insert into dhcp6_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('224','bar2','dhcp6',18,current_timestamp,'10, 7, 2, 14', false, false); +" + run_statement "insert otion definitions" "$sql" + + # Verify the inserted record counts. + qry="select count(*) from dhcp4_option_def;" + run_statement "#get 4_option_def_count before update" "$qry" 3 + + qry="select count(*) from dhcp6_option_def;" + run_statement "#get 6_option_def_count before update" "$qry" 3 + + # Upgrade to schema 22.1 + mysql_upgrade_schema_to_version 22.1 + + # Verify the migrated records. + qry="select type from dhcp4_option_def where name = 'foo';" + run_statement "#get 4_option_def_foo after update" "$qry" 17 + + qry="select type from dhcp4_option_def where name = 'bar';" + run_statement "#get 4_option_def_bar after update" "$qry" 254 + + qry="select type from dhcp4_option_def where name = 'bar2';" + run_statement "#get 4_option_def_bar2 after update" "$qry" 254 + + qry="select type from dhcp6_option_def where name = 'foo';" + run_statement "#get 6_option_def_foo after update" "$qry" 17 + + qry="select type from dhcp6_option_def where name = 'bar';" + run_statement "#get 6_option_def_bar after update" "$qry" 254 + + qry="select type from dhcp6_option_def where name = 'bar2';" + run_statement "#get 6_option_def_bar2 after update" "$qry" 254 + + # Let's wipe the whole database + mysql_wipe + + test_finish 0 +} + # Run tests. mysql_db_init_test mysql_host_reservation_init_test @@ -3325,3 +3406,4 @@ mysql_reservation_mode_off_parameters_test mysql_reservation_mode_all_parameters_test mysql_reservation_mode_global_parameters_test mysql_reservation_mode_out_of_pool_parameters_test +mysql_migrate_opt_record_type diff --git a/src/bin/admin/tests/pgsql_tests.sh.in b/src/bin/admin/tests/pgsql_tests.sh.in index 893fd13e90..662f85e40e 100644 --- a/src/bin/admin/tests/pgsql_tests.sh.in +++ b/src/bin/admin/tests/pgsql_tests.sh.in @@ -142,7 +142,7 @@ pgsql_db_version_test() { run_command \ "${kea_admin}" db-version pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" version="${OUTPUT}" - assert_str_eq "22.0" "${version}" "Expected kea-admin to return %s, returned value was %s" + assert_str_eq "22.1" "${version}" "Expected kea-admin to return %s, returned value was %s" # Let's wipe the whole database pgsql_wipe @@ -909,6 +909,14 @@ pgsql_upgrade_20_to_21_test() { assert_str_eq '4' "${OUTPUT}" "${query}: expected output %s, returned %s" } +pgsql_upgrade_22_to_22_1_test() { + query="SELECT count(id) from option_def_data_type" + run_command \ + pgsql_execute "${query}" + assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" + assert_str_eq '18' "${OUTPUT}" "${query}: expected output %s, returned %s" +} + pgsql_upgrade_test() { test_start "pgsql.upgrade" @@ -927,7 +935,7 @@ pgsql_upgrade_test() { # Verify upgraded schema reports the latest version. version=$("${kea_admin}" db-version pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}") - assert_str_eq "22.0" "${version}" 'Expected kea-admin to return %s, returned value was %s' + assert_str_eq "22.1" "${version}" 'Expected kea-admin to return %s, returned value was %s' # Check 1.0 to 2.0 upgrade pgsql_upgrade_1_0_to_2_0_test @@ -986,6 +994,9 @@ pgsql_upgrade_test() { # Check 20 to 21 upgrade pgsql_upgrade_20_to_21_test + # Check 22 to 22.1 upgrade + pgsql_upgrade_22_to_22_1_test + # Let's wipe the whole database pgsql_wipe @@ -2554,6 +2565,77 @@ pgsql_reservation_mode_out_of_pool_parameters_test() { test_finish 0 } +# Verifies that OPT_RECORD_TYPE values are updated +pgsql_migrate_opt_record_type() { + test_start "pgsql.migrate_opt_record_type" + + # Let's wipe the whole database + pgsql_wipe + + # We need to create an older database with lease data so we can + # verify the upgrade mechanisms which prepopulate the lease stat + # tables. + # + # Initialize database to schema 1.0. + pgsql_execute_script "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql" + assert_eq 0 "${EXIT_CODE}" "cannot initialize 1.0 database, expected exit code: %d, actual: %d" + + # Now upgrade to schema 22.0 + pgsql_upgrade_schema_to_version 22.0 + + # Now insert option definitions. + sql=\ +"select set_config('kea.disable_audit', 'true', false);\ + insert into dhcp4_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('222','foo','dhcp4',17,current_timestamp,NULL, false, false);\ + insert into dhcp4_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('223','bar','dhcp4',17,current_timestamp,'10, 7, 2, 14', false, false);\ + insert into dhcp4_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('224','bar2','dhcp4',18,current_timestamp,'10, 7, 2, 14', false, false); + insert into dhcp6_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('222','foo','dhcp6',17,current_timestamp,NULL, false, false);\ + insert into dhcp6_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('223','bar','dhcp6',17,current_timestamp,'10, 7, 2, 14', false, false);\ + insert into dhcp6_option_def (code,name,space,type,modification_ts,record_types, is_array, encapsulate)\ + values ('224','bar2','dhcp6',18,current_timestamp,'10, 7, 2, 14', false, false); +" + run_statement "insert otion definitions" "$sql" + + # Verify the inserted record counts. + qry="select count(*) from dhcp4_option_def;" + run_statement "#get 4_option_def_count before update" "$qry" 3 + + qry="select count(*) from dhcp6_option_def;" + run_statement "#get 6_option_def_count before update" "$qry" 3 + + # Upgrade to schema 24.0 + pgsql_upgrade_schema_to_version 24.0 + + # Verify the migrated records. + qry="select type from dhcp4_option_def where name = 'foo';" + run_statement "#get 4_option_def_foo after update" "$qry" 17 + + qry="select type from dhcp4_option_def where name = 'bar';" + run_statement "#get 4_option_def_bar after update" "$qry" 254 + + qry="select type from dhcp4_option_def where name = 'bar2';" + run_statement "#get 4_option_def_bar2 after update" "$qry" 254 + + qry="select type from dhcp6_option_def where name = 'foo';" + run_statement "#get 6_option_def_foo after update" "$qry" 17 + + qry="select type from dhcp6_option_def where name = 'bar';" + run_statement "#get 6_option_def_bar after update" "$qry" 254 + + qry="select type from dhcp6_option_def where name = 'bar2';" + run_statement "#get 6_option_def_bar2 after update" "$qry" 254 + + # Let's wipe the whole database + pgsql_wipe + + test_finish 0 +} + # Run tests. pgsql_db_init_test pgsql_db_version_test @@ -2580,3 +2662,4 @@ pgsql_reservation_mode_off_parameters_test pgsql_reservation_mode_all_parameters_test pgsql_reservation_mode_global_parameters_test pgsql_reservation_mode_out_of_pool_parameters_test +pgsql_migrate_opt_record_type diff --git a/src/lib/mysql/mysql_constants.h b/src/lib/mysql/mysql_constants.h index a0d79f97fc..964ab792d1 100644 --- a/src/lib/mysql/mysql_constants.h +++ b/src/lib/mysql/mysql_constants.h @@ -53,7 +53,7 @@ const int MLM_MYSQL_FETCH_FAILURE = 0; /// @name Current database schema version values. //@{ const uint32_t MYSQL_SCHEMA_VERSION_MAJOR = 22; -const uint32_t MYSQL_SCHEMA_VERSION_MINOR = 0; +const uint32_t MYSQL_SCHEMA_VERSION_MINOR = 1; //@} diff --git a/src/lib/pgsql/pgsql_connection.h b/src/lib/pgsql/pgsql_connection.h index e61d0763c6..b3fbf5b38a 100644 --- a/src/lib/pgsql/pgsql_connection.h +++ b/src/lib/pgsql/pgsql_connection.h @@ -19,7 +19,7 @@ namespace db { /// @brief Define the PostgreSQL backend version. const uint32_t PGSQL_SCHEMA_VERSION_MAJOR = 22; -const uint32_t PGSQL_SCHEMA_VERSION_MINOR = 0; +const uint32_t PGSQL_SCHEMA_VERSION_MINOR = 1; // Maximum number of parameters that can be used a statement // @todo This allows us to use an initializer list (since we can't diff --git a/src/share/database/scripts/mysql/.gitignore b/src/share/database/scripts/mysql/.gitignore index 6d84ac028e..cad07da56e 100644 --- a/src/share/database/scripts/mysql/.gitignore +++ b/src/share/database/scripts/mysql/.gitignore @@ -30,4 +30,5 @@ /upgrade_019_to_020.sh /upgrade_020_to_021.sh /upgrade_021_to_022.sh +/upgrade_022_to_022.1sh /wipe_data.sh diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql index abcb6ce1ee..b2203f3a44 100644 --- a/src/share/database/scripts/mysql/dhcpdb_create.mysql +++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql @@ -5907,6 +5907,53 @@ UPDATE schema_version -- This line concludes the schema upgrade to version 22.0. +-- This line starts the schema upgrade to version 22.1. + +SET @disable_audit = 1; +UPDATE dhcp4_option_def SET type = 254 WHERE record_types IS NOT NULL; +UPDATE dhcp6_option_def SET type = 254 WHERE record_types IS NOT NULL; + +-- Create the table that enumerates option definition data types. +CREATE TABLE option_def_data_type ( + id TINYINT UNSIGNED NOT NULL PRIMARY KEY, + name VARCHAR(32) NOT NULL +) ENGINE = InnoDB; + +-- Now insert supported types. +-- We skip (9, 'any-address') as it is not externally supported. +INSERT INTO option_def_data_type VALUES + (0, 'empty'), + (1, 'binary'), + (2, 'boolean'), + (3, 'int8"'), + (4, 'int16'), + (5, 'int32'), + (6, 'uint8'), + (7, 'uint16'), + (8, 'uint32'), + (10, 'ipv4-address'), + (11, 'ipv6-address'), + (12, 'ipv6-prefix'), + (13, 'psid'), + (14, 'string'), + (15, 'tuple'), + (16, 'fqdn'), + (17, 'internal'), + (254, 'record'); + +-- Add foreign key constraints to enforce only valid types. +ALTER TABLE dhcp4_option_def + ADD CONSTRAINT fk_option_def_data_type4 FOREIGN KEY (type) REFERENCES option_def_data_type(id); + +ALTER TABLE dhcp6_option_def + ADD CONSTRAINT fk_option_def_data_type6 FOREIGN KEY (type) REFERENCES option_def_data_type(id); + +-- Update the schema version number. +UPDATE schema_version + SET version = '22', minor = '1'; + +-- This line concludes the schema upgrade to version 22.1. + # Notes: # # Indexes diff --git a/src/share/database/scripts/mysql/dhcpdb_drop.mysql b/src/share/database/scripts/mysql/dhcpdb_drop.mysql index 7bbe6af08d..82881efb0f 100644 --- a/src/share/database/scripts/mysql/dhcpdb_drop.mysql +++ b/src/share/database/scripts/mysql/dhcpdb_drop.mysql @@ -161,3 +161,4 @@ DROP FUNCTION IF EXISTS checkLease6Limits; DROP FUNCTION IF EXISTS isJsonSupported; DROP TABLE IF EXISTS lease6_relay_id; DROP TABLE IF EXISTS lease6_remote_id; +DROP TABLE IF EXISTS option_def_data_type; diff --git a/src/share/database/scripts/mysql/upgrade_022_to_022.1.sh.in b/src/share/database/scripts/mysql/upgrade_022_to_022.1.sh.in new file mode 100644 index 0000000000..d69b17834e --- /dev/null +++ b/src/share/database/scripts/mysql/upgrade_022_to_022.1.sh.in @@ -0,0 +1,103 @@ +#!/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}" != "22.0"; then + printf 'This script upgrades 22.0 to 22.1. ' + 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 "$@" </dev/null <