From c164600d501beecba5a8d4d9daaf313a9a6908db Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Tue, 28 Jul 2020 09:46:08 -0300 Subject: [PATCH] Bug 22343: Add a new table to store SMTP servers configs This patch adds a new table to store SMTP servers configs. --- .../atomicupdate/bug_22343_smtp_servers.perl | 41 +++++++++++++++++ installer/data/mysql/kohastructure.sql | 46 +++++++++++++++++++ 2 files changed, 87 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_22343_smtp_servers.perl diff --git a/installer/data/mysql/atomicupdate/bug_22343_smtp_servers.perl b/installer/data/mysql/atomicupdate/bug_22343_smtp_servers.perl new file mode 100644 index 0000000000..6bbb421c0e --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22343_smtp_servers.perl @@ -0,0 +1,41 @@ +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + + unless (TableExists('smtp_servers')) { + + # Create the table + $dbh->do(q{ + CREATE TABLE `smtp_servers` ( + `id` INT(11) NOT NULL AUTO_INCREMENT, + `name` VARCHAR(80) NOT NULL, + `host` VARCHAR(80) NOT NULL DEFAULT 'localhost', + `port` INT(11) NOT NULL DEFAULT 25, + `timeout` INT(11) NOT NULL DEFAULT 120, + `ssl_mode` ENUM('disabled', 'ssl', 'starttls') NOT NULL, + `user_name` VARCHAR(80) NULL DEFAULT NULL, + `password` VARCHAR(80) NULL DEFAULT NULL, + `debug` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`id`), + KEY `host_idx` (`host`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + } + + unless (TableExists('library_smtp_servers')) { + $dbh->do(q{ + CREATE TABLE `library_smtp_servers` ( + `id` INT(11) NOT NULL AUTO_INCREMENT, + `library_id` VARCHAR(10) NOT NULL, + `smtp_server_id` INT(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `library_id_idx` (`library_id`), + KEY `smtp_server_id_idx` (`smtp_server_id`), + CONSTRAINT `library_id_fk` FOREIGN KEY (`library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `smtp_server_id_fk` FOREIGN KEY (`smtp_server_id`) REFERENCES `smtp_servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + }); + } + + # Always end with this (adjust the bug info) + NewVersion( $DBversion, 22343, "Add SMTP configuration options"); +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 9700ee04ab..503902ee45 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4576,6 +4576,52 @@ CREATE TABLE advanced_editor_macros ( CONSTRAINT borrower_macro_fk FOREIGN KEY ( borrowernumber ) REFERENCES borrowers ( borrowernumber ) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table smtp_servers +-- + +DROP TABLE IF EXISTS `smtp_servers`; +CREATE TABLE `smtp_servers` ( + `id` INT(11) NOT NULL AUTO_INCREMENT, + -- Unique ID of the server + `name` VARCHAR(80) NOT NULL, + -- Name of the SMTP server + `host` VARCHAR(80) NOT NULL DEFAULT 'localhost', + -- FQDN for the SMTP server + `port` INT(11) NOT NULL DEFAULT 25, + -- TCP port number + `timeout` INT(11) NOT NULL DEFAULT 120, + -- Maximum time in secs to wait for server + `ssl_mode` ENUM('disabled', 'ssl', 'starttls') NOT NULL, + -- If STARTTLS needs to be issued + `user_name` VARCHAR(80) NULL DEFAULT NULL, + -- The username to use for auth; optional + `password` VARCHAR(80) NULL DEFAULT NULL, + -- The password to use for auth; required if username is provided + `debug` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`id`), + KEY `host_idx` (`host`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table library_smtp_servers +-- + +DROP TABLE IF EXISTS `library_smtp_servers`; +CREATE TABLE `library_smtp_servers` ( + `id` INT(11) NOT NULL AUTO_INCREMENT, + -- Unique ID of the library<->SMTP server link + `library_id` VARCHAR(10) NOT NULL, + -- Internal identifier for the library + `smtp_server_id` INT(11) NOT NULL, + -- Internal identifier for the SMTP server + PRIMARY KEY (`id`), + UNIQUE KEY `library_id_idx` (`library_id`), + KEY `smtp_server_id_idx` (`smtp_server_id`), + CONSTRAINT `library_id_fk` FOREIGN KEY (`library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `smtp_server_id_fk` FOREIGN KEY (`smtp_server_id`) REFERENCES `smtp_servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -- 2.28.0