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

(-)a/installer/data/mysql/atomicupdate/bug_40808.pl (+77 lines)
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  => "40808",
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: hold',
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_old_reserves
68
            AFTER DELETE ON old_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
        say_success( $out, "Created database triggers for referential integrity" );
76
    },
77
};
(-)a/installer/data/mysql/kohastructure.sql (-1 / +22 lines)
Lines 160-165 CREATE TABLE `accountlines` ( Link Here
160
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
160
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
161
/*!40101 SET character_set_client = @saved_cs_client */;
161
/*!40101 SET character_set_client = @saved_cs_client */;
162
162
163
--
164
-- Table structure for table `accountline_links`
165
--
166
167
DROP TABLE IF EXISTS `accountline_links`;
168
/*!40101 SET @saved_cs_client     = @@character_set_client */;
169
/*!40101 SET character_set_client = utf8mb4 */;
170
CREATE TABLE `accountline_links` (
171
  `link_id` int(11) NOT NULL AUTO_INCREMENT,
172
  `accountlines_id` int(11) NOT NULL COMMENT 'foreign key to accountlines.accountlines_id',
173
  `link_type` varchar(32) NOT NULL COMMENT 'Type of linked entity: hold',
174
  `linked_id` int(11) NOT NULL COMMENT 'ID of the linked entity',
175
  `created_on` timestamp NOT NULL DEFAULT current_timestamp(),
176
  PRIMARY KEY (`link_id`),
177
  UNIQUE KEY `accountline_link_unique` (`accountlines_id`, `link_type`, `linked_id`),
178
  KEY `reverse_lookup` (`link_type`, `linked_id`, `accountlines_id`),
179
  KEY `accountlines_id` (`accountlines_id`),
180
  KEY `entity_lookup` (`link_type`, `linked_id`),
181
  CONSTRAINT `accountline_links_ibfk_accountlines` FOREIGN KEY (`accountlines_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE
182
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
183
/*!40101 SET character_set_client = @saved_cs_client */;
184
163
--
185
--
164
-- Table structure for table `action_logs`
186
-- Table structure for table `action_logs`
165
--
187
--
166
- 

Return to bug 40808