View | Details | Raw Unified | Return to bug 24289
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_24289.perl (+31 lines)
Line 0 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
    if( !foreign_key_exists( 'repeatable_holidays', 'repeatable_holidays_ibfk_1' ) ) {
4
        $dbh->do(q|
5
            DELETE h
6
            FROM repeatable_holidays h
7
            LEFT JOIN branches b ON h.branchcode=b.branchcode
8
            WHERE b.branchcode IS NULL;
9
        |);
10
        $dbh->do(q|
11
            ALTER TABLE repeatable_holidays
12
            ADD FOREIGN KEY repeatable_holidays_ibfk_1 (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
13
        |);
14
    }
15
16
    if( !foreign_key_exists( 'special_holidays', 'special_holidays_ibfk_1' ) ) {
17
        $dbh->do(q|
18
            DELETE h
19
            FROM special_holidays h
20
            LEFT JOIN branches b ON h.branchcode=b.branchcode
21
            WHERE b.branchcode IS NULL;
22
        |);
23
        $dbh->do(q|
24
            ALTER TABLE special_holidays
25
            ADD FOREIGN KEY special_holidays_ibfk_1 (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
26
        |);
27
    }
28
29
    SetVersion( $DBversion );
30
    print "Upgrade to $DBversion done (Bug 24289 - Adding foreign keys on *_holidays.branchcode tables)\n";
31
}
(-)a/installer/data/mysql/kohastructure.sql (-5 / +6 lines)
Lines 1320-1332 CREATE TABLE `printers_profile` ( Link Here
1320
DROP TABLE IF EXISTS `repeatable_holidays`;
1320
DROP TABLE IF EXISTS `repeatable_holidays`;
1321
CREATE TABLE `repeatable_holidays` ( -- information for the days the library is closed
1321
CREATE TABLE `repeatable_holidays` ( -- information for the days the library is closed
1322
  `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
1322
  `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
1323
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for
1323
  `branchcode` varchar(10) NOT NULL, -- foreign key from the branches table, defines which branch this closing is for
1324
  `weekday` smallint(6) default NULL, -- day of the week (0=Sunday, 1=Monday, etc) this closing is repeated on
1324
  `weekday` smallint(6) default NULL, -- day of the week (0=Sunday, 1=Monday, etc) this closing is repeated on
1325
  `day` smallint(6) default NULL, -- day of the month this closing is on
1325
  `day` smallint(6) default NULL, -- day of the month this closing is on
1326
  `month` smallint(6) default NULL, -- month this closing is in
1326
  `month` smallint(6) default NULL, -- month this closing is in
1327
  `title` varchar(50) NOT NULL default '', -- title of this closing
1327
  `title` varchar(50) NOT NULL default '', -- title of this closing
1328
  `description` MEDIUMTEXT NOT NULL, -- description for this closing
1328
  `description` MEDIUMTEXT NOT NULL, -- description for this closing
1329
  PRIMARY KEY  (`id`)
1329
  PRIMARY KEY  (`id`),
1330
  CONSTRAINT `repeatable_holidays_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1330
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1331
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1331
1332
1332
--
1333
--
Lines 1902-1915 CREATE TABLE `reviews` ( -- patron opac comments Link Here
1902
DROP TABLE IF EXISTS `special_holidays`;
1903
DROP TABLE IF EXISTS `special_holidays`;
1903
CREATE TABLE `special_holidays` ( -- non repeatable holidays/library closings
1904
CREATE TABLE `special_holidays` ( -- non repeatable holidays/library closings
1904
  `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
1905
  `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
1905
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for
1906
  `branchcode` varchar(10) NOT NULL, -- foreign key from the branches table, defines which branch this closing is for
1906
  `day` smallint(6) NOT NULL default 0, -- day of the month this closing is on
1907
  `day` smallint(6) NOT NULL default 0, -- day of the month this closing is on
1907
  `month` smallint(6) NOT NULL default 0, -- month this closing is in
1908
  `month` smallint(6) NOT NULL default 0, -- month this closing is in
1908
  `year` smallint(6) NOT NULL default 0, -- year this closing is in
1909
  `year` smallint(6) NOT NULL default 0, -- year this closing is in
1909
  `isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no)
1910
  `isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no)
1910
  `title` varchar(50) NOT NULL default '', -- title for this closing
1911
  `title` varchar(50) NOT NULL default '', -- title for this closing
1911
  `description` MEDIUMTEXT NOT NULL, -- description of this closing
1912
  `description` MEDIUMTEXT NOT NULL, -- description of this closing
1912
  PRIMARY KEY  (`id`)
1913
  PRIMARY KEY  (`id`),
1914
  CONSTRAINT `special_holidays_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1913
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1915
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1914
1916
1915
--
1917
--
1916
- 

Return to bug 24289