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

(-)a/installer/data/mysql/atomicupdate/bug_23916_add_issues_issuer.perl (+50 lines)
Line 0 Link Here
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
}
(-)a/installer/data/mysql/kohastructure.sql (-3 / +7 lines)
Lines 1622-1627 DROP TABLE IF EXISTS `issues`; Link Here
1622
CREATE TABLE `issues` ( -- information related to check outs or issues
1622
CREATE TABLE `issues` ( -- information related to check outs or issues
1623
  `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
1623
  `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
1624
  `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
1624
  `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
1625
  `issuer` int(11), -- foreign key, linking this to the borrowers table for the user who checked out this item
1625
  `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
1626
  `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
1626
  `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
1627
  `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
1627
  `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
1628
  `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
Lines 1643-1649 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
1643
  KEY `branchcode_idx` (`branchcode`),
1644
  KEY `branchcode_idx` (`branchcode`),
1644
  KEY `bordate` (`borrowernumber`,`timestamp`),
1645
  KEY `bordate` (`borrowernumber`,`timestamp`),
1645
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1646
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1646
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
1647
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1648
  CONSTRAINT `issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
1647
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1649
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1648
1650
1649
--
1651
--
Lines 1654-1659 DROP TABLE IF EXISTS `old_issues`; Link Here
1654
CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
1656
CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
1655
  `issue_id` int(11) NOT NULL, -- primary key for issues table
1657
  `issue_id` int(11) NOT NULL, -- primary key for issues table
1656
  `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
1658
  `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
1659
  `issuer` int(11) default NULL, -- foreign key, linking this to the borrowers table for the user who checked out this item
1657
  `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
1660
  `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
1658
  `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
1661
  `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
1659
  `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
1662
  `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
Lines 1676-1682 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1676
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1679
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1677
    ON DELETE SET NULL ON UPDATE SET NULL,
1680
    ON DELETE SET NULL ON UPDATE SET NULL,
1678
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1681
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1679
    ON DELETE SET NULL ON UPDATE SET NULL
1682
    ON DELETE SET NULL ON UPDATE SET NULL,
1683
  CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer`) REFERENCES `borrowers` (`borrowernumber`)
1684
    ON DELETE SET NULL ON UPDATE CASCADE
1680
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1685
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1681
1686
1682
--
1687
--
1683
- 

Return to bug 23916