def find_by_query(self, query, access_args=None):
"""
Find resources given a datastore query expression dict.
@param query a dict representation of a datastore query
@retval list of resource ids or resource objects matching query (dependent on id_only value)
"""
qual_ds_name = self._get_datastore_name()
query_ds_sub = query["query_args"].get("ds_sub", None)
query_format = query["query_args"].get("format", "")
pqb = PostgresQueryBuilder(query, qual_ds_name)
if self.profile == DataStore.DS_PROFILE.RESOURCES and not query_ds_sub:
table_alias = qual_ds_name if query_format != "complex" else "base"
pqb.where = self._add_access_filter(access_args, qual_ds_name, pqb.where, pqb.values,
add_where=False, tablealias=table_alias)
if self.profile == DataStore.DS_PROFILE.RESOURCES:
pqb.where = self._add_deleted_filter(pqb.table_aliases[0], query_ds_sub,
pqb.where, pqb.values,
with_deleted=query["query_args"].get("with_deleted", False) is True)
with self.pool.cursor(**self.cursor_args) as cur:
exec_query = pqb.get_query()
cur.execute(exec_query, pqb.get_values())
rows = cur.fetchall()
query_str = cur.query if len(cur.query) < 2000 else cur.query[:1000] + "...[" + str(len(cur.query) - 1200) + "]..." + cur.query[-200:]
log.info("find_by_query() QUERY: %s (%s rows)", query_str, cur.rowcount)
query_res = {}
query["_result"] = query_res
query_res["statement_gen"] = exec_query
query_res["statement_sql"] = cur.query
query_res["rowcount"] = cur.rowcount
id_only = query["query_args"].get("id_only", True)
if query_format == "complex" and pqb.has_basic_cols:
# Return format is list of lists
if id_only:
res_vals = [[self._prep_id(row[0])] + list(row[1:]) for row in rows]
else:
res_vals = [[self._persistence_dict_to_ion_object(row[1])] + list(rows[2:]) for row in rows]
elif query_format == "complex":
res_vals = [list(row) for row in rows]
else:
if id_only:
res_vals = [self._prep_id(row[0]) for row in rows]
else:
res_vals = [self._persistence_dict_to_ion_object(row[-1]) for row in rows]
return res_vals
def find_resources_mult(self, query):
"""
Find resources given a datastore query expression dict.
@param query a dict representation of a datastore query
@retval list of resource ids or resource objects matching query (dependent on id_only value)
"""
qual_ds_name = self._get_datastore_name()
pqb = PostgresQueryBuilder(query, qual_ds_name)
with self.pool.cursor(**self.cursor_args) as cur:
cur.execute(pqb.get_query(), pqb.get_values())
rows = cur.fetchall()
log.info("find_resources_mult() QUERY: %s (%s rows)", cur.query, cur.rowcount)
id_only = query["query_args"].get("id_only", True)
if id_only:
res_ids = [self._prep_id(row[0]) for row in rows]
return res_ids
else:
res_docs = [self._persistence_dict_to_ion_object(row[-1]) for row in rows]
return res_docs
def find_by_query(self, query, access_args=None):
"""
Find resources given a datastore query expression dict.
@param query a dict representation of a datastore query
@retval list of resource ids or resource objects matching query (dependent on id_only value)
"""
qual_ds_name = self._get_datastore_name()
pqb = PostgresQueryBuilder(query, qual_ds_name)
if self.profile == DataStore.DS_PROFILE.RESOURCES and not query["query_args"].get("ds_sub", None):
pqb.where = self._add_access_filter(access_args, qual_ds_name, pqb.where, pqb.values, add_where=False)
if self.profile == DataStore.DS_PROFILE.RESOURCES:
pqb.where = self._add_deleted_filter(pqb.basetable, query["query_args"].get("ds_sub", None),
pqb.where, pqb.values,
show_all=query["query_args"].get("show_all", False))
with self.pool.cursor(**self.cursor_args) as cur:
exec_query = pqb.get_query()
cur.execute(pqb.get_query(), pqb.get_values())
rows = cur.fetchall()
log.info("find_by_query() QUERY: %s (%s rows)", cur.query, cur.rowcount)
query_res = {}
query["_result"] = query_res
query_res["statement_gen"] = exec_query
query_res["statement_sql"] = cur.query
query_res["rowcount"] = cur.rowcount
id_only = query["query_args"].get("id_only", True)
if id_only:
res_ids = [self._prep_id(row[0]) for row in rows]
return res_ids
else:
res_docs = [self._persistence_dict_to_ion_object(row[-1]) for row in rows]
return res_docs
def test_wkt(self):
""" unit test to verify the DatastoreQuery to PostgresQuery to SQL translation for PostGIS WKT """
wkt = 'POINT(-72.0 40.0)'
buf = 0.1
# PostgresQueryBuilder - WKT (no buffer)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.overlaps_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Intersects(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.contains_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Contains(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.within_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Within(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
# PostgresQueryBuilder - WKT (with buffer)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.overlaps_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Intersects(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.contains_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Contains(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.within_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Within(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
def find_by_query(self, query, access_args=None):
"""
Find resources given a datastore query expression dict.
@param query a dict representation of a datastore query
@retval list of resource ids or resource objects matching query (dependent on id_only value)
"""
qual_ds_name = self._get_datastore_name()
query_ds_sub = query["query_args"].get("ds_sub", None)
query_format = query["query_args"].get("format", "")
pqb = PostgresQueryBuilder(query, qual_ds_name)
if self.profile == DataStore.DS_PROFILE.RESOURCES and not query_ds_sub:
table_alias = qual_ds_name if query_format != "complex" else "base"
pqb.where = self._add_access_filter(access_args,
qual_ds_name,
pqb.where,
pqb.values,
add_where=False,
tablealias=table_alias)
if self.profile == DataStore.DS_PROFILE.RESOURCES:
pqb.where = self._add_deleted_filter(
pqb.table_aliases[0],
query_ds_sub,
pqb.where,
pqb.values,
with_deleted=query["query_args"].get("with_deleted",
False) is True)
with self.pool.cursor(**self.cursor_args) as cur:
exec_query = pqb.get_query()
cur.execute(exec_query, pqb.get_values())
rows = cur.fetchall()
log.info("find_by_query() QUERY: %s (%s rows)", cur.query,
cur.rowcount)
query_res = {}
query["_result"] = query_res
query_res["statement_gen"] = exec_query
query_res["statement_sql"] = cur.query
query_res["rowcount"] = cur.rowcount
id_only = query["query_args"].get("id_only", True)
if query_format == "complex" and pqb.has_basic_cols:
# Return format is list of lists
if id_only:
res_vals = [[self._prep_id(row[0])] + list(row[1:])
for row in rows]
else:
res_vals = [[self._persistence_dict_to_ion_object(row[1])] +
list(rows[2:]) for row in rows]
elif query_format == "complex":
res_vals = [list(row) for row in rows]
else:
if id_only:
res_vals = [self._prep_id(row[0]) for row in rows]
else:
res_vals = [
self._persistence_dict_to_ion_object(row[-1])
for row in rows
]
return res_vals
def test_wkt(self):
""" unit test to verify the DatastoreQuery to PostgresQuery to SQL translation for PostGIS WKT """
wkt = 'POINT(-72.0 40.0)'
buf = 0.1
# PostgresQueryBuilder - WKT (no buffer)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.overlaps_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Intersects(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.contains_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Contains(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.within_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Within(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
# PostgresQueryBuilder - WKT (with buffer)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.overlaps_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Intersects(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.contains_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Contains(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.within_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Within(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.equals_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Equals(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
