From 50ee83eccc14286df264124826646cc625943404 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 28 Jul 2020 21:13:01 +0200 Subject: [PATCH] Bug 26145: DB changes - Add biblioimages.imagenumber Sponsored-by: Gerhard Sondermann Dialog e.K. (presseplus.de, presseshop.at, presseshop.ch) --- .../data/mysql/atomicupdate/bug_xxxxx.perl | 20 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 ++++-- 2 files changed, 24 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_xxxxx.perl diff --git a/installer/data/mysql/atomicupdate/bug_xxxxx.perl b/installer/data/mysql/atomicupdate/bug_xxxxx.perl new file mode 100644 index 0000000000..46e0614381 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_xxxxx.perl @@ -0,0 +1,20 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + if( !column_exists( 'biblioimages', 'itemnumber' ) ) { + $dbh->do(q| + ALTER TABLE biblioimages + ADD COLUMN itemnumber INT(11) DEFAULT NULL + AFTER biblionumber; + |); + $dbh->do(q| + ALTER TABLE biblioimages + ADD FOREIGN KEY bibliocoverimage_fk2 (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE + |); + $dbh->do(q| + ALTER TABLE biblioimages MODIFY biblionumber INT(11) DEFAULT NULL + |) + } + + NewVersion( $DBversion, 'XXXXX', "Add the biblioimages.itemnumber column"); +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 08cc35dcd6..7f7d6e2970 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3374,13 +3374,15 @@ DROP TABLE IF EXISTS `biblioimages`; CREATE TABLE `biblioimages` ( -- local cover images `imagenumber` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the image - `biblionumber` int(11) NOT NULL, -- foreign key from biblio table to link to biblionumber + `biblionumber` int(11) DEFAULT NULL, -- foreign key from biblio table to link to biblionumber + `itemnumber` int(11) DEFAULT NULL, -- foreign key from item table to link to itemnumber `mimetype` varchar(15) NOT NULL, -- image type `imagefile` mediumblob NOT NULL, -- image file contents `thumbnail` mediumblob NOT NULL, -- thumbnail file contents `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- image creation/update time PRIMARY KEY (`imagenumber`), - CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `bibliocoverimage_fk2` FOREIGN KEY (`itemnumber`) REFERENCES `item` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.20.1