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

(-)a/installer/data/mysql/atomicupdate/bug_23681_add_debarment_types.perl (+33 lines)
Line 0 Link Here
1
$DBversion = 'XXX';
2
3
if ( CheckVersion( $DBversion ) ) {
4
5
    if ( !TableExists( 'debarment_types' ) ) {
6
        $dbh->do( q|
7
            CREATE TABLE debarment_types (
8
                code varchar(50) NOT NULL PRIMARY KEY,
9
                display_text text NOT NULL,
10
                ronly tinyint(1) NOT NULL DEFAULT 0,
11
                dflt tinyint(1) NOT NULL DEFAULT 0
12
            ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
13
        | );
14
        $dbh->do( q|
15
            INSERT INTO debarment_types (code, display_text, ronly, dflt) VALUES
16
            ('MANUAL', 'Manual', 1, 1),
17
            ('OVERDUES', 'Overdues', 1, 0),
18
            ('SUSPENSION', 'Suspension', 1, 0),
19
            ('DISCHARGE', 'Discharge', 1, 0);
20
        |);
21
    }
22
    $dbh->do( q|
23
        ALTER TABLE borrower_debarments
24
        MODIFY COLUMN type varchar(50) NOT NULL
25
    | );
26
    $dbh->do( q|
27
        ALTER TABLE borrower_debarments
28
        ADD CONSTRAINT borrower_debarments_ibfk_2 FOREIGN KEY (type) REFERENCES debarment_types(code) ON DELETE NO ACTION ON UPDATE CASCADE;
29
    | );
30
31
    SetVersion( $DBversion );
32
    print "Upgrade to $DBversion done (Bug 23681 - Add debarment_types)\n";
33
}
(-)a/installer/data/mysql/en/mandatory/patron_restrictions.sql (+5 lines)
Line 0 Link Here
1
INSERT INTO debarment_types (code, display_text, ronly, dflt) VALUES
2
    ('MANUAL', 'Manual', 1, 1),
3
    ('OVERDUES', 'Overdues', 1, 0),
4
    ('SUSPENSION', 'Suspension', 1, 0),
5
    ('DISCHARGE', 'Discharge', 1, 0);
(-)a/installer/data/mysql/en/mandatory/patron_restrictions.txt (+1 lines)
Line 0 Link Here
1
Default Koha system patron restriction types
(-)a/installer/data/mysql/kohastructure.sql (-3 / +12 lines)
Lines 1614-1619 CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as Link Here
1614
    ON DELETE CASCADE ON UPDATE CASCADE
1614
    ON DELETE CASCADE ON UPDATE CASCADE
1615
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1615
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1616
1616
1617
DROP TABLE IF EXISTS `debarment_types`;
1618
CREATE TABLE debarment_types (
1619
    code varchar(50) NOT NULL PRIMARY KEY,
1620
    display_text text NOT NULL,
1621
    ronly tinyint(1) NOT NULL DEFAULT 0,
1622
    dflt tinyint(1) NOT NULL DEFAULT 0
1623
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1624
1617
--
1625
--
1618
-- Table structure for table `borrower_debarments`
1626
-- Table structure for table `borrower_debarments`
1619
--
1627
--
Lines 1623-1629 CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record Link Here
1623
  borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction
1631
  borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction
1624
  borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
1632
  borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
1625
  expiration date DEFAULT NULL, -- expiration date of the restriction
1633
  expiration date DEFAULT NULL, -- expiration date of the restriction
1626
  `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction
1634
  `type` varchar(50) NOT NULL, -- type of restriction, FK to debarment_types.code
1627
  `comment` MEDIUMTEXT, -- comments about the restriction
1635
  `comment` MEDIUMTEXT, -- comments about the restriction
1628
  manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
1636
  manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
1629
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
1637
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
Lines 1631-1637 CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record Link Here
1631
  PRIMARY KEY (borrower_debarment_id),
1639
  PRIMARY KEY (borrower_debarment_id),
1632
  KEY borrowernumber (borrowernumber),
1640
  KEY borrowernumber (borrowernumber),
1633
  CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1641
  CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1634
    ON DELETE CASCADE ON UPDATE CASCADE
1642
    ON DELETE CASCADE ON UPDATE CASCADE,
1643
  CONSTRAINT `borrower_debarments_ibfk_2` FOREIGN KEY (`type`)  REFERENCES `debarment_types` (`code`)
1644
    ON DELETE NO ACTION ON UPDATE CASCADE
1635
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1645
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1636
1646
1637
--
1647
--
1638
- 

Return to bug 23681