1 package SQL::Translator::Producer::PostgreSQL;
5 SQL::Translator::Producer::PostgreSQL - PostgreSQL producer for SQL::Translator
9 my $t = SQL::Translator->new( parser => '...', producer => 'PostgreSQL' );
14 Creates a DDL suitable for PostgreSQL. Very heavily based on the Oracle
17 Now handles PostGIS Geometry and Geography data types on table definitions.
18 Does not yet support PostGIS Views.
24 our ( $DEBUG, $WARN );
25 our $VERSION = '1.59';
26 $DEBUG = 0 unless defined $DEBUG;
28 use base qw(SQL::Translator::Producer);
29 use SQL::Translator::Schema::Constants;
30 use SQL::Translator::Utils qw(debug header_comment parse_dbms_version batch_alter_table_statements normalize_quote_options);
31 use SQL::Translator::Generator::DDL::PostgreSQL;
35 my ($quoting_generator, $nonquoting_generator);
38 return $options->{generator} if exists $options->{generator};
40 return normalize_quote_options($options)
41 ? $quoting_generator ||= SQL::Translator::Generator::DDL::PostgreSQL->new
42 : $nonquoting_generator ||= SQL::Translator::Generator::DDL::PostgreSQL->new(
48 my ( %translate, %index_name );
58 double => 'double precision',
61 mediumint => 'integer',
62 smallint => 'smallint',
63 tinyint => 'smallint',
65 varchar => 'character varying',
72 mediumblob => 'bytea',
74 enum => 'character varying',
75 set => 'character varying',
77 datetime => 'timestamp',
79 timestamp => 'timestamp',
87 varchar2 => 'character varying',
97 varchar => 'character varying',
98 datetime => 'timestamp',
102 tinyint => 'smallint',
107 my %reserved = map { $_, 1 } qw[
108 ALL ANALYSE ANALYZE AND ANY AS ASC
110 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
111 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
112 DEFAULT DEFERRABLE DESC DISTINCT DO
114 FALSE FOR FOREIGN FREEZE FROM FULL
116 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
117 JOIN LEADING LEFT LIKE LIMIT
118 NATURAL NEW NOT NOTNULL NULL
119 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
120 PRIMARY PUBLIC REFERENCES RIGHT
121 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
122 UNION UNIQUE USER USING VERBOSE WHEN WHERE
125 # my $max_id_length = 62;
126 my %used_identifiers = ();
132 =head1 PostgreSQL Create Table Syntax
134 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
135 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
136 | table_constraint } [, ... ]
138 [ INHERITS ( parent_table [, ... ] ) ]
139 [ WITH OIDS | WITHOUT OIDS ]
141 where column_constraint is:
143 [ CONSTRAINT constraint_name ]
144 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
146 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
147 [ ON DELETE action ] [ ON UPDATE action ] }
148 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
150 and table_constraint is:
152 [ CONSTRAINT constraint_name ]
153 { UNIQUE ( column_name [, ... ] ) |
154 PRIMARY KEY ( column_name [, ... ] ) |
155 CHECK ( expression ) |
156 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
157 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
158 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
160 =head1 Create Index Syntax
162 CREATE [ UNIQUE ] INDEX index_name ON table
163 [ USING acc_method ] ( column [ ops_name ] [, ...] )
165 CREATE [ UNIQUE ] INDEX index_name ON table
166 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
172 my $translator = shift;
173 local $DEBUG = $translator->debug;
174 local $WARN = $translator->show_warnings;
175 my $no_comments = $translator->no_comments;
176 my $add_drop_table = $translator->add_drop_table;
177 my $schema = $translator->schema;
178 my $pargs = $translator->producer_args;
179 my $postgres_version = parse_dbms_version(
180 $pargs->{postgres_version}, 'perl'
183 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
186 push @output, header_comment unless ($no_comments);
188 my (@table_defs, @fks);
190 for my $table ( $schema->get_tables ) {
192 my ($table_def, $fks) = create_table($table, {
193 generator => $generator,
194 no_comments => $no_comments,
195 postgres_version => $postgres_version,
196 add_drop_table => $add_drop_table,
197 type_defs => \%type_defs,
200 push @table_defs, $table_def;
204 for my $view ( $schema->get_views ) {
205 push @table_defs, create_view($view, {
206 postgres_version => $postgres_version,
207 add_drop_view => $add_drop_table,
208 generator => $generator,
209 no_comments => $no_comments,
213 for my $trigger ( $schema->get_triggers ) {
214 push @table_defs, create_trigger( $trigger, {
215 add_drop_trigger => $add_drop_table,
216 generator => $generator,
217 no_comments => $no_comments,
221 push @output, map { "$_;\n\n" } values %type_defs;
222 push @output, map { "$_;\n\n" } @table_defs;
224 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
225 push @output, map { "$_;\n\n" } @fks;
230 warn "Truncated " . keys( %truncated ) . " names:\n";
231 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
237 : join ('', @output);
241 my $basename = shift || '';
242 my $type = shift || '';
243 my $scope = shift || '';
244 my $critical = shift || '';
245 my $basename_orig = $basename;
246 # my $max_id_length = 62;
248 ? $max_id_length - (length($type) + 1)
250 $basename = substr( $basename, 0, $max_name )
251 if length( $basename ) > $max_name;
252 my $name = $type ? "${type}_$basename" : $basename;
254 if ( $basename ne $basename_orig and $critical ) {
255 my $show_type = $type ? "+'$type'" : "";
256 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
257 "character limit to make '$name'\n" if $WARN;
258 $truncated{ $basename_orig } = $name;
261 $scope ||= \%global_names;
262 if ( my $prev = $scope->{ $name } ) {
263 my $name_orig = $name;
264 $name .= sprintf( "%02d", ++$prev );
265 substr($name, $max_id_length - 3) = "00"
266 if length( $name ) > $max_id_length;
268 warn "The name '$name_orig' has been changed to ",
269 "'$name' to make it unique.\n" if $WARN;
271 $scope->{ $name_orig }++;
281 return 1 if $field->data_type eq 'geometry';
287 return 1 if $field->data_type eq 'geography';
292 my ($table, $options) = @_;
294 my $generator = _generator($options);
295 my $no_comments = $options->{no_comments} || 0;
296 my $add_drop_table = $options->{add_drop_table} || 0;
297 my $postgres_version = $options->{postgres_version} || 0;
298 my $type_defs = $options->{type_defs} || {};
300 my $table_name = $table->name or next;
301 my $table_name_qt = $generator->quote($table_name);
303 # print STDERR "$table_name table_name\n";
304 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @fks );
306 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
308 if ( $table->comments and !$no_comments ){
309 my $c = "-- Comments: \n-- ";
310 $c .= join "\n-- ", $table->comments;
318 my %field_name_scope;
319 for my $field ( $table->get_fields ) {
320 push @field_defs, create_field($field, {
321 generator => $generator,
322 postgres_version => $postgres_version,
323 type_defs => $type_defs,
324 constraint_defs => \@constraint_defs,
332 # my $idx_name_default;
333 for my $index ( $table->get_indices ) {
334 my ($idef, $constraints) = create_index($index, {
335 generator => $generator,
337 $idef and push @index_defs, $idef;
338 push @constraint_defs, @$constraints;
345 for my $c ( $table->get_constraints ) {
346 my ($cdefs, $fks) = create_constraint($c, {
347 generator => $generator,
349 push @constraint_defs, @$cdefs;
356 if(exists $table->extra->{temporary}) {
357 $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
360 my $create_statement;
361 $create_statement = join("\n", @comments);
362 if ($add_drop_table) {
363 if ($postgres_version >= 8.002) {
364 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
366 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
369 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
370 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
373 $create_statement .= @index_defs ? ';' : q{};
374 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
375 . join(";\n", @index_defs);
380 if(grep { is_geometry($_) } $table->get_fields){
381 $create_statement .= ";";
382 my @geometry_columns;
383 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
384 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
385 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
388 return $create_statement, \@fks;
392 my ($view, $options) = @_;
393 my $generator = _generator($options);
394 my $postgres_version = $options->{postgres_version} || 0;
395 my $add_drop_view = $options->{add_drop_view};
397 my $view_name = $view->name;
398 debug("PKG: Looking at view '${view_name}'\n");
401 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
402 unless $options->{no_comments};
403 if ($add_drop_view) {
404 if ($postgres_version >= 8.002) {
405 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
407 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
412 my $extra = $view->extra;
413 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
414 $create .= " VIEW " . $generator->quote($view_name);
416 if ( my @fields = $view->fields ) {
417 my $field_list = join ', ', map { $generator->quote($_) } @fields;
418 $create .= " ( ${field_list} )";
421 if ( my $sql = $view->sql ) {
422 $create .= " AS\n ${sql}\n";
425 if ( $extra->{check_option} ) {
426 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
434 my %field_name_scope;
438 my ($field, $options) = @_;
440 my $generator = _generator($options);
441 my $table_name = $field->table->name;
442 my $constraint_defs = $options->{constraint_defs} || [];
443 my $postgres_version = $options->{postgres_version} || 0;
444 my $type_defs = $options->{type_defs} || {};
446 $field_name_scope{$table_name} ||= {};
447 my $field_name = $field->name;
448 my $field_comments = $field->comments
449 ? "-- " . $field->comments . "\n "
452 my $field_def = $field_comments . $generator->quote($field_name);
457 my @size = $field->size;
458 my $data_type = lc $field->data_type;
459 my %extra = $field->extra;
460 my $list = $extra{'list'} || [];
461 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
463 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
464 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
465 $field_def .= ' '. $type_name;
466 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
467 "CREATE TYPE $type_name AS ENUM ($commalist)";
468 if (! exists $type_defs->{$type_name} ) {
469 $type_defs->{$type_name} = $new_type_def;
470 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
471 die "Attempted to redefine type name '$type_name' as a different type.\n";
474 $field_def .= ' '. convert_datatype($field);
480 __PACKAGE__->_apply_default_value(
486 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
491 # Not null constraint
493 $field_def .= ' NOT NULL' unless $field->is_nullable;
496 # Geometry constraints
498 if(is_geometry($field)){
499 foreach ( create_geometry_constraints($field) ) {
500 my ($cdefs, $fks) = create_constraint($_, {
501 generator => $generator,
503 push @$constraint_defs, @$cdefs;
512 sub create_geometry_constraints{
516 push @constraints, SQL::Translator::Schema::Constraint->new(
517 name => "enforce_dims_".$field->name,
518 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
519 table => $field->table,
523 push @constraints, SQL::Translator::Schema::Constraint->new(
524 name => "enforce_srid_".$field->name,
525 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
526 table => $field->table,
529 push @constraints, SQL::Translator::Schema::Constraint->new(
530 name => "enforce_geotype_".$field->name,
531 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
532 table => $field->table,
541 my ($index, $options) = @_;
543 my $generator = _generator($options);
544 my $table_name = $index->table->name;
546 my ($index_def, @constraint_defs);
550 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
552 my $type = $index->type || NORMAL;
553 my @fields = $index->fields;
554 return unless @fields;
556 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
557 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
558 if ( $type eq PRIMARY_KEY ) {
559 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
561 elsif ( $type eq UNIQUE ) {
562 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
564 elsif ( $type eq NORMAL ) {
566 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' . $field_names
570 warn "Unknown index type ($type) on table $table_name.\n"
574 return $index_def, \@constraint_defs;
577 sub create_constraint
579 my ($c, $options) = @_;
581 my $generator = _generator($options);
582 my $table_name = $c->table->name;
583 my (@constraint_defs, @fks);
585 my $name = $c->name || '';
587 my @fields = grep { defined } $c->fields;
589 my @rfields = grep { defined } $c->reference_fields;
591 next if !@fields && $c->type ne CHECK_C;
592 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
593 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
594 if ( $c->type eq PRIMARY_KEY ) {
595 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
597 elsif ( $c->type eq UNIQUE ) {
598 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
600 elsif ( $c->type eq CHECK_C ) {
601 my $expression = $c->expression;
602 push @constraint_defs, "${def_start}CHECK ($expression)";
604 elsif ( $c->type eq FOREIGN_KEY ) {
605 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
606 . "\n REFERENCES " . $generator->quote($c->reference_table);
609 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
612 if ( $c->match_type ) {
614 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
617 if ( $c->on_delete ) {
618 $def .= ' ON DELETE '. $c->on_delete;
621 if ( $c->on_update ) {
622 $def .= ' ON UPDATE '. $c->on_update;
625 if ( $c->deferrable ) {
626 $def .= ' DEFERRABLE';
632 return \@constraint_defs, \@fks;
636 my ($trigger,$options) = @_;
637 my $generator = _generator($options);
641 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
642 if $options->{add_drop_trigger};
644 my $scope = $trigger->scope || '';
645 $scope = " FOR EACH $scope" if $scope;
647 push @statements, sprintf(
648 'CREATE TRIGGER %s %s %s ON %s%s %s',
649 $generator->quote($trigger->name),
650 $trigger->perform_action_when,
651 join( ' OR ', @{ $trigger->database_events } ),
652 $generator->quote($trigger->on_table),
664 my @size = $field->size;
665 my $data_type = lc $field->data_type;
666 my $array = $data_type =~ s/\[\]$//;
668 if ( $data_type eq 'enum' ) {
670 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
671 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
672 # push @$constraint_defs,
673 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
674 # qq[IN ($commalist))];
675 $data_type = 'character varying';
677 elsif ( $data_type eq 'set' ) {
678 $data_type = 'character varying';
680 elsif ( $field->is_auto_increment ) {
681 if ( defined $size[0] && $size[0] > 11 ) {
682 $data_type = 'bigserial';
685 $data_type = 'serial';
690 $data_type = defined $translate{ $data_type } ?
691 $translate{ $data_type } :
695 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
696 if ( defined $size[0] && $size[0] > 6 ) {
701 if ( $data_type eq 'integer' ) {
702 if ( defined $size[0] && $size[0] > 0) {
703 if ( $size[0] > 10 ) {
704 $data_type = 'bigint';
706 elsif ( $size[0] < 5 ) {
707 $data_type = 'smallint';
710 $data_type = 'integer';
714 $data_type = 'integer';
718 my $type_with_size = join('|',
719 'bit', 'varbit', 'character', 'bit varying', 'character varying',
720 'time', 'timestamp', 'interval', 'numeric', 'float'
723 if ( $data_type !~ /$type_with_size/ ) {
727 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
728 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
729 $data_type .= $2 if(defined $2);
730 } elsif ( defined $size[0] && $size[0] > 0 ) {
731 $data_type .= '(' . join( ',', @size ) . ')';
741 if($data_type eq 'geography'){
742 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
751 my ($from_field, $to_field) = @_;
753 die "Can't alter field in another table"
754 if($from_field->table->name ne $to_field->table->name);
758 # drop geometry column and constraints
759 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
760 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
762 # it's necessary to start with rename column cause this would affect
763 # all of the following statements which would be broken if do the
765 # BUT: drop geometry is done before the rename, cause it work's on the
766 # $from_field directly
767 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
768 $to_field->table->name,
770 $to_field->name) if($from_field->name ne $to_field->name);
772 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
773 $to_field->table->name,
774 $to_field->name) if(!$to_field->is_nullable and
775 $from_field->is_nullable);
777 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
778 $to_field->table->name,
780 if ( !$from_field->is_nullable and $to_field->is_nullable );
783 my $from_dt = convert_datatype($from_field);
784 my $to_dt = convert_datatype($to_field);
785 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
786 $to_field->table->name,
788 $to_dt) if($to_dt ne $from_dt);
790 my $old_default = $from_field->default_value;
791 my $new_default = $to_field->default_value;
792 my $default_value = $to_field->default_value;
794 # fixes bug where output like this was created:
795 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
796 if(ref $default_value eq "SCALAR" ) {
797 $default_value = $$default_value;
798 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
799 $default_value = __PACKAGE__->_quote_string($default_value);
802 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
803 $to_field->table->name,
806 if ( defined $new_default &&
807 (!defined $old_default || $old_default ne $new_default) );
809 # fixes bug where removing the DEFAULT statement of a column
810 # would result in no change
812 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
813 $to_field->table->name,
815 if ( !defined $new_default && defined $old_default );
817 # add geometry column and constraints
818 push @out, add_geometry_column($to_field) if is_geometry($to_field);
819 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
821 return wantarray ? @out : join(";\n", @out);
824 sub rename_field { alter_field(@_) }
828 my ($new_field) = @_;
830 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
831 $new_field->table->name,
832 create_field($new_field));
833 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
834 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
841 my ($old_field, $options) = @_;
843 my $generator = _generator($options);
845 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
846 $generator->quote($old_field->table->name),
847 $generator->quote($old_field->name));
848 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
852 sub add_geometry_column{
853 my ($field,$options) = @_;
855 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
857 $field->table->schema->name,
858 $options->{table} ? $options->{table} : $field->table->name,
860 $field->extra->{dimensions},
861 $field->extra->{srid},
862 $field->extra->{geometry_type});
866 sub drop_geometry_column
870 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
871 $field->table->schema->name,
877 sub add_geometry_constraints{
880 my @constraints = create_geometry_constraints($field);
882 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
887 sub drop_geometry_constraints{
890 my @constraints = create_geometry_constraints($field);
892 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
898 my ($to_table, $options) = @_;
899 my $generator = _generator($options);
900 my $out = sprintf('ALTER TABLE %s %s',
901 $generator->quote($to_table->name),
902 $options->{alter_table_action});
903 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
908 my ($old_table, $new_table, $options) = @_;
909 my $generator = _generator($options);
910 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
912 my @geometry_changes;
913 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
914 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
916 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
918 return alter_table($old_table, $options);
921 sub alter_create_index {
922 my ($index, $options) = @_;
923 my $generator = _generator($options);
924 my ($idef, $constraints) = create_index($index, {
925 generator => $generator,
927 return $index->type eq NORMAL ? $idef
928 : sprintf('ALTER TABLE %s ADD %s',
929 $generator->quote($index->table->name),
930 join(q{}, @$constraints)
934 sub alter_drop_index {
935 my ($index, $options) = @_;
936 my $index_name = $index->name;
937 return "DROP INDEX $index_name";
940 sub alter_drop_constraint {
941 my ($c, $options) = @_;
942 my $generator = _generator($options);
944 # attention: Postgres has a very special naming structure for naming
945 # foreign keys and primary keys. It names them using the name of the
946 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
949 # Already has a name, just quote it
950 $c_name = $generator->quote($c->name);
951 } elsif ( $c->type eq FOREIGN_KEY ) {
952 # Doesn't have a name, and is foreign key, append '_fkey'
953 $c_name = $generator->quote($c->table->name . '_' .
954 ($c->fields)[0] . '_fkey');
955 } elsif ( $c->type eq PRIMARY_KEY ) {
956 # Doesn't have a name, and is primary key, append '_pkey'
957 $c_name = $generator->quote($c->table->name . '_pkey');
961 'ALTER TABLE %s DROP CONSTRAINT %s',
962 $generator->quote($c->table->name), $c_name
966 sub alter_create_constraint {
967 my ($index, $options) = @_;
968 my $generator = _generator($options);
969 my ($defs, $fks) = create_constraint(@_);
971 # return if there are no constraint definitions so we don't run
972 # into output like this:
973 # ALTER TABLE users ADD ;
975 return unless(@{$defs} || @{$fks});
976 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
977 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
978 'ADD', join(q{}, @{$defs}, @{$fks})
983 my ($table, $options) = @_;
984 my $generator = _generator($options);
985 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
987 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
989 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
993 sub batch_alter_table {
994 my ( $table, $diff_hash, $options ) = @_;
996 # as long as we're not renaming the table we don't need to be here
997 if ( @{$diff_hash->{rename_table}} == 0 ) {
998 return batch_alter_table_statements($diff_hash, $options);
1001 # first we need to perform drops which are on old table
1002 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
1003 alter_drop_constraint
1008 # next comes the rename_table
1009 my $old_table = $diff_hash->{rename_table}[0][0];
1010 push @sql, rename_table( $old_table, $table, $options );
1012 # for alter_field (and so also rename_field) we need to make sure old
1013 # field has table name set to new table otherwise calling alter_field dies
1014 $diff_hash->{alter_field} =
1015 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1016 $diff_hash->{rename_field} =
1017 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1019 # now add everything else
1020 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1025 alter_create_constraint
1034 # -------------------------------------------------------------------
1035 # Life is full of misery, loneliness, and suffering --
1036 # and it's all over much too soon.
1038 # -------------------------------------------------------------------
1044 SQL::Translator, SQL::Translator::Producer::Oracle.
1048 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.