From a43b27737b870a25ef4efbc14a234ba1f930c10d Mon Sep 17 00:00:00 2001
From: Tomas Cohen Arazi <tomascohen@theke.io>
Date: Wed, 24 Jan 2024 16:12:52 -0300
Subject: [PATCH] Bug 35919: DB changes

This patch adds:

* A new table: `record_sources`.
* A new user permission: `manage_record_sources`.
* A FK on record_sources.recour_source_id on the biblio_metadata tables

Record sources will contain a name and (for now) a flag telling if
records from the specific source can be manually edited.

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
---
 Koha/Schema/Result/RecordSource.pm            | 112 ++++++++++++++++++
 .../data/mysql/atomicupdate/bug_35919.pl      |  83 +++++++++++++
 installer/data/mysql/kohastructure.sql        |  23 +++-
 .../data/mysql/mandatory/userpermissions.sql  |   1 +
 4 files changed, 217 insertions(+), 2 deletions(-)
 create mode 100644 Koha/Schema/Result/RecordSource.pm
 create mode 100755 installer/data/mysql/atomicupdate/bug_35919.pl

diff --git a/Koha/Schema/Result/RecordSource.pm b/Koha/Schema/Result/RecordSource.pm
new file mode 100644
index 00000000000..c9b8ec6129d
--- /dev/null
+++ b/Koha/Schema/Result/RecordSource.pm
@@ -0,0 +1,112 @@
+use utf8;
+package Koha::Schema::Result::RecordSource;
+
+# Created by DBIx::Class::Schema::Loader
+# DO NOT MODIFY THE FIRST PART OF THIS FILE
+
+=head1 NAME
+
+Koha::Schema::Result::RecordSource
+
+=cut
+
+use strict;
+use warnings;
+
+use base 'DBIx::Class::Core';
+
+=head1 TABLE: C<record_sources>
+
+=cut
+
+__PACKAGE__->table("record_sources");
+
+=head1 ACCESSORS
+
+=head2 record_source_id
+
+  data_type: 'integer'
+  is_auto_increment: 1
+  is_nullable: 0
+
+Primary key for the `record_sources` table
+
+=head2 name
+
+  data_type: 'text'
+  is_nullable: 0
+
+User defined name for the record source
+
+=head2 can_be_edited
+
+  data_type: 'tinyint'
+  default_value: 0
+  is_nullable: 0
+
+If records from this source can be edited
+
+=cut
+
+__PACKAGE__->add_columns(
+  "record_source_id",
+  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
+  "name",
+  { data_type => "text", is_nullable => 0 },
+  "can_be_edited",
+  { data_type => "tinyint", default_value => 0, is_nullable => 0 },
+);
+
+=head1 PRIMARY KEY
+
+=over 4
+
+=item * L</record_source_id>
+
+=back
+
+=cut
+
+__PACKAGE__->set_primary_key("record_source_id");
+
+=head1 RELATIONS
+
+=head2 biblio_metadatas
+
+Type: has_many
+
+Related object: L<Koha::Schema::Result::BiblioMetadata>
+
+=cut
+
+__PACKAGE__->has_many(
+  "biblio_metadatas",
+  "Koha::Schema::Result::BiblioMetadata",
+  { "foreign.record_source_id" => "self.record_source_id" },
+  { cascade_copy => 0, cascade_delete => 0 },
+);
+
+=head2 deletedbiblio_metadatas
+
+Type: has_many
+
+Related object: L<Koha::Schema::Result::DeletedbiblioMetadata>
+
+=cut
+
+__PACKAGE__->has_many(
+  "deletedbiblio_metadatas",
+  "Koha::Schema::Result::DeletedbiblioMetadata",
+  { "foreign.record_source_id" => "self.record_source_id" },
+  { cascade_copy => 0, cascade_delete => 0 },
+);
+
+
+# Created by DBIx::Class::Schema::Loader v0.07049 @ 2024-01-24 18:05:50
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:WX72aRYhWBK9bQWr9AJk2A
+
+__PACKAGE__->add_columns(
+    '+can_be_edited' => { is_boolean => 1 },
+);
+
+1;
diff --git a/installer/data/mysql/atomicupdate/bug_35919.pl b/installer/data/mysql/atomicupdate/bug_35919.pl
new file mode 100755
index 00000000000..36b744dff44
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_35919.pl
@@ -0,0 +1,83 @@
+use Modern::Perl;
+
+return {
+    bug_number  => "35919",
+    description => "Add record_sources table",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        unless ( TableExists('record_sources') ) {
+            $dbh->do(
+                q{
+                CREATE TABLE `record_sources` (
+                    `record_source_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key for the `record_sources` table',
+                    `name` text NOT NULL COMMENT 'User defined name for the record source',
+                    `can_be_edited` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If records from this source can be edited',
+                    PRIMARY KEY (`record_source_id`)
+                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+            }
+            );
+
+            say $out "Added new table 'record_sources'";
+        }
+
+        unless ( column_exists( 'biblio_metadata', 'record_source_id' ) ) {
+            $dbh->do(
+                q{
+                    ALTER TABLE biblio_metadata
+                        ADD COLUMN `record_source_id` int(11) NULL DEFAULT NULL
+                        COMMENT 'The record source for the metadata'
+                        AFTER timestamp
+            }
+            );
+
+            say $out "Added new column 'biblio_metadata.record_source_id'";
+        }
+
+        unless ( foreign_key_exists( 'biblio_metadata', 'record_metadata_fk_2' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE biblio_metadata
+                ADD CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE ON UPDATE CASCADE
+            }
+            );
+
+            say $out "Added new foreign key 'biblio_metadata.record_metadata_fk_2'";
+        }
+
+        unless ( column_exists( 'deletedbiblio_metadata', 'record_source_id' ) ) {
+            $dbh->do(
+                q{
+                    ALTER TABLE deletedbiblio_metadata
+                        ADD COLUMN `record_source_id` int(11) NULL DEFAULT NULL
+                        COMMENT 'The record source for the metadata'
+                        AFTER timestamp
+            }
+            );
+
+            say $out "Added new column 'deletedbiblio_metadata.record_source_id'";
+        }
+
+        unless ( foreign_key_exists( 'deletedbiblio_metadata', 'deletedrecord_metadata_fk_2' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE deletedbiblio_metadata
+                ADD CONSTRAINT `deletedrecord_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE ON UPDATE CASCADE
+            }
+            );
+
+            say $out "Added new foreign key 'deletedbiblio_metadata.record_metadata_fk_2'";
+        }
+
+        $dbh->do(
+            q{
+            INSERT IGNORE INTO permissions (module_bit, code, description) VALUES
+            ( 3, 'manage_record_sources', 'Manage record sources')
+        }
+        );
+
+        say $out "Added new permission 'manage_record_sources'";
+
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 16310f0f449..b4ba69ca473 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1122,6 +1122,21 @@ CREATE TABLE `biblio_framework` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
+--
+-- Table structure for table `record_sources`
+--
+
+DROP TABLE IF EXISTS `record_sources`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `record_sources` (
+  `record_source_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key for the `record_sources` table',
+  `name` text NOT NULL COMMENT 'User defined name for the record source',
+  `can_be_edited` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If records from this source can be edited',
+  PRIMARY KEY (`record_source_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
 --
 -- Table structure for table `biblio_metadata`
 --
@@ -1136,10 +1151,12 @@ CREATE TABLE `biblio_metadata` (
   `schema` varchar(16) NOT NULL,
   `metadata` longtext NOT NULL,
   `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `record_source_id` int(11) NULL DEFAULT NULL COMMENT 'The record source for the metadata',
   PRIMARY KEY (`id`),
   UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`schema`),
   KEY `timestamp` (`timestamp`),
-  CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
@@ -2571,10 +2588,12 @@ CREATE TABLE `deletedbiblio_metadata` (
   `schema` varchar(16) NOT NULL,
   `metadata` longtext NOT NULL,
   `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `record_source_id` int(11) NULL DEFAULT NULL COMMENT 'The record source for the metadata',
   PRIMARY KEY (`id`),
   UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`schema`),
   KEY `timestamp` (`timestamp`),
-  CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `deletedbiblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `deletedbiblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `deletedrecord_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
diff --git a/installer/data/mysql/mandatory/userpermissions.sql b/installer/data/mysql/mandatory/userpermissions.sql
index 729c075548a..4b2e07b7dd2 100644
--- a/installer/data/mysql/mandatory/userpermissions.sql
+++ b/installer/data/mysql/mandatory/userpermissions.sql
@@ -43,6 +43,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES
    ( 3, 'manage_curbside_pickups', 'Manage curbside pickups'),
    ( 3, 'manage_search_filters', 'Manage custom search filters'),
    ( 3, 'manage_identity_providers', 'Manage identity providers'),
+   ( 3, 'manage_record_sources', 'Manage record sources'),
    ( 4, 'delete_borrowers', 'Delete patrons'),
    ( 4, 'edit_borrowers', 'Add, modify and view patron information'),
    ( 4, 'list_borrowers', 'Search, list and view patrons'),
-- 
2.34.1