def update_schemas(processes=1):
"""This updates the smaller tables and views. It is run on deploy.
"""
logger.info("Starting DB refresh...")
processes = int(processes)
graph = flow.get_graph()
for task in nx.topological_sort(graph):
path = os.path.join('data', 'sql_updates', '{}.sql'.format(task))
execute_sql_file(path)
execute_sql_file('data/rename_temporary_views.sql')
logger.info("Finished DB refresh.")
def update_schemas(processes=1):
"""This updates the smaller tables and views. It is run on deploy.
"""
logger.info("Starting DB refresh...")
processes = int(processes)
graph = flow.get_graph()
for task in nx.topological_sort(graph):
path = os.path.join('data', 'sql_updates', '{}.sql'.format(task))
execute_sql_file(path)
execute_sql_file('data/rename_temporary_views.sql')
logger.info("Finished DB refresh.")
def refresh_materialized(concurrent=True):
"""Refresh materialized views in dependency order
We usually want to refresh them concurrently so that we don't block other
connections that use the DB. In the case of tests, we cannot refresh concurrently as the
tables are not initially populated.
"""
logger.info('Refreshing materialized views...')
materialized_view_names = {
'audit_case': [
'ofec_audit_case_mv', 'ofec_audit_case_category_rel_mv',
'ofec_audit_case_sub_category_rel_mv',
'ofec_committee_fulltext_audit_mv',
'ofec_candidate_fulltext_audit_mv'
],
'cand_cmte_linkage': ['ofec_cand_cmte_linkage_mv'],
'candidate_aggregates': ['ofec_candidate_totals_mv'],
'candidate_detail': ['ofec_candidate_detail_mv'],
'candidate_election': ['ofec_candidate_election_mv'],
'candidate_flags': ['ofec_candidate_flag_mv'],
'candidate_fulltext': ['ofec_candidate_fulltext_mv'],
'candidate_history': ['ofec_candidate_history_mv'],
'candidate_history_future':
['ofec_candidate_history_with_future_election_mv'],
'candidate_totals_detail': ['ofec_candidate_totals_detail_mv'],
'committee_detail': ['ofec_committee_detail_mv'],
'committee_fulltext': ['ofec_committee_fulltext_mv'],
'committee_history': ['ofec_committee_history_mv'],
'communication_cost': ['ofec_communication_cost_mv'],
'communication_cost_by_candidate':
['ofec_communication_cost_aggregate_candidate_mv'],
'electioneering': ['ofec_electioneering_mv'],
'electioneering_by_candidate':
['ofec_electioneering_aggregate_candidate_mv'],
'elections_list': ['ofec_elections_list_mv'],
'filing_amendments_all': ['ofec_amendments_mv'],
'filing_amendments_house_senate': [
'ofec_house_senate_electronic_amendments_mv',
'ofec_house_senate_paper_amendments_mv'
],
'filing_amendments_pac_party': [
'ofec_pac_party_electronic_amendments_mv',
'ofec_pac_party_paper_amendments_mv'
],
'filing_amendments_presidential': [
'ofec_presidential_electronic_amendments_mv',
'ofec_presidential_paper_amendments_mv'
],
'filings': [
'ofec_filings_amendments_all_mv', 'ofec_filings_mv',
'ofec_filings_all_mv'
],
'ofec_agg_coverage_date': ['ofec_agg_coverage_date_mv'],
'ofec_sched_a_agg_state': ['ofec_sched_a_agg_state_mv'],
'ofec_sched_e_mv': ['ofec_sched_e_mv'],
'reports_house_senate': ['ofec_reports_house_senate_mv'],
'reports_ie': ['ofec_reports_ie_only_mv'],
'reports_pac_party': ['ofec_reports_pac_party_mv'],
'reports_presidential': ['ofec_reports_presidential_mv'],
'sched_a_by_size_merged': ['ofec_sched_a_aggregate_size_merged_mv'],
'sched_a_by_state_recipient_totals':
['ofec_sched_a_aggregate_state_recipient_totals_mv'],
'sched_e_by_candidate': ['ofec_sched_e_aggregate_candidate_mv'],
'totals_combined': ['ofec_totals_combined_mv'],
'totals_house_senate': ['ofec_totals_house_senate_mv'],
'totals_ie': ['ofec_totals_ie_only_mv'],
'totals_pac_party': ['ofec_totals_pacs_parties_mv'],
'totals_presidential': ['ofec_totals_presidential_mv'],
}
graph = flow.get_graph()
with db.engine.begin() as connection:
for node in nx.topological_sort(graph):
materialized_views = materialized_view_names.get(node, None)
if materialized_views:
for mv in materialized_views:
logger.info('Refreshing %s', mv)
if concurrent:
refresh_command = 'REFRESH MATERIALIZED VIEW CONCURRENTLY {}'.format(
mv)
else:
refresh_command = 'REFRESH MATERIALIZED VIEW {}'.format(
mv)
connection.execute(
sa.text(refresh_command).execution_options(
autocommit=True))
else:
logger.error(
'Error refreshing node %s: not found.'.format(node))
logger.info('Finished refreshing materialized views.')
def refresh_materialized(concurrent=True):
"""Refresh materialized views in dependency order
We usually want to refresh them concurrently so that we don't block other
connections that use the DB. In the case of tests, we cannot refresh concurrently as the
tables are not initially populated.
"""
logger.info('Refreshing materialized views...')
materialized_view_names = {
'filing_amendments_house_senate': ['ofec_house_senate_paper_amendments_mv'],
'sched_f': ['ofec_sched_f_mv'],
'reports_ie': ['ofec_reports_ie_only_mv'], # Anomale
'electioneering_by_candidate': ['ofec_electioneering_aggregate_candidate_mv'],
'candidate_history': ['ofec_candidate_history_mv'],
'candidate_detail': ['ofec_candidate_detail_mv'],
'candidate_election': ['ofec_candidate_election_mv'],
'candidate_history_latest': ['ofec_candidate_history_latest_mv'],
'election_outcome': ['ofec_election_result_mv'],
'sched_e_by_candidate': ['ofec_sched_e_aggregate_candidate_mv'],
'filing_amendments_presidential': ['ofec_presidential_paper_amendments_mv'],
'filing_amendments_pac_party': ['ofec_pac_party_paper_amendments_mv'],
'cand_cmte_linkage': ['ofec_cand_cmte_linkage_mv'],
'communication_cost_by_candidate': ['ofec_communication_cost_aggregate_candidate_mv'],
'electioneering': ['ofec_electioneering_mv'],
'filing_amendments_all': ['ofec_amendments_mv'],
'reports_house_senate': ['ofec_reports_house_senate_mv'],
'reports_pac_party': ['ofec_reports_pacs_parties_mv'],
'reports_presidential': ['ofec_reports_presidential_mv'],
'committee_history': ['ofec_committee_history_mv'],
'communication_cost': ['ofec_communication_cost_mv'],
'filings': ['ofec_filings_amendments_all_mv', 'ofec_filings_mv', 'ofec_filings_all_mv'],
'totals_combined': ['ofec_totals_combined_mv'],
'totals_ie': ['ofec_totals_ie_only_mv'],
'totals_house_senate': ['ofec_totals_house_senate_mv'],
'totals_presidential': ['ofec_totals_presidential_mv'],
'candidate_aggregates': ['ofec_candidate_totals_mv'],
'candidate_flags': ['ofec_candidate_flag_mv'],
'sched_c': ['ofec_sched_c_mv'],
'sched_a_by_state_recipient_totals': ['ofec_sched_a_aggregate_state_recipient_totals_mv'],
'rad_analyst': ['ofec_rad_mv'],
'committee_detail': ['ofec_committee_detail_mv'],
'committee_fulltext': ['ofec_committee_fulltext_mv'],
'sched_a_by_size_merged': ['ofec_sched_a_aggregate_size_merged_mv'],
'totals_pac_party': ['ofec_totals_pacs_parties_mv', 'ofec_totals_pacs_mv', 'ofec_totals_parties_mv'],
'large_aggregates': ['ofec_entity_chart_mv'],
'candidate_fulltext': ['ofec_candidate_fulltext_mv'],
'totals_candidate_committee': ['ofec_totals_candidate_committees_mv'],
'audit_case': ['ofec_audit_case_mv', 'ofec_audit_case_category_rel_mv', 'ofec_audit_case_sub_category_rel_mv', 'ofec_committee_fulltext_audit_mv', 'ofec_candidate_fulltext_audit_mv'],
'elections_list': ['ofec_elections_list_mv']
}
graph = flow.get_graph()
with db.engine.begin() as connection:
for node in nx.topological_sort(graph):
materialized_views = materialized_view_names.get(node, None)
if materialized_views:
for mv in materialized_views:
logger.info('Refreshing %s', mv)
if concurrent:
refresh_command = 'REFRESH MATERIALIZED VIEW CONCURRENTLY {}'.format(mv)
else:
refresh_command = 'REFRESH MATERIALIZED VIEW {}'.format(mv)
connection.execute(
sa.text(refresh_command).execution_options(
autocommit=True
)
)
else:
logger.error('Error refreshing node %s: not found.'.format(node))
logger.info('Finished refreshing materialized views.')