def setUpTestData(cls):
reset_object_class_cache()
mommy.make(
"accounts.TreasuryAppropriationAccount",
treasury_account_identifier=-99999,
allocation_transfer_agency_id="999",
agency_id="999",
beginning_period_of_availability="1700-01-01",
ending_period_of_availability="1700-12-31",
availability_type_code="000",
main_account_code="0000",
sub_account_code="0000",
tas_rendering_label="1004-1002-1003-1007-1008",
)
mommy.make("references.ObjectClass",
id=0,
major_object_class="00",
object_class="000",
direct_reimbursable=None)
# Setup default data in Broker Test DB
broker_objects_to_insert = {
"tas_lookup": {
"broker_object": _assemble_broker_tas_lookup_records(),
"conflict_column": "tas_id"
},
"submission": {
"broker_object": _assemble_broker_submission_records(),
"conflict_column": "submission_id"
},
"certify_history": {
"broker_object": _assemble_certify_history(),
"conflict_column": "certify_history_id"
},
"certified_award_financial": {
"broker_object": _assemble_certified_award_financial_records(),
"conflict_column": "certified_award_financial_id",
},
}
connection = connections["data_broker"]
with connection.cursor() as cursor:
for broker_table_name, value in broker_objects_to_insert.items():
broker_object = value["broker_object"]
conflict_column = value["conflict_column"]
for load_object in [
dict(**{broker_table_name: obj})
for obj in broker_object
]:
columns, values, pairs = format_insert_or_update_column_sql(
cursor, load_object, broker_table_name)
insert_sql = (
f"INSERT INTO {broker_table_name} {columns} VALUES {values}"
f" ON CONFLICT ({conflict_column}) DO UPDATE SET {pairs};"
)
cursor.execute(insert_sql)
def setUp(self):
"""
Because we are adding fields and tables to the database and we want to keep track of that, we're
using setUp instead of setUpClass so that we can retain some state on the object. Another
unfortunate side effect of using dblink and having to modify the database. We can refactor this
set of tests once either of those situations is alleviated.
"""
reset_object_class_cache()
mommy.make(
"accounts.TreasuryAppropriationAccount",
treasury_account_identifier=1,
agency_id="111",
availability_type_code="X",
main_account_code="1111",
sub_account_code="111",
tas_rendering_label="111-X-1111-111",
)
mommy.make(
"accounts.TreasuryAppropriationAccount",
treasury_account_identifier=2,
agency_id="222",
availability_type_code="X",
main_account_code="2222",
sub_account_code="222",
tas_rendering_label="222-X-2222-222",
)
mommy.make("references.ObjectClass", major_object_class="10", object_class="10.1", direct_reimbursable="D")
mommy.make("references.DisasterEmergencyFundCode", code="B", title="BB")
mommy.make("references.DisasterEmergencyFundCode", code="L", title="LL")
mommy.make("references.DisasterEmergencyFundCode", code="N", title="NN")
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2000041",
submission_fiscal_year=2000,
submission_fiscal_month=4,
is_quarter=True,
)
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2000040",
submission_fiscal_year=2000,
submission_fiscal_month=4,
is_quarter=False,
)
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2000041",
submission_fiscal_year=2000,
submission_fiscal_month=4,
is_quarter=True,
)
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2000050",
submission_fiscal_year=2000,
submission_fiscal_month=5,
is_quarter=False,
)
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2000060",
submission_fiscal_year=2000,
submission_fiscal_month=6,
is_quarter=False,
)
mommy.make(
"submissions.DABSSubmissionWindowSchedule",
id="2000091",
submission_fiscal_year=2000,
submission_fiscal_month=9,
is_quarter=True,
)
connection = connections["data_broker"]
with connection.cursor() as cursor:
self._nuke_broker_data()
cursor.execute(
"""
insert into tas_lookup (
tas_id,
account_num,
agency_identifier,
availability_type_code,
main_account_code,
sub_account_code,
internal_start_date
) (values
(1, 1, '111', 'X', '1111', '111', '1900-01-01'),
(2, 2, '222', 'X', '2222', '222', '1900-01-01')
)
"""
)
cursor.execute(
"""
insert into submission (
submission_id,
cgac_code,
frec_code,
reporting_start_date,
reporting_end_date,
reporting_fiscal_year,
reporting_fiscal_period,
is_quarter_format,
d2_submission,
publish_status_id,
updated_at
) (values
-- bunch of good records with a mix of all kinds of settings
(1, '001', null, '2000-01-01', '2000-03-31', 2000, 4, true, false, 2, now()),
(2, null, '0002', '2000-01-01', '2000-01-31', 2000, 4, false, false, 3, now()),
(3, '003', '0003', '2000-02-01', '2000-02-29', 2000, 5, false, false, 2, now()),
(4, '004', null, '2000-03-01', '2000-03-31', 2000, 6, false, false, 3, now()),
(5, null, '005', '2000-04-01', '2000-06-30', 2000, 9, true, false, 2, now()),
-- submissions that should never return for various reasons
(6, '006', null, '2000-01-01', '2000-03-31', 2000, 4, true, false, 1, now()), -- not publish type 2 or 3
(7, '007', null, '2000-01-01', '2000-03-31', 2000, 4, true, true, 2, now()) -- D2
)
"""
)
cursor.execute(
"""
insert into publish_history (
publish_history_id,
submission_id,
updated_at
) (values
(1, 1, '2000-01-01'), (2, 2, '2000-01-02'), (3, 3, '2000-01-03'), (4, 4, '2000-01-04'),
(5, 5, '2000-01-05'), (6, 6, '2000-01-06'), (7, 7, '2000-01-07')
)
"""
)
cursor.execute(
"""
insert into certify_history (
certify_history_id,
submission_id,
updated_at
) (values
(1, 1, '2000-02-01'), (3, 3, '2000-02-03'), (5, 5, '2000-02-05'), (7, 7, '2000-02-07')
)
"""
)
cursor.execute(
"""
insert into certified_appropriation (
certified_appropriation_id,
submission_id,
tas_id,
total_budgetary_resources_cpe
) (values
(1, 1, 1, 11),
(2, 2, 1, 22),
(3, 3, 2, 33),
(4, 4, 2, 44),
(5, 5, 2, 55),
(6, 6, 2, 66),
(7, 7, 2, 77)
)
"""
)
cursor.execute(
"""
insert into certified_object_class_program_activity (
certified_object_class_program_activity_id,
submission_id,
tas_id,
object_class,
gross_outlay_amount_by_pro_cpe,
disaster_emergency_fund_code
) (values
(1, 1, 1, '1101', 1111, null),
(2, 1, 1, '1101', 2222, 'B'),
(3, 1, 1, '1101', 3333, 'L'),
(4, 2, 1, '1101', 4444, null),
(5, 2, 1, '1101', 5555, null),
(6, 2, 1, '1101', 6666, null),
(7, 3, 2, '1101', 7777, 'L'),
(8, 3, 2, '1101', 8888, 'L'),
(9, 3, 2, '1101', 9999, 'L'),
(10, 4, 2, '1101', 1010, null),
(11, 5, 2, '1101', 1111, 'B'),
(12, 6, 2, '1101', 1212, 'L'),
(13, 7, 2, '1101', 1313, 'N')
)
"""
)
cursor.execute(
"""
insert into certified_award_financial (
certified_award_financial_id,
submission_id,
tas_id,
object_class,
gross_outlay_amount_by_awa_cpe,
transaction_obligated_amou,
disaster_emergency_fund_code
) (values
(1, 1, 1, '1101', 11111, 111110, null),
(2, 1, 1, '1101', 22222, 222220, 'B'),
(3, 1, 1, '1101', 33333, 333330, 'L'),
(4, 2, 1, '1101', 44444, 444440, null),
(5, 2, 1, '1101', 55555, 555550, null),
(6, 2, 1, '1101', 66666, 666660, null),
(7, 3, 2, '1101', 77777, 777770, 'L'),
(8, 3, 2, '1101', 88888, 888880, 'L'),
(9, 3, 2, '1101', 99999, 999990, 'L'),
(10, 4, 2, '1101', 10101, 101010, null),
(11, 5, 2, '1101', 11111, 111110, 'B'),
(12, 5, 2, '1101', null, null, 'B'), -- this should not load because of 0/null values
(13, 5, 2, '1101', 0, 0, 'B'), -- this should not load because of 0/null values
(14, 5, 2, '1101', null, 0, 'B'), -- this should not load because of 0/null values
(15, 5, 2, '1101', 0, null, 'B'), -- this should not load because of 0/null values
(16, 6, 2, '1101', 12121, 121210, 'L'),
(17, 7, 2, '1101', 13131, 131310, 'N')
)
"""
)
# This is an extremely brute force tactic, but there are many non-nullable fields in USAspending
# that are nullable in Broker. To keep from throwing not-null errors, we are going to provide
# zero values for a whole mess of fields known to be numeric. This will also prevent me having
# to mock a whole mess of additional data.
cursor.execute(
"""
select table_name, column_name
from information_schema.columns
where table_schema = 'public' and
table_name in (
'certified_appropriation',
'certified_object_class_program_activity',
'certified_award_financial'
) and
(column_name like '%cpe' or column_name like '%fyb')
"""
)
sqls = " ".join([f"update {r[0]} set {r[1]} = 0 where {r[1]} is null;" for r in cursor.fetchall()])
cursor.execute(sqls)