Bug 39176

Summary: Update additional_field_values.record_id to varchar(255)
Product: Koha Reporter: Martin Renvoize (ashimema) <martin.renvoize>
Component: Architecture, internals, and plumbingAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: julian.maurice, nick, tomascohen
Version: Main   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:

Description Martin Renvoize (ashimema) 2025-02-20 11:20:36 UTC
In bug 38663 we had to switch from int(11) to varchar(11) so we could properly store branchcode, the primary key for the branches table.

In bug 38457 I'm now faced with the same issue of needing to update the field from varchar(11) to varchar(80) to allow for the code field in the account_debit_types table which is a varchar(80) primary key.

Looking to the future and how we're starting to add more and more uses of additional fields I think we should pick the largest current primary key field and match that.

SELECT
    t.TABLE_NAME AS table_name,
    k.COLUMN_NAME AS primary_key_field,
    CASE
        WHEN k.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONCAT(k.DATA_TYPE, '(', k.CHARACTER_MAXIMUM_LENGTH, ')')
        WHEN k.NUMERIC_PRECISION IS NOT NULL THEN CONCAT(k.DATA_TYPE, '(', k.NUMERIC_PRECISION, ')')
        ELSE k.DATA_TYPE
    END AS primary_key_type
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS k
    ON t.TABLE_NAME = k.TABLE_NAME
    AND t.TABLE_SCHEMA = k.TABLE_SCHEMA
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
    ON t.TABLE_NAME = ku.TABLE_NAME
    AND t.TABLE_SCHEMA = ku.TABLE_SCHEMA
    AND k.COLUMN_NAME = ku.COLUMN_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON ku.TABLE_NAME = tc.TABLE_NAME
    AND ku.TABLE_SCHEMA = tc.TABLE_SCHEMA
    AND ku.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY t.TABLE_NAME;

Using the above query, I believe that shows the largest is a varchar(255).