1 package SQL::Translator::Producer::PostgreSQL;
3 # -------------------------------------------------------------------
4 # Copyright (C) 2002-2009 SQLFairy Authors
6 # This program is free software; you can redistribute it and/or
7 # modify it under the terms of the GNU General Public License as
8 # published by the Free Software Foundation; version 2.
10 # This program is distributed in the hope that it will be useful, but
11 # WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 # General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with this program; if not, write to the Free Software
17 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
19 # -------------------------------------------------------------------
23 SQL::Translator::Producer::PostgreSQL - PostgreSQL producer for SQL::Translator
27 my $t = SQL::Translator->new( parser => '...', producer => 'PostgreSQL' );
32 Creates a DDL suitable for PostgreSQL. Very heavily based on the Oracle
39 use vars qw[ $DEBUG $WARN $VERSION %used_names ];
41 $DEBUG = 0 unless defined $DEBUG;
43 use base qw(SQL::Translator::Producer);
44 use SQL::Translator::Schema::Constants;
45 use SQL::Translator::Utils qw(debug header_comment);
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 = ();
136 =head1 PostgreSQL Create Table Syntax
138 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
139 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
140 | table_constraint } [, ... ]
142 [ INHERITS ( parent_table [, ... ] ) ]
143 [ WITH OIDS | WITHOUT OIDS ]
145 where column_constraint is:
147 [ CONSTRAINT constraint_name ]
148 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
150 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
151 [ ON DELETE action ] [ ON UPDATE action ] }
152 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
154 and table_constraint is:
156 [ CONSTRAINT constraint_name ]
157 { UNIQUE ( column_name [, ... ] ) |
158 PRIMARY KEY ( column_name [, ... ] ) |
159 CHECK ( expression ) |
160 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
161 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
162 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
164 =head1 Create Index Syntax
166 CREATE [ UNIQUE ] INDEX index_name ON table
167 [ USING acc_method ] ( column [ ops_name ] [, ...] )
169 CREATE [ UNIQUE ] INDEX index_name ON table
170 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
175 # -------------------------------------------------------------------
177 my $translator = shift;
178 local $DEBUG = $translator->debug;
179 local $WARN = $translator->show_warnings;
180 my $no_comments = $translator->no_comments;
181 my $add_drop_table = $translator->add_drop_table;
182 my $schema = $translator->schema;
183 my $pargs = $translator->producer_args;
184 local %used_names = ();
186 my $postgres_version = $pargs->{postgres_version} || 0;
189 $qt = '"' if ($translator->quote_table_names);
191 $qf = '"' if ($translator->quote_field_names);
194 push @output, header_comment unless ($no_comments);
196 my (@table_defs, @fks);
197 for my $table ( $schema->get_tables ) {
199 my ($table_def, $fks) = create_table($table,
200 { quote_table_names => $qt,
201 quote_field_names => $qf,
202 no_comments => $no_comments,
203 postgres_version => $postgres_version,
204 add_drop_table => $add_drop_table,});
205 push @table_defs, $table_def;
210 for my $view ( $schema->get_views ) {
211 push @table_defs, create_view($view, {
212 add_drop_view => $add_drop_table,
213 quote_table_names => $qt,
214 quote_field_names => $qf,
215 no_comments => $no_comments,
220 foreach my $trigger ( $schema->get_triggers ) {
221 push @trigger_defs, create_trigger($trigger);
225 push @output, map { "$_;\n\n" } @table_defs;
227 push @output, "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
228 push @output, map { "$_;\n\n" } @fks;
232 push @output, "--\n-- Triggers \n--\n\n" unless $no_comments;
233 push @output, map { "$_;\n\n" } @trigger_defs;
238 warn "Truncated " . keys( %truncated ) . " names:\n";
239 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
243 warn "Encounted " . keys( %unreserve ) .
244 " unsafe names in schema (reserved or invalid):\n";
245 warn "\t" . join( "\n\t", sort keys %unreserve ) . "\n";
249 foreach my $procedure ( $schema->get_procedures ) {
250 my (@comments, $procedure_name);
252 $procedure_name = $procedure->name();
254 "--\n-- Procedure: $procedure_name\n--" unless $no_comments;
256 # text of procedure already has the 'create procedure' stuff
257 # so there is no need to do anything fancy. However, we should
258 # think about doing fancy stuff with granting permissions and
261 push (@output, join("\n\n",
270 : join ('', @output);
273 # -------------------------------------------------------------------
275 my $basename = shift || '';
276 my $type = shift || '';
277 my $scope = shift || '';
278 my $critical = shift || '';
279 my $basename_orig = $basename;
280 # my $max_id_length = 62;
282 ? $max_id_length - (length($type) + 1)
284 $basename = substr( $basename, 0, $max_name )
285 if length( $basename ) > $max_name;
286 my $name = $type ? "${type}_$basename" : $basename;
288 if ( $basename ne $basename_orig and $critical ) {
289 my $show_type = $type ? "+'$type'" : "";
290 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
291 "character limit to make '$name'\n" if $WARN;
292 $truncated{ $basename_orig } = $name;
295 $scope ||= \%global_names;
296 if ( my $prev = $scope->{ $name } ) {
297 my $name_orig = $name;
298 $name .= sprintf( "%02d", ++$prev );
299 substr($name, $max_id_length - 3) = "00"
300 if length( $name ) > $max_id_length;
302 warn "The name '$name_orig' has been changed to ",
303 "'$name' to make it unique.\n" if $WARN;
305 $scope->{ $name_orig }++;
312 # -------------------------------------------------------------------
314 my $name = shift || '';
315 my $schema_obj_name = shift || '';
317 my ( $suffix ) = ( $name =~ s/(\W.*)$// ) ? $1 : '';
319 # also trap fields that don't begin with a letter
320 return $name if (!$reserved{ uc $name }) && $name =~ /^[a-z]/i;
322 if ( $schema_obj_name ) {
323 ++$unreserve{"$schema_obj_name.$name"};
326 ++$unreserve{"$name (table name)"};
329 my $unreserve = sprintf '%s_', $name;
330 return $unreserve.$suffix;
333 # -------------------------------------------------------------------
334 sub next_unused_name {
335 my $name = shift || '';
336 if ( !defined( $used_names{$name} ) ) {
337 $used_names{$name} = $name;
342 while ( defined( $used_names{ $name . $i } ) ) {
346 $used_names{$name} = $name;
353 my ($table, $options) = @_;
355 my $qt = $options->{quote_table_names} || '';
356 my $qf = $options->{quote_field_names} || '';
357 my $no_comments = $options->{no_comments} || 0;
358 my $add_drop_table = $options->{add_drop_table} || 0;
359 my $postgres_version = $options->{postgres_version} || 0;
361 my $table_name = $table->name or next;
362 my ( $fql_tbl_name ) = ( $table_name =~ s/\W(.*)$// ) ? $1 : q{};
363 my $table_name_ur = $qt ? $table_name
364 : $fql_tbl_name ? join('.', $table_name, unreserve($fql_tbl_name))
365 : unreserve($table_name);
366 $table->name($table_name_ur);
368 # print STDERR "$table_name table_name\n";
369 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @type_defs, @type_drops, @fks );
371 push @comments, "--\n-- Table: $table_name_ur\n--\n" unless $no_comments;
373 if ( $table->comments and !$no_comments ){
374 my $c = "-- Comments: \n-- ";
375 $c .= join "\n-- ", $table->comments;
383 my %field_name_scope;
384 for my $field ( $table->get_fields ) {
385 push @field_defs, create_field($field, { quote_table_names => $qt,
386 quote_field_names => $qf,
387 table_name => $table_name_ur,
388 postgres_version => $postgres_version,
389 type_defs => \@type_defs,
390 type_drops => \@type_drops,
391 constraint_defs => \@constraint_defs,});
398 # my $idx_name_default;
399 for my $index ( $table->get_indices ) {
400 my ($idef, $constraints) = create_index($index,
402 quote_field_names => $qf,
403 quote_table_names => $qt,
404 table_name => $table_name,
406 $idef and push @index_defs, $idef;
407 push @constraint_defs, @$constraints;
414 for my $c ( $table->get_constraints ) {
415 my ($cdefs, $fks) = create_constraint($c,
417 quote_field_names => $qf,
418 quote_table_names => $qt,
419 table_name => $table_name,
421 push @constraint_defs, @$cdefs;
428 if(exists $table->{extra}{temporary}) {
429 $temporary = $table->{extra}{temporary} ? "TEMPORARY " : "";
432 my $create_statement;
433 $create_statement = join("\n", @comments);
434 if ($add_drop_table) {
435 if ($postgres_version >= 8.2) {
436 $create_statement .= qq[DROP TABLE IF EXISTS $qt$table_name_ur$qt CASCADE;\n];
437 $create_statement .= join (";\n", @type_drops) . ";\n"
438 if $postgres_version >= 8.3 && scalar @type_drops;
440 $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n];
443 $create_statement .= join(";\n", @type_defs) . ";\n"
444 if $postgres_version >= 8.3 && scalar @type_defs;
445 $create_statement .= qq[CREATE ${temporary}TABLE $qt$table_name_ur$qt (\n].
446 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
449 $create_statement .= @index_defs ? ';' : q{};
450 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
451 . join(";\n", @index_defs);
453 return $create_statement, \@fks;
457 my ($view, $options) = @_;
458 my $qt = $options->{quote_table_names} || '';
459 my $qf = $options->{quote_field_names} || '';
460 my $add_drop_view = $options->{add_drop_view};
462 my $view_name = $view->name;
463 debug("PKG: Looking at view '${view_name}'\n");
466 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n"
467 unless $options->{no_comments};
468 $create .= "DROP VIEW ${qt}${view_name}${qt};\n" if $add_drop_view;
471 my $extra = $view->extra;
472 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
473 $create .= " VIEW ${qt}${view_name}${qt}";
475 if ( my @fields = $view->fields ) {
476 my $field_list = join ', ', map { "${qf}${_}${qf}" } @fields;
477 $create .= " ( ${field_list} )";
480 if ( my $sql = $view->sql ) {
481 $create .= " AS\n ${sql}\n";
484 if ( $extra->{check_option} ) {
485 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
493 my %field_name_scope;
497 my ($field, $options) = @_;
499 my $qt = $options->{quote_table_names} || '';
500 my $qf = $options->{quote_field_names} || '';
501 my $table_name = $field->table->name;
502 my $constraint_defs = $options->{constraint_defs} || [];
503 my $postgres_version = $options->{postgres_version} || 0;
504 my $type_defs = $options->{type_defs} || [];
505 my $type_drops = $options->{type_drops} || [];
507 $field_name_scope{$table_name} ||= {};
508 my $field_name = $field->name;
509 my $field_name_ur = $qf ? $field_name : unreserve($field_name, $table_name );
510 $field->name($field_name_ur);
511 my $field_comments = $field->comments
512 ? "-- " . $field->comments . "\n "
515 my $field_def = $field_comments.qq[$qf$field_name_ur$qf];
520 my @size = $field->size;
521 my $data_type = lc $field->data_type;
522 my %extra = $field->extra;
523 my $list = $extra{'list'} || [];
524 # todo deal with embedded quotes
525 my $commalist = join( ', ', map { qq['$_'] } @$list );
527 if ($postgres_version >= 8.3 && $field->data_type eq 'enum') {
528 my $type_name = $field->table->name . '_' . $field->name . '_type';
529 $field_def .= ' '. $type_name;
530 push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist)";
531 push @$type_drops, "DROP TYPE IF EXISTS $type_name";
533 $field_def .= ' '. convert_datatype($field);
539 my $default = $field->default_value;
540 if ( defined $default ) {
541 SQL::Translator::Producer->_apply_default_value(
547 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
553 # Not null constraint
555 $field_def .= ' NOT NULL' unless $field->is_nullable;
562 my ($index, $options) = @_;
564 my $qt = $options->{quote_table_names} ||'';
565 my $qf = $options->{quote_field_names} ||'';
566 my $table_name = $index->table->name;
567 # my $table_name_ur = $qt ? unreserve($table_name) : $table_name;
569 my ($index_def, @constraint_defs);
571 my $name = $index->name || '';
573 $name = next_unused_name($name);
576 my $type = $index->type || NORMAL;
578 map { $_ =~ s/\(.+\)//; $_ }
579 map { $qt ? $_ : unreserve($_, $table_name ) }
583 my $def_start = qq[CONSTRAINT "$name" ];
584 if ( $type eq PRIMARY_KEY ) {
585 push @constraint_defs, "${def_start}PRIMARY KEY ".
586 '(' .$qf . join( $qf. ', '.$qf, @fields ) . $qf . ')';
587 } elsif ( $type eq UNIQUE ) {
588 push @constraint_defs, "${def_start}UNIQUE " .
589 '(' . $qf . join( $qf. ', '.$qf, @fields ) . $qf.')';
590 } elsif ( $type eq NORMAL ) {
592 "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name}${qt} (".
593 join( ', ', map { qq[$qf$_$qf] } @fields ).
597 warn "Unknown index type ($type) on table $table_name.\n"
601 return $index_def, \@constraint_defs;
604 sub create_constraint {
605 my ($c, $options) = @_;
607 my $qf = $options->{quote_field_names} ||'';
608 my $qt = $options->{quote_table_names} ||'';
609 my $table_name = $c->table->name;
610 my (@constraint_defs, @fks);
612 my $name = $c->name || '';
614 $name = next_unused_name($name);
618 map { $_ =~ s/\(.+\)//; $_ }
619 map { $qt ? $_ : unreserve( $_, $table_name )}
623 map { $_ =~ s/\(.+\)//; $_ }
624 map { $qt ? $_ : unreserve( $_, $table_name )}
625 $c->reference_fields;
627 next if !@fields && $c->type ne CHECK_C;
628 my $def_start = $name ? qq[CONSTRAINT "$name" ] : '';
629 if ( $c->type eq PRIMARY_KEY ) {
630 push @constraint_defs, "${def_start}PRIMARY KEY ".
631 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
632 } elsif ( $c->type eq UNIQUE ) {
633 $name = next_unused_name($name);
634 push @constraint_defs, "${def_start}UNIQUE " .
635 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
636 } elsif ( $c->type eq CHECK_C ) {
637 my $expression = $c->expression;
638 push @constraint_defs, "${def_start}CHECK ($expression)";
639 } elsif ( $c->type eq FOREIGN_KEY ) {
640 my $def .= "ALTER TABLE ${qt}${table_name}${qt} ADD FOREIGN KEY (" .
641 join( ', ', map { qq[$qf$_$qf] } @fields ) . ')' .
642 "\n REFERENCES " . $qt . $c->reference_table . $qt;
645 $def .= ' ('.$qf . join( $qf.', '.$qf, @rfields ) . $qf.')';
648 if ( $c->match_type ) {
650 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
653 if ( $c->on_delete ) {
654 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
657 if ( $c->on_update ) {
658 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
661 if ( $c->deferrable ) {
662 $def .= ' DEFERRABLE';
668 return \@constraint_defs, \@fks;
674 # CREATE TRIGGER tree_change_trig BEFORE DELETE or INSERT or UPDATE ON type FOR EACH ROW EXECUTE PROCEDURE type_tree_change();
675 my $db_events = join ' or ', $trigger->database_events;
676 my $out = sprintf('CREATE TRIGGER %s %s %s ON %s %s',
678 $trigger->perform_action_when || 'AFTER',
680 $trigger->table->name,
692 my @size = $field->size;
693 my $data_type = lc $field->data_type;
695 if ( $data_type eq 'enum' ) {
697 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
698 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
699 # push @$constraint_defs,
700 # qq[CONSTRAINT "$chk_name" CHECK ($qf$field_name$qf ].
701 # qq[IN ($commalist))];
702 $data_type = 'character varying';
704 elsif ( $data_type eq 'set' ) {
705 $data_type = 'character varying';
707 elsif ( $field->is_auto_increment ) {
708 if ( defined $size[0] && $size[0] > 11 ) {
709 $data_type = 'bigserial';
712 $data_type = 'serial';
717 $data_type = defined $translate{ $data_type } ?
718 $translate{ $data_type } :
722 if ( $data_type =~ /^time/i || $data_type =~ /^interval/i ) {
723 if ( defined $size[0] && $size[0] > 6 ) {
728 if ( $data_type eq 'integer' ) {
729 if ( defined $size[0] && $size[0] > 0) {
730 if ( $size[0] > 10 ) {
731 $data_type = 'bigint';
733 elsif ( $size[0] < 5 ) {
734 $data_type = 'smallint';
737 $data_type = 'integer';
741 $data_type = 'integer';
744 my @type_without_size = qw/bigint boolean box bytea cidr circle date inet
745 integer smallint text line lseg macaddr money
746 path point polygon real/;
747 foreach (@type_without_size) {
748 if ( $data_type =~ qr/$_/ ) {
753 if (defined $size[0] && $size[0] > 0 && $data_type =~ /^time/i ) {
754 $data_type =~ s/^(time.*?)( with.*)?$/$1($size[0])/;
755 $data_type .= $2 if(defined $2);
756 } elsif ( defined $size[0] && $size[0] > 0 ) {
757 $data_type .= '(' . join( ',', @size ) . ')';
768 my ($from_field, $to_field) = @_;
770 die "Can't alter field in another table"
771 if($from_field->table->name ne $to_field->table->name);
774 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
775 $to_field->table->name,
776 $to_field->name) if(!$to_field->is_nullable and
777 $from_field->is_nullable);
779 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL',
780 $to_field->table->name,
782 if ( !$from_field->is_nullable and $to_field->is_nullable );
785 my $from_dt = convert_datatype($from_field);
786 my $to_dt = convert_datatype($to_field);
787 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
788 $to_field->table->name,
790 $to_dt) if($to_dt ne $from_dt);
792 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
793 $to_field->table->name,
795 $to_field->name) if($from_field->name ne $to_field->name);
797 my $old_default = $from_field->default_value;
798 my $new_default = $to_field->default_value;
799 my $default_value = $to_field->default_value;
801 # fixes bug where output like this was created:
802 # ALTER TABLE users ALTER COLUMN column SET DEFAULT ThisIsUnescaped;
803 if(ref $default_value eq "SCALAR" ) {
804 $default_value = $$default_value;
805 } elsif( defined $default_value && $to_dt =~ /^(character|text)/xsmi ) {
806 $default_value =~ s/'/''/xsmg;
807 $default_value = q(') . $default_value . q(');
810 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
811 $to_field->table->name,
814 if ( defined $new_default &&
815 (!defined $old_default || $old_default ne $new_default) );
817 # fixes bug where removing the DEFAULT statement of a column
818 # would result in no change
820 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT',
821 $to_field->table->name,
823 if ( !defined $new_default && defined $old_default );
826 return wantarray ? @out : join("\n", @out);
829 sub rename_field { alter_field(@_) }
833 my ($new_field) = @_;
835 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
836 $new_field->table->name,
837 create_field($new_field));
844 my ($old_field) = @_;
846 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
847 $old_field->table->name,
854 my ($to_table, $options) = @_;
855 my $qt = $options->{quote_table_names} || '';
856 my $out = sprintf('ALTER TABLE %s %s',
857 $qt . $to_table->name . $qt,
858 $options->{alter_table_action});
863 my ($old_table, $new_table, $options) = @_;
864 my $qt = $options->{quote_table_names} || '';
865 $options->{alter_table_action} = "RENAME TO $qt$new_table$qt";
866 return alter_table($old_table, $options);
869 sub alter_create_index {
870 my ($index, $options) = @_;
871 my $qt = $options->{quote_table_names} || '';
872 my $qf = $options->{quote_field_names} || '';
873 my ($idef, $constraints) = create_index($index, {
874 quote_field_names => $qf,
875 quote_table_names => $qt,
876 table_name => $index->table->name,
878 return $index->type eq NORMAL ? $idef
879 : sprintf('ALTER TABLE %s ADD %s',
880 $qt . $index->table->name . $qt,
881 join(q{}, @$constraints)
885 sub alter_drop_index {
886 my ($index, $options) = @_;
887 my $index_name = $index->name;
888 return "DROP INDEX $index_name";
891 sub alter_drop_constraint {
892 my ($c, $options) = @_;
893 my $qt = $options->{quote_table_names} || '';
894 my $qc = $options->{quote_field_names} || '';
895 my $out = sprintf('ALTER TABLE %s DROP CONSTRAINT %s',
896 $qt . $c->table->name . $qt,
897 $qc . $c->name . $qc );
901 sub alter_create_constraint {
902 my ($index, $options) = @_;
903 my $qt = $options->{quote_table_names} || '';
904 my ($defs, $fks) = create_constraint(@_);
906 # return if there are no constraint definitions so we don't run
907 # into output like this:
908 # ALTER TABLE users ADD ;
910 return unless(@{$defs} || @{$fks});
911 return $index->type eq FOREIGN_KEY ? join(q{}, @{$fks})
912 : join( ' ', 'ALTER TABLE', $qt.$index->table->name.$qt,
913 'ADD', join(q{}, @{$defs}, @{$fks})
918 my ($table, $options) = @_;
919 my $qt = $options->{quote_table_names} || '';
920 return "DROP TABLE $qt$table$qt CASCADE";
925 # -------------------------------------------------------------------
926 # Life is full of misery, loneliness, and suffering --
927 # and it's all over much too soon.
929 # -------------------------------------------------------------------
935 SQL::Translator, SQL::Translator::Producer::Oracle.
939 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.