How to eliminate duplicate values in input for StoredId data connector when using RelationalDatabase data connector

[Dave Perry]

How about:
SELECT DISTINCT(principal), swissEduPersonUniqueID, mail, eduPersonEntitlement WHERE...

My SQL isn't the hottest but that should filter out the duplicate usernames (ergo uniqueID and mail)?


Dave

_________________________________________________
Dave Perry
eLearning Technologist, Hull College Group

Room L34 - Queens Gardens Library
Wilberforce Drive, Queen's Gardens, Hull, HU1 3DG
Extension 2230 / Direct Dial 01482 381930

* Need a fast reply? Try elearning at hull-college.ac.uk *

How is our service? Please rate us!
For students - http://library.hull-college.ac.uk/survey
For staff - http://library.hull-college.ac.uk/staffsurvey 

-----Original Message-----
From: users [mailto:users-bounces at shibboleth.net] On Behalf Of Daniel Lutz
Sent: 01 April 2016 16:06
To: Shib Users
Subject: Re: How to eliminate duplicate values in input for StoredId data connector when using RelationalDatabase data connector

Cantor, Scott schrieb am 01.04.16 um 16:04:
> Or re-work the queries. I didn't really understand the initial explanation.
> There are no multi-valued attributes in a database since there are no 
> attributes. The attributes just manifest from the queries, so using things like select distinct generally take care of duplication.

Here's a concrete example:


Let's consider a database view "view_attributes" that produces the following result set:

+-----------+------------------------+----------------------+-...-+-----
+-----------+------------------------+----------------------+-----------
+-----------+------------------------+----------------------+--------+
| principal | swissEduPersonUniqueID | mail                 | ... | eduPersonEntitlement   |
+-----------+------------------------+----------------------+-...-+-----
+-----------+------------------------+----------------------+-----------
+-----------+------------------------+----------------------+--------+
| student1  | 12345 at example.org      | student1 at example.org | ... | http://example.org/foo |
| student1  | 12345 at example.org      | student1 at example.org | ... | http://example.org/bar |
| student1  | 12345 at example.org      | student1 at example.org | ... | http://example.org/baz |
+-----------+------------------------+----------------------+-...-+-----
+-----------+------------------------+----------------------+-----------
+-----------+------------------------+----------------------+--------+
...

We have the following RelationalDatabase data connector:

    <resolver:DataConnector id="dbConnector" xsi:type="dc:RelationalDatabase">
	<dc:BeanManagedConnection>shibboleth.MySQLDataSource_Attributes</dc:BeanManagedConnection>
        <dc:QueryTemplate>
            <![CDATA[
                SELECT * FROM view_attributes WHERE principal = '$requestContext.principalName'
            ]]>
        </dc:QueryTemplate>
    </resolver:DataConnector>

For the principal "student1", this connector would produce the following attributes:

Attribute "principal"
  with values ["student1", "student1", "student1"]

Attribute "swissEduPersonUniqueID"
  with values ["12345 at example.org", "12345 at example.org", "12345 at example.org"]

Attribute "mail"
  with values ["student1 at example.org", "student1 at example.org", "student1 at example.org"]

Attribute "eduPersonEntitlement"
  with values ["http://example.org/foo", "http://example.org/bar", "http://example.org/baz"]


The RelationalDatabase data connector doesn't provide some option to remove duplicate values, does it?


May we need to use multiple RelationalDatabase data connectors, one per multi-valued attribute and one for the remaining single-valued attributes?

For example queries like these:

Query for a data connector producing the multi-valued attribute "eduPersonEntitlement":
  SELECT eduPersonEntitlement FROM view_attributes WHERE principal = '$requestContext.principalName' GROUP BY eduPersonEntitlement;

Query for a data connector producing the single-valued attributes:
  SELECT swissEduPersonUniqueID, mail, ... FROM view_attributes WHERE principal = '$requestContext.principalName' GROUP BY swissEduPersonUniqueID, mail, ...;




- Daniel

--
To unsubscribe from this list send an email to users-unsubscribe at shibboleth.net

**********************************************************************
This message is sent in confidence for the addressee
only. It may  contain confidential or sensitive
information.  The contents are not to be disclosed
to anyone other than the addressee.  Unauthorised
recipients are requested to preserve this
confidentiality and to advise us of any errors in
transmission.  Any views expressed in this message
are solely the views of the individual and do not
represent the views of the College.  Nothing in this
message should be construed as creating a contract.

Hull College Group owns the email infrastructure, including the contents.

Hull College Group is committed to sustainability, please reflect before printing this email.
**********************************************************************

TEXT