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',
92 my %reserved = map { $_, 1 } qw[
93 ALL ANALYSE ANALYZE AND ANY AS ASC
95 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
96 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
97 DEFAULT DEFERRABLE DESC DISTINCT DO
99 FALSE FOR FOREIGN FREEZE FROM FULL
101 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
102 JOIN LEADING LEFT LIKE LIMIT
103 NATURAL NEW NOT NOTNULL NULL
104 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
105 PRIMARY PUBLIC REFERENCES RIGHT
106 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
107 UNION UNIQUE USER USING VERBOSE WHEN WHERE
110 # my $max_id_length = 62;
111 my %used_identifiers = ();
117 =head1 PostgreSQL Create Table Syntax
119 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
120 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
121 | table_constraint } [, ... ]
123 [ INHERITS ( parent_table [, ... ] ) ]
124 [ WITH OIDS | WITHOUT OIDS ]
126 where column_constraint is:
128 [ CONSTRAINT constraint_name ]
129 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
131 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
132 [ ON DELETE action ] [ ON UPDATE action ] }
133 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
135 and table_constraint is:
137 [ CONSTRAINT constraint_name ]
138 { UNIQUE ( column_name [, ... ] ) |
139 PRIMARY KEY ( column_name [, ... ] ) |
140 CHECK ( expression ) |
141 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
142 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
143 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
145 =head1 Create Index Syntax
147 CREATE [ UNIQUE ] INDEX index_name ON table
148 [ USING acc_method ] ( column [ ops_name ] [, ...] )
150 CREATE [ UNIQUE ] INDEX index_name ON table
151 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
157 my $translator = shift;
158 local $DEBUG = $translator->debug;
159 local $WARN = $translator->show_warnings;
160 my $no_comments = $translator->no_comments;
161 my $add_drop_table = $translator->add_drop_table;
162 my $schema = $translator->schema;
163 my $pargs = $translator->producer_args;
164 my $postgres_version = parse_dbms_version(
165 $pargs->{postgres_version}, 'perl'
168 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
171 push @output, header_comment unless ($no_comments);
173 my (@table_defs, @fks);
175 for my $table ( $schema->get_tables ) {
177 my ($table_def, $fks) = create_table($table, {
178 generator => $generator,
179 no_comments => $no_comments,
180 postgres_version => $postgres_version,
181 add_drop_table => $add_drop_table,
182 type_defs => \%type_defs,
185 push @table_defs, $table_def;
189 for my $view ( $schema->get_views ) {
190 push @table_defs, create_view($view, {
191 postgres_version => $postgres_version,
192 add_drop_view => $add_drop_table,
193 generator => $generator,
194 no_comments => $no_comments,
198 for my $trigger ( $schema->get_triggers ) {
199 push @table_defs, create_trigger( $trigger, {
200 add_drop_trigger => $add_drop_table,
201 generator => $generator,
202 no_comments => $no_comments,
206 push @output, map { "$_;\n\n" } values %type_defs;
207 push @output, map { "$_;\n\n" } @table_defs;
209 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
210 push @output, map { "$_;\n\n" } @fks;
215 warn "Truncated " . keys( %truncated ) . " names:\n";
216 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
222 : join ('', @output);
226 my $basename = shift || '';
227 my $type = shift || '';
228 my $scope = shift || '';
229 my $critical = shift || '';
230 my $basename_orig = $basename;
231 # my $max_id_length = 62;
233 ? $max_id_length - (length($type) + 1)
235 $basename = substr( $basename, 0, $max_name )
236 if length( $basename ) > $max_name;
237 my $name = $type ? "${type}_$basename" : $basename;
239 if ( $basename ne $basename_orig and $critical ) {
240 my $show_type = $type ? "+'$type'" : "";
241 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
242 "character limit to make '$name'\n" if $WARN;
243 $truncated{ $basename_orig } = $name;
246 $scope ||= \%global_names;
247 if ( my $prev = $scope->{ $name } ) {
248 my $name_orig = $name;
249 $name .= sprintf( "%02d", ++$prev );
250 substr($name, $max_id_length - 3) = "00"
251 if length( $name ) > $max_id_length;
253 warn "The name '$name_orig' has been changed to ",
254 "'$name' to make it unique.\n" if $WARN;
256 $scope->{ $name_orig }++;
266 return 1 if $field->data_type eq 'geometry';
272 return 1 if $field->data_type eq 'geography';
277 my ($table, $options) = @_;
279 my $generator = _generator($options);
280 my $no_comments = $options->{no_comments} || 0;
281 my $add_drop_table = $options->{add_drop_table} || 0;
282 my $postgres_version = $options->{postgres_version} || 0;
283 my $type_defs = $options->{type_defs} || {};
285 my $table_name = $table->name or next;
286 my $table_name_qt = $generator->quote($table_name);
288 # print STDERR "$table_name table_name\n";
289 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @fks );
291 push @comments, "--\n-- Table: $table_name\n--\n" unless $no_comments;
293 if ( $table->comments and !$no_comments ){
294 my $c = "-- Comments: \n-- ";
295 $c .= join "\n-- ", $table->comments;
303 my %field_name_scope;
304 for my $field ( $table->get_fields ) {
305 push @field_defs, create_field($field, {
306 generator => $generator,
307 postgres_version => $postgres_version,
308 type_defs => $type_defs,
309 constraint_defs => \@constraint_defs,
317 # my $idx_name_default;
318 for my $index ( $table->get_indices ) {
319 my ($idef, $constraints) = create_index($index, {
320 generator => $generator,
322 $idef and push @index_defs, $idef;
323 push @constraint_defs, @$constraints;
330 for my $c ( $table->get_constraints ) {
331 my ($cdefs, $fks) = create_constraint($c, {
332 generator => $generator,
334 push @constraint_defs, @$cdefs;
341 if(exists $table->extra->{temporary}) {
342 $temporary = $table->extra->{temporary} ? "TEMPORARY " : "";
345 my $create_statement;
346 $create_statement = join("\n", @comments);
347 if ($add_drop_table) {
348 if ($postgres_version >= 8.002) {
349 $create_statement .= "DROP TABLE IF EXISTS $table_name_qt CASCADE;\n";
351 $create_statement .= "DROP TABLE $table_name_qt CASCADE;\n";
354 $create_statement .= "CREATE ${temporary}TABLE $table_name_qt (\n" .
355 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
358 $create_statement .= @index_defs ? ';' : q{};
359 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
360 . join(";\n", @index_defs);
365 if(grep { is_geometry($_) } $table->get_fields){
366 $create_statement .= ";";
367 my @geometry_columns;
368 foreach my $col ($table->get_fields) { push(@geometry_columns,$col) if is_geometry($col); }
369 $create_statement .= "\n".join("\n", map{ drop_geometry_column($_) } @geometry_columns) if $options->{add_drop_table};
370 $create_statement .= "\n".join("\n", map{ add_geometry_column($_) } @geometry_columns);
373 return $create_statement, \@fks;
377 my ($view, $options) = @_;
378 my $generator = _generator($options);
379 my $postgres_version = $options->{postgres_version} || 0;
380 my $add_drop_view = $options->{add_drop_view};
382 my $view_name = $view->name;
383 debug("PKG: Looking at view '${view_name}'\n");
386 $create .= "--\n-- View: " . $generator->quote($view_name) . "\n--\n"
387 unless $options->{no_comments};
388 if ($add_drop_view) {
389 if ($postgres_version >= 8.002) {
390 $create .= "DROP VIEW IF EXISTS " . $generator->quote($view_name) . ";\n";
392 $create .= "DROP VIEW " . $generator->quote($view_name) . ";\n";
397 my $extra = $view->extra;
398 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
399 $create .= " VIEW " . $generator->quote($view_name);
401 if ( my @fields = $view->fields ) {
402 my $field_list = join ', ', map { $generator->quote($_) } @fields;
403 $create .= " ( ${field_list} )";
406 if ( my $sql = $view->sql ) {
407 $create .= " AS\n ${sql}\n";
410 if ( $extra->{check_option} ) {
411 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
419 my %field_name_scope;
423 my ($field, $options) = @_;
425 my $generator = _generator($options);
426 my $table_name = $field->table->name;
427 my $constraint_defs = $options->{constraint_defs} || [];
428 my $postgres_version = $options->{postgres_version} || 0;
429 my $type_defs = $options->{type_defs} || {};
431 $field_name_scope{$table_name} ||= {};
432 my $field_name = $field->name;
433 my $field_comments = $field->comments
434 ? "-- " . $field->comments . "\n "
437 my $field_def = $field_comments . $generator->quote($field_name);
442 my @size = $field->size;
443 my $data_type = lc $field->data_type;
444 my %extra = $field->extra;
445 my $list = $extra{'list'} || [];
446 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
448 if ($postgres_version >= 8.003 && $field->data_type eq 'enum') {
449 my $type_name = $extra{'custom_type_name'} || $field->table->name . '_' . $field->name . '_type';
450 $field_def .= ' '. $type_name;
451 my $new_type_def = "DROP TYPE IF EXISTS $type_name CASCADE;\n" .
452 "CREATE TYPE $type_name AS ENUM ($commalist)";
453 if (! exists $type_defs->{$type_name} ) {
454 $type_defs->{$type_name} = $new_type_def;
455 } elsif ( $type_defs->{$type_name} ne $new_type_def ) {
456 die "Attempted to redefine type name '$type_name' as a different type.\n";
459 $field_def .= ' '. convert_datatype($field);
465 __PACKAGE__->_apply_default_value(
471 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
476 # Not null constraint
478 $field_def .= ' NOT NULL' unless $field->is_nullable;
481 # Geometry constraints
483 if(is_geometry($field)){
484 foreach ( create_geometry_constraints($field) ) {
485 my ($cdefs, $fks) = create_constraint($_, {
486 generator => $generator,
488 push @$constraint_defs, @$cdefs;
497 sub create_geometry_constraints{
501 push @constraints, SQL::Translator::Schema::Constraint->new(
502 name => "enforce_dims_".$field->name,
503 expression => "(ST_NDims($field) = ".$field->extra->{dimensions}.")",
504 table => $field->table,
508 push @constraints, SQL::Translator::Schema::Constraint->new(
509 name => "enforce_srid_".$field->name,
510 expression => "(ST_SRID($field) = ".$field->extra->{srid}.")",
511 table => $field->table,
514 push @constraints, SQL::Translator::Schema::Constraint->new(
515 name => "enforce_geotype_".$field->name,
516 expression => "(GeometryType($field) = '".$field->extra->{geometry_type}."'::text OR $field IS NULL)",
517 table => $field->table,
526 my ($index, $options) = @_;
528 my $generator = _generator($options);
529 my $table_name = $index->table->name;
531 my ($index_def, @constraint_defs);
535 || join('_', $table_name, 'idx', ++$index_name{ $table_name });
537 my $type = $index->type || NORMAL;
538 my @fields = $index->fields;
539 return unless @fields;
541 my $def_start = 'CONSTRAINT ' . $generator->quote($name) . ' ';
542 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
543 if ( $type eq PRIMARY_KEY ) {
544 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
546 elsif ( $type eq UNIQUE ) {
547 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
549 elsif ( $type eq NORMAL ) {
551 'CREATE INDEX ' . $generator->quote($name) . ' on ' . $generator->quote($table_name) . ' ' . $field_names
555 warn "Unknown index type ($type) on table $table_name.\n"
559 return $index_def, \@constraint_defs;
562 sub create_constraint
564 my ($c, $options) = @_;
566 my $generator = _generator($options);
567 my $table_name = $c->table->name;
568 my (@constraint_defs, @fks);
570 my $name = $c->name || '';
572 my @fields = grep { defined } $c->fields;
574 my @rfields = grep { defined } $c->reference_fields;
576 next if !@fields && $c->type ne CHECK_C;
577 my $def_start = $name ? 'CONSTRAINT ' . $generator->quote($name) . ' ' : '';
578 my $field_names = '(' . join(", ", (map { $_ =~ /\(.*\)/ ? $_ : ( $generator->quote($_) ) } @fields)) . ')';
579 if ( $c->type eq PRIMARY_KEY ) {
580 push @constraint_defs, "${def_start}PRIMARY KEY ".$field_names;
582 elsif ( $c->type eq UNIQUE ) {
583 push @constraint_defs, "${def_start}UNIQUE " .$field_names;
585 elsif ( $c->type eq CHECK_C ) {
586 my $expression = $c->expression;
587 push @constraint_defs, "${def_start}CHECK ($expression)";
589 elsif ( $c->type eq FOREIGN_KEY ) {
590 my $def .= "ALTER TABLE " . $generator->quote($table_name) . " ADD ${def_start}FOREIGN KEY $field_names"
591 . "\n REFERENCES " . $generator->quote($c->reference_table);
594 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
597 if ( $c->match_type ) {
599 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
602 if ( $c->on_delete ) {
603 $def .= ' ON DELETE '. $c->on_delete;
606 if ( $c->on_update ) {
607 $def .= ' ON UPDATE '. $c->on_update;
610 if ( $c->deferrable ) {
611 $def .= ' DEFERRABLE';
617 return \@constraint_defs, \@fks;
621 my ($trigger,$options) = @_;
622 my $generator = _generator($options);
626 push @statements, sprintf( 'DROP TRIGGER IF EXISTS %s', $generator->quote($trigger->name) )
627 if $options->{add_drop_trigger};
629 my $scope = $trigger->scope || '';
630 $scope = " FOR EACH $scope" if $scope;
632 push @statements, sprintf(
633 'CREATE TRIGGER %s %s %s ON %s%s %s',
634 $generator->quote($trigger->name),
635 $trigger->perform_action_when,
636 join( ' OR ', @{ $trigger->database_events } ),
637 $generator->quote($trigger->on_table),
649 my @size = $field->size;
650 my $data_type = lc $field->data_type;
651 my $array = $data_type =~ s/\[\]$//;
653 if ( $data_type eq 'enum' ) {
655 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
656 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
657 # push @$constraint_defs,
658 # 'CONSTRAINT "$chk_name" CHECK (' . $generator->quote(field_name) .
659 # qq[IN ($commalist))];
660 $data_type = 'character varying';
662 elsif ( $data_type eq 'set' ) {
663 $data_type = 'character varying';
665 elsif ( $field->is_auto_increment ) {
666 if ( defined $size[0] && $size[0] > 11 ) {
667 $data_type = 'bigserial';
670 $data_type = 'serial';
675 $data_type = defined $translate{ lc $data_type } ?
676 $translate{ lc $data_type } :
680 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
681 if ( defined $size[0] && $size[0] > 6 ) {
686 if ( $data_type eq 'integer' ) {
687 if ( defined $size[0] && $size[0] > 0) {
688 if ( $size[0] > 10 ) {
689 $data_type = 'bigint';
691 elsif ( $size[0] < 5 ) {
692 $data_type = 'smallint';
695 $data_type = 'integer';
699 $data_type = 'integer';
703 my $type_with_size = join('|',
704 'bit', 'varbit', 'character', 'bit varying', 'character varying',
705 'time', 'timestamp', 'interval', 'numeric', 'float'
708 if ( $data_type !~ /$type_with_size/ ) {
712 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
713 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
714 $data_type .= $2 if(defined $2);
715 } elsif ( defined $size[0] && $size[0] > 0 ) {
716 $data_type .= '(' . join( ',', @size ) . ')';
726 if($data_type eq 'geography'){
727 $data_type .= '('.$field->extra->{geography_type}.','. $field->extra->{srid} .')'
736 my ($from_field, $to_field) = @_;
738 die "Can't alter field in another table"
739 if($from_field->table->name ne $to_field->table->name);
743 # drop geometry column and constraints
744 push @out, drop_geometry_column($from_field) if is_geometry($from_field);
745 push @out, drop_geometry_constraints($from_field) if is_geometry($from_field);
747 # it's necessary to start with rename column cause this would affect
748 # all of the following statements which would be broken if do the
750 # BUT: drop geometry is done before the rename, cause it work's on the
751 # $from_field directly
752 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
753 $to_field->table->name,
755 $to_field->name) if($from_field->name ne $to_field->name);
757 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
758 $to_field->table->name,
759 $to_field->name) if(!$to_field->is_nullable and
760 $from_field->is_nullable);
762 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
763 $to_field->table->name,
765 if ( !$from_field->is_nullable and $to_field->is_nullable );
768 my $from_dt = convert_datatype($from_field);
769 my $to_dt = convert_datatype($to_field);
770 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
771 $to_field->table->name,
773 $to_dt) if($to_dt ne $from_dt);
775 my $old_default = $from_field->default_value;
776 my $new_default = $to_field->default_value;
777 my $default_value = $to_field->default_value;
779 # fixes bug where output like this was created:
780 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
781 if(ref $default_value eq "SCALAR" ) {
782 $default_value = $$default_value;
783 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
784 $default_value = __PACKAGE__->_quote_string($default_value);
787 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
788 $to_field->table->name,
791 if ( defined $new_default &&
792 (!defined $old_default || $old_default ne $new_default) );
794 # fixes bug where removing the DEFAULT statement of a column
795 # would result in no change
797 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
798 $to_field->table->name,
800 if ( !defined $new_default && defined $old_default );
802 # add geometry column and constraints
803 push @out, add_geometry_column($to_field) if is_geometry($to_field);
804 push @out, add_geometry_constraints($to_field) if is_geometry($to_field);
806 return wantarray ? @out : join(";\n", @out);
809 sub rename_field { alter_field(@_) }
813 my ($new_field) = @_;
815 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
816 $new_field->table->name,
817 create_field($new_field));
818 $out .= "\n".add_geometry_column($new_field) if is_geometry($new_field);
819 $out .= "\n".add_geometry_constraints($new_field) if is_geometry($new_field);
826 my ($old_field, $options) = @_;
828 my $generator = _generator($options);
830 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
831 $generator->quote($old_field->table->name),
832 $generator->quote($old_field->name));
833 $out .= "\n".drop_geometry_column($old_field) if is_geometry($old_field);
837 sub add_geometry_column{
838 my ($field,$options) = @_;
840 my $out = sprintf("INSERT INTO geometry_columns VALUES ('%s','%s','%s','%s','%s','%s','%s')",
842 $field->table->schema->name,
843 $options->{table} ? $options->{table} : $field->table->name,
845 $field->extra->{dimensions},
846 $field->extra->{srid},
847 $field->extra->{geometry_type});
851 sub drop_geometry_column
855 my $out = sprintf("DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s' AND f_geometry_column = '%s'",
856 $field->table->schema->name,
862 sub add_geometry_constraints{
865 my @constraints = create_geometry_constraints($field);
867 my $out = join("\n", map { alter_create_constraint($_); } @constraints);
872 sub drop_geometry_constraints{
875 my @constraints = create_geometry_constraints($field);
877 my $out = join("\n", map { alter_drop_constraint($_); } @constraints);
883 my ($to_table, $options) = @_;
884 my $generator = _generator($options);
885 my $out = sprintf('ALTER TABLE %s %s',
886 $generator->quote($to_table->name),
887 $options->{alter_table_action});
888 $out .= "\n".$options->{geometry_changes} if $options->{geometry_changes};
893 my ($old_table, $new_table, $options) = @_;
894 my $generator = _generator($options);
895 $options->{alter_table_action} = "RENAME TO " . $generator->quote($new_table);
897 my @geometry_changes;
898 push @geometry_changes, map { drop_geometry_column($_); } grep { is_geometry($_) } $old_table->get_fields;
899 push @geometry_changes, map { add_geometry_column($_, { table => $new_table }); } grep { is_geometry($_) } $old_table->get_fields;
901 $options->{geometry_changes} = join ("\n",@geometry_changes) if scalar(@geometry_changes);
903 return alter_table($old_table, $options);
906 sub alter_create_index {
907 my ($index, $options) = @_;
908 my $generator = _generator($options);
909 my ($idef, $constraints) = create_index($index, {
910 generator => $generator,
912 return $index->type eq NORMAL ? $idef
913 : sprintf('ALTER TABLE %s ADD %s',
914 $generator->quote($index->table->name),
915 join(q{}, @$constraints)
919 sub alter_drop_index {
920 my ($index, $options) = @_;
921 my $index_name = $index->name;
922 return "DROP INDEX $index_name";
925 sub alter_drop_constraint {
926 my ($c, $options) = @_;
927 my $generator = _generator($options);
929 # attention: Postgres has a very special naming structure for naming
930 # foreign keys and primary keys. It names them using the name of the
931 # table as prefix and fkey or pkey as suffix, concatenated by an underscore
934 # Already has a name, just quote it
935 $c_name = $generator->quote($c->name);
936 } elsif ( $c->type eq FOREIGN_KEY ) {
937 # Doesn't have a name, and is foreign key, append '_fkey'
938 $c_name = $generator->quote($c->table->name . '_' .
939 ($c->fields)[0] . '_fkey');
940 } elsif ( $c->type eq PRIMARY_KEY ) {
941 # Doesn't have a name, and is primary key, append '_pkey'
942 $c_name = $generator->quote($c->table->name . '_pkey');
946 'ALTER TABLE %s DROP CONSTRAINT %s',
947 $generator->quote($c->table->name), $c_name
951 sub alter_create_constraint {
952 my ($index, $options) = @_;
953 my $generator = _generator($options);
954 my ($defs, $fks) = create_constraint(@_);
956 # return if there are no constraint definitions so we don't run
957 # into output like this:
958 # ALTER TABLE users ADD ;
960 return unless(@{$defs} || @{$fks});
961 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
962 : join( ' ', 'ALTER TABLE', $generator->quote($index->table->name),
963 'ADD', join(q{}, @{$defs}, @{$fks})
968 my ($table, $options) = @_;
969 my $generator = _generator($options);
970 my $out = "DROP TABLE " . $generator->quote($table) . " CASCADE";
972 my @geometry_drops = map { drop_geometry_column($_); } grep { is_geometry($_) } $table->get_fields;
974 $out .= "\n".join("\n",@geometry_drops) if scalar(@geometry_drops);
978 sub batch_alter_table {
979 my ( $table, $diff_hash, $options ) = @_;
981 # as long as we're not renaming the table we don't need to be here
982 if ( @{$diff_hash->{rename_table}} == 0 ) {
983 return batch_alter_table_statements($diff_hash, $options);
986 # first we need to perform drops which are on old table
987 my @sql = batch_alter_table_statements($diff_hash, $options, qw(
988 alter_drop_constraint
993 # next comes the rename_table
994 my $old_table = $diff_hash->{rename_table}[0][0];
995 push @sql, rename_table( $old_table, $table, $options );
997 # for alter_field (and so also rename_field) we need to make sure old
998 # field has table name set to new table otherwise calling alter_field dies
999 $diff_hash->{alter_field} =
1000 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{alter_field}}];
1001 $diff_hash->{rename_field} =
1002 [map { $_->[0]->table($table) && $_ } @{$diff_hash->{rename_field}}];
1004 # now add everything else
1005 push @sql, batch_alter_table_statements($diff_hash, $options, qw(
1010 alter_create_constraint
1019 # -------------------------------------------------------------------
1020 # Life is full of misery, loneliness, and suffering --
1021 # and it's all over much too soon.
1023 # -------------------------------------------------------------------
1029 SQL::Translator, SQL::Translator::Producer::Oracle.
1033 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.