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 # print STDERR "$table_name table_name\n";
275 my ( @comments, @field_defs, @index_defs, @sequence_defs, @constraint_defs, @fks );
277 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
279 if ( !$no_comments and my $comments = $table->comments ) {
280 $comments =~ s/^/-- /mg;
281 push @comments, "-- Comments:\n$comments\n--\n";
287 for my $field ( $table->get_fields ) {
288 push @field_defs, create_field($field, {
289 generator => $generator,
290 postgres_version => $postgres_version,
291 type_defs => $type_defs,
292 constraint_defs => \@constraint_defs,
299 for my $index ( $table->get_indices ) {
300 my ($idef, $constraints) = create_index($index, {
301 generator => $generator,
303 $idef and push @index_defs, $idef;
304 push @constraint_defs, @$constraints;
310 for my $c ( $table->get_constraints ) {
311 my ($cdefs, $fks) = create_constraint($c, {
312 generator => $generator,
314 push @constraint_defs, @$cdefs;
319 my $create_statement = join("\n", @comments);
320 if ($add_drop_table) {
321 if ($postgres_version >= 8.002) {
322 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
324 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
327 my $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
328 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
329 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
332 $create_statement .= @index_defs ? ';' : q{};
333 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
334 . join(";\n", @index_defs);
339 if(grep { is_geometry($_) } $table->get_fields){
340 $create_statement .= ";";
341 my @geometry_columns;
342 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
343 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
344 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
347 return $create_statement, \@fks;
351 my ($view, $options) = @_;
352 my $generator = _generator($options);
353 my $postgres_version = $options->{postgres_version} || 0;
354 my $add_drop_view = $options->{add_drop_view};
356 my $view_name = $view->name;
357 debug("PKG: Looking at view '${view_name}'\n");
360 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
361 unless $options->{no_comments};
362 if ($add_drop_view) {
363 if ($postgres_version >= 8.002) {
364 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
366 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
371 my $extra = $view->extra;
372 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
373 $create .= " VIEW " . $generator->quote($view_name);
375 if ( my @fields = $view->fields ) {
376 my $field_list = join ', ', map { $generator->quote($_) } @fields;
377 $create .= " ( ${field_list} )";
380 if ( my $sql = $view->sql ) {
381 $create .= " AS\n ${sql}\n";
384 if ( $extra->{check_option} ) {
385 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
393 my %field_name_scope;
397 my ($field, $options) = @_;
399 my $generator = _generator($options);
400 my $table_name = $field->table->name;
401 my $constraint_defs = $options->{constraint_defs} || [];
402 my $postgres_version = $options->{postgres_version} || 0;
403 my $type_defs = $options->{type_defs} || {};
405 $field_name_scope{$table_name} ||= {};
406 my $field_name = $field->name;
407 my $field_comments = '';
408 if (my $comments = $field->comments) {
409 $comments =~ s/(?<!\A)^/ -- /mg;
410 $field_comments = "-- $comments\n ";
413 my $field_def = $field_comments . $generator->quote($field_name);
418 my @size = $field->size;
419 my $data_type = lc $field->data_type;
420 my %extra = $field->extra;
421 my $list = $extra{'list'} || [];
422 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
424 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
425 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
426 $field_def .= ' '. $type_name;
427 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
428 "CREATE TYPE $type_name AS ENUM ($commalist)";
429 if (! exists $type_defs->{$type_name} ) {
430 $type_defs->{$type_name} = $new_type_def;
431 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
432 die "Attempted to redefine type name '$type_name' as a different type.\n";
435 $field_def .= ' '. convert_datatype($field);
441 __PACKAGE__->_apply_default_value(
447 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
452 # Not null constraint
454 $field_def .= ' NOT NULL' unless $field->is_nullable;
457 # Geometry constraints
459 if(is_geometry($field)){
460 foreach ( create_geometry_constraints($field) ) {
461 my ($cdefs, $fks) = create_constraint($_, {
462 generator => $generator,
464 push @$constraint_defs, @$cdefs;
473 sub create_geometry_constraints{
477 push @constraints, SQL::Translator::Schema::Constraint->new(
478 name => "enforce_dims_".$field->name,
479 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
480 table => $field->table,
484 push @constraints, SQL::Translator::Schema::Constraint->new(
485 name => "enforce_srid_".$field->name,
486 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
487 table => $field->table,
490 push @constraints, SQL::Translator::Schema::Constraint->new(
491 name => "enforce_geotype_".$field->name,
492 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
493 table => $field->table,
504 my ($index, $options) = @_;
506 my $generator = _generator($options);
507 my $table_name = $index->table->name;
509 my ($index_def, @constraint_defs);
513 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
515 my $type = $index->type || NORMAL;
516 my @fields = $index->fields;
517 return unless @fields;
521 for my $opt ( $index->options ) {
522 if ( ref $opt eq 'HASH' ) {
523 foreach my $key (keys %$opt) {
524 my $value = $opt->{$key};
525 next unless defined $value;
526 if ( uc($key) eq 'USING' ) {
527 $index_using = "USING $value";
529 elsif ( uc($key) eq 'WHERE' ) {
530 $index_where = "WHERE $value";
536 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
537 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
538 if ( $type eq PRIMARY_KEY ) {
539 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
541 elsif ( $type eq UNIQUE ) {
542 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
544 elsif ( $type eq NORMAL ) {
546 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' .
547 join ' ', grep { defined } $index_using, $field_names, $index_where;
550 warn "Unknown index type ($type) on table $table_name.\n"
554 return $index_def, \@constraint_defs;
558 sub create_constraint
560 my ($c, $options) = @_;
562 my $generator = _generator($options);
563 my $table_name = $c->table->name;
564 my (@constraint_defs, @fks);
566 my $name = $c->name || '';
568 my @fields = grep { defined } $c->fields;
570 my @rfields = grep { defined } $c->reference_fields;
572 next if !@fields && $c->type ne CHECK_C;
573 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
574 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
575 if ( $c->type eq PRIMARY_KEY ) {
576 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
578 elsif ( $c->type eq UNIQUE ) {
579 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
581 elsif ( $c->type eq CHECK_C ) {
582 my $expression = $c->expression;
583 push @constraint_defs, "${def_start}CHECK ($expression)";
585 elsif ( $c->type eq FOREIGN_KEY ) {
586 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
587 . "\n REFERENCES " . $generator->quote($c->reference_table);
590 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
593 if ( $c->match_type ) {
595 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
598 if ( $c->on_delete ) {
599 $def .= ' ON DELETE '. $c->on_delete;
602 if ( $c->on_update ) {
603 $def .= ' ON UPDATE '. $c->on_update;
606 if ( $c->deferrable ) {
607 $def .= ' DEFERRABLE';
613 return \@constraint_defs, \@fks;
617 my ($trigger,$options) = @_;
618 my $generator = _generator($options);
622 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
623 if $options->{add_drop_trigger};
625 my $scope = $trigger->scope || '';
626 $scope = " FOR EACH $scope" if $scope;
628 push @statements, sprintf(
629 'CREATE TRIGGER %s %s %s ON %s%s %s',
630 $generator->quote($trigger->name),
631 $trigger->perform_action_when,
632 join( ' OR ', @{ $trigger->database_events } ),
633 $generator->quote($trigger->on_table),
645 my @size = $field->size;
646 my $data_type = lc $field->data_type;
647 my $array = $data_type =~ s/\[\]$//;
649 if ( $data_type eq 'enum' ) {
651 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
652 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
653 # push @$constraint_defs,
654 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
655 # qq[IN ($commalist))];
656 $data_type = 'character varying';
658 elsif ( $data_type eq 'set' ) {
659 $data_type = 'character varying';
661 elsif ( $field->is_auto_increment ) {
662 if ( defined $size[0] && $size[0] > 11 ) {
663 $data_type = 'bigserial';
666 $data_type = 'serial';
671 $data_type = defined $translate{ lc $data_type } ?
672 $translate{ lc $data_type } :
676 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
677 if ( defined $size[0] && $size[0] > 6 ) {
682 if ( $data_type eq 'integer' ) {
683 if ( defined $size[0] && $size[0] > 0) {
684 if ( $size[0] > 10 ) {
685 $data_type = 'bigint';
687 elsif ( $size[0] < 5 ) {
688 $data_type = 'smallint';
691 $data_type = 'integer';
695 $data_type = 'integer';
699 my $type_with_size = join('|',
700 'bit', 'varbit', 'character', 'bit varying', 'character varying',
701 'time', 'timestamp', 'interval', 'numeric', 'float'
704 if ( $data_type !~ /$type_with_size/ ) {
708 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
709 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
710 $data_type .= $2 if(defined $2);
711 } elsif ( defined $size[0] && $size[0] > 0 ) {
712 $data_type .= '(' . join( ',', @size ) . ')';
722 if($data_type eq 'geography'){
723 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
732 my ($from_field, $to_field, $options) = @_;
734 die "Can't alter field in another table"
735 if($from_field->table->name ne $to_field->table->name);
737 my $generator = _generator($options);
740 # drop geometry column and constraints
741 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
742 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
744 # it's necessary to start with rename column cause this would affect
745 # all of the following statements which would be broken if do the
747 # BUT: drop geometry is done before the rename, cause it work's on the
748 # $from_field directly
749 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
750 map($generator->quote($_),
751 $to_field->table->name,
756 if($from_field->name ne $to_field->name);
758 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
759 map($generator->quote($_),
760 $to_field->table->name,
764 if(!$to_field->is_nullable and $from_field->is_nullable);
766 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
767 map($generator->quote($_),
768 $to_field->table->name,
772 if (!$from_field->is_nullable and $to_field->is_nullable);
775 my $from_dt = convert_datatype($from_field);
776 my $to_dt = convert_datatype($to_field);
777 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
778 map($generator->quote($_),
779 $to_field->table->name,
784 if($to_dt ne $from_dt);
786 my $old_default = $from_field->default_value;
787 my $new_default = $to_field->default_value;
788 my $default_value = $to_field->default_value;
790 # fixes bug where output like this was created:
791 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
792 if(ref $default_value eq "SCALAR" ) {
793 $default_value = $$default_value;
794 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
795 $default_value = __PACKAGE__->_quote_string($default_value);
798 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
799 map($generator->quote($_),
800 $to_field->table->name,
805 if ( defined $new_default &&
806 (!defined $old_default || $old_default ne $new_default) );
808 # fixes bug where removing the DEFAULT statement of a column
809 # would result in no change
811 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
812 map($generator->quote($_),
813 $to_field->table->name,
817 if ( !defined $new_default && defined $old_default );
819 # add geometry column and constraints
820 push @out, add_geometry_column($to_field) if is_geometry($to_field);
821 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
823 return wantarray ? @out : join(";\n", @out);
826 sub rename_field { alter_field(@_) }
830 my ($new_field,$options) = @_;
832 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
833 _generator($options)->quote($new_field->table->name),
834 create_field($new_field, $options));
835 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
836 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
843 my ($old_field, $options) = @_;
845 my $generator = _generator($options);
847 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
848 $generator->quote($old_field->table->name),
849 $generator->quote($old_field->name));
850 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
854 sub add_geometry_column{
855 my ($field,$options) = @_;
857 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
859 $field->table->schema->name,
860 $options->{table} ? $options->{table} : $field->table->name,
862 $field->extra->{dimensions},
863 $field->extra->{srid},
864 $field->extra->{geometry_type});
868 sub drop_geometry_column
872 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
873 $field->table->schema->name,
879 sub add_geometry_constraints{
882 my @constraints = create_geometry_constraints($field);
884 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
889 sub drop_geometry_constraints{
892 my @constraints = create_geometry_constraints($field);
894 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
900 my ($to_table, $options) = @_;
901 my $generator = _generator($options);
902 my $out = sprintf('ALTER TABLE %s %s',
903 $generator->quote($to_table->name),
904 $options->{alter_table_action});
905 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
910 my ($old_table, $new_table, $options) = @_;
911 my $generator = _generator($options);
912 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
914 my @geometry_changes;
915 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
916 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
918 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
920 return alter_table($old_table, $options);
923 sub alter_create_index {
924 my ($index, $options) = @_;
925 my $generator = _generator($options);
926 my ($idef, $constraints) = create_index($index, {
927 generator => $generator,
929 return $index->type eq NORMAL ? $idef
930 : sprintf('ALTER TABLE %s ADD %s',
931 $generator->quote($index->table->name),
932 join(q{}, @$constraints)
936 sub alter_drop_index {
937 my ($index, $options) = @_;
938 return 'DROP INDEX '. _generator($options)->quote($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 use it
952 } elsif ( $c->type eq FOREIGN_KEY ) {
953 # Doesn't have a name, and is foreign key, append '_fkey'
954 $c_name = $c->table->name . '_' . ($c->fields)[0] . '_fkey';
955 } elsif ( $c->type eq PRIMARY_KEY ) {
956 # Doesn't have a name, and is primary key, append '_pkey'
957 $c_name = $c->table->name . '_pkey';
961 'ALTER TABLE %s DROP CONSTRAINT %s',
962 map { $generator->quote($_) } $c->table->name, $c_name,
966 sub alter_create_constraint {
967 my ($index, $options) = @_;
968 my $generator = _generator($options);
969 my ($defs, $fks) = create_constraint(@_);
971 # return if there are no constraint definitions so we don't run
972 # into output like this:
973 # ALTER TABLE users ADD ;
975 return unless(@{$defs} || @{$fks});
976 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
977 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
978 'ADD', join(q{}, @{$defs}, @{$fks})
983 my ($table, $options) = @_;
984 my $generator = _generator($options);
985 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
987 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
989 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
993 sub batch_alter_table {
994 my ( $table, $diff_hash, $options ) = @_;
996 # as long as we're not renaming the table we don't need to be here
997 if ( @{$diff_hash->{rename_table}} == 0 ) {
998 return batch_alter_table_statements($diff_hash, $options);
1001 # first we need to perform drops which are on old table
1002 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
1003 alter_drop_constraint
1008 # next comes the rename_table
1009 my $old_table = $diff_hash->{rename_table}[0][0];
1010 push @sql, rename_table( $old_table, $table, $options );
1012 # for alter_field (and so also rename_field) we need to make sure old
1013 # field has table name set to new table otherwise calling alter_field dies
1014 $diff_hash->{alter_field} =
1015 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1016 $diff_hash->{rename_field} =
1017 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1019 # now add everything else
1020 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1025 alter_create_constraint
1034 # -------------------------------------------------------------------
1035 # Life is full of misery, loneliness, and suffering --
1036 # and it's all over much too soon.
1038 # -------------------------------------------------------------------
1044 SQL::Translator, SQL::Translator::Producer::Oracle.
1048 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.