Attachment #90372 for bug #23066

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




$DBversion = 'XXX'; # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {

    # Add constraint for branchcode
    unless ( foreign_key_exists( 'issues', 'issues_ibfk_3' ) ) {
        $dbh->do(q{UPDATE issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL});
        $dbh->do(q{ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE});
    }
    unless ( foreign_key_exists( 'old_issues', 'old_issues_ibfk_3' ) ) {
        $dbh->do(q{UPDATE old_issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL});
        $dbh->do(q{ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE});
    }

    # Always end with this (adjust the bug info)
    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 15985 - Include transacting library in fines)\n";
}




  KEY `branchcode_idx` (`branchcode`),
  KEY `bordate` (`borrowernumber`,`timestamp`),
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--

  KEY `old_issuesitemidx` (`itemnumber`),
  KEY `branchcode_idx` (`branchcode`),
  KEY `old_bordate` (`borrowernumber`,`timestamp`),
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE
    ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
- 

Return to bug 23066

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_15985.perl (+17 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if( CheckVersion( $DBversion ) ) {
	3
	4	# Add constraint for branchcode
	5	unless ( foreign_key_exists( 'issues', 'issues_ibfk_3' ) ) {
	6	$dbh->do(q{UPDATE issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL});
	7	$dbh->do(q{ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE});
	8	}
	9	unless ( foreign_key_exists( 'old_issues', 'old_issues_ibfk_3' ) ) {
	10	$dbh->do(q{UPDATE old_issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL});
	11	$dbh->do(q{ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE});
	12	}
	13
	14	# Always end with this (adjust the bug info)
	15	SetVersion( $DBversion );
	16	print "Upgrade to $DBversion done (Bug 15985 - Include transacting library in fines)\n";
	17	}

Lines 1746-1752 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here

(-)a/installer/data/mysql/kohastructure.sql (-6 / +5 lines)
1746	KEY `branchcode_idx` (`branchcode`),	1746	KEY `branchcode_idx` (`branchcode`),
1747	KEY `bordate` (`borrowernumber`,`timestamp`),	1747	KEY `bordate` (`borrowernumber`,`timestamp`),
1748	CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,	1748	CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1749	CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE	1749	CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
		1750	CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE
1750	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1751	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1751		1752
1752	--	1753	--
Lines 1776-1785 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1776	KEY `old_issuesitemidx` (`itemnumber`),	1777	KEY `old_issuesitemidx` (`itemnumber`),
1777	KEY `branchcode_idx` (`branchcode`),	1778	KEY `branchcode_idx` (`branchcode`),
1778	KEY `old_bordate` (`borrowernumber`,`timestamp`),	1779	KEY `old_bordate` (`borrowernumber`,`timestamp`),
1779	CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)	1780	CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1780	ON DELETE SET NULL ON UPDATE SET NULL,	1781	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1781	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)	1782	CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE
1782	ON DELETE SET NULL ON UPDATE SET NULL
1783	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1783	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1784		1784
1785	--	1785	--
1786	-