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

(-)a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl (-16 / +17 lines)
Lines 14-32 print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table Link Here
14
        `overduerules_id` int(11) 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
        `itemtype` varchar(10) default '',
18
        `letter1` varchar(20) DEFAULT NULL,
18
        `onhold` tinyint(1) default 0,
19
        `debarred1` varchar(1) DEFAULT '0',
19
        `delay` int(4) NOT NULL default 1,
20
        `delay2` int(4) DEFAULT NULL,
20
        `letter` varchar(20) default NULL,
21
        `debarred2` varchar(1) DEFAULT '0',
21
        `debarred` varchar(1) default 0,
22
        `letter2` varchar(20) DEFAULT NULL,
22
        PRIMARY KEY (`overduerules_id`)
23
        `delay3` int(4) DEFAULT NULL,
24
        `letter3` varchar(20) DEFAULT NULL,
25
        `debarred3` int(1) DEFAULT '0',
26
        PRIMARY KEY (`overduerules_id`),
27
        UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
28
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
23
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
29
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
24
     foreach my $i (1..3){
25
        $dbh->do("INSERT INTO overduerules (delay, letter, debarred, branchcode, categorycode) ".
26
                 "SELECT delay$i AS delay, letter$i AS letter, debarred$i as debarred, branchcode, categorycode FROM old_overduerules");
27
     }
30
    $dbh->do("DROP TABLE old_overduerules");
28
    $dbh->do("DROP TABLE old_overduerules");
31
    $dbh->do("ALTER TABLE overduerules_transport_types
29
    $dbh->do("ALTER TABLE overduerules_transport_types
32
              ADD COLUMN overduerules_id int(11) NOT NULL");
30
              ADD COLUMN overduerules_id int(11) NOT NULL");
Lines 36-46 print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table Link Here
36
              DROP FOREIGN KEY overduerules_fk,
34
              DROP FOREIGN KEY overduerules_fk,
37
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
35
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
38
              DROP COLUMN branchcode,
36
              DROP COLUMN branchcode,
39
              DROP COLUMN categorycode");
37
              DROP COLUMN categorycode,
40
    my $s = $dbh->prepare("INSERT INTO overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
38
              DROP COLUMN letternumber");
41
                         ." VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
39
    my $s = $dbh->prepare(
40
        "INSERT INTO overduerules_transport_types (overduerules_id, id, message_transport_type) ".
41
        "VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?)"
42
    );
42
    foreach my $mtt(@$mtts){
43
    foreach my $mtt(@$mtts){
43
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
44
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{message_transport_type} );
44
    }
45
    }
45
    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
46
    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
46
#    print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
47
#    print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
(-)a/installer/data/mysql/kohastructure.sql (-11 / +10 lines)
Lines 1705-1720 DROP TABLE IF EXISTS `overduerules`; Link Here
1705
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1705
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1706
  `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)
1706
  `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)
1707
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1707
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1708
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
1708
 `itemtype` varchar(10) default '',
1709
  `letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice
1709
 `onhold` tinyint(1) default 0,
1710
  `debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)
1710
 `delay` int(4) NOT NULL default 1,
1711
  `delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent
1711
 `letter` varchar(20) default NULL,
1712
  `debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)
1712
 `debarred` varchar(1) default 0,
1713
  `letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice
1713
  PRIMARY KEY  (`overduerules_id`)
1714
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1715
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1716
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1717
  PRIMARY KEY  (`branchcode`,`categorycode`)
1718
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1714
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1719
1715
1720
--
1716
--
Lines 2581-2587 CREATE TABLE overduerules_transport_types( Link Here
2581
    `letternumber` INT(1) NOT NULL DEFAULT 1,
2577
    `letternumber` INT(1) NOT NULL DEFAULT 1,
2582
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2578
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2583
    PRIMARY KEY (id),
2579
    PRIMARY KEY (id),
2580
<<<<<<< HEAD
2584
    CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,
2581
    CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,
2582
=======
2583
    CONSTRAINT overduerules_transport_types_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
2584
>>>>>>> 6be069d... Bug 12772 - Update Database (Based on Bug13624)
2585
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2585
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2586
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2586
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2587
2587
2588
- 

Return to bug 12772