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 (grep { is_geometry($_) } $table->get_fields) {
337 $create_statement .= ";";
338 my @geometry_columns;
339 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
340 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
341 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
344 return $create_statement, \@fks;
348 my ($view, $options) = @_;
349 my $generator = _generator($options);
350 my $postgres_version = $options->{postgres_version} || 0;
351 my $add_drop_view = $options->{add_drop_view};
353 my $view_name = $view->name;
354 debug("PKG: Looking at view '${view_name}'\n");
357 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
358 unless $options->{no_comments};
359 if ($add_drop_view) {
360 if ($postgres_version >= 8.002) {
361 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
363 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
368 my $extra = $view->extra;
369 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
370 $create .= " VIEW " . $generator->quote($view_name);
372 if ( my @fields = $view->fields ) {
373 my $field_list = join ', ', map { $generator->quote($_) } @fields;
374 $create .= " ( ${field_list} )";
377 if ( my $sql = $view->sql ) {
378 $create .= " AS\n ${sql}\n";
381 if ( $extra->{check_option} ) {
382 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
390 my %field_name_scope;
394 my ($field, $options) = @_;
396 my $generator = _generator($options);
397 my $table_name = $field->table->name;
398 my $constraint_defs = $options->{constraint_defs} || [];
399 my $postgres_version = $options->{postgres_version} || 0;
400 my $type_defs = $options->{type_defs} || {};
402 $field_name_scope{$table_name} ||= {};
403 my $field_name = $field->name;
404 my $field_comments = '';
405 if (my $comments = $field->comments) {
406 $comments =~ s/(?<!\A)^/ -- /mg;
407 $field_comments = "-- $comments\n ";
410 my $field_def = $field_comments . $generator->quote($field_name);
415 my $data_type = lc $field->data_type;
416 my %extra = $field->extra;
417 my $list = $extra{'list'} || [];
418 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
420 if ($postgres_version >= 8.003 && $data_type eq 'enum') {
421 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
422 $field_def .= ' '. $type_name;
423 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
424 "CREATE TYPE $type_name AS ENUM ($commalist)";
425 if (! exists $type_defs->{$type_name} ) {
426 $type_defs->{$type_name} = $new_type_def;
427 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
428 die "Attempted to redefine type name '$type_name' as a different type.\n";
431 $field_def .= ' '. convert_datatype($field);
437 __PACKAGE__->_apply_default_value(
443 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
448 # Not null constraint
450 $field_def .= ' NOT NULL' unless $field->is_nullable;
453 # Geometry constraints
455 if (is_geometry($field)) {
456 foreach ( create_geometry_constraints($field) ) {
457 my ($cdefs, $fks) = create_constraint($_, {
458 generator => $generator,
460 push @$constraint_defs, @$cdefs;
469 sub create_geometry_constraints {
473 push @constraints, SQL::Translator::Schema::Constraint->new(
474 name => "enforce_dims_".$field->name,
475 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
476 table => $field->table,
480 push @constraints, SQL::Translator::Schema::Constraint->new(
481 name => "enforce_srid_".$field->name,
482 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
483 table => $field->table,
486 push @constraints, SQL::Translator::Schema::Constraint->new(
487 name => "enforce_geotype_".$field->name,
488 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
489 table => $field->table,
500 my ($index, $options) = @_;
502 my $generator = _generator($options);
503 my $table_name = $index->table->name;
505 my ($index_def, @constraint_defs);
509 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
511 my $type = $index->type || NORMAL;
512 my @fields = $index->fields;
513 return unless @fields;
517 for my $opt ( $index->options ) {
518 if ( ref $opt eq 'HASH' ) {
519 foreach my $key (keys %$opt) {
520 my $value = $opt->{$key};
521 next unless defined $value;
522 if ( uc($key) eq 'USING' ) {
523 $index_using = "USING $value";
525 elsif ( uc($key) eq 'WHERE' ) {
526 $index_where = "WHERE $value";
532 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
533 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
534 if ( $type eq PRIMARY_KEY ) {
535 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
537 elsif ( $type eq UNIQUE ) {
538 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
540 elsif ( $type eq NORMAL ) {
542 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' .
543 join ' ', grep { defined } $index_using, $field_names, $index_where;
546 warn "Unknown index type ($type) on table $table_name.\n"
550 return $index_def, \@constraint_defs;
554 sub create_constraint
556 my ($c, $options) = @_;
558 my $generator = _generator($options);
559 my $table_name = $c->table->name;
560 my (@constraint_defs, @fks);
562 my $name = $c->name || '';
564 my @fields = grep { defined } $c->fields;
566 my @rfields = grep { defined } $c->reference_fields;
568 next if !@fields && $c->type ne CHECK_C;
569 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
570 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
571 if ( $c->type eq PRIMARY_KEY ) {
572 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
574 elsif ( $c->type eq UNIQUE ) {
575 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
577 elsif ( $c->type eq CHECK_C ) {
578 my $expression = $c->expression;
579 push @constraint_defs, "${def_start}CHECK ($expression)";
581 elsif ( $c->type eq FOREIGN_KEY ) {
582 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
583 . "\n REFERENCES " . $generator->quote($c->reference_table);
586 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
589 if ( $c->match_type ) {
591 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
594 if ( $c->on_delete ) {
595 $def .= ' ON DELETE '. $c->on_delete;
598 if ( $c->on_update ) {
599 $def .= ' ON UPDATE '. $c->on_update;
602 if ( $c->deferrable ) {
603 $def .= ' DEFERRABLE';
609 return \@constraint_defs, \@fks;
613 my ($trigger,$options) = @_;
614 my $generator = _generator($options);
618 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
619 if $options->{add_drop_trigger};
621 my $scope = $trigger->scope || '';
622 $scope = " FOR EACH $scope" if $scope;
624 push @statements, sprintf(
625 'CREATE TRIGGER %s %s %s ON %s%s %s',
626 $generator->quote($trigger->name),
627 $trigger->perform_action_when,
628 join( ' OR ', @{ $trigger->database_events } ),
629 $generator->quote($trigger->on_table),
641 my @size = $field->size;
642 my $data_type = lc $field->data_type;
643 my $array = $data_type =~ s/\[\]$//;
645 if ( $data_type eq 'enum' ) {
647 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
648 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
649 # push @$constraint_defs,
650 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
651 # qq[IN ($commalist))];
652 $data_type = 'character varying';
654 elsif ( $data_type eq 'set' ) {
655 $data_type = 'character varying';
657 elsif ( $field->is_auto_increment ) {
658 if ( defined $size[0] && $size[0] > 11 ) {
659 $data_type = 'bigserial';
662 $data_type = 'serial';
667 $data_type = defined $translate{ lc $data_type } ?
668 $translate{ lc $data_type } :
672 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
673 if ( defined $size[0] && $size[0] > 6 ) {
678 if ( $data_type eq 'integer' ) {
679 if ( defined $size[0] && $size[0] > 0) {
680 if ( $size[0] > 10 ) {
681 $data_type = 'bigint';
683 elsif ( $size[0] < 5 ) {
684 $data_type = 'smallint';
687 $data_type = 'integer';
691 $data_type = 'integer';
695 my $type_with_size = join('|',
696 'bit', 'varbit', 'character', 'bit varying', 'character varying',
697 'time', 'timestamp', 'interval', 'numeric', 'float'
700 if ( $data_type !~ /$type_with_size/ ) {
704 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
705 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
706 $data_type .= $2 if(defined $2);
707 } elsif ( defined $size[0] && $size[0] > 0 ) {
708 $data_type .= '(' . join( ',', @size ) . ')';
718 if($data_type eq 'geography'){
719 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
728 my ($from_field, $to_field, $options) = @_;
730 die "Can't alter field in another table"
731 if($from_field->table->name ne $to_field->table->name);
733 my $generator = _generator($options);
736 # drop geometry column and constraints
737 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
738 push @out, drop_geometry_constraints($from_field) 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
816 push @out, add_geometry_column($to_field) if is_geometry($to_field);
817 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
819 return wantarray ? @out : join(";\n", @out);
822 sub rename_field { alter_field(@_) }
826 my ($new_field,$options) = @_;
828 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
829 _generator($options)->quote($new_field->table->name),
830 create_field($new_field, $options));
831 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
832 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
839 my ($old_field, $options) = @_;
841 my $generator = _generator($options);
843 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
844 $generator->quote($old_field->table->name),
845 $generator->quote($old_field->name));
846 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
850 sub add_geometry_column {
851 my ($field,$options) = @_;
854 "INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
856 $field->table->schema->name,
857 $options->{table} ? $options->{table} : $field->table->name,
859 $field->extra->{dimensions},
860 $field->extra->{srid},
861 $field->extra->{geometry_type},
866 sub drop_geometry_column {
870 "DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
871 $field->table->schema->name,
878 sub add_geometry_constraints {
881 my @constraints = create_geometry_constraints($field);
883 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
888 sub drop_geometry_constraints {
891 my @constraints = create_geometry_constraints($field);
893 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
899 my ($to_table, $options) = @_;
900 my $generator = _generator($options);
901 my $out = sprintf('ALTER TABLE %s %s',
902 $generator->quote($to_table->name),
903 $options->{alter_table_action});
904 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
909 my ($old_table, $new_table, $options) = @_;
910 my $generator = _generator($options);
911 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
913 my @geometry_changes;
914 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
915 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
917 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
919 return alter_table($old_table, $options);
922 sub alter_create_index {
923 my ($index, $options) = @_;
924 my $generator = _generator($options);
925 my ($idef, $constraints) = create_index($index, {
926 generator => $generator,
928 return $index->type eq NORMAL ? $idef
929 : sprintf('ALTER TABLE %s ADD %s',
930 $generator->quote($index->table->name),
931 join(q{}, @$constraints)
935 sub alter_drop_index {
936 my ($index, $options) = @_;
937 return 'DROP INDEX '. _generator($options)->quote($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 use it
951 } elsif ( $c->type eq FOREIGN_KEY ) {
952 # Doesn't have a name, and is foreign key, append '_fkey'
953 $c_name = $c->table->name . '_' . ($c->fields)[0] . '_fkey';
954 } elsif ( $c->type eq PRIMARY_KEY ) {
955 # Doesn't have a name, and is primary key, append '_pkey'
956 $c_name = $c->table->name . '_pkey';
960 'ALTER TABLE %s DROP CONSTRAINT %s',
961 map { $generator->quote($_) } $c->table->name, $c_name,
965 sub alter_create_constraint {
966 my ($index, $options) = @_;
967 my $generator = _generator($options);
968 my ($defs, $fks) = create_constraint(@_);
970 # return if there are no constraint definitions so we don't run
971 # into output like this:
972 # ALTER TABLE users ADD ;
974 return unless(@{$defs} || @{$fks});
975 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
976 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
977 'ADD', join(q{}, @{$defs}, @{$fks})
982 my ($table, $options) = @_;
983 my $generator = _generator($options);
984 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
986 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
988 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
992 sub batch_alter_table {
993 my ( $table, $diff_hash, $options ) = @_;
995 # as long as we're not renaming the table we don't need to be here
996 if ( @{$diff_hash->{rename_table}} == 0 ) {
997 return batch_alter_table_statements($diff_hash, $options);
1000 # first we need to perform drops which are on old table
1001 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
1002 alter_drop_constraint
1007 # next comes the rename_table
1008 my $old_table = $diff_hash->{rename_table}[0][0];
1009 push @sql, rename_table( $old_table, $table, $options );
1011 # for alter_field (and so also rename_field) we need to make sure old
1012 # field has table name set to new table otherwise calling alter_field dies
1013 $diff_hash->{alter_field} =
1014 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1015 $diff_hash->{rename_field} =
1016 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1018 # now add everything else
1019 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1024 alter_create_constraint
1033 # -------------------------------------------------------------------
1034 # Life is full of misery, loneliness, and suffering --
1035 # and it's all over much too soon.
1037 # -------------------------------------------------------------------
1043 SQL::Translator, SQL::Translator::Producer::Oracle.
1047 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.