Bugzilla – Attachment 35944 Details for
Bug 13707
Adding a new column datereceived for items and biblioitems
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 13707 - 1. Adding a new column datereceived for items and biblioitems
Bug-13707---1-Adding-a-new-column-datereceived-for.patch (text/plain), 6.40 KB, created by
Olli-Antti Kivilahti
on 2015-02-16 15:30:10 UTC
(
hide
)
Description:
Bug 13707 - 1. Adding a new column datereceived for items and biblioitems
Filename:
MIME Type:
Creator:
Olli-Antti Kivilahti
Created:
2015-02-16 15:30:10 UTC
Size:
6.40 KB
patch
obsolete
>From 456f2600243fac89ecf22b2a48ecd4bf4954cd92 Mon Sep 17 00:00:00 2001 >From: Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi> >Date: Fri, 13 Feb 2015 13:58:02 +0200 >Subject: [PATCH] Bug 13707 - 1. Adding a new column datereceived for items > and biblioitems > >This column tells when the Item or Biblio has been received for the first time >into a library. >Date-of-acquisition (dateaccessioned) answers, when the Item has been ordered. >Date-of-receival (datereceived) answers, when the Item has arrived to the >library or when the first Item for the Biblio has for the first time arrived to >the library. >This is very useful regarding statistics of acquisitions if they are based on >the date received instead of the date acquired. >Also other cool stuff can be built on top of it, like better searches for new >material. > >This patch adds new DB columns items.datereceived, biblioitems.datereceived, >deleteditems.datereceived, deletedbiblioitems.datereceived >and populates those columns from the acorders.datereceived if the Item has been >acquired using the acquisitions module, or from the >items.dateaccessioned-column if no acquisitions module has been used. >Biblioitems.datereceived is always the oldest timestamp from all the Items the >Biblio has. >--- > installer/data/mysql/kohastructure.sql | 2 ++ > installer/data/mysql/updatedatabase.pl | 43 ++++++++++++++++++++++++++++++++ > 2 files changed, 45 insertions(+) > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index cd83f99..fa1f834 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -182,6 +182,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in > `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a) > `totalissues` int(10), > `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML >+ `datereceived` timestamp default NULL, --When the first Item for this Biblio is received. Updated only once when the first Item has been received. > PRIMARY KEY (`biblioitemnumber`), > KEY `bibinoidx` (`biblioitemnumber`), > KEY `bibnoidx` (`biblionumber`), >@@ -813,6 +814,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t > `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a) > `totalissues` int(10), > `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML >+ `datereceived` timestamp default NULL, --When the first Item for this Biblio is received. Updated only once when the first Item has been received. > PRIMARY KEY (`biblioitemnumber`), > KEY `bibinoidx` (`biblioitemnumber`), > KEY `bibnoidx` (`biblionumber`), >diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl >index dab1c62..ea7961d 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -7858,6 +7858,49 @@ if ( CheckVersion($DBversion) ) { > SetVersion ($DBversion); > } > >+$DBversion = "3.19.00.XXX"; >+if ( CheckVersion($DBversion) ) { >+ print "- Upgrading (Bug 13707). Adding and recalculating datereceived-column for biblioitems- and items-tables. This may take a while. Sorry :(\n"; >+ #Check if this upgrade has already been deployed, and skip this. >+ my $sth_desc_biblioitems = $dbh->prepare("DESC biblioitems"); >+ $sth_desc_biblioitems->execute(); >+ my $desc_biblioitems = $sth_desc_biblioitems->fetchall_hashref('Field'); >+ if ($desc_biblioitems->{datereceived}) { >+ print "- It looks like this DB change has already been applied. If you want to rebuild datereceived-columns for items and biblioitems, run the following command:\n". >+ "- ALTER TABLE biblioitems DROP column datereceived; ALTER TABLE deletedbiblioitems DROP column datereceived; ALTER TABLE items DROP column datereceived; ALTER TABLE deleteditems DROP column datereceived;\n". >+ "- And rerun this updatedatabase.pl subroutine in a separate Perl-script.\n"; >+ } >+ else { >+ >+ #Datereceived is the aqorders.datereceived, so we can copy it from there. For alive and deleted items and biblioitems. >+ #If no aqorders.datereceived is present, we use the dateaccessioned. >+ #it is important to UPDTE also deleted items/biblioitems due to statistical consistency. >+ >+ ###Items UPDATE here ### >+ $dbh->do("ALTER TABLE items ADD COLUMN datereceived timestamp NULL"); >+ $dbh->do("ALTER TABLE deleteditems ADD COLUMN datereceived timestamp NULL"); >+ $dbh->do("UPDATE items i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber SET i.datereceived = IF(ai.ordernumber IS NOT NULL, ao.datereceived, i.dateaccessioned);"); >+ $dbh->do("UPDATE deleteditems i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber SET i.datereceived = IF(ai.ordernumber IS NOT NULL, ao.datereceived, i.dateaccessioned);"); >+ >+ ###Biblioitems UPDATE here ### >+ $dbh->do("ALTER TABLE biblioitems ADD COLUMN datereceived timestamp NULL"); >+ $dbh->do("ALTER TABLE deletedbiblioitems ADD COLUMN datereceived timestamp NULL"); >+ >+ #Set the biblioitems.datereceived to the smallest of items vs deleteditems or NULL >+ $dbh->do("UPDATE biblioitems bi >+ LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber >+ SET bi.datereceived = >+ IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);"); >+ #UPDATE deletedbiblioitems as well. >+ $dbh->do("UPDATE biblioitems bi >+ LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber >+ SET bi.datereceived = >+ IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);"); >+ } >+ print "Upgrade to $DBversion done (Bug 13707 - Add datereceived-column and and search index, for genuinely knowing the real Biblios datereceived.)\n"; >+ SetVersion ($DBversion); >+} >+ > $DBversion = "3.15.00.008"; > if ( CheckVersion($DBversion) ) { > $dbh->do(q{ >-- >1.7.9.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 13707
:
35908
|
35909
|
35910
|
35917
|
35933
|
35944
|
35945
|
35946
|
35966
|
35967
|
35968
|
35970
|
42391
|
42394
|
64343
|
64344