From 31f7efbcc5867dfa4e13d60f33932f2c01c3dcec Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 10 Nov 2014 14:17:40 +0100 Subject: [PATCH] Bug 10459: Borrowers should have a timestamp Content-Type: text/plain; charset=utf-8 This patch adds a timestamp column to the borrowers table in kohastructure and updatedatabase. (And also to the deletedborrowers table.) A timestamp may be useful in synchronizing with external systems (among other reasons). NOTE: In order to prevent ambigous column-problems with multiple table queries including more than one timestamps, I name this column bortimestamp instead of just timestamp. This name will be used only in table borrowers and deletedborrowers. Test plan: Run updatestructure on an existing database, or install a new one. Verify that the borrowers table has a timestamp now. Signed-off-by: Marcel de Rooy Tested updatestructure and running kohastructure.sql. Passed t/db_dependent/Members.t. --- installer/data/mysql/kohastructure.sql | 2 ++ installer/data/mysql/updatedatabase.pl | 13 +++++++++++++ 2 files changed, 15 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 8c777f7..bd279f0 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 + `bortimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- time of last change could be useful for synchronization with external systems (among others) UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), @@ -876,6 +877,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`), + `bortimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- time of last change could be useful for synchronization with external systems (among others) KEY borrowernumber (borrowernumber), KEY `cardnumber` (`cardnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 491c2b8..f531394 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9470,6 +9470,19 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } + +$DBversion = "3.17.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q| +ALTER TABLE borrowers ADD COLUMN bortimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER privacy; + |); + $dbh->do(q| +ALTER TABLE deletedborrowers ADD COLUMN bortimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER privacy; + |); + print "Upgrade to $DBversion done (Bug 10459: Borrowers should have a timestamp)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.7.6