Write function to recreate all database functions and triggers

[EPajares]

Goal of this issue

We were considering using alembic for managing the functions and triggers. Though this led to a couple of problems when maintaining and developing the functions. It would be better to move back to the original approach in which we dropped all functions and recreated them from scratch. This might be even easier now as we are having all functions in the schema public.

Resources

The functions are here: https://github.com/goat-community/goat/tree/main/app/api/src/db/sql I think the following procedure could work:

1. Drop all functions in schema basic.
2. We would have to read through the folder and create the functions one by one using the db-connections. There is one thing to be considered. Some functions are relying on each other. Meaning that cannot be created if the dependency functions do not exist yet.

I would also say that the new function recreating the PostgreSQL functions should go here: https://github.com/goat-community/goat/blob/main/app/api/src/db/sql/init_sql.py

This is the function that was previously called by alembic.

Deliverables

A python function that recreates all postgresql-functions and triggers.

[metemaddar]

@EPajares Should it patch to dev branch?

[EPajares]

Yes please to dev

[metemaddar]

We have some function files with same name as db functions, Also have some files witch their function is not in db and some new functions needed to add. We need to classify the new functions. And decide about removed functions.

helper/

• active_data_uploads_study_area.sql
• active_opportunities.sql
• active_opportunities_json.sql
• create_intersection_line.sql
• create_perpendicular_line.sql
• create_snapped_split_line.sql
• extend_line.sql
• modified_buildings.sql
• modified_edges.sql
• modified_pois.sql
• poi_categories.sql
• poi_categories_data_uploads.sql
• select_customization.sql
• select_user_customization.sql

  indicators/

• count_pois_multi_isochrones.sql
• count_publi_transport_station_frequency.sql
• get_poi_more_entrance.sql
• get_poi_one_entrance_sum.sql
• get_population_sum.sql
• heatmap_accessibility_population.sql
• heatmap_local_accessibility.sql
• heatmap_population.sql
• heatmap_prepare_artificial.sql
• poi_aoi_visualization.sql
• prepare_heatmap_local_accessibility.sql
• prepare_heatmap_population.sql
• reached_pois_heatmap.sql
• reached_population_polygon.sql
• reached_population_study_area.sql
• starting_points_multi_isochrones.sql
• thematic_data_sum.sql

  network/

• create_artificial_edges.sql
• create_multiple_artificial_edges.sql
• fetch_network_routing.sql
• fetch_network_routing_heatmap.sql
• fetch_network_routing_multi.sql
• fix_multiple_artificial_edges.sql
• query_edges_routing.sql

  scenario/

• network_modification.sql
• population_modification.sql
• split_by_drawn_line.sql

new functions (not in files but in db):

We should classify these functions.

• trigger_update_way_modified
• trigger_update_population_modified
• trigger_update_poi_modified
• trigger_insert_way_modified
• trigger_insert_population_modified
• trigger_insert_poi_modified
• starting_points_multi_isochrones
• split_by_drawn_lines
• get_poi_more_entrance_sum
• fetch_network_routing2
• count_public_transport_services_station
• coordinate_to_pixel

not in db but in files:

• count_publi_transport_station_frequency.sql
• get_poi_more_entrance.sql
• split_by_drawn_line.sql

Handling dependencies

For handling dependencies, I think we can add priorities to the file names. For example 0010 to 0500 for the file names. So if we had new functions with higher priority we can add with changing the lower value part (left side) and if we had new function with lower priority we can add to the end by adding to the right side (for example 0211).

Then we need to change the rewrite function to add according to priority. At the moment we have 50 functions.

Wipe db functions

We need to add a function to remove all db functions

[metemaddar]

We also can add the functions in a python list to set the priority. Then the function can find the file and apply to database.

[metemaddar]

A more simple way is to use try/except to add functions. Loop over them till they completely get empty. :x: But this can add much errors to database log. Also it can run into infinite loop when a dependency is not in the files.

[EPajares]

@metemaddar This is an excellent overview. Some of the missing functions are triggers. You can find them here: https://github.com/goat-community/goat/tree/dev/app/api/src/db/sql/triggers

I forgot to mention that also these we have to recreate. Concerning the other ones that are in DB but not in the codebase, I will review each of them. Though most of them we don't need anymore. They are mostly some testing functions that we forgot to delete.

[metemaddar]

@EPajares Let me recreate the list. I will edit the previous comment.

handling priorites

We also can write a function to read the file first and check if one of other unapplied functions is in the file. This can let us get rid of handling priorities with list or numbers.

[metemaddar]

@EPajares By including the triggers/ directory it had significant change, so added the report bellow:

helper/

• active_data_uploads_study_area.sql
• active_opportunities.sql
• active_opportunities_json.sql
• create_intersection_line.sql
• create_perpendicular_line.sql
• create_snapped_split_line.sql
• extend_line.sql
• modified_buildings.sql
• modified_edges.sql
• modified_pois.sql
• poi_categories.sql
• poi_categories_data_uploads.sql
• select_customization.sql
• select_user_customization.sql

  indicators/

• count_pois_multi_isochrones.sql
• count_publi_transport_station_frequency.sql
• get_poi_more_entrance.sql
• get_poi_one_entrance_sum.sql
• get_population_sum.sql
• heatmap_accessibility_population.sql
• heatmap_local_accessibility.sql
• heatmap_population.sql
• heatmap_prepare_artificial.sql
• poi_aoi_visualization.sql
• prepare_heatmap_local_accessibility.sql
• prepare_heatmap_population.sql
• reached_pois_heatmap.sql
• reached_population_polygon.sql
• reached_population_study_area.sql
• starting_points_multi_isochrones.sql
• thematic_data_sum.sql

  network/

• create_artificial_edges.sql
• create_multiple_artificial_edges.sql
• fetch_network_routing.sql
• fetch_network_routing_heatmap.sql
• fetch_network_routing_multi.sql
• fix_multiple_artificial_edges.sql
• query_edges_routing.sql

scenario/

• network_modification.sql
• population_modification.sql
• split_by_drawn_line.sql

triggers/

• trigger_heatmap_insert.sql
• trigger_network_modification_insert.sql
• trigger_heatmap_update.sql
• trigger_network_modification_update.sql

new functions (needs classification):

• [x] trigger_update_way_modified (file had the wrong name)
• [x] trigger_update_population_modified (this one is not needed anymore as it is handled in the API now)
• [x] trigger_update_poi_modified (file had the wrong name)
• [x] trigger_insert_way_modified (file had the wrong name)
• [x] trigger_insert_population_modified (this one is not needed anymore as it is handled in the API now)
• [x] trigger_insert_poi_modified (file had the wrong name)
• [x] fetch_network_routing2 (dump)
• [x] coordinate_to_pixel (add to helper)

not in db but in files:

• trigger_heatmap_insert.sql
• trigger_network_modification_insert.sql
• trigger_heatmap_update.sql
• trigger_network_modification_update.sql

[metemaddar]

We also can write a function to read the file first and check if one of other unapplied functions is in the file. This can let us get rid of handling priorities with list or numbers.

With this it ran into a circular dependency because of name similarity:

heatmap_accessibility_population : {'heatmap_local_accessibility', 'heatmap_population'}
heatmap_local_accessibility : {'prepare_heatmap_local_accessibility'}
prepare_heatmap_local_accessibility : {'heatmap_local_accessibility', 'modified_pois', 'poi_categories', 'select_customization', 'prepare_heatmap_local_accessibility', 'poi_aoi_visualization'}

[metemaddar]

I've updated the dependency checker. And the dependencies are more clear now.

prepare_heatmap_population : {'heatmap_population'}
heatmap_population : {'prepare_heatmap_population'}

heatmap_local_accessibility : {'prepare_heatmap_local_accessibility'}
prepare_heatmap_local_accessibility : {'heatmap_local_accessibility'}

:heavy_check_mark: Fixed by prepending a dot to the checking function name, as every function is coming from a specific schema. eg: basic.heatmap_local_accessibility() which has a dot between basic and function name.

[EPajares]

Thanks this is great!

[metemaddar]

After adding report to init_sql, some new differences was found:

# in db but not in files:
-  count_public_transport_services_station

## in files but not in db:
-  count_public_transport_station_frequency.sql

:heavy_check_mark: fixed by rename the file name.