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 $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,
219 $output = join(";\n\n", @table_defs) . ";\n\n";
221 $output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
222 $output .= join( ";\n\n", @fks ) . ";\n";
227 warn "Truncated " . keys( %truncated ) . " names:\n";
228 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
232 warn "Encounted " . keys( %unreserve ) .
233 " unsafe names in schema (reserved or invalid):\n";
234 warn "\t" . join( "\n\t", sort keys %unreserve ) . "\n";
241 # -------------------------------------------------------------------
243 my $basename = shift || '';
244 my $type = shift || '';
245 my $scope = shift || '';
246 my $critical = shift || '';
247 my $basename_orig = $basename;
248 # my $max_id_length = 62;
250 ? $max_id_length - (length($type) + 1)
252 $basename = substr( $basename, 0, $max_name )
253 if length( $basename ) > $max_name;
254 my $name = $type ? "${type}_$basename" : $basename;
256 if ( $basename ne $basename_orig and $critical ) {
257 my $show_type = $type ? "+'$type'" : "";
258 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
259 "character limit to make '$name'\n" if $WARN;
260 $truncated{ $basename_orig } = $name;
263 $scope ||= \%global_names;
264 if ( my $prev = $scope->{ $name } ) {
265 my $name_orig = $name;
266 $name .= sprintf( "%02d", ++$prev );
267 substr($name, $max_id_length - 3) = "00"
268 if length( $name ) > $max_id_length;
270 warn "The name '$name_orig' has been changed to ",
271 "'$name' to make it unique.\n" if $WARN;
273 $scope->{ $name_orig }++;
280 # -------------------------------------------------------------------
282 my $name = shift || '';
283 my $schema_obj_name = shift || '';
285 my ( $suffix ) = ( $name =~ s/(\W.*)$// ) ? $1 : '';
287 # also trap fields that don't begin with a letter
288 return $name if (!$reserved{ uc $name }) && $name =~ /^[a-z]/i;
290 if ( $schema_obj_name ) {
291 ++$unreserve{"$schema_obj_name.$name"};
294 ++$unreserve{"$name (table name)"};
297 my $unreserve = sprintf '%s_', $name;
298 return $unreserve.$suffix;
301 # -------------------------------------------------------------------
302 sub next_unused_name {
303 my $name = shift || '';
304 if ( !defined( $used_names{$name} ) ) {
305 $used_names{$name} = $name;
310 while ( defined( $used_names{ $name . $i } ) ) {
314 $used_names{$name} = $name;
321 my ($table, $options) = @_;
323 my $qt = $options->{quote_table_names} || '';
324 my $qf = $options->{quote_field_names} || '';
325 my $no_comments = $options->{no_comments} || 0;
326 my $add_drop_table = $options->{add_drop_table} || 0;
327 my $postgres_version = $options->{postgres_version} || 0;
329 my $table_name = $table->name or next;
330 $table_name = mk_name( $table_name, '', undef, 1 );
331 my ( $fql_tbl_name ) = ( $table_name =~ s/\W(.*)$// ) ? $1 : q{};
332 my $table_name_ur = $qt ? $table_name
333 : $fql_tbl_name ? join('.', $table_name, unreserve($fql_tbl_name))
334 : unreserve($table_name);
335 $table->name($table_name_ur);
337 # print STDERR "$table_name table_name\n";
338 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @type_defs, @type_drops, @fks );
340 push @comments, "--\n-- Table: $table_name_ur\n--\n" unless $no_comments;
342 if ( $table->comments and !$no_comments ){
343 my $c = "-- Comments: \n-- ";
344 $c .= join "\n-- ", $table->comments;
352 my %field_name_scope;
353 for my $field ( $table->get_fields ) {
354 push @field_defs, create_field($field, { quote_table_names => $qt,
355 quote_field_names => $qf,
356 table_name => $table_name_ur,
357 postgres_version => $postgres_version,
358 type_defs => \@type_defs,
359 type_drops => \@type_drops,
360 constraint_defs => \@constraint_defs,});
367 # my $idx_name_default;
368 for my $index ( $table->get_indices ) {
369 my ($idef, $constraints) = create_index($index,
371 quote_field_names => $qf,
372 quote_table_names => $qt,
373 table_name => $table_name,
375 $idef and push @index_defs, $idef;
376 push @constraint_defs, @$constraints;
383 for my $c ( $table->get_constraints ) {
384 my ($cdefs, $fks) = create_constraint($c,
386 quote_field_names => $qf,
387 quote_table_names => $qt,
388 table_name => $table_name,
390 push @constraint_defs, @$cdefs;
397 if(exists $table->{extra}{temporary}) {
398 $temporary = $table->{extra}{temporary} ? "TEMPORARY " : "";
401 my $create_statement;
402 $create_statement = join("\n", @comments);
403 if ($add_drop_table) {
404 if ($postgres_version >= 8.2) {
405 $create_statement .= qq[DROP TABLE IF EXISTS $qt$table_name_ur$qt CASCADE;\n];
406 $create_statement .= join (";\n", @type_drops) . ";\n"
407 if $postgres_version >= 8.3 && scalar @type_drops;
409 $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n];
412 $create_statement .= join(";\n", @type_defs) . ";\n"
413 if $postgres_version >= 8.3 && scalar @type_defs;
414 $create_statement .= qq[CREATE ${temporary}TABLE $qt$table_name_ur$qt (\n].
415 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
418 $create_statement .= @index_defs ? ';' : q{};
419 $create_statement .= ( $create_statement =~ /;$/ ? "\n" : q{} )
420 . join(";\n", @index_defs);
422 return $create_statement, \@fks;
426 my ($view, $options) = @_;
427 my $qt = $options->{quote_table_names} || '';
428 my $qf = $options->{quote_field_names} || '';
429 my $add_drop_view = $options->{add_drop_view};
431 my $view_name = $view->name;
432 debug("PKG: Looking at view '${view_name}'\n");
435 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n"
436 unless $options->{no_comments};
437 $create .= "DROP VIEW ${qt}${view_name}${qt};\n" if $add_drop_view;
440 my $extra = $view->extra;
441 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
442 $create .= " VIEW ${qt}${view_name}${qt}";
444 if ( my @fields = $view->fields ) {
445 my $field_list = join ', ', map { "${qf}${_}${qf}" } @fields;
446 $create .= " ( ${field_list} )";
449 if ( my $sql = $view->sql ) {
450 $create .= " AS (\n ${sql}\n )";
453 if ( $extra->{check_option} ) {
454 $create .= ' WITH ' . uc $extra->{check_option} . ' CHECK OPTION';
462 my %field_name_scope;
466 my ($field, $options) = @_;
468 my $qt = $options->{quote_table_names} || '';
469 my $qf = $options->{quote_field_names} || '';
470 my $table_name = $field->table->name;
471 my $constraint_defs = $options->{constraint_defs} || [];
472 my $postgres_version = $options->{postgres_version} || 0;
473 my $type_defs = $options->{type_defs} || [];
474 my $type_drops = $options->{type_drops} || [];
476 $field_name_scope{$table_name} ||= {};
477 my $field_name = mk_name(
478 $field->name, '', $field_name_scope{$table_name}, 1
480 my $field_name_ur = $qf ? $field_name : unreserve($field_name, $table_name );
481 $field->name($field_name_ur);
482 my $field_comments = $field->comments
483 ? "-- " . $field->comments . "\n "
486 my $field_def = $field_comments.qq[$qf$field_name_ur$qf];
491 my @size = $field->size;
492 my $data_type = lc $field->data_type;
493 my %extra = $field->extra;
494 my $list = $extra{'list'} || [];
495 # todo deal with embedded quotes
496 my $commalist = join( ', ', map { qq['$_'] } @$list );
498 if ($postgres_version >= 8.3 && $field->data_type eq 'enum') {
499 my $type_name = $field->table->name . '_' . $field->name . '_type';
500 $field_def .= ' '. $type_name;
501 push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist)";
502 push @$type_drops, "DROP TYPE IF EXISTS $type_name";
504 $field_def .= ' '. convert_datatype($field);
510 my $default = $field->default_value;
511 if ( defined $default ) {
512 SQL::Translator::Producer->_apply_default_value(
518 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
524 # Not null constraint
526 $field_def .= ' NOT NULL' unless $field->is_nullable;
534 my ($index, $options) = @_;
536 my $qt = $options->{quote_table_names} ||'';
537 my $qf = $options->{quote_field_names} ||'';
538 my $table_name = $index->table->name;
539 # my $table_name_ur = $qt ? unreserve($table_name) : $table_name;
541 my ($index_def, @constraint_defs);
543 my $name = $index->name || '';
545 $name = next_unused_name($name);
548 my $type = $index->type || NORMAL;
550 map { $_ =~ s/\(.+\)//; $_ }
551 map { $qt ? $_ : unreserve($_, $table_name ) }
555 my $def_start = qq[CONSTRAINT "$name" ];
556 if ( $type eq PRIMARY_KEY ) {
557 push @constraint_defs, "${def_start}PRIMARY KEY ".
558 '(' .$qf . join( $qf. ', '.$qf, @fields ) . $qf . ')';
560 elsif ( $type eq UNIQUE ) {
561 push @constraint_defs, "${def_start}UNIQUE " .
562 '(' . $qf . join( $qf. ', '.$qf, @fields ) . $qf.')';
564 elsif ( $type eq NORMAL ) {
566 "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name}${qt} (".
567 join( ', ', map { qq[$qf$_$qf] } @fields ).
572 warn "Unknown index type ($type) on table $table_name.\n"
576 return $index_def, \@constraint_defs;
579 sub create_constraint
581 my ($c, $options) = @_;
583 my $qf = $options->{quote_field_names} ||'';
584 my $qt = $options->{quote_table_names} ||'';
585 my $table_name = $c->table->name;
586 my (@constraint_defs, @fks);
588 my $name = $c->name || '';
590 $name = next_unused_name($name);
594 map { $_ =~ s/\(.+\)//; $_ }
595 map { $qt ? $_ : unreserve( $_, $table_name )}
599 map { $_ =~ s/\(.+\)//; $_ }
600 map { $qt ? $_ : unreserve( $_, $table_name )}
601 $c->reference_fields;
603 next if !@fields && $c->type ne CHECK_C;
604 my $def_start = $name ? qq[CONSTRAINT "$name" ] : '';
605 if ( $c->type eq PRIMARY_KEY ) {
606 push @constraint_defs, "${def_start}PRIMARY KEY ".
607 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
609 elsif ( $c->type eq UNIQUE ) {
610 $name = next_unused_name($name);
611 push @constraint_defs, "${def_start}UNIQUE " .
612 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
614 elsif ( $c->type eq CHECK_C ) {
615 my $expression = $c->expression;
616 push @constraint_defs, "${def_start}CHECK ($expression)";
618 elsif ( $c->type eq FOREIGN_KEY ) {
619 my $def .= "ALTER TABLE ${qt}${table_name}${qt} ADD FOREIGN KEY (" .
620 join( ', ', map { qq[$qf$_$qf] } @fields ) . ')' .
621 "\n REFERENCES " . $qt . $c->reference_table . $qt;
624 $def .= ' ('.$qf . join( $qf.', '.$qf, @rfields ) . $qf.')';
627 if ( $c->match_type ) {
629 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
632 if ( $c->on_delete ) {
633 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
636 if ( $c->on_update ) {
637 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
640 if ( $c->deferrable ) {
641 $def .= ' DEFERRABLE';
647 return \@constraint_defs, \@fks;
654 my @size = $field->size;
655 my $data_type = lc $field->data_type;
657 if ( $data_type eq 'enum' ) {
659 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
660 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
661 # push @$constraint_defs,
662 # qq[CONSTRAINT "$chk_name" CHECK ($qf$field_name$qf ].
663 # qq[IN ($commalist))];
664 $data_type = 'character varying';
666 elsif ( $data_type eq 'set' ) {
667 $data_type = 'character varying';
669 elsif ( $field->is_auto_increment ) {
670 if ( defined $size[0] && $size[0] > 11 ) {
671 $data_type = 'bigserial';
674 $data_type = 'serial';
679 $data_type = defined $translate{ $data_type } ?
680 $translate{ $data_type } :
684 if ( $data_type =~ /timestamp/i ) {
685 if ( defined $size[0] && $size[0] > 6 ) {
690 if ( $data_type eq 'integer' ) {
691 if ( defined $size[0] && $size[0] > 0) {
692 if ( $size[0] > 10 ) {
693 $data_type = 'bigint';
695 elsif ( $size[0] < 5 ) {
696 $data_type = 'smallint';
699 $data_type = 'integer';
703 $data_type = 'integer';
706 my @type_without_size = qw/bigint boolean box bytea cidr circle date inet
707 integer smallint text line lseg macaddr money
708 path point polygon real/;
709 foreach (@type_without_size) {
710 if ( $data_type =~ qr/$_/ ) {
715 if ( defined $size[0] && $size[0] > 0 ) {
716 $data_type .= '(' . join( ',', @size ) . ')';
718 elsif (defined $size[0] && $data_type eq 'timestamp' ) {
719 $data_type .= '(' . join( ',', @size ) . ')';
729 my ($from_field, $to_field) = @_;
731 die "Can't alter field in another table"
732 if($from_field->table->name ne $to_field->table->name);
735 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL',
736 $to_field->table->name,
737 $to_field->name) if(!$to_field->is_nullable and
738 $from_field->is_nullable);
740 my $from_dt = convert_datatype($from_field);
741 my $to_dt = convert_datatype($to_field);
742 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s',
743 $to_field->table->name,
745 $to_dt) if($to_dt ne $from_dt);
747 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s',
748 $to_field->table->name,
750 $to_field->name) if($from_field->name ne $to_field->name);
752 my $old_default = $from_field->default_value;
753 my $new_default = $to_field->default_value;
754 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s',
755 $to_field->table->name,
757 $to_field->default_value)
758 if ( defined $new_default &&
759 (!defined $old_default || $old_default ne $new_default) );
761 return wantarray ? @out : join("\n", @out);
764 sub rename_field { alter_field(@_) }
768 my ($new_field) = @_;
770 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
771 $new_field->table->name,
772 create_field($new_field));
779 my ($old_field) = @_;
781 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
782 $old_field->table->name,
789 my ($to_table, $options) = @_;
790 my $qt = $options->{quote_table_names} || '';
791 my $out = sprintf('ALTER TABLE %s %s',
792 $qt . $to_table->name . $qt,
793 $options->{alter_table_action});
798 my ($old_table, $new_table, $options) = @_;
799 my $qt = $options->{quote_table_names} || '';
800 $options->{alter_table_action} = "RENAME TO $qt$new_table$qt";
801 return alter_table($old_table, $options);
804 sub alter_create_index {
805 my ($index, $options) = @_;
806 my $qt = $options->{quote_table_names} || '';
807 my $qf = $options->{quote_field_names} || '';
808 my ($idef, $constraints) = create_index($index, {
809 quote_field_names => $qf,
810 quote_table_names => $qt,
811 table_name => $index->table->name,
813 return $index->type eq NORMAL ? $idef
814 : sprintf('ALTER TABLE %s ADD %s',
815 $qt . $index->table->name . $qt,
816 join(q{}, @$constraints)
820 sub alter_drop_index {
821 my ($index, $options) = @_;
822 my $index_name = $index->name;
823 return "DROP INDEX $index_name";
826 sub alter_drop_constraint {
827 my ($c, $options) = @_;
828 my $qt = $options->{quote_table_names} || '';
829 my $qc = $options->{quote_field_names} || '';
830 my $out = sprintf('ALTER TABLE %s DROP CONSTRAINT %s',
831 $qt . $c->table->name . $qt,
832 $qc . $c->name . $qc );
836 sub alter_create_constraint {
837 my ($index, $options) = @_;
838 my $qt = $options->{quote_table_names} || '';
839 return $index->type eq FOREIGN_KEY ? join(q{}, @{create_constraint(@_)})
840 : join( ' ', 'ALTER TABLE', $qt.$index->table->name.$qt,
841 'ADD', join(q{}, map { @{$_} } create_constraint(@_))
846 my ($table, $options) = @_;
847 my $qt = $options->{quote_table_names} || '';
848 return "DROP TABLE $qt$table$qt CASCADE";
853 # -------------------------------------------------------------------
854 # Life is full of misery, loneliness, and suffering --
855 # and it's all over much too soon.
857 # -------------------------------------------------------------------
863 SQL::Translator, SQL::Translator::Producer::Oracle.
867 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.