1 package SQL::Translator::Producer::PostgreSQL;
3 # -------------------------------------------------------------------
4 # $Id: PostgreSQL.pm,v 1.3 2002-11-26 03:59:58 kycl4rk Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002 Ken Y. Clark <kclark@cpan.org>,
7 # darren chamberlain <darren@cpan.org>
9 # This program is free software; you can redistribute it and/or
10 # modify it under the terms of the GNU General Public License as
11 # published by the Free Software Foundation; version 2.
13 # This program is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 # General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with this program; if not, write to the Free Software
20 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
22 # -------------------------------------------------------------------
26 SQL::Translator::Producer::PostgreSQL - PostgreSQL producer for SQL::Translator
31 use vars qw[ $DEBUG $WARN $VERSION ];
32 $VERSION = sprintf "%d.%02d", q$Revision: 1.3 $ =~ /(\d+)\.(\d+)/;
33 $DEBUG = 1 unless defined $DEBUG;
42 double => 'double precision',
44 float => 'double precision',
46 mediumint => 'integer',
47 smallint => 'smallint',
48 tinyint => 'smallint',
57 mediumblob => 'bytea',
62 datetime => 'timestamp',
64 timestamp => 'timestamp',
72 varchar2 => 'varchar',
83 datetime => 'timestamp',
85 real => 'double precision',
88 tinyint => 'smallint',
89 float => 'double precision',
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 = 30;
111 my %used_identifiers = ();
118 =head1 PostgreSQL Create Table Syntax
120 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
121 { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
122 | table_constraint } [, ... ]
124 [ INHERITS ( parent_table [, ... ] ) ]
125 [ WITH OIDS | WITHOUT OIDS ]
127 where column_constraint is:
129 [ CONSTRAINT constraint_name ]
130 { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
132 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
133 [ ON DELETE action ] [ ON UPDATE action ] }
134 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
136 and table_constraint is:
138 [ CONSTRAINT constraint_name ]
139 { UNIQUE ( column_name [, ... ] ) |
140 PRIMARY KEY ( column_name [, ... ] ) |
141 CHECK ( expression ) |
142 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
143 [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
144 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
148 # -------------------------------------------------------------------
150 my ( $translator, $data ) = @_;
151 $DEBUG = $translator->debug;
152 $WARN = $translator->show_warnings;
153 my $no_comments = $translator->no_comments;
154 my $add_drop_table = $translator->add_drop_table;
157 unless ( $no_comments ) {
159 "--\n-- Created by %s\n-- Created on %s\n--\n\n",
160 __PACKAGE__, scalar localtime;
165 sort { $a->[0] <=> $b->[0] }
166 map { [ $_->{'order'}, $_ ] }
169 my $table_name = $table->{'table_name'};
172 sort { $a->[0] <=> $b->[0] }
173 map { [ $_->{'order'}, $_ ] }
174 values %{ $table->{'fields'} };
176 $create .= "--\n-- Table: $table_name\n--\n" unless $no_comments;
177 $create = "DROP TABLE $table_name;\n" if $add_drop_table;
178 $create .= "CREATE TABLE $table_name (\n";
183 my %field_name_scope;
184 my @field_statements;
185 for my $field ( @fields ) {
186 my @fdata = ("", $field);
188 my $field_name = mk_name(
189 $field->{'name'}, '', \%field_name_scope, 1
191 my $field_name_ur = unreserve( $field_name, $table_name );
192 my $field_str = $field_name_ur;
195 push @fdata, sprintf "%s%s",
196 $field->{'data_type'},
197 ( defined $field->{'size'} )
198 ? "($field->{'size'})" : '';
201 push @fdata, "NOT NULL" unless $field->{'null'};
203 # Default? XXX Need better quoting!
204 my $default = $field->{'default'};
205 if ( defined $default ) {
206 push @fdata, "DEFAULT '$default'";
207 # if (int $default eq "$default") {
208 # push @fdata, "DEFAULT $default";
210 # push @fdata, "DEFAULT '$default'";
215 push @fdata, "auto_increment" if $field->{'is_auto_inc'};
218 push @fdata, "PRIMARY KEY" if $field->{'is_primary_key'};
220 push @field_statements, join( " ", @fdata );
223 $create .= join( ",\n", @field_statements );
228 my @indices = @{ $table->{'indices'} || [] };
229 for ( my $i = 0; $i <= $#indices; $i++ ) {
231 my $key = $indices[$i];
232 my ( $name, $type, $fields ) = @{ $key }{ qw( name type fields ) };
233 if ( $type eq 'primary_key' ) {
234 $create .= " PRIMARY KEY (@{$fields})"
238 $create .= " KEY $name (@{$fields})"
245 $create .= "\n);\n\n";
251 # -------------------------------------------------------------------
253 my ($basename, $type, $scope, $critical) = @_;
254 my $basename_orig = $basename;
255 my $max_name = $max_id_length - (length($type) + 1);
256 $basename = substr( $basename, 0, $max_name )
257 if length( $basename ) > $max_name;
258 my $name = $type ? "${type}_$basename" : $basename;
260 if ( $basename ne $basename_orig and $critical ) {
261 my $show_type = $type ? "+'$type'" : "";
262 warn "Truncating '$basename_orig'$show_type to $max_id_length ",
263 "character limit to make '$name'\n" if $WARN;
264 $truncated{ $basename_orig } = $name;
267 $scope ||= \%global_names;
268 if ( my $prev = $scope->{ $name } ) {
269 my $name_orig = $name;
270 $name .= sprintf( "%02d", ++$prev );
271 substr($name, $max_id_length - 3) = "00"
272 if length( $name ) > $max_id_length;
274 warn "The name '$name_orig' has been changed to ",
275 "'$name' to make it unique.\n" if $WARN;
277 $scope->{ $name_orig }++;
284 # -------------------------------------------------------------------
286 my ( $name, $schema_obj_name ) = @_;
287 my ( $suffix ) = ( $name =~ s/(\W.*)$// ) ? $1 : '';
289 # also trap fields that don't begin with a letter
290 return $_[0] if !$reserved{ uc $name } && $name =~ /^[a-z]/i;
292 if ( $schema_obj_name ) {
293 ++$unreserve{"$schema_obj_name.$name"};
296 ++$unreserve{"$name (table name)"};
299 my $unreserve = sprintf '%s_', $name;
300 return $unreserve.$suffix;
305 # -------------------------------------------------------------------
306 # Life is full of misery, loneliness, and suffering --
307 # and it's all over much too soon.
309 # -------------------------------------------------------------------
315 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>