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

(-)a/installer/data/mysql/kohastructure.sql (+2 lines)
Lines 182-187 CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Link Here
182
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
182
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
183
  `totalissues` int(10),
183
  `totalissues` int(10),
184
  `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
184
  `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
185
  `datereceived` timestamp default NULL, --When the first Item for this Biblio is received. Updated only once when the first Item has been received.
185
  PRIMARY KEY  (`biblioitemnumber`),
186
  PRIMARY KEY  (`biblioitemnumber`),
186
  KEY `bibinoidx` (`biblioitemnumber`),
187
  KEY `bibinoidx` (`biblioitemnumber`),
187
  KEY `bibnoidx` (`biblionumber`),
188
  KEY `bibnoidx` (`biblionumber`),
Lines 813-818 CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t Link Here
813
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
814
  `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
814
  `totalissues` int(10),
815
  `totalissues` int(10),
815
  `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
816
  `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
817
  `datereceived` timestamp default NULL, --When the first Item for this Biblio is received. Updated only once when the first Item has been received.
816
  PRIMARY KEY  (`biblioitemnumber`),
818
  PRIMARY KEY  (`biblioitemnumber`),
817
  KEY `bibinoidx` (`biblioitemnumber`),
819
  KEY `bibinoidx` (`biblioitemnumber`),
818
  KEY `bibnoidx` (`biblionumber`),
820
  KEY `bibnoidx` (`biblionumber`),
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +43 lines)
Lines 7858-7863 if ( CheckVersion($DBversion) ) { Link Here
7858
   SetVersion ($DBversion);
7858
   SetVersion ($DBversion);
7859
}
7859
}
7860
7860
7861
$DBversion = "3.19.00.XXX";
7862
if ( CheckVersion($DBversion) ) {
7863
    print "-    Upgrading (Bug 13707). Adding and recalculating datereceived-column for biblioitems- and items-tables. This may take a while. Sorry :(\n";
7864
    #Check if this upgrade has already been deployed, and skip this.
7865
    my $sth_desc_biblioitems = $dbh->prepare("DESC biblioitems");
7866
    $sth_desc_biblioitems->execute();
7867
    my $desc_biblioitems = $sth_desc_biblioitems->fetchall_hashref('Field');
7868
    if ($desc_biblioitems->{datereceived}) {
7869
        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".
7870
              "-    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".
7871
              "-    And rerun this updatedatabase.pl subroutine in a separate Perl-script.\n";
7872
    }
7873
    else {
7874
7875
        #Datereceived is the aqorders.datereceived, so we can copy it from there. For alive and deleted items and biblioitems.
7876
        #If no aqorders.datereceived is present, we use the dateaccessioned.
7877
        #it is important to UPDTE also deleted items/biblioitems due to statistical consistency.
7878
7879
        ###Items UPDATE here                   ###
7880
        $dbh->do("ALTER TABLE items ADD COLUMN datereceived timestamp NULL");
7881
        $dbh->do("ALTER TABLE deleteditems ADD COLUMN datereceived timestamp NULL");
7882
        $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);");
7883
        $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);");
7884
7885
        ###Biblioitems UPDATE here              ###
7886
        $dbh->do("ALTER TABLE biblioitems ADD COLUMN datereceived timestamp NULL");
7887
        $dbh->do("ALTER TABLE deletedbiblioitems ADD COLUMN datereceived timestamp NULL");
7888
7889
        #Set the biblioitems.datereceived to the smallest of items vs deleteditems or NULL
7890
        $dbh->do("UPDATE biblioitems bi
7891
                        LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber
7892
                  SET bi.datereceived =
7893
                        IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);");
7894
        #UPDATE deletedbiblioitems as well.
7895
        $dbh->do("UPDATE biblioitems bi
7896
                        LEFT JOIN items i ON i.biblionumber = bi.biblionumber LEFT JOIN deleteditems di ON di.biblionumber = bi.biblionumber
7897
                  SET bi.datereceived =
7898
                        IF(IFNULL(i.datereceived,'9999-12-31') < IFNULL(di.datereceived,'9999-12-31'), i.datereceived, di.datereceived);");
7899
    }
7900
    print "Upgrade to $DBversion done (Bug 13707 - Add datereceived-column and and search index, for genuinely knowing the real Biblios datereceived.)\n";
7901
    SetVersion ($DBversion);
7902
}
7903
7861
$DBversion = "3.15.00.008";
7904
$DBversion = "3.15.00.008";
7862
if ( CheckVersion($DBversion) ) {
7905
if ( CheckVersion($DBversion) ) {
7863
    $dbh->do(q{
7906
    $dbh->do(q{
7864
- 

Return to bug 13707