Attachment 41477 Details for Bug 12772 – Bug 12772 - Update Database (Based on Bug13624)

[patch] Bug 12772 - Update Database (Based on Bug13624)

Bug-12772---Update-Database-Based-on-Bug13624.patch (text/plain), 6.91 KB, created by Maxime Beaulieu on 2015-08-13 17:34:21 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Maxime Beaulieu

Created: 2015-08-13 17:34:21 UTC

Size: 6.91 KB

patch

obsolete

>From c7a106418566a2e57a5be156221c247f814e6b60 Mon Sep 17 00:00:00 2001
>From: mxbeaulieu <maxime.beaulieu@inlibro.com>
>Date: Tue, 21 Jul 2015 10:38:08 -0400
>Subject: [PATCH] Bug 12772 - Update Database (Based on Bug13624)
>
>This patch applies to Bug13624.
>
>Removes letter number from overduerules and overdurules_transport_types
>
>	modified:   installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
>	modified:   installer/data/mysql/kohastructure.sql
>---
> .../update_13624_overduerules_transport_type.pl    |   33 ++++++++++----------
> installer/data/mysql/kohastructure.sql             |   19 +++++------
> 2 files changed, 24 insertions(+), 28 deletions(-)
>
>diff --git a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
>index be3af3c..bcadb59 100755
>--- a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
>+++ b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl
>@@ -14,19 +14,17 @@ print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table
>         `overduerules_id` int(11) NOT NULL AUTO_INCREMENT,
>         `branchcode` varchar(10) NOT NULL DEFAULT '',
>         `categorycode` varchar(10) NOT NULL DEFAULT '',
>-        `delay1` int(4) DEFAULT NULL,
>-        `letter1` varchar(20) DEFAULT NULL,
>-        `debarred1` varchar(1) DEFAULT '0',
>-        `delay2` int(4) DEFAULT NULL,
>-        `debarred2` varchar(1) DEFAULT '0',
>-        `letter2` varchar(20) DEFAULT NULL,
>-        `delay3` int(4) DEFAULT NULL,
>-        `letter3` varchar(20) DEFAULT NULL,
>-        `debarred3` int(1) DEFAULT '0',
>-        PRIMARY KEY (`overduerules_id`),
>-        UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
>+        `itemtype` varchar(10) default '',
>+        `onhold` tinyint(1) default 0,
>+        `delay` int(4) NOT NULL default 1,
>+        `letter` varchar(20) default NULL,
>+        `debarred` varchar(1) default 0,
>+        PRIMARY KEY (`overduerules_id`)
>         ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
>-    $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules");
>+     foreach my $i (1..3){
>+        $dbh->do("INSERT INTO overduerules (delay, letter, debarred, branchcode, categorycode) ".
>+                 "SELECT delay$i AS delay, letter$i AS letter, debarred$i as debarred, branchcode, categorycode FROM old_overduerules");
>+     }
>     $dbh->do("DROP TABLE old_overduerules");
>     $dbh->do("ALTER TABLE overduerules_transport_types
>               ADD COLUMN overduerules_id int(11) NOT NULL");
>@@ -36,11 +34,14 @@ print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table
>               DROP FOREIGN KEY overduerules_fk,
>               ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
>               DROP COLUMN branchcode,
>-              DROP COLUMN categorycode");
>-    my $s = $dbh->prepare("INSERT INTO overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) "
>-                         ." VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)");
>+              DROP COLUMN categorycode,
>+              DROP COLUMN letternumber");
>+    my $s = $dbh->prepare(
>+        "INSERT INTO overduerules_transport_types (overduerules_id, id, message_transport_type) ".
>+        "VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?)"
>+    );
>     foreach my $mtt(@$mtts){
>-        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} );
>+        $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{message_transport_type} );
>     }
>     $dbh->do("SET FOREIGN_KEY_CHECKS=1");
> #    print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n";
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index eaec3df..572fad7 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1708,17 +1708,12 @@ CREATE TABLE `overduerules` ( -- overdue notice status and triggers
>   `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
>   `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries)
>   `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
>-  `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
>-  `letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice
>-  `debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)
>-  `delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent
>-  `debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)
>-  `letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice
>-  `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
>-  `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
>-  `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
>-  PRIMARY KEY  (`overduerules_id`),
>-  UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
>+ `itemtype` varchar(10) default '',
>+ `onhold` tinyint(1) default 0,
>+ `delay` int(4) NOT NULL default 1,
>+ `letter` varchar(20) default NULL,
>+ `debarred` varchar(1) default 0,
>+  PRIMARY KEY  (`overduerules_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
> 
> --
>@@ -2596,7 +2591,7 @@ CREATE TABLE overduerules_transport_types(
>     `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
>     `overduerules_id` INT(11) NOT NULL,
>     PRIMARY KEY (id),
>-    CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
>+    CONSTRAINT overduerules_transport_types_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
>     CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
> 
>-- 
>1.7.9.5

Actions: View | Diff | Splinter Review

Attachments on bug 12772: 41477 | 41478 | 41479 | 41480 | 41855 | 42126 | 45184 | 45185 | 45186 | 45187 | 45188 | 45189 | 45190 | 45191 | 45192 | 45193 | 45194 | 45195 | 45196 | 45197 | 45198 | 45199 | 45200 | 45201 | 45202 | 45203 | 45204 | 45544 | 45545 | 45546 | 45547 | 45548 | 45549 | 45550 | 45551 | 45552 | 56063 | 56077