The view definitions on this page are provided by the community, and are not directly supported by the COmanage Project.

A common pattern for the SQL Provisioning Plugin is to build view definitions on top of the plugin's database schemas in order to provision data into other applications. This page includes some view definitions that may be helpful, either as is or as a starting point.

Grouper Subject Source (Postgres)

CREATE VIEW comanage_grouper_source AS
SELECT      sp_co_groups.name,
            sp_identifiers.identifier AS subject_id,
            'comanage' AS subject_source_id
FROM        sp_co_group_members
INNER JOIN  sp_co_groups
ON          sp_co_groups.id=sp_co_group_members.co_group_id
INNER JOIN  sp_identifiers
ON          sp_identifiers.co_person_id=sp_co_group_members.co_person_id
WHERE       sp_co_group_members.member=true
AND         sp_identifiers.type='employeenumber'
AND         sp_identifiers.status='A';

Replace employeenumber with the appropriate identifier type.


midPoint "person" Resource (Postgres)

CREATE VIEW vwPersons AS
SELECT DISTINCT co.id AS coperson_id,
      nms.given AS givenname,
      nms.family AS familyname,
      concat(nms.given, ' ', nms.family) AS fullname,
      pn.identifier AS name,
      idt.identifier AS employeenumber,
      ep.identifier AS upstreameppn,
      em.mail AS emailaddress,
      concat(nms.given, ' ', nms.family, ' (', split_part(em.mail, '@', 2), ')') AS description,
      GREATEST(co.modified, nms.modified, pn.modified, idt.modified, em.modified) AS last_modified
FROM sp_co_people co
JOIN sp_names nms ON co.id = nms.co_person_id
JOIN sp_identifiers pn ON co.id = pn.co_person_id 
   AND pn.type = 'myNameBasedIdentifier'
JOIN sp_identifiers idt ON co.id = idt.co_person_id 
   AND idt.type = 'myOpaqueIdentifier'
LEFT JOIN sp_identifiers ep ON co.id = ep.co_person_id 
   AND ep.type = 'eppn'
LEFT JOIN sp_email_addresses em ON co.id = em.co_person_id 
   AND em.type = 'official' 
   AND em.org_identity_source_id is null
WHERE co.status = 'A';
  • No labels