def delete_stale_fpds(ids_to_delete):
logger.info("Starting deletion of stale FPDS data")
transactions = TransactionNormalized.objects.filter(
contract_data__detached_award_procurement_id__in=ids_to_delete)
update_award_ids, delete_award_ids = find_related_awards(transactions)
delete_transaction_ids = [
delete_result[0]
for delete_result in transactions.values_list("id")
]
delete_transaction_str_ids = ",".join(
[str(deleted_result) for deleted_result in delete_transaction_ids])
update_award_str_ids = ",".join(
[str(update_result) for update_result in update_award_ids])
delete_award_str_ids = ",".join(
[str(deleted_result) for deleted_result in delete_award_ids])
db_cursor = connections[DEFAULT_DB_ALIAS].cursor()
queries = []
if delete_transaction_ids:
fpds = "DELETE FROM transaction_fpds tf WHERE tf.transaction_id IN ({});".format(
delete_transaction_str_ids)
tn = "DELETE FROM transaction_normalized tn WHERE tn.id IN ({});".format(
delete_transaction_str_ids)
td = "DELETE FROM transaction_delta td WHERE td.transaction_id in ({});".format(
delete_transaction_str_ids)
queries.extend([fpds, tn, td])
# Update Awards
if update_award_ids:
# Removing FK values from awards so constraints don't cause script to fail
# Adding to AWARD_UPDATE_ID_LIST so the transaction FKs will be recalculated
AWARD_UPDATE_ID_LIST.extend(update_award_ids)
query_str = (
"UPDATE awards SET latest_transaction_id = null, earliest_transaction_id = null WHERE id IN ({});"
)
update_awards_query = query_str.format(update_award_str_ids)
queries.append(update_awards_query)
if delete_award_ids:
# Financial Accounts by Awards
query_str = "UPDATE financial_accounts_by_awards SET award_id = null WHERE award_id IN ({});"
fa = query_str.format(delete_award_str_ids)
# Subawards
sub = "UPDATE subaward SET award_id = null WHERE award_id IN ({});".format(
delete_award_str_ids)
# Parent Awards
pa_updates = "UPDATE parent_award SET parent_award_id = null WHERE parent_award_id IN ({});".format(
delete_award_str_ids)
pa_deletes = "DELETE FROM parent_award WHERE award_id IN ({});".format(
delete_award_str_ids)
# Delete Subawards
delete_awards_query = "DELETE FROM awards a WHERE a.id IN ({});".format(
delete_award_str_ids)
queries.extend(
[fa, sub, pa_updates, pa_deletes, delete_awards_query])
if queries:
db_query = "".join(queries)
db_cursor.execute(db_query, [])
def delete_stale_fabs(ids_to_delete):
"""ids_to_delete are published_award_financial_assistance_ids"""
logger.info("Starting deletion of stale FABS data")
if not ids_to_delete:
return []
transactions = TransactionNormalized.objects.filter(
assistance_data__published_award_financial_assistance_id__in=
ids_to_delete)
update_award_ids, delete_award_ids = find_related_awards(transactions)
delete_transaction_ids = [
delete_result[0] for delete_result in transactions.values_list("id")
]
delete_transaction_str_ids = ",".join(
[str(deleted_result) for deleted_result in delete_transaction_ids])
delete_award_str_ids = ",".join(
[str(deleted_result) for deleted_result in delete_award_ids])
queries = []
# Transaction FABS
if delete_transaction_ids:
fabs = 'DELETE FROM "transaction_fabs" tf WHERE tf."transaction_id" IN ({});'
tn = 'DELETE FROM "transaction_normalized" tn WHERE tn."id" IN ({});'
ts = 'DELETE FROM "transaction_search" ts WHERE ts."transaction_id" IN ({});'
td = "DELETE FROM transaction_delta td WHERE td.transaction_id in ({});"
queries.extend([
fabs.format(delete_transaction_str_ids),
tn.format(delete_transaction_str_ids),
ts.format(delete_transaction_str_ids),
td.format(delete_transaction_str_ids),
])
if delete_award_ids:
# Financial Accounts by Awards
faba = 'UPDATE "financial_accounts_by_awards" SET "award_id" = null WHERE "award_id" IN ({});'
# Subawards
sub = 'UPDATE "subaward" SET "award_id" = null WHERE "award_id" IN ({});'.format(
delete_award_str_ids)
# Delete Awards
delete_awards_query = 'DELETE FROM "awards" a WHERE a."id" IN ({});'.format(
delete_award_str_ids)
queries.extend(
[faba.format(delete_award_str_ids), sub, delete_awards_query])
if queries:
db_query = "".join(queries)
db_cursor = connections[DEFAULT_DB_ALIAS].cursor()
db_cursor.execute(db_query, [])
# Update Awards
if update_award_ids:
update_awards(tuple(update_award_ids))
return update_award_ids
def delete_stale_fabs(ids_to_delete):
logger.info('Starting deletion of stale FABS data')
if not ids_to_delete:
return []
transactions = TransactionNormalized.objects.filter(
assistance_data__afa_generated_unique__in=ids_to_delete)
update_award_ids, delete_award_ids = find_related_awards(transactions)
delete_transaction_ids = [
delete_result[0] for delete_result in transactions.values_list('id')
]
delete_transaction_str_ids = ','.join(
[str(deleted_result) for deleted_result in delete_transaction_ids])
delete_award_str_ids = ','.join(
[str(deleted_result) for deleted_result in delete_award_ids])
queries = []
# Transaction FABS
if delete_transaction_ids:
fabs = 'DELETE FROM "transaction_fabs" tf WHERE tf."transaction_id" IN ({});'
tn = 'DELETE FROM "transaction_normalized" tn WHERE tn."id" IN ({});'
queries.extend([
fabs.format(delete_transaction_str_ids),
tn.format(delete_transaction_str_ids)
])
if delete_award_ids:
# Financial Accounts by Awards
faba = 'UPDATE "financial_accounts_by_awards" SET "award_id" = null WHERE "award_id" IN ({});'
# Subawards
sub = 'UPDATE "subaward" SET "award_id" = null WHERE "award_id" IN ({});'.format(
delete_award_str_ids)
# Delete Awards
delete_awards_query = 'DELETE FROM "awards" a WHERE a."id" IN ({});'.format(
delete_award_str_ids)
queries.extend(
[faba.format(delete_award_str_ids), sub, delete_awards_query])
if queries:
db_query = ''.join(queries)
db_cursor = connections['default'].cursor()
db_cursor.execute(db_query, [])
# Update Awards
if update_award_ids:
update_awards(tuple(update_award_ids))
return update_award_ids
def delete_stale_fpds(self, ids_to_delete):
logger.info("Starting deletion of stale FPDS data")
transactions = TransactionNormalized.objects.filter(
contract_data__detached_award_procurement_id__in=ids_to_delete
)
update_award_ids, delete_award_ids = find_related_awards(transactions)
delete_transaction_ids = [delete_result[0] for delete_result in transactions.values_list("id")]
delete_transaction_str_ids = ",".join([str(deleted_result) for deleted_result in delete_transaction_ids])
update_award_str_ids = ",".join([str(update_result) for update_result in update_award_ids])
delete_award_str_ids = ",".join([str(deleted_result) for deleted_result in delete_award_ids])
db_cursor = connections["default"].cursor()
queries = []
if delete_transaction_ids:
fpds = "DELETE FROM transaction_fpds tf WHERE tf.transaction_id IN ({});".format(delete_transaction_str_ids)
# Transaction Normalized
tn = "DELETE FROM transaction_normalized tn WHERE tn.id IN ({});".format(delete_transaction_str_ids)
queries.extend([fpds, tn])
# Update Awards
if update_award_ids:
# Adding to AWARD_UPDATE_ID_LIST so the latest_transaction will be recalculated
AWARD_UPDATE_ID_LIST.extend(update_award_ids)
query_str = "UPDATE awards SET latest_transaction_id = null WHERE id IN ({});"
update_awards_query = query_str.format(update_award_str_ids)
queries.append(update_awards_query)
if delete_award_ids:
# Financial Accounts by Awards
query_str = "UPDATE financial_accounts_by_awards SET award_id = null WHERE award_id IN ({});"
fa = query_str.format(delete_award_str_ids)
# Subawards
sub = "UPDATE subaward SET award_id = null WHERE award_id IN ({});".format(delete_award_str_ids)
# Parent Awards
pa_updates = "UPDATE parent_award SET parent_award_id = null WHERE parent_award_id IN ({});".format(
delete_award_str_ids
)
pa_deletes = "DELETE FROM parent_award WHERE award_id IN ({});".format(delete_award_str_ids)
# Delete Subawards
delete_awards_query = "DELETE FROM awards a WHERE a.id IN ({});".format(delete_award_str_ids)
queries.extend([fa, sub, pa_updates, pa_deletes, delete_awards_query])
if queries:
db_query = "".join(queries)
db_cursor.execute(db_query, [])