Simplify SQL and Unitize Index Names

Steps to reproduce

From the mailing list:

Hi,
thanks for the reply, Lukas.
My suggestion would then be that we keep the code as it is to not
change things shortly before the release that might break anything, but
change the index and index name. Do you think that makes sense?
–Lars

On Tue, 2 Dec 2014 08:03:50 +0100
Lukas Rohner <lukas@entwinemedia.com> wrote:

> Hi Lars
>
> 1.) I agree with you, if it works with the simple script we should
> change it. 2.) The table mh_job_mh_service_registration is kind of
> special. It’s generated this way by JPA, so we have to adjust the
> Java as well if we wan’t to change it. If somebody wants to change
> it, feel free to do it but I don’t see any priority here. About the
> Index you mentioned 'KEY
> mhjobmhservice_registrationservicesRegistration_id‘. MySQL has a
> length limit on the names and therefore you can’t have proper and
> readable names anymore when you reach a certain length. Im totally
> fine with reading mhjobmhservice_registrationservicesRegistration_id,
> for example this looks much worser
> ‚mhjobmhserviceregistrationservicesregistrationid‘. Bye the way the
> example you mentioned is automatically named this way by JPA as well,
> but here we easally can adjust the naming without changing the JPA
> class.
>
> 3.) That’s my personal opinion but excluding the index definitions
> from the create table statement makes it in my opinion much more
> readable and as you said easier for porting to another SQL database.
> So I propose to go with this schema. –
>
> Lukas Rohner
> Software Engineer
> Entwine - Knowledge In Motion
>
> > Am 01.12.2014 um 17:29 schrieb Lars Kiesow <lkiesow@uos.de>:
> >
> > Hi Devs,
> > today I talked to our postgres using adopters (@Ulm) which, while
> > working on the 1.6 postgres upgrade scripts for the community docs,
> > came up with some questions about the 1.6 SQL changes.
> >
> > I tried to answer them, but wasn't exactly sure about some questions
> > and like to bring them up here:
> >
> > 1. Complicated Exists Statement
> > ===============================
> >
> > The SQL Upgrade script contains something like:
> >
> > 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 ( ...
> >
> > Is there a reason for not simply using:
> >
> > CREATE TABLE IF NOT EXISTS mh_job_mh_service_registration ( ...
> >
> > My guess was that there is no exact reason. Afaik, both statements
> > are doing the same except for the additional message that is
> > printed in the longer version. Thus using the short version
> > wouldn't be a problem. Correct?
> >
> > 2. Inconsistent Naming
> > ======================
> >
> > Before all tables were named mh_name_lowercase_underscore. With 1.6
> > there is now a table called
> >
> > mh_job_mh_service_registration
> >
> > which is kind of wired because of the two mh_ in the name.
> > Also, before, all indexes were named IX_mh_.... With 1.6 there is
> > now a
> >
> > KEY mhjobmhservice_registrationservicesRegistration_id
> >
> > which is a mix of underscores, camel case and without the IX_
> > prefix.
> >
> > Should we keep the names as they are now or change them before the
> > 1.6 release?
> >
> > 3. Index Definition
> > ===================
> >
> > This is more a request to make the porting of SQL scripts to other
> > DBs easier, but I guess we might as well honor that request. I've
> > always wondered why we are using dedicated CREATE INDEX statements
> > in our ddl scripts and it seems like doing it in the CREATE TABLE
> > statements is a specialty on MySQL.
> >
> > We discarded that with the 1.6 scripts and included internal KEY
> > statements—I guess due to dropping the official postgres support.
> > While it's not a problem, I guess we might want to continue using
> > dedicated CREATE INDEX statements to make things easier for other
> > adopters.
> >
> >
> >
> > Any thoughts on these points? Do we want to keep things as they are
> > now (it's a short time before the release and these are only naming
> > problems, they break no functionality). Do we want to have the names
> > changes in 1.7? Or do we still want to have a fix for 1.6?
> >
> > Regards,
> > Lars

Status

Assignee

Unassigned

Reporter

Lars Kiesow

Severity

Cosmetic Issue

Tags (folksonomy)

None

Components

Fix versions

Affects versions

Priority

Minor
Configure