[#680,!426] Delete embedded options when subnet gets deleted in mysql_cb.

4 files changed, 96 insertions, 1 deletions

diff --git a/src/share/database/scripts/mysql/.gitignore b/src/share/database/scripts/mysql/.gitignore
index 524e3c8365..f3ba934d66 100644
--- a/src/share/database/scripts/mysql/.gitignore
+++ b/src/share/database/scripts/mysql/.gitignore
@@ -9,4 +9,5 @@
 /upgrade_6.0_to_7.0.sh
 /upgrade_7.0_to_8.0.sh
 /upgrade_8.0_to_8.1.sh
+/upgrade_8.1_to_8.2.sh
 /wipe_data.sh
diff --git a/src/share/database/scripts/mysql/Makefile.am b/src/share/database/scripts/mysql/Makefile.am
index 1f85842069..6370f95af9 100644
--- a/src/share/database/scripts/mysql/Makefile.am
+++ b/src/share/database/scripts/mysql/Makefile.am
@@ -14,6 +14,7 @@ sqlscripts_DATA += upgrade_5.2_to_6.0.sh
 sqlscripts_DATA += upgrade_6.0_to_7.0.sh
 sqlscripts_DATA += upgrade_7.0_to_8.0.sh
 sqlscripts_DATA += upgrade_8.0_to_8.1.sh
+sqlscripts_DATA += upgrade_8.1_to_8.2.sh
 sqlscripts_DATA += wipe_data.sh
 
 DISTCLEANFILES = upgrade_1.0_to_2.0.sh
@@ -26,7 +27,7 @@ DISTCLEANFILES += upgrade_5.1_to_5.2.sh
 DISTCLEANFILES += upgrade_5.2_to_6.0.sh
 DISTCLEANFILES += upgrade_6.0_to_7.0.sh
 DISTCLEANFILES += upgrade_7.0_to_8.0.sh
-DISTCLEANFILES += upgrade_8.0_to_8.1.sh
+DISTCLEANFILES += upgrade_8.1_to_8.2.sh
 DISTCLEANFILES += wipe_data.sh
 
 EXTRA_DIST = ${sqlscripts_DATA}
diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql
index f5ce7e7817..253f2f45c7 100644
--- a/src/share/database/scripts/mysql/dhcpdb_create.mysql
+++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql
@@ -2377,6 +2377,40 @@ SET version = '8', minor = '1';
 
 # This line concludes database upgrade to version 8.1.
 
+# Do not perform cascade deletion of the data in the dhcp4_pool because
+# the cascade deletion does not execute triggers associated with the table.
+# Instead we are going to use triggers on the dhcp4_subnet table.
+ALTER TABLE dhcp4_pool
+    DROP FOREIGN KEY fk_dhcp4_pool_subnet_id;
+
+ALTER TABLE dhcp4_pool
+    ADD CONSTRAINT fk_dhcp4_pool_subnet_id FOREIGN KEY (subnet_id)
+    REFERENCES dhcp4_subnet (subnet_id)
+    ON DELETE NO ACTION ON UPDATE CASCADE;
+
+
+# Drop existing trigger on the dhcp4_subnet table.
+DROP TRIGGER dhcp4_subnet_ADEL;
+
+# Create new trigger which will delete pools associated with the subnet and
+# the options associated with the subnet.
+DELIMITER $$
+CREATE TRIGGER dhcp4_subnet_BDEL BEFORE DELETE ON dhcp4_subnet
+    FOR EACH ROW
+    BEGIN
+        CALL createAuditEntryDHCP4('dhcp4_subnet', OLD.subnet_id, "delete");
+        DELETE FROM dhcp4_pool WHERE subnet_id = OLD.subnet_id;
+        DELETE FROM dhcp4_options WHERE dhcp4_subnet_id = OLD.subnet_id;
+    END $$
+DELIMITER ;
+
+
+# Update the schema version number
+UPDATE schema_version
+SET version = '8', minor = '2';
+
+# This line concludes database upgrade to version 8.2.
+
 
 # Notes:
 #
diff --git a/src/share/database/scripts/mysql/upgrade_8.1_to_8.2.sh.in b/src/share/database/scripts/mysql/upgrade_8.1_to_8.2.sh.in
new file mode 100644
index 0000000000..a8dbcde246
--- /dev/null
+++ b/src/share/database/scripts/mysql/upgrade_8.1_to_8.2.sh.in
@@ -0,0 +1,59 @@
+#!/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=`mysql_version "$@"`
+
+if [ "$VERSION" != "8.1" ]; then
+    printf "This script upgrades 8.1 to 8.2. Reported version is $VERSION. Skipping upgrade.\n"
+    exit 0
+fi
+
+mysql "$@" <<EOF
+
+# Do not perform cascade deletion of the data in the dhcp4_pool because
+# the cascade deletion does not execute triggers associated with the table.
+# Instead we are going to use triggers on the dhcp4_subnet table.
+ALTER TABLE dhcp4_pool
+    DROP FOREIGN KEY fk_dhcp4_pool_subnet_id;
+
+ALTER TABLE dhcp4_pool
+    ADD CONSTRAINT fk_dhcp4_pool_subnet_id FOREIGN KEY (subnet_id)
+    REFERENCES dhcp4_subnet (subnet_id)
+    ON DELETE NO ACTION ON UPDATE CASCADE;
+
+
+# Drop existing trigger on the dhcp4_subnet table.
+DROP TRIGGER dhcp4_subnet_ADEL;
+
+# Create new trigger which will delete pools associated with the subnet and
+# the options associated with the subnet.
+DELIMITER $$
+CREATE TRIGGER dhcp4_subnet_BDEL BEFORE DELETE ON dhcp4_subnet
+    FOR EACH ROW
+    BEGIN
+        CALL createAuditEntryDHCP4('dhcp4_subnet', OLD.subnet_id, "delete");
+        DELETE FROM dhcp4_pool WHERE subnet_id = OLD.subnet_id;
+        DELETE FROM dhcp4_options WHERE dhcp4_subnet_id = OLD.subnet_id;
+    END $$
+DELIMITER ;
+
+
+# Update the schema version number
+UPDATE schema_version
+SET version = '8', minor = '2';
+
+# This line concludes database upgrade to version 8.2.
+
+EOF
+
+RESULT=$?
+
+exit $?