Adding upstream version 2.2.0.upstream/2.2.0upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1585 insertions, 0 deletions

diff --git a/src/bin/admin/tests/pgsql_tests.sh.in b/src/bin/admin/tests/pgsql_tests.sh.in
new file mode 100644
index 0000000..fb776e6
--- /dev/null
+++ b/src/bin/admin/tests/pgsql_tests.sh.in
@@ -0,0 +1,1585 @@
+#!/bin/sh
+
+# Copyright (C) 2015-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).
+
+# shellcheck disable=SC2154
+# SC2154: ... is referenced but not assigned.
+# Reason: some variables are sourced.
+
+# Exit with error if commands exit with non-zero and if undefined variables are
+# used.
+set -eu
+
+# Include common test library.
+. "@abs_top_builddir@/src/lib/testutils/dhcp_test_lib.sh"
+
+# Include admin utilities
+. "@abs_top_srcdir@/src/bin/admin/admin-utils.sh"
+
+# Set path to the production schema scripts
+db_scripts_dir="@abs_top_srcdir@/src/share/database/scripts"
+
+# Set location of the kea-admin.
+kea_admin="@abs_top_builddir@/src/bin/admin/kea-admin"
+
+# Convenience function for running an SQL statement
+# param hdr - text message to prepend to any error
+# param qry - SQL statement to run
+# param exp_value - optional expected value.  This can be used IF the SQL statement
+# generates a single value, such as a SELECT which returns one column for one row.
+# Examples:
+#
+#   qry="insert into lease6 (address, lease_type, subnet_id, state) values ($addr,$ltype,1,0);"
+#   run_statement "#2" "$qry"
+#
+#   qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0";
+#   run_statement "#3" "$qry" 1
+run_statement() {
+    hdr="$1";shift
+    qry="$1";shift
+    exp_value="${1-}" # Optional value. If not given, replace with empty string.
+
+    # Execute the statement
+    run_command \
+        pgsql_execute "${qry}"
+    value="${OUTPUT}"
+
+    # Execution should succeed
+    assert_eq 0 "${EXIT_CODE}" "$hdr: SQL=[$qry] failed: (expected status code %d, returned %d)"
+
+    # If there's an expected value, test it
+    if [ "x$exp_value" != "x" ]
+    then
+        assert_str_eq "$exp_value" "$value" "$hdr: SQL=[$qry] wrong: (expected value %s, returned %s)"
+    fi
+}
+
+# Wipe all tables from the DB:
+pgsql_wipe() {
+    printf "Wiping whole database %s...\n" "${db_name}"
+    export PGPASSWORD="${db_password}"
+
+    run_command \
+        psql --set ON_ERROR_STOP=1 -A -t -q -U keatest -d keatest -f "${db_scripts_dir}/pgsql/dhcpdb_drop.pgsql"
+    assert_eq 0 "${EXIT_CODE}" "pgsql_wipe drop failed, expected exit code: %d, actual: %d"
+}
+
+pgsql_db_init_test() {
+    test_start "pgsql.db-init"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    # Create the database
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init pgsql failed, expected exit code: %d, actual: %d"
+
+    # Verify that all the expected tables exist
+
+    # Check schema_version table
+    run_command \
+        pgsql_execute "SELECT version, minor FROM schema_version;"
+    assert_eq 0 "${EXIT_CODE}" "schema_version table check failed, expected exit code: %d, actual: %d"
+
+    # Check lease4 table
+    run_command \
+        pgsql_execute "SELECT address, hwaddr, client_id, valid_lifetime, expire, subnet_id, fqdn_fwd, fqdn_rev, hostname, state, user_context FROM lease4;"
+    assert_eq 0 "${EXIT_CODE}" "lease4 table check failed, expected exit code: %d, actual: %d"
+
+    # Check lease6 table
+    run_command \
+        pgsql_execute "SELECT address, duid, valid_lifetime, expire, subnet_id, pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, state, user_context FROM lease6;"
+    assert_eq 0 "${EXIT_CODE}" "lease6 table check failed, expected exit code: %d, actual: %d"
+
+    # Check lease6_types table
+    run_command \
+        pgsql_execute "SELECT lease_type, name FROM lease6_types;"
+    assert_eq 0 "${EXIT_CODE}" "lease6_types table check failed, expected exit code: %d, actual: %d"
+
+    # Check lease_state table
+    run_command \
+        pgsql_execute "SELECT state, name FROM lease_state;"
+    assert_eq 0 "${EXIT_CODE}" "lease_state table check failed, expected exit code: %d, actual: %d"
+
+    # Trying to create it again should fail.  This verifies the db present
+    # check
+    printf '\nDB created successfully, make sure we are not allowed to try it again:\n'
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 2 "${EXIT_CODE}" "kea-admin failed to deny db-init, expected exit code: %d, actual: %d"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+pgsql_db_version_test() {
+    test_start "pgsql.db-version"
+
+    # Wipe the whole database
+    pgsql_wipe
+
+    # Do not create any table so db-version will raise an error
+    printf 'Checking db-version error case...\n'
+    run_command \
+        "${kea_admin}" db-version pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}"
+    assert_eq 3 "${EXIT_CODE}" "schema_version table still exists. (expected %d, exit code %d)"
+
+    # Create the database
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "cannot initialize the database, expected exit code: %d, actual: %d"
+
+    # Verify that kea-admin db-version returns the latest version.
+    run_command \
+        "${kea_admin}" db-version pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}"
+    version="${OUTPUT}"
+    assert_str_eq "13.0" "${version}" "Expected kea-admin to return %s, returned value was %s"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+pgsql_upgrade_1_0_to_2_0() {
+    # Added state column to lease4
+    run_command \
+        pgsql_execute "select state from lease4;"
+    assert_eq 0 "${EXIT_CODE}" "lease4 is missing state column. (expected status code %d, returned %d)"
+
+    # Added state column to lease6
+    run_command \
+        pgsql_execute "select state from lease6;"
+    assert_eq 0 "${EXIT_CODE}" "lease6 is missing state column. (expected status code %d, returned %d)"
+
+    # Added stored procedures for lease dumps
+    run_command \
+        pgsql_execute "select lease4DumpHeader from lease4DumpHeader();"
+    assert_eq 0 "${EXIT_CODE}" "function lease4DumpHeader() broken or missing. (expected status code %d, returned %d)"
+
+    run_command \
+        pgsql_execute "select address from lease4DumpData();"
+    assert_eq 0 "${EXIT_CODE}" "function lease4DumpData() broken or missing. (expected status code %d, returned %d)"
+
+    run_command \
+        pgsql_execute "select lease6DumpHeader from lease6DumpHeader();"
+    assert_eq 0 "${EXIT_CODE}" "function lease6DumpHeader() broken or missing. (expected status code %d, returned %d)"
+
+    run_command \
+        pgsql_execute "select address from lease6DumpData();"
+    assert_eq 0 "${EXIT_CODE}" "function lease6DumpData() broken or missing. (expected status code %d, returned %d)"
+}
+
+pgsql_upgrade_2_0_to_3_0() {
+    # Added hwaddr, hwtype, and hwaddr_source columns to lease6 table
+    run_command \
+        pgsql_execute "select hwaddr, hwtype, hwaddr_source from lease6;"
+    assert_eq 0 "${EXIT_CODE}" "lease6 table not upgraded to 3.0 (expected status code %d, returned %d)"
+
+    # Added lease_hwaddr_source table
+    run_command \
+        pgsql_execute "select hwaddr_source, name from lease_hwaddr_source;"
+    assert_eq 0 "${EXIT_CODE}" "lease_hwaddr_source table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added hosts table
+    run_command \
+        pgsql_execute "select host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes, dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name, auth_key from hosts;"
+    assert_eq 0 "${EXIT_CODE}" "hosts table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added ipv6_reservations table
+    run_command \
+        pgsql_execute "select reservation_id, address, prefix_len, type, dhcp6_iaid, host_id from ipv6_reservations;"
+    assert_eq 0 "${EXIT_CODE}" "ipv6_reservations table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added dhcp4_options table
+    run_command \
+        pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id, scope_id from dhcp4_options;"
+    assert_eq 0 "${EXIT_CODE}" "dhcp4_options table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added dhcp6_options table
+    run_command \
+        pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"
+    assert_eq 0 "${EXIT_CODE}" "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added  host_identifier_type table
+    run_command \
+        pgsql_execute "select type, name from host_identifier_type;"
+    assert_eq 0 "${EXIT_CODE}" "host_identifier_type table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added dhcp_option_scope table
+    run_command \
+        pgsql_execute "select scope_id, scope_name from dhcp_option_scope;"
+    assert_eq 0 "${EXIT_CODE}" "dhcp_option_scope table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added dhcp6_options table
+    run_command \
+        pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"
+    assert_eq 0 "${EXIT_CODE}" "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"
+
+    # Added order by clause to lease4DumpData
+    run_command \
+        pgsql_execute "select address from lease4DumpData();"
+    assert_eq 0 "${EXIT_CODE}" "function lease4DumpData() broken or missing. (expected status code %d, returned %d)"
+    run_command \
+        pgsql_execute "\sf lease4DumpData"
+    assert_eq 0 "${EXIT_CODE}" "\sf of lease4DumpData failed. (expected status code %d, returned %d)"
+    count=$(echo "${OUTPUT}" | grep -Eci 'order by [a-z]*[\.]?address') || true
+    assert_eq 1 "${count}" "lease4DumpData is missing order by clause. (expected count %d, returned %d)"
+
+    # Added hwaddr columns to lease6DumpHeader
+    run_command \
+        pgsql_execute "select lease6DumpHeader from lease6DumpHeader();"
+    assert_eq 0 "${EXIT_CODE}" "function lease6DumpHeader() broken or missing. (expected status code %d, returned %d)"
+    count=$(echo "${OUTPUT}" | grep -Fci 'hwaddr') || true
+    assert_eq 1 "${count}" "lease6DumpHeader is missing the hwaddr column"
+    count=$(echo "${OUTPUT}" | grep -Fci 'hwtype') || true
+    assert_eq 1 "${count}" "lease6DumpHeader is missing the hwtype column"
+    count=$(echo "${OUTPUT}" | grep -Fci 'hwaddr_source') || true
+    assert_eq 1 "${count}" "lease6DumpHeader is missing the hwaddr_source column"
+
+    # Added hwaddr columns to lease6DumpData
+    run_command \
+        pgsql_execute "select hwaddr,hwtype,hwaddr_source from lease6DumpData();"
+    assert_eq 0 "${EXIT_CODE}" "function lease6DumpData() broken or missing. (expected status code %d, returned %d)"
+
+    # Added order by clause to lease6DumpData
+    run_command \
+        pgsql_execute "\sf lease6DumpData"
+    assert_eq 0 "${EXIT_CODE}" "\sf of lease6DumpData failed. (expected status code %d, returned %d)"
+    count=$(echo "${OUTPUT}" | grep -Eci 'order by [a-z]*[\.]?address') || true
+    assert_eq 1 "${count}" "lease6DumpData is missing order by clause. (expected count %d, returned %d)"
+
+    # lease_hardware_source should have row for source = 0
+    run_command \
+        pgsql_execute "select count(hwaddr_source) from lease_hwaddr_source where hwaddr_source = 0 and name='HWADDR_SOURCE_UNKNOWN';"
+    assert_eq 0 "${EXIT_CODE}" "select from lease_hwaddr_source failed. (expected status code %d, returned %d)"
+    assert_eq 1 "${OUTPUT}" "lease_hwaddr_source does not contain entry for HWADDR_SOURCE_UNKNOWN. (record count %d, expected %d)"
+}
+
+pgsql_upgrade_3_0_to_6_1() {
+    # Added user_context to lease4
+    run_command \
+        pgsql_execute "select user_context from lease4;"
+    assert_eq 0 "${EXIT_CODE}" "lease4 is missing user_context column. (expected status code %d, returned %d)"
+
+    # Added user_context to lease6
+    run_command \
+        pgsql_execute "select user_context from lease6;"
+    assert_eq 0 "${EXIT_CODE}" "lease6 is missing user_context column. (expected status code %d, returned %d)"
+
+    # Added logs table
+    run_command \
+        pgsql_execute "select timestamp, address, log from logs;"
+    assert_eq 0 "${EXIT_CODE}" "logs table is missing or broken. (expected status code %d, returned %d)"
+}
+
+pgsql_upgrade_6_1_to_6_2() {
+    insert_sql="\
+insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_address) values (decode('010101010101', 'hex'), 0, 1, x'FFAF0002'::int);\
+insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_address) values (decode('010101010102', 'hex'), 0, 1, x'FFAF0002'::int);"
+    run_command \
+        pgsql_execute "$insert_sql"
+    assert_eq 0 "${EXIT_CODE}" "insert into hosts failed, expected exit code %d, actual %d"
+}
+
+pgsql_upgrade_6_2_to_7_0() {
+    # dhcp4_server should have a single entry for 'all'
+    select_sql="SELECT id, tag, description, modification_ts from dhcp4_server where id = 1 and tag = 'all';"
+    run_command \
+        pgsql_execute "$select_sql"
+
+    assert_eq 0 "${EXIT_CODE}" "the dhcp4_server table is broken or missing. (expected status code %d, returned %d)"
+
+    # dhcp6_server should have a single entry for 'all'
+    select_sql="SELECT id, tag, description, modification_ts from dhcp6_server where id = 1 and tag = 'all';"
+    run_command \
+        pgsql_execute "$select_sql"
+
+    assert_eq 0 "${EXIT_CODE}" "the dhcp6_server table is broken or missing. (expected status code %d, returned %d)"
+
+    # Verify that session variable setting is present and functional.
+    session_sql="\
+select get_session_value('kea.text'); \
+select set_session_value('kea.text', 'booya'); \
+select get_session_value('kea.text'); \
+select get_session_boolean('kea.bool'); \
+select set_session_value('kea.bool', true); \
+select get_session_boolean('kea.bool'); \
+select get_session_big_int('kea.bigint'); \
+select set_session_value('kea.bigint', cast('1984' as BIGINT)); \
+select get_session_big_int('kea.bigint'); \
+"
+    run_command \
+        pgsql_execute "$session_sql"
+
+    assert_eq 0 "${EXIT_CODE}" "session variable handling broken. (expected status code %d, returned %d)"
+    clean_out=$(echo "${OUTPUT}" | tr '\n' ' ')
+    assert_str_eq "  booya f  t 0  1984 "  "${clean_out}" "session variable output incorrect"
+}
+
+pgsql_upgrade_7_0_to_8_0() {
+    run_command \
+        pgsql_execute "$session_sql"
+
+    # Added class_id to dhcp4_option_def
+    run_command \
+        pgsql_execute "select class_id from dhcp4_option_def;"
+    assert_eq 0 "${EXIT_CODE}" "dhcp4_option_def is missing class_id column. (expected status code %d, returned %d)"
+
+    # Added class_id to dhcp6_option_def
+    run_command \
+        pgsql_execute "select class_id from dhcp6_option_def;"
+    assert_eq 0 "${EXIT_CODE}" "dhcp6_option_def is missing class_id column. (expected status code %d, returned %d)"
+
+    # Added preferred lifetime columns to dhcp6_client_class.
+    run_command \
+        pgsql_execute "select preferred_lifetime, min_preferred_lifetime, max_preferred_lifetime from dhcp6_client_class;"
+    assert_eq 0 "${EXIT_CODE}" "dhcp6_client_class is missing preferred lifetime column(s). (expected status code %d, returned %d)"
+
+    # Check the output of colonSeparatedHex().
+    run_command \
+        pgsql_execute "SELECT colonSeparatedHex('f123456789');"
+    assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d'
+    assert_str_eq 'f1:23:45:67:89' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT colonSeparatedHex('');"
+    assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d'
+    assert_str_eq '' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT colonSeparatedHex('f');"
+    assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d'
+    assert_str_eq '0f' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT colonSeparatedHex('f1');"
+    assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d'
+    assert_str_eq 'f1' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT colonSeparatedHex('f12');"
+    assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d'
+    assert_str_eq '0f:12' "${OUTPUT}"
+
+    # Check lease4Dump*().
+    run_command \
+        pgsql_execute "INSERT INTO lease4 VALUES(10,E'\\\\x3230',E'\\\\x3330',40,TO_TIMESTAMP(1678900000),50,'t','t','one,example,com',0,'{ \"a\": 1, \"b\": 2 }');"
+    assert_eq 0 "${EXIT_CODE}" 'INSERT INTO lease4 failed, expected exit code %d, actual %d'
+    assert_str_eq '' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT * FROM lease4DumpHeader();"
+    assert_eq 0 "${EXIT_CODE}" 'lease4DumpHeader() failed, expected exit code %d, actual %d'
+    assert_str_eq 'address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state,user_context' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT * FROM lease4DumpData();" --field-separator=','
+    assert_eq 0 "${EXIT_CODE}" 'lease4DumpData() failed, expected exit code %d, actual %d'
+    assert_str_eq '0.0.0.10,32:30,33:30,40,1678900000,50,1,1,one&#x2cexample&#x2ccom,0,{ "a": 1&#x2c "b": 2 }' "${OUTPUT}"
+
+    # Check lease6Dump*().
+    run_command \
+        pgsql_execute "INSERT INTO lease6 VALUES('::10',E'\\\\x3230',30,TO_TIMESTAMP(1678900000),40,50,1,60,70,'t','t','one,example,com',0,E'\\\\x3830',16,0,'{ \"a\": 1, \"b\": 2 }');"
+    assert_eq 0 "${EXIT_CODE}" 'INSERT INTO lease6 failed, expected exit code %d, actual %d'
+    assert_str_eq '' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT * FROM lease6DumpHeader();"
+    assert_eq 0 "${EXIT_CODE}" 'lease6DumpHeader() failed, expected exit code %d, actual %d'
+    assert_str_eq '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' "${OUTPUT}"
+
+    run_command \
+        pgsql_execute "SELECT * FROM lease6DumpData();" --field-separator=','
+    assert_eq 0 "${EXIT_CODE}" 'lease6DumpData() failed, expected exit code %d, actual %d'
+    assert_str_eq '::10,32:30,30,1678900000,40,50,1,60,70,1,1,one&#x2cexample&#x2ccom,38:30,0,{ "a": 1&#x2c "b": 2 },16,0' "${OUTPUT}"
+
+    # Check lease4Upload().
+    run_command \
+        pgsql_execute "SELECT lease4Upload('192.0.0.0','ff0102030405','01ff0102030405',7200,1234567890,1,0,0,'',0,'');"
+    assert_eq 0 "${EXIT_CODE}" 'lease4Upload() failed, expected exit code %d, actual %d'
+    assert_str_eq '' "${OUTPUT}"
+
+    # Check lease6Upload().
+    run_command \
+        pgsql_execute "SELECT lease6Upload('2001:db8::','000100012955cb80ff0102030407',7200,1234567890,1,3600,0,1,128,0,0,'','ff0102030407',0,'',90,16);"
+    assert_eq 0 "${EXIT_CODE}" 'lease6Upload() failed, expected exit code %d, actual %d'
+    assert_str_eq '' "${OUTPUT}"
+}
+
+pgsql_upgrade_8_0_to_9_0() {
+    run_command \
+        pgsql_execute "$session_sql"
+
+    # Most changes are not readily testable without querying the information schema,
+    # not sure the effort is worthwhile.  Verify that function gmt_epoch() was created.
+    run_command \
+        pgsql_execute "select gmt_epoch(now());"
+
+    assert_eq 0 "${EXIT_CODE}" "function gmt_epoch() broken or missing. (expected status code %d, returned %d)"
+
+}
+
+pgsql_upgrade_9_0_to_10_0() {
+    run_command \
+        pgsql_execute "$session_sql"
+
+    # Get function source code so we can check that it returns NEW.
+    # Function name must be lower case for WHERE clause.
+    run_command \
+         pgsql_execute "select proname,prosrc from pg_proc where proname='func_dhcp6_client_class_check_dependency_bins'"
+
+    assert_eq 0 "${EXIT_CODE}" "function func_dhcp6_client_class_check_dependency_BINS() broken or missing. (expected status code %d, returned %d)"
+
+    count=$(echo "${OUTPUT}" | grep -Eci 'RETURN NEW') || true
+    assert_eq 1 "${count}" "func_dhcp6_client_class_check_dependency_BINS is missing RETURN NEW. (expected count %d, returned %d)"
+}
+
+pgsql_upgrade_10_0_to_11_0() {
+    run_command \
+        pgsql_execute "$session_sql"
+
+    # Get function source code so we can check that it returns NEW.
+    # Function name must be lower case for WHERE clause.
+    run_command \
+         pgsql_execute "select proname,prosrc from pg_proc where proname='createoptionauditdhcp6'"
+
+    assert_eq 0 "${EXIT_CODE}" "function createOptionAuditDHCP6() broken or missing. (expected status code %d, returned %d)"
+
+    count=$(echo "${OUTPUT}" | grep -Eci 'SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pd_pool_id') || true
+    assert_eq 1 "${count}" "function createOptionAuditDHCP6() is missing changed line. (expected count %d, returned %d)"
+}
+
+pgsql_upgrade_11_0_to_12_0() {
+    run_command \
+        pgsql_execute "$session_sql"
+
+    # Check function source code
+    run_command \
+         pgsql_execute "select proname,prosrc from pg_proc where proname='func_dhcp4_shared_network_bdel'"
+
+    assert_eq 0 "${EXIT_CODE}" "function func_dhcp4_shared_network_BDEL() broken or missing. (expected status code %d, returned %d)"
+
+    count=$(echo "${OUTPUT}" | grep -Eci 'UPDATE dhcp4_subnet SET shared_network_name = NULL') || true
+    assert_eq 1 "${count}" "function func_dhcp4_shared_network_BDEL() is missing changed line. (expected count %d, returned %d)"
+
+    # Check function source code
+    run_command \
+         pgsql_execute "select proname,prosrc from pg_proc where proname='func_dhcp6_shared_network_bdel'"
+
+    assert_eq 0 "${EXIT_CODE}" "function func_dhcp6_shared_network_BDEL() broken or missing. (expected status code %d, returned %d)"
+
+    count=$(echo "${OUTPUT}" | grep -Eci 'UPDATE dhcp6_subnet SET shared_network_name = NULL') || true
+    assert_eq 1 "${count}" "function func_dhcp6_shared_network_BDEL() is missing changed line. (expected count %d, returned %d)"
+
+    # user_context should have been added to dhcp4_client_class
+    qry="select user_context from dhcp4_client_class limit 1;"
+    run_command \
+        pgsql_execute "${qry}"
+    assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)"
+
+    # user_context should have been added to dhcp6_client_class
+    qry="select user_context from dhcp6_client_class limit 1;"
+    run_command \
+        pgsql_execute "${qry}"
+    assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)"
+}
+
+pgsql_upgrade_12_to_13_test() {
+    # -- lease counting tests --
+
+    # Clean up.
+    query='DELETE FROM lease4; DELETE FROM lease6;'
+    run_command \
+        pgsql_execute "${query}"
+    assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+    assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+    # Populate the lease tables. Also check that @json_supported is NULL at
+    # first, and then it is set after inserting leases.
+    run_command \
+        pgsql_execute "
+            INSERT INTO lease4 (address, subnet_id, state, user_context) VALUES (100,1,0,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease4 (address, subnet_id, state, user_context) VALUES (101,1,1,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease4 (address, subnet_id, state, user_context) VALUES (102,1,2,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease4 (address, subnet_id, state, user_context) VALUES (103,1,0,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease4 (address, subnet_id, state, user_context) VALUES (104,1,1,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease4 (address, subnet_id, state, user_context) VALUES (105,1,1,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (100,0,1,0,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (101,0,1,1,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (102,0,1,0,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (103,0,1,1,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (104,2,1,0,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (105,2,1,1,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (106,2,1,0,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+            INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (107,2,1,1,
+                '{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
+        "
+    assert_eq 0 "${EXIT_CODE}" 'INSERT INTO leases when upgrading from 11 to 12 failed. expected %d, returned %d'
+    assert_str_eq '' "${OUTPUT}" "INSERT INTO leases when upgrading from 11 to 12 failed. expected output %s, returned %s"
+
+    # Check that @json_supported is NULL by default.
+    query='SELECT isJsonSupported()'
+    run_command \
+        pgsql_execute "${query}"
+    assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+    json_supported="${OUTPUT}"
+    if test "${json_supported}" != 'f' && test "${json_supported}" != 't'; then
+        assert_str_eq '[ft]' "${json_supported}" "${query}. expected '[ft]', returned '${json_supported}'"
+    fi
+
+    for v in 4 6; do
+        # Check that client classes were counted correctly.
+        query="SELECT leases FROM lease${v}_stat_by_client_class WHERE client_class = 'foo' LIMIT 1;"
+        run_command \
+            pgsql_execute "${query}"
+        assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        if test "${json_supported}" = 't'; then
+            assert_str_eq 2 "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        # -- Verify some calls to checkLeaseXLimits(). --
+
+        query="SELECT checkLease${v}Limits('');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        fi
+        assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        query="SELECT checkLease${v}Limits('{}');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        fi
+        assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 1 } ] } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq "address limit 1 for client class \"foo\", current lease count 2" "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 1 } } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq "address limit 1 for subnet ID 1, current lease count 2" "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 2 } ] } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq "address limit 2 for client class \"foo\", current lease count 2" "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 2 } } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq "address limit 2 for subnet ID 1, current lease count 2" "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 4 } ] } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        fi
+        assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 4 } } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        fi
+        assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 1 }, { \"name\": \"bar\", \"address-limit\": 1 } ], \"subnet\": { \"id\": 1, \"address-limit\": 1 } } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq "address limit 1 for client class \"foo\", current lease count 2" "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 2 }, { \"name\": \"bar\", \"address-limit\": 4 } ], \"subnet\": { \"id\": 1, \"address-limit\": 4 } } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq "address limit 2 for client class \"foo\", current lease count 2" "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 4 }, { \"name\": \"bar\", \"address-limit\": 4 } ], \"subnet\": { \"id\": 1, \"address-limit\": 2 } } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq "address limit 2 for subnet ID 1, current lease count 2" "${OUTPUT}" "${query}: expected output %s, returned %s"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 4 }, { \"name\": \"bar\", \"address-limit\": 4 } ], \"subnet\": { \"id\": 1, \"address-limit\": 4 } } } }');"
+        run_command \
+            pgsql_execute "${query}"
+        if test "${json_supported}" = 't'; then
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        else
+            # Should fail with ERROR: operator does not exist: json -> unknown
+            assert_eq 3 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        fi
+        assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+    done
+
+    # Check that leases counters cannot go negative.
+    for v in 4 6; do
+        query="SELECT leases FROM lease${v}_stat WHERE subnet_id = 1 AND state = 0 LIMIT 1"
+        run_command \
+            pgsql_execute "${query}"
+        assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        assert_str_eq '2' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        # Artificially change the subnet counter from 2 down to 1.
+        query="UPDATE lease${v}_stat SET leases = 1 WHERE subnet_id = 1 AND state = 0"
+        run_command \
+            pgsql_execute "${query}"
+        assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        if test "${json_supported}" = 't'; then
+            query="SELECT leases FROM lease${v}_stat_by_client_class WHERE client_class = 'foo' LIMIT 1"
+            run_command \
+                pgsql_execute "${query}"
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '2' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+            # Artificially change the client class counter from 2 down to 1.
+            query="UPDATE lease${v}_stat_by_client_class SET leases = 1 WHERE client_class = 'foo'"
+            run_command \
+                pgsql_execute "${query}"
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+
+        # Clean up.
+        query="DELETE FROM lease${v}"
+        run_command \
+            pgsql_execute "${query}"
+        assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        # SELECT should finish successfully and the subnet counter should be 0.
+        query="SELECT leases FROM lease${v}_stat WHERE subnet_id = 1 AND state = 0 LIMIT 1"
+        run_command \
+            pgsql_execute "${query}"
+        assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+        assert_str_eq '0' "${OUTPUT}" "${query}: expected output %s, returned %s"
+
+        if test "${json_supported}" = 't'; then
+            # SELECT should finish successfully and the client class counter should be 0.
+            query="SELECT leases FROM lease${v}_stat_by_client_class WHERE client_class = 'foo' LIMIT 1"
+            run_command \
+                pgsql_execute "${query}"
+            assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d"
+            assert_str_eq '0' "${OUTPUT}" "${query}: expected output %s, returned %s"
+        fi
+    done
+}
+
+pgsql_upgrade_test() {
+    test_start "pgsql.upgrade-test"
+
+    # Wipe the whole database
+    pgsql_wipe
+
+    # Initialize database to schema 1.0.
+    run_command \
+        pgsql_execute_script "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql"
+    assert_eq 0 "${EXIT_CODE}" "cannot initialize the database, expected exit code: %d, actual: %d"
+
+    run_command \
+        "${kea_admin}" db-upgrade pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "db-upgrade failed, expected exit code: %d, actual: %d"
+
+    # 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 "13.0" "${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
+
+    # Check 2.0 to 3.0 upgrade
+    pgsql_upgrade_2_0_to_3_0
+
+    # Check 3.0 to 6.1 upgrade
+    pgsql_upgrade_3_0_to_6_1
+
+    # Check 6.1 to 6.2 upgrade
+    pgsql_upgrade_6_1_to_6_2
+
+    # Check 6.2 to 7.0 upgrade
+    pgsql_upgrade_6_2_to_7_0
+
+    # Check 7.0 to 8.0 upgrade
+    pgsql_upgrade_7_0_to_8_0
+
+    # Check 8.0 to 9.0 upgrade
+    pgsql_upgrade_8_0_to_9_0
+
+    # Check 9.0 to 10.0 upgrade
+    pgsql_upgrade_9_0_to_10_0
+
+    # Check 10.0 to 11.0 upgrade
+    pgsql_upgrade_10_0_to_11_0
+
+    # Check 11.0 to 12.0 upgrade
+    pgsql_upgrade_11_0_to_12_0
+
+    # Check 12 to 13 upgrade
+    pgsql_upgrade_12_to_13_test
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# Test verifies the ability to dump lease4 data to CSV file
+# The dump output file is compared against a reference file.
+# If the dump is successful, the file contents will be the
+# same.  Note that the expire field in the lease4 table
+# is of data type "timestamp with timezone". This means that
+# the dumped file content is dependent upon the timezone
+# setting the PostgreSQL server is using.  To account for
+# this the reference data contains a tag, "<timestamp>"
+# where the expire column's data would normally be. This
+# tag is replaced during text execution with a value
+# determined by querying the PostgreSQL server.  This
+# updated reference data is captured in a temporary file
+# which is used for the actual comparison.
+# May accept additional parameters to be passed to lease-dump.
+pgsql_lease4_dump_test() {
+    test_start "pgsql.lease4_dump_test"
+
+    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
+    output_dir="@abs_top_builddir@/src/bin/admin/tests"
+
+    output_file="$output_dir/data/pgsql.lease4_dump_test.output.csv"
+    ref_file="$test_dir/data/lease4_dump_test.reference.csv"
+
+    # Clean up any test files left from prior failed runs unless -y was provided in which case
+    # explicitly create the file to check that it will be automatically deleted.
+    # files should be removed by kea-admin itself.
+    if printf '%s' "${@}" | grep 'y' > /dev/null; then
+        touch "${output_file}"
+        touch "${output_file}.tmp"
+    else
+        rm -f "${output_file}"
+        rm -f "${output_file}.tmp"
+    fi
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    # Ok, now let's initialize the database
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "could not create database, expected exit code %d, actual %d"
+
+    # Insert the reference records. Normally, for the bytea values, you would have two backslashes.
+    # Because shell evaluates the double quoted string one more time, they need to be doubled.
+    # Otherwise, the value is interpreted as ASCII instead of raw bytes.
+    insert_sql="\
+insert into lease4 values(10,E'\\\\x3230',E'\\\\x3330',40,TO_TIMESTAMP(1642000000),50,'t','t','one.example.com',0,'');\
+insert into lease4 values(11,'',E'\\\\x313233',40,TO_TIMESTAMP(1643210000),50,'t','t','',1,'{  }');\
+insert into lease4 values(12,E'\\\\x3232','',40,TO_TIMESTAMP(1643212345),50,'t','t','three,example,com',2,'{ \"a\": 1, \"b\": \"c\" }');"
+
+    run_command \
+        pgsql_execute "$insert_sql"
+    assert_eq 0 "${EXIT_CODE}" "insert into lease4 failed, expected exit code %d, actual %d"
+
+    # Dump lease4 to output_file
+    run_command \
+        "${kea_admin}" lease-dump pgsql -4 -u "${db_user}" -p "${db_password}" -n "${db_name}" \
+            -d "${db_scripts_dir}" -o "${output_file}" "${@}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-dump -4 failed, expected exit code %d, actual %d"
+
+    # Compare the dump output to reference file, they should be identical
+    run_command \
+        cmp -s "${output_file}" "${ref_file}"
+    assert_eq 0 "${EXIT_CODE}" "dump file does not match reference file, expected exit code %d, actual %d, diff:\n$(diff "${ref_file}" "${output_file}")"
+
+    # Remove the output file.
+    rm -f "${output_file}"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# Test verifies the ability to dump lease6 data to CSV file
+# The dump output file is compared against a reference file.
+# If the dump is successful, the file contents will be the
+# same.  Note that the expire field in the lease6 table
+# is of data type "timestamp with timezone". This means that
+# the dumped file content is dependent upon the timezone
+# setting the PostgreSQL server is using.  To account for
+# this the reference data contains a tag, "<timestamp>"
+# where the expire column's data would normally be. This
+# tag is replaced during text execution with a value
+# determined by querying the PostgreSQL server.  This
+# updated reference data is captured in a temporary file
+# which is used for the actual comparison.
+pgsql_lease6_dump_test() {
+    test_start "pgsql.lease6_dump_test"
+
+    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
+    output_dir="@abs_top_builddir@/src/bin/admin/tests"
+
+    output_file="$output_dir/data/pgsql.lease6_dump_test.output.csv"
+    ref_file="$test_dir/data/lease6_dump_test.reference.csv"
+
+    # Clean up any test files left from prior failed runs unless -y was provided in which case
+    # explicitly create the file to check that it will be automatically deleted.
+    # files should be removed by kea-admin itself.
+    if printf '%s' "${@}" | grep 'y' > /dev/null; then
+        touch "${output_file}"
+        touch "${output_file}.tmp"
+    else
+        rm -f "${output_file}"
+        rm -f "${output_file}.tmp"
+    fi
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    # Ok, now let's initialize the database
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "could not create database,  status code %d"
+
+    # Insert the reference records. Normally, for the bytea values, you would have two backslashes.
+    # Because shell evaluates the double quoted string one more time, they need to be doubled.
+    # Otherwise, the value is interpreted as ASCII instead of raw bytes.
+    insert_sql="\
+insert into lease6 values('::10',E'\\\\x3230',30,TO_TIMESTAMP(1642000000),40,50,1,60,70,'t','t','one.example.com',0,decode(encode('80','hex'),'hex'),90,16,''); \
+insert into lease6 values('::11',E'\\\\x3231',30,TO_TIMESTAMP(1643210000),40,50,1,60,70,'t','t','',1,decode(encode('80','hex'),'hex'),90,1,'{  }'); \
+insert into lease6 values('::12',E'\\\\x3232',30,TO_TIMESTAMP(1643212345),40,50,1,60,70,'t','t','three,example,com',2,decode(encode('80','hex'),'hex'),90,4,'{ \"a\": 1, \"b\": \"c\" }');"
+
+    run_command \
+        pgsql_execute "$insert_sql"
+    assert_eq 0 "${EXIT_CODE}" "insert into lease6 failed, expected exit code %d, actual %d"
+
+    # Dump lease6 to output_file
+    run_command \
+        "${kea_admin}" lease-dump pgsql -6 -u "${db_user}" -p "${db_password}" -n "${db_name}" \
+            -d "${db_scripts_dir}" -o "${output_file}" "${@}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-dump -6 failed, expected exit code %d, actual %d"
+
+    # Compare the dump output to reference file, they should be identical
+    run_command \
+        cmp -s "${output_file}" "${ref_file}"
+    assert_eq 0 "${EXIT_CODE}" "dump file does not match reference file, expected exit code %d, actual %d, diff:\n$(diff "${ref_file}" "${output_file}")"
+
+    # Remove the output file.
+    rm -f "${output_file}"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# May accept additional parameters to be passed to lease-dump or to lease-upload.
+pgsql_lease4_upload_test() {
+    test_start "pgsql.lease4_upload_test"
+
+    input_file="@abs_top_srcdir@/src/bin/admin/tests/data/lease4_dump_test.reference.csv"
+    output_file="@abs_top_srcdir@/src/bin/admin/tests/data/lease4_dump_test.output.csv"
+
+    # Wipe the whole database.
+    pgsql_wipe
+
+    # Clean up any test files left from prior failed runs unless -y was provided in which case
+    # explicitly create the file to check that it will be automatically deleted.
+    # files should be removed by kea-admin itself.
+    if printf '%s' "${@}" | grep 'y' > /dev/null; then
+        touch "${input_file}.tmp"
+        touch "${output_file}"
+        touch "${output_file}.tmp"
+    else
+        rm -f "${input_file}.tmp"
+        rm -f "${output_file}"
+        rm -f "${output_file}.tmp"
+    fi
+
+    # Initialize the database.
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" \
+            -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "could not create database, expected exit code %d, actual %d"
+
+    # Upload leases.
+    run_command \
+        "${kea_admin}" lease-upload pgsql -4 -u "${db_user}" \
+            -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" \
+            -i "${input_file}" "${@}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-upload -4 failed, expected exit code %d, actual %d"
+
+    # Dump leases.
+    run_command \
+        "${kea_admin}" lease-dump pgsql -4 -u "${db_user}" \
+            -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" \
+            -o "${output_file}" "${@}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-dump -4 failed, expected exit code %d, actual %d"
+
+    # Compare the initial file used for upload to the file retrieved from dump, they should be identical.
+    run_command \
+        cmp -s "${input_file}" "${output_file}"
+    assert_eq 0 "${EXIT_CODE}" "file resulted from dump after upload does not match file used for upload, expected exit code %d, actual %d, diff:\n$(diff "${input_file}" "${output_file}")"
+
+    # Remove the output file.
+    rm -f "${output_file}"
+
+    # Wipe the whole database.
+    pgsql_wipe
+
+    test_finish 0
+}
+
+pgsql_lease6_upload_test() {
+    test_start "pgsql.lease6_upload_test"
+
+    input_file="@abs_top_srcdir@/src/bin/admin/tests/data/lease6_dump_test.reference.csv"
+    output_file="@abs_top_srcdir@/src/bin/admin/tests/data/lease6_dump_test.output.csv"
+
+    # Wipe the whole database.
+    pgsql_wipe
+
+    # Clean up any test files left from prior failed runs unless -y was provided in which case
+    # explicitly create the file to check that it will be automatically deleted.
+    # files should be removed by kea-admin itself.
+    if printf '%s' "${@}" | grep 'y' > /dev/null; then
+        touch "${input_file}.tmp"
+        touch "${output_file}"
+        touch "${output_file}.tmp"
+    else
+        rm -f "${input_file}.tmp"
+        rm -f "${output_file}"
+        rm -f "${output_file}.tmp"
+    fi
+
+    # Initialize the database.
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" \
+            -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "could not create database, expected exit code %d, actual %d"
+
+    # Upload leases.
+    run_command \
+        "${kea_admin}" lease-upload pgsql -6 -u "${db_user}" \
+            -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" \
+            -i "${input_file}" "${@}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-upload -6 failed, expected exit code %d, actual %d"
+
+    # Dump leases.
+    run_command \
+        "${kea_admin}" lease-dump pgsql -6 -u "${db_user}" \
+            -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" \
+            -o "${output_file}" "${@}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin lease-dump -6 failed, expected exit code %d, actual %d"
+
+    # Compare the initial file used for upload to the file retrieved from dump, they should be identical.
+    run_command \
+        cmp -s "${input_file}" "${output_file}"
+    assert_eq 0 "${EXIT_CODE}" "file resulted from dump after upload does not match file used for upload, expected exit code %d, actual %d, diff:\n$(diff "${input_file}" "${output_file}")"
+
+    # Remove the output file.
+    rm -f "${output_file}"
+
+    # Wipe the whole database.
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# Upgrades an existing schema to a target newer version
+# param target_version - desired schema version as "major.minor"
+pgsql_upgrade_schema_to_version() {
+    target_version=$1
+
+    # Check if the scripts directory exists at all.
+    if [ ! -d ${db_scripts_dir}/pgsql ]; then
+        log_error "Invalid scripts directory: ${db_scripts_dir}/pgsql"
+        exit 1
+    fi
+
+    # Check if there are any files in it
+    num_files=$(find ${db_scripts_dir}/pgsql/upgrade*.sh -type f | wc -l)
+    if [ "${num_files}" -eq 0 ]; then
+        log_error "No scripts in ${db_scripts_dir}/pgsql?"
+        exit 1
+    fi
+
+    # Postgres psql does not accept pw on command line, but can do it
+    # thru an env
+    export PGPASSWORD=$db_password
+
+    for script in "${db_scripts_dir}"/pgsql/upgrade*.sh
+    do
+        version=$(pgsql_version)
+        if [ "${version}" = "${target_version}" ]
+        then
+            break
+        fi
+
+        echo "Processing $script file..."
+        "${script}" -U "${db_user}" -d "${db_name}"
+    done
+
+    echo "Schema upgraded to $version"
+}
+
+# Verifies lease4_stat trigger operations on
+# an new, empty database.  It inserts, updates, and
+# deletes various leases, checking lease4_stat
+# values along the way.
+pgsql_lease4_stat_test() {
+    test_start "pgsql.lease4_stat_test"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    # Ok, now let's initialize the database
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init pgsql failed, expected %d, returned non-zero status code %d"
+
+    # Verify lease4 stat table is present
+    qry="select count(subnet_id) from lease4_stat"
+    run_statement "#1" "$qry" 0
+
+    # Insert lease4
+    qry="insert into lease4 (address, subnet_id, state) values (111,1,0)"
+    run_statement "#2" "$qry"
+
+    # Assigned state count should be 1
+    qry="select leases from lease4_stat where subnet_id = 1 and state = 0"
+    run_statement "#3" "$qry" 1
+
+    # Set lease state to declined
+    qry="update lease4 set state = 1 where address = 111"
+    run_statement "#4" "$qry"
+
+    # Leases state count for assigned should be 0
+    qry="select leases from lease4_stat where subnet_id = 1 and state = 0"
+    run_statement "#5" "$qry" 0
+
+    # Leases state count for declined should be 1
+    qry="select leases from lease4_stat where subnet_id = 1 and state = 1"
+    run_statement "#6" "$qry" 1
+
+    # Delete the lease
+    qry="delete from lease4 where address = 111"
+    run_statement "#7" "$qry"
+
+    # Leases state count for declined should be 0
+    qry="select leases from lease4_stat where subnet_id = 1 and state = 1"
+    run_statement "#8" "$qry" 0
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# Verifies that lease6_stat triggers operate correctly
+# for using a given address and lease_type.  It will
+# insert a lease, update it, and delete checking the
+# lease stat counts along the way.  It assumes the
+# database has been created but is empty.
+# param addr - address to use to add to subnet 1
+# param ltype - type of lease to create
+pgsql_lease6_stat_per_type() {
+    addr=$1;shift
+    ltype=$1
+
+    # insert a lease6 for addr and ltype, state assigned
+    qry="insert into lease6 (address, lease_type, subnet_id, state) values ('$addr',$ltype,1,0);"
+    run_statement "#2" "$qry"
+
+    # assigned stat should be 1
+    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0";
+    run_statement "#3" "$qry" 1
+
+    # update the lease, changing state to declined
+    qry="update lease6 set state = 1 where address = '$addr'"
+    run_statement "#4" "$qry"
+
+    # leases stat for assigned state should be 0
+    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and  state = 0";
+    run_statement "#5" "$qry" 0
+
+    # leases count for declined state should be 1
+    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and  state = 1";
+    run_statement "#6" "$qry" 1
+
+    # delete the lease
+    qry="delete from lease6 where address = '$addr';"
+    run_statement "#7" "$qry"
+
+    # leases count for declined state should be 0
+    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and  state = 0";
+    run_statement "#6" "$qry" 0
+}
+
+# Verifies that lease6_stat triggers operation correctly
+# for both NA and PD lease types, pgsql_lease6_stat_per_type()
+pgsql_lease6_stat_test() {
+
+    test_start "pgsql.lease6_stat_test"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    # Ok, now let's initialize the database
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init pgsql failed, expected %d, returned non-zero status code %d"
+
+    # verify lease6 stat table is present
+    qry="select count(subnet_id) from lease6_stat"
+    run_statement "#1" "$qry"
+
+    # Test for address 111, NA lease type
+    pgsql_lease6_stat_per_type "111" "0"
+
+    # Test for address 222, PD lease type
+    pgsql_lease6_stat_per_type "222" "1"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# Verifies that you can upgrade from earlier version and
+# lease<4/6>_stat tables will be populated based on existing
+# leases and that the stat triggers work properly.
+pgsql_lease_stat_upgrade_test() {
+    test_start "pgsql.lease_stat_upgrade_test"
+
+    # 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 2.0, this has lease_state in it
+    pgsql_upgrade_schema_to_version 2.0
+
+    # Now we need insert some leases to "migrate" for both v4 and v6
+    qry=\
+"insert into lease4 (address, subnet_id, state) values (111,10,0);\
+ insert into lease4 (address, subnet_id, state) values (222,10,0);\
+ insert into lease4 (address, subnet_id, state) values (333,10,1);\
+ insert into lease4 (address, subnet_id, state) values (444,10,2);\
+ insert into lease4 (address, subnet_id, state) values (555,77,0);"
+    run_statement "insert v4 leases" "$qry"
+
+    qry=\
+"insert into lease6 (address, lease_type, subnet_id, state) values (111,0,40,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (222,0,40,1);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (333,1,40,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (444,1,50,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (555,1,50,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (666,1,40,2);"
+    run_statement "insert v6 leases" "$qry"
+
+    # Ok, we have a 2.0 database with leases. Let's upgrade it to 4.0
+    run_command \
+        "${kea_admin}" db-upgrade pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+
+    #
+    # First we'll verify lease4_stats are correct after migration.
+    #
+
+    # Assigned leases for subnet 10 should be 2
+    qry="select leases from lease4_stat where subnet_id = 10 and state = 0"
+    run_statement "#4.1" "$qry" 2
+
+    # Assigned leases for subnet 77 should be 1
+    qry="select leases from lease4_stat where subnet_id = 77 and state = 0"
+    run_statement "#4.2" "$qry" 1
+
+    # Should be no records for EXPIRED
+    qry="select count(subnet_id) from lease4_stat where state = 2"
+    run_statement "#4.3" "$qry" 0
+
+    #
+    # Now we'll verify v4 trigger operation for insert,update, and delete
+    #
+
+    # Insert a new lease subnet 77
+    qry="insert into lease4 (address, subnet_id, state) values (777,77,0);"
+    run_statement "#4.4" "$qry"
+
+    # Assigned count for subnet 77 should be 2
+    qry="select leases from lease4_stat where subnet_id = 77 and state = 0"
+    run_statement "#4.5" "$qry" 2
+
+    # Update the state of the new lease to declined
+    qry="update lease4 set state = 1 where address = 777;"
+    run_statement "#4.6" "$qry"
+
+    # Assigned count for subnet 77 should be 1 again
+    qry="select leases from lease4_stat where subnet_id = 77 and state = 0"
+    run_statement "#4.7" "$qry" 1
+
+    # Declined count for subnet 77 should be 1
+    qry="select leases from lease4_stat where subnet_id = 77 and state = 1"
+    run_statement "#4.8" "$qry" 1
+
+    # Delete the lease.
+    qry="delete from lease4 where address = 777;"
+    run_statement "#4.9" "$qry"
+
+    # Declined count for subnet 77 should be 0
+    qry="select leases from lease4_stat where subnet_id = 77 and state = 1"
+    run_statement "#4.10" "$qry" 0
+
+    #
+    # Next we'll verify lease6_stats are correct after migration.
+    #
+
+    # Assigned leases for subnet 40 should be 1
+    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0"
+    run_statement "#6.1" "$qry" 1
+
+    # Assigned (PD) leases for subnet 40 should be 1
+    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 1 and state = 0"
+    run_statement "#6.2" "$qry" 1
+
+    # Declined leases for subnet 40 should be 1
+    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 1"
+    run_statement "#6.3" "$qry" 1
+
+    # Assigned (PD) leases for subnet 50 should be 2
+    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
+    run_statement "#6.4" "$qry" 2
+
+    # Should be no records for EXPIRED
+    qry="select count(subnet_id) from lease4_stat where state = 2"
+    run_statement "#6.5" "$qry" 0
+
+    #
+    # Finally we'll verify v6 trigger operation for insert,update, and delete
+    #
+
+    # Insert a new lease subnet 50
+    qry="insert into lease6 (address, subnet_id, lease_type, state) values ('777',50,1,0)"
+    run_statement "#6.5" "$qry"
+
+    # Assigned count for subnet 50 should be 3
+    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
+    run_statement "#6.6" "$qry" 3
+
+    # Update the state of the new lease to expired
+    qry="update lease6 set state = 2 where address = '777';"
+    run_statement "#6.7" "$qry"
+
+    # Assigned count for subnet 50 should be 2 again
+    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
+    run_statement "#6.8" "$qry" 2
+
+    # Delete another PD lease.
+    qry="delete from lease6 where address = '555'"
+    run_statement "#6.9" "$qry"
+
+    # Assigned leases for subnet 50 should be 1
+    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
+    run_statement "#6.10" "$qry" 1
+
+    # Let's wipe the whole database
+    pgsql_wipe
+}
+
+pgsql_lease_stat_recount_test() {
+    test_start "pgsql_lease_stat_recount_test"
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    # Ok, now let's initialize the database
+    run_command \
+        "${kea_admin}" db-init pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+
+    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init pgsql failed, expected %d, returned non-zero status code %d"
+
+    # Now we need insert some leases to "recount"
+    qry=\
+"insert into lease4 (address, subnet_id, state) values (111,10,0);\
+ insert into lease4 (address, subnet_id, state) values (222,10,0);\
+ insert into lease4 (address, subnet_id, state) values (333,10,1);\
+ insert into lease4 (address, subnet_id, state) values (444,10,2);\
+ insert into lease4 (address, subnet_id, state) values (555,77,0);"
+    run_statement "insert v4 leases" "$qry"
+
+    qry=\
+"insert into lease6 (address, lease_type, subnet_id, state) values (111,0,40,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (222,0,40,1);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (333,1,40,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (444,1,50,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (555,1,50,0);\
+ insert into lease6 (address, lease_type, subnet_id, state) values (666,1,40,2);"
+    run_statement "insert v6 leases" "$qry"
+
+    # Now we change some counters.
+    qry=\
+"insert into lease4_stat (subnet_id, state, leases) values (20,0,1);\
+ update lease4_stat set leases = 5 where subnet_id = 10 and state = 0;
+ delete from lease4_stat where subnet_id = 10 and state = 2;"
+    run_statement "change v4 stats" "$qry"
+
+    qry=\
+"insert into lease6_stat (subnet_id, lease_type, state, leases) values (20,1,0,1);
+ update lease6_stat set leases = 5 where subnet_id = 40 and lease_type = 0 and state = 0;
+ delete from lease6_stat where subnet_id = 40 and lease_type = 1 and state = 2;"
+    run_statement "change v6 stats" "$qry"
+
+    # Recount all statistics from scratch.
+    run_command \
+        "${kea_admin}" stats-recount pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}"
+
+    assert_eq 0 "${EXIT_CODE}" "kea-admin stats-recount pgsql failed, expected %d, returned non-zero status code %d"
+
+    #
+    # First we'll verify lease4_stats are correct after recount.
+    #
+
+    # Assigned leases for subnet 10 should be 2
+    qry="select leases from lease4_stat where subnet_id = 10 and state = 0"
+    run_statement "#4.1" "$qry" 2
+
+    # Assigned leases for subnet 77 should be 1
+    qry="select leases from lease4_stat where subnet_id = 77 and state = 0"
+    run_statement "#4.2" "$qry" 1
+
+    # Should be no records for EXPIRED
+    qry="select count(subnet_id) from lease4_stat where state = 2"
+    run_statement "#4.3" "$qry" 0
+
+    #
+    # Next we'll verify lease6_stats are correct after recount.
+    #
+
+    # Assigned leases for subnet 40 should be 1
+    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0"
+    run_statement "#6.1" "$qry" 1
+
+    # Assigned (PD) leases for subnet 40 should be 1
+    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 1 and state = 0"
+    run_statement "#6.2" "$qry" 1
+
+    # Declined leases for subnet 40 should be 1
+    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 1"
+    run_statement "#6.3" "$qry" 1
+
+    # Assigned (PD) leases for subnet 50 should be 2
+    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
+    run_statement "#6.4" "$qry" 2
+
+    # Should be no records for EXPIRED
+    qry="select count(subnet_id) from lease4_stat where state = 2"
+    run_statement "#6.5" "$qry" 0
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# Verifies that you can upgrade from earlier version and
+# that unused subnet ID values in hosts and options tables are
+# converted to NULL.
+pgsql_unused_subnet_id_test() {
+    test_start "pgsql.unused_subnet_id_test"
+
+    # 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 4.0
+    pgsql_upgrade_schema_to_version 4.0
+
+    # Now we need insert some hosts to "migrate" for both v4 and v6
+    qry=\
+"insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
+   values (0, '0123456', 0, 0, 'both'); \
+ insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
+   values (0, '1123456', 4, 0, 'v4only');
+ insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
+   values (0, '2123456', 0, 6, 'v6only');\
+ insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname) \
+   values (0, '3123456', 4, 6, 'neither');"
+
+    run_statement "insert hosts" "$qry"
+
+    # Now we need insert some options to "migrate" for both v4 and v6
+    qry=\
+"insert into dhcp4_options (code, dhcp4_subnet_id, scope_id) values (1, 4, 0);\
+ insert into dhcp4_options (code, dhcp4_subnet_id, scope_id) values (2, 0, 0);\
+ insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (1, 6, 0);\
+ insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (2, 0, 0);"
+
+    run_statement "insert options" "$qry"
+
+    # Ok, we have a 4.0 database with hosts and options. Let's upgrade it to 5.0
+    run_command \
+        "${kea_admin}" db-upgrade pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
+
+    # Upgrade should succeed
+    assert_eq 0 "${EXIT_CODE}" "upgrade failed"
+
+    # Two hosts should have null v4 subnet ids
+    qry="select count(host_id) from hosts where dhcp4_subnet_id is null;"
+    run_statement "#hosts.1" "$qry" 2
+
+    # Two hosts should have v4 subnet ids = 4
+    qry="select count(host_id) from hosts where dhcp4_subnet_id = 4;"
+    run_statement "#hosts.2" "$qry" 2
+
+    # Two hosts should have null v6 subnet ids
+    qry="select count(host_id) from hosts where dhcp6_subnet_id is null;"
+    run_statement "#hosts.3" "$qry" 2
+
+    # Two hosts should should have v6 subnet ids = 6
+    qry="select count(host_id) from hosts where dhcp6_subnet_id = 6;"
+    run_statement "#hosts.4" "$qry" 2
+
+    # One option should have null v4 subnet id
+    qry="select count(option_id) from dhcp4_options where dhcp4_subnet_id is null;"
+    run_statement "#options.1" "$qry" 1
+
+    # One option should have v4 subnet id = 4
+    qry="select count(option_id) from dhcp4_options where dhcp4_subnet_id = 4;"
+    run_statement "#options.2" "$qry" 1
+
+    # One option should have null v6 subnet id
+    qry="select count(option_id) from dhcp6_options where dhcp6_subnet_id is null;"
+    run_statement "#options.3" "$qry" 1
+
+    # One option should have v4 subnet id = 6
+    qry="select count(option_id) from dhcp6_options where dhcp6_subnet_id = 6;"
+    run_statement "#options.4" "$qry" 1
+
+    # Let's wipe the whole database
+    pgsql_wipe
+
+    test_finish 0
+}
+
+# Run tests.
+pgsql_db_init_test
+pgsql_db_version_test
+pgsql_upgrade_test
+pgsql_lease4_dump_test
+pgsql_lease4_dump_test -y
+pgsql_lease6_dump_test
+pgsql_lease6_dump_test -y
+pgsql_lease4_upload_test
+pgsql_lease4_upload_test -y
+pgsql_lease6_upload_test
+pgsql_lease6_upload_test -y
+pgsql_lease4_stat_test
+pgsql_lease6_stat_test
+pgsql_lease_stat_upgrade_test
+pgsql_lease_stat_recount_test
+pgsql_unused_subnet_id_test