From a7db35543a95bb62bde70d8d23bffa00edd0cd78 Mon Sep 17 00:00:00 2001 From: Thomas Markwalder Date: Wed, 15 Jun 2016 10:52:14 -0400 Subject: [PATCH] [4275] Postgresql schema 3.0 now matches MySql schema 4.2 Added 4.1 to 4.2 updates from MySQL and created upgrade script for 2.0 to 3.0 src/share/database/scripts/pgsql/dhcpdb_create.pgsql - Enclosed the entire script in a single transaction - Removed DROP TABLE statements - Added host_identifier_type table and data - Added dhcp_option_scope table and data - Updated unqiue constraints for hosts table - Added scope_id and foreign key constraint to dhcp4_options table - Added scope_id and foreign key constraint to dhcp6_options table - Added unique contraint to ipv6_reservations table - Changed 'HWADDR_SOURCE_DOCSIS' to 'HWADDR_SOURCE_DOCSIS_CMTS' - Inserted row for 'HWADDR_SOURCE_UKNOWN' src/share/database/scripts/pgsql/dhcpdb_drop.pgsql Added drops for host_identifier_type and dhcp_option_scope src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh.in New file for upgrading Postgresql from 2.0 to 3.0 configure.ac Added src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh src/bin/admin/tests/pgsql_tests.sh.in - pgsql_upgrade_1_0_to_2_0 - new function which contains all the checks used to verify 1.0 to 2.0 upgrade (extracted from pgsql_upgrade_test) - pgsql_upgrade_2_0_to_3_0 - new function which contains all the checks used to verify 2.0 to 3.0 upgrade - pgsql_upgrade_test() - modified use new upgrade check fucntions src/share/database/scripts/pgsql/Makefile.am Added entry for upgrade_2.0_to_3.0.sh --- configure.ac | 1 + src/bin/admin/tests/pgsql_tests.sh.in | 127 ++++++-- src/share/database/scripts/pgsql/Makefile.am | 1 + .../scripts/pgsql/dhcpdb_create.pgsql | 78 +++-- .../database/scripts/pgsql/dhcpdb_drop.pgsql | 2 + .../scripts/pgsql/upgrade_2.0_to_3.0.sh.in | 271 ++++++++++++++++++ 6 files changed, 438 insertions(+), 42 deletions(-) create mode 100644 src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh.in diff --git a/configure.ac b/configure.ac index 576df8d5e6..bf57a6e1b2 100755 --- a/configure.ac +++ b/configure.ac @@ -1485,6 +1485,7 @@ AC_CONFIG_FILES([compatcheck/Makefile src/share/database/scripts/mysql/upgrade_4.0_to_4.1.sh src/share/database/scripts/pgsql/Makefile src/share/database/scripts/pgsql/upgrade_1.0_to_2.0.sh + src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh tools/Makefile tools/path_replacer.sh ]) diff --git a/src/bin/admin/tests/pgsql_tests.sh.in b/src/bin/admin/tests/pgsql_tests.sh.in index 4c564d886a..833fa1ffe9 100644 --- a/src/bin/admin/tests/pgsql_tests.sh.in +++ b/src/bin/admin/tests/pgsql_tests.sh.in @@ -96,30 +96,18 @@ pgsql_lease_version_test() { test_finish 0 } -pgsql_upgrade_test() { - test_start "pgsql.upgrade-test" - - # Wipe the whole database - pgsql_wipe - - # Initialize database to schema 1.0. - pgsql_execute_script @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql - assert_eq 0 $? "cannot initialize the database, expected exit code: %d, actual: %d" - - ${keaadmin} lease-upgrade pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir - assert_eq 0 $? "lease-upgrade failed, expected exit code: %d, actual: %d" - - #table: state column added to lease4 (upgrade 1.0 -> 2.0) - output=`pgsql_execute "SELECT state from lease4;"` +pgsql_upgrade_1_0_to_2_0() { + # Added state column to lease4 + output=`pgsql_execute "select state from lease4;"` ERRCODE=$? assert_eq 0 $ERRCODE "lease4 is missing state column. (returned status code %d, expected %d)" - #table: state column added to lease6 (upgrade 1.0 -> 2.0) - output=`pgsql_execute "SELECT state from lease6;"` + # Added state column to lease6 + output=`pgsql_execute "select state from lease6;"` ERRCODE=$? assert_eq 0 $ERRCODE "lease6 is missing state column. (returned status code %d, expected %d)" - #table: stored procedures for lease dumps added (upgrade 1.0 -> 2.0) + # Added stored procedures for lease dumps output=`pgsql_execute "select lease4DumpHeader from lease4DumpHeader();"` assert_eq 0 $ERRCODE "function lease4DumpHeader() broken or missing. (returned status code %d, expected %d)" @@ -131,6 +119,107 @@ pgsql_upgrade_test() { output=`pgsql_execute "select address from lease6DumpData();"` assert_eq 0 $ERRCODE "function lease6DumpData() broken or missing. (returned status code %d, expected %d)" +} + +pgsql_upgrade_2_0_to_3_0() { + # Added hwaddr, hwtype, and hwaddr_source columns to lease6 table + output=`pgsql_execute "select hwaddr, hwtype, hwaddr_source from lease6;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "lease6 table not upgraded to 3.0 (returned status code %d, expected %d)" + + # Added lease_hwaddr_source table + output=`pgsql_execute "select hwaddr_source, name from lease_hwaddr_source;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "lease_hwaddr_source table is missing or broken. (returned status code %d, expected %d)" + + # Added hosts table + output=`pgsql_execute "select host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes from hosts;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "hosts table is missing or broken. (returned status code %d, expected %d)" + + # Added ipv6_reservations table + output=`pgsql_execute "select reservation_id, address, prefix_len, type, dhcp6_iaid, host_id from ipv6_reservations;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "ipv6_reservations table is missing or broken. (returned status code %d, expected %d)" + + # Added dhcp4_options table + output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id, scope_id from dhcp4_options;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "dhcp4_options table is missing or broken. (returned status code %d, expected %d)" + + # Added dhcp6_options table + output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "dhcp6_options table is missing or broken. (returned status code %d, expected %d)" + + # Added host_identifier_type table + output=`pgsql_execute "select type, name from host_identifier_type;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "host_identifier_type table is missing or broken. (returned status code %d, expected %d)" + + # Added dhcp_option_scope table + output=`pgsql_execute "select scope_id, scope_name from dhcp_option_scope;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "dhcp_option_scope table is missing or broken. (returned status code %d, expected %d)" + + # Added dhcp6_options table + output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"` + ERRCODE=$? + assert_eq 0 $ERRCODE "dhcp6_options table is missing or broken. (returned status code %d, expected %d)" + + # Added order by clause to lease4DumpData + output=`pgsql_execute "select address from lease4DumpData();"` + assert_eq 0 $ERRCODE "function lease4DumpData() broken or missing. (returned status code %d, expected %d)" + output=`pgsql_execute "\sf lease4DumpData"` + assert_eq 0 $ERRCODE "\sf of lease4DumpData failed. (returned status code %d, expected %d)" + count=`echo $output | grep -ic "order by l\.address"` + assert_eq 1 $count "lease4DumpData is missing order by clause" + + # Added hwaddr columns to lease6DumpHeader + output=`pgsql_execute "select lease6DumpHeader from lease6DumpHeader();"` + assert_eq 0 $ERRCODE "function lease6DumpHeader() broken or missing. (returned status code %d, expected %d)" + count=`echo $output | grep -ic "hwaddr,hwtype,hwaddr_source"` + assert_eq 1 $count "lease6DumpHeader is missing hwaddr columns" + + # Added hwaddr columns to lease6DumpData + output=`pgsql_execute "select hwaddr,hwttype,hwaddr_source from lease6DumpData();"` + assert_eq 0 $ERRCODE "function lease6DumpData() broken or missing. (returned status code %d, expected %d)" + + # Added order by clause to lease6DumpData + output=`pgsql_execute "\sf lease4DumpData"` + assert_eq 0 $ERRCODE "\sf of lease4DumpData failed. (returned status code %d, expected %d)" + count=`echo $output | grep -ic "order by l\.address"` + assert_eq 1 $count "lease4DumpData is missing order by clause" + + #lease_hardware_source should have row for source = 0 + output=`pgsql_execute "select count(hwaddr_source) from lease_hwaddr_source where hwaddr_source = 0 and name='HWADDR_SOURCE_UNKNOWN';"` + ERRCODE=$? + assert_eq 0 $ERRCODE "select from lease_hwaddr_source failed. (returned status code %d, expected %d)" + assert_eq 1 "$output" "lease_hwaddr_source does not contain entry for HWADDR_SOURCE_UKNOWN. (record count %d, expected %d)" + + # Verify upgraded schemd reports version 3.0. + version=$(${keaadmin} lease-version pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir) + assert_str_eq "3.0" ${version} "Expected kea-admin to return %s, returned value was %s" +} + +pgsql_upgrade_test() { + test_start "pgsql.upgrade-test" + + # Wipe the whole database + pgsql_wipe + + # Initialize database to schema 1.0. + pgsql_execute_script @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql + assert_eq 0 $? "cannot initialize the database, expected exit code: %d, actual: %d" + + ${keaadmin} lease-upgrade pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir + assert_eq 0 $? "lease-upgrade failed, expected exit code: %d, actual: %d" + + # Check 1.0 to 2.0 upgrade + pgsql_upgrade_1_0_to_2_0 + + # Check 2.0 to 3.0 upgrade + pgsql_upgrade_2_0_to_3_0 # Let's wipe the whole database pgsql_wipe @@ -145,7 +234,7 @@ get_local_time() { # Expiration field is a "timestamp with timezone" so we need a reference # time for the machine/DB this test is running upon. - ref_timestamp=`pgsql_execute "SELECT timestamptz '$1';"` + ref_timestamp=`pgsql_execute "select timestamptz '$1';"` ERRCODE=$? assert_eq 0 $ERRCODE "reference time query failed for [$timestamp], expected exit code %d, actual %d" echo $ref_timestamp diff --git a/src/share/database/scripts/pgsql/Makefile.am b/src/share/database/scripts/pgsql/Makefile.am index 75387dff90..693a76d7ef 100644 --- a/src/share/database/scripts/pgsql/Makefile.am +++ b/src/share/database/scripts/pgsql/Makefile.am @@ -4,5 +4,6 @@ sqlscriptsdir = ${datarootdir}/${PACKAGE_NAME}/scripts/pgsql sqlscripts_DATA = dhcpdb_create.pgsql sqlscripts_DATA += dhcpdb_drop.pgsql sqlscripts_DATA += upgrade_1.0_to_2.0.sh +sqlscripts_DATA += upgrade_2.0_to_3.0.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 0f7c4f57a3..92878300ca 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql @@ -19,6 +19,8 @@ -- @dhcpdb_create.pgsql +-- Start a single transaction for the Entire script +START TRANSACTION; -- Holds the IPv4 leases. CREATE TABLE lease4 ( @@ -71,11 +73,10 @@ CREATE TABLE lease6_types ( lease_type SMALLINT PRIMARY KEY NOT NULL, -- Lease type code. name VARCHAR(5) -- Name of the lease type ); -START TRANSACTION; + INSERT INTO lease6_types VALUES (0, 'IA_NA'); -- Non-temporary v6 addresses INSERT INTO lease6_types VALUES (1, 'IA_TA'); -- Temporary v6 addresses INSERT INTO lease6_types VALUES (2, 'IA_PD'); -- Prefix delegations -COMMIT; -- Finally, the version of the schema. We start at 0.1 during development. -- This table is only modified during schema upgrades. For historical reasons @@ -90,9 +91,8 @@ CREATE TABLE schema_version ( version INT PRIMARY KEY NOT NULL, -- Major version number minor INT -- Minor version number ); -START TRANSACTION; + INSERT INTO schema_version VALUES (1, 0); -COMMIT; -- -- Schema 2.0 specification starts here. @@ -121,11 +121,9 @@ CREATE TABLE lease_state ( name VARCHAR(64) NOT NULL); -- Insert currently defined state names. -START TRANSACTION; INSERT INTO lease_state VALUES (0, 'default'); INSERT INTO lease_state VALUES (1, 'declined'); INSERT INTO lease_state VALUES (2, 'expired-reclaimed'); -COMMIT; -- Add a constraint that any state value added to the lease4 must -- map to a value in the lease_state table. @@ -230,20 +228,41 @@ $$ LANGUAGE SQL; -- -- Set 2.0 schema version. -START TRANSACTION; UPDATE schema_version SET version = '2', minor = '0'; -COMMIT; -- Schema 2.0 specification ends here. -- Upgrade to schema 3.0 begins here: -- --- Table structure for table hosts +-- Table structure for table host_identifier_type -- -DROP TABLE IF EXISTS hosts; +CREATE TABLE host_identifier_type ( + type SMALLINT PRIMARY KEY NOT NULL, + name VARCHAR(32) DEFAULT NULL +); + +INSERT INTO host_identifier_type VALUES (0, 'hw-address'); +INSERT INTO host_identifier_type VALUES (1, 'duid'); +INSERT INTO host_identifier_type VALUES (2, 'circuit-id'); + +CREATE TABLE dhcp_option_scope ( + scope_id SMALLINT PRIMARY KEY NOT NULL, + scope_name varchar(32) DEFAULT NULL +); + +INSERT INTO dhcp_option_scope VALUES (0, 'global'); +INSERT INTO dhcp_option_scope VALUES (1, 'subnet'); +INSERT INTO dhcp_option_scope VALUES (2, 'client-class'); +INSERT INTO dhcp_option_scope VALUES (3, 'host'); + +-- +-- Table structure for table hosts +-- +-- Primary key and unique contraints automatically create indexes +-- foreign key constraints do not CREATE TABLE hosts ( host_id SERIAL PRIMARY KEY NOT NULL, dhcp_identifier BYTEA NOT NULL, @@ -253,18 +272,20 @@ CREATE TABLE hosts ( ipv4_address BIGINT DEFAULT NULL, hostname VARCHAR(255) DEFAULT NULL, dhcp4_client_classes VARCHAR(255) DEFAULT NULL, - dhcp6_client_classes VARCHAR(255) DEFAULT NULL + dhcp6_client_classes VARCHAR(255) DEFAULT NULL, + CONSTRAINT key_dhcp4_ipv4_address_subnet_id UNIQUE (ipv4_address, dhcp4_subnet_id), + CONSTRAINT key_dhcp4_identifier_subnet_id UNIQUE (dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id), + CONSTRAINT key_dhcp6_identifier_subnet_id UNIQUE (dhcp_identifier, dhcp_identifier_type, dhcp6_subnet_id), + CONSTRAINT fk_host_identifier_type FOREIGN KEY (dhcp_identifier_type) REFERENCES host_identifier_type (type) + ON DELETE CASCADE ); -CREATE INDEX key_dhcp4_identifier_subnet_id ON hosts (dhcp_identifier, dhcp_identifier_type); - -CREATE INDEX key_dhcp6_identifier_subnet_id ON hosts (dhcp_identifier, dhcp_identifier_type, dhcp6_subnet_id); +CREATE INDEX fk_host_identifier_type ON hosts (dhcp_identifier_type); -- -- Table structure for table dhcp4_options -- -DROP TABLE IF EXISTS dhcp4_options; CREATE TABLE dhcp4_options ( option_id SERIAL PRIMARY KEY NOT NULL, code SMALLINT NOT NULL, @@ -275,16 +296,18 @@ CREATE TABLE dhcp4_options ( dhcp_client_class VARCHAR(128) DEFAULT NULL, dhcp4_subnet_id INT DEFAULT NULL, host_id INT DEFAULT NULL, - CONSTRAINT fk_options_host1 FOREIGN KEY (host_id) REFERENCES hosts (host_id) ON DELETE CASCADE + scope_id SMALLINT NOT NULL, + CONSTRAINT fk_options_host1 FOREIGN KEY (host_id) REFERENCES hosts (host_id) ON DELETE CASCADE, + CONSTRAINT fk_dhcp4_option_scode FOREIGN KEY (scope_id) REFERENCES dhcp_option_scope (scope_id) ON DELETE CASCADE ); CREATE INDEX fk_dhcp4_options_host1_idx ON dhcp4_options (host_id); +CREATE INDEX fk_dhcp4_options_scope_idx ON dhcp4_options (scope_id); -- -- Table structure for table dhcp6_options -- -DROP TABLE IF EXISTS dhcp6_options; CREATE TABLE dhcp6_options ( option_id SERIAL PRIMARY KEY NOT NULL, code INT NOT NULL, @@ -295,16 +318,18 @@ CREATE TABLE dhcp6_options ( dhcp_client_class VARCHAR(128) DEFAULT NULL, dhcp6_subnet_id INT DEFAULT NULL, host_id INT DEFAULT NULL, - CONSTRAINT fk_options_host10 FOREIGN KEY (host_id) REFERENCES hosts (host_id) ON DELETE CASCADE + scope_id SMALLINT NOT NULL, + CONSTRAINT fk_options_host10 FOREIGN KEY (host_id) REFERENCES hosts (host_id) ON DELETE CASCADE, + CONSTRAINT fk_dhcp6_option_scode FOREIGN KEY (scope_id) REFERENCES dhcp_option_scope (scope_id) ON DELETE CASCADE ); CREATE INDEX fk_dhcp6_options_host1_idx ON dhcp6_options (host_id); +CREATE INDEX fk_dhcp6_options_scope_idx ON dhcp6_options (scope_id); -- -- Table structure for table ipv6_reservations -- -DROP TABLE IF EXISTS ipv6_reservations; CREATE TABLE ipv6_reservations ( reservation_id SERIAL PRIMARY KEY NOT NULL, address VARCHAR(39) NOT NULL, @@ -312,6 +337,7 @@ CREATE TABLE ipv6_reservations ( type SMALLINT NOT NULL DEFAULT '0', dhcp6_iaid INT DEFAULT NULL, host_id INT NOT NULL, + CONSTRAINT key_dhcp6_address_prefix_len UNIQUE (address, prefix_len), CONSTRAINT fk_ipv6_reservations_host FOREIGN KEY (host_id) REFERENCES hosts (host_id) ON DELETE CASCADE ); @@ -321,7 +347,6 @@ CREATE INDEX fk_ipv6_reservations_host_idx ON ipv6_reservations (host_id); -- Table structure for table lease_hwaddr_source -- -DROP TABLE IF EXISTS lease_hwaddr_source; CREATE TABLE lease_hwaddr_source ( hwaddr_source INT PRIMARY KEY NOT NULL, name VARCHAR(40) DEFAULT NULL @@ -346,8 +371,13 @@ INSERT INTO lease_hwaddr_source VALUES (16, 'HWADDR_SOURCE_REMOTE_ID'); INSERT INTO lease_hwaddr_source VALUES (32, 'HWADDR_SOURCE_SUBSCRIBER_ID'); -- Hardware address extracted from docsis options -INSERT INTO lease_hwaddr_source VALUES (64, 'HWADDR_SOURCE_DOCSIS'); +INSERT INTO lease_hwaddr_source VALUES (64, 'HWADDR_SOURCE_DOCSIS_CMTS'); + +INSERT INTO lease_hwaddr_source VALUES (128, 'HWADDR_SOURCE_DOCSIS_MODEM'); +-- In the event hardware address cannot be determined, we need to satisfy +-- foreign key constraint between lease6 and lease_hardware_source +INSERT INTO lease_hwaddr_source VALUES (0, 'HWADDR_SOURCE_UNKNOWN'); -- Adding ORDER BY clause to sort by lease address -- @@ -440,12 +470,14 @@ CREATE FUNCTION lease6DumpData() RETURNS left outer join lease_hwaddr_source h on (l.hwaddr_source = h.hwaddr_source) ORDER BY l.address; $$ LANGUAGE SQL; --- -- Set 3.0 schema version. -START TRANSACTION; UPDATE schema_version SET version = '3', minor = '0'; + +-- Schema 3.0 specification ends here. + +-- Commit the script transaction COMMIT; -- Notes: diff --git a/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql b/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql index 3b52e902d4..e399b786d9 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_drop.pgsql @@ -13,6 +13,8 @@ DROP TABLE IF EXISTS dhcp4_options CASCADE; DROP TABLE IF EXISTS dhcp6_options CASCADE; DROP TABLE IF EXISTS ipv6_reservations CASCADE; DROP TABLE IF EXISTS lease_hwaddr_source CASCADE; +DROP TABLE IF EXISTS host_identifier_type CASCADE; +DROP TABLE IF EXISTS dhcp_option_scope CASCADE; DROP TABLE IF EXISTS hosts CASCADE; DROP FUNCTION IF EXISTS lease4DumpHeader(); DROP FUNCTION IF EXISTS lease4DumpData(); diff --git a/src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh.in b/src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh.in new file mode 100644 index 0000000000..660142cecc --- /dev/null +++ b/src/share/database/scripts/pgsql/upgrade_2.0_to_3.0.sh.in @@ -0,0 +1,271 @@ +#!/bin/sh + +# 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" != "2.0" ]; then + printf "This script upgrades 2.0 to 3.0. Reported version is $VERSION. Skipping upgrade.\n" + exit 0 +fi + +psql "$@" >/dev/null <