def test_generate_query_string_for_data_with_start_index_filter(self):
start_index = 2
expected_sql = "SELECT json FROM logger_instance WHERE"\
" xform_id = %s AND CAST(json->>%s AS INT) > %s AND"\
" CAST(json->>%s AS INT) < %s AND deleted_at IS NULL "\
"ORDER BY id OFFSET %s"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
start_index,
self.limit,
self.last_submission_time,
self.all_data,
self.sort)
self.assertEquals(sql, expected_sql)
records = [record for record in DataView.query_iterator(sql,
columns,
params,
self.count)]
self.assertEquals(len(records), 1)
self.assertIn('name', records[0])
self.assertIn('age', records[0])
self.assertIn('gender', records[0])
self.assertNotIn('pizza_type', records[0])
def test_generate_query_string_for_data_with_start_index_filter(self):
start_index = 2
expected_sql = "SELECT json FROM logger_instance WHERE"\
" xform_id = %s AND CAST(json->>%s AS INT) > %s AND"\
" CAST(json->>%s AS INT) < %s AND deleted_at IS NULL "\
"ORDER BY id OFFSET %s"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
start_index,
self.limit,
self.last_submission_time,
self.all_data,
self.sort)
self.assertEquals(sql, expected_sql)
records = [record for record in DataView.query_iterator(sql,
columns,
params,
self.count)]
self.assertEquals(len(records), 1)
self.assertIn('name', records[0])
self.assertIn('age', records[0])
self.assertIn('gender', records[0])
self.assertNotIn('pizza_type', records[0])
def test_get_charts_data_with_empty_query(self):
data = {
'name': "My DataView",
'xform': 'http://testserver/api/v1/forms/%s' % self.xform.pk,
'project':
'http://testserver/api/v1/projects/%s' % self.project.pk,
'columns': '["name", "age", "gender"]',
'query': '[]'
}
self._create_dataview(data)
self.view = DataViewViewSet.as_view({
'get': 'charts',
})
data_view_data = DataView.query_data(self.data_view)
request = self.factory.get('/charts', **self.extra)
response = self.view(request, pk=self.data_view.pk)
self.assertEqual(response.status_code, 200)
data = {'field_name': 'age'}
request = self.factory.get('/charts', data, **self.extra)
response = self.view(request, pk=self.data_view.pk)
self.assertEqual(response.status_code, 200)
self.assertNotEqual(response.get('Cache-Control'), None)
self.assertEqual(response.data['field_type'], 'integer')
self.assertEqual(response.data['field_name'], 'age')
self.assertEqual(response.data['data_type'], 'numeric')
self.assertEqual(len(response.data['data']), len(data_view_data))
def test_get_charts_data(self):
self._create_dataview()
self.view = DataViewViewSet.as_view({
'get': 'charts',
})
data_view_data = DataView.query_data(self.data_view)
request = self.factory.get('/charts', **self.extra)
response = self.view(request, pk=self.data_view.pk)
self.assertEqual(response.status_code, 200)
data = {'field_name': 'age'}
request = self.factory.get('/charts', data, **self.extra)
response = self.view(request, pk=self.data_view.pk)
self.assertEqual(response.status_code, 200)
self.assertNotEqual(response.get('Cache-Control'), None)
self.assertEqual(response.data['field_type'], 'integer')
self.assertEqual(response.data['field_name'], 'age')
self.assertEqual(response.data['data_type'], 'numeric')
self.assertEqual(len(response.data['data']), len(data_view_data))
data = {'field_xpath': 'age'}
request = self.factory.get('/charts', data, **self.extra)
response = self.view(request, pk=self.data_view.pk)
self.assertEqual(response.status_code, 200)
self.assertNotEqual(response.get('Cache-Control'), None)
self.assertEqual(response.data['field_type'], 'integer')
self.assertEqual(response.data['field_name'], 'age')
self.assertEqual(response.data['data_type'], 'numeric')
self.assertEqual(len(response.data['data']), len(data_view_data))
def data(self, request, format='json', **kwargs):
"""Retrieve the data from the xform using this dataview"""
start = request.GET.get("start")
limit = request.GET.get("limit")
count = request.GET.get("count")
sort = request.GET.get("sort")
query = request.GET.get("query")
export_type = self.kwargs.get('format', request.GET.get("format"))
self.object = self.get_object()
if export_type is None or export_type in ['json', 'debug']:
data = DataView.query_data(self.object, start, limit,
str_to_bool(count), sort=sort,
filter_query=query)
if 'error' in data:
raise ParseError(data.get('error'))
serializer = self.get_serializer(data, many=True)
return Response(serializer.data)
else:
return custom_response_handler(request, self.object.xform, query,
export_type,
dataview=self.object)
def data(self, request, format='json', **kwargs):
"""Retrieve the data from the xform using this dataview"""
start = request.GET.get("start")
limit = request.GET.get("limit")
count = request.GET.get("count")
sort = request.GET.get("sort")
query = request.GET.get("query")
export_type = self.kwargs.get('format', request.GET.get("format"))
self.object = self.get_object()
if export_type is None or export_type in ['json', 'debug']:
data = DataView.query_data(self.object,
start,
limit,
str_to_bool(count),
sort=sort,
filter_query=query)
if 'error' in data:
raise ParseError(data.get('error'))
serializer = self.get_serializer(data, many=True)
return Response(serializer.data)
else:
return custom_response_handler(request,
self.object.xform,
query,
export_type,
dataview=self.object)
def get_last_submission_time(self, obj):
if obj:
last_submission_time = cache.get('{}{}'.format(
DATAVIEW_LAST_SUBMISSION_TIME, obj.xform.pk))
if last_submission_time:
return last_submission_time
last_submission_rows = DataView.query_data(
obj, last_submission_time=True) # data is returned as list
if 'error' in last_submission_rows:
raise ParseError(last_submission_rows.get('error'))
if len(last_submission_rows):
last_submission_row = last_submission_rows[0]
if LAST_SUBMISSION_TIME in last_submission_row:
last_submission_time = last_submission_row.get(
LAST_SUBMISSION_TIME)
cache.set(
'{}{}'.format(
DATAVIEW_LAST_SUBMISSION_TIME, obj.xform.pk),
last_submission_time)
return last_submission_time
return None
def get_last_submission_time(self, obj):
if obj:
last_submission_time = cache.get('{}{}'.format(
DATAVIEW_LAST_SUBMISSION_TIME, obj.xform.pk))
if last_submission_time:
return last_submission_time
last_submission_rows = DataView.query_data(
obj, last_submission_time=True) # data is returned as list
if 'error' in last_submission_rows:
raise ParseError(last_submission_rows.get('error'))
if len(last_submission_rows):
last_submission_row = last_submission_rows[0]
if LAST_SUBMISSION_TIME in last_submission_row:
last_submission_time = last_submission_row.get(
LAST_SUBMISSION_TIME)
cache.set(
'{}{}'.format(DATAVIEW_LAST_SUBMISSION_TIME,
obj.xform.pk), last_submission_time)
return last_submission_time
return None
def _additional_data_view_filters(data_view):
where, where_params = DataView._get_where_clause(data_view)
data_view_where = ""
if where:
data_view_where = u" AND " + u" AND ".join(where)
for it in where_params:
data_view_where = data_view_where.replace('%s', "'{}'".format(it), 1)
return data_view_where
def _additional_data_view_filters(data_view):
where, where_params = DataView._get_where_clause(data_view)
data_view_where = ""
if where:
data_view_where = u" AND " + u" AND ".join(where)
for it in where_params:
data_view_where = data_view_where.replace('%s', "'{}'".format(it), 1)
return data_view_where
def test_generate_query_string_for_data_with_sort_column_desc(self):
sort = '{"age": -1}'
expected_sql = "SELECT json FROM logger_instance WHERE"\
" xform_id = %s AND CAST(json->>%s AS INT) > %s AND"\
" CAST(json->>%s AS INT) < %s AND deleted_at IS NULL"\
" ORDER BY json->>%s DESC"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
self.start_index,
self.limit,
self.last_submission_time,
self.all_data,
sort)
self.assertEquals(sql, expected_sql)
records = [record for record in DataView.query_iterator(sql,
columns,
params,
self.count)]
self.assertTrue(self.is_sorted_desc([r.get("age") for r in records]))
def test_generate_query_string_for_data_with_limit_filter(self):
limit_filter = 1
expected_sql = "SELECT json FROM logger_instance"\
" WHERE xform_id = %s AND CAST(json->>%s AS INT) > %s"\
" AND CAST(json->>%s AS INT) < %s AND deleted_at "\
"IS NULL ORDER BY id LIMIT %s"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
self.start_index,
limit_filter,
self.last_submission_time,
self.all_data,
self.sort)
self.assertEquals(sql, expected_sql)
records = [record for record in DataView.query_iterator(sql,
columns,
params,
self.count)]
self.assertEquals(len(records), limit_filter)
def test_generate_query_string_for_data_with_sort_column_desc(self):
sort = '{"age": -1}'
expected_sql = "SELECT json FROM logger_instance WHERE"\
" xform_id = %s AND CAST(json->>%s AS INT) > %s AND"\
" CAST(json->>%s AS INT) < %s AND deleted_at IS NULL"\
" ORDER BY json->>%s DESC"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
self.start_index,
self.limit,
self.last_submission_time,
self.all_data,
sort)
self.assertEquals(sql, expected_sql)
records = [record for record in DataView.query_iterator(sql,
columns,
params,
self.count)]
self.assertTrue(self.is_sorted_desc([r.get("age") for r in records]))
def test_generate_query_string_for_data_with_limit_filter(self):
limit_filter = 1
expected_sql = "SELECT json FROM logger_instance"\
" WHERE xform_id = %s AND CAST(json->>%s AS INT) > %s"\
" AND CAST(json->>%s AS INT) < %s AND deleted_at "\
"IS NULL ORDER BY id LIMIT %s"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
self.start_index,
limit_filter,
self.last_submission_time,
self.all_data,
self.sort)
self.assertEquals(sql, expected_sql)
records = [record for record in DataView.query_iterator(sql,
columns,
params,
self.count)]
self.assertEquals(len(records), limit_filter)
def test_generate_query_string_for_data_without_filter(self):
expected_sql = "SELECT json->%s,json->%s,json->%s,json->%s,json->%s,"\
"json->%s,json->%s,json->%s FROM "\
"logger_instance WHERE xform_id = %s AND "\
"CAST(json->>%s AS INT) > %s AND "\
"CAST(json->>%s AS INT) < %s AND deleted_at IS NULL"\
" ORDER BY id"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
self.start_index,
self.limit,
self.last_submission_time,
self.all_data,
self.sort)
self.assertEquals(sql, expected_sql)
self.cursor.execute(sql, [unicode(i) for i in (columns + params)])
results = self.cursor.fetchall()
self.assertEquals(len(results), 3)
def test_generate_query_string_for_data_without_filter(self):
expected_sql = "SELECT json FROM "\
"logger_instance WHERE xform_id = %s AND "\
"CAST(json->>%s AS INT) > %s AND "\
"CAST(json->>%s AS INT) < %s AND deleted_at IS NULL"\
" ORDER BY id"
(sql, columns, params) = DataView.generate_query_string(
self.data_view,
self.start_index,
self.limit,
self.last_submission_time,
self.all_data,
self.sort)
self.assertEquals(sql, expected_sql)
self.assertEqual(len(columns), 8)
self.cursor.execute(sql, [str(i) for i in (params)])
results = self.cursor.fetchall()
self.assertEquals(len(results), 3)
def get_count(self, obj):
if obj:
count_dict = cache.get('{}{}'.format(DATAVIEW_COUNT, obj.xform.pk))
if count_dict:
if obj.pk in count_dict:
return count_dict.get(obj.pk)
else:
count_dict = {}
count_rows = DataView.query_data(obj, count=True)
if 'error' in count_rows:
raise ParseError(count_rows.get('error'))
count_row = count_rows[0]
if 'count' in count_row:
count = count_row.get('count')
count_dict.setdefault(obj.pk, count)
cache.set('{}{}'.format(DATAVIEW_COUNT, obj.xform.pk),
count_dict)
return count
return None
def get_count(self, obj):
if obj:
count_dict = cache.get('{}{}'.format(DATAVIEW_COUNT, obj.xform.pk))
if count_dict:
if obj.pk in count_dict:
return count_dict.get(obj.pk)
else:
count_dict = {}
count_rows = DataView.query_data(obj, count=True)
if 'error' in count_rows:
raise ParseError(count_rows.get('error'))
count_row = count_rows[0]
if 'count' in count_row:
count = count_row.get('count')
count_dict.setdefault(obj.pk, count)
cache.set('{}{}'.format(DATAVIEW_COUNT, obj.xform.pk),
count_dict)
return count
return None
