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)