Re: [keycloak-dev] need help on JPA schema changes

From: "Bill Burke" <bburke(a)redhat.com&gt; To: "Stian Thorgersen" <stian(a)redhat.com&gt; Cc: keycloak-dev(a)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(a)redhat.com&gt; >> To: keycloak-dev(a)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.

>> * 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