def search(self, owner_entity_id=None, owner_entity_type=None):
"""Search for voip_addresses matching the filtering criteria."""
where = [
"va.owner_entity_id = ei.entity_id",
]
binds = dict()
if owner_entity_id is not None:
where.append(
argument_to_sql(owner_entity_id, "va.owner_entity_id", binds,
int))
if owner_entity_type is not None:
where.append(
argument_to_sql(owner_entity_type, "ei.entity_type", binds,
int))
if where:
where = "WHERE " + " AND ".join(where)
else:
where = ""
return self.query(
"""
SELECT va.entity_id,
va.owner_entity_id, ei.entity_type as owner_entity_type
FROM [:table schema=cerebrum name=voip_address] va,
[:table schema=cerebrum name=entity_info] ei
""" + where, binds)
def search(self, entity_id=None, description=None, service_type=None,
ou_id=None):
"""Search for voip_services matching the filtering criteria."""
where = list()
binds = dict()
if entity_id is not None:
where.append(argument_to_sql(description, "vs.entity_id", binds,
int))
if description is not None:
where.append(argument_to_sql(description, "vs.description", binds))
if service_type is not None:
where.append(argument_to_sql(service_type, "vs.service_type",
binds, int))
if ou_id is not None:
where.append(argument_to_sql(ou_id, "vs.ou_id", binds, int))
if where:
where = "WHERE " + " AND ".join(where)
else:
where = ""
return self.query("""
SELECT entity_id, description, service_type, ou_id
FROM [:table schema=cerebrum name=voip_service] vs
""" + where, binds)
def get_pending_events(self, types=None,
subject_entity=None,
confirmation_key=None):
"""Short version of get_log_events that carries the information about
the pending requests as well as the change_log entries.
"""
where = list()
binds = dict()
if types is not None:
where.append(argument_to_sql(types, "cl.change_type_id", binds, int))
if subject_entity is not None:
where.append(argument_to_sql(subject_entity,
"cl.subject_entity",
binds, int))
if confirmation_key is not None:
where.append(argument_to_sql(confirmation_key,
"pcl.confirmation_key",
binds))
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
return self.query("""
SELECT cl.*, pcl.confirmation_key
FROM [:table schema=cerebrum name=change_log] cl
JOIN [:table schema=cerebrum name=pending_change_log] pcl
ON cl.change_id = pcl.change_id
%s
""" % where_str, binds)
def search(self, identifier=None, account_id=None, description=None):
"""
Get apikey value for a given account.
:rtype: six.text_type
:raises ValueError: if no account_id is given
:raises Cerebrum.Errors.NotFoundError: if no apikey exists
"""
binds = {}
filters = []
if account_id is not None:
filters.append(
argument_to_sql(account_id, 'account_id', binds, int))
if identifier is not None:
filters.append(
argument_to_sql(identifier, 'identifier', binds,
six.text_type))
if description is not None:
pass
# TODO: Implement LIKE matching
stmt = """
SELECT *
FROM [:table schema=cerebrum name=apikey_client_map]
{where}
ORDER BY updated_at
""".format(where=('WHERE ' + ' AND '.join(filters)) if filters else '')
return self.query(stmt, binds)
def is_moderator(self, moderator_id, group_id=None):
"""
Function to determine wheter an Entity is an moderator of a group in
general, or of one particular group, if specified. Either directly or
through membership in a moderator group
:param int moderator_id:
Entity id to look after moderatorship for
:param int group_id:
If specified, the moderatorship needs to be for this particular group
:return bool:
Whether the entity is an moderator (of the specified group)
"""
binds = {}
where = []
where.append(argument_to_sql(moderator_id, "moderator_id", binds, int))
if group_id is not None:
where.append(argument_to_sql(group_id, "group_id", binds, int))
exists_stmt = """
SELECT EXISTS (
SELECT 1
FROM [:table schema=cerebrum name=group_moderator]
WHERE {where}
)
""".format(where=" AND ".join(where))
return self.query_1(exists_stmt, binds)
def search(self,
entity_id=None,
description=None,
service_type=None,
ou_id=None):
"""Search for voip_services matching the filtering criteria."""
where = list()
binds = dict()
if entity_id is not None:
where.append(
argument_to_sql(description, "vs.entity_id", binds, int))
if description is not None:
where.append(argument_to_sql(description, "vs.description", binds))
if service_type is not None:
where.append(
argument_to_sql(service_type, "vs.service_type", binds, int))
if ou_id is not None:
where.append(argument_to_sql(ou_id, "vs.ou_id", binds, int))
if where:
where = "WHERE " + " AND ".join(where)
else:
where = ""
return self.query(
"""
SELECT entity_id, description, service_type, ou_id
FROM [:table schema=cerebrum name=voip_service] vs
""" + where, binds)
def get_pending_events(self,
types=None,
subject_entity=None,
confirmation_key=None):
"""Short version of get_log_events that carries the information about
the pending requests as well as the change_log entries.
"""
where = list()
binds = dict()
if types is not None:
where.append(
argument_to_sql(types, "cl.change_type_id", binds, int))
if subject_entity is not None:
where.append(
argument_to_sql(subject_entity, "cl.subject_entity", binds,
int))
if confirmation_key is not None:
where.append(
argument_to_sql(confirmation_key, "pcl.confirmation_key",
binds))
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
return self.query(
"""
SELECT cl.*, pcl.confirmation_key
FROM [:table schema=cerebrum name=change_log] cl
JOIN [:table schema=cerebrum name=pending_change_log] pcl
ON cl.change_id = pcl.change_id
%s
""" % where_str, binds)
def get_permission_holders_on_groups(self, op_set_id, group_id=None, account_id=None):
"""Collect all account-with-permissions-on-group satisfying certain criteria.
The idea is to figure out who has permission set represented by
opset_id on which group, in order to answer questions like 'List all
moderators of this group' or 'which groups do I own?'. The method is
generalised to accept multiple account_ids/group_ids/op_set_ids.
@type op_set_id: int or a non-empty sequence thereof.
FIXME: Should the interface be nicer and allow BofhdAuthOpSet
instances?
FIXME: BofhdAuthRole.list() could be (should be?) fixed to perform
this task.
@type group_id: int or a non-empty sequence thereof.
@type account_id: int or a non-empty sequence thereof
@return:
An iterable over db-rows with entity_ids of the permission
holders. (FIXME: do we want entity_types to go with entity_ids?)
"""
assert not (group_id and account_id), "Cannot specify both"
binds = {
"target_type": self.const.auth_target_type_group,
"domain": self.const.group_namespace,
"domain2": self.const.account_namespace,
}
where = [argument_to_sql(op_set_id, "ar.op_set_id", binds, int)]
if group_id is not None:
where.append(argument_to_sql(group_id, "aot.entity_id", binds, int))
elif account_id is not None:
where.append(argument_to_sql(account_id, "ar.entity_id", binds, int))
query = (
"""
SELECT DISTINCT ar.entity_id as account_id,
aot.entity_id as group_id,
en.entity_name as group_name,
en2.entity_name as account_name,
gi.description
FROM [:table schema=cerebrum name=auth_role] ar
JOIN [:table schema=cerebrum name=auth_op_target] aot
ON ar.op_target_id = aot.op_target_id AND
aot.target_type = :target_type AND"""
+ " AND ".join(where)
+ """
JOIN [:table schema=cerebrum name=group_info] gi
ON aot.entity_id = gi.group_id
LEFT OUTER JOIN [:table schema=cerebrum name=entity_name] en
ON en.entity_id = aot.entity_id AND
en.value_domain = :domain
LEFT OUTER JOIN [:table schema=cerebrum name=entity_name] en2
ON en2.entity_id = ar.entity_id AND
en2.value_domain = :domain2
"""
)
return list(x.dict() for x in self.query(query, binds))
def sql_search(db,
change_types=None,
operators=None,
entities=None,
targets=None,
record_ids=None,
after_id=None,
before_id=None,
after_timestamp=None,
before_timestamp=None,
fetchall=False):
""" TODO: document """
query = """
SELECT *
FROM [:table schema=cerebrum name=audit_log]
"""
clauses = []
binds = {}
#
# value selects
#
if record_ids:
clauses.append(argument_to_sql(record_ids, 'record_id', binds, int))
if change_types:
clauses.append(argument_to_sql(change_types, 'change_type', binds,
int))
if operators:
clauses.append(argument_to_sql(operators, 'operator', binds, int))
if entities:
clauses.append(argument_to_sql(entities, 'entity', binds, int))
if targets:
clauses.append(argument_to_sql(targets, 'target', binds, int))
#
# range selects
#
if after_timestamp is not None:
clauses.append("timestamp > :after_ts")
binds['after_ts'] = after_timestamp
if before_timestamp is not None:
clauses.append("timestamp < :before_ts")
binds['before_timestamp'] = before_timestamp
if after_id is not None:
clauses.append("record_id > :after_id")
binds['after_id'] = int(after_id)
if before_id is not None:
clauses.append("record_id < :before_id")
binds['before_id'] = int(before_id)
if clauses:
where = ' WHERE ' + ' AND '.join(clauses)
else:
where = ''
return db.query(query + where, binds, fetchall=fetchall)
def search(self,
username=None,
ssn=None,
source=None,
type=None,
comment=None,
name=None):
"""
Search for legacy usernames.
:type user_name: str
:param user_name: Find entries matching this username.
:type ssn: str
:param ssn: Find entry matching this norwegian national id.
:type source: str
:param source: Find entries matching this source.
:type type: str
:param source: Find entries matching this type.
:type comment: str
:param comment: Find entries with this comment. Supports wildcards.
:type name: str
:param name: Find entries with this owner name. Supports wildcards.
"""
filters = []
binds = dict()
if username:
filters.append(argument_to_sql(username, 'user_name', binds))
if ssn:
filters.append(argument_to_sql(ssn, 'ssn', binds))
if source:
filters.append(argument_to_sql(source, 'source', binds))
if type:
filters.append(argument_to_sql(type, 'type', binds))
if comment:
filters.append('comment like :comment')
binds['comment'] = prepare_string(comment)
if name:
filters.append('name like :name')
binds['name'] = prepare_string(name)
where = ('WHERE ' + ' AND '.join(filters)) if filters else ''
stmt = """
SELECT user_name, ssn, source, type, comment, name
FROM [:table schema=cerebrum name=legacy_users]
{where}
""".format(where=where)
return self.query(stmt, binds)
def search(self,
entity_id=None,
tag=None,
recipient=None,
message_id=None):
"""
Find messages for some given entities, tags, or recipients.
:param entity_id:
an entity_id or sequence of entity_ids to find
:param tag:
a tag or sequence of tags to find
:param recipient:
a recipient or sequence of recipients to find
:param message_id:
a message_id or sequence of message_ids to find
:return:
rows of matching messages.
Messages are ordered by entity_id, tag, recipient, created.
"""
binds = {}
conds = []
if entity_id is not None:
conds.append(argument_to_sql(entity_id, 'entity_id', binds, int))
if tag is not None:
conds.append(argument_to_sql(tag, 'tag', binds, six.text_type))
if recipient is not None:
conds.append(
argument_to_sql(recipient, 'recipient', binds, six.text_type))
if message_id is not None:
conds.append(argument_to_sql(message_id, 'message_id', binds, int))
stmt = """
SELECT
{columns}
FROM
[:table schema=cerebrum name=entity_gpg_data]
{where}
ORDER BY
entity_id, tag, recipient, created DESC
""".format(where='WHERE ' + ' AND '.join(conds) if conds else '',
columns=', '.join(self.columns))
return self.query(stmt, binds)
def list_consents(self,
consent_code=None,
entity_type=None,
consent_type=None,
entity_id=None,
filter_expired=True):
"""List all entities filtered by argument.
Note: consent_code, entity_type, consent_type and entity_id can also be
a tuple, set or list of the type specified below.
:type consent_code: Constants.EntityConsent
:param consent_code: The consent code(s) corresponding to proposition.
:type entity_type: Constants.EntityType
:param entity_type: Filter for entity_type(s) (part of consent code).
:type consent_type: Constants.ConsentType
:param consent_type: The type(s) of consents to list.
:type entity_id: int
:param entity_id: List consents for given entity(ies).
:type filter_expired: Bool
:param filter_expired: Iff true, remove expired consents.
:returns: List of db rows.
"""
filters = set()
args = {}
if consent_code:
filters.add(
argument_to_sql(consent_code, 'consent_code', args, int))
if entity_type:
filters.add(argument_to_sql(entity_type, 'entity_type', args, int))
if consent_type:
filters.add(
argument_to_sql(consent_type, 'consent_type', args, int))
if entity_id:
filters.add(argument_to_sql(entity_id, 'entity_id', args))
if filter_expired:
filters.add('(expiry is null or expiry < [:now])')
sql = """SELECT entity_consent.*
FROM [:table schema=cerebrum name=entity_consent]
INNER JOIN [:table schema=cerebrum name=entity_consent_code]
ON consent_code = code"""
if filters:
sql += " WHERE " + " AND ".join(filters)
return self.query(sql, args)
def list_consents(self, consent_code=None, entity_type=None,
consent_type=None, entity_id=None,
filter_expired=True):
"""List all entities filtered by argument.
Note: consent_code, entity_type, consent_type and entity_id can also be
a tuple, set or list of the type specified below.
:type consent_code: Constants.EntityConsent
:param consent_code: The consent code(s) corresponding to proposition.
:type entity_type: Constants.EntityType
:param entity_type: Filter for entity_type(s) (part of consent code).
:type consent_type: Constants.ConsentType
:param consent_type: The type(s) of consents to list.
:type entity_id: int
:param entity_id: List consents for given entity(ies).
:type filter_expired: Bool
:param filter_expired: Iff true, remove expired consents.
:returns: List of db rows.
"""
filters = set()
args = {}
if consent_code:
filters.add(
argument_to_sql(consent_code, 'consent_code', args, int))
if entity_type:
filters.add(
argument_to_sql(entity_type, 'entity_type', args, int))
if consent_type:
filters.add(
argument_to_sql(consent_type, 'consent_type', args, int))
if entity_id:
filters.add(
argument_to_sql(entity_id, 'entity_id', args))
if filter_expired:
filters.add('(expiry is null or expiry < [:now])')
sql = """SELECT entity_consent.*
FROM [:table schema=cerebrum name=entity_consent]
INNER JOIN [:table schema=cerebrum name=entity_consent_code]
ON consent_code = code"""
if filters:
sql += " WHERE " + " AND ".join(filters)
return self.query(sql, args)
def list_operations(self, operator, target_id):
"""Retrieves a list with operations the operator can perform.
Returns a list with tuples with the info (op_code, code_str).
"""
auth_entities = self._get_users_auth_entities(operator)
if not auth_entities:
return []
binds = {'target_id': target_id}
ent = argument_to_sql(auth_entities, 'ar.entity_id', binds, int)
sql = """
SELECT ao.op_code, aoc.code_str
FROM
[:table schema=cerebrum name=auth_op_code] aoc,
[:table schema=cerebrum name=auth_operation] ao,
[:table schema=cerebrum name=auth_operation_set] aos,
[:table schema=cerebrum name=auth_role] ar,
[:table schema=cerebrum name=auth_op_target] aot
WHERE
aoc.code=ao.op_code AND
ao.op_set_id=aos.op_set_id AND
aos.op_set_id=ar.op_set_id AND
{auth_entities} AND
ar.op_target_id=aot.op_target_id AND
aot.entity_id=:target_id
""".format(auth_entities=ent)
return self.query(sql, binds)
def delete(self):
"""Removes all moderator rights for a group upon deletion, and removes
moderator rights for other groups over this group."""
# Delete entity from auth_role
self.execute(
"""
DELETE FROM [:table schema=cerebrum name=auth_role]
WHERE entity_id=:e_id
""", {'e_id': self.entity_id})
# Find references to entity as op_target in auth_op_target
target_list = self.query(
"""
SELECT op_target_id
FROM [:table schema=cerebrum name=auth_op_target]
WHERE entity_id=:e_id
""", {'e_id': self.entity_id})
# If any references found, remove first from auth_role, then from
# auth_op_target
if target_list:
op_target_id = [row['op_target_id'] for row in target_list]
binds = dict()
targets = argument_to_sql(op_target_id, "op_target_id", binds, int)
# Delete entries from auth_role
self.execute(
"""
DELETE FROM [:table schema=cerebrum name=auth_role]
WHERE %s """ % targets, binds)
# Delete references to entity in auth_op_target
self.execute(
"""
DELETE FROM [:table schema=cerebrum name=auth_op_target]
WHERE %s """ % targets, binds)
def sql_select_consents(db,
consent_code=None,
consent_type=None,
entity_id=None,
entity_type=None,
filter_expired=True,
fetchall=True):
""" Get consents from the database. """
filters = set()
args = {}
query = """
SELECT entity_consent.*
FROM [:table schema=cerebrum name=entity_consent]
INNER JOIN [:table schema=cerebrum name=entity_consent_code]
ON consent_code = code
"""
for value, field, convert in ((consent_code, 'consent_code',
int), (consent_type, 'consent_type',
int), (entity_id, 'entity_id', int),
(entity_type, 'entity_type', int)):
if value:
filters.add(argument_to_sql(value, field, args, convert))
if filter_expired:
filters.add('(expiry is null or expiry < [:now])')
if filters:
query += " WHERE " + " AND ".join(filters)
return db.query(query, args, fetchall=fetchall)
def list_all_notes(self, entity_type=None):
"""If entity_type is None, returns all notes associated with all
entities. If entity_type is set, it filters on this entity type.
@param entity_type: Only return notes for entities of this type
@type entity_type: EntityTypeCode or a list of EntityTypeCodes
@return A list containing notes
@rtype list of rows"""
tables = ["[:table schema=cerebrum name=entity_note] enote"]
where = []
binds = {}
if entity_type is not None:
tables.append("""[:table schema=cerebrum name=entity_info] e""")
where.append("enote.entity_id = e.entity_id")
where.append(
argument_to_sql(entity_type, "e.entity_type", binds, int))
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
query_str = """SELECT enote.note_id, enote.create_date,
enote.creator_id, enote.subject, enote.description
FROM %s %s""" % (", ".join(tables), where_str)
return self.query(query_str, binds, fetchall=True)
def list_all_notes(self, entity_type=None):
"""If entity_type is None, returns all notes associated with all
entities. If entity_type is set, it filters on this entity type.
@param entity_type: Only return notes for entities of this type
@type entity_type: EntityTypeCode or a list of EntityTypeCodes
@return A list containing notes
@rtype list of rows"""
tables = ["[:table schema=cerebrum name=entity_note] enote"]
where = []
binds = {}
if entity_type is not None:
tables.append("""[:table schema=cerebrum name=entity_info] e""")
where.append("enote.entity_id = e.entity_id")
where.append(
argument_to_sql(entity_type, "e.entity_type", binds, int))
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
query_str = """SELECT enote.note_id, enote.create_date,
enote.creator_id, enote.subject, enote.description
FROM %s %s""" % (", ".join(tables), where_str)
return self.query(query_str, binds, fetchall=True)
def sql_select_consents(db,
consent_code=None,
consent_type=None,
entity_id=None,
entity_type=None,
filter_expired=True,
fetchall=True):
""" Get consents from the database. """
filters = set()
args = {}
query = """
SELECT entity_consent.*
FROM [:table schema=cerebrum name=entity_consent]
INNER JOIN [:table schema=cerebrum name=entity_consent_code]
ON consent_code = code
"""
for value, field, convert in (
(consent_code, 'consent_code', int),
(consent_type, 'consent_type', int),
(entity_id, 'entity_id', int),
(entity_type, 'entity_type', int)):
if value:
filters.add(argument_to_sql(value, field, args, convert))
if filter_expired:
filters.add('(expiry is null or expiry < [:now])')
if filters:
query += " WHERE " + " AND ".join(filters)
return db.query(query, args, fetchall=fetchall)
def delete(self):
"""Removes all moderator rights for a group upon deletion, and removes
moderator rights for other groups over this group."""
# Delete entity from auth_role
self.execute(
"""
DELETE FROM [:table schema=cerebrum name=auth_role]
WHERE entity_id=:e_id
""", {'e_id': self.entity_id})
# Find references to entity as op_target in auth_op_target
target_list = self.query(
"""
SELECT op_target_id
FROM [:table schema=cerebrum name=auth_op_target]
WHERE entity_id=:e_id
""", {'e_id': self.entity_id})
# If any references found, remove first from auth_role, then from
# auth_op_target
if target_list:
op_target_id = [row['op_target_id'] for row in target_list]
binds = dict()
targets = argument_to_sql(op_target_id, "op_target_id", binds, int)
# Delete entries from auth_role
self.execute(
"""
DELETE FROM [:table schema=cerebrum name=auth_role]
WHERE %s """ % targets, binds)
# Delete references to entity in auth_op_target
self.execute(
"""
DELETE FROM [:table schema=cerebrum name=auth_op_target]
WHERE %s """ % targets, binds)
super(BofhdAuthEntityMixin, self).delete()
def search(self,
entity_id=None,
spread=None,
before_date=None,
after_date=None,
fetchall=False):
"""
Search for spread expire dates.
:param entity_id:
Filter results by a single entity_id or a sequence of entity_id
values.
:param spread:
Filter results by a single spread or a sequence of spread values.
:param before_date:
Only include results with an expire_date older than this date.
:param after_date:
Only include results with an expire_date newer than this date.
"""
filters = []
binds = dict()
if entity_id:
filters.append(argument_to_sql(entity_id, 'entity_id', binds, int))
if spread:
filters.append(argument_to_sql(spread, 'spread', binds, int))
if before_date is not None:
binds['before_date'] = before_date
filters.append('expire_date < :before_date')
if after_date is not None:
binds['after_date'] = before_date
filters.append('expire_date > :after_date')
where = ('WHERE ' + ' AND '.join(filters)) if filters else ''
stmt = """
SELECT entity_id, spread, expire_date
FROM [:table schema=cerebrum name=spread_expire]
{where}
""".format(where=where)
return self.query(stmt, binds)
def search_gpg_data(self, message_id=None, entity_id=None, tag=None,
recipient=None, latest=False):
"""Search for GPG messages.
:param message_id: Message ID(s)
:type message_id: int or a seqence thereof
:param entity_id: Entity ID(s)
:type entity_id: int or a sequence thereof
:param tag: Tag
:type tag: str or a sequence thereof
:param recipient: Recipient key ID(s)
:type recipient: str or a sequence thereof
:returns: list of db rows
"""
tables = []
where = []
binds = {}
tables.append("[:table schema=cerebrum name=entity_gpg_data]")
if message_id is not None:
where.append(
argument_to_sql(message_id, 'message_id', binds, int))
if entity_id is not None:
where.append(
argument_to_sql(entity_id, 'entity_id', binds, int))
if tag is not None:
where.append(
argument_to_sql(tag, 'tag', binds, str))
if recipient is not None:
where.append(
argument_to_sql(recipient, 'recipient', binds, str))
sql = "SELECT * FROM {}".format(','.join(tables))
if where:
sql += " WHERE " + " AND ".join(where)
if latest:
sql += " ORDER BY created DESC LIMIT 1"
return self.query(sql, binds)
def list_quotas(self, spread=None, disk_id=None, all_users=False):
"""
List quota and homedir information for all users that has quota.
"""
binds = {}
where = []
where.append(
argument_to_sql(self.const.account_namespace, 'en.value_domain',
binds, int))
if spread:
where.append(argument_to_sql(spread, 'ah.spread', binds, int))
if disk_id:
where.append(argument_to_sql(disk_id, 'di.disk_id', binds, int))
query = """
SELECT
dq.homedir_id, ah.account_id, hi.home, en.entity_name, di.path,
dq.quota, dq.override_quota, dq.override_expiration, ah.spread
FROM
[:table schema=cerebrum name=disk_info] di,
[:table schema=cerebrum name=account_home] ah,
[:table schema=cerebrum name=account_info] ai,
[:table schema=cerebrum name=entity_name] en,
[:table schema=cerebrum name=homedir] hi
{if_left} JOIN
[:table schema=cerebrum name=disk_quota] dq
ON
dq.homedir_id = hi.homedir_id
WHERE
hi.disk_id=di.disk_id AND
hi.homedir_id=ah.homedir_id AND
ah.account_id=en.entity_id AND
ai.account_id=en.entity_id AND
(ai.expire_date IS NULL OR
ai.expire_date > [:now]) AND
{where}
""".format(
if_left=('LEFT' if all_users else ''),
where=' AND '.join(where),
)
return self._db.query(query, binds)
def fetch_exam_data(self, subjects, year, timecode=None):
""" Test for PPU3310L """
typecode = ('1FAG-HO-H', '2FAG-HO-H', 'H')
binds = {
'year': year,
}
subjects_clause = argument_to_sql(subjects, 've.emnekode', binds, str)
type_clause = ""
if typecode:
type_clause = 'AND ' + argument_to_sql(typecode, 've.vurdkombkode',
binds, str)
time_clause = ""
if timecode:
time_clause = "AND ve.vurdtidkode = :timecode "
binds['timecode'] = timecode
query = """SELECT ve.emnekode, ve.versjonskode, ve.vurdkombkode,
ve.vurdtidkode, ve.arstall, ve.institusjonsnr AS institusjon,
e.faknr_kontroll AS fakultet, e.instituttnr_kontroll AS institutt,
e.gruppenr_kontroll AS gruppe,
to_char(ve.dato_uttak,'yyyy-mm-dd') AS dato,
to_char(ve.klokkeslett_uttak,'hh24:mi') AS tid,
to_char(ve.dato_innlevering,'yyyy-mm-dd') AS dato_innlevering,
to_char(ve.klokkeslett_innlevering,'hh24:mi') AS tid_innlevering
FROM fs.vurdkombenhet ve
JOIN fs.emne e ON (
e.emnekode = ve.emnekode
AND e.institusjonsnr = ve.institusjonsnr
AND e.versjonskode = ve.versjonskode)
WHERE %(subjects)s
AND ve.arstall = :year
%(typecode)s
%(timecode)s
ORDER BY 1;""" % {
'subjects': subjects_clause,
'typecode': type_clause,
'timecode': time_clause,
}
return self.fs.db.query(query, binds)
def account_ids_to_person_ids(account_ids):
""" Takes a sequence of account IDs and returns their owners ID
if the owner is a person. """
if not account_ids:
return []
binds = {}
where = [
argument_to_sql(account_ids, 'ai.account_id', binds, int),
'ai.owner_id=ei.entity_id',
argument_to_sql(
co.entity_person, 'ei.entity_type', binds, int)]
where_str = "WHERE " + " AND ".join(where)
sql = """
SELECT DISTINCT ai.owner_id
FROM [:table schema=cerebrum name=account_info] ai,
[:table schema=cerebrum name=entity_info] ei
{}""".format(where_str)
return [x['owner_id'] for x in self.query(sql, binds)]
def account_ids_to_person_ids(account_ids):
""" Takes a sequence of account IDs and returns their owners ID
if the owner is a person. """
if not account_ids:
return []
binds = {}
where = [
argument_to_sql(account_ids, 'ai.account_id', binds, int),
'ai.owner_id=ei.entity_id',
argument_to_sql(co.entity_person, 'ei.entity_type', binds, int)
]
where_str = " AND ".join(where)
sql = """
SELECT DISTINCT ai.owner_id
FROM [:table schema=cerebrum name=account_info] ai,
[:table schema=cerebrum name=entity_info] ei
WHERE {}""".format(where_str)
return [x['owner_id'] for x in self.query(sql, binds)]
def remove_gpg_data_by_tag(self, tag):
"""Remove GPG encrypted data by tag for the current entity.
:param tag: Tag for data to be removed
:type tag: str or sequence thereof
:returns: Number of deleted rows
"""
where = []
binds = {}
where.append(
argument_to_sql(self.entity_id, 'entity_id', binds, int))
where.append(
argument_to_sql(tag, 'tag', binds, str))
sql = "DELETE FROM [:table schema=cerebrum name=entity_gpg_data] "
sql += " WHERE " + " AND ".join(where)
self.execute(sql, binds)
return self._db.rowcount
def fetch_exam_data(self, subjects, year, timecode=None):
""" Fetches digital exams from FS.
@type db: Cerebrum.Database
@param db: The database connection of an FS-object.
@type subject: string
@param subject: The subjects to fetch exam data for
@rtype: Cerebrum.extlib.db_row.row
@return: Database rows with the FS results (exams).
Fields:
emnekode (str), versjonskode (str), vurdkombkode (str),
vurdtidkode (str), arstall (int), institusjon (int),
fakultet (int), institutt (int), gruppe (int), dato (str),
tid (str),
"""
binds = {'year': year, 'typecode': cereconf.DIGEKS_TYPECODE}
subjects_clause = argument_to_sql(subjects, 'v2.emnekode', binds, str)
time_clause = ""
if timecode:
time_clause = "AND v2.vurdtidkode = :timecode "
binds['timecode'] = timecode
query = """SELECT DISTINCT v2.emnekode, v2.versjonskode, v2.vurdkombkode,
v2.vurdtidkode, v2.arstall, v2.institusjonsnr AS institusjon,
e.faknr_kontroll AS fakultet, e.instituttnr_kontroll AS institutt,
e.gruppenr_kontroll AS gruppe,
to_char(nvl(ve.dato_eksamen,v2.dato_eksamen),'yyyy-mm-dd') AS dato,
to_char(nvl(ve.klokkeslett_fremmote_tid,v2.klokkeslett_fremmote_tid),'hh24:mi') AS tid
FROM fs.vurdkombenhet ve
JOIN fs.vurderingskombinasjon v
ON (v.emnekode = ve.emnekode
AND v.versjonskode = ve.versjonskode
AND v.vurdkombkode = ve.vurdkombkode
AND v.status_vurdering = 'J')
JOIN fs.emne e
ON (e.emnekode = ve.emnekode
AND e.institusjonsnr = ve.institusjonsnr
AND e.versjonskode = ve.versjonskode)
LEFT OUTER JOIN fs.vurdkombenhet v2
ON (v2.emnekode = ve.emnekode
AND v2.versjonskode = ve.versjonskode
AND v2.vurdtidkode = ve.vurdtidkode
AND v2.arstall = ve.arstall)
WHERE NOT nvl(ve.dato_eksamen,v2.dato_eksamen) IS NULL
AND %(subjects)s
AND v2.vurdkombkode LIKE :typecode
AND v2.arstall = :year
%(timecode)s
ORDER BY 1;""" % {
'subjects': subjects_clause,
'timecode': time_clause,
}
return self.fs.db.query(query, binds)
def list_names(self, source_system=None, variant=None):
"""Return all names, optionally filtered on source_system or variant"""
binds = dict()
conditions = []
if source_system is not None:
cond = argument_to_sql(source_system, 'source_system', binds, int)
conditions.append(cond)
if variant is not None:
cond = argument_to_sql(variant, 'name_variant', binds, int)
conditions.append(cond)
where = ('WHERE ' + ' AND '.join(conditions)) if conditions else ''
return self.query(
"""
SELECT *
FROM [:table schema=cerebrum name=person_name]
""" + where,
binds)
def fetch_exam_data(self, subjects, year, timecode=None):
""" Fetches digital exams from FS.
@type db: Cerebrum.Database
@param db: The database connection of an FS-object.
@type subject: string
@param subject: The subjects to fetch exam data for
@rtype: Cerebrum.extlib.db_row.row
@return: Database rows with the FS results (exams).
Fields:
emnekode (str), versjonskode (str), vurdkombkode (str),
vurdtidkode (str), arstall (int), institusjon (int),
fakultet (int), institutt (int), gruppe (int), dato (str),
tid (str),
"""
binds = {'year': year, 'typecode': cereconf.DIGEKS_TYPECODE}
subjects_clause = argument_to_sql(subjects, 'v2.emnekode', binds, str)
time_clause = ""
if timecode:
time_clause = "AND v2.vurdtidkode = :timecode "
binds['timecode'] = timecode
query = """SELECT DISTINCT v2.emnekode, v2.versjonskode, v2.vurdkombkode,
v2.vurdtidkode, v2.arstall, v2.institusjonsnr AS institusjon,
e.faknr_kontroll AS fakultet, e.instituttnr_kontroll AS institutt,
e.gruppenr_kontroll AS gruppe,
to_char(nvl(ve.dato_eksamen,v2.dato_eksamen),'yyyy-mm-dd') AS dato,
to_char(nvl(ve.klokkeslett_fremmote_tid,v2.klokkeslett_fremmote_tid),'hh24:mi') AS tid
FROM fs.vurdkombenhet ve
JOIN fs.vurderingskombinasjon v
ON (v.emnekode = ve.emnekode
AND v.versjonskode = ve.versjonskode
AND v.vurdkombkode = ve.vurdkombkode
AND v.status_vurdering = 'J')
JOIN fs.emne e
ON (e.emnekode = ve.emnekode
AND e.institusjonsnr = ve.institusjonsnr
AND e.versjonskode = ve.versjonskode)
LEFT OUTER JOIN fs.vurdkombenhet v2
ON (v2.emnekode = ve.emnekode
AND v2.versjonskode = ve.versjonskode
AND v2.vurdtidkode = ve.vurdtidkode
AND v2.arstall = ve.arstall)
WHERE NOT nvl(ve.dato_eksamen,v2.dato_eksamen) IS NULL
AND %(subjects)s
AND v2.vurdkombkode LIKE :typecode
AND v2.arstall = :year
%(timecode)s
ORDER BY 1;""" % {'subjects': subjects_clause,
'timecode': time_clause, }
return self.fs.db.query(query, binds)
def search(self, owner_entity_id=None, owner_entity_type=None):
"""Search for voip_addresses matching the filtering criteria."""
where = ["va.owner_entity_id = ei.entity_id", ]
binds = dict()
if owner_entity_id is not None:
where.append(argument_to_sql(owner_entity_id, "va.owner_entity_id",
binds, int))
if owner_entity_type is not None:
where.append(argument_to_sql(owner_entity_type, "ei.entity_type",
binds, int))
if where:
where = "WHERE " + " AND ".join(where)
else:
where = ""
return self.query("""
SELECT va.entity_id,
va.owner_entity_id, ei.entity_type as owner_entity_type
FROM [:table schema=cerebrum name=voip_address] va,
[:table schema=cerebrum name=entity_info] ei
""" + where, binds)
def fetch_exam_data(self, subjects, year, timecode=None):
""" Test for PPU3310L """
typecode = ('1FAG-HO-H', '2FAG-HO-H', 'H')
binds = {'year': year, }
subjects_clause = argument_to_sql(subjects, 've.emnekode', binds, str)
type_clause = ""
if typecode:
type_clause = 'AND ' + argument_to_sql(typecode, 've.vurdkombkode', binds, str)
time_clause = ""
if timecode:
time_clause = "AND ve.vurdtidkode = :timecode "
binds['timecode'] = timecode
query = """SELECT ve.emnekode, ve.versjonskode, ve.vurdkombkode,
ve.vurdtidkode, ve.arstall, ve.institusjonsnr AS institusjon,
e.faknr_kontroll AS fakultet, e.instituttnr_kontroll AS institutt,
e.gruppenr_kontroll AS gruppe,
to_char(ve.dato_uttak,'yyyy-mm-dd') AS dato,
to_char(ve.klokkeslett_uttak,'hh24:mi') AS tid,
to_char(ve.dato_innlevering,'yyyy-mm-dd') AS dato_innlevering,
to_char(ve.klokkeslett_innlevering,'hh24:mi') AS tid_innlevering
FROM fs.vurdkombenhet ve
JOIN fs.emne e ON (
e.emnekode = ve.emnekode
AND e.institusjonsnr = ve.institusjonsnr
AND e.versjonskode = ve.versjonskode)
WHERE %(subjects)s
AND ve.arstall = :year
%(typecode)s
%(timecode)s
ORDER BY 1;""" % {'subjects': subjects_clause,
'typecode': type_clause,
'timecode': time_clause, }
return self.fs.db.query(query, binds)
def remove_gpg_message(self, message_id):
"""Remove GPG message(s) by message ID.
:param message_id: Message ID(s) to be removed
:type message_id: int or sequence thereof
:returns: Number of deleted rows
"""
binds = {}
where = [argument_to_sql(message_id, 'message_id', binds, int)]
sql = "DELETE FROM [:table schema=cerebrum name=entity_gpg_data] "
sql += " WHERE " + " AND ".join(where)
self.execute(sql, binds)
return self._db.rowcount
def delete(self, target_system=None, event_type=None):
u""" Removes event-to-target mappings.
:type target_system:
NoneType, int, str, TargetSystem, list
:param target_system:
Delete mappings that matches a given target system, or list of
target systems. A `None` value will match all target systems
(default).
:type event_type:
NoneType, int, str, TargetSystem, list
:param event_types:
Delete mappings that matches a given change type, or list of
change types. A `None` value will match all change types
(default).
"""
filters = set()
args = dict()
if target_system:
filters.add(
argument_to_sql(target_system, 'target_system', args,
lambda x: int(self._target_system_to_code(x))))
if event_type:
filters.add(
argument_to_sql(event_type, 'event_type', args,
lambda x: int(self._event_type_to_code(x))))
sql = """DELETE FROM [:table schema=cerebrum name=event_to_target]"""
if filters:
sql += " WHERE " + " AND ".join(filters)
try:
self.db.execute(sql, args)
except self.db.IntegrityError:
# TODO: Should we really pass silently?
pass
def get_mappings(self, target_systems=None, event_types=None):
u""" Gets current event-to-target mappings.
:type target_system:
NoneType, int, str, TargetSystem, list
:param target_system:
Filter by a single target system constant, or a list of target
system constants. A `None` value returns all target systems
(default).
:type event_types:
NoneType, int, str, TargetSystem, list
:param event_types:
Filter by a single event (change type) constant, or a list of event
constants. A `None` value returns all events (default).
:returns list:
Returns a list of db_rows. Columns:
(target_system, event_type)
"""
filters = set()
args = dict()
if target_systems:
filters.add(
argument_to_sql(target_systems, 'target_system', args,
lambda x: int(self._target_system_to_code(x))))
if event_types:
filters.add(
argument_to_sql(event_types, 'event_type', args,
lambda x: int(self._event_type_to_code(x))))
sql = """SELECT target_system, event_type
FROM [:table schema=cerebrum name=event_to_target]"""
if filters:
sql += " WHERE " + " AND ".join(filters)
return self.db.query(sql, args)
def get_mappings(self, target_systems=None, event_types=None):
u""" Gets current event-to-target mappings.
:type target_system:
NoneType, int, str, TargetSystem, list
:param target_system:
Filter by a single target system constant, or a list of target
system constants. A `None` value returns all target systems
(default).
:type event_types:
NoneType, int, str, TargetSystem, list
:param event_types:
Filter by a single event (change type) constant, or a list of event
constants. A `None` value returns all events (default).
:returns list:
Returns a list of db_rows. Columns:
(target_system, event_type)
"""
filters = set()
args = dict()
if target_systems:
filters.add(
argument_to_sql(target_systems, 'target_system', args,
lambda x: int(self._target_system_to_code(x))))
if event_types:
filters.add(
argument_to_sql(event_types, 'event_type', args,
lambda x: int(self._event_type_to_code(x))))
sql = """SELECT target_system, event_type
FROM [:table schema=cerebrum name=event_to_target]"""
if filters:
sql += " WHERE " + " AND ".join(filters)
return self.db.query(sql, args)
def delete(self, entity_id=None, tag=None, recipient=None):
"""
Delete messages for some given entities, tags, or recipients.
:param entity_id:
an entity_id or sequence of entity_ids to delete messages for
:param tag:
a tag or sequence of tags to delete messages for
:param recipient:
a recipient or sequence of recipients to delete messages for
:return:
rows of deleted messages
"""
binds = {}
conds = []
if not any((entity_id, tag, recipient)):
raise TypeError('No argument given (expected at least one)')
if entity_id is not None:
conds.append(argument_to_sql(entity_id, 'entity_id', binds, int))
if tag is not None:
conds.append(argument_to_sql(tag, 'tag', binds, six.text_type))
if recipient is not None:
conds.append(
argument_to_sql(recipient, 'recipient', binds, six.text_type))
stmt = """
DELETE FROM
[:table schema=cerebrum name=entity_gpg_data]
WHERE
{where}
RETURNING
{columns}
""".format(where=' AND '.join(conds), columns=', '.join(self.columns))
return self.query(stmt, binds)
def delete(self, target_system=None, event_type=None):
u""" Removes event-to-target mappings.
:type target_system:
NoneType, int, str, TargetSystem, list
:param target_system:
Delete mappings that matches a given target system, or list of
target systems. A `None` value will match all target systems
(default).
:type event_type:
NoneType, int, str, TargetSystem, list
:param event_types:
Delete mappings that matches a given change type, or list of
change types. A `None` value will match all change types
(default).
"""
filters = set()
args = dict()
if target_system:
filters.add(
argument_to_sql(target_system, 'target_system', args,
lambda x: int(self._target_system_to_code(x))))
if event_type:
filters.add(
argument_to_sql(event_type, 'event_type', args,
lambda x: int(self._event_type_to_code(x))))
sql = """DELETE FROM [:table schema=cerebrum name=event_to_target]"""
if filters:
sql += " WHERE " + " AND ".join(filters)
try:
self.db.execute(sql, args)
except self.db.IntegrityError:
# TODO: Should we really pass silently?
pass
def search_ad_email(self,
account_name=None,
local_part=None,
domain_part=None):
"""Search ad email table"""
binds = dict()
conditions = []
if account_name:
cond = argument_to_sql(account_name, 'account_name', binds)
conditions.append(cond)
if local_part:
cond = argument_to_sql(local_part, 'local_part', binds)
conditions.append(cond)
if domain_part:
cond = argument_to_sql(domain_part, 'domain_part', binds)
conditions.append(cond)
where = ('WHERE ' + ' AND '.join(conditions)) if conditions else ''
query = """
SELECT account_name, local_part, domain_part
FROM [:table schema=cerebrum name=ad_email]
""" + where
return self.query(query, binds)
def list_posix_users(self, spread=None, filter_expired=False):
"""Return account_id of all PosixUsers in database. Filters
are spread which can be a single spread or a tuple or list of
spreads. filter_expired also removes expired accounts."""
efrom, ewhere, bind = "", "", {}
if spread is not None:
efrom += """JOIN [:table schema=cerebrum name=entity_spread] es
ON pu.account_id=es.entity_id AND
""" + argument_to_sql(spread, 'es.spread', bind, int)
if filter_expired:
ewhere = "WHERE ai.expire_date IS NULL OR ai.expire_date > [:now]"
efrom += """JOIN [:table schema=cerebrum name=account_info] ai
ON ai.account_id=pu.account_id"""
return self.query("""
SELECT pu.account_id, pu.posix_uid, pu.gid, pu.gecos, pu.shell
FROM [:table schema=cerebrum name=posix_user] pu %s %s
""" % (efrom, ewhere), bind)
def list_posix_users(self, spread=None, filter_expired=False):
"""Return account_id of all PosixUsers in database. Filters
are spread which can be a single spread or a tuple or list of
spreads. filter_expired also removes expired accounts."""
efrom, ewhere, bind = "", "", {}
if spread is not None:
efrom += """JOIN [:table schema=cerebrum name=entity_spread] es
ON pu.account_id=es.entity_id AND
""" + argument_to_sql(spread, 'es.spread', bind, int)
if filter_expired:
ewhere = "WHERE ai.expire_date IS NULL OR ai.expire_date > [:now]"
efrom += """JOIN [:table schema=cerebrum name=account_info] ai
ON ai.account_id=pu.account_id"""
return self.query(
"""
SELECT pu.account_id, pu.posix_uid, pu.gid, pu.gecos, pu.shell
FROM [:table schema=cerebrum name=posix_user] pu %s %s
""" % (efrom, ewhere), bind)
def _query_permissions(self, operator, operation, target_id):
auth_entities = self._get_users_auth_entities(operator)
if not auth_entities:
return []
try:
operation = int(operation)
except (TypeError, ValueError):
where = """
LOWER(aoc.code_str) LIKE LOWER(:operation) AND
aoc.code=ao.op_code
"""
else:
where = 'ao.op_id=:operation'
binds = {
'operation': operation,
'target_id': target_id,
}
ent = argument_to_sql(auth_entities, 'ar.entity_id', binds, int)
sql = """
SELECT
aot.attr,
ao.op_id,
aot.op_target_id
FROM
[:table schema=cerebrum name=auth_op_code] aoc,
[:table schema=cerebrum name=auth_operation] ao,
[:table schema=cerebrum name=auth_operation_set] aos,
[:table schema=cerebrum name=auth_role] ar,
[:table schema=cerebrum name=auth_op_target] aot
WHERE
{where} AND
ao.op_set_id=aos.op_set_id AND
aos.op_set_id=ar.op_set_id AND
{auth_entities} AND
ar.op_target_id=aot.op_target_id AND
aot.entity_id=:target_id
""".format(where=where, auth_entities=ent)
return self.query(sql, {
'operation': operation,
'target_id': target_id,
})
def list_ad_attributes(self, entity_id=None, spread=None, attribute=None):
"""List all stored AD-attributes that matches the given criterias.
:type entity_id: int or list/tuple thereof
:param entity_id: If the list of attributes should be limited to given
entitites.
:type spread: constant, int or list/tuple thereof
:param spread: If the list of attributes should be limited to given
spread types.
:type attribute: constant, int or list/tuple thereof
:param attribute: If given, the result would be limited to only the
given attribute types.
:rtype: iterable of db-rows
:return:
All the attributes from the database, limited to the input
variables. The row elements are:
- `entity_id`: What entity the attribute is registered for
- `attr_code`: The attribute's attribute code
- `spread_code`: What spread the attribute is set up for
- `subattr_id`: Number for separating multivalued elements.
- `value`: The string with the attribute value.
"""
binds = dict()
where = list()
for var, name, transform in (("entity_id", "entity_id",
int), ("spread", "spread_code", int),
("attribute", "attr_code", int)):
if locals()[var] is not None:
where.append(
argument_to_sql(locals()[var], "at." + name, binds,
transform))
where = " AND ".join(where)
if where:
where = "WHERE " + where
return self.query(
"""
SELECT DISTINCT at.*
FROM [:table schema=cerebrum name=ad_attribute] at
%s""" % (where, ), binds)
def list_auth_data(self, auth_methods=None):
"""Return all authentication data registered for the given methods.
@type auth_methods: an int, an EntityAuthenticationCode or a sequence
thereof.
@param auth_methods:
Specify which authentication methods the data should be returned
for.
"""
binds = dict()
where = ""
if auth_methods is not None:
where = argument_to_sql(auth_methods, "auth_method", binds, int)
return self.query("""
SELECT entity_id, auth_method, auth_data
FROM [:table schema=cerebrum name=entity_authentication_info]
WHERE """ + where, binds)
def list_ad_attributes(self, entity_id=None, spread=None, attribute=None):
"""List all stored AD-attributes that matches the given criterias.
:type entity_id: int or list/tuple thereof
:param entity_id: If the list of attributes should be limited to given
entitites.
:type spread: constant, int or list/tuple thereof
:param spread: If the list of attributes should be limited to given
spread types.
:type attribute: constant, int or list/tuple thereof
:param attribute: If given, the result would be limited to only the
given attribute types.
:rtype: iterable of db-rows
:return:
All the attributes from the database, limited to the input
variables. The row elements are:
- `entity_id`: What entity the attribute is registered for
- `attr_code`: The attribute's attribute code
- `spread_code`: What spread the attribute is set up for
- `subattr_id`: Number for separating multivalued elements.
- `value`: The string with the attribute value.
"""
binds = dict()
where = list()
for var, name, transform in (("entity_id", "entity_id", int),
("spread", "spread_code", int),
("attribute", "attr_code", int)):
if locals()[var] is not None:
where.append(argument_to_sql(locals()[var], "at." + name, binds,
transform))
where = " AND ".join(where)
if where:
where = "WHERE " + where
return self.query("""
SELECT DISTINCT at.*
FROM [:table schema=cerebrum name=ad_attribute] at
%s""" % (where,), binds)
def list_auth_data(self, auth_methods=None):
"""Return all authentication data registered for the given methods.
@type auth_methods: an int, an EntityAuthenticationCode or a sequence
thereof.
@param auth_methods:
Specify which authentication methods the data should be returned
for.
"""
binds = dict()
where = ""
if auth_methods is not None:
where = argument_to_sql(auth_methods, "auth_method", binds, int)
return self.query(
"""
SELECT entity_id, auth_method, auth_data
FROM [:table schema=cerebrum name=entity_authentication_info]
WHERE """ + where, binds)
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_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, 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_relations(self, source_id=None, target_id=None,
relationship_code=None, indirect_relations=False):
"""Search for relationships betweeen policies by different criterias.
@type source_id: int or sequence of ints
@param source_id:
If given, all relations that has the given components as source
are returned.
If indirect_relations is True, all children (targets) of the given
source_ids are returned.
@type target_id: int or sequence of ints
@param target_id:
If given, all relations that has the given components as targets
are returned.
If indirect_relations is True, all parents (sources) of the given
target_ids are returned.
@type relationship_code: int or sequence of ints
@param relationship_code:
If given, only relations of the given type(s) are returned.
@type indirect_relations: bool
@param indirect_relations:
If True, relationships will be search for recursively, either their
parents or their children, depending on if source_id or target_id
is given.
Note that if indirect_relations is True and both source_id and
target_id is specified, you will not necessarily get what you
expect, since source and target are searched for individually. Try
to avoid this usage.
@rtype: iterable with db-rows
@return:
An iterator with db-rows with data about each relationship. The
db-rows contain the elements:
- source_id
- source_entity_type
- source_name
- target_id
- target_entity_type
- target_name
- relationship_id
- relationship_str
"""
# An effective helper function, copied from Cerebrum/Group.py
def search_transitive_closure(start_id_set, searcher, field):
"""Collect the transitive closure of L{ids} by using the search
strategy specified by L{searcher}. Relation loops are not
a problem.
L{searcher} is simply a tailored search-call which should not go
recursively.
L{field} is the key to extract from db-rows returned by the
L{searcher}. Occasionally we need group_id and other times
member_id. These are the two permissible values.
"""
result = set()
if isinstance(start_id_set, (tuple, set, list)):
workset = set(start_id_set)
else:
workset = set((start_id_set,))
while workset:
new_set = set([x[field] for x in searcher(workset)])
result.update(workset)
workset = new_set.difference(result)
return result
# end search_transitive_closure
binds = dict()
tables = [
'[:table schema=cerebrum name=hostpolicy_component] co1',
'[:table schema=cerebrum name=hostpolicy_component] co2',
'[:table schema=cerebrum name=entity_name] en1',
'[:table schema=cerebrum name=entity_name] en2',
'[:table schema=cerebrum name=hostpolicy_relationship] re',
'[:table schema=cerebrum name=hostpolicy_relationship_code] rc']
where = ['(re.relationship = rc.code)',
'(en1.entity_id = re.source_policy)',
'(en2.entity_id = re.target_policy)',
'(co1.component_id = re.source_policy)',
'(co2.component_id = re.target_policy)']
if source_id is not None:
if indirect_relations:
source_id = search_transitive_closure(
source_id,
lambda ids: self.search_relations(
source_id=ids,
indirect_relations=False,
relationship_code=relationship_code),
'target_id')
where.append(
argument_to_sql(source_id, 're.source_policy', binds, int))
if target_id is not None:
if indirect_relations:
target_id = search_transitive_closure(
target_id,
lambda ids: self.search_relations(
target_id=ids,
indirect_relations=False,
relationship_code=relationship_code),
'source_id')
where.append(
argument_to_sql(target_id, 're.target_policy', binds, int))
if relationship_code is not None:
where.append(
argument_to_sql(
relationship_code, 're.relationship', binds, int))
return self.query("""
SELECT DISTINCT co1.entity_type AS source_entity_type,
co2.entity_type AS target_entity_type,
en1.entity_name AS source_name,
en2.entity_name AS target_name,
rc.code_str AS relationship_str,
re.source_policy AS source_id,
re.target_policy AS target_id,
re.relationship AS relationship_id
FROM %(tables)s
WHERE %(where)s
""" % {'where': ' AND '.join(where),
'tables': ', '.join(tables)},
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, voip_address_id=None, voip_owner_id=None,
client_type=None, mac_address=None, client_info=None,
owner_entity_type=None):
"""Search for voip_clients subject to certain filter rules.
All filters are None, scalars, or sequences thereof. None means that
the filter is not applied. Scalar means we are looking for the exact
value. Sequence (list, tuple, set) of scalars means that we are
looking for voip_clients matching ANY one of the specified scalars in
the filter.
The filters are self-explanatory.
@param voip_owner_id:
This one is a bit special: we are looking for voip_clients where the
associated voip_address rows are owned by the specified
voip_owner_id. This is useful to answer queries like 'Locate all
voip_clients belonging to person Foo'.
@return:
An iterable over db_rows with query result.
"""
binds = dict()
where = list()
for name in ("entity_id", "voip_address_id", "client_type",
"client_info"):
if locals()[name] is not None:
where.append(argument_to_sql(locals()[name],
"vc." + name,
binds, int))
if mac_address is not None:
where.append(argument_to_sql(mac_address,
"vc.mac_address",
binds, str))
if voip_owner_id is not None:
where.append(argument_to_sql(voip_owner_id,
"va.owner_entity_id",
binds, int))
if owner_entity_type is not None:
where.append(argument_to_sql(owner_entity_type,
"ei.entity_type",
binds, int))
if where:
where = " AND " + " AND ".join(where)
else:
where = ""
return self.query("""
SELECT vc.entity_type, vc.entity_id, vc.voip_address_id, vc.client_type,
vc.sip_enabled, vc.mac_address, vc.client_info, va.owner_entity_id,
ei.entity_type as owner_entity_type
FROM [:table schema=cerebrum name=voip_client] vc,
[:table schema=cerebrum name=voip_address] va,
[:table schema=cerebrum name=entity_info] ei
WHERE vc.voip_address_id = va.entity_id AND
va.owner_entity_id = ei.entity_id
%s""" % where, binds)
def search_members(self, group_id=None, spread=None,
member_id=None, member_type=None,
indirect_members=False,
member_spread=None,
member_filter_expired=True,
include_member_entity_name=False):
"""Search for group *MEMBERS* satisfying certain criteria.
This method is a complement of L{search}. While L{search} returns
*group* information, L{search_members} returns member and membership
information. Despite the similarity in filters, the methods have
different objectives.
If a filter is None, it means that it will not be applied. Calling
this method without any argument will return all non-expired members
of groups (i.e. a huge chunk of the group_member table). Since
group_member is one of the largest tables, do not do that, unless you
have a good reason.
All filters except for L{group_id} are applied to members, rather than
groups containing members.
The db-rows eventually returned by this method contain at least these
keys: group_id, group_name, member_type, member_id. There may be other
keys as well.
:type group_id: int or a sequence thereof or None.
:param group_id:
Group ids to look for. Given a group_id, only memberships in the
specified groups will be returned. This is useful for answering
questions like 'give a list of all members of group <bla>'. See also
L{indirect_members}.
: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 member_id: int or a sequence thereof or None.
:param member_id:
The result membership list will be filtered by member_ids - only the
specified member_ids will be listed. This is useful for answering
questions like 'give a list of memberships held by <entity_id>'. See
also L{indirect_members}.
:type member_type:
int or an EntityType constant or a sequence thereof or None.
:param member_type:
The resulting membership list be filtered by member type - only the
member entities of the specified type will be returned. This is
useful for answering questions like 'give me a list of *group*
members of group <bla>'.
:type indirect_members: bool
:param indirect_members:
This parameter controls how 'deep' a search is performed. If True,
we recursively expand *all* group_ids matching the rest of the
filters.
This filter can and must be combined either with L{group_id} or with
L{member_id} (but not both).
When combined with L{group_id}, the search means 'return all
membership entries where members are direct AND indirect members of
the specified group_id(s)'.
When combined with L{member_id}, the search means 'return all
membership entries where the specified members are direct AND
indirect members'
When False, only direct memberships are considered for all filters.
:type member_spread: int or SpreadCode or sequence thereof or None.
:param member_spread:
Filter the resulting membership list by spread. I.e. only members
with specified spread(s) will be returned.
:type member_filter_expired: bool
:param member_filter_expired:
Filter the resulting membership list by expiration date. If set, do
NOT return any rows where members have expired (i.e. have
expire_date in the past relative to the call time).
:type include_member_entity_name: bool or dict
:param include_member_entity_name:
If the members' entity_name should be included in output or not. If
the value is a dict, it is used as a mapping of what entity_types' of
namespaces to get the names from, otherwise it uses
cereconf.ENTITY_TYPE_NAMESPACE.
:rtype: generator (yielding db-rows with membership information)
:return:
A generator that yields successive db-rows (from group_member)
matching all of the specified filters. These keys are available in
each of the db_rows:
- group_id
- group_name
- member_type
- member_id
- expire_date
There *may* be other keys, but the caller cannot rely on that; nor
can the caller assume that any other key will not be revoked at any
time. expire_date may be None, the rest is always set.
Note that if L{indirect_members} is specified, the answer may
contain member_ids that were NOT part of the initial filters. The
client code invoking search_members() this way should be prepared
for such an eventuality.
Note that if L{indirect_members} is specified, the answers may
contain duplicate member_id keys. The client code interested in
unique member_ids must filter the result set.
"""
# first of all, a help function to help us look for recursive
# memberships...
def search_transitive_closure(start_id_set, searcher, field):
"""Collect the transitive closure of L{ids} by using the search
strategy specified by L{searcher}.
L{searcher} is simply a tailored self.search()-call with
indirect_members=False.
L{field} is the key to extract from db-rows returned by the
L{searcher}. Occasionally we need group_id and other times
member_id. These are the two permissible values.
"""
result = set()
if isinstance(start_id_set, (tuple, set, list)):
workset = set(start_id_set)
else:
workset = set((start_id_set,))
while workset:
new_set = set([x[field] for x in searcher(workset)])
result.update(workset)
workset = new_set.difference(result)
return result
# end search_transitive_closure
# ... then a slight sanity check. We cannot allow a combination of
# group and member id filters combined with indirect_members (what
# kind of meaning can be attached to specifying all three?)
if indirect_members:
assert not (group_id and member_id), "Illegal API usage"
assert group_id or member_id, "Illegal API usage"
# ... and finally, let's generate the SQL statements for all the
# filters.
# IVR 2008-06-12 FIXME: Unfortunately, expire_date tests are not
# exactly pretty, to put it mildly. There are 2 tables, and we want to
# outer join on their union. *That* is hopeless (performancewise), so
# we take the outer joins in turn. It is not exactly pretty either,
# but at least it is feasible.
#
# Once the EntityExpire module is merged in and in production, all
# this junk can be simplified. Before modifying the expressions, make
# sure that the the queries actually work on multiple backends.
select = ["tmp1.group_id AS group_id",
"tmp1.entity_name AS group_name",
"tmp1.member_type AS member_type",
"tmp1.member_id AS member_id",
"tmp1.expire1 as expire1",
"gi.expire_date as expire2",
"NULL as expire_date"]
# We always grab the expiration dates, but we filter on them ONLY if
# member_filter_expired is set.
tables = [""" ( SELECT gm.*,
en.entity_name as entity_name,
ai.expire_date as expire1
FROM [:table schema=cerebrum name=group_member] gm
LEFT OUTER JOIN
[:table schema=cerebrum name=entity_name] en
ON
(en.entity_id = gm.group_id AND
en.value_domain = :vdomain)
LEFT OUTER JOIN
[:table schema=cerebrum name=account_info] ai
ON ai.account_id = gm.member_id
) AS tmp1
LEFT OUTER JOIN
[:table schema=cerebrum name=group_info] gi
ON gi.group_id = tmp1.member_id
""", ]
binds = {"vdomain": int(self.const.group_namespace)}
where = list()
if group_id is not None:
if indirect_members:
# expand group_id to include all direct and indirect *group*
# members of the initial set of group ids. This way we get
# *all* indirect non-group members
group_id = search_transitive_closure(
group_id,
lambda ids: self.search_members(
group_id=ids,
indirect_members=False,
member_type=self.const.entity_group,
member_filter_expired=False),
"member_id")
indirect_members = False
where.append(
argument_to_sql(
group_id,
"tmp1.group_id",
binds,
int))
if spread is not None:
tables.append(
"""JOIN [:table schema=cerebrum name=entity_spread] es2
ON (tmp1.group_id = es2.entity_id AND %s)
""" % argument_to_sql(spread, "es2.spread", binds, int))
if member_id is not None:
if indirect_members:
# expand member_id to include all direct and indirect *parent*
# groups of the initial set of member ids. This way, we reach
# *all* parent groups starting from a given set of direct
# members.
member_id = search_transitive_closure(
member_id,
lambda ids: self.search(member_id=ids,
indirect_members=False,
filter_expired=False),
"group_id")
indirect_members = False
where.append(
argument_to_sql(
member_id,
"tmp1.member_id",
binds,
int))
if member_type is not None:
where.append(argument_to_sql(member_type, "tmp1.member_type",
binds, int))
if member_spread is not None:
tables.append(
"""JOIN [:table schema=cerebrum name=entity_spread] es
ON (tmp1.member_id = es.entity_id AND %s)
""" % argument_to_sql(member_spread, "es.spread", binds, int))
if member_filter_expired:
where.append("""(tmp1.expire1 IS NULL OR tmp1.expire1 > [:now]) AND
(gi.expire_date IS NULL OR gi.expire_date > [:now])
""")
if include_member_entity_name:
if isinstance(include_member_entity_name, dict):
member_name_dict = include_member_entity_name
else:
member_name_dict = {}
for k, v in cereconf.ENTITY_TYPE_NAMESPACE.items():
member_name_dict[
self.const.EntityType(
k)] = self.const.ValueDomain(
v)
case = []
i = 0
for e_type, vdomain in member_name_dict.items():
e_type_name = "e_type%d" % i
vdomain_name = "vdomain%d" % i
case.append("WHEN tmp1.member_type=:%s THEN :%s"
% (e_type_name, vdomain_name))
binds[e_type_name] = e_type
binds[vdomain_name] = vdomain
i += 1
select.append("mn.entity_name AS member_name")
tables.append(
"""LEFT OUTER JOIN [:table schema=cerebrum name=entity_name] mn
ON tmp1.member_id = mn.entity_id
AND mn.value_domain = CASE %s
END""" % "\n".join(case))
where_str = ""
if where:
where_str = "WHERE " + " AND ".join(where)
query_str = "SELECT DISTINCT %s FROM %s %s" % (", ".join(select),
" ".join(tables),
where_str)
for entry in self.query(query_str, binds):
# IVR 2008-07-01 FIXME: We do NOT want to expose expire ugliness
# to the clients. They can all assume that 'expire_date' exists
# and is set appropriately (None or a date)
if entry["expire1"] is not None:
entry["expire_date"] = entry["expire1"]
elif entry["expire2"] is not None:
entry["expire_date"] = entry["expire2"]
yield entry
def search_employment(self, person_id=None, ou_id=None, description=None,
source_system=None, employment_code=None,
include_expired=True, main_employment=None):
"""Look for employment entries matching certain criteria.
@type person_id: int or a sequence thereof or None.
@param person_id:
Filter the results by person_id.
@type ou_id: int or a sequence thereof or None.
@param ou_id:
Filter the results by ou_id.
@type employment: int or constant or a sequence thereof or None.
@param person_id:
Filter the results by the specific employment(s).
@type include_expired: bool
@param include_expired:
Filter out results that has an end date in the past.
@type main_employment: bool
@param main_employment:
Only return results defined as the person's main employment.
@rtype: iterable over db_rows
@return:
Whichever rows match the filters specified. Without any filters,
return the entire table.
"""
where = list()
binds = {}
if person_id is not None:
where.append(argument_to_sql(person_id, "person_id", binds, int))
if ou_id is not None:
where.append(argument_to_sql(ou_id, "ou_id", binds, int))
if description is not None:
where.append(argument_to_sql(description, "description",
binds, six.text_type))
if source_system is not None:
where.append(argument_to_sql(source_system, "source_system",
binds, int))
if employment_code is not None:
where.append(argument_to_sql(employment_code, "employment_code",
binds, str))
if main_employment:
where.append(argument_to_sql('T', "main_employment", binds, str))
if not include_expired:
where.append('(end_date IS NULL OR end_date > [:now])')
query = """
SELECT person_id, ou_id, description, source_system,
employment_code, main_employment, percentage, start_date,
end_date
FROM %s
WHERE %s
""" % (self.__table, " AND ".join(where))
# This voodoo is necessary to hide how we represent booleans in the db.
for row in self.query(query, binds, fetchall=False):
if row["main_employment"] == 'T':
row["main_employment"] = True
elif row["main_employment"] == 'F':
row["main_employment"] = False
else:
assert False, "This cannot happen!"
yield row
def search(self, spread=None, host_id=None, dns_owner_id=None,
dns_owner_spread=None):
"""A search method for hosts.
To be expanded in the future with more functionality when needed, e.g.
to filter by zone_id(s) and name. Also DnsOwners has a an
L{expire_date}, which we in some situations might want to filter by.
@type spread: int or sequence thereof or None
@param spread:
If not None, only hosts with at least one of the given spreads will
be returned.
@type host_id: int or sequence thereof or None
@param host_id:
Filter the result by the given host_id(s).
@type dns_owner_id: int or sequence thereof or None
@param dns_owner_id:
Filter the result by the given dns owner id(s).
@type dns_owner_spread: int or sequence thereof or None
@param dns_owner_spread:
Filter the result by what spreads the dns owner has.
@rtype: iterable (yielding db-rows with host information)
@return:
The search result, filtered by the given search criterias. Each
yielded db row contains the keys:
- host_id
- dns_owner_id
- name
- ttl
- hinfo
- mx_set_id
- zone_id
"""
tables = []
tables.append("[:table schema=cerebrum name=dns_host_info] hi")
tables.append("[:table schema=cerebrum name=entity_name] en")
tables.append("[:table schema=cerebrum name=dns_owner] dno")
where = ['dno.dns_owner_id=hi.dns_owner_id',
'en.entity_id=dno.dns_owner_id']
binds = dict()
if spread is not None:
tables.append("[:table schema=cerebrum name=entity_spread] es")
where.append('es.entity_id=hi.host_id')
where.append(argument_to_sql(spread, 'es.spread', binds, int))
if dns_owner_spread is not None:
tables.append("[:table schema=cerebrum name=entity_spread] es2")
where.append('es2.entity_id=dno.dns_owner_id')
where.append(argument_to_sql(spread, 'es2.spread', binds, int))
if host_id is not None:
where.append(argument_to_sql(host_id, 'dno.host_id', binds, int))
if dns_owner_id is not None:
where.append(argument_to_sql(dns_owner_id, 'dno.dns_owner_id',
binds, int))
where_str = " AND ".join(where)
return self.query("""
SELECT DISTINCT hi.host_id, hi.dns_owner_id, hi.ttl, hi.hinfo,
en.entity_name AS name, dno.mx_set_id, dno.zone_id
FROM %s WHERE %s""" % (','.join(tables), where_str), binds)
def get_log_events(self, start_id=0, max_id=None, types=None,
subject_entity=None, dest_entity=None,
any_entity=None, change_by=None, change_program=None,
sdate=None, return_last_only=False):
""" Fetch change entries from the database.
:param int start_id:
The first change id that should be returned.
:param int max_id:
The last change id that should be returned.
:param int|Constants.ChangeType types:
Filter changes by a given change type.
:param int subject_entity:
Filter changes by a given subject entity.
:param int dest_entity:
Filter changes by a given destination entity.
:param int any_entity:
Filter changes where the given entity is either subject entity or
destination entity.
NOTE: Cannot be used with `subject_entity' or `dest_entity'.
:param int change_by:
Filter change by the entity that performed the change.
:param str change_program:
Filter change by the script that caused the change.
:param string sdate:
Filter changes by date (YYYY-MM-DD). All changes before this date
are filtered.
:param boolean return_last_only:
Only return the last change. Default: False.
NOTE: Requires `types' to be used as well.
:return list|dbrow:
Returns a list of dbrow results. If `return_last_only' is set, only
one row is returned.
"""
if any_entity and (dest_entity or subject_entity):
raise self.ProgrammingError("any_entity is mutually exclusive "
"with dest_entity or subject_entity")
if return_last_only and not types:
raise self.ProgrammingError(
"you need to choose at least one change type "
"to deliver last cl entry for")
where = ["change_id >= :start_id"]
bind = {'start_id': int(start_id)}
if subject_entity is not None:
where.append(argument_to_sql(subject_entity, "subject_entity",
bind, int))
if dest_entity is not None:
where.append("dest_entity=:dest_entity")
bind['dest_entity'] = int(dest_entity)
if any_entity is not None:
where.append("subject_entity=:any_entity OR "
"dest_entity=:any_entity")
bind['any_entity'] = int(any_entity)
if change_by is not None:
where.append("change_by=:change_by")
bind['change_by'] = int(change_by)
if change_program is not None:
where.append("change_program=:change_program")
bind['change_program'] = change_program
if max_id is not None:
where.append("change_id <= :max_id")
bind['max_id'] = int(max_id)
if types is not None:
where.append(argument_to_sql(types, "change_type_id", bind, int))
if sdate is not None:
where.append("tstamp > :sdate")
bind['sdate'] = sdate
where = "WHERE (" + ") AND (".join(where) + ")"
if return_last_only:
where = where + 'ORDER BY tstamp DESC LIMIT 1'
else:
where = where + 'ORDER BY change_id'
return self.query("""
SELECT tstamp, change_id, subject_entity, change_type_id, dest_entity,
change_params, change_by, change_program
FROM [:table schema=cerebrum name=change_log] %s
""" % where, bind, fetchall=False)