From 1cd2ad6e052d12e6ccf4d0b098ff2dd7a1fa526a Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 9 Feb 2018 15:44:25 -0300 Subject: [PATCH] Bug 16735: DBRev 17.12.00.008 Signed-off-by: Jonathan Druart --- Koha.pm | 2 +- installer/data/mysql/kohastructure.sql | 2 +- installer/data/mysql/updatedatabase.pl | 59 ++++++++++++++------------ 3 files changed, 34 insertions(+), 29 deletions(-) diff --git a/Koha.pm b/Koha.pm index d2c5707eef..5aa10ab2ce 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "17.12.00.007"; +$VERSION = "17.12.00.008"; sub version { return $VERSION; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3dee1a6bfc..d23364145f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4148,7 +4148,7 @@ CREATE TABLE library_groups ( PRIMARY KEY id ( id ), FOREIGN KEY (parent_id) REFERENCES library_groups(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON UPDATE CASCADE ON DELETE CASCADE, - UNIQUE( title ) + UNIQUE KEY title ( title ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 6017a5526d..8c30ed8c33 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -15236,7 +15236,7 @@ if( CheckVersion( $DBversion ) ) { PRIMARY KEY id ( id ), FOREIGN KEY (parent_id) REFERENCES library_groups(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON UPDATE CASCADE ON DELETE CASCADE, - UNIQUE( title ) + UNIQUE KEY title ( title ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; }); } @@ -15248,42 +15248,47 @@ if( CheckVersion( $DBversion ) ) { $DBversion = '17.12.00.008'; if ( CheckVersion($DBversion) ) { - $dbh->do(q{ - INSERT INTO library_groups ( title, description, created_on ) VALUES ( '__SEARCH_GROUPS__', 'Library search groups - Staff only', NOW() ) - }); - my $search_groups_staff_root_id = $dbh->last_insert_id(undef, undef, 'library_groups', undef); + if ( TableExists( 'branchcategories' ) and TableExists('branchrelations' )) { + $dbh->do(q{ + INSERT INTO library_groups ( title, description, created_on ) VALUES ( '__SEARCH_GROUPS__', 'Library search groups - Staff only', NOW() ) + }); + my $search_groups_staff_root_id = $dbh->last_insert_id(undef, undef, 'library_groups', undef); - $dbh->do(q{ - INSERT INTO library_groups ( title, description, created_on ) VALUE ( '__SEARCH_GROUPS_OPAC__', 'Library search groups - Staff only', NOW() ) - }); - my $search_groups_opac_root_id = $dbh->last_insert_id(undef, undef, 'library_groups', undef); + $dbh->do(q{ + INSERT INTO library_groups ( title, description, created_on ) VALUE ( '__SEARCH_GROUPS_OPAC__', 'Library search groups - Staff only', NOW() ) + }); + my $search_groups_opac_root_id = $dbh->last_insert_id(undef, undef, 'library_groups', undef); - my $sth = $dbh->prepare("SELECT * FROM branchcategories"); + my $sth = $dbh->prepare("SELECT * FROM branchcategories"); - my $sth2 = $dbh->prepare("INSERT INTO library_groups ( parent_id, title, description, created_on ) VALUES ( ?, ?, ?, NOW() )"); + my $sth2 = $dbh->prepare("INSERT INTO library_groups ( parent_id, title, description, created_on ) VALUES ( ?, ?, ?, NOW() )"); - my $sth3 = $dbh->prepare("SELECT * FROM branchrelations WHERE categorycode = ?"); + my $sth3 = $dbh->prepare("SELECT * FROM branchrelations WHERE categorycode = ?"); - my $sth4 = $dbh->prepare("INSERT INTO library_groups ( parent_id, branchcode, created_on ) VALUES ( ?, ?, NOW() )"); + my $sth4 = $dbh->prepare("INSERT INTO library_groups ( parent_id, branchcode, created_on ) VALUES ( ?, ?, NOW() )"); - $sth->execute(); - while ( my $lc = $sth->fetchrow_hashref ) { - my $description = $lc->{categorycode}; - $description .= " - " . $lc->{codedescription} if $lc->{codedescription}; - - $sth2->execute( - $lc->{show_in_pulldown} ? $search_groups_opac_root_id : $search_groups_staff_root_id, - $lc->{categoryname}, - $description, - ); + $sth->execute(); + while ( my $lc = $sth->fetchrow_hashref ) { + my $description = $lc->{categorycode}; + $description .= " - " . $lc->{codedescription} if $lc->{codedescription}; - my $subgroup_id = $dbh->last_insert_id(undef, undef, 'library_groups', undef); + $sth2->execute( + $lc->{show_in_pulldown} ? $search_groups_opac_root_id : $search_groups_staff_root_id, + $lc->{categoryname}, + $description, + ); - $sth3->execute( $lc->{categorycode} ); + my $subgroup_id = $dbh->last_insert_id(undef, undef, 'library_groups', undef); - while ( my $l = $sth3->fetchrow_hashref ) { - $sth4->execute( $subgroup_id, $l->{branchcode} ); + $sth3->execute( $lc->{categorycode} ); + + while ( my $l = $sth3->fetchrow_hashref ) { + $sth4->execute( $subgroup_id, $l->{branchcode} ); + } } + + $dbh->do("DROP TABLE branchrelations"); + $dbh->do("DROP TABLE branchcategories"); } print "Upgrade to $DBversion done (Bug 16735 - Migrate library search groups into the new hierarchical groups)\n"; -- 2.39.2