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 / +54 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 XXXX). 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
        #Biblioitems UPDATE here
7879
        $dbh->do("ALTER TABLE biblioitems ADD COLUMN datereceived timestamp NULL");
7880
        $dbh->do("ALTER TABLE deletedbiblioitems ADD COLUMN datereceived timestamp NULL");
7881
7882
        #Fetch the UNION of items and deleteditems with the acquisitions data. It seems impossible to make this SELECT-UPDATE in SQL, so using slow Perl UPDATE statements.
7883
        my $sth_fetchall = $dbh->prepare("
7884
            SELECT dada.biblionumber, dada.ordernumber, min(dada.datereceived) as datereceived, min(dada.dateaccessioned) as dateaccessioned, dada.deleted FROM
7885
            (   (SELECT i.biblionumber, ai.ordernumber, min(ao.datereceived) as datereceived, min(i.dateaccessioned) as dateaccessioned, 0 as deleted FROM items i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber GROUP BY i.biblionumber)
7886
                UNION ALL
7887
                (SELECT i.biblionumber, ai.ordernumber, min(ao.datereceived) as datereceived, min(i.dateaccessioned) as dateaccessioned, 1 as deleted FROM deleteditems i LEFT JOIN aqorders_items ai ON i.itemnumber = ai.itemnumber LEFT JOIN aqorders ao ON ai.ordernumber = ao.ordernumber GROUP BY i.biblionumber)
7888
            ) as dada GROUP BY dada.biblionumber;");
7889
        $sth_fetchall->execute();
7890
        my $results = $sth_fetchall->fetchall_hashref('biblionumber');
7891
        foreach my $biblionumber (keys $results) { #UPDATE each biblio
7892
            my $data = $results->{$biblionumber};
7893
            my $datereceived;
7894
            if ($data->{ordernumber}) {
7895
                $datereceived = $data->{datereceived} ? $data->{datereceived} : 'NULL'; #We can have an Biblio on order, but no Items have been received yet.
7896
            }
7897
            else {
7898
                $datereceived = $data->{dateaccessioned} ? $data->{dateaccessioned} : 'NULL';
7899
            }
7900
            $dbh->do("UPDATE deletedbiblioitems bi SET bi.datereceived = '$datereceived' WHERE biblionumber = $biblionumber") if $data->{deleted};
7901
            $dbh->do("UPDATE biblioitems bi SET bi.datereceived = '$datereceived' WHERE biblionumber = $biblionumber") unless $data->{deleted};
7902
        }
7903
        #Items UPDATE here
7904
        $dbh->do("ALTER TABLE items ADD COLUMN datereceived timestamp NULL");
7905
        $dbh->do("ALTER TABLE deleteditems ADD COLUMN datereceived timestamp NULL");
7906
        $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);");
7907
        $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);");
7908
        
7909
7910
    }
7911
    print "Upgrade to $DBversion done (Bug XXXX - Add datereceived-column and and search index, for genuinely knowing the real Biblios datereceived.)\n";
7912
    SetVersion ($DBversion);
7913
}
7914
7861
$DBversion = "3.15.00.008";
7915
$DBversion = "3.15.00.008";
7862
if ( CheckVersion($DBversion) ) {
7916
if ( CheckVersion($DBversion) ) {
7863
    $dbh->do(q{
7917
    $dbh->do(q{
7864
- 

Return to bug 13707