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 );
58 double => 'double precision',
61 mediumint => 'integer',
62 smallint => 'smallint',
63 tinyint => 'smallint',
65 varchar => 'character varying',
72 mediumblob => 'bytea',
74 enum => 'character varying',
75 set => 'character varying',
77 datetime => 'timestamp',
79 timestamp => 'timestamp',
87 varchar2 => 'character varying',
97 varchar => 'character varying',
98 datetime => 'timestamp',
102 tinyint => 'smallint',
107 my %reserved = map { $_, 1 } qw[
108 ALL ANALYSE ANALYZE AND ANY AS ASC
110 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
111 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
112 DEFAULT DEFERRABLE DESC DISTINCT DO
114 FALSE FOR FOREIGN FREEZE FROM FULL
116 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
117 JOIN LEADING LEFT LIKE LIMIT
118 NATURAL NEW NOT NOTNULL NULL
119 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
120 PRIMARY PUBLIC REFERENCES RIGHT
121 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
122 UNION UNIQUE USER USING VERBOSE WHEN WHERE
125 # my $max_id_length = 62;
126 my %used_identifiers = ();
132 =head1 PostgreSQL Create Table Syntax
134 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
135 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
136 | table_constraint } [, ... ]
138 [ INHERITS ( parent_table [, ... ] ) ]
139 [ WITH OIDS | WITHOUT OIDS ]
141 where column_constraint is:
143 [ CONSTRAINT constraint_name ]
144 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
146 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
147 [ ON DELETE action ] [ ON UPDATE action ] }
148 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
150 and table_constraint is:
152 [ CONSTRAINT constraint_name ]
153 { UNIQUE ( column_name [, ... ] ) |
154 PRIMARY KEY ( column_name [, ... ] ) |
155 CHECK ( expression ) |
156 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
157 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
158 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
160 =head1 Create Index Syntax
162 CREATE [ UNIQUE ] INDEX index_name ON table
163 [ USING acc_method ] ( column [ ops_name ] [, ...] )
165 CREATE [ UNIQUE ] INDEX index_name ON table
166 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
172 my $translator = shift;
173 local $DEBUG = $translator->debug;
174 local $WARN = $translator->show_warnings;
175 my $no_comments = $translator->no_comments;
176 my $add_drop_table = $translator->add_drop_table;
177 my $schema = $translator->schema;
178 my $pargs = $translator->producer_args;
179 my $postgres_version = parse_dbms_version(
180 $pargs->{postgres_version}, 'perl'
183 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
186 push @output, header_comment unless ($no_comments);
188 my (@table_defs, @fks);
190 for my $table ( $schema->get_tables ) {
192 my ($table_def, $fks) = create_table($table, {
193 generator => $generator,
194 no_comments => $no_comments,
195 postgres_version => $postgres_version,
196 add_drop_table => $add_drop_table,
197 type_defs => \%type_defs,
200 push @table_defs, $table_def;
204 for my $view ( $schema->get_views ) {
205 push @table_defs, create_view($view, {
206 postgres_version => $postgres_version,
207 add_drop_view => $add_drop_table,
208 generator => $generator,
209 no_comments => $no_comments,
213 for my $trigger ( $schema->get_triggers ) {
214 push @table_defs, create_trigger( $trigger, {
215 add_drop_trigger => $add_drop_table,
216 no_comments => $no_comments,
220 push @output, map { "$_;\n\n" } values %type_defs;
221 push @output, map { "$_;\n\n" } @table_defs;
223 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
224 push @output, map { "$_;\n\n" } @fks;
229 warn "Truncated " . keys( %truncated ) . " names:\n";
230 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
236 : join ('', @output);
240 my $basename = shift || '';
241 my $type = shift || '';
242 my $scope = shift || '';
243 my $critical = shift || '';
244 my $basename_orig = $basename;
245 # my $max_id_length = 62;
247 ? $max_id_length - (length($type) + 1)
249 $basename = substr( $basename, 0, $max_name )
250 if length( $basename ) > $max_name;
251 my $name = $type ? "${type}_$basename" : $basename;
253 if ( $basename ne $basename_orig and $critical ) {
254 my $show_type = $type ? "+'$type'" : "";
255 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
256 "character limit to make '$name'\n" if $WARN;
257 $truncated{ $basename_orig } = $name;
260 $scope ||= \%global_names;
261 if ( my $prev = $scope->{ $name } ) {
262 my $name_orig = $name;
263 $name .= sprintf( "%02d", ++$prev );
264 substr($name, $max_id_length - 3) = "00"
265 if length( $name ) > $max_id_length;
267 warn "The name '$name_orig' has been changed to ",
268 "'$name' to make it unique.\n" if $WARN;
270 $scope->{ $name_orig }++;
280 return 1 if $field->data_type eq 'geometry';
286 return 1 if $field->data_type eq 'geography';
291 my ($table, $options) = @_;
293 my $generator = _generator($options);
294 my $no_comments = $options->{no_comments} || 0;
295 my $add_drop_table = $options->{add_drop_table} || 0;
296 my $postgres_version = $options->{postgres_version} || 0;
297 my $type_defs = $options->{type_defs} || {};
299 my $table_name = $table->name or next;
300 my $table_name_qt = $generator->quote($table_name);
302 # print STDERR "$table_name table_name\n";
303 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @fks );
305 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
307 if ( $table->comments and !$no_comments ){
308 my $c = "-- Comments: \n-- ";
309 $c .= join "\n-- ", $table->comments;
317 my %field_name_scope;
318 for my $field ( $table->get_fields ) {
319 push @field_defs, create_field($field, {
320 generator => $generator,
321 postgres_version => $postgres_version,
322 type_defs => $type_defs,
323 constraint_defs => \@constraint_defs,
331 # my $idx_name_default;
332 for my $index ( $table->get_indices ) {
333 my ($idef, $constraints) = create_index($index, {
334 generator => $generator,
336 $idef and push @index_defs, $idef;
337 push @constraint_defs, @$constraints;
344 for my $c ( $table->get_constraints ) {
345 my ($cdefs, $fks) = create_constraint($c, {
346 generator => $generator,
348 push @constraint_defs, @$cdefs;
355 if(exists $table->extra->{temporary}) {
356 $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
359 my $create_statement;
360 $create_statement = join("\n", @comments);
361 if ($add_drop_table) {
362 if ($postgres_version >= 8.002) {
363 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
365 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
368 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
369 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
372 $create_statement .= @index_defs ? ';' : q{};
373 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
374 . join(";\n", @index_defs);
379 if(grep { is_geometry($_) } $table->get_fields){
380 $create_statement .= ";";
381 my @geometry_columns;
382 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
383 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
384 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
387 return $create_statement, \@fks;
391 my ($view, $options) = @_;
392 my $generator = _generator($options);
393 my $postgres_version = $options->{postgres_version} || 0;
394 my $add_drop_view = $options->{add_drop_view};
396 my $view_name = $view->name;
397 debug("PKG: Looking at view '${view_name}'\n");
400 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
401 unless $options->{no_comments};
402 if ($add_drop_view) {
403 if ($postgres_version >= 8.002) {
404 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
406 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
411 my $extra = $view->extra;
412 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
413 $create .= " VIEW " . $generator->quote($view_name);
415 if ( my @fields = $view->fields ) {
416 my $field_list = join ', ', map { $generator->quote($_) } @fields;
417 $create .= " ( ${field_list} )";
420 if ( my $sql = $view->sql ) {
421 $create .= " AS\n ${sql}\n";
424 if ( $extra->{check_option} ) {
425 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
433 my %field_name_scope;
437 my ($field, $options) = @_;
439 my $generator = _generator($options);
440 my $table_name = $field->table->name;
441 my $constraint_defs = $options->{constraint_defs} || [];
442 my $postgres_version = $options->{postgres_version} || 0;
443 my $type_defs = $options->{type_defs} || {};
445 $field_name_scope{$table_name} ||= {};
446 my $field_name = $field->name;
447 my $field_comments = $field->comments
448 ? "-- " . $field->comments . "\n "
451 my $field_def = $field_comments . $generator->quote($field_name);
456 my @size = $field->size;
457 my $data_type = lc $field->data_type;
458 my %extra = $field->extra;
459 my $list = $extra{'list'} || [];
460 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
462 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
463 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
464 $field_def .= ' '. $type_name;
465 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
466 "CREATE TYPE $type_name AS ENUM ($commalist)";
467 if (! exists $type_defs->{$type_name} ) {
468 $type_defs->{$type_name} = $new_type_def;
469 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
470 die "Attempted to redefine type name '$type_name' as a different type.\n";
473 $field_def .= ' '. convert_datatype($field);
479 __PACKAGE__->_apply_default_value(
485 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
490 # Not null constraint
492 $field_def .= ' NOT NULL' unless $field->is_nullable;
495 # Geometry constraints
497 if(is_geometry($field)){
498 foreach ( create_geometry_constraints($field) ) {
499 my ($cdefs, $fks) = create_constraint($_, {
500 generator => $generator,
502 push @$constraint_defs, @$cdefs;
511 sub create_geometry_constraints{
515 push @constraints, SQL::Translator::Schema::Constraint->new(
516 name => "enforce_dims_".$field->name,
517 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
518 table => $field->table,
522 push @constraints, SQL::Translator::Schema::Constraint->new(
523 name => "enforce_srid_".$field->name,
524 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
525 table => $field->table,
528 push @constraints, SQL::Translator::Schema::Constraint->new(
529 name => "enforce_geotype_".$field->name,
530 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
531 table => $field->table,
540 my ($index, $options) = @_;
542 my $generator = _generator($options);
543 my $table_name = $index->table->name;
545 my ($index_def, @constraint_defs);
549 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
551 my $type = $index->type || NORMAL;
552 my @fields = $index->fields;
553 return unless @fields;
555 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
556 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
557 if ( $type eq PRIMARY_KEY ) {
558 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
560 elsif ( $type eq UNIQUE ) {
561 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
563 elsif ( $type eq NORMAL ) {
565 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' . $field_names
569 warn "Unknown index type ($type) on table $table_name.\n"
573 return $index_def, \@constraint_defs;
576 sub create_constraint
578 my ($c, $options) = @_;
580 my $generator = _generator($options);
581 my $table_name = $c->table->name;
582 my (@constraint_defs, @fks);
584 my $name = $c->name || '';
586 my @fields = grep { defined } $c->fields;
588 my @rfields = grep { defined } $c->reference_fields;
590 next if !@fields && $c->type ne CHECK_C;
591 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
592 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
593 if ( $c->type eq PRIMARY_KEY ) {
594 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
596 elsif ( $c->type eq UNIQUE ) {
597 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
599 elsif ( $c->type eq CHECK_C ) {
600 my $expression = $c->expression;
601 push @constraint_defs, "${def_start}CHECK ($expression)";
603 elsif ( $c->type eq FOREIGN_KEY ) {
604 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
605 . "\n REFERENCES " . $generator->quote($c->reference_table);
608 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
611 if ( $c->match_type ) {
613 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
616 if ( $c->on_delete ) {
617 $def .= ' ON DELETE '. $c->on_delete;
620 if ( $c->on_update ) {
621 $def .= ' ON UPDATE '. $c->on_update;
624 if ( $c->deferrable ) {
625 $def .= ' DEFERRABLE';
631 return \@constraint_defs, \@fks;
635 my ($trigger,$options) = @_;
639 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $trigger->name )
640 if $options->{add_drop_trigger};
642 my $scope = $trigger->scope || '';
643 $scope = " FOR EACH $scope" if $scope;
645 push @statements, sprintf(
646 'CREATE TRIGGER %s %s %s ON %s%s %s',
648 $trigger->perform_action_when,
649 join( ' OR ', @{ $trigger->database_events } ),
662 my @size = $field->size;
663 my $data_type = lc $field->data_type;
664 my $array = $data_type =~ s/\[\]$//;
666 if ( $data_type eq 'enum' ) {
668 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
669 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
670 # push @$constraint_defs,
671 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
672 # qq[IN ($commalist))];
673 $data_type = 'character varying';
675 elsif ( $data_type eq 'set' ) {
676 $data_type = 'character varying';
678 elsif ( $field->is_auto_increment ) {
679 if ( defined $size[0] && $size[0] > 11 ) {
680 $data_type = 'bigserial';
683 $data_type = 'serial';
688 $data_type = defined $translate{ $data_type } ?
689 $translate{ $data_type } :
693 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
694 if ( defined $size[0] && $size[0] > 6 ) {
699 if ( $data_type eq 'integer' ) {
700 if ( defined $size[0] && $size[0] > 0) {
701 if ( $size[0] > 10 ) {
702 $data_type = 'bigint';
704 elsif ( $size[0] < 5 ) {
705 $data_type = 'smallint';
708 $data_type = 'integer';
712 $data_type = 'integer';
716 my $type_with_size = join('|',
717 'bit', 'varbit', 'character', 'bit varying', 'character varying',
718 'time', 'timestamp', 'interval', 'numeric', 'float'
721 if ( $data_type !~ /$type_with_size/ ) {
725 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
726 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
727 $data_type .= $2 if(defined $2);
728 } elsif ( defined $size[0] && $size[0] > 0 ) {
729 $data_type .= '(' . join( ',', @size ) . ')';
739 if($data_type eq 'geography'){
740 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
749 my ($from_field, $to_field) = @_;
751 die "Can't alter field in another table"
752 if($from_field->table->name ne $to_field->table->name);
756 # drop geometry column and constraints
757 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
758 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
760 # it's necessary to start with rename column cause this would affect
761 # all of the following statements which would be broken if do the
763 # BUT: drop geometry is done before the rename, cause it work's on the
764 # $from_field directly
765 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
766 $to_field->table->name,
768 $to_field->name) if($from_field->name ne $to_field->name);
770 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
771 $to_field->table->name,
772 $to_field->name) if(!$to_field->is_nullable and
773 $from_field->is_nullable);
775 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
776 $to_field->table->name,
778 if ( !$from_field->is_nullable and $to_field->is_nullable );
781 my $from_dt = convert_datatype($from_field);
782 my $to_dt = convert_datatype($to_field);
783 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
784 $to_field->table->name,
786 $to_dt) if($to_dt ne $from_dt);
788 my $old_default = $from_field->default_value;
789 my $new_default = $to_field->default_value;
790 my $default_value = $to_field->default_value;
792 # fixes bug where output like this was created:
793 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
794 if(ref $default_value eq "SCALAR" ) {
795 $default_value = $$default_value;
796 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
797 $default_value = __PACKAGE__->_quote_string($default_value);
800 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
801 $to_field->table->name,
804 if ( defined $new_default &&
805 (!defined $old_default || $old_default ne $new_default) );
807 # fixes bug where removing the DEFAULT statement of a column
808 # would result in no change
810 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
811 $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) = @_;
828 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
829 $new_field->table->name,
830 create_field($new_field));
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) = @_;
853 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
855 $field->table->schema->name,
856 $options->{table} ? $options->{table} : $field->table->name,
858 $field->extra->{dimensions},
859 $field->extra->{srid},
860 $field->extra->{geometry_type});
864 sub drop_geometry_column
868 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
869 $field->table->schema->name,
875 sub add_geometry_constraints{
878 my @constraints = create_geometry_constraints($field);
880 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
885 sub drop_geometry_constraints{
888 my @constraints = create_geometry_constraints($field);
890 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
896 my ($to_table, $options) = @_;
897 my $generator = _generator($options);
898 my $out = sprintf('ALTER TABLE %s %s',
899 $generator->quote($to_table->name),
900 $options->{alter_table_action});
901 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
906 my ($old_table, $new_table, $options) = @_;
907 my $generator = _generator($options);
908 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
910 my @geometry_changes;
911 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
912 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
914 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
916 return alter_table($old_table, $options);
919 sub alter_create_index {
920 my ($index, $options) = @_;
921 my $generator = _generator($options);
922 my ($idef, $constraints) = create_index($index, {
923 generator => $generator,
925 return $index->type eq NORMAL ? $idef
926 : sprintf('ALTER TABLE %s ADD %s',
927 $generator->quote($index->table->name),
928 join(q{}, @$constraints)
932 sub alter_drop_index {
933 my ($index, $options) = @_;
934 my $index_name = $index->name;
935 return "DROP INDEX $index_name";
938 sub alter_drop_constraint {
939 my ($c, $options) = @_;
940 my $generator = _generator($options);
942 # attention: Postgres has a very special naming structure for naming
943 # foreign keys and primary keys. It names them using the name of the
944 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
947 # Already has a name, just quote it
948 $c_name = $generator->quote($c->name);
949 } elsif ( $c->type eq FOREIGN_KEY ) {
950 # Doesn't have a name, and is foreign key, append '_fkey'
951 $c_name = $generator->quote($c->table->name . '_' .
952 ($c->fields)[0] . '_fkey');
953 } elsif ( $c->type eq PRIMARY_KEY ) {
954 # Doesn't have a name, and is primary key, append '_pkey'
955 $c_name = $generator->quote($c->table->name . '_pkey');
959 'ALTER TABLE %s DROP CONSTRAINT %s',
960 $generator->quote($c->table->name), $c_name
964 sub alter_create_constraint {
965 my ($index, $options) = @_;
966 my $generator = _generator($options);
967 my ($defs, $fks) = create_constraint(@_);
969 # return if there are no constraint definitions so we don't run
970 # into output like this:
971 # ALTER TABLE users ADD ;
973 return unless(@{$defs} || @{$fks});
974 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
975 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
976 'ADD', join(q{}, @{$defs}, @{$fks})
981 my ($table, $options) = @_;
982 my $generator = _generator($options);
983 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
985 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
987 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
991 sub batch_alter_table {
992 my ( $table, $diff_hash, $options ) = @_;
994 # as long as we're not renaming the table we don't need to be here
995 if ( @{$diff_hash->{rename_table}} == 0 ) {
996 return batch_alter_table_statements($diff_hash, $options);
999 # first we need to perform drops which are on old table
1000 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
1001 alter_drop_constraint
1006 # next comes the rename_table
1007 my $old_table = $diff_hash->{rename_table}[0][0];
1008 push @sql, rename_table( $old_table, $table, $options );
1010 # for alter_field (and so also rename_field) we need to make sure old
1011 # field has table name set to new table otherwise calling alter_field dies
1012 $diff_hash->{alter_field} =
1013 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1014 $diff_hash->{rename_field} =
1015 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1017 # now add everything else
1018 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1023 alter_create_constraint
1032 # -------------------------------------------------------------------
1033 # Life is full of misery, loneliness, and suffering --
1034 # and it's all over much too soon.
1036 # -------------------------------------------------------------------
1042 SQL::Translator, SQL::Translator::Producer::Oracle.
1046 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.