[Koha-devel] bug in marc_subfield_structure table definition?

[Fernando Canizo]

On 12/04/11 09:58, Galen Charlton wrote:
> On Tue, Apr 12, 2011 at 8:50 AM, Fernando Canizo<conan at lugmen.org.ar>  wrote:
>> Q1. Shouldn't both be equal?
>
> Yes.
>
>> Q2. Shouldn't authorised_value in marc_subfield_structure be a foreign key
>> referencing category on authorised_values table?
>
> Yes.  But note that implementing this would mean reshuffling the
> installation SQL scripts a bit.

With these answers in mind I filed bug 6175. Then added a patch that 
made both equal and varchar(30) because I found data in the SQL scripts 
that added values with more than 20 characters. I also added the constraint.

In fact I got a little ahead of myself and added the constraing for all 
these tables:

auth_subfield_structure
auth_tag_structure
borrower_attribute_types
marc_subfield_structure and
marc_tag_structure

I was too eager and also about to leave work, so I sent the patch 
without testing it, the next day I found the SQL script I modified 
didn't succeded. Then I found that there are insertions which try to 
insert an authorised values (actually they are categories, but the 
column names are authorised_value) which are empty strings, while others 
insert NULL.

I believe the proper set is NULL, since it's the default in the 
definition anyway, but I wanted to ask first here if I should do that 
change: replace all '' insertions with NULL for authorised_value columns 
(which point to category) or either do a nasty insertion in the 
authorised_values table with an empty string category so the other 
insertions can succeed?

Too much words and I don't know if I get myself clear, let's go with 
code better:

Should I replace all lines like:

insert into auth_subfield_structure (..., authorised_value, ...) values 
(..., '', ...)

with:

insert into auth_subfield_structure (..., authorised_value, ...) values 
(..., NULL, ...)

Or should I add this instead:

INSERT INTO authorised_values (category, authorised_value, lib, 
lib_opac, imageurl) VALUES ('', '', '', '', '');

I think the first is the right option, but I want an opinion.

Also I found insertions which point to a non existant categories, like:

INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, 
`liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, 
`tab`, `authorised_value`, `authtypecode`, `value_builder`, `isurl`, 
`hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`) VALUES
('101', 'a', 'langue doc.', '', 1, 1, '', 1, 'LANG', '', '', 0, 0, '', 
'', '', NULL),

For those I'm making this kind of insertions to allow SQL script to succeed:

INSERT INTO authorised_values (category, authorised_value, lib, 
lib_opac, imageurl) VALUES ('LANG', 'FIXME!', 'FIXME!', 'FIXME!', '');

It's "FIXME!" because I don't have the, probably several, values that go 
in there. Is either that or comment the offending lines. What should I 
do, comment them or keep the hackish insertion?

-- 
Fernando Canizo (a.k.a. conan) - http://conan.muriandre.com/
GCS d? s:+ a C++ P--- L++++ E--- W+++ w--- M-- PE-- !tv b+++ h---- y+++