def _execute_single_statement(context, sql_string, where_values):
if not datastore_helpers.is_single_statement(sql_string):
raise ValidationError({'query': ['Query is not a single statement.']})
results = context['connection'].execute(sql_string, [where_values])
return results
def test_is_single_statement(self):
singles = ['SELECT * FROM footable',
'SELECT * FROM "bartable"',
'SELECT * FROM "bartable";',
'SELECT * FROM "bart;able";',
"select 'foo'||chr(59)||'bar'"]
multiples = ['SELECT * FROM abc; SET LOCAL statement_timeout to'
'SET LOCAL statement_timeout to; SELECT * FROM abc',
'SELECT * FROM "foo"; SELECT * FROM "abc"']
for single in singles:
assert datastore_helpers.is_single_statement(single) is True
for multiple in multiples:
assert datastore_helpers.is_single_statement(multiple) is False
def test_is_single_statement(self):
singles = ['SELECT * FROM footable',
'SELECT * FROM "bartable"',
'SELECT * FROM "bartable";',
'SELECT * FROM "bart;able";',
"select 'foo'||chr(59)||'bar'"]
multiples = ['SELECT * FROM abc; SET LOCAL statement_timeout to'
'SET LOCAL statement_timeout to; SELECT * FROM abc',
'SELECT * FROM "foo"; SELECT * FROM "abc"']
for single in singles:
assert datastore_helpers.is_single_statement(single) is True
for multiple in multiples:
assert datastore_helpers.is_single_statement(multiple) is False
def _execute_single_statement(context, sql_string, where_values):
if not datastore_helpers.is_single_statement(sql_string):
raise ValidationError({"query": ["Query is not a single statement."]})
results = context["connection"].execute(sql_string, [where_values])
return results
def query_extent(data_dict, connection=None):
""" Return the spatial query extent of a datastore search
@param data_dict: Dictionary defining the search
@returns a dictionary defining:
{
total_count: The total number of rows in the query,
geom_count: The number of rows that have a geom,
bounds: ((lat min, long min), (lat max, long max)) for the
queries rows
"""
r = toolkit.get_action('datastore_search')({}, data_dict)
if 'total' not in r or r['total'] == 0:
return {
'total_count': 0,
'geom_count': 0,
'bounds': None
}
result = {
'total_count': r['total'],
'bounds': None
}
field_types = dict([(f['id'], f['type']) for f in r['fields']])
field_types['_id'] = 'int'
# Call plugin to obtain correct where statement
(ts_query, where_clause, values) = invoke_search_plugins(data_dict, field_types)
# Prepare and run our query
query = """
SELECT COUNT(r) AS count,
ST_YMIN(ST_EXTENT(r)) AS ymin,
ST_XMIN(ST_EXTENT(r)) AS xmin,
ST_YMAX(ST_EXTENT(r)) AS ymax,
ST_XMAX(ST_EXTENT(r)) AS xmax
FROM (
SELECT "{geom_field}" AS r
FROM "{resource_id}" {ts_query}
{where_clause}
) _tilemap_sub
""".format(
geom_field=config['postgis.field'],
resource_id=data_dict['resource_id'],
where_clause=where_clause,
ts_query=ts_query
)
if not is_single_statement(query):
raise datastore_db.ValidationError({
'query': ['Query is not a single statement.']
})
with get_connection(connection) as c:
query_result = c.execute(query, values)
r = query_result.fetchone()
result['geom_count'] = r['count']
if result['geom_count'] > 0:
result['bounds'] = ((r['ymin'], r['xmin']), (r['ymax'], r['xmax']))
return result
def datastore_search_sql(context, data_dict):
'''Execute SQL queries on the DataStore.
The datastore_search_sql action allows a user to search data in a resource
or connect multiple resources with join expressions. The underlying SQL
engine is the
`PostgreSQL engine
<http://www.postgresql.org/docs/9.1/interactive/sql/.html>`_. There is an
enforced timeout on SQL queries to avoid an unintended DOS. DataStore
resource that belong to a private CKAN resource cannot be searched with
this action. Use :meth:`~ckanext.datastore.logic.action.datastore_search`
instead.
.. note::
This action is only available when using PostgreSQL 9.X and using a
read-only user on the database. It is not available in :ref:`legacy
mode<legacy-mode>`.
:param sql: a single SQL select statement
:type sql: string
**Results:**
The result of this action is a dictionary with the following keys:
:rtype: A dictionary with the following keys
:param fields: fields/columns and their extra metadata
:type fields: list of dictionaries
:param records: list of matching results
:type records: list of dictionaries
'''
sql = _get_or_bust(data_dict, 'sql')
if not datastore_helpers.is_single_statement(sql):
raise p.toolkit.ValidationError({
'query': ['Query is not a single statement.']
})
p.toolkit.check_access('datastore_search_sql', context, data_dict)
data_dict['connection_url'] = pylons.config['ckan.datastore.read_url']
result = db.search_sql(context, data_dict)
result.pop('id', None)
result.pop('connection_url')
return result
def run_stats_query(select, resource_id, ts_query, where_clause, group_by,
values):
query = 'SELECT {select} FROM "{resource_id}" {ts_query} {where_clause} {group_by}'.format(
select=select,
resource_id=resource_id,
where_clause=where_clause,
ts_query=ts_query,
group_by=group_by)
if not is_single_statement(query):
raise datastore_db.ValidationError(
{'query': ['Query is not a single statement.']})
# The interfaces.IDatastore return SQL to be directly executed
# So just use an sqlalchemy connection, rather than the API
# So we don't have to faff around converting to pure SQL
engine = _get_engine()
with engine.begin() as connection:
try:
query_result = connection.execute(query, values)
return query_result.fetchall()
except (DatabaseError, DBAPIError):
pass
def run_stats_query(select, resource_id, ts_query, where_clause, group_by, values):
query = 'SELECT {select} FROM "{resource_id}" {ts_query} {where_clause} {group_by}'.format(
select=select,
resource_id=resource_id,
where_clause=where_clause,
ts_query=ts_query,
group_by=group_by
)
if not is_single_statement(query):
raise datastore_db.ValidationError({
'query': ['Query is not a single statement.']
})
# The interfaces.IDatastore return SQL to be directly executed
# So just use an sqlalchemy connection, rather than the API
# So we don't have to faff around converting to pure SQL
engine = _get_engine()
with engine.begin() as connection:
try:
query_result = connection.execute(query, values)
return query_result.fetchall()
except (DatabaseError, DBAPIError):
pass
