[keycloak-dev] need help on JPA schema changes

[Stian Thorgersen]

----- Original Message -----
> From: "Bill Burke" <bburke at redhat.com>
> To: "Stian Thorgersen" <stian at redhat.com>
> Cc: keycloak-dev at lists.jboss.org
> Sent: Thursday, 17 July, 2014 2:21:19 PM
> Subject: Re: [keycloak-dev] need help on JPA schema changes
> 
> 
> 
> On 7/17/2014 7:02 AM, Stian Thorgersen wrote:
> >
> >
> > ----- Original Message -----
> >> From: "Bill Burke" <bburke at redhat.com>
> >> To: keycloak-dev at lists.jboss.org
> >> Sent: Thursday, 17 July, 2014 12:42:28 AM
> >> Subject: [keycloak-dev] need help on JPA schema changes
> >>
> >> I refactored some things for both performance and for bulk delete of
> >> users.
> >>
> >> * use long @Id for all non-exposed entities, i.e. UserRoleMappingEntity,
> >> etc.  This is more efficient than strings
> >
> > I replaced all the long ids with composite ids (for example
> > UserRoleMappingEntity id is now userId + roleId). BTW we stopped using
> > generated long ids as they didn't work for all databases, but that's not a
> > problem any more as there's no generated ids at all now.
> >
> 
> Awesome!  I tried to do that, but I couldn't figure out the JPA magic to
> do it and was getting constraint errors in some tests.

I banged my head against the wall quite hard before I figure it out! Why don't we just move the JDBC ;)

> 
> >> * Ditched all @ElementCollections from UserEntity.  Bulk delete does not
> >> cascade and there is no way to bulk delete @ElementCollections from
> >> JPAQL!  This caused me to add UserAttributeEntity and
> >> UserRequiredActionEntity
> >
> > I hate ElementCollections, it's never worked properly! Much better to do
> > what you've done now so you get more control.
> >
> 
> will have to do the same for Realm.
> 
> >> * Added @ManyToOne UserEntity relationship to AuthenticationLinkEntity
> >> so that it can be bulk deleted.
> >>
> >> Questions:
> >>
> >> I'm not sure about moving things like social links to UserAttributes.
> >> SocialLinkEntity, UserRoleMappingEntity, UserRequiredActionEntity are
> >> all in separate tables.  Except for AuthenticationLinkEntity, which I
> >> hope to deal away with on the AuthenticationProvider refactor, I think
> >> UserEntity is fine the way it is.
> >
> > I can see two reasons to removing this. First using attributes instead
> > means we should be less likely to require schema changes in the future.
> > Secondly it will reduce the number of joins (and/or separate selects) to
> > retrieve a user. I think it's worth doing some performance eval with
> > regards to the latter.
> >
> 
> Not sure I agree that schema change is a problem, but the joins make sense.
> 
> >>
> >> Are we *ABSOLUTELY* sure we want to remove secondary key constraints
> >> from UserRoleMappingEntity and UserEntity for role and realm
> >> respectively?  These constraints caught a lot of problems for me that I
> >> wouldn't have found otherwise.  I just don't think we'd ever store users
> >> and realms in different stores.
> >
> > My vote is yes, but I don't feel to strongly about it (at least not atm).
> > With regards to constraints (and being able to delete users for a realm
> > when a realm is deleted, etc.) that is something we need to make to work
> > in either case if users are to be able to implement their own
> > UserProviders. In the future I think there will be cases where someone
> > will want to store realms and users in separate databases. For example for
> > a really big deployment realms would go into a single replicated db, while
> > for users you may want to shard as well.
> >
> 
> Marek already made the export--reimport point.  Can't remove/re-add
> realm/role rows if there are constraints.
> 
> --
> Bill Burke
> JBoss, a division of Red Hat
> http://bill.burkecentral.com
>