admin/tests/cql_tests.sh.in

#!/bin/sh

# Copyright (C) 2014-2021 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"

cql_wipe() {
    # Wipe the database.
    run_command \
        cql_execute_script $db_scripts_dir/cql/dhcpdb_drop.cql
    assert_eq 0 "${EXIT_CODE}" "drop table query failed, expected %d, returned %d"
}

cql_db_init_test() {
    test_start "cql.init"

    # Wipe the database.
    cql_wipe

    # Create the database
    run_command \
        "${kea_admin}" db-init cql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init cql failed, expected exit code: %d, actual: %d"

    # Verify that all the expected tables exist

    # Check schema_version table
    run_command \
      cql_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 \
      cql_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 \
      cql_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 \
      cql_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 \
      cql_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
    echo "Making sure keyspace creation fails the second time..."
    run_command \
      "${kea_admin}" db-init cql -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"

    # Wipe the database.
    cql_wipe

    test_finish 0
}

cql_db_version_test() {
    test_start "cql.version"

    # Wipe the database.
    cql_wipe

    # Create the database.
    run_command \
      "${kea_admin}" db-init cql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init cql failed, expected exit code: %d, actual: %d"

    # Verify that kea-admin db-version returns the correct version.
    version=$("${kea_admin}" db-version cql -u "${db_user}" -p "${db_password}" -n "${db_name}")
    assert_str_eq "5.0" "${version}" "Expected kea-admin to return %s, returned value was %s"

    # Wipe the database.
    cql_wipe

    test_finish 0
}

cql_upgrade_test() {
    test_start "cql.upgrade"

    # Wipe the database.
    cql_wipe

    # Initialize database to schema 1.0.
    run_command \
      cql_execute_script "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.cql"
    assert_eq 0 "${EXIT_CODE}" "cannot initialize the database, expected exit code: %d, actual: %d"

    run_command \
      "${kea_admin}" db-upgrade cql -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"

    # Wipe the database.
    cql_wipe

    test_finish 0
}

cql_lease4_dump_test() {
    test_start "cql.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/cql.lease4_dump_test.output.csv"
    sorted_file="$output_dir/data/cql.lease4_dump_test.output.sorted.csv"
    ref_file="$test_dir/data/cql.lease4_dump_test.reference.csv"

    # Wipe out any residuals from prior failed runs.
    if [ -e $output_file ]
    then
        rm $output_file
    fi

    if [ -e $sorted_file ]
    then
        rm $sorted_file
    fi

    # Wipe the database.
    cql_wipe

    # Create the database
    run_command \
        "${kea_admin}" db-init cql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"
    assert_eq 0 "${EXIT_CODE}" "kea-admin db-init cql failed, expected exit code: %d, actual: %d"

    # Insert the reference record.
    # -1073741302 corresponds to 192.0.2.10
    # -1073741301 corresponds to 192.0.2.11
    # -1073741300 corresponds to 192.0.2.12
    # 1430694930 corresponds to 2015-04-04 01:15:30
    # 1433464245 corresponds to 2015-05-05 02:30:45
    # 1436173267 corresponds to 2015-06-06 11:01:07
    insert_cql="\
    INSERT INTO lease4 (address, hwaddr, client_id, valid_lifetime, expire, subnet_id, \
        fqdn_fwd, fqdn_rev, hostname, state, user_context) \
        VALUES (-1073741302,textAsBlob('20'),textAsBlob('30'),40,1430694930,50,true,true,'one.example.com', 0, '');\
    INSERT INTO lease4 (address, hwaddr, client_id, valid_lifetime, expire, subnet_id, \
        fqdn_fwd, fqdn_rev, hostname, state, user_context) \
        VALUES (-1073741301,NULL,textAsBlob('123'),40,1433464245,50,true,true,'', 1, '');\
    INSERT INTO lease4 (address, hwaddr, client_id, valid_lifetime, expire, subnet_id, \
        fqdn_fwd, fqdn_rev, hostname, state, user_context) \
        VALUES (-1073741300,textAsBlob('22'),NULL,40,1436173267,50,true,true,'three.example.com', 2, '');"

    run_command \
        cql_execute "$insert_cql"
    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 cql -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"

    # sort data so we can compare
    sort -g "${output_file}" > "${sorted_file}"

    # Compare the dump output to reference file, they should be identical.
    run_command \
        cmp -s $sorted_file $ref_file
    assert_eq 0 "${EXIT_CODE}" "dump file does not match reference file, expected exit code %d, actual %d"

    # remove the output file.
    rm $output_file

    # remove the sorted file.
    rm $sorted_file

    # Wipe the database.
    cql_wipe

    test_finish 0
}

cql_lease6_dump_test() {
    test_start "cql.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/cql.lease6_dump_test.output.csv"
    sorted_file="$output_dir/data/cql.lease6_dump_test.output.sorted.csv"
    ref_file="$test_dir/data/cql.lease6_dump_test.reference.csv"

    # Wipe out any residuals from prior failed runs.
    if [ -e $output_file ]
    then
        rm $output_file
    fi

    if [ -e $sorted_file ]
    then
        rm $sorted_file
    fi

    # Wipe the database.
    cql_wipe

    # Create the database.
    run_command \
        "${kea_admin}" db-init cql -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 record.
    # 1430694930 corresponds to 2015-04-04 01:15:30
    # 1433464245 corresponds to 2015-05-05 02:30:45
    # 1436173267 corresponds to 2015-06-06 11:01:07
    insert_cql="\
    INSERT INTO lease6 (address, duid, valid_lifetime, expire, subnet_id, \
        pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, \
        hwaddr, hwtype, hwaddr_source, state, user_context) \
        VALUES ('2001:db8::10',textAsBlob('20'),30,1430694930,40,50,1,60,70,true,true, \
            'one.example.com',textAsBlob('80'),90,16,0,'');\
    INSERT INTO lease6 (address, duid, valid_lifetime, expire, subnet_id, \
        pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, \
        hwaddr, hwtype, hwaddr_source, state, user_context) \
        VALUES ('2001:db8::11',NULL,30,1433464245,40,50,1,60,70,true,true, \
            '',textAsBlob('80'),90,1,1,'');\
    INSERT INTO lease6 (address, duid, valid_lifetime, expire, subnet_id, \
        pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, \
        hwaddr, hwtype, hwaddr_source, state, user_context) \
        VALUES ('2001:db8::12',textAsBlob('21'),30,1436173267,40,50,1,60,70,true,true, \
            'three.example.com',textAsBlob('80'),90,4,2,'');"

    run_command \
        cql_execute "$insert_cql"
    assert_eq 0 "${EXIT_CODE}" "insert into lease6 failed, expected exit code %d, actual %d"

    # Dump lease4 to output_file.
    run_command \
        "${kea_admin}" lease-dump cql -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, status code %d"

    # sort data so we can compare
    sort -g "${output_file}" > "${sorted_file}"

    # Compare the dump output to reference file, they should be identical.
    run_command \
        cmp -s $sorted_file $ref_file
    assert_eq 0 "${EXIT_CODE}" "dump file does not match reference file, expected exit code %d, actual %d"

    # remove the output file.
    rm $output_file

    # remove the sorted file.
    rm $sorted_file

    # Wipe the database.
    cql_wipe

    test_finish 0
}

# Upgrades an existing schema to a target newer version
# param target_version - desired schema version as "major.minor"
cql_upgrade_schema_to_version() {
    target_version=$1

    # Check if the scripts directory exists at all.
    if [ ! -d ${db_scripts_dir}/cql ]; then
        log_error "Invalid scripts directory: ${db_scripts_dir}/cql"
        exit 1
    fi

    # Check if there are any files in it
    num_files=$(find ${db_scripts_dir}/cql/upgrade*.sh -type f | wc -l)
    if [ "${num_files}" -eq 0 ]; then
        log_error "No scripts in ${db_scripts_dir}/cql?"
        exit 1
    fi

    version=$(cql_version)
    for script in "${db_scripts_dir}"/cql/upgrade*.sh
    do
        if [ "${version}" = "${target_version}" ]
        then
            break
        fi

        echo "Processing $script file..."
        "${script}" -u "${db_user}" -p "${db_password}" -k "${db_name}"
        version=$(cql_version)
    done

    echo "Schema upgraded to $version"
}

# Verifies that you can upgrade from an earlier version and
# that unused subnet ID values in hosts and options tables are
# converted to -1
cql_unused_subnet_id_test() {
    test_start "cql.unused_subnet_id_test"

    # Let's wipe the whole database
    cql_wipe

    # We need to create an older database with lease data so we can
    # verify the upgrade mechanisms which convert subnet id values
    #
    # Initialize database to schema 1.0.
    cql_execute_script "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.cql"

    # Now upgrade to schema 2.0, the version just before global HRs
    cql_upgrade_schema_to_version 2.0

    # Now we need insert some hosts to "migrate" for both v4 and v6
    qry="\
    INSERT INTO host_reservations (id, host_identifier_type, host_identifier, host_ipv4_subnet_id, host_ipv6_subnet_id, hostname, option_subnet_id) \
        VALUES (1, 0, textAsBlob('0123456'), 0, 0, 'host0', 0);\
    INSERT INTO host_reservations (id, host_identifier_type, host_identifier, host_ipv4_subnet_id, host_ipv6_subnet_id, hostname, option_subnet_id) \
        VALUES (2, 0, textAsBlob('1123456'), 4, 0, 'Host1', 4);\
    INSERT INTO host_reservations (id, host_identifier_type, host_identifier, host_ipv4_subnet_id, host_ipv6_subnet_id, hostname, option_subnet_id) \
        VALUES (3, 0, textAsBlob('2123456'), 0, 6, 'host2', 6);\
    INSERT INTO host_reservations (id, host_identifier_type, host_identifier, host_ipv4_subnet_id, host_ipv6_subnet_id, hostname, option_subnet_id) \
        VALUES (4, 0, textAsBlob('3123456'), 4, 6, 'Host3', 0);\
    INSERT INTO host_reservations (id, host_identifier_type, host_identifier, host_ipv4_subnet_id, host_ipv6_subnet_id, hostname, option_subnet_id) \
        VALUES (5, 0, textAsBlob('3123456'), -1, 6, 'host3', 6);"

    run_command \
        cql_execute "$qry"
    assert_eq 0 "${EXIT_CODE}" "insert hosts failed, expected exit code: %d, actual: %d"

    # Ok, we have a 2.0 database with hosts and options. Let's upgrade it.
    run_command \
      "${kea_admin}" db-upgrade cql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"

    # Upgrade should succeed
    assert_eq 0 "${EXIT_CODE}" "upgrade failed"

    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
    ref_file="$test_dir/data/cql.subnet_id_test.reference.csv"

    output_dir="@abs_top_builddir@/src/bin/admin/tests"
    export_file="$output_dir/data/cql.subnet_id_test.csv"
    sorted_file="$output_dir/data/cql.subnet_id_test.sorted.csv"

    # Fetch host_reservation data for comparison
    echo "Exporting host_reservation data to $export_file ..."

    qry="\
    SELECT id, host_ipv4_subnet_id, host_ipv6_subnet_id, hostname, lower_case_hostname, option_subnet_id\
        FROM hosts WHERE id IN (1,2,3,4,5) ALLOW FILTERING;"

    run_command \
        cql_execute "$qry"
    printf '%s\n' "${OUTPUT}" > "${export_file}"
    assert_eq 0 "${EXIT_CODE}" "insert hosts failed, expected exit code: %d, actual: %d"

    # sort data so we can compare
    grep -F '|' "${export_file}" | sort -V | tr -d " " | sed 's/|/,/g' > $sorted_file

    # Compare the dump output to reference file, they should be identical.
    run_command \
        cmp -s $sorted_file $ref_file
    assert_eq 0 "${EXIT_CODE}" "export file does not match reference file, expected exit code %d, actual %d"

    # remove the output file.
    rm $export_file

    # remove the sorted file.
    rm $sorted_file

    # Wipe the database.
    cql_wipe

    # Report test success.
    test_finish 0
}

# Verifies that you can upgrade from an earlier version and
# that all hosts and options from old host_reservation table (version 3.0) are
# converted to new schema (version 4.0) with new key partition key and are
# moved to new hosts table
cql_upgrade_hosts_test() {
    test_start "cql.update_hosts_test"

    # Let's wipe the whole database
    cql_wipe

    # We need to create an older database with lease data so we can
    # verify the upgrade mechanisms which convert subnet id values
    #
    # Initialize database to schema 1.0.
    cql_execute_script "@abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.cql"

    # Now upgrade to schema 3.0, the version just before global HRs
    cql_upgrade_schema_to_version 3.0

    # Now we need insert some hosts to "migrate" for both v4 and v6
    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
    data_file="$test_dir/data/cql.hosts_data_test.csv"
    ref_file="$test_dir/data/cql.hosts_data_test.reference.csv"

    output_dir="@abs_top_builddir@/src/bin/admin/tests"
    export_file="$output_dir/data/cql.hosts_test.csv"
    sorted_file="$output_dir/data/cql.hosts_test.sorted.csv"

    # Fetch host_reservation data for comparison
    echo "Exporting host_reservation data to $export_file ..."

    qry="\
    COPY host_reservations \
        (id, host_identifier, host_identifier_type, host_ipv4_subnet_id, \
         host_ipv6_subnet_id, host_ipv4_address, host_ipv4_next_server, \
         host_ipv4_server_hostname, host_ipv4_boot_file_name, hostname, \
         auth_key, user_context, host_ipv4_client_classes, \
         host_ipv6_client_classes, reserved_ipv6_prefix_address, \
         reserved_ipv6_prefix_length, reserved_ipv6_prefix_address_type, \
         iaid, option_universe, option_code, option_value, \
         option_formatted_value, option_space, option_is_persistent, \
         option_client_class, option_subnet_id, option_user_context, \
         option_scope_id) \
    FROM '$data_file'"

    run_command \
        cql_execute "$qry"
    assert_eq 0 "${EXIT_CODE}" "insert hosts failed, expected exit code: %d, actual: %d"

    # Ok, we have a 3.0 database with hosts and options. Let's upgrade it.
    run_command \
      "${kea_admin}" db-upgrade cql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}"

    # Upgrade should succeed
    assert_eq 0 "${EXIT_CODE}" "upgrade failed"

    qry="\
    COPY hosts \
        (id, key, host_identifier, host_identifier_type, host_ipv4_subnet_id, \
         host_ipv6_subnet_id, host_ipv4_address, host_ipv4_next_server, \
         host_ipv4_server_hostname, host_ipv4_boot_file_name, hostname, \
         lower_case_hostname, auth_key, user_context, \
         host_ipv4_client_classes, \
         host_ipv6_client_classes, reserved_ipv6_prefix_address, \
         reserved_ipv6_prefix_length, reserved_ipv6_prefix_address_type, \
         iaid, option_universe, option_code, option_value, \
         option_formatted_value, option_space, option_is_persistent, \
         option_client_class, option_subnet_id, option_user_context, \
         option_scope_id) \
    TO '$export_file'"

    run_command \
        cql_execute "$qry"
    assert_eq 0 "${EXIT_CODE}" "insert hosts failed, expected exit code: %d, actual: %d"

    # sort data so we can compare
    sort -V "${export_file}" > "${sorted_file}"

    # Compare the dump output to reference file, they should be identical.
    run_command \
        cmp -s $sorted_file $ref_file
    assert_eq 0 "${EXIT_CODE}" "export file does not match reference file, expected exit code %d, actual %d"

    # remove the output file.
    rm $export_file

    # remove the sorted file.
    rm $sorted_file

    # Wipe the database.
    cql_wipe

    # Report test success.
    test_finish 0
}


# Run tests.
cql_db_init_test
cql_db_version_test
cql_upgrade_test
cql_lease4_dump_test
cql_lease6_dump_test
cql_unused_subnet_id_test
cql_upgrade_hosts_test