def search(self, spread=None, host_id=None, path=None, description=None):
"""Retrives a list of Disks filtered by the given criterias.
Returns a list of tuples with the info (disk_id, path, description).
If no criteria is given, all persons are returned. ``path`` and
``description`` should be strings if given. ``host_id`` should be int.
``spread`` can be either string or int. Wildcards * and ? are expanded
for "any chars" and "one char"."""
tables = []
where = []
tables.append("[:table schema=cerebrum name=disk_info] di")
if spread is not None:
tables.append("[:table schema=cerebrum name=entity_spread] es")
where.append("di.disk_id=es.entity_id")
where.append("es.entity_type=:entity_type")
try:
spread = int(spread)
except (TypeError, ValueError):
spread = prepare_string(spread)
tables.append("[:table schema=cerebrum name=spread_code] sc")
where.append("es.spread=sc.code")
where.append("LOWER(sc.code_str) LIKE :spread")
else:
where.append("es.spread=:spread")
if host_id is not None:
where.append("di.host_id=:host_id")
if path is not None:
path = prepare_string(path)
where.append("LOWER(di.path) LIKE :path")
if description is not None:
description = prepare_string(description)
where.append("LOWER(di.description) LIKE :description")
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
return self.query("""
SELECT DISTINCT di.disk_id AS disk_id, di.path AS path,
di.description AS description
FROM %s %s""" % (','.join(tables), where_str),
{'spread': spread, 'entity_type': int(self.const.entity_disk),
'host_id': host_id, 'path': path, 'description': description})
def search(self, feide_id=None, name=None):
tables = []
where = []
tables.append("[:table schema=cerebrum name=feide_service_info] fsi")
if name is not None:
name = prepare_string(name)
where.append("LOWER(fsi.name) LIKE :name")
if feide_id is not None:
where.append("fsi.feide_id=:feide_id")
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
return self.query(
"""
SELECT DISTINCT fsi.service_id AS service_id,
fsi.feide_id AS feide_id,
fsi.name AS name
FROM %s %s"""
% (",".join(tables), where_str),
{"feide_id": feide_id, "name": name},
)
def search(self, host_id=None, name=None, description=None):
"""Retrieves a list of Hosts filtered by the given criterias.
If no criteria is given, all hosts are returned. ``name`` and
``description`` should be strings if given. Wildcards * and ? are
expanded for "any chars" and "one char".
:return list:
A list of tuples/db_rows with fields: (host_id, name, description)
"""
where = list()
binds = dict()
query_fmt = """
SELECT DISTINCT hi.host_id, en.entity_name AS name, hi.description
FROM [:table schema=cerebrum name=host_info] hi
JOIN [:table schema=cerebrum name=entity_name] en
ON hi.host_id = en.entity_id AND
en.value_domain = [:get_constant name=host_namespace]
{where!s}
"""
if host_id is not None:
where.append(argument_to_sql(host_id, 'hi.host_id', binds, int))
if name is not None:
where.append("LOWER(en.entity_name) LIKE :name")
binds['name'] = prepare_string(name.lower())
if description is not None:
where.append("LOWER(hi.description) LIKE :desc")
binds['desc'] = prepare_string(description.lower())
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
return self.query(query_fmt.format(where=where_str), binds)
def search(self, host_id=None, name=None, description=None):
"""Retrieves a list of Hosts filtered by the given criterias.
Returns a list of tuples with the info (host_id, name, description).
If no criteria is given, all hosts are returned. ``name`` and
``description`` should be strings if given. Wildcards * and ? are
expanded for "any chars" and "one char"."""
where = []
if host_id is not None:
if isinstance(host_id, (list, tuple, set)):
where.append("host_id IN (%s)" %
", ".join(map(str, map(int, host_id))))
else:
where.append("host_id = %d" % int(host_id))
if name is not None:
name = prepare_string(name)
where.append("LOWER(en.entity_name) LIKE :name")
if description is not None:
description = prepare_string(description)
where.append("LOWER(hi.description) LIKE :description")
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
return self.query("""
SELECT DISTINCT hi.host_id, en.entity_name AS name, hi.description
FROM [:table schema=cerebrum name=host_info] hi
JOIN [:table schema=cerebrum name=entity_name] en
ON hi.host_id = en.entity_id AND
en.value_domain = [:get_constant name=host_namespace]
%s""" % where_str,
{'name': name, 'description': description})
def search(self, spread=None, filter_quarantined=False):
"""Retrives a list of OUs filtered by the given criteria.
Note that acronyms and other name variants is not a part of the basic
OU table, but could be searched for through
L{EntityNameWithLanguage.search_name_with_language}.
If no criteria is given, all OUs are returned.
"""
where = []
binds = dict()
tables = ["[:table schema=cerebrum name=ou_info] oi", ]
if spread is not None:
tables.append("[:table schema=cerebrum name=entity_spread] es")
where.append("oi.ou_id=es.entity_id")
where.append("es.entity_type=:entity_type")
binds["entity_type"] = int(self.const.entity_ou)
try:
spread = int(spread)
except (TypeError, ValueError):
spread = prepare_string(spread)
tables.append("[:table schema=cerebrum name=spread_code] sc")
where.append("es.spread=sc.code")
where.append("LOWER(sc.code_str) LIKE :spread")
else:
where.append("es.spread=:spread")
binds["spread"] = spread
if filter_quarantined:
where.append("""
(NOT EXISTS (SELECT 1
FROM
[:table schema=cerebrum name=entity_quarantine] eq
WHERE oi.ou_id=eq.entity_id))
""")
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
return self.query("""
SELECT DISTINCT oi.ou_id
FROM %s %s""" % (','.join(tables), where_str), binds)
def search(self,
group_id=None,
member_id=None,
indirect_members=False,
spread=None,
name=None,
description=None,
filter_expired=True,
creator_id=None,
expired_only=False):
"""Search for groups satisfying various filters.
Search **for groups** where the results are filtered by a number of
criteria. There are many filters that can be specified; the result
returned by this method satisfies all of the filters. Not all of the
filters are compatible (check the documentation)
If a filter is None, it means that it will not be applied. Calling
this method without any arguments will return all non-expired groups
registered in group_info.
:type group_id: int or sequence thereof or None.
:param group_id:
Group ids to look for. This is the most specific filter that can be
given. With this filter, only the groups matching the specified
id(s) will be returned.
This filter cannot be combined with L{member_id}.
:type member_id: int or sequence thereof or None.
:param member_id:
The resulting group list will be filtered by membership - only
groups that have members specified by member_id will be returned. If
member_id is a sequence, then a group g1 is returned if any of the
ids in the sequence are a member of g1.
This filter cannot be combined with L{group_id}.
:type indirect_members: bool
:param indirect_members:
This parameter controls how the L{member_id} filter is applied. When
False, only groups where L{member_id} is a/are direct member(s) will
be returned. When True, the membership of L{member_id} does not have
to be direct; if group g2 is a member of group g1, and member_id m1
is a member of g2, specifying indirect_members=True will return g1
as well as g2. Be careful, for some situations this can drastically
increase the result size.
This filter makes sense only when L{member_id} is set.
:type spread: int or SpreadCode or sequence thereof or None.
:param spread:
Filter the resulting group list by spread. I.e. only groups with
specified spread(s) will be returned.
:type name: basestring
:param name:
Filter the resulting group list by name. The name may contain SQL
wildcard characters.
:type description: basestring
:param description:
Filter the resulting group list by group description. The
description may contain SQL wildcard characters.
:type filter_expired: bool
:param filter_expired:
Filter the resulting group list by expiration date. If set, do NOT
return groups that have expired (i.e. have group_info.expire_date in
the past relative to the call time).
:type expired_only: bool
:param expired_only:
Filter the resulting group list by expiration date.
If set, return ONLY groups
that have expired_date set and expired (relative to the call time).
N.B. filter_expired and filter_expired are mutually exclusive
:rtype: iterable (yielding db-rows with group information)
:return:
An iterable (sequence or a generator) that yields successive db-rows
matching all of the specified filters. Regardless of the filters,
any given group_id is guaranteed to occur at most once in the
result. The keys available in db_rows are the content of the
group_info table and group's name (if it does not exist, None is
assigned to the 'name' key).
"""
# Sanity check: if indirect members is specified, then at least we
# need one id to go on.
if indirect_members:
assert member_id is not None
if isinstance(member_id, (list, tuple, set)):
assert member_id
# Sanity check: it is probably a bad idea to allow specifying both.
assert not (member_id and group_id)
def search_transitive_closure(member_id):
"""Return all groups where member_id is/are indirect member(s).
:type member_id: int or sequence thereof.
:param member_id:
We are looking for groups where L{member_id} is/are indirect
member(s).
:rtype: set (of group_ids (ints))
:result:
Set of group_ids where member_id is/are indirect members.
"""
result = set()
if not isinstance(member_id, (tuple, set, list)):
member_id = (member_id,)
# workset contains ids of the entities that are members. in each
# iteration we are looking for direct parents of whatever is in
# workset.
workset = set([int(x) for x in member_id])
while workset:
tmp = workset
workset = set()
for row in self.search(member_id=tmp,
indirect_members=False,
# We need to be *least* restrictive
# here. Final filtering will take care
# of 'expiredness'.
filter_expired=False):
group_id = int(row["group_id"])
if group_id in result:
continue
result.add(group_id)
if group_id not in workset:
workset.add(group_id)
return result
# end search_transitive_closure
select = """SELECT DISTINCT gi.group_id AS group_id,
en.entity_name AS name,
gi.description AS description,
gi.visibility AS visibility,
gi.creator_id AS creator_id,
ei.created_at AS created_at,
gi.expire_date AS expire_date
"""
tables = ["""[:table schema=cerebrum name=group_info] gi
LEFT OUTER JOIN
[:table schema=cerebrum name=entity_name] en
ON
en.entity_id = gi.group_id AND
en.value_domain = :vdomain
LEFT OUTER JOIN
[:table schema=cerebrum name=entity_info] ei
ON
ei.entity_id = gi.group_id AND
ei.entity_type = :entity_type
""", ]
where = list()
binds = {"vdomain": int(self.const.group_namespace),
"entity_type": int(self.const.entity_group)}
#
# group_id filter
if group_id is not None:
where.append(argument_to_sql(group_id, "gi.group_id", binds, int))
#
# member_id filters (all of them)
if member_id is not None:
if indirect_members:
# NB! This can be a very large group set.
group_ids = search_transitive_closure(member_id)
if not group_ids:
return []
where.append(argument_to_sql(group_ids, "gi.group_id", binds,
int))
else:
tables.append("[:table schema=cerebrum name=group_member] gm")
where.append("(gi.group_id = gm.group_id)")
where.append(argument_to_sql(member_id, "gm.member_id",
binds, int))
#
# spread filter
if spread is not None:
tables.append("[:table schema=cerebrum name=entity_spread] es")
where.append("(gi.group_id = es.entity_id)")
where.append(argument_to_sql(spread, "es.spread", binds, int))
#
# name filter
if name is not None:
name = prepare_string(name)
where.append("(LOWER(en.entity_name) LIKE :name)")
binds["name"] = name
# description filter
if description is not None:
description = prepare_string(description)
where.append("(LOWER(gi.description) LIKE :description)")
binds["description"] = description
#
# expired filter
if filter_expired:
where.append("(gi.expire_date IS NULL OR gi.expire_date > [:now])")
#
# creator_id filter
if creator_id is not None:
where.append(argument_to_sql(creator_id, "gi.creator_id", binds,
int))
#
# expired_only filter
if expired_only:
where.append("(gi.expire_date IS NOT NULL AND gi.expire_date < "
"[:now])")
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
query_str = "%s FROM %s %s" % (select, ", ".join(tables), where_str)
# IVR 2008-07-09 Originally the idea was to use a generator to avoid
# caching all rows in memory. Unfortunately, setting fetchall=False
# causes an ungodly amount of sql statement reparsing, which leads to
# an abysmal perfomance penalty.
return self.query(query_str, binds, fetchall=True)
def search(self, entity_id=None, entity_type=None, description=None,
name=None, create_start=None, create_end=None, foundation=None,
foundation_start=None, foundation_end=None):
"""Search for components that satisfy given criteria.
@type component_id: int or sequence of ints.
@param component_id:
Component ids to search for. If given, only the given components
are returned.
@type entity_type: int or sequence of ints.
@param entity_type:
If given, only components of the given type(s) are returned.
@type description: basestring
@param description:
Filter the results by their description. May contain SQL wildcard
characters.
@type foundation: basestring
@param foundation:
Filter the results by their foundation variable. May contain SQL
wildcard characters.
@rtype: iterable db-rows
@return:
An iterable with db-rows with information about each component
that matched the given criterias.
"""
# TODO: add fetchall as an option?
where = ['en.entity_id = co.component_id']
where.append('ei.entity_id = co.component_id')
binds = dict()
# TODO: what about the namespace?
if entity_type is not None:
where.append(argument_to_sql(entity_type, 'co.entity_type',
binds, int))
if description is not None:
where.append('(LOWER(co.description) LIKE :description)')
binds['description'] = prepare_string(description)
if foundation is not None:
where.append('(LOWER(co.foundation) LIKE :foundation)')
binds['foundation'] = prepare_string(foundation)
if name is not None:
where.append('(LOWER(en.entity_name) LIKE :name)')
binds['name'] = prepare_string(name)
if create_start is not None:
where.append("(ei.created_at >= :create_start)")
binds['create_start'] = create_start
if create_end is not None:
where.append("(ei.created_at <= :create_end)")
binds['create_end'] = create_end
if foundation_start is not None:
where.append("(co.foundation_date >= :foundation_start)")
binds['foundation_start'] = foundation_start
if foundation_end is not None:
where.append("(co.foundation_date <= :foundation_end)")
binds['foundation_end'] = foundation_end
return self.query("""
SELECT DISTINCT co.entity_type AS entity_type,
co.component_id AS component_id,
co.description AS description,
co.foundation AS foundation,
ei.created_at AS created_at,
co.foundation_date AS foundation_date,
en.entity_name AS name
FROM
[:table schema=cerebrum name=hostpolicy_component] co,
[:table schema=cerebrum name=entity_name] en,
[:table schema=cerebrum name=entity_info] ei
WHERE
%(where)s
""" % {'where': ' AND '.join(where)}, binds)
def search_hostpolicies(self, policy_id=None, policy_type=None,
dns_owner_id=None, host_name=None,
indirect_relations=False):
"""Search for hostpolicy relationships matching given criterias. By
relationships we here mean policies "attached" to hosts.
If a criteria is None, it will be ignored. Calling the method without
any argument will simply return all hostpolicy relationships from the
database.
@type policy_id: int or a sequence of ints
@param policy_id:
The policy component IDs to search for. Only hostpolicies related
to the given policies will be returned.
Note that if indirect_relations is True, the given policies' parent
policies are included in the search, since the given policies could
be indirectly related to hosts through their parents.
@type policy_type: int/EntityType or a sequence of ints/EntityTypes
@param policy_type:
Filter the result by policies type. Useful if you for instance only
are interested in atoms and not roles.
@type dns_owner_id: int or sequence of ints
@param dns_owner_id:
Filter the search to only return hostpolicies related to the given
host IDs.
Note that if indirect_relations is set to True, the hosts'
policies' children are also searched through, since these are
indirectly related to the given hosts.
@type host_name: string
@param host_name:
A string for matching host's entity_name.
@type indirect_relations: bool
@param indirect_relations:
If the search should find matches recursively. If this is True and
policy_id is set, it will also search through the given policies'
parents - useful for getting a list of hosts which has the given
policy eiter as a direct or indirect policy. If dns_owner_id is
given, it will search through the given host's policies and these
policies' children - useful for getting a complete list of all
policies attached to given hosts.
TODO: can both policy id and dns_owner_id be given when searching
indirectly?
@rtype: generator of db-rows
@return:
A generator yielding successive db-rows. The keys for the db-rows
are:
- entity_type - The policy's entity type
- policy_id
- policy_name
- dns_owner_id
- dns_owner_name
"""
where = ['co.component_id = hp.policy_id',
'hp.dns_owner_id = dnso.dns_owner_id',
'en1.entity_id = hp.dns_owner_id',
'en2.entity_id = hp.policy_id', ]
binds = dict()
if policy_id is not None:
if indirect_relations:
# Search recursively by just adding all policy_ids of policies
# that contains the given policy_ids.
if not isinstance(policy_id, (tuple, set, list)):
policy_id = (policy_id,)
# making it a set to avoid searching for same policy twice
policy_id = set(policy_id)
policy_id.update(
row['source_id'] for row in self.search_relations(
target_id=policy_id,
relationship_code=self.const.hostpolicy_contains,
indirect_relations=True))
where.append(
argument_to_sql(policy_id, 'hp.policy_id', binds, int))
if dns_owner_id is not None:
if indirect_relations:
# One way to do this is to fetch all the policies directly
# attached to the given host(s), and then get their children.
# How can this be given correctly?
#
# TODO: How to do this recursively?
#
raise Exception(
'Recursive search by host is not implemented yet')
where.append(
argument_to_sql(dns_owner_id, 'hp.dns_owner_id', binds, int))
if host_name is not None:
if indirect_relations:
# TODO: How to do this recursively?
raise Exception(
'Recursive search by host is not implemented yet')
where.append('(LOWER(en1.entity_name) LIKE :host_name)')
binds['host_name'] = prepare_string(host_name)
if policy_type is not None:
where.append(argument_to_sql(policy_type, 'co.entity_type', binds,
int))
return self.query("""
SELECT DISTINCT
co.entity_type AS entity_type,
hp.dns_owner_id AS dns_owner_id,
en1.entity_name AS dns_owner_name,
en2.entity_name AS policy_name,
hp.policy_id AS policy_id
FROM
[:table schema=cerebrum name=hostpolicy_component] co,
[:table schema=cerebrum name=hostpolicy_host_policy] hp,
[:table schema=cerebrum name=dns_owner] dnso,
[:table schema=cerebrum name=entity_name] en1,
[:table schema=cerebrum name=entity_name] en2
WHERE
%(where)s""" % {'where': ' AND '.join(where)}, binds)
def search_hostpolicies(self,
policy_id=None,
policy_type=None,
dns_owner_id=None,
host_name=None,
indirect_relations=False):
"""Search for hostpolicy relationships matching given criterias. By
relationships we here mean policies "attached" to hosts.
If a criteria is None, it will be ignored. Calling the method without
any argument will simply return all hostpolicy relationships from the
database.
@type policy_id: int or a sequence of ints
@param policy_id:
The policy component IDs to search for. Only hostpolicies related
to the given policies will be returned.
Note that if indirect_relations is True, the given policies' parent
policies are included in the search, since the given policies could
be indirectly related to hosts through their parents.
@type policy_type: int/EntityType or a sequence of ints/EntityTypes
@param policy_type:
Filter the result by policies type. Useful if you for instance only
are interested in atoms and not roles.
@type dns_owner_id: int or sequence of ints
@param dns_owner_id:
Filter the search to only return hostpolicies related to the given
host IDs.
Note that if indirect_relations is set to True, the hosts'
policies' children are also searched through, since these are
indirectly related to the given hosts.
@type host_name: string
@param host_name:
A string for matching host's entity_name.
@type indirect_relations: bool
@param indirect_relations:
If the search should find matches recursively. If this is True and
policy_id is set, it will also search through the given policies'
parents - useful for getting a list of hosts which has the given
policy eiter as a direct or indirect policy. If dns_owner_id is
given, it will search through the given host's policies and these
policies' children - useful for getting a complete list of all
policies attached to given hosts.
TODO: can both policy id and dns_owner_id be given when searching
indirectly?
@rtype: generator of db-rows
@return:
A generator yielding successive db-rows. The keys for the db-rows
are:
- entity_type - The policy's entity type
- policy_id
- policy_name
- dns_owner_id
- dns_owner_name
"""
where = [
'co.component_id = hp.policy_id',
'hp.dns_owner_id = dnso.dns_owner_id',
'en1.entity_id = hp.dns_owner_id',
'en2.entity_id = hp.policy_id',
]
binds = dict()
if policy_id is not None:
if indirect_relations:
# Search recursively by just adding all policy_ids of policies
# that contains the given policy_ids.
if not isinstance(policy_id, (tuple, set, list)):
policy_id = (policy_id, )
# making it a set to avoid searching for same policy twice
policy_id = set(policy_id)
policy_id.update(
row['source_id'] for row in self.search_relations(
target_id=policy_id,
relationship_code=self.const.hostpolicy_contains,
indirect_relations=True))
where.append(argument_to_sql(policy_id, 'hp.policy_id', binds,
int))
if dns_owner_id is not None:
if indirect_relations:
# One way to do this is to fetch all the policies directly
# attached to the given host(s), and then get their children.
# How can this be given correctly?
#
# TODO: How to do this recursively?
#
raise Exception(
'Recursive search by host is not implemented yet')
where.append(
argument_to_sql(dns_owner_id, 'hp.dns_owner_id', binds, int))
if host_name is not None:
if indirect_relations:
# TODO: How to do this recursively?
raise Exception(
'Recursive search by host is not implemented yet')
where.append('(LOWER(en1.entity_name) LIKE :host_name)')
binds['host_name'] = prepare_string(host_name)
if policy_type is not None:
where.append(
argument_to_sql(policy_type, 'co.entity_type', binds, int))
return self.query(
"""
SELECT DISTINCT
co.entity_type AS entity_type,
hp.dns_owner_id AS dns_owner_id,
en1.entity_name AS dns_owner_name,
en2.entity_name AS policy_name,
hp.policy_id AS policy_id
FROM
[:table schema=cerebrum name=hostpolicy_component] co,
[:table schema=cerebrum name=hostpolicy_host_policy] hp,
[:table schema=cerebrum name=dns_owner] dnso,
[:table schema=cerebrum name=entity_name] en1,
[:table schema=cerebrum name=entity_name] en2
WHERE
%(where)s""" % {'where': ' AND '.join(where)}, binds)
def search(self,
group_id=None,
member_id=None,
indirect_members=False,
spread=None,
name=None,
description=None,
filter_expired=True,
creator_id=None,
expired_only=False):
"""Search for groups satisfying various filters.
Search **for groups** where the results are filtered by a number of
criteria. There are many filters that can be specified; the result
returned by this method satisfies all of the filters. Not all of the
filters are compatible (check the documentation)
If a filter is None, it means that it will not be applied. Calling
this method without any arguments will return all non-expired groups
registered in group_info.
:type group_id: int or sequence thereof or None.
:param group_id:
Group ids to look for. This is the most specific filter that can be
given. With this filter, only the groups matching the specified
id(s) will be returned.
This filter cannot be combined with L{member_id}.
:type member_id: int or sequence thereof or None.
:param member_id:
The resulting group list will be filtered by membership - only
groups that have members specified by member_id will be returned. If
member_id is a sequence, then a group g1 is returned if any of the
ids in the sequence are a member of g1.
This filter cannot be combined with L{group_id}.
:type indirect_members: bool
:param indirect_members:
This parameter controls how the L{member_id} filter is applied. When
False, only groups where L{member_id} is a/are direct member(s) will
be returned. When True, the membership of L{member_id} does not have
to be direct; if group g2 is a member of group g1, and member_id m1
is a member of g2, specifying indirect_members=True will return g1
as well as g2. Be careful, for some situations this can drastically
increase the result size.
This filter makes sense only when L{member_id} is set.
:type spread: int or SpreadCode or sequence thereof or None.
:param spread:
Filter the resulting group list by spread. I.e. only groups with
specified spread(s) will be returned.
:type name: basestring
:param name:
Filter the resulting group list by name. The name may contain SQL
wildcard characters.
:type description: basestring
:param description:
Filter the resulting group list by group description. The
description may contain SQL wildcard characters.
:type filter_expired: bool
:param filter_expired:
Filter the resulting group list by expiration date. If set, do NOT
return groups that have expired (i.e. have group_info.expire_date in
the past relative to the call time).
:type expired_only: bool
:param expired_only:
Filter the resulting group list by expiration date.
If set, return ONLY groups
that have expired_date set and expired (relative to the call time).
N.B. filter_expired and filter_expired are mutually exclusive
:rtype: iterable (yielding db-rows with group information)
:return:
An iterable (sequence or a generator) that yields successive db-rows
matching all of the specified filters. Regardless of the filters,
any given group_id is guaranteed to occur at most once in the
result. The keys available in db_rows are the content of the
group_info table and group's name (if it does not exist, None is
assigned to the 'name' key).
"""
# Sanity check: if indirect members is specified, then at least we
# need one id to go on.
if indirect_members:
assert member_id is not None
if isinstance(member_id, (list, tuple, set)):
assert member_id
# Sanity check: it is probably a bad idea to allow specifying both.
assert not (member_id and group_id)
def search_transitive_closure(member_id):
"""Return all groups where member_id is/are indirect member(s).
:type member_id: int or sequence thereof.
:param member_id:
We are looking for groups where L{member_id} is/are indirect
member(s).
:rtype: set (of group_ids (ints))
:result:
Set of group_ids where member_id is/are indirect members.
"""
result = set()
if not isinstance(member_id, (tuple, set, list)):
member_id = (member_id,)
# workset contains ids of the entities that are members. in each
# iteration we are looking for direct parents of whatever is in
# workset.
workset = set([int(x) for x in member_id])
while workset:
tmp = workset
workset = set()
for row in self.search(member_id=tmp,
indirect_members=False,
# We need to be *least* restrictive
# here. Final filtering will take care
# of 'expiredness'.
filter_expired=False):
group_id = int(row["group_id"])
if group_id in result:
continue
result.add(group_id)
if group_id not in workset:
workset.add(group_id)
return result
# end search_transitive_closure
select = """SELECT DISTINCT gi.group_id AS group_id,
en.entity_name AS name,
gi.description AS description,
gi.visibility AS visibility,
gi.creator_id AS creator_id,
ei.created_at AS created_at,
gi.expire_date AS expire_date
"""
tables = ["""[:table schema=cerebrum name=group_info] gi
LEFT OUTER JOIN
[:table schema=cerebrum name=entity_name] en
ON
en.entity_id = gi.group_id AND
en.value_domain = :vdomain
LEFT OUTER JOIN
[:table schema=cerebrum name=entity_info] ei
ON
ei.entity_id = gi.group_id AND
ei.entity_type = :entity_type
""", ]
where = list()
binds = {"vdomain": int(self.const.group_namespace),
"entity_type": int(self.const.entity_group)}
#
# group_id filter
if group_id is not None:
where.append(argument_to_sql(group_id, "gi.group_id", binds, int))
#
# member_id filters (all of them)
if member_id is not None:
if indirect_members:
# NB! This can be a very large group set.
group_ids = search_transitive_closure(member_id)
if not group_ids:
return []
where.append(argument_to_sql(group_ids, "gi.group_id", binds,
int))
else:
tables.append("[:table schema=cerebrum name=group_member] gm")
where.append("(gi.group_id = gm.group_id)")
where.append(argument_to_sql(member_id, "gm.member_id",
binds, int))
#
# spread filter
if spread is not None:
tables.append("[:table schema=cerebrum name=entity_spread] es")
where.append("(gi.group_id = es.entity_id)")
where.append(argument_to_sql(spread, "es.spread", binds, int))
#
# name filter
if name is not None:
name = prepare_string(name)
where.append("(LOWER(en.entity_name) LIKE :name)")
binds["name"] = name
# description filter
if description is not None:
description = prepare_string(description)
where.append("(LOWER(gi.description) LIKE :description)")
binds["description"] = description
#
# expired filter
if filter_expired:
where.append("(gi.expire_date IS NULL OR gi.expire_date > [:now])")
#
# creator_id filter
if creator_id is not None:
where.append(argument_to_sql(creator_id, "gi.creator_id", binds,
int))
#
# expired_only filter
if expired_only:
where.append("(gi.expire_date IS NOT NULL AND gi.expire_date < "
"[:now])")
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
query_str = "%s FROM %s %s" % (select, ", ".join(tables), where_str)
# IVR 2008-07-09 Originally the idea was to use a generator to avoid
# caching all rows in memory. Unfortunately, setting fetchall=False
# causes an ungodly amount of sql statement reparsing, which leads to
# an abysmal perfomance penalty.
return self.query(query_str, binds, fetchall=True)
