Using undo followed by new instance creation with the same parameters causes unique constraint error
Description
With the addition of unique constraints that do not encompass the is_deleted field creating a new instance after using the undo button causes a unique constraint error.
How to reproduce
- Create a new gel with some lanes
- Create a new image for that gel and navigate to the Lanes tab
- Create a new lane for that image
- Click "Undo"
- Create another lane for that image
- See error in logs
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: image_lane.gel_id, image_lane.gel_lane_id, image_lane.image_id [SQL: INSERT INTO image_lane (gel_id, gel_lane_id, image_id, region, zero_line_points, is_deleted) VALUES (?, ?, ?, ?, ?, ?)]
Possible fixes
a) Convert is_deleted
into (something like) not_deleted[boolean, default=True, nullable]
and add that in the list of fields the constraints are checked against. In the application check either a True value for a non-deleted entry or a NULL value for a deleted entry. Unique constraint allows multiple null values so we can retain more than one deleted item in undo history but it still allows only one item to exist with a value True.
Pros: minimal work to implement for all but MS SQL Server - would suggest separate engine-based migration scripts.
Cons: MS SQL Server only allows a single NULL value - but it does allow to specify a conditional unique constraint.
b) Convert is_deleted[boolean]
into is_deleted[integer, default=0, not nullable]
and add that in the list of fields the constraints are checked against. In the application check either a zero value for non-deleted entries or a higher than 0 value for deleted entries. Assign a unique counter (or the ID value of the entry itself) into the is_deleted field to mark it as deleted. This way only one non-deleted entry with the value 0 can exist and all others are basically ignored by the constraint.
Pros: minimal work to implement while retaining all of the application logic.
Cons: Does not look like a pretty solution.
c) Keep the undo history only in memory on the application side.
Pros: keeps the database clean.
Cons: Hard to implement when using ORM, and would require substantial refactoring, have to restore multiple nested connected entries.