Attachment #45185 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";




CREATE TABLE `overduerules` ( -- overdue notice status and triggers
  `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  (`branchcode`,`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

    `letternumber` INT(1) NOT NULL DEFAULT 1,
    `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
    PRIMARY KEY (id),
<<<<<<< HEAD
    CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,
=======
    CONSTRAINT overduerules_transport_types_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
>>>>>>> 6be069d... Bug 12772 - Update Database (Based on Bug13624)
    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 1705-1720 DROP TABLE IF EXISTS `overduerules`; Link Here

(-)a/installer/data/mysql/kohastructure.sql (-11 / +10 lines)
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	-