package SQL::Translator::Parser::PostgreSQL;
# -------------------------------------------------------------------
-# $Id: PostgreSQL.pm,v 1.6 2003-02-25 14:55:36 kycl4rk Exp $
+# $Id: PostgreSQL.pm,v 1.12 2003-04-17 23:16:29 allenday Exp $
# -------------------------------------------------------------------
# Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>,
# Allen Day <allenday@users.sourceforge.net>,
[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
[ WHERE predicate ]
+Alter table:
+
+ ALTER TABLE [ ONLY ] table [ * ]
+ ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
+ ALTER TABLE [ ONLY ] table [ * ]
+ ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
+ ALTER TABLE [ ONLY ] table [ * ]
+ ALTER [ COLUMN ] column SET STATISTICS integer
+ ALTER TABLE [ ONLY ] table [ * ]
+ RENAME [ COLUMN ] column TO newcolumn
+ ALTER TABLE table
+ RENAME TO new_table
+ ALTER TABLE table
+ ADD table_constraint_definition
+ ALTER TABLE [ ONLY ] table
+ DROP CONSTRAINT constraint { RESTRICT | CASCADE }
+ ALTER TABLE table
+ OWNER TO new_owner
+
=cut
use strict;
use vars qw[ $DEBUG $VERSION $GRAMMAR @EXPORT_OK ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.6 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.12 $ =~ /(\d+)\.(\d+)/;
$DEBUG = 0 unless defined $DEBUG;
use Data::Dumper;
statement : create
| comment
+ | alter
| grant
| revoke
| drop
| connect
+ | set
| <error>
connect : /^\s*\\\connect.*\n/
+set : /SET/ /[^;]*/ ';'
+
revoke : /revoke/i WORD(s /,/) /on/i table_name /from/i name_with_opt_quotes(s /,/) ';'
{
my $table_name = $item{'table_name'};
drop : /drop/i /[^;]*/ ';'
+#
+# Create table.
+#
create : create_table table_name '(' create_definition(s /,/) ')' table_option(s?) ';'
{
my $table_name = $item{'table_name'};
};
}
- for my $constraint ( @{ $definition->{'constaints'} || [] } ) {
- $constraint->{'fields' } = [ $field_name ];
- push @{$tables{ $table_name }{'constraints'}}, $constraint;
+ for my $constraint ( @{ $definition->{'constraints'} || [] } ) {
+ $constraint->{'fields'} = [ $field_name ];
+ push @{ $tables{ $table_name }{'constraints'} },
+ $constraint;
}
}
elsif ( $definition->{'type'} eq 'constraint' ) {
$definition->{'type'} = $definition->{'constraint_type'};
- push @{ $tables{ $table_name }{'constraints'} }, $definition;
+ # group FKs at the field level
+ if ( $definition->{'type'} eq 'foreign_key' ) {
+ for my $fld ( @{ $definition->{'fields'} || [] } ) {
+ push @{
+ $tables{$table_name}{'fields'}{$fld}{'constraints'}
+ }, $definition;
+ }
+ }
+ else {
+ push @{ $tables{ $table_name }{'constraints'} },
+ $definition;
+ }
}
else {
push @{ $tables{ $table_name }{'indices'} }, $definition;
1;
}
+#
+# Create index.
+#
create : /create/i 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 : /create/i WORD /[^;]+/ ';'
+
using_method : /using/i WORD { $item[2] }
where_predicate : /where/i /[^;]+/
create_definition : field
- | index
| table_constraint
| <error>
comment : /^\s*(?:#|-{2}).*\n/
-field : field_name data_type field_meta(s?)
+field : comment(s?) field_name data_type field_meta(s?) comment(s?)
{
my ( $default, @constraints );
- for my $meta ( @{ $item[3] } ) {
+ for my $meta ( @{ $item[4] } ) {
$default = $meta if $meta->{'meta_type'} eq 'default';
push @constraints, $meta if $meta->{'meta_type'} eq 'constraint';
}
my $null = ( grep { $_->{'type'} eq 'not_null' } @constraints ) ? 0 : 1;
- $return = {
+ my @comments = ( @{ $item[1] }, @{ $item[5] } );
+
+ $return = {
type => 'field',
name => $item{'field_name'},
data_type => $item{'data_type'}{'type'},
null => $null,
default => $default->{'value'},
constraints => [ @constraints ],
+ comments => [ @comments ],
}
}
| <error>
/check/i '(' /[^)]+/ ')'
{ $return = { type => 'check', expression => $item[2] } }
|
- /references/i table_name parens_value_list(?) match_type(?) on_delete_do(?) on_update_do(?)
+ /references/i table_name parens_word_list(?) match_type(?) on_delete_do(?) on_update_do(?)
{
$return = {
type => 'foreign_key',
reference_table => $item[2],
- reference_fields => $item[3],
+ reference_fields => $item[3][0],
match_type => $item[4][0],
on_delete_do => $item[5][0],
on_update_do => $item[6][0],
}
}
-index : primary_key_index
- | unique_index
- | normal_index
-
table_name : name_with_opt_quotes
field_name : name_with_opt_quotes
-name_with_opt_quotes : double_quote(?) WORD double_quote(?) { $item[2] }
+name_with_opt_quotes : double_quote(?) NAME double_quote(?) { $item[2] }
double_quote: /"/
}
pg_data_type :
- /(bigint|int8|bigserial|serial8)/ { $return = [ 'integer', 8 ] }
+ /(bigint|int8|bigserial|serial8)/ { $return = [ 'integer(8) auto_increment'] }
|
/(smallint|int2)/ { $return = [ 'integer', 2 ] }
|
|
/(real|float4)/ { $return = [ 'real', 4 ] }
|
- /serial4?/ { $return = [ 'serial', 4 ] }
+ /serial4?/ { $return = [ 'integer(4) auto_increment'] }
|
- /bigserial/ { $return = [ 'serial', 8 ] }
+ /bigserial/ { $return = [ 'integer(8) auto_increment'] }
|
/(bit varying|varbit)/ { $return = 'varbit' }
|
| DIGITS
{ $return = $item[1] }
-table_constraint : constraint_name(?) table_constraint_type deferrable(?) deferred(?)
+table_constraint : comment(s?) constraint_name(?) table_constraint_type deferrable(?) deferred(?) comment(s?)
{
my $desc = $item{'table_constraint_type'};
my $type = $desc->{'type'};
my $fields = $desc->{'fields'};
my $expression = $desc->{'expression'};
+ my @comments = ( @{ $item[1] }, @{ $item[-1] } );
$return = {
name => $item{'constraint_name'}[0] || '',
reference_table => $desc->{'reference_table'},
reference_fields => $desc->{'reference_fields'},
match_type => $desc->{'match_type'}[0],
- on_delete_do => $desc->{'on_delete_do'}[0],
- on_update_do => $desc->{'on_update_do'}[0],
+ on_delete_do => $desc->{'on_delete_do'},
+ on_update_do => $desc->{'on_update_do'},
+ comments => [ @comments ],
}
}
|
/match partial/i { 'match_partial' }
-on_delete_do : /on delete/i WORD
+on_delete_do : /on delete/i WORD(s)
{ $item[2] }
-on_update_do : /on update/i WORD
+on_update_do : /on update/i WORD(s)
{ $item[2] }
+alter : alter_table table_name /add/i table_constraint ';'
+ {
+ my $table_name = $item[2];
+ my $constraint = $item[4];
+ $constraint->{'type'} = $constraint->{'constraint_type'};
+ push @{ $tables{ $table_name }{'constraints'} }, $constraint;
+ }
+
+alter_table : /alter/i /table/i only(?)
+
+only : /only/i
+
create_table : /create/i /table/i
create_index : /create/i /index/i
}
}
-auto_inc : /auto_increment/i { 1 }
-
-primary_key : /primary/i /key/i { 1 }
-
-primary_key_index : primary_key index_name(?) '(' field_name(s /,/) ')'
- {
- $return = {
- name => $item{'index_name'}[0],
- type => 'primary_key',
- fields => $item[4],
- }
- }
-
-normal_index : key index_name(?) '(' name_with_opt_paren(s /,/) ')'
- {
- $return = {
- name => $item{'index_name'}[0],
- type => 'normal',
- fields => $item[4],
- }
- }
-
-unique_index : unique key(?) index_name(?) '(' name_with_opt_paren(s /,/) ')'
- {
- $return = {
- name => $item{'index_name'}[0],
- type => 'unique',
- fields => $item[5],
- }
- }
-
name_with_opt_paren : NAME parens_value_list(s?)
{ $item[2][0] ? "$item[1]($item[2][0][0])" : $item[1] }
{ $item[2] }
| /\w+/
{ $item[1] }
+ | /[\$\w]+/
+ { $item[1] }
VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
{ $item[1] }
=head1 AUTHORS
Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
-Allen Day <allenday@users.sourceforge.net>.
+Allen Day <allenday@ucla.edu>.
=head1 SEE ALSO
projects / dbsrgits/SQL-Translator.git / blobdiff