/*
* Copyright 2012 SURFnet bv, The Netherlands
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package nl.surfnet.coin.teams.service.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import nl.surfnet.coin.teams.domain.ConversionRule;
import nl.surfnet.coin.teams.domain.GroupProvider;
import nl.surfnet.coin.teams.domain.GroupProviderPreconditionTypes;
import nl.surfnet.coin.teams.domain.GroupProviderUserOauth;
import nl.surfnet.coin.teams.domain.ServiceProviderGroupAcl;
import nl.surfnet.coin.teams.service.GroupProviderService;
/**
* SQL implementation of {@link GroupProviderService}
*/
public class GroupProviderServiceSQLImpl implements GroupProviderService {
private static final String SELECT_USER_OAUTH = "SELECT gp_user_oauth.user_id, gp_user_oauth.provider_id, gp_user_oauth.oauth_token, gp_user_oauth.oauth_secret " +
"FROM group_provider_user_oauth as gp_user_oauth " +
"WHERE gp_user_oauth.user_id = ?";
private static final String SELECT_GROUP_PROVIDER_BY_IDENTIFIER =
"SELECT gp.id, gp.identifier, gp.name, gp.classname, gp.logo_url FROM group_provider AS gp WHERE gp.identifier ";
private static final String SELECT_ALL_GROUP_PROVIDER =
"SELECT gp.id, gp.identifier, gp.name, gp.classname, gp.logo_url FROM group_provider AS gp ";
// Cannot autowire because OpenConext-teams already has a JdbcTemplate defined for Grouper
// or change autowire by name instead of type
private final JdbcTemplate jdbcTemplate;
public GroupProviderServiceSQLImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
* {@inheritDoc}
*/
@Override
public List<GroupProviderUserOauth> getGroupProviderUserOauths(String userId) {
List<GroupProviderUserOauth> gpUserOauths;
try {
RowMapper<GroupProviderUserOauth> rowMapper = new GroupProviderUserOauthRowMapper() ;
gpUserOauths = this.jdbcTemplate.query(
SELECT_USER_OAUTH, new Object[] {userId}, rowMapper);
} catch (EmptyResultDataAccessException e) {
gpUserOauths = new ArrayList<GroupProviderUserOauth>();
}
return gpUserOauths;
}
/* (non-Javadoc)
* @see nl.surfnet.coin.teams.service.GroupProviderService#getGroupProviderUserOauths(java.lang.String, java.lang.String)
*/
@Override
public GroupProviderUserOauth getGroupProviderUserOauth(String userId, String groupProviderIdentifier) {
GroupProviderUserOauth gpUserOauth = null;
try {
RowMapper<GroupProviderUserOauth> rowMapper = new GroupProviderUserOauthRowMapper() ;
gpUserOauth = this.jdbcTemplate.queryForObject(
SELECT_USER_OAUTH + " and gp_user_oauth.provider_id = ?", new Object[] {userId, groupProviderIdentifier}, rowMapper);
} catch (EmptyResultDataAccessException e) {
//ignore, can happen
}
return gpUserOauth;
}
/**
* {@inheritDoc}
*/
@Override
public GroupProvider getGroupProviderByStringIdentifier(String identifier) {
Object[] args = {identifier};
try {
return this.jdbcTemplate.queryForObject(
SELECT_GROUP_PROVIDER_BY_IDENTIFIER + '=' + '?', args, new RowMapper<GroupProvider>() {
@Override
public GroupProvider mapRow(ResultSet rs, int rowNum) throws SQLException {
return mapRowToGroupProvider(rs);
}
});
} catch (EmptyResultDataAccessException e) {
return null;
}
}
private GroupProvider mapRowToGroupProvider(ResultSet rs) throws SQLException {
Long id = rs.getLong("id");
String identifier = rs.getString("identifier");
String name = rs.getString("name");
String gpClassName = rs.getString("classname");
GroupProvider gp = new GroupProvider(id, identifier, name, gpClassName);
String logoUrl = rs.getString("logo_url");
gp.setLogoUrl(logoUrl);
gp.setAllowedOptions(getAllowedOptions(gp));
gp.setUserIdPrecondition(getUserIdPreCondition(id));
gp.setPersonDecorators(getPersonIdDecorators(gp));
gp.setGroupDecorators(getGroupIdDecorators(gp));
gp.setPersonFilters(getPersonIdFilters(gp));
gp.setGroupFilters(getGroupIdFilters(gp));
gp.setServiceProviderGroupAcls(getServiceProviderGroupAcl(gp));
return gp;
}
private String getUserIdPreCondition(Long id) {
Object[] args = {id, GroupProviderPreconditionTypes.USER_ID_REGEX.getStringValue()};
try {
return this.jdbcTemplate.queryForObject(
"SELECT gppo.value " +
"FROM group_provider_precondition gpp " +
"LEFT JOIN group_provider_precondition_option gppo ON gpp.id = gppo.group_provider_precondition_id " +
"WHERE gpp.group_provider_id = ? AND gpp.className = ?;", args, new RowMapper<String>() {
@Override
public String mapRow(ResultSet resultSet, int i) throws SQLException {
return resultSet.getString("value");
}
});
} catch (EmptyResultDataAccessException e) {
return null;
}
}
@Override
public List<GroupProvider> getOAuthGroupProviders(String userId) {
List<GroupProvider> groupProviders;
Object[] args = {userId};
try {
// We now select the group providers that already have an oauth access token for this user.
// Later this should change into "get all OAuth group providers the user has potentially
// access to".
groupProviders = this.jdbcTemplate.query(
SELECT_GROUP_PROVIDER_BY_IDENTIFIER +
" IN (SELECT gp_user_oauth.provider_id " +
" FROM group_provider_user_oauth as gp_user_oauth " +
" WHERE gp_user_oauth.user_id = ?);", args, new RowMapper<GroupProvider>() {
@Override
public GroupProvider mapRow(ResultSet rs, int rowNum) throws SQLException {
return mapRowToGroupProvider(rs);
}
});
} catch (EmptyResultDataAccessException e) {
groupProviders = new ArrayList<GroupProvider>();
}
return groupProviders;
}
/**
* Gets the allowed options for a Group Provider
*
* @param groupProvider {@link GroupProvider}
* @return Map with allowed options
*/
private Map<String, Object> getAllowedOptions(GroupProvider groupProvider) {
Object[] args = {groupProvider.getId()};
Map<String, Object> options = new HashMap<String, Object>();
final SqlRowSet sqlRowSet = this.jdbcTemplate.queryForRowSet(
"SELECT gp_option.name, gp_option.value " +
"FROM group_provider_option AS gp_option " +
"WHERE gp_option.group_provider_id = ?;",
args);
while (sqlRowSet.next()) {
options.put(sqlRowSet.getString("name"), sqlRowSet.getObject("value"));
}
return options;
}
private List<ConversionRule> getPersonIdDecorators(GroupProvider groupProvider) {
return getDecorators(groupProvider, "EngineBlock_Group_Provider_Decorator_UserIdReplace");
}
private List<ConversionRule> getGroupIdDecorators(GroupProvider groupProvider) {
return getDecorators(groupProvider, "EngineBlock_Group_Provider_Decorator_GroupIdReplace");
}
private List<ConversionRule> getDecorators(GroupProvider groupProvider, String providerClassName) {
Object[] args = {groupProvider.getId(), providerClassName};
try {
final SqlRowSet sqlRowSet = this.jdbcTemplate.queryForRowSet(
"SELECT gpd.id," +
" gpdo.name," +
" gpdo.value " +
" FROM group_provider_decorator gpd" +
" LEFT JOIN group_provider_decorator_option gpdo ON gpd.id = gpdo.group_provider_decorator_id" +
" WHERE gpd.group_provider_id = ? AND gpd.classname = ?" +
" ORDER BY gpd.id;",
args);
return getIdConverters(sqlRowSet);
} catch (EmptyResultDataAccessException e) {
return new ArrayList<ConversionRule>();
}
}
private List<ConversionRule> getPersonIdFilters(GroupProvider groupProvider) {
return getFilters(groupProvider, "groupMember");
}
private List<ConversionRule> getGroupIdFilters(GroupProvider groupProvider) {
return getFilters(groupProvider, "group");
}
private List<ConversionRule> getFilters(GroupProvider groupProvider, String filterType) {
Object[] args = {groupProvider.getId(), filterType};
try{
final SqlRowSet sqlRowSet = this.jdbcTemplate.queryForRowSet(
"SELECT gpf.id," +
" gpf.type," +
" gpf.classname," +
" gpfo.name," +
" gpfo.value " +
" FROM group_provider_filter gpf" +
" LEFT JOIN group_provider_filter_option gpfo ON gpf.id = gpfo.group_provider_filter_id" +
" WHERE gpf.group_provider_id = ?" +
" AND gpf.type = ?" +
" ORDER BY gpf.id;",
args);
return getIdConverters(sqlRowSet);
} catch (EmptyResultDataAccessException e) {
return new ArrayList<ConversionRule>();
}
}
private List<ConversionRule> getIdConverters(SqlRowSet sqlRowSet) {
Map<Integer, ConversionRule> idConverterMap = new HashMap<Integer, ConversionRule>();
ConversionRule converter;
Integer ruleId;
final String id = "id";
final String nameCol = "name";
final String valueCol = "value";
final String name_search = "search";
final String name_replace = "replace";
final String name_property = "property";
while (sqlRowSet.next()) {
ruleId = sqlRowSet.getInt(id);
if (idConverterMap.containsKey(ruleId)) {
converter = idConverterMap.get(ruleId);
} else {
converter = new ConversionRule();
converter.setPropertyName(id);
}
final String name = sqlRowSet.getString(nameCol);
final String value = sqlRowSet.getString(valueCol);
if (name_search.equals(name)) {
converter.setSearchPattern(value);
}
if (name_replace.equals(name)) {
converter.setReplaceWith(value);
}
if (name_property.equals(name)) {
converter.setPropertyName(value);
}
idConverterMap.put(ruleId, converter);
}
return new ArrayList<ConversionRule>(idConverterMap.values());
}
private List<ServiceProviderGroupAcl> getServiceProviderGroupAcl(
GroupProvider groupProvider) {
List<ServiceProviderGroupAcl> spGroupAcls;
try {
// Get all
RowMapper<ServiceProviderGroupAcl> rowMapper = new ServiceProviderGroupAclRowMapper();
spGroupAcls = this.jdbcTemplate
.query(
"SELECT group_provider_id, spentityid, allow_groups, allow_members FROM service_provider_group_acl WHERE group_provider_id = ?",
new Object[] { groupProvider.getId() },
rowMapper);
} catch (EmptyResultDataAccessException e) {
spGroupAcls = new ArrayList<ServiceProviderGroupAcl>();
}
return spGroupAcls;
}
private class ServiceProviderGroupAclRowMapper implements RowMapper<ServiceProviderGroupAcl> {
@Override
public ServiceProviderGroupAcl mapRow(ResultSet rs, int rowNum)
throws SQLException {
long providerId = rs.getLong("group_provider_id");
String serviceProviderEntityId = rs.getString("spentityid");
boolean allowGroups = rs.getBoolean("allow_groups");
boolean allowMembers = rs.getBoolean("allow_members");
return new ServiceProviderGroupAcl(allowGroups, allowMembers,
serviceProviderEntityId, providerId);
}
}
private class GroupProviderUserOauthRowMapper implements RowMapper<GroupProviderUserOauth> {
@Override
public GroupProviderUserOauth mapRow(ResultSet rs, int rowNum) throws SQLException {
String userId = rs.getString("user_id");
String providerId = rs.getString("provider_id");
String token = rs.getString("oauth_token");
String secret = rs.getString("oauth_secret");
return new GroupProviderUserOauth(userId, providerId, token, secret);
}
}
protected void execute(String sql) {
this.jdbcTemplate.execute(sql);
}
/* (non-Javadoc)
* @see nl.surfnet.coin.teams.service.GroupProviderService#getAllGroupProviders()
*/
@Override
public List<GroupProvider> getAllGroupProviders() {
try {
return this.jdbcTemplate.query(
SELECT_ALL_GROUP_PROVIDER, new RowMapper<GroupProvider>() {
@Override
public GroupProvider mapRow(ResultSet rs, int rowNum) throws SQLException {
return mapRowToGroupProvider(rs);
}
});
} catch (EmptyResultDataAccessException e) {
return new ArrayList<GroupProvider>();
}
}
}