|
Lines 16-21
return {
Link Here
|
| 16 |
} |
16 |
} |
| 17 |
); |
17 |
); |
| 18 |
say $out "Added column 'accountlines.old_issue_id'"; |
18 |
say $out "Added column 'accountlines.old_issue_id'"; |
|
|
19 |
} |
| 20 |
|
| 21 |
unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_old_issues' ) ) { |
| 19 |
|
22 |
|
| 20 |
$dbh->do( |
23 |
$dbh->do( |
| 21 |
q{ |
24 |
q{ |
|
Lines 28-75
return {
Link Here
|
| 28 |
} |
31 |
} |
| 29 |
); |
32 |
); |
| 30 |
say $out "Added constraint 'accountlines.old_issues'"; |
33 |
say $out "Added constraint 'accountlines.old_issues'"; |
|
|
34 |
} |
| 35 |
|
| 36 |
# Add constraint for issue_id |
| 37 |
unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) ) { |
| 31 |
|
38 |
|
| 32 |
$dbh->do( |
39 |
$dbh->do( |
| 33 |
q{ |
40 |
q{ |
| 34 |
UPDATE |
41 |
UPDATE |
| 35 |
accountlines a |
42 |
accountlines a |
| 36 |
LEFT JOIN old_issues o ON (a.issue_id = o.issue_id) |
43 |
LEFT JOIN old_issues o ON (a.issue_id = o.issue_id) |
| 37 |
SET |
44 |
SET |
| 38 |
a.old_issue_id = o.issue_id, |
45 |
a.old_issue_id = o.issue_id, |
| 39 |
a.issue_id = NULL |
46 |
a.issue_id = NULL |
| 40 |
WHERE |
47 |
WHERE |
| 41 |
o.issue_id IS NOT NULL |
48 |
o.issue_id IS NOT NULL |
| 42 |
} |
49 |
} |
| 43 |
); |
50 |
); |
| 44 |
say $out "Updated 'accountlines.old_issue_id' from 'old_issues'"; |
51 |
say $out "Updated 'accountlines.old_issue_id' from 'old_issues'"; |
| 45 |
|
52 |
|
| 46 |
# Add constraint for issue_id |
53 |
$dbh->do( |
| 47 |
unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) ) { |
54 |
q{ |
| 48 |
$dbh->do( |
55 |
UPDATE |
| 49 |
q{ |
56 |
accountlines a |
| 50 |
UPDATE |
57 |
LEFT JOIN issues i ON (a.issue_id = i.issue_id) |
| 51 |
accountlines a |
58 |
SET |
| 52 |
LEFT JOIN issues i ON (a.issue_id = i.issue_id) |
59 |
a.issue_id = NULL |
| 53 |
SET |
60 |
WHERE |
| 54 |
a.issue_id = NULL |
61 |
i.issue_id IS NULL |
| 55 |
WHERE |
62 |
} |
| 56 |
i.issue_id IS NULL |
63 |
); |
| 57 |
} |
64 |
say $out, "Fixed accountlines.issue_id where missing from issues"; |
| 58 |
); |
|
|
| 59 |
say $out "Fix accountlines.issue_id where missing from issues"; |
| 60 |
|
65 |
|
| 61 |
$dbh->do( |
66 |
$dbh->do( |
| 62 |
q{ |
67 |
q{ |
| 63 |
ALTER TABLE accountlines |
68 |
ALTER TABLE accountlines |
| 64 |
ADD CONSTRAINT `accountlines_ibfk_issues` |
69 |
ADD CONSTRAINT `accountlines_ibfk_issues` |
| 65 |
FOREIGN KEY (`issue_id`) |
70 |
FOREIGN KEY (`issue_id`) |
| 66 |
REFERENCES `issues` (`issue_id`) |
71 |
REFERENCES `issues` (`issue_id`) |
| 67 |
ON DELETE SET NULL |
72 |
ON DELETE SET NULL |
| 68 |
ON UPDATE CASCADE |
73 |
ON UPDATE CASCADE |
| 69 |
} |
|
|
| 70 |
); |
| 71 |
say $out "Added constraint 'accountlines.issues'"; |
| 72 |
} |
74 |
} |
|
|
75 |
); |
| 76 |
say $out "Added constraint 'accountlines.issues'"; |
| 73 |
} |
77 |
} |
| 74 |
|
78 |
|
| 75 |
say $out "Add old_issue_id to accountlines and setup appropriate constraints)\n"; |
79 |
say $out "Add old_issue_id to accountlines and setup appropriate constraints)\n"; |
| 76 |
- |
|
|