1 package SQL::Translator::Producer::PostgreSQL;
3 # -------------------------------------------------------------------
4 # $Id: PostgreSQL.pm,v 1.29 2007-06-04 04:01:14 mwz444 Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-4 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
25 SQL::Translator::Producer::PostgreSQL - PostgreSQL producer for SQL::Translator
29 my $t = SQL::Translator->new( parser => '...', producer => 'PostgreSQL' );
34 Creates a DDL suitable for PostgreSQL. Very heavily based on the Oracle
41 use vars qw[ $DEBUG $WARN $VERSION ];
42 $VERSION = sprintf "%d.%02d", q$Revision: 1.29 $ =~ /(\d+)\.(\d+)/;
43 $DEBUG = 1 unless defined $DEBUG;
45 use SQL::Translator::Schema::Constants;
46 use SQL::Translator::Utils qw(header_comment);
63 mediumint => 'integer',
64 smallint => 'smallint',
65 tinyint => 'smallint',
67 varchar => 'character varying',
74 mediumblob => 'bytea',
76 enum => 'character varying',
77 set => 'character varying',
79 datetime => 'timestamp',
81 timestamp => 'timestamp',
89 varchar2 => 'character varying',
99 varchar => 'character varying',
100 datetime => 'timestamp',
105 tinyint => 'smallint',
111 my %reserved = map { $_, 1 } qw[
112 ALL ANALYSE ANALYZE AND ANY AS ASC
114 CASE CAST CHECK COLLATE COLUMN CONSTRAINT CROSS
115 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
116 DEFAULT DEFERRABLE DESC DISTINCT DO
118 FALSE FOR FOREIGN FREEZE FROM FULL
120 ILIKE IN INITIALLY INNER INTERSECT INTO IS ISNULL
121 JOIN LEADING LEFT LIKE LIMIT
122 NATURAL NEW NOT NOTNULL NULL
123 OFF OFFSET OLD ON ONLY OR ORDER OUTER OVERLAPS
124 PRIMARY PUBLIC REFERENCES RIGHT
125 SELECT SESSION_USER SOME TABLE THEN TO TRAILING TRUE
126 UNION UNIQUE USER USING VERBOSE WHEN WHERE
129 # my $max_id_length = 62;
130 my %used_identifiers = ();
137 =head1 PostgreSQL Create Table Syntax
139 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
140 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
141 | table_constraint } [, ... ]
143 [ INHERITS ( parent_table [, ... ] ) ]
144 [ WITH OIDS | WITHOUT OIDS ]
146 where column_constraint is:
148 [ CONSTRAINT constraint_name ]
149 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
151 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
152 [ ON DELETE action ] [ ON UPDATE action ] }
153 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
155 and table_constraint is:
157 [ CONSTRAINT constraint_name ]
158 { UNIQUE ( column_name [, ... ] ) |
159 PRIMARY KEY ( column_name [, ... ] ) |
160 CHECK ( expression ) |
161 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
162 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
163 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
165 =head1 Create Index Syntax
167 CREATE [ UNIQUE ] INDEX index_name ON table
168 [ USING acc_method ] ( column [ ops_name ] [, ...] )
170 CREATE [ UNIQUE ] INDEX index_name ON table
171 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
176 # -------------------------------------------------------------------
178 my $translator = shift;
179 $DEBUG = $translator->debug;
180 $WARN = $translator->show_warnings;
181 my $no_comments = $translator->no_comments;
182 my $add_drop_table = $translator->add_drop_table;
183 my $schema = $translator->schema;
184 my $pargs = $translator->producer_args;
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);
195 # my %used_index_names;
197 my (@table_defs, @fks);
198 for my $table ( $schema->get_tables ) {
200 my ($table_def, $fks) = create_table($table,
201 { quote_table_names => $qt,
202 quote_field_names => $qf,
203 no_comments => $no_comments,
204 postgres_version => $postgres_version,
205 add_drop_table => $add_drop_table,});
206 push @table_defs, $table_def;
211 $output = join("\n\n", @table_defs);
213 $output .= "--\n-- Foreign Key Definitions\n--\n\n" unless $no_comments;
214 $output .= join( "\n\n", @fks ) . "\n";
219 warn "Truncated " . keys( %truncated ) . " names:\n";
220 warn "\t" . join( "\n\t", sort keys %truncated ) . "\n";
224 warn "Encounted " . keys( %unreserve ) .
225 " unsafe names in schema (reserved or invalid):\n";
226 warn "\t" . join( "\n\t", sort keys %unreserve ) . "\n";
233 # -------------------------------------------------------------------
235 my $basename = shift || '';
236 my $type = shift || '';
237 my $scope = shift || '';
238 my $critical = shift || '';
239 my $basename_orig = $basename;
240 # my $max_id_length = 62;
242 ? $max_id_length - (length($type) + 1)
244 $basename = substr( $basename, 0, $max_name )
245 if length( $basename ) > $max_name;
246 my $name = $type ? "${type}_$basename" : $basename;
248 if ( $basename ne $basename_orig and $critical ) {
249 my $show_type = $type ? "+'$type'" : "";
250 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
251 "character limit to make '$name'\n" if $WARN;
252 $truncated{ $basename_orig } = $name;
255 $scope ||= \%global_names;
256 if ( my $prev = $scope->{ $name } ) {
257 my $name_orig = $name;
258 $name .= sprintf( "%02d", ++$prev );
259 substr($name, $max_id_length - 3) = "00"
260 if length( $name ) > $max_id_length;
262 warn "The name '$name_orig' has been changed to ",
263 "'$name' to make it unique.\n" if $WARN;
265 $scope->{ $name_orig }++;
272 # -------------------------------------------------------------------
274 my $name = shift || '';
275 my $schema_obj_name = shift || '';
277 my ( $suffix ) = ( $name =~ s/(\W.*)$// ) ? $1 : '';
279 # also trap fields that don't begin with a letter
280 return $name if (!$reserved{ uc $name }) && $name =~ /^[a-z]/i;
282 if ( $schema_obj_name ) {
283 ++$unreserve{"$schema_obj_name.$name"};
286 ++$unreserve{"$name (table name)"};
289 my $unreserve = sprintf '%s_', $name;
290 return $unreserve.$suffix;
293 # -------------------------------------------------------------------
294 sub next_unused_name {
295 my $name = shift || '';
296 my $used_names = shift || '';
298 my %used_names = %$used_names;
300 if ( !defined($used_names{$name}) ) {
301 $used_names{$name} = $name;
306 while ( defined($used_names{$name . $i}) ) {
310 $used_names{$name} = $name;
316 my ($table, $options) = @_;
318 my $qt = $options->{quote_table_names} || '';
319 my $qf = $options->{quote_field_names} || '';
320 my $no_comments = $options->{no_comments} || 0;
321 my $add_drop_table = $options->{add_drop_table} || 0;
322 my $postgres_version = $options->{postgres_version} || 0;
324 my $table_name = $table->name or next;
325 $table_name = mk_name( $table_name, '', undef, 1 );
326 my $table_name_ur = $qt ? $table_name : unreserve($table_name);
327 $table->name($table_name_ur);
329 # print STDERR "$table_name table_name\n";
330 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @type_defs, @type_drops, @fks );
332 push @comments, "--\n-- Table: $table_name_ur\n--\n" unless $no_comments;
334 if ( $table->comments and !$no_comments ){
335 my $c = "-- Comments: \n-- ";
336 $c .= join "\n-- ", $table->comments;
344 my %field_name_scope;
345 for my $field ( $table->get_fields ) {
346 push @field_defs, create_field($field, { quote_table_names => $qt,
347 quote_field_names => $qf,
348 table_name => $table_name_ur,
349 postgres_version => $postgres_version,
350 type_defs => \@type_defs,
351 type_drops => \@type_drops,
352 constraint_defs => \@constraint_defs,});
359 # my $idx_name_default;
360 for my $index ( $table->get_indices ) {
361 my ($idef, $constraints) = create_index($index,
363 quote_field_names => $qf,
364 quote_table_names => $qt,
365 table_name => $table_name,
367 $idef and push @index_defs, $idef;
368 push @constraint_defs, @$constraints;
375 for my $c ( $table->get_constraints ) {
376 my ($cdefs, $fks) = create_constraint($c,
378 quote_field_names => $qf,
379 quote_table_names => $qt,
380 table_name => $table_name,
382 push @constraint_defs, @$cdefs;
386 my $create_statement;
387 $create_statement = join("\n", @comments);
388 if ($add_drop_table) {
389 if ($postgres_version >= 8.2) {
390 $create_statement .= qq[DROP TABLE IF EXISTS $qt$table_name_ur$qt CASCADE;\n];
391 $create_statement .= join ("\n", @type_drops) . "\n"
392 if $postgres_version >= 8.3;
394 $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n];
397 $create_statement .= join("\n", @type_defs) . "\n"
398 if $postgres_version >= 8.3;
399 $create_statement .= qq[CREATE TABLE $qt$table_name_ur$qt (\n].
400 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
404 $create_statement .= "\n" . join(";\n", @index_defs) . "\n";
406 return $create_statement, \@fks;
411 my %field_name_scope;
415 my ($field, $options) = @_;
417 my $qt = $options->{quote_table_names} || '';
418 my $qf = $options->{quote_field_names} || '';
419 my $table_name = $field->table->name;
420 my $constraint_defs = $options->{constraint_defs} || [];
421 my $postgres_version = $options->{postgres_version} || 0;
422 my $type_defs = $options->{type_defs} || [];
423 my $type_drops = $options->{type_drops} || [];
425 $field_name_scope{$table_name} ||= {};
426 my $field_name = mk_name(
427 $field->name, '', $field_name_scope{$table_name}, 1
429 my $field_name_ur = $qf ? $field_name : unreserve($field_name, $table_name );
430 $field->name($field_name_ur);
431 my $field_comments = $field->comments
432 ? "-- " . $field->comments . "\n "
435 my $field_def = $field_comments.qq[$qf$field_name_ur$qf];
440 my @size = $field->size;
441 my $data_type = lc $field->data_type;
442 my %extra = $field->extra;
443 my $list = $extra{'list'} || [];
444 # todo deal with embedded quotes
445 my $commalist = join( ', ', map { qq['$_'] } @$list );
448 if ($postgres_version >= 8.3 && $field->data_type eq 'enum') {
449 my $type_name = $field->table->name . '_' . $field->name . '_type';
450 $field_def .= ' '. $type_name;
451 push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist);";
452 push @$type_drops, "DROP TYPE IF EXISTS $type_name;";
454 $field_def .= ' '. convert_datatype($field);
458 # Default value -- disallow for timestamps
460 # my $default = $data_type =~ /(timestamp|date)/i
461 # ? undef : $field->default_value;
462 my $default = $field->default_value;
463 if ( defined $default ) {
465 $qd = '' if ($default eq 'now()' ||
466 $default eq 'CURRENT_TIMESTAMP');
467 $field_def .= sprintf( ' DEFAULT %s',
468 ( $field->is_auto_increment && $seq_name )
469 ? qq[nextval('"$seq_name"'::text)] :
470 ( $default =~ m/null/i ) ? 'NULL' : "$qd$default$qd"
475 # Not null constraint
477 $field_def .= ' NOT NULL' unless $field->is_nullable;
484 my %used_index_names;
488 my ($index, $options) = @_;
490 my $qt = $options->{quote_table_names} ||'';
491 my $qf = $options->{quote_field_names} ||'';
492 my $table_name = $index->table->name;
493 # my $table_name_ur = $qt ? unreserve($table_name) : $table_name;
495 my ($index_def, @constraint_defs);
497 $used_index_names{$table_name} ||= {};
498 my $name = $index->name || '';
500 $name = next_unused_name($name, $used_index_names{$table_name});
501 $used_index_names{$name} = $name;
504 my $type = $index->type || NORMAL;
506 map { $_ =~ s/\(.+\)//; $_ }
507 map { $qt ? $_ : unreserve($_, $table_name ) }
511 my $def_start = qq[Constraint "$name" ];
512 if ( $type eq PRIMARY_KEY ) {
513 push @constraint_defs, "${def_start}PRIMARY KEY ".
514 '(' .$qf . join( $qf. ', '.$qf, @fields ) . $qf . ')';
516 elsif ( $type eq UNIQUE ) {
517 push @constraint_defs, "${def_start}UNIQUE " .
518 '(' . $qf . join( $qf. ', '.$qf, @fields ) . $qf.')';
520 elsif ( $type eq NORMAL ) {
522 "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name}${qt} (".
523 join( ', ', map { qq[$qf$_$qf] } @fields ).
528 warn "Unknown index type ($type) on table $table_name.\n"
532 return $index_def, \@constraint_defs;
535 sub create_constraint
537 my ($c, $options) = @_;
539 my $qf = $options->{quote_field_names} ||'';
540 my $qt = $options->{quote_table_names} ||'';
541 my $table_name = $c->table->name;
542 my (@constraint_defs, @fks);
544 my $name = $c->name || '';
546 $name = next_unused_name($name, \%used_index_names);
547 $used_index_names{$name} = $name;
551 map { $_ =~ s/\(.+\)//; $_ }
552 map { $qt ? $_ : unreserve( $_, $table_name )}
556 map { $_ =~ s/\(.+\)//; $_ }
557 map { $qt ? $_ : unreserve( $_, $table_name )}
558 $c->reference_fields;
560 next if !@fields && $c->type ne CHECK_C;
561 my $def_start = $name ? qq[Constraint "$name" ] : '';
562 if ( $c->type eq PRIMARY_KEY ) {
563 push @constraint_defs, "${def_start}PRIMARY KEY ".
564 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
566 elsif ( $c->type eq UNIQUE ) {
567 $name = next_unused_name($name, \%used_index_names);
568 $used_index_names{$name} = $name;
569 push @constraint_defs, "${def_start}UNIQUE " .
570 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
572 elsif ( $c->type eq CHECK_C ) {
573 my $expression = $c->expression;
574 push @constraint_defs, "${def_start}CHECK ($expression)";
576 elsif ( $c->type eq FOREIGN_KEY ) {
577 my $def .= "ALTER TABLE ${qt}${table_name}${qt} ADD FOREIGN KEY (" .
578 join( ', ', map { qq[$qf$_$qf] } @fields ) . ')' .
579 "\n REFERENCES " . $qt . $c->reference_table . $qt;
582 $def .= ' ('.$qf . join( $qf.', '.$qf, @rfields ) . $qf.')';
585 if ( $c->match_type ) {
587 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
590 if ( $c->on_delete ) {
591 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
594 if ( $c->on_update ) {
595 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
598 if ( $c->deferrable ) {
599 $def .= ' DEFERRABLE';
605 return \@constraint_defs, \@fks;
613 my @size = $field->size;
614 my $data_type = lc $field->data_type;
616 if ( $data_type eq 'enum' ) {
618 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
619 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
620 # push @$constraint_defs,
621 # qq[Constraint "$chk_name" CHECK ($qf$field_name$qf ].
622 # qq[IN ($commalist))];
623 $data_type = 'character varying';
625 elsif ( $data_type eq 'set' ) {
626 $data_type = 'character varying';
628 elsif ( $field->is_auto_increment ) {
629 if ( defined $size[0] && $size[0] > 11 ) {
630 $data_type = 'bigserial';
633 $data_type = 'serial';
638 $data_type = defined $translate{ $data_type } ?
639 $translate{ $data_type } :
643 if ( $data_type =~ /timestamp/i ) {
644 if ( defined $size[0] && $size[0] > 6 ) {
649 if ( $data_type eq 'integer' ) {
650 if ( defined $size[0] && $size[0] > 0) {
651 if ( $size[0] > 10 ) {
652 $data_type = 'bigint';
654 elsif ( $size[0] < 5 ) {
655 $data_type = 'smallint';
658 $data_type = 'integer';
662 $data_type = 'integer';
665 my @type_without_size = qw/bigint boolean box bytea cidr circle date inet
666 integer smallint text line lseg macaddr money
667 path point polygon real/;
668 foreach (@type_without_size) {
669 if ( $data_type =~ qr/$_/ ) {
674 if ( defined $size[0] && $size[0] > 0 ) {
675 $data_type .= '(' . join( ',', @size ) . ')';
677 elsif (defined $size[0] && $data_type eq 'timestamp' ) {
678 $data_type .= '(' . join( ',', @size ) . ')';
688 my ($from_field, $to_field) = @_;
690 die "Can't alter field in another table"
691 if($from_field->table->name ne $to_field->table->name);
694 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL;',
695 $to_field->table->name,
696 $to_field->name) if(!$to_field->is_nullable and
697 $from_field->is_nullable);
699 my $from_dt = convert_datatype($from_field);
700 my $to_dt = convert_datatype($to_field);
701 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s;',
702 $to_field->table->name,
704 $to_dt) if($to_dt ne $from_dt);
706 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s;',
707 $to_field->table->name,
709 $to_field->name) if($from_field->name ne $to_field->name);
711 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;',
712 $to_field->table->name,
714 $to_field->default_value)
715 if(defined $to_field->default_value &&
716 $from_field->default_value ne $to_field->default_value);
718 return wantarray ? @out : join("\n", @out);
724 my ($new_field) = @_;
726 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s;',
727 $new_field->table->name,
728 create_field($new_field));
735 my ($old_field) = @_;
737 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s;',
738 $old_field->table->name,
746 # -------------------------------------------------------------------
747 # Life is full of misery, loneliness, and suffering --
748 # and it's all over much too soon.
750 # -------------------------------------------------------------------
756 SQL::Translator, SQL::Translator::Producer::Oracle.
760 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.