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()]