Reevaluate use of Goose for migrations

[jrschumacher]

During the implementation of #647 we determined a migration was needed to change the original schema value. Ideally, we wanted to implement this as part of the Goose migrations under the policy service, with the intention to support varying situations.

This proved more complicated than expected. The solution we tried to take was creating a migration with a 001 id and using this script. If failed due to how we create schemas outside Goose and figuring out the right conditions to preserve state.

It's curious if using a tool like atlasgo.io had resolved it, since you describe the state of the tables rather than the migration and let the tool determine the migration path.

DO $$
BEGIN
  -- if opentdf schema does exist and opentdf_policy schema does exist but contains no tables, drop it
  IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'opentdf') THEN
    IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'opentdf_policy') THEN
      -- if opentdf_policy schema contains only goose_db_version table, drop it
      IF (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'opentdf_policy') = 1 THEN
        DROP SCHEMA opentdf_policy CASCADE;
        ALTER SCHEMA opentdf RENAME TO opentdf_policy;
      END IF;
    ELSE 
      ALTER SCHEMA opentdf RENAME TO opentdf_policy;
    END IF;
  END IF;
END $$;

[pflynn-virtru]

We used Alembic in the past, in a python service. The nice feature was offline mode: https://alembic.sqlalchemy.org/en/latest/offline.html
Please consider a tool with this feature

Reference:
https://github.com/JeffGradyAtVirtru/python-example