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 |
}; |