[Dbix-class] Problem with non-PK FK's

[Brandon Black]

I haven't dug deep enough yet to find the real source of the issue yet
(I'm still relatively unfamiliar with DBIx::Class guts), but I've
found a nasty relationship issue.  The scenario is one that's not
common in most database schemas, which is probably why it's gone by
unnoticed so far.

The gist of it is documented better by this short script (which uses
sqlite3 and assumes it is in your path) that I can put it in words
really:

---------------------------------------------------------------

#!/usr/bin/perl -w

use strict;
use warnings;

use DBIx::Class::Loader;

unlink './music.db';

open( my $sqlite_fh, '|sqlite3 ./music.db' ) or die "Cannot execute sqlite3";
print $sqlite_fh qq{
    CREATE TABLE albums (
        id INTEGER PRIMARY KEY,
        other_id VARCHAR(32) UNIQUE,
        name VARCHAR(32),
        artist VARCHAR(32),
        year INTEGER
    );

    CREATE TABLE album_artwork (
        art_filename VARCHAR(32) PRIMARY KEY,
        album_other_id VARCHAR(32) REFERENCES albums(other_id)
    );

    INSERT INTO albums VALUES ( 1, 'id1', 'album1', 'artist1', 1999 );
    INSERT INTO albums VALUES ( 2, 'id2', 'album2', 'artist1', 2000 );
    INSERT INTO albums VALUES ( 3, 'id3', 'album3', 'artist2', 1934 );
    INSERT INTO albums VALUES ( 4, 'id4', 'album4', 'artist2', 2020 );

    INSERT INTO album_artwork VALUES ( 'album1.jpg', 'id1' );
    INSERT INTO album_artwork VALUES ( 'album2.jpg', 'id2' );
};
close($sqlite_fh);

my $loader = DBIx::Class::Loader->new(
    dsn                     => "dbi:SQLite:music.db",
    relationships           => 1,
    namespace               => 'MusicDB',
);

my @results = MusicDB::AlbumArtwork->search({ art_filename => 'album1.jpg' });
foreach my $res (@results) {
   my $album = $res->album_other_id; # XXX Fails Here
   print "Album name " . $album->name;
}

--------------------------

In the schema shown in the code above, the album_artwork table's
album_other_id column has a foreign key reference to column other_id
in table albums.  However, other_id is not the primary key of albums,
it is merely a column with a UNIQUE constraint (an alternate key if
you will).  When ->search returns a row from album_artwork, and then
you access the related album_row via album_artwork->album_other_id,
the result is that DBIx::Class cannot see the related row correctly
(it thinks the relationship must be to the primary key), and failing
to find it, it tries to create one via insert.

Luckily for my example schema above, the real primary key and the
"other_id" are two different data types, so the insert fails and the
script bombs.  If they were both integers, it would actually succeed
in inserting a new row into albums with the new row's "id" set to the
old row's "other_id", and the rest of the values null-ed out (you can
see this in action by changing other_id to an integer instead of
varchar, and changing the 'idX' column data in the insert statements
to integers that do not overlap the ones in 'id').  Not technically a
destructive bug, but it could result in unwarranted junk rows being
inserted into your tables during what should have been a read-only
operation.

I originally hit this problem on a real-world database using
PostgreSQL, but was able to make this test case in SQLite, so I doubt
it is vendor-specific.

I'll see if I can't find the exact source of the issue (or maybe even
a fix for it) this evening, but in the meantime I thought I'd report
that it exists.

-- Brandon