Attachment 151519 Details for Bug 30719 – Bug 30719: DB and API

[patch] Bug 30719: DB and API

Bug-30719-DB-and-API.patch (text/plain), 17.70 KB, created by Pedro Amorim on 2023-05-22 13:09:53 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Pedro Amorim

Created: 2023-05-22 13:09:53 UTC

Size: 17.70 KB

patch

obsolete

>From 453f581c96d55df1b5db995ea008a755f973bda7 Mon Sep 17 00:00:00 2001
>From: Pedro Amorim <pedro.amorim@ptfs-europe.com>
>Date: Fri, 12 May 2023 16:59:21 +0000
>Subject: [PATCH] Bug 30719: DB and API
>
>- Adds 'batch' accessor to Illrequest object
>- New illbatches and illbatch_statuses tables
>- New foreign key 'batch_id' in illrequests table
>- Atomic update file
>- Default illbatch_statuses
>- Add 'add' ill_requests api method
>- Add POST method in ill_requests path
>- Add 'batch_id property to ill_request api definition
>- Updated swagger.yml with new batches and batchstatuses endpoints
>
>Co-authored-by: Andrew Isherwood <andrew.isherwood@ptfs-europe.com>
>---
> Koha/Illrequest.pm                            | 27 ++++++
> Koha/REST/V1/Illrequests.pm                   | 87 +++++++++++++++++++
> api/v1/swagger/definitions/ill_request.yaml   | 10 +++
> api/v1/swagger/paths/ill_requests.yaml        | 51 +++++++++++
> api/v1/swagger/swagger.yaml                   | 34 ++++++++
> .../atomicupdate/bug_30719_add_ill_batches.pl | 54 ++++++++++++
> installer/data/mysql/kohastructure.sql        | 34 +++++++-
> .../mysql/mandatory/illbatch_statuses.sql     |  5 ++
> 8 files changed, 301 insertions(+), 1 deletion(-)
> create mode 100755 installer/data/mysql/atomicupdate/bug_30719_add_ill_batches.pl
> create mode 100644 installer/data/mysql/mandatory/illbatch_statuses.sql
>
>diff --git a/Koha/Illrequest.pm b/Koha/Illrequest.pm
>index 4af4aa8c75..7ff62af10a 100644
>--- a/Koha/Illrequest.pm
>+++ b/Koha/Illrequest.pm
>@@ -35,6 +35,7 @@ use Koha::Illrequestattributes;
> use Koha::AuthorisedValue;
> use Koha::Illrequest::Logger;
> use Koha::Patron;
>+use Koha::Illbatches;
> use Koha::AuthorisedValues;
> use Koha::Biblios;
> use Koha::Items;
>@@ -146,6 +147,20 @@ sub push_processor {
>     push @{$self->{processors}}, $processor;
> }
> 
>+=head3 batch
>+
>+    my $batch = $request->batch;
>+
>+Returns the batch associated with a request
>+
>+=cut
>+
>+sub batch {
>+    my ( $self ) = @_;
>+
>+    return Koha::Illbatches->find($self->_result->batch_id);
>+}
>+
> =head3 statusalias
> 
>     my $statusalias = $request->statusalias;
>@@ -1980,6 +1995,18 @@ sub strings_map {
>         };
>     }
> 
>+    my $batch = $self->batch;
>+    if ($batch) {
>+        $strings->{"batch"} = {
>+            id => $batch->id,
>+            name => $batch->name,
>+            backend => $batch->backend,
>+            borrowernumber => $batch->borrowernumber,
>+            branchcode => $batch->branchcode,
>+            statuscode => $batch->statuscode
>+        };
>+    }
>+
>     return $strings;
> }
> 
>diff --git a/Koha/REST/V1/Illrequests.pm b/Koha/REST/V1/Illrequests.pm
>index e60b8f679f..a647df5894 100644
>--- a/Koha/REST/V1/Illrequests.pm
>+++ b/Koha/REST/V1/Illrequests.pm
>@@ -27,6 +27,9 @@ use Koha::Patrons;
> use Koha::Libraries;
> use Koha::DateUtils qw( format_sqldatetime );
> 
>+use Scalar::Util qw( blessed );
>+use Try::Tiny qw( catch try );
>+
> =head1 NAME
> 
> Koha::REST::V1::Illrequests
>@@ -55,4 +58,88 @@ sub list {
>     };
> }
> 
>+=head3 add
>+
>+Adds a new ILL request
>+
>+=cut
>+
>+sub add {
>+    my $c = shift->openapi->valid_input or return;
>+
>+    return try {
>+
>+        Koha::Database->new->schema->txn_do(
>+            sub {
>+
>+                my $body = $c->validation->param('body');
>+                $body->{backend} = delete $body->{ill_backend_id};
>+                $body->{borrowernumber} = delete $body->{patron_id};
>+                $body->{branchcode} = delete $body->{library_id};
>+
>+                my $request = Koha::Illrequest->new->load_backend( $body->{backend} );
>+
>+                my $create_api = $request->_backend->capabilities('create_api');
>+
>+                if (!$create_api) {
>+                    return $c->render(
>+                        status  => 405,
>+                        openapi => {
>+                            errors => [ 'This backend does not allow request creation via API' ]
>+                        }
>+                    );
>+                }
>+
>+                my $create_result = &{$create_api}($body, $request);
>+                my $new_id = $create_result->illrequest_id;
>+
>+                my @new_req = Koha::Illrequests->search({
>+                    illrequest_id => $new_id
>+                })->as_list;
>+
>+                $c->res->headers->location($c->req->url->to_string . '/' . $new_req[0]->illrequest_id);
>+                return $c->render(
>+                    status  => 201,
>+                    openapi => $new_req[0]->to_api
>+                );
>+            }
>+        );
>+    }
>+    catch {
>+
>+        my $to_api_mapping = Koha::Illrequest->new->to_api_mapping;
>+
>+        if ( blessed $_ ) {
>+            if ( $_->isa('Koha::Exceptions::Object::DuplicateID') ) {
>+                return $c->render(
>+                    status  => 409,
>+                    openapi => { error => $_->error, conflict => $_->duplicate_id }
>+                );
>+            }
>+            elsif ( $_->isa('Koha::Exceptions::Object::FKConstraint') ) {
>+                return $c->render(
>+                    status  => 400,
>+                    openapi => {
>+                            error => "Given "
>+                            . $to_api_mapping->{ $_->broken_fk }
>+                            . " does not exist"
>+                    }
>+                );
>+            }
>+            elsif ( $_->isa('Koha::Exceptions::BadParameter') ) {
>+                return $c->render(
>+                    status  => 400,
>+                    openapi => {
>+                            error => "Given "
>+                            . $to_api_mapping->{ $_->parameter }
>+                            . " does not exist"
>+                    }
>+                );
>+            }
>+        }
>+
>+        $c->unhandled_exception($_);
>+    };
>+}
>+
> 1;
>diff --git a/api/v1/swagger/definitions/ill_request.yaml b/api/v1/swagger/definitions/ill_request.yaml
>index 7c668bdfb5..b4c2822187 100644
>--- a/api/v1/swagger/definitions/ill_request.yaml
>+++ b/api/v1/swagger/definitions/ill_request.yaml
>@@ -9,6 +9,11 @@ properties:
>       - integer
>       - "null"
>     description: Internal bibliographic record identifier
>+  batch_id:
>+    type:
>+      - integer
>+      - "null"
>+    description: Batch id this requests belongs to
>   patron_id:
>     type:
>       - integer
>@@ -113,6 +118,11 @@ properties:
>       - array
>       - "null"
>     description: The linked extended ill request attributes (x-koha-embed)
>+  batch:
>+    type:
>+      - object
>+      - "null"
>+    description: The linked ill batch object (x-koha-embed)
>   library:
>     type:
>       - object
>diff --git a/api/v1/swagger/paths/ill_requests.yaml b/api/v1/swagger/paths/ill_requests.yaml
>index 3ba3f2ca3c..d66f77ef68 100644
>--- a/api/v1/swagger/paths/ill_requests.yaml
>+++ b/api/v1/swagger/paths/ill_requests.yaml
>@@ -28,6 +28,7 @@
>             - comments
>             - comments+count
>             - extended_attributes
>+            - batch
>             - library
>             - id_prefix
>             - patron
>@@ -63,3 +64,53 @@
>     x-koha-authorization:
>       permissions:
>         ill: "1"
>+  post:
>+    x-mojo-to: Illrequests#add
>+    operationId: addIllrequest
>+    tags:
>+      - ill_request
>+    summary: Add ILL request
>+    parameters:
>+      - name: body
>+        in: body
>+        description: A JSON object containing informations about the new request
>+        required: true
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/ill_request"
>+    produces:
>+      - application/json
>+    responses:
>+      "201":
>+        description: Request added
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/ill_request"
>+      "400":
>+        description: Bad request
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/error"
>+      "401":
>+        description: Authentication required
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/error"
>+      "403":
>+        description: Access forbidden
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/error"
>+      "409":
>+        description: Conflict in creating resource
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/error"
>+      "500":
>+        description: |
>+          Internal server error. Possible `error_code` attribute values:
>+
>+          * `internal_server_error`
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/error"
>+      "503":
>+        description: Under maintenance
>+        schema:
>+          $ref: "../swagger.yaml#/definitions/error"
>+    x-koha-authorization:
>+      permissions:
>+        ill: "1"
>diff --git a/api/v1/swagger/swagger.yaml b/api/v1/swagger/swagger.yaml
>index 280610c261..e1145055d9 100644
>--- a/api/v1/swagger/swagger.yaml
>+++ b/api/v1/swagger/swagger.yaml
>@@ -60,6 +60,14 @@ definitions:
>     $ref: ./definitions/ill_status.yaml
>   ill_request:
>     $ref: ./definitions/ill_request.yaml
>+  illbatch:
>+    $ref: ./definitions/illbatch.yaml
>+  illbatches:
>+    $ref: ./definitions/illbatches.yaml
>+  illbatchstatus:
>+    $ref: ./definitions/illbatchstatus.yaml
>+  illbatchstatuses:
>+    $ref: ./definitions/illbatchstatuses.yaml
>   import_batch_profile:
>     $ref: ./definitions/import_batch_profile.yaml
>   import_batch_profiles:
>@@ -257,6 +265,14 @@ paths:
>     $ref: "./paths/ill_backends.yaml#/~1ill~1backends~1{ill_backend_id}"
>   /ill/requests:
>     $ref: ./paths/ill_requests.yaml#/~1ill~1requests
>+  /illbatches:
>+    $ref: ./paths/illbatches.yaml#/~1illbatches
>+  "/illbatches/{illbatch_id}":
>+    $ref: "./paths/illbatches.yaml#/~1illbatches~1{illbatch_id}"
>+  /illbatchstatuses:
>+    $ref: ./paths/illbatchstatuses.yaml#/~1illbatchstatuses
>+  "/illbatchstatuses/{illbatchstatus_code}":
>+    $ref: "./paths/illbatchstatuses.yaml#/~1illbatchstatuses~1{illbatchstatus_code}"
>   "/import_batches/{import_batch_id}/records/{import_record_id}/matches/chosen":
>     $ref: "./paths/import_batches.yaml#/~1import_batches~1{import_batch_id}~1records~1{import_record_id}~1matches~1chosen"
>   /import_batch_profiles:
>@@ -503,6 +519,18 @@ parameters:
>     name: hold_id
>     required: true
>     type: integer
>+  illbatch_id_pp:
>+    description: Internal ILL batch identifier
>+    in: path
>+    name: illbatch_id
>+    required: true
>+    type: integer
>+  illbatchstatus_code_pp:
>+    description: Internal ILL batch status identifier
>+    in: path
>+    name: illbatchstatus_code
>+    required: true
>+    type: string
>   import_batch_profile_id_pp:
>     description: Internal profile identifier
>     in: path
>@@ -805,6 +833,12 @@ tags:
>   - description: "Manage ILL module backends\n"
>     name: ill_backends
>     x-displayName: ILL backends
>+  - description: "Manage ILL module batches\n"
>+    name: illbatches
>+    x-displayName: ILL batches
>+  - description: "Manage ILL module batch statuses\n"
>+    name: illbatchstatuses
>+    x-displayName: ILL batch statuses
>   - description: "Manage ILL requests\n"
>     name: ill_requests
>     x-displayName: ILL requests
>diff --git a/installer/data/mysql/atomicupdate/bug_30719_add_ill_batches.pl b/installer/data/mysql/atomicupdate/bug_30719_add_ill_batches.pl
>new file mode 100755
>index 0000000000..fa6dc47e43
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_30719_add_ill_batches.pl
>@@ -0,0 +1,54 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => "30719",
>+    description => "Add ILL batches",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+        $dbh->do(q{
>+            CREATE TABLE IF NOT EXISTS `illbatches` (
>+                `id` int(11) NOT NULL auto_increment, -- Batch ID
>+                `name` varchar(100) NOT NULL,         -- Unique name of batch
>+                `backend` varchar(20) NOT NULL,       -- Name of batch backend
>+                `borrowernumber` int(11),             -- Patron associated with batch
>+                `branchcode` varchar(50),             -- Branch associated with batch
>+                `statuscode` varchar(20),             -- Status of batch
>+                PRIMARY KEY (`id`),
>+                UNIQUE KEY `u_illbatches__name` (`name`)
>+            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
>+        });
>+        $dbh->do(q{
>+            CREATE TABLE IF NOT EXISTS `illbatch_statuses` (
>+                `id` int(11) NOT NULL auto_increment, -- Status ID
>+                `name` varchar(100) NOT NULL,         -- Name of status
>+                `code` varchar(20) NOT NULL,          -- Unique, immutable code for status
>+                `is_system` int(1),                   -- Is this status required for system operation
>+                PRIMARY KEY (`id`),
>+                UNIQUE KEY `u_illbatchstatuses__code` (`code`)
>+            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE `illrequests`
>+                ADD COLUMN `batch_id` int(11) AFTER backend -- Optional ID of batch that this request belongs to
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE `illrequests`
>+                ADD CONSTRAINT `illrequests_ibfk` FOREIGN KEY (`batch_id`) REFERENCES `illbatches` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE `illbatches`
>+                ADD CONSTRAINT `illbatches_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE `illbatches`
>+                ADD CONSTRAINT `illbatches_bcfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE `illbatches`
>+                ADD CONSTRAINT `illbatches_sfk` FOREIGN KEY (`statuscode`) REFERENCES `illbatch_statuses` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
>+        });
>+
>+        say $out "Bug 30719: Add ILL batches completed"
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index d193d2aebb..7a885597a6 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -3279,6 +3279,36 @@ CREATE TABLE `illrequestattributes` (
>   CONSTRAINT `illrequestattributes_ifk` FOREIGN KEY (`illrequest_id`) REFERENCES `illrequests` (`illrequest_id`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
>+--
>+-- Table structure for table `illbatch_statuses`
>+--
>+DROP TABLE IF EXISTS `illbatch_statuses`;
>+CREATE TABLE `illbatch_statuses` (
>+    `id` int(11) NOT NULL auto_increment, -- Status ID
>+    `name` varchar(100) NOT NULL,         -- Name of status
>+    `code` varchar(20) NOT NULL,          -- Unique, immutable code for status
>+    `is_system` int(1),                   -- Is this status required for system operation
>+    PRIMARY KEY (`id`),
>+    UNIQUE KEY `u_illbatchstatuses__code` (`code`)
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+
>+--
>+-- Table structure for table `illbatches`
>+--
>+DROP TABLE IF EXISTS `illbatches`;
>+CREATE TABLE `illbatches` (
>+    `id` int(11) NOT NULL auto_increment, -- Batch ID
>+    `name` varchar(100) NOT NULL,         -- Unique name of batch
>+    `backend` varchar(20) NOT NULL,       -- Name of batch backend
>+    `borrowernumber` int(11),             -- Patron associated with batch
>+    `branchcode` varchar(50),             -- Branch associated with batch
>+    `statuscode` varchar(20),             -- Status of batch
>+    PRIMARY KEY (`id`),
>+    UNIQUE KEY `u_illbatches__name` (`name`),
>+    CONSTRAINT `illbatches_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>+    CONSTRAINT `illbatches_bcfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
>+    CONSTRAINT `illbatches_sfk` FOREIGN KEY (`statuscode`) REFERENCES `illbatch_statuses` (`code`) ON DELETE SET NULL ON UPDATE CASCADE
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
> -- Table structure for table `illrequests`
>@@ -3307,6 +3337,7 @@ CREATE TABLE `illrequests` (
>   `notesstaff` mediumtext DEFAULT NULL COMMENT 'Staff notes attached to request',
>   `orderid` varchar(50) DEFAULT NULL COMMENT 'Backend id attached to request',
>   `backend` varchar(20) DEFAULT NULL COMMENT 'The backend used to create request',
>+  `batch_id` int(11) COMMENT 'Optional ID of batch that this request belongs to',
>   PRIMARY KEY (`illrequest_id`),
>   KEY `illrequests_bnfk` (`borrowernumber`),
>   KEY `illrequests_bcfk_2` (`branchcode`),
>@@ -3314,7 +3345,8 @@ CREATE TABLE `illrequests` (
>   CONSTRAINT `illrequests_bibfk` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>   CONSTRAINT `illrequests_bcfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT `illrequests_bnfk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>-  CONSTRAINT `illrequests_safk` FOREIGN KEY (`status_alias`) REFERENCES `authorised_values` (`authorised_value`) ON DELETE SET NULL ON UPDATE CASCADE
>+  CONSTRAINT `illrequests_safk` FOREIGN KEY (`status_alias`) REFERENCES `authorised_values` (`authorised_value`) ON DELETE SET NULL ON UPDATE CASCADE,
>+  CONSTRAINT `illrequests_ibfk` FOREIGN KEY (`batch_id`) REFERENCES `illbatches` (`id`) ON DELETE SET NULL 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/illbatch_statuses.sql b/installer/data/mysql/mandatory/illbatch_statuses.sql
>new file mode 100644
>index 0000000000..8c6288eb8b
>--- /dev/null
>+++ b/installer/data/mysql/mandatory/illbatch_statuses.sql
>@@ -0,0 +1,5 @@
>+INSERT INTO illbatch_statuses ( name, code, is_system ) VALUES
>+('New', 'NEW', 1),
>+('In progress', 'IN_PROGRESS', 1),
>+('Completed', 'COMPLETED', 1),
>+('Unknown', 'UNKNOWN', 1);
>-- 
>2.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 30719: 144281 | 144285 | 144286 | 144287 | 144288 | 144289 | 144290 | 144291 | 144292 | 144293 | 144294 | 144295 | 144296 | 144297 | 144298 | 144299 | 144300 | 144301 | 144302 | 145760 | 145869 | 145870 | 145876 | 145934 | 146152 | 147658 | 147929 | 147930 | 147931 | 147932 | 147933 | 147934 | 147952 | 147953 | 147954 | 147955 | 147956 | 151152 | 151153 | 151154 | 151155 | 151156 | 151284 | 151285 | 151286 | 151287 | 151288 | 151303 | 151304 | 151305 | 151306 | 151307 | 151308 | 151309 | 151310 | 151311 | 151312 | 151513 | 151514 | 151515 | 151516 | 151517 | 151519 | 151520 | 151521 | 151522 | 151523 | 151571 | 151572 | 151573 | 151574 | 151575 | 151611 | 151612 | 151613 | 151614 | 151615 | 151804 | 151805 | 151806 | 151807 | 151808 | 151809 | 154046 | 154047 | 154048 | 154049 | 154050 | 154051 | 154052 | 155421 | 155422 | 155423 | 155424 | 155425 | 155426 | 155427 | 155428 | 155429 | 155430 | 155431 | 155432 | 155433 | 155434 | 155435 | 156556 | 156748 | 156749 | 156752 | 156783 | 156785 | 156811 | 156812 | 156827 | 156828