Attachment #111485 for bug #23916

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




$DBversion = 'XXX'; # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {
    if( !column_exists( 'issues', 'issuer' ) ) {
        $dbh->do( q| ALTER TABLE issues ADD issuer INT(11) AFTER borrowernumber | );
    }
    if (!foreign_key_exists( 'issues', 'issues_ibfk_borrowers_borrowernumber' )) {
        $dbh->do( q| ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE | );
    }
    if( !column_exists( 'old_issues', 'issuer' ) ) {
        $dbh->do( q| ALTER TABLE old_issues ADD issuer INT(11) AFTER borrowernumber | );
    }
    if (!foreign_key_exists( 'old_issues', 'old_issues_ibfk_borrowers_borrowernumber' )) {
        $dbh->do( q| ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE | );
    }

    # Now attempt to migrate previously action logged issues into issues.issuer
    # We assume that an item issued to a borrower on the same day is the same issue
    #
    # Get existing action logs
    # - user = issuer
    # - object = borrowernumber
    # - info = itemnumber
    my $action_logs = $dbh->selectall_arrayref("SELECT DATE(timestamp) AS dt, user, object, info FROM action_logs WHERE module='CIRCULATION' and action='ISSUE'", { Slice => {} });

    foreach my $log( @{$action_logs} ) {
        # Look for an issue of this item, to this borrower, on this day
        # We're doing DATE comparison in the database to avoid invoking
        # DateTime and it's performance sapping ways...
        #
        # If we're dealing with an actual borrower
        if ($log->{user} != 0) {
            my $done_issue = $dbh->do(
                "UPDATE issues SET issuer = ? WHERE DATE(timestamp) = ? AND borrowernumber = ? AND itemnumber = ?",
                undef,
                ( $log->{user}, $log->{dt}, $log->{object}, $log->{info} )
            );
            # If we didn't find the issue in 'issues', look in 'old_issues'
            if (!$done_issue) {
                $dbh->do(
                    "UPDATE old_issues SET issuer = ? WHERE DATE(timestamp) = ? AND borrowernumber = ? AND itemnumber = ?",
                    undef,
                    ( $log->{user}, $log->{dt}, $log->{object}, $log->{info} )
                );
            }
        }
    }

    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 23916 - Add issues.issuer)\n";
}




CREATE TABLE `issues` ( -- information related to check outs or issues
  `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
  `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
  `issuer` int(11), -- foreign key, linking this to the borrowers table for the user who checked out this item
  `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
  `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
  `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out

  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_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--

CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
  `issue_id` int(11) NOT NULL, -- primary key for issues table
  `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
  `issuer` int(11) default NULL, -- foreign key, linking this to the borrowers table for the user who checked out this item
  `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
  `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
  `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out

  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
    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_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
- 

Return to bug 23916

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_23916_add_issues_issuer.perl (+50 lines)
	1	$DBversion = 'XXX'; # will be replaced by the RM
	2	if( CheckVersion( $DBversion ) ) {
	3	if( !column_exists( 'issues', 'issuer' ) ) {
	4	$dbh->do( q\| ALTER TABLE issues ADD issuer INT(11) AFTER borrowernumber \| );
	5	}
	6	if (!foreign_key_exists( 'issues', 'issues_ibfk_borrowers_borrowernumber' )) {
	7	$dbh->do( q\| ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE \| );
	8	}
	9	if( !column_exists( 'old_issues', 'issuer' ) ) {
	10	$dbh->do( q\| ALTER TABLE old_issues ADD issuer INT(11) AFTER borrowernumber \| );
	11	}
	12	if (!foreign_key_exists( 'old_issues', 'old_issues_ibfk_borrowers_borrowernumber' )) {
	13	$dbh->do( q\| ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE \| );
	14	}
	15
	16	# Now attempt to migrate previously action logged issues into issues.issuer
	17	# We assume that an item issued to a borrower on the same day is the same issue
	18	#
	19	# Get existing action logs
	20	# - user = issuer
	21	# - object = borrowernumber
	22	# - info = itemnumber
	23	my $action_logs = $dbh->selectall_arrayref("SELECT DATE(timestamp) AS dt, user, object, info FROM action_logs WHERE module='CIRCULATION' and action='ISSUE'", { Slice => {} });
	24
	25	foreach my $log( @{$action_logs} ) {
	26	# Look for an issue of this item, to this borrower, on this day
	27	# We're doing DATE comparison in the database to avoid invoking
	28	# DateTime and it's performance sapping ways...
	29	#
	30	# If we're dealing with an actual borrower
	31	if ($log->{user} != 0) {
	32	my $done_issue = $dbh->do(
	33	"UPDATE issues SET issuer = ? WHERE DATE(timestamp) = ? AND borrowernumber = ? AND itemnumber = ?",
	34	undef,
	35	( $log->{user}, $log->{dt}, $log->{object}, $log->{info} )
	36	);
	37	# If we didn't find the issue in 'issues', look in 'old_issues'
	38	if (!$done_issue) {
	39	$dbh->do(
	40	"UPDATE old_issues SET issuer = ? WHERE DATE(timestamp) = ? AND borrowernumber = ? AND itemnumber = ?",
	41	undef,
	42	( $log->{user}, $log->{dt}, $log->{object}, $log->{info} )
	43	);
	44	}
	45	}
	46	}
	47
	48	SetVersion( $DBversion );
	49	print "Upgrade to $DBversion done (Bug 23916 - Add issues.issuer)\n";
	50	}

Lines 1617-1622 DROP TABLE IF EXISTS `issues`; Link Here

(-)a/installer/data/mysql/kohastructure.sql (-3 / +7 lines)
1617	CREATE TABLE `issues` ( -- information related to check outs or issues	1617	CREATE TABLE `issues` ( -- information related to check outs or issues
1618	`issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table	1618	`issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
1619	`borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to	1619	`borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
		1620	`issuer` int(11), -- foreign key, linking this to the borrowers table for the user who checked out this item
1620	`itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out	1621	`itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
1621	`date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)	1622	`date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
1622	`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out	1623	`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
Lines 1638-1644 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
1638	KEY `branchcode_idx` (`branchcode`),	1639	KEY `branchcode_idx` (`branchcode`),
1639	KEY `bordate` (`borrowernumber`,`timestamp`),	1640	KEY `bordate` (`borrowernumber`,`timestamp`),
1640	CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,	1641	CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1641	CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE	1642	CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
		1643	CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
1642	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1644	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1643		1645
1644	--	1646	--
Lines 1649-1654 DROP TABLE IF EXISTS `old_issues`; Link Here
1649	CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned	1651	CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
1650	`issue_id` int(11) NOT NULL, -- primary key for issues table	1652	`issue_id` int(11) NOT NULL, -- primary key for issues table
1651	`borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to	1653	`borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
		1654	`issuer` int(11) default NULL, -- foreign key, linking this to the borrowers table for the user who checked out this item
1652	`itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out	1655	`itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
1653	`date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)	1656	`date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
1654	`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out	1657	`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
Lines 1671-1677 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1671	CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)	1674	CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1672	ON DELETE SET NULL ON UPDATE SET NULL,	1675	ON DELETE SET NULL ON UPDATE SET NULL,
1673	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)	1676	CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1674	ON DELETE SET NULL ON UPDATE SET NULL	1677	ON DELETE SET NULL ON UPDATE SET NULL,
		1678	CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`)
		1679	ON DELETE SET NULL ON UPDATE CASCADE
1675	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	1680	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1676		1681
1677	--	1682	--
1678	-