def save_extraction(self, dashboard_id, **kw):
axis = request.json['graph_axis']
visualization_type = request.json['visualization']
validate_axis_against_extraction_visualization(
visualization_type, axis,
DBSession.query(Extraction).get(int(
request.json['extraction_id'])))
try:
de = DBSession.query(DashboardExtractionAssociation).filter(
DashboardExtractionAssociation.uid == request.json['uid'],
).one()
except (NoResultFound, KeyError):
de = DashboardExtractionAssociation()
de.dashboard_id = int(dashboard_id)
de.extraction_id = request.json['extraction_id']
de.extraction_id = request.json['extraction_id']
de.visualization = visualization_type
de.graph_axis = axis
de.index = request.json['index']
try:
columns = int(request.json['columns'])
except ValueError:
abort(412, detail='columns must be an integer beetween 4 and 8')
if 8 < columns or 4 > columns:
abort(412, detail='columns must be between 4 and 8')
de.columns = columns
DBSession.add(de)
return dict(
de=de,
dashboard=de.dashboard,
extraction=de.extraction,
)
def get_one(self, extraction=None, *args, **kw):
available_datasets = []
datasets_columns = {}
for d in DBSession.query(DataSet):
try:
cols = [str(c) for c in d.sample.columns]
except Exception as ex:
log.exception(str(ex))
cols = []
available_datasets.append((d.uid, d.name))
datasets_columns[d.uid] = cols
# Documentation of all the functions available to build extractions
docstring = {
key: value.__doc__
for (key, value) in list(FUNCTIONS.items())
}
docstring = escape_string_to_js(docstring)
from tgext.pluggable import app_model
categories = DBSession.query(app_model.Category)
return dict(extraction=extraction,
stepsformfields=dict(
(fname, f.form_fields)
for fname, f in list(FUNCTIONS.items())),
available_datasets=available_datasets,
datasets_columns=datasets_columns,
visualization_types=VISUALIZATION_TYPES,
docstring=json.dumps(docstring),
category_list=categories)
def set_extraction_index(self, dashboard_id):
uid = request.json['uid']
new_index = request.json['index']
if new_index < 0:
return abort(400, detail='cannot raise first extraction')
last_index = DBSession.query(func.max(DashboardExtractionAssociation.index))\
.filter_by(dashboard_id=dashboard_id).one()[0]
if new_index > last_index:
return abort(400, detail='cannot lower last extraction')
try:
de = DBSession.query(DashboardExtractionAssociation).filter(
DashboardExtractionAssociation.uid == uid, ).one()
except NoResultFound:
return abort(404, detail='dashboard not found')
old_index = de.index
try:
other_de = DBSession.query(DashboardExtractionAssociation).filter(
DashboardExtractionAssociation.dashboard_id == dashboard_id,
DashboardExtractionAssociation.index == new_index,
).one()
other_de.index = old_index
except NoResultFound:
other_de = None
de.index = new_index
return dict(de=de, other_de=other_de)
def delete(self, uid=None):
e_filter = DBSession.query(ExtractionFilter).get(uid)
if not e_filter:
abort(404)
extraction_id = e_filter.extraction_id
DBSession.delete(e_filter)
return redirect('/extractions/view/%s' % extraction_id)
def delete(self, dashboard_id):
if dashboard_id == '1':
return abort(400, detail='cannot delete the main dashboard')
dashboard = DBSession.query(Dashboard).filter_by(
uid=dashboard_id).one()
DBSession.delete(dashboard)
return redirect('/dashboard')
def index(self, **kw):
categories = DBSession.query(app_model.Category).all()
uncategorised = DBSession.query(Extraction).filter_by(
category_id=None).all()
categories += [Bunch(extractions=uncategorised, name="No Category")]
return dict(categories=categories,
has_validation_errors=request.validation.errors,
new_form=CreateExtractionForm)
def delete_extraction(self, dashboard_id):
uid = request.json['uid']
try:
de = DBSession.query(DashboardExtractionAssociation).filter(
DashboardExtractionAssociation.uid == uid, ).one()
DBSession.delete(de)
except NoResultFound:
pass
return dict()
def test_save_category(self):
extraction = DBSession.query(model.Extraction).get(self.extraction)
assert extraction.category_id == self.category
response = self.app.get('/editor/' + str(self.extraction) +
'/save_category',
params=dict(category=-1),
extra_environ=self.admin_env,
status=302)
extraction = DBSession.query(model.Extraction).get(self.extraction)
assert extraction.category_id is None
def test_toggle(self):
value = 1 if not DBSession.query(model.ExtractionStep).get(
self.step).enabled else 0
response = self.app.get('/editor/' + str(self.extraction) +
'/steps/toggle',
dict(uid=self.step, enabled=value),
extra_environ=self.admin_env,
status=200)
assert response.json == dict()
assert DBSession.query(model.ExtractionStep).get(
self.step).enabled is not value
def setup_class(cls):
cls.app = load_app('main', 'test_e2e.ini')
setup_app('test_e2e.ini')
# cmd = ServeCommand(Bunch(options=Bunch(debug=True, log_file=None, relative_plugins=False, verbose_level=1)), Bunch(verbose_level=2))
# cmd.run(Bunch(app_name=None, args=[], config_file='test_e2e.ini', daemon=False, monitor_restart=False, pid_file=None, reload=False, reload_interval=1, server=None, server_name=None, set_group=None, set_user=None, show_status=False, stop_daemon=False))
cat = app_model.Category(name='Default category 1')
DBSession.add(cat)
ds = model.Datasource(name='default_ds',
url=u'sqlite:///etl/tests/e2e/sales.db',
uid=randint(1, 100000))
model.DBSession.add(ds)
dataset1 = model.DataSet(name='products',
query='SELECT * FROM products',
datasource=ds,
uid=randint(1, 100000))
model.DBSession.add(dataset1)
dataset2 = model.DataSet(name='regions',
query='SELECT * FROM regions',
datasource=ds,
uid=randint(1, 100000))
model.DBSession.add(dataset2)
dataset3 = model.DataSet(name='sales',
query='SELECT * FROM sales',
datasource=ds,
uid=randint(1, 100000))
model.DBSession.add(dataset3)
dataset4 = model.DataSet(name='time',
query='SELECT * FROM time',
datasource=ds,
uid=randint(1, 100000))
model.DBSession.add(dataset4)
extraction = model.Extraction(name="Estrazione uno",
category=cat,
uid=randint(1, 100000))
model.DBSession.add(extraction)
DBSession.flush()
transaction.commit()
# create a new Chrome session
options = webdriver.ChromeOptions()
options.add_argument('--headless')
cls.driver = webdriver.Chrome(chrome_options=options)
cls.driver.implicitly_wait(30)
cls.driver.maximize_window()
def new(self):
dashboard = Dashboard()
dashboard.name = 'New dashboard'
DBSession.add(dashboard)
all_extractions = DBSession.query(Extraction).all()
return dict(
dashboard=dashboard,
form_dashboard_name=DashboardChangeName(),
form_dashboard_name_values=dashboard,
visualizationtypes=visualizationtypes,
all_extractions=all_extractions,
)
def evolve(self):
log.info('DashboardCreationEvolution migration running')
engine = create_engine(config.get('sqlalchemy.url'), echo=True)
DeclarativeBase.metadata.create_all(engine)
main = Dashboard()
main.name = 'main dashboard'
DBSession.add(main)
DBSession.flush()
transaction.commit()
def evolve(self):
log.info('TgappCategories migration running')
m = DBSession.query(Group).filter(
Group.group_name == "managers").first()
a = DBSession.query(Group).filter(Group.group_name == "admin").first()
p = DBSession.query(Permission).filter(
Permission.permission_name == "tgappcategories").first()
p.groups.append(m)
p.groups.append(a)
DBSession.flush()
transaction.commit()
def test_extraction_widget_pie_multicolor(self):
entities = self.create_dashboard(visualization='pie',
graph_axis='email_address,user_id')
DBSession.query(model.ExtractionStep).delete()
DBSession.flush()
transaction.commit()
response = self.app.get(
'/dashboard/extraction_widget/' + str(entities['dashboard']),
params=dict(uid=entities['dashboard_extraction_association']),
extra_environ=self.admin_env,
status=200)
assert 'default_ext' in response.text
assert len(response.html.find_all('div', class_="bk-root")) > 0
def edit(self, id=None):
try:
dashboard = DBSession.query(Dashboard).filter_by(uid=id).one()
except NoResultFound:
return abort(404, detail='dashboard not found')
all_extractions = DBSession.query(Extraction).all()
return dict(
dashboard=dashboard,
form_dashboard_name=DashboardChangeName(),
form_dashboard_name_values=dashboard,
visualizationtypes=visualizationtypes,
all_extractions=all_extractions,
)
def test_set_extraction_index(self):
entities = self.create_dashboard()
transaction.begin()
entities[
'dashboard_extraction_association_2'] = self.create_extraction_association(
entities['dashboard'], 1).uid
transaction.commit()
# Case 1 - wrong index < 0
self.app.put_json(
'/dashboard/set_extraction_index/' + str(entities['dashboard']), {
'uid': entities['dashboard_extraction_association'],
'index': -10
},
extra_environ=self.admin_env,
status=400)
# Case 2 - wrong index > last_index
self.app.put_json(
'/dashboard/set_extraction_index/' + str(entities['dashboard']), {
'uid': entities['dashboard_extraction_association'],
'index': 10
},
extra_environ=self.admin_env,
status=400)
# Case 3 - wrong association_id - NoResultFound
self.app.put_json('/dashboard/set_extraction_index/' +
str(entities['dashboard']), {
'uid': 123,
'index': 1
},
extra_environ=self.admin_env,
status=404)
# Case 4 - the last become the first and vice versa
response = self.app.put_json(
'/dashboard/set_extraction_index/' + str(entities['dashboard']), {
'uid': entities['dashboard_extraction_association_2'],
'index': 0
},
extra_environ=self.admin_env,
status=200)
assert response.json['de']['index'] == 0
assert response.json['other_de']['index'] == 1
assert DBSession.query(model.DashboardExtractionAssociation).get(
entities['dashboard_extraction_association']).index == 1
assert DBSession.query(model.DashboardExtractionAssociation).get(
entities['dashboard_extraction_association_2']).index == 0
def post(self, function=None, priority=None, options=None, **kwargs):
try:
if options is None:
options = {}
options = ExtractionStep.formfor(function).validate(options)
except Exception as e:
response.status = 412
return dict(errors=str(e))
DBSession.add(
ExtractionStep(extraction_id=tmpl_context.extraction.uid,
function=function,
priority=priority,
options=json.dumps(options)))
return dict()
def index(self, id=None):
try:
if not id:
dashboard = DBSession.query(Dashboard).first()
else:
dashboard = DBSession.query(Dashboard).filter_by(uid=id).one()
if not dashboard:
raise NoResultFound()
except NoResultFound:
return abort(404, detail='dashboard not found')
sorted_extractions = dashboard.extractions
sorted_extractions.sort(key=lambda e: e.index)
columned_extractions = gridify(sorted_extractions,
getter=lambda e: e.columns)
return dict(dashboard=dashboard,
columned_extractions=columned_extractions)
def reload_data(self, extraction):
extraction = DBSession.query(Extraction).get(extraction) or abort(404)
for dts in extraction.datasets:
empty_cache(dts.dataset.cache_key())
empty_cache(dts.dataset.cache_key(DEFAULT_LIMIT_FOR_PERFORMANCE))
flash('Data reloaded')
return redirect('/extractions/view/' + str(extraction.uid))
def set_default(cls, e_filter):
prev_default = DBSession.query(ExtractionFilter).filter(
ExtractionFilter.extraction_id == e_filter.extraction.uid,
ExtractionFilter.default == True).all()
for p in prev_default:
p.default = False
e_filter.default = True
def test_pipeline(self):
response = self.app.get('/editor/' + str(self.extraction) +
'/test_pipeline',
extra_environ=self.admin_env,
status=200)
extraction = DBSession.query(model.Extraction).get(self.extraction)
assert response.json['results'][0]['columns'] == [
'user_id', 'user_name', 'email_address', 'display_name',
'password', 'created'
]
assert response.json['results'][0]['data'] == [
[
'0', '1', 'admin', '*****@*****.**', 'Example Admin',
'fba0f3a1d7c6ca3afd2d60d68ca61412f20e770bce6ee7b...',
'2018-09-11 17:04:42.240496'
],
[
'1', '2', 'manager', '*****@*****.**',
'Example manager',
'ec26c4986d76515189e5e40d1eefa60ad9b9d20ecad1297...',
'2018-09-11 17:04:42.241971'
]
]
assert response.json['results'][0]['errors'] is None
assert len(extraction.steps) == len(response.json['results'])
def test_mongo_get_one(self):
self.ext1 = DBSession.merge(self.ext1)
response = self.app.get('/editor',
params=dict(extraction=self.ext1.uid),
extra_environ=self.admin_env,
status=200)
assert 'Editing mongo_ext1' in response.body.decode('utf-8')
def post(self, filter=None, extraction=None, **kwargs):
e_filter = ExtractionFilter(extraction=extraction,
name=filter.get('name'))
if filter.get('default'):
ExtractionFilter.set_default(e_filter)
DBSession.add(
ExtractionStep(extraction_filter=e_filter,
function='query',
priority=0,
options=json.dumps(
{'expression': filter.get('query')})))
DBSession.flush()
return dict(filter=e_filter)
def test_delete(self):
default_entities = self.create_dashboard()
custom_entities = self.create_dashboard(name='Custom dashboard')
# CASE 1:
# admin trying to delete the default dashboard => Error 400 Bad request
self.app.get('/dashboard/delete',
params=dict(dashboard_id=default_entities['dashboard']),
extra_environ=self.admin_env,
status=400)
# CASE 2
# Normal user try to delete a dashboard => Error 403 Forbidden
self.app.get('/dashboard/delete',
params=dict(dashboard_id=custom_entities['dashboard']),
extra_environ=self.manager_env,
status=403)
# Case 3
# Admin try to delete custonm dashboard (not default) => Success 302
response = self.app.get(
'/dashboard/delete',
params=dict(dashboard_id=custom_entities['dashboard']),
extra_environ=self.admin_env,
status=302).follow(extra_environ=self.admin_env)
assert 'Dahboard: Test purpose dashboard' in response.text
assert 'Edit Dashboard' in response.text
assert DBSession.query(model.Dashboard).get(
custom_entities['dashboard']) is None
class DataSetController(BaseController):
@expose('etl.templates.datasets.index')
@require(predicates.not_anonymous())
def index(self):
datasets = DBSession.query(DataSet).all()
return dict(datasets=datasets)
@expose('etl.templates.datasets.view')
@expose(content_type="text/csv")
@expose(content_type='application/json')
@require(predicates.Any(predicates.not_anonymous(), is_api_authenticated()))
@validate({'dataset': Convert(lambda v: DBSession.query(DataSet).filter_by(uid=v).one())},
error_handler=abort(404, error_handler=True))
def view(self, dataset, **kw):
try:
result = dataset.fetch()
except Exception as e:
log.exception('Failed to Retrieve Data')
flash('ERROR: %s' % e, 'error')
return dict(dataset=dataset, columns=[], results=[], count=0)
if request.response_type == 'text/csv':
return dateframe_to_csv(result)
elif request.response_type == 'application/json':
return dateframe_to_json(result)
return dict(
dataset=dataset,
columns=list(result.columns),
results=list(result.itertuples()),
count=len(result),
py2=py_version < 3
)
def extractions(self, dashboard_id, **kw):
try:
dashboard = DBSession.query(Dashboard).filter_by(
uid=dashboard_id).one()
except NoResultFound:
return abort(404, detail='dashboard not found')
extractions = [de.extraction for de in dashboard.extractions]
return dict(extractions=extractions, dashboard=dashboard)
def put(self, filter=None, extraction=None, **kwargs):
e_filter = DBSession.query(ExtractionFilter).get(filter.get('uid'))
if not e_filter:
return abort(404)
step = DBSession.query(ExtractionStep).get(
filter.get('steps')[0]['uid'])
if not step:
return abort(404)
e_filter.extraction = extraction
e_filter.name = filter.get('name')
if filter.get('default'):
ExtractionFilter.set_default(e_filter)
else:
e_filter.default = False
exp = {'expression': filter.get('query')}
step.options = json.dumps(exp)
return dict(filter=e_filter)
def save_name(self, **kw):
try:
dashboard = DBSession.query(Dashboard).filter_by(
uid=kw['uid']).one()
except NoResultFound:
return abort(404, detail='dashboard not found')
dashboard.name = kw['name']
return redirect('/dashboard/edit/%s' % kw['uid'])
def evolve(self):
for step in DBSession.query(ExtractionStep).filter_by(
function='slice').all():
options = json.loads(step.options)
if isinstance(options, list):
step.options = json.dumps({'fields': options})
for step in DBSession.query(ExtractionStep).filter_by(
function='concatcols').all():
options = json.loads(step.options)
if isinstance(options, list):
step.options = json.dumps({'columns': options})
for step in DBSession.query(ExtractionStep).filter_by(
function='setvalue').all():
options = json.loads(step.options)
if isinstance(options, list):
step.options = json.dumps({
'field': options[0],
'value': options[1]
})
for step in DBSession.query(ExtractionStep).filter_by(
function='striptime').all():
options = json.loads(step.options)
if isinstance(options, list):
step.options = json.dumps({'field': options[0]})
for step in DBSession.query(ExtractionStep).filter_by(
function='stripday').all():
options = json.loads(step.options)
if isinstance(options, list):
step.options = json.dumps({'field': options[0]})
for step in DBSession.query(ExtractionStep).filter_by(
function='rename').all():
options = json.loads(step.options)
if isinstance(options, list):
step.options = json.dumps({
'source': options[0],
'target': options[1]
})
for step in DBSession.query(ExtractionStep).filter_by(
function='setindex').all():
options = json.loads(step.options)
if isinstance(options, list):
step.options = json.dumps({'index': options[0]})
DBSession.flush()
transaction.commit()
def test_put(self):
self.filter_data.update({'uid': 1, 'steps': [{'uid': self.step}]})
self.app.put_json('/extractions/filter/put.json', {
'filter': self.filter_data,
'extraction': self.extraction
},
extra_environ=self.admin_env)
flt = DBSession.query(model.ExtractionFilter).get(self.filter)
assert flt.name == 'custom_flt'