Versions Compared

Old Version 20

changes.mady.by.user Chris Hyzer (upenn.edu)

Saved on

compared with

New Version Current

changes.mady.by.user ben.rappleyea.2@at.internet2.edu

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Include Page
spaceKeyGrouper
pageTitleNavigation

Panel
borderColor#ccc
bgColor#FcFEFF
titleColorwhite
titleBGColor#00a400

Image Removed  This topic is discussed in the Advanced Topics training video.

Grouper's views can be granted to schemas to make it possible to access Grouper data from SQL.  

...

Point in time attribute assignments (marker and name/value pairs like provisioning)

Code Block
create table temp_provisioning_assigns as
SELECT 
  case gpaa_marker.source_id as attribute_assign_id_marker,
  case when gpaa_marker.owner_group_id is not null then 'group'
  when gpaa_marker.owner_stem_id is not null then 'stem'
  end as assign_type,
  case 
    when gpaa_marker.owner_group_id is not null then gpg.name
    when gpaa_marker.owner_stem_id is not null then gps.name
  end as assign_name,
  gpadn_value.name attribute_name,
  gpaav.value_string,
  gpaav.end_time
   FROM grouper_pit_attribute_assign gpaa_marker
    left outer join grouper_pit_groups gpg on gpaa_marker.owner_group_id = gpg.id
    left outer join grouper_pit_stems gps on gpaa_marker.owner_stem_id = gps.id
    join grouper_pit_attribute_assign gpaa_value on gpaa_marker.id = gpaa_value.owner_attribute_assign_id
    join grouper_pit_attr_assn_value gpaav on gpaa_value.id = gpaav.attribute_assign_id
    join grouper_pit_attr_def_name gpadn_marker on gpaa_marker.attribute_def_name_id = gpadn_marker.id 
      and gpadn_marker.name = 'etc:provisioning:provisioningMarker'
    join grouper_pit_attr_def_name gpadn_value on gpaa_value.attribute_def_name_id = gpadn_value.id
  WHERE 
    (gpaa_marker.owner_group_id is not null or gpaa_marker.owner_stem_id is not null)
    and gpaav.end_time/1000000 > 1695814657
    order by 1, 2, 3, 4;

Make a SQL to generate GSH

Code Block
select distinct attribute_assign_id_marker, assign_name,
'new Provisionable' || 
  (case when exists (select 1 from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker 
  and tpa2.assign_type = 'group') then 
    'Group' else 'Stem' end)
  || 'Save().assignTargetName("'
  || (select tpa2.value_string from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker
and tpa2.attribute_name = 'etc:provisioning:provisioningTarget') 
  || '").assign' || 
  (case when exists (select 1 from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker 
  and tpa2.assign_type = 'group') then 
    'Group' else 'Stem' end) ||
  'Name("' || 
  (select distinct assign_name from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker) ||
   '")"' ||
   (case when exists (select 1 from temp_provisioning_assigns tpa2 where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker 
  and tpa2.assign_type = 'group') then 
    'Group' else 'Stem' end)
   || '".save();'
as script from temp_provisioning_assigns tpa 
where exists (select 1 from temp_provisioning_assigns tpa2 
where tpa.attribute_assign_id_marker = tpa2.attribute_assign_id_marker and tpa2.value_string = 'true'
and tpa2.attribute_name = 'etc:provisioning:provisioningDirectAssign') 
and assign_type = 'stem'
order by 2;