Generator-jhipster: Inserts fails with entities having fields named using MySQL reserved words

[pmarrone]

Using the sub-generator to create entities with fields that are named using MySQL keywords (e.g.: index, group) creates database columns that use those keywords as its names. Performing insert operations using jhispter´s generated Repository fails with hibernate´s SqlExceptionHelper reporting an SQL Exception. In my case:

org.hibernate.engine.jdbc.spi.SqlExceptionHelper - You have an error in your SQL syntax; check the     manual that corresponds to your MySQL server version for the right syntax to use near 'group,

Steps to reproduce:

1. Starting from the example jhipster app that uses a MySQL backend, create a new entity using the yo jhipster:entity subgenerator
2. Add at least one field to that entity named with a MySQL keyword (e.g.: group)
3. Lauch the application and go to to the Entities > [entity name] page and click Create a new [entity name]
4. Fill the form with valid values and click Save.

Result: The operation fails because of an SQL exception.
Running the generatad statement in the MySQL terminal fails in the same way unless you surround the offending name with backticks (`).

Using generator-jhipster 2.6.0 package, on Windows 7
MySQL Server version: 5.6.20

[jdubois]

Yes, if you use reserved words it's not going to work.
We can add some validation in the generator, to prevent people from using reserved words.

BTW, this is why we prefix the database tables with "T_".

[pmarrone]

I noticed that tables were prefixed for similar reasons, so I reported this. I thought that maybe it would be desirable to be able to choose a column prefix, or just appending a default column prefix to avoid the issue.
Once the entity is generated, I feel that the introduced defect is not obvious, since everything compiles and runs fine until you try to insert a new entity. Even then, the MySQL exception message is not entirely clarifying.
I'm new to yeoman, but since both the generated domain entity Column annotation and the generated liquibase config use the fieldNameUnderscored variable, prefixing it doesn't seem to break things.
As a workaround, I prefixed it manually in the .json file under the .jhipster folder and re-run the generator before re-launching the application.
I'm not sure if there are other implications involved if the fieldNameUnderscored actually diverges from, well, the uderscored field name.
This is clearly a minor issue and I think your generator is awesome. I'm just trying to help :)

[jdubois]

OK, I'm afraid I'm not being consistent here, but here are my thoughts:

• Prefixing tables: I've always used this, as I have an old DBA background, and in the old times we liked to have our tables prefixed by T_ and our views by V_, so when we typed our queries we knew if we were hitting a table or a view. Of course, this has the added benefit of allowing us to use reserved keywords.
• Prefixing fields: I find it annoying to have all fields prefixed. I used to have fields prefixed by their type (this is the old Hungarian notation, I was a Visual Basic developer in those times). But today I find this really annoying.

-> If I want to be consistent, let's prefix everything
-> But what I currently do is only prefix tables, and then avoid reserved keywords in the field names : that would just be another validation rules for fields

I like the second solution better, but I know I'm not being consistent, so I'm not doing this right away as I need more thoughts on this. Any feedback?

[tagliola]

As this is a Java oriented framework, I would say no prefixing. As you say,
it's old DBA style, not hipster. I dislike the implicit translation I have
to make when querying an entity directly in the database. Adding some
reserved keyword protection would be icing on the cake. You can also string
out the whole table naming and fall back onto the default tablenames then.

On 25 March 2015 at 11:57, Julien Dubois [email protected] wrote:

OK, I'm afraid I'm not being consistent here, but here are my thoughts:

• Prefixing tables: I've always used this, as I have an old DBA
  background, and in the old times we liked to have our tables prefixed by T_
  and our views by V_, so when we typed our queries we knew if we were
  hitting a table or a view. Of course, this has the added benefit of
  allowing us to use reserved keywords.
• Prefixing fields: I find it annoying to have all fields prefixed. I
  used to have fields prefixed by their type (this is the old Hungarian
  notation, I was a Visual Basic developer in those times). But today I find
  this really annoying.

-> If I want to be consistent, let's prefix everything
-> But what I currently do is only prefix tables, and then avoid reserved
keywords in the field names : that would just be another validation rules
for fields

I like the second solution better, but I know I'm not being consistent, so
I'm not doing this right away as I need more thoughts on this. Any feedback?

—
Reply to this email directly or view it on GitHub
https://github.com/jhipster/generator-jhipster/issues/1278#issuecomment-85982023
.

[wzomer]

I don't like prefix tables, I personally always remove it from my projects with jhipster.

[gzsombor]

Me too !

[PeterEltgroth]

I would also prefer no prefixes; however, creating a reserved word checking for MySQL, PostgreSQL, Mongo, and Cassandra may be somewhat time consuming.

MySQL - http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
PostgreSQL - http://www.postgresql.org/docs/7.3/static/sql-keywords-appendix.html
Mongo - http://docs.mongodb.org/manual/reference/command/ , this is closest thing I found to a list Cassandra - https://cassandra.apache.org/doc/cql3/CQL.html

So until it is done please keep the T_.

[jdubois]

Thanks @PeterEltgroth
I'll also add the Java reserved keywords:
https://docs.oracle.com/javase/tutorial/java/nutsandbolts/_keywords.html

[PeterEltgroth]

You're welcome. Thank you for the great project!

[jdubois]

For MongoDB I'm not sure about this list, so I'm ignoring it for the moment... This is already such a huge list!!!!

[jdubois]

OK, I've added validation everywhere (excepted for MongodDB, for which we need a list of keywords, but that's a minor issue I'm ignoring right now).

Now I'm going to remove the "T_" in front of tables:

• This is for consistency with the fields
• This shouldn't break existing code, as we have a JPA mapping (cool!), but I'll do a specific release for this, as this is a big change
• And now the BIG QUESTION: as "User" is a reserved word in PostgreSQL, our "User" JPA entity can't be mapped on a "USER" table. So how should we call it?

[PeterEltgroth]

UserAccount?

[jdubois]

@PeterEltgroth
By the way I don't like our REST endpoint with "/account" and "/user" -> that may be time to refactor all this, and have everything under "account"? I prefer "user", too bad it's a reserved keyword.

[PeterEltgroth]

Account works for me. I was thinking of those who may be building applications related to finance, as I have done in the past, but users in that niche can name their domain differently.

I did check http://www.thesaurus.com/browse/user but none seemed quite correct.

[jdubois]

@PeterEltgroth that's still an issue... I'm not really happy with Account, now.

[atomfrede]

Indeed User seems to be the best fitting name ;) What about (User)Profile or (User)Credential?

[mraible]

The entity name doesn't need to change, does it? In AppFuse, we use "User", but name the table "app_user".

https://github.com/appfuse/appfuse/blob/master/data/common/src/main/java/org/appfuse/model/User.java

[pkraeutli]

We usually call the table for the User entity users.

I think it is OK to have exceptions just for some reserved words. Every database has its own reserved words (in MySQL you can name a table "user", but not "order" for instance).

[bboutier]

As funny suggestions, i have:
Guy
Bro

And more conventional:
Usr
Person
EndUser

[cadocruz]

Users +1

[thiagonuic]

It could be done by implementing a custom NamingStrategy, doing a quick google search I found this link:

http://www.petrikainulainen.net/programming/tips-and-tricks/implementing-a-custom-namingstrategy-with-hibernate/

If we use a proper pluralization lib, it would be done automatically and we could just skip the @Table annotation.

[drissamri]

What about Identity?

Like mentioned before it's probably a good idea to keep the entity as USER and just rename the table.
If the plural form is used, it should be plural for all tables though.

[jdubois]

Thank you all!

• Of course we can't make it Broor Hispter but that would be fun :-)
• Plural is not a good idea, because then other tables would also need to be pluralize
• Identity Profileor UserAccount are my favorites

[wzomer]

+1 UserAccount

[drissamri]

It seems like you want to rename the Entity itself too? If you are I seem to prefer Identity over the other two.

[jdubois]

@drissamri makes a really good point: I wouldn't want to change the "User" entity. It is a very good name, and changing it just because of a reserved keyword in one of the databases is really annoying.

[windhood]

+1 users and all entity changed to plural, it is popular in other frameworks, such as rails

[cselti]

+1 for UserProfile
I'm not prefer the plural form on tables, because it's confusing me, when I have to access my data from SQL console - especially when the plural form isn't only adding an extra 's' (like Access - accesses). The plural form is logic for collection variables, but for tables isn't for me (a table always contains set of records - plural form is unnecessary) and the entity name isn't plural too, so we can skip an "@Table" annotation too, when the entity name is the same, as the table's.

[jdubois]

I know there are other framework that use the plural form ("users"), but I consider this a bad practice -> you then have all your table with "s", this is a useless prefix for me.

So to summarize:

• We'll keep the current Java names -> there will still be a "User" entity, as it's the most logical name, and we're not going to use another one just because of a reserved keyword in one specific database
• We still need to rename the "T_USER" table -> eventually I like @mraible comment about "app_user". As it's a specific JHipster table, we could name all default JHipster tables with a "JHI_" prefix. This is the same thing as the OAuth tables, which are prefixed. This has the advantages of being able to take all those tables at once (I'm doing this with the Oauth tables to ignore them in the Liquibase Maven plugin, and this is quite handy)
• And tables which are generated by the entity sub-generator would not have any prefix, as they are "end user" tables -> if you have a "Foo" entity, it will be mapped on the "FOO" table.

Is everyone OK with that?

[pkraeutli]

Not sure if I get a vote but I am happy with this solution! :+1:

IMHO the User entity usually does not belong to the "business domain" and therefore there is no harm in special treatment of its database table. In some applications you'll maybe not even need a table for users because authentication/authorization happens through some external system.

[jdubois]

@pkraeutli everybody has got a vote, especially if you agree with me :-)

[drissamri]

So basically not much changes, the T_ prefix becomes a JHI_ prefix? ;-)

It's probably the best way to go and it's a good thing to clearly distinguish end user and framework tables. +1

[PeterEltgroth]

Sounds reasonable to me. Keep up the good work!

[piedrahitac]

I think the JHI_ prefix for the automatically generated tables works fine, as long as it is not present in the tables created with the entity sub-generator...

[ricardojlrufino]

Some attributes that I use a lot are 'number' and 'type', and I'm getting errors when adding these attributes.

An alternative would be to enable Quotes:

<property name="hibernate.globally_quoted_identifiers">true</property>