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.

Activity

Show:
Lars Kiesow
January 13, 2015, 2:21 PM

This is a duplicate of for which there is already a pull request out.

Alexander Bias
January 13, 2015, 7:22 PM

Hi Lars,

thanks for pointing to the duplicated ticket. I must have missed that one, sorry.

However, your pull request in only solves items 1 and 4 of this ticket completely and item 2 of this ticket partly. The second part of item 2, the item 3 (which were both contained in the original mailinglist discussion) as well as items 5, 6 and 7 (which were not contained in the mailinglist discussion) are not solved by the pull request.

So, could you please revise this ticket?

Thanks,
Alex

Tobias Wunden
May 5, 2015, 8:43 PM
Edited

By now, all of the items have been addressed:

1. Pull request by Lars ()
2. Pull request on this ticket
3. I suggest to leave this as is since it is actually a join table between the two tables mh_job and mh_service.
4. Pull request by Lars ()
5. Done
6. Done, including code change.
7. Done

In addition, I have removed use of "KEY" and replaced it with the corresponding "CREATE INDEX" statements.

Now on to testing, will open the pull request once everything is confirmed to be working.

Lukas Rohner
June 18, 2015, 12:31 PM
Edited

Created pull request for point 2. 5. 6. and 7. (https://bitbucket.org/opencast-community/matterhorn/pull-request/562)

Fixed and reviewed

Assignee

Tobias Wunden

Reporter

Alexander Bias

Tags (folksonomy)

Components

Fix versions

Affects versions

Priority

Major