Feature #4230
closedMake use of existing or implement own mechanism for handling SQL schema migrations.
100%
Description
Currently there is no mechanism for intelligent handling of SQL schema changes. This could hurt very badly when we have to change or improve the schema. Take a look at Alembic.
Related issues
Updated by Jan Mach over 6 years ago
- Status changed from New to In Progress
- % Done changed from 0 to 50
Made use of Flask-Migrate plugin built on top of Alembic to enable metadata database migrations.
Updated by Jan Mach almost 6 years ago
- Blocks Bug #4367: Enumeration tables not consistent for non array columns that can contain NULL added
Updated by Pavel Kácha almost 6 years ago
Idea: Use SQLAlchemy for Alerts - for schema management only, not access, where we need psycopg for speed. Schema changes/migrations could also be managed by Flask-Migrate/Alembic then.
Updated by Pavel Kácha almost 6 years ago
Updated by Pavel Kácha almost 6 years ago
- Blocks Feature #4383: Consider switching string classification fields to enums added
Updated by Pavel Kácha almost 6 years ago
- Blocks Feature #4275: Split jsonb column into its own table added
Updated by Jan Mach almost 6 years ago
- Target version changed from Backlog to 2.3
- % Done changed from 50 to 80
The database migrations for IDEA event database mentat_events are now implemented. I have moved this issue to the next release, because there is code already merged in the current development branch that depended performing the migrations on target system. It was a choice of either removing that commit, or going forward with implementation. I have decided to implement it immediately, because this issue is blocking many more features.
More in the attached commit.
Updated by Pavel Kácha almost 6 years ago
Cooool!
As I understand it, this is general mechanism, so migrations themselves have to be written by hand, so there is still a possibility to try to switch DDL for Events to SQLAlchemy? Does it still make sense (for help with mig recipes generation, for example)?
Updated by Jan Mach almost 6 years ago
- Status changed from In Progress to Resolved
- Assignee changed from Jan Mach to Pavel Kácha
- % Done changed from 80 to 100
Pavel Kácha wrote:
Cooool!
As I understand it, this is general mechanism, so migrations themselves have to be written by hand, so there is still a possibility to try to switch DDL for Events to SQLAlchemy? Does it still make sense (for help with mig recipes generation, for example)?
Yes, it is still possible, but I don`t think it is worth the time and effort now. The alembic is generic enough to work without full database schema and writing the incremental migrations by hand is quite a simple task. It can serve us well for quite some time.
From certain point of view the full SQLAlchemy schema of event database might be useful in many cases (unified database creation, management and cleanup tasks), but I think it should be separate task/issue, because the amount of work is significant.
I have just tested out the migration mechanism for event database on our development server. I consider this issue to be resolved and ready for closing.
Updated by Pavel Kácha almost 6 years ago
Jan Mach wrote:
Pavel Kácha wrote:
Cooool!
As I understand it, this is general mechanism, so migrations themselves have to be written by hand, so there is still a possibility to try to switch DDL for Events to SQLAlchemy? Does it still make sense (for help with mig recipes generation, for example)?
Yes, it is still possible, but I don`t think it is worth the time and effort now. The alembic is generic enough to work without full database schema and writing the incremental migrations by hand is quite a simple task. It can serve us well for quite some time.
Does it work like having SQLAlchemy schema only allows to use Alembic tools to generate suggestion of migration, which has to be reviewed and handcrafted and incorporated by hand?
From certain point of view the full SQLAlchemy schema of event database might be useful in many cases (unified database creation, management and cleanup tasks), but I think it should be separate task/issue, because the amount of work is significant.
Agreed.
Updated by Jan Mach almost 6 years ago
Pavel Kácha wrote:
Jan Mach wrote:
Pavel Kácha wrote:
Cooool!
As I understand it, this is general mechanism, so migrations themselves have to be written by hand, so there is still a possibility to try to switch DDL for Events to SQLAlchemy? Does it still make sense (for help with mig recipes generation, for example)?
Yes, it is still possible, but I don`t think it is worth the time and effort now. The alembic is generic enough to work without full database schema and writing the incremental migrations by hand is quite a simple task. It can serve us well for quite some time.
Does it work like having SQLAlchemy schema only allows to use Alembic tools to generate suggestion of migration, which has to be reviewed and handcrafted and incorporated by hand?
Yes, exactly. When the declarative base model for database is available the option of migration autodetection is available. However this autodetection has its limitations and can not detect all schema changes, so it must be reviewed by developer.
In my opinion the true advantage of having the SQLAlchemy model even for our event database would be in consolidation of database creation and management tasks, which would be awesome and is worth doing sometime, but not within this task.
From certain point of view the full SQLAlchemy schema of event database might be useful in many cases (unified database creation, management and cleanup tasks), but I think it should be separate task/issue, because the amount of work is significant.
Agreed.
Updated by Jan Mach almost 6 years ago
- Related to Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base. added
Updated by Jan Mach almost 6 years ago
I have added the SQLAlchemy schema suggestion as related issue #4628, I think this issue can now be safely closed.
Updated by Pavel Kácha almost 6 years ago
- Status changed from Resolved to Closed