def __init__(self, query, basetable):
DatastoreQueryBuilder.check_query(query)
self.query = query
self.basetable = basetable
self.cols = ["id"]
if not self.query["query_args"].get("id_only", True):
self.cols.append("doc")
self._valcnt = 0
self.values = {}
self.where = self._build_where(self.query["where"])
self.order_by = self._build_order_by(self.query["order_by"])
def _build_ds_query(self, discovery_query, id_only=True):
query_exp = discovery_query["query"] or {}
index = query_exp.get("index", "resources_index")
ds_name = DATASTORE_MAP.get(index, None)
if ds_name is None:
raise BadRequest("Unknown index: %s" % index)
limit = discovery_query.get("limit", 0)
qb = DatastoreQueryBuilder(limit=limit, id_only=id_only)
where = None
for qm in self._qmatchers:
where = qm(discovery_query, qb)
if where:
break
if where is None:
raise BadRequest("Query had no matcher")
if index == "data_products_index":
filter_types = ["DataProduct", "DataProcess", "Deployment", "InstrumentDevice", "InstrumentModel",
"InstrumentAgentInstance", "InstrumentAgent", "PlatformDevice", "PlatformModel",
"PlatformAgentInstance", "PlatformAgent", "PlatformSite", "Observatory", "UserRole",
"Org", "Attachment", "ExternalDatasetAgent", "ExternalDatasetAgentInstance"]
where = qb.and_(where, qb.in_(DQ.ATT_TYPE, *filter_types), qb.neq(DQ.RA_LCSTATE, "RETIRED"))
qb.build_query(where=where)
return qb.get_query(), ds_name
def _build_ds_query(self, discovery_query, id_only=True):
query_exp = discovery_query["query"] or {}
index = query_exp.get("index", "resources_index")
ds_name = DATASTORE_MAP.get(index, None)
ds_profile = PROFILE_MAP.get(ds_name, None)
# TODO: Enable service defined indexes in addition to standard indexes
if ds_name is None:
raise BadRequest("Unknown index: %s" % index)
limit = discovery_query.get("limit", 0)
skip = discovery_query.get("skip", 0)
order = discovery_query.get("order", None)
qb = DatastoreQueryBuilder(limit=limit, skip=skip, id_only=id_only, profile=ds_profile)
where = None
for qm in self._qmatchers:
where = qm(discovery_query, qb)
if where:
break
if where is None:
raise BadRequest("Query had no matcher")
if index == "data_products_index":
filter_types = ["DataProduct", "DataProcess", "Deployment", "InstrumentDevice", "InstrumentModel",
"InstrumentAgentInstance", "InstrumentAgent", "PlatformDevice", "PlatformModel",
"PlatformAgentInstance", "PlatformAgent", "PlatformSite", "Observatory", "UserRole",
"Org", "Attachment", "ExternalDatasetAgent", "ExternalDatasetAgentInstance",
"Asset", "EventDuration"]
where = qb.and_(where, qb.in_(DQ.ATT_TYPE, *filter_types), qb.neq(DQ.RA_LCSTATE, "DELETED"))
order_by = None
if order:
order_list = []
if type(order) is dict:
for col, colsort in order.iteritems():
order_list.append((col, colsort))
elif type(order) in (list, tuple):
for column in order:
if type(column) in (list, tuple):
col, colsort = column
else:
col, colsort = column, "asc"
order_list.append((col, colsort))
order_by = qb.order_by(order_list)
qb.build_query(where=where, order_by=order_by)
return qb.get_query(), ds_name
def _build_ds_query(self, discovery_query, id_only=True):
query_exp = discovery_query["query"] or {}
index = query_exp.get("index", "resources_index")
ds_name = DATASTORE_MAP.get(index, None)
ds_profile = PROFILE_MAP.get(ds_name, None)
# TODO: Enable service defined indexes in addition to standard indexes
if ds_name is None:
raise BadRequest("Unknown index: %s" % index)
limit = discovery_query.get("limit", 0)
skip = discovery_query.get("skip", 0)
order = discovery_query.get("order", None)
qb = DatastoreQueryBuilder(limit=limit, skip=skip, id_only=id_only, profile=ds_profile)
where = None
for qm in self._qmatchers:
where = qm(discovery_query, qb)
if where:
break
if where is None:
raise BadRequest("Query had no matcher")
order_by = None
if order:
order_list = []
if type(order) is dict:
for col, colsort in order.iteritems():
order_list.append((col, colsort))
elif type(order) in (list, tuple):
for column in order:
if type(column) in (list, tuple):
col, colsort = column
else:
col, colsort = column, "asc"
order_list.append((col, colsort))
order_by = qb.order_by(order_list)
qb.build_query(where=where, order_by=order_by)
return qb.get_query(), ds_name
def _build_ds_query(self, discovery_query, id_only=True):
query_exp = discovery_query["query"] or {}
index = query_exp.get("index", "resources_index")
ds_name = DATASTORE_MAP.get(index, None)
ds_profile = PROFILE_MAP.get(ds_name, None)
# TODO: Enable service defined indexes in addition to standard indexes
if ds_name is None:
raise BadRequest("Unknown index: %s" % index)
limit = discovery_query.get("limit", 0)
skip = discovery_query.get("skip", 0)
order = discovery_query.get("order", None)
qb = DatastoreQueryBuilder(limit=limit,
skip=skip,
id_only=id_only,
profile=ds_profile)
where = None
for qm in self._qmatchers:
where = qm(discovery_query, qb)
if where:
break
if where is None:
raise BadRequest("Query had no matcher")
order_by = None
if order:
order_list = []
if type(order) is dict:
for col, colsort in order.iteritems():
order_list.append((col, colsort))
elif type(order) in (list, tuple):
for column in order:
if type(column) in (list, tuple):
col, colsort = column
else:
col, colsort = column, "asc"
order_list.append((col, colsort))
order_by = qb.order_by(order_list)
qb.build_query(where=where, order_by=order_by)
return qb.get_query(), ds_name
def __init__(self, query, basetable):
DatastoreQueryBuilder.check_query(query)
self.query = query
self.basetable = basetable
self.from_tables = basetable
self._valcnt = 0
self.values = {}
self.query_params = query.get("query_params", {})
self.ds_sub = self.query["query_args"].get("ds_sub", "")
self.query_format = self.query["query_args"].get("format", "")
self.table_aliases = [self.basetable]
self.has_basic_cols = True
if self.query_format == "sql":
self.basic_cols = False
self.cols = self.query["returns"]
self.from_tables = self.query["from"]
self.table_aliases = []
self.where = self.query.get("where", None)
self.group_by = self.query.get("group_by", None)
self.having = self.query.get("having", None)
self.order_by = self.query.get("order_by", None)
elif self.query_format == "complex":
# Build list of return values
self.cols = ["base.id"]
if not self.query["query_args"].get("id_only", True):
self.cols.append("base.doc")
if self.query.get("returns", None):
if self.query["returns"][0] is True:
self.cols.extend(self.query["returns"][1:])
else:
self.has_basic_cols = False
self.cols = self.query["returns"][1:]
# Build FROM fragment with aliases using base table and provided list of other tables
# Convention for table aliases:
# base table (resources, associations, events) as base
# subsequent from tables t0, t1 etc
self.table_aliases = ["base"]
self.from_tables += " AS base"
if self.query.get("from", None):
for i, from_table in enumerate(self.query["from"]):
if " as " in from_table.lower():
self.table_aliases.append(
from_table[from_table.lower().find(" as ") + 4:])
self.from_tables += ",%s" % from_table
else:
f_alias = "t%s" % i
self.table_aliases.append(f_alias)
self.from_tables += ",%s as %s" % (from_table, f_alias)
if self.query.get("where_join", None):
self.where = "(" + self._build_where(
self.query["where"]) + ") AND ((" + ") AND (".join(
self.query["where_join"]) + "))"
else:
self.where = self._build_where(self.query["where"])
self.order_by = self._build_order_by(self.query["order_by"])
self.group_by = self.query.get("group_by", None)
self.having = self.query.get("having", None)
else:
self.cols = ["id"]
if not self.query["query_args"].get("id_only", True):
self.cols.append("doc")
if self.ds_sub:
self.basetable += "_" + self.query["query_args"]["ds_sub"]
self.from_tables = self.basetable
self.table_aliases = [self.basetable]
self.where = self._build_where(self.query["where"])
self.order_by = self._build_order_by(self.query["order_by"])
self.group_by = None
self.having = None
def test_datastore_query(self):
data_store = self.ds_class(datastore_name='ion_test_ds', profile=DataStore.DS_PROFILE.RESOURCES, scope=get_sys_name())
# Just in case previous run failed without cleaning up, delete data store
try:
data_store.delete_datastore()
except NotFound:
pass
data_store.create_datastore()
self.data_store = data_store
self.resources = {}
from interface.objects import GeospatialIndex, ResourceVisibilityEnum, GeospatialLocation
# Create a few resources
plat1_obj_id = self._create_resource(RT.TestPlatform, 'Buoy1', description='My Platform')
aid1_obj_id = self._create_resource(RT.ActorIdentity, 'Actor1')
plat2_obj_id = self._create_resource(RT.TestPlatform, 'Buoy2', visibility=ResourceVisibilityEnum.OWNER)
self._create_association(plat2_obj_id, PRED.hasOwner, aid1_obj_id)
plat3_obj_id = self._create_resource(RT.TestPlatform, 'Buoy3', visibility=ResourceVisibilityEnum.OWNER)
dp1_obj_id = self._create_resource(RT.TestSite, 'Site1', location=GeospatialLocation(latitude=1.0, longitude=2.0))
# Queries
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.or_(qb.and_(qb.eq(qb.RA_NAME, "Buoy1"), qb.eq(qb.RA_NAME, "Buoy1")), qb.eq(qb.RA_NAME, "Buoy1")))
res = data_store.find_by_query(qb.get_query())
self.assertEquals(len(res), 1)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.and_(qb.like(qb.RA_NAME, "Si%"), qb.overlaps_bbox(qb.RA_GEOM, 1, -1.2, 4, 4)))
res = data_store.find_by_query(qb.get_query())
self.assertEquals(len(res), 1)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.attr_like("description", "My%"))
res = data_store.find_by_query(qb.get_query())
self.assertEquals(len(res), 1)
# two tests: first should NOT have above Site1 in radius, second should
qb = DatastoreQueryBuilder(where=qb.overlaps_geom(qb.RA_GEOM,'POINT(2.0 2.0)',0.5))
qb.build_query()
res = data_store.find_by_query(qb.get_query())
self.assertEquals(len(res), 0)
# -- additional 0.001 is to compensate for outer edge NOT being considered an overlap/intersect
qb = DatastoreQueryBuilder(where=qb.overlaps_geom(qb.RA_GEOM,'POINT(2.0 2.0)',1.001))
qb.build_query()
res = data_store.find_by_query(qb.get_query())
self.assertEquals(len(res), 1)
# Access tests
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.or_(qb.and_(qb.like(qb.RA_NAME, "Buoy%"), qb.eq(qb.ATT_TYPE, RT.TestPlatform))))
res = data_store.find_by_query(qb.get_query())
self.assertEquals(len(res), 1)
access_args = create_access_args(current_actor_id=aid1_obj_id)
res = data_store.find_by_query(qb.get_query(), access_args=access_args)
self.assertEquals(len(res), 2)
access_args = create_access_args(current_actor_id=aid1_obj_id, superuser_actor_ids=[aid1_obj_id])
res = data_store.find_by_query(qb.get_query(), access_args=access_args)
self.assertEquals(len(res), 3)
# Clean up
self.data_store.delete_mult([plat1_obj_id, plat2_obj_id, plat3_obj_id, aid1_obj_id, dp1_obj_id])
def test_datastore_query_builder(self):
wkt = 'POINT(-72.0 40.0)'
buf = 0.1
# DatastoreQueryBuilder - WKT
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.overlaps_geom(qb.RA_GEOM_LOC,wkt,buf))
self.assertEquals(qb.get_query()['where'], ['gop:overlaps_geom', ('geom_loc', 'POINT(-72.0 40.0)', 0.1)])
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.contains_geom(qb.RA_GEOM_LOC,wkt,buf))
self.assertEquals(qb.get_query()['where'], ['gop:contains_geom', ('geom_loc', 'POINT(-72.0 40.0)', 0.1)])
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.within_geom(qb.RA_GEOM_LOC,wkt,buf))
self.assertEquals(qb.get_query()['where'], ['gop:within_geom', ('geom_loc', 'POINT(-72.0 40.0)', 0.1)])
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 test_datastore_query(self):
if self.server_type != "postgresql":
raise SkipTest("find_resources_mult only works with Postgres")
data_store = self.ds_class(datastore_name='ion_test_ds', profile=DataStore.DS_PROFILE.RESOURCES, scope=get_sys_name())
self.data_store = data_store
self.resources = {}
# Create a few resources
plat1_obj_id = self._create_resource(RT.PlatformDevice, 'Buoy1', description='My Platform')
from interface.objects import GeospatialIndex
dp1_obj_id = self._create_resource(RT.PlatformSite, 'Site1', geospatial_point_center=GeospatialIndex(lat=1.0, lon=2.0))
# Queries
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.or_(qb.and_(qb.eq(qb.RA_NAME, "Buoy1"), qb.eq(qb.RA_NAME, "Buoy1")), qb.eq(qb.RA_NAME, "Buoy1")))
res = data_store.find_resources_mult(qb.get_query())
print res
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.and_(qb.like(qb.RA_NAME, "Si%"), qb.overlaps_bbox(qb.RA_GEOM, 1, -1.2, 4, 4)))
res = data_store.find_resources_mult(qb.get_query())
print res
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.attr_like("description", "My%"))
res = data_store.find_resources_mult(qb.get_query())
print res
def __init__(self, query, basetable):
DatastoreQueryBuilder.check_query(query)
self.query = query
self.basetable = basetable
self.from_tables = basetable
self._valcnt = 0
self.values = {}
self.query_params = query.get("query_params", {})
self.ds_sub = self.query["query_args"].get("ds_sub", "")
self.query_format = self.query["query_args"].get("format", "")
self.table_aliases = [self.basetable]
self.has_basic_cols = True
if self.query_format == "sql":
self.basic_cols = False
self.cols = self.query["returns"]
self.from_tables = self.query["from"]
self.table_aliases = []
self.where = self.query.get("where", None)
self.group_by = self.query.get("group_by", None)
self.having = self.query.get("having", None)
self.order_by = self.query.get("order_by", None)
elif self.query_format == "complex":
# Build list of return values
self.cols = ["base.id"]
if not self.query["query_args"].get("id_only", True):
self.cols.append("base.doc")
if self.query.get("returns", None):
if self.query["returns"][0] is True:
self.cols.extend(self.query["returns"][1:])
else:
self.has_basic_cols = False
self.cols = self.query["returns"][1:]
# Build FROM fragment with aliases using base table and provided list of other tables
# Convention for table aliases:
# base table (resources, associations, events) as base
# subsequent from tables t0, t1 etc
self.table_aliases = ["base"]
self.from_tables += " AS base"
if self.query.get("from", None):
for i, from_table in enumerate(self.query["from"]):
if " as " in from_table.lower():
self.table_aliases.append(from_table[from_table.lower().find(" as ") + 4:])
self.from_tables += ",%s" % from_table
else:
f_alias = "t%s" % i
self.table_aliases.append(f_alias)
self.from_tables += ",%s as %s" % (from_table, f_alias)
if self.query.get("where_join", None):
self.where = "(" + self._build_where(self.query["where"]) + ") AND ((" + ") AND (".join(self.query["where_join"]) + "))"
else:
self.where = self._build_where(self.query["where"])
self.order_by = self._build_order_by(self.query["order_by"])
self.group_by = self.query.get("group_by", None)
self.having = self.query.get("having", None)
else:
self.cols = ["id"]
if not self.query["query_args"].get("id_only", True):
self.cols.append("doc")
if self.ds_sub:
self.basetable += "_" + self.query["query_args"]["ds_sub"]
self.from_tables = self.basetable
self.table_aliases = [self.basetable]
self.where = self._build_where(self.query["where"])
self.order_by = self._build_order_by(self.query["order_by"])
self.group_by = None
self.having = None
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))")
