On 17.7.2014 01:42, Bill Burke wrote: > 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 > * 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 > * 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 wonder that something like AuthenticationLinkEntity (even if named differently) will be still needed? For users synced from LDAP, you may want to keep the link to LDAP to particular user?

> > 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. I am not sure about. Just want to point the usecase with migration between versions. For example if Realm change between Keycloak 1.0 and Keycloak 1.1, people may want to migrate their database (hence they export realmModel to JSON, transform it to 1.1 and import it again). And if user model won't change between versions, people won't need to export/import and delete all users as it's quite overhead to do it if they have like 20 million users in DB. Hence it might be useful if it's possible to delete just realm model stuff, but still keep all users in DB. Will it be possible with these constraints?

Also doesn't similar problem exists for user sessions as well? I mean that with bulk deleting of users, it seems that UserSessions for particular user are not deleted? Probably not a big issue as there is periodic cleanup of sessions...

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

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

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