Skip to content

Commit 86e835b

Browse files
Copilotpethers
Copilot
and
pethers
committed
Update full_schema.sql with new politician decision pattern view and fix person_id reference
- Added view_riksdagen_politician_decision_pattern to full_schema.sql (line 8945) - Added idx_person_ref_person_id index to full_schema.sql (line 11770) - Fixed changelog to use person_reference_id instead of person_id (correct column name in document_person_reference_da_0 table) - Updated index definition to use person_reference_id - Verified build succeeds Co-authored-by: pethers <1726836+pethers@users.noreply.github.com>
1 parent 76c75f7 commit 86e835b

2 files changed

Lines changed: 47 additions & 4 deletions

File tree

‎service.data.impl/src/main/resources/db-changelog-1.35.xml‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -471,7 +471,7 @@ INNER JOIN document_person_reference_da_0 dpr
471471
472472
-- Join to person_data for politician identification
473473
INNER JOIN person_data pd
474-
ON pd.id = dpr.person_id
474+
ON pd.id = dpr.person_reference_id
475475
476476
-- Filter for valid data
477477
WHERE dpd.chamber IS NOT NULL
@@ -518,10 +518,10 @@ ORDER BY
518518
</comment>
519519

520520
<sql splitStatements="true">
521-
-- Composite index on person_id for politician-specific queries
521+
-- Index on person_reference_id for politician-specific queries
522522
CREATE INDEX IF NOT EXISTS idx_person_ref_person_id
523-
ON document_person_reference_da_0(person_id)
524-
WHERE person_id IS NOT NULL;
523+
ON document_person_reference_da_0(person_reference_id)
524+
WHERE person_reference_id IS NOT NULL;
525525
</sql>
526526

527527
<rollback>

‎service.data.impl/src/main/resources/full_schema.sql‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8941,6 +8941,42 @@ CREATE MATERIALIZED VIEW public.view_riksdagen_party_document_daily_summary AS
89418941
-- Name: view_riksdagen_politician; Type: VIEW; Schema: public; Owner: -
89428942
--
89438943

8944+
--
8945+
-- Name: view_riksdagen_politician_decision_pattern; Type: VIEW; Schema: public; Owner: -
8946+
--
8947+
8948+
CREATE VIEW public.view_riksdagen_politician_decision_pattern AS
8949+
SELECT pd.id AS person_id,
8950+
pd.first_name,
8951+
pd.last_name,
8952+
dpr.party_short_code AS party,
8953+
dpd.committee,
8954+
dd.org AS committee_org,
8955+
date_trunc('month'::text, (dd.made_public_date)::timestamp with time zone) AS decision_month,
8956+
EXTRACT(year FROM dd.made_public_date) AS decision_year,
8957+
EXTRACT(month FROM dd.made_public_date) AS decision_month_num,
8958+
count(*) AS total_decisions,
8959+
count(*) FILTER (WHERE (((upper((dpd.chamber)::text) ~~ '%BIFALL%'::text) OR (upper((dpd.chamber)::text) ~~ '%GODKÄNT%'::text)) OR (upper((dpd.chamber)::text) ~~ '%BIFALLA%'::text))) AS approved_decisions,
8960+
count(*) FILTER (WHERE ((upper((dpd.chamber)::text) ~~ '%AVSLAG%'::text) OR (upper((dpd.chamber)::text) ~~ '%AVSLÅ%'::text))) AS rejected_decisions,
8961+
count(*) FILTER (WHERE ((upper((dpd.chamber)::text) ~~ '%ÅTERFÖRVISNING%'::text) OR (upper((dpd.chamber)::text) ~~ '%ÅTERFÖRVISA%'::text))) AS referred_back_decisions,
8962+
count(*) FILTER (WHERE (((((((upper((dpd.chamber)::text) !~~ '%BIFALL%'::text) AND (upper((dpd.chamber)::text) !~~ '%AVSLAG%'::text)) AND (upper((dpd.chamber)::text) !~~ '%GODKÄNT%'::text)) AND (upper((dpd.chamber)::text) !~~ '%BIFALLA%'::text)) AND (upper((dpd.chamber)::text) !~~ '%AVSLÅ%'::text)) AND (upper((dpd.chamber)::text) !~~ '%ÅTERFÖRVISNING%'::text)) AND (upper((dpd.chamber)::text) !~~ '%ÅTERFÖRVISA%'::text))) AS other_decisions,
8963+
round((((100.0 * (count(*) FILTER (WHERE (((upper((dpd.chamber)::text) ~~ '%BIFALL%'::text) OR (upper((dpd.chamber)::text) ~~ '%GODKÄNT%'::text)) OR (upper((dpd.chamber)::text) ~~ '%BIFALLA%'::text)))))::numeric / (NULLIF(count(*), (0)::bigint))::numeric)), 2) AS approval_rate,
8964+
round((((100.0 * (count(*) FILTER (WHERE ((upper((dpd.chamber)::text) ~~ '%AVSLAG%'::text) OR (upper((dpd.chamber)::text) ~~ '%AVSLÅ%'::text)))))::numeric / (NULLIF(count(*), (0)::bigint))::numeric)), 2) AS rejection_rate,
8965+
min(dd.made_public_date) AS earliest_decision_date,
8966+
max(dd.made_public_date) AS latest_decision_date
8967+
FROM ((((((document_proposal_data dpd
8968+
JOIN document_proposal_container dpc ON ((dpc.proposal_document_proposal_c_0 = dpd.hjid)))
8969+
JOIN document_status_container dsc ON ((dsc.document_proposal_document_s_0 = dpc.hjid)))
8970+
JOIN document_data dd ON (((dd.id)::text = (dsc.document_document_status_con_0)::text)))
8971+
JOIN document_person_reference_co_0 dprc ON ((dprc.hjid = dsc.document_person_reference_co_1)))
8972+
JOIN document_person_reference_da_0 dpr ON ((dpr.document_person_reference_li_1 = dprc.hjid)))
8973+
JOIN person_data pd ON (((pd.id)::text = (dpr.person_reference_id)::text)))
8974+
WHERE ((((dpd.chamber IS NOT NULL) AND (dpd.committee IS NOT NULL)) AND (dd.made_public_date IS NOT NULL)) AND ((pd.id IS NOT NULL) AND (length((dpd.chamber)::text) >= 6) AND (length((dpd.chamber)::text) <= 29)))
8975+
GROUP BY pd.id, pd.first_name, pd.last_name, dpr.party_short_code, dpd.committee, dd.org, (date_trunc('month'::text, (dd.made_public_date)::timestamp with time zone)), (EXTRACT(year FROM dd.made_public_date)), (EXTRACT(month FROM dd.made_public_date))
8976+
HAVING (count(*) > 0)
8977+
ORDER BY (EXTRACT(year FROM dd.made_public_date)) DESC, (EXTRACT(month FROM dd.made_public_date)) DESC, pd.last_name, pd.first_name, dpd.committee;
8978+
8979+
89448980
CREATE VIEW public.view_riksdagen_politician AS
89458981
SELECT base.person_id,
89468982
base.first_name,
@@ -11730,6 +11766,13 @@ CREATE INDEX idx_party_summary_party_won ON public.view_riksdagen_vote_data_ball
1173011766

1173111767
CREATE INDEX idx_person_ref_party ON public.document_person_reference_da_0 USING btree (party_short_code) WHERE (party_short_code IS NOT NULL);
1173211768

11769+
--
11770+
-- Name: idx_person_ref_person_id; Type: INDEX; Schema: public; Owner: -
11771+
--
11772+
11773+
CREATE INDEX idx_person_ref_person_id ON public.document_person_reference_da_0 USING btree (person_reference_id) WHERE (person_reference_id IS NOT NULL);
11774+
11775+
1173311776

1173411777
--
1173511778
-- Name: idx_person_status; Type: INDEX; Schema: public; Owner: -

0 commit comments

Comments
 (0)