Bugzilla – Attachment 4134 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]
patch
0001-bug_4002-issues-referential-integrity.patch (text/plain), 5.92 KB, created by
Srdjan Jankovic
on 2011-05-17 00:26:46 UTC
(
hide
)
Description:
patch
Filename:
MIME Type:
Creator:
Srdjan Jankovic
Created:
2011-05-17 00:26:46 UTC
Size:
5.92 KB
patch
obsolete
>From c80800cd437b5573dc54b28631b2578e68878732 Mon Sep 17 00:00:00 2001 >From: Srdjan Jankovic <srdjan@catalyst.net.nz> >Date: Tue, 17 May 2011 12:24:25 +1200 >Subject: [PATCH] bug_4002: issues referential integrity > >--- > installer/data/mysql/kohastructure.sql | 12 +++++----- > installer/data/mysql/updatedatabase.pl | 33 ++++++++++++++++++++++++++++++++ > 2 files changed, 39 insertions(+), 6 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 52951e7..a076e5b 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -4332,6 +4332,39 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { > SetVersion($DBversion); > } > >+$DBversion = "3.05.00.XXX"; >+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 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 DROP FOREIGN KEY `issues_ibfk_1`"); >+ $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`"); >+ $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) >-- >1.6.5 >
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