Attachment #90360 for bug #22421

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




$DBversion = 'XXX';    # will be replaced by the RM
if ( CheckVersion($DBversion) ) {

    # Add old_issue_id field
    unless ( column_exists( 'accountlines', 'old_issue_id' ) ) {
        $dbh->do(q{
            ALTER TABLE accountlines 
            ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id
        });
        $dbh->do(q{
            ALTER TABLE accountlines 
            ADD CONSTRAINT `accountlines_ibfk_old_issues` 
              FOREIGN KEY (`old_issue_id`) 
              REFERENCES `old_issues` (`issue_id`) 
              ON DELETE SET NULL 
              ON UPDATE CASCADE
        });
        $dbh->do(q{
            UPDATE
              accountlines a
              LEFT JOIN old_issues o ON (a.issue_id = o.issue_id)
            SET
              a.old_issue_id = o.issue_id,
              a.issue_id = NULL
            WHERE
              o.issue_id IS NOT NULL
        });

        # Add constraint for issue_id
        unless (
            foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) )
        {
            $dbh->do(q{
                UPDATE
                  accountlines a
                  LEFT JOIN issues i ON (a.issue_id = i.issue_id)
                SET
                  a.issue_id = NULL
                WHERE
                  i.issue_id IS NULL
            });
            $dbh->do(q{
                ALTER TABLE accountlines
                ADD CONSTRAINT `accountlines_ibfk_issues` 
                  FOREIGN KEY (`issue_id`) 
                  REFERENCES `issues` (`issue_id`) 
                  ON DELETE SET NULL 
                  ON UPDATE CASCADE
            });
        }
    }

    SetVersion($DBversion);
    print "Upgrade to $DBversion done (Bug 22421 - Add old_issue_id to accountlines and setup appropriate constraints)\n";
}

Lines 2672-2677 DROP TABLE IF EXISTS `accountlines`; Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +5 lines)
2672	CREATE TABLE `accountlines` (	2672	CREATE TABLE `accountlines` (
2673	`accountlines_id` int(11) NOT NULL AUTO_INCREMENT,	2673	`accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
2674	`issue_id` int(11) NULL DEFAULT NULL,	2674	`issue_id` int(11) NULL DEFAULT NULL,
		2675	`old_issue_id` int(11) NULL DEFAULT NULL,
2675	`borrowernumber` int(11) DEFAULT NULL,	2676	`borrowernumber` int(11) DEFAULT NULL,
2676	`itemnumber` int(11) default NULL,	2677	`itemnumber` int(11) default NULL,
2677	`date` date default NULL,	2678	`date` date default NULL,
Lines 2691-2699 CREATE TABLE `accountlines` ( Link Here
2691	KEY `timeidx` (`timestamp`),	2692	KEY `timeidx` (`timestamp`),
2692	KEY `itemnumber` (`itemnumber`),	2693	KEY `itemnumber` (`itemnumber`),
2693	KEY `branchcode` (`branchcode`),	2694	KEY `branchcode` (`branchcode`),
		2695	KEY `issue_id` (`issue_id`),
		2696	KEY `old_issue_id` (`old_issue_id`),
2694	KEY `manager_id` (`manager_id`),	2697	KEY `manager_id` (`manager_id`),
2695	CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,	2698	CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2696	CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,	2699	CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
		2700	CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
		2701	CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
2697	CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,	2702	CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
2698	CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE	2703	CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
2699	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	2704	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2700	-

Return to bug 22421

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_22421.perl (+55 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if ( CheckVersion($DBversion) ) {
	3
	4	# Add old_issue_id field
	5	unless ( column_exists( 'accountlines', 'old_issue_id' ) ) {
	6	$dbh->do(q{
	7	ALTER TABLE accountlines
	8	ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id
	9	});
	10	$dbh->do(q{
	11	ALTER TABLE accountlines
	12	ADD CONSTRAINT `accountlines_ibfk_old_issues`
	13	FOREIGN KEY (`old_issue_id`)
	14	REFERENCES `old_issues` (`issue_id`)
	15	ON DELETE SET NULL
	16	ON UPDATE CASCADE
	17	});
	18	$dbh->do(q{
	19	UPDATE
	20	accountlines a
	21	LEFT JOIN old_issues o ON (a.issue_id = o.issue_id)
	22	SET
	23	a.old_issue_id = o.issue_id,
	24	a.issue_id = NULL
	25	WHERE
	26	o.issue_id IS NOT NULL
	27	});
	28
	29	# Add constraint for issue_id
	30	unless (
	31	foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) )
	32	{
	33	$dbh->do(q{
	34	UPDATE
	35	accountlines a
	36	LEFT JOIN issues i ON (a.issue_id = i.issue_id)
	37	SET
	38	a.issue_id = NULL
	39	WHERE
	40	i.issue_id IS NULL
	41	});
	42	$dbh->do(q{
	43	ALTER TABLE accountlines
	44	ADD CONSTRAINT `accountlines_ibfk_issues`
	45	FOREIGN KEY (`issue_id`)
	46	REFERENCES `issues` (`issue_id`)
	47	ON DELETE SET NULL
	48	ON UPDATE CASCADE
	49	});
	50	}
	51	}
	52
	53	SetVersion($DBversion);
	54	print "Upgrade to $DBversion done (Bug 22421 - Add old_issue_id to accountlines and setup appropriate constraints)\n";
	55	}