ScenarioDbManager - delete using session/cursor

[victorterpstra]

Delete all tables within one session/cursor, so we don't have to worry about the order of the delete?

See: https://docs.sqlalchemy.org/en/14/orm/session.html

[victorterpstra]

To use a session and execute RAW SQL, see: https://stackoverflow.com/questions/17972020/how-to-execute-raw-sql-in-flask-sqlalchemy-app

Combining the 2 references (?):

with Session(engine) as session, session.begin():
    session.execute(some_sql)
    session.execute(some_other_sql)

[victorterpstra]

Added support for Transactions in v0.5.3.0b Based on https://docs.sqlalchemy.org/en/14/core/connections.html (i.e. NOT using Session)

Basic usage in code:

with self.engine.begin() as connection:
    connection.execute(sql)

Unfortunately, using transactions does NOT avoid FK constraint violations when:

• Inserting tables in the 'wrong' order
• Deleting tables in the wrong order

The effects are different in SQLite vs DB2:

• Deleting tables in wrong order seems to be allowed in DB2, with or without transactions (?)
• In both cases the insert in wrong order causes FK violations

There is a flag 'deferrable' in ForeignKeyConstraint that is supposed to tell the DB to defer FK constraint checking to after the transaction is complete. But that doesn't seem to work in SQLite. Worse in DB2: this flag causes an exception creating the schema.

[victorterpstra]

Validated in v0.5.3.0b that FK violations (or any other exception) will cause a rollback for replace_scenario_in_db The following APIs on ScenarioDbManager support transactions (and rollbacks):

• create_schema
• replace_scenario_in_db
• drop_all_tables