Running database migration downgrades raises errors

[jdavcs]

Running database migration downgrades (manage_db.sh downgrade) raises errors on both sqlite and postgresql. The postgresql errors are handled by the approach in #15746. The sqlite error is different, and is related to downgrading a batch operation handled in sqlite with the help of a temporary table (due to sqlite's very limited support for alter operations).

This is a very brief description for now - I'll add more details as I investigate. This is not related to the 2 heads problem in #15771.

[jdavcs]

The view collection_job_state_summary_view causes a problem. Downgrading to revision d0583094c8cd on sqlite is impossible. The problem is caused not by that migration script, but by the view:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) error in view collection_job_state_summary_view: no such table: main.job

This can be verified by adding a new revision which adds a column to a table, then upgrading and downgrading -1. If the table is not job, then the downgrade operation will proceed normally. But if the table is job, then the above error will be raised.

I don't know what causes this yet. I'll investigate.

EDIT: This is not new: same happens under 22.05. Once this is fixed, the migrations testing implemented in #15811 will guard against such bugs.

[jdavcs]

This is not a galaxy error. For details, see sqlalchemy/alembic#1207.

And, actually... we might not need views at all. I'm exploring this possibility.

EDIT: This relates to one of the occurring errors. The rest have other causes.

[jdavcs]

Fixed in #15811.