Grouper Users - Open Discussion List

[Reid Watson <>]

As I understand it the view “grouper_memberships_lw_v” will return all 
groups. We need to be able to query Direct or Indirect Membership, do you 
think “grouper_memberships_v” view will meet these requirements ? 


> On 24/10/2018, at 3:07 PM, Reid Watson 
> <>
>  wrote:
> 
> 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
>>>> 
>>>> 
>>> 
>