service.data.impl/src/main/resources/db-changelog-1.6.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<changeSet author="pether" id="1414872417007-265">
<dropView viewName="view_riksdagen_document_type_daily_summary" />
<dropView viewName="view_riksdagen_politician_document_daily_summary" />
<dropView viewName="view_riksdagen_party_document_daily_summary" />
<dropView viewName="view_riksdagen_org_document_daily_summary" />
<dropView viewName="view_riksdagen_politician_document" />
<sql>create materialized view view_riksdagen_politician_document as select hjid as id,document_document_status_con_0 as doc_id,document_type,label,made_public_date,org,number_value,rm,status,sub_title,sub_type,temp_label,title,role_description,person_reference_id,reference_name,party_short_code,order_number from (select * from document_status_container left join document_data on document_status_container.document_document_status_con_0 = document_data.id) as e3 left join (select document_person_reference_da_0.hjid as id,role_description,person_reference_id,reference_name,party_short_code,order_number,document_person_reference_li_1 from document_person_reference_da_0 left join (select document_person_reference_co_0.hjid as person_id_ref from document_status_container left join document_person_reference_co_0 on document_status_container.hjid = document_person_reference_co_0.hjid) e2 on document_person_reference_da_0.document_person_reference_li_1 = e2.person_id_ref) e4 on e3.document_person_reference_co_1 = e4.document_person_reference_li_1</sql>
<sql>create materialized view view_riksdagen_document_type_daily_summary as select left(made_public_date,10) as embedded_id_public_date,document_type as embedded_id_document_type,count(*) as total from document_element group by left(made_public_date,10),document_type</sql>
<sql>create materialized view view_riksdagen_politician_document_daily_summary as select made_public_date as embedded_id_public_date,person_reference_id as embedded_id_person_id,document_type as embedded_id_document_type,count(*) as total from view_riksdagen_politician_document group by made_public_date,document_type,person_reference_id</sql>
<sql>create materialized view view_riksdagen_party_document_daily_summary as select made_public_date as embedded_id_public_date,party_short_code as embedded_id_party_short_code,document_type as embedded_id_document_type,count(distinct id) distinct_documents,count(distinct person_reference_id) as distinct_people,count(*) total from view_riksdagen_politician_document group by made_public_date,document_type,party_short_code;</sql>
<sql>create materialized view view_riksdagen_org_document_daily_summary as select left(made_public_date,10) as embedded_id_public_date,org as embedded_id_org,document_type,count(*) as total from document_element group by left(made_public_date,10),document_type,org</sql>
</changeSet>
<changeSet author="pether" id="1414872417007-266">
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_politician_summary_index ON view_riksdagen_vote_data_ballot_politician_summary (embedded_id_intressent_id)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_politician_summary_daily_index ON view_riksdagen_vote_data_ballot_politician_summary_daily (embedded_id_intressent_id)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_politician_summary_weekly_index ON view_riksdagen_vote_data_ballot_politician_summary_weekly (embedded_id_intressent_id)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_politician_summary_monthly_index ON view_riksdagen_vote_data_ballot_politician_summary_monthly (embedded_id_intressent_id)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_politician_summary_annual_index ON view_riksdagen_vote_data_ballot_politician_summary_annual (embedded_id_intressent_id)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_party_summary_index ON view_riksdagen_vote_data_ballot_party_summary (embedded_id_party)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_party_summary_daily_index ON view_riksdagen_vote_data_ballot_party_summary_daily (embedded_id_party)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_party_summary_weekly_index ON view_riksdagen_vote_data_ballot_party_summary_weekly (embedded_id_party)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_party_summary_monthly_index ON view_riksdagen_vote_data_ballot_party_summary_monthly (embedded_id_party)</sql>
<sql>CREATE INDEX view_riksdagen_vote_data_ballot_party_summary_annual_index ON view_riksdagen_vote_data_ballot_party_summary_annual (embedded_id_party)</sql>
<sql>CREATE INDEX view_riksdagen_politician_document_index ON view_riksdagen_politician_document (person_reference_id)</sql>
<sql>CREATE INDEX view_riksdagen_document_type_daily_summary_index ON view_riksdagen_document_type_daily_summary (embedded_id_document_type)</sql>
<sql>CREATE INDEX view_riksdagen_politician_document_daily_summary_index ON view_riksdagen_politician_document_daily_summary (embedded_id_person_id)</sql>
<sql>CREATE INDEX view_riksdagen_party_document_daily_summary_index ON view_riksdagen_party_document_daily_summary (embedded_id_party_short_code)</sql>
<sql>CREATE INDEX view_riksdagen_org_document_daily_summary_index ON view_riksdagen_org_document_daily_summary (embedded_id_org)</sql>
</changeSet>
<changeSet author="pether" id="1414872417007-267">
<dropView viewName="view_worldbank_indicator_data_country_summary" />
<sql>create materialized view view_worldbank_indicator_data_country_summary as select indicator_id as embedded_id_indicator_id,country_id as embedded_id_country_id,max(indicator_name) as indicator_name,max(source_id) as source_id,max(source_value) as source_value,max(source_note) as source_note,max(source_organization) as source_organization,min(year_date) as start_year, max(year_date) as end_year,count(*) as data_point,max(topics::text) as topics from world_bank_data left join (select * from indicator_element inner join (select topic_topics_hjid,string_agg(distinct value_,';') as topics,count(*) as number_of_topics from topic group by topic_topics_hjid) AS derivedTable on indicator_element.topics_indicator_element_hjid=derivedTable.topic_topics_hjid) as indicators on indicator_id = indicators.id where data_value!='' and data_value!='0' and year_date is not null and source_organization is not null and source_organization !='' group by indicator_id,country_id</sql>
</changeSet>
<changeSet author="pether (generated)" id="1414872417007-268">
<addColumn tableName="application_session">
<column name="session_id" type="varchar(255)"/>
<column name="locale" type="varchar(255)"/>
<column name="operating_system" type="varchar(255)"/>
</addColumn>
</changeSet>
<changeSet author="pether (generated)" id="1414872417007-269">
<addColumn tableName="application_action_event">
<column name="session_id" type="varchar(255)"/>
<column name="page" type="varchar(255)"/>
<column name="page_mode" type="varchar(255)"/>
<column name="element_id" type="varchar(255)"/>
<column name="action_name" type="varchar(255)"/>
<column name="user_id" type="varchar(255)"/>
<column name="error_message" type="varchar(8192)"/>
<column name="application_message" type="varchar(255)"/>
</addColumn>
</changeSet>
<changeSet author="pether (generated)" id="1414872417007-270">
<addColumn tableName="user_account">
<column name="created_date" type="TIMESTAMP WITHOUT TIME ZONE" />
</addColumn>
</changeSet>
<changeSet author="pether (generated)" id="1414872417007-271">
<addColumn tableName="application_session">
<column name="user_id" type="varchar(255)"/>
</addColumn>
</changeSet>
<changeSet author="pether (generated)" id="1414872417007-272">
<addColumn tableName="application_session">
<column name="destroyed_date" type="TIMESTAMP WITHOUT TIME ZONE" />
</addColumn>
</changeSet>
<changeSet author="pether" id="1414872417007-273">
<sql>create view view_application_action_event_page_hourly_summary as select page as embedded_id_page,date_trunc('hour',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('hour',created_date) order by date_trunc('hour',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('hour',created_date) order by date_trunc('hour',created_date),count(*) desc) as rank from application_action_event where page is not null group by page,date_trunc('hour',created_date) order by date_trunc('hour',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_daily_summary as select page as embedded_id_page,date_trunc('day',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('day',created_date) order by date_trunc('day',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('day',created_date) order by date_trunc('day',created_date),count(*) desc) as rank from application_action_event where page is not null group by page,date_trunc('day',created_date) order by date_trunc('day',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_weekly_summary as select page as embedded_id_page,date_trunc('week',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('week',created_date) order by date_trunc('week',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('week',created_date) order by date_trunc('week',created_date),count(*) desc) as rank from application_action_event where page is not null group by page,date_trunc('week',created_date) order by date_trunc('week',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_annual_summary as select page as embedded_id_page,date_trunc('year',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('year',created_date) order by date_trunc('year',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('year',created_date) order by date_trunc('year',created_date),count(*) desc) as rank from application_action_event where page is not null group by page,date_trunc('year',created_date) order by date_trunc('year',created_date), count(*) desc</sql>
</changeSet>
<changeSet author="pether" id="1414872417007-274">
<sql>create view view_application_action_event_page_modes_hourly_summary as select page as embedded_id_page,page_mode as embedded_id_page_mode, date_trunc('hour',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('hour',created_date) order by date_trunc('hour',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('hour',created_date) order by date_trunc('hour',created_date),count(*) desc) as rank from application_action_event where page is not null and page_mode is not null group by page,page_mode,date_trunc('hour',created_date) order by date_trunc('hour',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_modes_daily_summary as select page as embedded_id_page,page_mode as embedded_id_page_mode, date_trunc('day',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('day',created_date) order by date_trunc('day',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('day',created_date) order by date_trunc('day',created_date),count(*) desc) as rank from application_action_event where page is not null and page_mode is not null group by page,page_mode,date_trunc('day',created_date) order by date_trunc('day',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_modes_weekly_summary as select page as embedded_id_page,page_mode as embedded_id_page_mode, date_trunc('week',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('week',created_date) order by date_trunc('week',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('week',created_date) order by date_trunc('week',created_date),count(*) desc) as rank from application_action_event where page is not null and page_mode is not null group by page,page_mode,date_trunc('week',created_date) order by date_trunc('week',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_modes_annual_summary as select page as embedded_id_page,page_mode as embedded_id_page_mode, date_trunc('year',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY date_trunc('year',created_date) order by date_trunc('year',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY date_trunc('year',created_date) order by date_trunc('year',created_date),count(*) desc) as rank from application_action_event where page is not null and page_mode is not null group by page,page_mode,date_trunc('year',created_date) order by date_trunc('year',created_date), count(*) desc</sql>
</changeSet>
<changeSet author="pether" id="1414872417007-275">
<sql>create view view_application_action_event_page_element_hourly_summary as select page as embedded_id_page,element_id as embedded_id_element_id, date_trunc('hour',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY page,date_trunc('hour',created_date) order by date_trunc('hour',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY page,date_trunc('hour',created_date) order by date_trunc('hour',created_date),count(*) desc) as rank from application_action_event where page is not null and element_id is not null and element_id != '' group by page,element_id,date_trunc('hour',created_date) order by date_trunc('hour',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_element_daily_summary as select page as embedded_id_page,element_id as embedded_id_element_id, date_trunc('day',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY page,date_trunc('day',created_date) order by date_trunc('day',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY page,date_trunc('day',created_date) order by date_trunc('day',created_date),count(*) desc) as rank from application_action_event where page is not null and element_id is not null and element_id != '' group by page,element_id,date_trunc('day',created_date) order by date_trunc('day',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_element_weekly_summary as select page as embedded_id_page,element_id as embedded_id_element_id, date_trunc('week',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY page,date_trunc('week',created_date) order by date_trunc('week',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY page,date_trunc('week',created_date) order by date_trunc('week',created_date),count(*) desc) as rank from application_action_event where page is not null and element_id is not null and element_id != '' group by page,element_id,date_trunc('week',created_date) order by date_trunc('week',created_date), count(*) desc</sql>
<sql>create view view_application_action_event_page_element_annual_summary as select page as embedded_id_page,element_id as embedded_id_element_id, date_trunc('year',created_date) as embedded_id_created_date ,count(*) as hits,percent_rank() over (PARTITION BY page,date_trunc('year',created_date) order by date_trunc('year',created_date),count(*) desc) as rank_percentage,rank() over (PARTITION BY page,date_trunc('year',created_date) order by date_trunc('year',created_date),count(*) desc) as rank from application_action_event where page is not null and element_id is not null and element_id != '' group by page,element_id,date_trunc('year',created_date) order by date_trunc('year',created_date), count(*) desc</sql>
</changeSet>
<changeSet author="pether" id="1414872417007-276">
<createTable tableName="application_configuration">
<column name="hjid" type="INT8">
<constraints nullable="false" />
</column>
<column name="model_object_id" type="INT4" />
<column name="model_object_version" type="INT4" />
<column name="config_title" type="VARCHAR(255)" />
<column name="config_description" type="VARCHAR(255)" />
<column name="configuration_group" type="VARCHAR(255)" />
<column name="component" type="VARCHAR(255)" />
<column name="component_title" type="VARCHAR(255)" />
<column name="component_description" type="VARCHAR(255)" />
<column name="property_id" type="VARCHAR(255)" />
<column name="property_value" type="VARCHAR(255)" />
<column name="created_date" type="TIMESTAMP WITHOUT TIME ZONE" />
<column name="updated_date" type="TIMESTAMP WITHOUT TIME ZONE" />
</createTable>
</changeSet>
<changeSet author="pether" id="1414872417007-277">
<dropView viewName="view_riksdagen_party_summary" />
<dropView viewName="view_riksdagen_politician" /> <createView viewName="view_riksdagen_politician">select
view_riksdagen_party_member.id as person_id,
max(first_name) as first_name,
max(last_name) as last_name,
max(party) as party,
max(born_year) as born_year,
max(gender) as gender,
min(assignment_data.from_date) as first_assignment_date,
max(assignment_data.to_date) as last_assignment_date,
sum(CASE WHEN assignment_data.status like 'Ledig%' THEN 0 else (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END) end) as total_days_served,
sum( CASE WHEN assignment_type='kammaruppdrag' and assignment_data.status not like 'Ledig%' THEN ((CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_parliament,
sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_committee,
sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_government,
sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_eu,
(CASE WHEN max(to_date) >= CURRENT_DATE THEN true ELSE false END) as active,
count(*) total_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments,
sum( CASE WHEN (assignment_type='talmansuppdrag') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_speaker,
CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (assignment_type='talmansuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_speaker,
sum( CASE WHEN (assignment_type='partiuppdrag') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_party,
CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (assignment_type='partiuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_party,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as current_committee_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as current_ministry_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as current_party_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as current_speaker_assignments,
sum((CASE WHEN org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as total_committee_assignments,
sum((CASE WHEN assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as total_party_assignments,
sum((CASE WHEN assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as total_speaker_assignments,
sum((CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as total_ministry_assignments,
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND detail='Europaparlamentet' THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_eu,
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_government,(
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (org_code is not null and assignment_type='uppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END)) as active_committee,
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status not like 'Ledig%' THEN 1 ELSE (CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status like 'Ledig%' THEN 0 ELSE 0 END) END) > 0 THEN true ELSE false END) as active_parliament
from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
<createView viewName="view_riksdagen_party_summary">select
party,
min(first_assignment_date) as first_assignment_date,
max(last_assignment_date) as last_assignment_date,
sum(total_days_served::int8)::int8 as total_days_served,
sum(total_days_served_parliament::int8)::int8 as total_days_served_parliament,
sum(total_days_served_committee::int8)::int8 as total_days_served_committee,
sum(total_days_served_government::int8)::int8 as total_days_served_government,
sum(total_days_served_eu::int8)::int8 as total_days_served_eu,
sum(total_days_served_speaker::int8)::int8 as total_days_served_speaker,
sum(total_days_served_party::int8)::int8 as total_days_served_party,
bool_or(active) as active,
bool_or(active_eu) as active_eu,
bool_or(active_government) as active_government,
bool_or(active_committee) as active_committee,
bool_or(active_parliament) as active_parliament,
bool_or(active_speaker) as active_speaker,
bool_or(active_party) as active_party,
sum(CASE WHEN active THEN 1 ELSE 0 END) as total_active,
sum(CASE WHEN active_eu THEN 1 ELSE 0 END) as total_active_eu,
sum(CASE WHEN active_government THEN 1 ELSE 0 END) as total_active_government,
sum(CASE WHEN active_committee THEN 1 ELSE 0 END) as total_active_committee,
sum(CASE WHEN active_parliament THEN 1 ELSE 0 END) as total_active_parliament,
sum(total_assignments::int8)::int8 as total_assignments,
sum(total_party_assignments::int8)::int8 as total_party_assignments,
sum(total_committee_assignments::int8)::int8 as total_committee_assignments,
sum(total_ministry_assignments::int8)::int8 as total_ministry_assignments,
sum(total_speaker_assignments::int8)::int8 as total_speaker_assignments,
sum(current_assignments::int8)::int8 as current_assignments,
sum(current_party_assignments::int8)::int8 as current_party_assignments,
sum(current_committee_assignments::int8)::int8 as current_committee_assignments,
sum(current_ministry_assignments::int8)::int8 as current_ministry_assignments,
sum(current_speaker_assignments::int8)::int8 as current_speaker_assignments
from view_riksdagen_politician group by party</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-278">
<dropView viewName="view_riksdagen_party" />
<dropView viewName="view_riksdagen_party_summary" />
<dropView viewName="view_riksdagen_politician" />
<dropView viewName="view_riksdagen_party_member" /> <createView viewName="view_riksdagen_party_member">select * from sweden_political_party LEFT JOIN person_data ON person_data.party = sweden_political_party.short_code where person_data.party = sweden_political_party.short_code</createView>
<createView viewName="view_riksdagen_party">select distinct party_id as party_number,party_name,short_code as party_id,website,registered_date,count(party_id) as head_count from view_riksdagen_party_member group by party_id,party_name,short_code,website,registered_date</createView>
<createView viewName="view_riksdagen_politician">select
view_riksdagen_party_member.id as person_id,
max(first_name) as first_name,
max(last_name) as last_name,
max(party) as party,
max(born_year) as born_year,
max(gender) as gender,
min(assignment_data.from_date) as first_assignment_date,
max(assignment_data.to_date) as last_assignment_date,
sum(CASE WHEN assignment_data.status like 'Ledig%' THEN 0 else (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END) end) as total_days_served,
sum( CASE WHEN assignment_type='kammaruppdrag' and assignment_data.status not like 'Ledig%' THEN ((CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_parliament,
sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_committee,
sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_government,
sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_eu,
(CASE WHEN max(to_date) >= CURRENT_DATE THEN true ELSE false END) as active,
count(*) total_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments,
sum( CASE WHEN (assignment_type='talmansuppdrag') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_speaker,
CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (assignment_type='talmansuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_speaker,
sum( CASE WHEN (assignment_type='partiuppdrag') THEN ( (CASE WHEN to_date >= CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date >= CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_party,
CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (assignment_type='partiuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_party,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as current_committee_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as current_ministry_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as current_party_assignments,
sum((CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE and assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as current_speaker_assignments,
sum((CASE WHEN org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as total_committee_assignments,
sum((CASE WHEN assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as total_party_assignments,
sum((CASE WHEN assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as total_speaker_assignments,
sum((CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as total_ministry_assignments,
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND detail='Europaparlamentet' THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_eu,
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_government,(
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND (org_code is not null and assignment_type='uppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END)) as active_committee,
(CASE WHEN sum(CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status not like 'Ledig%' THEN 1 ELSE (CASE WHEN to_date >= CURRENT_DATE AND from_date <= CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status like 'Ledig%' THEN 0 ELSE 0 END) END) > 0 THEN true ELSE false END) as active_parliament
from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
<createView viewName="view_riksdagen_party_summary">select
party,
min(first_assignment_date) as first_assignment_date,
max(last_assignment_date) as last_assignment_date,
sum(total_days_served::int8)::int8 as total_days_served,
sum(total_days_served_parliament::int8)::int8 as total_days_served_parliament,
sum(total_days_served_committee::int8)::int8 as total_days_served_committee,
sum(total_days_served_government::int8)::int8 as total_days_served_government,
sum(total_days_served_eu::int8)::int8 as total_days_served_eu,
sum(total_days_served_speaker::int8)::int8 as total_days_served_speaker,
sum(total_days_served_party::int8)::int8 as total_days_served_party,
bool_or(active) as active,
bool_or(active_eu) as active_eu,
bool_or(active_government) as active_government,
bool_or(active_committee) as active_committee,
bool_or(active_parliament) as active_parliament,
bool_or(active_speaker) as active_speaker,
bool_or(active_party) as active_party,
sum(CASE WHEN active THEN 1 ELSE 0 END) as total_active,
sum(CASE WHEN active_eu THEN 1 ELSE 0 END) as total_active_eu,
sum(CASE WHEN active_government THEN 1 ELSE 0 END) as total_active_government,
sum(CASE WHEN active_committee THEN 1 ELSE 0 END) as total_active_committee,
sum(CASE WHEN active_parliament THEN 1 ELSE 0 END) as total_active_parliament,
sum(total_assignments::int8)::int8 as total_assignments,
sum(total_party_assignments::int8)::int8 as total_party_assignments,
sum(total_committee_assignments::int8)::int8 as total_committee_assignments,
sum(total_ministry_assignments::int8)::int8 as total_ministry_assignments,
sum(total_speaker_assignments::int8)::int8 as total_speaker_assignments,
sum(current_assignments::int8)::int8 as current_assignments,
sum(current_party_assignments::int8)::int8 as current_party_assignments,
sum(current_committee_assignments::int8)::int8 as current_committee_assignments,
sum(current_ministry_assignments::int8)::int8 as current_ministry_assignments,
sum(current_speaker_assignments::int8)::int8 as current_speaker_assignments
from view_riksdagen_politician group by party</createView>
</changeSet>
</databaseChangeLog>