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

(-)a/installer/data/mysql/updatedatabase.pl (-49 / +35 lines)
Lines 151-157 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
151
151
152
$DBversion = "3.00.00.006";
152
$DBversion = "3.00.00.006";
153
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
153
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
154
    $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
154
    sanitize_zeo_date('issues', 'issuedate');
155
    print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
155
    print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
156
    SetVersion ($DBversion);
156
    SetVersion ($DBversion);
157
}
157
}
Lines 1949-1955 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
1949
1949
1950
    print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
1950
    print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
1951
    $dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
1951
    $dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
1952
    $dbh->do("UPDATE subscriptionhistory SET enddate=NULL WHERE enddate='0000-00-00'");
1952
1953
    sanitize_zeo_date('subscriptionhistory', 'enddate');
1954
1953
    SetVersion ($DBversion);
1955
    SetVersion ($DBversion);
1954
}
1956
}
1955
1957
Lines 2903-2909 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
2903
    # match the new type of the corresponding field
2905
    # match the new type of the corresponding field
2904
    $dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)');
2906
    $dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)');
2905
    # System did not ensure budgetdate was valid historically
2907
    # System did not ensure budgetdate was valid historically
2906
    $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|);
2908
    sanitize_zeo_date('fundmapping', 'budgetdate');
2909
    $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate IS NULL|);
2907
    # We save the map in fundmapping in case you need later processing
2910
    # We save the map in fundmapping in case you need later processing
2908
    $dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|);
2911
    $dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|);
2909
    # these can speed processing up
2912
    # these can speed processing up
Lines 3626-3632 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
3626
3629
3627
$DBversion = "3.01.00.130";
3630
$DBversion = "3.01.00.130";
3628
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3631
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3629
    $dbh->do("UPDATE reserves SET expirationdate = NULL WHERE expirationdate = '0000-00-00'");
3632
    sanitize_zero_date('reserves', 'expirationdate');
3630
    print "Upgrade to $DBversion done (change reserves.expirationdate values of 0000-00-00 to NULL (bug 1532)\n";
3633
    print "Upgrade to $DBversion done (change reserves.expirationdate values of 0000-00-00 to NULL (bug 1532)\n";
3631
    SetVersion ($DBversion);
3634
    SetVersion ($DBversion);
3632
}
3635
}
Lines 4574-4580 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
4574
4577
4575
$DBversion = "3.07.00.002";
4578
$DBversion = "3.07.00.002";
4576
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4579
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4577
    $dbh->do("UPDATE borrowers SET debarred=NULL WHERE debarred='0000-00-00';");
4580
    sanitize_zero_date('borrowers', 'debarred');
4578
    print "Setting NULL to debarred where 0000-00-00 is stored (bug 7272)\n";
4581
    print "Setting NULL to debarred where 0000-00-00 is stored (bug 7272)\n";
4579
    SetVersion($DBversion);
4582
    SetVersion($DBversion);
4580
}
4583
}
Lines 7603-7608 if ( CheckVersion($DBversion) ) { Link Here
7603
        SET firstacquidate = ?
7606
        SET firstacquidate = ?
7604
        WHERE subscriptionid = ?
7607
        WHERE subscriptionid = ?
7605
    |);
7608
    |);
7609
7610
    sanitize_zeo_date('subscription', 'firstacquidate');
7606
    my $get_subscriptions_sth = $dbh->prepare(qq|
7611
    my $get_subscriptions_sth = $dbh->prepare(qq|
7607
        SELECT subscriptionid, startdate
7612
        SELECT subscriptionid, startdate
7608
        FROM subscription
7613
        FROM subscription
Lines 10635-10648 if ( CheckVersion($DBversion) ) { Link Here
10635
10640
10636
$DBversion = "3.21.00.009";
10641
$DBversion = "3.21.00.009";
10637
if ( CheckVersion($DBversion) ) {
10642
if ( CheckVersion($DBversion) ) {
10638
    eval {
10643
10639
        local $dbh->{PrintError} = 0;
10644
    sanitize_zeo_date('aqorders', 'datecancellationprinted');
10640
        $dbh->do(q|
10645
10641
            UPDATE aqorders SET orderstatus='cancelled'
10646
    $dbh->do(q|
10642
            WHERE (datecancellationprinted IS NOT NULL OR
10647
        UPDATE aqorders SET orderstatus='cancelled'
10643
                   datecancellationprinted<>'0000-00-00');
10648
        WHERE (datecancellationprinted IS NOT NULL)
10644
        |);
10649
    |);
10645
    };
10650
10646
    print "Upgrade to $DBversion done (Bug 13993: Correct orderstatus for transferred orders)\n";
10651
    print "Upgrade to $DBversion done (Bug 13993: Correct orderstatus for transferred orders)\n";
10647
    SetVersion($DBversion);
10652
    SetVersion($DBversion);
10648
}
10653
}
Lines 10873-10893 if ( CheckVersion($DBversion) ) { Link Here
10873
10878
10874
$DBversion = "3.21.00.023";
10879
$DBversion = "3.21.00.023";
10875
if ( CheckVersion($DBversion) ) {
10880
if ( CheckVersion($DBversion) ) {
10876
    eval {
10881
10877
        local $dbh->{PrintError} = 0;
10882
    sanitize_zeo_date('borrowers', 'debarred');
10878
        $dbh->do(q{
10883
    sanitize_zeo_date('borrowers', 'dateexpiry');
10879
            UPDATE borrowers SET debarred=NULL WHERE debarred='0000-00-00'
10884
    sanitize_zeo_date('borrowers', 'dateofbirth');
10880
        });
10885
    sanitize_zeo_date('borrowers', 'dateenrolled');
10881
        $dbh->do(q{
10882
            UPDATE borrowers SET dateexpiry=NULL where dateexpiry='0000-00-00'
10883
        });
10884
        $dbh->do(q{
10885
            UPDATE borrowers SET dateofbirth=NULL where dateofbirth='0000-00-00'
10886
        });
10887
        $dbh->do(q{
10888
            UPDATE borrowers SET dateenrolled=NULL where dateenrolled='0000-00-00'
10889
        });
10890
    };
10891
10886
10892
    print "Upgrade to $DBversion done (Bug 14717: Prevent 0000-00-00 dates in patron data)\n";
10887
    print "Upgrade to $DBversion done (Bug 14717: Prevent 0000-00-00 dates in patron data)\n";
10893
    SetVersion($DBversion);
10888
    SetVersion($DBversion);
Lines 21697-21722 $DBversion = '19.12.00.076'; Link Here
21697
if( CheckVersion( $DBversion ) ) {
21692
if( CheckVersion( $DBversion ) ) {
21698
    my @warnings;
21693
    my @warnings;
21699
21694
21700
    eval {
21695
    sanitize_zeo_date('serial', 'planneddate');
21701
        local $dbh->{PrintError} = 0;
21696
    sanitize_zeo_date('serial', 'publisheddate');
21702
        $dbh->do(q|
21697
    sanitize_zeo_date('serial', 'claimdate');
21703
            UPDATE serial
21704
            SET planneddate = NULL
21705
            WHERE planneddate = '0000-00-00'
21706
        |);
21707
21708
        $dbh->do(q|
21709
            UPDATE serial
21710
            SET publisheddate = NULL
21711
            WHERE publisheddate = '0000-00-00'
21712
        |);
21713
21714
        $dbh->do(q|
21715
            UPDATE serial
21716
            SET claimdate = NULL
21717
            WHERE claimdate = '0000-00-00'
21718
        |);
21719
    };
21720
21698
21721
    $dbh->do(q|
21699
    $dbh->do(q|
21722
        ALTER TABLE serial
21700
        ALTER TABLE serial
Lines 23680-23683 sub CheckVersion { Link Here
23680
    }
23658
    }
23681
}
23659
}
23682
23660
23661
sub sanitize_zero_date {
23662
    my ( $table_name, $column_name ) = @_;
23663
    $dbh->do(qq|
23664
        UPDATE $table_name
23665
        SET $column_name = NULL
23666
        WHERE CAST($column_name AS CHAR(10)) = '0000-00-00';
23667
    |);
23668
}
23669
23683
exit;
23670
exit;
23684
- 

Return to bug 26997