Bugzilla – Attachment 54988 Details for
Bug 17216
Add a new table to store authorized value categories
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 17216: Add new table authorised_value_categories and populate it
Bug-17216-Add-new-table-authorisedvaluecategories-.patch (text/plain), 5.71 KB, created by
Jonathan Druart
on 2016-08-29 16:19:34 UTC
(
hide
)
Description:
Bug 17216: Add new table authorised_value_categories and populate it
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2016-08-29 16:19:34 UTC
Size:
5.71 KB
patch
obsolete
>From d352a0c93ad47d09ada4885f09e97f316b55b364 Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >Date: Mon, 29 Aug 2016 14:04:53 +0100 >Subject: [PATCH] Bug 17216: Add new table authorised_value_categories and > populate it > >This patch set adds a new table authorised_value_categories to store >authori(s|z)ed value categories into a separate table. >The problematic is explained on bug 15799 comment 4: >We need FK to the AV categories but some may not have authorized values >yet. > >What does this patch set: >- Add a new authorised_value_categories table >- Populate it with known categories >- Update FK items_search_fields.authorised_values_category >- Create a new FK marc_subfield_structure.authorised_value (FIXME should > be authorised_value_categories instead) > >They are some problems this patch set do not take into account: >- The .sql installer files won't insert correctly (will have to be > updated when this patch set will be ready to be pushed) >- All the categories (even the ones without authorized values defined) > are listed when you edit frameworks (marc_subfield_structure.pl) >- There is no way to delete a category (TODO). But to do so it would be > good to have a authorised_value_categories.is_internal field to mark > some categories as "cannot be deleted". > >Test plan: >0/ Execute the DB entry to create and populate the new table and set the FK >1/ Create a new AV category from the admin module (admin/authorised_values.pl) >2/ Add/edit subfield linked to a AV category >(admin/marc_subfield_structure.pl) >3/ You won't be allowed to add AV for branches, itemtypes or cn_source. >They are used internally. >--- > .../mysql/atomicupdate/bug_17216_1_add_table.sql | 38 ++++++++++++++++++++++ > installer/data/mysql/en/mandatory/auth_values.sql | 21 ++++++++++++ > installer/data/mysql/kohastructure.sql | 13 +++++++- > 3 files changed, 71 insertions(+), 1 deletion(-) > create mode 100644 installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql > >diff --git a/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql b/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql >new file mode 100644 >index 0000000..221a7e7 >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql >@@ -0,0 +1,38 @@ >+CREATE TABLE authorised_value_categories ( >+ category_name VARCHAR(32) NOT NULL, >+ primary key (category_name) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; >+ >+-- Add authorised value categories >+INSERT INTO authorised_value_categories (category_name ) >+ SELECT DISTINCT category FROM authorised_values; >+ >+-- Add special categories >+INSERT IGNORE INTO authorised_value_categories( category_name ) >+ VALUES >+ ('Asort1'), >+ ('Asort2'), >+ ('Bsort1'), >+ ('Bsort2'), >+ ('SUGGEST'), >+ ('DAMAGED'), >+ ('LOST'), >+ ('REPORT_GROUP'), >+ ('REPORT_SUBGROUP'), >+ ('DEPARTMENT'), >+ ('TERM'), >+ ('SUGGEST_STATUS'), >+ ('ITEMTYPECAT'); >+ >+-- Add very special categories >+INSERT IGNORE INTO authorised_value_categories( category_name ) >+ VALUES >+ ('branches'), >+ ('itemtypes'), >+ ('cn_source'); >+ >+-- Update the FK >+ALTER TABLE items_search_fields >+ DROP FOREIGN KEY items_search_fields_authorised_values_category; >+ALTER TABLE items_search_fields >+ ADD CONSTRAINT `items_search_fields_authorised_values_category` FOREIGN KEY (`authorised_values_category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE; >diff --git a/installer/data/mysql/en/mandatory/auth_values.sql b/installer/data/mysql/en/mandatory/auth_values.sql >index d7fb280..00eb2fc 100644 >--- a/installer/data/mysql/en/mandatory/auth_values.sql >+++ b/installer/data/mysql/en/mandatory/auth_values.sql >@@ -1,2 +1,23 @@ > INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','0','No','No'); > INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','1','Yes','Yes'); >+INSERT IGNORE INTO authorised_value_categories( category_name ) >+ VALUES >+ ('Asort1'), >+ ('Asort2'), >+ ('Bsort1'), >+ ('Bsort2'), >+ ('SUGGEST'), >+ ('DAMAGED'), >+ ('LOST'), >+ ('REPORT_GROUP'), >+ ('REPORT_SUBGROUP'), >+ ('DEPARTMENT'), >+ ('TERM'), >+ ('SUGGEST_STATUS'), >+ ('ITEMTYPECAT'); >+INSERT IGNORE INTO authorised_value_categories( category_name ) >+ VALUES >+ ('branches'), >+ ('itemtypes'), >+ ('cn_source'); >+ >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index a05ef06..a59598e 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -92,6 +92,17 @@ CREATE TABLE `auth_tag_structure` ( > CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >+ >+-- >+-- Table structure for table `authorised_value_categories` >+-- >+ >+DROP TABLE IF EXISTS `authorised_value_categories`; >+CREATE TABLE `authorised_value_categories` ( >+ `category_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', >+ PRIMARY KEY (`category_name`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ > -- > -- Table structure for table `authorised_values` > -- >@@ -3571,7 +3582,7 @@ CREATE TABLE items_search_fields ( > authorised_values_category VARCHAR(32) NULL DEFAULT NULL, > PRIMARY KEY(name), > CONSTRAINT items_search_fields_authorised_values_category >- FOREIGN KEY (authorised_values_category) REFERENCES authorised_values (category) >+ FOREIGN KEY (authorised_values_category) REFERENCES authorised_value_categories (category_name) > ON DELETE SET NULL ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >-- >2.8.1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 17216
:
54979
|
54980
|
54981
|
54982
|
54983
|
54984
|
54985
|
54987
|
54988
|
54989
|
54990
|
54991
|
54992
|
54993
|
55003
|
55004
|
55005
|
55008
|
55009
|
55010
|
55011
|
55021
|
55022
|
55023
|
55024
|
55025
|
55026
|
55027
|
55028
|
55029
|
55030
|
55031
|
55032
|
55047
|
55207
|
55208
|
55209
|
55210
|
55211
|
55212
|
55213
|
55214
|
55215
|
55216
|
55217
|
55218
|
55365
|
55366
|
55367
|
55368
|
55369
|
55370
|
55371
|
55372
|
55373
|
55374
|
55375
|
55376
|
55692
|
55693
|
55835
|
55840
|
56202
|
56203
|
56204
|
56205
|
56206
|
56207
|
56208
|
56209
|
56210
|
56211
|
56212
|
56213
|
56214
|
56215
|
56216
|
56217
|
56619
|
57729