ETL failure due to foreign table columns being introduced in Encounter Form

[himeshr]

Issue

Goonj ETL failure due to foreign table columns being introduced in Village Assessment Encounter Form

Points of concern

• Last 3 columns, "Center / Field office" text, "Organization Name" text, "Account name" are extraneous
• "Organization Name" text column seems to be taken as it is SynConceptValue of the GroupSubject, but where did "Center / Field office" and "Account name" come from.?

Stacktrace

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table "goonj".village_village_assessment_form ("id" integer, "uuid" text, "is_voided" boolean, "created_by_id" integer, "last_modified_by_id" integer, "created_date_time" timestamp with time zone, "last_modified_date_time" timestamp with time zone, "organisation_id" integer, "name" text, "individual_id" integer, "address_id" integer, "earliest_visit_date_time" timestamp with time zone, "max_visit_date_time" timestamp with time zone, "encounter_date_time" timestamp with time zone, "encounter_location" point, "cancel_date_time" timestamp with time zone, "cancel_location" point, "legacy_id" text, "latest_approval_status" text, "Households" text, "Community Types" text, "Any NGO is working there?" text, "Name of NGO" text, "schedule tribe type" text, "Missed Out Communities" text, "Other Occupation" text, "Any Migration Patterns" text, "Disaster Vulnerability?" text, "Other Biggest Challenges" text, "What Kind of Work is Needed?" text, "Other Work Needed" text, "Detail of previous work" text, "Emergency Contact Number" text, "Names of SHGs" text, "Other Missed Out Communities" text, "Select the top three occupations in this village" text, "Other available Drinking Water Sources" text, "Type of Road Connectivity" text, "Nearest Primary School" text, "Biggest Challenges Faced by the Village" text, "Other Source of Data" text, "Population" text, "other schedule tribe" text, "Migrant Community" text, "Community Culture and Aspects" text, "Availability of ASHA/Health Worker" text, "Nearest Health Facility" text, "Source of Data" text, "Name of Surveyor" text, "Date of Survey" date, "Organization Name" text, "Presence of SHGs" text, "Details of Disaster Vulnerability" text, "Available Drinking Water Sources" text, "Is drinking water available year-round?" text, "Public Transport Availability" text, "Common Health Issues" text, "Other Health Issues" text, "Previous Work in This Village?" text, "Preferred quarter for working" text, "Emergency Contact Name" text, "Center / Field office" text, "Organization Name" text, "Account  name" text );
grant all privileges on all tables in schema "goonj" to "goonj";
grant all privileges on all sequences in schema "goonj" to "goonj";
]
	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:309)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.avniproject.etl.repository.SchemaMetadataRepository.applyChanges(SchemaMetadataRepository.java:309)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:569)
	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 java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:569)
	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:57)
	at org.avniproject.etl.service.EtlService.runFor(EtlService.java:40)
	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: column "Organization Name" specified more than once
	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)
	... 38 more

[himeshr]

Issue is that we are joining all syncConceptValues present for the org's subjectType tables, and not the only ones that correspond to the encounter / enrolment's subjectType.

select c.id                                                             concept_id,
       c.name                                                                 concept_name,
       c.uuid                                                                 concept_uuid,
       (case when c.data_type = 'Coded' then fe.type else c.data_type end) as element_type
from form_mapping fm
         join subject_type st on fm.subject_type_id = st.id
         join form f on f.id = fm.form_id
         join form_element_group feg on feg.form_id = f.id and feg.is_voided = false
         join form_element fe on fe.form_element_group_id = feg.id and fe.is_voided = false
         join concept c on c.uuid = st.sync_registration_concept_1 and c.id = fe.concept_id
where c.id notnull
  and fm.is_voided = false
  and f.form_type = 'IndividualProfile';

Code Patch

Index: src/main/java/org/avniproject/etl/repository/SchemaMetadataRepository.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/main/java/org/avniproject/etl/repository/SchemaMetadataRepository.java b/src/main/java/org/avniproject/etl/repository/SchemaMetadataRepository.java
--- a/src/main/java/org/avniproject/etl/repository/SchemaMetadataRepository.java	(revision 19c08f25f736992280130da79e9699a188d29e17)
+++ b/src/main/java/org/avniproject/etl/repository/SchemaMetadataRepository.java	(date 1747920868537)
@@ -180,10 +180,19 @@
     }
 
     private void addSyncAttributeColumns(TableMetadata tableMetadata) {
-        List<Map<String, Object>> syncRegistrationConcept1 = runInOrgContext(() -> jdbcTemplate.queryForList(getSyncAttributeSql("sync_registration_concept_1")), jdbcTemplate);
-        List<Map<String, Object>> syncRegistrationConcept2 = runInOrgContext(() -> jdbcTemplate.queryForList(getSyncAttributeSql("sync_registration_concept_2")), jdbcTemplate);
-        addSyncAttribute(tableMetadata, syncRegistrationConcept1, Column.ColumnType.syncAttribute1);
-        addSyncAttribute(tableMetadata, syncRegistrationConcept2, Column.ColumnType.syncAttribute2);
+        String subjectTypeUuid = tableMetadata.getSubjectTypeUuid();
+        if (subjectTypeUuid == null) return;
+        
+        // Query the subject type ID from the database using the UUID
+        String sql = "SELECT id FROM subject_type WHERE uuid = ?";
+        Long subjectTypeId = runInOrgContext(() -> jdbcTemplate.queryForObject(sql, Long.class, subjectTypeUuid), jdbcTemplate);
+        
+        if (subjectTypeId != null) {
+            List<Map<String, Object>> syncRegistrationConcept1 = runInOrgContext(() -> jdbcTemplate.queryForList(getSyncAttributeSql(subjectTypeId, "sync_registration_concept_1")), jdbcTemplate);
+            List<Map<String, Object>> syncRegistrationConcept2 = runInOrgContext(() -> jdbcTemplate.queryForList(getSyncAttributeSql(subjectTypeId, "sync_registration_concept_2")), jdbcTemplate);
+            addSyncAttribute(tableMetadata, syncRegistrationConcept1, Column.ColumnType.syncAttribute1);
+            addSyncAttribute(tableMetadata, syncRegistrationConcept2, Column.ColumnType.syncAttribute2);
+        }
     }
 
     private void addSyncAttribute(TableMetadata tableMetadata, List<Map<String, Object>> syncRegistrationConcept, Column.ColumnType columnType) {
@@ -232,7 +241,7 @@
         tableMetadata.addColumnMetadata(List.of(headOfHouseholdIDColumnMetadata));
     }
 
-    private String getSyncAttributeSql(String columnName) {
+    private String getSyncAttributeSql(long subjectTypeId, String columnName) {
         return format("select c.id                                                             concept_id,\n" +
                 "       c.name                                                                 concept_name,\n" +
                 "       c.uuid                                                                 concept_uuid,\n" +
@@ -245,7 +254,8 @@
                 "         join concept c on c.uuid = st.%s and c.id = fe.concept_id\n" +
                 "where c.id notnull\n" +
                 "  and fm.is_voided = false\n" +
-                "  and f.form_type = 'IndividualProfile'", columnName);
+                "  and fm.subject_type_id = %d\n" +
+                "  and f.form_type = 'IndividualProfile'", columnName, subjectTypeId);
     }
 
     @Transactional(readOnly = true)