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;
546 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
547 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
548 if ( $type eq PRIMARY_KEY ) {
549 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
551 elsif ( $type eq UNIQUE ) {
552 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
554 elsif ( $type eq NORMAL ) {
556 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' . $field_names
560 warn "Unknown index type ($type) on table $table_name.\n"
564 return $index_def, \@constraint_defs;
567 sub create_constraint
569 my ($c, $options) = @_;
571 my $generator = _generator($options);
572 my $table_name = $c->table->name;
573 my (@constraint_defs, @fks);
575 my $name = $c->name || '';
577 my @fields = grep { defined } $c->fields;
579 my @rfields = grep { defined } $c->reference_fields;
581 next if !@fields && $c->type ne CHECK_C;
582 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
583 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
584 if ( $c->type eq PRIMARY_KEY ) {
585 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
587 elsif ( $c->type eq UNIQUE ) {
588 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
590 elsif ( $c->type eq CHECK_C ) {
591 my $expression = $c->expression;
592 push @constraint_defs, "${def_start}CHECK ($expression)";
594 elsif ( $c->type eq FOREIGN_KEY ) {
595 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
596 . "\n REFERENCES " . $generator->quote($c->reference_table);
599 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
602 if ( $c->match_type ) {
604 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
607 if ( $c->on_delete ) {
608 $def .= ' ON DELETE '. $c->on_delete;
611 if ( $c->on_update ) {
612 $def .= ' ON UPDATE '. $c->on_update;
615 if ( $c->deferrable ) {
616 $def .= ' DEFERRABLE';
622 return \@constraint_defs, \@fks;
626 my ($trigger,$options) = @_;
627 my $generator = _generator($options);
631 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
632 if $options->{add_drop_trigger};
634 my $scope = $trigger->scope || '';
635 $scope = " FOR EACH $scope" if $scope;
637 push @statements, sprintf(
638 'CREATE TRIGGER %s %s %s ON %s%s %s',
639 $generator->quote($trigger->name),
640 $trigger->perform_action_when,
641 join( ' OR ', @{ $trigger->database_events } ),
642 $generator->quote($trigger->on_table),
654 my @size = $field->size;
655 my $data_type = lc $field->data_type;
656 my $array = $data_type =~ s/\[\]$//;
658 if ( $data_type eq 'enum' ) {
660 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
661 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
662 # push @$constraint_defs,
663 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
664 # qq[IN ($commalist))];
665 $data_type = 'character varying';
667 elsif ( $data_type eq 'set' ) {
668 $data_type = 'character varying';
670 elsif ( $field->is_auto_increment ) {
671 if ( defined $size[0] && $size[0] > 11 ) {
672 $data_type = 'bigserial';
675 $data_type = 'serial';
680 $data_type = defined $translate{ lc $data_type } ?
681 $translate{ lc $data_type } :
685 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
686 if ( defined $size[0] && $size[0] > 6 ) {
691 if ( $data_type eq 'integer' ) {
692 if ( defined $size[0] && $size[0] > 0) {
693 if ( $size[0] > 10 ) {
694 $data_type = 'bigint';
696 elsif ( $size[0] < 5 ) {
697 $data_type = 'smallint';
700 $data_type = 'integer';
704 $data_type = 'integer';
708 my $type_with_size = join('|',
709 'bit', 'varbit', 'character', 'bit varying', 'character varying',
710 'time', 'timestamp', 'interval', 'numeric', 'float'
713 if ( $data_type !~ /$type_with_size/ ) {
717 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
718 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
719 $data_type .= $2 if(defined $2);
720 } elsif ( defined $size[0] && $size[0] > 0 ) {
721 $data_type .= '(' . join( ',', @size ) . ')';
731 if($data_type eq 'geography'){
732 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
741 my ($from_field, $to_field) = @_;
743 die "Can't alter field in another table"
744 if($from_field->table->name ne $to_field->table->name);
748 # drop geometry column and constraints
749 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
750 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
752 # it's necessary to start with rename column cause this would affect
753 # all of the following statements which would be broken if do the
755 # BUT: drop geometry is done before the rename, cause it work's on the
756 # $from_field directly
757 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
758 $to_field->table->name,
760 $to_field->name) if($from_field->name ne $to_field->name);
762 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
763 $to_field->table->name,
764 $to_field->name) if(!$to_field->is_nullable and
765 $from_field->is_nullable);
767 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
768 $to_field->table->name,
770 if ( !$from_field->is_nullable and $to_field->is_nullable );
773 my $from_dt = convert_datatype($from_field);
774 my $to_dt = convert_datatype($to_field);
775 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
776 $to_field->table->name,
778 $to_dt) if($to_dt ne $from_dt);
780 my $old_default = $from_field->default_value;
781 my $new_default = $to_field->default_value;
782 my $default_value = $to_field->default_value;
784 # fixes bug where output like this was created:
785 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
786 if(ref $default_value eq "SCALAR" ) {
787 $default_value = $$default_value;
788 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
789 $default_value = __PACKAGE__->_quote_string($default_value);
792 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
793 $to_field->table->name,
796 if ( defined $new_default &&
797 (!defined $old_default || $old_default ne $new_default) );
799 # fixes bug where removing the DEFAULT statement of a column
800 # would result in no change
802 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
803 $to_field->table->name,
805 if ( !defined $new_default && defined $old_default );
807 # add geometry column and constraints
808 push @out, add_geometry_column($to_field) if is_geometry($to_field);
809 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
811 return wantarray ? @out : join(";\n", @out);
814 sub rename_field { alter_field(@_) }
818 my ($new_field) = @_;
820 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
821 $new_field->table->name,
822 create_field($new_field));
823 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
824 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
831 my ($old_field, $options) = @_;
833 my $generator = _generator($options);
835 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
836 $generator->quote($old_field->table->name),
837 $generator->quote($old_field->name));
838 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
842 sub add_geometry_column{
843 my ($field,$options) = @_;
845 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
847 $field->table->schema->name,
848 $options->{table} ? $options->{table} : $field->table->name,
850 $field->extra->{dimensions},
851 $field->extra->{srid},
852 $field->extra->{geometry_type});
856 sub drop_geometry_column
860 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
861 $field->table->schema->name,
867 sub add_geometry_constraints{
870 my @constraints = create_geometry_constraints($field);
872 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
877 sub drop_geometry_constraints{
880 my @constraints = create_geometry_constraints($field);
882 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
888 my ($to_table, $options) = @_;
889 my $generator = _generator($options);
890 my $out = sprintf('ALTER TABLE %s %s',
891 $generator->quote($to_table->name),
892 $options->{alter_table_action});
893 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
898 my ($old_table, $new_table, $options) = @_;
899 my $generator = _generator($options);
900 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
902 my @geometry_changes;
903 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
904 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
906 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
908 return alter_table($old_table, $options);
911 sub alter_create_index {
912 my ($index, $options) = @_;
913 my $generator = _generator($options);
914 my ($idef, $constraints) = create_index($index, {
915 generator => $generator,
917 return $index->type eq NORMAL ? $idef
918 : sprintf('ALTER TABLE %s ADD %s',
919 $generator->quote($index->table->name),
920 join(q{}, @$constraints)
924 sub alter_drop_index {
925 my ($index, $options) = @_;
926 my $index_name = $index->name;
927 return "DROP INDEX $index_name";
930 sub alter_drop_constraint {
931 my ($c, $options) = @_;
932 my $generator = _generator($options);
934 # attention: Postgres has a very special naming structure for naming
935 # foreign keys and primary keys. It names them using the name of the
936 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
939 # Already has a name, just quote it
940 $c_name = $generator->quote($c->name);
941 } elsif ( $c->type eq FOREIGN_KEY ) {
942 # Doesn't have a name, and is foreign key, append '_fkey'
943 $c_name = $generator->quote($c->table->name . '_' .
944 ($c->fields)[0] . '_fkey');
945 } elsif ( $c->type eq PRIMARY_KEY ) {
946 # Doesn't have a name, and is primary key, append '_pkey'
947 $c_name = $generator->quote($c->table->name . '_pkey');
951 'ALTER TABLE %s DROP CONSTRAINT %s',
952 $generator->quote($c->table->name), $c_name
956 sub alter_create_constraint {
957 my ($index, $options) = @_;
958 my $generator = _generator($options);
959 my ($defs, $fks) = create_constraint(@_);
961 # return if there are no constraint definitions so we don't run
962 # into output like this:
963 # ALTER TABLE users ADD ;
965 return unless(@{$defs} || @{$fks});
966 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
967 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
968 'ADD', join(q{}, @{$defs}, @{$fks})
973 my ($table, $options) = @_;
974 my $generator = _generator($options);
975 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
977 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
979 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
983 sub batch_alter_table {
984 my ( $table, $diff_hash, $options ) = @_;
986 # as long as we're not renaming the table we don't need to be here
987 if ( @{$diff_hash->{rename_table}} == 0 ) {
988 return batch_alter_table_statements($diff_hash, $options);
991 # first we need to perform drops which are on old table
992 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
993 alter_drop_constraint
998 # next comes the rename_table
999 my $old_table = $diff_hash->{rename_table}[0][0];
1000 push @sql, rename_table( $old_table, $table, $options );
1002 # for alter_field (and so also rename_field) we need to make sure old
1003 # field has table name set to new table otherwise calling alter_field dies
1004 $diff_hash->{alter_field} =
1005 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1006 $diff_hash->{rename_field} =
1007 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1009 # now add everything else
1010 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1015 alter_create_constraint
1024 # -------------------------------------------------------------------
1025 # Life is full of misery, loneliness, and suffering --
1026 # and it's all over much too soon.
1028 # -------------------------------------------------------------------
1034 SQL::Translator, SQL::Translator::Producer::Oracle.
1038 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.