kohastructure.sql defines: marc_subfield_structure.authorised_value varchar(20) default NULL this column points to: authorised_values.category varchar(10) NOT NULL default '' Quick fix is straightforward: modify marc_subfield_structure to match authorised_values definition. However there's been a request to expand size for category column (http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5458). Also there is discussion to make all references to authorised_values.category foreign key references on koha-dev list.
Created attachment 3911 [details] [review] Fix mismatch, add foreign key constraints, reshuffle sql scripts, fix UI to match DB
(In reply to comment #1) > Created attachment 3911 [details] [review] > Fix mismatch, add foreign key constraints, reshuffle sql scripts, fix UI to > match DB This is broken, working on new one.
Created attachment 10086 [details] [review] Bug 6175 Mismatch on tables definition kohastructure.sql defines: marc_subfield_structure.authorised_value varchar(20) this column points to: authorised_values.category varchar(10) At first tought you would think about shortening in marc_subfield_structure table, but install scripts had values longer than 10 characters. Also there's been talk in the list about expanding the column. So: - Expand on authorised_values.category. - Add foreign keys constraints. - Reshuffle sql scripts to allow insertions given the new constraints. - Expand template input text field to match database column. Signed-off-by: MJ Ray <mjr@phonecoop.coop>
At a basic level, this looks like it works (it now applies and tests pass), so I have signed it off. If the brokenness was more subtle/structural, please make it Failed QA.
QA comment: This patch has some problems: * it introduces a constraint that is wrong: + CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_values` (`category`) ON DELETE CASCADE ON UPDATE CASCADE => the constraint can be on authorised_values, but also on branches or itemtype, so a SQL constraint is unapplicable here * it contains a lot of changes in french file that are just moving things from the bottom to the top of file without reason (at least without relation to this patch. As the patch has been made by someone from argentina, I think it was not intended to update french files !) * there is no updatedatabase part on this patch making existing Koha being de-synchronised from freshly installed Koha for those reasons, failed QA, sorry
(In reply to comment #5) > QA comment: This patch has some problems: > * it introduces a constraint that is wrong: > + CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY > (`authorised_value`) REFERENCES `authorised_values` (`category`) ON DELETE > CASCADE ON UPDATE CASCADE > => the constraint can be on authorised_values, but also on branches or > itemtype, so a SQL constraint is unapplicable here So is this potential mismatch unavoidable with the current database design? > * it contains a lot of changes in french file that are just moving things from > the bottom to the top of file without reason (at least without relation to this > patch. As the patch has been made by someone from argentina, I think it was not > intended to update french files !) > * there is no updatedatabase part on this patch making existing Koha being > de-synchronised from freshly installed Koha These are not currently among the http://wiki.koha-community.org/wiki/Coding_Guidelines#Database but perhaps they should be? This would not be the first patch to change kohastructure.sql without a matching updatedatabase, after all... but I remember them because they caused bugs, so maybe it should be a guideline.
This bug is still valid in master, but the patch appears to be abandoned. I'm going to reset this to NEW.
marc_subfield_structure `authorised_value` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, auth_subfield_structure `authorised_value` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, authorised_value_categories `category_name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', authorised_values `category` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', I think there is still a issue for the auth_subfield_structure.
Bug 29336 db_revs/211200009.pl: q|ALTER TABLE auth_subfield_structure CHANGE authorised_value authorised_value varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL|, Closing this one *** This bug has been marked as a duplicate of bug 29336 ***