def query(self, attr_names=None, entity_type=None, entity_id=None, entity_ids=None, where_clause=None, aggr_method=None, aggr_period=None, aggr_scope=None, from_date=None, to_date=None, last_n=None, limit=10000, offset=0, fiware_service=None, fiware_servicepath=None, geo_query: SlfQuery = None): """ This translator method is used by all API query endpoints. :param attr_names: Array of attribute names to query for. :param entity_type: (Optional). NGSI Entity Type to query about. Unique and optional as long as there are no 2 equal NGSI ids for any NGSI type. :param entity_id: NGSI Id of the entity you ask for. Cannot be used with entity_ids. :param entity_ids: Array of NGSI ids to consider in the response. Cannot be used with entity_id. :param where_clause: (Optional), to use a custom SQL query (not open to public API). :param aggr_method: (Optional), function to apply to the queried values. Must be one of the VALID_AGGR_METHODS (e.g, sum, avg, etc). You need to specify at least one attribute in attr_names, otherwise this will be ignored. :param aggr_period: (Optional), only valid when using aggr_method. Defines the time scope on to which the aggr_method will be applied, hence defines also the number of values that will be returned. Must be one of the VALID_AGGR_PERIODS (e.g, hour). I.e., querying avg per hour will return 24 values times the number of days of available measurements :param aggr_scope: (Not Implemented). Defaults to "entity", which means the aggrMethod will be applied N times, once for each entityId. "global" instead would allow cross-entity_id aggregations. :param from_date: (Optional), used to filter results, considering only from this date inclusive. :param to_date: (Optional), used to filter results, considering only up to this date inclusive. :param last_n: (Optional), used to filter results, return only the last_n elements of what would be the result of the query once all filters where applied. :param limit: (Optional), used to filter results, return up to limit elements of what would be the result of the query once all filters where applied. :param offset: (Optional), used to page results. :param fiware_service: (Optional), used to filter results, considering in the result only entities in this FIWARE Service. :param fiware_servicepath: (Optional), used to filter results, considering in the result only entities in this FIWARE ServicePath. :param geo_query: (Optional), filters results with an NGSI geo query. :return: The shape of the response is always something like this: [{ 'type': 'Room', 'id': 'Room1', or 'ids': ['Room1', 'Room2'], 'index': [t0, t1, ..., tn], 'attr_1': { 'index': [t0, t1, ..., tn], # index of this attr (if different) 'values': [v0, v1, ..., vn], 'type': Number }, ..., 'attr_N': ... },... ] It returns an array of dictionaries, each representing a query result on a particular NGSI Entity Type. Each of the dicts in this array consists of the following attributes. 'type' is the NGSI Entity Type of the response. 'id' or 'ids'. id if the response contains data from a specific NGSI entity (with that id) or ids in the case the response aggregates data from multiple entities (those with those ids). You get one or the other, not both. 'index': The time index applying to the response, applies to all attributes included in the response. It may not be present if each attribute has its own time index array, in the cases where attributes are measured at different moments in time. Note since this is a "global" time index for the entity, it may contain some NULL values where measurements were not available. It's an array containing time in ISO format representation, typically in the original timezone the Orion Notification used, or UTC if created within QL. Each attribute in the response will be represented by a dictionary, with an array called 'values' containing the actual historical values of the attributes as queried. An attribute 'type' will have the original NGSI type of the attribute (i.e, the type of each of the elements now in the values array). The type of an attribute is not expected to change in time, that'd be an error. Additionally, it may contain an array called 'index', just like the global index discussed above but for this specific attribute. Thus, this 'index' will never contain NONE values. If the user did not specify an aggrMethod, the response will not mix measurements of different entities in the same values array. So in this case, there will be many dictionaries in the response array, one for each NGSI Entity. When using aggrPeriod, the index array is a completely new index, composed of time steps of the original index of the attribute but zeroing the less significant bits of time. For example, if there were measurements in time 2018-04-03T08:15:15 and 2018-04-03T09:01:15, with aggrPeriod = minute the new index will contain, at least, the steps 2018-04-03T08:15:00 and 2018-04-03T09:01:00 respectively. :raises: ValueError in case of misuse of the attributes. UnsupportedOption for still-to-be-implemented features. crate.DatabaseError in case of errors with CrateDB interaction. """ last_n = self._parse_last_n(last_n) limit = self._parse_limit(limit) if last_n == 0 or limit == 0: return [] if entity_id and entity_ids: raise NGSIUsageError("Cannot use both entity_id and entity_ids " "params in the same call.") if aggr_method and aggr_method.lower() not in VALID_AGGR_METHODS: raise UnsupportedOption("aggr_method={}".format(aggr_method)) if aggr_period and aggr_period.lower() not in VALID_AGGR_PERIODS: raise UnsupportedOption("aggr_period={}".format(aggr_period)) # TODO check also entity_id and entity_type to not be SQL injection if entity_id and not entity_type: entity_type = self._get_entity_type(entity_id, fiware_service) if not entity_type: return [] if len(entity_type.split(',')) > 1: raise AmbiguousNGSIIdError(entity_id) if entity_id: entity_ids = tuple([entity_id]) lower_attr_names = [a.lower() for a in attr_names] \ if attr_names else attr_names select_clause = self._get_select_clause(lower_attr_names, aggr_method, aggr_period) if not where_clause: where_clause = self._get_where_clause(entity_ids, from_date, to_date, fiware_servicepath, geo_query) order_group_clause = self._get_order_group_clause( aggr_method, aggr_period, select_clause, last_n) if entity_type: table_names = [self._et2tn(entity_type, fiware_service)] else: table_names = self._get_et_table_names(fiware_service) limit = self._get_limit(limit, last_n) offset = max(0, offset) result = [] for tn in sorted(table_names): op = "select {select_clause} " \ "from {tn} " \ "{where_clause} " \ "{order_group_clause} " \ "limit {limit} offset {offset}".format( select_clause=select_clause, tn=tn, where_clause=where_clause, order_group_clause=order_group_clause, limit=limit, offset=offset, ) try: self.cursor.execute(op) except Exception as e: # Reason 1: fiware_service_path column in legacy dbs. logging.debug("{}".format(e)) entities = [] else: res = self.cursor.fetchall() col_names = [x[0] for x in self.cursor.description] entities = self._format_response(res, col_names, tn, last_n) result.extend(entities) return result
def query(self, attr_names=None, entity_type=None, entity_id=None, entity_ids=None, where_clause=None, aggr_method=None, from_date=None, to_date=None, last_n=None, limit=10000, offset=0, fiware_service=None, fiware_servicepath=None): if entity_id and entity_ids: raise ValueError("Cannot use both entity_id and entity_ids params " "in the same call.") if aggr_method and aggr_method not in VALID_AGGR_METHODS: raise UnsupportedOption("aggr_method={}".format(aggr_method)) if entity_id and not entity_type: entity_type = self._get_entity_type(entity_id, fiware_service) if not entity_type: return [] if len(entity_type.split(',')) > 1: raise AmbiguousNGSIIdError(entity_id) if entity_id: entity_ids = tuple([entity_id]) # User specifies 1 entity_id -> exclude ids from response add_ids = False else: add_ids = True select_clause = self._get_select_clause(attr_names, aggr_method, add_ids) if not where_clause: where_clause = self._get_where_clause(entity_ids, from_date, to_date, fiware_servicepath) if aggr_method: order_by = "" if select_clause == "*" else "group by entity_id" else: order_by = "order by {} ASC".format(self.TIME_INDEX_NAME) if entity_type: table_names = [self._et2tn(entity_type, fiware_service)] else: table_names = self._get_et_table_names(fiware_service) limit = self._get_limit(limit) offset = max(0, offset) result = [] for tn in table_names: op = "select {select_clause} " \ "from {tn} " \ "{where_clause} " \ "{order_by} " \ "limit {limit} offset {offset}".format( select_clause=select_clause, tn=tn, where_clause=where_clause, order_by=order_by, limit=limit, offset=offset, ) try: self.cursor.execute(op) except exceptions.ProgrammingError as e: # Reason 1: fiware_service_path column in legacy dbs. logging.debug("{}".format(e)) entities = [] else: res = self.cursor.fetchall() col_names = [x[0] for x in self.cursor.description] entities = list(self.translate_to_ngsi(res, col_names, tn)) result.extend(entities) if last_n: # TODO: embed last_n in query to avoid waste. return result[-last_n:] return result