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)
