[keycloak-dev] need help on JPA schema changes

[Bill Burke]

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.

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