Grouper Users - Open Discussion List

["Hyzer, Chris" <>]

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

>>

>>

>