def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('drafts',
sa.Column('user_id', sa.String(length=1000), nullable=True))
public_match_individual_name = PGFunction(
schema="public",
signature=
"match_individual_name(lastname IN VARCHAR, firstname IN VARCHAR)",
definition=
"RETURNS int[]\n LANGUAGE plpgsql\n AS\n $$\n DECLARE\n v_ids integer ARRAY;\n BEGIN\n SET pg_trgm.word_similarity_threshold = 0.4;\n \n SELECT array_agg(p.id)\n INTO v_ids\n FROM parties p\n WHERE p.registration_id_end IS NULL\n AND p.party_type = 'DI'\n AND lastname <% p.last_name_key\n AND ((firstname <% p.first_name_key AND word_similarity(firstname, p.first_name_key) >= .50) OR\n (firstname <% p.middle_initial AND word_similarity(firstname, p.middle_initial) >= .50) OR\n p.first_name_key IN (SELECT n.name \n FROM nicknames n \n WHERE n.name_id IN (SELECT n2.name_id\n FROM nicknames n2\n WHERE n2.name = firstname))); \n RETURN v_ids;\n END\n ; \n $$"
)
op.replace_entity(public_match_individual_name)
op.create_table(
'previous_financing_statements',
sa.Column('financing_id', sa.Integer(), nullable=False),
sa.Column('registration_type', sa.String(length=30), nullable=False),
sa.Column('cb_date', sa.DateTime(), nullable=True),
sa.Column('cb_number', sa.String(length=10), nullable=True),
sa.Column('cr_date', sa.DateTime(), nullable=True),
sa.Column('cr_number', sa.String(length=10), nullable=True),
sa.Column('mhr_date', sa.DateTime(), nullable=True),
sa.Column('mhr_number', sa.String(length=10), nullable=True),
sa.ForeignKeyConstraint(
['financing_id'],
['financing_statements.id'],
), sa.PrimaryKeyConstraint('financing_id'))
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
public_searchkey_business_name = PGFunction(
schema="public",
signature="searchkey_business_name(actual_name character varying)",
definition=
"returns character varying\n LANGUAGE plpgsql\nAS $function$\n DECLARE\n v_search_key VARCHAR(150);\n BEGIN\n v_search_key :=\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(\n REGEXP_REPLACE(actual_name,'^0000|^000|^00|^0|^THE | THE |\\([^()]*\\)',''),\n 'CORPORATION|CORP|COMPANY|CO\\.|LTD|INCORPORATED|INC$|INC.|INCORPOREE|LIMITED|LIMITEE|LTEE|LTD|ASSOCIATION$|ASSOC$|ASSN$|NON PERSONAL LIABILITY$|UNLIMITED LIABILITY COMPANY|N P L$|NPL$|PARTNERSHIP|SOCIETY$|SOC$',''),\n 'BRITISH COLUMBIA|BRITISHCOLUMBIA','BC'),\n '&','AND'),\n '#','NUMBER'),\n '1','ONE'),\n '2','TWO'),\n '3','THREE'),\n '4','FOUR'),\n '5','FIVE'),\n '6','SIX'),\n '7','SEVEN'),\n '8','EIGHT'),\n '9','NINE'),\n '0','ZERO'),\n 'TEN','ONEZERO'),\n 'TWENTY','TWOZERO'),\n 'THIRTY','THREEERO'),\n 'FORTY','FOURZERO'),\n 'FOURTY','FOURZERO'),\n 'FIFTY','FIVEZERO'),\n 'SIXTY','SIXZERO'),\n 'SEVENTY','SEVENZERO'),\n 'EIGHTY','EIGHTZERO'),\n 'NINETY','NINEZERO'),\n '[^0-9A-Za-z]','','gi');\n RETURN v_search_key;\n END\n; \n$function$"
)
op.replace_entity(public_searchkey_business_name)
public_match_individual_name = PGFunction(
schema="public",
signature=
"match_individual_name(lastname character varying, firstname character varying)",
definition=
"returns integer[]\n LANGUAGE plpgsql\nAS $function$\n DECLARE\n v_ids integer ARRAY;\n v_lastname_key VARCHAR(50);\n v_last1 VARCHAR(50);\n v_last2 VARCHAR(50);\n v_firstname_key VARCHAR(50);\n v_first1 VARCHAR(50);\n v_first2 VARCHAR(50);\n v_first3 VARCHAR(50);\n BEGIN\n v_lastname_key := searchkey_last_name(lastname);\n v_last1 = split_part(v_lastname_key, ' ', 1);\n v_last2 = split_part(v_lastname_key, ' ', 2); -- May be null\n v_firstname_key := searchkey_first_name(firstname);\n v_first1 = split_part(v_firstname_key, ' ', 1);\n v_first2 = split_part(v_firstname_key, ' ', 2); -- May be null\n v_first3 = split_part(v_firstname_key, ' ', 3); -- May be null\n\n IF (LENGTH(v_last2) < 1) THEN\n v_last2 := null;\n END IF;\n IF (LENGTH(v_first2) < 1) THEN\n v_first2 := null;\n END IF;\n IF (LENGTH(v_first3) < 1) THEN\n v_first3 := null;\n END IF;\n SET pg_trgm.similarity_threshold = 0.8;\n\n -- Replace where clause\\: Oracle uses UTL_MATCH.JARO_WINKLER_SIMILARITY\n SELECT array_agg(id)\n INTO v_ids\n FROM parties p\n WHERE registration_id_end IS NULL AND\n party_type = 'DI' AND\n (\n (p.last_name_key % v_lastname_key OR\n ((p.last_name_key1 = v_last1 OR (v_last2 IS NOT NULL AND v_last2 = p.last_name_key1)) OR\n (p.last_name_key2 IS NOT NULL AND (p.last_name_key2 = v_last1 OR (v_last2 IS NOT NULL AND v_last2 = p.last_name_key2)))\n )\n ) AND\n (\n p.first_name_key % v_firstname_key OR\n (p.first_name_key1 = v_first1 OR\n (v_first2 IS NOT NULL AND v_first2 = p.first_name_key1) OR\n (v_first3 IS NOT NULL AND v_first3 = p.first_name_key1)) OR\n (p.first_name_key2 IS NOT NULL AND \n (p.first_name_key2 = v_first1 OR \n (v_first2 IS NOT NULL AND v_first2 = p.first_name_key2) OR\n (v_first3 IS NOT NULL AND v_first3 = p.first_name_key2))) OR\n (p.first_name_key3 IS NOT NULL AND \n (p.first_name_key3 = v_first1 OR \n (v_first2 IS NOT NULL AND v_first2 = p.first_name_key3) OR\n (v_first3 IS NOT NULL AND v_first3 = p.first_name_key3))) OR\n (searchkey_nickname_match2(p.first_name_key1, p.first_name_key2, p.first_name_key3, v_first1, v_first2, v_first3) > 0)\n )\n );\n RETURN v_ids;\n END\n; \n$function$"
)
op.replace_entity(public_match_individual_name)
op.drop_index(op.f('ix_trust_indentures_registration_id_end'),
table_name='trust_indentures')
op.drop_index(op.f('ix_trust_indentures_registration_id'),
table_name='trust_indentures')
op.drop_index(op.f('ix_trust_indentures_financing_id'),
table_name='trust_indentures')
op.drop_index(op.f('ix_serial_collateral_registration_id_end'),
table_name='serial_collateral')
op.drop_index(op.f('ix_serial_collateral_registration_id'),
table_name='serial_collateral')
op.drop_index(op.f('ix_serial_collateral_mhr_number'),
table_name='serial_collateral')
op.drop_index(op.f('ix_serial_collateral_financing_id'),
table_name='serial_collateral')
op.drop_column('search_requests', 'user_id')
op.drop_index(op.f('ix_registrations_financing_id'),
table_name='registrations')
op.drop_index(op.f('ix_registrations_draft_id'),
table_name='registrations')
op.drop_index(op.f('ix_parties_registration_id_end'), table_name='parties')
op.drop_index(op.f('ix_parties_registration_id'), table_name='parties')
op.drop_index(op.f('ix_parties_middle_initial'), table_name='parties')
op.drop_index(op.f('ix_parties_financing_id'), table_name='parties')
op.drop_index(op.f('ix_parties_branch_id'), table_name='parties')
op.drop_index(op.f('ix_parties_address_id'), table_name='parties')
op.drop_column('parties', 'email_address')
op.drop_index(op.f('ix_general_collateral_registration_id_end'),
table_name='general_collateral')
op.drop_index(op.f('ix_general_collateral_registration_id'),
table_name='general_collateral')
op.drop_index(op.f('ix_general_collateral_financing_id'),
table_name='general_collateral')
op.drop_index(op.f('ix_court_orders_registration_id'),
table_name='court_orders')
op.drop_index(op.f('ix_client_codes_historical_users_id'),
table_name='client_codes_historical')
op.drop_index(op.f('ix_client_codes_historical_branch_id'),
table_name='client_codes_historical')
op.drop_index(op.f('ix_client_codes_historical_address_id'),
table_name='client_codes_historical')
op.drop_index(op.f('ix_client_codes_users_id'), table_name='client_codes')
op.drop_index(op.f('ix_client_codes_address_id'),
table_name='client_codes')
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('drafts', 'user_id')
public_match_individual_name = PGFunction(
schema="public",
signature=
"match_individual_name(lastname character varying, firstname character varying)",
definition=
"returns integer[]\n LANGUAGE plpgsql\nAS $function$\n DECLARE\n v_ids integer ARRAY;\n BEGIN\n -- SET pg_trgm.word_similarity_threshold = 0.8;\n -- SET pg_trgm.similarity_threshold = 0.8;\n \n SELECT array_agg(p.id)\n INTO v_ids\n FROM parties p\n WHERE p.registration_id_end IS NULL\n AND p.party_type = 'DI'\n AND lastname <% p.last_name_key\n AND word_similarity(lastname, p.last_name_key) >= .40\n AND ((firstname <% p.first_name_key AND word_similarity(firstname, p.first_name_key) >= .50) OR\n (firstname <% p.middle_initial AND word_similarity(firstname, p.middle_initial) >= .50) OR\n p.first_name_key IN (SELECT n.name \n FROM nicknames n \n WHERE n.name_id IN (SELECT n2.name_id\n FROM nicknames n2\n WHERE n2.name = firstname))); \n RETURN v_ids;\n END\n ; \n $function$"
)
op.replace_entity(public_match_individual_name)
op.drop_table('previous_financing_statements')
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
basic_poi_aoi_visualization = PGFunction(
schema="basic",
signature=
"poi_aoi_visualization(user_id_input integer, scenario_id_input integer, active_upload_ids integer[], active_study_area_id integer)",
definition=
"returns TABLE(id integer, uid text, category text, name text, opening_hours text, street text, housenumber text, zipcode text, edit_type text, geom geometry)\n LANGUAGE plpgsql\nAS $function$\nDECLARE \t\n\taoi_categories TEXT[]; \n\tpoi_categories jsonb = basic.poi_categories(user_id_input);\n\tdata_upload_poi_categories TEXT[] = '{}'::TEXT[];\n\tcombined_poi_categories text[];\n\texcluded_pois_id text[] := ARRAY[]::text[]; \n\tbuffer_geom_study_area geometry; \nBEGIN\n\tdata_upload_poi_categories = basic.poi_categories_data_uploads(user_id_input);\n\n\t/*Get combined poi categories*/\n\tSELECT ARRAY_AGG(c.category)\n\tINTO combined_poi_categories\n\tFROM \t\n\t(\n\t\tSELECT jsonb_array_elements_text(poi_categories -> 'true') category\n\t\tUNION ALL \n\t\tSELECT jsonb_array_elements_text(poi_categories -> 'false') category\n\t) c;\n\t\n\tRAISE NOTICE '%', excluded_pois_id; \n\tRAISE NOTICE '%', active_upload_ids ; \n\tRAISE NOTICE '%', combined_poi_categories ; \n\n\n\t/*Prepare AOI categories*/\n\tDROP TABLE IF EXISTS aoi_groups_default; \n\tCREATE TEMP TABLE aoi_groups_default AS \n\tWITH aoi_groups AS \n\t(\n\t\tSELECT jsonb_array_elements(basic.select_customization('aoi_groups')) aoi_group\n\t)\n\tSELECT jsonb_array_elements(p.aoi_group -> jsonb_object_keys(p.aoi_group) -> 'children') AS aoi_category \n\tFROM aoi_groups p;\n\n\tSELECT ARRAY_AGG(object_keys) AS aoi_category\n\tINTO aoi_categories\n\tFROM aoi_groups_default p, LATERAL jsonb_object_keys(p.aoi_category) object_keys; \n\t\n\t/*Check if POI scenario*/\n\tIF scenario_id_input <> 0 THEN \n\t\texcluded_pois_id = basic.modified_pois(scenario_id_input);\n\tEND IF; \n\t/*Buffer study area to avoid border effects*/\n\tbuffer_geom_study_area = (SELECT buffer_geom_heatmap AS geom FROM basic.study_area s WHERE s.id = active_study_area_id);\n\n RETURN query\n \tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, NULL AS edit_type, p.geom \n\tFROM basic.poi p\n\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\tAND p.geom && buffer_geom_study_area\n\tAND p.category NOT IN (SELECT UNNEST(data_upload_poi_categories));\n\t\n\tRETURN query \n\tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, NULL AS edit_type, p.geom \n\tFROM customer.poi_user p\n\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\tAND p.data_upload_id IN (SELECT UNNEST(active_upload_ids))\n\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\tAND p.geom && buffer_geom_study_area;\n\t\n\tRETURN query \n\t/*No scenarios nor aoi_user is implemented at the moment*/\n\tSELECT p.id, NULL, p.category, p.name, p.opening_hours, NULL AS street, NULL AS housenumber, NULL AS zipcode, NULL AS edit_type, p.geom\n\tFROM basic.aoi p \n\tWHERE p.category IN (SELECT UNNEST(aoi_categories))\n\tAND p.geom && buffer_geom_study_area; \n\t\n\tIF scenario_id_input <> 0 THEN \n\t \tRETURN query \n\t \tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, p.edit_type, p.geom \n\t\tFROM customer.poi_modified p\n\t\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\t\tAND p.geom && buffer_geom_study_area\n\t\tAND p.scenario_id = scenario_id_input; \n\t \t\n\t\tRETURN query\n\t \tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, 'd' AS edit_type, p.geom \n\t\tFROM basic.poi p\n\t\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\t\tAND p.uid IN (SELECT UNNEST(excluded_pois_id))\n\t\tAND p.geom && buffer_geom_study_area\n\t\tAND p.category NOT IN (SELECT UNNEST(data_upload_poi_categories));\n\t\n\t\tRETURN query \n\t\tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, 'd' AS edit_type, p.geom \n\t\tFROM customer.poi_user p\n\t\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\t\tAND p.data_upload_id IN (SELECT UNNEST(active_upload_ids))\n\t\tAND p.uid IN (SELECT UNNEST(excluded_pois_id))\n\t\tAND p.geom && buffer_geom_study_area;\n\tEND IF; \nEND ;\n$function$"
)
op.replace_entity(basic_poi_aoi_visualization)
basic_prepare_heatmap_local_accessibility = PGFunction(
schema="basic",
signature=
"prepare_heatmap_local_accessibility(amenities_json jsonb, user_id_input integer, active_study_area_id integer, modus_input text DEFAULT 'default'::text, scenario_id_input integer DEFAULT 0, data_upload_ids integer[] DEFAULT '{}'::integer[])",
definition=
"returns TABLE(grid_visualization_id bigint, accessibility_index bigint)\n LANGUAGE plpgsql\nAS $function$\nDECLARE\n\tarray_amenities text[];\n\tpois_one_entrance jsonb := basic.poi_categories(user_id_input) -> 'false';\n\tpois_more_entrances jsonb := basic.poi_categories(user_id_input) -> 'true';\n\tsensitivities integer[]; \n\ttranslation_sensitivities jsonb;\n\texcluded_poi_uids text[] := '{}'::TEXT[];\nBEGIN\n \t\n\tSELECT ARRAY_AGG(s.sensitivity::integer) \n\tINTO sensitivities \n\tFROM (\n\t\tSELECT jsonb_array_elements_text(basic.select_customization('heatmap_sensitivities')) sensitivity \n\t) s;\n\n\tSELECT jsonb_agg(a.category)\n\tINTO pois_one_entrance \n\tFROM jsonb_object_keys(amenities_json) AS a(category), jsonb_array_elements_text(pois_one_entrance) AS o(category) \n\tWHERE a.category = o.category; \n\t\n\tSELECT jsonb_agg(a.category)\n\tINTO pois_more_entrances \n\tFROM jsonb_object_keys(amenities_json) AS a(category), jsonb_array_elements_text(pois_more_entrances) AS o(category) \n\tWHERE a.category = o.category; \n\n\tSELECT jsonb_object_agg(k, (sensitivities # (v ->> 'sensitivity')::integer)::smallint)\n\tINTO translation_sensitivities\n\tFROM jsonb_each(amenities_json) AS u(k, v);\n\n\tIF modus_input <> 'default' AND scenario_id_input <> 0 THEN \n\t\texcluded_poi_uids = basic.modified_pois(scenario_id_input); \n\tEND IF; \n\n\tRETURN query\n\tSELECT u.grid_visualization_id, (u.accessibility_index * (amenities_json -> x.category ->> 'weight')::SMALLINT)::bigint AS accessibility_index \n\tFROM (\n\t\tSELECT grid_visualization_ids, accessibility_indices[(translation_sensitivities ->> p.category)::integer\\:(translation_sensitivities ->> p.category)::integer][1\\:], p.category\n\t\tFROM customer.reached_poi_heatmap r, \n\t\t(\n\t\t\tSELECT p.uid, p.category \n\t\t\tFROM basic.study_area s, basic.poi p \n\t\t\tWHERE ST_Intersects(p.geom, s.buffer_geom_heatmap) \n\t\t\tAND s.id = active_study_area_id\n\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_one_entrance))\n\t\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_poi_uids))\n\t\t) p \n\t\tWHERE p.uid = r.poi_uid \n\t\tAND r.scenario_id IS NULL \n\t)x, UNNEST(x.grid_visualization_ids, x.accessibility_indices) AS u(grid_visualization_id, accessibility_index);\n\t\n\tRETURN query \n\tSELECT u.grid_visualization_id, (max(u.accessibility_index) * (amenities_json -> x.category ->> 'weight')::SMALLINT)::bigint AS accessibility_index \n\tFROM (\n\t\tSELECT grid_visualization_ids, accessibility_indices[(translation_sensitivities ->> p.category)::integer\\:(translation_sensitivities ->> p.category)::integer][1\\:], \n\t\tp.category, p.name\n\t\tFROM customer.reached_poi_heatmap r, \n\t\t(\n\t\t\tSELECT p.uid, p.category, p.name \n\t\t\tFROM basic.study_area s, basic.poi p \n\t\t\tWHERE ST_Intersects(p.geom, s.buffer_geom_heatmap) \n\t\t\tAND s.id = active_study_area_id\n\t\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_poi_uids))\n\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_more_entrances))\n\t\t) p \n\t\tWHERE p.uid = r.poi_uid \n\t\tAND r.scenario_id IS NULL \n\t)x, UNNEST(x.grid_visualization_ids, x.accessibility_indices) AS u(grid_visualization_id, accessibility_index)\n\tGROUP BY u.grid_visualization_id, x.category, x.name; \n\t\n\tIF data_upload_ids <> '{}'::integer[] THEN \n\t\tRETURN query\n\t\tSELECT u.grid_visualization_id, (u.accessibility_index * (amenities_json -> x.category ->> 'weight')::SMALLINT)::bigint AS accessibility_index \n\t\tFROM (\n\t\t\tSELECT grid_visualization_ids, accessibility_indices[(translation_sensitivities ->> p.category)::integer\\:(translation_sensitivities ->> p.category)::integer][1\\:], p.category\n\t\t\tFROM customer.reached_poi_heatmap r, \n\t\t\t(\n\t\t\t\tSELECT p.uid, p.category \n\t\t\t\tFROM basic.study_area s, customer.poi_user p \n\t\t\t\tWHERE ST_Intersects(p.geom, s.buffer_geom_heatmap) \n\t\t\t\tAND s.id = active_study_area_id\n\t\t\t\tAND p.data_upload_id IN (SELECT UNNEST(data_upload_ids))\n\t\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_one_entrance))\n\t\t\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_poi_uids))\n\t\t\t) p \n\t\t\tWHERE p.uid = r.poi_uid \n\t\t\tAND r.scenario_id IS NULL \n\t\t)x, UNNEST(x.grid_visualization_ids, x.accessibility_indices) AS u(grid_visualization_id, accessibility_index);\n\t\n\t\tRETURN query \n\t\tSELECT u.grid_visualization_id, (max(u.accessibility_index) * (amenities_json -> x.category ->> 'weight')::SMALLINT)::bigint AS accessibility_index \n\t\tFROM (\n\t\t\tSELECT grid_visualization_ids, accessibility_indices[(translation_sensitivities ->> p.category)::integer\\:(translation_sensitivities ->> p.category)::integer][1\\:], \n\t\t\tp.category, p.name\n\t\t\tFROM customer.reached_poi_heatmap r, \n\t\t\t(\n\t\t\t\tSELECT p.uid, p.category, p.name \n\t\t\t\tFROM basic.study_area s, customer.poi_user p \n\t\t\t\tWHERE ST_Intersects(p.geom, s.buffer_geom_heatmap) \n\t\t\t\tAND s.id = active_study_area_id\n\t\t\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_poi_uids))\n\t\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_more_entrances))\n\t\t\t\tAND p.data_upload_id IN (SELECT UNNEST(data_upload_ids))\n\t\t\t) p \n\t\t\tWHERE p.uid = r.poi_uid \t\n\t\t\tAND r.scenario_id IS NULL \n\t\t)x, UNNEST(x.grid_visualization_ids, x.accessibility_indices) AS u(grid_visualization_id, accessibility_index)\n\t\tGROUP BY u.grid_visualization_id, x.category, x.name; \t\n\tEND IF;\n\t\n\tIF modus_input <> 'default' AND scenario_id_input <> 0 THEN \n\t\tRETURN query \n\t\tSELECT u.grid_visualization_id, (u.accessibility_index * (amenities_json -> x.category ->> 'weight')::SMALLINT)::bigint AS accessibility_index \n\t\tFROM (\n\t\t\tSELECT grid_visualization_ids, accessibility_indices[(translation_sensitivities ->> p.category)::integer\\:(translation_sensitivities ->> p.category)::integer][1\\:], p.category\n\t\t\tFROM customer.reached_poi_heatmap r, \n\t\t\t(\n\t\t\t\tSELECT p.uid, p.category, p.scenario_id \n\t\t\t\tFROM basic.study_area s, customer.poi_modified p \n\t\t\t\tWHERE ST_Intersects(p.geom, s.buffer_geom_heatmap) \n\t\t\t\tAND s.id = active_study_area_id\n\t\t\t\tAND p.edit_type <> 'd'\n\t\t\t\tAND p.scenario_id = scenario_id_input \n\t\t\t) p \n\t\t\tWHERE p.uid = r.poi_uid \n\t\t\tAND r.scenario_id = scenario_id_input \n\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_one_entrance))\n\t\t)x, UNNEST(x.grid_visualization_ids, x.accessibility_indices) AS u(grid_visualization_id, accessibility_index);\n\t\t\n\t\tRETURN query \n\t\tSELECT u.grid_visualization_id, (max(u.accessibility_index) * (amenities_json -> x.category ->> 'weight')::SMALLINT)::bigint AS accessibility_index \n\t\tFROM (\n\t\t\tSELECT grid_visualization_ids, accessibility_indices[(translation_sensitivities ->> p.category)::integer\\:(translation_sensitivities ->> p.category)::integer][1\\:], \n\t\t\tp.category, p.name\n\t\t\tFROM customer.reached_poi_heatmap r, \n\t\t\t(\n\t\t\t\tSELECT p.uid, p.category, p.name, p.scenario_id FROM basic.study_area s, customer.poi_modified p \n\t\t\t\tWHERE ST_Intersects(p.geom, s.buffer_geom_heatmap) \n\t\t\t\tAND s.id = active_study_area_id\n\t\t\t\tAND p.edit_type <> 'd'\n\t\t\t\tAND p.scenario_id = scenario_id_input \n\t\t\t) p \n\t\t\tWHERE p.uid = r.poi_uid \n\t\t\tAND r.scenario_id = scenario_id_input \n\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_more_entrances))\t\n\t\t)x, UNNEST(x.grid_visualization_ids, x.accessibility_indices) AS u(grid_visualization_id, accessibility_index)\n\t\tGROUP BY u.grid_visualization_id, x.category, x.name; \n\tEND IF; \n\t\nEND;\n$function$"
)
op.replace_entity(basic_prepare_heatmap_local_accessibility)
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
basic_poi_aoi_visualization = PGFunction(
schema="basic",
signature=
"poi_aoi_visualization(user_id_input integer, scenario_id_input integer, active_upload_ids integer[], active_study_area_id integer)",
definition=
"returns TABLE(id integer, uid text, category text, name text, opening_hours text, street text, housenumber text, zipcode text, edit_type text, geom geometry)\n LANGUAGE plpgsql\nAS $function$\nDECLARE \t\n\taoi_categories TEXT[]; \n\tpoi_categories jsonb = basic.poi_categories(user_id_input);\n\tdata_upload_poi_categories TEXT[] = basic.poi_categories_data_uploads(user_id_input);\n\tcombined_poi_categories text[];\n\texcluded_pois_id text[] := ARRAY[]::text[]; \n\tbuffer_geom_study_area geometry; \nBEGIN\n\t/*Get combined poi categories*/\n\tSELECT ARRAY_AGG(c.category)\n\tINTO combined_poi_categories\n\tFROM \t\n\t(\n\t\tSELECT jsonb_array_elements_text(poi_categories -> 'true') category\n\t\tUNION ALL \n\t\tSELECT jsonb_array_elements_text(poi_categories -> 'false') category\n\t) c;\n\n\t/*Prepare AOI categories*/\n\tDROP TABLE IF EXISTS aoi_groups_default; \n\tCREATE TEMP TABLE aoi_groups_default AS \n\tWITH aoi_groups AS \n\t(\n\t\tSELECT jsonb_array_elements(basic.select_customization('aoi_groups')) aoi_group\n\t)\n\tSELECT jsonb_array_elements(p.aoi_group -> jsonb_object_keys(p.aoi_group) -> 'children') AS aoi_category \n\tFROM aoi_groups p;\n\n\tSELECT ARRAY_AGG(object_keys) AS aoi_category\n\tINTO aoi_categories\n\tFROM aoi_groups_default p, LATERAL jsonb_object_keys(p.aoi_category) object_keys; \n\t\n\t/*Check if POI scenario*/\n\tIF scenario_id_input <> 0 THEN \n\t\texcluded_pois_id = basic.modified_pois(scenario_id_input);\n\tEND IF; \n\t/*Buffer study area to avoid border effects*/\n\tbuffer_geom_study_area = (SELECT buffer_geom_heatmap AS geom FROM basic.study_area s WHERE s.id = active_study_area_id);\n\n RETURN query\n \tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, NULL AS edit_type, p.geom \n\tFROM basic.poi p\n\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\tAND p.geom && buffer_geom_study_area\n\tAND p.category NOT IN (SELECT UNNEST(data_upload_poi_categories));\n\t\n\tRETURN query \n\tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, NULL AS edit_type, p.geom \n\tFROM customer.poi_user p\n\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\tAND p.data_upload_id IN (SELECT UNNEST(active_upload_ids))\n\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\tAND p.geom && buffer_geom_study_area;\n\t\n\tRETURN query \n\t/*No scenarios nor aoi_user is implemented at the moment*/\n\tSELECT p.id, NULL, p.category, p.name, p.opening_hours, NULL AS street, NULL AS housenumber, NULL AS zipcode, NULL AS edit_type, p.geom\n\tFROM basic.aoi p \n\tWHERE p.category IN (SELECT UNNEST(aoi_categories))\n\tAND p.geom && buffer_geom_study_area; \n\t\n\tIF scenario_id_input <> 0 THEN \n\t \tRETURN query \n\t \tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, p.edit_type, p.geom \n\t\tFROM customer.poi_modified p\n\t\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\t\tAND p.geom && buffer_geom_study_area\n\t\tAND p.scenario_id = scenario_id_input; \n\t \t\n\t\tRETURN query\n\t \tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, 'd' AS edit_type, p.geom \n\t\tFROM basic.poi p\n\t\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\t\tAND p.uid IN (SELECT UNNEST(excluded_pois_id))\n\t\tAND p.geom && buffer_geom_study_area\n\t\tAND p.category NOT IN (SELECT UNNEST(data_upload_poi_categories));\n\t\n\t\tRETURN query \n\t\tSELECT p.id, p.uid, p.category, p.name, p.opening_hours, p.street, p.housenumber, p.zipcode, 'd' AS edit_type, p.geom \n\t\tFROM customer.poi_user p\n\t\tWHERE p.category IN (SELECT UNNEST(combined_poi_categories))\n\t\tAND p.data_upload_id IN (SELECT UNNEST(active_upload_ids))\n\t\tAND p.uid IN (SELECT UNNEST(excluded_pois_id))\n\t\tAND p.geom && buffer_geom_study_area;\n\tEND IF; \nEND ;\n$function$"
)
op.replace_entity(basic_poi_aoi_visualization)
basic_poi_categories_data_uploads = PGFunction(
schema="basic",
signature="poi_categories_data_uploads(user_id_input integer)",
definition=
"returns text[]\n LANGUAGE plpgsql\nAS $function$\nDECLARE \n\tupload_ids integer[];\n\tarea_id integer;\n\tupload_id integer; \n\tpoi_user_category TEXT;\n\tcategories text[] := '{}'::TEXT[];\nBEGIN \n\t\n\t\n\tSELECT u.active_data_upload_ids, u.active_study_area_id \n\tINTO upload_ids, area_id \n\tFROM customer.USER u \n\tWHERE u.id = 4;\n\n\tFOREACH upload_id IN ARRAY upload_ids \n\tLOOP\n\t\tSELECT category \n\t\tINTO poi_user_category \n\t\tFROM customer.poi_user p, customer.data_upload d\n\t\tWHERE p.data_upload_id = upload_id \n\t\tAND p.data_upload_id = d.id \n\t\tAND d.study_area_id = area_id \n\t\tLIMIT 1; \n\t\t\n\t\tcategories = array_append(categories, poi_user_category );\n\t\t\t\n\tEND LOOP;\n\t\n\tRETURN categories; \n\nEND ;\n$function$"
)
op.replace_entity(basic_poi_categories_data_uploads)
op.drop_column('layer_library', 'min_resolution', schema='customer')
op.drop_column('layer_library', 'max_resolution', schema='customer')
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
public_searchkey_aircraft = PGFunction(
schema="public",
signature="searchkey_aircraft(aircraft_number character varying)",
definition=
"returns character varying\n LANGUAGE plpgsql\nAS $function$\n DECLARE\n v_search_key VARCHAR(25);\n BEGIN\n v_search_key := TRIM(REGEXP_REPLACE(aircraft_number,'\\s|-','','gi'));\n IF (LENGTH(v_search_key) > 6) THEN\n v_search_key := RIGHT(v_search_key, 6);\n END IF;\n RETURN v_search_key;\n END\n ; \n $function$"
)
op.replace_entity(public_searchkey_aircraft)
op.drop_column('search_results', 'account_name')
op.drop_column('search_results', 'doc_storage_url')
op.drop_column('search_results', 'callback_url')
op.drop_index(op.f('ix_event_tracking_key_id'),
table_name='event_tracking')
op.drop_index(op.f('ix_event_tracking_event_ts'),
table_name='event_tracking')
op.drop_index(op.f('ix_event_tracking_event_tracking_type'),
table_name='event_tracking')
op.drop_table('event_tracking')
op.drop_table('event_tracking_types')
# Added manually
op.execute(DropSequence(Sequence('event_tracking_id_seq')))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'search_results',
sa.Column('callback_url', sa.String(length=1000), nullable=True))
op.add_column(
'search_results',
sa.Column('doc_storage_url', sa.String(length=1000), nullable=True))
op.add_column(
'search_results',
sa.Column('account_name', sa.String(length=1000), nullable=True))
public_searchkey_aircraft = PGFunction(
schema="public",
signature="searchkey_aircraft(aircraft_number IN VARCHAR)",
definition=
"RETURNS VARCHAR\n LANGUAGE plpgsql\n AS\n $$\n DECLARE\n v_search_key VARCHAR(25);\n BEGIN\n v_search_key := TRIM(right(REGEXP_REPLACE(aircraft_number,'[^\\w]+','','gi'),6));\n RETURN v_search_key;\n END\n ; \n $$"
)
op.replace_entity(public_searchkey_aircraft)
# Added manually
op.execute(
CreateSequence(Sequence('event_tracking_id_seq', start=1,
increment=1)))
event_tracking_types = op.create_table(
'event_tracking_types',
sa.Column('event_tracking_type', sa.String(length=20), nullable=False),
sa.Column('event_tracking_desc', sa.String(length=100),
nullable=False),
sa.PrimaryKeyConstraint('event_tracking_type'))
op.create_table(
'event_tracking', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('key_id', sa.Integer(), nullable=False),
sa.Column('event_ts', sa.DateTime(), nullable=False),
sa.Column('event_tracking_type', sa.String(length=20), nullable=False),
sa.Column('status', sa.Integer(), nullable=True),
sa.Column('message', sa.String(length=2000), nullable=True),
sa.Column('email_address', sa.String(length=250), nullable=True),
sa.ForeignKeyConstraint(
['event_tracking_type'],
['event_tracking_types.event_tracking_type'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_event_tracking_event_tracking_type'),
'event_tracking', ['event_tracking_type'],
unique=False)
op.create_index(op.f('ix_event_tracking_event_ts'),
'event_tracking', ['event_ts'],
unique=False)
op.create_index(op.f('ix_event_tracking_key_id'),
'event_tracking', ['key_id'],
unique=False)
# Added manually
op.bulk_insert(
event_tracking_types,
[{
'event_tracking_type':
'SEARCH_REPORT',
'event_tracking_desc':
'Search Detail large report generation and storage.'
}, {
'event_tracking_type':
'API_NOTIFICATION',
'event_tracking_desc':
'Notification by API callback of successful event outcome.'
}, {
'event_tracking_type': 'EMAIL',
'event_tracking_desc': 'Email notification.'
}, {
'event_tracking_type':
'SURFACE_MAIL',
'event_tracking_desc':
'Surface mail delivery of report to service provider.'
}, {
'event_tracking_type': 'EMAIL_REPORT',
'event_tracking_desc': 'Email delivery of report.'
}])
def upgrade():
op.replace_entity(public_project_site)
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
public_distinct_intersection_existing_network_postgres_trigger = PGGrantTable(
schema='public',
table='distinct_intersection_existing_network',
columns=[],
role='postgres',
grant='TRIGGER',
with_grant_option=True)
op.create_entity(
public_distinct_intersection_existing_network_postgres_trigger)
public_distinct_intersection_existing_network_postgres_truncate = PGGrantTable(
schema='public',
table='distinct_intersection_existing_network',
columns=[],
role='postgres',
grant='TRUNCATE',
with_grant_option=True)
op.create_entity(
public_distinct_intersection_existing_network_postgres_truncate)
public_distinct_intersection_existing_network_postgres_delete = PGGrantTable(
schema='public',
table='distinct_intersection_existing_network',
columns=[],
role='postgres',
grant='DELETE',
with_grant_option=True)
op.create_entity(
public_distinct_intersection_existing_network_postgres_delete)
public_buf_postgres_trigger = PGGrantTable(schema='public',
table='buf',
columns=[],
role='postgres',
grant='TRIGGER',
with_grant_option=True)
op.create_entity(public_buf_postgres_trigger)
public_buf_postgres_truncate = PGGrantTable(schema='public',
table='buf',
columns=[],
role='postgres',
grant='TRUNCATE',
with_grant_option=True)
op.create_entity(public_buf_postgres_truncate)
public_buf_postgres_delete = PGGrantTable(schema='public',
table='buf',
columns=[],
role='postgres',
grant='DELETE',
with_grant_option=True)
op.create_entity(public_buf_postgres_delete)
public_test_postgres_trigger = PGGrantTable(schema='public',
table='test',
columns=[],
role='postgres',
grant='TRIGGER',
with_grant_option=True)
op.create_entity(public_test_postgres_trigger)
public_test_postgres_truncate = PGGrantTable(schema='public',
table='test',
columns=[],
role='postgres',
grant='TRUNCATE',
with_grant_option=True)
op.create_entity(public_test_postgres_truncate)
public_test_postgres_delete = PGGrantTable(schema='public',
table='test',
columns=[],
role='postgres',
grant='DELETE',
with_grant_option=True)
op.create_entity(public_test_postgres_delete)
public_test_postgres_update = PGGrantTable(
schema='public',
table='test',
columns=[
'coordinates_3857', 'cost', 'death_end', 'geom', 'id',
'length_3857', 'reverse_cost', 'source', 'starting_geoms',
'starting_ids', 'target'
],
role='postgres',
grant='UPDATE',
with_grant_option=True)
op.create_entity(public_test_postgres_update)
public_test_postgres_select = PGGrantTable(
schema='public',
table='test',
columns=[
'coordinates_3857', 'cost', 'death_end', 'geom', 'id',
'length_3857', 'reverse_cost', 'source', 'starting_geoms',
'starting_ids', 'target'
],
role='postgres',
grant='SELECT',
with_grant_option=True)
op.create_entity(public_test_postgres_select)
public_test_postgres_references = PGGrantTable(
schema='public',
table='test',
columns=[
'coordinates_3857', 'cost', 'death_end', 'geom', 'id',
'length_3857', 'reverse_cost', 'source', 'starting_geoms',
'starting_ids', 'target'
],
role='postgres',
grant='REFERENCES',
with_grant_option=True)
op.create_entity(public_test_postgres_references)
public_test_postgres_insert = PGGrantTable(
schema='public',
table='test',
columns=[
'coordinates_3857', 'cost', 'death_end', 'geom', 'id',
'length_3857', 'reverse_cost', 'source', 'starting_geoms',
'starting_ids', 'target'
],
role='postgres',
grant='INSERT',
with_grant_option=True)
op.create_entity(public_test_postgres_insert)
public_distinct_intersection_existing_network_postgres_update = PGGrantTable(
schema='public',
table='distinct_intersection_existing_network',
columns=['geom', 'id'],
role='postgres',
grant='UPDATE',
with_grant_option=True)
op.create_entity(
public_distinct_intersection_existing_network_postgres_update)
public_distinct_intersection_existing_network_postgres_select = PGGrantTable(
schema='public',
table='distinct_intersection_existing_network',
columns=['geom', 'id'],
role='postgres',
grant='SELECT',
with_grant_option=True)
op.create_entity(
public_distinct_intersection_existing_network_postgres_select)
public_distinct_intersection_existing_network_postgres_references = PGGrantTable(
schema='public',
table='distinct_intersection_existing_network',
columns=['geom', 'id'],
role='postgres',
grant='REFERENCES',
with_grant_option=True)
op.create_entity(
public_distinct_intersection_existing_network_postgres_references)
public_distinct_intersection_existing_network_postgres_insert = PGGrantTable(
schema='public',
table='distinct_intersection_existing_network',
columns=['geom', 'id'],
role='postgres',
grant='INSERT',
with_grant_option=True)
op.create_entity(
public_distinct_intersection_existing_network_postgres_insert)
public_buf_postgres_update = PGGrantTable(schema='public',
table='buf',
columns=['geom'],
role='postgres',
grant='UPDATE',
with_grant_option=True)
op.create_entity(public_buf_postgres_update)
public_buf_postgres_select = PGGrantTable(schema='public',
table='buf',
columns=['geom'],
role='postgres',
grant='SELECT',
with_grant_option=True)
op.create_entity(public_buf_postgres_select)
public_buf_postgres_references = PGGrantTable(schema='public',
table='buf',
columns=['geom'],
role='postgres',
grant='REFERENCES',
with_grant_option=True)
op.create_entity(public_buf_postgres_references)
public_buf_postgres_insert = PGGrantTable(schema='public',
table='buf',
columns=['geom'],
role='postgres',
grant='INSERT',
with_grant_option=True)
op.create_entity(public_buf_postgres_insert)
basic_thematic_data_sum = PGFunction(
schema="basic",
signature=
"thematic_data_sum(user_id_input integer, input_isochrone_calculation_id integer, modus text, scenario_id_input integer DEFAULT 0, active_upload_ids integer[] DEFAULT '{}'::integer[])",
definition=
"returns TABLE(isochrone_feature_id integer, isochrone_feature_step integer, isochrone_feature_reached_opportunities jsonb)\n LANGUAGE plpgsql\nAS $function$\nDECLARE \n\tpoi_categories jsonb = basic.poi_categories(user_id_input);\n\tpois_one_entrance jsonb = poi_categories -> 'false'; \n\tpois_more_entrance jsonb = poi_categories -> 'true';\n\texcluded_pois_id text[] := ARRAY[]::text[]; \n\texcluded_buildings_id integer[] := ARRAY[]::integer[];\nBEGIN \t\t\n\n\tIF modus = 'scenario' THEN\n\t\texcluded_pois_id = basic.modified_pois(scenario_id_input);\n\t\texcluded_buildings_id = basic.modified_buildings(scenario_id_input);\n\tEND IF;\n\n --Calculate reached AOIs\n\tDROP TABLE IF EXISTS reached_aois; \n\tCREATE TEMP TABLE reached_aois AS \n\tWITH area_cnt AS \n\t(\n\t\tSELECT i.id, a.category, count(*) as cnt, intersec.area \n\t\tFROM customer.isochrone_feature i, basic.aoi a, \n\t\tLATERAL (SELECT ST_Area(st_intersection(i.geom,a.geom)::geography)::integer area) AS intersec \n\t\tWHERE isochrone_calculation_id = input_isochrone_calculation_id\n\t\tAND st_intersects(i.geom,a.geom)\n\t\tGROUP BY i.id, category, name, intersec.area\n\t),\n\tjson_area_cnt AS\n\t(\n\t\tSELECT p.id, p.category, jsonb_build_object('cnt',sum(cnt),'area',sum(area)) AS aois_json\n\t\tFROM area_cnt p \n\t\tGROUP BY p.id, p.category\n\t)\n\tSELECT j.id, jsonb_object_agg(category, aois_json) aois_json_agg\n\tFROM json_area_cnt j\n\tGROUP BY j.id; \n\t\n\n\tDROP TABLE IF EXISTS reached_opportunities; \n\tCREATE TEMP TABLE reached_opportunities \n\t(\n\t\tid integer,\n\t\topportunity_type TEXT, \n\t\tcnt integer\n\t);\n\n\t--Calculate reached population \n\tINSERT INTO reached_opportunities\n\tWITH temp_sum AS \n\t(\n\t\tSELECT s.population,i.id \n \tFROM customer.isochrone_feature i\n \tCROSS JOIN LATERAL \n \t(\n\t \tSELECT sum(p.population) AS population\n\t \tFROM basic.population p \n\t \tWHERE st_intersects(i.geom,p.geom)\n\t \tAND p.building_id NOT IN (SELECT UNNEST(excluded_buildings_id))\t\n \t) s\n \tWHERE i.isochrone_calculation_id = input_isochrone_calculation_id\t\n \tUNION ALL \n \tSELECT s.population,i.id \n \tFROM customer.isochrone_feature i\n \tCROSS JOIN LATERAL \n \t(\n\t \tSELECT sum(p.population) AS population\n\t \tFROM customer.population_modified p \n\t \tWHERE st_intersects(i.geom,p.geom)\n\t \tAND p.scenario_id = scenario_id_input \n \t) s\n \tWHERE i.isochrone_calculation_id = input_isochrone_calculation_id\n\t)\n\tSELECT s.id, 'sum_pop', sum(s.population)::integer+(5-(sum(s.population)::integer % 5)) as sum_pop \n\tFROM temp_sum s \t \n\tGROUP BY s.id; \n\n\t--Calculate reached POIs one entrance \n\tINSERT INTO reached_opportunities\n\tSELECT i.id, s.category, count(*)\n \tFROM customer.isochrone_feature i\n \tCROSS JOIN LATERAL \n\t(\n\t\tSELECT p.category, i.id\n\t\tFROM basic.poi p\n\t\tWHERE ST_Intersects(i.geom, p.geom)\n\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_one_entrance))\n\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\t\tUNION ALL \n\t\tSELECT p.category, i.id\n\t\tFROM customer.poi_user p\n\t\tWHERE ST_Intersects(i.geom, p.geom)\n\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_one_entrance))\n\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\t\tAND p.data_upload_id IN (SELECT UNNEST(active_upload_ids))\n\t\tUNION ALL \n\t\tSELECT p.category, i.id \n\t\tFROM customer.poi_modified p\n\t\tWHERE ST_Intersects(i.geom, p.geom)\n\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_one_entrance))\n\t\tAND p.scenario_id = scenario_id_input \n\t) s\n\tWHERE i.isochrone_calculation_id = input_isochrone_calculation_id\t\n\tGROUP BY category, i.id;\n\n\t--Calculate reached POIs more entrances \n\tINSERT INTO reached_opportunities\n\tWITH more_entrances AS \n\t(\n\t\tSELECT s.category, i.id\n\t \tFROM customer.isochrone_feature i\n\t \tCROSS JOIN LATERAL \n\t\t(\n\t\t\tSELECT p.category, p.name, i.id\n\t\t\tFROM basic.poi p\n\t\t\tWHERE ST_Intersects(i.geom, p.geom)\n\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_more_entrance))\n\t\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\t\t\tUNION ALL \n\t\t\tSELECT p.category, p.name, i.id\n\t\t\tFROM customer.poi_user p\n\t\t\tWHERE ST_Intersects(i.geom, p.geom)\n\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_more_entrance))\n\t\t\tAND p.uid NOT IN (SELECT UNNEST(excluded_pois_id))\n\t\t\tAND p.data_upload_id IN (SELECT UNNEST(active_upload_ids)) \n\t\t\tUNION ALL \n\t\t\tSELECT p.category, p.name, i.id \n\t\t\tFROM customer.poi_modified p\n\t\t\tWHERE ST_Intersects(i.geom, p.geom)\n\t\t\tAND p.category IN (SELECT jsonb_array_elements_text(pois_more_entrance))\n\t\t\tAND p.scenario_id = scenario_id_input \n\t\t) s\n\t\tWHERE i.isochrone_calculation_id = input_isochrone_calculation_id\n\t\tGROUP BY name, category, i.id\n\t)\n\tSELECT m.id, m.category, count(*) \n\tFROM more_entrances m \n\tGROUP BY m.category, m.id;\n\t\n\tRAISE NOTICE '%', active_upload_ids; \n\tRETURN QUERY \n\tWITH group_reached_opportunities AS \n\t(\n\t\tSELECT r.id, jsonb_object_agg(opportunity_type, cnt) reached_opportunities \n\t\tFROM reached_opportunities r \n\t\tGROUP BY id \n\t),\n\tcombined_opportunities AS \n\t(\n\t\tSELECT COALESCE(g.id, r.id) AS id, COALESCE(reached_opportunities, '{}'::jsonb) || COALESCE(aois_json_agg, '{}'::jsonb) AS reached_opportunities \n\t\tFROM group_reached_opportunities g\n\t\tFULL JOIN reached_aois r \n\t\tON r.id = g.id \n\t)\n\tUPDATE customer.isochrone_feature i \n\tSET reached_opportunities = c.reached_opportunities \n\tFROM combined_opportunities c \n\tWHERE i.id = c.id\n\tRETURNING i.id, i.step, i.reached_opportunities; \nEND ;\n$function$"
)
op.replace_entity(basic_thematic_data_sum)
op.drop_constraint(None, 'scenario', schema='customer', type_='foreignkey')
op.drop_column('scenario', 'study_area_id', schema='customer')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_index(op.f('ix_client_codes_address_id'),
'client_codes', ['address_id'],
unique=False)
op.create_index(op.f('ix_client_codes_users_id'),
'client_codes', ['users_id'],
unique=False)
op.create_index(op.f('ix_client_codes_historical_address_id'),
'client_codes_historical', ['address_id'],
unique=False)
op.create_index(op.f('ix_client_codes_historical_branch_id'),
'client_codes_historical', ['branch_id'],
unique=False)
op.create_index(op.f('ix_client_codes_historical_users_id'),
'client_codes_historical', ['users_id'],
unique=False)
op.create_index(op.f('ix_court_orders_registration_id'),
'court_orders', ['registration_id'],
unique=False)
op.create_index(op.f('ix_general_collateral_financing_id'),
'general_collateral', ['financing_id'],
unique=False)
op.create_index(op.f('ix_general_collateral_registration_id'),
'general_collateral', ['registration_id'],
unique=False)
op.create_index(op.f('ix_general_collateral_registration_id_end'),
'general_collateral', ['registration_id_end'],
unique=False)
op.add_column(
'parties',
sa.Column('email_address', sa.String(length=250), nullable=True))
op.create_index(op.f('ix_parties_address_id'),
'parties', ['address_id'],
unique=False)
op.create_index(op.f('ix_parties_branch_id'),
'parties', ['branch_id'],
unique=False)
op.create_index(op.f('ix_parties_financing_id'),
'parties', ['financing_id'],
unique=False)
op.create_index(op.f('ix_parties_middle_initial'),
'parties', ['middle_initial'],
unique=False)
op.create_index(op.f('ix_parties_registration_id'),
'parties', ['registration_id'],
unique=False)
op.create_index(op.f('ix_parties_registration_id_end'),
'parties', ['registration_id_end'],
unique=False)
op.create_index(op.f('ix_registrations_draft_id'),
'registrations', ['draft_id'],
unique=False)
op.create_index(op.f('ix_registrations_financing_id'),
'registrations', ['financing_id'],
unique=False)
op.add_column('search_requests',
sa.Column('user_id', sa.String(length=1000), nullable=True))
op.create_index(op.f('ix_serial_collateral_financing_id'),
'serial_collateral', ['financing_id'],
unique=False)
op.create_index(op.f('ix_serial_collateral_mhr_number'),
'serial_collateral', ['mhr_number'],
unique=False)
op.create_index(op.f('ix_serial_collateral_registration_id'),
'serial_collateral', ['registration_id'],
unique=False)
op.create_index(op.f('ix_serial_collateral_registration_id_end'),
'serial_collateral', ['registration_id_end'],
unique=False)
op.create_index(op.f('ix_trust_indentures_financing_id'),
'trust_indentures', ['financing_id'],
unique=False)
op.create_index(op.f('ix_trust_indentures_registration_id'),
'trust_indentures', ['registration_id'],
unique=False)
op.create_index(op.f('ix_trust_indentures_registration_id_end'),
'trust_indentures', ['registration_id_end'],
unique=False)
public_match_individual_name = PGFunction(
schema="public",
signature=
"match_individual_name(lastname IN VARCHAR, firstname IN VARCHAR)",
definition=
"RETURNS int[]\n LANGUAGE plpgsql\n AS\n $$\n DECLARE\n v_ids integer ARRAY;\n BEGIN\n -- SET pg_trgm.word_similarity_threshold = 0.8;\n -- SET pg_trgm.similarity_threshold = 0.8;\n \n SELECT array_agg(p.id)\n INTO v_ids\n FROM parties p\n WHERE p.registration_id_end IS NULL\n AND p.party_type = 'DI'\n AND lastname <% p.last_name_key\n AND word_similarity(lastname, p.last_name_key) >= .40\n AND ((firstname <% p.first_name_key AND word_similarity(firstname, p.first_name_key) >= .50) OR\n (firstname <% p.middle_initial AND word_similarity(firstname, p.middle_initial) >= .50) OR\n p.first_name_key IN (SELECT n.name \n FROM nicknames n \n WHERE n.name_id IN (SELECT n2.name_id\n FROM nicknames n2\n WHERE n2.name = firstname))); \n RETURN v_ids;\n END\n ; \n $$"
)
op.replace_entity(public_match_individual_name)
public_searchkey_business_name = PGFunction(
schema="public",
signature="searchkey_business_name(actual_name IN VARCHAR)",
definition=
"RETURNS VARCHAR\n LANGUAGE plpgsql\n AS\n $$\n DECLARE\n v_search_key VARCHAR(150);\n BEGIN\n v_search_key :=\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(\n regexp_replace(actual_name, '[[\\:<\\:]](BC|B C |B.C)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](BRITISH COLUMBIA|BRITISHCOLUMBIA)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](INCORPORATED|INCORPOREE)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](CORPORATION)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](ULC|HOLDINGS|HOLDING|ASSOCIATION|ASSOCIATES| ASSOC| ASSN|NON PERSONAL LIABILITY|UNLIMITED LIABILITY COMPANY|N P L|NPL|PARTNERSHIP|SOCIETY)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](^THE|AND)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](^DR)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](CONSTRUCTION|CONTRACTING|CONTRACTOR)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](CONSULTANTS|CONSULTANT|CONSULTING)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](SERVICES|SERVICE)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](TRUST)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](CORP|COMPANY|CO\\|LTD|LIMITED|LIMITEE|LTEE)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](LTD|LIMITED|LIMITEE|LTEE)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[[\\:<\\:]](CO|INC)[[\\:>\\:]]', ' ', 'gi'\n ),\n '[^a-zA-Z0-9]+', '', 'gi'\n ),\n '( ){2,}', ''); \n RETURN TRIM(v_search_key);\n END\n ; \n $$"
)
op.replace_entity(public_searchkey_business_name)