View | Details | Raw Unified | Return to bug 13707
Collapse All | Expand All

(-)a/installer/data/mysql/kohastructure.sql (+4 lines)
Lines 183-188 CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Link Here
183
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
183
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
184
  `totalissues` int(10),
184
  `totalissues` int(10),
185
  `marcxml` longtext, -- full bibliographic MARC record in MARCXML
185
  `marcxml` longtext, -- full bibliographic MARC record in MARCXML
186
  `datereceived` timestamp default NULL, --When the first Item for this Biblio is received. Updated only once when the first Item has been received.
186
  PRIMARY KEY  (`biblioitemnumber`),
187
  PRIMARY KEY  (`biblioitemnumber`),
187
  KEY `bibinoidx` (`biblioitemnumber`),
188
  KEY `bibinoidx` (`biblioitemnumber`),
188
  KEY `bibnoidx` (`biblionumber`),
189
  KEY `bibnoidx` (`biblionumber`),
Lines 815-820 CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t Link Here
815
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
816
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
816
  `totalissues` int(10),
817
  `totalissues` int(10),
817
  `marcxml` longtext, -- full bibliographic MARC record in MARCXML
818
  `marcxml` longtext, -- full bibliographic MARC record in MARCXML
819
  `datereceived` timestamp default NULL, -- When the first Item for this Biblio is received. Updated only once when the first Item has been received.
818
  PRIMARY KEY  (`biblioitemnumber`),
820
  PRIMARY KEY  (`biblioitemnumber`),
819
  KEY `bibinoidx` (`biblioitemnumber`),
821
  KEY `bibinoidx` (`biblioitemnumber`),
820
  KEY `bibnoidx` (`biblionumber`),
822
  KEY `bibnoidx` (`biblionumber`),
Lines 911-916 CREATE TABLE `deleteditems` ( Link Here
911
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
913
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
912
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
914
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
913
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
915
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
916
  `datereceived` timestamp default NULL, -- When this Item was received.
914
  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
917
  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
915
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
918
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
916
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
919
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
Lines 1213-1218 CREATE TABLE `items` ( -- holdings/item information Link Here
1213
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
1216
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
1214
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
1217
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
1215
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
1218
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
1219
  `datereceived` timestamp default NULL, -- When this Item was received.
1216
  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
1220
  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
1217
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
1221
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
1218
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
1222
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +43 lines)
Lines 7904-7909 if ( CheckVersion($DBversion) ) { Link Here
7904
   SetVersion ($DBversion);
7904
   SetVersion ($DBversion);
7905
}
7905
}
7906
7906
7907
$DBversion = "3.19.00.XXX";
7908
if ( CheckVersion($DBversion) ) {
7909
    print "-    Upgrading (Bug 13707). Adding and recalculating datereceived-column for biblioitems- and items-tables. This may take a while. Sorry :(\n";
7910
    #Check if this upgrade has already been deployed, and skip this.
7911
    my $sth_desc_biblioitems = $dbh->prepare("DESC biblioitems");
7912
    $sth_desc_biblioitems->execute();
7913
    my $desc_biblioitems = $sth_desc_biblioitems->fetchall_hashref('Field');
7914
    if ($desc_biblioitems->{datereceived}) {
7915
        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".
7916
              "-    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".
7917
              "-    And rerun this updatedatabase.pl subroutine in a separate Perl-script.\n";
7918
    }
7919
    else {
7920
7921
        #Datereceived is the aqorders.datereceived, so we can copy it from there. For alive and deleted items and biblioitems.
7922
        #If no aqorders.datereceived is present, we use the dateaccessioned.
7923
        #it is important to UPDTE also deleted items/biblioitems due to statistical consistency.
7924
7925
        ###Items UPDATE here                   ###
7926
        $dbh->do("ALTER TABLE items ADD COLUMN datereceived timestamp NULL");
7927
        $dbh->do("ALTER TABLE deleteditems ADD COLUMN datereceived timestamp NULL");
7928
        $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);");
7929
        $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);");
7930
7931
        ###Biblioitems UPDATE here              ###
7932
        $dbh->do("ALTER TABLE biblioitems ADD COLUMN datereceived timestamp NULL");
7933
        $dbh->do("ALTER TABLE deletedbiblioitems ADD COLUMN datereceived timestamp NULL");
7934
7935
        #Set the biblioitems.datereceived to the smallest of items vs deleteditems or NULL
7936
        $dbh->do("UPDATE biblioitems bi
7937
                        LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber
7938
                  SET bi.datereceived =
7939
                        IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);");
7940
        #UPDATE deletedbiblioitems as well.
7941
        $dbh->do("UPDATE biblioitems bi
7942
                        LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber
7943
                  SET bi.datereceived =
7944
                        IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);");
7945
    }
7946
    print "Upgrade to $DBversion done (Bug 13707 - Add datereceived-column and and search index, for genuinely knowing the real Biblios datereceived.)\n";
7947
    SetVersion ($DBversion);
7948
}
7949
7907
$DBversion = "3.15.00.008";
7950
$DBversion = "3.15.00.008";
7908
if ( CheckVersion($DBversion) ) {
7951
if ( CheckVersion($DBversion) ) {
7909
    $dbh->do(q{
7952
    $dbh->do(q{
7910
- 

Return to bug 13707