From c164600d501beecba5a8d4d9daaf313a9a6908db Mon Sep 17 00:00:00 2001
From: Tomas Cohen Arazi <tomascohen@theke.io>
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