X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FPostgreSQL.pm;h=8c0ec5d6d1ea6298368b5617279e36ddfe8b8c4b;hb=124b192cb3e01a3fe6441874c29918609d24de65;hp=f711b4f8094a8ec6b809c7831b2089d22b684eb1;hpb=8d11f4cb2c75adafc4686ee3196331675c554162;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm index f711b4f..8c0ec5d 100644 --- a/lib/SQL/Translator/Producer/PostgreSQL.pm +++ b/lib/SQL/Translator/Producer/PostgreSQL.pm @@ -1,9 +1,7 @@ package SQL::Translator::Producer::PostgreSQL; # ------------------------------------------------------------------- -# $Id: PostgreSQL.pm,v 1.29 2007-06-04 04:01:14 mwz444 Exp $ -# ------------------------------------------------------------------- -# Copyright (C) 2002-4 SQLFairy Authors +# Copyright (C) 2002-2009 SQLFairy Authors # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License as @@ -39,7 +37,7 @@ producer. use strict; use warnings; use vars qw[ $DEBUG $WARN $VERSION %used_names ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.29 $ =~ /(\d+)\.(\d+)/; +$VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; use base qw(SQL::Translator::Producer); @@ -192,8 +190,8 @@ sub produce { my $qf = ''; $qf = '"' if ($translator->quote_field_names); - my $output; - $output .= header_comment unless ($no_comments); + my @output; + push @output, header_comment unless ($no_comments); my (@table_defs, @fks); for my $table ( $schema->get_tables ) { @@ -218,10 +216,10 @@ sub produce { }); } - $output = join(";\n\n", @table_defs) . ";\n\n"; + push @output, map { "$_;\n\n" } @table_defs; if ( @fks ) { - $output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments; - $output .= join( ";\n\n", @fks ) . ";\n"; + push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments; + push @output, map { "$_;\n\n" } @fks; } if ( $WARN ) { @@ -237,7 +235,9 @@ sub produce { } } - return $output; + return wantarray + ? @output + : join ('', @output); } # ------------------------------------------------------------------- @@ -329,7 +329,6 @@ sub create_table my $postgres_version = $options->{postgres_version} || 0; my $table_name = $table->name or next; - $table_name = mk_name( $table_name, '', undef, 1 ); my ( $fql_tbl_name ) = ( $table_name =~ s/\W(.*)$// ) ? $1 : q{}; my $table_name_ur = $qt ? $table_name : $fql_tbl_name ? join('.', $table_name, unreserve($fql_tbl_name)) @@ -405,14 +404,14 @@ sub create_table if ($add_drop_table) { if ($postgres_version >= 8.2) { $create_statement .= qq[DROP TABLE IF EXISTS $qt$table_name_ur$qt CASCADE;\n]; - $create_statement .= join ("\n", @type_drops) . "\n" - if $postgres_version >= 8.3; + $create_statement .= join (";\n", @type_drops) . ";\n" + if $postgres_version >= 8.3 && scalar @type_drops; } else { $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n]; } } - $create_statement .= join("\n", @type_defs) . "\n" - if $postgres_version >= 8.3; + $create_statement .= join(";\n", @type_defs) . ";\n" + if $postgres_version >= 8.3 && scalar @type_defs; $create_statement .= qq[CREATE ${temporary}TABLE $qt$table_name_ur$qt (\n]. join( ",\n", map { " $_" } @field_defs, @constraint_defs ). "\n)" @@ -449,7 +448,7 @@ sub create_view { } if ( my $sql = $view->sql ) { - $create .= " AS (\n ${sql}\n )"; + $create .= " AS\n ${sql}\n"; } if ( $extra->{check_option} ) { @@ -476,9 +475,7 @@ sub create_view { my $type_drops = $options->{type_drops} || []; $field_name_scope{$table_name} ||= {}; - my $field_name = mk_name( - $field->name, '', $field_name_scope{$table_name}, 1 - ); + my $field_name = $field->name; my $field_name_ur = $qf ? $field_name : unreserve($field_name, $table_name ); $field->name($field_name_ur); my $field_comments = $field->comments @@ -683,7 +680,7 @@ sub convert_datatype $data_type; } - if ( $data_type =~ /timestamp/i ) { + if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) { if ( defined $size[0] && $size[0] > 6 ) { $size[0] = 6; } @@ -714,12 +711,13 @@ sub convert_datatype } } - if ( defined $size[0] && $size[0] > 0 ) { - $data_type .= '(' . join( ',', @size ) . ')'; - } - elsif (defined $size[0] && $data_type eq 'timestamp' ) { - $data_type .= '(' . join( ',', @size ) . ')'; + if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) { + $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/; + $data_type .= $2 if(defined $2); + } elsif ( defined $size[0] && $size[0] > 0 ) { + $data_type .= '(' . join( ',', @size ) . ')'; } + return $data_type; @@ -739,6 +737,12 @@ sub alter_field $to_field->name) if(!$to_field->is_nullable and $from_field->is_nullable); + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL', + $to_field->table->name, + $to_field->name) + if ( !$from_field->is_nullable and $to_field->is_nullable ); + + my $from_dt = convert_datatype($from_field); my $to_dt = convert_datatype($to_field); push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s', @@ -753,13 +757,33 @@ sub alter_field my $old_default = $from_field->default_value; my $new_default = $to_field->default_value; + my $default_value = $to_field->default_value; + + # fixes bug where output like this was created: + # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped; + if(ref $default_value eq "SCALAR" ) { + $default_value = $$default_value; + } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) { + $default_value =~ s/'/''/xsmg; + $default_value = q(') . $default_value . q('); + } + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s', $to_field->table->name, $to_field->name, - $to_field->default_value) + $default_value) if ( defined $new_default && (!defined $old_default || $old_default ne $new_default) ); + # fixes bug where removing the DEFAULT statement of a column + # would result in no change + + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT', + $to_field->table->name, + $to_field->name) + if ( !defined $new_default && defined $old_default ); + + return wantarray ? @out : join("\n", @out); } @@ -838,9 +862,16 @@ sub alter_drop_constraint { sub alter_create_constraint { my ($index, $options) = @_; my $qt = $options->{quote_table_names} || ''; - return $index->type eq FOREIGN_KEY ? join(q{}, @{create_constraint(@_)}) + my ($defs, $fks) = create_constraint(@_); + + # return if there are no constraint definitions so we don't run + # into output like this: + # ALTER TABLE users ADD ; + + return unless(@{$defs} || @{$fks}); + return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks}) : join( ' ', 'ALTER TABLE', $qt.$index->table->name.$qt, - 'ADD', join(q{}, map { @{$_} } create_constraint(@_)) + 'ADD', join(q{}, @{$defs}, @{$fks}) ); }