...
- Lets make a function which strips out special chars:
| Code Block |
|---|
CREATE OR REPLACE PACKAGE AUTHZADM.authzadm_pkg
AS
FUNCTION remove_special_chars (the_input varchar2)
RETURN varchar2;
END authzadm_pkg;
/
CREATE OR REPLACE PACKAGE BODY AUTHZADM.authzadm_pkg
AS
FUNCTION remove_special_chars (the_input varchar2)
RETURN varchar2
AS
the_string varchar2(4000);
BEGIN
--take out anything not alphanumeric, space, underscore, or dash
the_string := REGEXP_REPLACE(the_input, '[Penn organizational hierarchy - part 1^a-zA-Z0-9 _\-]', '');
the_string := trim(the_string);
return the_string;
END;
END authzadm_pkg;
/
|
* Implement the view of orgs. Note, in the view you can easily use unions in the sql to end the hierarchy at a different node. At first we were going to do this, then we decided against it. However, you will see that we do filter out certain branches and nodes. Also note we shorten the names of some top level nodes.
...
- Make a view about the person org metadata
| Code Block |
|---|
CREATE OR REPLACE FORCE VIEW ORG_LOADER_PERSON_META_V
(
GROUP_NAME,
GROUP_DISPLAY_NAME,
READERS,
VIEWERS,
ORG_ID
)
AS
SELECT DISTINCT
'penn:community:employee:org:'
|| OLV.ORG_NAME
|| ':'
|| OLV.ORG_NAME
|| '_personorg'
AS group_name,
'penn:community:employee:org:'
|| OLV.ORG_NAME
|| ' - '
|| olv.ORG_DISPLAY_NAME
|| ':'
|| OLV.ORG_NAME
|| ' - '
|| olv.ORG_DISPLAY_NAME
AS group_display_name,
'penn:community:employee:orgSecurity:orgReaders' AS readers,
'penn:community:employee:orgSecurity:orgViewers' AS viewers,
OLV.ORG_NAME AS org_id
FROM org_list_v olv
WHERE olv.PAYROLL_FLAG = 'Y'
ORDER BY 2;
|
* This data looks like this
| Code Block |
|---|
penn:community:employee:org:0001:0001_personorg penn:community:employee:org:0001 - General University:0001 - General University penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0001
penn:community:employee:org:0007:0007_personorg penn:community:employee:org:0007 - General University EB Pool:0007 - General University EB Pool penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0007
penn:community:employee:org:0030:0030_personorg penn:community:employee:org:0030 - Learning Alliance for Higher Education LLC:0030 - Learning Alliance for Higher Education LLC penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0030
penn:community:employee:org:0101:0101_personorg penn:community:employee:org:0101 - Anthropology:0101 - Anthropology penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0101
penn:community:employee:org:0102:0102_personorg penn:community:employee:org:0102 - Asian and Middle Eastern Studies:0102 - Asian and Middle Eastern Studies penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 0102
|
* Penn has two types of orgs: orgs that hold people, and orgs that dont (they hold other orgs). So I will create them separately, here is a view of orgs that hold people, that the loader will use. Note: these will not be include/exclude since we only need that on the higher level groups (rollups). Note, each group here should end in _personorg so we can know which groups are managed by this loader process.
...
- Add the config group. Note, there are no org members yet (1=0), so I can inspect the grouperorgs_hierarchical table
* Run the jobCode Block [appadmin@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% GSH_DEBUG=true true gsh 1% grouperSession = GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 9702ff7015a84c019ae58ce6ae950115,'GrouperSystem','application' gsh 2% stem = StemFinder.findByName(grouperSession, "penn:community:employee"); stem: name='penn:community:employee' displayName='penn:community:employee' uuid='3cb63130-03e1-4b60-8f01-1454ee3c9588' gsh 4% group = addGroup("penn:community:employee", "orgConfig", "orgConfig"); group: name='penn:community:employee:orgConfig' displayName='penn:community:employee:orgConfig' uuid='f36a72d38053405d997ee8fc6eb66ff4' gsh 5% groupAddType("penn:community:employee:orgConfig", "grouperLoader"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderDbName", "grouper"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuartzCron", "0 46 6 * * ? "); true gsh 8% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuery", "select group_name, subject_id from org_loader_person_v where 1=0"); true gsh 9% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderScheduleType", "CRON"); true gsh 10% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderType", "SQL_GROUP_LIST"); true
-* Inspect the grouperorgs_hierarchy table. Note, there were some problems, so we adding the function to strip bad chars and trim the data... also adjust the org_loader_person_v (so the names and everything are ok). Here is what the org_loader_person_v looks like (person data scrubbed)-Code Block [appadmin@lorenzo bin]$ ./gsh.sh Type help() for instructions gsh 0% grouperSession = GrouperSession.startRootSession(); edu.internet2.middleware.grouper.GrouperSession: 6e1432f7de314aeca2f927f939f1a5be,'GrouperSystem','application' gsh 1% group = GroupFinder.findByName(grouperSession, "penn:community:employee:orgConfig"); group: name='penn:community:employee:orgConfig' displayName='penn:community:employee:orgConfig' uuid='f36a72d38053405d997ee8fc6eb66ff4' gsh 2% loaderRunOneJob(group); loader ran successfully, inserted 0 memberships, deleted 0 memberships, total membership count: 0
* Add the group query, and fix the member query (take out 1=0)Code Block GROUP_NAME SUBJECT_ID penn:community:employee:org:TOPU:UNIV:USCH:51XX:DPDN:5188:5188_personorg 12345678 penn:community:employee:org:TOPU:UNIV:USTU:85XX:CRSC:ASPP:8508:8508_personorg 12345679 penn:community:employee:org:TOPU:UNIV:USCH:36XX:APPC:3604:3604_personorg 12345680 penn:community:employee:org:TOPU:UNIV:USCH:02XX:GRAD:GRAO:0315:0315_personorg 12345681
Code Block gsh 5% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderQuery", "select group_name, subject_id from org_loader_person_v"); true gsh 6% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGroupQuery", "select olpmv.GROUP_NAME as group_name, olpmv.GROUP_DISPLAY_NAME as group_display_name, olpmv.READERS, olpmv.VIEWERS, olpmv.ORG_ID from ORG_LOADER_PERSON_META_V olpmv"); true gsh 7% setGroupAttr("penn:community:employee:orgConfig", "grouperLoaderGroupsLike", "penn:community:employee:org:%_personorg"); true gsh 8% loaderRunOneJob(group);
...