search

Maria-Liakata-NLP-Group / annotations-interface

New iteration of the Annotations Interface tool
MIT License
0 stars 0 forks source link

Fix Alembic migration scripts for SQL table renames #42

Open dsj976 opened 12 months ago

dsj976 commented 12 months ago

Summary

There seems to be an issue when building the database from scratch with flask db upgrade, followed by flask db migrate. This last command gives the following error:

sqlalchemy.exc.NoSuchTableError: ps_dialog_turn_annotation_client

The tables to store client, therapist and dyad annotations where renamed with commits 90da551, b30fb0a and 7894798. It seems that the op.rename_table Alembic command is not sufficient to properly execute the table name change (e.g. how to handle foreign keys in other tables pointing to this table), or it's not working as expected for a SQLite database (tried replacing op.rename_table with raw SQL - ALTER TABLE ... RENAME TO... but that didn't solve the error either).

What needs to be done?

Who can help?

Updates

Lorem ipsum dolor sit amet, consectetur adipiscing.

dsj976 commented 12 months ago

Commit e27a9d0 modifies the relevant migration scripts. These scripts drop the old tables and replace them with new tables with new names. This is not the ideal scenario as all data is deleted by doing so, and we just want a name change. However, this is OK in the dev environment. See suggestion below for a better approach:

Things to bear in mind:

  1. You can use op.rename_table() to get an alter table statement which means that you shouldn't loose any data (worth double checking)...

  2. ...However, you will probably need to write a bunch of op.execute() s if you want auto-generated names (sequences, indexes, etc) to be renamed too.

  3. After manually renaming a table with SQL, you can use alembic revision --autogenerate as a shortcut to find out what keys and constraints you'll need to do manually.

  4. I add a CI step to check that both my upgrade() and downgrade() work as expected.