From 2c4123bc53286e5cb3aeb4e70d7120161af96414 Mon Sep 17 00:00:00 2001
From: Jake Deery <jake.deery@ptfs-europe.com>
Date: Fri, 14 Feb 2025 15:55:18 +0000
Subject: [PATCH] Bug 35761: Database updates

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
---
 .../bug_35761-add_SFTP_tables_and_perm.pl     | 44 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        | 27 ++++++++++++
 .../data/mysql/mandatory/userpermissions.sql  |  3 +-
 3 files changed, 73 insertions(+), 1 deletion(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_35761-add_SFTP_tables_and_perm.pl

diff --git a/installer/data/mysql/atomicupdate/bug_35761-add_SFTP_tables_and_perm.pl b/installer/data/mysql/atomicupdate/bug_35761-add_SFTP_tables_and_perm.pl
new file mode 100755
index 00000000000..72ba331436f
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_35761-add_SFTP_tables_and_perm.pl
@@ -0,0 +1,44 @@
+use Modern::Perl;
+
+return {
+    bug_number  => "35761",
+    description => "Add new table and permission for generalised SFTP",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        $dbh->do(
+            q{
+                INSERT IGNORE INTO permissions (module_bit, code, description)
+                VALUES (3, 'manage_sftp_servers', 'Manage FTP/SFTP servers configuration');
+            }
+        );
+        say $out "Added new manage_sftp_servers permission";
+
+        unless ( TableExists('sftp_servers') ) {
+            $dbh->do(
+                q {
+                    CREATE TABLE `sftp_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 22,
+                    `transport` enum('ftp','sftp') NOT NULL DEFAULT 'sftp',
+                    `passive` tinyint(1) NOT NULL DEFAULT 1,
+                    `user_name` varchar(80) DEFAULT NULL,
+                    `password` mediumtext DEFAULT NULL,
+                    `key_file` mediumtext DEFAULT NULL,
+                    `auth_mode` enum('password','key_file','noauth') NOT NULL DEFAULT 'password',
+                    `download_directory` mediumtext DEFAULT NULL,
+                    `upload_directory` mediumtext DEFAULT NULL,
+                    `status` varchar(32) 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;
+                }
+            );
+            say $out "Added new sftp_servers table";
+        }
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 9b7dc7fc6ec..d17f7f04a1c 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -5964,6 +5964,33 @@ CREATE TABLE `sessions` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
+--
+-- Table structure for table `sftp_servers`
+--
+
+DROP TABLE IF EXISTS `sftp_servers`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `sftp_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 22,
+  `transport` enum('ftp','sftp') NOT NULL DEFAULT 'sftp',
+  `passive` tinyint(1) NOT NULL DEFAULT 1,
+  `user_name` varchar(80) DEFAULT NULL,
+  `password` mediumtext DEFAULT NULL,
+  `key_file` mediumtext DEFAULT NULL,
+  `auth_mode` enum('password','key_file','noauth') NOT NULL DEFAULT 'password',
+  `download_directory` mediumtext DEFAULT NULL,
+  `upload_directory` mediumtext DEFAULT NULL,
+  `status` varchar(32) 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;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
 --
 -- Table structure for table `sms_providers`
 --
diff --git a/installer/data/mysql/mandatory/userpermissions.sql b/installer/data/mysql/mandatory/userpermissions.sql
index 1d8b7d034f2..983ad208f9f 100644
--- a/installer/data/mysql/mandatory/userpermissions.sql
+++ b/installer/data/mysql/mandatory/userpermissions.sql
@@ -39,6 +39,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES
    ( 3, 'manage_additional_fields', 'Add, edit, or delete additional custom fields for baskets or subscriptions (also requires order_manage or edit_subscription permissions)'),
    ( 3, 'manage_keyboard_shortcuts', 'Manage keyboard shortcuts for the advanced cataloging editor'),
    ( 3, 'manage_smtp_servers', 'Manage SMTP servers configuration'),
+   ( 3, 'manage_sftp_servers', 'Manage FTP/SFTP servers configuration'),
    ( 3, 'manage_background_jobs', 'Manage background jobs'),
    ( 3, 'manage_curbside_pickups', 'Manage curbside pickups'),
    ( 3, 'manage_search_filters', 'Manage custom search filters'),
@@ -164,4 +165,4 @@ INSERT INTO permissions (module_bit, code, description) VALUES
    (25, 'takepayment', 'Access the point of sale page and take payments'),
    (26, 'manage_problem_reports', 'Manage OPAC problem reports'),
    (27, 'manage_recalls', 'Manage recalls for patrons')
-;
+;
\ No newline at end of file
-- 
2.48.1