Changes in the OASP4J Framework to address the issue of providing Column Annotation for user attribute (mapped to column user in database) explicitly in AdvancedRevisionEntity for databases MSSQL Server, PostGres, Oracle

[kiran-vadla]

Changes in the OASP4J Framework to address the issue of providing Column Annotation for user attribute (mapped to column user in database) explicitly in AdvancedRevisionEntity for databases MSSQL Server, PostGres, Oracle

[kiran-vadla]

@hohwille , Any thoughts on this ? As of now , the workaround provided for this DBIntegration Git hub issue is that one has to change AdvancedRevisionEntiy as per the instructions at https://github.com/oasp/oasp4j/wiki/guide-database-migration#database-integration and recompile oasp4j .

The instructions at the wiki say that, to integrate OASP4J with corresponding Database , User (a developer in this case) has to configure spring active profile, say for eg: orcl in case of Oracle Database in oasp4j/samples/src/main/resources/config/application.properties of sample project that is created.

After that , Column annotation for 'user' attribute in AdvancedRevisionEntity has to be changed manually. Hibernate generates the SQL for this Entity accordingly.

Challenges , I see here are

1. making the spring active profile information available from the sample application that is created inside oasp4j/oasp4j-jpa-envers project (and in AdvancedRevisionEnity probably) so as to manipulate the Column annotation for 'user' attribute in AdvancedRevisionEntity on the fly using JavaAssist or any other ByteCode Engineering APIs . Even if this is possible , I don't think this is a concrete solution since the component (in this case oasp4j) that is provided is being manipulated runtime at the User end.

Appreciate your suggestions/thoughts.

Thanks, Kiran.

[hohwille]

To make things clear - we are talking about this class: https://github.com/oasp/oasp4j/blob/develop/modules/jpa-envers/src/main/java/io/oasp/module/jpa/dataaccess/api/AdvancedRevisionEntity.java

Your observation is that the column name user is causing problems in some databases because of keyword-conflicts, correct? When does that problem occur? Because I am using OASP in my project with Oracle Database and we did not have to change anything here to make it work. Without a detailed stracktrace I can not tell you if simply do something wrong or different that is causing the problem. What configuration are you using for hbm2ddl? Do you have the schema in flyway scripts? When does the error occur? If the workaround is to add a column annotation with the quoted column name, would that workaround cause problems in other databases? Coulnd't all this be solved via SQL Dialect?

My input for the aspects are:

• Create separate folders for flyway scripts per database as you seemed to have started here: https://github.com/oasp/oasp4j/tree/develop/samples/core/src/main/resources/db/migration BTW: You should not use orcl for oracle what is causing more confusion and errors than the 2 characters that you are saving. People will for sure file bug reports saying that they properly configured the oracle profile for Oracle DB but "it is not working" (because they should have written orcl for some strange reason). I would expect to find mysql and all the other databases here as well.
• If DDL is created via flyway and we have control over the SQL for that per each DB there will be no problem. Hibernate is able to map the "user" column properly with Oracle, MySQL, etc. Otherwise specify the real problem with full stacktrace.
• As you already started, you also define application properties for each DB as profile: https://github.com/oasp/oasp4j/blob/develop/samples/core/src/main/resources/config/application-orcl.properties IMHO you should include them in the WAR but do not hardcode passwords and JDBC URL (https://github.com/oasp/oasp4j/blob/develop/samples/core/src/main/resources/config/application-orcl.properties). Things such as passwords have to be provided by external application.properties of the end user outside the WAR that should then look like this:

  spring.profiles.active=oracle
  #spring.profiles.active=mysql
  #spring.profiles.active=h2
  #spring.profiles.active=...
  spring.datasource.username=...mylogin...
  spring.datasource.password=...mypassword...
  spring.datasource.url=jdbc:...

  That should be all the end user needs to do besides ensuring that the DB specific jdbc driver in on the classpath.

[kiran-vadla]

@hohwille , thanks for the inputs . I will reproduce the problem/error and provide the full stacktrace.

Thanks, Kiran.

[kiran-vadla]

@hohwille , for Oracle 11g , there is no separate dialect , we have to use Oracle 10g dialect 'org.hibernate.dialect.Oracle10gDialect' . I am using the same.

Regarding 'hbm2ddl', I am not overriding it . It is the same that is defined at (https://github.com/oasp/oasp4j/blob/develop/modules/jpa/src/test/resources/config/app/application-default.properties) database.hibernate.hbm2ddl.auto = create.

Attached is the full stack trace of the error that I receive when I don't add the column annotation '@Column(name = "\"user\"")' for user attribute in https://github.com/oasp/oasp4j/blob/develop/modules/jpa- detailedError.txt envers/src/main/java/io/oasp/module/jpa/dataaccess/api/AdvancedRevisionEntity.java.

Pls let me know.

Thanks, Kiran.

[kiran-vadla]

Hi @hohwille ,

Just to keep you posted , I have tried overriding 'database.hibernate.hbm2ddl.auto' in sample project . Tried 'update' and 'validate' for 'database.hibernate.hbm2ddl.auto' but in vain. I receive the same error.

Thanks, Kiran.

[kiran-vadla]

The fix for this issue is addressed as part of the Pull Request #526 (https://github.com/oasp/oasp4j/pull/526/)

[hohwille]

So the actual error is:

Caused by: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification

And that is triggered from here

at io.oasp.module.jpa.dataaccess.base.AbstractGenericDao.findOne(AbstractGenericDao.java:132)

What comes from here:

at io.oasp.gastronomy.restaurant.salesmanagement.logic.impl.usecase.UcFindBillImpl.findBill(UcFindBillImpl.java:41)

[hohwille]

https://github.com/oasp/oasp4j/blob/master/samples/core/src/main/java/io/oasp/gastronomy/restaurant/salesmanagement/logic/impl/usecase/UcFindBillImpl.java#L40

So findOne on a Bill is not working.

Strange as Bill does not even use any keywords in column names:

https://github.com/oasp/oasp4j/blob/master/samples/core/src/main/java/io/oasp/gastronomy/restaurant/salesmanagement/dataaccess/api/BillEntity.java

Too bad that Oracle generates such lousy error messages and does not even tell us the exact table and column causing the problem.

[hohwille]

However the hbm2ddl property should be set to verify for real usage (application.properties located in default package). For development (config/application.properties) it may be overridden to create. But that may be part of the problem.

[hohwille]

I guess there is a simple solution to the problem but this might require some deeper research. As I said earlier a simple solution to the problem is just to rename the columns entirely to avoid the use of reserved keywords. However, this will cause a compatibility issue. You could also deprecate the related class and create a new copy of it and there simply rename the column(s) (e.g. login instead of user, etc.).

[kiran-vadla]

@hohwille , I have incorporated the changes related to deprecating AdvancedRevisionEntity and creating a new copy and renaming the column to 'userName' instead of 'user' . These changes are submitted as part of PR #526. Please review.

Thanks, Kiran.

[kiran-vadla]

Addressed as part of PR #526 .

Thanks, @kiran-vadla