|
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 |
- |
|
|