bug in marc_subfield_structure table definition?
I would file a bug, but since I'm too new here I preferred to ask before. Current marc_subfield_structure table definition looks like this: CREATE TABLE `marc_subfield_structure` ( ... `authorised_value` varchar(20) default NULL, ...); That column points to 'category' in authorised_values table, which is defined like this: CREATE TABLE `authorised_values` ( ... `category` varchar(10) NOT NULL default '', ...); Two questions and a possible digression: Q1. Shouldn't both be equal? Q2. Shouldn't authorised_value in marc_subfield_structure be a foreign key referencing category on authorised_values table? The quick solution for this is to make authorised_value a varchar(10) in marc_subfield_structure, since all other tables pointing to authorised_values.category use that. Making it also a foreign key would be desirable to add database consistency. Should I file a bug or I'm seeing this in a wrong way? Note: the digression grew too extensive, I'll hear answers to before going with it. -- 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+++
Hi, On Tue, Apr 12, 2011 at 8:50 AM, Fernando Canizo <conan@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. Regards, Galen -- Galen Charlton gmcharlt@gmail.com
On 12/04/11 09:58, Galen Charlton wrote:
On Tue, Apr 12, 2011 at 8:50 AM, Fernando Canizo<conan@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+++
participants (2)
-
Fernando Canizo -
Galen Charlton