From 92908cc5fdc23b77d9cc0ee18e4ad175d64b32ba Mon Sep 17 00:00:00 2001 From: Jake Deery Date: Fri, 14 Feb 2025 15:55:18 +0000 Subject: [PATCH] Bug 35761: Database updates Signed-off-by: Martin Renvoize --- .../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 5440bd82fd2..ede1fe62883 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