From 2b358cf7ab806202bd5d96a5543c51810cb79b2f Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Mon, 27 Jan 2014 12:51:21 -0500 Subject: [PATCH] Bug 9303 [2] - relative's checkouts in the opac - Database Updates --- installer/data/mysql/kohastructure.sql | 6 ++++++ installer/data/mysql/updatedatabase.pl | 21 +++++++++++++++++++++ 2 files changed, 27 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 92ab713..901a231 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -265,6 +265,7 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history + `privacy_relative_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), @@ -867,6 +868,7 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history KEY `borrowernumber` (`borrowernumber`), + `privacy_relative_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts KEY borrowernumber (borrowernumber), KEY `cardnumber` (`cardnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1113,6 +1115,7 @@ CREATE TABLE `import_items` ( DROP TABLE IF EXISTS `issues`; CREATE TABLE `issues` ( -- information related to check outs or issues + `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table `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` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss) @@ -1124,6 +1127,7 @@ CREATE TABLE `issues` ( -- information related to check outs or issues `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` datetime default NULL, -- date the item was checked out or issued + PRIMARY KEY (`issue_id`), KEY `issuesborridx` (`borrowernumber`), KEY `itemnumber_idx` (`itemnumber`), KEY `branchcode_idx` (`branchcode`), @@ -1583,6 +1587,7 @@ CREATE TABLE `oai_sets_biblios` ( DROP TABLE IF EXISTS `old_issues`; CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned + `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table `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` datetime default NULL, -- date the item is due (yyyy-mm-dd) @@ -1594,6 +1599,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r `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` datetime default NULL, -- date the item was checked out or issued + PRIMARY KEY (`issue_id`), KEY `old_issuesborridx` (`borrowernumber`), KEY `old_issuesitemidx` (`itemnumber`), KEY `branchcode_idx` (`branchcode`), diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 3b1718c..a89dd69 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8465,6 +8465,27 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.15.00.XXX"; +if(CheckVersion($DBversion)) { + $dbh->do(q{ + ALTER TABLE borrowers ADD privacy_relative_checkouts BOOLEAN NOT NULL DEFAULT '0' + }); + $dbh->do(q{ + ALTER TABLE deletedborrowers ADD privacy_relative_checkouts BOOLEAN NOT NULL DEFAULT '0' + }); + $dbh->do(q{ + ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST + }); + $dbh->do(q{ + ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; + }); + $dbh->do(qq{ + UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC + }); + print "Upgrade to $DBversion done (Bug 9303 - relative's checkouts in the opac)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.2.5