2020-12-28 06:50

Switching API to the fecp-driven sched tables: schedule c, schedule d, schedule e, schedule f tables and related objects

This is to deploy the work completed in derived from #3007

We are switching API to access fecp-driven sched tables. The big, partitioned sched_a and sched_b tables had been switched. This issue is focused on the rest of the the schedule tables; including schedule c, schedule e (including schedule_e and form57 tables), and schedule f tables and their related objects .

[x] modify table structure of fecp-driven sched c, sched d, and sched f tables to add columns used by API to eliminate the need for MV on these two tables. [x] make API code changes to use fecp-driven schedule tables [x] modify materialized view definition scripts that utilize these tables [x] evaluate and create appropriate indexes on the fecp-driven schedule tables


  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • weixin_39875941 weixin_39875941 4月前
    • For fec_fitem_sched_e related tables (including f57): mv public.ofec_sched_e_mv base on the FECP-driven tables had been created for ticket that add cmpte_tp, cand_id; it had been used instead of ofec_sched_e table ever since.

    The source of public.ofec_sched_e_aggregate_candidate_mv will be switched to FECP driven tables instead of views: fec_fitem_sched_e_vw => disclosure.fec_fitem_sched_e fec_fitem_f57_vw => disclosure.fec_fitem_f57

    • For fec_fitem_sched_c table: A structure comparison of public.ofec_sched_c_mv vs disclosure.fec_fitem_sched_c had been done.

    The following columns will be added: candidate_name_text loan_source_name_text

    • for disclosure.fec_fitem_sched_f table: A structure comparison of public.ofec_sched_f_mv vs disclosure.fec_fitem_sched_f had been done.

    The following column will be added: payee_name_text

    • for disclosure.fec_fitem_sched_d table: A structure comparison of public.fec_fitem_sched_d_vw vs disclosure.fec_fitem_sched_d had been done. disclosure.fec_fitem_sched_d data type definition is more precise.

    fec_fitem_sched_d_vw |   | disclosure.fec_fitem_sched_d |   -- | -- | -- | -- tran_id | character varying | tran_id | character varying(32) schedule_type | unknown | schedule_type | character varying(8) election_cycle | numeric | election_cycle | numeric(4,0)

    The following column will be added: creditor_debtor_name_text

    点赞 评论 复制链接分享
  • weixin_39875941 weixin_39875941 4月前

    Triggers and trigger functions will be added to fec_fitem_sched_c, fec_fitem_sched_d and fec_fitem_sched_f tables to handle the population of the added columns.

    Data comparison had been performed between public.ofec_sched_c_mv vs disclosure.fec_fitem_sched_c public.fec_fitem_sched_d_vw vs disclosure.fec_fitem_sched_d public.ofec_sched_f_mv vs disclosure.fec_fitem_sched_f All data except pg_date column matched (the new columns are derived from existing column)

    点赞 评论 复制链接分享
  • weixin_39875941 weixin_39875941 4月前

    For public.ofec_sched_e_aggregate_candidate_mv: In addition to switch the source to use FECP driven tables, some improvement had been made: There is already election_cycle column in these tables (and views too) = (records.rpt_yr + records.rpt_yr % 2::numeric). No need to calculate again

    in original MV definition: WHERE records.exp_amt IS NOT NULL AND (records.rpt_tp::text <> ALL (ARRAY['24'::character varying, '48'::character varying]::text[])) AND (records.memo_cd::text <> 'X'::text OR records.memo_cd IS NULL)

    There is no rpt_tp '24', '48' in these two tables (f_itme tables sched_tp_cd = 'SE' does not include rpt_tp '24', '48'; disclosure.fec_fitem_f57 exclude data with rpt_tp '24', '48' while loading. and disclosure.fec_fitem_f57 does not have memo_cd column

    in original MV definition, there is an un-necessary join of fec_fitem_f57_vw f57 JOIN fec_vsum_f5_vw. fec_fitem_f57_vw () already has rpt_yr information from f_item table, no need to join to fec_vsum_f5_vw to get this information

    点赞 评论 复制链接分享
  • weixin_39875941 weixin_39875941 4月前

    the source of API for the following end_point had been updated to use the FECP driven tables instead of MVs. These MVs will be dropped in issue #3100 http://localhost:5000/developers/#/party-coordinated%20expenditures should be disclosure.fec_fitem_sched_f instead of public.ofec_sched_f_mv

    http://localhost:5000/developers/#/loans should be disclosure.fec_fitem_sched_c instead of public.ofec_sched_c_mv

    http://localhost:5000/developers/#/debts should be disclousre.fec.fitem_sched_d instead of public.fec_fitem_sched_d_vw

    点赞 评论 复制链接分享
  • weixin_39881155 weixin_39881155 4月前

    PR is merged, and so close.

    点赞 评论 复制链接分享
  • weixin_39875941 weixin_39875941 4月前

    There are more itemized tables/views in use nowadays. This original ticket will be used for sched_e related table sets (fec_fitem_sched_e and fec_fitem_f57) and their related views. This is the last bigger itemized table sets. More individual issues will be opened to address other smaller itemized tables.

    点赞 评论 复制链接分享
  • weixin_39584549 weixin_39584549 4月前

    does this work still need to be completed?

    点赞 评论 复制链接分享
  • weixin_39875941 weixin_39875941 4月前

    Yes. We had completed part of the work in other PR. But work still need to be done to complete this task.

    From: Laura Beaufort Sent: Tuesday, November 27, 2018 11:42 AM To: fecgov/openFEC Cc: Jiingjau Jeng ; Mention Subject: Re: [fecgov/openFEC] Switching API to the fecp-driven schedule table (sched_e) (#3089)

    https://github.com/fecjjeng does this work still need to be completed?

    — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/fecgov/openFEC/issues/3089#issuecomment-442128514, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AXyaGj13AF_kxS1KBt2F3WmJWV0DtkHvks5uzWtKgaJpZM4Tmi3E.

    点赞 评论 复制链接分享