def version() -> jsonify: """DB metadata API endpoint.""" with get_db_connection() as db_conn: return jsonify( Version().dump( dict(code_db_schema_version=code_db_schema_version, db_schema_version=utils.query_db_schema_version(db_conn))).data)
def api(tac: str) -> Union[abort, jsonify]: """ TAC API endpoint (version 1). Arguments: tac: str format TAC value Returns: abort: if any error in value json: if correct value """ if len(tac) != 8: abort(400, 'Bad TAC format') try: int(tac) except ValueError: abort(400, 'Bad TAC format') with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute('SELECT * FROM gsma_data WHERE tac = %s', [tac]) rec = cursor.fetchone() if rec is None: return jsonify(GSMATacInfo().dump(dict(tac=tac, gsma=None)).data) return jsonify(GSMATacInfo().dump(dict(tac=tac, gsma=rec._asdict())).data)
def verify_schema(): """Function to verify the schema before the first request.""" with get_db_connection() as conn: try: utils.verify_db_schema(conn, 'dirbs_core_api') except utils.DatabaseSchemaException: raise ServiceUnavailable(description='Invalid database schema or database schema requires upgrade')
def msisdn_api(msisdn: str) -> Union[abort, jsonify]: """ MSISDN API endpoint. Arguments: msisdn: string value of the MSISDN (15 digits) Returns: abort(): if msisdn is not in correct format jsonified response: JSON response of the results """ if len(msisdn) > 15: abort(400, 'Bad MSISDN format (too long)') try: int(msisdn) except ValueError: abort(400, 'Bad MSISDN format (can only contain digit characters)') with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute( """SELECT imei_norm, imsi, manufacturer AS gsma_manufacturer, model_name AS gsma_model_name FROM gsma_data RIGHT JOIN monthly_network_triplets_country_no_null_imeis ON SUBSTRING(imei_norm, 1, 8) = tac WHERE %s = msisdn """, [msisdn]) resp = [MSISDN().dump(rec._asdict()).data for rec in cursor] return jsonify(resp)
def get(self, imei): """IMEI API handler for IMEI.""" imei_norm = self._validate_imei(imei) with get_db_connection() as db_conn, db_conn.cursor() as cursor: condition_results = self._get_conditions(cursor, imei_norm) response = { 'imei_norm': imei_norm, 'block_date': self._get_block_date(cursor, imei_norm), 'classification_state': { 'blocking_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if value['blocking'] ], 'informative_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if not value['blocking'] ] }, 'realtime_checks': { 'ever_observed_on_network': self._ever_observed_on_network(cursor, imei_norm), 'invalid_imei': False if re.match(r'^\d{14}$', imei_norm) else True, 'is_paired': self._is_paired(cursor, imei_norm), 'is_exempted_device': self._is_exempted_device(cursor, imei_norm) }, 'registration_status': self._registration_list_status(cursor, imei_norm), 'stolen_status': self._stolen_list_status(cursor, imei_norm) } return jsonify(IMEIV2().dump(response).data)
def msisdn_api(msisdn): """ MSISDN API endpoint. :param msisdn: MSISDN value :return: json response """ if len(msisdn) > 15: abort(400, 'Bad MSISDN format (too long)') try: int(msisdn) except ValueError: abort(400, 'Bad MSISDN format (can only contain digit characters)') with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute( """SELECT imei_norm, imsi, manufacturer AS gsma_manufacturer, model_name AS gsma_model_name FROM gsma_data RIGHT JOIN monthly_network_triplets_country_no_null_imeis ON SUBSTRING(imei_norm, 1, 8) = tac WHERE %s = msisdn """, [msisdn]) resp = [MSISDN().dump(rec._asdict()).data for rec in cursor] return jsonify(resp)
def post(self, **kwargs): """TAC POST API endpoint (version 2).""" if kwargs is not None: tacs = kwargs.get('tacs') if tacs is not None: tacs = list(set(tacs)) else: abort(400, 'Bad TAC Input format.') if tacs is not None: if not len(tacs) > 1000 and not len(tacs) == 0: with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute("""SELECT tac, manufacturer, bands, allocation_date, model_name, device_type, optional_fields FROM gsma_data WHERE tac IN %(tacs)s""", {'tacs': tuple(tacs)}) gsma_data = cursor.fetchall() response = [] for rec in gsma_data: response.append(TacInfo().dump(dict(tac=rec.tac, gsma=rec._asdict())).data) existing_tacs = [res['tac'] for res in response] for tac in tacs: if tac not in existing_tacs: response.append(TacInfo().dump(dict(tac=tac, gsma=None)).data) return jsonify({'results': response}) abort(400, 'Bad TAC Input format (Minimum 1 & Maximum 1000 allowed).') abort(400, 'Bad TAC Input format.') abort(400, 'Bad TAC Input format.')
def tac_batch_api(**kwargs: dict) -> jsonify: """ TAC POST API endpoint (version 2). Arguments: kwargs: required arguments (TAC list) Returns: JSON response """ tacs = list(set(kwargs.get('tacs'))) with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute( """SELECT tac, manufacturer, bands, allocation_date, model_name, device_type, optional_fields FROM gsma_data WHERE tac IN %(tacs)s""", {'tacs': tuple(tacs)}) gsma_data = cursor.fetchall() response = [] for rec in gsma_data: response.append(TacInfo().dump( dict(tac=rec.tac, gsma=rec._asdict())).data) existing_tacs = [res['tac'] for res in response] for tac in tacs: if tac not in existing_tacs: response.append(TacInfo().dump(dict(tac=tac, gsma=None)).data) return jsonify({'results': response})
def get(self, msisdn): """MSISDN API (version 2) GET endpoint.""" if len(msisdn) > 15: abort(400, 'Bad MSISDN format (too long)') try: int(msisdn) except ValueError: abort(400, 'Bad MSISDN format (can only contain digit characters)') with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute( """SELECT mntc.imei_norm, mntc.imsi, reg.imei_norm AS reg_imei, reg.make, reg.model, reg.brand_name, gsma.tac, gsma.manufacturer, gsma.model_name, gsma.optional_fields, mntc.last_seen FROM gsma_data AS gsma RIGHT JOIN monthly_network_triplets_country_no_null_imeis AS mntc ON SUBSTRING(imei_norm, 1, 8) = tac LEFT JOIN registration_list AS reg ON reg.imei_norm = mntc.imei_norm WHERE msisdn = %s """, [msisdn]) recs = cursor.fetchall() data = [ MSISDNV2().dump( dict(imei_norm=rec[0], imsi=rec[1], last_seen=rec[10], gsma=rec._asdict() if rec[6] else None, registration=rec if rec[2] else None)).data for rec in recs ] return jsonify({'results': data})
def api(): """DB metadata API endpoint.""" with get_db_connection() as db_conn: return jsonify(Version().dump( dict(code_db_schema_version=code_db_schema_version, potential_whitespace_imsis_msisdns=False, schema_version=utils.query_db_schema_version(db_conn))).data)
def validate_exempted_device_types(): """Function to validate exempted devices types before the first request.""" with get_db_connection() as conn: try: utils.validate_exempted_device_types(conn, app.config['DIRBS_CONFIG']) except ConfigParseException: msg = 'Exempted device types specified in config are not valid device types as per GSMA database.' raise ServiceUnavailable(description=msg)
def version() -> jsonify: """DB metadata API endpoint.""" with get_db_connection() as db_conn: return jsonify(Version().dump( dict(source_code_version=dirbs_core_version, code_db_schema_version=code_db_schema_version, db_schema_version=utils.query_db_schema_version(db_conn), report_schema_version=report_schema_version)).data)
def get_metadata(command: List[str] = None, subcommand: List[str] = None, run_id: List[int] = None, status: List[str] = None, order: str = 'ASC', offset: int = 0, limit: int = 10): """Job Metadata API method handler. Arguments: command: List of specific command names subcommand: List of specific sub-command names run_id: List of run ids of the jobs status: List of acceptable status of jobs order: Ascending or Descending order by start_time of the job (default ASC) offset: Offset of the results to fetch from (default from start) limit: number of results per page (default 10) Returns: PostgreSQL records callables """ with get_db_connection() as db_conn, db_conn.cursor() as cursor: # Build the query with params retrieved from request filters_sql = [] for field, label in [(status, 'status'), (command, 'command'), (subcommand, 'subcommand')]: if len(field) > 0: mogrified_sql = cursor.mogrify( sql.SQL("""{0}::TEXT IN %s""").format( sql.Identifier(label)), [tuple(field)]) filters_sql.append( sql.SQL(str(mogrified_sql, db_conn.encoding))) if len(run_id) > 0: mogrified_sql = cursor.mogrify( sql.SQL("""{0} IN (SELECT UNNEST(%s::BIGINT[]))""").format( sql.Identifier('run_id')), [(run_id)]) filters_sql.append(sql.SQL(str(mogrified_sql, db_conn.encoding))) base_sql = sql.SQL( """SELECT *, COUNT(*) OVER() AS total_count FROM job_metadata""") final_sql = base_sql if len(filters_sql) > 0: final_sql = sql.SQL('{0} WHERE {1}').format( base_sql, sql.SQL(' AND ').join(filters_sql)) final_sql = sql.SQL( '{final_sql} ORDER BY start_time {order_type} OFFSET {offset} LIMIT {limit}' ).format(final_sql=final_sql, order_type=sql.SQL(order), offset=sql.SQL(str(offset)), limit=sql.SQL(str(limit))) cursor.execute(final_sql) return cursor.fetchall()
def api(max_results=None, **kwargs): """Data catalog API endpoint.""" # Build filters to be applied to the SQL query filters, filter_params = _build_sql_query_filters(**kwargs) query = sql.SQL( """SELECT array_agg(status ORDER BY run_id DESC)::TEXT[] AS status_list, dc.* FROM (SELECT file_id, filename, file_type, compressed_size_bytes, modified_time, is_valid_zip, is_valid_format, md5, extra_attributes, first_seen, last_seen, uncompressed_size_bytes, num_records FROM data_catalog {filters} ORDER BY last_seen DESC, file_id DESC LIMIT %s) dc LEFT JOIN (SELECT run_id, status, extra_metadata FROM job_metadata WHERE command = 'dirbs-import') jm ON md5 = (extra_metadata->>'input_file_md5')::uuid GROUP BY file_id, filename, file_type, compressed_size_bytes, modified_time, is_valid_zip, is_valid_format, md5, extra_attributes, first_seen, last_seen, uncompressed_size_bytes, num_records ORDER BY last_seen DESC, file_id DESC""") # noqa Q444 where_clause = sql.SQL('') if len(filters) > 0: where_clause = sql.SQL('WHERE {0}').format( sql.SQL(' AND ').join(filters)) # Append max_results to the list of arguments to be supplied to the query. filter_params.append(max_results) with get_db_connection() as conn, conn.cursor() as cursor: cursor.execute( cursor.mogrify(query.format(filters=where_clause), filter_params)) resp = [CatalogFile().dump(rec._asdict()).data for rec in cursor] return jsonify(resp)
def imei_batch_api(**kwargs): """ IMEI API POST method handler for IMEI-Batch request. :param kwargs: input imei list :return: json """ if bool(kwargs): imeis = kwargs.get('imeis') data = [] with get_db_connection() as db_conn, db_conn.cursor() as cursor: for imei in imeis: imei_norm = validate_imei(imei) condition_results = get_conditions(cursor, imei_norm) response = { 'imei_norm': imei_norm, 'block_date': block_date(cursor, imei_norm), 'classification_state': { 'blocking_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if value['blocking'] ], 'informative_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if not value['blocking'] ] }, 'realtime_checks': { 'ever_observed_on_network': ever_observed_on_network(cursor, imei_norm), 'invalid_imei': False if re.match(r'^\d{14}$', imei_norm) else True, 'is_paired': is_paired(cursor, imei_norm), 'is_exempted_device': is_exempted_device(cursor, imei_norm) }, 'registration_status': registration_list_status(cursor, imei_norm), 'stolen_status': stolen_list_status(cursor, imei_norm) } data.append(IMEI().dump(response).data) return jsonify({'results': data}) abort(400, 'Bad Input format (args cannot be empty)')
def api(imei, include_seen_with=False, include_paired_with=False): """IMEI API common functionality.""" imei_norm = ImeiApi._validate_imei(imei) tac = imei_norm[:8] with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute('SELECT NOT EXISTS (SELECT * FROM gsma_data WHERE tac = %s) AS not_in_gsma', [tac]) rt_gsma_not_found = cursor.fetchone()[0] condition_results = ImeiApi._get_conditions(cursor, imei_norm) resp = { 'imei_norm': imei_norm, 'classification_state': { 'blocking_conditions': {k: v['result'] for k, v in condition_results.items() if v['blocking']}, 'informative_conditions': {k: v['result'] for k, v in condition_results.items() if not v['blocking']} }, 'realtime_checks': { 'invalid_imei': False if re.match(r'^\d{14}$', imei_norm) else True, 'gsma_not_found': rt_gsma_not_found } } # add a real-time check for the registration list resp['realtime_checks']['in_registration_list'] = ImeiApi._is_in_registration_list(db_conn, cursor, imei_norm) # add a real-time check for if IMEI was ever observed on the network resp['realtime_checks']['ever_observed_on_network'] = ImeiApi._ever_observed_on_network(cursor, imei_norm) if include_seen_with: cursor.execute("""SELECT DISTINCT imsi, msisdn FROM monthly_network_triplets_country_no_null_imeis WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""", {'imei_norm': imei_norm}) resp['seen_with'] = [{'imsi': x.imsi, 'msisdn': x.msisdn} for x in cursor] cursor.execute("""SELECT EXISTS(SELECT 1 FROM pairing_list WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s))""", {'imei_norm': imei_norm}) resp['is_paired'] = [x.exists for x in cursor][0] if include_paired_with: cursor.execute("""SELECT imsi FROM pairing_list WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""", {'imei_norm': imei_norm}) resp['paired_with'] = [x.imsi for x in cursor] return jsonify(IMEI().dump(resp).data)
def get(self, tac): """TAC GET API endpoint (version 2).""" self._validate_tac(tac) with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute("""SELECT tac, manufacturer, bands, allocation_date, model_name, device_type, optional_fields FROM gsma_data WHERE tac = %s""", [tac]) gsma_data = cursor.fetchone() print(gsma_data) return jsonify(TacInfo().dump(dict(tac=tac, gsma=gsma_data._asdict() if gsma_data is not None else None)).data)
def api(command=None, subcommand=None, run_id=None, status=None, max_results=10, show_details=True): """Job metadata API endpoint.""" with get_db_connection() as db_conn, db_conn.cursor() as cursor: # Build the query with params retrieved from request filters_sql = [] for field, label in [(status, 'status'), (command, 'command'), (subcommand, 'subcommand')]: if len(field) > 0: mogrified_sql = cursor.mogrify( sql.SQL("""{0}::TEXT IN %s""").format( sql.Identifier(label)), [tuple(field)]) filters_sql.append( sql.SQL(str(mogrified_sql, db_conn.encoding))) if len(run_id) > 0: mogrified_sql = cursor.mogrify( sql.SQL("""{0} IN (SELECT UNNEST(%s::BIGINT[]))""").format( sql.Identifier('run_id')), [(run_id)]) filters_sql.append(sql.SQL(str(mogrified_sql, db_conn.encoding))) base_sql = sql.SQL("""SELECT * FROM job_metadata""") final_sql = base_sql if len(filters_sql) > 0: final_sql = sql.SQL('{0} WHERE {1}').format( base_sql, sql.SQL(' AND ').join(filters_sql)) final_sql = sql.SQL('{0} ORDER BY start_time DESC LIMIT %s').format( final_sql) cursor.execute(final_sql, [max_results]) if not show_details: resp = [ JobMetadata(exclude=('extra_metadata', )).dump( rec._asdict()).data for rec in cursor ] else: resp = [JobMetadata().dump(rec._asdict()).data for rec in cursor] return jsonify(resp)
def imei_subscribers_api(imei: str, **kwargs: dict) -> jsonify: """ IMEI-Subscribers API handler. Arguments: imei: IMEI value kwargs: required arguments dictionary Returns: JSON response """ imei_norm = validate_imei(imei) offset = kwargs.get('offset') limit = kwargs.get('limit') order = kwargs.get('order') with get_db_connection() as db_conn, db_conn.cursor() as cursor: query = """SELECT DISTINCT imsi, msisdn, last_seen, COUNT(*) OVER() AS total_count FROM monthly_network_triplets_country_no_null_imeis WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s) ORDER BY last_seen {order_type} OFFSET {data_offset} LIMIT {data_limit}""".format(order_type=order, data_offset=offset, data_limit=limit) # noqa Q447 cursor.execute(query, {'imei_norm': imei_norm}) if cursor is not None: subscribers = [{ 'imsi': x.imsi, 'msisdn': x.msisdn, 'last_seen': x.last_seen, 'total_count': x.total_count } for x in cursor] keys = { 'current_key': offset, 'next_key': offset + limit if subscribers else '', 'result_size': subscribers[0].get('total_count') if subscribers else 0 } return jsonify(IMEISubscribers().dump( dict(imei_norm=imei_norm, subscribers=subscribers, _keys=keys)).data) keys = {'current_key': offset, 'next_key': '', 'result_size': 0} return jsonify(IMEISubscribers().dump( dict(imei_norm=imei_norm, subscribers=None, _keys=keys)))
def imei_api(imei: str, include_seen_with: bool = False, include_paired_with: bool = False) -> jsonify: """ IMEI API handler. Arguments: imei: IMEI number in format [15, 16] digits include_seen_with: bool to include seen with information in response (default False) include_paired_with: bool to include paired with information in response (default False) Returns: JSON response """ imei_norm = validate_imei(imei) tac = imei_norm[:8] with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute('SELECT NOT EXISTS (SELECT * FROM gsma_data WHERE tac = %s) AS not_in_gsma', [tac]) rt_gsma_not_found = cursor.fetchone()[0] condition_results = get_conditions(cursor, imei_norm) resp = { 'imei_norm': imei_norm, 'classification_state': { 'blocking_conditions': {k: v['result'] for k, v in condition_results.items() if v['blocking']}, 'informative_conditions': {k: v['result'] for k, v in condition_results.items() if not v['blocking']} }, 'realtime_checks': { 'invalid_imei': False if re.match(r'^\d{14}$', imei_norm) else True, 'gsma_not_found': rt_gsma_not_found } } # add a real-time check for the registration list resp['realtime_checks']['in_registration_list'] = is_in_registration_list(db_conn, cursor, imei_norm) # add a real-time check for if IMEI was ever observed on the network resp['realtime_checks']['ever_observed_on_network'] = ever_observed_on_network(cursor, imei_norm) resp['is_paired'] = is_paired(cursor, imei_norm) if include_seen_with: resp['seen_with'] = get_subscribers(cursor, imei_norm) if include_paired_with: cursor.execute("""SELECT imsi FROM pairing_list WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""", {'imei_norm': imei_norm}) resp['paired_with'] = [x.imsi for x in cursor] return jsonify(IMEI().dump(resp).data)
def get_info(self, imei): """IMEI API imei/<imei>/info handler.""" imei_norm = self._validate_imei(imei) with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute("""SELECT imei_norm, make, model, status, model_number, brand_name, device_type, radio_interface FROM registration_list WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""", {'imei_norm': imei_norm}) rec = cursor.fetchone() if rec is not None: return jsonify(IMEIInfo().dump(rec._asdict()).data) return {}
def api(tac): """TAC API endpoint (version 1).""" if len(tac) != 8: abort(400, 'Bad TAC format') try: int(tac) except ValueError: abort(400, 'Bad TAC format') with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute('SELECT * FROM gsma_data WHERE tac = %s', [tac]) rec = cursor.fetchone() if rec is None: return jsonify(GSMATacInfo().dump(dict(tac=tac, gsma=None)).data) return jsonify(GSMATacInfo().dump(dict(tac=tac, gsma=rec._asdict())).data)
def get_metadata(command=None, subcommand=None, run_id=None, status=None): """ Get metadata for jobs. :param command: command name (default None) :param subcommand: sub-command name (default None) :param run_id: job run id (default None) :param status: job execution status (default None) :return: psycopg2 results """ with get_db_connection() as db_conn, db_conn.cursor() as cursor: # Build the query with params retrieved from request filters_sql = [] for field, label in [(status, 'status'), (command, 'command'), (subcommand, 'subcommand')]: if len(field) > 0: mogrified_sql = cursor.mogrify( sql.SQL("""{0}::TEXT IN %s""").format( sql.Identifier(label)), [tuple(field)]) filters_sql.append( sql.SQL(str(mogrified_sql, db_conn.encoding))) if len(run_id) > 0: mogrified_sql = cursor.mogrify( sql.SQL("""{0} IN (SELECT UNNEST(%s::BIGINT[]))""").format( sql.Identifier('run_id')), [(run_id)]) filters_sql.append(sql.SQL(str(mogrified_sql, db_conn.encoding))) base_sql = sql.SQL("""SELECT * FROM job_metadata""") final_sql = base_sql if len(filters_sql) > 0: final_sql = sql.SQL('{0} WHERE {1}').format( base_sql, sql.SQL(' AND ').join(filters_sql)) final_sql = sql.SQL('{0} ORDER BY start_time').format(final_sql) cursor.execute(final_sql) return cursor.fetchall()
def get_pairings(self, imei, **kwargs): """Handler method for IMEI-Pairings API (version 2.0).""" imei_norm = self._validate_imei(imei) offset = kwargs.get('offset') limit = kwargs.get('limit') order = kwargs.get('order') if offset is None: offset = 1 if limit is None: limit = 10 with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute("""SELECT pairing_list.imsi, network_triplets.last_seen FROM pairing_list LEFT JOIN monthly_network_triplets_country_no_null_imeis AS network_triplets ON network_triplets.imsi = pairing_list.imsi AND network_triplets.imei_norm = pairing_list.imei_norm WHERE pairing_list.imei_norm = '{imei_norm}'""" .format(imei_norm=imei_norm)) if cursor is not None: pairings = [{'imsi': x.imsi, 'last_seen': x.last_seen} for x in cursor] paginated_data = Pagination.paginate(pairings, offset, limit) if order == 'Ascending': paginated_data.get('data').sort(key=operator.itemgetter('last_seen')) return jsonify(IMEIPairings().dump(dict(imei_norm=imei_norm, pairs=paginated_data.get('data'), _keys=paginated_data.get('keys'))).data) elif order == 'Descending': paginated_data.get('data').sort(key=operator.itemgetter('last_seen'), reverse=True) return jsonify(IMEIPairings().dump(dict(imei_norm=imei_norm, pairs=paginated_data.get('data'), _keys=paginated_data.get('keys'))).data) return jsonify(IMEIPairings().dump(dict(imei_norm=imei_norm, pairs=paginated_data.get('data'), _keys=paginated_data.get('keys'))).data) keys = {'offset': offset, 'limit': limit, 'current_key': offset, 'next_key': '', 'result_size': 0} return jsonify(IMEIPairings().dump(dict(imei_norm=imei_norm, pairs=None, _keys=keys)))
def tac_api(tac: str) -> jsonify: """ TAC GET API endpoint (version 2). Arguments: tac: 8 digit TAC value Returns: JSON response """ validate_tac(tac) with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute( """SELECT tac, manufacturer, bands, allocation_date, model_name, device_type, optional_fields FROM gsma_data WHERE tac = %s""", [tac]) gsma_data = cursor.fetchone() return jsonify(TacInfo().dump( dict(tac=tac, gsma=gsma_data._asdict() if gsma_data is not None else None)).data)
def get_subscribers(self, imei, **kwargs): """Handler method for IMEI-Subscribers API (version 2.0).""" imei_norm = self._validate_imei(imei) offset = kwargs.get('offset') limit = kwargs.get('limit') order = kwargs.get('order') if offset is None: offset = 1 if limit is None: limit = 10 with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute("""SELECT DISTINCT imsi, msisdn, last_seen FROM monthly_network_triplets_country_no_null_imeis WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""", {'imei_norm': imei_norm}) if cursor is not None: subscribers = [{'imsi': x.imsi, 'msisdn': x.msisdn, 'last_seen': x.last_seen} for x in cursor] paginated_data = Pagination.paginate(subscribers, offset, limit) if order == 'Ascending': paginated_data.get('data').sort(key=operator.itemgetter('last_seen')) return jsonify(IMEISubscribers().dump(dict(imei_norm=imei_norm, subscribers=paginated_data.get('data'), _keys=paginated_data.get('keys'))).data) elif order == 'Descending': paginated_data.get('data').sort(key=operator.itemgetter('last_seen'), reverse=True) return jsonify(IMEISubscribers().dump(dict(imei_norm=imei_norm, subscribers=paginated_data.get('data'), _keys=paginated_data.get('keys'))).data) return jsonify(IMEISubscribers().dump(dict(imei_norm=imei_norm, subscribers=paginated_data.get('data'), _keys=paginated_data.get('keys'))).data) keys = {'offset': offset, 'limit': limit, 'current_key': offset, 'next_key': '', 'result_size': 0} return jsonify(IMEISubscribers().dump(dict(imei_norm=imei_norm, subscribers=None, _keys=keys)))
def imei_info_api(imei: str) -> jsonify: """ IMEI-Info API method handler. Arguments: imei: IMEI value to extract information Returns: JSON response if exists otherwise """ imei_norm = validate_imei(imei) with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute( """SELECT imei_norm, make, model, status, model_number, brand_name, device_type, radio_interface FROM registration_list WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""", {'imei_norm': imei_norm}) info_rec = cursor.fetchone() cursor.execute( """SELECT imei_norm FROM registration_list WHERE device_id = (SELECT device_id FROM registration_list WHERE imei_norm = %(imei_norm)s AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)) AND imei_norm NOT IN (%(imei_norm)s)""", {'imei_norm': imei_norm}) if info_rec is not None: response = info_rec._asdict() response['associated_imeis'] = [rec.imei_norm for rec in cursor] \ if cursor is not None else [] return jsonify(IMEIInfo().dump(response).data) return {}
def catalog_api(**kwargs): """ Defines handler for Catalog API (version 2.0) GET method. :param kwargs: input args :return: json """ sorting_order = kwargs.get('order') offset_key = kwargs.get('offset') per_page_limit = kwargs.get('limit') # Build filters to be applied to the SQL query filters, filter_params = _build_sql_query_filters(**kwargs) query = sql.SQL( """SELECT array_agg(status ORDER BY run_id DESC)::TEXT[] AS status_list, dc.* FROM (SELECT file_id, filename, file_type, compressed_size_bytes, modified_time, is_valid_zip, is_valid_format, md5, extra_attributes, first_seen, last_seen, uncompressed_size_bytes, num_records FROM data_catalog {filters} ORDER BY last_seen DESC, file_id DESC LIMIT ALL) dc LEFT JOIN (SELECT run_id, status, extra_metadata FROM job_metadata WHERE command = 'dirbs-import') jm ON md5 = (extra_metadata->>'input_file_md5')::uuid GROUP BY file_id, filename, file_type, compressed_size_bytes, modified_time, is_valid_zip, is_valid_format, md5, extra_attributes, first_seen, last_seen, uncompressed_size_bytes, num_records ORDER BY last_seen DESC, file_id DESC""" ) # noqa Q444 where_clause = sql.SQL('') if len(filters) > 0: where_clause = sql.SQL('WHERE {0}').format( sql.SQL(' AND ').join(filters)) with get_db_connection() as conn, conn.cursor() as cursor: cursor.execute( cursor.mogrify(query.format(filters=where_clause), filter_params)) resp = [CatalogFile().dump(rec._asdict()).data for rec in cursor] if sorting_order is not None or (offset_key is not None and per_page_limit is not None): paginated_data = Pagination.paginate(resp, offset_key, per_page_limit) if sorting_order == 'Ascending': paginated_data.get('data').sort( key=operator.itemgetter('file_id')) response = { '_keys': Keys().dump(dict(paginated_data.get('keys'))).data, 'files': [file_data for file_data in paginated_data.get('data')] } return jsonify(response) elif sorting_order == 'Descending': paginated_data.get('data').sort( key=operator.itemgetter('file_id'), reverse=True) response = { '_keys': Keys().dump(dict(paginated_data.get('keys'))).data, 'files': [file_data for file_data in paginated_data.get('data')] } return jsonify(response) response = { '_keys': Keys().dump(dict(paginated_data.get('keys'))).data, 'files': [file_data for file_data in paginated_data.get('data')] } return jsonify(response) keys = { 'offset': '', 'limit': '', 'previous_key': '', 'next_key': '', 'result_size': len(resp) } response = {'_keys': Keys().dump(dict(keys)).data, 'files': resp} return jsonify(response)
def imei_batch_api(**kwargs: dict) -> jsonify: """ IMEI API POST method handler for IMEI-Batch request. Arguments: kwargs: required arguments (list of IMEIs) Returns: JSON response """ imeis = kwargs.get('imeis') include_registration_status = kwargs.get('include_registration_status') include_stolen_status = kwargs.get('include_stolen_status') data = [] with get_db_connection() as db_conn, db_conn.cursor() as cursor: for imei in imeis: imei_norm = validate_imei(imei) tac = imei_norm[:8] condition_results = get_conditions(cursor, imei_norm) first_seen_date = first_seen(cursor, imei_norm) cursor.execute( 'SELECT NOT EXISTS (SELECT * FROM gsma_data WHERE tac = %s) AS not_in_gsma', [tac]) rt_gsma_not_found = cursor.fetchone()[0] response = { 'imei_norm': imei_norm, 'block_date': block_date(cursor, imei_norm), 'first_seen': first_seen_date, 'classification_state': { 'blocking_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if value['blocking'] ], 'informative_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if not value['blocking'] ] }, 'realtime_checks': { 'ever_observed_on_network': True if first_seen_date else False, 'invalid_imei': False if re.match(r'^\d{14}$', imei_norm) else True, 'is_paired': is_paired(cursor, imei_norm), 'is_exempted_device': is_exempted_device(cursor, imei_norm), 'gsma_not_found': rt_gsma_not_found, 'in_registration_list': is_in_registration_list(db_conn, cursor, imei_norm) } } if include_registration_status: response['registration_status'] = registration_list_status( cursor, imei_norm) if include_stolen_status: response['stolen_status'] = stolen_list_status( cursor, imei_norm) data.append(IMEI().dump(response).data) return jsonify({'results': data})
def imei_api(imei: str, include_registration_status: bool = False, include_stolen_status: bool = False) -> jsonify: """ IMEI API handler. Arguments: imei: value of the IMEI include_registration_status: boolean weather to include reg status or not (default False) include_stolen_status: boolean weather to include stolen status or not (default False) Returns: JSON response """ imei_norm = validate_imei(imei) tac = imei_norm[:8] tac = imei_norm[:8] with get_db_connection() as db_conn, db_conn.cursor() as cursor: cursor.execute( 'SELECT NOT EXISTS (SELECT * FROM gsma_data WHERE tac = %s) AS not_in_gsma', [tac]) rt_gsma_not_found = cursor.fetchone()[0] first_seen_date = first_seen(cursor, imei_norm) condition_results = get_conditions(cursor, imei_norm) response = { 'imei_norm': imei_norm, 'block_date': block_date(cursor, imei_norm), 'first_seen': first_seen_date, 'classification_state': { 'blocking_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if value['blocking'] ], 'informative_conditions': [ dict({ 'condition_name': key, 'condition_met': value['result'] }) for key, value in condition_results.items() if not value['blocking'] ] }, 'realtime_checks': { 'ever_observed_on_network': True if first_seen_date else False, 'invalid_imei': False if re.match(r'^\d{14}$', imei_norm) else True, 'is_paired': is_paired(cursor, imei_norm), 'is_exempted_device': is_exempted_device(cursor, imei_norm), 'in_registration_list': is_in_registration_list(db_conn, cursor, imei_norm), 'gsma_not_found': rt_gsma_not_found } } if include_registration_status: response['registration_status'] = registration_list_status( cursor, imei_norm) if include_stolen_status: response['stolen_status'] = stolen_list_status(cursor, imei_norm) return jsonify(IMEI().dump(response).data)