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

(-)a/C4/Biblio.pm (-7 lines)
Lines 387-399 sub DelBiblio { Link Here
387
387
388
    return $error if $error;
388
    return $error if $error;
389
389
390
    # We delete attached subscriptions
391
    require C4::Serials;
392
    my $subscriptions = C4::Serials::GetFullSubscriptionsFromBiblionumber($biblionumber);
393
    foreach my $subscription (@$subscriptions) {
394
        C4::Serials::DelSubscription( $subscription->{subscriptionid} );
395
    }
396
397
    # We delete any existing holds
390
    # We delete any existing holds
398
    my $holds = $biblio->holds;
391
    my $holds = $biblio->holds;
399
    while ( my $hold = $holds->next ) {
392
    while ( my $hold = $holds->next ) {
(-)a/C4/Serials.pm (-2 lines)
Lines 1703-1710 sub DelSubscription { Link Here
1703
    my ($subscriptionid) = @_;
1703
    my ($subscriptionid) = @_;
1704
    my $dbh = C4::Context->dbh;
1704
    my $dbh = C4::Context->dbh;
1705
    $dbh->do("DELETE FROM subscription WHERE subscriptionid=?", undef, $subscriptionid);
1705
    $dbh->do("DELETE FROM subscription WHERE subscriptionid=?", undef, $subscriptionid);
1706
    $dbh->do("DELETE FROM subscriptionhistory WHERE subscriptionid=?", undef, $subscriptionid);
1707
    $dbh->do("DELETE FROM serial WHERE subscriptionid=?", undef, $subscriptionid);
1708
1706
1709
    Koha::AdditionalFieldValues->search({
1707
    Koha::AdditionalFieldValues->search({
1710
        'field.tablename' => 'subscription',
1708
        '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 1454-1483 CREATE TABLE `search_marc_to_field` ( Link Here
1454
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1454
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1455
1455
1456
--
1456
--
1457
-- Table structure for table `serial`
1458
--
1459
1460
DROP TABLE IF EXISTS `serial`;
1461
CREATE TABLE `serial` ( -- issues related to subscriptions
1462
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1463
  `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
1464
  `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
1465
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1466
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1467
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
1468
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
1469
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
1470
  `planneddate` date default NULL, -- date expected
1471
  `notes` MEDIUMTEXT, -- notes
1472
  `publisheddate` date default NULL, -- date published
1473
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
1474
  `claimdate` date default NULL, -- date claimed
1475
  claims_count int(11) default 0, -- number of claims made related to this issue
1476
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
1477
  PRIMARY KEY (`serialid`)
1478
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1479
1480
--
1481
-- Table structure for table `sessions`
1457
-- Table structure for table `sessions`
1482
--
1458
--
1483
1459
Lines 1988-1994 CREATE TABLE subscription_numberpatterns ( Link Here
1988
1964
1989
DROP TABLE IF EXISTS `subscription`;
1965
DROP TABLE IF EXISTS `subscription`;
1990
CREATE TABLE `subscription` ( -- information related to the subscription
1966
CREATE TABLE `subscription` ( -- information related to the subscription
1991
  `biblionumber` int(11) NOT NULL default 0, -- foreign key for biblio.biblionumber that this subscription is attached to
1967
  `biblionumber` int(11) NOT NULL, -- foreign key for biblio.biblionumber that this subscription is attached to
1992
  `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
1968
  `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
1993
  `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
1969
  `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
1994
  `startdate` date default NULL, -- start date for this subscription
1970
  `startdate` date default NULL, -- start date for this subscription
Lines 2032-2040 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
2032
  `previousitemtype` VARCHAR( 10 ) NULL,
2008
  `previousitemtype` VARCHAR( 10 ) NULL,
2033
  `mana_id` int(11) NULL DEFAULT NULL,
2009
  `mana_id` int(11) NULL DEFAULT NULL,
2034
  PRIMARY KEY  (`subscriptionid`),
2010
  PRIMARY KEY  (`subscriptionid`),
2035
  KEY `by_biblionumber` (`biblionumber`),
2036
  CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
2011
  CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
2037
  CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE
2012
  CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE,
2013
  CONSTRAINT subscription_ibfk_3 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
2014
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2015
2016
--
2017
-- Table structure for table `serial`
2018
--
2019
2020
DROP TABLE IF EXISTS `serial`;
2021
CREATE TABLE `serial` ( -- issues related to subscriptions
2022
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
2023
  `biblionumber` int(11) NOT NULL, -- foreign key for the biblio.biblionumber that this issue is attached to
2024
  `subscriptionid` int(11) NOT NULL, -- foreign key to the subscription.subscriptionid that this issue is part of
2025
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
2026
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
2027
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
2028
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
2029
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
2030
  `planneddate` date default NULL, -- date expected
2031
  `notes` MEDIUMTEXT, -- notes
2032
  `publisheddate` date default NULL, -- date published
2033
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
2034
  `claimdate` date default NULL, -- date claimed
2035
  claims_count int(11) default 0, -- number of claims made related to this issue
2036
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
2037
  PRIMARY KEY (`serialid`),
2038
  CONSTRAINT serial_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
2039
  CONSTRAINT serial_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
2038
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2040
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2039
2041
2040
--
2042
--
Lines 2043-2050 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
2043
2045
2044
DROP TABLE IF EXISTS `subscriptionhistory`;
2046
DROP TABLE IF EXISTS `subscriptionhistory`;
2045
CREATE TABLE `subscriptionhistory` (
2047
CREATE TABLE `subscriptionhistory` (
2046
  `biblionumber` int(11) NOT NULL default 0,
2048
  `biblionumber` int(11) NOT NULL,
2047
  `subscriptionid` int(11) NOT NULL default 0,
2049
  `subscriptionid` int(11) NOT NULL,
2048
  `histstartdate` date default NULL,
2050
  `histstartdate` date default NULL,
2049
  `histenddate` date default NULL,
2051
  `histenddate` date default NULL,
2050
  `missinglist` LONGTEXT NOT NULL,
2052
  `missinglist` LONGTEXT NOT NULL,
Lines 2052-2058 CREATE TABLE `subscriptionhistory` ( Link Here
2052
  `opacnote` LONGTEXT NULL,
2054
  `opacnote` LONGTEXT NULL,
2053
  `librariannote` LONGTEXT NULL,
2055
  `librariannote` LONGTEXT NULL,
2054
  PRIMARY KEY  (`subscriptionid`),
2056
  PRIMARY KEY  (`subscriptionid`),
2055
  KEY `biblionumber` (`biblionumber`)
2057
  CONSTRAINT subscription_history_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
2058
  CONSTRAINT subscription_history_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
2056
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2059
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2057
2060
2058
--
2061
--
(-)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 587-593 subtest 'deletedbiblio_metadata' => sub { Link Here
587
};
589
};
588
590
589
subtest 'DelBiblio' => sub {
591
subtest 'DelBiblio' => sub {
590
    plan tests => 2;
592
    plan tests => 5;
591
593
592
    my ($biblionumber, $biblioitemnumber) = C4::Biblio::AddBiblio(MARC::Record->new, '');
594
    my ($biblionumber, $biblioitemnumber) = C4::Biblio::AddBiblio(MARC::Record->new, '');
593
    my $deleted = C4::Biblio::DelBiblio( $biblionumber );
595
    my $deleted = C4::Biblio::DelBiblio( $biblionumber );
Lines 595-600 subtest 'DelBiblio' => sub { Link Here
595
597
596
    $deleted = C4::Biblio::DelBiblio( $biblionumber );
598
    $deleted = C4::Biblio::DelBiblio( $biblionumber );
597
    is( $deleted, undef, 'DelBiblo should return undef is the record did not exist');
599
    is( $deleted, undef, 'DelBiblo should return undef is the record did not exist');
600
601
    my $biblio       = $builder->build_sample_biblio;
602
    my $subscription = $builder->build_object(
603
        {
604
            class => 'Koha::Subscriptions',
605
            value => { biblionumber => $biblio->biblionumber }
606
        }
607
    );
608
    my $serial = $builder->build_object(
609
        {
610
            class => 'Koha::Serials',
611
            value => {
612
                biblionumber   => $biblio->biblionumber,
613
                subscriptionid => $subscription->subscriptionid
614
            }
615
        }
616
    );
617
    my $subscription_history = $builder->build_object(
618
        {
619
            class => 'Koha::Subscription::Histories',
620
            value => {
621
                biblionumber   => $biblio->biblionumber,
622
                subscriptionid => $subscription->subscriptionid
623
            }
624
        }
625
    );
626
    C4::Biblio::DelBiblio($biblio->biblionumber); # Or $biblio->delete
627
    is( $subscription->get_from_storage, undef, 'subscription should be deleted on biblio deletion' );
628
    is( $serial->get_from_storage, undef, 'serial should be deleted on biblio deletion' );
629
    is( $subscription_history->get_from_storage, undef, 'subscription history should be deleted on biblio deletion' );
598
};
630
};
599
631
600
subtest 'MarcFieldForCreatorAndModifier' => sub {
632
subtest 'MarcFieldForCreatorAndModifier' => sub {
601
- 

Return to bug 21901