X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FPostgreSQL.pm;h=768558731814ebfdf33707281dd1b77a990b9f11;hb=b4414534f4fb5bfa82073f0768d36914340175e9;hp=b1f528149781623c9a9e207eeeb24f5513019d26;hpb=f741008213392307f73bd65e270c42b40a71e282;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm index b1f5281..7685587 100644 --- a/lib/SQL/Translator/Producer/PostgreSQL.pm +++ b/lib/SQL/Translator/Producer/PostgreSQL.pm @@ -246,14 +246,12 @@ sub produce { } } -sub is_geometry -{ - my $field = shift; - return 1 if $field->data_type eq 'geometry'; +sub is_geometry { + my $field = shift; + return 1 if $field->data_type eq 'geometry'; } -sub is_geography -{ +sub is_geography { my $field = shift; return 1 if $field->data_type eq 'geography'; } @@ -271,8 +269,7 @@ sub create_table my $table_name = $table->name or next; my $table_name_qt = $generator->quote($table_name); -# print STDERR "$table_name table_name\n"; - my ( @comments, @field_defs, @index_defs, @sequence_defs, @constraint_defs, @fks ); + my ( @comments, @field_defs, @index_defs, @constraint_defs, @fks ); push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments; @@ -333,16 +330,13 @@ sub create_table $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} ) . join(";\n", @index_defs); - # - # Geometry - # - if(grep { is_geometry($_) } $table->get_fields){ - $create_statement .= ";"; - my @geometry_columns; - foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); } - $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table}; - $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns); - } + # + # Geometry + # + if (my @geometry_columns = grep { is_geometry($_) } $table->get_fields) { + $create_statement .= join(";\n", '', map{ drop_geometry_column($_, $options) } @geometry_columns) if $options->{add_drop_table}; + $create_statement .= join(";\n", '', map{ add_geometry_column($_, $options) } @geometry_columns); + } return $create_statement, \@fks; } @@ -415,13 +409,12 @@ sub create_view { # # Datatype # - my @size = $field->size; my $data_type = lc $field->data_type; my %extra = $field->extra; my $list = $extra{'list'} || []; my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list ); - if ($postgres_version >= 8.003 && $field->data_type eq 'enum') { + if ($postgres_version >= 8.003 && $data_type eq 'enum') { my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type'; $field_def .= ' '. $type_name; my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" . @@ -453,48 +446,47 @@ sub create_view { # $field_def .= ' NOT NULL' unless $field->is_nullable; - # - # Geometry constraints - # - if(is_geometry($field)){ - foreach ( create_geometry_constraints($field) ) { - my ($cdefs, $fks) = create_constraint($_, { - generator => $generator, - }); - push @$constraint_defs, @$cdefs; - push @$fks, @$fks; - } + # + # Geometry constraints + # + if (is_geometry($field)) { + foreach ( create_geometry_constraints($field, $options) ) { + my ($cdefs, $fks) = create_constraint($_, $options); + push @$constraint_defs, @$cdefs; + push @$fks, @$fks; + } } return $field_def; } } -sub create_geometry_constraints{ - my $field = shift; - - my @constraints; - push @constraints, SQL::Translator::Schema::Constraint->new( - name => "enforce_dims_".$field->name, - expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")", - table => $field->table, - type => CHECK_C, - ); - - push @constraints, SQL::Translator::Schema::Constraint->new( - name => "enforce_srid_".$field->name, - expression => "(ST_SRID($field) = ".$field->extra->{srid}.")", - table => $field->table, - type => CHECK_C, - ); - push @constraints, SQL::Translator::Schema::Constraint->new( - name => "enforce_geotype_".$field->name, - expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)", - table => $field->table, - type => CHECK_C, - ); - - return @constraints; +sub create_geometry_constraints { + my ($field, $options) = @_; + + my $fname = _generator($options)->quote($field); + my @constraints; + push @constraints, SQL::Translator::Schema::Constraint->new( + name => "enforce_dims_".$field->name, + expression => "(ST_NDims($fname) = ".$field->extra->{dimensions}.")", + table => $field->table, + type => CHECK_C, + ); + + push @constraints, SQL::Translator::Schema::Constraint->new( + name => "enforce_srid_".$field->name, + expression => "(ST_SRID($fname) = ".$field->extra->{srid}.")", + table => $field->table, + type => CHECK_C, + ); + push @constraints, SQL::Translator::Schema::Constraint->new( + name => "enforce_geotype_".$field->name, + expression => "(GeometryType($fname) = ". __PACKAGE__->_quote_string($field->extra->{geometry_type}) ."::text OR $fname IS NULL)", + table => $field->table, + type => CHECK_C, + ); + + return @constraints; } { @@ -738,8 +730,10 @@ sub alter_field my @out; # drop geometry column and constraints - push @out, drop_geometry_column($from_field) if is_geometry($from_field); - push @out, drop_geometry_constraints($from_field) if is_geometry($from_field); + push @out, + drop_geometry_column($from_field, $options), + drop_geometry_constraints($from_field, $options), + if is_geometry($from_field); # it's necessary to start with rename column cause this would affect # all of the following statements which would be broken if do the @@ -817,8 +811,10 @@ sub alter_field if ( !defined $new_default && defined $old_default ); # add geometry column and constraints - push @out, add_geometry_column($to_field) if is_geometry($to_field); - push @out, add_geometry_constraints($to_field) if is_geometry($to_field); + push @out, + add_geometry_column($to_field, $options), + add_geometry_constraints($to_field, $options), + if is_geometry($to_field); return wantarray ? @out : join(";\n", @out); } @@ -832,8 +828,9 @@ sub add_field my $out = sprintf('ALTER TABLE %s ADD COLUMN %s', _generator($options)->quote($new_field->table->name), create_field($new_field, $options)); - $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field); - $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field); + $out .= ";\n".add_geometry_column($new_field, $options) + . ";\n".add_geometry_constraints($new_field, $options) + if is_geometry($new_field); return $out; } @@ -847,53 +844,54 @@ sub drop_field my $out = sprintf('ALTER TABLE %s DROP COLUMN %s', $generator->quote($old_field->table->name), $generator->quote($old_field->name)); - $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field); + $out .= ";\n".drop_geometry_column($old_field, $options) + if is_geometry($old_field); return $out; } -sub add_geometry_column{ - my ($field,$options) = @_; - - my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')", - '', - $field->table->schema->name, - $options->{table} ? $options->{table} : $field->table->name, - $field->name, - $field->extra->{dimensions}, - $field->extra->{srid}, - $field->extra->{geometry_type}); - return $out; -} +sub add_geometry_column { + my ($field, $options) = @_; -sub drop_geometry_column -{ - my $field = shift; - - my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'", - $field->table->schema->name, - $field->table->name, - $field->name); - return $out; + return sprintf( + "INSERT INTO geometry_columns VALUES (%s,%s,%s,%s,%s,%s,%s)", + map(__PACKAGE__->_quote_string($_), + '', + $field->table->schema->name, + $options->{table} ? $options->{table} : $field->table->name, + $field->name, + $field->extra->{dimensions}, + $field->extra->{srid}, + $field->extra->{geometry_type}, + ), + ); } -sub add_geometry_constraints{ - my $field = shift; +sub drop_geometry_column { + my ($field) = @_; - my @constraints = create_geometry_constraints($field); + return sprintf( + "DELETE FROM geometry_columns WHERE f_table_schema = %s AND f_table_name = %s AND f_geometry_column = %s", + map(__PACKAGE__->_quote_string($_), + $field->table->schema->name, + $field->table->name, + $field->name, + ), + ); +} - my $out = join("\n", map { alter_create_constraint($_); } @constraints); +sub add_geometry_constraints { + my ($field, $options) = @_; - return $out; + return join(";\n", map { alter_create_constraint($_, $options) } + create_geometry_constraints($field, $options)); } -sub drop_geometry_constraints{ - my $field = shift; - - my @constraints = create_geometry_constraints($field); +sub drop_geometry_constraints { + my ($field, $options) = @_; - my $out = join("\n", map { alter_drop_constraint($_); } @constraints); + return join(";\n", map { alter_drop_constraint($_, $options) } + create_geometry_constraints($field, $options)); - return $out; } sub alter_table { @@ -902,7 +900,7 @@ sub alter_table { my $out = sprintf('ALTER TABLE %s %s', $generator->quote($to_table->name), $options->{alter_table_action}); - $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes}; + $out .= ";\n".$options->{geometry_changes} if $options->{geometry_changes}; return $out; } @@ -911,11 +909,12 @@ sub rename_table { my $generator = _generator($options); $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table); - my @geometry_changes; - push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields; - push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields; + my @geometry_changes = map { + drop_geometry_column($_, $options), + add_geometry_column($_, { %{$options}, table => $new_table }), + } grep { is_geometry($_) } $old_table->get_fields; - $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes); + $options->{geometry_changes} = join (";\n",@geometry_changes) if @geometry_changes; return alter_table($old_table, $options); } @@ -923,9 +922,7 @@ sub rename_table { sub alter_create_index { my ($index, $options) = @_; my $generator = _generator($options); - my ($idef, $constraints) = create_index($index, { - generator => $generator, - }); + my ($idef, $constraints) = create_index($index, $options); return $index->type eq NORMAL ? $idef : sprintf('ALTER TABLE %s ADD %s', $generator->quote($index->table->name), @@ -986,7 +983,7 @@ sub drop_table { my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields; - $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops); + $out .= join(";\n", '', @geometry_drops) if @geometry_drops; return $out; }