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 1457-1486 CREATE TABLE `search_marc_to_field` ( Link Here
1457
  FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE
1457
  FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE
1458
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1458
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1459
1459
1460
--
1461
-- Table structure for table `serial`
1462
--
1463
1464
DROP TABLE IF EXISTS `serial`;
1465
CREATE TABLE `serial` ( -- issues related to subscriptions
1466
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1467
  `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
1468
  `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
1469
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1470
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1471
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
1472
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
1473
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
1474
  `planneddate` date default NULL, -- date expected
1475
  `notes` MEDIUMTEXT, -- notes
1476
  `publisheddate` date default NULL, -- date published
1477
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
1478
  `claimdate` date default NULL, -- date claimed
1479
  claims_count int(11) default 0, -- number of claims made related to this issue
1480
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
1481
  PRIMARY KEY (`serialid`)
1482
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1483
1484
--
1460
--
1485
-- Table structure for table `sessions`
1461
-- Table structure for table `sessions`
1486
--
1462
--
Lines 1993-1999 CREATE TABLE subscription_numberpatterns ( Link Here
1993
1969
1994
DROP TABLE IF EXISTS `subscription`;
1970
DROP TABLE IF EXISTS `subscription`;
1995
CREATE TABLE `subscription` ( -- information related to the subscription
1971
CREATE TABLE `subscription` ( -- information related to the subscription
1996
  `biblionumber` int(11) NOT NULL default 0, -- foreign key for biblio.biblionumber that this subscription is attached to
1972
  `biblionumber` int(11) NOT NULL, -- foreign key for biblio.biblionumber that this subscription is attached to
1997
  `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
1973
  `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
1998
  `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
1974
  `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
1999
  `startdate` date default NULL, -- start date for this subscription
1975
  `startdate` date default NULL, -- start date for this subscription
Lines 2037-2045 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
2037
  `previousitemtype` VARCHAR( 10 ) NULL,
2013
  `previousitemtype` VARCHAR( 10 ) NULL,
2038
  `mana_id` int(11) NULL DEFAULT NULL,
2014
  `mana_id` int(11) NULL DEFAULT NULL,
2039
  PRIMARY KEY  (`subscriptionid`),
2015
  PRIMARY KEY  (`subscriptionid`),
2040
  KEY `by_biblionumber` (`biblionumber`),
2041
  CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
2016
  CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
2042
  CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE
2017
  CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE,
2018
  CONSTRAINT subscription_ibfk_3 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
2019
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2020
2021
--
2022
-- Table structure for table `serial`
2023
--
2024
2025
DROP TABLE IF EXISTS `serial`;
2026
CREATE TABLE `serial` ( -- issues related to subscriptions
2027
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
2028
  `biblionumber` int(11) NOT NULL, -- foreign key for the biblio.biblionumber that this issue is attached to
2029
  `subscriptionid` int(11) NOT NULL, -- foreign key to the subscription.subscriptionid that this issue is part of
2030
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
2031
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
2032
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
2033
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
2034
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
2035
  `planneddate` date default NULL, -- date expected
2036
  `notes` MEDIUMTEXT, -- notes
2037
  `publisheddate` date default NULL, -- date published
2038
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
2039
  `claimdate` date default NULL, -- date claimed
2040
  claims_count int(11) default 0, -- number of claims made related to this issue
2041
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
2042
  PRIMARY KEY (`serialid`),
2043
  CONSTRAINT serial_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
2044
  CONSTRAINT serial_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
2043
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2045
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2044
2046
2045
--
2047
--
Lines 2048-2055 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
2048
2050
2049
DROP TABLE IF EXISTS `subscriptionhistory`;
2051
DROP TABLE IF EXISTS `subscriptionhistory`;
2050
CREATE TABLE `subscriptionhistory` (
2052
CREATE TABLE `subscriptionhistory` (
2051
  `biblionumber` int(11) NOT NULL default 0,
2053
  `biblionumber` int(11) NOT NULL,
2052
  `subscriptionid` int(11) NOT NULL default 0,
2054
  `subscriptionid` int(11) NOT NULL,
2053
  `histstartdate` date default NULL,
2055
  `histstartdate` date default NULL,
2054
  `histenddate` date default NULL,
2056
  `histenddate` date default NULL,
2055
  `missinglist` LONGTEXT NOT NULL,
2057
  `missinglist` LONGTEXT NOT NULL,
Lines 2057-2063 CREATE TABLE `subscriptionhistory` ( Link Here
2057
  `opacnote` LONGTEXT NULL,
2059
  `opacnote` LONGTEXT NULL,
2058
  `librariannote` LONGTEXT NULL,
2060
  `librariannote` LONGTEXT NULL,
2059
  PRIMARY KEY  (`subscriptionid`),
2061
  PRIMARY KEY  (`subscriptionid`),
2060
  KEY `biblionumber` (`biblionumber`)
2062
  CONSTRAINT subscription_history_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
2063
  CONSTRAINT subscription_history_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE
2061
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2064
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2062
2065
2063
--
2066
--
(-)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