View | Details | Raw Unified | Return to bug 21901
Collapse All | Expand All

(-)a/C4/Biblio.pm (-7 lines)
Lines 386-398 sub DelBiblio { Link Here
386
386
387
    return $error if $error;
387
    return $error if $error;
388
388
389
    # We delete attached subscriptions
390
    require C4::Serials;
391
    my $subscriptions = C4::Serials::GetFullSubscriptionsFromBiblionumber($biblionumber);
392
    foreach my $subscription (@$subscriptions) {
393
        C4::Serials::DelSubscription( $subscription->{subscriptionid} );
394
    }
395
396
    # We delete any existing holds
389
    # We delete any existing holds
397
    my $holds = $biblio->holds;
390
    my $holds = $biblio->holds;
398
    while ( my $hold = $holds->next ) {
391
    while ( my $hold = $holds->next ) {
(-)a/C4/Serials.pm (-2 lines)
Lines 1731-1738 sub DelSubscription { Link Here
1731
    my ($subscriptionid) = @_;
1731
    my ($subscriptionid) = @_;
1732
    my $dbh = C4::Context->dbh;
1732
    my $dbh = C4::Context->dbh;
1733
    $dbh->do("DELETE FROM subscription WHERE subscriptionid=?", undef, $subscriptionid);
1733
    $dbh->do("DELETE FROM subscription WHERE subscriptionid=?", undef, $subscriptionid);
1734
    $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=?", undef, $subscriptionid);
1735
    $dbh->do("DELETE FROM serial WHERE subscriptionid=?", undef, $subscriptionid);
1736
1734
1737
    Koha::AdditionalFieldValues->search({
1735
    Koha::AdditionalFieldValues->search({
1738
        'field.tablename' => 'subscription',
1736
        'field.tablename' => 'subscription',
(-)a/installer/data/mysql/atomicupdate/bug_21901.perl (+62 lines)
Line 0 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
4
    $dbh->do(q|
5
        ALTER TABLE serial
6
        MODIFY COLUMN biblionumber INT(11) NOT NULL
7
    |);
8
9
    unless ( foreign_key_exists( 'serial', 'serial_ibfk_1' ) ) {
10
        $dbh->do(q|
11
            ALTER TABLE serial
12
            ADD CONSTRAINT serial_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
13
        |);
14
    }
15
16
    $dbh->do(q|
17
        ALTER TABLE serial
18
        MODIFY COLUMN subscriptionid INT(11) NOT NULL
19
    |);
20
21
    unless ( foreign_key_exists( 'serial', 'serial_ibfk_2' ) ) {
22
        $dbh->do(q|
23
            ALTER TABLE serial
24
            ADD CONSTRAINT serial_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
25
        |);
26
    }
27
28
    $dbh->do(q|
29
        ALTER TABLE subscriptionhistory
30
        MODIFY COLUMN biblionumber int(11),
31
        MODIFY COLUMN subscriptionid int(11)
32
    |);
33
34
    unless ( foreign_key_exists( 'subscriptionhistory', 'subscription_history_ibfk_1' ) ) {
35
        $dbh->do(q|
36
            ALTER TABLE subscriptionhistory
37
            ADD CONSTRAINT subscription_history_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
38
        |);
39
    }
40
41
    unless ( foreign_key_exists( 'subscriptionhistory', 'subscription_history_ibfk_2' ) ) {
42
        $dbh->do(q|
43
            ALTER TABLE subscriptionhistory
44
            ADD CONSTRAINT subscription_history_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
45
        |);
46
    }
47
48
    $dbh->do(q|
49
        ALTER TABLE subscription
50
        MODIFY COLUMN biblionumber int(11)
51
    |);
52
53
    unless ( foreign_key_exists( 'subscription', 'subscription_ibfk_3' ) ) {
54
        $dbh->do(q|
55
            ALTER TABLE subscription
56
            ADD CONSTRAINT subscription_ibfk_3 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
57
        |);
58
    }
59
60
    SetVersion( $DBversion );
61
    print "Upgrade to $DBversion done (Bug 21901 - Add foreign key constraints on serial)\n";
62
}
(-)a/installer/data/mysql/kohastructure.sql (-30 / +33 lines)
Lines 1401-1430 CREATE TABLE `search_marc_to_field` ( Link Here
1401
  FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE
1401
  FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE
1402
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1402
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1403
1403
1404
--
1405
-- Table structure for table `serial`
1406
--
1407
1408
DROP TABLE IF EXISTS `serial`;
1409
CREATE TABLE `serial` ( -- issues related to subscriptions
1410
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1411
  `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
1412
  `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
1413
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1414
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1415
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
1416
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
1417
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
1418
  `planneddate` date default NULL, -- date expected
1419
  `notes` MEDIUMTEXT, -- notes
1420
  `publisheddate` date default NULL, -- date published
1421
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
1422
  `claimdate` date default NULL, -- date claimed
1423
  claims_count int(11) default 0, -- number of claims made related to this issue
1424
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
1425
  PRIMARY KEY (`serialid`)
1426
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1427
1428
--
1404
--
1429
-- Table structure for table `sessions`
1405
-- Table structure for table `sessions`
1430
--
1406
--
Lines 1937-1943 CREATE TABLE subscription_numberpatterns ( Link Here
1937
1913
1938
DROP TABLE IF EXISTS `subscription`;
1914
DROP TABLE IF EXISTS `subscription`;
1939
CREATE TABLE `subscription` ( -- information related to the subscription
1915
CREATE TABLE `subscription` ( -- information related to the subscription
1940
  `biblionumber` int(11) NOT NULL default 0, -- foreign key for biblio.biblionumber that this subscription is attached to
1916
  `biblionumber` int(11) NOT NULL, -- foreign key for biblio.biblionumber that this subscription is attached to
1941
  `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
1917
  `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
1942
  `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
1918
  `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
1943
  `startdate` date default NULL, -- start date for this subscription
1919
  `startdate` date default NULL, -- start date for this subscription
Lines 1981-1989 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
1981
  `previousitemtype` VARCHAR( 10 ) NULL,
1957
  `previousitemtype` VARCHAR( 10 ) NULL,
1982
  `mana_id` int(11) NULL DEFAULT NULL,
1958
  `mana_id` int(11) NULL DEFAULT NULL,
1983
  PRIMARY KEY  (`subscriptionid`),
1959
  PRIMARY KEY  (`subscriptionid`),
1984
  KEY `by_biblionumber` (`biblionumber`),
1985
  CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
1960
  CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
1986
  CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE
1961
  CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE,
1962
  CONSTRAINT subscription_ibfk_3 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
1963
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1964
1965
--
1966
-- Table structure for table `serial`
1967
--
1968
1969
DROP TABLE IF EXISTS `serial`;
1970
CREATE TABLE `serial` ( -- issues related to subscriptions
1971
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1972
  `biblionumber` int(11) NOT NULL, -- foreign key for the biblio.biblionumber that this issue is attached to
1973
  `subscriptionid` int(11) NOT NULL, -- foreign key to the subscription.subscriptionid that this issue is part of
1974
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1975
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1976
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
1977
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
1978
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
1979
  `planneddate` date default NULL, -- date expected
1980
  `notes` MEDIUMTEXT, -- notes
1981
  `publisheddate` date default NULL, -- date published
1982
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
1983
  `claimdate` date default NULL, -- date claimed
1984
  claims_count int(11) default 0, -- number of claims made related to this issue
1985
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
1986
  PRIMARY KEY (`serialid`),
1987
  CONSTRAINT serial_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
1988
  CONSTRAINT serial_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
1987
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1989
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1988
1990
1989
--
1991
--
Lines 1992-1999 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
1992
1994
1993
DROP TABLE IF EXISTS `subscriptionhistory`;
1995
DROP TABLE IF EXISTS `subscriptionhistory`;
1994
CREATE TABLE `subscriptionhistory` (
1996
CREATE TABLE `subscriptionhistory` (
1995
  `biblionumber` int(11) NOT NULL default 0,
1997
  `biblionumber` int(11) NOT NULL,
1996
  `subscriptionid` int(11) NOT NULL default 0,
1998
  `subscriptionid` int(11) NOT NULL,
1997
  `histstartdate` date default NULL,
1999
  `histstartdate` date default NULL,
1998
  `histenddate` date default NULL,
2000
  `histenddate` date default NULL,
1999
  `missinglist` LONGTEXT NOT NULL,
2001
  `missinglist` LONGTEXT NOT NULL,
Lines 2001-2007 CREATE TABLE `subscriptionhistory` ( Link Here
2001
  `opacnote` LONGTEXT NULL,
2003
  `opacnote` LONGTEXT NULL,
2002
  `librariannote` LONGTEXT NULL,
2004
  `librariannote` LONGTEXT NULL,
2003
  PRIMARY KEY  (`subscriptionid`),
2005
  PRIMARY KEY  (`subscriptionid`),
2004
  KEY `biblionumber` (`biblionumber`)
2006
  CONSTRAINT subscription_history_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
2007
  CONSTRAINT subscription_history_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
2005
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2008
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2006
2009
2007
--
2010
--
(-)a/t/db_dependent/Biblio.t (-2 / +33 lines)
Lines 38-43 $schema->storage->txn_begin; Link Here
38
my $dbh = C4::Context->dbh;
38
my $dbh = C4::Context->dbh;
39
Koha::Caches->get_instance->clear_from_cache( "MarcSubfieldStructure-" );
39
Koha::Caches->get_instance->clear_from_cache( "MarcSubfieldStructure-" );
40
40
41
my $builder = t::lib::TestBuilder->new;
42
41
subtest 'GetMarcSubfieldStructureFromKohaField' => sub {
43
subtest 'GetMarcSubfieldStructureFromKohaField' => sub {
42
    plan tests => 25;
44
    plan tests => 25;
43
45
Lines 588-594 subtest 'deletedbiblio_metadata' => sub { Link Here
588
};
590
};
589
591
590
subtest 'DelBiblio' => sub {
592
subtest 'DelBiblio' => sub {
591
    plan tests => 2;
593
    plan tests => 5;
592
594
593
    my ($biblionumber, $biblioitemnumber) = C4::Biblio::AddBiblio(MARC::Record->new, '');
595
    my ($biblionumber, $biblioitemnumber) = C4::Biblio::AddBiblio(MARC::Record->new, '');
594
    my $deleted = C4::Biblio::DelBiblio( $biblionumber );
596
    my $deleted = C4::Biblio::DelBiblio( $biblionumber );
Lines 596-601 subtest 'DelBiblio' => sub { Link Here
596
598
597
    $deleted = C4::Biblio::DelBiblio( $biblionumber );
599
    $deleted = C4::Biblio::DelBiblio( $biblionumber );
598
    is( $deleted, undef, 'DelBiblo should return undef is the record did not exist');
600
    is( $deleted, undef, 'DelBiblo should return undef is the record did not exist');
601
602
    my $biblio       = $builder->build_sample_biblio;
603
    my $subscription = $builder->build_object(
604
        {
605
            class => 'Koha::Subscriptions',
606
            value => { biblionumber => $biblio->biblionumber }
607
        }
608
    );
609
    my $serial = $builder->build_object(
610
        {
611
            class => 'Koha::Serials',
612
            value => {
613
                biblionumber   => $biblio->biblionumber,
614
                subscriptionid => $subscription->subscriptionid
615
            }
616
        }
617
    );
618
    my $subscription_history = $builder->build_object(
619
        {
620
            class => 'Koha::Subscription::Histories',
621
            value => {
622
                biblionumber   => $biblio->biblionumber,
623
                subscriptionid => $subscription->subscriptionid
624
            }
625
        }
626
    );
627
    C4::Biblio::DelBiblio($biblio->biblionumber); # Or $biblio->delete
628
    is( $subscription->get_from_storage, undef, 'subscription should be deleted on biblio deletion' );
629
    is( $serial->get_from_storage, undef, 'serial should be deleted on biblio deletion' );
630
    is( $subscription_history->get_from_storage, undef, 'subscription history should be deleted on biblio deletion' );
599
};
631
};
600
632
601
subtest 'MarcFieldForCreatorAndModifier' => sub {
633
subtest 'MarcFieldForCreatorAndModifier' => sub {
602
- 

Return to bug 21901