[Gmod-schema] Did we break the stock table?

[Bob M. <r.m...@im...>]

Hi all,

A while back now, we allowed NULL values for organism_id in the stock
table.  This was to allow "natural diversity" stock material (samples,
populations, whatever) to have undetermined organisms which could be
specified in substocks or via species determination assays in
nd_experiment.  Nobody complained at the time...

I've just been looking at the behaviour of table constraints where
there are NULL values.
The postgres documentation says this:
"However, null values are not considered equal in this comparison.
That means even in the presence of a unique constraint it is possible
to store duplicate rows that contain a null value in at least one of
the constrained columns."
http://www.postgresql.org/docs/8.1/static/ddl-constraints.html


And I tested it thus:

insert into stock (uniquename, type_id) values ('deleteme', 123);
insert into stock (uniquename, type_id) values ('deleteme', 123);

select * from stock;

stock_id | dbxref_id | organism_id | name | uniquename | description |
type_id | is_obsolete
----------+-----------+-------------+------+------------+-------------+---------+-------------
      241 |           |             |      | deleteme   |
|     123 | f
      242 |           |             |      | deleteme   |
|     123 | f

So at least in Chado implementations where NULL is stored for
organism, there could be issues.

However, with Bio::Chado::Schema I get what I would call the expected
behaviour (it seems to Do The Right Thing), even though the
DBIx::Class docs warn to expect undefined behaviour when doing
find_or_create on

Treating each of these chunks of code as an independent transaction
that is later rolled back:

$stocks->find_or_create( { organism_id => 33, uniquename =>
'my-stock', type_id => 4 } );
$stocks->find_or_create( { organism_id => 33, uniquename =>
'my-stock', type_id => 4 } );
# one stock created

$stocks->find_or_create( { organism_id => 33, uniquename =>
'my-stock', type_id => 4 } );
$stocks->find_or_create( { uniquename => 'my-stock', type_id => 4 } );
# one stock created

$stocks->find_or_create( { uniquename => 'my-stock', type_id => 4 } );
$stocks->find_or_create( { uniquename => 'my-stock', type_id => 4 } );
# one stock created

$stocks->find_or_create( { organism_id => undef, uniquename =>
'my-stock', type_id => 4 } );
$stocks->find_or_create( { organism_id => undef, uniquename =>
'my-stock', type_id => 4 } );
# one stock created

$stocks->find_or_create( { organism_id => 33, uniquename =>
'my-stock', type_id => 4 } );
$stocks->find_or_create( { organism_id => undef, uniquename =>
'my-stock', type_id => 4 } );
# TWO stocks created

So I'm not going to lose sleep over it!

cheers,
Bob.