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 );
57 double => 'double precision',
60 mediumint => 'integer',
61 tinyint => 'smallint',
63 varchar => 'character varying',
69 mediumblob => 'bytea',
71 enum => 'character varying',
72 set => 'character varying',
73 datetime => 'timestamp',
80 varchar2 => 'character varying',
97 my %reserved = map { $_, 1 } qw[
98 ALL ANALYSE ANALYZE AND ANY AS ASC
100 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
101 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
102 DEFAULT DEFERRABLE DESC DISTINCT DO
104 FALSE FOR FOREIGN FREEZE FROM FULL
106 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
107 JOIN LEADING LEFT LIKE LIMIT
108 NATURAL NEW NOT NOTNULL NULL
109 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
110 PRIMARY PUBLIC REFERENCES RIGHT
111 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
112 UNION UNIQUE USER USING VERBOSE WHEN WHERE
115 # my $max_id_length = 62;
116 my %used_identifiers = ();
122 =head1 PostgreSQL Create Table Syntax
124 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
125 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
126 | table_constraint } [, ... ]
128 [ INHERITS ( parent_table [, ... ] ) ]
129 [ WITH OIDS | WITHOUT OIDS ]
131 where column_constraint is:
133 [ CONSTRAINT constraint_name ]
134 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
136 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
137 [ ON DELETE action ] [ ON UPDATE action ] }
138 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
140 and table_constraint is:
142 [ CONSTRAINT constraint_name ]
143 { UNIQUE ( column_name [, ... ] ) |
144 PRIMARY KEY ( column_name [, ... ] ) |
145 CHECK ( expression ) |
146 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
147 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
148 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
150 =head1 Create Index Syntax
152 CREATE [ UNIQUE ] INDEX index_name ON table
153 [ USING acc_method ] ( column [ ops_name ] [, ...] )
155 CREATE [ UNIQUE ] INDEX index_name ON table
156 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
162 my $translator = shift;
163 local $DEBUG = $translator->debug;
164 local $WARN = $translator->show_warnings;
165 my $no_comments = $translator->no_comments;
166 my $add_drop_table = $translator->add_drop_table;
167 my $schema = $translator->schema;
168 my $pargs = $translator->producer_args;
169 my $postgres_version = parse_dbms_version(
170 $pargs->{postgres_version}, 'perl'
173 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
176 push @output, header_comment unless ($no_comments);
178 my (@table_defs, @fks);
180 for my $table ( $schema->get_tables ) {
182 my ($table_def, $fks) = create_table($table, {
183 generator => $generator,
184 no_comments => $no_comments,
185 postgres_version => $postgres_version,
186 add_drop_table => $add_drop_table,
187 type_defs => \%type_defs,
190 push @table_defs, $table_def;
194 for my $view ( $schema->get_views ) {
195 push @table_defs, create_view($view, {
196 postgres_version => $postgres_version,
197 add_drop_view => $add_drop_table,
198 generator => $generator,
199 no_comments => $no_comments,
203 for my $trigger ( $schema->get_triggers ) {
204 push @table_defs, create_trigger( $trigger, {
205 add_drop_trigger => $add_drop_table,
206 generator => $generator,
207 no_comments => $no_comments,
211 push @output, map { "$_;\n\n" } values %type_defs;
212 push @output, map { "$_;\n\n" } @table_defs;
214 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
215 push @output, map { "$_;\n\n" } @fks;
220 warn "Truncated " . keys( %truncated ) . " names:\n";
221 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
227 : join ('', @output);
231 my $basename = shift || '';
232 my $type = shift || '';
233 my $scope = shift || '';
234 my $critical = shift || '';
235 my $basename_orig = $basename;
236 # my $max_id_length = 62;
238 ? $max_id_length - (length($type) + 1)
240 $basename = substr( $basename, 0, $max_name )
241 if length( $basename ) > $max_name;
242 my $name = $type ? "${type}_$basename" : $basename;
244 if ( $basename ne $basename_orig and $critical ) {
245 my $show_type = $type ? "+'$type'" : "";
246 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
247 "character limit to make '$name'\n" if $WARN;
248 $truncated{ $basename_orig } = $name;
251 $scope ||= \%global_names;
252 if ( my $prev = $scope->{ $name } ) {
253 my $name_orig = $name;
254 $name .= sprintf( "%02d", ++$prev );
255 substr($name, $max_id_length - 3) = "00"
256 if length( $name ) > $max_id_length;
258 warn "The name '$name_orig' has been changed to ",
259 "'$name' to make it unique.\n" if $WARN;
261 $scope->{ $name_orig }++;
271 return 1 if $field->data_type eq 'geometry';
277 return 1 if $field->data_type eq 'geography';
282 my ($table, $options) = @_;
284 my $generator = _generator($options);
285 my $no_comments = $options->{no_comments} || 0;
286 my $add_drop_table = $options->{add_drop_table} || 0;
287 my $postgres_version = $options->{postgres_version} || 0;
288 my $type_defs = $options->{type_defs} || {};
290 my $table_name = $table->name or next;
291 my $table_name_qt = $generator->quote($table_name);
293 # print STDERR "$table_name table_name\n";
294 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @fks );
296 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
298 if ( $table->comments and !$no_comments ){
299 my $c = "-- Comments: \n-- ";
300 $c .= join "\n-- ", $table->comments;
308 my %field_name_scope;
309 for my $field ( $table->get_fields ) {
310 push @field_defs, create_field($field, {
311 generator => $generator,
312 postgres_version => $postgres_version,
313 type_defs => $type_defs,
314 constraint_defs => \@constraint_defs,
322 # my $idx_name_default;
323 for my $index ( $table->get_indices ) {
324 my ($idef, $constraints) = create_index($index, {
325 generator => $generator,
327 $idef and push @index_defs, $idef;
328 push @constraint_defs, @$constraints;
335 for my $c ( $table->get_constraints ) {
336 my ($cdefs, $fks) = create_constraint($c, {
337 generator => $generator,
339 push @constraint_defs, @$cdefs;
346 if(exists $table->extra->{temporary}) {
347 $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
350 my $create_statement;
351 $create_statement = join("\n", @comments);
352 if ($add_drop_table) {
353 if ($postgres_version >= 8.002) {
354 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
356 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
359 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
360 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
363 $create_statement .= @index_defs ? ';' : q{};
364 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
365 . join(";\n", @index_defs);
370 if(grep { is_geometry($_) } $table->get_fields){
371 $create_statement .= ";";
372 my @geometry_columns;
373 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
374 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
375 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
378 return $create_statement, \@fks;
382 my ($view, $options) = @_;
383 my $generator = _generator($options);
384 my $postgres_version = $options->{postgres_version} || 0;
385 my $add_drop_view = $options->{add_drop_view};
387 my $view_name = $view->name;
388 debug("PKG: Looking at view '${view_name}'\n");
391 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
392 unless $options->{no_comments};
393 if ($add_drop_view) {
394 if ($postgres_version >= 8.002) {
395 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
397 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
402 my $extra = $view->extra;
403 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
404 $create .= " VIEW " . $generator->quote($view_name);
406 if ( my @fields = $view->fields ) {
407 my $field_list = join ', ', map { $generator->quote($_) } @fields;
408 $create .= " ( ${field_list} )";
411 if ( my $sql = $view->sql ) {
412 $create .= " AS\n ${sql}\n";
415 if ( $extra->{check_option} ) {
416 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
424 my %field_name_scope;
428 my ($field, $options) = @_;
430 my $generator = _generator($options);
431 my $table_name = $field->table->name;
432 my $constraint_defs = $options->{constraint_defs} || [];
433 my $postgres_version = $options->{postgres_version} || 0;
434 my $type_defs = $options->{type_defs} || {};
436 $field_name_scope{$table_name} ||= {};
437 my $field_name = $field->name;
438 my $field_comments = $field->comments
439 ? "-- " . $field->comments . "\n "
442 my $field_def = $field_comments . $generator->quote($field_name);
447 my @size = $field->size;
448 my $data_type = lc $field->data_type;
449 my %extra = $field->extra;
450 my $list = $extra{'list'} || [];
451 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
453 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
454 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
455 $field_def .= ' '. $type_name;
456 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
457 "CREATE TYPE $type_name AS ENUM ($commalist)";
458 if (! exists $type_defs->{$type_name} ) {
459 $type_defs->{$type_name} = $new_type_def;
460 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
461 die "Attempted to redefine type name '$type_name' as a different type.\n";
464 $field_def .= ' '. convert_datatype($field);
470 __PACKAGE__->_apply_default_value(
476 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
481 # Not null constraint
483 $field_def .= ' NOT NULL' unless $field->is_nullable;
486 # Geometry constraints
488 if(is_geometry($field)){
489 foreach ( create_geometry_constraints($field) ) {
490 my ($cdefs, $fks) = create_constraint($_, {
491 generator => $generator,
493 push @$constraint_defs, @$cdefs;
502 sub create_geometry_constraints{
506 push @constraints, SQL::Translator::Schema::Constraint->new(
507 name => "enforce_dims_".$field->name,
508 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
509 table => $field->table,
513 push @constraints, SQL::Translator::Schema::Constraint->new(
514 name => "enforce_srid_".$field->name,
515 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
516 table => $field->table,
519 push @constraints, SQL::Translator::Schema::Constraint->new(
520 name => "enforce_geotype_".$field->name,
521 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
522 table => $field->table,
531 my ($index, $options) = @_;
533 my $generator = _generator($options);
534 my $table_name = $index->table->name;
536 my ($index_def, @constraint_defs);
540 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
542 my $type = $index->type || NORMAL;
543 my @fields = $index->fields;
544 return unless @fields;
548 for my $opt ( $index->options ) {
549 if ( ref $opt eq 'HASH' ) {
550 foreach my $key (keys %$opt) {
551 my $value = $opt->{$key};
552 next unless defined $value;
553 if ( uc($key) eq 'USING' ) {
554 $index_using = "USING $value";
556 elsif ( uc($key) eq 'WHERE' ) {
557 $index_where = "WHERE $value";
563 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
564 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
565 if ( $type eq PRIMARY_KEY ) {
566 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
568 elsif ( $type eq UNIQUE ) {
569 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
571 elsif ( $type eq NORMAL ) {
573 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' .
574 join ' ', grep { defined } $index_using, $field_names, $index_where;
577 warn "Unknown index type ($type) on table $table_name.\n"
581 return $index_def, \@constraint_defs;
584 sub create_constraint
586 my ($c, $options) = @_;
588 my $generator = _generator($options);
589 my $table_name = $c->table->name;
590 my (@constraint_defs, @fks);
592 my $name = $c->name || '';
594 my @fields = grep { defined } $c->fields;
596 my @rfields = grep { defined } $c->reference_fields;
598 next if !@fields && $c->type ne CHECK_C;
599 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
600 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
601 if ( $c->type eq PRIMARY_KEY ) {
602 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
604 elsif ( $c->type eq UNIQUE ) {
605 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
607 elsif ( $c->type eq CHECK_C ) {
608 my $expression = $c->expression;
609 push @constraint_defs, "${def_start}CHECK ($expression)";
611 elsif ( $c->type eq FOREIGN_KEY ) {
612 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
613 . "\n REFERENCES " . $generator->quote($c->reference_table);
616 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
619 if ( $c->match_type ) {
621 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
624 if ( $c->on_delete ) {
625 $def .= ' ON DELETE '. $c->on_delete;
628 if ( $c->on_update ) {
629 $def .= ' ON UPDATE '. $c->on_update;
632 if ( $c->deferrable ) {
633 $def .= ' DEFERRABLE';
639 return \@constraint_defs, \@fks;
643 my ($trigger,$options) = @_;
644 my $generator = _generator($options);
648 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
649 if $options->{add_drop_trigger};
651 my $scope = $trigger->scope || '';
652 $scope = " FOR EACH $scope" if $scope;
654 push @statements, sprintf(
655 'CREATE TRIGGER %s %s %s ON %s%s %s',
656 $generator->quote($trigger->name),
657 $trigger->perform_action_when,
658 join( ' OR ', @{ $trigger->database_events } ),
659 $generator->quote($trigger->on_table),
671 my @size = $field->size;
672 my $data_type = lc $field->data_type;
673 my $array = $data_type =~ s/\[\]$//;
675 if ( $data_type eq 'enum' ) {
677 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
678 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
679 # push @$constraint_defs,
680 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
681 # qq[IN ($commalist))];
682 $data_type = 'character varying';
684 elsif ( $data_type eq 'set' ) {
685 $data_type = 'character varying';
687 elsif ( $field->is_auto_increment ) {
688 if ( defined $size[0] && $size[0] > 11 ) {
689 $data_type = 'bigserial';
692 $data_type = 'serial';
697 $data_type = defined $translate{ lc $data_type } ?
698 $translate{ lc $data_type } :
702 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
703 if ( defined $size[0] && $size[0] > 6 ) {
708 if ( $data_type eq 'integer' ) {
709 if ( defined $size[0] && $size[0] > 0) {
710 if ( $size[0] > 10 ) {
711 $data_type = 'bigint';
713 elsif ( $size[0] < 5 ) {
714 $data_type = 'smallint';
717 $data_type = 'integer';
721 $data_type = 'integer';
725 my $type_with_size = join('|',
726 'bit', 'varbit', 'character', 'bit varying', 'character varying',
727 'time', 'timestamp', 'interval', 'numeric', 'float'
730 if ( $data_type !~ /$type_with_size/ ) {
734 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
735 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
736 $data_type .= $2 if(defined $2);
737 } elsif ( defined $size[0] && $size[0] > 0 ) {
738 $data_type .= '(' . join( ',', @size ) . ')';
748 if($data_type eq 'geography'){
749 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
758 my ($from_field, $to_field) = @_;
760 die "Can't alter field in another table"
761 if($from_field->table->name ne $to_field->table->name);
765 # drop geometry column and constraints
766 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
767 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
769 # it's necessary to start with rename column cause this would affect
770 # all of the following statements which would be broken if do the
772 # BUT: drop geometry is done before the rename, cause it work's on the
773 # $from_field directly
774 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
775 $to_field->table->name,
777 $to_field->name) if($from_field->name ne $to_field->name);
779 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
780 $to_field->table->name,
781 $to_field->name) if(!$to_field->is_nullable and
782 $from_field->is_nullable);
784 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
785 $to_field->table->name,
787 if ( !$from_field->is_nullable and $to_field->is_nullable );
790 my $from_dt = convert_datatype($from_field);
791 my $to_dt = convert_datatype($to_field);
792 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
793 $to_field->table->name,
795 $to_dt) if($to_dt ne $from_dt);
797 my $old_default = $from_field->default_value;
798 my $new_default = $to_field->default_value;
799 my $default_value = $to_field->default_value;
801 # fixes bug where output like this was created:
802 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
803 if(ref $default_value eq "SCALAR" ) {
804 $default_value = $$default_value;
805 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
806 $default_value = __PACKAGE__->_quote_string($default_value);
809 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
810 $to_field->table->name,
813 if ( defined $new_default &&
814 (!defined $old_default || $old_default ne $new_default) );
816 # fixes bug where removing the DEFAULT statement of a column
817 # would result in no change
819 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
820 $to_field->table->name,
822 if ( !defined $new_default && defined $old_default );
824 # add geometry column and constraints
825 push @out, add_geometry_column($to_field) if is_geometry($to_field);
826 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
828 return wantarray ? @out : join(";\n", @out);
831 sub rename_field { alter_field(@_) }
835 my ($new_field) = @_;
837 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
838 $new_field->table->name,
839 create_field($new_field));
840 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
841 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
848 my ($old_field, $options) = @_;
850 my $generator = _generator($options);
852 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
853 $generator->quote($old_field->table->name),
854 $generator->quote($old_field->name));
855 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
859 sub add_geometry_column{
860 my ($field,$options) = @_;
862 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
864 $field->table->schema->name,
865 $options->{table} ? $options->{table} : $field->table->name,
867 $field->extra->{dimensions},
868 $field->extra->{srid},
869 $field->extra->{geometry_type});
873 sub drop_geometry_column
877 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
878 $field->table->schema->name,
884 sub add_geometry_constraints{
887 my @constraints = create_geometry_constraints($field);
889 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
894 sub drop_geometry_constraints{
897 my @constraints = create_geometry_constraints($field);
899 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
905 my ($to_table, $options) = @_;
906 my $generator = _generator($options);
907 my $out = sprintf('ALTER TABLE %s %s',
908 $generator->quote($to_table->name),
909 $options->{alter_table_action});
910 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
915 my ($old_table, $new_table, $options) = @_;
916 my $generator = _generator($options);
917 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
919 my @geometry_changes;
920 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
921 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
923 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
925 return alter_table($old_table, $options);
928 sub alter_create_index {
929 my ($index, $options) = @_;
930 my $generator = _generator($options);
931 my ($idef, $constraints) = create_index($index, {
932 generator => $generator,
934 return $index->type eq NORMAL ? $idef
935 : sprintf('ALTER TABLE %s ADD %s',
936 $generator->quote($index->table->name),
937 join(q{}, @$constraints)
941 sub alter_drop_index {
942 my ($index, $options) = @_;
943 my $index_name = $index->name;
944 return "DROP INDEX $index_name";
947 sub alter_drop_constraint {
948 my ($c, $options) = @_;
949 my $generator = _generator($options);
951 # attention: Postgres has a very special naming structure for naming
952 # foreign keys and primary keys. It names them using the name of the
953 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
956 # Already has a name, just quote it
957 $c_name = $generator->quote($c->name);
958 } elsif ( $c->type eq FOREIGN_KEY ) {
959 # Doesn't have a name, and is foreign key, append '_fkey'
960 $c_name = $generator->quote($c->table->name . '_' .
961 ($c->fields)[0] . '_fkey');
962 } elsif ( $c->type eq PRIMARY_KEY ) {
963 # Doesn't have a name, and is primary key, append '_pkey'
964 $c_name = $generator->quote($c->table->name . '_pkey');
968 'ALTER TABLE %s DROP CONSTRAINT %s',
969 $generator->quote($c->table->name), $c_name
973 sub alter_create_constraint {
974 my ($index, $options) = @_;
975 my $generator = _generator($options);
976 my ($defs, $fks) = create_constraint(@_);
978 # return if there are no constraint definitions so we don't run
979 # into output like this:
980 # ALTER TABLE users ADD ;
982 return unless(@{$defs} || @{$fks});
983 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
984 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
985 'ADD', join(q{}, @{$defs}, @{$fks})
990 my ($table, $options) = @_;
991 my $generator = _generator($options);
992 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
994 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
996 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
1000 sub batch_alter_table {
1001 my ( $table, $diff_hash, $options ) = @_;
1003 # as long as we're not renaming the table we don't need to be here
1004 if ( @{$diff_hash->{rename_table}} == 0 ) {
1005 return batch_alter_table_statements($diff_hash, $options);
1008 # first we need to perform drops which are on old table
1009 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
1010 alter_drop_constraint
1015 # next comes the rename_table
1016 my $old_table = $diff_hash->{rename_table}[0][0];
1017 push @sql, rename_table( $old_table, $table, $options );
1019 # for alter_field (and so also rename_field) we need to make sure old
1020 # field has table name set to new table otherwise calling alter_field dies
1021 $diff_hash->{alter_field} =
1022 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1023 $diff_hash->{rename_field} =
1024 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1026 # now add everything else
1027 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1032 alter_create_constraint
1041 # -------------------------------------------------------------------
1042 # Life is full of misery, loneliness, and suffering --
1043 # and it's all over much too soon.
1045 # -------------------------------------------------------------------
1051 SQL::Translator, SQL::Translator::Producer::Oracle.
1055 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.