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_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 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)