Attachment #45547 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 1706-1722 CREATE TABLE `overduerules` ( -- overdue notice status and triggers Link Here

(-)a/installer/data/mysql/kohastructure.sql (-13 / +7 lines)
1706	`overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules	1706	`overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
1707	`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	`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)
1708	`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for	1708	`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
1709	`delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent	1709	`itemtype` varchar(10) default '',
1710	`letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice	1710	`onhold` tinyint(1) default 0,
1711	`debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)	1711	`delay` int(4) NOT NULL default 1,
1712	`delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent	1712	`letter` varchar(20) default NULL,
1713	`debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)	1713	`debarred` varchar(1) default 0,
1714	`letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice	1714	PRIMARY KEY (`overduerules_id`)
1715	`delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
1716	`letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
1717	`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
1718	PRIMARY KEY (`overduerules_id`),
1719	UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
1720	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	1715	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1721		1716
1722	--	1717	--
Lines 2581-2587 CREATE TABLE overduerules_transport_types( Link Here
2581	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',	2576	`message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
2582	`overduerules_id` INT(11) NOT NULL,	2577	`overduerules_id` INT(11) NOT NULL,
2583	PRIMARY KEY (id),	2578	PRIMARY KEY (id),
2584	CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,	2579	CONSTRAINT overduerules_transport_types_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) 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	2580	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;	2581	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
2587		2582
2588	-