Attachment #102655 for bug #25037

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




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

    # ISSUES
    # Convert issues.onsite_checkout to checkout_type => 'CHECKOUT' / 'ONSITE'
    if ( ! column_exists( 'issues', 'checkout_type' ) ) {
        $dbh->do( q|
            ALTER TABLE issues ADD COLUMN checkout_type ENUM('CHECKOUT', 'ONSITE') NOT NULL DEFAULT 'CHECKOUT' AFTER onsite_checkout;
        | );
    }
    if ( column_exists( 'issues', 'onsite_checkout' ) && column_exists( 'issues', 'checkout_type' ) ) {
        $dbh->do( q|
            UPDATE issues SET checkout_type='CHECKOUT' WHERE onsite_checkout<>1;
        | );
        $dbh->do( q|
            UPDATE issues SET checkout_type='ONSITE' WHERE onsite_checkout=1;
        | );
    }
    if ( column_exists( 'issues', 'onsite_checkout' ) ) {
        $dbh->do( q|
            ALTER TABLE issues DROP COLUMN onsite_checkout;
        | );
    }

    # OLD_ISSUES
    # Convert old_issues.onsite_checkout to checkout_type => 'CHECKOUT' / 'ONSITE'
    if ( ! column_exists( 'old_issues', 'checkout_type' ) ) {
        $dbh->do( q|
            ALTER TABLE old_issues ADD COLUMN checkout_type ENUM('CHECKOUT', 'ONSITE') NOT NULL DEFAULT 'CHECKOUT' AFTER onsite_checkout;
        | );
    }
    if ( column_exists( 'old_issues', 'onsite_checkout' ) && column_exists( 'old_issues', 'checkout_type' ) ) {
        $dbh->do( q|
            UPDATE old_issues SET checkout_type='CHECKOUT' WHERE onsite_checkout<>1;
        | );
        $dbh->do( q|
            UPDATE old_issues SET checkout_type='ONSITE' WHERE onsite_checkout=1;
        | );
    }
    if ( column_exists( 'old_issues', 'onsite_checkout' ) ) {
        $dbh->do( q|
            ALTER TABLE old_issues DROP COLUMN onsite_checkout;
        | );
    }


    # Always end with this (adjust the bug info)
    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 25037 - Convert issues.onsite_checkout to issues.checkout_type column)\n";
}




  `auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
  `issuedate` datetime default NULL, -- date the item was checked out or issued
  `checkout_type` ENUM('CHECKOUT', 'ONSITE') NOT NULL default 'CHECKOUT', -- checkout type
  `note` LONGTEXT default NULL, -- issue note text
  `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
  `noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null

  KEY `branchcode_idx` (`branchcode`),
  KEY `bordate` (`borrowernumber`,`timestamp`),
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--

  `auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
  `issuedate` datetime default NULL, -- date the item was checked out or issued
  `checkout_type` ENUM('CHECKOUT', 'ONSITE') NOT NULL default 'CHECKOUT', -- checkout type
  `note` LONGTEXT default NULL, -- issue note text
  `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
  `noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null

    ON DELETE SET NULL ON UPDATE SET NULL,
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
    ON DELETE SET NULL ON UPDATE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
- 

Return to bug 25037

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_25037_2_convert_onsite_checkout_to_checkout_type.perl (+50 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if( CheckVersion( $DBversion ) ) {
	3
	4	# ISSUES
	5	# Convert issues.onsite_checkout to checkout_type => 'CHECKOUT' / 'ONSITE'
	6	if ( ! column_exists( 'issues', 'checkout_type' ) ) {
	7	$dbh->do( q\|
	8	ALTER TABLE issues ADD COLUMN checkout_type ENUM('CHECKOUT', 'ONSITE') NOT NULL DEFAULT 'CHECKOUT' AFTER onsite_checkout;
	9	\| );
	10	}
	11	if ( column_exists( 'issues', 'onsite_checkout' ) && column_exists( 'issues', 'checkout_type' ) ) {
	12	$dbh->do( q\|
	13	UPDATE issues SET checkout_type='CHECKOUT' WHERE onsite_checkout<>1;
	14	\| );
	15	$dbh->do( q\|
	16	UPDATE issues SET checkout_type='ONSITE' WHERE onsite_checkout=1;
	17	\| );
	18	}
	19	if ( column_exists( 'issues', 'onsite_checkout' ) ) {
	20	$dbh->do( q\|
	21	ALTER TABLE issues DROP COLUMN onsite_checkout;
	22	\| );
	23	}
	24
	25	# OLD_ISSUES
	26	# Convert old_issues.onsite_checkout to checkout_type => 'CHECKOUT' / 'ONSITE'
	27	if ( ! column_exists( 'old_issues', 'checkout_type' ) ) {
	28	$dbh->do( q\|
	29	ALTER TABLE old_issues ADD COLUMN checkout_type ENUM('CHECKOUT', 'ONSITE') NOT NULL DEFAULT 'CHECKOUT' AFTER onsite_checkout;
	30	\| );
	31	}
	32	if ( column_exists( 'old_issues', 'onsite_checkout' ) && column_exists( 'old_issues', 'checkout_type' ) ) {
	33	$dbh->do( q\|
	34	UPDATE old_issues SET checkout_type='CHECKOUT' WHERE onsite_checkout<>1;
	35	\| );
	36	$dbh->do( q\|
	37	UPDATE old_issues SET checkout_type='ONSITE' WHERE onsite_checkout=1;
	38	\| );
	39	}
	40	if ( column_exists( 'old_issues', 'onsite_checkout' ) ) {
	41	$dbh->do( q\|
	42	ALTER TABLE old_issues DROP COLUMN onsite_checkout;
	43	\| );
	44	}
	45
	46
	47	# Always end with this (adjust the bug info)
	48	SetVersion( $DBversion );
	49	print "Upgrade to $DBversion done (Bug 25037 - Convert issues.onsite_checkout to issues.checkout_type column)\n";
	50	}

Lines 1638-1644 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here

(-)a/installer/data/mysql/kohastructure.sql (-4 / +4 lines)
1638	`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error	1638	`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
1639	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched	1639	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1640	`issuedate` datetime default NULL, -- date the item was checked out or issued	1640	`issuedate` datetime default NULL, -- date the item was checked out or issued
1641	`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag	1641	`checkout_type` ENUM('CHECKOUT', 'ONSITE') NOT NULL default 'CHECKOUT', -- checkout type
1642	`note` LONGTEXT default NULL, -- issue note text	1642	`note` LONGTEXT default NULL, -- issue note text
1643	`notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)	1643	`notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
1644	`noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null	1644	`noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null
Lines 1649-1655 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
1649	KEY `branchcode_idx` (`branchcode`),	1649	KEY `branchcode_idx` (`branchcode`),
1650	KEY `bordate` (`borrowernumber`,`timestamp`),	1650	KEY `bordate` (`borrowernumber`,`timestamp`),
1651	CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,	1651	CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1652	CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE	1652	CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1653	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1653	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1654		1654
1655	--	1655	--
Lines 1670-1676 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1670	`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error	1670	`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
1671	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched	1671	`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1672	`issuedate` datetime default NULL, -- date the item was checked out or issued	1672	`issuedate` datetime default NULL, -- date the item was checked out or issued
1673	`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag	1673	`checkout_type` ENUM('CHECKOUT', 'ONSITE') NOT NULL default 'CHECKOUT', -- checkout type
1674	`note` LONGTEXT default NULL, -- issue note text	1674	`note` LONGTEXT default NULL, -- issue note text
1675	`notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)	1675	`notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
1676	`noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null	1676	`noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null
Lines 1683-1688 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1683	ON DELETE SET NULL ON UPDATE SET NULL,	1683	ON DELETE SET NULL ON UPDATE SET NULL,
1684	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)	1684	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1685	ON DELETE SET NULL ON UPDATE SET NULL	1685	ON DELETE SET NULL ON UPDATE SET NULL
		1686
1686	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1687	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1687		1688
1688	--	1689	--
1689	-