In Grouper v2.5.23+ you can configure Grouper to schedule a SQL or GSH script
grouper-loader.properties
| Config attribute | Value |
|---|---|
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 |
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" |
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.
Example 1: schedule a SQL script with the script in the config file
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' 6 rows affected SQL: update grouper_stems set description = 'whatever' where name = 'd:e' 28 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; 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: /var/folders/jn/9hs8490j46d5bnw0skpnjrk00000gn/T/someFile.sql connecting to jdbc:mysql://localhost:3306/grouper_v2_5?useSSL=false Loading com.mysql.jdbc.Driver using system loader. Executing resource: /var/folders/jn/9hs8490j46d5bnw0skpnjrk00000gn/T/someFile.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