Bug 5458

Summary: length of items.ccode disagrees with authorised_values.authorised_value
Product: Koha Reporter: Peter Huerter <pete.huerter>
Component: DatabaseAssignee: Fridolin Somers <fridolin.somers>
Status: CLOSED FIXED QA Contact: Bugs List <koha-bugs>
Severity: minor    
Priority: P5 - low CC: bibliwho, black23, conan, fridolin.somers, jonathan.druart, josef.moravec, katrin.fischer, kyle, martin.renvoize, nick
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=18097
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 21617    
Attachments: Bug 5458: increase ccode column to varchar 80
Bug 5458: Update DBIx schema
Bug 5458: increase ccode column to varchar 80
Bug 5458: Update DBIx schema
Bug 5458: increase ccode column to varchar 80
Bug 5458: Update DBIx schema
Bug 5458: increase ccode column to varchar 80
Bug 5458: Update DBIx schema

Description Peter Huerter 2010-11-30 17:23:15 UTC
Koha allows you to define an authorised value having length 80, but then you
can't use it.

Here is why:

The SQL field items.ccode is a varchar(10).

The SQL field authorised_values.authorised_value is a varchar(80).

Therefore the authorised value is truncated to length 10 in items.ccode and the mapping from authorised value to description string fails in Koha.

Not sure how this change will "bubble up" in Koha but the change to the table schema seems straightforward:  increase the size of items.ccode to varchar(80).  This is preferable to shortening authorised_values.authorised_value since we have many category codes (CCODE) that are longer than length 10.
Comment 1 Peter Huerter 2010-11-30 17:26:30 UTC
Precedent for increasing the size of items.ccode:

items.location is varchar(80) which maps to LOC authorised values.

items.ccode should be the same size as items.location.
Comment 2 Katrin Fischer 2013-03-24 18:08:23 UTC
In current versions of Koha:

items.ccode varchar(10)
items.locatrion varchar(80)

authorised_value varchar(80)

So there is still some inconsistency.
Comment 3 Fridolin Somers 2018-09-04 10:19:11 UTC
Created attachment 78385 [details] [review]
Bug 5458: increase ccode column to varchar 80

Koha allows you to define an authorised value having length 80, but then you
can't use it.

Here is why:
The SQL field items.ccode is a varchar(10).
The SQL field authorised_values.authorised_value is a varchar(80).
Therefore the authorised value is truncated to length 10 in items.ccode and the mapping from authorised value to description string fails in Koha.

This patch increases ccode column to varchar 80, like location.

Test plan :
1) Check there is on item field a subfield with authorised value category CCODE
2) Check this subfield is linked with database column items.ccode
3) Create an authorised values in category CCODE with 'DOCUMENTATION'
4) Edit an item on ccode and choose 'DOCUMENTATION'
5) Save item
6) Look at items.ccode in database or export in MARCXML
7) Without patch the value is troncated to 'DOCUMENTAT', not with patch
Comment 4 Fridolin Somers 2018-09-04 10:19:30 UTC
Created attachment 78386 [details] [review]
Bug 5458: Update DBIx schema
Comment 5 Cab Vinton 2018-09-04 11:12:43 UTC
Patch applied successfully in sandbox, but value still appears truncated to DOCUMENTAT both w/ report displaying items.ccode and w/ XML export of record.
Comment 6 Fridolin Somers 2018-09-10 23:43:37 UTC
(In reply to Cab Vinton from comment #5)
> Patch applied successfully in sandbox, but value still appears truncated to
> DOCUMENTAT both w/ report displaying items.ccode and w/ XML export of record.

Oh indeed my test plan is missing that the trucation is made when item is saved.
I correct the test plan.
Comment 7 Fridolin Somers 2018-09-10 23:48:33 UTC
Created attachment 78553 [details] [review]
Bug 5458: increase ccode column to varchar 80

Koha allows you to define an authorised value having length 80, but then you
can't use it.

Here is why:
The SQL field items.ccode is a varchar(10).
The SQL field authorised_values.authorised_value is a varchar(80).
Therefore the authorised value is truncated to length 10 in items.ccode and the mapping from authorised value to description string fails in Koha.

This patch increases ccode column to varchar 80, like location.

Test plan :
1) Dont apply patches
2) Check there is on item field a subfield with authorised value category CCODE
3) Check this subfield is linked with database column items.ccode
4) Create an authorised values in category CCODE with 'DOCUMENTATION'
5) Edit an item on ccode and choose 'DOCUMENTATION'
6) Save item
7) Look at items.ccode in database or export in MARCXML
8) The value is troncated to 'DOCUMENTAT'
9) Apply patches and update database with installer/data/mysql/atomicupdate/bug_5458.sql
10) Repeate step 5 to 7
11) The value is not truncated 'DOCUMENTATION'
Comment 8 Fridolin Somers 2018-09-10 23:48:52 UTC
Created attachment 78554 [details] [review]
Bug 5458: Update DBIx schema
Comment 9 Fridolin Somers 2018-09-10 23:52:19 UTC
(In reply to Fridolin SOMERS from comment #6)
> (In reply to Cab Vinton from comment #5)
> > Patch applied successfully in sandbox, but value still appears truncated to
> > DOCUMENTAT both w/ report displaying items.ccode and w/ XML export of record.
> 
> Oh indeed my test plan is missing that the trucation is made when item is
> saved.
> I correct the test plan.

To test on a sandbox :
- configure a sandbox with no patch using "master" instead of bug number
- run test plan until step 8
- reconfigure sandbox with this bug number
- run test plan skipping steps 8,9 and 10

Thanks for your tests ;)
Comment 10 Michal Denar 2018-09-24 15:13:36 UTC
Created attachment 79316 [details] [review]
Bug 5458: increase ccode column to varchar 80

Koha allows you to define an authorised value having length 80, but then you
can't use it.

Here is why:
The SQL field items.ccode is a varchar(10).
The SQL field authorised_values.authorised_value is a varchar(80).
Therefore the authorised value is truncated to length 10 in items.ccode and the mapping from authorised value to description string fails in Koha.

This patch increases ccode column to varchar 80, like location.

Test plan :
1) Dont apply patches
2) Check there is on item field a subfield with authorised value category CCODE
3) Check this subfield is linked with database column items.ccode
4) Create an authorised values in category CCODE with 'DOCUMENTATION'
5) Edit an item on ccode and choose 'DOCUMENTATION'
6) Save item
7) Look at items.ccode in database or export in MARCXML
8) The value is troncated to 'DOCUMENTAT'
9) Apply patches and update database with installer/data/mysql/atomicupdate/bug_5458.sql
10) Repeate step 5 to 7
11) The value is not truncated 'DOCUMENTATION'

Signed-off-by: Michal Denar <black23ěgmail.com>

https://bugs.koha-community.org/show_bug.cgi?id=17282
Comment 11 Michal Denar 2018-09-24 15:13:41 UTC
Created attachment 79317 [details] [review]
Bug 5458: Update DBIx schema

Signed-off-by: Michal Denar <black23ěgmail.com>

Signed-off-by: Your Full Name <your_email>

https://bugs.koha-community.org/show_bug.cgi?id=17282
Comment 12 Michal Denar 2018-09-24 15:17:13 UTC
Sorry, my devbox drive me crazy .... 

Patch tested but some mess added on sign.

Mike
Comment 13 Fridolin Somers 2018-09-24 15:24:45 UTC
Indeed, signoff text is strange :

Signed-off-by: Michal Denar <black23ěgmail.com>
Signed-off-by: Your Full Name <your_email>

Please correct
Comment 14 Michal Denar 2018-09-24 15:34:37 UTC
I know, It's some default value in cofiguration. I've checked setting twice and set user name and email manually, but still same.

I'll start again from scratch.


Sorry


Mike
Comment 15 Fridolin Somers 2018-09-24 15:44:13 UTC
(In reply to Michal Denar from comment #14)
> I know, It's some default value in cofiguration. I've checked setting twice
> and set user name and email manually, but still same.
> 
> I'll start again from scratch.
> 
> 
> Sorry
> 
> 
> Mike

Its just a text in commit message, you may correct manually with "git commit --amend".

Thanks a lot for testing ;)
Comment 16 Martin Renvoize 2018-09-24 17:31:37 UTC
Created attachment 79331 [details] [review]
Bug 5458: increase ccode column to varchar 80

Koha allows you to define an authorised value having length 80, but then you
can't use it.

Here is why:
The SQL field items.ccode is a varchar(10).
The SQL field authorised_values.authorised_value is a varchar(80).
Therefore the authorised value is truncated to length 10 in items.ccode and the mapping from authorised value to description string fails in Koha.

This patch increases ccode column to varchar 80, like location.

Test plan :
1) Dont apply patches
2) Check there is on item field a subfield with authorised value category CCODE
3) Check this subfield is linked with database column items.ccode
4) Create an authorised values in category CCODE with 'DOCUMENTATION'
5) Edit an item on ccode and choose 'DOCUMENTATION'
6) Save item
7) Look at items.ccode in database or export in MARCXML
8) The value is troncated to 'DOCUMENTAT'
9) Apply patches and update database with installer/data/mysql/atomicupdate/bug_5458.sql
10) Repeate step 5 to 7
11) The value is not truncated 'DOCUMENTATION'

Signed-off-by: Michal Denar <black23@gmail.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 17 Martin Renvoize 2018-09-24 17:31:41 UTC
Created attachment 79332 [details] [review]
Bug 5458: Update DBIx schema

Signed-off-by: Michal Denar <black23@gmail.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 18 Martin Renvoize 2018-09-24 17:32:39 UTC
Works well, nice bit of tidy up Fridolin, thanks!

Tidied up the signoff lines and passing qa.
Comment 19 Nick Clemens 2018-09-26 19:38:08 UTC
Awesome work all!

Pushed to master for 18.11
Comment 20 Martin Renvoize 2018-09-27 12:33:11 UTC
Pushed to 18.05.x for 18.05.05
Comment 21 Fridolin Somers 2018-10-03 13:34:53 UTC
Pushed to 17.11.x for 17.11.11
Comment 22 Jonathan Druart 2018-10-19 11:39:24 UTC
statistics.ccode change missing from update DB entry!
See bug 21617
Comment 23 Jonathan Druart 2019-12-12 08:22:48 UTC
*** Bug 18097 has been marked as a duplicate of this bug. ***