From b5a3bcb80152224ef164ffd0a04392cb500bb6ef Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@biblibre.com>
Date: Tue, 31 Mar 2015 13:18:21 +0200
Subject: [PATCH] Bug 8480: Add constraint on
 auth_subfield_structure.authtypecode

In order not to have useless entries in the auth_subfield_structure
table, this patch modifies the DB structure to add a foreign key on the
authtypecode column.
Note that the auth_tag_structure already has this constraint.

Test plan:
0/ Don't apply this patch
1/ Create a now authority type 'RM_ME'
2/ Look at the MARC structure, to create the subfield structure and
populate the auth_subfield_structure table.
3/ Delete the authority type
4/ Using your SQL cli:
 SELECT COUNT(*) FROM auth_subfield_structure WHERE authtypecode='RM_ME';
=> The data are still in this table.
5/ Apply this patch
6/ Execute the updatedb entry
7/ Confirm the entries in the auth_subfield_structure table related to
RM_ME have been deleted
8/ Repeat 1, 2 and 3 and verify the auth_subfield_structure entries have
been correctly removed.
---
 installer/data/mysql/kohastructure.sql |  3 ++-
 installer/data/mysql/updatedatabase.pl | 18 ++++++++++++++++++
 2 files changed, 20 insertions(+), 1 deletion(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 6ddba43..ce97c8a 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -58,7 +58,8 @@ CREATE TABLE `auth_subfield_structure` (
   `frameworkcode` varchar(10) NOT NULL default '',
   `defaultvalue` TEXT DEFAULT '',
   PRIMARY KEY  (`authtypecode`,`tagfield`,`tagsubfield`),
-  KEY `tab` (`authtypecode`,`tab`)
+  KEY `tab` (`authtypecode`,`tab`),
+  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 --
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 951060f..72a4948 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -9902,6 +9902,24 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.19.00.XXX";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do(q{
+        DELETE ass.*
+        FROM auth_subfield_structure AS ass
+        LEFT JOIN auth_types USING(authtypecode)
+        WHERE auth_types.authtypecode IS NULL
+    });
+
+    $dbh->do(q{
+        ALTER TABLE auth_subfield_structure
+        ADD CONSTRAINT auth_subfield_structure_ibfk_1 FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode) ON DELETE CASCADE ON UPDATE CASCADE
+    });
+
+    print "Upgrade to $DBversion done (Bug 8480 - Add foreign key on auth_subfield_structure.authtypecode)\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
-- 
2.1.0