Grouper Users - Open Discussion List

[Reid Watson <>]

Hi Chris 

2.4 would be great, we are looking at upgrading from 2.3 next week. Also our 
dev’s made the following comments  

"Ran query using the view above. It does not quite meet our requirements. 
Looked at other views and found grouper_memberships_v which does give the 
"correct" result, namely, the same as per the grouper UI."

Will you base the WS call on “grouper_memberships_lw_v” or 
“grouper_memberships_v” view ? 

Cheers

Reid 


> On 23/10/2018, at 8:04 AM, Hyzer, Chris 
> <>
>  wrote:
> 
> Ive been thinking about this... It would be:
>  
> 1. This could either be a new operation or it could just use 
> getMemberships() (existing, with additional options to identify as 
> lightweight) but the result isn’t quite as full featured, ie. It wouldn’t 
> say if direct or indirect and it wouldn’t have the full group objects etc
> 2. If you have inherited READ (or ADMIN), on a folder, and request groups 
> in that folder, then your request will be even faster.  Or if you check 
> only one group.  In your case, if you are getting memberships for the 
> subject, if the caller is an admin, its fast, otherwise needs to join to 
> security tables.
> 3. The fields would be the same as grouper_memberships_lw_v:  SUBJECT_ID, 
> SUBJECT_SOURCE, GROUP_NAME, LIST_NAME, LIST_TYPE, GROUP_ID, MEMBER_ID.  
> Note, could also add SUBJECT_IDENTIFIER, SUBJECT_NAME, SUBJECT_DESCRIPTION 
> probably (i.e. stuff in grouper_members table)
> 4. We could have intelligent paging with a large limit on max (so that 
> clients could reliably page through results)
> 5. Could cache results or have an option to not cache
> 6. Would not return other info or objects
>  
> If we can do this as a low risk patch in 2.4 would you use it in 2.4?  😊  
> or would you want for 2.5.  We have had other requests for something like 
> this, so im interested in it...
>  
> Thanks
> Chris
>  
>  
>  
> -----Original Message-----
> From: Reid Watson 
> <>
>  
> Sent: Sunday, October 21, 2018 4:22 PM
> To: Hyzer, Chris 
> <>
> Cc: 
> 
> Subject: Re: [FORGED] [grouper-users] Grouper-WS-2.3 - GetMemberships — 
> “PageSize and PageNumber"
>  
> What would your time expectations be to deliver a lightweight WS version, 
> would the solution support the pervious versions or just 2.4 ?
>  
>  
> > On 19/10/2018, at 2:43 AM, Hyzer, Chris 
> > <>
> >  wrote:
> > 
> > We use that view a lot at penn, might be something you could use.  Or we 
> > could make a lightweight WS based on this view that doesn’t resolve all 
> > those other things to make it a lot fast and more suitable for large 
> > queries... thoughts?
> > 
> > -----Original Message-----
> > From: 
> > 
> >  
> > <>
> >  On Behalf Of Reid Watson
> > Sent: Monday, October 15, 2018 7:52 PM
> > To: 
> > 
> > Subject: Re: [FORGED] [grouper-users] Grouper-WS-2.3 - GetMemberships — 
> > “PageSize and PageNumber"
> > 
> > Today we looked at the following view “grouper_memberships_lw_v — This 
> > view contains all memberships (direct, indirect, groups, people, etc).” 
> > and it seems this view returns the data required within milliseconds 
> > compared to seconds, correct me if Im wrong but this view is used for 
> > large list of data and potentially we should integrate the query we 
> > require into Grouper-ws.  
> > 
> > https://spaces.at.internet2.edu/display/Grouper/Grouper+SQL+interface
> > 
> > Im just looking for advice on this topic 
> > 
> > Cheers
> > 
> > Reid 
> > 
> >> On 15/10/2018, at 8:43 PM, Reid Watson 
> >> <>
> >>  wrote:
> >> 
> >> Hi Everyone, 
> >> 
> >> The grouper team added some improvements to GetMemberships — “PageSize 
> >> and PageNumber"
> >> 
> >> https://bugs.internet2.edu/jira/browse/GRP-1570
> >> 
> >> With the amount domains and groups (Folder / Groups) a user has assigned 
> >> were noticing a response time between 6 and 15 seconds for one request 
> >> to Grouper-ws Service "v2_3_000/memberships"
> >> 
> >> Example - user has 643 groups (Grouper v2.3) 
> >> 
> >> 1. POST /grouper-ws/servicesRest/json/v2_3_000/subjects HTTP/1.1.
> >> {"WsRestGetSubjectsLiteRequest":{"pageNumber":"1","pageSize":"20","ascending":"T","sortString":"id","subjectIdentifier":"wwan174”}
> >> 
> >> 1.1. Result 
> >> {"WsGetSubjectsResults":{"responseMetadata":{"millis":"258","serverVersion":"2.3.0"},"resultMetadata":{"resultCode":"SUCCESS","resultMessage":"Queried
> >>  1 
> >> subjects","success":"T"},"wsSubjects":[{"id":"4695399","identifierLookup":"wwan174","name":"Wenlai
> >>  Wang","resultCode":"SUCCESS","sourceId":"jdbc","success":"T"}]}}
> >> 
> >> 
> >> 2. POST /grouper-ws/servicesRest/json/v2_3_000/memberships HTTP/1.1.
> >> {"WsRestGetMembershipsRequest":{"pageSize":"500","pageNumber":"1","ascending":"T","wsSubjectLookups":[{"subjectId":"4695399"}]}}
> >> 
> >> 2.1 Snippet of the result  
> >> 
> >> {"WsGetMembershipsResults":{"responseMetadata":{"millis":"8340","serverVersion":"2.3.0"},"resultMetadata":{"resultCode":"SUCCESS","resultMessage":"Found
> >>  2227 results involving 500 groups and 1 
> >> subjects","success":"T"},"wsGroups":[{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":"1989.auckland.ac.nz:NAAdminGroups","extension":"NAAdminGroups","idIndex":"446040","name":"1989.auckland.ac.nz:NAAdminGroups","typeOfGroup":"group","uuid":"e13b649a10514393b3373ea7d1c85592"},{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":"1996.auckland.ac.nz:NAAdminGroups","extension":"NAAdminGroups","idIndex":"411614","name":"1996.auckland.ac.nz:NAAdminGroups","typeOfGroup":"group","uuid":"0507d40a28274c62a2d071e6672e4870"},{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":"1997.auckland.ac.nz:NAAdminGroups","extension":"NAAdminGroups","idIndex":"411630","name":"1997.auckland.ac.nz:NAAdminGroups","typeOfGroup":"group","uuid":"7a4085bb84274c17a2a1061e6fe21bb3"},{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":”1998
> >> 
> >> 
> >> The problem is the following query runs 5 times because we set 
> >> "pageSize:500” and the id is batched to 100 items, each query takes 1.7 
> >> seconds to respond directly from the Oracle DB..
> >> 
> >>   select distinct membership1_.membership_id as membership_id1_26_0_, 
> >> membership1_.immediate_membership_id as immediate_membersh2_26_0_, 
> >> membership1_.group_set_id as group_set_id3_26_0_, group2_.id as 
> >> id1_22_1_, member0_.id as id1_24_2_, 
> >> membership1_.hibernate_version_number as hibernate_version_4_26_0_, 
> >> membership1_.owner_id as owner_id5_26_0_, membership1_.owner_attr_def_id 
> >> as owner_attr_def_id6_26_0_, membership1_.owner_group_id as 
> >> owner_group_id7_26_0_, membership1_.owner_stem_id as 
> >> owner_stem_id8_26_0_, membership1_.member_id as member_id9_26_0_, 
> >> membership1_.field_id as field_id10_26_0_, 
> >> membership1_.immediate_field_id as immediate_field_i11_26_0_, 
> >> membership1_.via_composite_id as via_composite_id12_26_0_, 
> >> membership1_.membership_creator_id as membership_creato13_26_0_, 
> >> membership1_.membership_create_time as membership_create14_26_0_, 
> >> membership1_.group_set_creator_id as group_set_creator15_26_0_, 
> >> membership1_.group_set_create_time as group_set_create_16_26_0_, 
> >> membership1_.context_id as context_id17_26_0_, 
> >> membership1_.group_set_parent_id as group_set_parent_18_26_0_, 
> >> membership1_.via_group_id as via_group_id19_26_0_, membership1_.depth as 
> >> depth20_26_0_, membership1_.mship_type as mship_type21_26_0_, 
> >> membership1_.immediate_mship_enabled as immediate_mship_e22_26_0_, 
> >> membership1_.immediate_mship_enabled_time as immediate_mship_e23_26_0_, 
> >> membership1_.immediate_mship_disabled_time as immediate_mship_d24_26_0_, 
> >> group2_.hibernate_version_number as hibernate_version_2_22_1_, 
> >> group2_.last_membership_change as last_membership_ch3_22_1_, 
> >> group2_.last_imm_membership_change as last_imm_membershi4_22_1_, 
> >> group2_.parent_stem as parent_stem5_22_1_, group2_.creator_id as 
> >> creator_id6_22_1_, group2_.create_time as create_time7_22_1_, 
> >> group2_.modifier_id as modifier_id8_22_1_, group2_.modify_time as 
> >> modify_time9_22_1_, group2_.name as name10_22_1_, group2_.display_name 
> >> as display_name11_22_1_, group2_.extension as extension12_22_1_, 
> >> group2_.display_extension as display_extension13_22_1_, 
> >> group2_.description as description14_22_1_, group2_.context_id as 
> >> context_id15_22_1_, group2_.alternate_name as alternate_name16_22_1_, 
> >> group2_.type_of_group as type_of_group17_22_1_, group2_.id_index as 
> >> id_index18_22_1_, member0_.hibernate_version_number as 
> >> hibernate_version_2_24_2_, member0_.subject_id as subject_id3_24_2_, 
> >> member0_.subject_source as subject_source4_24_2_, member0_.subject_type 
> >> as subject_type5_24_2_, member0_.context_id as context_id6_24_2_, 
> >> member0_.subject_identifier0 as subject_identifier7_24_2_, 
> >> member0_.sort_string0 as sort_string8_24_2_, member0_.sort_string1 as 
> >> sort_string9_24_2_, member0_.sort_string2 as sort_string10_24_2_, 
> >> member0_.sort_string3 as sort_string11_24_2_, member0_.sort_string4 as 
> >> sort_string12_24_2_, member0_.search_string0 as search_string13_24_2_, 
> >> member0_.search_string1 as search_string14_24_2_, 
> >> member0_.search_string2 as search_string15_24_2_, 
> >> member0_.search_string3 as search_string16_24_2_, 
> >> member0_.search_string4 as search_string17_24_2_, member0_.name as 
> >> name18_24_2_, member0_.description as description19_24_2_ from 
> >> grouper_members member0_ cross join grouper_memberships_all_v 
> >> membership1_ cross join grouper_groups group2_ cross join grouper_fields 
> >> field3_ where membership1_.owner_group_id=group2_.id and 
> >> membership1_.member_id=member0_.id and 
> >> membership1_.immediate_mship_enabled='T' and 
> >> membership1_.field_id=field3_.id and field3_.type='list' and 
> >> (membership1_.owner_group_id in ('5f6fbfe087e74471a5badfd3b3e4c24b' , 
> >> 'e89ac5a4027d415dbb6e82b0a83ee49b' , '615f41a690dc4503a3b59190c52c2149' 
> >> , 'f704e9e33f884f23bf57f75721e9daee' , 
> >> '33139201d35f4372a99efc8b2c76aadf' , '56c3b795eed64810a7f21d1fd1c79dc0' 
> >> , '714d1347db904a298074065e24099a5f' , 
> >> 'b0ba21fb6d194fbdb9bbc0fb7b9f227a' , '66ba7e778615484ba5b78f44859dd076' 
> >> , 'a776e803f9b1475a92984b977043c96e' , 
> >> '64a0faed6ac440979d14877a93d2dd5c' , 'b5a7db29a7f14b4d92ecc7c7b4591945' 
> >> , '304fb43c85f847e1b7729d241529f7b1' , 
> >> '01984212137a4ba5bd9ea299dbf78259' , 'c2d567315f384be99e3c88149df84c74' 
> >> , '526e8fa76146428589d15140a25d16b1' , 
> >> '2041c0d557624814b267dd2c97cbb66a' , '8472df45758343eb8ece1b0a4b54bbd8' 
> >> , '91677c7eeba34571af49a80b13dba750' , 
> >> '5bc9679b210542c8875215df99d9b80d' , '1432ace3a5d243e69b4b0d7a0309cd15' 
> >> , '12c9ec4221b349a3aef419c32ea2bce1' , 
> >> '4f6c6a93993242108e412357394f764d' , '794b01653e404eaf89be1dcd520d1eba' 
> >> , '95bf323f921b491ab818daf449f1a033' , 
> >> 'f909aaf0917f48bb88689d925041a0aa' , '64e4cbecb9d44cf3b6825acb705ab09c' 
> >> , '00b062c6b5334dac9f1456c9497acd9b' , 
> >> 'e1dfa96735d24cc0adfd30f8dc678fa5' , '4da63441c75c4597a2a41d62b7960742' 
> >> , '04f69e2014e544b0972331f431c45096' , 
> >> 'c5f96126c808489c86dbc0f7a3d7ff1b' , '8ace229d662f4bc6a33723a7d4d0dc74' 
> >> , 'a70962032ab54635ac20f6bc4f9d70b0' , 
> >> '66bca41dcbd347418224330e759eddca' , '6c6275c458dd4be8bb6a74f828776295' 
> >> , 'b782d24ccf09413a8e84d6fcc8eb92a3' , 
> >> 'e7a9e7d35f404635a611678c18f13304' , 'a1d5c4027844444392afb9a468304b4e' 
> >> , '9df335f404584e9e92a4de660f459f25' , 
> >> '33953b2e677e463b968927ebbcacf513' , '689b7a4ea39142339a70cc7bd2d67914' 
> >> , '134c62b1ba5043798efe16106d129938' , 
> >> 'b01a2ed3cf084614990755d62c8357b2' , 'dcc3940ba74e42e59786d8b3e50e20ec' 
> >> , '98ccb0e7bc4e45358ed6f5f306c9a751' , 
> >> '7447ff9e2b9049fe84fab1b0a9d0c6a2' , '69ed88423a1e4838ae2228bfe183a354' 
> >> , '8c52eba0c710498da3fecd33fae276a4' , 
> >> '8c9fd31bcbae4ea3b3dfaaafa4c626d6' , 'e8524ff5975e4b939d2810e73986635c' 
> >> , '5a12e00f022742b68f85927efb5b3f91' , 
> >> '10c16a018dc04abca342cfbb6aa8a088' , 'cde48ccad1b74cb591ab4bf484aeac12' 
> >> , 'bbf0e98bdc42464a94cd040c6233669f' , 
> >> '3adf91449be74332ae2cd89b42472d88' , '4036c58865df4cbea691a346b2463177' 
> >> , '845da7370f4e4cf3bc4d31b490e67170' , 
> >> 'b13628533a7744a08db68df5f24b16b0' , '4a00d5daf65640deb7d145b5755e41c7' 
> >> , '723c8cf0b43745409422fd4de88b9fda' , 
> >> '70308d952c9f44e0a5664a8b48d8afe3' , 'e70ffdf56565495f93de7148c2b11776' 
> >> , 'bb12a37e9ae64e8a81242095859b6da5' , 
> >> 'aab8be74d392415db5ae81e3cb99c4ee' , 'a4f112f41e964294a7214765b6f804b1' 
> >> , '93d1ae5a5e9149b78fc6fff10d144ffd' , 
> >> 'aa7a3edf2d224198b2863c3b09f5de0c' , '1d461d0bc54f49b5911ad13f3e1c8b79' 
> >> , 'daf877a600b74b20bedc0fbc5f29566c' , 
> >> '83052510c6eb46db83711c21d2da8671' , '226820f3837c4dc2bfa94aa5cef4afb5' 
> >> , '7dedd10b25004d67857233dc3ae1d745' , 
> >> '137e20b8fc43466ca31878967460f956' , 'fe9e9cb756e24e849dd4ea10dfe7b832' 
> >> , '0306497361d34e389b86b7477b8affe3' , 
> >> '116777951bdc44ee82fc22aa86b175e0' , '734934019e8543df9c2f2242aa3b5700' 
> >> , 'ace9dbf53e4e4ab9937088fb89ffee2c' , 
> >> '8779d027eb83427183cb1bf09d8c7eaa' , '881c77e476064f5f8d27dc721a147871' 
> >> , 'c7db595b90524d9b995567e7439dca06' , 
> >> '33e59abeeb7a4839a8e3e21061c12f0a' , '6e864564e99b4c498264ad22fbd7d29f' 
> >> , '17b7aa53b4684bcdbcd4d6378e77ef25' , 
> >> 'cde139198a4c4a87b651264b3c63f4a7' , '23352904f9da4b319025445dcc526d66' 
> >> , '30feb447977647d999304bf9105e489f' , 
> >> 'c6f98ce79f8c479f9588786fc737239b' , '8b0a1b77fb164f34b446704307e0f85d' 
> >> , 'a26c09e0ab1b4cc3b5683f19cd27a0c0' , 
> >> 'c6bd8669e11c4e9ba58bad23491f185a' , '55258aa1c8e64312ba98ee06b9f547dd' 
> >> , 'b917c19aaad143519531cf6365f0d935' , 
> >> '15b37ee403404cdf8e52eac4f26b8792' , 'd9daff4da8f54288ac159c6c7067e8ca' 
> >> , '607a0353a37a4062a17a373c1c30a55f' , 
> >> 'e09dac2f79e9407c80d9f1a8c7990007' , '9b5557848c0345fcb77aed4f98126efe' 
> >> , 'd462d13ba3134841aec3c057357c21d0')) and (membership1_.member_id in 
> >> ('ae0b887741fc492aa86fb843af0f02cc'))
> >> 
> >> Any advice to improve the response time from the query ?
> >> 
> >> Cheers
> >> 
> >> Reid Watson
> >> 
> >> 
> >