Skip to end of metadata
Go to start of metadata

In Grouper v2.5.23+ you can configure Grouper to schedule a SQL or GSH script

grouper-loader.properties

Config attributeValue

otherJob.<configId>.class

edu.internet2.middleware.grouper.app.loader.OtherJobScript

otherJob.<configId>.quartzCron

0 0 0 * * ?   (nightly at midnight)

otherJob.<configId>.scriptType

sql or gsh
Note, sql runs against the grouper database.  To run against a different database, use a GSH script. Here is an example

otherJob.<configId>.scriptSource

this is the SQL or GSH script, use $newline$ to separate lines.

note: in SQL you should commit after DML commands.

Mutually exclusive with "fileName"

otherJob.<configId>.fileName

fileName of script to run.

Mutually exclusive with "scriptSource"

otherJob.<configId>.connectionNameif SQL this is the connection name to use (v2.5.40+)

Note: The output of the script (or the error) is in the daemon log (can view from UI).

SQL logs details from edu.internet2.middleware.grouper.ddl.GrouperAntProject at INFO level if you want to get them from log4j.

Update the loader log from GSH (v2.5.26+)

import edu.internet2.middleware.grouper.app.loader.OtherJobScript;

// if you want to know the jobName ( value in the grouper-loader.properties config ).
jobName = OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().getJobName();
// jobName can help you lookup other config vars for the script. ( you may need to do more parsing/fixing of the string )//edu.internet2.middleware.grouper.app.loader.GrouperLoaderConfig.retrieveConfig().propertyValueString(...)
// or for grouper.properties config
//edu.internet2.middleware.grouper.cfg.GrouperConfig.retrieveConfig().propertyValueString(...)

// generally you will add to the counts
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().addInsertCount(5);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().addTotalCount(6);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().addUpdateCount(7);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().addDeleteCount(8);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().addUnresolvableSubjectCount(1);

// if you just want to set the count you can do that too.  Other fields in Hib3GrouperLoaderLog can be set too
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().setInsertCount(1);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().setTotalCount(2);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().setUpdateCount(3);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().setDeleteCount(4);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().setUnresolvableSubjectCount(1);

// if you just want to get the count you can do that too. Other fields in Hib3GrouperLoaderLog can be set too
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().getInsertCount(1);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().getTotalCount(2);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().getUpdateCount(3);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().getDeleteCount(4);
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().getUnresolvableSubjectCount(1);// and you can just "increment" the UnresolvableSubjectCount (only)
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().incrementUnresolvableSubjectCount();// for long running scripts you likely should also set this from time to time...
OtherJobScript.retrieveFromThreadLocal().getOtherJobInput().getHib3GrouperLoaderLog().setLastUpdated(Timestamp lastUpdated1);



Example 1: schedule a SQL script with the script in the config file

grouper-loader.properties

otherJob.scriptSql.class = edu.internet2.middleware.grouper.app.loader.OtherJobScript
otherJob.scriptSql.quartzCron = 0 0 0 * * ?
otherJob.scriptSql.scriptType = sql
otherJob.scriptSql.scriptSource = update grouper_groups set description = 'whatever' where name = 'a:b';$newline$update grouper_stems set description = 'whatever' where name='d:e';$newline$commit;

Job summary

scriptType: sql
scriptSource: update grouper_groups set description = 'whatever' where name = 'a:b';
update grouper_stems set description = 'whatever' where name = 'd:e';
commit;

connecting to jdbc:mysql://localhost:3306/grouper_v2_5?useSSL=false
Loading com.mysql.jdbc.Driver using system loader.
Executing resource: /Users/mchyzer/git/grouper_v2_5/grouper/ddlScripts/grouperDdl_20200414_17_27_15_852.sql
SQL:  update grouper_groups set description = 'whatever' where name = 'a:b'
1 rows affected
SQL:  update grouper_stems set description = 'whatever' where name = 'd:e'
1 rows affected
SQL:  commit
0 rows affected
Committing transaction
3 of 3 SQL statements executed successfully

Script was executed successfully

Example 2: schedule a SQL script with a script in a file in your container

File:   /opt/grouper/scripts/mySqlScript.sql

update grouper_groups set description = 'whatever' where name = 'a:b';
update grouper_stems set description = 'whatever' where name='d:e';
commit;

File: grouper-loader.properties

otherJob.scriptSqlFile.class = edu.internet2.middleware.grouper.app.loader.OtherJobScript
otherJob.scriptSqlFile.quartzCron = 0 0 0 * * ?
otherJob.scriptSqlFile.scriptType = sql
otherJob.scriptSqlFile.fileName = /opt/grouper/scripts/mySqlScript.sql

Job message

scriptType: sql
fileName: /opt/grouper/scripts/mySqlScript.sql

connecting to jdbc:mysql://localhost:3306/grouper_v2_5?useSSL=false
Loading com.mysql.jdbc.Driver using system loader.
Executing resource: /opt/grouper/scripts/mySqlScript.sql
SQL:  update grouper_groups set description = 'whatever2' where name = 'a:b'
1 rows affected
SQL:  update grouper_stems set description = 'whatever2' where name='d:e'
1 rows affected
SQL:  commit
0 rows affected
Committing transaction
3 of 3 SQL statements executed successfully

Script was executed successfully



Example 3: schedule a GSH script with a script in the config file

grouper-loader.properties

otherJob.scriptGsh.class = edu.internet2.middleware.grouper.app.loader.OtherJobScript
otherJob.scriptGsh.quartzCron = 0 0 0 * * ?
otherJob.scriptGsh.scriptType = gsh
otherJob.scriptGsh.scriptSource = GrouperSession grouperSession = GrouperSession.startRootSession();$newline$new GroupSave(grouperSession).assignName("stem1:a").assignCreateParentStemsIfNotExist(true).save();

Job message

scriptType: gsh
scriptSource: GrouperSession grouperSession = GrouperSession.startRootSession(); 
 new GroupSave(grouperSession).assignName("stem1:a").assignCreateParentStemsIfNotExist(true).save();

(2.5.0-beta-2, JVM: 1.8.0_161)
Type ':help' or ':h' for help.
-------------------------------------------------------------------------------
groovy:000> :load '/Users/mchyzer/git/grouper_v2_5/grouper/target/classes/groovysh.profile'
groovy:000> GrouperSession grouperSession = GrouperSession.startRootSession();
===> b37a372289bd430a887239801a1bab90,'GrouperSystem','application'
groovy:000> new GroupSave(grouperSession).assignName("stem1:a").assignCreateParentStemsIfNotExist(true).save();
===> Group[name=stem1:a,uuid=cb7b2bead8ee433aa912ac7ec321a532]
groovy:000> :exit


Example 4: schedule a GSH script from a file in your container

File:   /opt/grouper/scripts/myGshScript.gsh

GrouperSession grouperSession = GrouperSession.startRootSession(); 
new GroupSave(grouperSession).assignName("stem1:a").assignCreateParentStemsIfNotExist(true).save();

File: grouper-loader.properties

otherJob.scriptGshFile.class = edu.internet2.middleware.grouper.app.loader.OtherJobScript
otherJob.scriptGshFile.quartzCron = 0 0 0 * * ?
otherJob.scriptGshFile.scriptType = gsh
otherJob.scriptGshFile.fileName = /opt/grouper/scripts/myGshScript.gsh

Job message

scriptType: gsh
fileName: /opt/grouper/scripts/myGshScript.gsh

(2.5.0-beta-2, JVM: 1.8.0_161)
Type ':help' or ':h' for help.
-------------------------------------------------------------------------------
groovy:000> :load '/Users/mchyzer/git/grouper_v2_5/grouper/target/classes/groovysh.profile'
groovy:000> GrouperSession grouperSession = GrouperSession.startRootSession();
===> b37a372289bd430a887239801a1bab90,'GrouperSystem','application'
groovy:000> new GroupSave(grouperSession).assignName("stem1:a").assignCreateParentStemsIfNotExist(true).save();
===> Group[name=stem1:a,uuid=cb7b2bead8ee433aa912ac7ec321a532]
groovy:000> :exit



  • No labels