From b9ae064aef63953b883ab7ce2354b032656e534f Mon Sep 17 00:00:00 2001
From: Marcin Siodelski <marcin@isc.org>
Date: Mon, 22 Jul 2019 14:36:01 +0200
Subject: [PATCH] [#680,!426] Updated MySQL upgrade script from 8.1 to 8.2.

---
 .../scripts/mysql/upgrade_8.1_to_8.2.sh.in    | 83 +++++++++++++++++--
 1 file changed, 77 insertions(+), 6 deletions(-)

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
index a8dbcde246..fed3be3aec 100644
--- 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
@@ -18,6 +18,35 @@ fi
 
 mysql "$@" <<EOF
 
+# Drop existing trigger on the dhcp4_shared_network table.
+DROP TRIGGER dhcp4_shared_network_ADEL;
+
+# Create new trigger which will delete options associated with the shared
+# network.
+DELIMITER $$
+CREATE TRIGGER dhcp4_shared_network_BDEL BEFORE DELETE ON dhcp4_shared_network
+    FOR EACH ROW
+    BEGIN
+        CALL createAuditEntryDHCP4('dhcp4_shared_network', OLD.id, "delete");
+        DELETE FROM dhcp4_options WHERE shared_network_name = OLD.name;
+    END $$
+DELIMITER ;
+
+# 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 ;
+
 # 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.
@@ -29,22 +58,64 @@ ALTER TABLE dhcp4_pool
     REFERENCES dhcp4_subnet (subnet_id)
     ON DELETE NO ACTION ON UPDATE CASCADE;
 
+# Drop existing trigger on the dhcp6_shared_network table.
+DROP TRIGGER dhcp6_shared_network_ADEL;
 
-# Drop existing trigger on the dhcp4_subnet table.
-DROP TRIGGER dhcp4_subnet_ADEL;
+# Create new trigger which will delete options associated with the shared
+# network.
+DELIMITER $$
+CREATE TRIGGER dhcp6_shared_network_BDEL BEFORE DELETE ON dhcp6_shared_network
+    FOR EACH ROW
+    BEGIN
+        CALL createAuditEntryDHCP6('dhcp6_shared_network', OLD.id, "delete");
+        DELETE FROM dhcp6_options WHERE shared_network_name = OLD.name;
+    END $$
+DELIMITER ;
+
+# Drop existing trigger on the dhcp6_subnet table.
+DROP TRIGGER dhcp6_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
+CREATE TRIGGER dhcp6_subnet_BDEL BEFORE DELETE ON dhcp6_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;
+        CALL createAuditEntryDHCP6('dhcp6_subnet', OLD.subnet_id, "delete");
+        DELETE FROM dhcp6_pool WHERE subnet_id = OLD.subnet_id;
+        DELETE FROM dhcp6_pd_pool WHERE subnet_id = OLD.subnet_id;
+        DELETE FROM dhcp6_options WHERE dhcp6_subnet_id = OLD.subnet_id;
     END $$
 DELIMITER ;
 
+# Do not perform cascade deletion of the data in the dhcp6_pool and dhcp6_pd_pool
+# because the cascaded deletion does not execute triggers associated with the table.
+# Instead we are going to use triggers on the dhcp6_subnet table.
+ALTER TABLE dhcp6_pool
+    DROP FOREIGN KEY fk_dhcp6_pool_subnet_id;
+
+ALTER TABLE dhcp6_pd_pool
+    DROP FOREIGN KEY fk_dhcp6_pd_pool_subnet_id;
+
+ALTER TABLE dhcp6_pool
+    ADD CONSTRAINT fk_dhcp6_pool_subnet_id FOREIGN KEY (subnet_id)
+    REFERENCES dhcp6_subnet (subnet_id)
+    ON DELETE NO ACTION ON UPDATE CASCADE;
+
+ALTER TABLE dhcp6_pd_pool
+    ADD CONSTRAINT fk_dhcp6_pd_pool_subnet_id FOREIGN KEY (subnet_id)
+    REFERENCES dhcp6_subnet (subnet_id)
+    ON DELETE NO ACTION ON UPDATE CASCADE;
+
+# Create trigger which removes pool specific options upon removal of
+# the pool.
+DELIMITER $$
+CREATE TRIGGER dhcp6_pd_pool_BDEL BEFORE DELETE ON dhcp6_pd_pool FOR EACH ROW
+BEGIN
+DELETE FROM dhcp6_options WHERE scope_id = 6 AND pd_pool_id = OLD.id;
+END
+$$
+DELIMITER ;
 
 # Update the schema version number
 UPDATE schema_version
-- 
2.47.2