package SQL::Translator::Parser::PostgreSQL;
# -------------------------------------------------------------------
-# $Id: PostgreSQL.pm,v 1.34 2003-12-10 23:09:19 kycl4rk Exp $
+# $Id: PostgreSQL.pm,v 1.47 2006-06-09 13:56:58 schiffbruechige Exp $
# -------------------------------------------------------------------
-# Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>,
-# Allen Day <allenday@users.sourceforge.net>,
-# darren chamberlain <darren@cpan.org>,
-# Chris Mungall <cjm@fruitfly.org>
+# Copyright (C) 2002-4 SQLFairy Authors
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
use strict;
use vars qw[ $DEBUG $VERSION $GRAMMAR @EXPORT_OK ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.34 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.47 $ =~ /(\d+)\.(\d+)/;
$DEBUG = 0 unless defined $DEBUG;
use Data::Dumper;
startrule : statement(s) eofile { \%tables }
eofile : /^\Z/
+
statement : create
| comment_on_table
| comment_on_column
+ | comment_on_other
| comment
| alter
| grant
drop : /drop/i /[^;]*/ ';'
-insert : /insert/i /[^;]*/ ';'
+string :
+ /'(\\.|''|[^\\\'])*'/
-update : /update/i /[^;]*/ ';'
+nonstring : /[^;\'"]+/
+
+statement_body : (string | nonstring)(s?)
+
+insert : /insert/i statement_body ';'
+
+update : /update/i statement_body ';'
#
# Create table.
#
-create : create_table table_name '(' create_definition(s /,/) ')' table_option(s?) ';'
+create : create_table table_name '(' create_definition(s? /,/) ')' table_option(s?) ';'
{
my $table_name = $item{'table_name'};
$tables{ $table_name }{'order'} = ++$table_order;
1;
}
-create : /create/i unique(?) /(index|key)/i index_name /on/i table_name using_method(?) '(' field_name(s /,/) ')' where_predicate(?) ';'
+create : CREATE unique(?) /(index|key)/i index_name /on/i table_name using_method(?) '(' field_name(s /,/) ')' where_predicate(?) ';'
{
push @{ $tables{ $item{'table_name'} }{'indices'} },
{
}
#
-# Create anything else (e.g., domain, function, etc.)
+# Create anything else (e.g., domain, etc.)
#
-create : /create/i WORD /[^;]+/ ';'
+create : CREATE WORD /[^;]+/ ';'
{ @table_comments = (); }
using_method : /using/i WORD { $item[2] }
| table_constraint
| <error>
-table_comment : comment
- {
- my $comment = $item[1];
+comment : /^\s*(?:#|-{2})(.*)\n/
+ {
+ my $comment = $item[1];
+ $comment =~ s/^\s*(#|-*)\s*//;
+ $comment =~ s/\s*$//;
$return = $comment;
push @table_comments, $comment;
}
-comment : /^\s*(?:#|-{2}).*\n/
-
comment_on_table : /comment/i /on/i /table/i table_name /is/i comment_phrase ';'
{
push @{ $tables{ $item{'table_name'} }{'comments'} }, $item{'comment_phrase'};
{
my $table_name = $item[4]->{'table'};
my $field_name = $item[4]->{'field'};
- push @{ $tables{ $table_name }{'fields'}{ $field_name }{'comments'} },
- $item{'comment_phrase'};
+ if ($tables{ $table_name }{'fields'}{ $field_name } ) {
+ push @{ $tables{ $table_name }{'fields'}{ $field_name }{'comments'} },
+ $item{'comment_phrase'};
+ }
+ else {
+ die "No such column as $table_name.$field_name";
+ }
+ }
+
+comment_on_other : /comment/i /on/i /\w+/ /\w+/ /is/i comment_phrase ';'
+ {
+ push(@table_comments, $item{'comment_phrase'});
}
+# [added by cjm 20041019]
+# [TODO: other comment-on types]
+# for now we just have a general mechanism for handling other
+# kinds of comments than table/column; I'm not sure of the best
+# way to incorporate these into the datamodel
+#
+# this is the exhaustive list of types of comment:
+#COMMENT ON DATABASE my_database IS 'Development Database';
+#COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee id';
+#COMMENT ON RULE my_rule IS 'Logs UPDATES of employee records';
+#COMMENT ON SEQUENCE my_sequence IS 'Used to generate primary keys';
+#COMMENT ON TABLE my_table IS 'Employee Information';
+#COMMENT ON TYPE my_type IS 'Complex Number support';
+#COMMENT ON VIEW my_view IS 'View of departmental costs';
+#COMMENT ON COLUMN my_table.my_field IS 'Employee ID number';
+#COMMENT ON TRIGGER my_trigger ON my_table IS 'Used for R.I.';
+#
+# this is tested by test 08
+
column_name : NAME '.' NAME
{ $return = { table => $item[1], field => $item[3] } }
-comment_phrase : /'.*?'|NULL/
+comment_phrase : /null/i
+ { $return = 'NULL' }
+
+comment_phrase : /'/ comment_phrase_unquoted(s) /'/
+ { my $phrase = join(' ', @{ $item[2] });
+ $return = $phrase}
+
+# [cjm TODO: double-single quotes in a comment_phrase]
+comment_phrase_unquoted : /[^\']*/
+ { $return = $item[1] }
+
+
+xxxcomment_phrase : /'.*?'|NULL/
{
my $val = $item[1] || '';
$val =~ s/^'|'$//g;
$return = $val;
}
-field : comment(s?) field_name data_type field_meta(s?) comment(s?)
+field : field_comment(s?) field_name data_type field_meta(s?) field_comment(s?)
{
my ( $default, @constraints, $is_pk );
my $is_nullable = 1;
}
| <error>
+field_comment : /^\s*(?:#|-{2})(.*)\n/
+ {
+ my $comment = $item[1];
+ $comment =~ s/^\s*(#|-*)\s*//;
+ $comment =~ s/\s*$//;
+ $return = $comment;
+ }
+
field_meta : default_val
| column_constraint
reference_table => $desc->{'reference_table'},
reference_fields => $desc->{'reference_fields'},
match_type => $desc->{'match_type'},
- on_delete_do => $desc->{'on_delete_do'},
- on_update_do => $desc->{'on_update_do'},
+ on_delete => $desc->{'on_delete'} || $desc->{'on_delete_do'},
+ on_update => $desc->{'on_update'} || $desc->{'on_update_do'},
}
}
reference_table => $item[2],
reference_fields => $item[3][0],
match_type => $item[4][0],
- on_delete_do => $on_delete,
- on_update_do => $on_update,
+ on_delete => $on_delete,
+ on_update => $on_update,
}
}
#
# We can deduce some sizes from the data type's name.
#
- $data_type->{'size'} ||= $item[2][0];
+ if ( my $size = $item[2][0] ) {
+ $data_type->{'size'} = $size;
+ }
$return = $data_type;
}
};
}
|
+ /interval/i
+ {
+ $return = { type => 'interval' };
+ }
+ |
/(integer|int4?)/i # interval must come before this
{
$return = {
$return = { type => 'bytea' };
}
|
- /(timestamptz|timestamp)/i
+ /(timestamptz|timestamp)( with(out)? time zone)?/i
{
$return = { type => 'timestamp' };
}
};
}
|
- /(bit|box|cidr|circle|date|inet|interval|line|lseg|macaddr|money|numeric|decimal|path|point|polygon|timetz|time|varchar)/i
+ /(bit|box|cidr|circle|date|inet|line|lseg|macaddr|money|numeric|decimal|path|point|polygon|timetz|time|varchar)/i
{
$return = { type => $item[1] };
}
reference_table => $desc->{'reference_table'},
reference_fields => $desc->{'reference_fields'},
match_type => $desc->{'match_type'}[0],
- on_delete_do => $desc->{'on_delete_do'},
- on_update_do => $desc->{'on_update_do'},
+ on_delete => $desc->{'on_delete'} || $desc->{'on_delete_do'},
+ on_update => $desc->{'on_update'} || $desc->{'on_update_do'},
comments => [ @comments ],
}
}
reference_table => $item[6],
reference_fields => $item[7][0],
match_type => $item[8][0],
- on_delete_do => $on_delete || '',
- on_update_do => $on_update || '',
+ on_delete => $on_delete || '',
+ on_update => $on_update || '',
}
}
key_delete : /on delete/i key_mutation
{
- $return => {
+ $return = {
type => 'delete',
action => $item[2],
};
key_update : /on update/i key_mutation
{
- $return => {
+ $return = {
type => 'update',
action => $item[2],
};
# End basically useless stuff. - ky
#
-create_table : /create/i TABLE
+create_table : CREATE TABLE
-create_index : /create/i /index/i
+create_index : CREATE /index/i
default_val : DEFAULT /(\d+|'[^']*'|\w+\(.*?\))|\w+/
{
ALTER : /alter/i
+CREATE : /create/i
+
ONLY : /only/i
DEFAULT : /default/i
my $schema = $translator->schema;
my @tables = sort {
- $result->{ $a }->{'order'} <=> $result->{ $b }->{'order'}
+ ( $result->{ $a }{'order'} || 0 ) <=> ( $result->{ $b }{'order'} || 0 )
} keys %{ $result };
for my $table_name ( @tables ) {
name => $tdata->{'table_name'},
) or die "Couldn't create table '$table_name': " . $schema->error;
+ $table->comments( $tdata->{'comments'} );
+
my @fields = sort {
- $tdata->{'fields'}->{ $a }->{'order'}
+ $tdata->{'fields'}{ $a }{'order'}
<=>
- $tdata->{'fields'}->{ $b }->{'order'}
+ $tdata->{'fields'}{ $b }{'order'}
} keys %{ $tdata->{'fields'} };
for my $fname ( @fields ) {
default_value => $fdata->{'default'},
is_auto_increment => $fdata->{'is_auto_increment'},
is_nullable => $fdata->{'is_nullable'},
+ comments => $fdata->{'comments'},
) or die $table->error;
$table->primary_key( $field->name ) if $fdata->{'is_primary_key'};
reference_table => $cdata->{'reference_table'},
reference_fields => $cdata->{'reference_fields'},
match_type => $cdata->{'match_type'} || '',
- on_delete => $cdata->{'on_delete_do'},
- on_update => $cdata->{'on_update_do'},
+ on_delete => $cdata->{'on_delete'} || $cdata->{'on_delete_do'},
+ on_update => $cdata->{'on_update'} || $cdata->{'on_update_do'},
expression => $cdata->{'expression'},
) or die "Can't add constraint of type '" .
$cdata->{'type'} . "' to table '" . $table->name .
projects / dbsrgits/SQL-Translator.git / blobdiff