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

(-)a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl (-7 / +8 lines)
Lines 5-17 use warnings; Link Here
5
use C4::Context;
5
use C4::Context;
6
my $dbh=C4::Context->dbh;
6
my $dbh=C4::Context->dbh;
7
7
8
print "Will do : Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
8
print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types\n";
9
9
10
#if ( CheckVersion($DBversion) ) {
10
#if ( CheckVersion($DBversion) ) {
11
    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
11
    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
12
    $dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
12
    $dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
13
    $dbh->do("CREATE TABLE overduerules (
13
    $dbh->do("CREATE TABLE overduerules (
14
        `overduerules_id` mediumint NOT NULL AUTO_INCREMENT,
14
        `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
15
        `branchcode` varchar(10) NOT NULL DEFAULT '',
15
        `branchcode` varchar(10) NOT NULL DEFAULT '',
16
        `categorycode` varchar(10) NOT NULL DEFAULT '',
16
        `categorycode` varchar(10) NOT NULL DEFAULT '',
17
        `delay1` int(4) DEFAULT NULL,
17
        `delay1` int(4) DEFAULT NULL,
Lines 23-43 print "Will do : Upgrade to $DBversion done (Bug 13624 - Remove columns branchco Link Here
23
        `delay3` int(4) DEFAULT NULL,
23
        `delay3` int(4) DEFAULT NULL,
24
        `letter3` varchar(20) DEFAULT NULL,
24
        `letter3` varchar(20) DEFAULT NULL,
25
        `debarred3` int(1) DEFAULT '0',
25
        `debarred3` int(1) DEFAULT '0',
26
        PRIMARY KEY (`overduerules_id`)
26
        PRIMARY KEY (`overduerules_id`),
27
        UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
27
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
28
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
28
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
29
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
29
    $dbh->do("DROP TABLE old_overduerules");
30
    $dbh->do("DROP TABLE old_overduerules");
30
    $dbh->do("ALTER TABLE overduerules_transport_types
31
    $dbh->do("ALTER TABLE overduerules_transport_types
31
              ADD COLUMN overduerules_id mediumint NOT NULL");
32
              ADD COLUMN overduerules_id int(11) NOT NULL");
32
    my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} });
33
    my $mtts = $dbh->selectall_arrayref("SELECT * FROM overduerules_transport_types", { Slice => {} });
33
    $dbh->do("DELETE FROM overduerules_transport_types");
34
    $dbh->do("DELETE FROM overduerules_transport_types");
34
    $dbh->do("ALTER TABLE overduerules_transport_types
35
    $dbh->do("ALTER TABLE overduerules_transport_types
35
              DROP FOREIGN KEY overduerules_fk,
36
              DROP FOREIGN KEY overduerules_fk,
36
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
37
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
37
              DROP COLUMN branchcode,
38
              DROP COLUMN branchcode,
38
              DROP COLUMN categorycode");
39
              DROP COLUMN categorycode");
39
    my $s = $dbh->prepare("insert into overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
40
    my $s = $dbh->prepare("INSERT INTO overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
40
                         ." values((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
41
                         ." VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
41
    foreach my $mtt(@$mtts){
42
    foreach my $mtt(@$mtts){
42
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
43
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
43
    }
44
    }
(-)a/installer/data/mysql/kohastructure.sql (-4 / +4 lines)
Lines 1705-1711 CREATE TABLE `opac_news` ( -- data from the news tool Link Here
1705
1705
1706
DROP TABLE IF EXISTS `overduerules`;
1706
DROP TABLE IF EXISTS `overduerules`;
1707
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1707
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1708
  `overduerules_id` mediumint NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1708
  `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1709
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries)
1709
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries)
1710
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1710
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1711
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
1711
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
Lines 1717-1723 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here
1717
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1717
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1718
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1718
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1719
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1719
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1720
  PRIMARY KEY  (`overduerules_id`)
1720
  PRIMARY KEY  (`overduerules_id`),
1721
  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
1721
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1722
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1722
1723
1723
--
1724
--
Lines 2593-2599 DROP TABLE IF EXISTS `overduerules_transport_types`; Link Here
2593
CREATE TABLE overduerules_transport_types(
2594
CREATE TABLE overduerules_transport_types(
2594
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2595
    `id` INT(11) NOT NULL AUTO_INCREMENT,
2595
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2596
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2596
    `overduerules_id` mediumint NOT NULL,
2597
    `overduerules_id` INT(11) NOT NULL,
2597
    PRIMARY KEY (id),
2598
    PRIMARY KEY (id),
2598
    CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
2599
    CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
2599
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2600
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2600
- 

Return to bug 13624