Attachment 161435 Details for Bug 32607 – Bug 32607: DB changes

[patch] Bug 32607: DB changes

Bug-32607-DB-changes.patch (text/plain), 10.47 KB, created by Tomás Cohen Arazi (tcohen) on 2024-01-25 15:44:21 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Tomás Cohen Arazi (tcohen)

Created: 2024-01-25 15:44:21 UTC

Size: 10.47 KB

patch

obsolete

>From 2fc60e3a96418b95c0214d21c992ab4c9be8d88f 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 32607: 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.
>---
> Koha/Schema/Result/RecordSource.pm            | 112 ++++++++++++++++++
> .../data/mysql/atomicupdate/bug_32607.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_32607.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_32607.pl b/installer/data/mysql/atomicupdate/bug_32607.pl
>new file mode 100755
>index 00000000000..f235cf40195
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_32607.pl
>@@ -0,0 +1,83 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number  => "32607",
>+    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 7e52abd262d..3487579894e 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, 'view_borrower_infos_from_any_libraries', 'View patron infos from any libraries'),
>-- 
>2.43.0

Actions: View | Diff | Splinter Review

Attachments on bug 32607: 146410 | 146411 | 146412 | 149777 | 149827 | 149828 | 149829 | 154006 | 154007 | 154008 | 154009 | 154010 | 154438 | 155682 | 155683 | 155684 | 155685 | 155686 | 155958 | 157756 | 157757 | 157758 | 157759 | 157760 | 157761 | 157762 | 161362 | 161363 | 161364 | 161365 | 161366 | 161367 | 161435 | 161436 | 161437 | 161438 | 161439 | 161440 | 161441 | 161474