Bugzilla – Attachment 4815 Details for
Bug 4002
Issues with borrowernumber set as NULL
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
proposed fix signed-off
0001-bug_4002-issues-referential-integrity.patch (text/plain), 6.46 KB, created by
Paul Poulain
on 2011-08-03 09:30:11 UTC
(
hide
)
Description:
proposed fix signed-off
Filename:
MIME Type:
Creator:
Paul Poulain
Created:
2011-08-03 09:30:11 UTC
Size:
6.46 KB
patch
obsolete
>From 01fb62e42d6739c2ea5cbddbc8b6fddea72953c6 Mon Sep 17 00:00:00 2001 >From: Paul Poulain <paul.poulain@biblibre.com> >Date: Wed, 3 Aug 2011 11:29:01 +0200 >Subject: [PATCH] bug_4002: issues referential integrity > >Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz> >Signed-off-by: Paul Poulain <paul.poulain@biblibre.com> >--- > installer/data/mysql/kohastructure.sql | 12 +++++----- > installer/data/mysql/updatedatabase.pl | 33 ++++++++++++++++++++++++++++++++ > kohaversion.pl | 2 +- > 3 files changed, 40 insertions(+), 7 deletions(-) > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index e3c6f42..cd0d722 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -936,8 +936,8 @@ CREATE TABLE `import_items` ( > > DROP TABLE IF EXISTS `issues`; > CREATE TABLE `issues` ( >- `borrowernumber` int(11) default NULL, >- `itemnumber` int(11) default NULL, >+ `borrowernumber` int(11), >+ `itemnumber` int(11), > `date_due` date default NULL, > `branchcode` varchar(10) default NULL, > `issuingbranch` varchar(18) default NULL, >@@ -947,11 +947,11 @@ CREATE TABLE `issues` ( > `renewals` tinyint(4) default NULL, > `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, > `issuedate` date default NULL, >+ PRIMARY KEY (`itemnumber`), > KEY `issuesborridx` (`borrowernumber`), >- KEY `issuesitemidx` (`itemnumber`), > KEY `bordate` (`borrowernumber`,`timestamp`), >- CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, >- CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL >+ 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 > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > -- >@@ -2098,7 +2098,7 @@ CREATE TABLE `serialitems` ( > UNIQUE KEY `serialitemsidx` (`itemnumber`), > KEY `serialitems_sfk_1` (`serialid`), > CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE >+ CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > DROP TABLE IF EXISTS `user_permissions`; >diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl >index 9cbc8db..ab313ea 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -4370,6 +4370,39 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > SetVersion($DBversion); > } > >+$DBversion = "3.05.00.005"; >+if (C4::Context->preference("Version") < TransformToNum($DBversion)) { >+ $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) >+ SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE borrowernumber IS NULL"); >+ $dbh->do("DELETE FROM issues WHERE borrowernumber IS NULL"); >+ >+ $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) >+ SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE itemnumber IS NULL"); >+ $dbh->do("DELETE FROM issues WHERE itemnumber IS NULL"); >+ >+ $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) >+ SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE borrowernumber = issues.borrowernumber)"); >+ $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE borrowernumber = issues.borrowernumber)"); >+ >+ $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) >+ SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)"); >+ $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)"); >+ >+ $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_1`"); >+ $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`"); >+ $dbh->do("ALTER TABLE issues ALTER COLUMN borrowernumber DROP DEFAULT"); >+ $dbh->do("ALTER TABLE issues ALTER COLUMN itemnumber DROP DEFAULT"); >+ $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL"); >+ $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL"); >+ $dbh->do("ALTER TABLE issues DROP KEY `issuesitemidx`"); >+ $dbh->do("ALTER TABLE issues ADD PRIMARY KEY (`itemnumber`)"); >+ $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE"); >+ $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE"); >+ >+ print "Upgrade to $DBversion done (issues referential integrity)\n"; >+ SetVersion ($DBversion); >+} >+ > =head1 FUNCTIONS > > =head2 DropAllForeignKeys($table) >diff --git a/kohaversion.pl b/kohaversion.pl >index 00bdbec..9021eb0 100644 >--- a/kohaversion.pl >+++ b/kohaversion.pl >@@ -16,7 +16,7 @@ the kohaversion is divided in 4 parts : > use strict; > > sub kohaversion { >- our $VERSION = '3.05.00.004'; >+ our $VERSION = '3.05.00.005'; > # version needs to be set this way > # so that it can be picked up by Makefile.PL > # during install >-- >1.7.4.1 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 4002
:
3906
|
3917
|
4120
|
4134
|
4454
|
4814
| 4815