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)
