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(
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',
100 =head1 PostgreSQL Create Table Syntax
102 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
103 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
104 | table_constraint } [, ... ]
106 [ INHERITS ( parent_table [, ... ] ) ]
107 [ WITH OIDS | WITHOUT OIDS ]
109 where column_constraint is:
111 [ CONSTRAINT constraint_name ]
112 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
114 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
115 [ ON DELETE action ] [ ON UPDATE action ] }
116 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
118 and table_constraint is:
120 [ CONSTRAINT constraint_name ]
121 { UNIQUE ( column_name [, ... ] ) |
122 PRIMARY KEY ( column_name [, ... ] ) |
123 CHECK ( expression ) |
124 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
125 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
126 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
128 =head1 Create Index Syntax
130 CREATE [ UNIQUE ] INDEX index_name ON table
131 [ USING acc_method ] ( column [ ops_name ] [, ...] )
133 CREATE [ UNIQUE ] INDEX index_name ON table
134 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
140 my $translator = shift;
141 local $DEBUG = $translator->debug;
142 local $WARN = $translator->show_warnings;
143 my $no_comments = $translator->no_comments;
144 my $add_drop_table = $translator->add_drop_table;
145 my $schema = $translator->schema;
146 my $pargs = $translator->producer_args;
147 my $postgres_version = parse_dbms_version(
148 $pargs->{postgres_version}, 'perl'
151 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
154 push @output, header_comment unless ($no_comments);
156 my (@table_defs, @fks);
158 for my $table ( $schema->get_tables ) {
160 my ($table_def, $fks) = create_table($table, {
161 generator => $generator,
162 no_comments => $no_comments,
163 postgres_version => $postgres_version,
164 add_drop_table => $add_drop_table,
165 type_defs => \%type_defs,
168 push @table_defs, $table_def;
172 for my $view ( $schema->get_views ) {
173 push @table_defs, create_view($view, {
174 postgres_version => $postgres_version,
175 add_drop_view => $add_drop_table,
176 generator => $generator,
177 no_comments => $no_comments,
181 for my $trigger ( $schema->get_triggers ) {
182 push @table_defs, create_trigger( $trigger, {
183 add_drop_trigger => $add_drop_table,
184 generator => $generator,
185 no_comments => $no_comments,
189 push @output, map { "$_;\n\n" } values %type_defs;
190 push @output, map { "$_;\n\n" } @table_defs;
192 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
193 push @output, map { "$_;\n\n" } @fks;
198 warn "Truncated " . keys( %truncated ) . " names:\n";
199 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
205 : join ('', @output);
211 my $basename = shift || '';
212 my $type = shift || '';
213 my $scope = shift || '';
214 my $critical = shift || '';
215 my $basename_orig = $basename;
218 ? MAX_ID_LENGTH - (length($type) + 1)
220 $basename = substr( $basename, 0, $max_name )
221 if length( $basename ) > $max_name;
222 my $name = $type ? "${type}_$basename" : $basename;
224 if ( $basename ne $basename_orig and $critical ) {
225 my $show_type = $type ? "+'$type'" : "";
226 warn "Truncating '$basename_orig'$show_type to ", MAX_ID_LENGTH,
227 " character limit to make '$name'\n" if $WARN;
228 $truncated{ $basename_orig } = $name;
231 $scope ||= \%global_names;
232 if ( my $prev = $scope->{ $name } ) {
233 my $name_orig = $name;
234 $name .= sprintf( "%02d", ++$prev );
235 substr($name, MAX_ID_LENGTH - 3) = "00"
236 if length( $name ) > MAX_ID_LENGTH;
238 warn "The name '$name_orig' has been changed to ",
239 "'$name' to make it unique.\n" if $WARN;
241 $scope->{ $name_orig }++;
252 return 1 if $field->data_type eq 'geometry';
258 return 1 if $field->data_type eq 'geography';
263 my ($table, $options) = @_;
265 my $generator = _generator($options);
266 my $no_comments = $options->{no_comments} || 0;
267 my $add_drop_table = $options->{add_drop_table} || 0;
268 my $postgres_version = $options->{postgres_version} || 0;
269 my $type_defs = $options->{type_defs} || {};
271 my $table_name = $table->name or next;
272 my $table_name_qt = $generator->quote($table_name);
274 my ( @comments, @field_defs, @index_defs, @constraint_defs, @fks );
276 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
278 if ( !$no_comments and my $comments = $table->comments ) {
279 $comments =~ s/^/-- /mg;
280 push @comments, "-- Comments:\n$comments\n--\n";
286 for my $field ( $table->get_fields ) {
287 push @field_defs, create_field($field, {
288 generator => $generator,
289 postgres_version => $postgres_version,
290 type_defs => $type_defs,
291 constraint_defs => \@constraint_defs,
298 for my $index ( $table->get_indices ) {
299 my ($idef, $constraints) = create_index($index, {
300 generator => $generator,
302 $idef and push @index_defs, $idef;
303 push @constraint_defs, @$constraints;
309 for my $c ( $table->get_constraints ) {
310 my ($cdefs, $fks) = create_constraint($c, {
311 generator => $generator,
313 push @constraint_defs, @$cdefs;
318 my $create_statement = join("\n", @comments);
319 if ($add_drop_table) {
320 if ($postgres_version >= 8.002) {
321 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
323 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
326 my $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
327 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
328 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
331 $create_statement .= @index_defs ? ';' : q{};
332 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
333 . join(";\n", @index_defs);
338 if(grep { is_geometry($_) } $table->get_fields){
339 $create_statement .= ";";
340 my @geometry_columns;
341 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
342 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
343 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
346 return $create_statement, \@fks;
350 my ($view, $options) = @_;
351 my $generator = _generator($options);
352 my $postgres_version = $options->{postgres_version} || 0;
353 my $add_drop_view = $options->{add_drop_view};
355 my $view_name = $view->name;
356 debug("PKG: Looking at view '${view_name}'\n");
359 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
360 unless $options->{no_comments};
361 if ($add_drop_view) {
362 if ($postgres_version >= 8.002) {
363 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
365 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
370 my $extra = $view->extra;
371 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
372 $create .= " VIEW " . $generator->quote($view_name);
374 if ( my @fields = $view->fields ) {
375 my $field_list = join ', ', map { $generator->quote($_) } @fields;
376 $create .= " ( ${field_list} )";
379 if ( my $sql = $view->sql ) {
380 $create .= " AS\n ${sql}\n";
383 if ( $extra->{check_option} ) {
384 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
392 my %field_name_scope;
396 my ($field, $options) = @_;
398 my $generator = _generator($options);
399 my $table_name = $field->table->name;
400 my $constraint_defs = $options->{constraint_defs} || [];
401 my $postgres_version = $options->{postgres_version} || 0;
402 my $type_defs = $options->{type_defs} || {};
404 $field_name_scope{$table_name} ||= {};
405 my $field_name = $field->name;
406 my $field_comments = '';
407 if (my $comments = $field->comments) {
408 $comments =~ s/(?<!\A)^/ -- /mg;
409 $field_comments = "-- $comments\n ";
412 my $field_def = $field_comments . $generator->quote($field_name);
417 my $data_type = lc $field->data_type;
418 my %extra = $field->extra;
419 my $list = $extra{'list'} || [];
420 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
422 if ($postgres_version >= 8.003 && $data_type eq 'enum') {
423 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
424 $field_def .= ' '. $type_name;
425 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
426 "CREATE TYPE $type_name AS ENUM ($commalist)";
427 if (! exists $type_defs->{$type_name} ) {
428 $type_defs->{$type_name} = $new_type_def;
429 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
430 die "Attempted to redefine type name '$type_name' as a different type.\n";
433 $field_def .= ' '. convert_datatype($field);
439 __PACKAGE__->_apply_default_value(
445 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
450 # Not null constraint
452 $field_def .= ' NOT NULL' unless $field->is_nullable;
455 # Geometry constraints
457 if(is_geometry($field)){
458 foreach ( create_geometry_constraints($field) ) {
459 my ($cdefs, $fks) = create_constraint($_, {
460 generator => $generator,
462 push @$constraint_defs, @$cdefs;
471 sub create_geometry_constraints{
475 push @constraints, SQL::Translator::Schema::Constraint->new(
476 name => "enforce_dims_".$field->name,
477 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
478 table => $field->table,
482 push @constraints, SQL::Translator::Schema::Constraint->new(
483 name => "enforce_srid_".$field->name,
484 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
485 table => $field->table,
488 push @constraints, SQL::Translator::Schema::Constraint->new(
489 name => "enforce_geotype_".$field->name,
490 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
491 table => $field->table,
502 my ($index, $options) = @_;
504 my $generator = _generator($options);
505 my $table_name = $index->table->name;
507 my ($index_def, @constraint_defs);
511 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
513 my $type = $index->type || NORMAL;
514 my @fields = $index->fields;
515 return unless @fields;
519 for my $opt ( $index->options ) {
520 if ( ref $opt eq 'HASH' ) {
521 foreach my $key (keys %$opt) {
522 my $value = $opt->{$key};
523 next unless defined $value;
524 if ( uc($key) eq 'USING' ) {
525 $index_using = "USING $value";
527 elsif ( uc($key) eq 'WHERE' ) {
528 $index_where = "WHERE $value";
534 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
535 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
536 if ( $type eq PRIMARY_KEY ) {
537 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
539 elsif ( $type eq UNIQUE ) {
540 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
542 elsif ( $type eq NORMAL ) {
544 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' .
545 join ' ', grep { defined } $index_using, $field_names, $index_where;
548 warn "Unknown index type ($type) on table $table_name.\n"
552 return $index_def, \@constraint_defs;
556 sub create_constraint
558 my ($c, $options) = @_;
560 my $generator = _generator($options);
561 my $table_name = $c->table->name;
562 my (@constraint_defs, @fks);
564 my $name = $c->name || '';
566 my @fields = grep { defined } $c->fields;
568 my @rfields = grep { defined } $c->reference_fields;
570 next if !@fields && $c->type ne CHECK_C;
571 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
572 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
573 if ( $c->type eq PRIMARY_KEY ) {
574 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
576 elsif ( $c->type eq UNIQUE ) {
577 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
579 elsif ( $c->type eq CHECK_C ) {
580 my $expression = $c->expression;
581 push @constraint_defs, "${def_start}CHECK ($expression)";
583 elsif ( $c->type eq FOREIGN_KEY ) {
584 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
585 . "\n REFERENCES " . $generator->quote($c->reference_table);
588 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
591 if ( $c->match_type ) {
593 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
596 if ( $c->on_delete ) {
597 $def .= ' ON DELETE '. $c->on_delete;
600 if ( $c->on_update ) {
601 $def .= ' ON UPDATE '. $c->on_update;
604 if ( $c->deferrable ) {
605 $def .= ' DEFERRABLE';
611 return \@constraint_defs, \@fks;
615 my ($trigger,$options) = @_;
616 my $generator = _generator($options);
620 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
621 if $options->{add_drop_trigger};
623 my $scope = $trigger->scope || '';
624 $scope = " FOR EACH $scope" if $scope;
626 push @statements, sprintf(
627 'CREATE TRIGGER %s %s %s ON %s%s %s',
628 $generator->quote($trigger->name),
629 $trigger->perform_action_when,
630 join( ' OR ', @{ $trigger->database_events } ),
631 $generator->quote($trigger->on_table),
643 my @size = $field->size;
644 my $data_type = lc $field->data_type;
645 my $array = $data_type =~ s/\[\]$//;
647 if ( $data_type eq 'enum' ) {
649 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
650 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
651 # push @$constraint_defs,
652 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
653 # qq[IN ($commalist))];
654 $data_type = 'character varying';
656 elsif ( $data_type eq 'set' ) {
657 $data_type = 'character varying';
659 elsif ( $field->is_auto_increment ) {
660 if ( defined $size[0] && $size[0] > 11 ) {
661 $data_type = 'bigserial';
664 $data_type = 'serial';
669 $data_type = defined $translate{ lc $data_type } ?
670 $translate{ lc $data_type } :
674 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
675 if ( defined $size[0] && $size[0] > 6 ) {
680 if ( $data_type eq 'integer' ) {
681 if ( defined $size[0] && $size[0] > 0) {
682 if ( $size[0] > 10 ) {
683 $data_type = 'bigint';
685 elsif ( $size[0] < 5 ) {
686 $data_type = 'smallint';
689 $data_type = 'integer';
693 $data_type = 'integer';
697 my $type_with_size = join('|',
698 'bit', 'varbit', 'character', 'bit varying', 'character varying',
699 'time', 'timestamp', 'interval', 'numeric', 'float'
702 if ( $data_type !~ /$type_with_size/ ) {
706 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
707 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
708 $data_type .= $2 if(defined $2);
709 } elsif ( defined $size[0] && $size[0] > 0 ) {
710 $data_type .= '(' . join( ',', @size ) . ')';
720 if($data_type eq 'geography'){
721 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
730 my ($from_field, $to_field, $options) = @_;
732 die "Can't alter field in another table"
733 if($from_field->table->name ne $to_field->table->name);
735 my $generator = _generator($options);
738 # drop geometry column and constraints
739 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
740 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
742 # it's necessary to start with rename column cause this would affect
743 # all of the following statements which would be broken if do the
745 # BUT: drop geometry is done before the rename, cause it work's on the
746 # $from_field directly
747 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
748 map($generator->quote($_),
749 $to_field->table->name,
754 if($from_field->name ne $to_field->name);
756 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
757 map($generator->quote($_),
758 $to_field->table->name,
762 if(!$to_field->is_nullable and $from_field->is_nullable);
764 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
765 map($generator->quote($_),
766 $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 map($generator->quote($_),
777 $to_field->table->name,
782 if($to_dt ne $from_dt);
784 my $old_default = $from_field->default_value;
785 my $new_default = $to_field->default_value;
786 my $default_value = $to_field->default_value;
788 # fixes bug where output like this was created:
789 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
790 if(ref $default_value eq "SCALAR" ) {
791 $default_value = $$default_value;
792 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
793 $default_value = __PACKAGE__->_quote_string($default_value);
796 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
797 map($generator->quote($_),
798 $to_field->table->name,
803 if ( defined $new_default &&
804 (!defined $old_default || $old_default ne $new_default) );
806 # fixes bug where removing the DEFAULT statement of a column
807 # would result in no change
809 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
810 map($generator->quote($_),
811 $to_field->table->name,
815 if ( !defined $new_default && defined $old_default );
817 # add geometry column and constraints
818 push @out, add_geometry_column($to_field) if is_geometry($to_field);
819 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
821 return wantarray ? @out : join(";\n", @out);
824 sub rename_field { alter_field(@_) }
828 my ($new_field,$options) = @_;
830 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
831 _generator($options)->quote($new_field->table->name),
832 create_field($new_field, $options));
833 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
834 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
841 my ($old_field, $options) = @_;
843 my $generator = _generator($options);
845 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
846 $generator->quote($old_field->table->name),
847 $generator->quote($old_field->name));
848 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
852 sub add_geometry_column{
853 my ($field,$options) = @_;
855 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
857 $field->table->schema->name,
858 $options->{table} ? $options->{table} : $field->table->name,
860 $field->extra->{dimensions},
861 $field->extra->{srid},
862 $field->extra->{geometry_type});
866 sub drop_geometry_column
870 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
871 $field->table->schema->name,
877 sub add_geometry_constraints{
880 my @constraints = create_geometry_constraints($field);
882 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
887 sub drop_geometry_constraints{
890 my @constraints = create_geometry_constraints($field);
892 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
898 my ($to_table, $options) = @_;
899 my $generator = _generator($options);
900 my $out = sprintf('ALTER TABLE %s %s',
901 $generator->quote($to_table->name),
902 $options->{alter_table_action});
903 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
908 my ($old_table, $new_table, $options) = @_;
909 my $generator = _generator($options);
910 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
912 my @geometry_changes;
913 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
914 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
916 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
918 return alter_table($old_table, $options);
921 sub alter_create_index {
922 my ($index, $options) = @_;
923 my $generator = _generator($options);
924 my ($idef, $constraints) = create_index($index, {
925 generator => $generator,
927 return $index->type eq NORMAL ? $idef
928 : sprintf('ALTER TABLE %s ADD %s',
929 $generator->quote($index->table->name),
930 join(q{}, @$constraints)
934 sub alter_drop_index {
935 my ($index, $options) = @_;
936 return 'DROP INDEX '. _generator($options)->quote($index->name);
939 sub alter_drop_constraint {
940 my ($c, $options) = @_;
941 my $generator = _generator($options);
943 # attention: Postgres has a very special naming structure for naming
944 # foreign keys and primary keys. It names them using the name of the
945 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
948 # Already has a name, just use it
950 } elsif ( $c->type eq FOREIGN_KEY ) {
951 # Doesn't have a name, and is foreign key, append '_fkey'
952 $c_name = $c->table->name . '_' . ($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 = $c->table->name . '_pkey';
959 'ALTER TABLE %s DROP CONSTRAINT %s',
960 map { $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>.