Soft deletion and unique constraint.

Support
#1

I found if entity is turned on soft deletion, then you will not be able to recreate an instance with same unique attribute even you’ve deleted the early one.
The doc said there are three ways to turn off it, but:

  1. EntityManager is required to be used on middle tier, if we don’t have a middle tier, then it is opt out.
  2. DataManager is seems perfect when you ready to manipulate data by code, what if it’s just standard
remove

?
3. For collection datasource, I’ve tried to set soft deletion to false either in xml or in

init()

of entity browser, but it seems not work, I still cannot recreate entity with same unique attribute once built before. For entity editor, there is no collection datasource at all.

BTW. I want to add a button besides, and let it truly purge/flush the unwanted soft deletions, then I can reuse the unique attribute, how to get it?

#2

if entity is turned on soft deletion, then you will not be able to recreate an instance with same unique attribute even you’ve deleted the early one

This problem does not exist when you use PostgreSQL, Microsoft SQL Server or Oracle, because for these databases you can create unique indexes either with “where” clause or including DELETE_TS column. Studio does this automatically.

In HSQL and MySQL null values in unique index make rows non-unique, so there is no simple solution for uniqueness of soft-deleted entities. A possible workaround for MySQL is provided here: MySQL Database Specifics - CUBA Platform. Developer’s Manual. For HSQL we just ignore this problem because we think this database should be used for prototypes only.

#3

Konstantin,
I would like to have the opposite case - having an entity with entirely unique column, including soft deleted ones. Is there a way to tell Studio not to generate partial index with (where DELETE_TS is null) but rather create an ordinary one?

#4

It’s impossible to make Studio generate such an index, but you can do the following:

  • In a manually created update script, drop the existing index and create a new one by your rules
  • Most probably, on the next generation, Studio will want to drop your index and create one by its standard rules
  • In this case, just Exclude the script and Studio will remember it and never generate it again.
1 Like
#5

Konstantin,
thank you, I’ll try to master Exclude function, I have never used it before.

#6

This works for an existing database, but for a brand new one created with Create database command all update scripts marked as executed (is_init=1) and the index retained from create scripts.

Is there a proper way to force an update script to be executed or may we use init data script to do the job?

#7

Yes, init scripts will contain indexes created by Studio rules. But you can drop them in the Init data (30.create-db.sql) script and create your own. This script is never touched by Studio, so you can keep your custom schema modifications in it.

#8

Thank you, this one (30.create-db.sql) was exactly I was talking about.