DBIx::Class has_one <-> might_have relationship

Question

I'm trying to set up a database, the relevant bits of which are shown below. I'm using SQLite3 (3.8.8.3-1) on Arch Linux, with DBIx::Class 0.082820.

It's part of a simple book-keeping system. An invoice line has_one transaction, but a transaction only might_have an corresponding invoice line (because some transactions can be created without an invoice).

I can't get DBIx::Class to insert an invoice line and its corresponding transaction in one go. The error messages are below too.

Am I doing it wrong? Or doing something that doesn't make sense?

And why does it start by searching for an existing transaction with the same description?

Here are the gory details of my much-simplified test case:

InvoiceLine.pm:

package Test::DB::Schema::Result::InvoiceLine;

use strict;
use warnings;

use base 'DBIx::Class::Core';
__PACKAGE__->table("invoice_lines");
__PACKAGE__->add_columns(

  "id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },

  "txn_id",
  { data_type => "integer", is_foreign_key => 1, is_nullable => 0, 
    is_deferrable => 1 }, # tried this, but it doesn't help

  'details',
  { data_type => 'text', is_nullable => 0 },

);
__PACKAGE__->set_primary_key("id");

# Invoice line has an associated transaction
__PACKAGE__->has_one(
  "txn",
  "Test::DB::Schema::Result::Transaction",
  'id',
);

# Experimental -- this doesn't work either
#__PACKAGE__->belongs_to(
#  "txn",
#  "Test::DB::Schema::Result::Transaction",
#  "txn_id",
#);

1;

Transaction.pm:

use utf8;
package Test::DB::Schema::Result::Transaction;

use strict;
use warnings;

use base 'DBIx::Class::Core';
__PACKAGE__->table("transactions");
__PACKAGE__->add_columns(
    "id",
    { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },

    'description',
    { data_type => 'text', is_nullable => 0 },

    # Invoice line 
    # Null if no associated invoice
    'invoice_line_id',
    {data_type => 'integer', is_nullable => 1 },
);
__PACKAGE__->set_primary_key("id");

# Some transactions have a single corresponding
# invoice line
__PACKAGE__->might_have(
    "invoice_line",
    "Test::DB::Schema::Result::InvoiceLine",
    'id',
    { cascade_copy => 0, cascade_delete => 0 },
);
# EXPERIMENTAL == this doesn't work either
# might_have isn't working, so try has_many (where many can be 0):
#__PACKAGE__->has_many(
#   'invoice_lines',
#   "Test::DB::Schema::Result::InvoiceLine",
#   'txn_id',
#);

1;

Test.pl

#!/usr/bin/perl
# Test.pl
# Testing might_have <-> has_one relationship
use Test::DB::Schema;
my $schema = Test::DB::Schema->connect(
    "dbi:SQLite:dbname=dbic_test.db", '', '', {}
);
$schema->deploy({ add_drop_table => 1 } , '.');
$schema->storage->debug(1);
my $data1 = {
    details => 'abc',
    txn => {
        description => 'xyz',
    }
};
my $new1 = $schema->resultset('InvoiceLine')->create($data1);

The result of running Test.pl is:

BEGIN WORK
SELECT me.id, me.description, me.invoice_line_id FROM transactions me WHERE ( me.description = ? ): 'xyz'
INSERT INTO transactions ( description) VALUES ( ? ): 'xyz'
INSERT INTO invoice_lines ( details, id) VALUES ( ?, ? ): 'abc', '1'
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::SQLite::st execute failed: NOT NULL constraint failed: invoice_lines.txn_id [for Statement "INSERT INTO invoice_lines ( details, id) VALUES ( ?, ? )"] at ./Test.pl line 16
DBIx::Class::Storage::TxnScopeGuard::DESTROY(): A DBIx::Class::Storage::TxnScopeGuard went out of scope without explicit commit or error. Rolling back. at /usr/share/perl5/site_perl/DBIx/Class/Exception.pm line 77
ROLLBACK

score 1 · Accepted Answer · answered Apr 19 '15 at 22:15

1

Wrong relationship definitions. Use this:

# InvoiceLine.pm
__PACKAGE__->might_have(
    "txn",
    "Test::DB::Schema::Result::Transaction",
    "invoice_line_id",
);

# Transaction.pm
__PACKAGE__->belongs_to(
    "invoice_line",
    "Test::DB::Schema::Result::InvoiceLine",
    "invoice_line_id",
);

answered Apr 19 '15 at 22:15

Denis Ibaev

2,470
23
29

Thanks for the answer -- I'll try that tomorrow. – Chris Dennis Apr 19 '15 at 22:57

score 1 · Answer 2 · answered Apr 20 '15 at 19:13

Thanks to Denis Ibaev's answer, I've reworked the problem, and found a good solution.

In fact, I need the invoice line to have a has_one relationship rather than might_have, but that's only a slight change.

Changing the transaction's side of the relationship from might_have to belongs_to was the important bit.

I also had to manually enter the txn_id in the invoice line.

Here's my new code:

InvoiceLine.pm:

package Test::DB::Schema::Result::InvoiceLine;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table("invoice_lines");
__PACKAGE__->add_columns(
    "id",
    { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
    "txn_id",
    { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, # because , is_deferrable => 1 }, doesn't work with SQLite
    'details',
    { data_type => 'text', is_nullable => 0 },
);
__PACKAGE__->set_primary_key("id");
# Invoice line has an associated transaction
__PACKAGE__->has_one(
    "txn",
    "Test::DB::Schema::Result::Transaction",
    'invoice_line_id',
);
1;

Transaction.pm:

package Test::DB::Schema::Result::Transaction;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table("transactions");
__PACKAGE__->add_columns(
    "id",
    { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
    'description',
    { data_type => 'text', is_nullable => 0 },
    # Invoice line 
    # Null if no associated invoice
    'invoice_line_id',
    {data_type => 'integer', is_nullable => 1 }, 
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->belongs_to(
    "invoice_line",
    "Test::DB::Schema::Result::InvoiceLine",
    'invoice_line_id', # our_fk_column
);
1;

Test.pl:

my $schema = Test::DB::Schema->connect(
    "dbi:SQLite:dbname=dbic_test.db", '', '', {}
);
$schema->deploy({ add_drop_table => 1 } , '.');
$schema->storage->debug(1);
my $data1 = {
    details => 'abc',
    txn => {
        description => 'xyz',
    }
};
$schema->txn_do(sub {
    my $new1 = $schema->resultset('InvoiceLine')->create($data1);
    # add the reverse link
    $new1->txn_id($new1->txn->id);
    $new1->update;
}); # end of txn_do
# Add another one with the same data to make sure
# they end up as separate rows
$schema->txn_do(sub {
    my $new2 = $schema->resultset('InvoiceLine')->create($data1);
    $new2->txn_id($new2->txn->id);
    $new2->update;
}); # end of txn_do

Running Test.pl generates and runs this SQL:

BEGIN WORK
INSERT INTO invoice_lines ( details) VALUES ( ? ): 'abc'
INSERT INTO transactions ( description, invoice_line_id) VALUES ( ?, ? ): 'xyz', '1'
UPDATE invoice_lines SET txn_id = ? WHERE ( id = ? ): '1', '1'
COMMIT
BEGIN WORK
INSERT INTO invoice_lines ( details) VALUES ( ? ): 'abc'
INSERT INTO transactions ( description, invoice_line_id) VALUES ( ?, ? ): 'xyz', '2'
UPDATE invoice_lines SET txn_id = ? WHERE ( id = ? ): '2', '2'
COMMIT

And now the tables contain the right values:

Invoice Lines       
1|1|abc
2|2|abc

Transactions
1|xyz|1
2|xyz|2

To summarise what this has taught me: has_many, has_one, and might_have relationship (nearly) always apply in the parent->child direction; belongs_to applies in the other direction. — Chris Dennis, Apr 22 '15 at 19:01

DBIx::Class has_one <-> might_have relationship

2 Answers2