Composite foreign key

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Composite foreign key

Markus Hauser
Composite foreign key

Hi everybody,

I am working with a legacy database with composite primary keys. This feature is supported by grails, also renaming the PK columns.

But, as far as I understand, grails does not support renaming columns of a composite foreign key.


Example:
class Author implements Serializable {
        String surname
        String firstname
        static mapping = {
                id composite: ['surname', 'firstname']
        }
}

class Book  {
    String title
    static belongsTo = [author: Author]
    static mapping = {
      columns {
        author column: ['fk_surname', 'fk_name']
      }
   }
}

This example results in an error inside the class Book: Cannot treat multi-column property as a single-column property

How would you solve this problem without redesignig the database?
Is there a workaround?

Thanks for your supprt.

Regards
Markus


Markus
Reply | Threaded
Open this post in threaded view
|

Re: Composite foreign key

Burt Beckwith-2
The only way I know (until the JIRA issue is fixed) is with a custom Configuration subclass:

package com.mycompany.myapp;

import java.util.Collection;

import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration;
import org.hibernate.MappingException;
import org.hibernate.mapping.Column;
import org.hibernate.mapping.ForeignKey;
import org.hibernate.mapping.PersistentClass;
import org.hibernate.mapping.RootClass;

public class MyConfiguration extends GrailsAnnotationConfiguration {

   private static final long serialVersionUID = 1;

   private boolean _alreadyProcessed;

   @SuppressWarnings("unchecked")
   @Override
   protected void secondPassCompile() throws MappingException {
      super.secondPassCompile();

      if (_alreadyProcessed) {
         return;
      }

      for (PersistentClass pc : (Collection<PersistentClass>)classes.values()) {
         if (pc instanceof RootClass) {
            RootClass root = (RootClass)pc;
            if (root.getEntityName().equals("com.mycompany.myapp.Book")) {
               ForeignKey fk = (ForeignKey)root.getTable().getForeignKeyIterator().next();
               for (int i = 0; i < fk.getColumnSpan(); i++) {
                  Column column = fk.getColumn(i);
                  if (column.getName().equals("author_surname")) {
                     column.setName("fk_surname");
                  }
                  else if (column.getName().equals("author_firstname")) {
                     column.setName("fk_name");
                  }
                  else {
                     // ?
                  }
               }
            }
         }
      }

      _alreadyProcessed = true;
   }
}

This has to be a Java class, and you register it in DataSource.groovy with the configClass attribute:

dataSource {
   pooled = true
   driverClassName = ...
   username = ...
   ...
   configClass = com.mycompany.myapp.MyConfiguration
}

It's a quick example and has a hard-coded assumption that there's just one foreign key, but that'd easy enough to inspect and find the correct one if there are multiple.

Burt

On Wed, Nov 17, 2010 at 4:28 AM, Markus Hauser (GVP mbH) <[hidden email]> wrote:

Hi everybody,

I am working with a legacy database with composite primary keys. This feature is supported by grails, also renaming the PK columns.

But, as far as I understand, grails does not support renaming columns of a composite foreign key.


Example:
class Author implements Serializable {
        String surname
        String firstname
        static mapping = {
                id composite: ['surname', 'firstname']
        }
}

class Book  {
    String title
    static belongsTo = [author: Author]
    static mapping = {
      columns {
        author column: ['fk_surname', 'fk_name']
      }
   }
}

This example results in an error inside the class Book: Cannot treat multi-column property as a single-column property

How would you solve this problem without redesignig the database?
Is there a workaround?

Thanks for your supprt.

Regards
Markus



Reply | Threaded
Open this post in threaded view
|

Re: Composite foreign key

::SammyRulez::
this means that if the column names are the same on the two tables is
works out-of the box?

2010/11/17 Burt Beckwith <[hidden email]>:

> The only way I know (until the JIRA issue is fixed) is with a custom
> Configuration subclass:
> package com.mycompany.myapp;
> import java.util.Collection;
> import
> org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration;
> import org.hibernate.MappingException;
> import org.hibernate.mapping.Column;
> import org.hibernate.mapping.ForeignKey;
> import org.hibernate.mapping.PersistentClass;
> import org.hibernate.mapping.RootClass;
> public class MyConfiguration extends GrailsAnnotationConfiguration {
>    private static final long serialVersionUID = 1;
>    private boolean _alreadyProcessed;
>    @SuppressWarnings("unchecked")
>    @Override
>    protected void secondPassCompile() throws MappingException {
>       super.secondPassCompile();
>       if (_alreadyProcessed) {
>          return;
>       }
>       for (PersistentClass pc :
> (Collection<PersistentClass>)classes.values()) {
>          if (pc instanceof RootClass) {
>             RootClass root = (RootClass)pc;
>             if (root.getEntityName().equals("com.mycompany.myapp.Book")) {
>                ForeignKey fk =
> (ForeignKey)root.getTable().getForeignKeyIterator().next();
>                for (int i = 0; i < fk.getColumnSpan(); i++) {
>                   Column column = fk.getColumn(i);
>                   if (column.getName().equals("author_surname")) {
>                      column.setName("fk_surname");
>                   }
>                   else if (column.getName().equals("author_firstname")) {
>                      column.setName("fk_name");
>                   }
>                   else {
>                      // ?
>                   }
>                }
>             }
>          }
>       }
>       _alreadyProcessed = true;
>    }
> }
> This has to be a Java class, and you register it in DataSource.groovy with
> the configClass attribute:
> dataSource {
>    pooled = true
>    driverClassName = ...
>    username = ...
>    ...
>    configClass = com.mycompany.myapp.MyConfiguration
> }
> It's a quick example and has a hard-coded assumption that there's just one
> foreign key, but that'd easy enough to inspect and find the correct one if
> there are multiple.
> Burt
>
> On Wed, Nov 17, 2010 at 4:28 AM, Markus Hauser (GVP mbH)
> <[hidden email]> wrote:
>>
>> Hi everybody,
>>
>> I am working with a legacy database with composite primary keys. This
>> feature is supported by grails, also renaming the PK columns.
>>
>> But, as far as I understand, grails does not support renaming columns of a
>> composite foreign key.
>>
>> Example:
>> class Author implements Serializable {
>>         String surname
>>         String firstname
>>         static mapping = {
>>                 id composite: ['surname', 'firstname']
>>         }
>> }
>>
>> class Book  {
>>     String title
>>     static belongsTo = [author: Author]
>>     static mapping = {
>>       columns {
>>         author column: ['fk_surname', 'fk_name']
>>       }
>>    }
>> }
>>
>> This example results in an error inside the class Book: Cannot treat
>> multi-column property as a single-column property
>>
>> How would you solve this problem without redesignig the database?
>> Is there a workaround?
>>
>> Thanks for your supprt.
>>
>> Regards
>> Markus
>
>



--
SammyRulez
http://www.sammyrulez.com
http://twitter.com/sammyrulez
http://www.linkedin.com/in/sammyrulez

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Composite foreign key

Markus Hauser
Yes, that's right.
Markus
-------- Original-Nachricht --------
> Datum: Wed, 17 Nov 2010 12:04:49 +0100
> Von: "::SammyRulez::" <[hidden email]>
> An: [hidden email]
> Betreff: Re: [grails-user] Composite foreign key

> this means that if the column names are the same on the two tables is
> works out-of the box?
>
> 2010/11/17 Burt Beckwith <[hidden email]>:
> > The only way I know (until the JIRA issue is fixed) is with a custom
> > Configuration subclass:
> > package com.mycompany.myapp;
> > import java.util.Collection;
> > import
> >
> org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration;
> > import org.hibernate.MappingException;
> > import org.hibernate.mapping.Column;
> > import org.hibernate.mapping.ForeignKey;
> > import org.hibernate.mapping.PersistentClass;
> > import org.hibernate.mapping.RootClass;
> > public class MyConfiguration extends GrailsAnnotationConfiguration {
> >    private static final long serialVersionUID = 1;
> >    private boolean _alreadyProcessed;
> >    @SuppressWarnings("unchecked")
> >    @Override
> >    protected void secondPassCompile() throws MappingException {
> >       super.secondPassCompile();
> >       if (_alreadyProcessed) {
> >          return;
> >       }
> >       for (PersistentClass pc :
> > (Collection<PersistentClass>)classes.values()) {
> >          if (pc instanceof RootClass) {
> >             RootClass root = (RootClass)pc;
> >             if
> (root.getEntityName().equals("com.mycompany.myapp.Book")) {
> >                ForeignKey fk =
> > (ForeignKey)root.getTable().getForeignKeyIterator().next();
> >                for (int i = 0; i < fk.getColumnSpan(); i++) {
> >                   Column column = fk.getColumn(i);
> >                   if
> (column.getName().equals("author_surname")) {
> >                      column.setName("fk_surname");
> >                   }
> >                   else if
> (column.getName().equals("author_firstname")) {
> >                      column.setName("fk_name");
> >                   }
> >                   else {
> >                      // ?
> >                   }
> >                }
> >             }
> >          }
> >       }
> >       _alreadyProcessed = true;
> >    }
> > }
> > This has to be a Java class, and you register it in DataSource.groovy
> with
> > the configClass attribute:
> > dataSource {
> >    pooled = true
> >    driverClassName = ...
> >    username = ...
> >    ...
> >    configClass = com.mycompany.myapp.MyConfiguration
> > }
> > It's a quick example and has a hard-coded assumption that there's just
> one
> > foreign key, but that'd easy enough to inspect and find the correct one
> if
> > there are multiple.
> > Burt
> >
> > On Wed, Nov 17, 2010 at 4:28 AM, Markus Hauser (GVP mbH)
> > <[hidden email]> wrote:
> >>
> >> Hi everybody,
> >>
> >> I am working with a legacy database with composite primary keys. This
> >> feature is supported by grails, also renaming the PK columns.
> >>
> >> But, as far as I understand, grails does not support renaming columns
> of a
> >> composite foreign key.
> >>
> >> Example:
> >> class Author implements Serializable {
> >>         String surname
> >>         String firstname
> >>         static mapping = {
> >>                 id composite: ['surname', 'firstname']
> >>         }
> >> }
> >>
> >> class Book  {
> >>     String title
> >>     static belongsTo = [author: Author]
> >>     static mapping = {
> >>       columns {
> >>         author column: ['fk_surname', 'fk_name']
> >>       }
> >>    }
> >> }
> >>
> >> This example results in an error inside the class Book: Cannot treat
> >> multi-column property as a single-column property
> >>
> >> How would you solve this problem without redesignig the database?
> >> Is there a workaround?
> >>
> >> Thanks for your supprt.
> >>
> >> Regards
> >> Markus
> >
> >
>
>
>
> --
> SammyRulez
> http://www.sammyrulez.com
> http://twitter.com/sammyrulez
> http://www.linkedin.com/in/sammyrulez
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>     http://xircles.codehaus.org/manage_email
>
>

--
Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!  
Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Markus
Reply | Threaded
Open this post in threaded view
|

Re: Composite foreign key

Lauro Becker
Hi,

I'd like to show you a simple workaround I've developed based on Burt's example.
It is in Portuguese, but it's easy to read through the sample code: http://laurobecker.wordpress.com/2011/04/01/grails-personalizando-nomes-de-chaves-estrangeiras/

Hope this helps.

Lauro L. V. Becker
---------------------------
Blog: http://laurobecker.wordpress.com


2010/11/17 Markus Hauser <[hidden email]>
Yes, that's right.
Markus
-------- Original-Nachricht --------
> Datum: Wed, 17 Nov 2010 12:04:49 +0100
> Von: "::SammyRulez::" <[hidden email]>
> An: [hidden email]
> Betreff: Re: [grails-user] Composite foreign key

> this means that if the column names are the same on the two tables is
> works out-of the box?
>
> 2010/11/17 Burt Beckwith <[hidden email]>:
> > The only way I know (until the JIRA issue is fixed) is with a custom
> > Configuration subclass:
> > package com.mycompany.myapp;
> > import java.util.Collection;
> > import
> >
> org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration;
> > import org.hibernate.MappingException;
> > import org.hibernate.mapping.Column;
> > import org.hibernate.mapping.ForeignKey;
> > import org.hibernate.mapping.PersistentClass;
> > import org.hibernate.mapping.RootClass;
> > public class MyConfiguration extends GrailsAnnotationConfiguration {
> >    private static final long serialVersionUID = 1;
> >    private boolean _alreadyProcessed;
> >    @SuppressWarnings("unchecked")
> >    @Override
> >    protected void secondPassCompile() throws MappingException {
> >       super.secondPassCompile();
> >       if (_alreadyProcessed) {
> >          return;
> >       }
> >       for (PersistentClass pc :
> > (Collection<PersistentClass>)classes.values()) {
> >          if (pc instanceof RootClass) {
> >             RootClass root = (RootClass)pc;
> >             if
> (root.getEntityName().equals("com.mycompany.myapp.Book")) {
> >                ForeignKey fk =
> > (ForeignKey)root.getTable().getForeignKeyIterator().next();
> >                for (int i = 0; i < fk.getColumnSpan(); i++) {
> >                   Column column = fk.getColumn(i);
> >                   if
> (column.getName().equals("author_surname")) {
> >                      column.setName("fk_surname");
> >                   }
> >                   else if
> (column.getName().equals("author_firstname")) {
> >                      column.setName("fk_name");
> >                   }
> >                   else {
> >                      // ?
> >                   }
> >                }
> >             }
> >          }
> >       }
> >       _alreadyProcessed = true;
> >    }
> > }
> > This has to be a Java class, and you register it in DataSource.groovy
> with
> > the configClass attribute:
> > dataSource {
> >    pooled = true
> >    driverClassName = ...
> >    username = ...
> >    ...
> >    configClass = com.mycompany.myapp.MyConfiguration
> > }
> > It's a quick example and has a hard-coded assumption that there's just
> one
> > foreign key, but that'd easy enough to inspect and find the correct one
> if
> > there are multiple.
> > Burt
> >
> > On Wed, Nov 17, 2010 at 4:28 AM, Markus Hauser (GVP mbH)
> > <[hidden email]> wrote:
> >>
> >> Hi everybody,
> >>
> >> I am working with a legacy database with composite primary keys. This
> >> feature is supported by grails, also renaming the PK columns.
> >>
> >> But, as far as I understand, grails does not support renaming columns
> of a
> >> composite foreign key.
> >>
> >> Example:
> >> class Author implements Serializable {
> >>         String surname
> >>         String firstname
> >>         static mapping = {
> >>                 id composite: ['surname', 'firstname']
> >>         }
> >> }
> >>
> >> class Book  {
> >>     String title
> >>     static belongsTo = [author: Author]
> >>     static mapping = {
> >>       columns {
> >>         author column: ['fk_surname', 'fk_name']
> >>       }
> >>    }
> >> }
> >>
> >> This example results in an error inside the class Book: Cannot treat
> >> multi-column property as a single-column property
> >>
> >> How would you solve this problem without redesignig the database?
> >> Is there a workaround?
> >>
> >> Thanks for your supprt.
> >>
> >> Regards
> >> Markus
> >
> >
>
>
>
> --
> SammyRulez
> http://www.sammyrulez.com
> http://twitter.com/sammyrulez
> http://www.linkedin.com/in/sammyrulez
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>     http://xircles.codehaus.org/manage_email
>
>

--
Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email