From 4b7ae0caa575461fc24455b851759d1b68fb0c62 Mon Sep 17 00:00:00 2001 From: Razvan Becheriu Date: Thu, 25 May 2023 19:09:26 +0300 Subject: [PATCH] [#145] add schema upgrade unittests --- src/bin/admin/admin-utils.sh.in | 8 + src/bin/admin/tests/mysql_tests.sh.in | 694 ++++++++++++------ src/bin/admin/tests/pgsql_tests.sh.in | 566 +++++++++----- .../scripts/mysql/dhcpdb_create.mysql | 8 + .../scripts/mysql/upgrade_017_to_018.sh.in | 8 + .../scripts/pgsql/dhcpdb_create.pgsql | 8 + .../scripts/pgsql/upgrade_016_to_017.sh.in | 8 + 7 files changed, 897 insertions(+), 403 deletions(-) diff --git a/src/bin/admin/admin-utils.sh.in b/src/bin/admin/admin-utils.sh.in index f0e86f26d6..acf084121c 100644 --- a/src/bin/admin/admin-utils.sh.in +++ b/src/bin/admin/admin-utils.sh.in @@ -200,6 +200,10 @@ INSERT INTO lease4_stat (subnet_id, state, leases) \ SELECT subnet_id, state, COUNT(*) \ FROM lease4 WHERE state = 0 OR state = 1 \ GROUP BY subnet_id, state; \ +DELETE FROM lease4_pool_stat; \ +INSERT INTO lease4_pool_stat (subnet_id, pool_id, state, leases) \ + SELECT subnet_id, pool_id, state, count(*) FROM lease4 \ + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, state; \ COMMIT;" export _RECOUNT4_QUERY @@ -212,5 +216,9 @@ INSERT INTO lease6_stat (subnet_id, lease_type, state, leases) \ SELECT subnet_id, lease_type, state, COUNT(*) \ FROM lease6 WHERE state = 0 OR state = 1 \ GROUP BY subnet_id, lease_type, state; \ +DELETE FROM lease6_pool_stat; \ +INSERT INTO lease6_pool_stat (subnet_id, pool_id, lease_type, state, leases) \ + SELECT subnet_id, pool_id, lease_type, state, count(*) FROM lease6 \ + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, lease_type, state; \ COMMIT;" export _RECOUNT6_QUERY diff --git a/src/bin/admin/tests/mysql_tests.sh.in b/src/bin/admin/tests/mysql_tests.sh.in index 08a029f107..8a83a68298 100644 --- a/src/bin/admin/tests/mysql_tests.sh.in +++ b/src/bin/admin/tests/mysql_tests.sh.in @@ -36,10 +36,10 @@ kea_admin="@abs_top_builddir@/src/bin/admin/kea-admin" # 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);" +# 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"; +# 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 @@ -70,7 +70,6 @@ mysql_wipe() { run_command \ mysql_execute_script "${db_scripts_dir}/mysql/dhcpdb_drop.mysql" - assert_eq 0 "${EXIT_CODE}" "mysql-wipe: drop table sql failed, expected %d, returned %d" } @@ -83,38 +82,37 @@ mysql_db_init_test() { # Ok, now let's initialize the database run_command \ "${kea_admin}" db-init mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" - assert_eq 0 "${EXIT_CODE}" "kea-admin db-init mysql failed, expected %d, returned non-zero status code %d" # Ok, now let's check if the tables are indeed there. # First table: schema_version. Should have 2 columns: version and minor. run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT version, minor FROM schema_version;' + 'SELECT version, minor FROM schema_version' assert_eq 0 "${EXIT_CODE}" "schema_version table is missing or broken. (expected status code %d, returned %d)" # Second table: lease4 run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT address, hwaddr, client_id, valid_lifetime, expire, subnet_id, fqdn_fwd, fqdn_rev, hostname FROM lease4;' + 'SELECT address, hwaddr, client_id, valid_lifetime, expire, subnet_id, fqdn_fwd, fqdn_rev, hostname FROM lease4' assert_eq 0 "${EXIT_CODE}" "lease4 table is missing or broken. (expected status code %d, returned %d)" # Third table: lease6 run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT address, duid, valid_lifetime, expire, subnet_id, pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, hwaddr, hwtype, hwaddr_source FROM lease6;' + 'SELECT address, duid, valid_lifetime, expire, subnet_id, pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, hwaddr, hwtype, hwaddr_source FROM lease6' assert_eq 0 "${EXIT_CODE}" "lease6 table is missing or broken. (expected status code %d, returned %d)" # Fourth table: lease6_types run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT lease_type, name FROM lease6_types;' + 'SELECT lease_type, name FROM lease6_types' assert_eq 0 "${EXIT_CODE}" "lease6_types table is missing or broken. (expected status code %d, returned %d)" # Fifth table: lease_hwaddr_source run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT hwaddr_source, name FROM lease_hwaddr_source;' + '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)" # Let's wipe the whole database @@ -142,7 +140,7 @@ mysql_db_version_test() { version INT PRIMARY KEY NOT NULL, minor INT ); -INSERT INTO schema_version VALUES (1, 7);' +INSERT INTO schema_version VALUES (1, 7)' assert_eq 0 "${EXIT_CODE}" "schema_version table cannot be created. (expected %d, exit code %d)" run_command \ @@ -175,7 +173,7 @@ mysql_db_version_with_extra_test() { version INT PRIMARY KEY NOT NULL, minor INT ); -INSERT INTO schema_version VALUES (1, 7);' +INSERT INTO schema_version VALUES (1, 7)' assert_eq 0 "${EXIT_CODE}" "schema_version table cannot be created. (expected %d, exit code %d)" # Single -x. @@ -217,43 +215,43 @@ mysql_host_reservation_init_test() { # First table: schema_version. Should have 2 columns: version and minor. run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT version, minor FROM schema_version;' + 'SELECT version, minor FROM schema_version' assert_eq 0 "${EXIT_CODE}" "schema_version table is missing or broken. (expected status code %d, returned %d)" # Second table: hosts run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - '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;' + '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)" # Third table: ipv6_reservations run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT reservation_id, address, prefix_len, type, dhcp6_iaid, host_id FROM ipv6_reservations;' + '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)" # Fourth table: dhcp4_options run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id, scope_id FROM dhcp4_options;' + '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)" # Fifth table: dhcp6_options run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id, scope_id FROM dhcp6_options;' + '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)" # Sixth table: host_identifier_type run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT type, name FROM host_identifier_type;' + '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)" # Seventh table: dhcp_option_scope run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT scope_id, scope_name FROM dhcp_option_scope;' + '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)" # Let's wipe the whole database @@ -313,78 +311,78 @@ mysql_upgrade_schema_to_version() { mysql_upgrade_12_to_13_test() { # Check the output of colonSeparatedHex(). run_command \ - mysql_execute 'SELECT colonSeparatedHex(HEX(0xF123456789));' + mysql_execute 'SELECT colonSeparatedHex(HEX(0xF123456789))' assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d' assert_str_eq 'f1:23:45:67:89' "${OUTPUT}" run_command \ - mysql_execute 'SELECT colonSeparatedHex("");' + mysql_execute 'SELECT colonSeparatedHex("")' assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d' assert_str_eq '' "${OUTPUT}" run_command \ - mysql_execute 'SELECT colonSeparatedHex(HEX(0xF));' + mysql_execute 'SELECT colonSeparatedHex(HEX(0xF))' assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d' assert_str_eq '0f' "${OUTPUT}" run_command \ - mysql_execute 'SELECT colonSeparatedHex(HEX(0xF1));' + mysql_execute 'SELECT colonSeparatedHex(HEX(0xF1))' assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d' assert_str_eq 'f1' "${OUTPUT}" run_command \ - mysql_execute 'SELECT colonSeparatedHex(HEX(0xF12));' + mysql_execute 'SELECT colonSeparatedHex(HEX(0xF12))' assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d' assert_str_eq '0f:12' "${OUTPUT}" run_command \ - mysql_execute 'SELECT colonSeparatedHex(HEX(458753));' + mysql_execute 'SELECT colonSeparatedHex(HEX(458753))' assert_eq 0 "${EXIT_CODE}" 'colonSeparatedHex() failed, expected exit code %d, actual %d' assert_str_eq '07:00:01' "${OUTPUT}" # Check lease4Dump*(). run_command \ - mysql_execute "INSERT INTO lease4 VALUES(10,20,30,40,(SELECT FROM_UNIXTIME(1678900000)),50,1,1,'one,example,com',0,'{ \"a\": 1, \"b\": 2 }',NULL,NULL,0);" + mysql_execute "INSERT INTO lease4 VALUES(10,20,30,40,(SELECT FROM_UNIXTIME(1678900000)),50,1,1,'one,example,com',0,'{ \"a\": 1, \"b\": 2 }',NULL,NULL,0)" assert_eq 0 "${EXIT_CODE}" 'INSERT INTO lease4 failed, expected exit code %d, actual %d' assert_str_eq '' "${OUTPUT}" run_command \ - mysql_execute "CALL lease4DumpHeader();" + mysql_execute "CALL 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,pool_id' "${OUTPUT}" run_command \ - mysql_execute "CALL lease4DumpData();" + mysql_execute "CALL lease4DumpData()" assert_eq 0 "${EXIT_CODE}" 'lease4DumpData() failed, expected exit code %d, actual %d' output=$(printf '%s' "${OUTPUT}" | sed 's/\t/,/g') # turn tabs into commas assert_str_eq '0.0.0.10,32:30,33:30,40,1678900000,50,1,1,oneˎxampleˌom,0,{ "a": 1, "b": 2 },0' "${output}" # Check lease6Dump*(). run_command \ - mysql_execute "INSERT INTO lease6 VALUES('::10',20,30,(SELECT FROM_UNIXTIME(1678900000)),40,50,1,60,70,1,1,'one,example,com',80,90,16,0,'{ \"a\": 1, \"b\": 2 }',NULL,0);" + mysql_execute "INSERT INTO lease6 VALUES('::10',20,30,(SELECT FROM_UNIXTIME(1678900000)),40,50,1,60,70,1,1,'one,example,com',80,90,16,0,'{ \"a\": 1, \"b\": 2 }',NULL,0)" assert_eq 0 "${EXIT_CODE}" 'INSERT INTO lease6 failed, expected exit code %d, actual %d' assert_str_eq '' "${OUTPUT}" run_command \ - mysql_execute "CALL lease6DumpHeader();" + mysql_execute "CALL 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,pool_id' "${OUTPUT}" run_command \ - mysql_execute "CALL lease6DumpData();" + mysql_execute "CALL lease6DumpData()" assert_eq 0 "${EXIT_CODE}" 'lease6DumpData() failed, expected exit code %d, actual %d' output=$(printf '%s' "${OUTPUT}" | sed 's/\t/,/g') # turn tabs into commas assert_str_eq '::10,32:30,30,1678900000,40,50,1,60,70,1,1,oneˎxampleˌom,38:30,0,{ "a": 1, "b": 2 },90,16,0' "${output}" # Check lease4Upload(). run_command \ - mysql_execute "CALL lease4Upload('192.0.0.0','ff0102030405','01ff0102030405',7200,1234567890,1,0,0,'',0,'',0);" + mysql_execute "CALL lease4Upload('192.0.0.0','ff0102030405','01ff0102030405',7200,1234567890,1,0,0,'',0,'',0)" assert_eq 0 "${EXIT_CODE}" 'lease4Upload() failed, expected exit code %d, actual %d' assert_str_eq '' "${OUTPUT}" # Check lease6Upload(). run_command \ - mysql_execute "CALL lease6Upload('2001:db8::','000100012955cb80ff0102030407',7200,1234567890,1,3600,0,1,128,0,0,'','ff0102030407',0,'',90,16,0);" + mysql_execute "CALL lease6Upload('2001:db8::','000100012955cb80ff0102030407',7200,1234567890,1,3600,0,1,128,0,0,'','ff0102030407',0,'',90,16,0)" assert_eq 0 "${EXIT_CODE}" 'lease6Upload() failed, expected exit code %d, actual %d' assert_str_eq '' "${OUTPUT}" } @@ -392,8 +390,7 @@ mysql_upgrade_12_to_13_test() { mysql_upgrade_13_to_14_test() { # Check function source code run_command \ - mysql_execute "select action_statement from information_schema.TRIGGERS where trigger_schema = '${db_name}' and trigger_name = 'dhcp4_shared_network_BDEL'"; - + mysql_execute "select action_statement from information_schema.TRIGGERS where trigger_schema = '${db_name}' and trigger_name = '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 @@ -401,21 +398,20 @@ mysql_upgrade_13_to_14_test() { # Check function source code run_command \ - mysql_execute "select action_statement from information_schema.TRIGGERS where trigger_schema = '${db_name}' and trigger_name = 'dhcp6_shared_network_BDEL'"; - + mysql_execute "select action_statement from information_schema.TRIGGERS where trigger_schema = '${db_name}' and trigger_name = '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;" + qry="select user_context from dhcp4_client_class limit 1" run_command \ mysql_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;" + qry="select user_context from dhcp6_client_class limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" @@ -430,7 +426,7 @@ mysql_upgrade_13_to_14_test() { assert_str_eq "NULL" "${OUTPUT}" "${query}: expected output %s, returned %s" # Clean up. - query='DELETE FROM lease4; DELETE FROM lease6;' + query='DELETE FROM lease4; DELETE FROM lease6' run_command \ mysql_execute "${query}" assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" @@ -484,7 +480,7 @@ mysql_upgrade_13_to_14_test() { 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;" + query="SELECT leases FROM lease${v}_stat_by_client_class WHERE client_class = 'foo' LIMIT 1" run_command \ mysql_execute "${query}" assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" @@ -496,14 +492,14 @@ mysql_upgrade_13_to_14_test() { # -- Verify some calls to checkLeaseXLimits(). -- - query="SELECT checkLease${v}Limits('');" + query="SELECT checkLease${v}Limits('')" run_command \ mysql_execute "${query}" # Should fail with ERROR 4037 (HY000): Unexpected end of JSON text in argument 1 to function 'json_extract' assert_eq 1 "${EXIT_CODE}" "${query}: expected %d, returned %d" assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" - query="SELECT checkLease${v}Limits('{}');" + query="SELECT checkLease${v}Limits('{}')" run_command \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -514,7 +510,7 @@ mysql_upgrade_13_to_14_test() { 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 } ] } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 1 } ] } } }')" run_command \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -526,7 +522,7 @@ mysql_upgrade_13_to_14_test() { assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" fi - query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 1 } } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 1 } } } }')" run_command \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -538,7 +534,7 @@ mysql_upgrade_13_to_14_test() { 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 } ] } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 2 } ] } } }')" run_command \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -550,7 +546,7 @@ mysql_upgrade_13_to_14_test() { assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" fi - query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 2 } } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 2 } } } }')" run_command \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -562,7 +558,7 @@ mysql_upgrade_13_to_14_test() { 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 } ] } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"client-classes\": [ { \"name\": \"foo\", \"address-limit\": 4 } ] } } }')" run_command \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -573,7 +569,7 @@ mysql_upgrade_13_to_14_test() { fi assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" - query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 4 } } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 4 } } } }')" run_command \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -584,7 +580,7 @@ mysql_upgrade_13_to_14_test() { 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 } } } }');" + 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 \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -596,7 +592,7 @@ mysql_upgrade_13_to_14_test() { 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 } } } }');" + 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 \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -608,7 +604,7 @@ mysql_upgrade_13_to_14_test() { 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 } } } }');" + 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 \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -620,7 +616,7 @@ mysql_upgrade_13_to_14_test() { 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 } } } }');" + 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 \ mysql_execute "${query}" if test "${json_supported}" = 1; then @@ -697,19 +693,19 @@ mysql_upgrade_14_to_15_test() { run_statement "dhcp6_options" "$qry" # Check if offer_lifetime was added to dhcp4_shared_network table. - qry="SELECT offer_lifetime from dhcp4_shared_network limit 1;" + qry="SELECT offer_lifetime from dhcp4_shared_network limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if offer_lifetime was added to dhcp4_subnet table. - qry="SELECT offer_lifetime from dhcp4_subnet limit 1;" + qry="SELECT offer_lifetime from dhcp4_subnet limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if offer_lifetime was added to dhcp4_client_class table. - qry="SELECT offer_lifetime from dhcp4_client_class limit 1;" + qry="SELECT offer_lifetime from dhcp4_client_class limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" @@ -717,37 +713,37 @@ mysql_upgrade_14_to_15_test() { mysql_upgrade_16_to_17_test() { # Check if allocator was added to dhcp4_shared_network table. - qry="SELECT allocator from dhcp4_shared_network limit 1;" + qry="SELECT allocator from dhcp4_shared_network limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if allocator was added to dhcp6_shared_network table. - qry="SELECT allocator from dhcp6_shared_network limit 1;" + qry="SELECT allocator from dhcp6_shared_network limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if pd_allocator was added to dhcp6_shared_network table. - qry="SELECT pd_allocator from dhcp6_shared_network limit 1;" + qry="SELECT pd_allocator from dhcp6_shared_network limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if allocator was added to dhcp4_subnet table. - qry="SELECT allocator from dhcp4_subnet limit 1;" + qry="SELECT allocator from dhcp4_subnet limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if allocator was added to dhcp6_subnet table. - qry="SELECT allocator from dhcp6_subnet limit 1;" + qry="SELECT allocator from dhcp6_subnet limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if pd_allocator was added to dhcp6_subnet table. - qry="SELECT pd_allocator from dhcp6_subnet limit 1;" + qry="SELECT pd_allocator from dhcp6_subnet limit 1" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" @@ -755,30 +751,30 @@ mysql_upgrade_16_to_17_test() { mysql_upgrade_17_to_18_test() { # lease4 client_id should support 255 long strings. - qry="insert into lease4 (address, client_id, subnet_id) values (1, '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 1);" + qry="insert into lease4 (address, client_id, subnet_id) values (1, '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 1)" run_statement "lease4_255_long_client_id" "$qry" # lease4 relay_id should support 255 long strings. - qry="insert into lease4 (address, remote_id, subnet_id) values (2, '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 1);" + qry="insert into lease4 (address, remote_id, subnet_id) values (2, '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 1)" run_statement "lease4_255_long_relay_id" "$qry" # lease4 remote_id should support 255 long strings. - qry="insert into lease4 (address, remote_id, subnet_id) values (3, '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 1);" + qry="insert into lease4 (address, remote_id, subnet_id) values (3, '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 1)" run_statement "lease4_255_long_remote_id" "$qry" # hosts dhcp_identifier should support 255 long strings. - qry="insert into hosts (dhcp_identifier, dhcp_identifier_type) values ('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 0);" + qry="insert into hosts (dhcp_identifier, dhcp_identifier_type) values ('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345', 0)" run_statement "hosts_255_long_dhcp_identifier" "$qry" #lease6 duid should support 130 long strings. - qry="insert into lease6 values('::10',12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890,30,(SELECT FROM_UNIXTIME(1642000000)),40,50,1,60,70,1,1,'one.example.com',80,90,16,0,NULL,NULL,0);" + qry="insert into lease6 values('::10',12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890,30,(SELECT FROM_UNIXTIME(1642000000)),40,50,1,60,70,1,1,'one.example.com',80,90,16,0,NULL,NULL,0)" run_statement "lease6_130_long_duid" "$qry" #lease6 new binaddr column. qry="select binaddr from lease6" run_statement "lease6" "$qry" - qry="show indexes from lease6 where key_name = 'lease6_by_binaddr'"; + qry="show indexes from lease6 where key_name = 'lease6_by_binaddr'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show indexes from lease6 failed. (expected status code %d, returned %d)" @@ -788,14 +784,26 @@ mysql_upgrade_17_to_18_test() { #lease6_relay_id new table. run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT extended_info_id, relay_id, lease_addr FROM lease6_relay_id;' + 'SELECT extended_info_id, relay_id, lease_addr FROM lease6_relay_id' assert_eq 0 "${EXIT_CODE}" "lease6_relay_id table is missing or broken. (expected status code %d, returned %d)" #lease6_remote_id new table. run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT extended_info_id, remote_id, lease_addr FROM lease6_remote_id;' + 'SELECT extended_info_id, remote_id, lease_addr FROM lease6_remote_id' assert_eq 0 "${EXIT_CODE}" "lease6_remote_id table is missing or broken. (expected status code %d, returned %d)" + + #lease4_pool_stat new table. + run_command \ + mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ + 'SELECT subnet_id, pool_id, state, leases FROM lease4_pool_stat' + assert_eq 0 "${EXIT_CODE}" "lease4_pool_stat table is missing or broken. (expected status code %d, returned %d)" + + #lease6_pool_stat new table. + run_command \ + mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ + 'SELECT subnet_id, pool_id, lease_type, state, leases FROM lease6_pool_stat' + assert_eq 0 "${EXIT_CODE}" "lease6_pool_stat table is missing or broken. (expected status code %d, returned %d)" } mysql_upgrade_test() { @@ -826,65 +834,65 @@ mysql_upgrade_test() { #table: lease6 (upgrade 1.0 -> 2.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT hwaddr, hwtype, hwaddr_source FROM lease6;' + 'SELECT hwaddr, hwtype, hwaddr_source FROM lease6' assert_eq 0 "${EXIT_CODE}" "lease6 table not upgraded to 2.0 (expected status code %d, returned %d)" #table: lease_hwaddr_source (upgrade 1.0 -> 2.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT hwaddr_source, name FROM lease_hwaddr_source;' + '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)" #table: hosts (upgrade 2.0 -> 3.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes FROM hosts;' + 'SELECT host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes FROM hosts' assert_eq 0 "${EXIT_CODE}" "hosts table is missing or broken. (expected status code %d, returned %d)" #table: ipv6_reservations (upgrade 2.0 -> 3.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT reservation_id, address, prefix_len, type, dhcp6_iaid, host_id FROM ipv6_reservations;' + '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)" #table: dhcp4_options (upgrade 2.0 -> 3.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id FROM dhcp4_options;' + 'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id FROM dhcp4_options' assert_eq 0 "${EXIT_CODE}" "dhcp4_options table is missing or broken. (expected status code %d, returned %d)" #table: dhcp6_options (upgrade 2.0 -> 3.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id FROM dhcp6_options;' + 'SELECT option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id FROM dhcp6_options' assert_eq 0 "${EXIT_CODE}" "dhcp6_options table is missing or broken. (expected status code %d, returned %d)" #table: lease_state table added (upgrade 3.0 -> 4.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT state,name from lease_state;' + 'SELECT state,name from lease_state' assert_eq 0 "${EXIT_CODE}" "dhcp6_options table is missing or broken. (expected status code %d, returned %d)" #table: state column added to lease4 (upgrade 3.0 -> 4.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT state from lease4;' + 'SELECT state from lease4' assert_eq 0 "${EXIT_CODE}" "lease4 is missing state column. (expected status code %d, returned %d)" #table: state column added to lease6 (upgrade 3.0 -> 4.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT state from lease6;' + 'SELECT state from lease6' assert_eq 0 "${EXIT_CODE}" "lease6 is missing state column. (expected status code %d, returned %d)" #table: stored procedures for lease dumps added (upgrade 3.0 -> 4.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'call lease4DumpHeader(); call lease4DumpData(); call lease6DumpHeader(); call lease6DumpHeader();' + 'call lease4DumpHeader(); call lease4DumpData(); call lease6DumpHeader(); call lease6DumpHeader()' assert_eq 0 "${EXIT_CODE}" "lease dump stored procedures are missing or broken. (expected status code %d, returned %d)" #lease_hardware_source should have row for source = 0 (upgrade 4.0 -> 4.1) - qry="select count(hwaddr_source) from lease_hwaddr_source where hwaddr_source = 0 and name='HWADDR_SOURCE_UNKNOWN';" + qry="select count(hwaddr_source) from lease_hwaddr_source where hwaddr_source = 0 and name='HWADDR_SOURCE_UNKNOWN'" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -910,7 +918,7 @@ mysql_upgrade_test() { #table: host_identifier_type (upgrade 4.1 -> 5.0) # verify that host_identifier_type table exists. - qry="select count(*) from host_identifier_type"; + qry="select count(*) from host_identifier_type" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -918,7 +926,7 @@ mysql_upgrade_test() { assert_eq 5 "${count}" "host_identifier_type does not contain correct number of entries. (expected count %d, returned %d)" # verify that foreign key fk_host_identifier_type exists - qry="show create table hosts"; + qry="show create table hosts" run_command \ mysql_execute "${qry}" count=$(echo "${OUTPUT}" | grep -Fci -m 1 'fk_host_identifier_type') || true @@ -927,7 +935,7 @@ mysql_upgrade_test() { #table: dhcp_option_scope (upgrade 4.1 -> 5.0) # verify that dhcp_option_scope table exists. - qry="select count(*) from dhcp_option_scope"; + qry="select count(*) from dhcp_option_scope" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -937,7 +945,7 @@ mysql_upgrade_test() { #table: scope_id columns to dhcp4_options (upgrade 4.1 -> 5.0) # verify that dhcp4_options table includes scope_id - qry="select scope_id from dhcp4_options"; + qry="select scope_id from dhcp4_options" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -945,7 +953,7 @@ mysql_upgrade_test() { #table: scope_id columns to dhcp6_options (upgrade 4.1 -> 5.0) # verify that dhcp6_options table includes scope_id - qry="select scope_id from dhcp6_options"; + qry="select scope_id from dhcp6_options" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -953,7 +961,7 @@ mysql_upgrade_test() { #table: DHCPv4 fixed field columns (upgrade 4.1 -> 5.0) # verify that hosts table has columns holding values for DHCPv4 fixed fields - qry="select dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name, auth_key from hosts"; + qry="select dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name, auth_key from hosts" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -976,7 +984,7 @@ mysql_upgrade_test() { assert_eq 1 "${count}" "dhcp6_subnet_id is not of unsigned type. (expected count %d, returned %d)" #host_identifier_type should have rows for types 3 and 4 (upgrade 5.0 -> 5.1) - qry="select count(*) from host_identifier_type"; + qry="select count(*) from host_identifier_type" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -985,21 +993,21 @@ mysql_upgrade_test() { #table: user_context columns to hosts, dhcp4_options and dhcp6_options (upgrade 5.2 -> 6.0) # verify that hosts table includes user_context - qry="select user_context from hosts"; + qry="select user_context from hosts" run_command \ mysql_execute "${qry}" count="${OUTPUT}" assert_eq 0 "${EXIT_CODE}" "select user_context from hosts failed. (expected status code %d, returned %d)" # verify that dhcp4_options table includes user_context - qry="select user_context from dhcp4_options"; + qry="select user_context from dhcp4_options" run_command \ mysql_execute "${qry}" count="${OUTPUT}" assert_eq 0 "${EXIT_CODE}" "select user_context from dhcp4_options failed. (expected status code %d, returned %d)" # verify that dhcp6_options table includes user_context - qry="select user_context from dhcp6_options"; + qry="select user_context from dhcp6_options" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -1009,14 +1017,14 @@ mysql_upgrade_test() { #table: user_context to lease4 and lease6 (upgrade 6.0 -> 7.0) # verify that lease4 table includes user_context - qry="select user_context from lease4"; + qry="select user_context from lease4" run_command \ mysql_execute "${qry}" count="${OUTPUT}" assert_eq 0 "${EXIT_CODE}" "select user_context from lease4 failed. (expected status code %d, returned %d)" # verify that lease6 table includes user_context - qry="select user_context from lease6"; + qry="select user_context from lease6" run_command \ mysql_execute "${qry}" count="${OUTPUT}" @@ -1025,7 +1033,7 @@ mysql_upgrade_test() { #table: logs (upgrade 6.0 -> 7.0) run_command \ mysql -u"${db_user}" -p"${db_password}" "${db_name}" -e \ - 'SELECT timestamp, address, log FROM logs;' + 'SELECT timestamp, address, log FROM logs' assert_eq 0 "${EXIT_CODE}" "logs table is missing or broken. (expected status code %d, returned %d)" # table: modification (upgrade 6.0 -> 7.0) @@ -1186,7 +1194,7 @@ mysql_upgrade_test() { run_statement "createOptionAuditDHCP4 cascade update" "$qry" # The number of rows matching the audit entry should be 0. - qry="SELECT COUNT(*) FROM dhcp4_audit WHERE object_type = 'dhcp4_options' AND object_id = 1024"; + qry="SELECT COUNT(*) FROM dhcp4_audit WHERE object_type = 'dhcp4_options' AND object_id = 1024" run_statement "createOptionAuditDHCP4 cascade update, entry not inserted" "$qry" 0; # This time set the cascade_update to 0 and expect that the @@ -1194,7 +1202,7 @@ mysql_upgrade_test() { qry="SET @audit_revision_id = (SELECT id FROM dhcp4_audit_revision LIMIT 1); SET @cascade_transaction = 0; CALL createOptionAuditDHCP4('create', 0, 1024, NULL, NULL, NULL, NULL, now())" run_statement "createOptionAuditDHCP4 cascade update" "$qry" - qry="SELECT COUNT(*) FROM dhcp4_audit WHERE object_type = 'dhcp4_options' AND object_id = 1024"; + qry="SELECT COUNT(*) FROM dhcp4_audit WHERE object_type = 'dhcp4_options' AND object_id = 1024" run_statement "createOptionAuditDHCP4 cascade update, entry not inserted" "$qry" 1; # Test that createAuditRevisionDHCP6 exists and creates entry in @@ -1229,7 +1237,7 @@ mysql_upgrade_test() { run_statement "createOptionAuditDHCP6 cascade update" "$qry" # The number of rows matching the audit entry should be 0. - qry="SELECT COUNT(*) FROM dhcp6_audit WHERE object_type = 'dhcp6_options' AND object_id = 1024"; + qry="SELECT COUNT(*) FROM dhcp6_audit WHERE object_type = 'dhcp6_options' AND object_id = 1024" run_statement "createOptionAuditDHCP6 cascade update, entry not inserted" "$qry" 0; # This time set the cascade_update to 0 and expect that the @@ -1237,14 +1245,14 @@ mysql_upgrade_test() { qry="SET @audit_revision_id = (SELECT id FROM dhcp6_audit_revision LIMIT 1); SET @cascade_transaction = 0; CALL createOptionAuditDHCP6('create', 0, 1024, NULL, NULL, NULL, NULL, NULL,now())" run_statement "createOptionAuditDHCP6 cascade update" "$qry" - qry="SELECT COUNT(*) FROM dhcp6_audit WHERE object_type = 'dhcp6_options' AND object_id = 1024"; + qry="SELECT COUNT(*) FROM dhcp6_audit WHERE object_type = 'dhcp6_options' AND object_id = 1024" run_statement "createOptionAuditDHCP6 cascade update, entry not inserted" "$qry" 1; # New triggers aren't tested here because the extensive tests are # provided with the backend implementations. # parameter_data_type must exist and must have 4 rows. - qry="SELECT COUNT(*) FROM parameter_data_type"; + qry="SELECT COUNT(*) FROM parameter_data_type" run_statement "parameter_data_type count" "$qry" 4; # Schema upgrade from 8.0 to 8.2 @@ -1313,7 +1321,7 @@ mysql_upgrade_test() { insert_sql="\ insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_address) values (hex('010101010101'), 0, 1, inet_aton('192.0.2.0'));\ -insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_address) values (hex('010101010102'), 0, 1, inet_aton('192.0.2.0'));" +insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_address) values (hex('010101010102'), 0, 1, inet_aton('192.0.2.0'))" run_command \ mysql_execute "$insert_sql" assert_eq 0 "${EXIT_CODE}" "insert into hosts failed, expected exit code %d, actual %d" @@ -1324,7 +1332,7 @@ insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_a qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp4_shared_network" run_statement "dhcp4_shared_network" "$qry" - qry="show columns from dhcp4_shared_network like 'reservation_mode'"; + qry="show columns from dhcp4_shared_network like 'reservation_mode'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show columns from dhcp4_shared_network like 'reservation_mode' failed. (expected status code %d, returned %d)" @@ -1335,7 +1343,7 @@ insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_a qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp4_subnet" run_statement "dhcp4_subnet" "$qry" - qry="show columns from dhcp4_subnet like 'reservation_mode'"; + qry="show columns from dhcp4_subnet like 'reservation_mode'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show columns from dhcp4_subnet like 'reservation_mode' failed. (expected status code %d, returned %d)" @@ -1346,7 +1354,7 @@ insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_a qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp6_shared_network" run_statement "dhcp6_shared_network" "$qry" - qry="show columns from dhcp6_shared_network like 'reservation_mode'"; + qry="show columns from dhcp6_shared_network like 'reservation_mode'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show columns from dhcp6_shared_network like 'reservation_mode' failed. (expected status code %d, returned %d)" @@ -1357,7 +1365,7 @@ insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_a qry="select reservations_global, reservations_in_subnet, reservations_out_of_pool from dhcp6_subnet" run_statement "dhcp6_subnet" "$qry" - qry="show columns from dhcp6_subnet like 'reservation_mode'"; + qry="show columns from dhcp6_subnet like 'reservation_mode'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show columns from dhcp6_subnet like 'reservation_mode' failed. (expected status code %d, returned %d)" @@ -1382,20 +1390,20 @@ insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_a qry="select cache_threshold, cache_max_age from dhcp6_subnet" run_statement "dhcp6_shared_network" "$qry" - qry='SELECT id FROM logs'; + qry='SELECT id FROM logs' run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry} failed: expected status code %d, returned %d" # Check upgrade from 10.0 to 11.0. - qry="show indexes from lease4 where key_name = 'lease4_by_expire_state'"; + qry="show indexes from lease4 where key_name = 'lease4_by_expire_state'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show indexes from lease4 failed. (expected status code %d, returned %d)" count=$(echo "${OUTPUT}" | grep -Fci lease4_by_expire_state) assert_eq 2 "${count}" "lease4_by_expire_state wrong or missing. (expected count %d, actual %d)" - qry="show indexes from lease6 where key_name = 'lease6_by_expire_state'"; + qry="show indexes from lease6 where key_name = 'lease6_by_expire_state'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show indexes from lease6 failed. (expected status code %d, returned %d)" @@ -1417,7 +1425,7 @@ INSERT INTO dhcp4_client_class(name, modification_ts) VALUES ('foo', now());\ INSERT INTO dhcp4_options(code, scope_id, dhcp_client_class, modification_ts) VALUES (222, 2, 'foo', now());\ INSERT INTO dhcp6_client_class(name, modification_ts) VALUES ('foo', now());\ INSERT INTO dhcp6_options(code, scope_id, dhcp_client_class, modification_ts) VALUES (222, 2, 'foo', now());\ -SET @disable_audit = 0;" +SET @disable_audit = 0" run_command \ mysql_execute "$qry" assert_eq 0 "${EXIT_CODE}" "inserting classes and options failed, expected exit code %d, actual %d" @@ -1427,7 +1435,7 @@ SET @disable_audit = 0;" SET @disable_audit = 1;\ DELETE FROM dhcp4_client_class;\ DELETE FROM dhcp6_client_class;\ -SET @disable_audit = 0;" +SET @disable_audit = 0" run_command \ mysql_execute "$qry" assert_eq 0 "${EXIT_CODE}" "deleting classes failed, expected exit code %d, actual %d" @@ -1455,7 +1463,7 @@ SET @disable_audit = 0;" qry="select relay_id from lease4" run_statement "lease4" "$qry" - qry="show indexes from lease4 where key_name = 'lease4_by_relay_id'"; + qry="show indexes from lease4 where key_name = 'lease4_by_relay_id'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show indexes from lease4 failed. (expected status code %d, returned %d)" @@ -1466,7 +1474,7 @@ SET @disable_audit = 0;" qry="select remote_id from lease4" run_statement "lease4" "$qry" - qry="show indexes from lease4 where key_name = 'lease4_by_remote_id'"; + qry="show indexes from lease4 where key_name = 'lease4_by_remote_id'" run_command \ mysql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "show indexes from lease4 failed. (expected status code %d, returned %d)" @@ -1522,7 +1530,7 @@ mysql_lease4_dump_test() { insert_sql="\ insert into lease4 values(10,20,30,40,(SELECT FROM_UNIXTIME(1642000000)),50,1,1,'one.example.com',0,NULL,NULL,NULL,0); insert into lease4 values(11,NULL,123,40,(SELECT FROM_UNIXTIME(1643210000)),50,1,1,'',1,'{ }',NULL,NULL,0);\ -insert into lease4 values(12,22,NULL,40,(SELECT FROM_UNIXTIME(1643212345)),50,1,1,'three,example,com',2,'{ \"a\": 1, \"b\": \"c\" }',NULL,NULL,0);" +insert into lease4 values(12,22,NULL,40,(SELECT FROM_UNIXTIME(1643212345)),50,1,1,'three,example,com',2,'{ \"a\": 1, \"b\": \"c\" }',NULL,NULL,0)" run_command \ mysql_execute "$insert_sql" @@ -1585,7 +1593,7 @@ mysql_lease6_dump_test() { insert_sql="\ insert into lease6 values('::10',203,30,(SELECT FROM_UNIXTIME(1642000000)),40,50,1,60,70,1,1,'one.example.com',80,90,16,0,NULL,NULL,0);\ insert into lease6 values('::11',213,30,(SELECT FROM_UNIXTIME(1643210000)),40,50,1,60,70,1,1,'',80,90,1,1,'{ }',NULL,0);\ -insert into lease6 values('::12',223,30,(SELECT FROM_UNIXTIME(1643212345)),40,50,1,60,70,1,1,'three,example,com',80,90,4,2,'{ \"a\": 1, \"b\": \"c\" }',NULL,0);" +insert into lease6 values('::12',223,30,(SELECT FROM_UNIXTIME(1643212345)),40,50,1,60,70,1,1,'three,example,com',80,90,4,2,'{ \"a\": 1, \"b\": \"c\" }',NULL,0)" run_command \ mysql_execute "$insert_sql" @@ -1770,36 +1778,80 @@ mysql_lease4_stat_test() { assert_eq 0 "${EXIT_CODE}" "kea-admin db-init mysql failed, expected %d, returned non-zero status code %d" # Verify lease4 stat table is present - qry="select count(subnet_id) from lease4_stat"; + 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);" + 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"; + qry="select leases from lease4_stat where subnet_id = 1 and state = 0" run_statement "#3" "$qry" 1 + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 0" + run_statement "#4" "$qry" 1 + # Set lease state to declined - qry="update lease4 set state = 1 where address = 111;" - run_statement "#4" "$qry" + qry="update lease4 set state = 1 where address = 111" + run_statement "#5" "$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 + qry="select leases from lease4_stat where subnet_id = 1 and state = 0" + run_statement "#6" "$qry" 0 + + # Leases state count for assigned should be 0 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 0" + run_statement "#7" "$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 "#8" "$qry" 1 # 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 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 1" + run_statement "#9" "$qry" 1 # Delete the lease - qry="delete from lease4 where address = 111;" - run_statement "#7" "$qry" + qry="delete from lease4 where address = 111" + run_statement "#10" "$qry" + + # Leases state count for declined should be 0 + qry="select leases from lease4_stat where subnet_id = 1 and state = 1" + run_statement "#11" "$qry" 0 # 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 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 1" + run_statement "#12" "$qry" 0 + + # Insert lease4 + qry="insert into lease4 (address, subnet_id, pool_id, state) values (112,1,1,0)" + run_statement "#13" "$qry" + + # Assigned state count should be 1 + qry="select leases from lease4_stat where subnet_id = 1 and state = 0" + run_statement "#14" "$qry" 1 + + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 1 and state = 0" + run_statement "#15" "$qry" 1 + + # Insert lease4 + qry="insert into lease4 (address, subnet_id, pool_id, state) values (113,1,2,0)" + run_statement "#16" "$qry" + + # Assigned state count should be 2 + qry="select leases from lease4_stat where subnet_id = 1 and state = 0" + run_statement "#17" "$qry" 2 + + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 1 and state = 0" + run_statement "#18" "$qry" 1 + + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 2 and state = 0" + run_statement "#18" "$qry" 1 # Let's wipe the whole database mysql_wipe @@ -1816,35 +1868,81 @@ mysql_lease4_stat_test() { # param ltype - type of lease to create mysql_lease6_stat_per_type() { addr=$1;shift + addr1=$1;shift + addr2=$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);" + 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"; + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0" run_statement "#3" "$qry" 1 + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 0" + run_statement "#4" "$qry" 1 + # update the lease, changing state to declined - qry="update lease6 set state = 1 where address = $addr;" - run_statement "#4" "$qry" + qry="update lease6 set state = 1 where address = $addr" + run_statement "#5" "$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 "#6" "$qry" 0 # 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 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 0" + run_statement "#7" "$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 "#8" "$qry" 1 # 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 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 1" + run_statement "#9" "$qry" 1 # delete the lease - qry="delete from lease6 where address = $addr;" - run_statement "#7" "$qry" + qry="delete from lease6 where address = $addr" + run_statement "#10" "$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 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0" + run_statement "#11" "$qry" 0 + + # leases count for declined state should be 0 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 0" + run_statement "#12" "$qry" 0 + + # insert a lease6 for addr and ltype, state assigned + qry="insert into lease6 (address, lease_type, subnet_id, pool_id, state) values ($addr1,$ltype,1,1,0)" + run_statement "#13" "$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 "#14" "$qry" 1 + + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 1 and state = 0" + run_statement "#15" "$qry" 1 + + # insert a lease6 for addr and ltype, state assigned + qry="insert into lease6 (address, lease_type, subnet_id, pool_id, state) values ($addr2,$ltype,1,2,0)" + run_statement "#16" "$qry" + + # assigned stat should be 2 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0" + run_statement "#17" "$qry" 2 + + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 1 and state = 0" + run_statement "#18" "$qry" 1 + + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 2 and state = 0" + run_statement "#19" "$qry" 1 } # Verifies that lease6_stat triggers operation correctly @@ -1866,10 +1964,10 @@ mysql_lease6_stat_test() { run_statement "#1" "$qry" # Test for address 111, NA lease type - mysql_lease6_stat_per_type "111" "0" + mysql_lease6_stat_per_type "111" "112" "113" "0" # Test for address 222, PD lease type - mysql_lease6_stat_per_type "222" "1" + mysql_lease6_stat_per_type "222" "223" "224" "1" # Let's wipe the whole database mysql_wipe @@ -1902,7 +2000,7 @@ mysql_lease_stat_upgrade_test() { 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);" + insert into lease4 (address, subnet_id, state) values (555,77,0)" run_statement "insert v4 leases" "$qry" qry=\ @@ -1911,7 +2009,7 @@ mysql_lease_stat_upgrade_test() { 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);" + insert into lease6 (address, lease_type, subnet_id, state) values (666,1,40,2)" run_statement "insert v6 leases" "$qry" # Let's upgrade it to the latest version. @@ -1926,45 +2024,81 @@ mysql_lease_stat_upgrade_test() { qry="select leases from lease4_stat where subnet_id = 10 and state = 0" run_statement "#4.1" "$qry" 2 + # Assigned leases for subnet 10 should be 2 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 0 and state = 0" + run_statement "#4.2" "$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 + run_statement "#4.3" "$qry" 1 + + # Assigned leases for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 0 and state = 0" + run_statement "#4.4" "$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 + run_statement "#4.5" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease4_pool_stat where state = 2" + run_statement "#4.6" "$qry" 0 # - # Now we'll verify v4 trigger operation for insert,update, and delete + # 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" + qry="insert into lease4 (address, subnet_id, pool_id, state) values (777,77,1,0)" + run_statement "#4.7" "$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 + run_statement "#4.8" "$qry" 2 + + # Assigned count for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 0 and state = 0" + run_statement "#4.9" "$qry" 1 + + # Assigned count for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 0" + run_statement "#4.10" "$qry" 1 # Update the state of the new lease to declined - qry="update lease4 set state = 1 where address = 777;" - run_statement "#4.6" "$qry" + qry="update lease4 set state = 1 where address = 777" + run_statement "#4.11" "$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 + run_statement "#4.12" "$qry" 1 + + # Assigned count for subnet 77 should be 1 again + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 0 and state = 0" + run_statement "#4.13" "$qry" 1 + + # Assigned count for subnet 77 should be 0 again + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 0" + run_statement "#4.14" "$qry" 0 # 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 + run_statement "#4.15" "$qry" 1 + + # Declined count for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 1" + run_statement "#4.16" "$qry" 1 # Delete the lease. - qry="delete from lease4 where address = 777;" - run_statement "#4.9" "$qry" + qry="delete from lease4 where address = 777" + run_statement "#4.17" "$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 + run_statement "#4.18" "$qry" 0 + + # Declined count for subnet 77 should be 0 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 1" + run_statement "#4.19" "$qry" 0 # # Next we'll verify lease6_stats are correct after migration. @@ -1974,49 +2108,85 @@ mysql_lease_stat_upgrade_test() { qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0" run_statement "#6.1" "$qry" 1 + # Assigned leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 0" + run_statement "#6.2" "$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 + run_statement "#6.3" "$qry" 1 + + # Assigned (PD) leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.4" "$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 + run_statement "#6.5" "$qry" 1 + + # Declined leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 1" + run_statement "#6.6" "$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 + run_statement "#6.7" "$qry" 2 + + # Assigned (PD) leases for subnet 50 should be 2 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.8" "$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 + qry="select count(subnet_id) from lease6_stat where state = 2" + run_statement "#6.9" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease6_pool_stat where state = 2" + run_statement "#6.10" "$qry" 0 # - # Finally we'll verify v6 trigger operation for insert,update, and delete + # 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" + qry="insert into lease6 (address, subnet_id, pool_id, lease_type, state) values (777,50,1,1,0)" + run_statement "#6.11" "$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 + run_statement "#6.12" "$qry" 3 + + # Assigned count for subnet 50 should be 2 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.13" "$qry" 2 + + # Assigned count for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 1 and state = 0" + run_statement "#6.14" "$qry" 1 # Update the state of the new lease to expired - qry="update lease6 set state = 2 where address = 777;" - run_statement "#6.7" "$qry" + qry="update lease6 set state = 2 where address = 777" + run_statement "#6.15" "$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 + run_statement "#6.16" "$qry" 2 + + # Assigned count for subnet 50 should be 0 again + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 1 and state = 0" + run_statement "#6.17" "$qry" 0 # Delete another PD lease. - qry="delete from lease6 where address = 555;" - run_statement "#6.9" "$qry" + qry="delete from lease6 where address = 555" + run_statement "#6.18" "$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 + run_statement "#6.19" "$qry" 1 + + # Assigned leases for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.20" "$qry" 1 # Let's wipe the whole database mysql_wipe @@ -2038,32 +2208,44 @@ mysql_lease_stat_recount_test() { # 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, pool_id, state) values (222,10,1,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);" + insert into lease4 (address, subnet_id, pool_id, state) values (555,77,2,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, pool_id, state) values ('::222',0,40,1,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);" + insert into lease6 (address, lease_type, subnet_id, pool_id, state) values ('::555',1,50,2,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;" + 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;" +"insert into lease4_pool_stat (subnet_id, pool_id, state, leases) values (20,3,0,1);\ + update lease4_pool_stat set leases = 5 where subnet_id = 10 and pool_id = 0 and state = 0;\ + delete from lease4_pool_stat where subnet_id = 10 and pool_id = 0 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" + + qry=\ +"insert into lease6_pool_stat (subnet_id, pool_id, lease_type, state, leases) values (20,3,1,0,1);\ + update lease6_pool_stat set leases = 5 where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 0;\ + delete from lease6_pool_stat where subnet_id = 40 and lease_type = 1 and pool_id = 0 and state = 2" run_statement "change v6 stats" "$qry" # Recount all statistics from scratch. @@ -2079,13 +2261,37 @@ mysql_lease_stat_recount_test() { qry="select leases from lease4_stat where subnet_id = 10 and state = 0" run_statement "#4.1" "$qry" 2 + # Assigned leases for subnet 10 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 0 and state = 0" + run_statement "#4.2" "$qry" 1 + + # Assigned leases for subnet 10 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 1 and state = 0" + run_statement "#4.3" "$qry" 1 + + # Declined leases for subnet 10 should be 1 + qry="select leases from lease4_stat where subnet_id = 10 and state = 1" + run_statement "#4.4" "$qry" 1 + + # Assigned leases for subnet 10 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 0 and state = 1" + run_statement "#4.5" "$qry" 1 + # 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 + run_statement "#4.6" "$qry" 1 + + # Assigned leases for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 2 and state = 0" + run_statement "#4.7" "$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 + run_statement "#4.8" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease4_pool_stat where state = 2" + run_statement "#4.9" "$qry" 0 # # Next we'll verify lease6_stats are correct after recount. @@ -2095,21 +2301,45 @@ mysql_lease_stat_recount_test() { qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0" run_statement "#6.1" "$qry" 1 + # Assigned leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 0" + run_statement "#6.2" "$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 + run_statement "#6.3" "$qry" 1 + + # Assigned (PD) leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.4" "$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 + run_statement "#6.5" "$qry" 1 + + # Declined leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 1 and state = 1" + run_statement "#6.6" "$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 + run_statement "#6.7" "$qry" 2 + + # Assigned (PD) leases for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.8" "$qry" 1 + + # Assigned (PD) leases for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 2 and state = 0" + run_statement "#6.9" "$qry" 1 # Should be no records for EXPIRED - qry="select count(subnet_id) from lease4_stat where state = 2" - run_statement "#6.5" "$qry" 0 + qry="select count(subnet_id) from lease6_stat where state = 2" + run_statement "#6.10" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease6_pool_stat where state = 2" + run_statement "#6.11" "$qry" 0 # Let's wipe the whole database mysql_wipe @@ -2144,7 +2374,7 @@ mysql_unused_subnet_id_test() { 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');" + values (0, '3123456', 4, 6, 'neither')" run_statement "insert hosts" "$qry" @@ -2153,7 +2383,7 @@ mysql_unused_subnet_id_test() { "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);" + insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (2, 0, 0)" run_statement "insert options" "$qry" @@ -2168,35 +2398,35 @@ mysql_unused_subnet_id_test() { assert_str_eq "7.0" "${version}" "Expected kea-admin to return %s, returned value was %s" # Two hosts should have null v4 subnet ids - qry="select count(host_id) from hosts where dhcp4_subnet_id is null;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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 @@ -2258,39 +2488,39 @@ mysql_reservation_mode_upgrade_test() { values (3456, '2001:db8:2::/64', current_timestamp, 2);\ insert into dhcp6_subnet (subnet_id, subnet_prefix, modification_ts, reservation_mode)\ values (4567, '2001:db8:3::/64', current_timestamp, 3);\ - set @disable_audit = 0;" + set @disable_audit = 0" run_statement "insert reservation_mode" "$sql" - qry="select count(*) from dhcp4_shared_network;" + qry="select count(*) from dhcp4_shared_network" run_statement "#get 4_shared count before update" "$qry" 4 - qry="select count(*) from dhcp4_subnet;" + qry="select count(*) from dhcp4_subnet" run_statement "#get 4_subnet count before update" "$qry" 4 - qry="select count(*) from dhcp6_shared_network;" + qry="select count(*) from dhcp6_shared_network" run_statement "#get 6_shared count before update" "$qry" 4 - qry="select count(*) from dhcp6_subnet;" + qry="select count(*) from dhcp6_subnet" run_statement "#get 6_subnet count before update" "$qry" 4 # Upgrade to schema 9.5. mysql_upgrade_schema_to_version 9.5 # Test DISABLED (0) -> false, false, null - qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test0';" + qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test0'" run_statement "#4_shared_disabled" "$qry" 1 # Test OUT_OF_POOL (1) -> false, true, true - qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and name = 'test1';" + qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and name = 'test1'" run_statement "#4_shared_out_of_pool" "$qry" 1 # Test GLOBAL (2) -> true, false, null - qry="select count(id) from dhcp4_shared_network where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test2';" + qry="select count(id) from dhcp4_shared_network where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test2'" run_statement "#4_shared_global" "$qry" 1 # Test ALL (3) -> false, true, false - qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and name = 'test3';" + qry="select count(id) from dhcp4_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and name = 'test3'" run_statement "#4_shared_all" "$qry" 1 # Test DISABLED (0) -> false, false, null @@ -2310,19 +2540,19 @@ mysql_reservation_mode_upgrade_test() { run_statement "#4_subnet_all" "$qry" 1 # Test DISABLED (0) -> false, false, null - qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test0';" + qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test0'" run_statement "#6_shared_disabled" "$qry" 1 # Test OUT_OF_POOL (1) -> false, true, true - qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and name = 'test1';" + qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = true and name = 'test1'" run_statement "#6_shared_out_of_pool" "$qry" 1 # Test GLOBAL (2) -> true, false, null - qry="select count(id) from dhcp6_shared_network where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test2';" + qry="select count(id) from dhcp6_shared_network where reservations_global = true and reservations_in_subnet = false and reservations_out_of_pool is null and name = 'test2'" run_statement "#6_shared_global" "$qry" 1 # Test ALL (3) -> false, true, false - qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and name = 'test3';" + qry="select count(id) from dhcp6_shared_network where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and name = 'test3'" run_statement "#6_shared_all" "$qry" 1 # Test DISABLED (0) -> false, false, null @@ -2341,16 +2571,16 @@ mysql_reservation_mode_upgrade_test() { qry="select count(subnet_id) from dhcp6_subnet where reservations_global = false and reservations_in_subnet = true and reservations_out_of_pool = false and subnet_prefix = '2001:db8:3::/64'" run_statement "#6_subnet_all" "$qry" 1 - qry="select count(*) from dhcp4_shared_network;" + qry="select count(*) from dhcp4_shared_network" run_statement "#get 4_shared count before update" "$qry" 4 - qry="select count(*) from dhcp4_subnet;" + qry="select count(*) from dhcp4_subnet" run_statement "#get 4_subnet count before update" "$qry" 4 - qry="select count(*) from dhcp6_shared_network;" + qry="select count(*) from dhcp6_shared_network" run_statement "#get 6_shared count before update" "$qry" 4 - qry="select count(*) from dhcp6_subnet;" + qry="select count(*) from dhcp6_subnet" run_statement "#get 6_subnet count before update" "$qry" 4 # Let's wipe the whole database @@ -2384,7 +2614,7 @@ mysql_client_class_test() { sql=\ "SET @disable_audit = 1; \ INSERT INTO ${table_prefix}_server (tag, modification_ts) VALUES ('server1', NOW()); \ - SET @disable_audit = 0;" + SET @disable_audit = 0" run_statement "insert servers" "$sql" @@ -2397,7 +2627,7 @@ mysql_client_class_test() { INSERT INTO ${table_prefix}_client_class_server (class_id, server_id) \ VALUES (@last_id, (SELECT id FROM ${table_prefix}_server WHERE tag = 'all')); \ SET @disable_audit = 0; \ - COMMIT;" + COMMIT" run_statement "insert client class foo" "$sql" # Insert client class foobar after the foo class. @@ -2409,7 +2639,7 @@ mysql_client_class_test() { INSERT INTO ${table_prefix}_client_class_server (class_id, server_id) \ VALUES (@last_id, (SELECT id FROM ${table_prefix}_server WHERE tag = 'server1')); \ SET @disable_audit = 0; \ - COMMIT;" + COMMIT" run_statement "insert client class foobar" "$sql" # Insert the client class bar at the end. This class depends on the client @@ -2424,23 +2654,23 @@ mysql_client_class_test() { INSERT INTO ${table_prefix}_client_class_dependency (class_id, dependency_id) \ VALUES (@last_id, (SELECT id FROM ${table_prefix}_client_class WHERE name = 'foo')); \ SET @disable_audit = 0; \ - COMMIT;" + COMMIT" run_statement "insert client class bar" "$sql" # Ensure that all three classes have been added in the expected order. sql="SELECT o.order_index FROM ${table_prefix}_client_class AS c \ INNER JOIN ${table_prefix}_client_class_order AS o \ - ON c.id = o.class_id WHERE c.name = 'foo'"; + ON c.id = o.class_id WHERE c.name = 'foo'" run_statement "#get order index of class foo" "$sql" 1 sql="SELECT o.order_index FROM ${table_prefix}_client_class AS c \ INNER JOIN ${table_prefix}_client_class_order AS o \ - ON c.id = o.class_id WHERE c.name = 'bar'"; + ON c.id = o.class_id WHERE c.name = 'bar'" run_statement "#get order index of class bar" "$sql" 2 sql="SELECT o.order_index FROM ${table_prefix}_client_class AS c \ INNER JOIN ${table_prefix}_client_class_order AS o \ - ON c.id = o.class_id WHERE c.name = 'foobar'"; + ON c.id = o.class_id WHERE c.name = 'foobar'" run_statement "#get order index of class foobar" "$sql" 3 # Update the class bar moving behind the foobar class. @@ -2449,31 +2679,31 @@ mysql_client_class_test() { SET @disable_audit = 1; \ UPDATE ${table_prefix}_client_class SET follow_class_name = 'foobar' WHERE name = 'bar'; \ SET @disable_audit = 0; \ - COMMIT;" + COMMIT" run_statement "update client class bar with re-positioning" "$sql" # Check that the order of the last two classes was changed. sql="SELECT o.order_index FROM ${table_prefix}_client_class AS c \ INNER JOIN ${table_prefix}_client_class_order AS o \ - ON c.id = o.class_id WHERE c.name = 'bar'"; + ON c.id = o.class_id WHERE c.name = 'bar'" run_statement "#get order index of class bar" "$sql" 4 sql="SELECT o.order_index FROM ${table_prefix}_client_class AS c \ INNER JOIN ${table_prefix}_client_class_order AS o \ - ON c.id = o.class_id WHERE c.name = 'foobar'"; + ON c.id = o.class_id WHERE c.name = 'foobar'" run_statement "#get order index of class foobar" "$sql" 3 # Check that the first class is still at the first position. sql="SELECT o.order_index FROM ${table_prefix}_client_class AS c \ INNER JOIN ${table_prefix}_client_class_order AS o \ - ON c.id = o.class_id WHERE c.name = 'foo'"; + ON c.id = o.class_id WHERE c.name = 'foo'" run_statement "#get order index of class foo" "$sql" 1 sql=\ "SET @disable_audit = 1; \ INSERT INTO ${table_prefix}_options(code, scope_id, dhcp_client_class, modification_ts) \ VALUES (222, 0, '', now()); \ - SET @disable_audit = 0;" + SET @disable_audit = 0" run_statement "add option with an empty dhcp_client class" "$sql" # Let's make sure that we can upgrade to version 12.0. This version @@ -2503,11 +2733,11 @@ mysql_shrink_server_tag_test() { # long server tags. Let's extend it back, but not to 256 characters # because it is proven to cause errors in some configurations. sql=\ -"ALTER TABLE dhcp4_server MODIFY COLUMN tag VARCHAR(128) NOT NULL;" +"ALTER TABLE dhcp4_server MODIFY COLUMN tag VARCHAR(128) NOT NULL" run_statement "extend server DHCPv4 server tag column", "$sql" sql=\ -"ALTER TABLE dhcp6_server MODIFY COLUMN tag VARCHAR(128) NOT NULL;" +"ALTER TABLE dhcp6_server MODIFY COLUMN tag VARCHAR(128) NOT NULL" run_statement "extend server DHCPv6 server tag column", "$sql" mysql_upgrade_schema_to_version 10.0 @@ -2516,13 +2746,13 @@ mysql_shrink_server_tag_test() { sql=\ "SELECT CHARACTER_MAXIMUM_LENGTH \ FROM INFORMATION_SCHEMA.COLUMNS \ - WHERE TABLE_SCHEMA='${db_name}' AND TABLE_NAME='dhcp4_server' AND COLUMN_NAME='tag';" + WHERE TABLE_SCHEMA='${db_name}' AND TABLE_NAME='dhcp4_server' AND COLUMN_NAME='tag'" run_statement "get new tag column length" "$sql" 64 sql=\ "SELECT CHARACTER_MAXIMUM_LENGTH \ FROM INFORMATION_SCHEMA.COLUMNS \ - WHERE TABLE_SCHEMA='${db_name}' AND TABLE_NAME='dhcp6_server' AND COLUMN_NAME='tag';" + WHERE TABLE_SCHEMA='${db_name}' AND TABLE_NAME='dhcp6_server' AND COLUMN_NAME='tag'" run_statement "get new tag column length" "$sql" 64 mysql_wipe @@ -2549,7 +2779,7 @@ mysql_update_empty_duid_test() { sql=\ "insert into lease6 values('::10',203,30,(SELECT FROM_UNIXTIME(1642000000)),40,50,1,60,70,1,1,'one.example.com',80,90,16,0,NULL);\ - insert into lease6 values('::11',UNHEX('00'),30,(SELECT FROM_UNIXTIME(1643210000)),40,50,1,60,70,1,1,'',80,90,1,1,'{ }');" + insert into lease6 values('::11',UNHEX('00'),30,(SELECT FROM_UNIXTIME(1643210000)),40,50,1,60,70,1,1,'',80,90,1,1,'{ }')" run_statement "insert v6 leases" "$sql" @@ -2558,11 +2788,11 @@ mysql_update_empty_duid_test() { "${kea_admin}" db-upgrade mysql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" # leases count for declined state should be 1 with DUID updated (0x000000) - qry="select count(*) from lease6 where address = '::11' and duid = 0x000000 and state = 1"; + qry="select count(*) from lease6 where address = '::11' and duid = 0x000000 and state = 1" run_statement "#2" "$qry" 1 # leases count for non declined state should be 1 with DUID unchanged (0x323033) - qry="select count(*) from lease6 where address = '::10' and duid = 0x323033 and state = 0"; + qry="select count(*) from lease6 where address = '::10' and duid = 0x323033 and state = 0" run_statement "#3" "$qry" 1 # Let's wipe the whole database diff --git a/src/bin/admin/tests/pgsql_tests.sh.in b/src/bin/admin/tests/pgsql_tests.sh.in index 8087ef57ca..9de49a7a23 100644 --- a/src/bin/admin/tests/pgsql_tests.sh.in +++ b/src/bin/admin/tests/pgsql_tests.sh.in @@ -36,10 +36,10 @@ kea_admin="@abs_top_builddir@/src/bin/admin/kea-admin" # 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);" +# 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"; +# 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 @@ -86,27 +86,27 @@ pgsql_db_init_test() { # Check schema_version table run_command \ - pgsql_execute "SELECT version, minor FROM schema_version;" + 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;" + 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;" + 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;" + 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;" + 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 @@ -154,81 +154,81 @@ pgsql_db_version_test() { pgsql_upgrade_1_0_to_2_0_test() { # Added state column to lease4 run_command \ - pgsql_execute "select state from lease4;" + 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;" + 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();" + 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();" + 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();" + 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();" + 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_test() { # Added hwaddr, hwtype, and hwaddr_source columns to lease6 table run_command \ - pgsql_execute "select hwaddr, hwtype, hwaddr_source from lease6;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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();" + 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" @@ -238,7 +238,7 @@ pgsql_upgrade_2_0_to_3_0_test() { # Added hwaddr columns to lease6DumpHeader run_command \ - pgsql_execute "select lease6DumpHeader from lease6DumpHeader();" + 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" @@ -249,7 +249,7 @@ pgsql_upgrade_2_0_to_3_0_test() { # Added hwaddr columns to lease6DumpData run_command \ - pgsql_execute "select hwaddr,hwtype,hwaddr_source from lease6DumpData();" + 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 @@ -261,7 +261,7 @@ pgsql_upgrade_2_0_to_3_0_test() { # 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';" + 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)" } @@ -269,24 +269,24 @@ pgsql_upgrade_2_0_to_3_0_test() { pgsql_upgrade_3_0_to_6_1_test() { # Added user_context to lease4 run_command \ - pgsql_execute "select user_context from lease4;" + 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;" + 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;" + 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_test() { 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);" +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" @@ -294,17 +294,15 @@ insert into hosts(dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, ipv4_a pgsql_upgrade_6_2_to_7_0_test() { # 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';" + 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';" + 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. @@ -321,7 +319,6 @@ 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" @@ -333,86 +330,86 @@ pgsql_upgrade_7_0_to_8_0_test() { # Added class_id to dhcp4_option_def run_command \ - pgsql_execute "select class_id from dhcp4_option_def;" + 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;" + 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;" + 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');" + 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('');" + 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');" + 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');" + 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');" + 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 }');" + 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();" + 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,pool_id' "${OUTPUT}" run_command \ - pgsql_execute "SELECT * FROM lease4DumpData();" --field-separator=',' + 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ˎxampleˌom,0,{ "a": 1, "b": 2 },0' "${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 }',NULL);" + 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 }',NULL)" 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();" + 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,pool_id' "${OUTPUT}" run_command \ - pgsql_execute "SELECT * FROM lease6DumpData();" --field-separator=',' + 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ˎxampleˌom,38:30,0,{ "a": 1, "b": 2 },16,0,0' "${OUTPUT}" # Check lease4Upload(). run_command \ - pgsql_execute "SELECT lease4Upload('192.0.0.0','ff0102030405','01ff0102030405',7200,1234567890,1,0,0,'',0,'',0);" + pgsql_execute "SELECT lease4Upload('192.0.0.0','ff0102030405','01ff0102030405',7200,1234567890,1,0,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,0);" + pgsql_execute "SELECT lease6Upload('2001:db8::','000100012955cb80ff0102030407',7200,1234567890,1,3600,0,1,128,0,0,'','ff0102030407',0,'',90,16,0)" assert_eq 0 "${EXIT_CODE}" 'lease6Upload() failed, expected exit code %d, actual %d' assert_str_eq '' "${OUTPUT}" } @@ -424,8 +421,7 @@ pgsql_upgrade_8_0_to_9_0_test() { # 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());" - + pgsql_execute "select gmt_epoch(now())" assert_eq 0 "${EXIT_CODE}" "function gmt_epoch() broken or missing. (expected status code %d, returned %d)" } @@ -437,7 +433,6 @@ pgsql_upgrade_9_0_to_10_test() { # 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 @@ -452,7 +447,6 @@ pgsql_upgrade_10_to_11_test() { # 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 @@ -466,7 +460,6 @@ pgsql_upgrade_11_to_12_test() { # 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 @@ -475,20 +468,19 @@ pgsql_upgrade_11_to_12_test() { # 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;" + 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;" + 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)" @@ -498,7 +490,7 @@ pgsql_upgrade_12_to_13_test() { # -- lease counting tests -- # Clean up. - query='DELETE FROM lease4; DELETE FROM lease6;' + query='DELETE FROM lease4; DELETE FROM lease6' run_command \ pgsql_execute "${query}" assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" @@ -552,7 +544,7 @@ pgsql_upgrade_12_to_13_test() { 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;" + 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" @@ -564,7 +556,7 @@ pgsql_upgrade_12_to_13_test() { # -- Verify some calls to checkLeaseXLimits(). -- - query="SELECT checkLease${v}Limits('');" + query="SELECT checkLease${v}Limits('')" run_command \ pgsql_execute "${query}" if test "${json_supported}" = 't'; then @@ -575,7 +567,7 @@ pgsql_upgrade_12_to_13_test() { fi assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" - query="SELECT checkLease${v}Limits('{}');" + query="SELECT checkLease${v}Limits('{}')" run_command \ pgsql_execute "${query}" if test "${json_supported}" = 't'; then @@ -586,7 +578,7 @@ pgsql_upgrade_12_to_13_test() { 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 } ] } } }');" + 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 @@ -598,7 +590,7 @@ pgsql_upgrade_12_to_13_test() { assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" fi - query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 1 } } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 1 } } } }')" run_command \ pgsql_execute "${query}" if test "${json_supported}" = 't'; then @@ -610,7 +602,7 @@ pgsql_upgrade_12_to_13_test() { 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 } ] } } }');" + 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 @@ -622,7 +614,7 @@ pgsql_upgrade_12_to_13_test() { assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" fi - query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 2 } } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 2 } } } }')" run_command \ pgsql_execute "${query}" if test "${json_supported}" = 't'; then @@ -634,7 +626,7 @@ pgsql_upgrade_12_to_13_test() { 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 } ] } } }');" + 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 @@ -645,7 +637,7 @@ pgsql_upgrade_12_to_13_test() { fi assert_str_eq '' "${OUTPUT}" "${query}: expected output %s, returned %s" - query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 4 } } } }');" + query="SELECT checkLease${v}Limits('{ \"ISC\": { \"limits\": { \"subnet\": { \"id\": 1, \"address-limit\": 4 } } } }')" run_command \ pgsql_execute "${query}" if test "${json_supported}" = 't'; then @@ -656,7 +648,7 @@ pgsql_upgrade_12_to_13_test() { 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 } } } }');" + 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 @@ -668,7 +660,7 @@ pgsql_upgrade_12_to_13_test() { 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 } } } }');" + 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 @@ -680,7 +672,7 @@ pgsql_upgrade_12_to_13_test() { 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 } } } }');" + 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 @@ -692,7 +684,7 @@ pgsql_upgrade_12_to_13_test() { 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 } } } }');" + 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 @@ -765,28 +757,28 @@ pgsql_upgrade_13_to_14_test() { # Added cancelled column to dhcp4_options run_command \ - pgsql_execute "select cancelled from dhcp4_options;" + pgsql_execute "select cancelled from dhcp4_options" assert_eq 0 "${EXIT_CODE}" "dhcp4_options is missing cancelled column. (expected status code %d, returned %d)" # Added cancelled column to dhcp6_options run_command \ - pgsql_execute "select cancelled from dhcp6_options;" + pgsql_execute "select cancelled from dhcp6_options" assert_eq 0 "${EXIT_CODE}" "dhcp6_options is missing cancelled column. (expected status code %d, returned %d)" # Check if offer_lifetime was added to dhcp4_shared_network table. - qry="SELECT offer_lifetime from dhcp4_shared_network limit 1;" + qry="SELECT offer_lifetime from dhcp4_shared_network limit 1" run_command \ pgsql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if offer_lifetime was added to dhcp4_subnet table. - qry="SELECT offer_lifetime from dhcp4_subnet limit 1;" + qry="SELECT offer_lifetime from dhcp4_subnet limit 1" run_command \ pgsql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" # Check if offer_lifetime was added to dhcp4_client_class table. - qry="SELECT offer_lifetime from dhcp4_client_class limit 1;" + qry="SELECT offer_lifetime from dhcp4_client_class limit 1" run_command \ pgsql_execute "${qry}" assert_eq 0 "${EXIT_CODE}" "${qry}. (expected status code %d, returned %d)" @@ -795,62 +787,72 @@ pgsql_upgrade_13_to_14_test() { pgsql_upgrade_14_to_15_test() { # Added relay_id column to lease4 run_command \ - pgsql_execute "select relay_id from lease4;" + pgsql_execute "select relay_id from lease4" assert_eq 0 "${EXIT_CODE}" "lease4 is missing relay_id column. (expected status code %d, returned %d)" # Added remote_id column to lease4 run_command \ - pgsql_execute "select remote_id from lease4;" + pgsql_execute "select remote_id from lease4" assert_eq 0 "${EXIT_CODE}" "lease4 is missing remote_id column. (expected status code %d, returned %d)" } pgsql_upgrade_15_to_16_test() { # Added allocator column to dhcp4_shared_network run_command \ - pgsql_execute "select allocator from dhcp4_shared_network;" + pgsql_execute "select allocator from dhcp4_shared_network" assert_eq 0 "${EXIT_CODE}" "dhcp4_shared_network is missing allocator column. (expected status code %d, returned %d)" # Added allocator column to dhcp6_shared_network run_command \ - pgsql_execute "select allocator from dhcp6_shared_network;" + pgsql_execute "select allocator from dhcp6_shared_network" assert_eq 0 "${EXIT_CODE}" "dhcp6_shared_network is missing allocator column. (expected status code %d, returned %d)" # Added pd_allocator column to dhcp6_shared_network run_command \ - pgsql_execute "select pd_allocator from dhcp6_shared_network;" + pgsql_execute "select pd_allocator from dhcp6_shared_network" assert_eq 0 "${EXIT_CODE}" "dhcp6_shared_network is missing pd_allocator column. (expected status code %d, returned %d)" # Added allocator column to dhcp4_subnet run_command \ - pgsql_execute "select allocator from dhcp4_subnet;" + pgsql_execute "select allocator from dhcp4_subnet" assert_eq 0 "${EXIT_CODE}" "dhcp4_subnet is missing allocator column. (expected status code %d, returned %d)" # Added allocator column to dhcp6_subnet run_command \ - pgsql_execute "select allocator from dhcp6_subnet;" + pgsql_execute "select allocator from dhcp6_subnet" assert_eq 0 "${EXIT_CODE}" "dhcp6_subnet is missing allocator column. (expected status code %d, returned %d)" # Added pd_allocator column to dhcp6_subnet run_command \ - pgsql_execute "select pd_allocator from dhcp6_subnet;" + pgsql_execute "select pd_allocator from dhcp6_subnet" assert_eq 0 "${EXIT_CODE}" "dhcp6_subnet is missing pd_allocator column. (expected status code %d, returned %d)" } pgsql_upgrade_16_to_17_test() { # Added binaddr column to lease6 run_command \ - pgsql_execute "select binaddr from lease6;" + pgsql_execute "select binaddr from lease6" assert_eq 0 "${EXIT_CODE}" "lease6 is missing binaddr column. (expected status code %d, returned %d)" # Added lease6_relay_id table run_command \ - pgsql_execute "select extended_info_id, relay_id, lease_addr from lease6_relay_id;" + pgsql_execute "select extended_info_id, relay_id, lease_addr from lease6_relay_id" assert_eq 0 "${EXIT_CODE}" "lease6_relay_id table is missing or broken. (expected status code %d, returned %d)" # Added lease6_remote_id table run_command \ - pgsql_execute "select extended_info_id, remote_id, lease_addr from lease6_remote_id;" + pgsql_execute "select extended_info_id, remote_id, lease_addr from lease6_remote_id" assert_eq 0 "${EXIT_CODE}" "lease6_remote_id table is missing or broken. (expected status code %d, returned %d)" + + # Added lease4_pool_stat table + run_command \ + pgsql_execute "SELECT subnet_id, pool_id, state, leases FROM lease4_pool_stat" + assert_eq 0 "${EXIT_CODE}" "lease4_pool_stat table is missing or broken. (expected status code %d, returned %d)" + + # Added lease6_pool_stat table + run_command \ + pgsql_execute "SELECT subnet_id, pool_id, lease_type, state, leases FROM lease6_pool_stat" + assert_eq 0 "${EXIT_CODE}" "lease6_pool_stat table is missing or broken. (expected status code %d, returned %d)" } pgsql_upgrade_test() { @@ -972,7 +974,7 @@ pgsql_lease4_dump_test() { 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\" }');" +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" @@ -1046,7 +1048,7 @@ pgsql_lease6_dump_test() { insert_sql="\ insert into lease6 values('::10',E'\\\\x323033',30,TO_TIMESTAMP(1642000000),40,50,1,60,70,'t','t','one.example.com',0,decode(encode('80','hex'),'hex'),90,16,'',NULL); \ insert into lease6 values('::11',E'\\\\x323133',30,TO_TIMESTAMP(1643210000),40,50,1,60,70,'t','t','',1,decode(encode('80','hex'),'hex'),90,1,'{ }',NULL); \ -insert into lease6 values('::12',E'\\\\x323233',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\" }',NULL);" +insert into lease6 values('::12',E'\\\\x323233',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\" }',NULL)" run_command \ pgsql_execute "$insert_sql" @@ -1289,25 +1291,69 @@ pgsql_lease4_stat_test() { qry="select leases from lease4_stat where subnet_id = 1 and state = 0" run_statement "#3" "$qry" 1 + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 0" + run_statement "#4" "$qry" 1 + # Set lease state to declined qry="update lease4 set state = 1 where address = 111" - run_statement "#4" "$qry" + run_statement "#5" "$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 + run_statement "#6" "$qry" 0 + + # Leases state count for assigned should be 0 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 0" + run_statement "#7" "$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 + run_statement "#8" "$qry" 1 + + # Leases state count for declined should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 1" + run_statement "#9" "$qry" 1 # Delete the lease qry="delete from lease4 where address = 111" - run_statement "#7" "$qry" + run_statement "#10" "$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 + run_statement "#11" "$qry" 0 + + # Leases state count for declined should be 0 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 0 and state = 1" + run_statement "#12" "$qry" 0 + + # Insert lease4 + qry="insert into lease4 (address, subnet_id, pool_id, state) values (112,1,1,0)" + run_statement "#13" "$qry" + + # Assigned state count should be 1 + qry="select leases from lease4_stat where subnet_id = 1 and state = 0" + run_statement "#14" "$qry" 1 + + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 1 and state = 0" + run_statement "#15" "$qry" 1 + + # Insert lease4 + qry="insert into lease4 (address, subnet_id, pool_id, state) values (113,1,2,0)" + run_statement "#16" "$qry" + + # Assigned state count should be 2 + qry="select leases from lease4_stat where subnet_id = 1 and state = 0" + run_statement "#17" "$qry" 2 + + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 1 and state = 0" + run_statement "#18" "$qry" 1 + + # Assigned state count should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 1 and pool_id = 2 and state = 0" + run_statement "#19" "$qry" 1 # Let's wipe the whole database pgsql_wipe @@ -1324,35 +1370,81 @@ pgsql_lease4_stat_test() { # param ltype - type of lease to create pgsql_lease6_stat_per_type() { addr=$1;shift + addr1=$1;shift + addr2=$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);" + 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"; + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0" run_statement "#3" "$qry" 1 + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 0" + run_statement "#4" "$qry" 1 + # update the lease, changing state to declined qry="update lease6 set state = 1 where address = '$addr'" - run_statement "#4" "$qry" + run_statement "#5" "$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 "#6" "$qry" 0 # 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 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 0" + run_statement "#7" "$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 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 1" + run_statement "#8" "$qry" 1 + + # leases count for declined state should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 1" + run_statement "#9" "$qry" 1 # delete the lease - qry="delete from lease6 where address = '$addr';" - run_statement "#7" "$qry" + qry="delete from lease6 where address = '$addr'" + run_statement "#10" "$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 "#11" "$qry" 0 # 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 "#8" "$qry" 0 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 0 and state = 0" + run_statement "#12" "$qry" 0 + + # insert a lease6 for addr and ltype, state assigned + qry="insert into lease6 (address, lease_type, subnet_id, pool_id, state) values ('$addr1',$ltype,1,1,0)" + run_statement "#13" "$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 "#14" "$qry" 1 + + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 1 and state = 0" + run_statement "#15" "$qry" 1 + + # insert a lease6 for addr and ltype, state assigned + qry="insert into lease6 (address, lease_type, subnet_id, pool_id, state) values ('$addr2',$ltype,1,2,0)" + run_statement "#16" "$qry" + + # assigned stat should be 2 + qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0" + run_statement "#17" "$qry" 2 + + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 1 and state = 0" + run_statement "#18" "$qry" 1 + + # assigned stat should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 1 and lease_type = $ltype and pool_id = 2 and state = 0" + run_statement "#19" "$qry" 1 } # Verifies that lease6_stat triggers operation correctly @@ -1374,10 +1466,10 @@ pgsql_lease6_stat_test() { run_statement "#1" "$qry" # Test for address 111, NA lease type - pgsql_lease6_stat_per_type "111" "0" + pgsql_lease6_stat_per_type "111" "112" "113" "0" # Test for address 222, PD lease type - pgsql_lease6_stat_per_type "222" "1" + pgsql_lease6_stat_per_type "222" "223" "224" "1" # Let's wipe the whole database pgsql_wipe @@ -1411,7 +1503,7 @@ pgsql_lease_stat_upgrade_test() { 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);" + insert into lease4 (address, subnet_id, state) values (555,77,0)" run_statement "insert v4 leases" "$qry" qry=\ @@ -1420,7 +1512,7 @@ pgsql_lease_stat_upgrade_test() { 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);" + insert into lease6 (address, lease_type, subnet_id, state) values (666,1,40,2)" run_statement "insert v6 leases" "$qry" # Let's upgrade it to the latest version. @@ -1435,45 +1527,81 @@ pgsql_lease_stat_upgrade_test() { qry="select leases from lease4_stat where subnet_id = 10 and state = 0" run_statement "#4.1" "$qry" 2 + # Assigned leases for subnet 10 should be 2 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 0 and state = 0" + run_statement "#4.2" "$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 + run_statement "#4.3" "$qry" 1 + + # Assigned leases for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 0 and state = 0" + run_statement "#4.4" "$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 + run_statement "#4.5" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease4_pool_stat where state = 2" + run_statement "#4.6" "$qry" 0 # - # Now we'll verify v4 trigger operation for insert,update, and delete + # 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" + qry="insert into lease4 (address, subnet_id, pool_id, state) values (777,77,1,0)" + run_statement "#4.7" "$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 + run_statement "#4.8" "$qry" 2 + + # Assigned count for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 0 and state = 0" + run_statement "#4.9" "$qry" 1 + + # Assigned count for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 0" + run_statement "#4.10" "$qry" 1 # Update the state of the new lease to declined - qry="update lease4 set state = 1 where address = 777;" - run_statement "#4.6" "$qry" + qry="update lease4 set state = 1 where address = 777" + run_statement "#4.11" "$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 + run_statement "#4.12" "$qry" 1 + + # Assigned count for subnet 77 should be 1 again + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 0 and state = 0" + run_statement "#4.13" "$qry" 1 + + # Assigned count for subnet 77 should be 0 again + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 0" + run_statement "#4.14" "$qry" 0 # 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 + run_statement "#4.15" "$qry" 1 + + # Declined count for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 1" + run_statement "#4.16" "$qry" 1 # Delete the lease. - qry="delete from lease4 where address = 777;" - run_statement "#4.9" "$qry" + qry="delete from lease4 where address = 777" + run_statement "#4.17" "$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 + run_statement "#4.18" "$qry" 0 + + # Declined count for subnet 77 should be 0 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 1 and state = 1" + run_statement "#4.19" "$qry" 0 # # Next we'll verify lease6_stats are correct after migration. @@ -1483,52 +1611,90 @@ pgsql_lease_stat_upgrade_test() { qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0" run_statement "#6.1" "$qry" 1 + # Assigned leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 0" + run_statement "#6.2" "$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 + run_statement "#6.3" "$qry" 1 + + # Assigned (PD) leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.4" "$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 + run_statement "#6.5" "$qry" 1 + + # Declined leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 1" + run_statement "#6.6" "$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 + run_statement "#6.7" "$qry" 2 + + # Assigned (PD) leases for subnet 50 should be 2 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.8" "$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 + qry="select count(subnet_id) from lease6_stat where state = 2" + run_statement "#6.9" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease6_pool_stat where state = 2" + run_statement "#6.10" "$qry" 0 # - # Finally we'll verify v6 trigger operation for insert,update, and delete + # 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" + qry="insert into lease6 (address, subnet_id, pool_id, lease_type, state) values ('777',50,1,1,0)" + run_statement "#6.11" "$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 + run_statement "#6.12" "$qry" 3 + + # Assigned count for subnet 50 should be 2 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.13" "$qry" 2 + + # Assigned count for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 1 and state = 0" + run_statement "#6.14" "$qry" 1 # Update the state of the new lease to expired - qry="update lease6 set state = 2 where address = '777';" - run_statement "#6.7" "$qry" + qry="update lease6 set state = 2 where address = '777'" + run_statement "#6.15" "$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 + run_statement "#6.16" "$qry" 2 + + # Assigned count for subnet 50 should be 0 again + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 1 and state = 0" + run_statement "#6.17" "$qry" 0 # Delete another PD lease. qry="delete from lease6 where address = '555'" - run_statement "#6.9" "$qry" + run_statement "#6.18" "$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 + run_statement "#6.19" "$qry" 1 + + # Assigned leases for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.20" "$qry" 1 # Let's wipe the whole database pgsql_wipe + + test_finish 0 } pgsql_lease_stat_recount_test() { @@ -1540,44 +1706,54 @@ pgsql_lease_stat_recount_test() { # 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, pool_id, state) values (222,10,1,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);" + insert into lease4 (address, subnet_id, pool_id, state) values (555,77,2,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, pool_id, state) values (222,0,40,1,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);" + insert into lease6 (address, lease_type, subnet_id, pool_id, state) values (555,1,50,2,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;" + 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;" +"insert into lease4_pool_stat (subnet_id, pool_id, state, leases) values (20,3,0,1);\ + update lease4_pool_stat set leases = 5 where subnet_id = 10 and pool_id = 0 and state = 0;\ + delete from lease4_pool_stat where subnet_id = 10 and pool_id = 0 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" + + qry=\ +"insert into lease6_pool_stat (subnet_id, pool_id, lease_type, state, leases) values (20,3,1,0,1);\ + update lease6_pool_stat set leases = 5 where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 0;\ + delete from lease6_pool_stat where subnet_id = 40 and lease_type = 1 and pool_id = 0 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" # @@ -1588,13 +1764,37 @@ pgsql_lease_stat_recount_test() { qry="select leases from lease4_stat where subnet_id = 10 and state = 0" run_statement "#4.1" "$qry" 2 + # Assigned leases for subnet 10 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 0 and state = 0" + run_statement "#4.2" "$qry" 1 + + # Assigned leases for subnet 10 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 1 and state = 0" + run_statement "#4.3" "$qry" 1 + + # Declined leases for subnet 10 should be 1 + qry="select leases from lease4_stat where subnet_id = 10 and state = 1" + run_statement "#4.4" "$qry" 1 + + # Declined leases for subnet 10 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 10 and pool_id = 0 and state = 0" + run_statement "#4.5" "$qry" 1 + # 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 + run_statement "#4.6" "$qry" 1 + + # Assigned leases for subnet 77 should be 1 + qry="select leases from lease4_pool_stat where subnet_id = 77 and pool_id = 2 and state = 0" + run_statement "#4.7" "$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 + run_statement "#4.8" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease4_pool_stat where state = 2" + run_statement "#4.9" "$qry" 0 # # Next we'll verify lease6_stats are correct after recount. @@ -1604,21 +1804,45 @@ pgsql_lease_stat_recount_test() { qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0" run_statement "#6.1" "$qry" 1 + # Assigned leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 0 and state = 0" + run_statement "#6.2" "$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 + run_statement "#6.3" "$qry" 1 + + # Assigned (PD) leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.4" "$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 + run_statement "#6.5" "$qry" 1 + + # Declined leases for subnet 40 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 40 and lease_type = 0 and pool_id = 1 and state = 1" + run_statement "#6.6" "$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 + run_statement "#6.7" "$qry" 2 + + # Assigned (PD) leases for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 0 and state = 0" + run_statement "#6.8" "$qry" 1 + + # Assigned (PD) leases for subnet 50 should be 1 + qry="select leases from lease6_pool_stat where subnet_id = 50 and lease_type = 1 and pool_id = 2 and state = 0" + run_statement "#6.9" "$qry" 1 # Should be no records for EXPIRED - qry="select count(subnet_id) from lease4_stat where state = 2" - run_statement "#6.5" "$qry" 0 + qry="select count(subnet_id) from lease6_stat where state = 2" + run_statement "#6.10" "$qry" 0 + + # Should be no records for EXPIRED + qry="select count(subnet_id) from lease6_pool_stat where state = 2" + run_statement "#6.11" "$qry" 0 # Let's wipe the whole database pgsql_wipe @@ -1626,7 +1850,7 @@ pgsql_lease_stat_recount_test() { test_finish 0 } -# Verifies that you can upgrade from earlier version and +# Verifies that you can upgrade from an earlier version and # that unused subnet ID values in hosts and options tables are # converted to NULL. pgsql_unused_subnet_id_test() { @@ -1655,7 +1879,7 @@ pgsql_unused_subnet_id_test() { 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');" + values (0, '3123456', 4, 6, 'neither')" run_statement "insert hosts" "$qry" @@ -1664,7 +1888,7 @@ pgsql_unused_subnet_id_test() { "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);" + insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (2, 0, 0)" run_statement "insert options" "$qry" @@ -1676,35 +1900,35 @@ pgsql_unused_subnet_id_test() { 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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;" + 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 @@ -1734,7 +1958,7 @@ pgsql_update_empty_duid_test() { qry=\ "insert into lease6 values('::10',E'\\\\x323033',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'\\\\x00',30,TO_TIMESTAMP(1643210000),40,50,1,60,70,'t','t','',1,decode(encode('80','hex'),'hex'),90,1,'{ }');" + insert into lease6 values('::11',E'\\\\x00',30,TO_TIMESTAMP(1643210000),40,50,1,60,70,'t','t','',1,decode(encode('80','hex'),'hex'),90,1,'{ }')" run_statement "insert v6 leases" "$qry" @@ -1743,11 +1967,11 @@ pgsql_update_empty_duid_test() { "${kea_admin}" db-upgrade pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}" # leases count for declined state should be 1 with DUID updated (0x000000) - qry="select count(*) from lease6 where address = '::11' and duid = E'\\\\x000000' and state = 1"; + qry="select count(*) from lease6 where address = '::11' and duid = E'\\\\x000000' and state = 1" run_statement "#2" "$qry" 1 # leases count for non declined state should be 1 with DUID unchanged (0x323033) - qry="select count(*) from lease6 where address = '::10' and duid = E'\\\\x323033' and state = 0"; + qry="select count(*) from lease6 where address = '::10' and duid = E'\\\\x323033' and state = 0" run_statement "#3" "$qry" 1 # Let's wipe the whole database diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql index 73963c1136..666e61ba82 100644 --- a/src/share/database/scripts/mysql/dhcpdb_create.mysql +++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql @@ -5775,6 +5775,14 @@ BEGIN END $$ DELIMITER ; +INSERT INTO lease4_pool_stat (subnet_id, pool_id, state, leases) + SELECT subnet_id, pool_id, state, count(*) FROM lease4 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, state; + +INSERT INTO lease6_pool_stat (subnet_id, pool_id, lease_type, state, leases) + SELECT subnet_id, pool_id, lease_type, state, count(*) FROM lease6 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, lease_type, state; + -- Update the schema version number. UPDATE schema_version SET version = '18', minor = '0'; diff --git a/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in b/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in index 94cbe8c7e9..e5f8a31cad 100644 --- a/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in +++ b/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in @@ -663,6 +663,14 @@ BEGIN END $$ DELIMITER ; +INSERT INTO lease4_pool_stat (subnet_id, pool_id, state, leases) + SELECT subnet_id, pool_id, state, count(*) FROM lease4 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, state; + +INSERT INTO lease6_pool_stat (subnet_id, pool_id, lease_type, state, leases) + SELECT subnet_id, pool_id, lease_type, state, count(*) FROM lease6 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, lease_type, state; + -- Update the schema version number. UPDATE schema_version SET version = '18', minor = '0'; diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql index 1a1ec74a3f..a65b772865 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql @@ -6122,6 +6122,14 @@ BEGIN END $$ LANGUAGE plpgsql; +INSERT INTO lease4_pool_stat (subnet_id, pool_id, state, leases) + SELECT subnet_id, pool_id, state, count(*) FROM lease4 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, state; + +INSERT INTO lease6_pool_stat (subnet_id, pool_id, lease_type, state, leases) + SELECT subnet_id, pool_id, lease_type, state, count(*) FROM lease6 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, lease_type, state; + -- Update the schema version number. UPDATE schema_version SET version = '17', minor = '0'; diff --git a/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in b/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in index 4300285390..3009959398 100644 --- a/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in +++ b/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in @@ -525,6 +525,14 @@ BEGIN END \$\$ LANGUAGE plpgsql; +INSERT INTO lease4_pool_stat (subnet_id, pool_id, state, leases) + SELECT subnet_id, pool_id, state, count(*) FROM lease4 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, state; + +INSERT INTO lease6_pool_stat (subnet_id, pool_id, lease_type, state, leases) + SELECT subnet_id, pool_id, lease_type, state, count(*) FROM lease6 + WHERE state = 0 OR state = 1 GROUP BY subnet_id, pool_id, lease_type, state; + -- Update the schema version number. UPDATE schema_version SET version = '17', minor = '0'; -- 2.47.2