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

(-)a/installer/data/mysql/atomicupdate/bug_22421.pl (+79 lines)
Line 0 Link Here
1
use Modern::Perl;
2
3
return {
4
    bug_number  => "22421",
5
    description => "Add issue constraints to accountlines",
6
    up          => sub {
7
        my ($args) = @_;
8
        my ( $dbh, $out ) = @$args{qw(dbh out)};
9
10
        # Add old_issue_id field
11
        unless ( column_exists( 'accountlines', 'old_issue_id' ) ) {
12
            $dbh->do(
13
                q{
14
            ALTER TABLE accountlines
15
            ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id
16
            }
17
            );
18
            say $out "Added column 'accountlines.old_issue_id'";
19
20
            $dbh->do(
21
                q{
22
            ALTER TABLE accountlines
23
            ADD CONSTRAINT `accountlines_ibfk_old_issues`
24
              FOREIGN KEY (`old_issue_id`)
25
              REFERENCES `old_issues` (`issue_id`)
26
              ON DELETE SET NULL
27
              ON UPDATE CASCADE
28
            }
29
            );
30
            say $out "Added constraint 'accountlines.old_issues'";
31
32
            $dbh->do(
33
                q{
34
            UPDATE
35
              accountlines a
36
              LEFT JOIN old_issues o ON (a.issue_id = o.issue_id)
37
            SET
38
              a.old_issue_id = o.issue_id,
39
              a.issue_id = NULL
40
            WHERE
41
              o.issue_id IS NOT NULL
42
            }
43
            );
44
            say $out "Updated 'accountlines.old_issue_id' from 'old_issues'";
45
46
            # Add constraint for issue_id
47
            unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) ) {
48
                $dbh->do(
49
                    q{
50
                UPDATE
51
                  accountlines a
52
                  LEFT JOIN issues i ON (a.issue_id = i.issue_id)
53
                SET
54
                  a.issue_id = NULL
55
                WHERE
56
                  i.issue_id IS NULL
57
                }
58
                );
59
                say $out "Fix accountlines.issue_id where missing from issues";
60
61
                $dbh->do(
62
                    q{
63
                ALTER TABLE accountlines
64
                ADD CONSTRAINT `accountlines_ibfk_issues`
65
                  FOREIGN KEY (`issue_id`)
66
                  REFERENCES `issues` (`issue_id`)
67
                  ON DELETE SET NULL
68
                  ON UPDATE CASCADE
69
                }
70
                );
71
                say $out "Added constraint 'accountlines.issues'";
72
            }
73
        }
74
75
        say $out "Add old_issue_id to accountlines and setup appropriate constraints)\n";
76
77
    }
78
79
};
(-)a/installer/data/mysql/kohastructure.sql (-1 / +6 lines)
Lines 119-124 DROP TABLE IF EXISTS `accountlines`; Link Here
119
CREATE TABLE `accountlines` (
119
CREATE TABLE `accountlines` (
120
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
120
  `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
121
  `issue_id` int(11) DEFAULT NULL,
121
  `issue_id` int(11) DEFAULT NULL,
122
  `old_issue_id` int(11) DEFAULT NULL,
122
  `borrowernumber` int(11) DEFAULT NULL,
123
  `borrowernumber` int(11) DEFAULT NULL,
123
  `itemnumber` int(11) DEFAULT NULL,
124
  `itemnumber` int(11) DEFAULT NULL,
124
  `date` timestamp NULL DEFAULT NULL,
125
  `date` timestamp NULL DEFAULT NULL,
Lines 143-148 CREATE TABLE `accountlines` ( Link Here
143
  KEY `debit_type_code` (`debit_type_code`),
144
  KEY `debit_type_code` (`debit_type_code`),
144
  KEY `itemnumber` (`itemnumber`),
145
  KEY `itemnumber` (`itemnumber`),
145
  KEY `branchcode` (`branchcode`),
146
  KEY `branchcode` (`branchcode`),
147
  KEY `issue_id` (`issue_id`),
148
  KEY `old_issue_id` (`old_issue_id`),
146
  KEY `manager_id` (`manager_id`),
149
  KEY `manager_id` (`manager_id`),
147
  KEY `accountlines_ibfk_registers` (`register_id`),
150
  KEY `accountlines_ibfk_registers` (`register_id`),
148
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
151
  CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
Lines 150-160 CREATE TABLE `accountlines` ( Link Here
150
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
153
  CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
151
  CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON UPDATE CASCADE,
154
  CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON UPDATE CASCADE,
152
  CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON UPDATE CASCADE,
155
  CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON UPDATE CASCADE,
156
  CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
153
  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
157
  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
158
  CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
154
  CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
159
  CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
155
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
160
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
156
/*!40101 SET character_set_client = @saved_cs_client */;
161
/*!40101 SET character_set_client = @saved_cs_client */;
157
162
163
158
--
164
--
159
-- Table structure for table `action_logs`
165
-- Table structure for table `action_logs`
160
--
166
--
161
- 

Return to bug 22421