1 $DBversion = 'XXX'; # will be replaced by the RM
2 if( CheckVersion( $DBversion ) ) {
4 if ( !column_exists( 'illrequests', 'status_alias' ) ) {
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" );
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");
14 if ( index_exists( 'illrequests', 'illrequests_safk' ) ) {
15 $dbh->do( "DROP INDEX illrequests_safk IN illrequests" );
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" );
23 while (my @row = $sth->fetchrow_array()) {
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
30 $dbh->do( "UPDATE illrequests SET status_alias = ? WHERE illrequest_id = ?", {}, ($av_val, $r_id) );
34 if ( !foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) {
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" );
37 $dbh->do( "INSERT IGNORE INTO authorised_value_categories SET category_name = 'ILLSTATUS'");
39 SetVersion( $DBversion );
40 print "Upgrade to $DBversion done (Bug 20581 - Allow manual selection of custom ILL request statuses)\n";