Attachment 32777 Details for Bug 12601 – Bug 12601: Add a foreign key for aqorders.budget_id

[patch] Bug 12601: Add a foreign key for aqorders.budget_id

Bug-12601-Add-a-foreign-key-for-aqordersbudgetid.patch (text/plain), 5.18 KB, created by Katrin Fischer on 2014-10-26 20:55:29 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Katrin Fischer

Created: 2014-10-26 20:55:29 UTC

Size: 5.18 KB

patch

obsolete

>From 553b8fa31a990a649d7614546960b541c4ad5650 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@biblibre.com>
>Date: Fri, 18 Jul 2014 12:34:07 +0200
>Subject: [PATCH] Bug 12601: Add a foreign key for aqorders.budget_id
>
>When creating an order, the fund value is mandatory but the DB structure
>does not show this constraint.
>
>The aqorders.budget_id field should be linked to aqbudgets.budget_id.
>
>The updatedatabase.pl (entry 3.01.00.077) commented this constraint,
>certainly for a retro compatibility reason.
>
>Actually I found some cases (in production) where aqorders.budget_id is
>set to "0". To add this constraint, we should add a temporary fund to
>linked orphan orders.
>
>Test plan:
>0/ Verify it is not possible to create an order not linked to a fund via
>the Koha interface.
>1/ Using your SQL CLI (or equivalent), create or update 1+ orders and set
>"0" in the budget_id field.
>2/ Execute the updatedabase script.
>3/ Verify that your order is linked to a new fund (which is linked to a
>new budget).
>4/ Verify the constraint has correctly been added (show create table
>aqorders).
>
>Signed-off-by: Paola Rossi <paola.rossi@cineca.it>
>---
> installer/data/mysql/kohastructure.sql             |  1 +
> installer/data/mysql/updatedatabase.pl             | 42 ++++++++++++++++++++++
> .../prog/en/modules/admin/aqbudgets.tt             |  1 +
> 3 files changed, 44 insertions(+)
>
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index e91bb6b..433cbfa 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -3051,6 +3051,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items
>   KEY `basketno` (`basketno`),
>   KEY `biblionumber` (`biblionumber`),
>   KEY `budget_id` (`budget_id`),
>+  CONSTRAINT `aqorders_budget_id_fk` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>   CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE,
>diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
>index e1fb8be..918151f 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -8845,6 +8845,48 @@ if ( CheckVersion($DBversion) ) {
>    SetVersion ($DBversion);
> }
> 
>+
>+$DBversion = "3.17.00.XXX";
>+if ( CheckVersion($DBversion) ) {
>+    my $number_of_orders_not_linked = $dbh->selectcol_arrayref(q|
>+        SELECT COUNT(*)
>+        FROM aqorders o
>+        WHERE NOT EXISTS (
>+            SELECT NULL
>+            FROM aqbudgets b
>+            WHERE b.budget_id = o.budget_id
>+        );
>+    |);
>+
>+    if ( $number_of_orders_not_linked->[0] > 0 ) {
>+        $dbh->do(q|
>+            INSERT INTO aqbudgetperiods(budget_period_startdate, budget_period_enddate, budget_period_active, budget_period_description, budget_period_total) VALUES ( CAST(NOW() AS date), CAST(NOW() AS date), 0, "WARNING: This budget has been automatically created by the updatedatabase script, please see bug 12601 for more information", 0)
>+        |);
>+        my $budget_period_id = $dbh->last_insert_id( undef, undef, 'aqbudgetperiods', undef );
>+        $dbh->do(qq|
>+            INSERT INTO aqbudgets(budget_code, budget_name, budget_amount, budget_period_id) VALUES ( "BACKUP_TMP", "WARNING: fund created by the updatedatabase script, please see bug 12601", 0, $budget_period_id );
>+        |);
>+        my $budget_id = $dbh->last_insert_id( undef, undef, 'aqbudgets', undef );
>+        $dbh->do(qq|
>+            UPDATE aqorders o
>+            SET budget_id = $budget_id
>+            WHERE NOT EXISTS (
>+                SELECT NULL
>+                FROM aqbudgets b
>+                WHERE b.budget_id = o.budget_id
>+            )
>+        |);
>+    }
>+
>+    $dbh->do(q|
>+        ALTER TABLE aqorders
>+        ADD CONSTRAINT aqorders_budget_id_fk FOREIGN KEY (budget_id) REFERENCES aqbudgets(budget_id) ON DELETE CASCADE ON UPDATE CASCADE
>+    |);
>+
>+    print "Upgrade to $DBversion done (Bug 12601 - Add new foreign key aqorders.budget_id" . ( ( $number_of_orders_not_linked->[0] > 0 )  ? ' WARNING: temporary budget and fund have been created (search for "BACKUP_TMP"). At least one of your order was not linked to a budget' : '' ) . ")\n";
>+    SetVersion($DBversion);
>+}
>+
> =head1 FUNCTIONS
> 
> =head2 TableExists($table)
>diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt
>index b0c4344..78299e9 100644
>--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt
>+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt
>@@ -593,6 +593,7 @@ var MSG_PARENT_BENEATH_BUDGET = "- " + _("New budget-parent is beneath budget")
> 
> [% IF op == 'delete_confirm' %]
> <div class="dialog alert"> <h3>Delete fund [% budget_name %]?</h3>
>+If you delete this fund, all orders linked to this fund will be deleted!
> <table>
>     <tr>
>     <th scope="row">Fund amount:</th>
>-- 
>1.9.1

Actions: View | Diff | Splinter Review

Attachments on bug 12601: 29820 | 30977 | 32543 | 32777 | 33067 | 33798 | 33799 | 62992