From b9853cc1bdf335d08b1ce27560c713bc717d4526 Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Wed, 24 Jul 2013 15:03:34 -0400 Subject: [PATCH] refactoring --- Open-ILS/src/sql/Pg/make-pgtap-tests.pl | 341 +++++++++++++++++++------------- 1 file changed, 203 insertions(+), 138 deletions(-) diff --git a/Open-ILS/src/sql/Pg/make-pgtap-tests.pl b/Open-ILS/src/sql/Pg/make-pgtap-tests.pl index 7313175405..336155989b 100755 --- a/Open-ILS/src/sql/Pg/make-pgtap-tests.pl +++ b/Open-ILS/src/sql/Pg/make-pgtap-tests.pl @@ -16,6 +16,10 @@ GetOptions( 'db_pw=s' => \$db_pw, ); +#---------------------------------------------------------- +# Database connection +#---------------------------------------------------------- + use DBI; my $dsn = "dbi:Pg:dbname=$db_name;host=$db_host;port=$db_port"; @@ -27,7 +31,52 @@ unless($dbh) { exit 1; } -print q^ +#---------------------------------------------------------- +# Main logic +#---------------------------------------------------------- + +print pgtap_sql_header(); +handle_schemas( + sub { + my $schema = shift; + handle_tables( + $schema, + sub { + my $schema = shift; + my $table = shift; + handle_columns( + $schema, + $table, + undef + ); + } + ); + handle_views( + $schema, + sub { + my $schema = shift; + my $view = shift; + handle_columns( + $schema, + $view, + undef + ); + } + ); + + } +); +print pgtap_sql_footer(); + +$dbh->disconnect; +exit 0; + +#---------------------------------------------------------- +# subroutines +#---------------------------------------------------------- + +sub pgtap_sql_header { + return q^ \set ECHO \set QUIET 1 -- Turn off echo and keep things quiet. @@ -50,148 +99,15 @@ SELECT no_plan(); -- Run the tests. ^; - -my @schemas = fetch_schemas(); -foreach my $schema ( @schemas ) { - print "\n-- schema " . $dbh->quote($schema) . "\n\n"; - print "SELECT has_schema(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote("Has schema $schema") . "\n);\n"; - - sub handle_columns { - my ($schema,$table) = (shift,shift); - my @columns = fetch_columns($schema,$table); - foreach my $column_array ( @columns ) { - - my $column = $column_array->[0]; - my $col_type_original = $column_array->[1]; - my $col_type = $col_type_original; - my $col_nullable = $column_array->[2]; - my $col_default = $column_array->[3]; - my $col_numeric_precision = $column_array->[4]; - my $col_numeric_scale = $column_array->[5]; - my $col_udt_schema = $column_array->[6]; - my $col_udt_name = $column_array->[7]; - my $col_character_maximum_length = $column_array->[8]; - - if (defined $col_default && $col_default =~ /::text/) { - $col_default =~ s/^'(.*)'::text$/$1/; - } - if (defined $col_default && $col_default =~ /::bpchar/) { - $col_default =~ s/^'(.*)'::bpchar$/$1/; - } - if ($col_type eq 'numeric' && defined $col_numeric_precision) { - $col_type .= "($col_numeric_precision"; - if (defined $col_numeric_scale) { - $col_type .= ",$col_numeric_scale"; - } - $col_type .= ')'; - } - if ($col_type eq 'USER-DEFINED' && defined $col_udt_schema) { - $col_type = "$col_udt_schema.$col_udt_name"; - if ($col_type eq 'public.hstore') { - $col_type = 'hstore'; # an exception - } - } - if ($col_type eq 'character' && defined $col_character_maximum_length) { - $col_type .= "($col_character_maximum_length)"; - } - if ($col_type eq 'ARRAY' && defined $col_udt_name) { - $col_type = substr($col_udt_name,1) . '[]'; - } - - print "\n-- -- -- column " . $dbh->quote("$schema.$table.$column") . "\n\n"; - print "SELECT has_column(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote($table) . ",\n"; - print "\t" . $dbh->quote($column) . ",\n"; - print "\t" . $dbh->quote("Has column $schema.$table.$column") . "\n);\n"; - print "SELECT col_type_is(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote($table) . ",\n"; - print "\t" . $dbh->quote($column) . ",\n"; - print "\t" . $dbh->quote($col_type) . ",\n"; - print "\t" . $dbh->quote("Column $schema.$table.$column is type $col_type"); - print "\n);\n"; - if ($col_nullable eq 'YES') { - print "SELECT col_is_null(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote($table) . ",\n"; - print "\t" . $dbh->quote($column) . ",\n"; - print "\t" . $dbh->quote("Column $schema.$table.$column is nullable"); - print "\n);\n"; - } else { - print "SELECT col_not_null(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote($table) . ",\n"; - print "\t" . $dbh->quote($column) . ",\n"; - print "\t" . $dbh->quote("Column $schema.$table.$column is not nullable"); - print "\n);\n"; - } - if (defined $col_default) { - my $fixme = ''; - if ($col_type eq 'interval') { - # FIXME - ERROR: invalid input syntax for type interval: "'1 day'::interval" - $fixme = '-- FIXME type 1 -- '; - } elsif ($col_type eq 'time without time zone') { - # FIXME - ERROR: invalid input syntax for type time: "'17:00:00'::time without time zone" - $fixme = '-- FIXME type 2 -- '; - } elsif ($col_default =~ 'org_unit_custom_tree_purpose') { - # FIXME - ERROR: invalid input value for enum actor.org_unit_custom_tree_purpose: "'opac'::actor.org_unit_custom_tree_purpose" - $fixme = '-- FIXME type 3 -- '; - } elsif ($col_type eq 'integer' && $col_default =~ '\(-?\d+\)') { - # FIXME - ERROR: invalid input syntax for integer: "(-1)" - $fixme = '-- FIXME type 4 -- '; - } elsif ($col_type_original eq 'USER-DEFINED') { - # FIXME - ERROR: Unexpected end of string - $fixme = '-- FIXME type 5 -- '; - } - # I would love to SELECT todo past these, but they cause hard failures - print $fixme . "SELECT col_default_is(\n"; - print $fixme . "\t" . $dbh->quote($schema) . ",\n"; - print $fixme . "\t" . $dbh->quote($table) . ",\n"; - print $fixme . "\t" . $dbh->quote($column) . ",\n"; - print $fixme . "\t" . $dbh->quote($col_default) . ",\n"; - print $fixme . "\t" . $dbh->quote("Column $schema.$table.$column has default value: $col_default"); - print "\n$fixme);\n"; - } else { - print "SELECT col_hasnt_default(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote($table) . ",\n"; - print "\t" . $dbh->quote($column) . ",\n"; - print "\t" . $dbh->quote("Column $schema.$table.$column has no default value"); - print "\n);\n"; - } - } - } - - my @tables = fetch_tables($schema); - foreach my $table ( @tables ) { - print "\n-- -- table " . $dbh->quote("$schema.$table") . "\n\n"; - print "SELECT has_table(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote($table) . ",\n"; - print "\t" . $dbh->quote("Has table $schema.$table") . "\n);\n"; - handle_columns($schema,$table); - } - - my @views = fetch_views($schema); - foreach my $view ( @views ) { - print "\n-- -- view " . $dbh->quote("$schema.$view") . "\n\n"; - print "SELECT has_view(\n"; - print "\t" . $dbh->quote($schema) . ",\n"; - print "\t" . $dbh->quote($view) . ",\n"; - print "\t" . $dbh->quote("Has view $schema.$view") . "\n);\n"; - handle_columns($schema,$view); - } - } -print q^ +sub pgtap_sql_footer { + return q^ -- Finish the tests and clean up. SELECT * FROM finish(); ROLLBACK; ^; +} sub fetch_schemas { my $sth = $dbh->prepare(" @@ -258,5 +174,154 @@ sub fetch_columns { return sort { $a->[0] cmp $b->[0] } (map { $_ } @{ $columns }); } -$dbh->disconnect; +sub handle_schemas { + my $callback = shift; + + my @schemas = fetch_schemas(); + foreach my $schema ( @schemas ) { + print "\n-- schema " . $dbh->quote($schema) . "\n\n"; + print "SELECT has_schema(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote("Has schema $schema") . "\n);\n"; + $callback->($schema) if $callback; + } +} + +sub handle_tables { + my $schema = shift; + my $callback = shift; + + my @tables = fetch_tables($schema); + foreach my $table ( @tables ) { + print "\n-- -- table " . $dbh->quote("$schema.$table") . "\n\n"; + print "SELECT has_table(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote($table) . ",\n"; + print "\t" . $dbh->quote("Has table $schema.$table") . "\n);\n"; + $callback->($schema,$table) if $callback; + } +} + +sub handle_views { + my $schema = shift; + my $callback = shift; + + my @views = fetch_views($schema); + foreach my $view ( @views ) { + print "\n-- -- view " . $dbh->quote("$schema.$view") . "\n\n"; + print "SELECT has_view(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote($view) . ",\n"; + print "\t" . $dbh->quote("Has view $schema.$view") . "\n);\n"; + $callback->($schema,$view) if $callback; + } +} + +sub handle_columns { + my ($schema,$table) = (shift,shift); + my @columns = fetch_columns($schema,$table); + foreach my $column_array ( @columns ) { + + my $column = $column_array->[0]; + my $col_type_original = $column_array->[1]; + my $col_type = $col_type_original; + my $col_nullable = $column_array->[2]; + my $col_default = $column_array->[3]; + my $col_numeric_precision = $column_array->[4]; + my $col_numeric_scale = $column_array->[5]; + my $col_udt_schema = $column_array->[6]; + my $col_udt_name = $column_array->[7]; + my $col_character_maximum_length = $column_array->[8]; + + if (defined $col_default && $col_default =~ /::text/) { + $col_default =~ s/^'(.*)'::text$/$1/; + } + if (defined $col_default && $col_default =~ /::bpchar/) { + $col_default =~ s/^'(.*)'::bpchar$/$1/; + } + if ($col_type eq 'numeric' && defined $col_numeric_precision) { + $col_type .= "($col_numeric_precision"; + if (defined $col_numeric_scale) { + $col_type .= ",$col_numeric_scale"; + } + $col_type .= ')'; + } + if ($col_type eq 'USER-DEFINED' && defined $col_udt_schema) { + $col_type = "$col_udt_schema.$col_udt_name"; + if ($col_type eq 'public.hstore') { + $col_type = 'hstore'; # an exception + } + } + if ($col_type eq 'character' && defined $col_character_maximum_length) { + $col_type .= "($col_character_maximum_length)"; + } + if ($col_type eq 'ARRAY' && defined $col_udt_name) { + $col_type = substr($col_udt_name,1) . '[]'; + } + + print "\n-- -- -- column " . $dbh->quote("$schema.$table.$column") . "\n\n"; + print "SELECT has_column(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote($table) . ",\n"; + print "\t" . $dbh->quote($column) . ",\n"; + print "\t" . $dbh->quote("Has column $schema.$table.$column") . "\n);\n"; + print "SELECT col_type_is(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote($table) . ",\n"; + print "\t" . $dbh->quote($column) . ",\n"; + print "\t" . $dbh->quote($col_type) . ",\n"; + print "\t" . $dbh->quote("Column $schema.$table.$column is type $col_type"); + print "\n);\n"; + if ($col_nullable eq 'YES') { + print "SELECT col_is_null(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote($table) . ",\n"; + print "\t" . $dbh->quote($column) . ",\n"; + print "\t" . $dbh->quote("Column $schema.$table.$column is nullable"); + print "\n);\n"; + } else { + print "SELECT col_not_null(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote($table) . ",\n"; + print "\t" . $dbh->quote($column) . ",\n"; + print "\t" . $dbh->quote("Column $schema.$table.$column is not nullable"); + print "\n);\n"; + } + if (defined $col_default) { + my $fixme = ''; + if ($col_type eq 'interval') { + # FIXME - ERROR: invalid input syntax for type interval: "'1 day'::interval" + $fixme = '-- FIXME type 1 -- '; + } elsif ($col_type eq 'time without time zone') { + # FIXME - ERROR: invalid input syntax for type time: "'17:00:00'::time without time zone" + $fixme = '-- FIXME type 2 -- '; + } elsif ($col_default =~ 'org_unit_custom_tree_purpose') { + # FIXME - ERROR: invalid input value for enum actor.org_unit_custom_tree_purpose: "'opac'::actor.org_unit_custom_tree_purpose" + $fixme = '-- FIXME type 3 -- '; + } elsif ($col_type eq 'integer' && $col_default =~ '\(-?\d+\)') { + # FIXME - ERROR: invalid input syntax for integer: "(-1)" + $fixme = '-- FIXME type 4 -- '; + } elsif ($col_type_original eq 'USER-DEFINED') { + # FIXME - ERROR: Unexpected end of string + $fixme = '-- FIXME type 5 -- '; + } + # I would love to SELECT todo past these, but they cause hard failures + print $fixme . "SELECT col_default_is(\n"; + print $fixme . "\t" . $dbh->quote($schema) . ",\n"; + print $fixme . "\t" . $dbh->quote($table) . ",\n"; + print $fixme . "\t" . $dbh->quote($column) . ",\n"; + print $fixme . "\t" . $dbh->quote($col_default) . ",\n"; + print $fixme . "\t" . $dbh->quote("Column $schema.$table.$column has default value: $col_default"); + print "\n$fixme);\n"; + } else { + print "SELECT col_hasnt_default(\n"; + print "\t" . $dbh->quote($schema) . ",\n"; + print "\t" . $dbh->quote($table) . ",\n"; + print "\t" . $dbh->quote($column) . ",\n"; + print "\t" . $dbh->quote("Column $schema.$table.$column has no default value"); + print "\n);\n"; + } + } +} + -- 2.11.0