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 %used_names ];
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;
185 local %used_names = ();
187 my $postgres_version = $pargs->{postgres_version} || 0;
190 $qt = '"' if ($translator->quote_table_names);
192 $qf = '"' if ($translator->quote_field_names);
195 $output .= header_comment unless ($no_comments);
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 if ( !defined( $used_names{$name} ) ) {
297 $used_names{$name} = $name;
302 while ( defined( $used_names{ $name . $i } ) ) {
306 $used_names{$name} = $name;
313 my ($table, $options) = @_;
315 my $qt = $options->{quote_table_names} || '';
316 my $qf = $options->{quote_field_names} || '';
317 my $no_comments = $options->{no_comments} || 0;
318 my $add_drop_table = $options->{add_drop_table} || 0;
319 my $postgres_version = $options->{postgres_version} || 0;
321 my $table_name = $table->name or next;
322 $table_name = mk_name( $table_name, '', undef, 1 );
323 my $table_name_ur = $qt ? $table_name : unreserve($table_name);
324 $table->name($table_name_ur);
326 # print STDERR "$table_name table_name\n";
327 my ( @comments, @field_defs, @sequence_defs, @constraint_defs, @type_defs, @type_drops, @fks );
329 push @comments, "--\n-- Table: $table_name_ur\n--\n" unless $no_comments;
331 if ( $table->comments and !$no_comments ){
332 my $c = "-- Comments: \n-- ";
333 $c .= join "\n-- ", $table->comments;
341 my %field_name_scope;
342 for my $field ( $table->get_fields ) {
343 push @field_defs, create_field($field, { quote_table_names => $qt,
344 quote_field_names => $qf,
345 table_name => $table_name_ur,
346 postgres_version => $postgres_version,
347 type_defs => \@type_defs,
348 type_drops => \@type_drops,
349 constraint_defs => \@constraint_defs,});
356 # my $idx_name_default;
357 for my $index ( $table->get_indices ) {
358 my ($idef, $constraints) = create_index($index,
360 quote_field_names => $qf,
361 quote_table_names => $qt,
362 table_name => $table_name,
364 $idef and push @index_defs, $idef;
365 push @constraint_defs, @$constraints;
372 for my $c ( $table->get_constraints ) {
373 my ($cdefs, $fks) = create_constraint($c,
375 quote_field_names => $qf,
376 quote_table_names => $qt,
377 table_name => $table_name,
379 push @constraint_defs, @$cdefs;
383 my $create_statement;
384 $create_statement = join("\n", @comments);
385 if ($add_drop_table) {
386 if ($postgres_version >= 8.2) {
387 $create_statement .= qq[DROP TABLE IF EXISTS $qt$table_name_ur$qt CASCADE;\n];
388 $create_statement .= join ("\n", @type_drops) . "\n"
389 if $postgres_version >= 8.3;
391 $create_statement .= qq[DROP TABLE $qt$table_name_ur$qt CASCADE;\n];
394 $create_statement .= join("\n", @type_defs) . "\n"
395 if $postgres_version >= 8.3;
396 $create_statement .= qq[CREATE TABLE $qt$table_name_ur$qt (\n].
397 join( ",\n", map { " $_" } @field_defs, @constraint_defs ).
401 $create_statement .= "\n" . join("\n", @index_defs) . "\n";
403 return $create_statement, \@fks;
408 my %field_name_scope;
412 my ($field, $options) = @_;
414 my $qt = $options->{quote_table_names} || '';
415 my $qf = $options->{quote_field_names} || '';
416 my $table_name = $field->table->name;
417 my $constraint_defs = $options->{constraint_defs} || [];
418 my $postgres_version = $options->{postgres_version} || 0;
419 my $type_defs = $options->{type_defs} || [];
420 my $type_drops = $options->{type_drops} || [];
422 $field_name_scope{$table_name} ||= {};
423 my $field_name = mk_name(
424 $field->name, '', $field_name_scope{$table_name}, 1
426 my $field_name_ur = $qf ? $field_name : unreserve($field_name, $table_name );
427 $field->name($field_name_ur);
428 my $field_comments = $field->comments
429 ? "-- " . $field->comments . "\n "
432 my $field_def = $field_comments.qq[$qf$field_name_ur$qf];
437 my @size = $field->size;
438 my $data_type = lc $field->data_type;
439 my %extra = $field->extra;
440 my $list = $extra{'list'} || [];
441 # todo deal with embedded quotes
442 my $commalist = join( ', ', map { qq['$_'] } @$list );
445 if ($postgres_version >= 8.3 && $field->data_type eq 'enum') {
446 my $type_name = $field->table->name . '_' . $field->name . '_type';
447 $field_def .= ' '. $type_name;
448 push @$type_defs, "CREATE TYPE $type_name AS ENUM ($commalist);";
449 push @$type_drops, "DROP TYPE IF EXISTS $type_name;";
451 $field_def .= ' '. convert_datatype($field);
455 # Default value -- disallow for timestamps
457 # my $default = $data_type =~ /(timestamp|date)/i
458 # ? undef : $field->default_value;
459 my $default = $field->default_value;
460 if ( defined $default ) {
462 $qd = '' if ($default eq 'now()' ||
463 $default eq 'CURRENT_TIMESTAMP');
464 $field_def .= sprintf( ' DEFAULT %s',
465 ( $field->is_auto_increment && $seq_name )
466 ? qq[nextval('"$seq_name"'::text)] :
467 ( $default =~ m/null/i ) ? 'NULL' : "$qd$default$qd"
472 # Not null constraint
474 $field_def .= ' NOT NULL' unless $field->is_nullable;
482 my ($index, $options) = @_;
484 my $qt = $options->{quote_table_names} ||'';
485 my $qf = $options->{quote_field_names} ||'';
486 my $table_name = $index->table->name;
487 # my $table_name_ur = $qt ? unreserve($table_name) : $table_name;
489 my ($index_def, @constraint_defs);
491 my $name = $index->name || '';
493 $name = next_unused_name($name);
496 my $type = $index->type || NORMAL;
498 map { $_ =~ s/\(.+\)//; $_ }
499 map { $qt ? $_ : unreserve($_, $table_name ) }
503 my $def_start = qq[Constraint "$name" ];
504 if ( $type eq PRIMARY_KEY ) {
505 push @constraint_defs, "${def_start}PRIMARY KEY ".
506 '(' .$qf . join( $qf. ', '.$qf, @fields ) . $qf . ')';
508 elsif ( $type eq UNIQUE ) {
509 push @constraint_defs, "${def_start}UNIQUE " .
510 '(' . $qf . join( $qf. ', '.$qf, @fields ) . $qf.')';
512 elsif ( $type eq NORMAL ) {
514 "CREATE INDEX ${qf}${name}${qf} on ${qt}${table_name}${qt} (".
515 join( ', ', map { qq[$qf$_$qf] } @fields ).
520 warn "Unknown index type ($type) on table $table_name.\n"
524 return $index_def, \@constraint_defs;
527 sub create_constraint
529 my ($c, $options) = @_;
531 my $qf = $options->{quote_field_names} ||'';
532 my $qt = $options->{quote_table_names} ||'';
533 my $table_name = $c->table->name;
534 my (@constraint_defs, @fks);
536 my $name = $c->name || '';
538 $name = next_unused_name($name);
542 map { $_ =~ s/\(.+\)//; $_ }
543 map { $qt ? $_ : unreserve( $_, $table_name )}
547 map { $_ =~ s/\(.+\)//; $_ }
548 map { $qt ? $_ : unreserve( $_, $table_name )}
549 $c->reference_fields;
551 next if !@fields && $c->type ne CHECK_C;
552 my $def_start = $name ? qq[Constraint "$name" ] : '';
553 if ( $c->type eq PRIMARY_KEY ) {
554 push @constraint_defs, "${def_start}PRIMARY KEY ".
555 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
557 elsif ( $c->type eq UNIQUE ) {
558 $name = next_unused_name($name);
559 push @constraint_defs, "${def_start}UNIQUE " .
560 '('.$qf . join( $qf.', '.$qf, @fields ) . $qf.')';
562 elsif ( $c->type eq CHECK_C ) {
563 my $expression = $c->expression;
564 push @constraint_defs, "${def_start}CHECK ($expression)";
566 elsif ( $c->type eq FOREIGN_KEY ) {
567 my $def .= "ALTER TABLE ${qt}${table_name}${qt} ADD FOREIGN KEY (" .
568 join( ', ', map { qq[$qf$_$qf] } @fields ) . ')' .
569 "\n REFERENCES " . $qt . $c->reference_table . $qt;
572 $def .= ' ('.$qf . join( $qf.', '.$qf, @rfields ) . $qf.')';
575 if ( $c->match_type ) {
577 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
580 if ( $c->on_delete ) {
581 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
584 if ( $c->on_update ) {
585 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
588 if ( $c->deferrable ) {
589 $def .= ' DEFERRABLE';
595 return \@constraint_defs, \@fks;
602 my @size = $field->size;
603 my $data_type = lc $field->data_type;
605 if ( $data_type eq 'enum' ) {
607 # $len = ($len < length($_)) ? length($_) : $len for (@$list);
608 # my $chk_name = mk_name( $table_name.'_'.$field_name, 'chk' );
609 # push @$constraint_defs,
610 # qq[Constraint "$chk_name" CHECK ($qf$field_name$qf ].
611 # qq[IN ($commalist))];
612 $data_type = 'character varying';
614 elsif ( $data_type eq 'set' ) {
615 $data_type = 'character varying';
617 elsif ( $field->is_auto_increment ) {
618 if ( defined $size[0] && $size[0] > 11 ) {
619 $data_type = 'bigserial';
622 $data_type = 'serial';
627 $data_type = defined $translate{ $data_type } ?
628 $translate{ $data_type } :
632 if ( $data_type =~ /timestamp/i ) {
633 if ( defined $size[0] && $size[0] > 6 ) {
638 if ( $data_type eq 'integer' ) {
639 if ( defined $size[0] && $size[0] > 0) {
640 if ( $size[0] > 10 ) {
641 $data_type = 'bigint';
643 elsif ( $size[0] < 5 ) {
644 $data_type = 'smallint';
647 $data_type = 'integer';
651 $data_type = 'integer';
654 my @type_without_size = qw/bigint boolean box bytea cidr circle date inet
655 integer smallint text line lseg macaddr money
656 path point polygon real/;
657 foreach (@type_without_size) {
658 if ( $data_type =~ qr/$_/ ) {
663 if ( defined $size[0] && $size[0] > 0 ) {
664 $data_type .= '(' . join( ',', @size ) . ')';
666 elsif (defined $size[0] && $data_type eq 'timestamp' ) {
667 $data_type .= '(' . join( ',', @size ) . ')';
677 my ($from_field, $to_field) = @_;
679 die "Can't alter field in another table"
680 if($from_field->table->name ne $to_field->table->name);
683 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET NOT NULL;',
684 $to_field->table->name,
685 $to_field->name) if(!$to_field->is_nullable and
686 $from_field->is_nullable);
688 my $from_dt = convert_datatype($from_field);
689 my $to_dt = convert_datatype($to_field);
690 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s TYPE %s;',
691 $to_field->table->name,
693 $to_dt) if($to_dt ne $from_dt);
695 push @out, sprintf('ALTER TABLE %s RENAME COLUMN %s TO %s;',
696 $to_field->table->name,
698 $to_field->name) if($from_field->name ne $to_field->name);
700 push @out, sprintf('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;',
701 $to_field->table->name,
703 $to_field->default_value)
704 if(defined $to_field->default_value &&
705 $from_field->default_value ne $to_field->default_value);
707 return wantarray ? @out : join("\n", @out);
713 my ($new_field) = @_;
715 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s;',
716 $new_field->table->name,
717 create_field($new_field));
724 my ($old_field) = @_;
726 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s;',
727 $old_field->table->name,
735 # -------------------------------------------------------------------
736 # Life is full of misery, loneliness, and suffering --
737 # and it's all over much too soon.
739 # -------------------------------------------------------------------
745 SQL::Translator, SQL::Translator::Producer::Oracle.
749 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.