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

(-)a/installer/data/mysql/atomicupdate/bug_22343_smtp_servers.perl (+41 lines)
Line 0 Link Here
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
3
4
    unless (TableExists('smtp_servers')) {
5
6
        # Create the table
7
        $dbh->do(q{
8
            CREATE TABLE `smtp_servers` (
9
                `id` INT(11) NOT NULL AUTO_INCREMENT,
10
                `name` VARCHAR(80) NOT NULL,
11
                `host` VARCHAR(80) NOT NULL DEFAULT 'localhost',
12
                `port` INT(11) NOT NULL DEFAULT 25,
13
                `timeout` INT(11) NOT NULL DEFAULT 120,
14
                `ssl_mode` ENUM('disabled', 'ssl', 'starttls') NOT NULL,
15
                `user_name` VARCHAR(80) NULL DEFAULT NULL,
16
                `password` VARCHAR(80) NULL DEFAULT NULL,
17
                `debug` TINYINT(1) NOT NULL DEFAULT 0,
18
                PRIMARY KEY (`id`),
19
                KEY `host_idx` (`host`)
20
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
21
        });
22
    }
23
24
    unless (TableExists('library_smtp_servers')) {
25
        $dbh->do(q{
26
            CREATE TABLE `library_smtp_servers` (
27
                `id` INT(11) NOT NULL AUTO_INCREMENT,
28
                `library_id` VARCHAR(10) NOT NULL,
29
                `smtp_server_id` INT(11) NOT NULL,
30
                PRIMARY KEY (`id`),
31
                UNIQUE KEY `library_id_idx` (`library_id`),
32
                KEY `smtp_server_id_idx` (`smtp_server_id`),
33
                CONSTRAINT `library_id_fk` FOREIGN KEY (`library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
34
                CONSTRAINT `smtp_server_id_fk` FOREIGN KEY (`smtp_server_id`) REFERENCES `smtp_servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
35
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
36
        });
37
    }
38
39
    # Always end with this (adjust the bug info)
40
    NewVersion( $DBversion, 22343, "Add SMTP configuration options");
41
}
(-)a/installer/data/mysql/kohastructure.sql (-1 / +46 lines)
Lines 4582-4587 CREATE TABLE advanced_editor_macros ( Link Here
4582
    CONSTRAINT borrower_macro_fk FOREIGN KEY ( borrowernumber ) REFERENCES borrowers ( borrowernumber ) ON UPDATE CASCADE ON DELETE CASCADE
4582
    CONSTRAINT borrower_macro_fk FOREIGN KEY ( borrowernumber ) REFERENCES borrowers ( borrowernumber ) ON UPDATE CASCADE ON DELETE CASCADE
4583
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4583
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4584
4584
4585
--
4586
-- Table structure for table smtp_servers
4587
--
4588
4589
DROP TABLE IF EXISTS `smtp_servers`;
4590
CREATE TABLE `smtp_servers` (
4591
    `id` INT(11) NOT NULL AUTO_INCREMENT,
4592
    -- Unique ID of the server
4593
    `name` VARCHAR(80) NOT NULL,
4594
    -- Name of the SMTP server
4595
    `host` VARCHAR(80) NOT NULL DEFAULT 'localhost',
4596
    -- FQDN for the SMTP server
4597
    `port` INT(11) NOT NULL DEFAULT 25,
4598
    -- TCP port number
4599
    `timeout` INT(11) NOT NULL DEFAULT 120,
4600
    -- Maximum time in secs to wait for server
4601
    `ssl_mode` ENUM('disabled', 'ssl', 'starttls') NOT NULL,
4602
    -- If STARTTLS needs to be issued
4603
    `user_name` VARCHAR(80) NULL DEFAULT NULL,
4604
    -- The username to use for auth; optional
4605
    `password` VARCHAR(80) NULL DEFAULT NULL,
4606
    -- The password to use for auth; required if username is provided
4607
    `debug` TINYINT(1) NOT NULL DEFAULT 0,
4608
    PRIMARY KEY (`id`),
4609
    KEY `host_idx` (`host`)
4610
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4611
4612
--
4613
-- Table structure for table library_smtp_servers
4614
--
4615
4616
DROP TABLE IF EXISTS `library_smtp_servers`;
4617
CREATE TABLE `library_smtp_servers` (
4618
    `id` INT(11) NOT NULL AUTO_INCREMENT,
4619
    -- Unique ID of the library<->SMTP server link
4620
    `library_id` VARCHAR(10) NOT NULL,
4621
    -- Internal identifier for the library
4622
    `smtp_server_id` INT(11) NOT NULL,
4623
    -- Internal identifier for the SMTP server
4624
    PRIMARY KEY (`id`),
4625
    UNIQUE KEY `library_id_idx` (`library_id`),
4626
    KEY `smtp_server_id_idx` (`smtp_server_id`),
4627
    CONSTRAINT `library_id_fk` FOREIGN KEY (`library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
4628
    CONSTRAINT `smtp_server_id_fk` FOREIGN KEY (`smtp_server_id`) REFERENCES `smtp_servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
4629
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
4630
4585
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
4631
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
4586
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
4632
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
4587
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
4633
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
4588
- 

Return to bug 22343