Announcement Announcement Module
Collapse
No announcement yet.
Trouble with legacy DB and composite keys Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Trouble with legacy DB and composite keys

    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:
    Code:
    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:
    Code:
    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!

  • #2
    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:

    Code:
    addresses	joinTable: [name: 'APPLICANT_ADDRESSES_VW', key: 'applicant_addresses_id', column: 'address_pid']
    So now I have:
    Code:
        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!?

    Comment


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

      Code:
      dataSource {
          logSql = true
          ...
      }
      That should give you an indication why you're not getting any Address instances back.

      Comment


      • #4
        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.

        Comment

        Working...
        X