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 1576-1581 CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as Link Here
1576
    ON DELETE CASCADE ON UPDATE CASCADE
1576
    ON DELETE CASCADE ON UPDATE CASCADE
1577
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1577
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1578
1578
1579
DROP TABLE IF EXISTS `debarment_types`;
1580
CREATE TABLE debarment_types (
1581
    code varchar(50) NOT NULL PRIMARY KEY,
1582
    display_text text NOT NULL,
1583
    ronly tinyint(1) NOT NULL DEFAULT 0,
1584
    dflt tinyint(1) NOT NULL DEFAULT 0
1585
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1586
1579
--
1587
--
1580
-- Table structure for table `borrower_debarments`
1588
-- Table structure for table `borrower_debarments`
1581
--
1589
--
Lines 1585-1591 CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record Link Here
1585
  borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction
1593
  borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction
1586
  borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
1594
  borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
1587
  expiration date DEFAULT NULL, -- expiration date of the restriction
1595
  expiration date DEFAULT NULL, -- expiration date of the restriction
1588
  `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction
1596
  `type` varchar(50) NOT NULL, -- type of restriction, FK to debarment_types.code
1589
  `comment` MEDIUMTEXT, -- comments about the restriction
1597
  `comment` MEDIUMTEXT, -- comments about the restriction
1590
  manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
1598
  manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
1591
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
1599
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
Lines 1593-1599 CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record Link Here
1593
  PRIMARY KEY (borrower_debarment_id),
1601
  PRIMARY KEY (borrower_debarment_id),
1594
  KEY borrowernumber (borrowernumber),
1602
  KEY borrowernumber (borrowernumber),
1595
  CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1603
  CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1596
    ON DELETE CASCADE ON UPDATE CASCADE
1604
    ON DELETE CASCADE ON UPDATE CASCADE,
1605
  CONSTRAINT `borrower_debarments_ibfk_2` FOREIGN KEY (`type`)  REFERENCES `debarment_types` (`code`)
1606
    ON DELETE NO ACTION ON UPDATE CASCADE
1597
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1607
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1598
1608
1599
--
1609
--
1600
- 

Return to bug 23681