/*
* 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 org.springframework.jdbc.core.RowMapper;
import org.springframework.util.Assert;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @deprecated use the official grouper web service instead, it supports pagination from version 2.x.x onwards.
*/
@Deprecated
public abstract class AbstractGrouperDaoImpl {
protected static String SQL_FIND_ALL_TEAMS_ROWCOUNT = "select count(distinct gg.name) "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms, "
+ " grouper_fields gf, grouper_group_set ggs "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gs.name != 'etc' "
+ "and ggs.field_id = gf.id "
+ " and gg.id = ggs.owner_group_id "
+ "and gms.owner_id = ggs.member_id "
+ " and gms.field_id = ggs.member_field_id "
+ "and ((gf.type = 'access' and gf.name = 'viewers') or gm.subject_id = ?) ";
protected static String SQL_FIND_ALL_TEAMS = "select distinct gg.name, gg.display_name ,gg.description, "
+ "gs.name as stem_name, gs.display_name as stem_display_name, gs.description as stem_description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms, "
+ " grouper_fields gf, grouper_group_set ggs "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gs.name != 'etc' "
+ " and ggs.field_id = gf.id "
+ " and gg.id = ggs.owner_group_id "
+ "and gms.owner_id = ggs.member_id "
+ " and gms.field_id = ggs.member_field_id "
+ "and ((gf.type = 'access' and gf.name = 'viewers') or gm.subject_id = ?) "
+ "order by gg.name limit ? offset ?";
protected static String SQL_FIND_TEAMS_LIKE_GROUPNAME_ROWCOUNT = "select count(distinct gg.name) "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm,"
+ "grouper_memberships gms, "
+ " grouper_fields gf, grouper_group_set ggs "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gs.name != 'etc' "
+ " and ggs.field_id = gf.id "
+ " and gg.id = ggs.owner_group_id "
+ "and gms.owner_id = ggs.member_id "
+ " and gms.field_id = ggs.member_field_id "
+ "and ((gf.type = 'access' and gf.name = 'viewers') or gm.subject_id = ?) "
+ "and upper(gg.name) like ?";
protected static String SQL_FIND_TEAMS_LIKE_GROUPNAME =
"select distinct gg.name, gg.display_name ,gg.description, gs.name as stem_name, "
+ "gs.display_name as stem_display_name, gs.description as stem_description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm,"
+ "grouper_memberships gms, "
+ " grouper_fields gf, grouper_group_set ggs "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gs.name != 'etc' "
+ " and ggs.field_id = gf.id "
+ " and gg.id = ggs.owner_group_id "
+ "and gms.owner_id = ggs.member_id "
+ " and gms.field_id = ggs.member_field_id "
+ "and ((gf.type = 'access' and gf.name = 'viewers') or gm.subject_id = ?) "
+ "and upper(gg.name) like ? order by gg.name limit ? offset ?";
protected static String SQL_FIND_TEAM_BY_MEMBER_AND_BY_GROUPNAME =
"select distinct gg.name, gg.display_name ,gg.description, gs.name as stem_name, "
+ "gs.display_name as stem_display_name, gs.description as stem_description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm,"
+ "grouper_memberships gms, "
+ " grouper_fields gf, grouper_group_set ggs "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gs.name != 'etc' "
+ " and ggs.field_id = gf.id "
+ " and gg.id = ggs.owner_group_id "
+ "and gms.owner_id = ggs.member_id "
+ " and gms.field_id = ggs.member_field_id "
+ "and (gm.subject_id = ?) "
+ "and upper(gg.name) = ?";
protected static String SQL_FIND_ALL_TEAMS_BY_MEMBER_ROWCOUNT = "select count(distinct gg.name) from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ "and gm.subject_id = ? and gs.name != 'etc'";
protected static String SQL_FIND_ALL_TEAMS_BY_MEMBER = "select distinct gg.name, gg.display_name ,gg.description, gs.name as stem_name, gs.display_name as stem_display_name, gs.description as stem_description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms, grouper_fields gf "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ "and gm.subject_id = ? "
+ "and gs.name != 'etc' "
+ "and gf.id = gms.field_id and gf.name = 'members' "
+ "order by gg.name limit ? offset ?";
protected static String SQL_FIND_ALL_TEAMS_BY_MEMBER_SORTED = "select distinct gg.name, gg.display_name ,gg.description, gs.name as stem_name, gs.display_name as stem_display_name, gs.description as stem_description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms, grouper_fields gf "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ "and gm.subject_id = ? "
+ "and gs.name != 'etc' "
+ "and gf.id = gms.field_id and gf.name = 'members' "
+ "order by gg.%s limit ? offset ?";
protected static String SQL_FIND_TEAMS_BY_MEMBER_ROWCOUNT = "select count(distinct gg.name) "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ "and gm.subject_id = ? and upper(gg.name) like ?";
protected static String SQL_FIND_TEAMS_BY_MEMBER = "select distinct gg.name, gg.display_name ,gg.description, gs.name as stem_name, gs.display_name as stem_display_name, gs.description as stem_description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms, grouper_fields gf "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ "and gm.subject_id = ? "
+ "and gs.name != 'etc' "
+ "and gf.id = gms.field_id and gf.name = 'members' "
+ "and upper(gg.name) like ? order by gg.name limit ? offset ?";
protected static String SQL_FIND_STEMS_BY_MEMBER = "select distinct gs.name, gs.display_name, gs.description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ "grouper_memberships gms "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ "and gm.subject_id = ? "
+ "and gs.name != 'etc' ";
protected static final String SQL_ROLES_BY_TEAMS = " select gf.name as fieldname, " +
"gg.name as groupname from grouper_memberships gms, "
+ "grouper_groups gg, grouper_fields gf, "
+ " grouper_stems gs, grouper_members gm where "
+ " gms.field_id = gf.id and gms.owner_group_id = gg.id and "
+ " gms.member_id = gm.id "
+ " and gm.subject_id = ? "
+ " and gg.parent_stem = gs.id "
+ " and gs.name != 'etc' "
+ " and (gf.name = 'admins' or gf.name = 'updaters') order by gg.name ";
/**
* Pad a string with SQL wildcards
* @param part the string to search for
* @return padded string
*/
protected String wildCard(String part) {
Assert.hasText(part);
part = ("%" + part + "%").toUpperCase();
return part;
}
protected static final String SQL_ROLES_BY_TEAM_AND_MEMBERS = "select gm.subject_id as subject_id, " +
"gf.name as fieldname, gg.name as groupname from grouper_memberships gms, " +
"grouper_groups gg, grouper_fields gf, grouper_stems gs, grouper_members gm " +
"where gms.field_id = gf.id and gms.owner_group_id = gg.id and gms.member_id = gm.id " +
"and gg.parent_stem = gs.id and gs.name != 'etc' and subject_id in (:identifiers) " +
"and (gf.name = 'admins' or gf.name = 'updaters') and gg.name = :groupId";
protected static final String SQL_MEMBERS_BY_TEAM = " select distinct gm.subject_id as subject_id " +
"from grouper_memberships gms, grouper_groups gg, grouper_stems gs, " +
"grouper_members gm where gms.owner_group_id = gg.id and gms.member_id = gm.id " +
"and gg.parent_stem = gs.id and gs.name != 'etc' and gm.subject_id != 'GrouperSystem' " +
"and gm.subject_id != 'GrouperAll' and gg.name = ? order by gm.subject_id limit ? offset ?";
protected static final String SQL_ADD_MEMBER_COUNT_TO_TEAMS = "select gg.name as groupname, " +
"count(distinct gms.member_id) as membercount from "
+ " grouper_groups gg, grouper_stems gs, grouper_members gm, "
+ " grouper_memberships gms "
+ " where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gm.subject_type = 'person' "
+ " and gs.name != 'etc' "
+ " and gg.id in (select distinct(ggo.id) from grouper_groups ggo, grouper_members gmo, grouper_memberships gmso "
+ " where gmso.member_id = gmo.id and gmso.owner_group_id = ggo.id and gmo.subject_id = ?) "
+ " group by gg.name ";
protected static String SQL_FIND_TEAMS_BY_GROUPIDS_ROWCOUNT =
"select count(distinct gg.name) as groupcount "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm,"
+ "grouper_memberships gms, "
+ " grouper_fields gf, grouper_group_set ggs "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gs.name != 'etc' "
+ " and ggs.field_id = gf.id "
+ " and gg.id = ggs.owner_group_id "
+ "and gms.owner_id = ggs.member_id "
+ " and gms.field_id = ggs.member_field_id "
+ "and gg.name in (:groupId)";
protected static String SQL_FIND_TEAMS_BY_GROUPIDS =
"select distinct gg.name, gg.display_name ,gg.description, gs.name as stem_name, "
+ "gs.display_name as stem_display_name, gs.description as stem_description "
+ "from grouper_groups gg, grouper_stems gs, grouper_members gm,"
+ "grouper_memberships gms, "
+ " grouper_fields gf, grouper_group_set ggs "
+ "where gg.parent_stem = gs.id and gms.member_id = gm.id and gms.owner_group_id = gg.id "
+ " and gs.name != 'etc' "
+ " and ggs.field_id = gf.id "
+ " and gg.id = ggs.owner_group_id "
+ "and gms.owner_id = ggs.member_id "
+ " and gms.field_id = ggs.member_field_id "
+ "and gg.name in (:groupId) order by gg.name limit :limit offset :offset";
/**
* Template method Row Mapper that only extracts the fields from the resultset, leaving creation
* of a concrete group to implementations.
* @param <T> the group class to create.
*/
public abstract static class GrouperRowMapper<T> implements RowMapper<T> {
public abstract T createObj(String id, String name, String description);
@Override
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
String id = rs.getString("name");
String name = rs.getString("display_name");
name = name.substring(name.lastIndexOf(':') + 1);
String description = rs.getString("description");
return createObj(id, name, description);
}
}
protected Integer correctOffset(Integer offset) {
if (offset == null) {
offset = new Integer(0);
}
return offset;
}
protected Integer correctPageSize(Integer pageSize) {
if (pageSize == null || pageSize.intValue() == 0) {
pageSize = Integer.MAX_VALUE;
}
return pageSize;
}
}