A note on datasources: You will need to adjust the datasource names to match your own. These routes assume (primarily, unless otherwise noted) two datasources: a connection to your OpenGroupware database called "OpenGroupware" and a connection to a foriegn database called "MOA". The name of your foriegn database will almost certainly be different.

You also will need to change the e-mail address used in the exception actions, since you probably don't want a backtrace of your exception events e-mailed to us! :)

Syncronization workflows (from OpenGroupware to your foriegn database).

NOTE: None of these flows handle removal of teams, accounts, or projects. They only process updates and creations.

OGoSyncAccounts --------------- The route populates the table ogo_account (see below) in your foriegn database with the account information from the OpenGroupware database. The account_id is the "company_id" from the OpenGroupware database and is used as the persitent identifier for an account. If account_id already exists and UPDATE is performed on ogo_account, otherwise an INSERT is performed to create the account.

CREATE TABLE ogo_account ( account_id INT PRIMARY KEY, login VARCHAR(20) NOT NULL, number VARCHAR(20), firstname VARCHAR(20), lastname VARCHAR(45));

OGoSyncTeams ------------ This route requires the ogo_account table to be current.

This route populates the ogo_team table (see below) in your foriegn database with team information from the OpenGroupware database. The team_id is the "company_id" from the OpenGroupware databse and is used as the persistent identifier for the team. If the team_id already exists an UPDATE is performed on the team name, otherwise an INSERT is performed to create the team. For each team, once the update or insert has been performed, the route retrieves a list of all members (from company_assignment in the OpenGroupware database) and syncronizes the team_membership table adding any members not present and removing any members that no longer are recorded as members in OpenGroupware.

CREATE TABLE ogo_team ( team_id INT PRIMARY KEY, name VARCHAR(60));

CREATE TABLE ogo_team_membership ( account_id INT REFERENCES ogo_account, team_id INT REFERENCES ogo_team, PRIMARY KEY(account_id, team_id));

OGoSyncProjects ---------------- This route populates the ogo_project (see below) in your forign database with project information from the OpenGroupware database. The project_id is the project_is fromt he OpenGroupware database and is used as the persistent identifier for the team. If the project_Id already exists and UPDATE is performed for the project's name and fakeness, otherwise and INSERT is performed to create the project. For each project once the update or insert has been performed the route retrieves the list of access grands (from project_company_assignment in the OpenGroupware database) and syncronizes the ogo_project_access table (see below) adding or updating any access grants and removing any grants no longer present in OpenGroupware.

CREATE TABLE ogo_project ( project_id INT PRIMARY KEY, company CHAR(1) CHECK (company == Y OR company == N), name VARCHAR(60));

CREATE TABLE ogo_project_access ( record_id SERIAL PRIMARY KEY, project_id INT REFERENCES ogo_project, object_id INT NOT NULL, permissions VARCHAR(20) NOT NULL);

NOTE: By default this route will only look an not fake (is_fake == 0) projects. But the route will handle both types, you just need to tweak the first SELECT action.