| Subject: | parse() in SQL::Translator::Parser::DBI::PostgreSQL does not handle multi-column primary keys |
Setup:
(1) PostgreSQL table having a primary key involving more than one column
, see attachment Create_table.sql
(2) Calling translate() on an SQL::Translator for a database containing
the above table, see attachment test.pl.
----------------------------------------
Error message:
Argument "1 2" isn't numeric in subtraction (-) at
C:/strawberry/perl/site/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm l
ine 157.
----------------------------------------
Reason:
In line 157, the parse() method tries to determine a column name for the
current primary key:
my $col_name=$column_names[($$indexhash{'indkey'} - 1)];
But in the test case, the primary key consists of two columns with
indices 1 and 2.
----------------------------------------
Possible resolution:
A proposal for a small patch can be found in the attachment
PostgreSQL.patch.
| Subject: | test.pl |
use strict;
use warnings;
use DBI;
use SQL::Translator;
my $dbh = DBI->connect( 'dbi:Pg:dbname=playground', 'postgres', '********' );
my $translator = SQL::Translator->new(
parser => 'DBI',
parser_args => { dbh => $dbh, }
);
$translator->translate();
| Subject: | Create_table.sql |
Message body not shown because it is not plain text.
| Subject: | PostgreSQL.patch |
--- C:/Users/jose/CPAN/SQL-Translator-Parser-DBI-PostgreSQL/PostgreSQL.pm Thu Nov 01 21:28:38 2012
+++ C:/Users/jose/CPAN/SQL-Translator-Parser-DBI-PostgreSQL/PostgreSQL_patched.pm Thu Nov 01 21:28:57 2012
@@ -150,12 +150,16 @@
or !defined($$indexhash{'indkey'}) );
my $type;
+ my @column_ids = split /\s+/, $$indexhash{'indkey'};
+
if ($$indexhash{'indisprimary'}) {
$type = UNIQUE; #PRIMARY_KEY;
#tell sqlt that this is the primary key:
- my $col_name=$column_names[($$indexhash{'indkey'} - 1)];
- $table->get_field($col_name)->{is_primary_key}=1;
+ foreach my $column_id (@column_ids) {
+ my $col_name=$column_names[($column_id - 1)];
+ $table->get_field($col_name)->{is_primary_key}=1;
+ }
} elsif ($$indexhash{'indisunique'}) {
$type = UNIQUE;
@@ -163,8 +167,7 @@
$type = NORMAL;
}
-
- my @column_ids = split /\s+/, $$indexhash{'indkey'};
+
my @columns = split /\s+/, $$indexhash{'attname'};
$table->add_index(