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

(-)a/installer/data/mysql/updatedatabase.pl (-118 / +121 lines)
Lines 9677-9682 if ( CheckVersion($DBversion) ) { Link Here
9677
    SetVersion($DBversion);
9677
    SetVersion($DBversion);
9678
}
9678
}
9679
9679
9680
$DBversion = "3.19.00.007";
9681
if ( CheckVersion($DBversion) ) {
9682
    my $orphan_budgets = $dbh->selectall_arrayref(q|
9683
        SELECT budget_id, budget_name, budget_code
9684
        FROM aqbudgets
9685
        WHERE   budget_parent_id IS NOT NULL
9686
            AND budget_parent_id NOT IN (
9687
                SELECT DISTINCT budget_id FROM aqbudgets
9688
            )
9689
    |, { Slice => {} } );
9690
9691
    if ( @$orphan_budgets ) {
9692
        for my $b ( @$orphan_budgets ) {
9693
            print "Fund $b->{budget_name} (code:$b->{budget_code}, id:$b->{budget_id}) does not have a parent, it may cause problem\n";
9694
        }
9695
        print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: FAIL)\n";
9696
    } else {
9697
        print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: OK)\n";
9698
    }
9699
    SetVersion($DBversion);
9700
}
9701
9702
$DBversion = "3.19.00.008";
9703
if ( CheckVersion($DBversion) ) {
9704
    my $number_of_orders_not_linked = $dbh->selectcol_arrayref(q|
9705
        SELECT COUNT(*)
9706
        FROM aqorders o
9707
        WHERE NOT EXISTS (
9708
            SELECT NULL
9709
            FROM aqbudgets b
9710
            WHERE b.budget_id = o.budget_id
9711
        );
9712
    |);
9713
9714
    if ( $number_of_orders_not_linked->[0] > 0 ) {
9715
        $dbh->do(q|
9716
            INSERT INTO aqbudgetperiods(budget_period_startdate, budget_period_enddate, budget_period_active, budget_period_description, budget_period_total) VALUES ( CAST(NOW() AS date), CAST(NOW() AS date), 0, "WARNING: This budget has been automatically created by the updatedatabase script, please see bug 12601 for more information", 0)
9717
        |);
9718
        my $budget_period_id = $dbh->last_insert_id( undef, undef, 'aqbudgetperiods', undef );
9719
        $dbh->do(qq|
9720
            INSERT INTO aqbudgets(budget_code, budget_name, budget_amount, budget_period_id) VALUES ( "BACKUP_TMP", "WARNING: fund created by the updatedatabase script, please see bug 12601", 0, $budget_period_id );
9721
        |);
9722
        my $budget_id = $dbh->last_insert_id( undef, undef, 'aqbudgets', undef );
9723
        $dbh->do(qq|
9724
            UPDATE aqorders o
9725
            SET budget_id = $budget_id
9726
            WHERE NOT EXISTS (
9727
                SELECT NULL
9728
                FROM aqbudgets b
9729
                WHERE b.budget_id = o.budget_id
9730
            )
9731
        |);
9732
    }
9733
9734
    $dbh->do(q|
9735
        ALTER TABLE aqorders
9736
        ADD CONSTRAINT aqorders_budget_id_fk FOREIGN KEY (budget_id) REFERENCES aqbudgets(budget_id) ON DELETE CASCADE ON UPDATE CASCADE
9737
    |);
9738
9739
    print "Upgrade to $DBversion done (Bug 12601 - Add new foreign key aqorders.budget_id" . ( ( $number_of_orders_not_linked->[0] > 0 )  ? ' WARNING: temporary budget and fund have been created (search for "BACKUP_TMP"). At least one of your order was not linked to a budget' : '' ) . ")\n";
9740
    SetVersion($DBversion);
9741
}
9742
9743
$DBversion = "3.19.00.009";
9744
if ( CheckVersion($DBversion) ) {
9745
    $dbh->do(q|
9746
        UPDATE suggestions s SET s.budgetid = NULL
9747
        WHERE NOT EXISTS (
9748
            SELECT NULL
9749
            FROM aqbudgets b
9750
            WHERE b.budget_id = s.budgetid
9751
        );
9752
    |);
9753
9754
    $dbh->do(q|
9755
        ALTER TABLE suggestions
9756
        ADD CONSTRAINT suggestions_budget_id_fk FOREIGN KEY (budgetid) REFERENCES aqbudgets(budget_id) ON DELETE SET NULL ON UPDATE CASCADE
9757
    |);
9758
9759
    print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid)\n";
9760
    SetVersion($DBversion);
9761
}
9762
9763
$DBversion = "3.19.00.010";
9764
if ( CheckVersion($DBversion) ) {
9765
    $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('SessionRestrictionByIP','1','Check for Change in  Remote IP address for Session Security. Disable when remote ip address changes frequently.','','YesNo')");
9766
    print "Upgrade to $DBversion done (Bug 5511 - SessionRestrictionByIP)\n";
9767
    SetVersion ($DBversion);
9768
}
9769
9770
$DBversion = "3.19.00.011";
9771
if ( CheckVersion($DBversion) ) {
9772
    $dbh->do(q|
9773
        INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES
9774
        (20, 'lists', 'Lists', 0)
9775
    |);
9776
    $dbh->do(q|
9777
        INSERT INTO permissions (module_bit, code, description) VALUES
9778
        (20, 'delete_public_lists', 'Delete public lists')
9779
    |);
9780
    print "Upgrade to $DBversion done (Bug 13417: Add permission to delete public lists)\n";
9781
    SetVersion ($DBversion);
9782
}
9783
9784
$DBversion = "3.19.00.012";
9785
if(CheckVersion($DBversion)) {
9786
    $dbh->do(q{
9787
        ALTER TABLE biblioitems MODIFY COLUMN marcxml longtext
9788
    });
9789
9790
    $dbh->do(q{
9791
        ALTER TABLE deletedbiblioitems MODIFY COLUMN marcxml longtext
9792
    });
9793
9794
    print "Upgrade to $DBversion done (Bug 13523 - Remove NOT NULL restriction on field marcxml due to mysql STRICT_TRANS_TABLES)\n";
9795
    SetVersion($DBversion);
9796
}
9797
9680
$DBversion = "3.19.00.XXX";
9798
$DBversion = "3.19.00.XXX";
9681
if ( CheckVersion($DBversion) ) {
9799
if ( CheckVersion($DBversion) ) {
9682
    $dbh->do(q{
9800
    $dbh->do(q{
Lines 9859-9985 if ( CheckVersion($DBversion) ) { Link Here
9859
        }
9977
        }
9860
    }
9978
    }
9861
9979
9862
    print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n";
9980
    $dbh->do("DROP TABLE accountoffsets");
9863
    SetVersion ($DBversion);
9981
    $dbh->do("DROP TABLE accountlines");
9864
}
9865
9866
$DBversion = "3.19.00.007";
9867
if ( CheckVersion($DBversion) ) {
9868
    my $orphan_budgets = $dbh->selectall_arrayref(q|
9869
        SELECT budget_id, budget_name, budget_code
9870
        FROM aqbudgets
9871
        WHERE   budget_parent_id IS NOT NULL
9872
            AND budget_parent_id NOT IN (
9873
                SELECT DISTINCT budget_id FROM aqbudgets
9874
            )
9875
    |, { Slice => {} } );
9876
9877
    if ( @$orphan_budgets ) {
9878
        for my $b ( @$orphan_budgets ) {
9879
            print "Fund $b->{budget_name} (code:$b->{budget_code}, id:$b->{budget_id}) does not have a parent, it may cause problem\n";
9880
        }
9881
        print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: FAIL)\n";
9882
    } else {
9883
        print "Upgrade to $DBversion done (Bug 12905: Check budget integrity: OK)\n";
9884
    }
9885
    SetVersion($DBversion);
9886
}
9887
9888
$DBversion = "3.19.00.008";
9889
if ( CheckVersion($DBversion) ) {
9890
    my $number_of_orders_not_linked = $dbh->selectcol_arrayref(q|
9891
        SELECT COUNT(*)
9892
        FROM aqorders o
9893
        WHERE NOT EXISTS (
9894
            SELECT NULL
9895
            FROM aqbudgets b
9896
            WHERE b.budget_id = o.budget_id
9897
        );
9898
    |);
9899
9900
    if ( $number_of_orders_not_linked->[0] > 0 ) {
9901
        $dbh->do(q|
9902
            INSERT INTO aqbudgetperiods(budget_period_startdate, budget_period_enddate, budget_period_active, budget_period_description, budget_period_total) VALUES ( CAST(NOW() AS date), CAST(NOW() AS date), 0, "WARNING: This budget has been automatically created by the updatedatabase script, please see bug 12601 for more information", 0)
9903
        |);
9904
        my $budget_period_id = $dbh->last_insert_id( undef, undef, 'aqbudgetperiods', undef );
9905
        $dbh->do(qq|
9906
            INSERT INTO aqbudgets(budget_code, budget_name, budget_amount, budget_period_id) VALUES ( "BACKUP_TMP", "WARNING: fund created by the updatedatabase script, please see bug 12601", 0, $budget_period_id );
9907
        |);
9908
        my $budget_id = $dbh->last_insert_id( undef, undef, 'aqbudgets', undef );
9909
        $dbh->do(qq|
9910
            UPDATE aqorders o
9911
            SET budget_id = $budget_id
9912
            WHERE NOT EXISTS (
9913
                SELECT NULL
9914
                FROM aqbudgets b
9915
                WHERE b.budget_id = o.budget_id
9916
            )
9917
        |);
9918
    }
9919
9920
    $dbh->do(q|
9921
        ALTER TABLE aqorders
9922
        ADD CONSTRAINT aqorders_budget_id_fk FOREIGN KEY (budget_id) REFERENCES aqbudgets(budget_id) ON DELETE CASCADE ON UPDATE CASCADE
9923
    |);
9924
9925
    print "Upgrade to $DBversion done (Bug 12601 - Add new foreign key aqorders.budget_id" . ( ( $number_of_orders_not_linked->[0] > 0 )  ? ' WARNING: temporary budget and fund have been created (search for "BACKUP_TMP"). At least one of your order was not linked to a budget' : '' ) . ")\n";
9926
    SetVersion($DBversion);
9927
}
9928
9929
$DBversion = "3.19.00.009";
9930
if ( CheckVersion($DBversion) ) {
9931
    $dbh->do(q|
9932
        UPDATE suggestions s SET s.budgetid = NULL
9933
        WHERE NOT EXISTS (
9934
            SELECT NULL
9935
            FROM aqbudgets b
9936
            WHERE b.budget_id = s.budgetid
9937
        );
9938
    |);
9939
9940
    $dbh->do(q|
9941
        ALTER TABLE suggestions
9942
        ADD CONSTRAINT suggestions_budget_id_fk FOREIGN KEY (budgetid) REFERENCES aqbudgets(budget_id) ON DELETE SET NULL ON UPDATE CASCADE
9943
    |);
9944
9982
9945
    print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid)\n";
9983
    print "Upgrade to $DBversion done ( Bug 6427 - Rewrite of the accounts system )\n";
9946
    SetVersion($DBversion);
9947
}
9948
9949
$DBversion = "3.19.00.010";
9950
if ( CheckVersion($DBversion) ) {
9951
    $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('SessionRestrictionByIP','1','Check for Change in  Remote IP address for Session Security. Disable when remote ip address changes frequently.','','YesNo')");
9952
    print "Upgrade to $DBversion done (Bug 5511 - SessionRestrictionByIP)\n";
9953
    SetVersion ($DBversion);
9954
}
9955
9956
$DBversion = "3.19.00.011";
9957
if ( CheckVersion($DBversion) ) {
9958
    $dbh->do(q|
9959
        INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES
9960
        (20, 'lists', 'Lists', 0)
9961
    |);
9962
    $dbh->do(q|
9963
        INSERT INTO permissions (module_bit, code, description) VALUES
9964
        (20, 'delete_public_lists', 'Delete public lists')
9965
    |);
9966
    print "Upgrade to $DBversion done (Bug 13417: Add permission to delete public lists)\n";
9967
    SetVersion ($DBversion);
9984
    SetVersion ($DBversion);
9968
}
9985
}
9969
9986
9970
$DBversion = "3.19.00.012";
9971
if(CheckVersion($DBversion)) {
9972
    $dbh->do(q{
9973
        ALTER TABLE biblioitems MODIFY COLUMN marcxml longtext
9974
    });
9975
9976
    $dbh->do(q{
9977
        ALTER TABLE deletedbiblioitems MODIFY COLUMN marcxml longtext
9978
    });
9979
9980
    print "Upgrade to $DBversion done (Bug 13523 - Remove NOT NULL restriction on field marcxml due to mysql STRICT_TRANS_TABLES)\n";
9981
    SetVersion($DBversion);
9982
}
9983
9987
9984
=head1 FUNCTIONS
9988
=head1 FUNCTIONS
9985
9989
9986
- 

Return to bug 6427