kea/src/bin/admin/tests/pgsql_tests.sh.in

#!/bin/sh

# Copyright (C) 2015-2017 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/.

# 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

db_user="keatest"
db_password="keatest"
db_name="keatest"
db_host="localhost"

# Set location of the kea-admin.
keaadmin=@abs_top_builddir@/src/bin/admin/kea-admin

# Wipe all tables from the DB:
pgsql_wipe() {
    printf "Wiping whole database %s\n" $db_name
    export PGPASSWORD=$db_password

    cat $db_scripts_dir/pgsql/dhcpdb_drop.pgsql | psql --set ON_ERROR_STOP=1 -A -t -h localhost -q -U keatest -d keatest >/dev/null 2>&1
    assert_eq 0 $?  "pgsql_wipe drop failed, expected exit code: %d, actual: %d"
}

pgsql_lease_init_test() {
    test_start "pgsql.lease-init"

    # Let's wipe the whole database
    pgsql_wipe

    # Create the database
    ${keaadmin} lease-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    assert_eq 0 $? "kea-admin lease-init pgsql failed, expected exit code: %d, actual: %d"

    # Verify that all the expected tables exist

    # Check schema_version table
    RESULT=`pgsql_execute "SELECT version, minor FROM schema_version;"`
    assert_eq 0 $? "schema_version table check failed, expected exit code: %d, actual: %d"

    # Check lease4 table
    RESULT=`pgsql_execute "SELECT address, hwaddr, client_id, valid_lifetime, expire, subnet_id, fqdn_fwd, fqdn_rev, hostname, state FROM lease4;"`
    assert_eq 0 $? "lease4 table check failed, expected exit code: %d, actual: %d"

    # Check lease6 table
    RESULT=`pgsql_execute "SELECT address, duid, valid_lifetime, expire, subnet_id, pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, state FROM lease6;"`
    assert_eq 0 $? "lease6 table check failed, expected exit code: %d, actual: %d"

    # Check lease6_types table
    RESULT=`pgsql_execute "SELECT lease_type, name FROM lease6_types;"`
    assert_eq 0 $? "lease6_types table check failed, expected exit code: %d, actual: %d"

    # Check lease_state table
    RESULT=`pgsql_execute "SELECT state, name FROM lease_state;"`
    assert_eq 0 $? "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 ""
    echo "DB created successfully, make sure we aren't allowed to try it again:"
    ${keaadmin} lease-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    assert_eq 2 $? "kea-admin failed to deny lease-init, expected exit code: %d, actual: %d"

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

pgsql_lease_version_test() {
    test_start "pgsql.lease-version"

    # Wipe the whole database
    pgsql_wipe

    # Create the database
    ${keaadmin} lease-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    assert_eq 0 $? "cannot initialize the database, expected exit code: %d, actual: %d"

    # Verify that kea-admin lease-version returns the correct version
    version=$(${keaadmin} lease-version pgsql -u $db_user -p $db_password -n $db_name)
    assert_str_eq "3.1" ${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
    output=`pgsql_execute "select state from lease4;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "lease4 is missing state column. (expected status code %d, returned %d)"

    # Added state column to lease6
    output=`pgsql_execute "select state from lease6;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "lease6 is missing state column. (expected status code %d, returned %d)"

    # Added stored procedures for lease dumps
    output=`pgsql_execute "select lease4DumpHeader from lease4DumpHeader();"`
    assert_eq 0 $ERRCODE "function lease4DumpHeader() broken or missing. (expected status code %d, returned %d)"

    output=`pgsql_execute "select address from lease4DumpData();"`
    assert_eq 0 $ERRCODE "function lease4DumpData() broken or missing. (expected status code %d, returned %d)"

    output=`pgsql_execute "select lease6DumpHeader from lease6DumpHeader();"`
    assert_eq 0 $ERRCODE "function lease6DumpHeader() broken or missing. (expected status code %d, returned %d)"

    output=`pgsql_execute "select address from lease6DumpData();"`
    assert_eq 0 $ERRCODE "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
    output=`pgsql_execute "select hwaddr, hwtype, hwaddr_source from lease6;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "lease6 table not upgraded to 3.0 (expected status code %d, returned %d)"

    # Added lease_hwaddr_source table
    output=`pgsql_execute "select hwaddr_source, name from lease_hwaddr_source;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "lease_hwaddr_source table is missing or broken. (expected status code %d, returned %d)"

    # Added hosts table
    output=`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 from hosts;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "hosts table is missing or broken. (expected status code %d, returned %d)"

    # Added ipv6_reservations table
    output=`pgsql_execute "select reservation_id, address, prefix_len, type, dhcp6_iaid, host_id from ipv6_reservations;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "ipv6_reservations table is missing or broken. (expected status code %d, returned %d)"

    # Added dhcp4_options table
    output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id, scope_id from dhcp4_options;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dhcp4_options table is missing or broken. (expected status code %d, returned %d)"

    # Added dhcp6_options table
    output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"

    # Added  host_identifier_type table
    output=`pgsql_execute "select type, name from host_identifier_type;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "host_identifier_type table is missing or broken. (expected status code %d, returned %d)"

    # Added dhcp_option_scope table
    output=`pgsql_execute "select scope_id, scope_name from dhcp_option_scope;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dhcp_option_scope table is missing or broken. (expected status code %d, returned %d)"

    # Added dhcp6_options table
    output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"

    # Added order by clause to lease4DumpData
    output=`pgsql_execute "select address from lease4DumpData();"`
    assert_eq 0 $ERRCODE "function lease4DumpData() broken or missing. (expected status code %d, returned %d)"
    output=`pgsql_execute "\sf lease4DumpData"`
    assert_eq 0 $ERRCODE "\sf of lease4DumpData failed. (expected status code %d, returned %d)"
    count=`echo $output | grep -ic "order by l\.address"`
    assert_eq 1 $count "lease4DumpData is missing order by clause"

    # Added hwaddr columns to lease6DumpHeader
    output=`pgsql_execute "select lease6DumpHeader from lease6DumpHeader();"`
    assert_eq 0 $ERRCODE "function lease6DumpHeader() broken or missing. (expected status code %d, returned %d)"
    count=`echo $output | grep -ic "hwaddr,hwtype,hwaddr_source"`
    assert_eq 1 $count "lease6DumpHeader is missing hwaddr columns"

    # Added hwaddr columns to lease6DumpData
    output=`pgsql_execute "select hwaddr,hwtype,hwaddr_source from lease6DumpData();"`
    assert_eq 0 $ERRCODE "function lease6DumpData() broken or missing. (expected status code %d, returned %d)"

    # Added order by clause to lease6DumpData
    output=`pgsql_execute "\sf lease4DumpData"`
    assert_eq 0 $ERRCODE "\sf of lease4DumpData failed. (expected status code %d, returned %d)"
    count=`echo $output | grep -ic "order by l\.address"`
    assert_eq 1 $count "lease4DumpData is missing order by clause"

    # lease_hardware_source should have row for source = 0
    output=`pgsql_execute "select count(hwaddr_source) from lease_hwaddr_source where hwaddr_source = 0 and name='HWADDR_SOURCE_UNKNOWN';"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "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_3_1() {
    # Verify upgraded schema reports version 3.1.
    version=$(${keaadmin} lease-version pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir)
    assert_str_eq "3.1" ${version} "Expected kea-admin to return %s, returned value was %s"
}

pgsql_upgrade_test() {
    test_start "pgsql.upgrade-test"

    # Wipe the whole database
    pgsql_wipe

    # Initialize database to schema 1.0.
    pgsql_execute_script @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql
    assert_eq 0 $? "cannot initialize the database, expected exit code: %d, actual: %d"

    ${keaadmin} lease-upgrade pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    assert_eq 0 $? "lease-upgrade failed, expected exit code: %d, actual: %d"

    # 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 3.1 upgrade
    pgsql_upgrade_3_0_to_3_1

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

# Given a valid timestamp string, returns a timestamp with timezone string
# for the give time localized by the PostgreSQL server.
get_local_time() {
    timestamp="$1"

    # Expiration field is a "timestamp with timezone" so we need a reference
    # time for the machine/DB this test is running upon.
    ref_timestamp=`pgsql_execute "select timestamptz '$1';"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "reference time query failed for [$timestamp], expected exit code %d, actual %d"
    echo $ref_timestamp
}


# 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.
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"
    script_dir="@abs_top_srcdir@/src/bin/admin/scripts"

    output_file="$output_dir/data/pgsql.lease4_dump_test.output.csv"

    ref_file="$test_dir/data/pgsql.lease4_dump_test.reference.csv"
    ref_file_tmp="$output_dir/data/pgsql.lease4_dump_test.reference.csv.tmp"

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

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

    # Let's wipe the whole database
    pgsql_wipe

    # Ok, now let's initialize the database
    ${keaadmin} lease-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    ERRCODE=$?
    assert_eq 0 $ERRCODE "could not create database, expected exit code %d, actual %d"

    timestamp1="2015-01-01 01:15:30"
    timestamp2="2015-02-02 02:30:45"
    timestamp3="2015-03-03 11:01:07"

    # Insert the reference records
    insert_sql="\
insert into lease4 values(10,E'\\x20',E'\\x30',40,'$timestamp1',50,'t','t','one.example.com', 0);\
insert into lease4 values(11,'',E'\\x0123',40,'$timestamp2',50,'t','t','', 1);\
insert into lease4 values(12,E'\\x22','',40,'$timestamp3',50,'t','t','three.example.com', 2);"

    pgsql_execute "$insert_sql"
    ERRCODE=$?
    assert_eq 0 $ERRCODE "insert into lease4 failed, expected exit code %d, actual %d"

    # Dump lease4 to output_file
    ${keaadmin} lease-dump pgsql -4 -u $db_user -p $db_password -n $db_name -d $db_scripts_dir -o $output_file
    ERRCODE=$?
    assert_eq 0 $ERRCODE "kea-admin lease-dump -4 failed,  status code %d"

    # Expiration field is a "timestamp with timezone" so we need a localized reference
    # times for the machine/DB this test is running upon.
    local_timestamp1=`get_local_time "$timestamp1"`
    local_timestamp2=`get_local_time "$timestamp2"`
    local_timestamp3=`get_local_time "$timestamp3"`

    # Create the comparison file by replacing the <timestamp> tags
    # with the local reference timestamp
    sedstr="\
sed 's/<timestamp1>/$local_timestamp1/g' $ref_file | \
sed 's/<timestamp2>/$local_timestamp2/g' | \
sed 's/<timestamp3>/$local_timestamp3/g' "

    eval $sedstr >$ref_file_tmp
    ERRCODE=$?
    assert_eq 0 $ERRCODE "timestamp replacement failed, expected exit code %d, actual %d"

    # Compare the dump output to reference file, they should be identical
    cmp -s $output_file  $ref_file_tmp
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dump file does not match reference file, expected exit code %d, actual %d"

    # Remove the output file and temporary reference file
    rm $output_file
    rm $ref_file_tmp

    # 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"
    script_dir="@abs_top_srcdir@/src/bin/admin/scripts"

    output_file="$output_dir/data/pgsql.lease6_dump_test.output.csv"

    ref_file="$test_dir/data/pgsql.lease6_dump_test.reference.csv"
    ref_file_tmp="$output_dir/data/pgsql.lease6_dump_test.reference.csv.tmp"

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

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

    # Let's wipe the whole database
    pgsql_wipe

    # Ok, now let's initialize the database
    ${keaadmin} lease-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    ERRCODE=$?
    assert_eq 0 $ERRCODE "could not create database,  status code %d"

    timestamp1="2015-04-04 01:15:30"
    timestamp2="2015-02-02 02:30:45"
    timestamp3="2015-06-06 11:01:07"

    # Insert the reference records
    insert_sql="\
insert into lease6 values(10,E'\\x20',30,'$timestamp1',40,50,1,60,70,'t','t','one.example.com', 0,decode('80','hex'),90,16);\
insert into lease6 values(11,'',30,'$timestamp2',40,50,1,60,70,'t','t','', 1,decode('80','hex'),90,1);\
insert into lease6 values(12,E'\\x21',30,'$timestamp3',40,50,1,60,70,'t','t','three.example.com', 2,decode('80','hex'),90,4);"

    pgsql_execute "$insert_sql"
    ERRCODE=$?
    assert_eq 0 $ERRCODE "insert into lease6 failed, status code %d"

    # Dump lease6 to output_file
    ${keaadmin} lease-dump pgsql -6 -u $db_user -p $db_password -n $db_name -d $db_scripts_dir -o $output_file
    ERRCODE=$?
    assert_eq 0 $ERRCODE "kea-admin lease-dump -6 failed, status code %d"

    # Expiration field is a "timestamp with timezone" so we need a localized reference
    # times for the machine/DB this test is running upon.
    local_timestamp1=`get_local_time "$timestamp1"`
    local_timestamp2=`get_local_time "$timestamp2"`
    local_timestamp3=`get_local_time "$timestamp3"`

    # Create the comparison file by replacing the <timestamp> tags
    # with the local reference timestamp
    sedstr="\
sed 's/<timestamp1>/$local_timestamp1/g' $ref_file | \
sed 's/<timestamp2>/$local_timestamp2/g' | \
sed 's/<timestamp3>/$local_timestamp3/g' "

    eval $sedstr >$ref_file_tmp
    ERRCODE=$?
    assert_eq 0 $ERRCODE "timestamp replacement failed, expected exit code %d, actual %d"

    # Compare the dump output to reference file, they should be identical
    cmp -s $output_file  $ref_file_tmp
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dump file does not match reference file"

    # Remove the output file and temporary reference file
    rm $output_file
    rm $ref_file_tmp

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

pgsql_lease_init_test
pgsql_lease_version_test
pgsql_upgrade_test
pgsql_lease4_dump_test
pgsql_lease6_dump_test