def test_resolved_has_access_gives_submission_value():
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the tables are all built and reset
build_and_reset_data_sources(scenario_builder)
submission_data = [
latest_submission_row('1', 'NO', n_days_ago(n=1)),
latest_submission_row('2', 'YES', n_days_ago(n=1)),
latest_submission_row('3', 'NO', n_days_ago(n=1)),
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# the results are 3 lines long and include all the codes
assert len(results) == 3
nhs_numbers_and_resolved_has_needs = {(result[NHS_NUMBER_COL],
result[RESOLVED_NEEDS_COL])
for result in results}
assert nhs_numbers_and_resolved_has_needs == {('1', 'NO'),
('2', 'YES'),
('3', 'NO')}
def test_latest_feedback_opt_out_gets_latest_opt_out_row(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the all_la_feedback view will return feedback for one person at multiple times
test_nhs_number = f'{random_nhs_number()}'
feedback_data = [
all_la_feedback_row(test_nhs_number, 'YF19', n_days_ago(n=10), ""),
all_la_feedback_row(test_nhs_number, 'W003', n_days_ago(n=9), ""),
all_la_feedback_row(test_nhs_number, 'RM19', n_days_ago(n=8), ""),
all_la_feedback_row(test_nhs_number, 'D001', n_days_ago(n=7), ""),
all_la_feedback_row(test_nhs_number, 'W004', n_days_ago(n=6),
"expected"),
all_la_feedback_row(test_nhs_number, 'YF19', n_days_ago(n=5), ""),
]
scenario_builder.insert_multiple_into_arbitrary_table(
"all_la_feedback", feedback_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_la_feedback_opt_out_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# the results for test_number is 1 lines long and has only the feedback from 6 days ago
results_for_nhs_number = [
result for result in results if result[0] == test_nhs_number
]
assert len(results_for_nhs_number) == 1
assert results_for_nhs_number[0][1] == 'W004'
def test_resolved_has_access_overrides_opt_out_with_later_web_submission():
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# web needs food 10 days ago + feedback doesnt need food 1 day ago
build_and_reset_data_sources(scenario_builder)
submission_data = [latest_submission_row('1', 'NO', n_days_ago(n=1))]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
opt_out_data = [
latest_la_feedback_opt_out_row('1', 'F002', n_days_ago(5),
'do not expect opt out')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_la_feedback_to_stop_boxes", opt_out_data)
# WHEN
# we run the full_submission record query
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# later web submission overrides the opt out feedback
assert len(results) == 1
assert results[0][RESOLVED_NEEDS_COL] == 'NO'
def test_full_submission_record_feeds_through_all_latest_submissions(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the tables are all built and reset
build_and_reset_data_sources(scenario_builder)
submission_data = [
latest_submission_row('1', 'NO', n_days_ago(n=1)),
latest_submission_row('2', 'NO', n_days_ago(n=1)),
latest_submission_row('3', 'NO', n_days_ago(n=1)),
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# the results are 3 lines long and include all the codes
assert len(results) == 3
nhs_numbers = [result[NHS_NUMBER_COL] for result in results]
assert set(nhs_numbers) == {'1', '2', '3'}
def test_address_details_are_pulled_from_latest_address_not_nhs_record(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a user who has registered for food and has a latest address postcode that differs from their nhs postcode
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging",
[nhs_clean_staging_row(1, postcode="nhs postcode")])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1,
postcode="latest address postcode"),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'NO', n_days_ago(n=1)),
])
# WHEN
# we build the latest feedback user submission stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# the latest address postcode is what is pulled through on the record
assert len(results) == 1
assert results[0][POSTCODE] == 'latest address postcode'
def test_users_that_deregister_themselves_are_marked_as_having_access_to_food(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a simple situation where users have registered on the web or ivr
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'YES', n_days_ago(n=1)),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging", [nhs_clean_staging_row(1)])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1),
])
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# there are 3 results returned
assert len(results) == 1
assert results[0][HAS_ACCESS] == 'YES'
def test_resolved_has_access_overrides_opt_out_with_web_submission_on_same_day(
):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# feedback doesnt need food yesterday - web submission yesterday at noon requires food
build_and_reset_data_sources(scenario_builder)
opt_out_data = [
latest_la_feedback_opt_out_row('1', 'F002', n_days_ago(1), '')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_la_feedback_to_stop_boxes", opt_out_data)
submission_data = [
latest_submission_row('1', 'NO',
n_days_ago(n=1, time_str='12:00:00'))
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
# WHEN
# we run the full_submission record query
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# later web submission the same day overrides the opt out feedback
assert len(results) == 1
assert results[0][RESOLVED_NEEDS_COL] == 'NO'
def test_users_that_are_registered_have_pii_columns_available(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a user who has registered for food
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging", [nhs_clean_staging_row(1)])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'NO', n_days_ago(n=1)),
])
# WHEN
# we build the latest feedback user submission stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# the user has pii data available
assert len(results) == 1
assert results[0][LAST_NAME] != ''
assert results[0][POSTCODE] != ''
assert results[0][MOBILE] != ''
def test_users_that_are_deregistered_by_wholesaler_then_reregister_are_marked_as_needing_food(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a user who registered for food after being deregistered by the wholesaler
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging", [nhs_clean_staging_row(1)])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_wholesaler_opt_out",
[latest_wholesaler_opt_out_row(HEX_ID_1, n_days_ago(5), '3', '')])
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'NO', n_days_ago(n=1)),
])
# WHEN
# we build the latest feedback user submission stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# the user is marked as needing food
assert len(results) == 1
assert results[0][HAS_ACCESS] == 'NO'
def test_users_that_are_deceased_have_pii_surpressed_and_marked_as_not_needing_supplies(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a user who has registered for food but is now deceased
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging", [nhs_clean_staging_row(1, nhs_deceased='1')])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'YES', n_days_ago(n=10)),
])
# WHEN
# we build the latest feedback user submission stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# the user has most of their data surpressed
assert len(results) == 1
assert results[0][HAS_ACCESS] == 'YES'
assert results[0][LAST_NAME] == ''
assert results[0][POSTCODE] == ''
assert results[0][MOBILE] == ''
def test_users_that_are_deregistered_by_la_are_marked_as_having_access_to_food(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a user who registered for food and has been deregistered by the la
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'NO', n_days_ago(n=10)),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging", [nhs_clean_staging_row(1)])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_la_feedback_to_stop_boxes",
[latest_la_feedback_opt_out_row('1', 'F002', n_days_ago(5), '')])
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# there are 3 results returned
assert len(results) == 1
assert results[0][HAS_ACCESS] == 'YES'
def test_all_la_feedback_dates():
# GIVEN A SCENARIO WITH KNOWN LA FEEDBACK
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
scenario_builder.reset()
test_date = '2010-11-01 00:00:00.000'
scenario_builder.insert_la_feedback('test_nhs_1', 'test_code',
test_date, '')
scenario_builder.insert_la_feedback('test_nhs_2', 'test_code',
'2010-11-01 00:00:00.000', '')
scenario_builder.insert_la_feedback('test_nhs_3', 'test_code',
'01-11-2010', '')
scenario_builder.insert_la_feedback('test_nhs_4', 'test_code',
'01/11/2010', '')
with open('sql_to_build/all_la_feedback.sql', 'r') as f:
query = f.read()
results = presto_transaction(query)
row_dates = [row[2] for row in results]
assert row_dates == [test_date] * 4
def test_resolved_has_access_wholesaler_feedback_is_overridden_by_web_submission_on_same_day(
):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# web needs food 10 days ago + wholesaler opt out 1 day ago
build_and_reset_data_sources(scenario_builder)
# wholesaler opt out yesterday with an id corresponding to nhs_number=1
wholesaler_data = [
latest_wholesaler_opt_out_row(HEX_ID_1, n_days_ago(1), '3', '')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_wholesaler_opt_out", wholesaler_data)
# web submission yesterday at noon
submission_data = [
latest_submission_row('1', 'NO',
n_days_ago(n=1, time_str="12:00:00"))
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
# WHEN
# we run the full_submission record query
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# the web submission overrides the feedback
assert len(results) == 1
assert results[0][RESOLVED_NEEDS_COL] == 'NO'
def test_full_submission_record_appends_wholesaler_feedback_if_present():
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the tables are all built and reset
build_and_reset_data_sources(scenario_builder)
# a submission exists for nhs_number = 1
submission_data = [latest_submission_row('1', 'NO', n_days_ago(n=1))]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
# wholesaler data with an id corresponding to nhs_number=1
wholesaler_data = [
latest_wholesaler_opt_out_row(HEX_ID_1, n_days_ago(1), '3',
'test wholesaler comments')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_wholesaler_opt_out", wholesaler_data)
# WHEN
# we build the full submission stack and run it in presto
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# the results are 1 lines long and includes all the codes
assert len(results) == 1
assert 'test wholesaler comments' in results[0]
def test_full_submission_record_appends_opt_out_if_present():
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the tables are all built and reset
build_and_reset_data_sources(scenario_builder)
submission_data = [latest_submission_row('1', 'NO', n_days_ago(n=1))]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", submission_data)
opt_in_data = [
latest_la_feedback_opt_in_row('1', 'F003', n_days_ago(1),
'expect opt out')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_la_feedback_to_continue_boxes", opt_in_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = read_query('sql_to_build/full_submission_record.sql')
results = presto_transaction(query)
# THEN
# the results are 1 lines long and includes all the codes
assert len(results) == 1
assert 'F003' in results[0]
assert 'expect opt out' in results[0]
def test_round_trip():
results = []
try:
pg_transaction(
"CREATE TABLE IF NOT EXISTS book (id SERIAL, title TEXT)")
pg_transaction("DELETE FROM book")
for title in ("Ender's Game", "The Magus"):
pg_transaction(f"INSERT INTO book (title) VALUES (%s)", [title])
results = presto_transaction('SELECT * FROM book')
except Exception as e:
print(f"EXCEPTION: {repr(e)}")
finally:
pass
assert len(results) > 0
def test_wholesaler_feedback_selects_opt_out_codes_only(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the clean_deliveries_outcome_staging table has a mix of outcome entries
build_clean_deliveries_outcome_as_table(scenario_builder)
OPT_OUT_CODE = '3'
ANY_OTHER_CODE = '1'
feedback_data = [
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(5),
outcome=OPT_OUT_CODE,
comment='expected'),
clean_deliveries_outcome_row(id='2',
deldate=n_days_ago(4),
outcome=ANY_OTHER_CODE,
comment=''),
clean_deliveries_outcome_row(id='3',
deldate=n_days_ago(3),
outcome=OPT_OUT_CODE,
comment='expected'),
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(2),
outcome=ANY_OTHER_CODE,
comment='')
]
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_deliveries_outcome_staging", feedback_data)
# WHEN
# we build the latest wholesaler opt out stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_wholesaler_opt_out_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# the 2 opt out lines are selected
assert len(results) == 2
assert results[0][3] == 'expected'
assert results[0][3] == 'expected'
def test_wholesaler_feedback_returns_one_row_if_multiple_latest(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the all_la_feedback view will return multiple opt out entries for one person on the latest day
build_clean_deliveries_outcome_as_table(scenario_builder)
OPT_OUT_CODE = '3'
feedback_data = [
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(5),
outcome=OPT_OUT_CODE,
comment=''),
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(1),
outcome=OPT_OUT_CODE,
comment='most recent 1'),
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(1),
outcome=OPT_OUT_CODE,
comment='most recent 2'),
clean_deliveries_outcome_row(id='1',
deldate=n_days_ago(4),
outcome=OPT_OUT_CODE,
comment=''),
]
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_deliveries_outcome_staging", feedback_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_wholesaler_opt_out_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# only 1 result is returned and it is one of the most recent
assert len(results) == 1
assert {'most recent 1', 'most recent 2'}.__contains__(results[0][3])
def test_latest_feedback_deregister_template_selects_opt_out_feedback_codes(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the all_la_feedback view will return mixed feedback codes
build_all_la_feedback_as_table(scenario_builder)
opt_out_codes = ['W003', 'W004', 'D001', 'F002']
feedback_data = [
all_la_feedback_row('test_1_1', 'YF19', n_days_ago(n=10), ""),
all_la_feedback_row('test_1_2', 'W003', n_days_ago(n=10),
"opt out code"),
all_la_feedback_row('test_1_3', 'RM19', n_days_ago(n=10), ""),
all_la_feedback_row('test_1_4', 'D001', n_days_ago(n=10),
"opt out code"),
all_la_feedback_row('test_1_5', 'W004', n_days_ago(n=10),
"opt out code"),
all_la_feedback_row('test_1_6', 'YF19', n_days_ago(n=10), ""),
all_la_feedback_row('test_1_7', 'F002', n_days_ago(n=10),
"opt out code"),
]
scenario_builder.insert_multiple_into_arbitrary_table(
"all_la_feedback", feedback_data)
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_la_feedback_opt_out_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# the results are 3 lines long and include all the codes
assert len(results) == 4
codes = [result[1] for result in results]
assert set(codes) == set(opt_out_codes)
def test_submissions_feed_through_to_final_query(tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# a simple situation where users have registered on the web or ivr
build_and_reset_data_sources(scenario_builder)
scenario_builder.insert_multiple_into_arbitrary_table(
"latest_submission", [
latest_submission_row('1', 'NO', n_days_ago(n=1)),
latest_submission_row('2', 'NO', n_days_ago(n=1)),
latest_submission_row('3', 'NO', n_days_ago(n=1)),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"nhs_clean_staging", [
nhs_clean_staging_row(1),
nhs_clean_staging_row(2),
nhs_clean_staging_row(3),
])
scenario_builder.insert_multiple_into_arbitrary_table(
"clean_latest_address_staging", [
clean_latest_address_row(1),
clean_latest_address_row(2),
clean_latest_address_row(3)
])
# WHEN
# we build the latest feedback deregister stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/wholesaler_latest_user_submission_test_stack_TEMPLATE.sql'
)
results = presto_transaction(query)
# THEN
# there are 3 results returned
assert len(results) == 3
def test_latest_feedback_opt_out_returns_single_opt_out_row_if_two_on_the_same_day(
tmp_path: pytest.fixture):
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
# GIVEN
# the all_la_feedback view will return feedback for one person at multiple times
# including two lots of opt out feedback on the most recent occasion
test_nhs_number = f'{random_nhs_number()}'
feedback_data = [
all_la_feedback_row(test_nhs_number, 'YF19', n_days_ago(n=10), ""),
all_la_feedback_row(test_nhs_number, 'D001', n_days_ago(n=9), ""),
all_la_feedback_row(test_nhs_number, 'RM19', n_days_ago(n=8), ""),
all_la_feedback_row(test_nhs_number, 'W003', n_days_ago(n=6),
"either this one"),
all_la_feedback_row(test_nhs_number, 'YF19', n_days_ago(n=6), ""),
all_la_feedback_row(test_nhs_number, 'W004', n_days_ago(n=6),
"or this one")
]
scenario_builder.insert_multiple_into_arbitrary_table(
"all_la_feedback", feedback_data)
# WHEN
# we build the latest feedback opt out stack and run it in presto
query = build_query(
tmp_path,
'sql_to_build/latest_la_feedback_opt_out_stack_TEMPLATE.sql')
results = presto_transaction(query)
# THEN
# the results for test_number is 1 lines long and has one of the feedback results from 6 days ago
results_for_nhs_number = [
result for result in results if result[0] == test_nhs_number
]
assert len(results_for_nhs_number) == 1
assert results_for_nhs_number[0][
1] == 'W004' or results_for_nhs_number[0][1] == 'W003'
def test_latest_submission_picks_most_recent_submission():
with pg_connect() as con:
scenario_builder = ScenarioBuilder(con)
table_schema = {
'provenance': 'TEXT',
'nhs_number': 'TEXT',
'submission_time': 'TEXT',
'has_access_to_essential_supplies': 'TEXT',
'is_able_to_carry_supplies': 'TEXT',
'email_address': 'TEXT',
'phone_number_calls': 'TEXT',
'phone_number_texts': 'TEXT'
}
scenario_builder.build_arbitrary_table("all_submissions", table_schema)
submission_data = [
{
'provenance': 'IVR',
'nhs_number': '123',
'submission_time': n_days_ago(n=5),
'has_access_to_essential_supplies': 'YES',
'is_able_to_carry_supplies': 'YES',
'email_address': '*****@*****.**',
'phone_number_calls': '12345',
'phone_number_texts': '12345'
},
{
'provenance': 'WEB',
'nhs_number': '123',
'submission_time': n_days_ago(n=1),
'has_access_to_essential_supplies': 'NO',
'is_able_to_carry_supplies': 'no',
'email_address': 'test_mail@test_mail.com',
'phone_number_calls': '0891505050',
'phone_number_texts': '0891505050'
},
{
'provenance': 'IVR',
'nhs_number': '123',
'submission_time': n_days_ago(n=3),
'has_access_to_essential_supplies': 'YES',
'is_able_to_carry_supplies': 'YES',
'email_address': '*****@*****.**',
'phone_number_calls': '12345',
'phone_number_texts': '12345'
}, {
'provenance': 'IVR',
'nhs_number': '123',
'submission_time': n_days_ago(n=4),
'has_access_to_essential_supplies': 'YES',
'is_able_to_carry_supplies': 'YES',
'email_address': '*****@*****.**',
'phone_number_calls': '12345',
'phone_number_texts': '12345'
}
]
expected_row = submission_data[1]
scenario_builder.insert_multiple_into_arbitrary_table(
"all_submissions", submission_data
)
with open('sql_to_build/latest_submission.sql', 'r') as f:
query = f.read()
results = presto_transaction(query)
assert len(results) == 1
assert all((item in list(expected_row.values()) for item in results[0]))
def test_python_can_connect_to_presto():
results = presto_transaction('SELECT * FROM system.runtime.nodes')
assert len(results) > 0
