Trouble with legacy DB and composite keys

[Brian Riley]

This is my first attempt at trying to tie a Grails app to a legacy Oracle DB and I'm having a lot of trouble. To top it off the tables are both transactional meaning that to simply get the current record for each domain I had to build views (because it required things like: 'table_active = 'Y' and table_field IS NOT NULL'

I have 2 domains:

class Applicant {
    long pid
    String firstName
    ...
	
    static hasMany = [addresses: Address]
	
    static constraints = {
	....
        addresses	nullable: true
    }
	
    static mapping = {
        table		"LEGACY_APPLICANT_VW"	
	cache		'read-only'
		
	id			column: 'table_pid', name: 'pid'
	version		false
		
	columns{
             pid               column: 'table_pid'
             firstName      column: 'table_first_nam' 
             ...
		
	}
		
    }
	
}

class Address implements Serializable {
    long pid
    String type
	
    String line1
    ...
	
    static constraints = {
         pid		nullable: false
	 type		blank: false
	 line1		blank: false
	 ...
    }
	
    static mapping = {
         table		"LEGACY_ADDRESS_VW"
	 cache	'read-only'
		
	 id		composite: ['pid', 'type']
	 version	false
		
	 columns{
	     pid	column: 'table_pid'
	     type	column: 'table_atyp_code'
	     line1	column: 'table_street_line1'
	     ...
	}
}

My app compiles but when I try to access Applicant?.addresses I get the following GSP error:

Error 500: Error processing GroovyPageView: could not initialize a collection: [my.domain.Applicant.addresses#321303]
Servlet: grails
URI: /Project/grails/applicant/worklist.dispatch
Exception Message: ORA-00972: identifier is too long

This leads me to believe that GORM is trying to create something like LEGACY_APPLICANT_VW_LEGACY_ADDRESS_VW as the join and Oracle is throwing an exception because it only allows 30 characters.

So rather than letting GORM create its own join table I created a new Oracle View and gave it applicant_pid, pid, and type columns to represent the 3 key fields. I then added the following to my mapping on the Applicant class:

addresses	  joinTable: [table: 'LEGACY_JOIN_VW', key: 'pid', column: 'applicant_pid']

When I rerun I get the following error while it compiles:

2011-07-28 14:58:46,580 [main] ERROR context.GrailsContextLoader - Error executing bootstraps: Error creating bean with name 'messageSource': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is org.hibernate.MappingException: Foreign key (FK74BD0D89024004:LEGACY_APPLICANT_VW_LEGACY_ADDRE SS_VW [pid,address_pid,address_type])) must have same number of columns as the referenced primary key (LEGACY_ADDRESS_VW [table_pid,table_atyp_code])
org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'messageSource': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is org.hibernate.MappingException: Foreign key (FK74BD0D89024004:LEGACY_APPLICANT_VW_LEGACY_ADDRE SS_VW [pid,address_pid,address_type])) must have same number of columns as the referenced primary key (LEGACY_ADDRESS_VW [table_pid,table_atyp_code])

What am I doing wrong? Any help is greatly appreciated!

[Brian Riley]

Ok, so I made what I think is a little progress. I added a new View to my Oracle DB called APPLICANT_ADDRESSES_VW with the columns applicant_addresses_pid, address_pid, address_type. I then changed the mapping section of the Applicant class and added:

addresses	joinTable: [name: 'APPLICANT_ADDRESSES_VW', key: 'applicant_addresses_id', column: 'address_pid']

So now I have:

    LEGACY_APPLICANT_VW  with table_pid as the PKey
    LEGACY_ADDRESS_VW with table_pid and table_atyp_code as the composite PKey

    APPLICANT_ADDRESSES_VW with the columns applicant_addresses_id, address_pid, address_type

    class Applicant {
        static hasMany = [addresses: Address]
        long pid

        static mapping = {
            table    'LEGACY_APPLICANT_VW'
            id        column: 'table_pid', name: 'pid'

            joinTable: [name: 'APPLICANT_ADDRESSES_VW', key: 'applicant_addresses_id', column: 'address_id']
    
            columns{
                 pid    column: 'table_pid'
            }
        }
    }

    class Address{
        long pid
        String type

        static mapping = {
            table    'LEGACY_ADDRESS_VW'
            id        composite: 'pid', 'type'

            columns{
                pid    column: 'table_pid'
                type  column: 'table_atyp_code'
        }
     }

Note that the pid column in the Address table is the foreign key to the Applicant table but it needs to be combined with the type column to give the address a unique id.

I no longer get an error but when I try to access the applicant's addresses I get an empty Set. What am I doing wrong!?

[pledbrook]

I think you need to turn Hibernate logging on to discover what queries are been sent to your database. In DataSource.groovy:

dataSource {
    logSql = true
    ...
}

That should give you an indication why you're not getting any Address instances back.

[Brian Riley]

Thanks Pledbrook. I'll give that a try. I've switched over to using hbm.xml files but turning the debug on should greatly help with getting everything up and running.