Spike: a template implementation of index & constraint migration in SqlConnector

[bajtos]

This is a follow-up task for the proposal outlined in loopbackio/loopback-next#2712.

The intention is to share as much of index/FK migration logic among all SQL connectors, to ensure consistent behavior. This spike will allow us to better understand the effort needed to implement migration in our SQL connectors (loopbackio/loopback-next#2332).

When working on the spike, use the existing implementation in MySQL, PostgreSQL and MSSQL connectors for inspiration. Try to preserve as much backwards compatibility as feasible. We should keep support for connector-specific behavior that's easy to implement in the shared base class. It's ok to drop support for use cases that would be difficult to preserve or that would complicate the codebase too much. Take notes about what will not work so that we can write helpful release notes and a migration guide.

The implementation needs to detect index/FK metadata not supported by SQL and report warnings to console.

Acceptance criteria

• A draft pull request with a PoC implementation showing a template implementation of INDEX and FOREIGN KEY migration in SqlConnector.
• In the PoC, propose how to test this functionality in automated way in all SQL connectors. It's important to test both autoupdate (incremental changes to existing schema) and automigrate (creating new tables). Do we want to create a shared test suite in loopback-connector to be executed by all SQL connectors to verify that they are supporting all common INDEX/FK configurations?
• A draft pull request in MySQL or PostgreSQL connector leveraging the new template implementation to replace connector-specific migration.
• Create a follow-up story (or multiple stories if needed) to implement the template in production quality.
• Create follow-up tasks to leverage the new template in our SQL connectors and thus let the connectors understand the new FK/INDEX definition syntax. For each SQL connector, create a new GH issue in the connector repository. As part of each story, we need to assess backwards compatibility and compile a list of breaking changes.
  • MySQL
  • PostgreSQL
  • MSSQL
  • Oracle
  • DB2
  • db2iseries
  • dashdb
  • informix
  • db2x
• Update Database migration should create SQL constraints for relations loopback-next#2332 with links to newly created GH issues.

[b-admike]

Timebox this spike for week and a half.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.