From bf23224c999d6a99d0c23e84bebb59415b4ad28d Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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. Signed-off-by: Frederic Demians - Works exactly as described in the (very good) test plan. - After authority type deletion, auth_subfield_structure still contains entries for deleted authority type. Applying the patch clean the previously undeleted records in auth_subfield_strucute. Now deleting a authority type cleans propertly all appropriate records in auth_subfield_structure. - Fix a merge conflict Signed-off-by: Katrin Fischer Signed-off-by: Tomas Cohen Arazi --- 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 aaef328c54..c41710cc93 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 13c03d5ada..2d58cb68ef 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -10425,6 +10425,24 @@ if ( CheckVersion($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); +} + # DEVELOPER PROCESS, search for anything to execute in the db_update directory # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. -- 2.39.5