...
Code Block |
---|
HSQLDB: CREATE TABLE myincrementaltable ( id INTEGER NOT NULL, subject_id VARCHAR(255), subject_identifier VARCHAR(255), subject_id_or_identifier VARCHAR(255), source_id VARCHAR(255), loader_group_name VARCHAR(1024) NOT NULL, timestamp BIGINT NOT NULL, completed_timestamp BIGINT, PRIMARY KEY (id) ); |
Examples
...
Example of SQL_SIMPLE using Oracle (loader table has a group name field to allow multiple SQL_SIMPLE jobs):
Code Block |
---|
Say you have a loader table that looks like the following:
CREATE TABLE myloadertable
(
subject_id VARCHAR(255),
group_name VARCHAR(255)
);
With the following incremental table:
CREATE TABLE myincrementaltable
(
id NUMBER NOT NULL,
subject_id VARCHAR(255),
subject_identifier VARCHAR(255),
subject_id_or_identifier VARCHAR(255),
source_id VARCHAR(255),
loader_group_name VARCHAR(1024) NOT NULL,
timestamp NUMBER NOT NULL,
completed_timestamp NUMBER,
PRIMARY KEY (id)
);
And a sequence for the primary key on the incremental table:
CREATE SEQUENCE myincrementaltable_seq;
And the following loader job:
addRootStem("test", "test")
addGroup("test", "loader1", "loader1")
groupAddType("test:loader1", "grouperLoader")
setGroupAttr("test:loader1", "grouperLoaderDbName", "grouper")
setGroupAttr("test:loader1", "grouperLoaderType", "SQL_SIMPLE")
setGroupAttr("test:loader1", "grouperLoaderScheduleType", "START_TO_START_INTERVAL")
setGroupAttr("test:loader1", "grouperLoaderQuery", "select subject_id from myloadertable")
setGroupAttr("test:loader1", "grouperLoaderIntervalSeconds", "86400")
Assuming this is on the same database as Grouper, you could add the following configuration in grouper-loader.properties (run every 5 seconds):
otherJob.incrementalLoader1.class = edu.internet2.middleware.grouper.app.loader.GrouperLoaderIncrementalJob
otherJob.incrementalLoader1.quartzCron = 0/5 * * * * ?
otherJob.incrementalLoader1.databaseName=grouper
otherJob.incrementalLoader1.tableName=myincrementaltable
And the following trigger:
CREATE OR REPLACE TRIGGER mytrigger
AFTER INSERT OR DELETE OR UPDATE ON myloadertable
FOR EACH ROW
DECLARE
timemillis NUMBER;
BEGIN
select extract(day from(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000
+ to_number(to_char(sys_extract_utc(systimestamp), 'SSSSSFF3')) into timemillis from dual;
IF (:new.subject_id is not null) THEN
INSERT INTO myincrementaltable (id, subject_id, loader_group_name, timestamp) values (myincrementaltable_seq.nextval, :new.subject_id, :new.group_name, timemillis);
END IF;
IF (:old.subject_id is not null) THEN
INSERT INTO myincrementaltable (id, subject_id, loader_group_name, timestamp) values (myincrementaltable_seq.nextval, :old.subject_id, :old.group_name, timemillis);
END IF;
END; |
Example of SQL_GROUP_LIST using Oracle (assumes a single loader job):
Code Block |
---|
Say you have a loader table that looks like the following: CREATE TABLE myloadertable ( subject_id VARCHAR(255), group_name VARCHAR(255) ); With the following incremental table: CREATE TABLE myincrementaltable ( id NUMBER NOT NULL, subject_id VARCHAR(255), subject_identifier VARCHAR(255), subject_id_or_identifier VARCHAR(255), source_id VARCHAR(255), loader_group_name VARCHAR(1024) NOT NULL, timestamp NUMBER NOT NULL, completed_timestamp NUMBER, PRIMARY KEY (id) ); And a sequence for the primary key on the incremental table: CREATE SEQUENCE myincrementaltable_seq; And the following loader job: addRootStem("test", "test") addGroup("test", "owner", "owner") groupAddType("test:owner", "grouperLoader") setGroupAttr("test:owner", "grouperLoaderDbName", "grouper") setGroupAttr("test:owner", "grouperLoaderType", "SQL_GROUP_LIST") setGroupAttr("test:owner", "grouperLoaderScheduleType", "START_TO_START_INTERVAL") setGroupAttr("test:owner", "grouperLoaderQuery", "select group_name, subject_id from myloadertable") setGroupAttr("test:owner", "grouperLoaderIntervalSeconds", "86400") setGroupAttr("test:owner", "grouperLoaderGroupsLike", "test:loader%") Assuming this is on the same database as Grouper, you could add the following configuration in grouper-loader.properties (run every 5 seconds): otherJob.incrementalLoader1.class = edu.internet2.middleware.grouper.app.loader.GrouperLoaderIncrementalJob otherJob.incrementalLoader1.quartzCron = 0/5 * * * * ? otherJob.incrementalLoader1.databaseName=grouper otherJob.incrementalLoader1.tableName=myincrementaltable And the following trigger: CREATE OR REPLACE TRIGGER mytrigger AFTER INSERT OR DELETE OR UPDATE ON myloadertable FOR EACH ROW DECLARE timemillis NUMBER; BEGIN select extract(day from(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000 + to_number(to_char(sys_extract_utc(systimestamp), 'SSSSSFF3')) into timemillis from dual; IF (:new.subject_id is not null) THEN INSERT INTO myincrementaltable (id, subject_id, loader_group_name, timestamp) values (myincrementaltable_seq.nextval, :new.subject_id, 'test:owner', timemillis); END IF; IF (:old.subject_id is not null) THEN INSERT INTO myincrementaltable (id, subject_id, loader_group_name, timestamp) values (myincrementaltable_seq.nextval, :old.subject_id, 'test:owner', timemillis); END IF; END; |
Daemon
- The daemon would retrieve the 10000 oldest rows, removing duplicates.
- Spawn off threads based on the thread configuration.
- If SQL_SIMPLE, query grouperLoaderQuery to see if user should be a member. Query and change Grouper if needed.
- If SQL_GROUP_LIST, query grouperLoaderQuery to see which groups the user should be a member of. Need to figure out which Grouper groups are managed by this loader job to see what changes to make. We will require the "like" attribute.
- Need to take into account grouperLoaderAndGroups. If configured, user must be in all all of the "and" groups to be in the loader group.
- Any special logic around include/excludes? No
- If group doesn't exist in Grouper, run full sync.
- If more than 100 changes for the same group, sleep for a minute, run full sync, mark changes in change log as completed.
- Fail safe? Covered by above.
- Update with time when complete and delete if more than a day.
- Log to grouper_loader_log.