COmanage Developers List

[Scott Koranda <>]

Hi,

I have Grouper 2.1.2 using COmanage r352 as its subject
source.

This is accomplished by creating a view in the database like
this:

CREATE
    VIEW cm_grouper_subjects AS
SELECT
    cm_co_people.id AS 'id',
    CONCAT(GROUP_CONCAT(DISTINCT cm_names.given),' ',GROUP_CONCAT(DISTINCT 
cm_names.family)) AS 'name',
    CONCAT(GROUP_CONCAT(DISTINCT cm_names.family),',',GROUP_CONCAT(DISTINCT 
cm_names.given)) AS 'lfname',
    CONCAT(GROUP_CONCAT(DISTINCT cm_names.given),' ',GROUP_CONCAT(DISTINCT 
cm_names.family),' (',GROUP_CONCAT(DISTINCT cm_cos.description),')') AS 
'description',
    SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT cm_identifiers.identifier),',',1) 
AS 'loginid1',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_identifiers.identifier),','),',',2),',',-1) AS 'loginid2',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_identifiers.identifier),','),',',3),',',-1) AS 'loginid3',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_identifiers.identifier),','),',',4),',',-1) AS 'loginid4',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_identifiers.identifier),','),',',5),',',-1) AS 'loginid5',
    SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT cm_email_addresses.mail),',',1) AS 
'email1',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_email_addresses.mail),','),',',2),',',-1) AS 'email2',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_email_addresses.mail),','),',',3),',',-1) AS 'email3',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_email_addresses.mail),','),',',4),',',-1) AS 'email4',
    SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(DISTINCT 
cm_email_addresses.mail),','),',',5),',',-1) AS 'email5'
FROM
    cm_co_people
    LEFT JOIN cm_names ON cm_co_people.id = cm_names.co_person_id
    LEFT JOIN cm_identifiers ON cm_co_people.id = cm_identifiers.co_person_id
    LEFT JOIN cm_email_addresses ON cm_co_people.id = 
cm_email_addresses.co_person_id
    LEFT JOIN cm_cos ON cm_co_people.co_id = cm_cos.id
GROUP BY 
    cm_co_people.id
;

It needs to take this form (or something like it) because Grouper
has the restriction that if one is going to use a JDBC source
then any query to find a subject has to return at most one
row.

This is a bit tricky if we want multiple identifiers to be associated
with the subject and available for "login", as well as multiple
email addresses or other attributes to be listed for the subject
in the Grouper UI.

The view defined above flattens out the COmanage tables so that all
identifiers and email addresses become available in a single row
associated with the subject and its canonical id (cm_co_person.id).

For example:

mysql> SELECT * FROM cm_grouper_subjects WHERE id = 3\G
*************************** 1. row ***************************
         id: 3
       name: Albert Lazzarini
     lfname: Lazzarini,Albert
description: Albert Lazzarini (LIGO Laboratory)
   loginid1: 

   loginid2: 

   loginid3: 
   loginid4: 
   loginid5: 
     email1: 

     email2: 

     email3: 

     email4: 
     email5: 

The same could probably be done with names but for now we are going
to live with the restriction that each CO person has one name.

Note that the description includes the name of the CO in the (). This
allows different CO people with the same name (the same person perhaps)
to be distinguised in the Grouper UI when trying to manage
memberships through that UI.

I am going to guess that with enough Grouper configuration magic we can
make it so that people in a CO using the Grouper UI can only see
subjects in their CO, but I am not going to worry about that level
of configuration now and will ask Chris about it in Philadelphia.

More later...

Scott

P.S. Yes, the view definition is only going to work for MySQL. My
quick research shows that an equivalent approach but with entirely
different syntax will work for PostgreSQL. Yay SQL standards (not).

P.P.S. If anyone can think of a better view definition please do
send it along...