def list_worksheets(self, owner_id=None):
'''
Return a list of row dicts, one per worksheet. These dicts do NOT contain
ALL worksheet items; this method is meant to make it easy for a user to see
the currently existing worksheets. Included worksheet items are those that
define metadata that one will likely want to see in a list view (e.g. title).
'''
cols_to_select = [cl_worksheet.c.id,
cl_worksheet.c.uuid,
cl_worksheet.c.name,
cl_worksheet.c.owner_id,
cl_group_object_permission.c.permission]
if owner_id is None:
# query for public worksheets
stmt = select(cols_to_select).\
where(cl_worksheet.c.uuid == cl_group_object_permission.c.object_uuid).\
where(cl_group_object_permission.c.group_uuid == self.public_group_uuid)
else:
# query for worksheets owned by owner_id
cols1 = cols_to_select[:4]
cols1.extend([literal(GROUP_OBJECT_PERMISSION_ALL).label('permission')])
stmt1 = select(cols1).where(cl_worksheet.c.owner_id == owner_id)
# query for worksheets visible to owner_id or co-owned by owner_id
stmt2_groups = select([cl_user_group.c.group_uuid]).\
where(cl_user_group.c.user_id == owner_id)
stmt2 = select(cols_to_select).\
where(cl_worksheet.c.uuid == cl_group_object_permission.c.object_uuid).\
where(or_(
cl_group_object_permission.c.group_uuid.in_(stmt2_groups),
cl_group_object_permission.c.group_uuid == self.public_group_uuid)).\
where(cl_worksheet.c.owner_id != owner_id)
stmt = union(stmt1, stmt2)
with self.engine.begin() as connection:
rows = connection.execute(stmt).fetchall()
if not rows:
return []
uuids = set(row.uuid for row in rows)
item_rows = connection.execute(
cl_worksheet_item.select().\
where(cl_worksheet_item.c.worksheet_uuid.in_(uuids)).\
where(or_(
cl_worksheet_item.c.type == 'title',
cl_worksheet_item.c.type == 'description'))
).fetchall()
row_dicts = [dict(row) for row in sorted(rows, key=lambda item: item['id'])]
uuid_index_map = {}
for i in range(0, len(row_dicts)):
row_dict = row_dicts[i]
row_dict.update({'items': []})
uuid_index_map[row_dict['uuid']] = i
for item_row in item_rows:
idx = uuid_index_map.get(item_row.worksheet_uuid, -1)
if idx < 0:
raise IntegrityError('Got item %s without worksheet' % (item_row,))
row_dicts[idx]['items'].append(dict(item_row))
return row_dicts
def get_child_worksheets(self, bundle_uuid):
'''
Return a list of worksheets that depend on the given bundle.
'''
with self.engine.begin() as connection:
rows = connection.execute(cl_worksheet_item.select().where(
cl_worksheet_item.c.bundle_uuid == bundle_uuid
)).fetchall()
uuids = set(row.worksheet_uuid for row in rows)
return self.batch_get_worksheets(uuid=uuids)
def batch_get_worksheets(self, fetch_items, **kwargs):
'''
Get a list of worksheets, all of which satisfy the clause given by kwargs.
'''
base_worksheet_uuid = kwargs.pop('base_worksheet_uuid', None)
clause = self.make_kwargs_clause(cl_worksheet, kwargs)
# Handle base_worksheet_uuid specially
if base_worksheet_uuid:
clause = and_(clause,
cl_worksheet_item.c.subworksheet_uuid == cl_worksheet.c.uuid,
cl_worksheet_item.c.worksheet_uuid == base_worksheet_uuid)
with self.engine.begin() as connection:
worksheet_rows = connection.execute(
cl_worksheet.select().distinct().where(clause)
).fetchall()
if not worksheet_rows:
if base_worksheet_uuid != None:
# We didn't find any results restricting to base_worksheet_uuid,
# so do a global search
return self.batch_get_worksheets(fetch_items, **kwargs)
return []
# Fetch the items of all the worksheets
if fetch_items:
uuids = set(row.uuid for row in worksheet_rows)
item_rows = connection.execute(cl_worksheet_item.select().where(
cl_worksheet_item.c.worksheet_uuid.in_(uuids)
)).fetchall()
# Make a dictionary for each worksheet with both its main row and its items.
worksheet_values = {row.uuid: dict(row) for row in worksheet_rows}
if fetch_items:
for value in worksheet_values.itervalues():
value['items'] = []
for item_row in sorted(item_rows, key=item_sort_key):
if item_row.worksheet_uuid not in worksheet_values:
raise IntegrityError('Got item %s without worksheet' % (item_row,))
worksheet_values[item_row.worksheet_uuid]['items'].append(item_row)
return [Worksheet(value) for value in worksheet_values.itervalues()]
def batch_get_worksheets(self, **kwargs):
'''
Get a list of worksheets, all of which satisfy the clause given by kwargs.
'''
clause = self.make_kwargs_clause(cl_worksheet, kwargs)
with self.engine.begin() as connection:
worksheet_rows = connection.execute(
cl_worksheet.select().where(clause)
).fetchall()
if not worksheet_rows:
return []
uuids = set(row.uuid for row in worksheet_rows)
item_rows = connection.execute(cl_worksheet_item.select().where(
cl_worksheet_item.c.worksheet_uuid.in_(uuids)
)).fetchall()
# Make a dictionary for each worksheet with both its main row and its items.
worksheet_values = {row.uuid: dict(row) for row in worksheet_rows}
for value in worksheet_values.itervalues():
value['items'] = []
for item_row in sorted(item_rows, key=item_sort_key):
if item_row.worksheet_uuid not in worksheet_values:
raise IntegrityError('Got item %s without worksheet' % (item_row,))
worksheet_values[item_row.worksheet_uuid]['items'].append(item_row)
return [Worksheet(value) for value in worksheet_values.itervalues()]
