Attachment #102421 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 => null / 'OS'
    if ( ! column_exists( 'issues', 'checkout_type' ) ) {
        $dbh->do( q|
            ALTER TABLE issues ADD COLUMN checkout_type VARCHAR(80) DEFAULT 'C' AFTER onsite_checkout;
        | );
    }
    if ( column_exists( 'issues', 'onsite_checkout' ) && column_exists( 'issues', 'checkout_type' ) ) {
        $dbh->do( q|
            UPDATE issues SET checkout_type='C' WHERE onsite_checkout<>1;
        | );
        $dbh->do( q|
            UPDATE issues SET checkout_type='OS' WHERE onsite_checkout=1;
        | );
    }
    if ( column_exists( 'issues', 'onsite_checkout' ) ) {
        $dbh->do( q|
            ALTER TABLE issues DROP COLUMN onsite_checkout;
        | );
    }

    # Add a foreign key between old_issues.checkout_type and authorised_values.authorised_value
    if ( !foreign_key_exists( 'issues', 'issues_ctfk' ) ) {
        $dbh->do( "ALTER TABLE issues ADD CONSTRAINT issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" );
    }

    # OLD_ISSUES
    # Convert old_issues.onsite_checkout to checkout_type => null / 'OS'
    if ( ! column_exists( 'old_issues', 'checkout_type' ) ) {
        $dbh->do( q|
            ALTER TABLE old_issues ADD COLUMN checkout_type VARCHAR(80) DEFAULT NULL 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='C' WHERE onsite_checkout<>1;
        | );
        $dbh->do( q|
            UPDATE old_issues SET checkout_type='OS' WHERE onsite_checkout=1;
        | );
    }
    if ( column_exists( 'old_issues', 'onsite_checkout' ) ) {
        $dbh->do( q|
            ALTER TABLE old_issues DROP COLUMN onsite_checkout;
        | );
    }

    # Add a foreign key between issues.checkout_type and authorised_values.authorised_value
    if ( !foreign_key_exists( 'old_issues', 'old_issues_ctfk' ) ) {
        $dbh->do( "ALTER TABLE old_issues ADD CONSTRAINT old_issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" );
    }

    # Always end with this (adjust the bug info)
    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 25037 - Add issues.checkout_type column, convert issues.onsite_checkout=1 to issues.checkout_type=OS, delete issues.onsite_checkout 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` varchar(80) default "C", -- checkout type, default C means normal checkout
  `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,
  CONSTRAINT `issues_ctfk`   FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`) ON DELETE SET NULL 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` varchar(80) default "C", -- checkout type, default C means normal checkout
  `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
  CONSTRAINT `old_issues_ctfk`   FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`)
    ON DELETE SET NULL ON UPDATE CASCADE

) 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 (+59 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 => null / 'OS'
	6	if ( ! column_exists( 'issues', 'checkout_type' ) ) {
	7	$dbh->do( q\|
	8	ALTER TABLE issues ADD COLUMN checkout_type VARCHAR(80) DEFAULT 'C' 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='C' WHERE onsite_checkout<>1;
	14	\| );
	15	$dbh->do( q\|
	16	UPDATE issues SET checkout_type='OS' 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	# Add a foreign key between old_issues.checkout_type and authorised_values.authorised_value
	26	if ( !foreign_key_exists( 'issues', 'issues_ctfk' ) ) {
	27	$dbh->do( "ALTER TABLE issues ADD CONSTRAINT issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" );
	28	}
	29
	30	# OLD_ISSUES
	31	# Convert old_issues.onsite_checkout to checkout_type => null / 'OS'
	32	if ( ! column_exists( 'old_issues', 'checkout_type' ) ) {
	33	$dbh->do( q\|
	34	ALTER TABLE old_issues ADD COLUMN checkout_type VARCHAR(80) DEFAULT NULL AFTER onsite_checkout;
	35	\| );
	36	}
	37	if ( column_exists( 'old_issues', 'onsite_checkout' ) && column_exists( 'old_issues', 'checkout_type' ) ) {
	38	$dbh->do( q\|
	39	UPDATE old_issues SET checkout_type='C' WHERE onsite_checkout<>1;
	40	\| );
	41	$dbh->do( q\|
	42	UPDATE old_issues SET checkout_type='OS' WHERE onsite_checkout=1;
	43	\| );
	44	}
	45	if ( column_exists( 'old_issues', 'onsite_checkout' ) ) {
	46	$dbh->do( q\|
	47	ALTER TABLE old_issues DROP COLUMN onsite_checkout;
	48	\| );
	49	}
	50
	51	# Add a foreign key between issues.checkout_type and authorised_values.authorised_value
	52	if ( !foreign_key_exists( 'old_issues', 'old_issues_ctfk' ) ) {
	53	$dbh->do( "ALTER TABLE old_issues ADD CONSTRAINT old_issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" );
	54	}
	55
	56	# Always end with this (adjust the bug info)
	57	SetVersion( $DBversion );
	58	print "Upgrade to $DBversion done (Bug 25037 - Add issues.checkout_type column, convert issues.onsite_checkout=1 to issues.checkout_type=OS, delete issues.onsite_checkout column )\n";
	59	}

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

(-)a/installer/data/mysql/kohastructure.sql (-4 / +7 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` varchar(80) default "C", -- checkout type, default C means normal checkout
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	CONSTRAINT `issues_ctfk` FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`) ON DELETE SET NULL ON UPDATE CASCADE
1653	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1654	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1654		1655
1655	--	1656	--
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	1671	`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	1672	`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	1673	`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	1674	`checkout_type` varchar(80) default "C", -- checkout type, default C means normal checkout
1674	`note` LONGTEXT default NULL, -- issue note text	1675	`note` LONGTEXT default NULL, -- issue note text
1675	`notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)	1676	`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	1677	`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,	1684	ON DELETE SET NULL ON UPDATE SET NULL,
1684	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)	1685	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1685	ON DELETE SET NULL ON UPDATE SET NULL	1686	ON DELETE SET NULL ON UPDATE SET NULL
		1687	CONSTRAINT `old_issues_ctfk` FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`)
		1688	ON DELETE SET NULL ON UPDATE CASCADE
		1689
1686	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1690	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1687		1691
1688	--	1692	--
1689	-