[keycloak-dev] need help on JPA schema changes

[Stian Thorgersen]

----- Original Message -----
> From: "Stian Thorgersen" <stian at redhat.com>
> To: "Bill Burke" <bburke at redhat.com>
> Cc: keycloak-dev at lists.jboss.org
> Sent: Thursday, 17 July, 2014 12:02:36 PM
> Subject: Re: [keycloak-dev] need help on JPA schema changes
> 
> 
> 
> ----- 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.
> 
> > * 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.
> 
> > * 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.
> 
> > 
> > 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.

Another thing I just thought about if UserEntity.realm is a RealmEntity you actually have to go and fetch the RealmEntity to create or update a user.

> 
> > 
> > 
> > --
> > Bill Burke
> > JBoss, a division of Red Hat
> > http://bill.burkecentral.com
> > _______________________________________________
> > keycloak-dev mailing list
> > keycloak-dev at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/keycloak-dev
> > 
> _______________________________________________
> keycloak-dev mailing list
> keycloak-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-dev
>