Goal
When running integration tests, I would like to populate the database with preset data using GORM.
Problem
For certain domains, native SQL queries can't see data inserted by GORM and vice versa (GORM queries can't see data inserted by native SQL). Within a single run, one domain will have this issue and another won't.
Description
Please see the console output near the bottom of this post for a clear description. I think clues to the solution can be seen in the output.
Project: Grails 2.4.2 Datasource:
test {
cehCode = "PR"
schemaName = "AF"
parallelSchemaName = "AF"
dataSource {
dbCreate = "create-drop"
url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS AF"
driverClassName = "org.h2.Driver"
properties {
initialSize = 2
minIdle = 1
maxIdle = 2
maxActive = 2
maxWait = 2000
maxAge = 60000
minEvictableIdleTimeMillis=30000
timeBetweenEvictionRunsMillis=30000
abandonWhenPercentageFull = 50
numTestsPerEvictionRun=3
testOnBorrow=true
testWhileIdle=true
testOnReturn=true
validationQuery="SELECT 1"
validationInterval=500
//defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_UNCOMMITTED
}
}
}
Integration test:
void "Test CUST filter"() {
when: 'Build test data'
then:
assert testQueryService.testInsert() == "Done with inserts"
assert testQueryService.testQuery() == "Done with queries"
}
Service:
package com.lrs.accrual.common
import com.lrs.contract.TFastCodes
import grails.transaction.Transactional
import groovy.sql.Sql
import com.lrs.accrual.IntermodalRatingAuth
@Transactional
class TestQueryService {
def sessionFactory
def testInsert() {
println "----------------------START EXECUTING INSERTS ON TABLES------------------------------"
println "********* START NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********"
def nativeInsert = """INSERT INTO AF.TFAST_CODES (APPL_ELE,APPL_CD_VAL,APPL_CD_DESC,CAS_OWNER,ACS_TYPE_CD)
VALUES ('NATIVE SQL2','1','ORPT-TEST PR','ZZZZ','BU')"""
def sql = new Sql(sessionFactory.getCurrentSession().connection())
sql.executeInsert(nativeInsert)
sql.commit()
TFastCodes codes = new TFastCodes(applicationElement: 'GORM new 3', casOwner: 'ZZZZ', applicationCodeDescription: 'JUNK2', applicationCodeValue: '1', accessTypeCode: 'BU', newColumn2: 'Val')
println "Valid TFastCodes domain? " + codes.validate()
codes.save(flush: true)
println "********* END NEW TABLE/DOMAIN *********"
println "********* START OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********"
def nativeInsert2 = """INSERT INTO AF.TIMDL_RT_AUTH (AGREEMENT_NUMB,MRKT_RATE_KEY,WB_RT_AUTH_REF)
VALUES ('NATIVE',1,'2016-01-01-01.01.01.000000')"""
def sql2 = new Sql(sessionFactory.getCurrentSession().connection())
sql2.executeInsert(nativeInsert2)
sql2.commit()
IntermodalRatingAuth auth= new IntermodalRatingAuth(agreementNumber:"GORM", marketingRateKey:1, waybillRateAuthRef: "other string")
println "Valid IntermodalRatingAuth domain? " + auth.validate()
auth.save(flush: true)
println "********* END OLD TABLE/DOMAIN *********"
println "----------------------END EXECUTING INSERTS ON TABLES------------------------------"
return "Done with inserts"
}
def testQuery() {
println "----------------------START EXECUTING QUERIES ON TABLES------------------------------"
println "********* START QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********"
def codes = TFastCodes.list()
codes?.eachWithIndex { val, i -> println "Gorm TFastCodes result $i: " + val.properties }
println "Native TFAST_CODES query result: " + sessionFactory.getCurrentSession()?.createSQLQuery("SELECT * FROM AF.TFAST_CODES").list()
println "********* END QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********"
println "********* START QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********"
def auths = IntermodalRatingAuth.list()
auths?.eachWithIndex { val, i -> println "Gorm IntermodalRatingAuth result $i: " + val.properties }
println "Native TIMDL_RT_AUTH query result: " + sessionFactory.getCurrentSession()?.createSQLQuery("SELECT * FROM AF.TIMDL_RT_AUTH").list()
println "********* END QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********"
println "----------------------END EXECUTING QUERIES ON TABLES------------------------------"
return "Done with queries"
}
}
Domain created from scratch (works):
class TFastCodes implements Serializable {
String applicationElement
String applicationCodeValue
String applicationCodeDescription
String casOwner
String accessTypeCode
static mapping = {
table 'AF.TFAST_CODES'
version false
id composite: ['applicationElement', 'applicationCodeValue']
applicationElement column:'APPL_ELE'
applicationCodeValue column:'APPL_CD_VAL'
applicationCodeDescription column:'APPL_CD_DESC'
casOwner column:'CAS_OWNER'
accessTypeCode column:'ACS_TYPE_CD'
}
static constraints = { applicationElement( blank:false)
applicationCodeValue( blank:false)}
}
Existing domain (does not work):
class IntermodalRatingAuth {
String agreementNumber=LRAccrualConstants.STRING_EMPTY
Integer marketingRateKey= LRAccrualConstants.ZERO
String waybillRateAuthRef
static mapping = {
table 'AF.TIMDL_RT_AUTH'
version false
agreementNumber column:'AGREEMENT_NUMB'
marketingRateKey column:'MRKT_RATE_KEY'
waybillRateAuthRef column:'WB_RT_AUTH_REF'
}
// Read-only. No constraints needed.
}
Console Output:
----------------------START EXECUTING INSERTS ON TABLES------------------------------
********* START NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********
Valid TFastCodes domain? true
Hibernate: select tfastcodes_.APPL_ELE, tfastcodes_.APPL_CD_VAL, tfastcodes_.ACS_TYPE_CD as ACS_TYPE3_37_, tfastcodes_.APPL_CD_DESC as APPL_CD_4_37_, tfastcodes_.CAS_OWNER as CAS_OWNE5_37_ from AF.TFAST_CODES tfastcodes_ where tfastcodes_.APPL_ELE=? and tfastcodes_.APPL_CD_VAL=?
Hibernate: insert into AF.TFAST_CODES (ACS_TYPE_CD, APPL_CD_DESC, CAS_OWNER, APPL_ELE, APPL_CD_VAL) values (?, ?, ?, ?, ?)
********* END NEW TABLE/DOMAIN *********
********* START OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********
Valid IntermodalRatingAuth domain? true
********* END OLD TABLE/DOMAIN *********
----------------------END EXECUTING INSERTS ON TABLES------------------------------
----------------------START EXECUTING QUERIES ON TABLES------------------------------
********* START QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********
Hibernate: select this_.APPL_ELE as APPL_ELE1_37_0_, this_.APPL_CD_VAL as APPL_CD_2_37_0_, this_.ACS_TYPE_CD as ACS_TYPE3_37_0_, this_.APPL_CD_DESC as APPL_CD_4_37_0_, this_.CAS_OWNER as CAS_OWNE5_37_0_ from AF.TFAST_CODES this_
Gorm TFastCodes result 0: [applicationCodeDescription:ORPT-TEST PR, applicationCodeValue:1, accessTypeCode:BU, applicationElement:NATIVE SQL2, casOwner:ZZZZ]
Gorm TFastCodes result 1: [applicationCodeValue:1, accessTypeCode:BU, applicationElement:GORM new 3, casOwner:ZZZZ, applicationCodeDescription:JUNK2]
Hibernate: SELECT * FROM AF.TFAST_CODES
Native TFAST_CODES query result: [[NATIVE SQL2, 1, BU, ORPT-TEST PR, ZZZZ], [GORM new 3, 1, BU, JUNK2, ZZZZ]]
********* END QUERIES AGAINST NEW TABLE/DOMAIN TFAST_CODES/TFastCodes *********
********* START QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********
Gorm IntermodalRatingAuth result 0: [marketingRateKey:1, agreementNumber:GORM, waybillRateAuthRef:other string]
Hibernate: SELECT * FROM AF.TIMDL_RT_AUTH
Native TIMDL_RT_AUTH query result: [[1, NATIVE, 1, 2016-01-01-01.01.01.000000]]
********* END QUERIES AGAINST OLD TABLE/DOMAIN TIMDL_RT_AUTH/IntermodalRatingAuth *********
----------------------END EXECUTING QUERIES ON TABLES------------------------------
Additional details
In my Grails project, I have setup an in-memory DB for the test environment. I would like to save a set of test data fresh every time when running specific integration tests.
When saving the data using GORM (domain.save()), the GORM queries in the service are able to find the data just fine, but are not able to see the data inserted by the native SQL inserts. The sessionFactory native SQL queries, on the other hand, are not able to see the GORM data inserted, but are able to see the data inserted by the native SQL.
As you can see below, I do flush:true and have tried every command I know to commit the data run. Most of the domains I have tested with are existing domains and it never works for them right off. However, when I create new domains from scratch (using Grails "create domain" command), it often works. It seems to me that the issue is somewhere deeper in the Grails or Hibernate architecture. The code examples below are simplified, but I have a service that demonstrates an old domain and new domain side by side:
If it works, it works for both GORM and native. If it doesn't work, neither sees the data inserted by the other.
Debugging steps I have taken:
Sergio/droggo,
Thank you for your help! I think I tracked it down. As is often the case, it is difficult to know exactly what code to share to allow others to help troubleshoot. It seems that the native SQL was hitting the datasource config, but the GORM/domains were mocked so were not persisted to the datasource. I did not realize this, because I am using a Grails plugin called build-test-data whose @Build annotation is a wrapper for the @Mock annotation (obviously I was not aware of this). @Build is meant for unit tests only, not integration tests. For anyone to be able to help me, they would have needed to see the integration test's annotations (below). Sorry for the missing source code!
@TestFor(TestQueryService)
@Build([ContractFilter, ContractHeader, Qualifier, Provision, TFastCodes])
class ContractFilterServiceSpec extends Specification {