DBI::Class relationship naming

[Beatnik]

I've used DBIx::Class to access some similar tables. Some of the methods around a table relationship puzzled me. On one of my tables, it worked but on the other one it didn't. Schema below (Postgres):

CREATE TABLE r1
( id integer,
  name character varying(64),
  PRIMARY KEY (id) 
);

CREATE TABLE t1
( id integer,
  groupnames integer REFERENCES r1(id),
  PRIMARY KEY (id)
);

CREATE TABLE r2
( id integer,
  name character varying(64),
  PRIMARY KEY (id) 
);

CREATE TABLE t2
( id integer,
  groupname integer REFERENCES r2(id),
  PRIMARY KEY (id)
);

So when I use dbicdump to generate my classes, everything seems to be fine, the has_many callers are mapped properly. Despite the fields called groupnames (t1) and groupname (t2), the relationship mapping only works with groupname. When using groupnames, the method will load the identifier and not the relationship value. I probably overlooked something in the DBIx::Class documentation about the naming conventions for table relationships. Since I cracked my head on this for a few days, thought I'd share this so others can avoid the same pitfalls.

use lib qw(lib);
use DBIC::Schema;
use Data::Dumper;

my $schema = DBIC::Schema->connect("dbi:Pg:dbname=dbic; host=localhost
+","pguser","pgpassword");

my $t1_rs = $schema->resultset('T1');
$query_rs = $t1_rs->search;
while (my $account = $query_rs->next)
{ print Dumper $account->groupname->name; }
  
my $t2_rs = $schema->resultset('T2');
$query_rs = $t2_rs->search;
while (my $account = $query_rs->next)
{ print Dumper $account->groupname->name; }

[shmem]

Is this exactly the code you are running? If so, you probably are missing a 's'

my $t1_rs = $schema->resultset('T1');
$query_rs = $t1_rs->search;
while (my $account = $query_rs->next)
{ print Dumper $account->groupname->name; }
#                         here ---^

since in your table t1 the column is named "groupnames", not "groupname".

[Beatnik]

Actually, that was exactly my point. The code is running fine despite the fact that the field is not called the same. The relationship mapping is renaming the field/method.

[soonix]

Can you replicate the problem, if you use names that are more dissimilar, e.g. foogroup and bargroup? Perhaps there is some strncmp involved somewhere (or some other "groupname" …)

[Beatnik]

Complete code (schemas etc) with unambiguous field names:

#!/usr/bin/perl

use lib qw(lib);
use DBIC::Schema;
use Data::Dumper;

my $schema = DBIC::Schema->connect("dbi:Pg:dbname=dbic; host=localhost
+","pguser","pgpassword");

my $t1_rs = $schema->resultset('T1');
$query_rs = $t1_rs->search;
while (my $account = $query_rs->next)
# LOOK HERE!
{ print Dumper $account->fieldwith->name; }
# DOES NOT WORK WITH print Dumper $account->fieldwiths->name;
# METHOD HAS NO *S* IN IT
my $t2_rs = $schema->resultset('T2');
$query_rs = $t2_rs->search;
while (my $account = $query_rs->next)
{ print Dumper $account->fieldwithout->name; }


__DATA__
CREATE DATABASE dbic
  WITH OWNER = pguser
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;
GRANT ALL ON DATABASE ng TO postgres;
REVOKE CONNECT ON DATABASE pguser FROM PUBLIC;

GRANT CONNECT
ON DATABASE ng
TO pguser;

\c dbic

CREATE TABLE r1
(
  id integer,
  name character varying(64),
  PRIMARY KEY (id) 
)
WITH (
  OIDS=TRUE
);

ALTER TABLE r1
  OWNER TO pguser;

CREATE TABLE t1
(
  id integer,
  fieldwiths integer REFERENCES r1(id),
  PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE t1
  OWNER TO pguser;

CREATE TABLE r2
(
  id integer,
  name character varying(64),
  PRIMARY KEY (id) 
)
WITH (
  OIDS=TRUE
);

ALTER TABLE r2
  OWNER TO pguser;

CREATE TABLE t2
(
  id integer,
  fieldwithout integer REFERENCES r2(id),
  PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE t2
  OWNER TO pguser;



REVOKE CONNECT ON DATABASE dbic FROM PUBLIC;

GRANT CONNECT
ON DATABASE dbic
TO pguser;

REVOKE ALL
ON ALL TABLES IN SCHEMA public 
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO pguser;


dbicdump -o dump_directory=./lib \
         -o components='["InflateColumn::DateTime"]' \
         -o debug=1 \
         DBIC::Schema \
         'dbi:Pg:dbname=dbic;  host=localhost' \
         pguser \
         pgpassword


delete from t1;
delete from r1;

insert into r1(id, name) values (1, 'Alpha');
insert into r1(id, name) values (2, 'Bravo');
insert into r1(id, name) values (3, 'Charlie');

insert into t1(id, fieldwiths) values(1, 1);
insert into t1(id, fieldwiths) values(2, 2);
insert into t1(id, fieldwiths) values(3, 3);


delete from t2;
delete from r2;

insert into r2(id, name) values ('1', 'Alpha');
insert into r2(id, name) values ('2', 'Bravo');
insert into r2(id, name) values ('3', 'Charlie');

insert into t2(id, fieldwithout) values(1, '1');
insert into t2(id, fieldwithout) values(2, '2');
insert into t2(id, fieldwithout) values(3, '3');

[soonix]

Probably a red herring, but "fieldwiths" and "fieldwithout" share a common prefix of length 9, as do "groupnames" and "groupname" (also length 9).

[Beatnik]

That would be the easy explanation.. Unfortunately, the production fieldnames are 20+ characters long and the behavior is the same.

[Mr. Muskrat]

I believe that it is working as designed.

You'll want to read through the docs for DBIx::Class::Schema::Loader including DBIx::Class::Schema::Loader::Base. Pay close attention when you get to the section on naming.

[Beatnik]

Thanks.. I did submit a bug report in the mean time so I'll most likely cancel that one or leave it there for future reference.