|
Lines 2-11
$DBversion = 'XXX'; # will be replaced by the RM
Link Here
|
| 2 |
if( CheckVersion( $DBversion ) ) { |
2 |
if( CheckVersion( $DBversion ) ) { |
| 3 |
|
3 |
|
| 4 |
if ( !column_exists( 'illrequests', 'status_alias' ) ) { |
4 |
if ( !column_exists( 'illrequests', 'status_alias' ) ) { |
| 5 |
$dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias integer DEFAULT NULL AFTER status" ); |
5 |
# Fresh upgrade, just add the column and constraint |
|
|
6 |
$dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias varchar(80) DEFAULT NULL AFTER status" ); |
| 7 |
} else { |
| 8 |
# Migrate all existing foreign keys from referencing authorised_values.id |
| 9 |
# to referencing authorised_values.authorised_value |
| 10 |
# First remove the foreign key constraint and index |
| 11 |
if ( foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) { |
| 12 |
$dbh->do( "ALTER TABLE illrequests DROP FOREIGN KEY illrequests_safk"); |
| 13 |
} |
| 14 |
if ( index_exists( 'illrequests', 'illrequests_safk' ) ) { |
| 15 |
$dbh->do( "DROP INDEX illrequests_safk IN illrequests" ); |
| 16 |
} |
| 17 |
# Now change the illrequests.status_alias column definition from int to varchar |
| 18 |
$dbh->do( "ALTER TABLE illrequests MODIFY COLUMN status_alias varchar(80)" ); |
| 19 |
# Now replace all references to authorised_values.id with their |
| 20 |
# corresponding authorised_values.authorised_value |
| 21 |
my $sth = $dbh->prepare( "SELECT illrequest_id, status_alias FROM illrequests WHERE status_alias IS NOT NULL" ); |
| 22 |
$sth->execute(); |
| 23 |
while (my @row = $sth->fetchrow_array()) { |
| 24 |
my $r_id = $row[0]; |
| 25 |
my $av_id = $row[1]; |
| 26 |
# Get the authorised value's authorised_value value |
| 27 |
my ($av_val) = $dbh->selectrow_array( "SELECT authorised_value FROM authorised_values WHERE id = ?", {}, $av_id ); |
| 28 |
# Now update illrequests.status_alias |
| 29 |
if ($av_val) { |
| 30 |
$dbh->do( "UPDATE illrequests SET status_alias = ? WHERE illrequest_id = ?", {}, ($av_val, $r_id) ); |
| 31 |
} |
| 32 |
} |
| 6 |
} |
33 |
} |
| 7 |
if ( !foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) { |
34 |
if ( !foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) { |
| 8 |
$dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(id) ON UPDATE CASCADE ON DELETE SET NULL" ); |
35 |
$dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" ); |
| 9 |
} |
36 |
} |
| 10 |
$dbh->do( "INSERT IGNORE INTO authorised_value_categories SET category_name = 'ILLSTATUS'"); |
37 |
$dbh->do( "INSERT IGNORE INTO authorised_value_categories SET category_name = 'ILLSTATUS'"); |
| 11 |
|
38 |
|