Attachment 37794 Details for Bug 8480 – Bug 8480: Add constraint on auth_subfield_structure.authtypecode

[patch] Bug 8480: Add constraint on auth_subfield_structure.authtypecode

Bug-8480-Add-constraint-on-authsubfieldstructureau.patch (text/plain), 3.41 KB, created by Frédéric Demians on 2015-04-14 06:59:21 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Frédéric Demians

Created: 2015-04-14 06:59:21 UTC

Size: 3.41 KB

patch

obsolete

>From 8594c88529f181816e7c2fa13d820b92e095e4a8 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.
>
>Signed-off-by: Frederic Demians <f.demians@tamil.fr>
>- 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
>---
> installer/data/mysql/kohastructure.sql |  3 ++-
> installer/data/mysql/updatedatabase.pl | 19 +++++++++++++++++++
> 2 files changed, 21 insertions(+), 1 deletion(-)
>
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index a74d81a..8453735 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 338246c..88d30a1 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -10025,6 +10025,25 @@ 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.3.5

Actions: View | Diff | Splinter Review

Attachments on bug 8480: 37402 | 37794 | 38226