From e56c4b249d9ac66e05054f916560a0f7bea9a194 Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Tue, 31 May 2011 13:35:14 +1200 Subject: [PATCH] Bug 6430 - DB changes needed to support hourly circ Changing columns to datetime instead of date Squashed commit of the following: commit ccf37880eaf660685d439b000298dcc025e78b6e Author: Chris Cormack Date: Thu May 26 12:06:24 2011 +1200 Bug 5549 backing out of decision to change the name of the colums commit 2e326fdec7d768fe93d41e93abf3ca665a363791 Author: Chris Cormack Date: Tue May 24 14:31:26 2011 +1200 Bug 5549 : Starting work on hourly loans, changing issues table --- installer/data/mysql/kohastructure.sql | 16 ++++++++-------- installer/data/mysql/updatedatabase.pl | 16 ++++++++++++++++ 2 files changed, 24 insertions(+), 8 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 153e17b8e4..9e7d7a4f37 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -949,15 +949,15 @@ DROP TABLE IF EXISTS `issues`; CREATE TABLE `issues` ( -- information related to check outs or issues `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out - `date_due` date default NULL, -- date the item is due (yyyy-mm-dd) + `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss) `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out `issuingbranch` varchar(18) default NULL, - `returndate` date default NULL, -- date the item was returned, will be NULL until moved to old_issues - `lastreneweddate` date default NULL, -- date the item was last renewed + `returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues + `lastreneweddate` datetime default NULL, -- date the item was last renewed `return` varchar(4) default NULL, `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched - `issuedate` date default NULL, -- date the item was checked out or issued + `issuedate` datetime default NULL, -- date the item was checked out or issued KEY `issuesborridx` (`borrowernumber`), KEY `bordate` (`borrowernumber`,`timestamp`), CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, @@ -1363,15 +1363,15 @@ DROP TABLE IF EXISTS `old_issues`; CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out - `date_due` date default NULL, -- date the item is due (yyyy-mm-dd) + `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd) `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out `issuingbranch` varchar(18) default NULL, - `returndate` date default NULL, -- date the item was returned - `lastreneweddate` date default NULL, -- date the item was last renewed + `returndate` datetime default NULL, -- date the item was returned + `lastreneweddate` datetime default NULL, -- date the item was last renewed `return` varchar(4) default NULL, `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched - `issuedate` date default NULL, -- date the item was checked out or issued + `issuedate` datetime default NULL, -- date the item was checked out or issued KEY `old_issuesborridx` (`borrowernumber`), KEY `old_issuesitemidx` (`itemnumber`), KEY `old_bordate` (`borrowernumber`,`timestamp`), diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index a97ccdaef4..19e6cdc9b3 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4932,6 +4932,22 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE issues CHANGE date_due date_due datetime"); + $dbh->do("ALTER TABLE issues CHANGE returndate returndate datetime"); + $dbh->do("ALTER TABLE issues CHANGE lastreneweddate lastreneweddate datetime"); + $dbh->do("ALTER TABLE issues CHANGE issuedate issuedate datetime"); + $dbh->do("ALTER TABLE old_issues CHANGE date_due date_due datetime"); + $dbh->do("ALTER TABLE old_issues CHANGE returndate returndate datetime"); + $dbh->do("ALTER TABLE old_issues CHANGE lastreneweddate lastreneweddate datetime"); + $dbh->do("ALTER TABLE old_issues CHANGE issuedate issuedate datetime"); + print "Upgrade to $DBversion done (Setting up issues tables for hourly loans)\n"; + SetVersion($DBversion); +} + + + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 2.39.2