Improve MySQL DDL to make it consistent again

Description

See https://groups.google.com/a/opencast.org/d/msg/matterhorn/HyWMbU2V5yA/aQiiqzjm1HwJ for the background discussion.

Today, we managed to transfer the MySQL DDL and Upgrade Scripts to 1.6 and this discussion came back to my mind.

Now that MH 1.6 is out and there’s some time until the feature freeze for 1.7, please would anyone volunteer to optimize the MySQL DDL in the following terms:

1. In table mh_job_mh_service_registration, the key „mhjobmhservice_registrationservicesRegistration_id“ is created within the CREATE TABLE statement. This in unusual and should be replaced by a CREATE INDEX statement which follows the CREATE TABLE statement.

2. The key mhjobmhservice_registrationservicesRegistration_id and the foreign key mhjobmhservice_registrationservicesRegistration_id don't meet the key name scheme which was used up to 1.6, they are a mix of underscores, camel case and without the standard IX_ / FK_ prefixes.
Lukas explained that there is a problem with the length of key names in Java, but perhaps you could improve these names within the length boundary anyway.

3. Table mh_job_mh_service_registration has two times "mh_" in its name. This is confusing, perhaps it could be renamed to mh_job_service_registration.

4. If the 1.5->1.6 upgrade script will be used after 1.6 again,
the part

should be changed to

which is much simpler

5. Please rename these keys and constraints to adhere the existing name scheme for keys:
FK_job_incident_jobid -> FK_mh_job_incident_jobid
FK_job_context_id -> FK_mh_job_context_id
FK_service_registration_host_registration -> FK_mh_service_registration_host_registration
FK_job_argument_id -> FK_mh_job_argument_id
UNQ_job_context_0 -> UNQ_mh_job_context_0

6. In table mh_group_member, there are colums JpaGroup_id and MEMBERS which are not all lowercase. For MySQL, this might not matter, but for the transition to Postgres and other DB systems which are case sensitive, this is important. I would like to see all column names in lowercase to make DDL transition to Postgres easier

7. In thy MySQL DDL, there are several comments saying

and comments before the ACL manager and groups section
Please remove them as they are inconsistent and unnecessary.

Assignee

Tobias Wunden

Reporter

Alexander Bias

Tags (folksonomy)

Components

Fix versions

Affects versions

Priority

Major
Configure