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(
50 my ( %translate, %index_name );
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',
96 my %reserved = map { $_, 1 } qw[
97 ALL ANALYSE ANALYZE AND ANY AS ASC
99 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
100 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
101 DEFAULT DEFERRABLE DESC DISTINCT DO
103 FALSE FOR FOREIGN FREEZE FROM FULL
105 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
106 JOIN LEADING LEFT LIKE LIMIT
107 NATURAL NEW NOT NOTNULL NULL
108 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
109 PRIMARY PUBLIC REFERENCES RIGHT
110 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
111 UNION UNIQUE USER USING VERBOSE WHEN WHERE
114 my %used_identifiers = ();
120 =head1 PostgreSQL Create Table Syntax
122 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
123 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
124 | table_constraint } [, ... ]
126 [ INHERITS ( parent_table [, ... ] ) ]
127 [ WITH OIDS | WITHOUT OIDS ]
129 where column_constraint is:
131 [ CONSTRAINT constraint_name ]
132 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
134 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
135 [ ON DELETE action ] [ ON UPDATE action ] }
136 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
138 and table_constraint is:
140 [ CONSTRAINT constraint_name ]
141 { UNIQUE ( column_name [, ... ] ) |
142 PRIMARY KEY ( column_name [, ... ] ) |
143 CHECK ( expression ) |
144 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
145 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
146 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
148 =head1 Create Index Syntax
150 CREATE [ UNIQUE ] INDEX index_name ON table
151 [ USING acc_method ] ( column [ ops_name ] [, ...] )
153 CREATE [ UNIQUE ] INDEX index_name ON table
154 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
160 my $translator = shift;
161 local $DEBUG = $translator->debug;
162 local $WARN = $translator->show_warnings;
163 my $no_comments = $translator->no_comments;
164 my $add_drop_table = $translator->add_drop_table;
165 my $schema = $translator->schema;
166 my $pargs = $translator->producer_args;
167 my $postgres_version = parse_dbms_version(
168 $pargs->{postgres_version}, 'perl'
171 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
174 push @output, header_comment unless ($no_comments);
176 my (@table_defs, @fks);
178 for my $table ( $schema->get_tables ) {
180 my ($table_def, $fks) = create_table($table, {
181 generator => $generator,
182 no_comments => $no_comments,
183 postgres_version => $postgres_version,
184 add_drop_table => $add_drop_table,
185 type_defs => \%type_defs,
188 push @table_defs, $table_def;
192 for my $view ( $schema->get_views ) {
193 push @table_defs, create_view($view, {
194 postgres_version => $postgres_version,
195 add_drop_view => $add_drop_table,
196 generator => $generator,
197 no_comments => $no_comments,
201 for my $trigger ( $schema->get_triggers ) {
202 push @table_defs, create_trigger( $trigger, {
203 add_drop_trigger => $add_drop_table,
204 generator => $generator,
205 no_comments => $no_comments,
209 push @output, map { "$_;\n\n" } values %type_defs;
210 push @output, map { "$_;\n\n" } @table_defs;
212 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
213 push @output, map { "$_;\n\n" } @fks;
218 warn "Truncated " . keys( %truncated ) . " names:\n";
219 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
225 : join ('', @output);
229 my $basename = shift || '';
230 my $type = shift || '';
231 my $scope = shift || '';
232 my $critical = shift || '';
233 my $basename_orig = $basename;
236 ? MAX_ID_LENGTH - (length($type) + 1)
238 $basename = substr( $basename, 0, $max_name )
239 if length( $basename ) > $max_name;
240 my $name = $type ? "${type}_$basename" : $basename;
242 if ( $basename ne $basename_orig and $critical ) {
243 my $show_type = $type ? "+'$type'" : "";
244 warn "Truncating '$basename_orig'$show_type to ", MAX_ID_LENGTH,
245 " character limit to make '$name'\n" if $WARN;
246 $truncated{ $basename_orig } = $name;
249 $scope ||= \%global_names;
250 if ( my $prev = $scope->{ $name } ) {
251 my $name_orig = $name;
252 $name .= sprintf( "%02d", ++$prev );
253 substr($name, MAX_ID_LENGTH - 3) = "00"
254 if length( $name ) > MAX_ID_LENGTH;
256 warn "The name '$name_orig' has been changed to ",
257 "'$name' to make it unique.\n" if $WARN;
259 $scope->{ $name_orig }++;
269 return 1 if $field->data_type eq 'geometry';
275 return 1 if $field->data_type eq 'geography';
280 my ($table, $options) = @_;
282 my $generator = _generator($options);
283 my $no_comments = $options->{no_comments} || 0;
284 my $add_drop_table = $options->{add_drop_table} || 0;
285 my $postgres_version = $options->{postgres_version} || 0;
286 my $type_defs = $options->{type_defs} || {};
288 my $table_name = $table->name or next;
289 my $table_name_qt = $generator->quote($table_name);
291 # print STDERR "$table_name table_name\n";
292 my ( @comments, @field_defs, @index_defs, @sequence_defs, @constraint_defs, @fks );
294 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
296 if ( !$no_comments and my $comments = $table->comments ) {
297 $comments =~ s/^/-- /mg;
298 push @comments, "-- Comments:\n$comments\n--\n";
304 for my $field ( $table->get_fields ) {
305 push @field_defs, create_field($field, {
306 generator => $generator,
307 postgres_version => $postgres_version,
308 type_defs => $type_defs,
309 constraint_defs => \@constraint_defs,
316 for my $index ( $table->get_indices ) {
317 my ($idef, $constraints) = create_index($index, {
318 generator => $generator,
320 $idef and push @index_defs, $idef;
321 push @constraint_defs, @$constraints;
327 for my $c ( $table->get_constraints ) {
328 my ($cdefs, $fks) = create_constraint($c, {
329 generator => $generator,
331 push @constraint_defs, @$cdefs;
336 my $create_statement = join("\n", @comments);
337 if ($add_drop_table) {
338 if ($postgres_version >= 8.002) {
339 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
341 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
344 my $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
345 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
346 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
349 $create_statement .= @index_defs ? ';' : q{};
350 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
351 . join(";\n", @index_defs);
356 if(grep { is_geometry($_) } $table->get_fields){
357 $create_statement .= ";";
358 my @geometry_columns;
359 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
360 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
361 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
364 return $create_statement, \@fks;
368 my ($view, $options) = @_;
369 my $generator = _generator($options);
370 my $postgres_version = $options->{postgres_version} || 0;
371 my $add_drop_view = $options->{add_drop_view};
373 my $view_name = $view->name;
374 debug("PKG: Looking at view '${view_name}'\n");
377 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
378 unless $options->{no_comments};
379 if ($add_drop_view) {
380 if ($postgres_version >= 8.002) {
381 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
383 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
388 my $extra = $view->extra;
389 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
390 $create .= " VIEW " . $generator->quote($view_name);
392 if ( my @fields = $view->fields ) {
393 my $field_list = join ', ', map { $generator->quote($_) } @fields;
394 $create .= " ( ${field_list} )";
397 if ( my $sql = $view->sql ) {
398 $create .= " AS\n ${sql}\n";
401 if ( $extra->{check_option} ) {
402 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
410 my %field_name_scope;
414 my ($field, $options) = @_;
416 my $generator = _generator($options);
417 my $table_name = $field->table->name;
418 my $constraint_defs = $options->{constraint_defs} || [];
419 my $postgres_version = $options->{postgres_version} || 0;
420 my $type_defs = $options->{type_defs} || {};
422 $field_name_scope{$table_name} ||= {};
423 my $field_name = $field->name;
424 my $field_comments = '';
425 if (my $comments = $field->comments) {
426 $comments =~ s/(?<!\A)^/ -- /mg;
427 $field_comments = "-- $comments\n ";
430 my $field_def = $field_comments . $generator->quote($field_name);
435 my @size = $field->size;
436 my $data_type = lc $field->data_type;
437 my %extra = $field->extra;
438 my $list = $extra{'list'} || [];
439 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
441 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
442 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
443 $field_def .= ' '. $type_name;
444 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
445 "CREATE TYPE $type_name AS ENUM ($commalist)";
446 if (! exists $type_defs->{$type_name} ) {
447 $type_defs->{$type_name} = $new_type_def;
448 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
449 die "Attempted to redefine type name '$type_name' as a different type.\n";
452 $field_def .= ' '. convert_datatype($field);
458 __PACKAGE__->_apply_default_value(
464 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
469 # Not null constraint
471 $field_def .= ' NOT NULL' unless $field->is_nullable;
474 # Geometry constraints
476 if(is_geometry($field)){
477 foreach ( create_geometry_constraints($field) ) {
478 my ($cdefs, $fks) = create_constraint($_, {
479 generator => $generator,
481 push @$constraint_defs, @$cdefs;
490 sub create_geometry_constraints{
494 push @constraints, SQL::Translator::Schema::Constraint->new(
495 name => "enforce_dims_".$field->name,
496 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
497 table => $field->table,
501 push @constraints, SQL::Translator::Schema::Constraint->new(
502 name => "enforce_srid_".$field->name,
503 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
504 table => $field->table,
507 push @constraints, SQL::Translator::Schema::Constraint->new(
508 name => "enforce_geotype_".$field->name,
509 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
510 table => $field->table,
519 my ($index, $options) = @_;
521 my $generator = _generator($options);
522 my $table_name = $index->table->name;
524 my ($index_def, @constraint_defs);
528 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
530 my $type = $index->type || NORMAL;
531 my @fields = $index->fields;
532 return unless @fields;
536 for my $opt ( $index->options ) {
537 if ( ref $opt eq 'HASH' ) {
538 foreach my $key (keys %$opt) {
539 my $value = $opt->{$key};
540 next unless defined $value;
541 if ( uc($key) eq 'USING' ) {
542 $index_using = "USING $value";
544 elsif ( uc($key) eq 'WHERE' ) {
545 $index_where = "WHERE $value";
551 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
552 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
553 if ( $type eq PRIMARY_KEY ) {
554 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
556 elsif ( $type eq UNIQUE ) {
557 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
559 elsif ( $type eq NORMAL ) {
561 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' .
562 join ' ', grep { defined } $index_using, $field_names, $index_where;
565 warn "Unknown index type ($type) on table $table_name.\n"
569 return $index_def, \@constraint_defs;
572 sub create_constraint
574 my ($c, $options) = @_;
576 my $generator = _generator($options);
577 my $table_name = $c->table->name;
578 my (@constraint_defs, @fks);
580 my $name = $c->name || '';
582 my @fields = grep { defined } $c->fields;
584 my @rfields = grep { defined } $c->reference_fields;
586 next if !@fields && $c->type ne CHECK_C;
587 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
588 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
589 if ( $c->type eq PRIMARY_KEY ) {
590 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
592 elsif ( $c->type eq UNIQUE ) {
593 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
595 elsif ( $c->type eq CHECK_C ) {
596 my $expression = $c->expression;
597 push @constraint_defs, "${def_start}CHECK ($expression)";
599 elsif ( $c->type eq FOREIGN_KEY ) {
600 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
601 . "\n REFERENCES " . $generator->quote($c->reference_table);
604 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
607 if ( $c->match_type ) {
609 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
612 if ( $c->on_delete ) {
613 $def .= ' ON DELETE '. $c->on_delete;
616 if ( $c->on_update ) {
617 $def .= ' ON UPDATE '. $c->on_update;
620 if ( $c->deferrable ) {
621 $def .= ' DEFERRABLE';
627 return \@constraint_defs, \@fks;
631 my ($trigger,$options) = @_;
632 my $generator = _generator($options);
636 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
637 if $options->{add_drop_trigger};
639 my $scope = $trigger->scope || '';
640 $scope = " FOR EACH $scope" if $scope;
642 push @statements, sprintf(
643 'CREATE TRIGGER %s %s %s ON %s%s %s',
644 $generator->quote($trigger->name),
645 $trigger->perform_action_when,
646 join( ' OR ', @{ $trigger->database_events } ),
647 $generator->quote($trigger->on_table),
659 my @size = $field->size;
660 my $data_type = lc $field->data_type;
661 my $array = $data_type =~ s/\[\]$//;
663 if ( $data_type eq 'enum' ) {
665 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
666 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
667 # push @$constraint_defs,
668 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
669 # qq[IN ($commalist))];
670 $data_type = 'character varying';
672 elsif ( $data_type eq 'set' ) {
673 $data_type = 'character varying';
675 elsif ( $field->is_auto_increment ) {
676 if ( defined $size[0] && $size[0] > 11 ) {
677 $data_type = 'bigserial';
680 $data_type = 'serial';
685 $data_type = defined $translate{ lc $data_type } ?
686 $translate{ lc $data_type } :
690 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
691 if ( defined $size[0] && $size[0] > 6 ) {
696 if ( $data_type eq 'integer' ) {
697 if ( defined $size[0] && $size[0] > 0) {
698 if ( $size[0] > 10 ) {
699 $data_type = 'bigint';
701 elsif ( $size[0] < 5 ) {
702 $data_type = 'smallint';
705 $data_type = 'integer';
709 $data_type = 'integer';
713 my $type_with_size = join('|',
714 'bit', 'varbit', 'character', 'bit varying', 'character varying',
715 'time', 'timestamp', 'interval', 'numeric', 'float'
718 if ( $data_type !~ /$type_with_size/ ) {
722 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
723 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
724 $data_type .= $2 if(defined $2);
725 } elsif ( defined $size[0] && $size[0] > 0 ) {
726 $data_type .= '(' . join( ',', @size ) . ')';
736 if($data_type eq 'geography'){
737 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
746 my ($from_field, $to_field) = @_;
748 die "Can't alter field in another table"
749 if($from_field->table->name ne $to_field->table->name);
753 # drop geometry column and constraints
754 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
755 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
757 # it's necessary to start with rename column cause this would affect
758 # all of the following statements which would be broken if do the
760 # BUT: drop geometry is done before the rename, cause it work's on the
761 # $from_field directly
762 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
763 $to_field->table->name,
765 $to_field->name) if($from_field->name ne $to_field->name);
767 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
768 $to_field->table->name,
769 $to_field->name) if(!$to_field->is_nullable and
770 $from_field->is_nullable);
772 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
773 $to_field->table->name,
775 if ( !$from_field->is_nullable and $to_field->is_nullable );
778 my $from_dt = convert_datatype($from_field);
779 my $to_dt = convert_datatype($to_field);
780 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
781 $to_field->table->name,
783 $to_dt) if($to_dt ne $from_dt);
785 my $old_default = $from_field->default_value;
786 my $new_default = $to_field->default_value;
787 my $default_value = $to_field->default_value;
789 # fixes bug where output like this was created:
790 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
791 if(ref $default_value eq "SCALAR" ) {
792 $default_value = $$default_value;
793 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
794 $default_value = __PACKAGE__->_quote_string($default_value);
797 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
798 $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 $to_field->table->name,
810 if ( !defined $new_default && defined $old_default );
812 # add geometry column and constraints
813 push @out, add_geometry_column($to_field) if is_geometry($to_field);
814 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
816 return wantarray ? @out : join(";\n", @out);
819 sub rename_field { alter_field(@_) }
823 my ($new_field) = @_;
825 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
826 $new_field->table->name,
827 create_field($new_field));
828 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
829 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
836 my ($old_field, $options) = @_;
838 my $generator = _generator($options);
840 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
841 $generator->quote($old_field->table->name),
842 $generator->quote($old_field->name));
843 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
847 sub add_geometry_column{
848 my ($field,$options) = @_;
850 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
852 $field->table->schema->name,
853 $options->{table} ? $options->{table} : $field->table->name,
855 $field->extra->{dimensions},
856 $field->extra->{srid},
857 $field->extra->{geometry_type});
861 sub drop_geometry_column
865 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
866 $field->table->schema->name,
872 sub add_geometry_constraints{
875 my @constraints = create_geometry_constraints($field);
877 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
882 sub drop_geometry_constraints{
885 my @constraints = create_geometry_constraints($field);
887 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
893 my ($to_table, $options) = @_;
894 my $generator = _generator($options);
895 my $out = sprintf('ALTER TABLE %s %s',
896 $generator->quote($to_table->name),
897 $options->{alter_table_action});
898 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
903 my ($old_table, $new_table, $options) = @_;
904 my $generator = _generator($options);
905 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
907 my @geometry_changes;
908 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
909 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
911 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
913 return alter_table($old_table, $options);
916 sub alter_create_index {
917 my ($index, $options) = @_;
918 my $generator = _generator($options);
919 my ($idef, $constraints) = create_index($index, {
920 generator => $generator,
922 return $index->type eq NORMAL ? $idef
923 : sprintf('ALTER TABLE %s ADD %s',
924 $generator->quote($index->table->name),
925 join(q{}, @$constraints)
929 sub alter_drop_index {
930 my ($index, $options) = @_;
931 my $index_name = $index->name;
932 return "DROP INDEX $index_name";
935 sub alter_drop_constraint {
936 my ($c, $options) = @_;
937 my $generator = _generator($options);
939 # attention: Postgres has a very special naming structure for naming
940 # foreign keys and primary keys. It names them using the name of the
941 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
944 # Already has a name, just quote it
945 $c_name = $generator->quote($c->name);
946 } elsif ( $c->type eq FOREIGN_KEY ) {
947 # Doesn't have a name, and is foreign key, append '_fkey'
948 $c_name = $generator->quote($c->table->name . '_' .
949 ($c->fields)[0] . '_fkey');
950 } elsif ( $c->type eq PRIMARY_KEY ) {
951 # Doesn't have a name, and is primary key, append '_pkey'
952 $c_name = $generator->quote($c->table->name . '_pkey');
956 'ALTER TABLE %s DROP CONSTRAINT %s',
957 $generator->quote($c->table->name), $c_name
961 sub alter_create_constraint {
962 my ($index, $options) = @_;
963 my $generator = _generator($options);
964 my ($defs, $fks) = create_constraint(@_);
966 # return if there are no constraint definitions so we don't run
967 # into output like this:
968 # ALTER TABLE users ADD ;
970 return unless(@{$defs} || @{$fks});
971 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
972 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
973 'ADD', join(q{}, @{$defs}, @{$fks})
978 my ($table, $options) = @_;
979 my $generator = _generator($options);
980 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
982 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
984 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
988 sub batch_alter_table {
989 my ( $table, $diff_hash, $options ) = @_;
991 # as long as we're not renaming the table we don't need to be here
992 if ( @{$diff_hash->{rename_table}} == 0 ) {
993 return batch_alter_table_statements($diff_hash, $options);
996 # first we need to perform drops which are on old table
997 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
998 alter_drop_constraint
1003 # next comes the rename_table
1004 my $old_table = $diff_hash->{rename_table}[0][0];
1005 push @sql, rename_table( $old_table, $table, $options );
1007 # for alter_field (and so also rename_field) we need to make sure old
1008 # field has table name set to new table otherwise calling alter_field dies
1009 $diff_hash->{alter_field} =
1010 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1011 $diff_hash->{rename_field} =
1012 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1014 # now add everything else
1015 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1020 alter_create_constraint
1029 # -------------------------------------------------------------------
1030 # Life is full of misery, loneliness, and suffering --
1031 # and it's all over much too soon.
1033 # -------------------------------------------------------------------
1039 SQL::Translator, SQL::Translator::Producer::Oracle.
1043 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.