Plan improving public transport SQL schema migration

[haphut]

NB: The below points should not (necessarily) be fixed in the scope of this ticket, but only should be converted to own tickets IF they are deemed necessary.

Among the code of public transport model:

• [x] -- FIXME: constraint: allow only one copy of label to be valid at the same time. add after adding valid time intervals => to be handled in #533
• [x] -- FIXME: trigger constraint: direction of stop must match allowed directions of infrastructure link => handled in #534
• [x] -- FIXME: functions for updating and deleting
• [x] -- FIXME: constraint: The vehicle modes must match the vehicle types safe to traverse the infrastructure link on which the scheduled stop point is located. i.e. vehicle mode must be available on infrastructure_link -> vehicle_type -> vehicle_mode => handled in #534
• [X] -- FIXME: starting location and destination might become separate tables referred to from each scheduled stop point, e.g. "Erottaja". If that happens and description has form "starting loction - destination", normalize description out of this table. - comment will remind of this improvement when the time has come
• [X] -- FIXME: view constraint: try the no-gap window approach to sequence numbers: https://dba.stackexchange.com/a/135446 . then expose only the view and rename route.infrastructure_links_along_routes to internal_route.infrastructure_links_along_routes. Same applies to other sequence numbers. => Deemed not useful, since it does not leave gaps for the frontend fo insert new links between two existing ones
• [x] -- FIXME: trigger constraint: is_traversal_forwards must match available directions in infrastructure_links => handled in #534
• [x] #535
• [x] -- FIXME: constraint: allow only stops that are positioned along the route of the journey pattern => handled in #534

Not among the code:

• [X] (fixed by #508) create line

• [X] reorder columns within tables to reduce alignment holes thus optimizing table size. e.g. PointZ is 40 bytes, uuid 16 => deemed expensive to maintain and will not "pay itself back" => skipping

• [x] change scheduled stop point to quay and thus expand to fixed object model. maybe modify journey pattern to directly reference quays instead of through likely redundant joins: journey pattern -> scheduled stop point -> stop assignment -> quay

• [X] add ALTER DEFAULT PRIVILEGES lines for the schema extensions => the extensions schema was dropped because it required substantial extra work to have all services use the extensions from there (with hasura creating the extensions in the public schema if it cannot find them) => no need to do this

• [X] switch authorization to be schema-centric instead of user-centric so it is easier to add new schemas (GRANT ... schema TO user1, user2 instead of GRANT ... schema1, schema2 TO user) => This is deemed unnecessary, since we currently add new users (almost) as often as new schemas

• [x] (fixed by #497) consider also creating the DB users in the SQL schema migration files.

  Currently the DB users are created in jore4-deploy. The current approach requires preparing every database with the users before running the string interpolation and applying the SQL schema migration files, even when developing locally.

  Alternatively:

  1. create the usernames and passwords for all PostgreSQL roles into Azure vault in jore4-deploy,
  2. create and authorize only the SQL migrator role into the Azure DB with jore4-deploy and
  3. create and authorize the rest of the PostgreSQL roles in the SQL schema migration files.

  Pros:

  • It might be easier to apply the SQL schema migrations into a local database while developing SQL schemas. Just use the vanilla postgres superuser as the SQL migrator user. The placeholders for other users and their passwords are valid SQL as such so even running string interpolation is voluntary during development.

  Cons:

  • Not all DB roles are created and authorized in the same place.
  • The SQL migrator user has visibility to the passwords of other SQL users.

• [X] add validity period for lines, routes / journey patterns, stops => handled in #517

• [x] add validity checks for above mentioned validity periods => handled in #533

[jschummer]

Check that different traffic (bus) line entities can use the same line number. This case can occur when new municipalities (which previously had their own bus network) join the system.