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

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

Return to bug 18050