Hi,
About the unique constraints, here is the modified method that also include
unique indexes. I put first the original query to get the unique
constraints and then the query to get unique indexes. After, I check if
there is already another constraint having the same columns. If yes, skip,
otherwise store as result.
In my tests the program below brought the unique constraints ignoring its
duplicated indexes that I had created previously. When there isn't a
constraint but only unique index, the program added that to the result.
Hope this helps.
sub _table_uniq_info {
my ($self, $table) = @_;
my @uniqs;
my $sth = $self->{_cache}->{db2_uniq} ||= $self->dbh->prepare(<<'EOF');
SELECT trim(kcu.colname), trim(kcu.constname), kcu.colseq
FROM syscat.tabconst as tc
JOIN syscat.keycoluse as kcu
ON tc.constname = kcu.constname
AND tc.tabschema = kcu.tabschema
AND tc.tabname = kcu.tabname
WHERE tc.tabschema = ? and tc.tabname = ? and tc.type = 'U'
UNION
SELECT trim(sc.colname), trim(si.name), sc.colseq
FROM sysibm.sysindexes si, sysibm.sysindexcoluse sc
WHERE si.tbcreator=? and si.name=sc.indname and si.tbname=? AND
si.uniquerule = 'U'
EOF
$sth->execute($table->schema, $table->name, $table->schema,
$table->name);
my %keydata;
my %cache;
while(my $row = $sth->fetchrow_arrayref) {
my ($col, $constname, $seq) = @$row;
push(@{$keydata{$constname}}, [ $seq, $self->_lc($col) ]);
}
foreach my $keyname (keys %keydata) {
my @ordered_cols = map { $_->[1] } sort { $a->[0] <=> $b->[0] }
@{$keydata{$keyname}};
if ($cache{join '+', @ordered_cols}){
#print "Skipping $keyname since another constraint exists with
the same columns\n";
next;
} else {
$cache{join '+', @ordered_cols}=1;
}
push(@uniqs, [ $keyname => \@ordered_cols ]);
}
$sth->finish;
return \@uniqs;
}
On Mon, Jan 6, 2014 at 8:46 PM, Carlos Eduardo Panazzolo Guidugli <
guidugli@gmail.com> wrote:
Show quoted text> Hi, found the problem that prevented DBIx-Class-Schema-Loader from getting
> the relationships.
>
> On the file /usr/local/share/perl5/DBIx/Class/Schema/Loader/DBI/DB2.pm,
> method _table_fk_info, the query is currently:
> my $sth = $self->{_cache}->{db2_fk} ||= $self->dbh->prepare(<<'EOF');
> SELECT tc.constname, sr.reftabschema, sr.reftabname,
> kcu.colname, rkcu.colname, kcu.colseq,
> sr.deleterule, sr.updaterule
> FROM ........
>
> The problem is that DB2 retrieves some columns preserving spaces at its
> end. So, the sr.reftabschema was bringing 'MYSCHEMA ' instead of
> 'MYSCHEMA'.
>
> To solve this problem I changed the query to:
> my $sth = $self->{_cache}->{db2_fk} ||= $self->dbh->prepare(<<'EOF');
> SELECT trim(tc.constname), trim(sr.reftabschema), trim(sr.reftabname),
> trim(kcu.colname), trim(rkcu.colname), kcu.colseq,
> sr.deleterule, sr.updaterule
> FROM .........
>
> With this change I was able to retrieve relation information.
>
> Another point that I'd like to show is about UNIQUE constraint in method
> _table_uniq_info. In DB2, it is possible to create a UNIQUE constraint, or
> a UNIQUE index with exactly the same effect. I was using unique indexes, so
> none appeared on the model. In order to make it work I created the unique
> constraints as well. I am new to DBIx, but what would happen if there are
> two unique constraints in the resulting class, as shown in the example
> below.
>
> __PACKAGE__->add_unique_constraint("SERVICELINE_SLNAME_UN", ["slname"]);
> __PACKAGE__->add_unique_constraint("SERVICELINE_SLNAME_IDX", ["slname"]);
>
> If this is not a problem, maybe the query could be changed to retrieve
> unique indexes as well:
>
> SELECT sc.colname, si.name, sc.colseq
> FROM sysibm.sysindexes si, sysibm.sysindexcoluse sc
> WHERE si.tbcreator=? and si.name=sc.indname and si.tbname=? AND
> si.uniquerule = 'U'
>
> UNION
>
> SELECT kcu.colname, kcu.constname, kcu.colseq
> FROM syscat.tabconst as tc
> JOIN syscat.keycoluse as kcu
> ON tc.constname = kcu.constname
> AND tc.tabschema = kcu.tabschema
> AND tc.tabname = kcu.tabname
> WHERE tc.tabschema = ? and tc.tabname = ? and tc.type = 'U'
>
> What do you think? Also, it may be a safe bet to add TRIM to char, varchar
> columns that have strings with variable length.
>
> Thank you
>
>
> On Mon, Jan 6, 2014 at 3:47 PM, Carlos Eduardo Panazzolo Guidugli <
> guidugli@gmail.com> wrote:
>
>> Thank you. Do you of any bug regarding relationships? Somehow the loader
>> detects the primary keys but is not building the has_many, belongs_to, etc
>> methods.
>> On Jan 2, 2014 8:07 PM, "Dagfinn Ilmari Mannsaker via RT" <
>> bug-DBIx-Class-Schema-Loader@rt.cpan.org> wrote:
>>
>>
>
