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, @index_defs, @sequence_defs, @constraint_defs, @fks );
296 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
298 if ( !$no_comments and my $comments = $table->comments ) {
299 $comments =~ s/^/-- /mg;
300 push @comments, "-- Comments:\n$comments\n--\n";
306 for my $field ( $table->get_fields ) {
307 push @field_defs, create_field($field, {
308 generator => $generator,
309 postgres_version => $postgres_version,
310 type_defs => $type_defs,
311 constraint_defs => \@constraint_defs,
318 for my $index ( $table->get_indices ) {
319 my ($idef, $constraints) = create_index($index, {
320 generator => $generator,
322 $idef and push @index_defs, $idef;
323 push @constraint_defs, @$constraints;
329 for my $c ( $table->get_constraints ) {
330 my ($cdefs, $fks) = create_constraint($c, {
331 generator => $generator,
333 push @constraint_defs, @$cdefs;
338 my $create_statement = join("\n", @comments);
339 if ($add_drop_table) {
340 if ($postgres_version >= 8.002) {
341 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
343 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
346 my $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
347 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
348 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
351 $create_statement .= @index_defs ? ';' : q{};
352 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
353 . join(";\n", @index_defs);
358 if(grep { is_geometry($_) } $table->get_fields){
359 $create_statement .= ";";
360 my @geometry_columns;
361 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
362 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
363 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
366 return $create_statement, \@fks;
370 my ($view, $options) = @_;
371 my $generator = _generator($options);
372 my $postgres_version = $options->{postgres_version} || 0;
373 my $add_drop_view = $options->{add_drop_view};
375 my $view_name = $view->name;
376 debug("PKG: Looking at view '${view_name}'\n");
379 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
380 unless $options->{no_comments};
381 if ($add_drop_view) {
382 if ($postgres_version >= 8.002) {
383 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
385 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
390 my $extra = $view->extra;
391 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
392 $create .= " VIEW " . $generator->quote($view_name);
394 if ( my @fields = $view->fields ) {
395 my $field_list = join ', ', map { $generator->quote($_) } @fields;
396 $create .= " ( ${field_list} )";
399 if ( my $sql = $view->sql ) {
400 $create .= " AS\n ${sql}\n";
403 if ( $extra->{check_option} ) {
404 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
412 my %field_name_scope;
416 my ($field, $options) = @_;
418 my $generator = _generator($options);
419 my $table_name = $field->table->name;
420 my $constraint_defs = $options->{constraint_defs} || [];
421 my $postgres_version = $options->{postgres_version} || 0;
422 my $type_defs = $options->{type_defs} || {};
424 $field_name_scope{$table_name} ||= {};
425 my $field_name = $field->name;
426 my $field_comments = '';
427 if (my $comments = $field->comments) {
428 $comments =~ s/(?<!\A)^/ -- /mg;
429 $field_comments = "-- $comments\n ";
432 my $field_def = $field_comments . $generator->quote($field_name);
437 my @size = $field->size;
438 my $data_type = lc $field->data_type;
439 my %extra = $field->extra;
440 my $list = $extra{'list'} || [];
441 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
443 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
444 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
445 $field_def .= ' '. $type_name;
446 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
447 "CREATE TYPE $type_name AS ENUM ($commalist)";
448 if (! exists $type_defs->{$type_name} ) {
449 $type_defs->{$type_name} = $new_type_def;
450 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
451 die "Attempted to redefine type name '$type_name' as a different type.\n";
454 $field_def .= ' '. convert_datatype($field);
460 __PACKAGE__->_apply_default_value(
466 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
471 # Not null constraint
473 $field_def .= ' NOT NULL' unless $field->is_nullable;
476 # Geometry constraints
478 if(is_geometry($field)){
479 foreach ( create_geometry_constraints($field) ) {
480 my ($cdefs, $fks) = create_constraint($_, {
481 generator => $generator,
483 push @$constraint_defs, @$cdefs;
492 sub create_geometry_constraints{
496 push @constraints, SQL::Translator::Schema::Constraint->new(
497 name => "enforce_dims_".$field->name,
498 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
499 table => $field->table,
503 push @constraints, SQL::Translator::Schema::Constraint->new(
504 name => "enforce_srid_".$field->name,
505 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
506 table => $field->table,
509 push @constraints, SQL::Translator::Schema::Constraint->new(
510 name => "enforce_geotype_".$field->name,
511 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
512 table => $field->table,
521 my ($index, $options) = @_;
523 my $generator = _generator($options);
524 my $table_name = $index->table->name;
526 my ($index_def, @constraint_defs);
530 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
532 my $type = $index->type || NORMAL;
533 my @fields = $index->fields;
534 return unless @fields;
538 for my $opt ( $index->options ) {
539 if ( ref $opt eq 'HASH' ) {
540 foreach my $key (keys %$opt) {
541 my $value = $opt->{$key};
542 next unless defined $value;
543 if ( uc($key) eq 'USING' ) {
544 $index_using = "USING $value";
546 elsif ( uc($key) eq 'WHERE' ) {
547 $index_where = "WHERE $value";
553 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
554 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
555 if ( $type eq PRIMARY_KEY ) {
556 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
558 elsif ( $type eq UNIQUE ) {
559 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
561 elsif ( $type eq NORMAL ) {
563 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' .
564 join ' ', grep { defined } $index_using, $field_names, $index_where;
567 warn "Unknown index type ($type) on table $table_name.\n"
571 return $index_def, \@constraint_defs;
574 sub create_constraint
576 my ($c, $options) = @_;
578 my $generator = _generator($options);
579 my $table_name = $c->table->name;
580 my (@constraint_defs, @fks);
582 my $name = $c->name || '';
584 my @fields = grep { defined } $c->fields;
586 my @rfields = grep { defined } $c->reference_fields;
588 next if !@fields && $c->type ne CHECK_C;
589 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
590 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
591 if ( $c->type eq PRIMARY_KEY ) {
592 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
594 elsif ( $c->type eq UNIQUE ) {
595 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
597 elsif ( $c->type eq CHECK_C ) {
598 my $expression = $c->expression;
599 push @constraint_defs, "${def_start}CHECK ($expression)";
601 elsif ( $c->type eq FOREIGN_KEY ) {
602 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
603 . "\n REFERENCES " . $generator->quote($c->reference_table);
606 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
609 if ( $c->match_type ) {
611 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
614 if ( $c->on_delete ) {
615 $def .= ' ON DELETE '. $c->on_delete;
618 if ( $c->on_update ) {
619 $def .= ' ON UPDATE '. $c->on_update;
622 if ( $c->deferrable ) {
623 $def .= ' DEFERRABLE';
629 return \@constraint_defs, \@fks;
633 my ($trigger,$options) = @_;
634 my $generator = _generator($options);
638 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
639 if $options->{add_drop_trigger};
641 my $scope = $trigger->scope || '';
642 $scope = " FOR EACH $scope" if $scope;
644 push @statements, sprintf(
645 'CREATE TRIGGER %s %s %s ON %s%s %s',
646 $generator->quote($trigger->name),
647 $trigger->perform_action_when,
648 join( ' OR ', @{ $trigger->database_events } ),
649 $generator->quote($trigger->on_table),
661 my @size = $field->size;
662 my $data_type = lc $field->data_type;
663 my $array = $data_type =~ s/\[\]$//;
665 if ( $data_type eq 'enum' ) {
667 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
668 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
669 # push @$constraint_defs,
670 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
671 # qq[IN ($commalist))];
672 $data_type = 'character varying';
674 elsif ( $data_type eq 'set' ) {
675 $data_type = 'character varying';
677 elsif ( $field->is_auto_increment ) {
678 if ( defined $size[0] && $size[0] > 11 ) {
679 $data_type = 'bigserial';
682 $data_type = 'serial';
687 $data_type = defined $translate{ lc $data_type } ?
688 $translate{ lc $data_type } :
692 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
693 if ( defined $size[0] && $size[0] > 6 ) {
698 if ( $data_type eq 'integer' ) {
699 if ( defined $size[0] && $size[0] > 0) {
700 if ( $size[0] > 10 ) {
701 $data_type = 'bigint';
703 elsif ( $size[0] < 5 ) {
704 $data_type = 'smallint';
707 $data_type = 'integer';
711 $data_type = 'integer';
715 my $type_with_size = join('|',
716 'bit', 'varbit', 'character', 'bit varying', 'character varying',
717 'time', 'timestamp', 'interval', 'numeric', 'float'
720 if ( $data_type !~ /$type_with_size/ ) {
724 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
725 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
726 $data_type .= $2 if(defined $2);
727 } elsif ( defined $size[0] && $size[0] > 0 ) {
728 $data_type .= '(' . join( ',', @size ) . ')';
738 if($data_type eq 'geography'){
739 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
748 my ($from_field, $to_field) = @_;
750 die "Can't alter field in another table"
751 if($from_field->table->name ne $to_field->table->name);
755 # drop geometry column and constraints
756 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
757 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
759 # it's necessary to start with rename column cause this would affect
760 # all of the following statements which would be broken if do the
762 # BUT: drop geometry is done before the rename, cause it work's on the
763 # $from_field directly
764 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
765 $to_field->table->name,
767 $to_field->name) if($from_field->name ne $to_field->name);
769 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
770 $to_field->table->name,
771 $to_field->name) if(!$to_field->is_nullable and
772 $from_field->is_nullable);
774 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
775 $to_field->table->name,
777 if ( !$from_field->is_nullable and $to_field->is_nullable );
780 my $from_dt = convert_datatype($from_field);
781 my $to_dt = convert_datatype($to_field);
782 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
783 $to_field->table->name,
785 $to_dt) if($to_dt ne $from_dt);
787 my $old_default = $from_field->default_value;
788 my $new_default = $to_field->default_value;
789 my $default_value = $to_field->default_value;
791 # fixes bug where output like this was created:
792 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
793 if(ref $default_value eq "SCALAR" ) {
794 $default_value = $$default_value;
795 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
796 $default_value = __PACKAGE__->_quote_string($default_value);
799 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
800 $to_field->table->name,
803 if ( defined $new_default &&
804 (!defined $old_default || $old_default ne $new_default) );
806 # fixes bug where removing the DEFAULT statement of a column
807 # would result in no change
809 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
810 $to_field->table->name,
812 if ( !defined $new_default && defined $old_default );
814 # add geometry column and constraints
815 push @out, add_geometry_column($to_field) if is_geometry($to_field);
816 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
818 return wantarray ? @out : join(";\n", @out);
821 sub rename_field { alter_field(@_) }
825 my ($new_field) = @_;
827 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
828 $new_field->table->name,
829 create_field($new_field));
830 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
831 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
838 my ($old_field, $options) = @_;
840 my $generator = _generator($options);
842 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
843 $generator->quote($old_field->table->name),
844 $generator->quote($old_field->name));
845 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
849 sub add_geometry_column{
850 my ($field,$options) = @_;
852 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
854 $field->table->schema->name,
855 $options->{table} ? $options->{table} : $field->table->name,
857 $field->extra->{dimensions},
858 $field->extra->{srid},
859 $field->extra->{geometry_type});
863 sub drop_geometry_column
867 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
868 $field->table->schema->name,
874 sub add_geometry_constraints{
877 my @constraints = create_geometry_constraints($field);
879 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
884 sub drop_geometry_constraints{
887 my @constraints = create_geometry_constraints($field);
889 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
895 my ($to_table, $options) = @_;
896 my $generator = _generator($options);
897 my $out = sprintf('ALTER TABLE %s %s',
898 $generator->quote($to_table->name),
899 $options->{alter_table_action});
900 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
905 my ($old_table, $new_table, $options) = @_;
906 my $generator = _generator($options);
907 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
909 my @geometry_changes;
910 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
911 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
913 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
915 return alter_table($old_table, $options);
918 sub alter_create_index {
919 my ($index, $options) = @_;
920 my $generator = _generator($options);
921 my ($idef, $constraints) = create_index($index, {
922 generator => $generator,
924 return $index->type eq NORMAL ? $idef
925 : sprintf('ALTER TABLE %s ADD %s',
926 $generator->quote($index->table->name),
927 join(q{}, @$constraints)
931 sub alter_drop_index {
932 my ($index, $options) = @_;
933 my $index_name = $index->name;
934 return "DROP INDEX $index_name";
937 sub alter_drop_constraint {
938 my ($c, $options) = @_;
939 my $generator = _generator($options);
941 # attention: Postgres has a very special naming structure for naming
942 # foreign keys and primary keys. It names them using the name of the
943 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
946 # Already has a name, just quote it
947 $c_name = $generator->quote($c->name);
948 } elsif ( $c->type eq FOREIGN_KEY ) {
949 # Doesn't have a name, and is foreign key, append '_fkey'
950 $c_name = $generator->quote($c->table->name . '_' .
951 ($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 = $generator->quote($c->table->name . '_pkey');
958 'ALTER TABLE %s DROP CONSTRAINT %s',
959 $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 .= "\n".join("\n",@geometry_drops) if scalar(@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>.