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

[Daniel Lutz]

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