...
Because we cannot beforehand know how many attribute values we need to store for a given user (multiple emails, group memberships, entitlements) we will store the AA data in a flat table that mimics a key value store.
Database CO_AA
Table attribute holds a lookup table of the attributes we support.
| Code Block |
|---|
CREATE TABLE `attribute` (
`a_id` int(11) NOT NULL,
`a_name` varchar(256) DEFAULT NULL,
`a_oid` varchar(256) DEFAULT NULL,
PRIMARY KEY (`a_id`)
) |
| Code Block |
|---|
INSERT INTO `a_type` VALUES (1,'eduPersonAffiliation','urn:oid:1.3.6.1.4.1.5923.1.1.1.1'),(2,'eduPersonEntitlement','urn:oid:1.3.6.1.4.1.5923.1.1.1.7'),(3,'eduPersonPrincipalName','urn:oid:1.3.6.1.4.1.5923.1.1.1.6'),(4,'eduPersonScopedAffiliation','urn:oid:1.3.6.1.4.1.5923.1.1.1.9'),(5,'eduPersonUniqueId','urn:oid:1.3.6.1.4.1.5923.1.1.1.13'),(6,'eduPersonOrcid','urn:oid:1.3.6.1.4.1.5923.1.1.1.16'),(7,'cn','urn:oid:2.5.4.3'),(8,'description','urn:oid:2.5.4.13'),(9,'displayName','urn:oid:2.16.840.1.113730.3.1.241'),(10,'facsimileTelephoneNumber','urn:oid:2.5.4.23'),(11,'givenName','urn:oid:2.5.4.42'),(12,'l','urn:oid:2.5.4.7'),(13,'labeledURI','urn:oid:1.3.6.1.4.1.250.1.57'),(14,'mail','urn:oid:0.9.2342.19200300.100.1.3'),(15,'mobile','urn:oid:0.9.2342.19200300.100.1.41'),(16,'o','urn:oid:2.5.4.10'),(17,'ou','urn:oid:2.5.4.11'),(18,'postalAddress','urn:oid:2.5.4.16'),(19,'postalCode','urn:oid:2.5.4.17'),(20,'sn','urn:oid:2.5.4.4'),(21,'st','urn:oid:2.5.4.8'),(22,'street','urn:oid:2.5.4.9'),(23,'telephoneNumber','urn:oid:2.5.4.20'),(24,'title','urn:oid:2.5.4.12'),(25,'uid','urn:oid:0.9.2342.19200300.100.1.1'),(26,'isMemberOf','urn:oid:1.3.6.1.4.1.5923.1.5.1.1'); |
Table user is a lookup table for the identifier of the users we have data for
| Code Block |
|---|
CREATE TABLE `user` (
`u_id` int(11) NOT NULL,
`u_identifier` varchar(256) NOT NULL,
PRIMARY KEY (`uid`),
KEY `u_identifier` (`u_identifier`)
) |
The table vo is a lookup table that holds the collaborative organisations that we have data for
| Code Block |
|---|
CREATE TABLE `vo` (
`vo_id` int(11) NOT NULL,
`vo_identifier` varchar(256) NOT NULL,
PRIMARY KEY (`vid`)
) |
The attr provides a key - value pair based table of the attributes we have per user and per VO
| Code Block |
|---|
CREATE TABLE `attr` ( `attr_values_id` int(11) NOT NULL AUTO_INCREMENT, `v`vo_id` int(11) NOT NULL, `u_id` int(11) NOT NULL, `a_id` int(11) NOT NULL, `a_val` varchar(2048) DEFAULT NULL, PRIMARY KEY (`attr_values_id`), KEY `v_id` (`v_id`,`u_id`) ) |
For the AA we need a per user, per VO representation of the attributes. To this end we create a view user_attributes that will be queried by the aa.
| Code Block |
|---|
CREATE VIEW `user_attributes` AS select `u`.`user_identifier` AS `user_identifier`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.1`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.7'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.7`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.6'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.6`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.9'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.9`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.13'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.13`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.1.1.16'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.1.1.16`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.3'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.3`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.13'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.13`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.16.840.1.113730.3.1.241'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.16.840.1.113730.3.1.241`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.23'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.23`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.42'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.42`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.7'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.7`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.250.1.57'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.250.1.57`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.3'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.3`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.41'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.41`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.10'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.10`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.11'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.11`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.16'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.16`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.17'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.17`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.4'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.4`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.8'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.8`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.9'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.9`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.20'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.20`,group_concat(if((`atype`.`a_oid` = 'urn:oid:2.5.4.12'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:2.5.4.12`,group_concat(if((`atype`.`a_oid` = 'urn:oid:0.9.2342.19200300.100.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:0.9.2342.19200300.100.1.1`,group_concat(if((`atype`.`a_oid` = 'urn:oid:1.3.6.1.4.1.5923.1.5.1.1'),`a`.`a_val`,NULL) separator ',') AS `urn:oid:1.3.6.1.4.1.5923.1.5.1.1` from (((`attr` `a` left join `a_type` `atype` on((`a`.`a_id` = `atype`.`a_id`))) left join `user` `u` on((`a`.`u_id` = `u`.`uid`))) left join `vo` `v` on((`a`.`v_id` = `v`.`vid`))) order by `atype`.`a_oid` |