...
| Code Block |
|---|
ORG_CODE PENN_ID
0007 12345678
8105 12345679
|
-* Now configure the grouper.properties. Add the hook, and the org management section
-| Code Block |
|---|
hooks.loader.class=edu.internet2.middleware.grouper.hooks.examples.HierarchicalOrgLoaderHook
|
| Code Block |
|---|
#####################################
## org management
#####################################
# if the orgs table(s) should be included in the DDL (includes the hierarchical table
orgs.includePocOrgsTablesInDdl = true
# loader connection of the database where orgs are (grouper means the grouper db in grouper.hibernate.properties)
orgs.databaseName = grouper
#table name of the org table (can prefix by schema name if you like)
orgs.orgTableName = org_list_v
#column names of this table
orgs.orgIdCol = org_name
orgs.orgNameCol = org_name
orgs.orgDisplayNameCol = org_display_name
orgs.orgParentIdCol = parent_id
#stem where the orgs are, e.g. poc:orgs
orgs.parentStemName = penn:community:employee:org
#org config name
orgs.configGroupName = penn:community:employee:orgConfig
|
-* Add the tables:
-| Code Block |
|---|
[appadmin@lorenzo bin]$ ./gsh.sh -registry -check
Using GROUPER_HOME: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/bin/..
Using GROUPER_CONF: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/bin/../classes
Using JAVA: /opt/appserv/java5/bin/java
using MEMORY: 64m-512m
Grouper starting up: version: 1.4.2, build date: 2009/05/19 16:13:03, env: PROD
grouper.properties read from: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/grouper.properties
Grouper current directory is: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/bin
log4j.properties read from: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/log4j.properties
Grouper is logging to file: /opt/appserv/tomcat_3c/logs/fastGrouper/grouper_error.log, at min level WARN for package: edu.internet2.middleware.grouper, based on log4j.properties
grouper.hibernate.properties: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/grouper.hibernate.properties
grouper.hibernate.properties: schema@jdbc:oracle:thin:@dbserver.whatever.whatever:1521:sid
sources.xml read from: /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/classes/sources.xml
sources.xml jdbc source id: pennperson: GrouperJdbcConnectionProvider
sources.xml groupersource id: g:gsa
sources.xml jdbc source id: servPrinc: GrouperJdbcConnectionProvider
(note, might need to type in your response multiple times (Java stdin is flaky))
(note, you can whitelist or blacklist db urls and users in the grouper.properties)
Are you sure you want to schemaexport all tables (dropThenCreate=F,writeAndRunScript=F) in db user 'schema', db url 'jdbc:oracle:thin:@dbserver.whatever.whatever:1521:sid'? (y|n):
y
Continuing...
Grouper ddl object type 'GrouperOrg' has dbVersion: 0 and java version: 1
Grouper database schema DDL requires updates
(should run script manually and carefully, in sections, verify data before drop statements, backup/export important data before starting, follow change log on confluence, dont run exact same script in multiple envs - generate a new one for each env),
script file is:
/opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/ddlScripts/grouperDdl_20090519_16_44_25_124.sql
Note: this script was not executed due to option passed in
To run script via gsh, carefully review it, then run this:
gsh -registry -runsqlfile /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/ddlScripts/grouperDdl_20090519_16_44_25_124.sql
|
-* Now I will carefully inspect and run the sql file, which adds the hierarchy tables in grouper (grouperorg tables and view)
-| Code Block |
|---|
[appadmin@lorenzo bin]$ ./gsh.sh -registry -runsqlfile /opt/appserv/tomcat_3c/webapps/fastGrouperProdDaemon/WEB-INF/ddlScripts/grouperDdl_20090519_16_44_25_124.sql
|
* Make a view about the person org metadata
| Code Block |
|---|
CREATE OR REPLACE FORCE VIEW ORG_LOADER_PERSON_META_V
(GROUP
GROUP_NAME,
group GROUP_displayDISPLAY_nameNAME,
readers READERS,
viewers VIEWERS,
org ORG_idID
)
AS
select distinct gh.ORG_HIERARCHICAL_STEM || ':' || gh.ORG_ID || '_personorg' as group_name,
gh.ORG_HIERARCHICAL_STEM || ' - ' || olv.ORG_DISPLAY_NAME SELECT DISTINCT
'penn:community:employee:org:'
|| OLV.ORG_NAME
|| ':'
|| gh.ORG_ID || ' - ' || olvOLV.ORG_DISPLAY_NAME as group_display_name,
'penn:community:employee:orgSecurity:orgReaders' as readers,
'penn:community:employee:orgSecurity:orgViewers' as viewers,
gh.org_id as org_id
from grouperorgs_hierarchical gh, org_list_v olv
where gh.ORG_ID = olv.ORG_NAME
and olv.PAYROLL_FLAG = 'Y' order by 2
|
* This data looks like this| Code Block |
|---|
GROUP_NAME GROUP_DISPLAY_NAME READERS VIEWERS ORG_ID
penn:community:employee:org:TOPU:NOTU:HCAG:21XX:2100:2100_personorg
|| '_personorg'
AS group_name,
'penn:community:employee:org:TOPU:NOTU:HCAG:21XX:2100 - Health System:2100 - Health System penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 2100
'
|| 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:TOPU:NOTU:HCAG:21XX:2101:2101_personorg 0001:0001_personorg penn:community:employee:org:TOPU:NOTU:HCAG:21XX:21010001 - Hospital of theGeneral University of Pennsylvania:21010001 - Hospital of theGeneral University of Pennsylvania penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 2101
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:9931:9931_personorg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:9931 - Organic Letters Journal:9931 - Organic Letters Journal 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 91310007
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:9979:99790030:0030_personorg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:99790030 - AmericanLearning AcademyAlliance offor PoliticalHigher andEducation Social ScienceLLC:99790030 - American Academy of Political and Social Science Learning Alliance for Higher Education LLC penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 99790030
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG87:9940:99400101:0101_personorg penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG87:9940 - Hillel Foundation:9940 - Hillel Foundation 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 99400102
|
* 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.
| Code Block |
|---|
/* Formatted on 3/10/2013 12:47:28 PM (QP5 v5.163.1008.3004) */
CREATE OR REPLACE FORCE VIEW ORG_LOADER_PERSON_V
(GROUP
GROUP_NAME,
SUBJECT SUBJECT_ID
)
AS
select SELECT distinctDISTINCT olpmv.GROUP_NAME , oav.PENN_ID asAS subject_id
from FROM ORG_LOADER_PERSON_META_V olpmv, org_assign_v oav
where WHERE olpmv.org_id = oav.ORG_CODE;
COMMENT ON TABLE ORG_LOADER_PERSON_V IS 'view which the loader uses to load people to payroll orgs';
|
- Add the config group. Note, there are no org members yet (1=0), so I can inspect the grouperorgs_hierarchical table
| Code 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
|
* Run the job
| 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
|
-* 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 |
|---|
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
|
* Add the group query, and fix the member query (take out 1=0) | 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);
|
- This created 736 org groups with 33k members
...
Image Modified
...
Rollup orgs
- Now we need a meta view which has information about the rollup group: note the top two levels are filtered out
| Code Block |
|---|
CREATE OR REPLACE VIEW ORG_LOADER_ROLLUP_META_V
(GROUP_NAME, GROUP_DISPLAY_NAME, GROUP_DESCRIPTION, READERS, VIEWERS,
ORG_ID, GROUP_OVERALL_NAME, PARENT_ID)
AS
select distinct gh.ORG_HIERARCHICAL_STEM || ':' || gh.ORG_ID || '_rolluporg_systemOfRecord' as group_name,
gh.ORG_HIERARCHICAL_STEM || ' - ' || olv.ORG_DISPLAY_NAME || ':' || gh.ORG_ID || ' - ' || olv.ORG_DISPLAY_NAME || ' system of record' as group_display_name,
gh.ORG_HIER_ALL_SOR_DESCRIPTION as group_description,
'penn:community:employee:orgSecurity:orgReaders' as readers,
'penn:community:employee:orgSecurity:orgViewers' as viewers,
gh.org_id as org_id,
gh.ORG_HIERARCHICAL_STEM || ':' || gh.ORG_ID || '_rolluporg' as group_overall_name,
olv.PARENT_ID
from grouperorgs_hierarchical gh, org_list_v olv
where gh.ORG_ID = olv.ORG_NAME and gh.ORG_HIER_ALL_NAME is not null
and olv.PAYROLL_FLAG = 'N' and olv.ORG_NAME not in ('TOPU', 'UNIV', 'NOTU') order by 2
|
* This has data which looks like this: | Code Block |
|---|
GROUP_NAME GROUP_DISPLAY_NAME GROUP_DESCRIPTION READERS VIEWERS ORG_ID GROUP_OVERALL_NAME
penn:community:employee:org:TOPU:NOTU:HCAG:HCAG_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG - Health Care and Agencies:HCAG - Health Care and Agencies system of record Members of HCAG and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers HCAG penn:community:employee:org:TOPU:NOTU:HCAG:HCAG_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:21XX:21XX_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:21XX - University of Pennsylvania Health System:21XX - University of Pennsylvania Health System system of record Members of 21XX and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 21XX penn:community:employee:org:TOPU:NOTU:HCAG:21XX:21XX_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX - External Organizations Parent:99XX - External Organizations Parent system of record Members of 99XX and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers 99XX penn:community:employee:org:TOPU:NOTU:HCAG:99XX:99XX_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02 - Agencies for SAS:AG02 - Agencies for SAS system of record Members of AG02 and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers AG02 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:AG04_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04 - Agencies for Provost Interdisciplinary Center:AG04 - Agencies for Provost Interdisciplinary Center system of record Members of AG04 and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers AG04 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:AG04
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:AG06_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06 - Agencies for School of Nursing:AG06 - Agencies for School of Nursing system of record Members of AG06 and all groups underneath the hierarchy penn:community:employee:orgSecurity:orgReaders penn:community:employee:orgSecurity:orgViewers AG06 penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:AG06
|
- Now the rollups, make a view which assigns the rollup. Note this is based on the meta view, so it only includes groups in the meta view
| Code Block |
|---|
CREATE OR REPLACE VIEW ORG_LOADER_ROLLUP_V
(GROUP_NAME, MEMBER_GROUP_NAME)
AS
(select distinct rollup_parent.GROUP_NAME as group_name,
/* records which are rollups directly under the rollup */
rollup_child.GROUP_OVERALL_NAME as subject_identifier
from ORG_LOADER_ROLLUP_META_V rollup_parent, ORG_LOADER_ROLLUP_META_V rollup_child
where rollup_child.PARENT_ID = rollup_parent.ORG_ID)
union
/* payroll orgs (which hold people) directly under the rollup */
(select distinct rollup_parent.GROUP_NAME ,
gh_member.ORG_HIERARCHICAL_STEM || ':' || gh_member.ORG_ID || '_personorg' as subject_identifier
from grouperorgs_hierarchical gh_member, ORG_LOADER_ROLLUP_META_V rollup_parent, org_list_v olv_child
where olv_child.PARENT_ID = rollup_parent.org_id and olv_child.ORG_NAME = gh_member.org_id
and olv_child.PAYROLL_FLAG = 'Y' )
|
* The data for this view looks like this | Code Block |
|---|
GROUP_NAME MEMBER_GROUP_NAME
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:9938:9938_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:AG02_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG02:9979:9979_personorg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:AG04_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG04:9992:9992_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:AG06_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG06:9907:9907_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9902:9902_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9911:9911_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9912:9912_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9913:9913_rolluporg
penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:AG07_rolluporg_systemOfRecord penn:community:employee:org:TOPU:NOTU:HCAG:99XX:AG07:9914:9914_rolluporg
|
...