Attachment #41477 for bug #12772

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




        `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
        `branchcode` varchar(10) NOT NULL DEFAULT '',
        `categorycode` varchar(10) NOT NULL DEFAULT '',
        `itemtype` varchar(10) default '',
        `onhold` tinyint(1) default 0,
        `delay` int(4) NOT NULL default 1,
        `letter` varchar(20) default NULL,
        `debarred` varchar(1) default 0,
        PRIMARY KEY (`overduerules_id`)
        `delay3` int(4) DEFAULT NULL,
        `letter3` varchar(20) DEFAULT NULL,
        `debarred3` int(1) DEFAULT '0',
        PRIMARY KEY (`overduerules_id`),
        UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
     foreach my $i (1..3){
        $dbh->do("INSERT INTO overduerules (delay, letter, debarred, branchcode, categorycode) ".
                 "SELECT delay$i AS delay, letter$i AS letter, debarred$i as debarred, branchcode, categorycode FROM old_overduerules");
     }
    $dbh->do("DROP TABLE old_overduerules");
    $dbh->do("ALTER TABLE overduerules_transport_types
              ADD COLUMN overduerules_id int(11) NOT NULL");

              DROP FOREIGN KEY overduerules_fk,
              ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
              DROP COLUMN branchcode,
              DROP COLUMN categorycode,
              DROP COLUMN letternumber");
    my $s = $dbh->prepare(
        "INSERT INTO overduerules_transport_types (overduerules_id, id, message_transport_type) ".
        "VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?)"
    );
    foreach my $mtt(@$mtts){
        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{message_transport_type} );
    }
    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
#    print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";




  `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
  `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)
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
 `itemtype` varchar(10) default '',
 `onhold` tinyint(1) default 0,
 `delay` int(4) NOT NULL default 1,
 `letter` varchar(20) default NULL,
 `debarred` varchar(1) default 0,
  PRIMARY KEY  (`overduerules_id`)
  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
  PRIMARY KEY  (`overduerules_id`),
  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
    `overduerules_id` INT(11) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT overduerules_transport_types_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

- 

Return to bug 12772

Lines 14-32 print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table Link Here

(-)a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl (-16 / +17 lines)
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";

Lines 1708-1724 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here

(-)a/installer/data/mysql/kohastructure.sql (-13 / +7 lines)
1708	`overduerules_id` int(11) 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	`itemtype` varchar(10) default '',
1712	`letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice	1712	`onhold` tinyint(1) default 0,
1713	`debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)	1713	`delay` int(4) NOT NULL default 1,
1714	`delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent	1714	`letter` varchar(20) default NULL,
1715	`debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)	1715	`debarred` varchar(1) default 0,
1716	`letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice	1716	PRIMARY KEY (`overduerules_id`)
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
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`),
1721	UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
1722	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	1717	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1723		1718
1724	--	1719	--
Lines 2596-2602 CREATE TABLE overduerules_transport_types( Link Here
2596	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',	2591	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2597	`overduerules_id` INT(11) NOT NULL,	2592	`overduerules_id` INT(11) NOT NULL,
2598	PRIMARY KEY (id),	2593	PRIMARY KEY (id),
2599	CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,	2594	CONSTRAINT overduerules_transport_types_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) 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	2595	CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2601	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	2596	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2602		2597
2603	-