1 $DBversion = 'XXX'; # will be replaced by the RM
2 if( CheckVersion( $DBversion ) ) {
10 planneddate = '0000-00-00'
19 publisheddate = '0000-00-00'
28 claimdate = '0000-00-00'
33 MODIFY COLUMN biblionumber INT(11) NOT NULL
36 unless ( foreign_key_exists( 'serial', 'serial_ibfk_1' ) ) {
37 my $serials = $dbh->selectall_arrayref(q|
38 SELECT serialid FROM serial WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio)
41 warn q|WARNING - The following serials are deleted, they were not attached to an existing bibliographic record (serialid): | . join ", ", map { $_->{serialid} } @$serials;
43 DELETE FROM serial WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio)
48 ADD CONSTRAINT serial_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
54 MODIFY COLUMN subscriptionid INT(11) NOT NULL
57 unless ( foreign_key_exists( 'serial', 'serial_ibfk_2' ) ) {
58 my $serials = $dbh->selectall_arrayref(q|
59 SELECT serialid FROM serial WHERE subscriptionid NOT IN (SELECT subscriptionid FROM subscription)
62 warn q|WARNING - The following serials are deleted, they were not attached to an existing subscription (serialid): | . join ", ", map { $_->{serialid} } @$serials;
64 DELETE FROM serial WHERE subscriptionid NOT IN (SELECT subscriptionid FROM subscription)
69 ADD CONSTRAINT serial_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
74 ALTER TABLE subscriptionhistory
75 MODIFY COLUMN biblionumber int(11) NOT NULL,
76 MODIFY COLUMN subscriptionid int(11) NOT NULL
79 unless ( foreign_key_exists( 'subscriptionhistory', 'subscription_history_ibfk_1' ) ) {
81 DELETE FROM subscriptionhistory WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio)
84 ALTER TABLE subscriptionhistory
85 ADD CONSTRAINT subscription_history_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
89 unless ( foreign_key_exists( 'subscriptionhistory', 'subscription_history_ibfk_2' ) ) {
91 DELETE FROM subscriptionhistory WHERE subscriptionid NOT IN (SELECT subscriptionid FROM subscription)
94 ALTER TABLE subscriptionhistory
95 ADD CONSTRAINT subscription_history_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
100 ALTER TABLE subscription
101 MODIFY COLUMN biblionumber int(11) NOT NULL
104 unless ( foreign_key_exists( 'subscription', 'subscription_ibfk_3' ) ) {
105 my $subscriptions = $dbh->selectall_arrayref(q|
106 SELECT subscriptionid FROM subscription WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio)
108 if ( @$subscriptions ) {
109 warn q|WARNING - The following subscriptions are deleted, they were not attached to an existing bibliographic record (subscriptionid): | . join ", ", map { $_->{subscriptionid} } @$subscriptions;
112 DELETE FROM subscription WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio)
116 ALTER TABLE subscription
117 ADD CONSTRAINT subscription_ibfk_3 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
121 SetVersion( $DBversion );
122 print "Upgrade to $DBversion done (Bug 21901 - Add foreign key constraints on serial)\n";