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

1 2 3 4 5 set @exist := (SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'mh_job_mh_service_registration'); set @sqlstmt := if( @exist > 0, 'SELECT "mh_job_mh_service_registration table already existing"', 'CREATE TABLE mh_job_mh_service_registration ( ...

should be changed to

1 CREATE TABLE IF NOT EXISTS mh_job_mh_service_registration ( ...

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

1 -- @changelog UNI-216

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

Steps to reproduce

None

Status

Assignee

Tobias Wunden

Reporter

Alexander Bias

Criticality

None

Tags (folksonomy)

Components

Fix versions

Affects versions

1.6.0

Priority

Major
Configure