def get_data_sets_from_sql(model, raw_where_clause, parsed_where_clause=None):
"""
Gets a data set for the given model,using provided where_clause, using
raw sql and the QueryParser. Datasets are ordered by id and limited to 10
entries.
returns a dataset based on raw execution and a dataset based on parsed execution
params:
model - django model being queried
raw_where_clause - string of sql query to be used for raw data set
parsed_where_clause - string of sql query to be used for parsed data set
"""
if not parsed_where_clause:
parsed_where_clause = raw_where_clause
raw_sql = "SELECT * FROM {} {} ORDER BY ID LIMIT 10"\
.format(model._meta.db_table, raw_where_clause)
parsed_sql = "SELECT * FROM {} {}"\
.format(model._meta.db_table, parsed_where_clause)
raw_dataset = model.objects.raw(raw_sql.replace("%", "%%")) # % signs need to be escaped or the connection expects parameters
f = QueryParser(model, parsed_sql)
# to debug test_equality_operator error:
#print parsed_sql, f.where_conditions
parsed_dataset = f.extend_query(model.objects.order_by('id'))[:10]
return raw_dataset, parsed_dataset
def get_data_sets_from_sql(model, raw_where_clause, parsed_where_clause=None):
"""
Gets a data set for the given model,using provided where_clause, using
raw sql and the QueryParser. Datasets are ordered by id and limited to 10
entries.
returns a dataset based on raw execution and a dataset based on parsed execution
params:
model - django model being queried
raw_where_clause - string of sql query to be used for raw data set
parsed_where_clause - string of sql query to be used for parsed data set
"""
if not parsed_where_clause:
parsed_where_clause = raw_where_clause
raw_sql = "SELECT * FROM {} {} ORDER BY ID LIMIT 10"\
.format(model._meta.db_table, raw_where_clause)
parsed_sql = "SELECT * FROM {} {}"\
.format(model._meta.db_table, parsed_where_clause)
raw_dataset = model.objects.raw(
raw_sql.replace("%", "%%")
) # % signs need to be escaped or the connection expects parameters
f = QueryParser(model, parsed_sql)
# to debug test_equality_operator error:
#print parsed_sql, f.where_conditions
parsed_dataset = f.extend_query(model.objects.order_by('id'))[:10]
return raw_dataset, parsed_dataset
def test_geo_query_new(self, **kwargs):
sql = "WHERE point within buffer(-122.246, 37.896, 1000)" #return all photos within 1,000 meters
f = QueryParser(Photo, sql)
parsed_dataset = f.extend_query(Photo.objects.order_by('id'))
#expect two photos to return
self.assertEqual(len(parsed_dataset), 2)
sql = "WHERE point WITHIN buffer(-122.246, 37.896, 10)" #return all photos within 10 meters
f = QueryParser(Photo, sql)
parsed_dataset = f.extend_query(Photo.objects.order_by('id'))
#expect 1 photo to return
self.assertEqual(len(parsed_dataset), 1)
def test_geo_query_new(self, **kwargs):
sql = "WHERE point within buffer(-122.246, 37.896, 1000)" #return all photos within 1,000 meters
f = QueryParser(Photo, sql)
parsed_dataset = f.extend_query(Photo.objects.order_by('id'))
#expect two photos to return
self.assertEqual(len(parsed_dataset), 2)
sql = "WHERE point WITHIN buffer(-122.246, 37.896, 10)" #return all photos within 10 meters
f = QueryParser(Photo, sql)
parsed_dataset = f.extend_query(Photo.objects.order_by('id'))
#expect 1 photo to return
self.assertEqual(len(parsed_dataset), 1)
def compare_sql(self, model, raw_where, parsed_where=None):
raw_dataset, parsed_dataset = get_data_sets_from_sql(
model, raw_where, parsed_where)
n = 0
for r, p in zip(raw_dataset, parsed_dataset):
n += 1
self.assertEqual(r.id, p.id)
if n == 0:
raw_count = len(list(raw_dataset))
parsed_count = len(list(parsed_dataset))
# output incorrectly parsed SQL and raise failure error:
if not parsed_where:
parsed_where = raw_where
parsed_sql = "SELECT * FROM {} {}".format(model._meta.db_table,
parsed_where)
f = QueryParser(model, parsed_where)
self.fail("No results to compare - raw:{} parsed:{};\n\
Where clause '{}' parsed to: '{}'".format(
raw_count, parsed_count, parsed_where, f.where_conditions))
