Polling Connector Module

Introduction

Polling Connector Module monitors the datasource for any changes, then update Penrose caches immediately. This way the entries in Penrose can be kept synchronized with the datasources.

Configure Persistent Cache

To use polling connector first you need to enable Persistent Cache. Make sure this feature has been configured correctly before proceeding to the next step.

Create Change Table and Triggers

Penrose relies on your database to log the changes on your source table. The change logs should be stored in a change table in the same location as your source table. You will need to create one change table for each source table. Then you also need to create triggers that adds change log entry into the change table whenever the source table is updated. The following example shows you how to create the change table and triggers for MySQL database.

Suppose you have a "categories" table with the following format:

Column	Type	Primary Key
id	integer	yes
name	varchar
description	varchar

Create a change table with the same name as the source table plus "_changes":

create table categories_changes (
    changeNumber integer auto_increment,
    changeTime datetime,
    changeAction varchar(10),
    changeUser varchar(10),
    id integer,
    primary key (changeNumber)
);

The first 4 columns (changeNumber, changeTime, changeAction, changeUser) should be defined as above. Then it should be followed by the primary key(s) of your source table (e.g. id).

Next create a trigger that logs add operations:

create trigger catagories_add after insert on categories
for each row insert into categories_changes values (
    null,
    now(),
    'ADD',
    substring_index(user(),_utf8'@',1),
    new.id
);

Then create a trigger that logs update operations:

delimiter |
create trigger catagories_modify after update on categories
for each row begin
    if new.id = old.id then
        insert into categories_changes values (
            null,
            now(),
            'MODIFY',
            substring_index(user(),_utf8'@',1),
            new.id
        );
    else
        insert into categories_changes values (
            null,
            now(),
            'DELETE',
            substring_index(user(),_utf8'@',1),
            old.id
        );
        insert into categories_changes values (
            null,
            now(),
            'ADD',
            substring_index(user(),_utf8'@',1),
            new.id
        );
    end if;
end;|
delimiter ;

Then create a trigger that logs delete operations:

create trigger catagories_delete after delete on categories
for each row insert into categories_changes values (
    null,
    now(),
   'DELETE',
   substring_index(user(),_utf8'@',1),
   old.id
);