def people_query(self, columns=None, **constraints):
people_columns = [c.name for c in PeopleTable.columns]
# Queryable columns are temporarily limited until
# privacy filtering is implemented for this method.
column_map = {
'id': PeopleTable.c.id,
'username': PeopleTable.c.username,
#'human_name': PeopleTable.c.human_name,
#'gpg_keyid': PeopleTable.c.gpg_keyid,
#'ssh_key': PeopleTable.c.ssh_key,
'email': PeopleTable.c.email,
#'country_code': PeopleTable.c.country_code,
#'creation': PeopleTable.c.creation,
'ircnick': PeopleTable.c.ircnick,
'status': PeopleTable.c.status,
#'locale': PeopleTable.c.locale,
#'timezone': PeopleTable.c.timezone,
#'latitude': PeopleTable.c.latitude,
#'longitude': PeopleTable.c.longitude,
'group': GroupsTable.c.name,
'group_type': GroupsTable.c.group_type,
'role_status': PersonRolesTable.c.role_status,
'role_type': PersonRolesTable.c.role_type,
'role_approval': PersonRolesTable.c.approval,
}
if columns:
cols = columns.split(',')
else:
# By default, return all of the people columns in column_map.
cols = [c for c in people_columns if c in column_map]
print cols
print constraints
groupjoin = []
if 'group' in constraints \
or 'group_type' in constraints \
or 'role_status' in constraints \
or 'role_type' in constraints:
groupjoin = [PeopleTable.join(PersonRolesTable,
PersonRolesTable.c.person_id == PeopleTable.c.id).join(GroupsTable,
GroupsTable.c.id == PersonRolesTable.c.group_id)]
try:
query = select([column_map[c] for c in cols], from_obj=groupjoin)
except KeyError:
return dict(success=False, error='Invalid column requested.', data={})
for k, v in constraints.iteritems():
if k not in column_map:
return dict(success=False,
error='Invalid constraint specified.', data={})
query = query.where(column_map[k].like(v))
results = [dict(zip(cols, r)) for r in query.execute()]
return dict(success=True, data=results)
def dump(self, groupname=None, role_type=None):
if not groupname:
stmt = select(
[
People.privacy, People.username, People.email,
People.human_name, "'user'", 's.sponsored'
],
from_obj=PeopleTable.outerjoin(
select([
PersonRoles.sponsor_id,
func.count(PersonRoles.sponsor_id).label('sponsored')
]).group_by(PersonRoles.sponsor_id).correlate().alias(
's'))).order_by(People.username)
else:
stmt = select(
[
People.privacy, People.username, People.email,
People.human_name, PersonRoles.role_type, 's.sponsored'
],
from_obj=GroupsTable.join(PersonRolesTable).join(
PeopleTable,
onclause=PeopleTable.c.id == PersonRolesTable.c.person_id).
outerjoin(
select([
PersonRoles.sponsor_id,
func.count(PersonRoles.sponsor_id).label('sponsored')
]).where(
and_(PersonRoles.group_id == Groups.id,
Groups.name == groupname)).group_by(
PersonRoles.sponsor_id).correlate().alias('s')
)).where(
and_(Groups.name == groupname,
PersonRoles.role_status == 'approved')).order_by(
People.username)
people = []
if identity.in_any_group(config.get('admingroup', 'accounts'),
config.get('systemgroup', 'fas-system')):
user = '******'
elif identity.current.anonymous:
user = '******'
else:
user = '******'
username = identity.current.user_name
for row in stmt.execute():
person = list(row[1:])
if not row['sponsored']:
person[-1] = 0
if row['privacy'] and user != 'admin' \
and username != row['username']:
# filter private data
person[2] = u''
people.append(person)
return dict(people=people)
def dump(self, groupname=None, role_type=None):
if not groupname:
stmt = select([People.privacy, People.username, People.email,
People.human_name, "'user'", 's.sponsored'],
from_obj=PeopleTable.outerjoin(select([PersonRoles.sponsor_id,
func.count(PersonRoles.sponsor_id).label('sponsored')]
).group_by(PersonRoles.sponsor_id
).correlate().alias('s')
)).order_by(People.username)
else:
stmt = select([People.privacy, People.username, People.email,
People.human_name, PersonRoles.role_type, 's.sponsored'],
from_obj=GroupsTable.join(PersonRolesTable).join(PeopleTable,
onclause=PeopleTable.c.id==PersonRolesTable.c.person_id
).outerjoin(select([PersonRoles.sponsor_id,
func.count(PersonRoles.sponsor_id).label('sponsored')]
).where(and_(
PersonRoles.group_id==Groups.id,
Groups.name==groupname)).group_by(
PersonRoles.sponsor_id).correlate().alias('s')
)).where(and_(Groups.name==groupname,
PersonRoles.role_status=='approved')
).order_by(People.username)
people = []
if identity.in_any_group(config.get('admingroup', 'accounts'),
config.get('systemgroup', 'fas-system')):
user = '******'
elif identity.current.anonymous:
user = '******'
else:
user = '******'
username = identity.current.user_name
for row in stmt.execute():
person = list(row[1:])
if not row['sponsored']:
person[-1] = 0
if row['privacy'] and user != 'admin' \
and username != row['username']:
# filter private data
person[2] = u''
people.append(person)
return dict(people=people)
def people_query(self, columns=None, **constraints):
people_columns = [c.name for c in PeopleTable.columns]
# Queryable columns are temporarily limited until
# privacy filtering is implemented for this method.
column_map = {
'id': PeopleTable.c.id,
'username': PeopleTable.c.username,
#'human_name': PeopleTable.c.human_name,
#'gpg_keyid': PeopleTable.c.gpg_keyid,
#'ssh_key': PeopleTable.c.ssh_key,
'email': PeopleTable.c.email,
#'country_code': PeopleTable.c.country_code,
#'creation': PeopleTable.c.creation,
'ircnick': PeopleTable.c.ircnick,
'status': PeopleTable.c.status,
#'locale': PeopleTable.c.locale,
#'timezone': PeopleTable.c.timezone,
#'latitude': PeopleTable.c.latitude,
#'longitude': PeopleTable.c.longitude,
'group': GroupsTable.c.name,
'group_type': GroupsTable.c.group_type,
'role_status': PersonRolesTable.c.role_status,
'role_type': PersonRolesTable.c.role_type,
'role_approval': PersonRolesTable.c.approval,
}
if columns:
cols = columns.split(',')
else:
# By default, return all of the people columns in column_map.
cols = [c for c in people_columns if c in column_map]
print cols
print constraints
groupjoin = []
if 'group' in constraints \
or 'group_type' in constraints \
or 'role_status' in constraints \
or 'role_type' in constraints:
groupjoin = [
PeopleTable.join(
PersonRolesTable,
PersonRolesTable.c.person_id == PeopleTable.c.id).join(
GroupsTable,
GroupsTable.c.id == PersonRolesTable.c.group_id)
]
try:
query = select([column_map[c] for c in cols], from_obj=groupjoin)
except KeyError:
return dict(success=False,
error='Invalid column requested.',
data={})
for k, v in constraints.iteritems():
if k not in column_map:
return dict(success=False,
error='Invalid constraint specified.',
data={})
query = query.where(column_map[k].like(v))
results = [dict(zip(cols, r)) for r in query.execute()]
return dict(success=True, data=results)
