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)