def get_distinct_geometries_across_time(self, *kwargs):
time_query = 'SELECT DISTINCT ST_AsText({geom_field}), COUNT(0) FROM {table} GROUP BY ST_AsText({geom_field})'
time_query = time_query.format(geom_field=GEOM_FIELD_NAME, table=self.table)
with connection.cursor() as c:
c.execute(time_query)
response = dictfetchall(c)
return response
def get_distinct_geometries_across_time(self, *kwargs):
time_query = 'SELECT DISTINCT ST_AsText({geom_field}), COUNT(0) FROM {table} GROUP BY ST_AsText({geom_field})'
time_query = time_query.format(geom_field=POINT_FIELD_NAME, table=self.table)
with get_cursor() as c:
c.execute(time_query)
response = dictfetchall(c)
return response
def perform_query(self, limit=0, offset=0, **kwargs):
limit, offset = max(limit, 0), max(offset, 0)
count_only = bool(kwargs.pop('count_only', False))
ids_only = bool(kwargs.pop('ids_only', False))
return_fields = [f.strip() for f in list(kwargs.get('return_fields') or '*')]
return_geometry = bool(kwargs.get('return_geometry', True))
out_sr = kwargs.get('out_sr') or WEB_MERCATOR_SRID
additional_where_clause = kwargs.get('additional_where_clause')
additional_where_clause = additional_where_clause.replace('"', '') if additional_where_clause else None
# Break out fields and DESC / ASC modifiers
order_by_field_objs = []
for field in [f.strip() for f in kwargs.get('order_by_fields') or '']:
m = re.match('(\S*)\s?(asc|desc)?', field, re.IGNORECASE)
field_obj = {'field_name': m.group(1)}
try:
field_obj['order_modifier'] = m.group(2)
except IndexError:
pass # No modifier
order_by_field_objs.append(field_obj)
order_by_field_names = [f['field_name'] for f in order_by_field_objs]
# These are the possible points of SQL injection. All other dynamically composed pieces of SQL are
# constructed using items within the database, or are escaped using the database engine.
if not ids_only:
include_related = bool(kwargs.get('object_ids'))
self._validate_fields(return_fields, include_related)
self._validate_fields(order_by_field_names, include_related)
self._validate_where_clause(additional_where_clause)
# Build SELECT, JOIN, WHERE and ORDER BY from inputs
if count_only:
return_fields = order_by_field_names = []
select_fields = 'COUNT(0)'
elif ids_only:
return_fields = order_by_field_names = [self.object_id_field]
select_fields = 'DISTINCT {0}'.format(self._alias_fields(return_fields))
else:
if self.object_id_field not in return_fields:
return_fields.insert(0, self.object_id_field)
select_fields = self._expand_fields(return_fields)
if return_geometry:
select_fields += ', ST_AsText(ST_Transform("source"."dbasin_geom", {0}))'.format(out_sr)
join, related_tables = self._build_join_clause(return_fields, additional_where_clause)
where, query_params = self._build_where_clause(additional_where_clause, count_only, **kwargs)
order_by = '' if count_only else self._build_order_by_clause(
field_objs=order_by_field_objs, related_tables=(None if ids_only else related_tables)
)
query_clause = 'SELECT {fields} FROM "{table}" AS "source" {join} {where} {order_by} {limit} {offset}'
query_clause = query_clause.format(
fields=select_fields, table=self.table, join=join.strip(), where=where.strip(), order_by=order_by,
limit='' if limit == 0 else 'LIMIT {limit}'.format(limit=limit + 1),
offset='' if offset == 0 else 'OFFSET {offset}'.format(offset=offset)
)
# Execute query with optional limit and offset, and prepare return data
with connection.cursor() as c:
c.execute(query_clause, query_params)
queried_data = dictfetchall(c)
limited_data = 0 < limit < len(queried_data)
queried_data = queried_data[:-1] if limited_data else queried_data
return {'data': queried_data, 'exceeded_limit': limited_data}
def perform_query(self, limit=0, offset=0, **kwargs):
limit, offset = max(limit, 0), max(offset, 0)
count_only = bool(kwargs.pop('count_only', False))
ids_only = bool(kwargs.pop('ids_only', False))
return_fields = [f.strip() for f in list(kwargs.get('return_fields') or '*')]
return_geometry = bool(kwargs.get('return_geometry', True))
out_sr = kwargs.get('out_sr') or WEB_MERCATOR_SRID
additional_where_clause = kwargs.get('additional_where_clause')
additional_where_clause = additional_where_clause.replace('"', '') if additional_where_clause else None
# Break out fields and DESC / ASC modifiers
order_by_field_objs = []
for field in [f.strip() for f in kwargs.get('order_by_fields') or '']:
m = re.match('(\S*)\s?(asc|desc)?', field, re.IGNORECASE)
field_obj = {'field_name': m.group(1)}
try:
field_obj['order_modifier'] = m.group(2)
except IndexError:
pass # No modifier
order_by_field_objs.append(field_obj)
order_by_field_names = [f['field_name'] for f in order_by_field_objs]
# These are the possible points of SQL injection. All other dynamically composed pieces of SQL are
# constructed using items within the database, or are escaped using the database engine.
if not ids_only:
include_related = bool(kwargs.get('object_ids'))
self._validate_fields(return_fields, include_related)
self._validate_fields(order_by_field_names, include_related)
self._validate_where_clause(additional_where_clause)
# Build SELECT, JOIN, WHERE and ORDER BY from inputs
if count_only:
return_fields = order_by_field_names = []
select_fields = 'COUNT(0)'
elif ids_only:
return_fields = order_by_field_names = [self.object_id_field]
select_fields = 'DISTINCT {0}'.format(self._alias_fields(return_fields))
else:
if self.object_id_field not in return_fields:
return_fields.insert(0, self.object_id_field)
select_fields = self._expand_fields(return_fields)
if return_geometry:
select_fields += ', ST_AsText(ST_Transform("source"."dbasin_geom", {0}))'.format(out_sr)
join, related_tables = self._build_join_clause(return_fields, additional_where_clause)
where, query_params = self._build_where_clause(additional_where_clause, count_only, **kwargs)
order_by = '' if count_only else self._build_order_by_clause(
field_objs=order_by_field_objs, related_tables=(None if ids_only else related_tables)
)
query_clause = 'SELECT {fields} FROM "{table}" AS "source" {join} {where} {order_by} {limit} {offset}'
query_clause = query_clause.format(
fields=select_fields, table=self.table, join=join.strip(), where=where.strip(), order_by=order_by,
limit='' if limit == 0 else 'LIMIT {limit}'.format(limit=limit + 1),
offset='' if offset == 0 else 'OFFSET {offset}'.format(offset=offset)
)
# Execute query with optional limit and offset, and prepare return data
with get_cursor() as c:
c.execute(query_clause, query_params)
queried_data = dictfetchall(c)
limited_data = 0 < limit < len(queried_data)
queried_data = queried_data[:-1] if limited_data else queried_data
return {'data': queried_data, 'exceeded_limit': limited_data}
