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

(-)a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl (-1 / +50 lines)
Line 0 Link Here
0
- 
1
#! /usr/bin/perl
2
3
use strict;
4
use warnings;
5
use C4::Context;
6
my $dbh=C4::Context->dbh;
7
8
print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types\n";
9
10
#if ( CheckVersion($DBversion) ) {
11
    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
12
    $dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
13
    $dbh->do("CREATE TABLE overduerules (
14
        `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
15
        `branchcode` varchar(10) NOT NULL DEFAULT '',
16
        `categorycode` varchar(10) NOT NULL DEFAULT '',
17
        `delay1` int(4) DEFAULT NULL,
18
        `letter1` varchar(20) DEFAULT NULL,
19
        `debarred1` varchar(1) DEFAULT '0',
20
        `delay2` int(4) DEFAULT NULL,
21
        `debarred2` varchar(1) DEFAULT '0',
22
        `letter2` varchar(20) DEFAULT NULL,
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;");
29
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
30
    $dbh->do("DROP TABLE old_overduerules");
31
    $dbh->do("ALTER TABLE overduerules_transport_types
32
              ADD COLUMN overduerules_id int(11) NOT NULL");
33
    my $mtts = $dbh->selectall_arrayref("SELECT * FROM overduerules_transport_types", { Slice => {} });
34
    $dbh->do("DELETE FROM overduerules_transport_types");
35
    $dbh->do("ALTER TABLE overduerules_transport_types
36
              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,
38
              DROP COLUMN branchcode,
39
              DROP COLUMN categorycode");
40
    my $s = $dbh->prepare("INSERT INTO overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
41
                         ." VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
42
    foreach my $mtt(@$mtts){
43
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
44
    }
45
    $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
#   SetVersion ($DBversion);
48
#}
49
50
print "\nDone\n";

Return to bug 12772