Database table indexes too large for common hosting setups

Description

hvp_libraries table coupled with utf8 charset has too large index on some db setups.
This completely hinders some people from installing H5P on their servers.
This is very likely to happen on the other platforms as well.
Just removing this index is problematic because it is used very often, a solution must be found.

Be aware that Moodle is adding a different charset: utf8mb4_unicode_ci" collation (4 bytes per character, allowing emojis).

This would mean either shortening column machine_name to 191 characters (191 x 4 = 764 < 767 characters, which is the default maximum length), or using a prefix for this column in the key definition.

as noted by Jzip.
We should be very deliberate when choosing keys and make sure they are as small as possible to not break this constraint.

Environment

None

Acceptance Criteria

  • Answer the thread at https://h5p.org/node/62795

  • Make sure our indexes work at all common db setups for all platforms

  • If index is changed all queries using this index must be revisited

  • Make sure that an integration test for checking indexes on install is added to the integration test suite, so we don't forget about this limitation on indexes: https://github.com/h5p/h5p-integration-test-suite

  • 64 bytes long

  • A validation of library.json on install/upload

  • Specification of library.json in documentation on h5p.org should be updated

Status

Assignee

Unassigned

Reporter

Thomas Marstrander

Labels

None

Funding

None

Code reviewer

None

Released

None

Time tracking

14h

Fix versions

None

Sprint

None

Priority

High