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;
34 use constant MAX_ID_LENGTH => 62;
37 my ($quoting_generator, $nonquoting_generator);
40 return $options->{generator} if exists $options->{generator};
42 return normalize_quote_options($options)
43 ? $quoting_generator ||= SQL::Translator::Generator::DDL::PostgreSQL->new
44 : $nonquoting_generator ||= SQL::Translator::Generator::DDL::PostgreSQL->new(
58 double => 'double precision',
61 mediumint => 'integer',
62 tinyint => 'smallint',
64 varchar => 'character varying',
70 mediumblob => 'bytea',
72 enum => 'character varying',
73 set => 'character varying',
74 datetime => 'timestamp',
81 varchar2 => 'character varying',
100 =head1 PostgreSQL Create Table Syntax
102 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
103 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
104 | table_constraint } [, ... ]
106 [ INHERITS ( parent_table [, ... ] ) ]
107 [ WITH OIDS | WITHOUT OIDS ]
109 where column_constraint is:
111 [ CONSTRAINT constraint_name ]
112 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
114 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
115 [ ON DELETE action ] [ ON UPDATE action ] }
116 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
118 and table_constraint is:
120 [ CONSTRAINT constraint_name ]
121 { UNIQUE ( column_name [, ... ] ) |
122 PRIMARY KEY ( column_name [, ... ] ) |
123 CHECK ( expression ) |
124 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
125 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
126 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
128 =head1 Create Index Syntax
130 CREATE [ UNIQUE ] INDEX index_name ON table
131 [ USING acc_method ] ( column [ ops_name ] [, ...] )
133 CREATE [ UNIQUE ] INDEX index_name ON table
134 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
140 my $translator = shift;
141 local $DEBUG = $translator->debug;
142 local $WARN = $translator->show_warnings;
143 my $no_comments = $translator->no_comments;
144 my $add_drop_table = $translator->add_drop_table;
145 my $schema = $translator->schema;
146 my $pargs = $translator->producer_args;
147 my $postgres_version = parse_dbms_version(
148 $pargs->{postgres_version}, 'perl'
151 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
154 push @output, header_comment unless ($no_comments);
156 my (@table_defs, @fks);
158 for my $table ( $schema->get_tables ) {
160 my ($table_def, $fks) = create_table($table, {
161 generator => $generator,
162 no_comments => $no_comments,
163 postgres_version => $postgres_version,
164 add_drop_table => $add_drop_table,
165 type_defs => \%type_defs,
168 push @table_defs, $table_def;
172 for my $view ( $schema->get_views ) {
173 push @table_defs, create_view($view, {
174 postgres_version => $postgres_version,
175 add_drop_view => $add_drop_table,
176 generator => $generator,
177 no_comments => $no_comments,
181 for my $trigger ( $schema->get_triggers ) {
182 push @table_defs, create_trigger( $trigger, {
183 add_drop_trigger => $add_drop_table,
184 generator => $generator,
185 no_comments => $no_comments,
189 push @output, map { "$_;\n\n" } values %type_defs;
190 push @output, map { "$_;\n\n" } @table_defs;
192 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
193 push @output, map { "$_;\n\n" } @fks;
198 warn "Truncated " . keys( %truncated ) . " names:\n";
199 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
205 : join ('', @output);
211 my $basename = shift || '';
212 my $type = shift || '';
213 my $scope = shift || '';
214 my $critical = shift || '';
215 my $basename_orig = $basename;
218 ? MAX_ID_LENGTH - (length($type) + 1)
220 $basename = substr( $basename, 0, $max_name )
221 if length( $basename ) > $max_name;
222 my $name = $type ? "${type}_$basename" : $basename;
224 if ( $basename ne $basename_orig and $critical ) {
225 my $show_type = $type ? "+'$type'" : "";
226 warn "Truncating '$basename_orig'$show_type to ", MAX_ID_LENGTH,
227 " character limit to make '$name'\n" if $WARN;
228 $truncated{ $basename_orig } = $name;
231 $scope ||= \%global_names;
232 if ( my $prev = $scope->{ $name } ) {
233 my $name_orig = $name;
234 $name .= sprintf( "%02d", ++$prev );
235 substr($name, MAX_ID_LENGTH - 3) = "00"
236 if length( $name ) > MAX_ID_LENGTH;
238 warn "The name '$name_orig' has been changed to ",
239 "'$name' to make it unique.\n" if $WARN;
241 $scope->{ $name_orig }++;
251 return 1 if $field->data_type eq 'geometry';
256 return 1 if $field->data_type eq 'geography';
261 my ($table, $options) = @_;
263 my $generator = _generator($options);
264 my $no_comments = $options->{no_comments} || 0;
265 my $add_drop_table = $options->{add_drop_table} || 0;
266 my $postgres_version = $options->{postgres_version} || 0;
267 my $type_defs = $options->{type_defs} || {};
269 my $table_name = $table->name or next;
270 my $table_name_qt = $generator->quote($table_name);
272 my ( @comments, @field_defs, @index_defs, @constraint_defs, @fks );
274 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
276 if ( !$no_comments and my $comments = $table->comments ) {
277 $comments =~ s/^/-- /mg;
278 push @comments, "-- Comments:\n$comments\n--\n";
284 for my $field ( $table->get_fields ) {
285 push @field_defs, create_field($field, {
286 generator => $generator,
287 postgres_version => $postgres_version,
288 type_defs => $type_defs,
289 constraint_defs => \@constraint_defs,
296 for my $index ( $table->get_indices ) {
297 my ($idef, $constraints) = create_index($index, {
298 generator => $generator,
300 $idef and push @index_defs, $idef;
301 push @constraint_defs, @$constraints;
307 for my $c ( $table->get_constraints ) {
308 my ($cdefs, $fks) = create_constraint($c, {
309 generator => $generator,
311 push @constraint_defs, @$cdefs;
316 my $create_statement = join("\n", @comments);
317 if ($add_drop_table) {
318 if ($postgres_version >= 8.002) {
319 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
321 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
324 my $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
325 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
326 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
329 $create_statement .= @index_defs ? ';' : q{};
330 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
331 . join(";\n", @index_defs);
336 if (my @geometry_columns = grep { is_geometry($_) } $table->get_fields) {
337 $create_statement .= ";";
338 $create_statement .= join("\n", '', map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
339 $create_statement .= join("\n", '', map{ add_geometry_column($_) } @geometry_columns);
342 return $create_statement, \@fks;
346 my ($view, $options) = @_;
347 my $generator = _generator($options);
348 my $postgres_version = $options->{postgres_version} || 0;
349 my $add_drop_view = $options->{add_drop_view};
351 my $view_name = $view->name;
352 debug("PKG: Looking at view '${view_name}'\n");
355 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
356 unless $options->{no_comments};
357 if ($add_drop_view) {
358 if ($postgres_version >= 8.002) {
359 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
361 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
366 my $extra = $view->extra;
367 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
368 $create .= " VIEW " . $generator->quote($view_name);
370 if ( my @fields = $view->fields ) {
371 my $field_list = join ', ', map { $generator->quote($_) } @fields;
372 $create .= " ( ${field_list} )";
375 if ( my $sql = $view->sql ) {
376 $create .= " AS\n ${sql}\n";
379 if ( $extra->{check_option} ) {
380 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
388 my %field_name_scope;
392 my ($field, $options) = @_;
394 my $generator = _generator($options);
395 my $table_name = $field->table->name;
396 my $constraint_defs = $options->{constraint_defs} || [];
397 my $postgres_version = $options->{postgres_version} || 0;
398 my $type_defs = $options->{type_defs} || {};
400 $field_name_scope{$table_name} ||= {};
401 my $field_name = $field->name;
402 my $field_comments = '';
403 if (my $comments = $field->comments) {
404 $comments =~ s/(?<!\A)^/ -- /mg;
405 $field_comments = "-- $comments\n ";
408 my $field_def = $field_comments . $generator->quote($field_name);
413 my $data_type = lc $field->data_type;
414 my %extra = $field->extra;
415 my $list = $extra{'list'} || [];
416 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
418 if ($postgres_version >= 8.003 && $data_type eq 'enum') {
419 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
420 $field_def .= ' '. $type_name;
421 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
422 "CREATE TYPE $type_name AS ENUM ($commalist)";
423 if (! exists $type_defs->{$type_name} ) {
424 $type_defs->{$type_name} = $new_type_def;
425 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
426 die "Attempted to redefine type name '$type_name' as a different type.\n";
429 $field_def .= ' '. convert_datatype($field);
435 __PACKAGE__->_apply_default_value(
441 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
446 # Not null constraint
448 $field_def .= ' NOT NULL' unless $field->is_nullable;
451 # Geometry constraints
453 if (is_geometry($field)) {
454 foreach ( create_geometry_constraints($field) ) {
455 my ($cdefs, $fks) = create_constraint($_, {
456 generator => $generator,
458 push @$constraint_defs, @$cdefs;
467 sub create_geometry_constraints {
471 push @constraints, SQL::Translator::Schema::Constraint->new(
472 name => "enforce_dims_".$field->name,
473 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
474 table => $field->table,
478 push @constraints, SQL::Translator::Schema::Constraint->new(
479 name => "enforce_srid_".$field->name,
480 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
481 table => $field->table,
484 push @constraints, SQL::Translator::Schema::Constraint->new(
485 name => "enforce_geotype_".$field->name,
486 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
487 table => $field->table,
498 my ($index, $options) = @_;
500 my $generator = _generator($options);
501 my $table_name = $index->table->name;
503 my ($index_def, @constraint_defs);
507 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
509 my $type = $index->type || NORMAL;
510 my @fields = $index->fields;
511 return unless @fields;
515 for my $opt ( $index->options ) {
516 if ( ref $opt eq 'HASH' ) {
517 foreach my $key (keys %$opt) {
518 my $value = $opt->{$key};
519 next unless defined $value;
520 if ( uc($key) eq 'USING' ) {
521 $index_using = "USING $value";
523 elsif ( uc($key) eq 'WHERE' ) {
524 $index_where = "WHERE $value";
530 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
531 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
532 if ( $type eq PRIMARY_KEY ) {
533 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
535 elsif ( $type eq UNIQUE ) {
536 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
538 elsif ( $type eq NORMAL ) {
540 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' .
541 join ' ', grep { defined } $index_using, $field_names, $index_where;
544 warn "Unknown index type ($type) on table $table_name.\n"
548 return $index_def, \@constraint_defs;
552 sub create_constraint
554 my ($c, $options) = @_;
556 my $generator = _generator($options);
557 my $table_name = $c->table->name;
558 my (@constraint_defs, @fks);
560 my $name = $c->name || '';
562 my @fields = grep { defined } $c->fields;
564 my @rfields = grep { defined } $c->reference_fields;
566 next if !@fields && $c->type ne CHECK_C;
567 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
568 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
569 if ( $c->type eq PRIMARY_KEY ) {
570 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
572 elsif ( $c->type eq UNIQUE ) {
573 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
575 elsif ( $c->type eq CHECK_C ) {
576 my $expression = $c->expression;
577 push @constraint_defs, "${def_start}CHECK ($expression)";
579 elsif ( $c->type eq FOREIGN_KEY ) {
580 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
581 . "\n REFERENCES " . $generator->quote($c->reference_table);
584 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
587 if ( $c->match_type ) {
589 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
592 if ( $c->on_delete ) {
593 $def .= ' ON DELETE '. $c->on_delete;
596 if ( $c->on_update ) {
597 $def .= ' ON UPDATE '. $c->on_update;
600 if ( $c->deferrable ) {
601 $def .= ' DEFERRABLE';
607 return \@constraint_defs, \@fks;
611 my ($trigger,$options) = @_;
612 my $generator = _generator($options);
616 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
617 if $options->{add_drop_trigger};
619 my $scope = $trigger->scope || '';
620 $scope = " FOR EACH $scope" if $scope;
622 push @statements, sprintf(
623 'CREATE TRIGGER %s %s %s ON %s%s %s',
624 $generator->quote($trigger->name),
625 $trigger->perform_action_when,
626 join( ' OR ', @{ $trigger->database_events } ),
627 $generator->quote($trigger->on_table),
639 my @size = $field->size;
640 my $data_type = lc $field->data_type;
641 my $array = $data_type =~ s/\[\]$//;
643 if ( $data_type eq 'enum' ) {
645 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
646 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
647 # push @$constraint_defs,
648 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
649 # qq[IN ($commalist))];
650 $data_type = 'character varying';
652 elsif ( $data_type eq 'set' ) {
653 $data_type = 'character varying';
655 elsif ( $field->is_auto_increment ) {
656 if ( defined $size[0] && $size[0] > 11 ) {
657 $data_type = 'bigserial';
660 $data_type = 'serial';
665 $data_type = defined $translate{ lc $data_type } ?
666 $translate{ lc $data_type } :
670 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
671 if ( defined $size[0] && $size[0] > 6 ) {
676 if ( $data_type eq 'integer' ) {
677 if ( defined $size[0] && $size[0] > 0) {
678 if ( $size[0] > 10 ) {
679 $data_type = 'bigint';
681 elsif ( $size[0] < 5 ) {
682 $data_type = 'smallint';
685 $data_type = 'integer';
689 $data_type = 'integer';
693 my $type_with_size = join('|',
694 'bit', 'varbit', 'character', 'bit varying', 'character varying',
695 'time', 'timestamp', 'interval', 'numeric', 'float'
698 if ( $data_type !~ /$type_with_size/ ) {
702 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
703 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
704 $data_type .= $2 if(defined $2);
705 } elsif ( defined $size[0] && $size[0] > 0 ) {
706 $data_type .= '(' . join( ',', @size ) . ')';
716 if($data_type eq 'geography'){
717 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
726 my ($from_field, $to_field, $options) = @_;
728 die "Can't alter field in another table"
729 if($from_field->table->name ne $to_field->table->name);
731 my $generator = _generator($options);
734 # drop geometry column and constraints
736 drop_geometry_column($from_field),
737 drop_geometry_constraints($from_field),
738 if is_geometry($from_field);
740 # it's necessary to start with rename column cause this would affect
741 # all of the following statements which would be broken if do the
743 # BUT: drop geometry is done before the rename, cause it work's on the
744 # $from_field directly
745 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
746 map($generator->quote($_),
747 $to_field->table->name,
752 if($from_field->name ne $to_field->name);
754 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
755 map($generator->quote($_),
756 $to_field->table->name,
760 if(!$to_field->is_nullable and $from_field->is_nullable);
762 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
763 map($generator->quote($_),
764 $to_field->table->name,
768 if (!$from_field->is_nullable and $to_field->is_nullable);
771 my $from_dt = convert_datatype($from_field);
772 my $to_dt = convert_datatype($to_field);
773 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
774 map($generator->quote($_),
775 $to_field->table->name,
780 if($to_dt ne $from_dt);
782 my $old_default = $from_field->default_value;
783 my $new_default = $to_field->default_value;
784 my $default_value = $to_field->default_value;
786 # fixes bug where output like this was created:
787 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
788 if(ref $default_value eq "SCALAR" ) {
789 $default_value = $$default_value;
790 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
791 $default_value = __PACKAGE__->_quote_string($default_value);
794 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
795 map($generator->quote($_),
796 $to_field->table->name,
801 if ( defined $new_default &&
802 (!defined $old_default || $old_default ne $new_default) );
804 # fixes bug where removing the DEFAULT statement of a column
805 # would result in no change
807 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
808 map($generator->quote($_),
809 $to_field->table->name,
813 if ( !defined $new_default && defined $old_default );
815 # add geometry column and constraints
817 add_geometry_column($to_field),
818 add_geometry_constraints($to_field)
819 if is_geometry($to_field);
821 return wantarray ? @out : join(";\n", @out);
824 sub rename_field { alter_field(@_) }
828 my ($new_field,$options) = @_;
830 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
831 _generator($options)->quote($new_field->table->name),
832 create_field($new_field, $options));
833 $out .= "\n".add_geometry_column($new_field)
834 . "\n".add_geometry_constraints($new_field)
835 if is_geometry($new_field);
842 my ($old_field, $options) = @_;
844 my $generator = _generator($options);
846 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
847 $generator->quote($old_field->table->name),
848 $generator->quote($old_field->name));
849 $out .= "\n".drop_geometry_column($old_field)
850 if is_geometry($old_field);
854 sub add_geometry_column {
855 my ($field, $options) = @_;
858 "INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
860 $field->table->schema->name,
861 $options->{table} ? $options->{table} : $field->table->name,
863 $field->extra->{dimensions},
864 $field->extra->{srid},
865 $field->extra->{geometry_type},
869 sub drop_geometry_column {
873 "DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
874 $field->table->schema->name,
880 sub add_geometry_constraints {
881 my ($field, $options) = @_;
883 return join("\n", map { alter_create_constraint($_) }
884 create_geometry_constraints($field));
887 sub drop_geometry_constraints {
888 my ($field, $options) = @_;
890 return join("\n", map { alter_drop_constraint($_) }
891 create_geometry_constraints($field));
896 my ($to_table, $options) = @_;
897 my $generator = _generator($options);
898 my $out = sprintf('ALTER TABLE %s %s',
899 $generator->quote($to_table->name),
900 $options->{alter_table_action});
901 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
906 my ($old_table, $new_table, $options) = @_;
907 my $generator = _generator($options);
908 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
910 my @geometry_changes = map {
911 drop_geometry_column($_),
912 add_geometry_column($_, { table => $new_table }),
913 } grep { is_geometry($_) } $old_table->get_fields;
915 $options->{geometry_changes} = join ("\n",@geometry_changes) if @geometry_changes;
917 return alter_table($old_table, $options);
920 sub alter_create_index {
921 my ($index, $options) = @_;
922 my $generator = _generator($options);
923 my ($idef, $constraints) = create_index($index, {
924 generator => $generator,
926 return $index->type eq NORMAL ? $idef
927 : sprintf('ALTER TABLE %s ADD %s',
928 $generator->quote($index->table->name),
929 join(q{}, @$constraints)
933 sub alter_drop_index {
934 my ($index, $options) = @_;
935 return 'DROP INDEX '. _generator($options)->quote($index->name);
938 sub alter_drop_constraint {
939 my ($c, $options) = @_;
940 my $generator = _generator($options);
942 # attention: Postgres has a very special naming structure for naming
943 # foreign keys and primary keys. It names them using the name of the
944 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
947 # Already has a name, just use it
949 } elsif ( $c->type eq FOREIGN_KEY ) {
950 # Doesn't have a name, and is foreign key, append '_fkey'
951 $c_name = $c->table->name . '_' . ($c->fields)[0] . '_fkey';
952 } elsif ( $c->type eq PRIMARY_KEY ) {
953 # Doesn't have a name, and is primary key, append '_pkey'
954 $c_name = $c->table->name . '_pkey';
958 'ALTER TABLE %s DROP CONSTRAINT %s',
959 map { $generator->quote($_) } $c->table->name, $c_name,
963 sub alter_create_constraint {
964 my ($index, $options) = @_;
965 my $generator = _generator($options);
966 my ($defs, $fks) = create_constraint(@_);
968 # return if there are no constraint definitions so we don't run
969 # into output like this:
970 # ALTER TABLE users ADD ;
972 return unless(@{$defs} || @{$fks});
973 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
974 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
975 'ADD', join(q{}, @{$defs}, @{$fks})
980 my ($table, $options) = @_;
981 my $generator = _generator($options);
982 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
984 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
986 $out .= join("\n", '', @geometry_drops) if @geometry_drops;
990 sub batch_alter_table {
991 my ( $table, $diff_hash, $options ) = @_;
993 # as long as we're not renaming the table we don't need to be here
994 if ( @{$diff_hash->{rename_table}} == 0 ) {
995 return batch_alter_table_statements($diff_hash, $options);
998 # first we need to perform drops which are on old table
999 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
1000 alter_drop_constraint
1005 # next comes the rename_table
1006 my $old_table = $diff_hash->{rename_table}[0][0];
1007 push @sql, rename_table( $old_table, $table, $options );
1009 # for alter_field (and so also rename_field) we need to make sure old
1010 # field has table name set to new table otherwise calling alter_field dies
1011 $diff_hash->{alter_field} =
1012 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1013 $diff_hash->{rename_field} =
1014 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1016 # now add everything else
1017 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1022 alter_create_constraint
1031 # -------------------------------------------------------------------
1032 # Life is full of misery, loneliness, and suffering --
1033 # and it's all over much too soon.
1035 # -------------------------------------------------------------------
1041 SQL::Translator, SQL::Translator::Producer::Oracle.
1045 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.