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

(-)a/installer/data/mysql/atomicupdate/bug_25103_dynamically_add_checkout_types.perl (+45 lines)
Line 0 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
4
    if ($dbh->do("SELECT 1 FROM authorised_value_categories WHERE category_name='CHECKOUT_TYPE'") == 0) {
5
        # Add authorised value category "CHECKOUT_TYPE"
6
        $dbh->do( q|
7
            INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES ('CHECKOUT_TYPE');
8
        | );
9
    }
10
11
    if ($dbh->do("SELECT 1 FROM authorised_values WHERE authorised_value='ONSITE'") == 0) {
12
        # Add authorised value "ONSITE" under "CHECKOUT_TYPE" category
13
        $dbh->do( q|
14
            INSERT IGNORE INTO authorised_values(category, authorised_value, lib, lib_opac) VALUES ('CHECKOUT_TYPE', 'ONSITE', 'On-site checkout', 'On-site checkout');
15
        | );
16
    }
17
    if ($dbh->do("SELECT 1 FROM authorised_values WHERE authorised_value='CHECKOUT'") == 0) {
18
        # Add authorised value "CHECKOUT" under "CHECKOUT_TYPE" category
19
        $dbh->do( q|
20
            INSERT IGNORE INTO authorised_values(category, authorised_value, lib, lib_opac) VALUES ('CHECKOUT_TYPE', 'CHECKOUT', 'Normal checkout', 'Normal checkout');
21
        | );
22
    }
23
24
    $dbh->do( q|
25
        ALTER TABLE issues MODIFY COLUMN checkout_type VARCHAR(80) NOT NULL DEFAULT 'CHECKOUT';
26
    | );
27
28
    $dbh->do( q|
29
        ALTER TABLE old_issues MODIFY COLUMN checkout_type VARCHAR(80) NOT NULL DEFAULT 'CHECKOUT';
30
    | );
31
32
    # Add a foreign key between old_issues.checkout_type and authorised_values.authorised_value
33
    if ( !foreign_key_exists( 'issues', 'issues_ctfk' ) ) {
34
        $dbh->do( "ALTER TABLE issues ADD CONSTRAINT issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE RESTRICT" );
35
    }
36
37
    # Add a foreign key between issues.checkout_type and authorised_values.authorised_value
38
    if ( !foreign_key_exists( 'old_issues', 'old_issues_ctfk' ) ) {
39
        $dbh->do( "ALTER TABLE old_issues ADD CONSTRAINT old_issues_ctfk FOREIGN KEY (checkout_type) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE RESTRICT" );
40
    }
41
42
    # Always end with this (adjust the bug info)
43
    SetVersion( $DBversion );
44
    print "Upgrade to $DBversion done (Bug 25103 - Dynamically add checkout types\n";
45
}
(-)a/installer/data/mysql/kohastructure.sql (-2 / +5 lines)
Lines 1649-1655 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
1649
  KEY `branchcode_idx` (`branchcode`),
1649
  KEY `branchcode_idx` (`branchcode`),
1650
  KEY `bordate` (`borrowernumber`,`timestamp`),
1650
  KEY `bordate` (`borrowernumber`,`timestamp`),
1651
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1651
  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1652
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
1652
  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
1653
  CONSTRAINT `issues_ctfk` FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`) ON UPDATE RESTRICT ON DELETE RESTRICT
1653
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1654
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1654
1655
1655
--
1656
--
Lines 1682-1688 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1682
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1683
  CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1683
    ON DELETE SET NULL ON UPDATE SET NULL,
1684
    ON DELETE SET NULL ON UPDATE SET NULL,
1684
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1685
  CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1685
    ON DELETE SET NULL ON UPDATE SET NULL
1686
    ON DELETE SET NULL ON UPDATE SET NULL,
1687
  CONSTRAINT `old_issues_ctfk` FOREIGN KEY (`checkout_type`) REFERENCES `authorised_values`(`authorised_value`)
1688
    ON UPDATE RESTRICT ON DELETE RESTRICT
1686
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1689
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1687
1690
1688
--
1691
--
(-)a/installer/data/mysql/mandatory/auth_val_cat.sql (-1 / +4 lines)
Lines 65-67 INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES Link Here
65
-- For Claims returned
65
-- For Claims returned
66
INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES
66
INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES
67
    ('RETURN_CLAIM_RESOLUTION');
67
    ('RETURN_CLAIM_RESOLUTION');
68
- 
68
69
-- For Circulation types
70
INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES
71
    ('CHECKOUT_TYPE');

Return to bug 25103