From f6f72be37418bff99928f0c4855fd65d1d5813d6 Mon Sep 17 00:00:00 2001
From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Date: Mon, 10 Nov 2014 14:17:40 +0100
Subject: [PATCH] Bug 10459: Borrowers should have a timestamp

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).

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 <m.de.rooy@rijksmuseum.nl>
Tested updatestructure and running kohastructure.sql.
Passed t/db_dependent/Members.t.

updatedatabase.pl did not apply. I edited and then run it. Columns were added as expected.
Signed-off-by: Marc Veron <veron@veron.ch>

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
---
 installer/data/mysql/kohastructure.sql |  2 ++
 installer/data/mysql/updatedatabase.pl | 12 ++++++++++++
 2 files changed, 14 insertions(+)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 42bff46..b6b34f7 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -618,6 +618,7 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower
   `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
   `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history  KEY `borrowernumber` (`borrowernumber`),
   `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
+  `timestamp` 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`),
   KEY `sms_provider_id` (`sms_provider_id`)
@@ -1623,6 +1624,7 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons
   `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
   `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
   `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
+  `timestamp` 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`),
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 9563a58..bd96f16 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -12593,6 +12593,18 @@ if ( CheckVersion($DBversion) ) {
     SetVersion($DBversion);
 }
 
+$DBversion = "3.23.00.XXX";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do(q|
+ALTER TABLE borrowers ADD COLUMN timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER privacy;
+    |);
+    $dbh->do(q|
+ALTER TABLE deletedborrowers ADD COLUMN timestamp 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);
+}
+
 
 # DEVELOPER PROCESS, search for anything to execute in the db_update directory
 # SEE bug 13068
-- 
2.1.4