Change database structure to allow extra unique index

[harolds]

It would be nice if you would use an index (both for quickly finding translations and to prevent double entries).
But because you use utf8mb4 every character takes 4 bytes to be stored.

So trying to add this index:
ALTER TABLE eabo.translator_translations ADD UNIQUE translator_translations_unique_key (locale, namespace, group, item);

Results in this error:
#1071 - The specified search key is to long. The maximum length is 3072

Please change the database structure to use a basic utf8_general_ci.
And why is every field 255 characters long? A bit long for translation labels.
I think that 100 charcters is enought for most of us.

So something like this:

===

CREATE TABLE translator_languages (
id int(10) UNSIGNED NOT NULL,
locale varchar(6) COLLATE utf8_general_ci NOT NULL,
name varchar(60) COLLATE utf8_general_ci NOT NULL,
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL,
deleted_at timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE translator_translations (
id int(10) UNSIGNED NOT NULL,
locale varchar(6) COLLATE utf8_general_ci NOT NULL,
namespace varchar(100) COLLATE utf8_general_ci NOT NULL DEFAULT '*',
group varchar(100) COLLATE utf8_general_ci NOT NULL,
item varchar(100) COLLATE utf8_general_ci NOT NULL,
text text COLLATE utf8_general_ci NOT NULL,
unstable tinyint(1) NOT NULL DEFAULT '0',
locked tinyint(1) NOT NULL DEFAULT '0',
created_at timestamp NULL DEFAULT NULL,
updated_at timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE translator_languages
ADD PRIMARY KEY (id),
ADD UNIQUE KEY translator_languages_locale_unique (locale);

ALTER TABLE translator_translations
ADD PRIMARY KEY (id),
ADD KEY translator_translations_locale_foreign (locale);

ALTER TABLE eabo.translator_translations ADD UNIQUE translator_translations_unique_key (locale, namespace, group, item)

ALTER TABLE translator_languages
MODIFY id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE translator_translations
MODIFY id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE translator_translations
ADD CONSTRAINT translator_translations_locale_foreign FOREIGN KEY (locale) REFERENCES translator_languages (locale);

===

[Balakir]

To add to this I have the same issue since 3.4 was released and the limit of 150 was removed from the namespace, group and item columns.

This prevents the migration from running as the index is too large coming in at 3,084 bytes and that is before the migration runs to increase locale from 6 to 10 chars which would make the index 3,100 bytes

For myself a simple solution of just updating the migration file to re-instate namespace, group and item back to 150 chars would be a sufficient fix.

PR #168 created that re-instates the 150 char limit.