Attachment #40330 for bug #13624

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




use C4::Context;
my $dbh=C4::Context->dbh;

print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types\n";

#if ( CheckVersion($DBversion) ) {
    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
    $dbh->do("ALTER TABLE overduerules RENAME old_overduerules");
    $dbh->do("CREATE TABLE overduerules (
        `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
        `branchcode` varchar(10) NOT NULL DEFAULT '',
        `categorycode` varchar(10) NOT NULL DEFAULT '',
        `delay1` int(4) DEFAULT NULL,

        `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;");
    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
    $dbh->do("DROP TABLE old_overduerules");
    $dbh->do("ALTER TABLE overduerules_transport_types
              ADD COLUMN overduerules_id int(11) NOT NULL");
    my $mtts = $dbh->selectall_arrayref("SELECT * FROM overduerules_transport_types", { Slice => {} });
    $dbh->do("DELETE FROM overduerules_transport_types");
    $dbh->do("ALTER TABLE overduerules_transport_types
              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");
    my $s = $dbh->prepare("INSERT INTO overduerules_transport_types (overduerules_id, id, letternumber, 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->{letternumber}, $mtt->{message_transport_type} );
    }





DROP TABLE IF EXISTS `overduerules`;
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
  `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
  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent

  `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;

--

CREATE TABLE overduerules_transport_types(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
    `overduerules_id` INT(11) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT overduerules_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
- 

Return to bug 13624

Lines 5-17 use warnings; Link Here

(-)a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl (-7 / +8 lines)
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	}

Lines 1706-1712 CREATE TABLE `opac_news` ( -- data from the news tool Link Here

(-)a/installer/data/mysql/kohastructure.sql (-4 / +4 lines)
1706		1706
1707	DROP TABLE IF EXISTS `overduerules`;	1707	DROP TABLE IF EXISTS `overduerules`;
1708	CREATE TABLE `overduerules` ( -- overdue notice status and triggers	1708	CREATE TABLE `overduerules` ( -- overdue notice status and triggers
1709	`overduerules_id` mediumint NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules	1709	`overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1710	`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	`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)
1711	`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for	1711	`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1712	`delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent	1712	`delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
Lines 1718-1724 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here
1718	`delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent	1718	`delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1719	`letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice	1719	`letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1720	`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)	1720	`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1721	PRIMARY KEY (`overduerules_id`)	1721	PRIMARY KEY (`overduerules_id`),
		1722	UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
1722	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	1723	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1723		1724
1724	--	1725	--
Lines 2594-2600 DROP TABLE IF EXISTS `overduerules_transport_types`; Link Here
2594	CREATE TABLE overduerules_transport_types(	2595	CREATE TABLE overduerules_transport_types(
2595	`id` INT(11) NOT NULL AUTO_INCREMENT,	2596	`id` INT(11) NOT NULL AUTO_INCREMENT,
2596	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',	2597	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2597	`overduerules_id` mediumint NOT NULL,	2598	`overduerules_id` INT(11) NOT NULL,
2598	PRIMARY KEY (id),	2599	PRIMARY KEY (id),
2599	CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,	2600	CONSTRAINT overduerules_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	2601	CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
2601	-