Improve handling of views with nullable columns, re nullable PK warnings

[timbunce]

When I run rdbic.pl on our main db (which no longer crashes, thanks!) I'm greeted with pages of output like this:

DBIx::Class::ResultSource::set_primary_key(): Primary key of source 'ViewLeads' includes the column 'is_courtesy_lead' which has its 'is_nullable' attribute set to true. This is a mistake and will cause various Result-object operations to fail at /home/tim/trunk/comp/rapidapp/cpan/lib/perl5/RapidApp/TableSpec/Role/DBIC.pm line 236
DBIx::Class::ResultSource::set_primary_key(): Primary key of source 'ViewLeads' includes the column 'license_id' which has its 'is_nullable' attribute set to true. This is a mistake and will cause various Result-object operations to fail at /home/tim/trunk/comp/rapidapp/cpan/lib/perl5/RapidApp/TableSpec/Role/DBIC.pm line 236
DBIx::Class::ResultSource::set_primary_key(): Primary key of source 'ViewLeads' includes the column 'marketing_ad_route' which has its 'is_nullable' attribute set to true. This is a mistake and will cause various Result-object operations to fail at /home/tim/trunk/comp/rapidapp/cpan/lib/perl5/RapidApp/TableSpec/Role/DBIC.pm line 236
DBIx::Class::ResultSource::set_primary_key(): Primary key of source 'ViewLeads' includes the column 'realtor_sms_count' which has its 'is_nullable' attribute set to true. This is a mistake and will cause various Result-object operations to fail at /home/tim/trunk/comp/rapidapp/cpan/lib/perl5/RapidApp/TableSpec/Role/DBIC.pm line 236
DBIx::Class::ResultSource::set_primary_key(): Primary key of source 'ViewLeads' includes the column 'lender_sms_count' which has its 'is_nullable' attribute set to true. This is a mistake and will cause various Result-object operations to fail at /home/tim/trunk/comp/rapidapp/cpan/lib/perl5/RapidApp/TableSpec/Role/DBIC.pm line 236
DBIx::Class::ResultSource::set_primary_key(): Primary key of source 'ViewLeads' includes the column 'lead_sms_response_count' which has its 'is_nullable' attribute set to true. This is a mistake and will cause various Result-object operations to fail at /home/tim/trunk/comp/rapidapp/cpan/lib/perl5/RapidApp/TableSpec/Role/DBIC.pm line 236

A total of 3687 instances of that kind of warning are generated!

I presume this is because there's no primary key defined on a view so a 'synthetic' PK is defined as being all the columns. Perhaps the synthetic PK could be defined as all non-nullable columns?

[vanstyn]

This is a DBIC-generated message... @ribasushi - any suggestions?

[ribasushi]

@timbunce correctly deduced what happens, and it is squarely within RapidApp. Defining stuff as a primary key (or unique constraint) which then can return NULLs from the actual cursors can lead to a lot of problems, hence the warning.

@vanstyn Is there a reason you need the "fake PK" in the first place?

[vanstyn]

@ribasushi - we need a PK to be able to address the row (i.e. via a REST url)... for tables with no primary key at all, I have no choice but to consider the whole row, unless you have a better idea...

[vanstyn]

Also, regarding @timbunce 's suggestion "Perhaps the synthetic PK could be defined as all non-nullable columns?" -- what happens when two rows are identical except for values in nullable columns? This is a very plausible case imo...

[ribasushi]

As with everything else it depends how deep do you want to go.

• Since these are views, you can simply add a $SIG{__WARN__} trap and move on - some complex prefetches will fail, but it is not likely views will be involved in those. And writable views are pretty rare too.
• You could step it up and check for a suitable unique constraint and if none are found convert the thing to a hard exception
• You could reuse the comprehensive logic of DBIC itself deciding on whether a particular selection is unique, and which intelligently can route around a missing PK.

Pick your poison