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

(-)a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl (-3 / +18 lines)
Lines 6-18 if( CheckVersion( $DBversion ) ) { Link Here
6
6
7
    |);
7
    |);
8
8
9
    my $message = "";
10
11
    if($count_missing_budget->[0] > 0) {
12
        $dbh->do(q{
13
            CREATE TABLE _bug_18050_aqbudgets AS
14
            SELECT * FROM aqbudgets ab WHERE NOT EXISTS
15
                (SELECT * FROM aqbudgetperiods abp WHERE abp.budget_period_id = ab.budget_period_id)
16
        });
17
18
        $dbh->do(q{
19
            UPDATE aqbudgets ab SET budget_period_id = NULL
20
            WHERE NOT EXISTS
21
                (SELECT * FROM aqbudgetperiods abp WHERE abp.budget_period_id = ab.budget_period_id)
22
        });
23
24
        $message = "There are $count_missing_budget->[0] funds in your database that are not linked to a valid budget. Setting invalid budget id (budget_period_id) to null. The table _bug_18050_aqbudgets was created with original data. Please check that table and place valid ids in aqbudget table as soon as possible."
25
    }
26
9
    if ( !foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) && $count_missing_budget->[0] == 0 ) {
27
    if ( !foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) && $count_missing_budget->[0] == 0 ) {
10
        $dbh->do(q|
28
        $dbh->do(q|
11
            ALTER TABLE aqbudgets ADD CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON UPDATE CASCADE ON DELETE CASCADE
29
            ALTER TABLE aqbudgets ADD CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON UPDATE CASCADE ON DELETE CASCADE
12
        |);
30
        |);
13
        NewVersion( $DBversion, 18050, "Add FK constraint on aqbudgets.budget_period_id");
31
        NewVersion( $DBversion, 18050, "Add FK constraint on aqbudgets.budget_period_id");
14
    } elsif ( $count_missing_budget->[0] > 0 )  {
15
        NewVersion( $DBversion, 18050, "FK constraint on aqbudgets.budget_period_id couldn't be added. There are $count_missing_budget->[0] funds in your database that are not linked to a valid budget.");
16
    } else {
32
    } else {
17
        NewVersion( $DBversion, 18050, "FK constraint on aqbudgets.budget already exists");
33
        NewVersion( $DBversion, 18050, "FK constraint on aqbudgets.budget already exists");
18
    }
34
    }
19
- 

Return to bug 18050