ScenarioDbManager - drop all tables using SQLAlchemy

[victorterpstra]

Use SQLAlchemy features to clear all tables from a schema (as part of the create_schema()). See ScenarioDbManager._drop_all_tables_transaction()

Drop all tables without having to loop and know all tables See: https://stackoverflow.com/questions/35918605/how-to-delete-a-table-in-sqlalchemy See https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.MetaData.drop_all

[victorterpstra]

Would this work (in ScenarioDbManager._drop_all_tables_transaction()) ?:

self.metadata.reflect(bind=connection)  # To reflect any tables in the DB, but not in the current schema
self.metadata.drop_all(bind=connection)

[victorterpstra]

Implementation:

my_metadata: sqlalchemy.MetaData = sqlalchemy.MetaData(schema=schema)
my_metadata.reflect(bind=connection, schema=schema, resolve_fks=False)
my_metadata.drop_all(bind=connection)

This works well with PostgreSQL (local and in IBM Cloud). But the reflect fails with DB2Cloud: seems the reflect is picking up FK constraints from different schemas

[victorterpstra]

Implemented 3 approaches for dropping tables. Code tries in order:

1. Using MetaData.reflect and then a MetaData.drop_all SQLAlchemy APIs. This has shown to be buggy in DB2 on Cloud
2. Using inspect to get the tables. Then we can do a SQL DROP TABLE
3. Use SQL 'DROP TABLE` based on the tables in the self.metadata

Would be great to find out why option 1 fails with DB2, but for now closing this issue