|
Line 0
Link Here
|
|
|
1 |
use Modern::Perl; |
| 2 |
use Koha::Installer::Output qw(say_warning say_failure say_success say_info); |
| 3 |
|
| 4 |
return { |
| 5 |
bug_number => "XXXXX", |
| 6 |
description => "Add accountline_links table for polymorphic linking of accountlines to holds, issues, etc.", |
| 7 |
up => sub { |
| 8 |
my ($args) = @_; |
| 9 |
my ( $dbh, $out ) = @$args{qw(dbh out)}; |
| 10 |
|
| 11 |
# Create accountline_links table |
| 12 |
unless ( TableExists('accountline_links') ) { |
| 13 |
$dbh->do( |
| 14 |
q{ |
| 15 |
CREATE TABLE `accountline_links` ( |
| 16 |
`link_id` int(11) NOT NULL AUTO_INCREMENT, |
| 17 |
`accountlines_id` int(11) NOT NULL COMMENT 'foreign key to accountlines.accountlines_id', |
| 18 |
`link_type` varchar(32) NOT NULL COMMENT 'Type of linked entity: reserve, old_reserve, issue, old_issue, article_request, etc.', |
| 19 |
`linked_id` int(11) NOT NULL COMMENT 'ID of the linked entity', |
| 20 |
`created_on` timestamp NOT NULL DEFAULT current_timestamp(), |
| 21 |
PRIMARY KEY (`link_id`), |
| 22 |
UNIQUE KEY `accountline_link_unique` (`accountlines_id`, `link_type`, `linked_id`), |
| 23 |
KEY `reverse_lookup` (`link_type`, `linked_id`, `accountlines_id`), |
| 24 |
KEY `accountlines_id` (`accountlines_id`), |
| 25 |
KEY `entity_lookup` (`link_type`, `linked_id`), |
| 26 |
CONSTRAINT `accountline_links_ibfk_accountlines` |
| 27 |
FOREIGN KEY (`accountlines_id`) REFERENCES `accountlines` (`accountlines_id`) |
| 28 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 29 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
| 30 |
} |
| 31 |
); |
| 32 |
say_success( $out, "Created accountline_links table" ); |
| 33 |
} else { |
| 34 |
say_info( $out, "accountline_links table already exists" ); |
| 35 |
} |
| 36 |
|
| 37 |
# Migrate existing issue_id links |
| 38 |
if ( column_exists( 'accountlines', 'issue_id' ) ) { |
| 39 |
$dbh->do( |
| 40 |
q{ |
| 41 |
INSERT IGNORE INTO accountline_links (accountlines_id, link_type, linked_id) |
| 42 |
SELECT accountlines_id, 'checkout', issue_id |
| 43 |
FROM accountlines |
| 44 |
WHERE issue_id IS NOT NULL |
| 45 |
} |
| 46 |
); |
| 47 |
say_success( $out, "Migrated existing issue_id links to accountline_links" ); |
| 48 |
} |
| 49 |
|
| 50 |
# Migrate existing old_issue_id links |
| 51 |
if ( column_exists( 'accountlines', 'old_issue_id' ) ) { |
| 52 |
$dbh->do( |
| 53 |
q{ |
| 54 |
INSERT IGNORE INTO accountline_links (accountlines_id, link_type, linked_id) |
| 55 |
SELECT accountlines_id, 'old_checkout', old_issue_id |
| 56 |
FROM accountlines |
| 57 |
WHERE old_issue_id IS NOT NULL |
| 58 |
} |
| 59 |
); |
| 60 |
say_success( $out, "Migrated existing old_issue_id links to accountline_links" ); |
| 61 |
} |
| 62 |
|
| 63 |
# Add database triggers for maintaining referential integrity |
| 64 |
# Cleanup links when reserves are deleted |
| 65 |
$dbh->do( |
| 66 |
q{ |
| 67 |
CREATE TRIGGER accountline_links_cleanup_reserves |
| 68 |
AFTER DELETE ON reserves |
| 69 |
FOR EACH ROW |
| 70 |
DELETE FROM accountline_links |
| 71 |
WHERE link_type = 'hold' AND linked_id = OLD.reserve_id |
| 72 |
} |
| 73 |
); |
| 74 |
|
| 75 |
$dbh->do( |
| 76 |
q{ |
| 77 |
CREATE TRIGGER accountline_links_cleanup_old_reserves |
| 78 |
AFTER DELETE ON old_reserves |
| 79 |
FOR EACH ROW |
| 80 |
DELETE FROM accountline_links |
| 81 |
WHERE link_type = 'old_hold' AND linked_id = OLD.reserve_id |
| 82 |
} |
| 83 |
); |
| 84 |
|
| 85 |
# Cleanup links when issues are deleted |
| 86 |
$dbh->do( |
| 87 |
q{ |
| 88 |
CREATE TRIGGER accountline_links_cleanup_issues |
| 89 |
AFTER DELETE ON issues |
| 90 |
FOR EACH ROW |
| 91 |
DELETE FROM accountline_links |
| 92 |
WHERE link_type = 'checkout' AND linked_id = OLD.issue_id |
| 93 |
} |
| 94 |
); |
| 95 |
|
| 96 |
$dbh->do( |
| 97 |
q{ |
| 98 |
CREATE TRIGGER accountline_links_cleanup_old_issues |
| 99 |
AFTER DELETE ON old_issues |
| 100 |
FOR EACH ROW |
| 101 |
DELETE FROM accountline_links |
| 102 |
WHERE link_type = 'old_checkout' AND linked_id = OLD.issue_id |
| 103 |
} |
| 104 |
); |
| 105 |
|
| 106 |
say_success( $out, "Created database triggers for referential integrity" ); |
| 107 |
|
| 108 |
say_success( $out, "Bug XXXXX: accountline_links polymorphic linking system implemented successfully" ); |
| 109 |
}, |
| 110 |
}; |