ETL should not fail when special characters are present in the names of table, column, dbuser and schema

[nupoorkhandelwal]

https://avni.freshdesk.com/a/tickets/3647
more similar tickets

Need:

There are multiple instances where ETL has been failing for multiple implementations whenever special characters are used in the concept name.

Technical Context:

• Postgres allows all special characters
• But when " need to be in a column then it needs to be prepended with a ". Difficult to write SQL queries in reports making it prone to mistakes

Cases already handled in code:

• allowing special characters except double quotes by escaping
• allowing concept names above 63 length by truncating

AC:

• "(doube quotes) if present in names of concept, organisation, schema, db user to be removed when creating ETL tables or schema based on wherever it will fail. No need to handle in any of the above places, if it will not fail.

Logs:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table ctrith_iph.individual_child_mdat_12m rename column "FM31. Identifies the longer stick 3 out of 3 or 4 out of 5)" to "FM31. Identifies the "longer" stick 3 out of 3 or 4 out of 5)";] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431) at org.avniproject.etl.repository.SchemaMetadataRepository.lambda$applyChanges$24(SchemaMetadataRepository.java:298) at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) at org.avniproject.etl.repository.SchemaMetadataRepository.applyChanges(SchemaMetadataRepository.java:298) at jdk.internal.reflect.GeneratedMethodAccessor65.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) at org.avniproject.etl.repository.SchemaMetadataRepository$$SpringCGLIB$$0.applyChanges(<generated>) at org.avniproject.etl.service.SchemaMigrationService.migrate(SchemaMigrationService.java:39) at jdk.internal.reflect.GeneratedMethodAccessor61.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) at org.avniproject.etl.service.SchemaMigrationService$$SpringCGLIB$$0.migrate(<generated>) at org.avniproject.etl.service.EtlService.runFor(EtlService.java:52) at org.avniproject.etl.service.EtlService.runFor(EtlService.java:35) at org.avniproject.etl.scheduler.EtlJob.execute(EtlJob.java:27) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "longer" Position: 152 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292) at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ... 36 more

Has failed with BadSQLGrammar exception for below cases:

alter table gvamp_uat.distribution add column "Is the activity available for linking?" text;
alter table ctrith_iph.individual_child_mdat_12m rename column "FM31. Identifies the longer stick 3 out of 3 or 4 out of 5)" to "FM31. Identifies the "longer" stick 3 out of 3 or 4 out of 5)"
alter table synctest.person rename column "Items required Text" to "Text"

[1t5j0y]

Org name not considered in ETL.
Org creation fails for org with double quotes in db user or schema user field names.
Table name generation already strips special characters including double quotes.

Column name (based on concept name) generation fixed to strip double quotes if present.