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 );
62 mediumint => 'integer',
63 smallint => 'smallint',
64 tinyint => 'smallint',
66 varchar => 'character varying',
73 mediumblob => 'bytea',
75 enum => 'character varying',
76 set => 'character varying',
78 datetime => 'timestamp',
80 timestamp => 'timestamp',
88 varchar2 => 'character varying',
98 varchar => 'character varying',
99 datetime => 'timestamp',
104 tinyint => 'smallint',
110 my %reserved = map { $_, 1 } qw[
111 ALL ANALYSE ANALYZE AND ANY AS ASC
113 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
114 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
115 DEFAULT DEFERRABLE DESC DISTINCT DO
117 FALSE FOR FOREIGN FREEZE FROM FULL
119 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
120 JOIN LEADING LEFT LIKE LIMIT
121 NATURAL NEW NOT NOTNULL NULL
122 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
123 PRIMARY PUBLIC REFERENCES RIGHT
124 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
125 UNION UNIQUE USER USING VERBOSE WHEN WHERE
128 # my $max_id_length = 62;
129 my %used_identifiers = ();
135 =head1 PostgreSQL Create Table Syntax
137 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
138 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
139 | table_constraint } [, ... ]
141 [ INHERITS ( parent_table [, ... ] ) ]
142 [ WITH OIDS | WITHOUT OIDS ]
144 where column_constraint is:
146 [ CONSTRAINT constraint_name ]
147 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
149 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
150 [ ON DELETE action ] [ ON UPDATE action ] }
151 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
153 and table_constraint is:
155 [ CONSTRAINT constraint_name ]
156 { UNIQUE ( column_name [, ... ] ) |
157 PRIMARY KEY ( column_name [, ... ] ) |
158 CHECK ( expression ) |
159 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
160 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
161 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
163 =head1 Create Index Syntax
165 CREATE [ UNIQUE ] INDEX index_name ON table
166 [ USING acc_method ] ( column [ ops_name ] [, ...] )
168 CREATE [ UNIQUE ] INDEX index_name ON table
169 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
175 my $translator = shift;
176 local $DEBUG = $translator->debug;
177 local $WARN = $translator->show_warnings;
178 my $no_comments = $translator->no_comments;
179 my $add_drop_table = $translator->add_drop_table;
180 my $schema = $translator->schema;
181 my $pargs = $translator->producer_args;
182 my $postgres_version = parse_dbms_version(
183 $pargs->{postgres_version}, 'perl'
186 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
189 push @output, header_comment unless ($no_comments);
191 my (@table_defs, @fks);
193 for my $table ( $schema->get_tables ) {
195 my ($table_def, $fks) = create_table($table, {
196 generator => $generator,
197 no_comments => $no_comments,
198 postgres_version => $postgres_version,
199 add_drop_table => $add_drop_table,
200 type_defs => \%type_defs,
203 push @table_defs, $table_def;
207 for my $view ( $schema->get_views ) {
208 push @table_defs, create_view($view, {
209 postgres_version => $postgres_version,
210 add_drop_view => $add_drop_table,
211 generator => $generator,
212 no_comments => $no_comments,
216 for my $trigger ( $schema->get_triggers ) {
217 push @table_defs, create_trigger( $trigger, {
218 add_drop_trigger => $add_drop_table,
219 no_comments => $no_comments,
223 push @output, map { "$_;\n\n" } values %type_defs;
224 push @output, map { "$_;\n\n" } @table_defs;
226 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
227 push @output, map { "$_;\n\n" } @fks;
232 warn "Truncated " . keys( %truncated ) . " names:\n";
233 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
239 : join ('', @output);
243 my $basename = shift || '';
244 my $type = shift || '';
245 my $scope = shift || '';
246 my $critical = shift || '';
247 my $basename_orig = $basename;
248 # my $max_id_length = 62;
250 ? $max_id_length - (length($type) + 1)
252 $basename = substr( $basename, 0, $max_name )
253 if length( $basename ) > $max_name;
254 my $name = $type ? "${type}_$basename" : $basename;
256 if ( $basename ne $basename_orig and $critical ) {
257 my $show_type = $type ? "+'$type'" : "";
258 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
259 "character limit to make '$name'\n" if $WARN;
260 $truncated{ $basename_orig } = $name;
263 $scope ||= \%global_names;
264 if ( my $prev = $scope->{ $name } ) {
265 my $name_orig = $name;
266 $name .= sprintf( "%02d", ++$prev );
267 substr($name, $max_id_length - 3) = "00"
268 if length( $name ) > $max_id_length;
270 warn "The name '$name_orig' has been changed to ",
271 "'$name' to make it unique.\n" if $WARN;
273 $scope->{ $name_orig }++;
283 return 1 if $field->data_type eq 'geometry';
289 return 1 if $field->data_type eq 'geography';
294 my ($table, $options) = @_;
296 my $generator = _generator($options);
297 my $no_comments = $options->{no_comments} || 0;
298 my $add_drop_table = $options->{add_drop_table} || 0;
299 my $postgres_version = $options->{postgres_version} || 0;
300 my $type_defs = $options->{type_defs} || {};
302 my $table_name = $table->name or next;
303 my $table_name_qt = $generator->quote($table_name);
305 # print STDERR "$table_name table_name\n";
306 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @fks );
308 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
310 if ( $table->comments and !$no_comments ){
311 my $c = "-- Comments: \n-- ";
312 $c .= join "\n-- ", $table->comments;
320 my %field_name_scope;
321 for my $field ( $table->get_fields ) {
322 push @field_defs, create_field($field, {
323 generator => $generator,
324 postgres_version => $postgres_version,
325 type_defs => $type_defs,
326 constraint_defs => \@constraint_defs,
334 # my $idx_name_default;
335 for my $index ( $table->get_indices ) {
336 my ($idef, $constraints) = create_index($index, {
337 generator => $generator,
339 $idef and push @index_defs, $idef;
340 push @constraint_defs, @$constraints;
347 for my $c ( $table->get_constraints ) {
348 my ($cdefs, $fks) = create_constraint($c, {
349 generator => $generator,
351 push @constraint_defs, @$cdefs;
358 if(exists $table->extra->{temporary}) {
359 $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
362 my $create_statement;
363 $create_statement = join("\n", @comments);
364 if ($add_drop_table) {
365 if ($postgres_version >= 8.002) {
366 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
368 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
371 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
372 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
375 $create_statement .= @index_defs ? ';' : q{};
376 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
377 . join(";\n", @index_defs);
382 if(grep { is_geometry($_) } $table->get_fields){
383 $create_statement .= ";";
384 my @geometry_columns;
385 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
386 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
387 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
390 return $create_statement, \@fks;
394 my ($view, $options) = @_;
395 my $generator = _generator($options);
396 my $postgres_version = $options->{postgres_version} || 0;
397 my $add_drop_view = $options->{add_drop_view};
399 my $view_name = $view->name;
400 debug("PKG: Looking at view '${view_name}'\n");
403 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
404 unless $options->{no_comments};
405 if ($add_drop_view) {
406 if ($postgres_version >= 8.002) {
407 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
409 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
414 my $extra = $view->extra;
415 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
416 $create .= " VIEW " . $generator->quote($view_name);
418 if ( my @fields = $view->fields ) {
419 my $field_list = join ', ', map { $generator->quote($_) } @fields;
420 $create .= " ( ${field_list} )";
423 if ( my $sql = $view->sql ) {
424 $create .= " AS\n ${sql}\n";
427 if ( $extra->{check_option} ) {
428 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
436 my %field_name_scope;
440 my ($field, $options) = @_;
442 my $generator = _generator($options);
443 my $table_name = $field->table->name;
444 my $constraint_defs = $options->{constraint_defs} || [];
445 my $postgres_version = $options->{postgres_version} || 0;
446 my $type_defs = $options->{type_defs} || {};
448 $field_name_scope{$table_name} ||= {};
449 my $field_name = $field->name;
450 my $field_comments = $field->comments
451 ? "-- " . $field->comments . "\n "
454 my $field_def = $field_comments . $generator->quote($field_name);
459 my @size = $field->size;
460 my $data_type = lc $field->data_type;
461 my %extra = $field->extra;
462 my $list = $extra{'list'} || [];
463 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
465 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
466 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
467 $field_def .= ' '. $type_name;
468 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
469 "CREATE TYPE $type_name AS ENUM ($commalist)";
470 if (! exists $type_defs->{$type_name} ) {
471 $type_defs->{$type_name} = $new_type_def;
472 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
473 die "Attempted to redefine type name '$type_name' as a different type.\n";
476 $field_def .= ' '. convert_datatype($field);
482 __PACKAGE__->_apply_default_value(
488 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
493 # Not null constraint
495 $field_def .= ' NOT NULL' unless $field->is_nullable;
498 # Geometry constraints
500 if(is_geometry($field)){
501 foreach ( create_geometry_constraints($field) ) {
502 my ($cdefs, $fks) = create_constraint($_, {
503 generator => $generator,
505 push @$constraint_defs, @$cdefs;
514 sub create_geometry_constraints{
518 push @constraints, SQL::Translator::Schema::Constraint->new(
519 name => "enforce_dims_".$field->name,
520 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
521 table => $field->table,
525 push @constraints, SQL::Translator::Schema::Constraint->new(
526 name => "enforce_srid_".$field->name,
527 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
528 table => $field->table,
531 push @constraints, SQL::Translator::Schema::Constraint->new(
532 name => "enforce_geotype_".$field->name,
533 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
534 table => $field->table,
543 my ($index, $options) = @_;
545 my $generator = _generator($options);
546 my $table_name = $index->table->name;
548 my ($index_def, @constraint_defs);
552 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
554 my $type = $index->type || NORMAL;
555 my @fields = $index->fields;
556 return unless @fields;
558 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
559 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
560 if ( $type eq PRIMARY_KEY ) {
561 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
563 elsif ( $type eq UNIQUE ) {
564 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
566 elsif ( $type eq NORMAL ) {
568 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' . $field_names
572 warn "Unknown index type ($type) on table $table_name.\n"
576 return $index_def, \@constraint_defs;
579 sub create_constraint
581 my ($c, $options) = @_;
583 my $generator = _generator($options);
584 my $table_name = $c->table->name;
585 my (@constraint_defs, @fks);
587 my $name = $c->name || '';
589 my @fields = grep { defined } $c->fields;
591 my @rfields = grep { defined } $c->reference_fields;
593 next if !@fields && $c->type ne CHECK_C;
594 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
595 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
596 if ( $c->type eq PRIMARY_KEY ) {
597 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
599 elsif ( $c->type eq UNIQUE ) {
600 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
602 elsif ( $c->type eq CHECK_C ) {
603 my $expression = $c->expression;
604 push @constraint_defs, "${def_start}CHECK ($expression)";
606 elsif ( $c->type eq FOREIGN_KEY ) {
607 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
608 . "\n REFERENCES " . $generator->quote($c->reference_table);
611 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
614 if ( $c->match_type ) {
616 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
619 if ( $c->on_delete ) {
620 $def .= ' ON DELETE '. $c->on_delete;
623 if ( $c->on_update ) {
624 $def .= ' ON UPDATE '. $c->on_update;
627 if ( $c->deferrable ) {
628 $def .= ' DEFERRABLE';
634 return \@constraint_defs, \@fks;
638 my ($trigger,$options) = @_;
642 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $trigger->name )
643 if $options->{add_drop_trigger};
645 my $scope = $trigger->scope || '';
646 $scope = " FOR EACH $scope" if $scope;
648 push @statements, sprintf(
649 'CREATE TRIGGER %s %s %s ON %s%s %s',
651 $trigger->perform_action_when,
652 join( ' OR ', @{ $trigger->database_events } ),
665 my @size = $field->size;
666 my $data_type = lc $field->data_type;
667 my $array = $data_type =~ s/\[\]$//;
669 if ( $data_type eq 'enum' ) {
671 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
672 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
673 # push @$constraint_defs,
674 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
675 # qq[IN ($commalist))];
676 $data_type = 'character varying';
678 elsif ( $data_type eq 'set' ) {
679 $data_type = 'character varying';
681 elsif ( $field->is_auto_increment ) {
682 if ( defined $size[0] && $size[0] > 11 ) {
683 $data_type = 'bigserial';
686 $data_type = 'serial';
691 $data_type = defined $translate{ $data_type } ?
692 $translate{ $data_type } :
696 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
697 if ( defined $size[0] && $size[0] > 6 ) {
702 if ( $data_type eq 'integer' ) {
703 if ( defined $size[0] && $size[0] > 0) {
704 if ( $size[0] > 10 ) {
705 $data_type = 'bigint';
707 elsif ( $size[0] < 5 ) {
708 $data_type = 'smallint';
711 $data_type = 'integer';
715 $data_type = 'integer';
719 my $type_with_size = join('|',
720 'bit', 'varbit', 'character', 'bit varying', 'character varying',
721 'time', 'timestamp', 'interval', 'numeric'
724 if ( $data_type !~ /$type_with_size/ ) {
728 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
729 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
730 $data_type .= $2 if(defined $2);
731 } elsif ( defined $size[0] && $size[0] > 0 ) {
732 $data_type .= '(' . join( ',', @size ) . ')';
742 if($data_type eq 'geography'){
743 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
752 my ($from_field, $to_field) = @_;
754 die "Can't alter field in another table"
755 if($from_field->table->name ne $to_field->table->name);
759 # drop geometry column and constraints
760 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
761 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
763 # it's necessary to start with rename column cause this would affect
764 # all of the following statements which would be broken if do the
766 # BUT: drop geometry is done before the rename, cause it work's on the
767 # $from_field directly
768 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
769 $to_field->table->name,
771 $to_field->name) if($from_field->name ne $to_field->name);
773 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
774 $to_field->table->name,
775 $to_field->name) if(!$to_field->is_nullable and
776 $from_field->is_nullable);
778 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
779 $to_field->table->name,
781 if ( !$from_field->is_nullable and $to_field->is_nullable );
784 my $from_dt = convert_datatype($from_field);
785 my $to_dt = convert_datatype($to_field);
786 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
787 $to_field->table->name,
789 $to_dt) if($to_dt ne $from_dt);
791 my $old_default = $from_field->default_value;
792 my $new_default = $to_field->default_value;
793 my $default_value = $to_field->default_value;
795 # fixes bug where output like this was created:
796 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
797 if(ref $default_value eq "SCALAR" ) {
798 $default_value = $$default_value;
799 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
800 $default_value = __PACKAGE__->_quote_string($default_value);
803 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
804 $to_field->table->name,
807 if ( defined $new_default &&
808 (!defined $old_default || $old_default ne $new_default) );
810 # fixes bug where removing the DEFAULT statement of a column
811 # would result in no change
813 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
814 $to_field->table->name,
816 if ( !defined $new_default && defined $old_default );
818 # add geometry column and constraints
819 push @out, add_geometry_column($to_field) if is_geometry($to_field);
820 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
822 return wantarray ? @out : join(";\n", @out);
825 sub rename_field { alter_field(@_) }
829 my ($new_field) = @_;
831 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
832 $new_field->table->name,
833 create_field($new_field));
834 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
835 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
842 my ($old_field, $options) = @_;
844 my $generator = _generator($options);
846 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
847 $generator->quote($old_field->table->name),
848 $generator->quote($old_field->name));
849 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
853 sub add_geometry_column{
854 my ($field,$options) = @_;
856 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
858 $field->table->schema->name,
859 $options->{table} ? $options->{table} : $field->table->name,
861 $field->extra->{dimensions},
862 $field->extra->{srid},
863 $field->extra->{geometry_type});
867 sub drop_geometry_column
871 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
872 $field->table->schema->name,
878 sub add_geometry_constraints{
881 my @constraints = create_geometry_constraints($field);
883 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
888 sub drop_geometry_constraints{
891 my @constraints = create_geometry_constraints($field);
893 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
899 my ($to_table, $options) = @_;
900 my $generator = _generator($options);
901 my $out = sprintf('ALTER TABLE %s %s',
902 $generator->quote($to_table->name),
903 $options->{alter_table_action});
904 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
909 my ($old_table, $new_table, $options) = @_;
910 my $generator = _generator($options);
911 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
913 my @geometry_changes;
914 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
915 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
917 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
919 return alter_table($old_table, $options);
922 sub alter_create_index {
923 my ($index, $options) = @_;
924 my $generator = _generator($options);
925 my ($idef, $constraints) = create_index($index, {
926 generator => $generator,
928 return $index->type eq NORMAL ? $idef
929 : sprintf('ALTER TABLE %s ADD %s',
930 $generator->quote($index->table->name),
931 join(q{}, @$constraints)
935 sub alter_drop_index {
936 my ($index, $options) = @_;
937 my $index_name = $index->name;
938 return "DROP INDEX $index_name";
941 sub alter_drop_constraint {
942 my ($c, $options) = @_;
943 my $generator = _generator($options);
945 # attention: Postgres has a very special naming structure for naming
946 # foreign keys and primary keys. It names them using the name of the
947 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
950 # Already has a name, just quote it
951 $c_name = $generator->quote($c->name);
952 } elsif ( $c->type eq FOREIGN_KEY ) {
953 # Doesn't have a name, and is foreign key, append '_fkey'
954 $c_name = $generator->quote($c->table->name . '_' .
955 ($c->fields)[0] . '_fkey');
956 } elsif ( $c->type eq PRIMARY_KEY ) {
957 # Doesn't have a name, and is primary key, append '_pkey'
958 $c_name = $generator->quote($c->table->name . '_pkey');
962 'ALTER TABLE %s DROP CONSTRAINT %s',
963 $generator->quote($c->table->name), $c_name
967 sub alter_create_constraint {
968 my ($index, $options) = @_;
969 my $generator = _generator($options);
970 my ($defs, $fks) = create_constraint(@_);
972 # return if there are no constraint definitions so we don't run
973 # into output like this:
974 # ALTER TABLE users ADD ;
976 return unless(@{$defs} || @{$fks});
977 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
978 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
979 'ADD', join(q{}, @{$defs}, @{$fks})
984 my ($table, $options) = @_;
985 my $generator = _generator($options);
986 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
988 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
990 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
994 sub batch_alter_table {
995 my ( $table, $diff_hash, $options ) = @_;
997 # as long as we're not renaming the table we don't need to be here
998 if ( @{$diff_hash->{rename_table}} == 0 ) {
999 return batch_alter_table_statements($diff_hash, $options);
1002 # first we need to perform drops which are on old table
1003 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
1004 alter_drop_constraint
1009 # next comes the rename_table
1010 my $old_table = $diff_hash->{rename_table}[0][0];
1011 push @sql, rename_table( $old_table, $table, $options );
1013 # for alter_field (and so also rename_field) we need to make sure old
1014 # field has table name set to new table otherwise calling alter_field dies
1015 $diff_hash->{alter_field} =
1016 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1017 $diff_hash->{rename_field} =
1018 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1020 # now add everything else
1021 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1026 alter_create_constraint
1035 # -------------------------------------------------------------------
1036 # Life is full of misery, loneliness, and suffering --
1037 # and it's all over much too soon.
1039 # -------------------------------------------------------------------
1045 SQL::Translator, SQL::Translator::Producer::Oracle.
1049 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.