move MDS table schema

[hunterowens]

current the schema for all the MDS is the default public schema. Let's switch this to mds or transportation

the existing mds schema has some loaded data from an attempt to get mds-provider-services package from the City of SM working that is not ideal for our usage.

[ian-r-rose]

Some documentation: https://www.postgresql.org/docs/current/sql-altertable.html

Action items:

1. Remove old/bad tables from the current mds schema.
2. Move trips and status_changes to mds.

Question: can mds-provider be configured to insert into a schema?

[RMK0110]

The ALTER TABLE...SET SCHEMA command will move tables, data, index, contraints and sequences to the 'mds' schema, but not the trigger functions: route_process( ) and status_change_processor ( ). The triggers need to be added using ALTER FUNCTION....SET SCHEMA.

[hunterowens]

The script for the triggers is here if you need to recreate them.

For change management, we will need to modify dependent users of the tables. Which I believe are the scooter-stat DAG, some of the neighborhood-scoots notebooks from @ian-r-rose, and @paultsan's dashboards. LMK if I missed anything.

[ian-r-rose]

I have no problem updating my notebooks and DAGs to point to the new tables.

[RMK0110]

https://gist.github.com/RMK0110/e18d2b877e5169f208a4ff33c4806424
A gist showing the script for sending the tables and triggers to a new schema.