def format_run_query(request, query_string, data, dashboard_id, data_source): """" Format query string and execute using data source """ dashboard_variables = [] collection_variables = [] if dashboard_id: dashboard = session.query(Dashboard).get(dashboard_id) dashboard_variables = dashboard.variables if dashboard.collection_id: collection_variables = session.query(Variable).filter_by( container_id=dashboard.collection_id).all() # javascript format string for column, value in data.items(): query_string = query_string.replace('${%s}' % column, value) # format in dashboard variables for variable in dashboard_variables: query_string = query_string.replace('${%s}' % variable.name, variable.value) # format in collection variables for variable in collection_variables: query_string = query_string.replace('${%s}' % variable.name, variable.value) # query directly over http connection if data_source.url.startswith('http'): return http_query(request.accept_encoding, data_source, query_string) # query directly from database return database_query(data_source, query_string)
def list_collections(request): """ List collections :param request: :return: """ filters = request.params query = session.query(Collection) user = get_user(request.authenticated_userid) if not user.has_role(Administrator): # list collections with user access query = query.join( Collection.users).filter(User.id == request.authenticated_userid) collections = [] for collection in query.order_by(Collection.title).all(): cdata = collection.as_dict(request.authenticated_userid) if 'list_dashboards' in filters: dashboards = session.query(Dashboard).filter( Dashboard.collection_id == collection.id) if not user.has_role(Administrator): # list dashboards with user access dashboards = dashboards.join(Dashboard.users).filter( User.id == request.authenticated_userid) cdata['dashboards'] = [ dash.as_dict(request.authenticated_userid) for dash in dashboards.all() ] collections.append(cdata) return collections
def add_collection_user_access_view(request): """ Add user access role for this collection :param request: :return: """ collection_id = request.matchdict['id'] user_id = request.json_body['user_id'] role_name = request.json_body['role'] role = session.query(Role).filter(Role.name == role_name).first() user_access = create(UserCollection, user_id=user_id, collection_id=collection_id, role_id=role.id) # add access to dashboards in collection dashboards = session.query( Dashboard.id).filter(Dashboard.collection_id == collection_id).all() for dash in dashboards: if not session.query(UserDashboard).filter_by(dashboard_id=dash.id, user_id=user_id).count(): create(UserDashboard, user_id=user_id, dashboard_id=dash.id, role_id=role.id) return user_access.as_dict()
def _paste_dashboard_relationships(dashboard, pasted_dashboard): """ Copy all relationships when pasting new dashboard """ # copy list of users user_access = session.query(UserDashboard).filter( UserDashboard.dashboard_id == dashboard.id).all() for access in user_access: create(UserDashboard, user_id=access.user_id, dashboard_id=pasted_dashboard.id, role_id=access.role_id) # copy list of variables for variable in dashboard.variables: create(Variable, name=variable.name, value=variable.value, container_id=pasted_dashboard.id) # copy charts for chart in session.query(Chart).filter( Chart.dashboard_id == dashboard.id).all(): data = {attr: getattr(chart, attr) for attr in chart_attrs} data['dashboard_id'] = pasted_dashboard.id data['index'] = chart.index pasted_chart = create(Chart, **data) for group in chart.group_by: create(ChartGroupBy, chart_id=pasted_chart.id, dashboard_view_id=group.dashboard_view_id, value=group.value) # copy single stats for stat in session.query(SingleStat).filter( SingleStat.dashboard_id == dashboard.id).all(): create(SingleStat, title=stat.title, query=stat.query, decimals=stat.decimals, format=stat.format, thresholds=stat.thresholds, colors=stat.colors, dashboard_id=pasted_dashboard.id, data_source_id=stat.data_source_id) # copy queries for query in session.query(Query).filter( Query.dashboard_id == dashboard.id).all(): data = {attr: getattr(query, attr) for attr in query_attrs} data['dashboard_id'] = pasted_dashboard.id create(Query, **data)
def delete_dashboard_user_access_view(request): """ Delete user role for this dashboard :param request: :return: """ dashboard_id = request.matchdict['id'] user_id = request.params['user_id'] session.query(UserDashboard). \ filter(UserDashboard.dashboard_id == dashboard_id, UserDashboard.user_id == user_id). \ delete(synchronize_session='fetch') return {}
def delete_collection_user_access_view(request): """ Delete user role for this collection :param request: :return: """ collection_id = request.matchdict['id'] user_id = request.params['user_id'] session.query(UserCollection). \ filter(UserCollection.collection_id == collection_id, UserCollection.user_id == user_id). \ delete(synchronize_session='fetch') return {}
def edit_user(request): """ Edit user :param request: :return: """ user_id = request.matchdict.get('id') email = request.json_body.get('email') telephone = request.json_body.get('telephone') password = request.json_body.get('password') role = request.json_body.get('role') user = get(request, User, as_dict=False) data = get_values(request, attrs, required_attrs) # only admin or owner of user profile can edit profile if user_id != request.authenticated_userid: admin_user = get_user(user_id=request.authenticated_userid, role=Administrator) if not admin_user: raise exc.HTTPForbidden() if telephone: usr = get_user(telephone=telephone) if usr and (usr.id != user_id): raise exc.HTTPBadRequest(json_body={'message': 'User with telephone number %s already exists' % telephone}) if email: usr = get_user(email=email) if usr and (usr.id != user_id): raise exc.HTTPBadRequest(json_body={'message': 'User with email %s already exists' % email}) if not request.json_body.get('role'): raise exc.HTTPBadRequest(json_body={'message': 'User role is required'}) if request.json_body.get('confirmed_registration') is None: data['confirmed_registration'] = False edit(user, **data) if password: user.set_password(password) # update user role existing_role = session.query(Role).filter(Role.name == role).first() user_role = session.query(UserRole).filter(UserRole.user_id == user_id, UserRole.role_id == existing_role.id).first() if not user_role: user.roles = [] user.roles.append(existing_role) return user.as_dict()
def edit_dashboard_user_access_view(request): """ Set user role for this dashboard :param request: :return: """ dashboard_id = request.matchdict['id'] user_id = request.json_body['user_id'] role_name = request.json_body['role'] user_access = session.query(UserDashboard).filter( UserDashboard.dashboard_id == dashboard_id, UserDashboard.user_id == user_id).first() role = session.query(Role).filter(Role.name == role_name).first() user_access.role = role
def edit_collection_user_access_view(request): """ Set user role for this collection :param request: :return: """ collection_id = request.matchdict['id'] user_id = request.json_body['user_id'] role_name = request.json_body['role'] user_access = session.query(UserCollection).filter( UserCollection.collection_id == collection_id, UserCollection.user_id == user_id).first() role = session.query(Role).filter(Role.name == role_name).first() user_access.role = role
def delete(request, model): """ Delete a script view :param request: HTTP Request :param model: SQLAlchemy model instance :return: """ record_id = request.matchdict.get('id') record = session.query(model).filter(model.id == record_id).first() if not record: raise exc.HTTPNotFound(json_body={'message': 'Record not found.'}) return session.query(model). \ filter(model.id == record_id). \ delete(synchronize_session='fetch')
def test_query_view(request): """ Run a query with test values :param request: :return: """ try: query = request.json_body['query'] query_string = query['query'] test_data = json.loads(request.json_body['data']) dashboard_id = request.matchdict['dashboard_id'] data_source = session.query(DataSource).get(query['data_source_id']) except KeyError as err: return {'KeyError': str(err)} except TypeError as err: return {'TypeError': str(err)} except Exception as err: return {'Error': repr(err)} try: return format_run_query(request, query_string, test_data, dashboard_id, data_source) except KeyError as err: return {'KeyError': str(err)} except ProgrammingError as err: return {'SQL ProgrammingError': str(err)} except Exception as err: return {'Error': repr(err)}
def create_chart_view(request): """ Create Chart :param request: :return Chart: """ data = get_values(request, attrs, required_attrs) group_by = request.json_body.get('group_by', []) if data.get('decimals') is None: data['decimals'] = 2 if data.get('index') is None: prev = session.query(Chart).filter_by( dashboard_id=data['dashboard_id']).order_by( Chart.index.desc()).first() index = 0 if prev: index = prev.index + 1 data['index'] = index chart = create(Chart, **data) for group in group_by: group['chart_id'] = chart.id create(ChartGroupBy, **group) return chart.as_dict()
def get_user(user_id=None, telephone=None, email=None, role=None, inactive=None): """ Get a user :param user_id: :param telephone: :param email: :param role: :param inactive: :return: """ query = session.query(User) if inactive is not None: query = query.filter(User.inactive == inactive) if user_id: query = query.filter(User.id == user_id) if telephone: query = query.filter(User.telephone == telephone) # case insensitive match by email if email: query = query.filter(User.email.ilike(email)) if role: query = query.join(User.roles).filter(Role.name == role) return query.first()
def list_dashboards_view(request): """ List dashboards :param request: :return: """ filters = request.params.copy() query = session.query(Dashboard) # filter dashboards either by collection or users if 'collection_id' in filters: # filter for dashboards in a collection, and dashboard not in a collection if filters['collection_id']: query = query.filter( Dashboard.collection_id == filters['collection_id']).order_by( Dashboard.index) else: query = query.filter(Dashboard.collection_id == None).order_by( Dashboard.index) user = get_user(request.authenticated_userid) if not user.has_role(Administrator) or filters.get('user_id'): # filter by users that can view the dashboards query = query.join(Dashboard.users).filter( User.id == user.id).order_by(Dashboard.index) return [ dashboard.as_dict(request.authenticated_userid) for dashboard in query.all() ]
def get_from_secret(secret): """Return the OTP that matches the given secret.""" try: otp = session.query(OTP).filter(OTP.secret == secret).first() except DBAPIError as DBE: log.info("There was a database error: {}".format(DBE)) raise return otp
def list_users(request): filters = request.params query = session.query(User) if 'role' in filters: role = session.query(Role).filter(Role.name == filters['role']).first() query = query.join(UserRole).filter(UserRole.role_id == role.id) # query matches user fullname if 'name' in filters: query = query.filter(User.fullname.ilike(u'{}%'.format(filters['name']))) # search for users on name if 'search' in filters: query = query.filter(or_(User.fullname.ilike(u'{}%'.format(filters['search'])), User.firstname.ilike(u'{}%'.format(filters['search'])), User.surname.ilike(u'{}%'.format(filters['search'])))) if 'inactive' in filters: query = query.filter(User.inactive == filters['inactive']) return [user.as_dict() for user in query.order_by(User.surname).all()]
def __acl__(self): # This is called on every request. Employ some caching if self.function in self._cache: return self._cache[self.function] acl = [] for r in self.allowed: view_roles = session.query(Role). \ filter_by(name=r). \ join(Role.permissions, aliased=True). \ filter_by(name=view_permission).all() edit_roles = session.query(Role). \ filter_by(name=r).join(Role.permissions, aliased=True). \ filter_by(name=edit_permission).all() add_roles = session.query(Role).filter_by(name=r). \ join(Role.permissions, aliased=True). \ filter_by(name=add_permission).all() delete_roles = session.query(Role).filter_by(name=r). \ join(Role.permissions, aliased=True). \ filter_by(name=delete_permission).all() view_roles = [r.name for r in view_roles] edit_roles = [r.name for r in edit_roles] add_roles = [r.name for r in add_roles] delete_roles = [r.name for r in delete_roles] # Ensure that all roles are also viewers view_roles = list( dict.fromkeys(edit_roles + view_roles + add_roles + delete_roles).keys()) acl += [(Allow, r, view_permission) for r in view_roles] + \ [(Allow, r, edit_permission) for r in edit_roles] + \ [(Allow, r, add_permission) for r in add_roles] + \ [(Allow, r, delete_permission) for r in delete_roles] acl += [(Deny, Everyone, view_permission)] self.cache_acl(self.function, acl) return acl
def get_dashboards_user_access_view(request): """ Get the logged in user access role for this dashboard :param request: :return: """ dashboard_id = request.matchdict['id'] user_access = session.query(UserDashboard).filter( UserDashboard.dashboard_id == dashboard_id, UserDashboard.user_id == request.authenticated_userid).first() return user_access.as_dict()
def list_dashboards_user_access_view(request): """ List user dashboards mapping for this dashboard with roles :param request: :return: """ dashboard_id = request.matchdict['id'] user_access = session.query(UserDashboard).filter(UserDashboard.dashboard_id == dashboard_id) \ .join(UserDashboard.user).order_by(User.surname).all() return [access.as_dict() for access in user_access]
def confirm_user_otp(otp_str, otp_secret): """ Confirm user registration from OTP :param otp_str: :param otp_secret: :return: """ otp = session.query(OTP).filter(OTP.otp == otp_str.strip()).first() if not otp: raise exc.HTTPBadRequest(json_body={'message': 'Incorrect OTP.'}) identifier = validate(otp_str, otp_secret) if not identifier: raise exc.HTTPBadRequest(json_body={'message': 'Invalid OTP or expired OTP.'}) user = session.query(User).get(otp.user_id) if not user: return None user.confirmed_registration = True session.add(user) session.flush() delete(otp) return user
def delete_user(request): """ Delete a user view :param request: :return: """ user_id = request.matchdict.get('id') if not user_id: raise exc.HTTPBadRequest(json_body={'message': 'Need user id.'}) user = get_user(user_id) if not user: raise exc.HTTPNotFound(json_body={'message': 'No user found.'}) session.query(UserRole). \ filter(UserRole.user_id == user_id). \ delete(synchronize_session='fetch') session.query(User). \ filter(User.id == user_id). \ delete(synchronize_session='fetch')
def list_collections_user_access_view(request): """ List user collections mapping for this collection with roles :param request: :return: """ collection_id = request.matchdict['id'] user_access = session.query(UserCollection).filter(UserCollection.collection_id == collection_id) \ .join(UserCollection.user).order_by(User.surname).all() return [access.as_dict() for access in user_access]
def reorder_chart_view(request): """ Delete a chart view :param request: :return: """ chart = get(request, Chart, as_dict=False) data = get_values(request, ['index'], ['index']) dashboard_charts = session.query(Chart).filter( chart.dashboard_id == chart.dashboard_id) reorder(data.get('index'), chart, Chart, dashboard_charts) return {}
def get_collections_user_access_view(request): """ Get the logged in user access role for this collection :param request: :return: """ collection_id = request.matchdict['id'] user_access = session.query(UserCollection).filter( UserCollection.collection_id == collection_id, UserCollection.user_id == request.authenticated_userid).first() return user_access.as_dict()
def create_dashboard_view(request): """ Create Dashboard :param request: :return Dashboard: """ data = get_values(request, attrs, required_attrs) collection_id = request.json_body.get('collection_id') if data.get('index') is None and collection_id: result = session.query(func.count(Dashboard.id)).filter( Dashboard.collection_id == collection_id).first() data['index'] = result[0] dashboard = create(Dashboard, **data) # when creating a dashboard in a collection copy the user access from the collection user_access = [] if collection_id: user_access = session.query(UserCollection).filter( UserCollection.collection_id == collection_id).all() for access in user_access: create(UserDashboard, user_id=access.user_id, dashboard_id=dashboard.id, role_id=access.role_id) if not [ access for access in user_access if access.user_id == request.authenticated_userid ]: # add logged in user to dashboard with admin role admin_role = session.query(Role).filter_by(name=Administrator).first() create(UserDashboard, user_id=request.authenticated_userid, dashboard_id=dashboard.id, role_id=admin_role.id) return dashboard.as_dict(request.authenticated_userid)
def reorder_dashboard_view(request): """ Reorder a dashboard in a list view :param request: :return Dashboard: """ dashboard = get(request, Dashboard, as_dict=False) data = get_values(request, ['index'], ['index']) collection_dashboards = session.query(Dashboard).filter( Dashboard.collection_id == dashboard.collection_id) reorder(data.get('index'), dashboard, Dashboard, collection_dashboards) return {}
def paste(request, model, copied_data, parent_id_attribute, name_attribute): """ Create a copy of the record """ data = request.json_body parent_id = data.get(parent_id_attribute) record = get(request, model, as_dict=False) name = getattr(record, name_attribute) # make "Copy of" name when pasting in same parent if not parent_id or (parent_id == getattr(record, parent_id_attribute)): name = 'Copy of %s' % name search_str = '%s%s' % (name, '%') query = session.query(model).filter(getattr(model, name_attribute).ilike(search_str)) \ .order_by(getattr(model, name_attribute).desc()) if parent_id: query = query.filter( getattr(model, parent_id_attribute) == parent_id) query = query.all() if query: number = re.search(r'\((\d+)\)', getattr(query[0], name_attribute)) if number and number.group(1): name = '%s (%s)' % (name, (int(number.group(1)) + 1)) else: name = '%s (2)' % name # set new record index value if it has one if hasattr(model, 'index'): result = session.query(func.count(getattr(model, 'id'))) if parent_id: result = result.filter( getattr(model, parent_id_attribute) == parent_id) copied_data['index'] = result.first()[0] copied_data[name_attribute] = name if parent_id_attribute: copied_data[parent_id_attribute] = parent_id return create(model, **copied_data)
def create_query_view(request): """ Create Query :param request: :return Query: """ data = get_values(request, attrs, required_attrs) existing = session.query(Query).filter_by(name=data['name'], dashboard_id=data['dashboard_id']).first() if existing: raise exc.HTTPBadRequest(json_body={'message': 'Query %s already exists for this dashboard' % data['name']}) query = create(Query, **data) return query.as_dict()
def filtered_list(request, model, order_by): """ List records and filter if filters specified :param request: HTTP Request :param model: SQLAlchemy model instance :param order_by: SQLAlchemy column :return: """ filters = request.params query = session.query(model) for column, value in filters.items(): query = query.filter(getattr(model, column) == value) return [record.as_dict() for record in query.order_by(order_by).all()]
def run_query_view(request): """ Run a query :param request: :return: """ query_name = request.matchdict['name'] dashboard_id = request.matchdict['dashboard_id'] query = session.query(Query).filter(Query.name == query_name, Query.dashboard_id == dashboard_id).first() if not query: raise exc.HTTPNotFound(json_body={'message': 'Query %s not found' % query_name}) # format query with parameters and dashboard variables # run http or database query return format_run_query(request, query.query, request.json_body, dashboard_id, query.data_source)