Attachment 109003 Details for Bug 22343 – Bug 22343: Add a new table to store SMTP servers configs

[patch] Bug 22343: Add a new table to store SMTP servers configs

Bug-22343-Add-a-new-table-to-store-SMTP-servers-co.patch (text/plain), 5.11 KB, created by Tomás Cohen Arazi (tcohen) on 2020-08-24 15:30:57 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Tomás Cohen Arazi (tcohen)

Created: 2020-08-24 15:30:57 UTC

Size: 5.11 KB

patch

obsolete

>From 7c6f6260887280050e9dd6092b6fdcfb259bd2dc 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 27bb117b31..b1dc981c6e 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -4574,6 +4574,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

Actions: View | Diff | Splinter Review

Attachments on bug 22343: 107598 | 107599 | 107600 | 107601 | 107602 | 107603 | 107604 | 107605 | 107987 | 107988 | 107989 | 107990 | 107991 | 107992 | 107993 | 107994 | 107995 | 107996 | 107997 | 107998 | 107999 | 108009 | 108107 | 108108 | 108109 | 108110 | 108111 | 108112 | 108113 | 108114 | 108115 | 108116 | 108117 | 108118 | 108119 | 108131 | 108132 | 108133 | 108134 | 108135 | 108136 | 108137 | 108138 | 108139 | 108140 | 108141 | 108142 | 108143 | 108529 | 108537 | 108538 | 108539 | 108540 | 108541 | 108542 | 108543 | 108544 | 108545 | 108546 | 108547 | 108548 | 108549 | 108609 | 108638 | 108642 | 108643 | 108644 | 108645 | 108646 | 108647 | 108648 | 108649 | 108650 | 108651 | 108652 | 108653 | 108654 | 108655 | 108656 | 108715 | 108716 | 109003 | 109004 | 109005 | 109006 | 109007 | 109008 | 109009 | 109010 | 109011 | 109012 | 109013 | 109014 | 109015 | 109016 | 109017 | 109018 | 109155 | 109156 | 109157 | 109158 | 109159 | 109160 | 109161 | 109162 | 109163 | 109164 | 109165 | 109166 | 109167 | 109168 | 109169 | 109170 | 109601 | 109602 | 109603 | 109604 | 109605 | 109606 | 109607 | 109608 | 109609 | 109610 | 109611 | 109612 | 109613 | 109614 | 109615 | 109616 | 109723 | 109724 | 109725 | 109726 | 109727 | 109728 | 109729 | 109730 | 109731 | 109732 | 109733 | 109734 | 109735 | 109736 | 109737 | 109738 | 109739 | 109740 | 109787 | 109788 | 111048 | 111087 | 111088 | 111089 | 111090 | 111154 | 111165 | 111166 | 111175 | 111176 | 111213 | 111215 | 111216 | 111442 | 111482 | 113240 | 113827 | 113847