Attachment 55722 Details for Bug 17234 – Bug 17234: Add constraint_exists and column_exists to updatedatabase.pl

[patch] Bug 17234: Add constraint_exists and column_exists to updatedatabase.pl

Bug-17234-Add-constraintexists-and-columnexists-to.patch (text/plain), 10.67 KB, created by Jonathan Druart on 2016-09-20 15:57:46 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2016-09-20 15:57:46 UTC

Size: 10.67 KB

patch

obsolete

>From 48f6e79bf1e058749d60871fc521f2aab99ff4ef Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Tue, 20 Sep 2016 16:55:40 +0100
>Subject: [PATCH] Bug 17234: Add constraint_exists and column_exists to
> updatedatabase.pl
>
>These 2 subroutines will help us deal with the absense of ALTER IGNORE
>TABLE
>---
> installer/data/mysql/updatedatabase.pl | 219 ++++++++++++++++++++++-----------
> 1 file changed, 146 insertions(+), 73 deletions(-)
>
>diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
>index a82283d..afb199f 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -10420,15 +10420,21 @@ if ( CheckVersion($DBversion) ) {
> 
> $DBversion = "3.19.00.041";
> if ( CheckVersion($DBversion) ) {
>-    $dbh->do(q|
>-        ALTER TABLE suggestions ADD KEY status (STATUS)
>-    |);
>-    $dbh->do(q|
>-        ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
>-    |);
>-    $dbh->do(q|
>-        ALTER TABLE suggestions ADD KEY branchcode (branchcode)
>-    |);
>+    unless ( constraint_exists( 'suggestions', 'status' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE suggestions ADD KEY status (STATUS)
>+        |);
>+    }
>+    unless ( constraint_exists( 'suggestions', 'biblionumber' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
>+        |);
>+    }
>+    unless ( constraint_exists( 'suggestions', 'branchcode' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE suggestions ADD KEY branchcode (branchcode)
>+        |);
>+    }
>     print "Upgrade to $DBversion done (Bug 14132: suggestions table is missing indexes)\n";
>     SetVersion ($DBversion);
> }
>@@ -10442,12 +10448,14 @@ if ( CheckVersion($DBversion) ) {
>         WHERE auth_types.authtypecode IS NULL
>     });
> 
>-    $dbh->do(q{
>-        ALTER TABLE auth_subfield_structure
>-        ADD CONSTRAINT auth_subfield_structure_ibfk_1
>-        FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
>-        ON DELETE CASCADE ON UPDATE CASCADE
>-    });
>+    unless ( constraint_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {
>+        $dbh->do(q{
>+            ALTER TABLE auth_subfield_structure
>+            ADD CONSTRAINT auth_subfield_structure_ibfk_1
>+            FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
>+            ON DELETE CASCADE ON UPDATE CASCADE
>+        });
>+    }
> 
>     print "Upgrade to $DBversion done (Bug 8480: Add foreign key on auth_subfield_structure.authtypecode)\n";
>     SetVersion($DBversion);
>@@ -10563,31 +10571,58 @@ if ( CheckVersion($DBversion) ) {
> 
> $DBversion = "3.21.00.007";
> if ( CheckVersion($DBversion) ) {
>-    $dbh->do(q|
>-        ALTER TABLE aqbasket
>-            ADD KEY authorisedby (authorisedby)
>-    |);
>-    $dbh->do(q|
>-        ALTER TABLE aqbooksellers
>-            ADD KEY name (name(255))
>-    |);
>-    $dbh->do(q|
>-        ALTER TABLE aqbudgets
>-            ADD KEY budget_parent_id (budget_parent_id),
>-            ADD KEY budget_code (budget_code),
>-            ADD KEY budget_branchcode (budget_branchcode),
>-            ADD KEY budget_period_id (budget_period_id),
>-            ADD KEY budget_owner_id (budget_owner_id)
>-    |);
>-    $dbh->do(q|
>-        ALTER TABLE aqbudgets_planning
>-            ADD KEY budget_period_id (budget_period_id)
>-    |);
>-    $dbh->do(q|
>-        ALTER TABLE aqorders
>-            ADD KEY parent_ordernumber (parent_ordernumber),
>-            ADD KEY orderstatus (orderstatus)
>-    |);
>+    unless ( constraint_exists( 'aqbasket', 'authorisedby' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbasket
>+                ADD KEY authorisedby (authorisedby)
>+        |);
>+    }
>+    unless ( constraint_exists( 'aqbooksellers', 'name' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbooksellers
>+                ADD KEY name (name(255))
>+        |);
>+    }
>+    unless ( constraint_exists( 'aqbudgets', 'budget_parent_id' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbudgets
>+                ADD KEY budget_parent_id (budget_parent_id)|);
>+        }
>+    unless ( constraint_exists( 'aqbudgets', 'budget_code' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbudgets
>+                ADD KEY budget_code (budget_code)|);
>+    }
>+    unless ( constraint_exists( 'aqbudgets', 'budget_branchcode' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbudgets
>+                ADD KEY budget_branchcode (budget_branchcode)|);
>+    }
>+    unless ( constraint_exists( 'aqbudgets', 'budget_period_id' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbudgets
>+                ADD KEY budget_period_id (budget_period_id)|);
>+    }
>+    unless ( constraint_exists( 'aqbudgets', 'budget_owner_id' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbudgets
>+                ADD KEY budget_owner_id (budget_owner_id)|);
>+    }
>+    unless ( constraint_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqbudgets_planning
>+                ADD KEY budget_period_id (budget_period_id)|);
>+    }
>+    unless ( constraint_exists( 'aqorders', 'parent_ordernumber' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqorders
>+                ADD KEY parent_ordernumber (parent_ordernumber)|);
>+    }
>+    unless ( constraint_exists( 'aqorders', 'orderstatus' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE aqorders
>+                ADD KEY orderstatus (orderstatus)|);
>+    }
>     print "Upgrade to $DBversion done (Bug 14053: Acquisition db tables are missing indexes)\n";
>     SetVersion ($DBversion);
> }
>@@ -10688,9 +10723,12 @@ if ( CheckVersion($DBversion) ) {
>         INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
>         VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient|persistent','Choice')
>     });
>-    $dbh->do(q|
>-        ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
>-    |);
>+
>+    if ( constraint_exists( 'oai_sets_biblios', 'oai_sets_biblios_ibfk_1' ) ) {
>+        $dbh->do(q|
>+            ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
>+        |);
>+    }
>     print "Upgrade to $DBversion done (Bug 3206: OAI repository deleted record support)\n";
>     SetVersion ($DBversion);
> }
>@@ -10771,12 +10809,14 @@ if ( CheckVersion($DBversion) ) {
> 
> $DBversion = "3.21.00.019";
> if ( CheckVersion($DBversion) ) {
>-    $dbh->do(q{
>-        ALTER TABLE reserves DROP constrainttype
>-    });
>-    $dbh->do(q{
>-        ALTER TABLE old_reserves DROP constrainttype
>-    });
>+    if ( column_exists( 'reserves', 'constrainttype' ) ) {
>+        $dbh->do(q{
>+            ALTER TABLE reserves DROP constrainttype
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE old_reserves DROP constrainttype
>+        });
>+    }
>     $dbh->do(q{
>         DROP TABLE IF EXISTS reserveconstraints
>     });
>@@ -10816,8 +10856,10 @@ if ( CheckVersion($DBversion) ) {
>     my ($print_error) = $dbh->{PrintError};
>     $dbh->{RaiseError} = 0;
>     $dbh->{PrintError} = 0;
>-    $dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
>-    $dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
>+    if ( constraint_exists('course_reserves', 'course_reserves_ibfk_2') ) {
>+        $dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
>+        $dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
>+    }
>     $dbh->{PrintError} = $print_error;
> 
>     $dbh->do(q{
>@@ -10915,28 +10957,32 @@ if ( CheckVersion($DBversion) ) {
> 
> $DBversion = "3.21.00.028";
> if ( CheckVersion($DBversion) ) {
>-    $dbh->do(q{
>-        ALTER TABLE uploaded_files
>-            ADD COLUMN public tinyint,
>-            ADD COLUMN permanent tinyint
>-    });
>-    $dbh->do(q{
>-        UPDATE uploaded_files SET public=1, permanent=1
>-    });
>-    $dbh->do(q{
>-        ALTER TABLE uploaded_files
>-            CHANGE COLUMN categorycode uploadcategorycode tinytext
>-    });
>+    unless ( column_exists('uploaded_files', 'public') ) {
>+        $dbh->do(q{
>+            ALTER TABLE uploaded_files
>+                ADD COLUMN public tinyint,
>+                ADD COLUMN permanent tinyint
>+        });
>+        $dbh->do(q{
>+            UPDATE uploaded_files SET public=1, permanent=1
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE uploaded_files
>+                CHANGE COLUMN categorycode uploadcategorycode tinytext
>+        });
>+    }
>     print "Upgrade to $DBversion done (Bug 14321: Merge UploadedFile and UploadedFiles into Koha::Upload)\n";
>     SetVersion($DBversion);
> }
> 
> $DBversion = "3.21.00.029";
> if ( CheckVersion($DBversion) ) {
>-    $dbh->do(q{
>-        ALTER TABLE discharges
>-            ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
>-    });
>+    unless ( column_exists('discharges', 'discharge_id') ) {
>+        $dbh->do(q{
>+            ALTER TABLE discharges
>+                ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
>+        });
>+    }
>     print "Upgrade to $DBversion done (Bug 14368: Add discharges history)\n";
>     SetVersion($DBversion);
> }
>@@ -12983,12 +13029,14 @@ if ( CheckVersion($DBversion) ) {
> 
> $DBversion = "16.06.00.027";
> if ( CheckVersion($DBversion) ) {
>-    $dbh->do(q{
>-        ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
>-    });
>-    $dbh->do(q{
>-        ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
>-    });
>+    unless ( column_exists('borrowers', 'lastseen') ) {
>+        $dbh->do(q{
>+            ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
>+        });
>+        $dbh->do(q{
>+            ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
>+        });
>+    }
>     $dbh->do(q{
>         INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');
>     });
>@@ -13144,4 +13192,29 @@ sub CheckVersion {
>     }
> }
> 
>+sub constraint_exists {
>+    my ( $table_name, $key_name ) = @_;
>+    my $dbh = C4::Context->dbh;
>+    my ($exists) = $dbh->selectrow_array(
>+        qq|
>+        SHOW INDEX FROM $table_name
>+        WHERE key_name = ?
>+        |, undef, $key_name
>+    );
>+    return $exists;
>+}
>+
>+sub column_exists {
>+    my ( $table_name, $column_name ) = @_;
>+    my $dbh = C4::Context->dbh;
>+    my ($exists) = $dbh->selectrow_array(
>+        qq|
>+        SHOW COLUMNS FROM $table_name
>+        WHERE Field = ?
>+        |, undef, $column_name
>+    );
>+    return $exists;
>+
>+}
>+
> exit;
>-- 
>2.8.1

Actions: View | Diff | Splinter Review

Attachments on bug 17234: 55113 | 55144 | 55634 | 55635 | 55704 | 55722 | 55729 | 55833 | 56820 | 56821 | 56822 | 56823 | 56824 | 56825 | 58548 | 58551 | 58552 | 58865 | 59003