X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FPostgreSQL.pm;h=d778af4dcf099b71bef906574d516eedb923ea01;hb=4ab3763d2ad756c236b757306989cafa08e7f35e;hp=ac6863480f2246eee8f576843428f2153e5bfa5d;hpb=a25ac5d2649e414439ab60a075297361f2e5819d;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm index ac68634..d778af4 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,9 +37,10 @@ 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); use SQL::Translator::Schema::Constants; use SQL::Translator::Utils qw(debug header_comment); use Data::Dumper; @@ -217,10 +216,10 @@ sub produce { }); } - $output = join("\n\n", @table_defs); + $output = join(";\n\n", @table_defs) . ";\n\n"; if ( @fks ) { $output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments; - $output .= join( "\n\n", @fks ) . "\n"; + $output .= join( ";\n\n", @fks ) . ";\n"; } if ( $WARN ) { @@ -327,9 +326,12 @@ sub create_table my $add_drop_table = $options->{add_drop_table} || 0; my $postgres_version = $options->{postgres_version} || 0; - my $table_name = $table->name or next; - $table_name = mk_name( $table_name, '', undef, 1 ); - my $table_name_ur = $qt ? $table_name : unreserve($table_name); + 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)) + : unreserve($table_name); $table->name($table_name_ur); # print STDERR "$table_name table_name\n"; @@ -389,6 +391,13 @@ sub create_table push @fks, @$fks; } + + my $temporary = ""; + + if(exists $table->{extra}{temporary}) { + $temporary = $table->{extra}{temporary} ? "TEMPORARY " : ""; + } + my $create_statement; $create_statement = join("\n", @comments); if ($add_drop_table) { @@ -402,13 +411,14 @@ sub create_table } $create_statement .= join("\n", @type_defs) . "\n" if $postgres_version >= 8.3; - $create_statement .= qq[CREATE TABLE $qt$table_name_ur$qt (\n]. + $create_statement .= qq[CREATE ${temporary}TABLE $qt$table_name_ur$qt (\n]. join( ",\n", map { " $_" } @field_defs, @constraint_defs ). - "\n);" + "\n)" ; + $create_statement .= @index_defs ? ';' : q{}; + $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} ) + . join(";\n", @index_defs); - $create_statement .= "\n" . join("\n", @index_defs) . "\n"; - return $create_statement, \@fks; } @@ -444,7 +454,6 @@ sub create_view { $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION'; } - $create .= ";\n\n"; return $create; } @@ -485,32 +494,30 @@ sub create_view { my $list = $extra{'list'} || []; # todo deal with embedded quotes my $commalist = join( ', ', map { qq['$_'] } @$list ); - my $seq_name; if ($postgres_version >= 8.3 && $field->data_type eq 'enum') { my $type_name = $field->table->name . '_' . $field->name . '_type'; $field_def .= ' '. $type_name; - push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist);"; - push @$type_drops, "DROP TYPE IF EXISTS $type_name;"; + push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist)"; + push @$type_drops, "DROP TYPE IF EXISTS $type_name"; } else { $field_def .= ' '. convert_datatype($field); } # - # Default value -- disallow for timestamps + # Default value # -# my $default = $data_type =~ /(timestamp|date)/i -# ? undef : $field->default_value; my $default = $field->default_value; if ( defined $default ) { - my $qd = "'"; - $qd = '' if ($default eq 'now()' || - $default eq 'CURRENT_TIMESTAMP'); - $field_def .= sprintf( ' DEFAULT %s', - ( $field->is_auto_increment && $seq_name ) - ? qq[nextval('"$seq_name"'::text)] : - ( $default =~ m/null/i ) ? 'NULL' : "$qd$default$qd" - ); + SQL::Translator::Producer->_apply_default_value( + \$field_def, + $default, + [ + 'NULL' => \'NULL', + 'now()' => 'now()', + 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP', + ], + ); } # @@ -545,7 +552,7 @@ sub create_view { $index->fields; next unless @fields; - my $def_start = qq[Constraint "$name" ]; + my $def_start = qq[CONSTRAINT "$name" ]; if ( $type eq PRIMARY_KEY ) { push @constraint_defs, "${def_start}PRIMARY KEY ". '(' .$qf . join( $qf. ', '.$qf, @fields ) . $qf . ')'; @@ -558,7 +565,7 @@ sub create_view { $index_def = "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name}${qt} (". join( ', ', map { qq[$qf$_$qf] } @fields ). - ');' + ')' ; } else { @@ -594,7 +601,7 @@ sub create_view { $c->reference_fields; next if !@fields && $c->type ne CHECK_C; - my $def_start = $name ? qq[Constraint "$name" ] : ''; + my $def_start = $name ? qq[CONSTRAINT "$name" ] : ''; if ( $c->type eq PRIMARY_KEY ) { push @constraint_defs, "${def_start}PRIMARY KEY ". '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')'; @@ -634,7 +641,7 @@ sub create_view { $def .= ' DEFERRABLE'; } - push @fks, "$def;"; + push @fks, "$def"; } return \@constraint_defs, \@fks; @@ -652,7 +659,7 @@ sub convert_datatype # $len = ($len < length($_)) ? length($_) : $len for (@$list); # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' ); # push @$constraint_defs, -# qq[Constraint "$chk_name" CHECK ($qf$field_name$qf ]. +# qq[CONSTRAINT "$chk_name" CHECK ($qf$field_name$qf ]. # qq[IN ($commalist))]; $data_type = 'character varying'; } @@ -725,39 +732,42 @@ sub alter_field if($from_field->table->name ne $to_field->table->name); my @out; - push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL;', + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL', $to_field->table->name, $to_field->name) if(!$to_field->is_nullable and $from_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;', + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s', $to_field->table->name, $to_field->name, $to_dt) if($to_dt ne $from_dt); - push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s;', + push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s', $to_field->table->name, $from_field->name, $to_field->name) if($from_field->name ne $to_field->name); - push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;', + my $old_default = $from_field->default_value; + my $new_default = $to_field->default_value; + push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s', $to_field->table->name, $to_field->name, - $to_field->default_value) - if(defined $to_field->default_value && - $from_field->default_value ne $to_field->default_value); + $to_field->default_value) + if ( defined $new_default && + (!defined $old_default || $old_default ne $new_default) ); return wantarray ? @out : join("\n", @out); - } +sub rename_field { alter_field(@_) } + sub add_field { my ($new_field) = @_; - my $out = sprintf('ALTER TABLE %s ADD COLUMN %s;', + my $out = sprintf('ALTER TABLE %s ADD COLUMN %s', $new_field->table->name, create_field($new_field)); return $out; @@ -768,13 +778,76 @@ sub drop_field { my ($old_field) = @_; - my $out = sprintf('ALTER TABLE %s DROP COLUMN %s;', + my $out = sprintf('ALTER TABLE %s DROP COLUMN %s', $old_field->table->name, $old_field->name); return $out; } +sub alter_table { + my ($to_table, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $out = sprintf('ALTER TABLE %s %s', + $qt . $to_table->name . $qt, + $options->{alter_table_action}); + return $out; +} + +sub rename_table { + my ($old_table, $new_table, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + $options->{alter_table_action} = "RENAME TO $qt$new_table$qt"; + return alter_table($old_table, $options); +} + +sub alter_create_index { + my ($index, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $qf = $options->{quote_field_names} || ''; + my ($idef, $constraints) = create_index($index, { + quote_field_names => $qf, + quote_table_names => $qt, + table_name => $index->table->name, + }); + return $index->type eq NORMAL ? $idef + : sprintf('ALTER TABLE %s ADD %s', + $qt . $index->table->name . $qt, + join(q{}, @$constraints) + ); +} + +sub alter_drop_index { + my ($index, $options) = @_; + my $index_name = $index->name; + return "DROP INDEX $index_name"; +} + +sub alter_drop_constraint { + my ($c, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $qc = $options->{quote_field_names} || ''; + my $out = sprintf('ALTER TABLE %s DROP CONSTRAINT %s', + $qt . $c->table->name . $qt, + $qc . $c->name . $qc ); + return $out; +} + +sub alter_create_constraint { + my ($index, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + return $index->type eq FOREIGN_KEY ? join(q{}, @{create_constraint(@_)}) + : join( ' ', 'ALTER TABLE', $qt.$index->table->name.$qt, + 'ADD', join(q{}, map { @{$_} } create_constraint(@_)) + ); +} + +sub drop_table { + my ($table, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + return "DROP TABLE $qt$table$qt CASCADE"; +} + 1; # -------------------------------------------------------------------