@@ -, +, @@ column_exists to C4::Installer Bug 17234: Need to separate KEY and FOREIGN KEY checks In the previous patch we use the constraint_exists subroutine to verify if an index or a foreign key exists. But the `SHOW INDEX` query does not return foreign keys (as its name suggests!). We need another subroutine foreign_key_exists to check the FK existence. I have found that because t/db_dependent/TestBuilder.t fails on oai_sets_biblios, because oai_sets_biblios_ibfk_1 has not been removed. Test plan: 0/ Do not apply this patch 1/ Use a 3.20 DB 2/ update the DB 3/ SHOW CREATE TABLE oai_sets_biblios will display oai_sets_biblios_ibfk_1 Apply the patch and repeat 1, 2, 3 => Will not display oai_sets_biblios_ibfk_1 It has been removed as expected. Bug 17234: Test the column and constraint non-existence Bug 17234: Two new functions lack tests This adds two tests to t/db_dependent/Installer.t TEST PLAN --------- 1) Apply patch 2) prove -v t/db_dependent/Installer.t -- column and constraint tests were added. 3) run koha qa test tools Bug 17234: Move new subroutines to C4::Installer 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 --- C4/Installer.pm | 37 +++++++++++++++++++++++ t/db_dependent/Installer.t | 73 ++++++++++++++++++++++++++++++++++++---------- 2 files changed, 95 insertions(+), 15 deletions(-) --- a/C4/Installer.pm +++ a/C4/Installer.pm @@ -26,6 +26,13 @@ use C4::Installer::PerlModules; use DBI; use Koha; +use vars qw(@ISA @EXPORT); +BEGIN { + require Exporter; + @ISA = qw( Exporter ); + push @EXPORT, qw( foreign_key_exists index_exists column_exists ); +}; + =head1 NAME C4::Installer @@ -487,6 +494,36 @@ sub get_file_path_from_name { } +sub foreign_key_exists { + my ( $table_name, $constraint_name ) = @_; + my $dbh = C4::Context->dbh; + my (undef, $infos) = $dbh->selectrow_array(qq|SHOW CREATE TABLE $table_name|); + return $infos =~ m|CONSTRAINT `$constraint_name` FOREIGN KEY|; +} + +sub index_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; +} =head1 AUTHOR --- a/t/db_dependent/Installer.t +++ a/t/db_dependent/Installer.t @@ -1,23 +1,66 @@ #!/usr/bin/perl # -# This Koha test module is a stub! -# Add more tests here!!! -# Bug 11541 +# This file is part of Koha. +# +# Copyright (C) 2014 Aleisha Amohia (Bug 11541) +# Copyright (C) 2016 Mark Tompsett (Bug 17234) +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . -use strict; -use warnings; +# This Koha test module is still a stub! +# Add more tests here!!! -use Test::More tests => 9; +use Modern::Perl; +use Test::More tests => 15; +use Koha::Database; BEGIN { - use_ok('C4::Installer'); + use_ok('C4::Installer'); } -ok ( my $installer = C4::Installer->new(), 'Testing NewInstaller' ); -is ( ref $installer, 'C4::Installer', 'Testing class of object' ); -is ( $installer->{'dbname'}, C4::Context->config("database"), 'Testing DbName' ); -is ( $installer->{'dbms'}, C4::Context->config("db_scheme") ? C4::Context->config("db_scheme") : "mysql", 'Testing DbScheme' ); -is ( $installer->{'hostname'}, C4::Context->config("hostname"), 'Testing Hostname' ); -is ( $installer->{'port'}, C4::Context->config("port"), 'Testing Port' ); -is ( $installer->{'user'}, C4::Context->config("user"), 'Testing User' ); -is ( $installer->{'password'}, C4::Context->config("pass"), 'Testing Password' ); +ok( my $installer = C4::Installer->new(), 'Testing NewInstaller' ); +is( ref $installer, 'C4::Installer', 'Testing class of object' ); +is( $installer->{'dbname'}, C4::Context->config('database'), 'Testing DbName' ); +is( + $installer->{'dbms'}, + C4::Context->config('db_scheme') + ? C4::Context->config('db_scheme') + : 'mysql', + 'Testing DbScheme' +); +is( + $installer->{'hostname'}, + C4::Context->config('hostname'), + 'Testing Hostname' +); +is( $installer->{'port'}, C4::Context->config('port'), 'Testing Port' ); +is( $installer->{'user'}, C4::Context->config('user'), 'Testing User' ); +is( $installer->{'password'}, C4::Context->config('pass'), 'Testing Password' ); + +# The borrower table is known to have columns and constraints. +my $schema = Koha::Database->new->schema; +my $source = $schema->source('Borrower'); + +my @column_names = $source->columns(); +my $column_name = $column_names[0]; +ok( column_exists( 'borrowers', $column_name ), 'Known column does exist' ); +ok( ! column_exists( 'borrowers', 'xxx'), 'Column xxx does not exist' ); + +my @constraint_names = $source->unique_constraint_names(); +my $constraint_name = $constraint_names[0]; +ok( index_exists( 'borrowers', $constraint_name), 'Known contraint does exist' ); +ok( ! index_exists( 'borrowers', 'xxx'), 'Constraint xxx does not exist' ); + +ok( foreign_key_exists( 'borrowers', 'borrowers_ibfk_1' ), 'FK borrowers_ibfk_1 exists' ); +ok( ! foreign_key_exists( 'borrowers', 'xxx' ), 'FK xxxx does not exist' ); --