From 571fa79782aca3435c44fcc28f66843532ce502b Mon Sep 17 00:00:00 2001 From: Francis Dupont Date: Wed, 20 May 2020 17:06:26 +0200 Subject: [PATCH] [#1196] Checkpoint: fixed pgsql --- configure.ac | 1 + src/bin/admin/tests/pgsql_tests.sh.in | 14 +- src/lib/pgsql/pgsql_connection.h | 4 +- src/share/database/scripts/pgsql/.gitignore | 1 + src/share/database/scripts/pgsql/Makefile.am | 2 + .../scripts/pgsql/dhcpdb_create.pgsql | 105 ++++++++++++++- .../scripts/pgsql/upgrade_6.0_to_6.1.sh.in | 125 ++++++++++++++++++ 7 files changed, 242 insertions(+), 10 deletions(-) create mode 100644 src/share/database/scripts/pgsql/upgrade_6.0_to_6.1.sh.in diff --git a/configure.ac b/configure.ac index 8749dda704..1b5f790ee9 100755 --- a/configure.ac +++ b/configure.ac @@ -1740,6 +1740,7 @@ AC_CONFIG_FILES([Makefile src/share/database/scripts/pgsql/upgrade_4.0_to_5.0.sh src/share/database/scripts/pgsql/upgrade_5.0_to_5.1.sh src/share/database/scripts/pgsql/upgrade_5.1_to_6.0.sh + src/share/database/scripts/pgsql/upgrade_6.0_to_6.1.sh src/share/database/scripts/pgsql/wipe_data.sh src/share/yang/Makefile src/share/yang/modules/Makefile diff --git a/src/bin/admin/tests/pgsql_tests.sh.in b/src/bin/admin/tests/pgsql_tests.sh.in index 43802cbaed..0a1dbc9474 100644 --- a/src/bin/admin/tests/pgsql_tests.sh.in +++ b/src/bin/admin/tests/pgsql_tests.sh.in @@ -1,6 +1,6 @@ #!/bin/sh -# Copyright (C) 2015-2019 Internet Systems Consortium, Inc. ("ISC") +# Copyright (C) 2015-2020 Internet Systems Consortium, Inc. ("ISC") # # This Source Code Form is subject to the terms of the Mozilla Public # License, v. 2.0. If a copy of the MPL was not distributed with this @@ -120,7 +120,7 @@ pgsql_db_version_test() { # Verify that kea-admin db-version returns the correct version version=$(${keaadmin} db-version pgsql -u $db_user -p $db_password -n $db_name) - assert_str_eq "6.0" ${version} "Expected kea-admin to return %s, returned value was %s" + assert_str_eq "6.1" ${version} "Expected kea-admin to return %s, returned value was %s" # Let's wipe the whole database pgsql_wipe @@ -230,10 +230,10 @@ pgsql_upgrade_2_0_to_3_0() { assert_eq 1 "$output" "lease_hwaddr_source does not contain entry for HWADDR_SOURCE_UNKNOWN. (record count %d, expected %d)" } -pgsql_upgrade_3_0_to_6_0() { - # Verify upgraded schema reports version 6.0. +pgsql_upgrade_3_0_to_6_1() { + # Verify upgraded schema reports version 6.1. version=$(${keaadmin} db-version pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir) - assert_str_eq "6.0" ${version} "Expected kea-admin to return %s, returned value was %s" + assert_str_eq "6.1" ${version} "Expected kea-admin to return %s, returned value was %s" # Added user_context to lease4 output=`pgsql_execute "select user_context from lease4;"` @@ -270,8 +270,8 @@ pgsql_upgrade_test() { # Check 2.0 to 3.0 upgrade pgsql_upgrade_2_0_to_3_0 - # Check 3.0 to 6.0 upgrade - pgsql_upgrade_3_0_to_6_0 + # Check 3.0 to 6.1 upgrade + pgsql_upgrade_3_0_to_6_1 # Let's wipe the whole database pgsql_wipe diff --git a/src/lib/pgsql/pgsql_connection.h b/src/lib/pgsql/pgsql_connection.h index d2ff62bf3c..b02346eb81 100644 --- a/src/lib/pgsql/pgsql_connection.h +++ b/src/lib/pgsql/pgsql_connection.h @@ -17,9 +17,9 @@ namespace isc { namespace db { -/// @brief Define PostgreSQL backend version: 6.0 +/// @brief Define PostgreSQL backend version: 6.1 const uint32_t PG_SCHEMA_VERSION_MAJOR = 6; -const uint32_t PG_SCHEMA_VERSION_MINOR = 0; +const uint32_t PG_SCHEMA_VERSION_MINOR = 1; // Maximum number of parameters that can be used a statement // @todo This allows us to use an initializer list (since we can't diff --git a/src/share/database/scripts/pgsql/.gitignore b/src/share/database/scripts/pgsql/.gitignore index 7b8fa70b4f..e9fd4a794a 100644 --- a/src/share/database/scripts/pgsql/.gitignore +++ b/src/share/database/scripts/pgsql/.gitignore @@ -7,4 +7,5 @@ /upgrade_4.0_to_5.0.sh /upgrade_5.0_to_5.1.sh /upgrade_5.1_to_6.0.sh +/upgrade_6.0_to_6.1.sh /wipe_data.sh diff --git a/src/share/database/scripts/pgsql/Makefile.am b/src/share/database/scripts/pgsql/Makefile.am index 60c03bfa2a..6fc0376ea7 100644 --- a/src/share/database/scripts/pgsql/Makefile.am +++ b/src/share/database/scripts/pgsql/Makefile.am @@ -12,6 +12,7 @@ sqlscripts_DATA += upgrade_3.3_to_4.0.sh sqlscripts_DATA += upgrade_4.0_to_5.0.sh sqlscripts_DATA += upgrade_5.0_to_5.1.sh sqlscripts_DATA += upgrade_5.1_to_6.0.sh +sqlscripts_DATA += upgrade_6.0_to_6.1.sh sqlscripts_DATA += wipe_data.sh DISTCLEANFILES = upgrade_1.0_to_2.0.sh @@ -23,6 +24,7 @@ DISTCLEANFILES += upgrade_3.3_to_4.0.sh DISTCLEANFILES += upgrade_4.0_to_5.0.sh DISTCLEANFILES += upgrade_5.0_to_5.1.sh DISTCLEANFILES += upgrade_5.1_to_6.0.sh +DISTCLEANFILES += upgrade_6.0_to_6.1.sh DISTCLEANFILES += wipe_data.sh EXTRA_DIST = ${sqlscripts_DATA} diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql index c22d9b71b5..ea4595978c 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql @@ -907,7 +907,110 @@ WHERE hostname IS NOT NULL; UPDATE schema_version SET version = '6', minor = '0'; --- Schema 5.1a specification ends here. +-- Schema 6.0 specification ends here. + +-- Commit the script transaction +COMMIT; + +-- Upgrade to schema 6.1 begins here: + +START TRANSACTION; + +-- Fix v4 update trigger procedure +CREATE OR REPLACE FUNCTION proc_stat_lease4_update () RETURNS trigger AS $stat_lease4_update$ +BEGIN + IF OLD.subnet_id != NEW.subnet_id OR OLD.state != NEW.state THEN + IF OLD.state < 2 THEN + -- Decrement the old state count if record exists + UPDATE lease4_stat SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = OLD.subnet_id AND state = OLD.state; + END IF; + + IF NEW.state < 2 THEN + -- Increment the new state count if record exists + UPDATE lease4_stat SET leases = leases + 1 + WHERE subnet_id = NEW.subnet_id AND state = NEW.state; + + -- Insert new state record if it does not exist + IF NOT FOUND THEN + INSERT INTO lease4_stat VALUES (NEW.subnet_id, NEW.state, 1); + END IF; + END IF; + END IF; + + -- Return is ignored since this is an after insert + RETURN NULL; +END; +$stat_lease4_update$ LANGUAGE plpgsql; + +-- +-- Fix the v4 delete trigger procedure +CREATE OR REPLACE FUNCTION proc_stat_lease4_delete () RETURNS trigger AS $stat_lease4_delete$ +BEGIN + IF OLD.state < 2 THEN + -- Decrement the state count if record exists + UPDATE lease4_stat SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = OLD.subnet_id AND OLD.state = state; + END IF; + + -- Return is ignored since this is an after insert + RETURN NULL; +END; +$stat_lease4_delete$ LANGUAGE plpgsql; + +-- +-- Fix v6 update trigger procedure +CREATE OR REPLACE FUNCTION proc_stat_lease6_update () RETURNS trigger AS $stat_lease6_update$ +BEGIN + IF OLD.subnet_id != NEW.subnet_id OR + OLD.lease_type != NEW.lease_type OR + OLD.state != NEW.state THEN + IF OLD.state < 2 THEN + -- Decrement the old state count if record exists + UPDATE lease6_stat SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = OLD.subnet_id AND lease_type = OLD.lease_type + AND state = OLD.state; + END IF; + + IF NEW.state < 2 THEN + -- Increment the new state count if record exists + UPDATE lease6_stat SET leases = leases + 1 + WHERE subnet_id = NEW.subnet_id AND lease_type = NEW.lease_type + AND state = NEW.state; + + -- Insert new state record if it does not exist + IF NOT FOUND THEN + INSERT INTO lease6_stat VALUES (NEW.subnet_id, NEW.lease_type, NEW.state, 1); + END IF; + END IF; + END IF; + + -- Return is ignored since this is an after insert + RETURN NULL; +END; +$stat_lease6_update$ LANGUAGE plpgsql; + +-- +-- Fix the v6 delete trigger procedure +CREATE OR REPLACE FUNCTION proc_stat_lease6_delete() RETURNS trigger AS $stat_lease6_delete$ +BEGIN + IF OLD.state < 2 THEN + -- Decrement the state count if record exists + UPDATE lease6_stat SET leases = GREATEST(leases - 1, 0) + WHERE subnet_id = OLD.subnet_id AND lease_type = OLD.lease_type + AND OLD.state = state; + END IF; + + -- Return is ignored since this is an after insert + RETURN NULL; +END; +$stat_lease6_delete$ LANGUAGE plpgsql; + +-- Set 6.1 schema version. +UPDATE schema_version + SET version = '6', minor = '1'; + +-- Schema 6.1 specification ends here. -- Commit the script transaction COMMIT; diff --git a/src/share/database/scripts/pgsql/upgrade_6.0_to_6.1.sh.in b/src/share/database/scripts/pgsql/upgrade_6.0_to_6.1.sh.in new file mode 100644 index 0000000000..69d2125b99 --- /dev/null +++ b/src/share/database/scripts/pgsql/upgrade_6.0_to_6.1.sh.in @@ -0,0 +1,125 @@ +#!/bin/sh + +prefix=@prefix@ +# Include utilities. Use installed version if available and +# use build version if it isn't. +if [ -e @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh ]; then + . @datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh +else + . @abs_top_builddir@/src/bin/admin/admin-utils.sh +fi + +VERSION=`pgsql_version "$@"` + +if [ "$VERSION" != "6.0" ]; then + printf "This script upgrades 6.0 to 6.1. Reported version is $VERSION. Skipping upgrade.\n" + exit 0 +fi + +psql "$@" >/dev/null <